Differences
This shows you the differences between two versions of the page.
| mssql_management_scripts [2019/10/18 20:04] – external edit 127.0.0.1 | mssql_management_scripts [2020/03/09 07:12] (current) – andonovj | ||
|---|---|---|---|
| Line 58: | Line 58: | ||
| GO' from sys.databases where name not in (' | GO' from sys.databases where name not in (' | ||
| + | </ | ||
| + | ===Index Fragmentation=== | ||
| + | <sxh bash> | ||
| + | SELECT dbschemas.[name] as ' | ||
| + | dbtables.[name] as ' | ||
| + | dbindexes.[name] as ' | ||
| + | indexstats.alloc_unit_type_desc, | ||
| + | indexstats.avg_fragmentation_in_percent, | ||
| + | indexstats.page_count | ||
| + | FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats | ||
| + | INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id] | ||
| + | INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id] | ||
| + | INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id] | ||
| + | AND indexstats.index_id = dbindexes.index_id | ||
| + | WHERE indexstats.database_id = DB_ID() | ||
| + | ORDER BY indexstats.avg_fragmentation_in_percent desc | ||
| </ | </ | ||