September 3, 2009 at 5:32 pm
I'm using SQL Server 2005 with a .43 TB database. The database has been implemented and deployed with minimal index implementation.
Using the SQL Server Performance Dashboard Missing Indexes report produces a list of 143 missing indexes.
I've run SQL Server Profiler using the Tuning Trace. This was followed by running the Database Engine Tuning Advisor and selecting the tuning trace output from Profiler as the DTA input. At the conclusion of the analysis, DTA gives a warning on the Consuming Workload: "54% of consumed workload has syntax errors. Check tuning log." The tuning log shows that hundreds of stored procedures could not be found. This is erroneous. These stored procedures exist and are being executed in production.
Additionally, DTA has not one recommendation for additional indexes.
I have used these software products for several years and I have never seen this situation before.
If anyone has any suggestions as to how I might be able to fix this problem, I would appreciate their ideas.
Thanks,
LC
September 4, 2009 at 7:29 am
Are you running the DTA against the production server? I hope not. If you're running it against another machine, you may need to validate that it has all the exact same procedures as the production instance.
BTW, DTA is a fairly weak tool. Considering the size of your system, you'd be better off following through with more traditional performance enhancing techniquest, measure, report, tune, measure, report, tune, repeat a gazllion times.
"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
September 4, 2009 at 1:44 pm
Grant Fritchey (9/4/2009)
Are you running the DTA against the production server? I hope not. If you're running it against another machine, you may need to validate that it has all the exact same procedures as the production instance.BTW, DTA is a fairly weak tool. Considering the size of your system, you'd be better off following through with more traditional performance enhancing techniquest, measure, report, tune, measure, report, tune, repeat a gazllion times.
Yes, running against the production server. Plenty of reserve power. No harm in doing it. DTA barely shows any resource utilization on PERFMON.
Our largest database (.43 TB) contains 140 tables. We have 6 databases. 80-90% of all database activity is INSERTs or UPDATEs. And, our application software is in a constant state of flux.
Optimizing indexes the old fashioned way (I know how to do it and I'm good at it), I could make analyzing and indexing/re-indexing my life's work. And since the databases and application software are in a constant state of flux, no choice of indexes would be permanent.
There has to be a better way. Any suggestions other than endlessly slogging through one table at a time, forever?
And, to my original question, any idea why DTA has provided nothing? Could I have a parameter set incorrectly? Have I chosen the wrong trace template, the Tuning trace template? Would a different trace template be more suitable for DTA?
LC
September 5, 2009 at 5:58 am
Every time I use the DTA I'm impressed by what it misses. Yes, you could try making adjustments to the template and you will get more suggestions, not necessarily good ones though.
As to why it missed so many procedures, another though occurred. Has there been some change to security? A new schema introduced or something along those lines that might mean the DTA is running with a different access level than the application that's calling the procedures?
"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
September 5, 2009 at 9:38 am
Grant,
I don't think so. I'm running DTA as a server and dba admin with authority to do anything so I don't think that's it but thanks for asking about it.
Are there any after-market software tools like DTA that can do a better job of performing some automated tuning recommendations?
Lee
September 6, 2009 at 5:48 am
crainlee2 (9/5/2009)
Grant,I don't think so. I'm running DTA as a server and dba admin with authority to do anything so I don't think that's it but thanks for asking about it.
Are there any after-market software tools like DTA that can do a better job of performing some automated tuning recommendations?
Lee
There are a couple out there, but I'm not informed at all as to how effective they are. Sorry.
"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
September 7, 2009 at 3:03 pm
>>There has to be a better way.
not sure why you think that. Actually there is a better way - a knowledgeable and experienced tuner doing his/her job. The permutations are nearly endless, which is why (to my knowledge) there is NOT a good automated tuner out there. And DTA really is to be avoided - it will do awful things to your database if allowed.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 7, 2009 at 7:37 pm
crainlee2 (9/4/2009)
Yes, running against the production server. Plenty of reserve power. No harm in doing it. DTA barely shows any resource utilization on PERFMON.
It's good to know that DTA is perfectly safe to run on production systems*.
crainlee2 (9/4/2009)
Our largest database (.43 TB) contains 140 tables. We have 6 databases. 80-90% of all database activity is INSERTs or UPDATEs. And, our application software is in a constant state of flux.
That sounds perfectly normal. Even for a 0.0000000000003910617773 YB database.
crainlee2 (9/4/2009)
Optimizing indexes the old fashioned way (I know how to do it and I'm good at it), I could make analyzing and indexing/re-indexing my life's work.
If you know how to do it and are good at it, it seems you just need to get on with it. Knowing how to prioritize, when to stop, and which indexes are likely to be useful in sufficiently wide cases is also a skill.
crainlee2 (9/4/2009)
And, to my original question, any idea why DTA has provided nothing? Could I have a parameter set incorrectly? Have I chosen the wrong trace template, the Tuning trace template? Would a different trace template be more suitable for DTA?
Most likely a permissions or configuration issue, it's hard to say. Much like the others, I don't use DTA except very very rarely to see if it has improved at all.
Paul
* this is a joke
September 9, 2009 at 7:17 am
The DTA didn't help me either. I used another software that provided me with much better results - I think it was called somethink like qure sql.. I'm sure Google can find it.
September 9, 2009 at 7:26 am
Thanks to all of you for your replies.
LC
September 9, 2009 at 7:39 am
>>The DTA didn't help me either. I used another software that provided me with much better results - I think it was called somethink like qure sql.. I'm sure Google can find it.
I have been wondering about that product/service for some time. I may have to check it out now.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 9, 2009 at 7:59 am
It may be an awesome and flawless product. I might have to check it out too.
I'd like to think there will always be a place for (appropriately remunerated) hand-tuning index wizards/gurus 😀
September 9, 2009 at 8:26 am
Paul White (9/9/2009)
It may be an awesome and flawless product. I might have to check it out too.I'd like to think there will always be a place for (appropriately remunerated) hand-tuning index wizards/gurus 😀
I certainly hope so too. However, if this product IS good at tuning (and/or index recommendations) I bet I can make an arrangement with the company to use it as a consultant while encouraging (or even training) client's to make use of it after I leave. I have arrangements like that with other product vendors. This company also made some overtures to the SQL MVP community IIRC.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 9, 2009 at 8:43 am
Interesting stuff. Thank you Mr Guru.
September 9, 2009 at 1:18 pm
I have only been able to use the DTA on single SQL statements effectively in the past.
I think the reason you did not get any results is that the DTA run completed with an error.
I would isolate several of the costliest statements identified in your trace (based on Reads, Duration etc.) and run those individually through the DTA, and that ideally on a non-prod environment.
Make sure no hypothetical indexes were created on the tables the DTA was in the process of analyzing when it aborted. This kind of junk is left behind when, for example, the DTA is aborted by the user prior to completion. Make sure you drop these indexes.
A good way of finding missing indexes is through looking at cached execution plans; the missing-index details is recorded in the plan itself (at least in SQL 2008 that is the case; I forget whether that's also the case in SQL2005).
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply