January 18, 2011 at 11:11 am
Let's say I run a DELETE statement that includes 90K rows on a database named RegReqs (see attachment) and it takes a few minutes to complete....
Why would everything from another database named ITDev (see attachment) on the same server get blocked?
January 18, 2011 at 11:13 am
First thing: Check for some cross database ownership chaining and cross DB calls from your procs. They're waiting, it seems, for access to the pages in memory (latches).
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 18, 2011 at 11:20 am
Has to be triggers or something going on behind the scenes.
"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
January 18, 2011 at 11:28 am
Craig Farrell (1/18/2011)
First thing: Check for some cross database ownership chaining and cross DB calls from your procs. They're waiting, it seems, for access to the pages in memory (latches).
Thanks Craig. It looks like cross db ownership chaining is off and there maybe some cross DB calls but definitely not with these two databases.
January 18, 2011 at 11:32 am
Grant Fritchey (1/18/2011)
Has to be triggers or something going on behind the scenes.
Grant, what's the best way to see if anything is happening behind the scenes? This happens every day. ITDev is used as a development database and we have a few production databases on the same server. We can't ever update, delete, insert, etc to ITDev without it causing issues with production DB's.
January 18, 2011 at 11:38 am
SQL08Kid (1/18/2011)
Grant Fritchey (1/18/2011)
Has to be triggers or something going on behind the scenes.Grant, what's the best way to see if anything is happening behind the scenes? This happens every day. ITDev is used as a development database and we have a few production databases on the same server. We can't ever update, delete, insert, etc to ITDev without it causing issues with production DB's.
Run screaming...
No, that's probably not a good solution. Get dev off of production. Those two should never cross, ever. It's like crossing the streams. As you're seeing, it would be bad.
Assuming you can't... lock down the production systems. Ensure that there is no cross-over on permissions. Honestly, that's going to be harder than simply moving dev off of production. Use a workstation or a hefty laptop for dev, but don't put it on production. Especially if the developers have the ability to hop by accident into the database.
Ask the business if they don't mind the downtime it would entail for you to restore the system after a developer forgets which database he's connected to. And that does happen, all the time. I've done it (once).
"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
January 18, 2011 at 2:43 pm
SQL08Kid (1/18/2011)
Grant Fritchey (1/18/2011)
Has to be triggers or something going on behind the scenes.Grant, what's the best way to see if anything is happening behind the scenes? This happens every day. ITDev is used as a development database and we have a few production databases on the same server. We can't ever update, delete, insert, etc to ITDev without it causing issues with production DB's.
Hmmmm... this is a curious case. Does the symptoms fit the disease...
What kind of data transience do you have on the rest of your production databases? Are they 99% read only?
If you don't have cross-database pollination, which it sounds like you shouldn't, then you have resource concurrency issues.
I'd start tracking page faults and seeing if you're dealing with some serious swapfile issues concurrent with low memory availability. Perfmon is about to become your best friend for trying to figure out what the heck's going on under the hood.
Oh, yeah, and as Grant said, get your Dev the heck off production. Silly DBA. 😀 Prod servers are for customers!
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 18, 2011 at 2:46 pm
What does DBCC INPUTBUFFER (or such) give you for the blocked process ?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply