1

Our primary DB is 15GB in size. The server has 16GB of RAM. We are using the simple recovery model.

Lately we've noticed that our custom apps lookups - just basic reads with adhoc SQL statements - have slowed down noticeably. A couple seconds longer in most cases.

I'm not a SQL Server Admin and am not sure where to look next.

We have monitored the CPU which never seems to peak. Same with the RAM. We have tons of indexes, but I thought that was only an issue with write speed...

Any suggestions on what I could look at next?

6
  • It's going to be hard to compare without having metrics from before the change in behaviour, but does Resource Monitor or Performance Monitor show that your disk is busy? Once you subtract OS and SQL Server overheads, a 16GB server might only be giving you 12GB in buffered data. Feb 28, 2014 at 14:16
  • Outdated statistics are also a possible source for performance degradation. You could run EXEC sp_updatestats on the database using Management Studio to update the statistics.
    – MicSim
    Feb 28, 2014 at 14:52
  • 1
    @MicSim this very well could be, but depending on the state of things it would be a good idea to run this off hours. Updating stats will wipe out you plan cache which will cause a CPU spike while new plans are compiled, not to mention it could also generate a lot of IO and bump stuff out of the buffer pool, further slowing things down. This is only for a short period of time, but not knowing anything about the hardware or typical load, this could cause issues if ran in the middle of the day. Feb 28, 2014 at 16:14
  • 1
    @Mark Wilkinson: Should have added a note of caution, indeed.
    – MicSim
    Feb 28, 2014 at 16:16
  • 1
    In addition to the advice in the answers below, open up Perfmon, look at avg sec/read and avg sec/write i.e. IO latency. Also check your file autogrowth (should be in reasonable increments, NOT 1MB/10%), filesystem level fragmentation, and use DBCC LOGINFO to check for too many VLFs. In general, though, you should find out what the bottleneck is before looking at what to change. Network errors, even - perfmon can show you errored packets. Did a RAID group drop a disk and is in degraded mode perhaps? Mar 1, 2014 at 7:56

3 Answers 3

5

There could be many reasons for this. A few questions:

  • Do you do index maintenance?
  • Do you import data? Or is it all entered via a front-end application by users?

Option 1 - We could look at your wait stats and see if that helps pin-point a direction to look.

Option 2 - We could look at the execution plan of a slow-running query.

For option 1: Run this via Management Studio (it will run for 30 seconds), after you hit execute, run one of your slow-running queries in a second tab, then switch back to the previous tab and copy and paste the results here:

    DECLARE @FinishSampleTime   DATETIME

    IF OBJECT_ID('tempdb..#WaitStats') IS NOT NULL 
        DROP TABLE #WaitStats;

    CREATE TABLE #WaitStats
    (
        Pass TINYINT NOT NULL,
        wait_type NVARCHAR(60),
        wait_time_ms BIGINT,
        signal_wait_time_ms BIGINT,
        waiting_tasks_count BIGINT,
        SampleTime DATETIME
    );

    -- Populate #WaitStats with DMV data. In a second, we'll compare these.
    INSERT #WaitStats(Pass, SampleTime, wait_type, wait_time_ms, signal_wait_time_ms, waiting_tasks_count)
    SELECT
        1 AS Pass,
        GETDATE() AS SampleTime,
        os.wait_type,
        SUM(os.wait_time_ms) OVER (PARTITION BY os.wait_type) as sum_wait_time_ms,
        SUM(os.signal_wait_time_ms) OVER (PARTITION BY os.wait_type ) as sum_signal_wait_time_ms,
        SUM(os.waiting_tasks_count) OVER (PARTITION BY os.wait_type) AS sum_waiting_tasks
    FROM sys.dm_os_wait_stats os
    WHERE
        os.wait_type not in (
            'REQUEST_FOR_DEADLOCK_SEARCH',
            'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
            'SQLTRACE_BUFFER_FLUSH',
            'LAZYWRITER_SLEEP',
            'XE_TIMER_EVENT',
            'XE_DISPATCHER_WAIT',
            'FT_IFTS_SCHEDULER_IDLE_WAIT',
            'LOGMGR_QUEUE',
            'CHECKPOINT_QUEUE',
            'BROKER_TO_FLUSH',
            'BROKER_TASK_STOP',
            'BROKER_EVENTHANDLER',
            'SLEEP_TASK',
            'WAITFOR',
            'DBMIRROR_DBM_MUTEX',
            'DBMIRROR_EVENTS_QUEUE',
            'DBMIRRORING_CMD',
            'DISPATCHER_QUEUE_SEMAPHORE',
            'BROKER_RECEIVE_WAITFOR',
            'CLR_AUTO_EVENT',
            'DIRTY_PAGE_POLL',
            'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
            'ONDEMAND_TASK_QUEUE',
            'FT_IFTSHC_MUTEX',
            'CLR_MANUAL_EVENT',
            'SP_SERVER_DIAGNOSTICS_SLEEP',
            'HADR_CLUSAPI_CALL',
            'HADR_LOGCAPTURE_WAIT',
            'HADR_TIMER_TASK',
            'HADR_WORK_QUEUE'
        )
    ORDER BY sum_wait_time_ms DESC;

    -- Wait for the specified amount of time
    SET @FinishSampleTime = DATEADD(second,30,GETDATE());
    WAITFOR TIME @FinishSampleTime;

    -- Populate #WaitStats with DMV data. In a second, we'll compare these.
    INSERT #WaitStats(Pass, SampleTime, wait_type, wait_time_ms, signal_wait_time_ms, waiting_tasks_count)
    SELECT
        2 AS Pass,
        GETDATE() AS SampleTime,
        os.wait_type,
        SUM(os.wait_time_ms) OVER (PARTITION BY os.wait_type) as sum_wait_time_ms,
        SUM(os.signal_wait_time_ms) OVER (PARTITION BY os.wait_type ) as sum_signal_wait_time_ms,
        SUM(os.waiting_tasks_count) OVER (PARTITION BY os.wait_type) AS sum_waiting_tasks
    FROM sys.dm_os_wait_stats os
    WHERE
        os.wait_type not in (
            'REQUEST_FOR_DEADLOCK_SEARCH',
            'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
            'SQLTRACE_BUFFER_FLUSH',
            'LAZYWRITER_SLEEP',
            'XE_TIMER_EVENT',
            'XE_DISPATCHER_WAIT',
            'FT_IFTS_SCHEDULER_IDLE_WAIT',
            'LOGMGR_QUEUE',
            'CHECKPOINT_QUEUE',
            'BROKER_TO_FLUSH',
            'BROKER_TASK_STOP',
            'BROKER_EVENTHANDLER',
            'SLEEP_TASK',
            'WAITFOR',
            'DBMIRROR_DBM_MUTEX',
            'DBMIRROR_EVENTS_QUEUE',
            'DBMIRRORING_CMD',
            'DISPATCHER_QUEUE_SEMAPHORE',
            'BROKER_RECEIVE_WAITFOR',
            'CLR_AUTO_EVENT',
            'DIRTY_PAGE_POLL',
            'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
            'ONDEMAND_TASK_QUEUE',
            'FT_IFTSHC_MUTEX',
            'CLR_MANUAL_EVENT',
            'SP_SERVER_DIAGNOSTICS_SLEEP',
            'HADR_CLUSAPI_CALL',
            'HADR_LOGCAPTURE_WAIT',
            'HADR_TIMER_TASK',
            'HADR_WORK_QUEUE'
        )
    ORDER BY sum_wait_time_ms DESC;

    ----------------------------
    -- What happened: #WaitStats
    ----------------------------
    ;with max_batch as (
        select max(SampleTime) as SampleTime
        from #WaitStats
    )
    SELECT
        'WAIT STATS' as Pattern,
        b.SampleTime as [Sample Ended],
        datediff(ss,wd1.SampleTime, wd2.SampleTime) as [Seconds Sample],
        wd1.wait_type,
        c.[Wait Time (Seconds)],
        c.[Signal Wait Time (Seconds)],
        (wd2.waiting_tasks_count - wd1.waiting_tasks_count) AS [Number of Waits]
    FROM  max_batch b
        JOIN #WaitStats wd2 on
            wd2.SampleTime =b.SampleTime
        JOIN #WaitStats wd1 ON 
            wd1.wait_type=wd2.wait_type AND
            wd2.SampleTime > wd1.SampleTime
        CROSS APPLY (SELECT
            cast((wd2.wait_time_ms-wd1.wait_time_ms)/1000. as numeric(10,1)) as [Wait Time (Seconds)],
            cast((wd2.signal_wait_time_ms - wd1.signal_wait_time_ms)/1000. as numeric(10,1)) as [Signal Wait Time (Seconds)]) AS c
    WHERE (wd2.waiting_tasks_count - wd1.waiting_tasks_count) > 0
        and wd2.wait_time_ms-wd1.wait_time_ms > 0
    ORDER BY [Wait Time (Seconds)] DESC;


--  ===========================================================================
--  ********** Begin Clean-Up **********
--  ===========================================================================
    DROP TABLE #WaitStats

Like I said, this code takes 30 seconds to execute. So you hit 'Execute' then run your slow-running query in a second tab, then come back to the first tab and wait for the results. Please tell me if this is unclear.

For option 2: Queries that suddenly slow down make me think indexing troubles. If your indexes are badly fragmented it could effect execution plans, which could cause what you are experiencing.

Assuming you are using SQL Management Studio to run your queries, hit 'CTRL+M' before executing the queries. This will generate a graphical execution plan. If you could post a screen shot it might give us something to work from. This is assuming you are ok with users of StackExchange seeing your table/index names.

Depending on how comfortable you are with everything, you could also get a free copy of SQL Sentry Plan Explorer, it will allow you to upload anonymized execution plans so others can look at them in-depth. Get that and the SSMS add-on. When you execute a query after pressing 'CTRL+M' you can right-click on the plan to open it in Plan Explorer.

3

You need to look at the execution plan for the queries which are running slow as well as the wait stats if there's no problems in the execution plan.

Odds are the statistics for the indexes are simply out of date and SQL is creating bad execution plans for the queries. Updating the statistics for the tables using the UPDATE STATISTICS command will probably fix the problems. If it doesn't you'll need to dig into the execution plans to see where the problems are coming from.

If you look at the plan now and the estimated number of rows is 1 and the actual number of rows is a lot higher than 1 then the problem is statistics.

2
  • Thanks, I am gonna dive into this on Monday. You are saying, just so I'm sure, that the Indexes can cause slowness with the reads as well if there out of date? Feb 28, 2014 at 20:07
  • No, the statistics will cause a bad execution plan to be created which will cause SQL to miss use the indexes which will cause the slowness.
    – mrdenny
    Feb 28, 2014 at 21:16
2

Here's how you can check your index fragmentation (make sure you are connected to the desired database in SSMS before running). Note that this is being filtered for indexes fragmented >= 10% and have >= 100 pages. Feel free to post the results if you'd like suggestions on how to proceed.

SELECT
    i.[object_id],
    i.[index_id],
    OBJECT_SCHEMA_NAME( i.object_id, DB_ID() ) AS SchemaName,
    t.[name] AS TableName,
    i.[name] AS IndexName,
    (
    CASE i.[fill_factor]
        WHEN 0 THEN 100
        ELSE i.fill_factor
    END
    ) AS [FillFactor],
    PhysicalStats.[avg_fragmentation_in_percent],
    PhysicalStats.[pages]
FROM
    sys.dm_db_index_usage_stats AS ddius
    INNER JOIN sys.indexes AS i ON
        ddius.object_id = i.object_id
        AND ddius.index_id = i.index_id
    INNER JOIN sys.tables AS t ON
        ddius.[object_id] = t.[object_id]
    INNER JOIN (
        SELECT
            database_id,
            object_id,
            index_id,
            MAX( avg_fragmentation_in_percent ) AS avg_fragmentation_in_percent,
            SUM( page_count ) AS pages
        FROM
            sys.dm_db_index_physical_stats
            ( DB_ID(), NULL, NULL, NULL, 'SAMPLED' )
        GROUP BY
            database_id,
            object_id,
            index_id
    ) AS PhysicalStats ON
        ddius.[database_id] = PhysicalStats.[database_id]
        AND ddius.[object_id] = PhysicalStats.[object_id]
        AND ddius.[index_id] = PhysicalStats.[index_id]
WHERE
    PhysicalStats.[avg_fragmentation_in_percent] >= 10
    AND PhysicalStats.[pages] >= 100
ORDER BY
    PhysicalStats.avg_fragmentation_in_percent DESC,
    TableName,
    IndexName

For statistics, see if your database is currently set to "Auto Update Statistics". Right click on the database in SSMS, click on "Properties", and then "Options".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.