Data World

Archive for the ‘Performance Tuning’ Category

SQL Server Tuning – Find Fragmented Indexes

Posted by Pramod Singla on March 10, 2017


Problem Statement: Script to find fragmented Indexes.
Solution: No need to explain how killing fragmented indexes could be.Use following script to find the fragmented indexes and script to de-fragment them can be found here.

SELECT Db_name()              AS DBname,
Quotename(s.NAME)      AS CchemaName,
Quotename(o.NAME)      AS TableName,
i.NAME                 AS IndexName,
stats.Index_type_desc  AS IndexType,
stats.page_count       AS [PageCount],
stats.partition_number AS PartitionNumber,
CASE
WHEN i.fill_factor > 0 THEN i.fill_factor
ELSE 90
END                    AS [Fill Factor],
stats.avg_fragmentation_in_percent,
stats.fragment_count,
CASE
WHEN stats.index_level = 0 THEN ‘Leaf Level’
ELSE ‘Nonleaf Level’
END                    AS IndexLevel
FROM   sys.dm_db_index_physical_stats(Db_id(), NULL, NULL, NULL, ‘LIMITED’) AS
stats,
sys.objects AS o,
sys.schemas AS s,
sys.indexes AS i
WHERE  o.OBJECT_ID = stats.OBJECT_ID
AND s.schema_id = o.schema_id
AND i.OBJECT_ID = stats.OBJECT_ID
AND i.index_id = stats.index_id
AND stats.avg_fragmentation_in_percent >= 20
AND stats.page_count >= 1000
ORDER  BY stats.avg_fragmentation_in_percent DESC,
stats.page_count DESC

Advertisements

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

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 »

MSSQL Server: Find Bad Indexes

Posted by Pramod Singla on March 4, 2017


Problem Statement: Script to find the Bad indexes
Solution: The indexes returned by the below query degrade the performance of the write queries. Consider deleting them.

–Find the bad indexes using following DMV:
— Possible Bad NC Indexes (writes > reads)
SELECT Object_name(s.[object_id])                                AS [Table Name]
,
i.NAME                                                    AS
[Index Name]
,
i.index_id,
user_updates                                              AS
[Total Writes],–number of updates by user queries
user_seeks + user_scans + user_lookups                    AS
[Total Reads],
user_updates  ( user_seeks + user_scans + user_lookups ) AS [Difference]
FROM   sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
WHERE  OBJECTPROPERTY(s.[object_id], ‘IsUserTable’) = 1
AND s.database_id = Db_id()
AND user_updates > ( user_seeks + user_scans + user_lookups )
AND i.index_id > 1
ORDER  BY [Difference] DESC,
[Total Writes] DESC,
[Total Reads] ASC
OPTION (RECOMPILE);  

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