Last active
August 8, 2022 13:31
-
-
Save michaellwest/f32478b9ef1e06377ed84c8e957dea86 to your computer and use it in GitHub Desktop.
SQL Maintenance Scripts for Sitecore
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- https://www.sqlshack.com/how-to-identify-and-resolve-sql-server-index-fragmentation/ | |
SELECT S.name as 'Schema', | |
T.name as 'Table', | |
I.name as 'Index', | |
DDIPS.avg_fragmentation_in_percent, | |
DDIPS.page_count | |
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS | |
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id | |
INNER JOIN sys.schemas S on T.schema_id = S.schema_id | |
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id | |
AND DDIPS.index_id = I.index_id | |
WHERE DDIPS.database_id = DB_ID() | |
and I.name is not null | |
AND DDIPS.avg_fragmentation_in_percent > 0 | |
ORDER BY DDIPS.avg_fragmentation_in_percent desc |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
t.name AS TableName, | |
i.name as indexName, | |
sum(p.rows) as RowCounts, | |
sum(a.total_pages) as TotalPages, | |
sum(a.used_pages) as UsedPages, | |
sum(a.data_pages) as DataPages, | |
(sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, | |
(sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, | |
(sum(a.data_pages) * 8) / 1024 as DataSpaceMB | |
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 | |
WHERE | |
t.name NOT LIKE 'dt%' AND | |
i.object_id > 255 AND | |
i.index_id <= 1 | |
GROUP BY | |
t.name, i.object_id, i.index_id, i.name | |
ORDER BY | |
object_name(i.object_id) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- https://www.mssqltips.com/sqlservertip/1367/sql-server-script-to-rebuild-all-indexes-for-all-tables-and-all-databases/ | |
DECLARE @Database NVARCHAR(255) | |
DECLARE @Table NVARCHAR(255) | |
DECLARE @cmd NVARCHAR(1000) | |
DECLARE DatabaseCursor CURSOR READ_ONLY FOR | |
SELECT name FROM master.sys.databases | |
WHERE name NOT IN ('master','msdb','tempdb','model','distribution') -- databases to exclude | |
--WHERE name IN ('DB1', 'DB2') -- use this to select specific databases and comment out line above | |
AND state = 0 -- database is online | |
AND is_in_standby = 0 -- database is not read only for log shipping | |
ORDER BY 1 | |
OPEN DatabaseCursor | |
FETCH NEXT FROM DatabaseCursor INTO @Database | |
WHILE @@FETCH_STATUS = 0 | |
BEGIN | |
SET @cmd = 'DECLARE TableCursor CURSOR READ_ONLY FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + | |
table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE''' | |
-- create table cursor | |
EXEC (@cmd) | |
OPEN TableCursor | |
FETCH NEXT FROM TableCursor INTO @Table | |
WHILE @@FETCH_STATUS = 0 | |
BEGIN | |
BEGIN TRY | |
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD' | |
--PRINT @cmd -- uncomment if you want to see commands | |
EXEC (@cmd) | |
END TRY | |
BEGIN CATCH | |
PRINT '---' | |
PRINT @cmd | |
PRINT ERROR_MESSAGE() | |
PRINT '---' | |
END CATCH | |
FETCH NEXT FROM TableCursor INTO @Table | |
END | |
CLOSE TableCursor | |
DEALLOCATE TableCursor | |
FETCH NEXT FROM DatabaseCursor INTO @Database | |
END | |
CLOSE DatabaseCursor | |
DEALLOCATE DatabaseCursor |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment