Data World

Posts Tagged ‘Performance Tuning’

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 »

MSSQL Server: Find Missing Indexes

Posted by Pramod Singla on March 4, 2017


Problem Statement: Script to find the missing indexes using 3 different ways.
Solution: 1) Find missing indexes using DMVs

--Find top ten missing indexes using following DMV 
SELECT TOP 10 ( user_seeks ) * avg_total_user_cost * ( avg_user_impact * 0.01 ) 
              AS 
              [index_advantage],--%advantage in the query 
              migs.last_user_seek, 
              --Date and time of last seek caused by user queries that the recommended index in the group could have been used for.
              mid.[statement] 
              AS [Database.Schema.Table], 
              'Create index idx_' 
              + Parsename( mid.[statement], 1) + '_' 
              + Cast(row_number() OVER(ORDER BY (user_seeks) * 
              avg_total_user_cost * ( 
              avg_user_impact * 0.01 ) DESC) AS VARCHAR(150)) 
              + ' on ' + Parsename( mid.[statement], 1) + '(' ++ 
              equality_columns + ')' + CASE WHEN mid.included_columns IS NOT 
              NULL THEN 
              ' include ('+ mid.included_columns+')' ELSE '' END 
              create_index, 
              'drop index idx_' 
              + Parsename( mid.[statement], 1) + '_' 
              + Cast(row_number() OVER(ORDER BY (user_seeks) * 
              avg_total_user_cost * ( 
              avg_user_impact * 0.01 ) DESC) AS VARCHAR(150)) 
              + ' on ' + Parsename( mid.[statement], 1) 
              drop_index, 
              mid.equality_columns, 
              mid.inequality_columns, 
              mid.included_columns, 
              migs.unique_compiles, 
              migs.user_seeks, 
              migs.avg_total_user_cost, 
              --  Average cost of the user queries that could be reduced by the index in the group. 
              migs.avg_user_impact 
--Average percentage benefit that user queries could experience, 
FROM   sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK) 
       INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) 
               ON migs.group_handle = mig.index_group_handle 
       INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) 
               ON mig.index_handle = mid.index_handle 
WHERE  mid.database_id = Db_id() -- Remove this to see for entire instance 
ORDER  BY index_advantage DESC 
OPTION (RECOMPILE);

2) Find missing indexes on FK

SELECT fk.NAME    AS CONSTRAINT_NAME,
s.NAME     AS SCHEMA_NAME,
o.NAME     AS TABLE_NAME,
fkc_c.NAME AS CONSTRAINT_COLUMN_NAME
FROM   sys.foreign_keys AS fk
JOIN sys.foreign_key_columns AS fkc
ON fk.object_id = fkc.constraint_object_id
JOIN sys.columns AS fkc_c
ON fkc.parent_object_id = fkc_c.object_id
AND fkc.parent_column_id = fkc_c.column_id
LEFT JOIN sys.index_columns ic
JOIN sys.columns AS c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
ON fkc.parent_object_id = ic.object_id
AND fkc.parent_column_id = ic.column_id
JOIN sys.objects AS o
ON o.object_id = fk.parent_object_id
JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
WHERE  c.NAME IS NULL

3) Find missing indexes using stored query plans.

SELECT MissingIndexNode.value('(MissingIndexGroup/@Impact)[1]', 'float')     AS 
       impact, 
       Object_name(sub.objectid, sub.dbid)                                   AS 
       calling_object_name, 
       MissingIndexNode.value ('(MissingIndexGroup/MissingIndex/@Database)[1]', 
       'VARCHAR(128)') 
       + '.' 
       + MissingIndexNode.value ('(MissingIndexGroup/MissingIndex/@Schema)[1]', 
       'VARCHAR(128)') 
       + '.' 
       + MissingIndexNode.value ('(MissingIndexGroup/MissingIndex/@Table)[1]', 
       'VARCHAR(128)')                                                       AS 
       table_name, 
       Stuff((SELECT ',' + c.value('(@Name)[1]', 'VARCHAR(128)') 
              FROM   MissingIndexNode.nodes ( 
'MissingIndexGroup/MissingIndex/                             ColumnGroup[@Usage=''EQUALITY'']/Column'
) AS t ( c ) 
 FOR XML PATH('')), 1, 1, '')                                         AS 
       equality_columns, 
Stuff((SELECT ',' + c.value('(@Name)[1]', 'VARCHAR(128)') 
       FROM   MissingIndexNode.nodes ( 
'MissingIndexGroup/MissingIndex/                             ColumnGroup[@Usage=''INEQUALITY'']/Column'
) AS t ( c ) 
 FOR XML PATH('')), 1, 1, '')                                         AS 
       inequality_columns, 
Stuff((SELECT ',' + c.value('(@Name)[1]', 'VARCHAR(128)') 
       FROM   MissingIndexNode.nodes ( 
'MissingIndexGroup/MissingIndex/                             ColumnGroup[@Usage=''INCLUDE'']/Column'
) AS t ( c ) 
 FOR XML PATH('')), 1, 1, '')                                         AS 
       include_columns, 
