3

This is related to my own post here

The SQL server starts everyday at 8:30 AM and stops around 9:30 PM.( So wait times will reset everyday) SQL server will be used by around 60 users simultaneously(used directly to modify data and also back-end database for application which we use in our office) The server has 8 GB ram. Every hour log backup will be done for most of the databases.(Backup done by our own backup tool which automates SQL backup)

As described in this article I used below query.

select *
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')
order by wait_time_ms desc; 

And the result was as shown below(Only top 25 rows are shown)

enter image description here

Now after getting this result i am confused whether these values are really high or it is normal scenario.( I have also taken performance counter values related to SQL,but not able to troubleshoot using that, as most articles only describe which performance counters to be used,not about analysing it properly or it is hard part for me to find performance of my server)So what is the ideal value for these? Whether I have performance problem in my sql server? How to decide it?

  • 1
    First I would use the query from Paul here that gives a bit more meaningful information: sqlskills.com/blogs/paul/… – Shawn Melton Jun 12 '14 at 12:44
  • For the performance counter translation I will reference this often: quest.com/techbrief/… However there are a handful of whitepapers from Microsoft Teams on counters that has some good information as well. – Shawn Melton Jun 12 '14 at 12:47
  • 4
    Nothing is too high or low unless you have a baseline to compare it to. Start capturing the waitstats and establish a baseline to compare. – Kin Shah Jun 12 '14 at 13:25
  • Agreed with @Kin about the baseline. What is "normal"? Is today better or worse than "normal"? Are users complaining about performance? Just looking at wait stats for high numbers is pretty counter-productive unless you have an actual problem to solve. Something is always going to be the highest wait consumer, next you have to figure out if it is actually a problem (and no there are no magic thresholds). – Aaron Bertrand Jun 12 '14 at 13:38
  • 1
    Glancing quickly at your waits, I would probably jump to the conclusion that you have very, very, very slow I/O. Are you using mechanical hard drives? Are they locally attached? Is there more than one? With so many I/O-related waits floating to the top, my first guess would be that you are running SQL Server off a thumb drive. And my advice without any further information at all would be to validate and fix the hardware. Just because you can run SQL Server on slow disks doesn't mean you should. – Aaron Bertrand Jun 12 '14 at 13:39
4

What you should think about is not the just absolute values of wait_time_ms or waiting_tasks_count you should also look at the average wait times. When you look at an average wait, you should ask yourself: "Is this a reasonable amount of time to wait on this resource?"

For example (if I am doing the math right) your PAGEIOLATCH wait is in the range of 4-5ms. This is "good" - because that is the response time of a hard drive to fetch an I/O request. However, if you are running on SSD (where this value is expected to linger in the 1ms range), it is "bad".

As another example, your WRITELOG wait is over 10ms. This is "bad" because if you do sequential I/O right, this value (even on spinning rust) should really be in the 1ms range.

However, all of this has to be seen in the context of what you are trying to achieve. If you are looking to "generally make things faster" (not an uncommon request) you need to look at the biggest waits (by wait_time_ms) first as they are most likely to hurt you. If your tuning is more targeted, look at specific wait types, even if they are lower on the list.

For example, you have a few LCK_M_S waits that average 70ms. They don't look important because there are so few of them. But maybe they affect some important users. If this is an OLTP system where queries should get in and out fast - 70ms is a "bad" number (because locks in short running transactions should be held only a few milli- or even microseconds).

Another example: If you are trying to make DML queries faster, you will want to target the WRITELOG and PAGEIOLATCH_EX waits. If you are optimising for reads, you would focus more on either reducing the PAGEIOLATCH_SH wait (for example with SSD or RAM) or making the database do less read I/O (for example by optimising indexing).

1

I've noticed there's no CXPACKET wait in you result set. Have you disabled parallelism and if so why? If you look at your MAXDOP setting I'm guessing it will be set to 1. There are occasions when this is ok but they are rare. I would enable parallelism on this box and tune your workload. I might reverse the order here. Tune then enable parallelism.

Additionally, I see BACKUPBUFFER and BACKUPIO, are you using a third party tool do complete your back ups? Are your backups being run to a network share?

Fist glance I'm with Aaron. There's something seriously wrong with you IO subsystem.

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.