December 1, 2009 at 6:00 am
We have a 3rd party designed Data Warehouse d/b (SS2K5) that has the AUTOSHRINK feature turned on. We recently encountered what appeared to be deadlocks. Using sp_who2 and sp_lock for the analysis, the spid under which AUTOSHRINK was running blocked a job (from the 3rd party vendor) which was executing a long running stored procedure performing numerous mass DELETE's. That job appeared to block some Business Objects reports and the entire hot mess (AUTOSHRINK, DELETE's and Business Objects reports) appeared to be deadlocked.
3 questions...
1). Can AUTOSHRINK cause or result in deadlocks?
2). From the majority of postings I've read, the recommendation is to have AUTOSHRINK switched off and replaced by scheduled DBCC SHRINK commands instead. Is that a fair representation of "the norm" (for want of a better expression)?
3). To remove the log jam I resorted to stopping/restarting the SQL Server instance? Was that the only way to address the deadlocks or was there another less intrusive method I could have used?
Thanks!
Chris.
December 1, 2009 at 7:00 am
Hi,
You can't control when it kicks in. Although it doesn't have any effect like long-term blocking, it does take up a lot of resources, both IO and CPU. It also moves a lot of data through the buffer pool and so can cause hot pages to be pushed out to disk, slowing things down further. If the server is already pushing the limits of the IO subsystem, running shrink may push it over, causing long disk queue lengths and possibly IO timeouts.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 1, 2009 at 7:05 am
this sounds like a block rather than a deadlock, the two are not the same thing.
In this circumstance I would have killed the shrink job.
It is good practice to turn off autoshrink. You should never shrink files unless on an ad-hoc basis when you really need the space and are sure the file won't have to grow back to that size again.
---------------------------------------------------------------------
December 1, 2009 at 7:07 am
Yes,I agree with George Sibbald
chris.worthington,Please run the Server-side trace for this situation
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 1, 2009 at 7:19 am
don't see a need for a trace in this instance. the sp_who2 identified the spid at the head of the blocking chain, a decision can be made from there as to whether that spid can be killed.
heres some code I stole from somewhere to identify current commands running
--How to isolate the current running commands in SQL Server. This query isolates the SQL in the batch
-- actually running at this point rather than the last command to execute
SELECT SDER.[statement_start_offset],
SDER.[statement_end_offset],
CASE
WHEN SDER.[statement_start_offset] > 0 THEN
--The start of the active command is not at the beginning of the full command text
CASE SDER.[statement_end_offset]
WHEN -1 THEN
--The end of the full command is also the end of the active statement
SUBSTRING(DEST.TEXT, (SDER.[statement_start_offset]/2) + 1, 2147483647)
ELSE
--The end of the active statement is not at the end of the full command
SUBSTRING(DEST.TEXT, (SDER.[statement_start_offset]/2) + 1, (SDER.[statement_end_offset] - SDER.[statement_start_offset])/2)
END
ELSE
--1st part of full command is running
CASE SDER.[statement_end_offset]
WHEN -1 THEN
--The end of the full command is also the end of the active statement
RTRIM(LTRIM(DEST.[text]))
ELSE
--The end of the active statement is not at the end of the full command
LEFT(DEST.TEXT, (SDER.[statement_end_offset]/2) +1)
END
END AS [executing statement],
DEST.[text] AS [full statement code]
FROM sys.[dm_exec_requests] SDER CROSS APPLY sys.[dm_exec_sql_text](SDER.[sql_handle]) DEST
WHERE SDER.session_id > 50
ORDER BY SDER.[session_id], SDER.[request_id]
---------------------------------------------------------------------
December 1, 2009 at 7:25 am
chris.worthington (12/1/2009)
2). From the majority of postings I've read, the recommendation is to have AUTOSHRINK switched off and replaced by scheduled DBCC SHRINK commands instead. Is that a fair representation of "the norm" (for want of a better expression)?
No. The recommendation is to turn autoshrink off and not run scheduled shrink operations. Why do you want to shrink? Databases tend to grow as more data gets put in them. It's in their nature.
Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2009 at 7:44 am
Hi,
Thanks for script George Sibbald
chris,
U can use this query also
select * from sys.sysprocesses where blocked <> 0
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 1, 2009 at 7:52 am
muthukkumaran (12/1/2009)
U can use this query also
select * from sys.sysprocesses where blocked <> 0
Sysprocesses is deprecated and should not be used in new development. It will be removed in a future version of SQL server. Use the session-related DMVs instead
sys.dm_exec_sessions
sys.dm_exec_requests
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2009 at 8:27 am
Thanks for DMV's Gail .Always i learned lot from u.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply