 | |
Performance problem
microsoft.public.sqlserver.tools microsoft.public.sqlserver.tools
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
RE: Performance problem -
07-01-2009
, 05:17 PM
|
Excellent! Just a comment on your comments on the performance impact of the tool itself. What I found is that if you can get to the server in time to collect data when the problem is already there, you are probably okay to do all these joins on the DMVs. But often (1) you don't know when a performance problem may appear and (2) when it occurs, it may disappear before you get there, so you may have to collect data continuously or at a fairly high frequency (say once every few seconds). In that case, a monitoring tool that does heavy joins every time it collects perf data can easily become a top load contributor, kind of defeating the purpose. |
It is not intended to be a proactive monitoring tool, but one you use
when you have trouble.
If you have issues like you describe where things clog up for 10 seconds,
you are better off with a trace.
As for the DMV joins, my experience is that the most expensive part is
accessing sys.dm_tran_locks. There is a GROUP BY, but it is really the
DMV itself which is slow.
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx
#12
| |||
| |||
|
|
If you have issues like you describe where things clog up for 10 seconds, you are better off with a trace. |
not a good solution because (1) we don't know what we are looking for, (2)
the system is very busy with a lot of traffic, and (3) the system uses a lot
of scalar function calls. Doing a trace on a specific spid is fine on this
system. But doing a system-wide trace can be too expensive. And the key
problem is that SQL tracing just doesn't give quick enough feedback.
I found that taking snapshots of sysprocesses plus DBCC INPUTBUFFER and
fn_get_sql() for SQL2000 or a cross join with sys.dm_exec_sql_text for
SQL2005/2008 for only those spids that are consuming cpu/io is both simple
and good enough. And this can give us fast feedback to catch rogue spids.
Linchi
"Erland Sommarskog" wrote:
Quote:
|
Linchi Shea (LinchiShea (AT) discussions (DOT) microsoft.com) writes: Excellent! Just a comment on your comments on the performance impact of the tool itself. What I found is that if you can get to the server in time to collect data when the problem is already there, you are probably okay to do all these joins on the DMVs. But often (1) you don't know when a performance problem may appear and (2) when it occurs, it may disappear before you get there, so you may have to collect data continuously or at a fairly high frequency (say once every few seconds). In that case, a monitoring tool that does heavy joins every time it collects perf data can easily become a top load contributor, kind of defeating the purpose. Agreed. Beta_lockinfo is nothing you should run every ten seconds or so. It is not intended to be a proactive monitoring tool, but one you use when you have trouble. If you have issues like you describe where things clog up for 10 seconds, you are better off with a trace. As for the DMV joins, my experience is that the most expensive part is accessing sys.dm_tran_locks. There is a GROUP BY, but it is really the DMV itself which is slow. -- Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.