From: esquel@sommarskog.se   
      
    (amitpatel815@gmail.com) writes:   
   > So, here i am confused and not sure why my site  is not working smoothly   
   > while other site with more users is working absolutely fine and i have   
   > enough hardware resources on the server.   
   >   
   > Can anyone please help me or suggest me how can i resolve or   
   > troubleshoot this issue.   
   >   
      
   A start is to collect wait statistics. Run this procedure with ten minutes   
   in between at a time when the server is slow:   
      
   CREATE PROCEDURE wait_stats_monitor AS   
      
   SET NOCOUNT ON   
      
   DECLARE @latest datetime,   
           @now    datetime   
      
   SELECT @now = getdate()   
      
   IF object_id('waitstats') IS NULL   
   BEGIN   
     CREATE TABLE waitstats (   
         sample_time       datetime     NOT NULL,   
         wait_type         nvarchar(60) NOT NULL,   
         sample_length_sec int          NULL,   
         delta_tasks_count bigint       NULL,   
         delta_wait_time   bigint       NULL,   
         delta_signal      bigint       NULL,   
         max_wait_time_ms  bigint       NOT NULL,   
         tot_tasks_count   bigint       NOT NULL,   
         tot_wait_time     bigint       NOT NULL,   
         tot_signal        bigint       NOT NULL,   
         CONSTRAINT pk_waitstats PRIMARY KEY (wait_type, sample_time)   
     )   
      
     CREATE INDEX sample_time ON waitstats (sample_time)   
   END   
      
   IF NOT EXISTS (SELECT * FROM waitstats)   
   BEGIN   
     INSERT waitstats (sample_time, wait_type, max_wait_time_ms,   
                        tot_tasks_count, tot_wait_time, tot_signal)   
         SELECT @now, wait_type, max_wait_time_ms,   
                waiting_tasks_count, wait_time_ms, signal_wait_time_ms   
         FROM   sys.dm_os_wait_stats   
   END   
   ELSE   
   BEGIN   
      SELECT @latest = MAX(sample_time) FROM waitstats   
      
      INSERT waitstats (sample_time, wait_type, sample_length_sec,   
                        delta_tasks_count,   
                        delta_wait_time,   
                        delta_signal,   
                        max_wait_time_ms, tot_tasks_count, tot_wait_time,   
                        tot_signal)   
      SELECT @now, ws.wait_type, datediff(ss, m.sample_time, @now),   
             ws.waiting_tasks_count - m.tot_tasks_count,   
             ws.wait_time_ms        - m.tot_wait_time,   
             ws.signal_wait_time_ms - m.tot_signal,   
             ws.max_wait_time_ms, ws.waiting_tasks_count, ws.wait_time_ms,   
             ws.signal_wait_time_ms   
      FROM   sys.dm_os_wait_stats ws   
      CROSS  APPLY (SELECT TOP 1 *   
                    FROM   waitstats m   
                    WHERE  m.wait_type = ws.wait_type   
                    ORDER  BY sample_time DESC) m   
      WHERE  ws.waiting_tasks_count <> m.tot_tasks_count OR   
             ws.wait_time_ms        <> m.tot_wait_time   OR   
             ws.signal_wait_time_ms <> m.tot_signal   
   END   
      
      
   When you have that data run this query:   
      
   SELECT TOP 10 wait_type, delta_tasks_count, delta_wait_time,   
                 delta_signal   
   FROM   waitstats   
    WHERE [wait_type] NOT IN (   
           N'BROKER_EVENTHANDLER',         N'BROKER_RECEIVE_WAITFOR',   
           N'BROKER_TASK_STOP',            N'BROKER_TO_FLUSH',   
           N'BROKER_TRANSMITTER',          N'CHECKPOINT_QUEUE',   
           N'CHKPT',                       N'CLR_AUTO_EVENT',   
           N'CLR_MANUAL_EVENT',            N'CLR_SEMAPHORE',   
           N'DBMIRROR_DBM_EVENT',          N'DBMIRROR_EVENTS_QUEUE',   
           N'DBMIRROR_WORKER_QUEUE',       N'DBMIRRORING_CMD',   
           N'DIRTY_PAGE_POLL',             N'DISPATCHER_QUEUE_SEMAPHORE',   
           N'EXECSYNC',                    N'FSAGENT',   
           N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',   
           N'HADR_CLUSAPI_CALL',   
   N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',   
           N'HADR_LOGCAPTURE_WAIT',        N'HADR_NOTIFICATION_DEQUEUE',   
           N'HADR_TIMER_TASK',             N'HADR_WORK_QUEUE',   
           N'KSOURCE_WAKEUP',              N'LAZYWRITER_SLEEP',   
           N'LOGMGR_QUEUE',                N'ONDEMAND_TASK_QUEUE',   
           N'PWAIT_ALL_COMPONENTS_INITIALIZED',   
           N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',   
           N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',   
           N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',   
           N'SERVER_IDLE_CHECK',           N'SLEEP_BPOOL_FLUSH',   
           N'SLEEP_DBSTARTUP',             N'SLEEP_DCOMSTARTUP',   
           N'SLEEP_MASTERDBREADY',         N'SLEEP_MASTERMDREADY',   
           N'SLEEP_MASTERUPGRADED',        N'SLEEP_MSDBSTARTUP',   
           N'SLEEP_SYSTEMTASK',            N'SLEEP_TASK',   
           N'SLEEP_TEMPDBSTARTUP',         N'SNI_HTTP_ACCEPT',   
           N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',   
           N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',   
           N'SQLTRACE_WAIT_ENTRIES',       N'WAIT_FOR_RESULTS',   
           N'WAITFOR',                     N'WAITFOR_TASKSHUTDOWN',   
           N'WAIT_XTP_HOST_WAIT',          N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',   
           N'WAIT_XTP_CKPT_CLOSE',         N'XE_DISPATCHER_JOIN',   
           N'XE_DISPATCHER_WAIT',          N'XE_TIMER_EVENT')   
   ORDER BY delta_wait_time   
      
   Post this output here.   
      
      
   --   
   Erland Sommarskog, Stockholm, esquel@sommarskog.se   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)