|
I have a Microsoft SQL Server instance on my server with more than 100 databases and users. Sometimes, the SQL Server process use 100% of server CPU and I want to know which query/user/database is causing this and using so much resources. For MySQL, I use the I would like to know if there are similar commands for SQL Server. |
||||
|
|
|
There's a ton of dynamic management views (DMVs) you can use to get the data, but the easiest way is to use Adam Machanic's sp_WhoIsActive stored proc. Here's a video on how to use it: http://www.brentozar.com/archive/2010/09/sql-server-dba-scripts-how-to-find-slow-sql-server-queries/ And you can download it from here: http://sqlblog.com/blogs/adam_machanic/archive/tags/sp_5F00_whoisactive/default.aspx The output includes columns for the database name, CPU cycles used, query duration, and more. |
|||
|
|
|
The CPU at 100% is only a problem if it stays at 100% for a prolonged period of time. More important is disk IO and memory usage. If these are high then you will need to do some tuning of the databases, such as getting better hardware of splitting the databases across servers. The next action to make is to run profiler ( http://msdn.microsoft.com/en-us/library/ms181091.aspx ). You could do a couple of passes with this. Firstly you could just look at connections to see which DB's are being used the most and which are not used. Next look for long running queries - these are the ones to tune as they will be using the most resources. If the database is transactional, look for the queries that are run very frequently (put the profiler output into a database table and count by query) and look at tuning them. You could also explore the system tables which store some of the information you need ( http://msdn.microsoft.com/en-us/library/aa260604%28v=sql.80%29.aspx ) If performance is still an issue, then you need to look at new hardware (bigger server, faster disk, more memory, faster network) or to split the databases across servers. |
|||
|
|
