Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!eternal-september.org!feeder.eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail
From: Erland Sommarskog <esquel@sommarskog.se>
Newsgroups: comp.databases.ms-sqlserver
Subject: Re: SQL Server Performance Issue
Date: Tue, 10 Jun 2014 23:15:56 +0200
Organization: Erland Sommarskog
Lines: 121
Message-ID: <XnsA348ECABE86D8Yazorman@127.0.0.1>
References: <fc736b42-e860-420c-bab5-19bb37f9c0a8@googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit
Injection-Info: mx05.eternal-september.org; posting-host="0c412830dbc354c08130c47cfccdbc83"; logging-data="17193"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX1+7JfySz5WEFjx/tQEENd0a"
User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock: sha1:VbJvk/HGfatXUVLhyD4dS+2ygq8=
Xref: csiph.com comp.databases.ms-sqlserver:1764

 (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