Archive for the ‘sql server’ Category
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
Posted in Performance Tuning, sql server | Tagged: defragment index, DMV | 1 Comment »
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: DMV, fragmented indexes, Performance Tuning | 1 Comment »
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: DMV, duplicate stats, Performance Tuning | Leave a Comment »
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: DMV, IO waits, sql server waits, Virtual file stats | 2 Comments »
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: Bad indexes, Find bad indexes, Performance Tuning | Leave a Comment »
