Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 blocking

Author  Topic 

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2013-04-08 : 15:26:34
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.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-08 : 16:28:35
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
   

- Advertisement -