|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
using profiler to log master.sysprocesses entries ?
select * from master..sysprocesses where spid in (select blocked from master..sysprocesses) or blocked != 0 EXEC sp_who 'active' To help me ID possible locking performance problem. I keep the profiler running 24/7 looking for queries over 5000ms which also helps to ID problems. How can i run a profile on sys.processes to help me ID lock waits as they happen real time rather than executing the above script ? thanks Scott Scott
Have a look at http://sqlblog.com/blogs/adam_machanic/archive/2009/03/30/who-is-active-v8-40-now-with-delta-power.aspx Show quoteHide quote "Scott" <nospam123@yahoo.co.uk> wrote in message news:uOISNc6$JHA.2604@TK2MSFTNGP03.phx.gbl... > Im running the following: > > select * from master..sysprocesses where spid in (select blocked from > > master..sysprocesses) or blocked != 0 > > EXEC sp_who 'active' > > > > To help me ID possible locking performance problem. > > I keep the profiler running 24/7 looking for queries over 5000ms which > also helps to ID problems. > > > > How can i run a profile on sys.processes to help me ID lock waits as they > happen real time rather than executing the above script ? > > > > thanks > > Scott > > > > Scott (nospam123@yahoo.co.uk) writes:
Show quoteHide quote > Im running the following: Running Profiler against a busy production system continuously is not> > select * from master..sysprocesses where spid in (select blocked from > master..sysprocesses) or blocked != 0 > > EXEC sp_who 'active' > > > > To help me ID possible locking performance problem. > > I keep the profiler running 24/7 looking for queries over 5000ms which > also helps to ID problems. > > > > How can i run a profile on sys.processes to help me ID lock waits as they > happen real time rather than executing the above script ? very good, although if you have good filter the cost is limited. Nevertheless, server-side traces are to recommend. However, in your case, traces may not be the best at all. First run this, sp_confiugure 'blocked process threshold', <number> reconfigure (You may first have to run sp_configure 'show advanced options', 1). When blocked process threshold is in effect, SQL Server generates an event when a process have been blocked for that many seconds as you specified. (So of if you provided the number = 5, that will be 5 seconds.) This event can be captured in two ways: One is in a trace, by capturing the event "blocked process report". The other way to do it, is to create an event notification, which then can be used to generate alerts. The command for this is CREATE EVENT NOTIFICATION, and you can read more about it in Books Online. I am not giving any examples, because I've never used them myself. -- Erland Sommarskog, SQL Server MVP, esq***@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 Erland
Just start using beta-lock , it looks very promising Show quoteHide quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns9C427063616DBYazorman@127.0.0.1... > Scott (nospam123@yahoo.co.uk) writes: >> Im running the following: >> >> select * from master..sysprocesses where spid in (select blocked from >> master..sysprocesses) or blocked != 0 >> >> EXEC sp_who 'active' >> >> >> >> To help me ID possible locking performance problem. >> >> I keep the profiler running 24/7 looking for queries over 5000ms which >> also helps to ID problems. >> >> >> >> How can i run a profile on sys.processes to help me ID lock waits as they >> happen real time rather than executing the above script ? > > Running Profiler against a busy production system continuously is not > very good, although if you have good filter the cost is limited. > Nevertheless, server-side traces are to recommend. > > However, in your case, traces may not be the best at all. > > First run this, > > sp_confiugure 'blocked process threshold', <number> > reconfigure > > (You may first have to run sp_configure 'show advanced options', 1). > > When blocked process threshold is in effect, SQL Server generates an > event when a process have been blocked for that many seconds as you > specified. (So of if you provided the number = 5, that will be 5 seconds.) > > This event can be captured in two ways: One is in a trace, by capturing > the event "blocked process report". The other way to do it, is to create > an event notification, which then can be used to generate alerts. The > command for this is CREATE EVENT NOTIFICATION, and you can read more > about it in Books Online. I am not giving any examples, because I've > never used them myself. > > > -- > Erland Sommarskog, SQL Server MVP, esq***@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 > Default values show: blocked process threshold 0 86400 0 0
Changed to : blocked process threshold 0 86400 5 5 Running profiler looking for "Blocked Process Report". thank you scott just to confirm , the blocked process threshold values "config_value" and
"run_value is simply a reporting threshold, i.e it doesnt stop the process when it hits this value ? ... it simply generates a record when its above this value ? thanks Scott Scott (nospam123@yahoo.co.uk) writes:
> just to confirm , the blocked process threshold values "config_value" and Right. Having the system automatically kill processes sounds like a > "run_value is simply a reporting threshold, i.e it doesnt stop the process > when it hits this value ? ... it simply generates a record when its above > this value ? nightmare to me. -- Erland Sommarskog, SQL Server MVP, esq***@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 Running profiler 24x7 for the stated purpose is definitely a bad idea. Even
running SQL trace to capture blocking info is a bad idea because taking snapshots of sysprocesses would be much simpler and more efficient. After all, you'll have to process those trace files. The easiest way is to run a job at a regularly inteval to check sysprocesses for waittime greater than a threshold. Linchi Show quoteHide quote "Scott" wrote: > Im running the following: > > select * from master..sysprocesses where spid in (select blocked from > > master..sysprocesses) or blocked != 0 > > EXEC sp_who 'active' > > > > To help me ID possible locking performance problem. > > I keep the profiler running 24/7 looking for queries over 5000ms which also > helps to ID problems. > > > > How can i run a profile on sys.processes to help me ID lock waits as they > happen real time rather than executing the above script ? > > > > thanks > > Scott > > > > > 
Other interesting topics
Best Practices - using params passed to stored procedure
Locks and SQLAgent - Generic refresher, Alert Engine Connection server utilizing trusted connection to other SQL Server RESTORING drop table not showing up in the transaction log deleting DB strange BLOB beahaviour AWE on SQL Server2005 SQL 2k and autonumbering Backups & Transaction Files |
|||||||||||||||||||||||
