May 5, 2006 at 2:04 am
I've been investigating performance problems on a live server. I know I should not run Tuning Advisor on a live server, but on a test server, but I did.
Twice it has left Schema Locks hanging on the server, even if the program has been shut down (closed). It may have been when I have hit the "Stop Analysis" or "Stop Analysis (with recommendations)", but I'm not sure which came first; the system hanging, or me beeing impatient and hitting "Stop".
Are there any known bugs with Database Engine Tuning Advisor ?
I've not been able to find anything on the internet on this.
TIA,
Henrik Staun Poulsen
MCDBA/2000
Stovi Software, Denmark
May 5, 2006 at 7:54 am
Sorry never used myself. However if locks are left usually it means a process is still running. Look in EM and see if you ca find the connections. Using KILL might free them. If nothing else stopping the SQL Server and restarting might fix as a last resort, however be aware if the server is hung it might require rebooting the server.
May 19, 2006 at 5:00 pm
You may like to check if dtaengine90.exe is running on the machine from where you initiated tuning the server. If thats the case you can use task manager to kill the process.
However this may have the undesirable effect of objects created by DTA to be left behind on the server being tuned. You can use BOL to get an idea of what the objects could be and drop these either manually or using a script.
DTA creates hypothetical indexes, indexed views and statistics during the tuning process (BOL documents the kinds of objects created by DTA) that can cause it to acquire schema locks.
July 28, 2006 at 3:59 am
Thank you very much, for these suggestions. I ended up "killing myself", and yes, there were stuff left behind on the server to delete. But I forgot what it was.
Just goes to show that it is dangerous to do optimization on a live server.
Best regards
Henrik Staun Poulsen
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply