0

I am having an issue with an application as it is running supper slow. I don't know if it is a database issue or something else as the is nothing that I can find to be the issue.

I tried

select * from master..sysprocesses order by waittime DESC;

this query list 54 rows. This seems to be high but I am not sure if it is or not.

I don't know of there is some lock that is causing an issue or if a process that is causing locks that is bringing the application to a halt.

I can't do a restart to the server at the moment so I need to see if I can spot any SQL Server issues.

here is a small list of the output that I get form this query

    12  1692    0   0x007E  1204083901 ONDEMAND_TASK_QUEUE                                                                                                                                                                                                                                                                              
    15  1992    0   0x00A9  1204082395  BROKER_TRANSMITTER                                                                                                                                                                                                                                                                                  
    16  2328    0   0x00A9  1204082395  BROKER_TRANSMITTER                                                                                                                                                                                                                                                                                  
    7   1676    0   0x009D  1204050037  KSOURCE_WAKEUP                                                                                                                                                                                                                                                                                      
    3   1644    0   0x015A  1112854258  XE_DISPATCHER_WAIT                                                                                                                                                                                                                                                                                  
    14  1996    0   0x00AE  20577875    BROKER_EVENTHANDLER                                                                                                                                                                                                                                                                                 
    9   1780    0   0x00A2  3770    SQLTRACE_INCREMENTAL_FLUSH_SLEEP                                                                                                                                                                                                                                                                    
    2   1756    0   0x0158  1671    XE_TIMER_EVENT                                                                                                                                                                                                                                                                                      
    13  1684    0   0x0081  1364    CHECKPOINT_QUEUE                                                                                                                                                                                                                                                                                    
    6   1668    0   0x0080  1043    REQUEST_FOR_DEADLOCK_SEARCH                                                                                                                                                                                                                                                                         
    4   1784    0   0x0060  764 LAZYWRITER_SLEEP                                                                                                                                                                                                                                                                                    
    5   1660    0   0x007F  380 LOGMGR_QUEUE                                                                                                                                                                                                                                                                                        
    10  2144    0   0x0069  216 SLEEP_TASK                                                                                                                                                                                                                                                                                          
    11  0   0   0x0000  0   MISCELLANEOUS                                                                                                                                                                                                                                                                                       
    8   0   0   0x0000  0   MISCELLANEOUS                                                                                                                                                                                                                                                                                       
    1   1760    0   0x0000  0   PREEMPTIVE_XE_CALLBACKEXECUTE                                                                                                                                                                                                                                                                       
    17  0   0   0x0000  0   MISCELLANEOUS                                                                                                                                                                                                                                                                                       
    18  0   0   0x0000  0   MISCELLANEOUS                                                                                                                                                                                                                                                                                       
    19  0   0   0x0000  0   MISCELLANEOUS                                                                                                                                                                                                                                                                                       
    20  0   0   0x0000  0   MISCELLANEOUS                                                                                                                                                                                                                                                                                       
    21  0   0   0x0000  0   MISCELLANEOUS         

I am afraid to kill anything and that will cause other issues.

Not sure what to do at this point and how can I tell what is halting the service? What process can I kill?

  • 1
    How could you expect us to tell which process to kill. Focus on what is blocking Select * from sys.sysprocesses where blocked <> 0 You can also use Understanding and resolving [1]: support.microsoft.com/KB/224453 Plus what you showed are mainly system processes – Shanky Dec 3 '14 at 18:33
  • I am not expecting you to tell me exactly what is causing the problem, I am hoping for tips that will help me determine what is causing the issue. the query you have me returned nothing – Mike Dec 3 '14 at 18:35
  • In that case please take help of the link posted by me – Shanky Dec 3 '14 at 18:37
  • 5
    Stop using sysprocesses; it's been deprecated since SQL Server 2005 was announced. Grab sp_whoisactive from Adam Machanic. Short of that, use sys.dm_exec_requests - it will show you wait times and blocking. Also, your gut instinct shouldn't be to fire from the hip and kill processes - it should be to understand exactly why things are slow, and solve those things. Killing a process is like killing an ant with a shoe. Many more, just like it, are coming back. – Aaron Bertrand Dec 3 '14 at 18:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.