|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Performance problemI have a problem with a production SQL box.
The problem is that once or twice a day the box is getting its processor allocated for 100% for 10+ seconds. How can I find out what command or something causes that? Thanks Well task manager will tell you if it is SQL Server or something else. If it
is SQL then you can run a trace to see what is using lots of CPU cycles. -- Show quoteHide quoteAndrew J. Kelly SQL MVP Solid Quality Mentors "Mark Goldin" <mgoldin@UFANDD.LOCAL> wrote in message news:OXuKJwa%23JHA.5780@TK2MSFTNGP03.phx.gbl... >I have a problem with a production SQL box. > The problem is that once or twice a day the box is getting its processor > allocated for 100% for 10+ seconds. > How can I find out what command or something causes that? > > Thanks If you take quick snapshots of sysprocesses when the CPU usage is high, and
find the incremental cpu usage between the consecutive snapshots for each spid for the same session, you can find the sessions that are the largest CPU consumers at the time, and that can usually help you determine what SQL statements are contributing to the the high CPU usage. Linchi Show quoteHide quote "Mark Goldin" wrote: > I have a problem with a production SQL box. > The problem is that once or twice a day the box is getting its processor > allocated for 100% for 10+ seconds. > How can I find out what command or something causes that? > > Thanks > > Linchi Shea (LinchiS***@discussions.microsoft.com) writes:
> If you take quick snapshots of sysprocesses when the CPU usage is high, And if you instead use beta_lockinfo, which includes a CPU column, you> and find the incremental cpu usage between the consecutive snapshots for > each spid for the same session, you can find the sessions that are the > largest CPU consumers at the time, and that can usually help you > determine what SQL statements are contributing to the the high CPU > usage. can also see what that process is up to. beta_lockinfo is available on my website: http://www.sommarskog.se/sqlutil/beta_lockinfo.html -- 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
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. One solution is to collect perf data lightly (in terms of its perf impact) but regularly, and shift heavy processing to the reporting/query time. And if you store the data somewher else, you can afford to run fancy joins to look for the info you want. Linchi Show quoteHide quote "Erland Sommarskog" wrote: > Linchi Shea (LinchiS***@discussions.microsoft.com) writes: > > If you take quick snapshots of sysprocesses when the CPU usage is high, > > and find the incremental cpu usage between the consecutive snapshots for > > each spid for the same session, you can find the sessions that are the > > largest CPU consumers at the time, and that can usually help you > > determine what SQL statements are contributing to the the high CPU > > usage. > > And if you instead use beta_lockinfo, which includes a CPU column, you > can also see what that process is up to. > > beta_lockinfo is available on my website: > http://www.sommarskog.se/sqlutil/beta_lockinfo.html > > > -- > 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 > > > all these joins on the DMVs. But often (1) you don't know when a performance In my experience, performance problems that are shorter than your tolerance> 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). for querying the DMVs (depending on how hard you are querying them), are not really problems. The Resource Governor works in the same way... It only checks for long-running queries so often; anything shorter is ignored. > One solution is to collect perf data lightly (in terms of its perf impact) I do agree with this. The key is to collect the bare minimum on a routine> but regularly, and shift heavy processing to the reporting/query time. And if > you store the data somewher else, you can afford to run fancy joins to look > for the info you want. schedule. An extension would be to add code that dynamically determines that a problem is creeping up and at that point take the more detailed info for a few cycles. > In my experience, performance problems that are shorter than your tolerance That really depends on the nature of the app in question. Frankly, > for querying the DMVs (depending on how hard you are querying them), are not > really problems. performance problems that are long running (or relatively long running) are easy to troubleshoot, precisely because they can easily caught. The tough performance problems are those that come and go, but they are really problems. For instance, I have been called to find out why there are 10-20 second gaps in certain appl flow (like why don't we see trades coming in between 10:31:10 and 10:32:25?). If you have to query a bunch of DMVs and do fancy joins once every few seconds, you'd quickly find your monitoring query consuming a lot of CPU cycles. Linchi Show quoteHide quote "Aaron Bertrand [SQL Server MVP]" wrote: > > 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 my experience, performance problems that are shorter than your tolerance > for querying the DMVs (depending on how hard you are querying them), are not > really problems. The Resource Governor works in the same way... It only > checks for long-running queries so often; anything shorter is ignored. > > > One solution is to collect perf data lightly (in terms of its perf impact) > > but regularly, and shift heavy processing to the reporting/query time. And if > > you store the data somewher else, you can afford to run fancy joins to look > > for the info you want. > > I do agree with this. The key is to collect the bare minimum on a routine > schedule. An extension would be to add code that dynamically determines > that a problem is creeping up and at that point take the more detailed info > for a few cycles. > > Linchi Shea (LinchiS***@discussions.microsoft.com) writes:
> Excellent! Just a comment on your comments on the performance impact of Agreed. Beta_lockinfo is nothing you should run every ten seconds or so.> 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, 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 > If you have issues like you describe where things clog up for 10 seconds, Well for this type of issues in this particular environment, SQL tracing is > 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 Show quoteHide quote "Erland Sommarskog" wrote: > Linchi Shea (LinchiS***@discussions.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, 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 > > How do I properly read a column "cpu"? I see some very big numbers there.
Thanks Show quoteHide quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns9C3AF35BB3B90Yazorman@127.0.0.1... > Linchi Shea (LinchiS***@discussions.microsoft.com) writes: >> If you take quick snapshots of sysprocesses when the CPU usage is high, >> and find the incremental cpu usage between the consecutive snapshots for >> each spid for the same session, you can find the sessions that are the >> largest CPU consumers at the time, and that can usually help you >> determine what SQL statements are contributing to the the high CPU >> usage. > > And if you instead use beta_lockinfo, which includes a CPU column, you > can also see what that process is up to. > > beta_lockinfo is available on my website: > http://www.sommarskog.se/sqlutil/beta_lockinfo.html > > > -- > 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 > The values in that column is cumulative. And cumulative values usually don't
mean anything in terms of their magnitude unless you have a feel for teh time span in which the value is cumulated. You need to take a difference to find how many CPU milliseconds are consumed by that spid in that time period. In addition, when you do take that diff or delta, make sure it is still the same session because spid can be reused and doing a cpu diff for the same spid but different sessions is meaningless. Linchi Show quoteHide quote "Mark Goldin" wrote: > How do I properly read a column "cpu"? I see some very big numbers there. > > Thanks > > "Erland Sommarskog" <esq***@sommarskog.se> wrote in message > news:Xns9C3AF35BB3B90Yazorman@127.0.0.1... > > Linchi Shea (LinchiS***@discussions.microsoft.com) writes: > >> If you take quick snapshots of sysprocesses when the CPU usage is high, > >> and find the incremental cpu usage between the consecutive snapshots for > >> each spid for the same session, you can find the sessions that are the > >> largest CPU consumers at the time, and that can usually help you > >> determine what SQL statements are contributing to the the high CPU > >> usage. > > > > And if you instead use beta_lockinfo, which includes a CPU column, you > > can also see what that process is up to. > > > > beta_lockinfo is available on my website: > > http://www.sommarskog.se/sqlutil/beta_lockinfo.html > > > > > > -- > > 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 > > > > I understand about the same spid but I am not sure about:
<how many CPU milliseconds are consumed by that spid in that time period What is 'that time period'? Thanks Show quoteHide quote "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote in message news:BB36606B-4C07-48E3-AF26-44FD28C16A48@microsoft.com... > The values in that column is cumulative. And cumulative values usually > don't > mean anything in terms of their magnitude unless you have a feel for teh > time > span in which the value is cumulated. You need to take a difference to > find > how many CPU milliseconds are consumed by that spid in that time period. > In > addition, when you do take that diff or delta, make sure it is still the > same > session because spid can be reused and doing a cpu diff for the same spid > but > different sessions is meaningless. > > Linchi > > "Mark Goldin" wrote: > >> How do I properly read a column "cpu"? I see some very big numbers there. >> >> Thanks >> >> "Erland Sommarskog" <esq***@sommarskog.se> wrote in message >> news:Xns9C3AF35BB3B90Yazorman@127.0.0.1... >> > Linchi Shea (LinchiS***@discussions.microsoft.com) writes: >> >> If you take quick snapshots of sysprocesses when the CPU usage is >> >> high, >> >> and find the incremental cpu usage between the consecutive snapshots >> >> for >> >> each spid for the same session, you can find the sessions that are the >> >> largest CPU consumers at the time, and that can usually help you >> >> determine what SQL statements are contributing to the the high CPU >> >> usage. >> > >> > And if you instead use beta_lockinfo, which includes a CPU column, you >> > can also see what that process is up to. >> > >> > beta_lockinfo is available on my website: >> > http://www.sommarskog.se/sqlutil/beta_lockinfo.html >> > >> > >> > -- >> > 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 >> > >> >> Whatever time period you make it to be. So, if you are sampling these DMVs
once every 10 seconds, it would be the incremental for that 10 seconds. You need to determine how often you sample them. Ultimately, it depends on the nature of the app and what you want to use the sampled data for. For instance, if you are collecting the perf data for long term trending and/or capacity planning purposes, you probably cna live with sample intervals that are in minutes. Linchi Show quoteHide quote "Mark Goldin" wrote: > I understand about the same spid but I am not sure about: > <how many CPU milliseconds are consumed by that spid in that time period > What is 'that time period'? > > Thanks > > "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote in message > news:BB36606B-4C07-48E3-AF26-44FD28C16A48@microsoft.com... > > The values in that column is cumulative. And cumulative values usually > > don't > > mean anything in terms of their magnitude unless you have a feel for teh > > time > > span in which the value is cumulated. You need to take a difference to > > find > > how many CPU milliseconds are consumed by that spid in that time period. > > In > > addition, when you do take that diff or delta, make sure it is still the > > same > > session because spid can be reused and doing a cpu diff for the same spid > > but > > different sessions is meaningless. > > > > Linchi > > > > "Mark Goldin" wrote: > > > >> How do I properly read a column "cpu"? I see some very big numbers there. > >> > >> Thanks > >> > >> "Erland Sommarskog" <esq***@sommarskog.se> wrote in message > >> news:Xns9C3AF35BB3B90Yazorman@127.0.0.1... > >> > Linchi Shea (LinchiS***@discussions.microsoft.com) writes: > >> >> If you take quick snapshots of sysprocesses when the CPU usage is > >> >> high, > >> >> and find the incremental cpu usage between the consecutive snapshots > >> >> for > >> >> each spid for the same session, you can find the sessions that are the > >> >> largest CPU consumers at the time, and that can usually help you > >> >> determine what SQL statements are contributing to the the high CPU > >> >> usage. > >> > > >> > And if you instead use beta_lockinfo, which includes a CPU column, you > >> > can also see what that process is up to. > >> > > >> > beta_lockinfo is available on my website: > >> > http://www.sommarskog.se/sqlutil/beta_lockinfo.html > >> > > >> > > >> > -- > >> > 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 > >> > > >> > >> > >  
Other interesting topics
Profiler: Deadlock Graph - Associated ObjectID
How to manage the transaction log VS 2005 (BIDS) deploys the wrong data source connection string Enterprise Manager displays nvarchar as <binary> Appending Data via BCP No management Studio BCP Error 08001, Nativeerror 2 >>Maintenance Plan - backup Where is the Mgmt Studio ?? How to bcp in fixed position data while skipping certain positions |
|||||||||||||||||||||||