sub.usecounts                                                         AS 
       qp_usecounts, 
sub.refcounts                                                         AS 
       qp_refcounts, 
qs.execution_count                                                    AS 
       qs_execution_count, 
qs.last_execution_time                                                AS 
       qs_last_exec_time, 
qs.total_logical_reads                                                AS 
       qs_total_logical_reads, 
qs.total_elapsed_time                                                 AS 
       qs_total_elapsed_time, 
qs.total_physical_reads                                               AS 
       qs_total_physical_reads, 
qs.total_worker_time                                                  AS 
       qs_total_worker_time, 
StmtPlanStub.value('(StmtSimple/@StatementText)[1]', 'varchar(8000)') AS 
statement_text 
FROM   (SELECT ROW_NUMBER() 
                 OVER ( 
                   PARTITION BY qs.plan_handle 
                   ORDER BY qs.statement_start_offset ) AS StatementID, 
               qs.* 
        FROM   sys.dm_exec_query_stats qs) AS qs 
       JOIN (SELECT x.query('../../..')                          AS StmtPlanStub 
                    , 
                    x.query('.') 
                    AS MissingIndexNode, 
                    x.value('(../../../@StatementId)[1]', 'int') AS StatementID, 
                    cp.*, 
                    qp.* 
             FROM   sys.dm_exec_cached_plans AS cp 
                    CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle) qp 
                    CROSS APPLY qp.query_plan.nodes ( 
'/ShowPlanXML/BatchSequence/                                         Batch/Statements/StmtSimple/                                         QueryPlan/MissingIndexes/                                         MissingIndexGroup'
) mi ( x )) AS sub 
  ON qs.plan_handle = sub.plan_handle 
     AND qs.StatementID = sub.StatementID

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

MSSQL Server: Get VLF counts of all Dbs

Posted by Pramod Singla on March 4, 2017


Problem Statement:Get the VLF count of all the dbs in a SQL server Instance.
Solution: 

--variables to hold each 'iteration'  
DECLARE @query VARCHAR(100)
DECLARE @dbname SYSNAME
DECLARE @vlfs INT
--table variable used to 'loop' over databases  
DECLARE @databases TABLE
(
dbname SYSNAME
)

INSERT INTO @databases
--only choose online databases  
SELECT NAME
FROM   sys.databases
WHERE  state = 0

--table variable to hold results  
DECLARE @vlfcounts TABLE
(
dbname   SYSNAME,
vlfcount INT
)
--table variable to capture DBCC loginfo output  
--changes in the output of DBCC loginfo from SQL2012 mean we have to determine the version 
DECLARE @MajorVersion TINYINT

SET @MajorVersion = LEFT(Cast(Serverproperty('ProductVersion') AS NVARCHAR(max))
,
Charindex('.', Cast(
Serverproperty('ProductVersion') AS
NVARCHAR(max))) - 1)

IF @MajorVersion < 11 -- pre-SQL2012 
BEGIN
DECLARE @dbccloginfo TABLE
(
fileid       TINYINT,
file_size    BIGINT,
start_offset BIGINT,
fseqno       INT,
[status]     TINYINT,
parity       TINYINT,
create_lsn   NUMERIC(25, 0)
)

WHILE EXISTS(SELECT TOP 1 dbname
FROM   @databases)
BEGIN
SET @dbname = (SELECT TOP 1 dbname
FROM   @databases)
SET @query = 'dbcc loginfo (' + '''' + @dbname + ''') '

INSERT INTO @dbccloginfo
EXEC (@query)

SET @vlfs = @@rowcount

INSERT @vlfcounts
VALUES(@dbname,
@vlfs)

DELETE FROM @databases
WHERE  dbname = @dbname
END --while 
END
ELSE
BEGIN
DECLARE @dbccloginfo2012 TABLE
(
RecoveryUnitId INT,
fileid         TINYINT,
file_size      BIGINT,
start_offset   BIGINT,
fseqno         INT,
[status]       TINYINT,
parity         TINYINT,
create_lsn     NUMERIC(25, 0)
)

WHILE EXISTS(SELECT TOP 1 dbname
FROM   @databases)
BEGIN
SET @dbname = (SELECT TOP 1 dbname
FROM   @databases)
SET @query = 'dbcc loginfo (' + '''' + @dbname + ''') '

INSERT INTO @dbccloginfo2012
EXEC (@query)

SET @vlfs = @@rowcount

INSERT @vlfcounts
VALUES(@dbname,
@vlfs)

DELETE FROM @databases
WHERE  dbname = @dbname
END --while 
END

--output the full list  
SELECT dbname,
vlfcount
FROM   @vlfcounts
ORDER  BY dbname

Posted in DBA, Performance Tuning, Scripts | Tagged: , , , , | Leave a Comment »