Troubleshoot a Slow Database: SQL Server 2008
Once you have determined that it is SQL Server that is causing the performance issues, we need to find the query which is causing the problems.
Your primary performance investigations may have told you that it was one of three issues:
In SSMS, open a new query and type the following line of code:
CPU issue: "Select * from sys.sysprocesses order by cpu desc"
Disk Issue: "Select * from sys.sysprocesses order by physical_io desc"
Memory Issue: "Select * from sys.sysprocesses order by memusage desc"
Each of these order clauses tells you what the most expensive queries are in terms of what you’re ordering by. So the “order by memusage desc” clause puts the highest memory usage queries to the top of the list.
Further Investigations:
If the queries above do not identify any issues you’ll need to check some other things in the 'sys.sysprocesses' table.
Blocking:
In SSMS, execute the following code: "Select * from sys.sysprocesses order by blocked"
Look at the blocked column. A small amount of blocking in your database is ok provided that these are intermittent. However, if you’ve got a single process that’s blocking everything else then this is a problem. This block will subsequently block another process, sparking a chain reaction of blocked processes.
We need to identify the first process that is causing the chain reaction of blocked processes. It can be identified as the process that isn't blocked by anything else.
The numbers in the blocked column relate to the numbers in the spid column. Thus we can see that the spid; 114 is not being blocked by anything.
You can now hand this over to your DBA. If you happen to be the DBA you can find out what that spid is doing by executing the DBCC InputBuffer(spid) in SSMS query window:
DBCC InputBuffer(spid)
To look at spid 114 type: DBCC InputBuffer(114)
"lastwaittype":
Another more complex column to look at within 'sys.sysprocesses' is 'lastwaittype'. This can tell you which query the initial blocked spid is waiting to complete. More can be written about this but we will keep it simple for now and just take a look at the following values.
Cxpacket – This usually means that your process is waiting on other parallel processes to complete. So what’s probably happening is you have a process running on more than one CPU and one thread finished before the rest. To fix this: limit your parallelism either at the query or server level. It could also be a query tuning issue.
IO_Completion – Long wait times indicate disk issues. You could either not have enough disks for the operation or you could be running an array in degraded mode.
Network_io – If you see this for an extended amount of time, you’re waiting on the network, and this is an indication that there is too much traffic. Solution: Call your network team.
SOS_SCHEDULER_YIELD – Long wait times indicate CPU issues. You may not have enough CPU or you may have some queries that need tuning.
Useful Links:
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/
- Troubleshooting database slowness can be difficult. Most problems will present themselves to you right away.
- Sometimes the exact issue may not appear obvious. Furthermore once you find the issue, there isn't a satisfactory solution.
Once you have determined that it is SQL Server that is causing the performance issues, we need to find the query which is causing the problems.
Your primary performance investigations may have told you that it was one of three issues:
- A CPU issue
- A disk issue
- Memory Issue
In SSMS, open a new query and type the following line of code:
CPU issue: "Select * from sys.sysprocesses order by cpu desc"
Disk Issue: "Select * from sys.sysprocesses order by physical_io desc"
Memory Issue: "Select * from sys.sysprocesses order by memusage desc"
Each of these order clauses tells you what the most expensive queries are in terms of what you’re ordering by. So the “order by memusage desc” clause puts the highest memory usage queries to the top of the list.
Further Investigations:
If the queries above do not identify any issues you’ll need to check some other things in the 'sys.sysprocesses' table.
Blocking:
In SSMS, execute the following code: "Select * from sys.sysprocesses order by blocked"
Look at the blocked column. A small amount of blocking in your database is ok provided that these are intermittent. However, if you’ve got a single process that’s blocking everything else then this is a problem. This block will subsequently block another process, sparking a chain reaction of blocked processes.
We need to identify the first process that is causing the chain reaction of blocked processes. It can be identified as the process that isn't blocked by anything else.
| spid | blocked |
| 101 | 121 |
| 114 | 0 |
| 121 | 114 |
| 150 | 136 |
| 136 | 101 |
The numbers in the blocked column relate to the numbers in the spid column. Thus we can see that the spid; 114 is not being blocked by anything.
You can now hand this over to your DBA. If you happen to be the DBA you can find out what that spid is doing by executing the DBCC InputBuffer(spid) in SSMS query window:
DBCC InputBuffer(spid)
To look at spid 114 type: DBCC InputBuffer(114)
"lastwaittype":
Another more complex column to look at within 'sys.sysprocesses' is 'lastwaittype'. This can tell you which query the initial blocked spid is waiting to complete. More can be written about this but we will keep it simple for now and just take a look at the following values.
- Cxpacket
- IO_Completion
- Network_io
- SOS_SCHEDULER_YIELD
Cxpacket – This usually means that your process is waiting on other parallel processes to complete. So what’s probably happening is you have a process running on more than one CPU and one thread finished before the rest. To fix this: limit your parallelism either at the query or server level. It could also be a query tuning issue.
IO_Completion – Long wait times indicate disk issues. You could either not have enough disks for the operation or you could be running an array in degraded mode.
Network_io – If you see this for an extended amount of time, you’re waiting on the network, and this is an indication that there is too much traffic. Solution: Call your network team.
SOS_SCHEDULER_YIELD – Long wait times indicate CPU issues. You may not have enough CPU or you may have some queries that need tuning.
Useful Links:
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/
No comments:
Post a Comment