April 24, 2013 at 8:03 am
I have a table with 127 rows that keeps timing out.
the table is used to determine the ROLE of the logon user.
it has 2 fields
id, role
if a user (99%) of all users is 'just a plain' user then no rows are returned.
if a user is a manager or sr_manager we return a M or S from the role field.
There is a clustered unique index on the ID.
So simple right?
Here is the problem, I am getting timeouts on this table!!
SELECT ROLE FROM dbo.Mgr_List_Role WITH (nolock) WHERE id = 'xxx15119'
I get timeout expired. And again 99% of the time, no rows are returned.
I have spent a huge amount of time looking at every other query we run to insure optimization. this one has got me stumped.
How is that possible?
My server has 8 processors and a 16gig of RAM, Windows 2008 Enterprise server and has been running for 4 years now so I am sure it is not configuration problem.
April 24, 2013 at 8:23 am
Can i suggest you install Adam Machanic's sp_whoisactive:
http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx
and when you run your select, run sp_whoisactive to see what's going on?
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
April 24, 2013 at 8:42 am
It sounds like a blocking issue. You can use the software suggested or just look at sys.dm_exec_requests to see if there are processes holding locks that will prevent your query from selecting.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 24, 2013 at 8:47 am
Could it be that Mgr_List_Role is a view and not a table? If so you might want to check the view it self.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 24, 2013 at 8:49 am
it is a table.
Also I cannot install third party apps. it has to be SQL to find the problem. old school
April 24, 2013 at 8:58 am
did you try with [] like
SELECT [ROLE] FROM dbo.Mgr_List_Role WITH (nolock) WHERE id = 'xxx15119'
the Role is a keyword may be that's giving a time out
April 24, 2013 at 8:59 am
sp_whoisactive is just a TSQL sproc but as Grant has already mentioned, there are DMVs that will give you information on possible locking/blocking.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
April 24, 2013 at 9:06 am
What happens when you run the SELECT statement from Management Studio?
John
April 24, 2013 at 9:13 am
time out expired.. which one? connection timeout or command timeout? And yes, did you try the query from management studio as suggested by John Mitchell?
April 24, 2013 at 9:31 am
Is there an open transaction, holding locks to the table?
April 24, 2013 at 9:42 am
Grant Fritchey (4/24/2013)
It sounds like a blocking issue. You can use the software suggested or just look at sys.dm_exec_requests to see if there are processes holding locks that will prevent your query from selecting.
Because nolock hint is is spesified, no shared locks are required, and it seems unlikely that it is a blocking issue( Except when you are also running a long running transaction that modifies the table structure).
Also seems more likely to be a client issue.
April 24, 2013 at 9:44 am
the table is created once a day.
April 24, 2013 at 10:10 am
Is it then perhaps timing out when the table is created? Do you see Sch-S (schema stability) locks appearing?
April 24, 2013 at 10:13 am
If you run a query against sys.dm_exec_requests as I specified while the query is running, before it times out, you can see if it's getting blocked. That's what I would do. All other suggestions at this point are just speculation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply