Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 mysqladmin pr command to see which users are currently connected. Also, the slow query log shows the queries that take more than X seconds to be completed.

I would like to know if there are similar commands for SQL Server.

share|improve this question
add comment

2 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.