Data World

Posts Tagged ‘DMV’

SQL Server Tuning – Find Duplicate Stats

Posted by Pramod Singla on March 10, 2017


Problem Statement: Script to find the duplicate stats.
Solution: SQL server allows to create duplicate statistics but having duplicate statistics are overkill to system.Use following script to find the duplicate statistics.

;WITH all_stats
AS (SELECT s.object_id,
s.NAME AS stats_name,
s.stats_id,
s.has_filter,
s.auto_created,
sc.stats_column_id,
sc.column_id,
c.NAME AS column_name
FROM   sys.stats AS s
INNER JOIN sys.stats_columns AS sc
ON s.stats_id = sc.stats_id
AND s.object_id = sc.object_id
AND sc.stats_column_id = 1
INNER JOIN sys.columns AS c
ON c.object_id = s.object_id
AND c.object_id = sc.object_id
AND c.column_id = sc.column_id
WHERE  objectproperty(s.object_id, ‘IsUserTable’) = 1)
SELECT row_number()
OVER(
partition BY s1.object_id, s1.column_name
ORDER BY s1.column_name) AS stats_no,
Object_name(s1.object_id)    AS table_name,
s1.stats_name                AS stats_name,
s2.stats_name                AS identical_stats_name,
s2.column_name
FROM   all_stats AS s1
INNER JOIN all_stats AS s2
ON s1.object_id = s2.object_id
AND s1.stats_id != s2.stats_id
AND s1.stats_column_id = s2.stats_column_id
AND s1.column_id = s2.column_id
AND s1.has_filter = s2.has_filter
AND s1.auto_created != s2.auto_created

Posted in Performance Tuning, sql server | Tagged: , , | Leave a Comment »

SQL Server Tuning: Find IO wait or Virtual File Stats

Posted by Pramod Singla on March 8, 2017


Problem Statement: Script to get the virtual file waits for IO waits.
Solution: Virtual File waits are useful in identifying the IO waits occurring on any of the database file. This script is used along with SQL server wait stats script to understand the IO waits well.

SELECT Db_name(vfs.database_id)                           AS database_name,
vfs.database_id,
vfs.FILE_ID,
io_stall_read_ms / NULLIF(num_of_reads, 0)         AS avg_read_latency,
io_stall_write_ms / NULLIF(num_of_writes, 0)       AS avg_write_latency,
io_stall / NULLIF(num_of_reads + num_of_writes, 0) AS avg_total_latency,
num_of_bytes_read / NULLIF(num_of_reads, 0)        AS avg_bytes_per_read,
num_of_bytes_written / NULLIF(num_of_writes, 0)    AS avg_bytes_per_write
,
vfs.io_stall,
vfs.num_of_reads,
vfs.num_of_bytes_read,
vfs.io_stall_read_ms,
vfs.num_of_writes,
vfs.num_of_bytes_written,
vfs.io_stall_write_ms,
size_on_disk_bytes / 1024 / 1024.                  AS size_on_disk_mbytes
,
physical_name
FROM   sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf
ON vfs.database_id = mf.database_id
AND vfs.FILE_ID = mf.FILE_ID
ORDER  BY avg_total_latency DESC

Posted in Performance Tuning, sql server | Tagged: , , , | 2 Comments »

MSSSQL Server Tuning – Find Wait stats

Posted by Pramod Singla on March 5, 2017


Problem Statement: Script to get the SQL Server wait stats. You can find the significance of most common waits below it.
Solution: Knowing resource waits is the good way to start the performance tuning.Following is the good script i got from SQL skills.

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’,
— Maybe uncomment these four if you have mirroring issues
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’,
— Maybe uncomment these six if you have AG issues
N’HADR_CLUSAPI_CALL’,

,
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’MEMORY_ALLOCATION_EXT’,
N’ONDEMAND_TASK_QUEUE’,
N’PREEMPTIVE_XE_GETTARGETSTATE’,
N’PWAIT_ALL_COMPONENTS_INITIALIZED’,
N’PWAIT_DIRECTLOGCONSUMER_GETNEXT’,
N’QDS_PERSIST_TASK_MAIN_LOOP_SLEEP’
,
N’QDS_ASYNC_QUEUE’,
N’QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP’,
N’QDS_SHUTDOWN_QUEUE’,
N’REDO_THREAD_PENDING_WORK’,
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_RECOVERY’, 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)
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],
Cast (https://www.sqlskills.com/help/waits/’
+ Max ([W1].[wait_type]) AS XML)
AS [Help/Info URL]
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

Common Significant Wait types explanations are as:

Sno. Wait Type Description
1. LCK_M_IX Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock
2. LCK_M_IU Occurs when a task is waiting to acquire an Intent Update (IU) lock
3. LCK_M_S Occurs when a task is waiting to acquire a Shared lock
4. ASYNC_NETWORK_IO Occurs on network writes when the task is blocked behind the network
5. ASYNC_IO_COMPLETION Occurs when a task is waiting for I/Os to finish
6.  

IO_COMPLETION

Occurs while waiting for I/O operations to complete.                       This wait type generally represents non-data page I/Os. Data page I/O completion waits appear
7. PAGEIOLATCH_SH Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.
8.  

 

PAGEIOLATCH_EX

 

Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem.

 

9. WRITELOG

 

Occurs while waiting for a log flush to complete.Common operations that cause log flushes are checkpoints and transaction commits.
10. PAGELATCH_EX

 

Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Exclusive mode.
11. BACKUPIO

 

Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data
12. SOS_SCHEDULER_YIELD

 

Occurs when a task voluntarily yields the scheduler for other tasks to execute.  During this wait the task is waiting for its quantum to be renewed.
13. THREADPOOL

 

Occurs when a task is waiting for a worker to run on. This can indicate that the maximum worker setting is too low, or that batch executions are taking  unusually long, thus reducing the number of workers available to satisfy other batches.
14. CX_PACKET

 

Occurs when trying to synchronize the query processor exchange iterator
15. ONDEMAND_TASK_QUEUE

 

Occurs while a background task waits for high priority system task requests. Long wait times indicate that there have been no high priority requests to process, and should not cause concern. You may consider lowering the degree of parallelism if contention on this wait type becomes a problem

Posted in DBA, Performance Tuning | Tagged: , , , , | 1 Comment »