0

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?

  • 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. – Nathan Jolly Feb 28 '14 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 '14 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. – Mark Wilkinson Feb 28 '14 at 16:14
  • 1
    @Mark Wilkinson: Should have added a note of caution, indeed. – MicSim Feb 28 '14 at 16:16
  • 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? – Anti-weakpasswords Mar 1 '14 at 7:56
4

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.

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".

2

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.

  • 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? – Refracted Paladin Feb 28 '14 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 '14 at 21:16
0

Almost every write process will not only be slowed down by having to create an index, it will most likely also slow down further usage of said indices for READING due to fragmentation, every time that write process stores information for a different index than the write process before it, basically.

The system tables sys.dm_db_index_physical_stats and sys.indexes can provide you with information regarding, among other things, the fragmentation level and size of your indices.

  • Weird, I'm providing an answer for the misconception about indices only influencing write speed and give pointers on what to check to find if fragmentation is an issue and get downvoted? Because I didn't provide the complete code for the lazy? – Sascha Rambeaud Feb 28 '14 at 18:29

Your Answer

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

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