May 24, 2014 at 2:44 pm
Hi,
I am working with SP performance tuning.
As a part of SP tuning, I run DTA and it gave several Statistics creation. Is it need to create all those statistics? How to identify which statistics are really need to create?
Please let me know anyone work in performance environment, how often you do update statistics in dev, QA environments. Do we need to consider the index fragmentation in dev and test environments also? I am working in performance testing environment, not in functional test environment.
What kind of suggestions we need to give to the client to improve performance of the SP?
May 24, 2014 at 2:50 pm
DO NOT create everything DTA suggests. Test the recommendations one by one, consider implementing any that help, ignore the rest.
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
May 25, 2014 at 12:45 am
ramana3327 (5/24/2014)
Hi,I am working with SP performance tuning.
As a part of SP tuning, I run DTA and it gave several Statistics creation. Is it need to create all those statistics? How to identify which statistics are really need to create?
Please let me know anyone work in performance environment, how often you do update statistics in dev, QA environments. Do we need to consider the index fragmentation in dev and test environments also? I am working in performance testing environment, not in functional test environment.
What kind of suggestions we need to give to the client to improve performance of the SP?
Gail has already made the best recommendaton for DTA.
As for the rest, you really should be doing everything on the Dev and QA boxes that you do on the Production box. Index/stats maintenance, Point-in-Time backups, security and security checks, etc, etc. In fact, such things shoud first be done on the Dev and QA boxes LONG before they make it to Prod because, ostensibly, you're testing your DBA stuff in Dev and QA just like everyone else does. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 25, 2014 at 4:04 am
Development should be the path to production, so yes, everything you're going to do on production should be done on development first.
And never, ever trust the DTA. Test everything it suggests and evaluate it's suggestions against current indexes or statistics. It's notorious for bad suggestions, incorrect suggestions and duplicate suggestions. Also, remember, it's just a set of suggestions. You don't have to listen to it, especially if you test them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 26, 2014 at 4:45 pm
Ok. Thanks
May 27, 2014 at 8:08 am
Agree. When I use DTA and it suggests indexes I take a copy of the production db, restore to non prod server and then one by one add the indexes to see which ones really help and which ones dont.
May 28, 2014 at 12:20 am
I guess the DTA is helpful...to a point, ish...kinda.
I know its echoing the above but take what it suggests and evaluate yourself prior to implementing, it certainly has the tendancy to over index! I read a long time ago to try and understand its reasoning for any suggestion and see if you agree!
Its a bit like the false Buddha quote:
Believe nothing, no matter where you read it, or who said it, no matter if I have said it, unless it agrees with your own reason and your own common sense.
D
'Only he who wanders finds new paths'
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply