What is your FIRST SQL command to run to troubleshoot SQL Server performance?

Question

When the SQL Server (2000/2005/2008) is running sluggish, what is the first command that you run to see where the problem is?

The purpose of this question is that, when all the answer is compiled, other users can benefit by running your command of choice to segregate where the problem might be. There are other troubleshooting posts regarding SQL Server performances but they can be useful only for specific cases.

If you roll out and run your own custom SQL script,
then would you let others know what

• the purpose of the script is
• it returns (return value)
• to do to figure out where problem is

If you could provide source for the script, please post it.

In my case,

sp_lock

I run to figure out if there are any locks (purpose) to return SQL server lock information. Since result set displays object IDs (thus not so human readable), I would usually skim through result to see if there are abnormally many locks.


Feel free to update tags

Answer 1

sp_who

http://msdn.microsoft.com/en-us/library/aa260384(SQL.80).aspx

I want to see "who", what machines/users are running what queries, length of time, etc. I can also easily scan for blocks.

If something is blocking a bunch of other transactions I can use the spid to issue a kill command if necessary.

Answer 2

I use queries like those:
Number of open/active connections in ms sql server 2005

Answer 3

sp_who_3 - Provides a lot of information available elsewhere but in one nice output. Also has several parameters to allow customized output.

Answer 4

A custom query which combines what you would expect in sp_who with DBCC INPUTBUFFER(spid) to get the last query text on each spid ordered by the blocked/blocking graph.

Process data is avaliable via master..sysprocesses.

Answer 5

sp_who3 returns standand sp_who2 output, until you specify a specific spid, then gives 6 different recordsets about that spid including locks, blocks, what it's currently doing, the T/SQL it's running, and the statement within the T/SQL that is currently running.

Answer 6

Why run a single query when a picture is worth a thousand words!

I prefer to run the freely avaialable Performance Dashboard Reports.

They provide a complete snapshot overview of your servers performance in seconds. You can then choose the a specific area to investigate (locking, currently running queries, wait requests etc.) simply by clicking the apporpriate area on the Dashboard.

http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

One slight caveat, I beleive these are only available in SQL 2005 and above.

Answer 7

Ian Stirk has a great script I like to use as detailed in this article: http://msdn2.microsoft.com/en-ca/magazine/cc135978.aspx

In particular, I like the missing indexes one:

SELECT 
    DatabaseName = DB_NAME(database_id)
    ,[Number Indexes Missing] = count(*) 
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY 2 DESC;

Answer 8

DBCC OPENTRAN to see what the oldest active transaction is

Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database. Results are displayed only if there is an active transaction or if the database contains replication information. An informational message is displayed if there are no active transactions.

followed by sp_who2