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.
| Author |
Topic |
|
eversm
Starting Member
8 Posts |
 Posted - 2003-05-05 : 12:59:16
|
| I started having major blocking happening last week that has raised it's ugly head again. Wishing it away has not produced the intended results over the weekend. Can you help me in isolating the cause, if SQL related. We are using an ERP solution that could be causing my problems but I want to monitor some sort of statistics to rule out database issues. The wierd thing is that it always seems to be related to a certain table.Thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-05 : 13:01:47
|
| Have you tried running SQL Profiler and/or Perfomance Monitor yet to determine what the problem is (whether it be SQL related, hardware related, etc...)?Tara |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-05-05 : 13:05:11
|
| You can run a scheduled job which looks at master..sysprocesses for blocked queries and then saves the query using dbcc inputbuffer.seewww.nigelrivett.comsp_nrlocksfor a few ideas.Suspect a similar thing can be done with the profiler.Maybe just have a task logging blocked spids and use the profiler to log what is happenning.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
eversm
Starting Member
8 Posts |
Posted - 2003-05-05 : 13:12:41
|
What specific statistics would you look at in Performance monitor under my situation?quote:
Have you tried running SQL Profiler and/or Perfomance Monitor yet to determine what the problem is (whether it be SQL related, hardware related, etc...)?Tara
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-05 : 13:22:13
|
| Blocking can occur because queries are taking too long to run. Here are some of the things that I usually look at:Disk Usage Counters:· Physical Disk: Avg Disk Queue Length· Physical Disk: % Read Time· Physical Disk: % Write Time Memory Counters:· Process: Page Faults/sec: sqlservr· Process: Working Set· Memory: Available Mbytes· Memory: Pages/sec and Page Faults/sec· SQL Server: Buffer Manager· SQL Server: Memory Manager: Total Server Memory (KB)Processor Counters:· System: Processor Queue Length · Processor: % Privileged Time· Processor: % Processor Time· Process: % Processor Time: sqlservrSQL Server and Database Counters:· SQL Server: General Statistics· SQL Server: Locks: Number of Deadlocks/sec· SQL Server: Databases: Transactions/secFor an explanation of these counters, go to [url]http://www.sql-server-performance.com/[/url].For SQL Profiler, run a trace for about two hours. Then analyze the trace paying close attention to the Duration of the queries. You will want to run the trace when the blocking normally occurs. You might want to send the trace through the index tuning wizard to determine if any new indexes need to be added to your database.TaraEdited by - tduggan on 05/05/2003 13:22:57 |
|
|
|
chadmat
The Chadinator
1974 Posts |
|
|
|
|
|
Major Blocking going on!