Read this post
http://blog.sqlauthority.com/2009/01/02/sql-server-2008-2005-find-longest-running-query-tsql/
-----------------------------------------------------------------------------------------------------------------
Using SQL Profiler
1. Run SQL Profiler; searching for queries which have a high number of "Reads". This mostly indicates "Index Scans" which could cause the long runtime. Check the "Execution PLan" of those queries to optimize e.g. Indexes etc.. According to this you should check the Statistics and Fragmentation degree of that table and fix it if necessary
2. Checking for Blocks. I proceed as described here: http://dynamicsuser.net/blogs/stryk/archive/2008/11/03/blocks-amp-deadlocks-in-nav-with-sql-server.aspx
3. Check "Wait Statistics" to find out if theres an I/O problem, e.g. a problem with network or disk-subsystem. Therefore I look into the "sys.dm_os_wait_stats" DMV
Original Post
http://www.sqlservercentral.com/Forums/Topic619606-360-1.aspx
Run this to see all running processes
exec sp_who2
Look for any task marked as runnable, with large numbers
run this to kill the process with SPID 58
kill 58
No comments:
Post a Comment