Home Forums SQL Server 2008 SQL Server 2008 Performance Tuning All queries running slow after moving the db from staging server to production Server in SQL Server 2008 R2 RE: All queries running slow after moving the db from staging server to production Server in SQL Server 2008 R2

  • Have you checked wait stats?

    I got into the habit of using the below query for initial investigation into performance related prolems:

    This is from Paul Randals blog: http://www.sqlskills.com/blogs/paul/

    WITH [Waits] AS

    (SELECT

    [wait_type],

    [wait_time_ms] / 1000.0 AS [WaitS],

    ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],

    [signal_wait_time_ms] / 1000.0 AS [SignalS],

    [waiting_tasks_count] AS [WaitCount],

    100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],

    ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]

    FROM sys.dm_os_wait_stats

    WHERE [wait_type] NOT IN (

    N'CLR_SEMAPHORE', N'LAZYWRITER_SLEEP',

    N'RESOURCE_QUEUE', N'SQLTRACE_BUFFER_FLUSH',

    N'SLEEP_TASK', N'SLEEP_SYSTEMTASK',

    N'WAITFOR', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',

    N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',

    N'XE_TIMER_EVENT', N'XE_DISPATCHER_JOIN',

    N'LOGMGR_QUEUE', N'FT_IFTS_SCHEDULER_IDLE_WAIT',

    N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',

    N'CLR_AUTO_EVENT', N'DISPATCHER_QUEUE_SEMAPHORE',

    N'TRACEWRITE', N'XE_DISPATCHER_WAIT',

    N'BROKER_TO_FLUSH', N'BROKER_EVENTHANDLER',

    N'FT_IFTSHC_MUTEX', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',

    N'DIRTY_PAGE_POLL', N'SP_SERVER_DIAGNOSTICS_SLEEP')

    )

    SELECT

    [W1].[wait_type] AS [WaitType],

    CAST ([W1].[WaitS] AS DECIMAL(14, 2)) AS [Wait_S],

    CAST ([W1].[ResourceS] AS DECIMAL(14, 2)) AS [Resource_S],

    CAST ([W1].[SignalS] AS DECIMAL(14, 2)) AS [Signal_S],

    [W1].[WaitCount] AS [WaitCount],

    CAST ([W1].[Percentage] AS DECIMAL(4, 2)) AS [Percentage],

    CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgWait_S],

    CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgRes_S],

    CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgSig_S]

    FROM [Waits] AS [W1]

    INNER JOIN [Waits] AS [W2]

    ON [W2].[RowNum] <= [W1].[RowNum]

    GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],

    [W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]

    HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95; -- percentage threshold

    GO

    ---------------------------------------------------------

    It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
    David Edwards - Media lens[/url]

    Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
    Howard Zinn