Data World

Archive for the ‘sql server’ Category

SQL Server Tuning – De-fragment Indexes

Posted by Pramod Singla on March 10, 2017


Problem Statement: Script to generate the scripts used to de-fragment indexes.

Solution: Use this script to find the fragmented indexes and following script can be used to de-fragment them.

SELECT ‘Alter index ‘ + i.NAME + ‘ on ‘
+ Quotename(s.NAME) + ‘.’ + Quotename(o.NAME) + CASE WHEN
stats.avg_fragmentation_in_percent >=60 THEN ‘ rebuild ‘ ELSE
‘ reorganize’ END
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 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

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 »

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 »