March 7, 2011 at 8:57 am
Can I get some assistance with a DTA admin problem I’m having?
I have a server with a largish database on it (130 gigs).
I’m using SQL Server 2008 Standard Edition, SP1.
This is a dedicated SQL Server server.
The database response has been slow and as a result I created a file using SQL Profiler.
I let it run for a day.
I tried using this file with the DTA but that’s where I have a problem.
I let it run unlimited and also I limited the time. (Increments of 1 hour, 4 hours and even a 24 hour run)
And oddly the Advisor comes up without any recommendations.
I tried running just a few tables at a time, nothing.
I even tried just the biggest tables but once again nothing.
There should be at least one recommendation coming through, shouldn’t there?
Has anyone had a similar problem or perhaps let me know where/how I’m going wrong on this?
Any help/suggestions would be appreciated.
March 9, 2011 at 12:44 pm
I've never use DTA, so I can't help there, however I find the missing index query at the below blog quite helpful. Exercise caution - don't blindly create an index just because it says to.
http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx
Being you have a trace, you could try to isolate queries that take the longest to run (or ones that should run quicker) and investigate those directly.
If the app uses stored procedures, there was a great article posted a few days back on getting to the bottom of issues with the query execution plan.
March 10, 2011 at 12:02 am
thank you so much for the response.
i am looking at link you sent right now
will check it out
Once again, thank you so much
March 11, 2011 at 5:48 am
1) reread all the requirements for using DTA to do what you want it to do. you may have missed something
2) a full day's profiler run on a busy system is going to be a LOT of data. perhaps too much.
3) did you happen to have RPC: statement completed or TSQL: statement completed turned on? BAD news if you did and your system uses cursors or scalar UDFs
4) BE VERY CAREFUL USING DTA'S RECOMMENDATIONS!!! I have been paid for HUNDREDS of hours of consulting work to clean up DISASTEROUSLY BAD INDEXING put on client systems by people using DTA!!! Best thing you can do is simply hire a performance tuning professional to give your system a performance review. He/she can quickly identify all kinds of things that need to be addressed and help you learn how to identify/fix them yourselves. Huge win.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 11, 2011 at 6:01 am
I would not focus on the DTA issue, I would rather start from the original problem: slow sql server.
It doesn't necessarily mean you need more (or better) indexes.
So, slow at what? You have to find out the root cause of that slowness.
A good start point is looking at your wait stats:
http://www.sqlskills.com/BLOGS/PAUL/post/Wait-statistics-or-please-tell-me-where-it-hurts.aspx
Hope this helps,
Gianluca
-- Gianluca Sartori
March 13, 2011 at 11:48 pm
Thank you everyone.
Lessons learnt:
1. DTA not the be all and end all.
2. Logic is flawed on my point, should have focussed on issue not on what i wanted to be the answer to the issue.
3. SQLSERVERCENTRAL.com community is top notch, you guys gave me some solid stuff not only for this problem but a couple other glitches.
Much appreciated.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply