Post reply

Using (nolock) hint on a system table

  • October 14, 2003 at 3:18 am

    #84618

    Hi!

    Trying to run the following query with (nolock):

    SET @who_tlocks = (SELECT count(*) from master..syslockinfo (nolock)

    WHERE req_spid = @who_spid)

    When I use this query in a every minute job, the server gets slow.

    I know that some system tables are fake tables, so any hints do not work for them.

    Is there a way to know, if a particular system table is a fake table?

    Is master..syslockinfo a fake table?

    What can be other explanations for slow server performance when running this query?

    Thanks.

  • October 14, 2003 at 3:45 am

    #478096

    hi,

    syslockinfo is not a 'fake' table. see syslockinfo in BOL

    Paul

  • October 14, 2003 at 3:55 am

    #478098

    quote:

    syslockinfo is not a 'fake' table. see syslockinfo in BOL

    I'm not sure if this is correct

    Try

    SELECT OBJECTPROPERTY(OBJECT_ID('sysprocesses'),'TableIsFake')

    SELECT OBJECTPROPERTY(OBJECT_ID('syslockinfo'),'TableIsFake')

    SELECT OBJECTPROPERTY(OBJECT_ID('syscolumns'),'TableIsFake')

    should return

    -----------

    1

    (1 row(s) affected)

    -----------

    1

    (1 row(s) affected)

    -----------

    0

    (1 row(s) affected)

    From BOL

    quote:

    The table is not real. It is materialized internally on demand by SQL Server.

    1 = True

    0 = False

    Frank

    http://www.insidesql.de

    Edited by - Frank Kalis on 10/14/2003 04:50:55 AM

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • October 14, 2003 at 4:53 am

    #478103

    thanks for that frank. something i'd missed.

    at least I've learned something today, so the day hasn't been wasted 🙂

    Paul

  • October 14, 2003 at 5:02 am

    #478109

    quote:

    thanks for that frank. something i'd missed.

    at least I've learned something today, so the day hasn't been wasted 🙂

    lucky one!!!

    SNAFU day here

    Frank

    http://www.insidesql.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • October 14, 2003 at 5:38 am

    #478115

    So there is no way to run a query with (nolock) on this table and such queries will always degrade serve performance?

    It is really bad day

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply