Odd Client tools problem | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Odd Client tools problem

Can anyone see if they can reproduce this behavoir. Its not a huge problem, but im just curious whats happening… In query analyser – sql 2000 Paste this, the example in BOL for showplan_text…. SET SHOWPLAN_TEXT ON
GO
USE pubs
SELECT *
FROM roysched
WHERE lorange < 5000
GO
SET SHOWPLAN_TEXT OFF
GO
When the database in query analyser is set to PUBS, this returns instantly, with the expected result set. If the database is set to any other (in the drop down list), the query stalls and never returns. It must be cancelled. I wonder if it occurs on anyone elses server?
Funny, I get the same result. The query has to be canceled. But if one moves the "USE pubs" statment above "SET SHOWPLAN_TEXT ON" it works. Same issue in SQL 7.<br /><br />If you do a profiler trace on the query that never finish, you’ll see that an execption occurs over and over again in and endless loop:<br /><br />Error: 208, Severity: 16, State: 1<br /><br />And selecting error 208 from the sysmessages table will give you this "Invalid object name ‘%.*ls'<br /><br />Funny that the query results in an endless loop (and that it is an example in BOL) [<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />]<br /><br />/Argyle
Ok, thanks. Its good to know its not just my server (well, kinda anyway <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />
Ouch. The query will consume 100% CPU (At least on my P3 500 Mhz test computer) when throwing the exceptions in a loop. And there is no need to be a sysadmin to execute it. Watch out for malicious users <img src=’/community/emoticons/emotion-4.gif’ alt=’:p‘ />. Interesting find Chappy. <br /><br />/Argyle
I tested som more on this. The error loop only occurs when you have the where clause added. If there is a where clause the error will loop endlessly and you will see the sql statment being inserted and removed from the cache between every error. In other words with this single query one is able to consume a lot of the available CPU power on the server, and possible bring down a smaller server (didn’t take 100% cpu on a dual machine I tested it on). And it doesn’t have to be a query against the pubs database that many remove. It could be against a table in msdb that the public role has access to. /Argyle
Same results on my machine (100% on a local 866MHz). It appears that for some reason the USE statement is not executed properly when SET SHOWPLAN is ON. I can only assume that the parser validates and appears to accept the initial ‘does object exist’ test, but the query later fails instead when query engine actually tries to access it. To demonstrate this, if you qualify the object, it doesnt suffer from the problem SET SHOWPLAN_TEXT ON
GO
USE pubs
select * from pubs.dbo.roysched where lorange < 5000
GO
SET SHOWPLAN_TEXT OFF
GO And to further demonstrate, if you query an unqualified table which exists in all databases, the problem is not apparent either (but the resulting plan refers to current database, and not to pubs database like youd expect). SET SHOWPLAN_TEXT ON
GO
USE pubs
select * from sysfiles where fileid > 0
GO
SET SHOWPLAN_TEXT OFF
GO Thanks for the help. I think its safe to say this is a bug, I best let SQL team know.
]]>