August 12, 2011 at 10:28 am
Greetings all;
I have a fairly complex stored procedure that I want to run through the Database Tuning Advisor. I've done this countless times before. However it appears that whenever I run DTA I get no results however I get an "Estimated Improvement :99%"
1) has anyone else experienced this?
2) I'm sort of out of any clues as to why this isn't working.
I have run DTA on 3 different platforms and 2 different releases of SS2008R2 (10.50.1600 & 10.50.1617)
It is driving me nuts! :w00t:
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
August 12, 2011 at 11:11 am
I'm a little confused by your post. Have you run other procs through DTA and gotten different results or do all your attempts to use DTA end up with no data?
If the later, have verified that your profiler trace is picking up the proc properly (hasn't been filtered out of the results) so that you have something to run through your DTA?
August 12, 2011 at 11:36 am
Brandie;
Sorry for the confusion.... What I should have said DTA is no longer providing me any results. Any script I run through DTA comes back with the same results.... IE no recommendations and yet Estimated Improvement: 99%.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
August 12, 2011 at 12:14 pm
What changed between the time it was providing results and the time it stopped?
Any OS service packs? Any SQL Server hot fixes / SPs? Any major issues with your database(s)?
August 12, 2011 at 12:16 pm
Kurt W. Zimmerman (8/12/2011)
Any script I run through DTA comes back with the same results.... IE no recommendations and yet Estimated Improvement: 99%.
And being totally snarky (forgive the humor during your time of frustration), I'm sure SQL Server would work wonderfully if you just removed all the stored procedures from it. @=) 99% improvement's worth, even.
August 12, 2011 at 12:43 pm
Brandie Tarvin (8/12/2011)
What changed between the time it was providing results and the time it stopped?Any OS service packs? Any SQL Server hot fixes / SPs? Any major issues with your database(s)?
ahhhh.... I moved to SS2008R2 from SS2008 (upgrade).... I just ran DTA against another script on a SS2005 box and it worked. (from my workstation, to which the save version of DTA doesn't produce results against SS2008R2)
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
August 12, 2011 at 12:45 pm
Brandie Tarvin (8/12/2011)
Kurt W. Zimmerman (8/12/2011)
Any script I run through DTA comes back with the same results.... IE no recommendations and yet Estimated Improvement: 99%.And being totally snarky (forgive the humor during your time of frustration), I'm sure SQL Server would work wonderfully if you just removed all the stored procedures from it. @=) 99% improvement's worth, even.
No problem with the snarky humor... .. 😀
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
August 15, 2011 at 5:52 am
Kurt W. Zimmerman (8/12/2011)
Brandie Tarvin (8/12/2011)
What changed between the time it was providing results and the time it stopped?Any OS service packs? Any SQL Server hot fixes / SPs? Any major issues with your database(s)?
ahhhh.... I moved to SS2008R2 from SS2008 (upgrade).... I just ran DTA against another script on a SS2005 box and it worked. (from my workstation, to which the save version of DTA doesn't produce results against SS2008R2)
This may be a Microsoft bug or an issue with the way the upgrade went, then. Check Technet to see if anyone else has posted similar issues. If not, you might want to post a bug report on Microsoft Connect to get them to look at it.
August 15, 2011 at 10:33 am
Well today I'm a happy man... I found the issue that I have been having issues with. It turns out to be a GRANT issue in the MSDB database. I reran DTA and I have results.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
August 15, 2011 at 10:37 am
Would you post the details of what you did to fix the problem?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply