SQL Server Performance

Odd Client tools problem

Discussion in 'General DBA Questions' started by Chappy, Feb 15, 2003.

  1. Chappy New Member

    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?
  2. Argyle New Member

    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
  3. Chappy New Member

    Ok, thanks. Its good to know its not just my server (well, kinda anyway <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  4. Argyle New Member

    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
  5. Argyle New Member

    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
  6. Chappy New Member

    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.






Share This Page