Ideal values for wait time in SQL server
-
I am using sql server 2008 r2 express. I wanted to trouble shoot the sql server performance.
So as described in this article[/url] 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)
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?
-
The only 'normal' which exists is normal for your server. Different databases, hardware and workloads will have different 'normal' wait times. It's also impossible to tell anything about yours, because wait times are cumulative since SQL started, and we don't know when your SQL instance started.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
The very best wait times are zero.
But, that's not going to happen, so you need to know how your waits are changing over time to determine if there are issues. As Gail says, just looking at the cumulative wait times and nothing else, there's just about nothing to go on.
Capture the waits again today, subtract yesterday's values, then see which wait stats boil to the top. Compare their total wait times to actual differences in time between the two runs to get an idea of the percentage of time spent waiting. Then you'll have a good indication of wait statistics over the last day. Do this daily or once a week or something and then you'll really understand how waits are affecting your performance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
SQL server restarts every night. Does these values reset on restart?
-
IT researcher (6/12/2014)
SQL server restarts every night. Does these values reset on restart?wait times are cumulative since SQL started
For a change I'm going to disagree with Grant. Daily is not granular enough to draw good conclusions in many cases. When I'm doing wait analysis, I take stats every 30 minutes. This is especially for servers where the workload differs by time of day (backups in the evening, bulk loads overnight, transactional activity during the day)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (6/12/2014)
IT researcher (6/12/2014)
SQL server restarts every night. Does these values reset on restart?wait times are cumulative since SQL started
For a change I'm going to disagree with Grant. Daily is not granular enough to draw good conclusions in many cases. When I'm doing wait analysis, I take stats every 30 minutes. This is especially for servers where the workload differs by time of day (backups in the evening, bulk loads overnight, transactional activity during the day)
For a change? Ha!
I don't exactly disagree with you, but I'd say that everything has a first step. Daily will get you going with meaningful data. Then, you will absolutely have to drill down to hourly, every 30 minutes, every 10, or even, this query, to really get good evaluations on wait statistics.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
IT researcher (6/12/2014)
SQL server restarts every night. Does these values reset on restart?Yes, it's since a restart. So this is cumulative since that restart. But, normal can only be compared to some other value. Right now you're comparing some number to no number at all. Some number is greater than no number, so, in theory, it's really, really bad. But, you need to have yesterday to compare to today to compare to tomorrow. Then you can see how things are changing over time in order to understand how things are behaving.
But, understand, there is no value that is bad and a value that is good (except zero, no waits is good, if impossible). You can't say, "Ah, 34 cxpacket waits, that's ok because it's only bad at 35." It's completely dependent on your system, your queries, your structures.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Grant Fritchey (6/12/2014)
For a change? Ha!😀 :hehe:
Daily will get you going with meaningful data.
True, yes, it's a good starting point, better than the total wait stats since sometime last month.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Now i again took the report and found PAGEIOLATCH_SH is still at the top and have value of wait_time_ms 11067000.
The sql server starts everyday at 8:30 AM and stops around 9:30 PM.
It 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 you have said I will take report for every 1 hours or 10 min, but how can I decide does SQL server really has any performance bottleneck by using these reports? (Because these values may be a normal range in a environment like of my sql server.Also i am new to performance analysis of sql server)
-
IT researcher (6/12/2014)
The sql server starts everyday at 8:30 AM and stops around 9:30 PM.Why?
SQL instances don't need to be rebooted daily. Hell, I know of instances that have been running over 6 months.
As you have said I will take report for every 1 hours or 10 min, but how can I decide does SQL server really has any performance bottleneck by using these reports? (Because these values may be a normal range in a environment like of my sql server.Also i am new to performance analysis of sql server)
Start by getting data for a few days. See if the waits times are steady or whether that was an abnormality. Once you have more than 2 or 3 data points, start investigating.
http://www.red-gate.com/community/books/accidental-dba
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Wait stats tell you what the server is waiting on. But, you can't know if you have a performance problem by only looking at cumulative wait times, even though you're starting to compare one day to the next.
Instead, you need to look at wait times as a percentage of total time. If the server has been online for 1 hour and you have a cumulative wait time of 59 minutes, the server might be under stress. But, if you have a cumulative wait time of 59 seconds, you still might have performance issues at the query level. Maybe only one query has been called in the last hour, but it ran for a full minute.
In short, wait times are incredibly useful, but they're not the only measure you use. You also have to use CPU percentage, paging in memory, I/O queues, query run time averages, peaks, and frequency of call. In short, monitoring and troubleshooting your server requires a lot of different metrics.
To get started, you might want to look at the book that Gail co-wrote called "Troubleshooting SQL Server for the Accidental DBA". It's available as a free download. That will give you a lot more information. You could also look to the first couple of chapters of my book (linked below) which gives a slightly different point of view (although not radically so since we all pretty much agree).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
GilaMonster (6/12/2014)
IT researcher (6/12/2014)
The sql server starts everyday at 8:30 AM and stops around 9:30 PM.Why?
SQL instances don't need to be rebooted daily. Hell, I know of instances that have been running over 6 months.
We had servers that would run for a year at a time. They'd probably run a lot longer than that, but there was a company policy that all servers had to be rebooted annually. Reboots frequently revealed failing or failed hardware that wouldn't appear on its own. It was an interesting lesson and a useful practice.
I'm curious why you're shutting it down every night too. You do know that will add considerable load to the server every morning while it recompiles all the queries that it compiled yesterday and moves the data it needs off of disk and into memory that it moved yesterday?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Why?
SQL instances don't need to be rebooted daily. Hell, I know of instances that have been running over 6 months.
The SQL server instance is used by employees in office. So it will be running only during office timings. So we keep the server OFF during night.
I will take the report for some days and will analyse it.
-
GilaMonster (6/12/2014)
IT researcher (6/12/2014)
The sql server starts everyday at 8:30 AM and stops around 9:30 PM. -
IT researcher (6/12/2014)
Why?
SQL instances don't need to be rebooted daily. Hell, I know of instances that have been running over 6 months.
The SQL server instance is used by employees in office. So it will be running only during office timings. So we keep the server OFF during night.
I will take the report for some days and will analyse it.
But every morning when you turn it back on, performance is going to be awful. That's a trade-off you should consider.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply