SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 blocking
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

laddu
Constraint Violating Yak Guru

USA
329 Posts

Posted - 04/08/2013 :  15:26:34  Show Profile  Reply with Quote
Hi,

Our monitoring tool has reported that blocking on prodution

i checked the details

one user is running adhoc query on standalone database(not production database)

one more query is running by the same user against tempdb

SELECT SCHEMA_NAME(tbl.schema_id) AS [Schema], tbl.name AS [Name] FROM sys.tables AS tbl WHERE (tbl.name not like '#%') ORDER BY [Schema] ASC,[Name] ASC

is this Intellisense feature? (which helps the user to make correct SQL Queries)

one of production process (running aginst production db) is blocked by this user temp db query.


standalone database running on the same server production

So my question is user is trying to run adhoc query in standalone database and production db process is blocked. Just trying to understand how SQL works (because nothing in appln client or our server processes accesses this standalone databse)


Please clarify.

Edited by - laddu on 04/08/2013 15:55:06

James K
Flowing Fount of Yak Knowledge

3585 Posts

Posted - 04/08/2013 :  16:28:35  Show Profile  Reply with Quote
The query you have posted should complete within milliseconds unless the user explicitly acquired some type of lock and did not release it. If you see that query running for a long time, there is something not right with that session/query.

Is the query run by the user on the standalone database accessing any resources (tables/views) from the production database? If it does, that may point something.

Another possibility (although you said it is BLOCKing), is taht your server resources might be being taxed by the standalone db query.


Also, the tempdb is a shared resource, shared by all databases on the server. So there could be resource contention. Also, if there are global temp tables used that again could cause interactions between databases where you think there may be none.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000