What FIRST SQL command do I run to troubleshoot SQL Server performance?

Question

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

The purpose of this question is that when all the answers are compiled, it may be helpful to other users by executing their selection command to separate it where the problem may be. There are other troubleshooting messages regarding SQL Server performance, but they can only be useful for specific cases.

If you deploy and run your own SQL script,
then you could tell others that

• the goal of the script is
• returns (return value)
• to find out where the problem is

If you could provide a source for the script, submit it.

In my case

 sp_lock 

I run to find out if there are any locks (target) for returning SQL server lock information. Since the identifiers of the objects are displayed in the result set (thus, this is not so clear for a person), I would usually look at the result to see if there are abnormally many locks.




Feel free to update tags.

Answer 1

Why run a single query when an image is worth a thousand words!

I prefer to run free avaialable Dashboard Performance Reports .

They provide a complete overview of your server performance snapshots in seconds. Then you can select a specific area for investigation (blocking, ongoing requests, expectations, etc.) by simply clicking on the corresponding area in the dashboard.

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

One small caveat, I believe that they are only available in SQL 2005 and above.

Answer 2

 sp_who 

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

I want to see who, which machines / users are running requests, time, etc. I can also easily scan blocks.

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

Answer 3

sp_who_3 - Provides a lot of information available elsewhere, but in one good release. It also has several options that allow you to customize the output.

Answer 4

A custom query that combines what you expect in sp_who with DBCC INPUTBUFFER (spid) to get the last query text on each spid sorted by a locked / blocking graph.

Process data is available through master..sysprocesses.

Answer 5

sp_who3 returns standand sp_who2 output until you specify a specific spid, then specify 6 different sets of entries for this spid, including locks, blocks, what it is doing now, T / SQL is running and the statement in T / SQL, which currently working.

Answer 6

Ian Stirk has an excellent script that I would like to use, as described in detail in this article: http://msdn2.microsoft.com/en-ca/magazine/cc135978.aspx

In particular, I like the missing indexes:

 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 7

DBCC OPENTRAN to find out what is the oldest active transaction

Displays information about the oldest active transaction and the oldest distributed and unallocated replicated transactions, if any, in 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.

then sp_who2

Answer 8

I use the following queries:
Number of open / active connections in ms sql server 2005