November 5, 2015 at 3:58 am
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'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
N'CHKPT', N'CLR_AUTO_EVENT',
N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC', N'FSAGENT',
N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED',
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
AND [waiting_tasks_count] > 0
)
Hi,
I have used this quey to check my servers statiscts:
It is a query from paul randal:
SELECT
MAX ([W1].[wait_type]) AS [WaitType],
CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
MAX ([W1].[WaitCount]) AS [WaitCount],
CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95; -- percentage threshold
GO
I got the following results:
WaitTypeWait_SResource_SSignal_SWaitCountPercentageAvgWait_SAvgRes_SAvgSig_S
WRITELOG1428058.061381411.6746646.3933102989176.980.00430.00420.0001
PAGELATCH_EX95329.6363675.3731654.262689788955.140.00040.00020.0001
SOS_SCHEDULER_YIELD83276.24345.7482930.502574107034.490.00030.00000.0003
LCK_M_S41394.0641261.49132.571363242.230.30360.30270.0010
BACKUPIO26762.2326667.6594.5831221101.440.00860.00850.0000
BACKUPBUFFER23140.8622858.88281.9846286451.250.00500.00490.0001
ASYNC_IO_COMPLETION22127.6122127.540.076611.1933.476033.47590.0001
IO_COMPLETION20302.4820198.53103.9481644431.090.00250.00250.0000
OLEDB16436.2716436.270.0011282011590.890.00000.00000.0000
LCK_M_IX14001.6213989.1712.44451520.750.31010.30980.0003
What do you think needs to be improved.
November 5, 2015 at 4:03 am
The values of the WRITELOG aren't very above the value?
November 5, 2015 at 4:11 am
Sorry I can't understand the question, what is it your after?
November 5, 2015 at 5:06 am
Have better perfomance on the server
November 5, 2015 at 5:08 am
November 5, 2015 at 5:17 am
I'm trying to see if from the values below something neeeds to be changed because it is becoming a bootleneck
WaitTypeWait_SResource_SSignal_SWaitCountPercentageAvgWait_SAvgRes_SAvgSig_S
WRITELOG1428058.061381411.6746646.3933102989176.980.00430.00420.0001
PAGELATCH_EX95329.6363675.3731654.262689788955.140.00040.00020.0001
SOS_SCHEDULER_YIELD83276.24345.7482930.502574107034.490.00030.00000.0003
LCK_M_S41394.0641261.49132.571363242.230.30360.30270.0010
BACKUPIO26762.2326667.6594.5831221101.440.00860.00850.0000
BACKUPBUFFER23140.8622858.88281.9846286451.250.00500.00490.0001
ASYNC_IO_COMPLETION22127.6122127.540.076611.1933.476033.47590.0001
IO_COMPLETION20302.4820198.53103.9481644431.090.00250.00250.0000
OLEDB16436.2716436.270.0011282011590.890.00000.00000.0000
LCK_M_IX14001.6213989.1712.44451520.750.31010.30980.0003
Do you see any values , that I should be concerned with?
November 5, 2015 at 5:20 am
For instance, this result:
WaitTypeWait_SResource_SSignal_SWaitCountPercentageAvgWait_SAvgRes_SAvgSig_S
WRITELOG1428099.931381452.4146647.5233103900276.980.00430.00420.0001
The log write percentage is almost on 77% ? is this normal ?
November 5, 2015 at 5:30 am
river1 (11/5/2015)
For instance, this result:WaitTypeWait_SResource_SSignal_SWaitCountPercentageAvgWait_SAvgRes_SAvgSig_S
WRITELOG1428099.931381452.4146647.5233103900276.980.00430.00420.0001
The log write percentage is almost on 77% ? is this normal ?
Depends on your disks, might be normal, might not be.
How are you determining that its a bottleneck?
November 5, 2015 at 5:36 am
I am trying to see why some data is taking more time to be distributed.
November 5, 2015 at 5:38 am
If I add an additional transaction log (on other disk) to the database that would devide the i/0 between logs . which would help, correct?
I belive that 77% is a very high value
November 5, 2015 at 5:46 am
It might be high, it might not be high, you cant just go off a one time select of the value, you need to monitor over time.
Adding another file wont help you in the log arena, log files are sequential so wont use the second file till the first is full.
If you want to reduce the value, look at the link I posted, there are steps listed on things you can do to reduce the wait stat
November 5, 2015 at 5:55 am
When a user transaction is committed (either explicitly via a COMMIT statement, or implicitly), SQL Server writes all changes from the Log Cache out to the log files on disk. This process is termed a log flush. The user that issued the commit must wait until the log flush is complete before they can continue. If the log flush takes a long time, this will degrade the user's response time.
With 77% its a lot..
November 5, 2015 at 6:13 am
77% of your total wait time is on WRITELOG, yes that maybe high for your workload, it might not be, depends on the workload.
I have a heavy write syslog server to log every log event from every server, as you can imagine the writelog wait time is high as all its doing is constantly writing, only in an audit situation would we need to read from it. Is it a bottleneck no as the system is running against standard workload.
As mentioned if this is causing you a bottleneck, take a look at the link I posted, it details steps you can take to reduce the wait stat.
November 5, 2015 at 9:18 am
river1 (11/5/2015)
If I add an additional transaction log (on other disk) to the database that would devide the i/0 between logs . which would help, correct?
No, it wouldn't.
SQL does not use log files in parallel. It'll use one, then the other.
As for 77% being high, what is it 77% of?
Make sure you understand what you're looking for. I can get a server that's completely idle to show 90% writelog waits with that query. It's the % of waits, not the % of time or anything like that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 5, 2015 at 10:12 am
To tune for performance, you first need to review missing index stats and index usage stats (and index operational stats, if you can). The main improvement is to determine and implement the best clustered index for each table based on that specific table's usage. Until you do that, you will have lesser overall performance and probably a tendency to deadlocks. And while the initial focus in on the clustered indexes, the nonclustered indexes on critical tables can also naturally be reviewed at the same time.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply