Using (nolock) hint on a system table
-
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.
-
hi,
syslockinfo is not a 'fake' table. see syslockinfo in BOL
Paul
-
quote:
syslockinfo is not a 'fake' table. see syslockinfo in BOLI'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
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] -
thanks for that frank. something i'd missed.
at least I've learned something today, so the day hasn't been wasted
Paul
-
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
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply