A. Precheck before indexing
1. Check Index fragmentation
SELECT @@servername as Server, DB_NAME(ips.database_id) AS DatabaseName,@@servicename as ServiceName,
SCHEMA_NAME(ob.[schema_id]) SchemaNames,
ob.[name] AS ObjectName,
ix.[name] AS IndexName,
ob.type_desc AS ObjectType,
ix.type_desc AS IndexType,
-- ips.partition_number AS PartitionNumber,
ips.page_count AS [PageCount], -- Only Available in DETAILED Mode
ips.record_count AS [RecordCount],
ips.avg_fragmentation_in_percent AS AvgFragmentationInPercent
-- FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'DETAILED') ips
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'SAMPLED') ips -- QuickResult
INNER JOIN sys.indexes ix ON ips.[object_id] = ix.[object_id]
AND ips.index_id = ix.index_id
INNER JOIN sys.objects ob ON ix.[object_id] = ob.[object_id]
WHERE ob.[type] IN('U','V')
AND ob.is_ms_shipped = 0
AND ix.[type] IN(1,2,3,4)
AND ix.is_disabled = 0
AND ix.is_hypothetical = 0
AND ips.alloc_unit_type_desc = 'IN_ROW_DATA'
AND ips.index_level = 0
and ob.[name] ='SalesOrderHeaderEnlarged'
-- AND ips.page_count >= 1000 -- Filter to check only table with over 1000 pages
-- AND ips.record_count >= 100 -- Filter to check only table with over 1000 rows
-- AND ips.database_id = DB_ID() -- Filter to check only current database
-- AND ips.avg_fragmentation_in_percent > 50 -- Filter to check over 50% indexes
-- ORDER BY DatabaseName
order by ips.avg_fragmentation_in_percent desc, DatabaseName asc
2. check size table
SELECT @@servername as ServerName, db_name() as DBName,@@servicename as ServiceName,
t.NAME AS TableName,
p.rows AS RowCounts,
CONVERT(DECIMAL,SUM(a.total_pages)) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB ,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME = 'SalesOrderHeaderEnlarged'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
UsedSpaceKB DESC, t.Name
3. check capacity report
SELECT @@SERVERNAME as [Server_name], db_name() [DB_Name], @@servicename as ServiceName,getdate() as [Date],
--row_number() over(order by a3.name, a2.name))%2 as l1,
a3.name AS [schemaname],
a2.name AS [tablename],
a1.rows as row_count,
((a1.reserved + ISNULL(a4.reserved,0))* 8)/1024 AS reserved_in_MB,
(a1.data * 8)/1024 AS data_in_MB,
((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END))/1024 * 8 AS index_size_in_MB,
((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8)/1024 AS unused_in_MB
FROM
(SELECT
ps.object_id,
SUM (
CASE
WHEN (ps.index_id < 2) THEN row_count
ELSE 0
END
) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (
CASE
WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps with (NOLOCK)
WHERE ps.object_id NOT IN (SELECT object_id FROM sys.tables with (NOLOCK) WHERE is_memory_optimized = 1)
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
(SELECT
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps with (NOLOCK)
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N'S' and a2.type <> N'IT'
and a2.name = 'SalesOrderHeaderEnlarged'
ORDER BY reserved_in_MB desc
B. Execute Indexing manual or can create job
-- create job
use AdventureWorks2019
go
Print '--Indexing of dbo.SalesOrderHeaderEnlarged started'
select GETDATE()
DBCC DBREINDEX ('Sales.SalesOrderHeaderEnlarged', ' ', 85);
GO
C. Post check after indexing
1. Check Index fragmentation
SELECT @@servername as Server, DB_NAME(ips.database_id) AS DatabaseName,@@servicename as ServiceName,
SCHEMA_NAME(ob.[schema_id]) SchemaNames,
ob.[name] AS ObjectName,
ix.[name] AS IndexName,
ob.type_desc AS ObjectType,
ix.type_desc AS IndexType,
-- ips.partition_number AS PartitionNumber,
ips.page_count AS [PageCount], -- Only Available in DETAILED Mode
ips.record_count AS [RecordCount],
ips.avg_fragmentation_in_percent AS AvgFragmentationInPercent
-- FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'DETAILED') ips
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'SAMPLED') ips -- QuickResult
INNER JOIN sys.indexes ix ON ips.[object_id] = ix.[object_id]
AND ips.index_id = ix.index_id
INNER JOIN sys.objects ob ON ix.[object_id] = ob.[object_id]
WHERE ob.[type] IN('U','V')
AND ob.is_ms_shipped = 0
AND ix.[type] IN(1,2,3,4)
AND ix.is_disabled = 0
AND ix.is_hypothetical = 0
AND ips.alloc_unit_type_desc = 'IN_ROW_DATA'
AND ips.index_level = 0
and ob.[name] ='SalesOrderHeaderEnlarged'
-- AND ips.page_count >= 1000 -- Filter to check only table with over 1000 pages
-- AND ips.record_count >= 100 -- Filter to check only table with over 1000 rows
-- AND ips.database_id = DB_ID() -- Filter to check only current database
-- AND ips.avg_fragmentation_in_percent > 50 -- Filter to check over 50% indexes
-- ORDER BY DatabaseName
order by ips.avg_fragmentation_in_percent desc, DatabaseName asc
2. check size table
SELECT @@servername as ServerName, db_name() as DBName,@@servicename as ServiceName,
t.NAME AS TableName,
p.rows AS RowCounts,
CONVERT(DECIMAL,SUM(a.total_pages)) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB ,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME = 'SalesOrderHeaderEnlarged'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
UsedSpaceKB DESC, t.Name
3. check capacity report
SELECT @@SERVERNAME as [Server_name], db_name() [DB_Name], @@servicename as ServiceName,getdate() as [Date],
--row_number() over(order by a3.name, a2.name))%2 as l1,
a3.name AS [schemaname],
a2.name AS [tablename],
a1.rows as row_count,
((a1.reserved + ISNULL(a4.reserved,0))* 8)/1024 AS reserved_in_MB,
(a1.data * 8)/1024 AS data_in_MB,
((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END))/1024 * 8 AS index_size_in_MB,
((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8)/1024 AS unused_in_MB
FROM
(SELECT
ps.object_id,
SUM (
CASE
WHEN (ps.index_id < 2) THEN row_count
ELSE 0
END
) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (
CASE
WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps with (NOLOCK)
WHERE ps.object_id NOT IN (SELECT object_id FROM sys.tables with (NOLOCK) WHERE is_memory_optimized = 1)
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
(SELECT
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps with (NOLOCK)
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N'S' and a2.type <> N'IT'
and a2.name = 'SalesOrderHeaderEnlarged'
ORDER BY reserved_in_MB desc
D. Compare before and after indexing
No comments:
Post a Comment