|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Profiler Filtering recommendation on Locks and Deadlocks1. What data column and data column value would indicate there may be a problem? 2. What filter or filters should be used? 3. Also, since this is a template, are all of these Events and Data Columns necessary? EVENTS Errors and Warnings Exception Missing Column Statistics Missing Join Predicates Locks Lock:Acquired Lock:Cancel Lock:Deadlock Lock:Deadlock Chain Lock:Escalation Lock:Released Lock:Timeout Performance Execution Plan Scans Scan:Started Scan:Stopped Security Audit Audit Login Audit Logout Sessions Existing Connection Stored Procedures RPC:Completed RPC:Starting SP:StmtCompleted SP:StmtStarting DATA COLUMNS Groups Event Class Columns SPID ApplicationName BinaryData Mode EndTime StartTime DatabaseID CPU HostName Duration Error ObjectID IndexID IntegerData Reads Writes TextData These might hep:
http://www.support.microsoft.com/?id=224453 Blocking Problems http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000 Blocking -- Show quoteAndrew J. Kelly SQL MVP "Robert Richards via SQLMonster.com" <fo***@SQLMonster.com> wrote in message news:53d84af05deb453b925bed3009babcff@SQLMonster.com... >I came across a template for diagnosing potential locks and deadlocks. It >recommended the following events and data columns. Since I have never >analyzed a lock or deadlock from Profiler (I am afraid I would not >recognize a lock or deadlock being reported in Profiler unless it stopped >and said so), my questions are: > 1. What data column and data column value would indicate there may be a > problem? > 2. What filter or filters should be used? > 3. Also, since this is a template, are all of these Events and Data > Columns necessary? > > EVENTS > Errors and Warnings > Exception > Missing Column Statistics > Missing Join Predicates > > Locks > Lock:Acquired > Lock:Cancel > Lock:Deadlock > Lock:Deadlock Chain > Lock:Escalation > Lock:Released > Lock:Timeout > Performance > Execution Plan > Scans > Scan:Started > Scan:Stopped > Security Audit > Audit Login > Audit Logout > Sessions > Existing Connection > Stored Procedures > RPC:Completed > RPC:Starting > SP:StmtCompleted > SP:StmtStarting > > DATA COLUMNS > Groups > Event Class > Columns > SPID > ApplicationName > BinaryData > Mode > EndTime > StartTime > DatabaseID > CPU > HostName > Duration > Error > ObjectID > IndexID > IntegerData > Reads > Writes > TextData > > -- > Message posted via http://www.sqlmonster.com Thanks. I came across those sites in my search and have been running sp_blocker_pss80 script, as well as running DBCC TraceOn (3605,1204,-1). The problem I am having is that I suspect locks due to some command timeouts. In Performance Monitor, the CPU, Memory, and Disk I/O are all running at acceptable levels. I am also running Profiler based on duration of queries and the duration of any given query is under 2 seconds, most of them being only fraction of a second. I also have a Profiler set up to evaluate blocking, but the results coming from that are largely why I am in this forum. In my blocking Profile trace, I have a filter of Severity less than 24. But even with that, there is large amounts of data being collected.
So, with that background, and what I have researched, (and that is why I am using this forum) is that, like I said, "I am afraid I would not recognize a lock, deadlock, or block being reported in Profiler unless it stopped and said so". Since there is large amounts of data being collected, once it is collected, I do not know what to search for in the collected data that might indicate their is a potential problem. So, the issue is not entirely a filter, although that would be helpful, but also what data column and data column value would indicate there may be a problem, based upon the data columns I have included in this write up? Robert,
I am not sure why you are filtering on Severity less than 24 as that is essentially everything. Looking for blocking is best done with sp_who2, sp_lock or the blocker script you have. Blocking in and of itself is a normal type occurrence and you will go crazy trying to track every lock. Look for long running blockers via one of the above mention scripts or sps. As for deadlocks you have the trace running and any deadlocks will be reported in the error log. Do you see any? If you want to look for them in profiler then use the Lock:Deadlock and Lock:Deadlock Chain events. If you have so much data being generated by profiler you might want to cut down most everything else so you can focus on the deadlocks. If you want to see one then simply open two QA windows and update two seperate tables in the reverse order one step at a time. In QA Window # 1 issue a BEGIN TRAN and then Lock a row in TableA. In QA window #2 issue a BEGIN TRAN and lock a row in Table B. Then in QA #1 attempt to lock the same row in Table B and when you attempt to lock the same row in TableA you should see a deadlock. -- Show quoteAndrew J. Kelly SQL MVP "Robert Richards via SQLMonster.com" <fo***@SQLMonster.com> wrote in message news:50cfc847fe8048a79595a1e20d5d8ac0@SQLMonster.com... > Thanks. I came across those sites in my search and have been running > sp_blocker_pss80 script, as well as running DBCC TraceOn (3605,1204,-1). > The problem I am having is that I suspect locks due to some command > timeouts. In Performance Monitor, the CPU, Memory, and Disk I/O are all > running at acceptable levels. I am also running Profiler based on duration > of queries and the duration of any given query is under 2 seconds, most of > them being only fraction of a second. I also have a Profiler set up to > evaluate blocking, but the results coming from that are largely why I am > in this forum. In my blocking Profile trace, I have a filter of Severity > less than 24. But even with that, there is large amounts of data being > collected. > > So, with that background, and what I have researched, (and that is why I > am using this forum) is that, like I said, "I am afraid I would not > recognize a lock, deadlock, or block being reported in Profiler unless it > stopped and said so". > > Since there is large amounts of data being collected, once it is > collected, I do not know what to search for in the collected data that > might indicate their is a potential problem. > > So, the issue is not entirely a filter, although that would be helpful, > but also what data column and data column value would indicate there may > be a problem, based upon the data columns I have included in this write > up? > > -- > Message posted via http://www.sqlmonster.com  |
|||||||||||||||||||||||
Other interesting topics