SQL logs
in [Programming SQL]
|
Prev: running a job
Next: Sum on Rounded column
From: Simon Whale on 28 Jun 2010 15:52 Hi, i have at the moment in SQL 2005 a period of time when processes lock and slow down a back office system, but when reported they seem to disappear. What i am asking is that from the logs in SQL Server can i trace period where deadlocks or wait requests happen? any links would great :) Many thanks Simon
From: Erland Sommarskog on 28 Jun 2010 17:51 Simon Whale (simon(a)nospam.com) writes: > i have at the moment in SQL 2005 a period of time when processes lock and > slow down a back office system, but when reported they seem to disappear. > > What i am asking is that from the logs in SQL Server can i trace period > where deadlocks or wait requests happen? > > any links would great :) You can use the configuration option "blocked process threshold (s)". If you set this to 5, then if a process is blocked for more than 5 seconds, it will generate an event which you can catch with a Profiler trace or an event notification. You also mention deadlocks. The best way to get information about deadlocks is to enable trace flag 1222. In the startup parameters to SQL Server add this ;-T1222. (Make sure that you don't add any spaces anywhere.) -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Uri Dimant on 29 Jun 2010 03:04 Simon Also you can download a word document download.microsoft.com/.../Performance_Tuning_Waits_Queues.doc where there are two stored procedures to track wait statistics "Simon Whale" <simon(a)nospam.com> wrote in message news:eK9YxrvFLHA.588(a)TK2MSFTNGP06.phx.gbl... > Hi, > > i have at the moment in SQL 2005 a period of time when processes lock and > slow down a back office system, but when reported they seem to disappear. > > What i am asking is that from the logs in SQL Server can i trace period > where deadlocks or wait requests happen? > > any links would great :) > > Many thanks > Simon >
From: Michael MacGregor on 29 Jun 2010 16:13 You can also use Profiler to monitor the locks and deadlocks but don't do them in the same trace. Monitor deadlocks in a separate trace always and don't filter the trace to a specific database, it won't work. Under Events, expand Locks and select Deadlock Graph and select at least the TextData column, but it's worth adding some or all of the other columns, such as SPID. The information in the TextData can be saved as an XML but the graphic presentation in Profiler provides a lot of information. If you want to associate this with more information about the transactions involved then in another trace, select other events under the Locks category, such as Lock:Deadlock and Lock:Deadlock Chain. You can also use other events to collect information about Locks in general, such as Lock:Acquired, Lock:Released, Lock:Cancel, etc.. For more details about the Events and information contained under each column look up SQL Server Event Class Reference in BOL, the URL is ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/0f0fe567-e115-4ace-b63c-73dc3428c0f6.htm Michael MacGregor Database Architect
From: Erland Sommarskog on 29 Jun 2010 17:46
Michael MacGregor (nospam(a)nospam.com) writes: > If you want to associate this with more information about the transactions > involved then in another trace, select other events under the Locks > category, such as Lock:Deadlock and Lock:Deadlock Chain. You can also use > other events to collect information about Locks in general, such as > Lock:Acquired, Lock:Released, Lock:Cancel, etc.. Never trace these events on a production server. You may be able to find some hefty filtering that reduces the number of events. However, SQL Server will still generate the events, and that alone may take quite some toll. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |