October 26, 2011 at 12:06 pm
Hello Everyone,
I ran a server side trace this morning and used the same to see what the DETA recommends for improvement in performance. Interestingly it recommended to create the indexes that were already existing and a lot statistics (multiple statistics for the same table). Can anyone please suggest if I should follow the recommendations it suggested or how should I approach with the results.
Also the estimated improvement I see is 70%. Which is really good.
Thanks a ton in advance.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
October 26, 2011 at 12:09 pm
Have you looked at the fragmentation of your indexes?
Thanks,
Jared
Jared
CE - Microsoft
October 26, 2011 at 12:25 pm
Yes..They are heavily fragmented.
With the below conditions
avg_fragmentation_in_percent>=25
index_type_desc<>'HEAP'
page_count>25
I see the below results in AvgPageFragmentation column:
97.3684210526316
97.4930362116992
98.9464442493415
32.6492537313433
99.1079961771265
98.8682650167569
99.3258426966292
99.0776871230933
97.5475959987093
99.4094488188976
99.3220338983051
98.6636971046771
95.7746478873239
98.8826815642458
97.9591836734694
96.3414634146341
99.0182328190743
99.3698630136986
99.1575677007872
98.8620199146515
99.0721649484536
99.0215264187867
98.6111111111111
99.2857142857143
99.3220338983051
97.2972972972973
95.1965065502183
98.8695540087651
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
October 26, 2011 at 12:28 pm
Do you have an index maintenance schedule in place?
As of now, you should rebuild all your indexes.
October 26, 2011 at 12:31 pm
Well there is your first problem 🙂 Can you rebuild your indexes at some time soon?
Jared
Jared
CE - Microsoft
October 26, 2011 at 12:43 pm
Yes...I can rebuild the indexes tonight...But the last time I rebuild the indexes (1 week back) the developers responded that there was no improvement in performance. Hence I went with the approach of identifying if any new indexes will improve the performance and generated a server side trace and used DETA for this.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
October 26, 2011 at 12:47 pm
Well, besides your indexes being fragmented you should look at the queries that are running slow. Performance is many times due to bad queries. Also, are your developers coding the queries into their code or are they calling on stored procs? Also, read this post http://qa.sqlservercentral.com/Forums/Topic553269-146-1.aspx
Thanks,
Jared
Jared
CE - Microsoft
October 26, 2011 at 12:48 pm
Sapen (10/26/2011)
I ran a server side trace this morning and used the same to see what the DETA recommends for improvement in performance. Interestingly it recommended to create the indexes that were already existing and a lot statistics (multiple statistics for the same table). Can anyone please suggest if I should follow the recommendations it suggested or how should I approach with the results.
Never implement any DTA recommendations without thorough testing to see if they really do help performance.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 26, 2011 at 12:54 pm
Here is another good link for you to look at http://qa.sqlservercentral.com/articles/Performance/71001/
Thanks,
Jared
Jared
CE - Microsoft
October 26, 2011 at 12:55 pm
Technically I dont see any stored procs under the programmability-->stored procedures folder of this database.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
October 26, 2011 at 1:00 pm
Sapen (10/26/2011)
Technically I dont see any stored procs under the programmability-->stored procedures folder of this database.
Ok, which has what to do with DTA and your indexes?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 26, 2011 at 1:06 pm
Gail,
I dont see anything in the stored procedures folder and in the trace file I see sp_executesql followed by the queries and the trace was filtered with only a specific database.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
October 26, 2011 at 1:08 pm
GilaMonster (10/26/2011)
Sapen (10/26/2011)
Technically I dont see any stored procs under the programmability-->stored procedures folder of this database.Ok, which has what to do with DTA and your indexes?
Sorry, I asked if the devs were using SPs or embedding their queries in their code. Simply to see if it will be harder or easier for the OP to optimize the queries being placed against the db.
Jared
Jared
CE - Microsoft
October 26, 2011 at 1:16 pm
The other thing I noticed is the indexes are fragmented heavily back within less than a week. I understand there are lots of inserts and updates happening. But is that the only reason?
Also, I am not shrinking the databases.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
October 26, 2011 at 1:28 pm
Couple of things I would check, and I'm sure Gail will have a more concise reply 🙂
1. Make sure that you are SURE no shrinking is happening. It can sometimes be from an old maintenance plan or even a third party maintenance application. No harm in double-checking.
2. Get a good feeling as to how quickly your indexes become fragmented; i.e. within an hour, 4 hours, 12, 1day... You get the idea?
3. If it takes a week for your indexes to become fragmented, how long does it take until 10-15%? Maybe, if it is plausible for your business, you can reorganize nightly or whatever fits your model.
4. Look at those 2 links I posted earlier and work on optimizing queries and other pieces of the db. There are reasons for and against using stored procs (most of the against is from a dev's perspective), but work with them and maybe they will meet you halfway.
Jared
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply