September 19, 2017 at 1:25 am
How can I join all the results of the below query into a single result set?
Thanks
exec master..sp_MSForeachdb
'
SELECT dbschemas.[name],db_name(),
dbtables.[name] ,
dbindexes.[name] ,
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
'
September 19, 2017 at 2:15 am
DROP TABLE #Results
CREATE TABLE #Results (
SchemaName sysname NOT NULL
, DBName sysname NOT NULL
, TableName sysname NOT NULL
, IndexName sysname NULL
, Frag decimal NOT NULL
, Pages int NOT NULL
)
INSERT INTO #Results
EXEC master..sp_MSForeachdb
'
SELECT dbschemas.[name],
''?'' ,
dbtables.[name] ,
dbindexes.[name] ,
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
'
John
September 19, 2017 at 9:15 am
IF OBJECT_ID('tempdb.dbo.#index_info') IS NOT NULL
DROP TABLE #index_info;
SELECT TOP (0)
dbschemas.[name] AS schema_name,db_name() AS db_name,
dbtables.[name] AS table_name ,
dbindexes.[name] AS index_name ,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
INTO #index_info
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
EXEC sp_MSForeachdb
'
USE [?];
--PRINT ''?'';
INSERT INTO #index_info
SELECT dbschemas.[name],db_name(),
dbtables.[name] ,
dbindexes.[name] ,
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
'
SELECT *
FROM #index_info
ORDER BY db_name, table_name, index_name
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 19, 2017 at 6:10 pm
sqlnewbie17 - Tuesday, September 19, 2017 1:25 AMHow can I join all the results of the below query into a single result set?
Thanksexec master..sp_MSForeachdb
'
SELECT dbschemas.[name],db_name(),
dbtables.[name] ,
dbindexes.[name] ,
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
'
Stop looking at fragmentation. SQL Server execution plans don't consider it and it has little effect on today's boxes. If you insist on causing page splits by defragging your goodies, look instead at the average page fullness instead of fragmentation. I'll also tell you that defragging indexes, especially non-clustered indexes and non-ever-increasing clustered indexes is pretty much a total waste of time. I've not rebuilt indexes on my production boxes since 17 Jan 2016... performance actually got better in the first 3 months thanks to the "natural fill factor" (my term for it) that formed and has not degraded since then even though my big databases have nearly doubled in size since then.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 25, 2017 at 11:50 am
Jeff Moden - Tuesday, September 19, 2017 6:10 PMsqlnewbie17 - Tuesday, September 19, 2017 1:25 AMHow can I join all the results of the below query into a single result set?
Thanksexec master..sp_MSForeachdb
'
SELECT dbschemas.[name],db_name(),
dbtables.[name] ,
dbindexes.[name] ,
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
'Stop looking at fragmentation. SQL Server execution plans don't consider it and it has little effect on today's boxes. If you insist on causing page splits by defragging your goodies, look instead at the average page fullness instead of fragmentation. I'll also tell you that defragging indexes, especially non-clustered indexes and non-ever-increasing clustered indexes is pretty much a total waste of time. I've not rebuilt indexes on my production boxes since 17 Jan 2016... performance actually got better in the first 3 months thanks to the "natural fill factor" (my term for it) that formed and has not degraded since then even though my big databases have nearly doubled in size since then.
If you have a table with fill factor of 70 or so (maybe less), wouldn't you avoid that initial burst of page splitting?
September 25, 2017 at 12:02 pm
patrickmcginnis59 10839 - Monday, September 25, 2017 11:50 AMJeff Moden - Tuesday, September 19, 2017 6:10 PMsqlnewbie17 - Tuesday, September 19, 2017 1:25 AMHow can I join all the results of the below query into a single result set?
Thanksexec master..sp_MSForeachdb
'
SELECT dbschemas.[name],db_name(),
dbtables.[name] ,
dbindexes.[name] ,
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
'Stop looking at fragmentation. SQL Server execution plans don't consider it and it has little effect on today's boxes. If you insist on causing page splits by defragging your goodies, look instead at the average page fullness instead of fragmentation. I'll also tell you that defragging indexes, especially non-clustered indexes and non-ever-increasing clustered indexes is pretty much a total waste of time. I've not rebuilt indexes on my production boxes since 17 Jan 2016... performance actually got better in the first 3 months thanks to the "natural fill factor" (my term for it) that formed and has not degraded since then even though my big databases have nearly doubled in size since then.
If you have a table with fill factor of 70 or so (maybe less), wouldn't you avoid that initial burst of page splitting?
Yes. Just like if you have a table that has already gone through page splitting and the "natural fill factor" has taken over.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 25, 2017 at 12:26 pm
Jeff Moden - Monday, September 25, 2017 12:02 PMpatrickmcginnis59 10839 - Monday, September 25, 2017 11:50 AMJeff Moden - Tuesday, September 19, 2017 6:10 PMsqlnewbie17 - Tuesday, September 19, 2017 1:25 AMHow can I join all the results of the below query into a single result set?
Thanksexec master..sp_MSForeachdb
'
SELECT dbschemas.[name],db_name(),
dbtables.[name] ,
dbindexes.[name] ,
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
'Stop looking at fragmentation. SQL Server execution plans don't consider it and it has little effect on today's boxes. If you insist on causing page splits by defragging your goodies, look instead at the average page fullness instead of fragmentation. I'll also tell you that defragging indexes, especially non-clustered indexes and non-ever-increasing clustered indexes is pretty much a total waste of time. I've not rebuilt indexes on my production boxes since 17 Jan 2016... performance actually got better in the first 3 months thanks to the "natural fill factor" (my term for it) that formed and has not degraded since then even though my big databases have nearly doubled in size since then.
If you have a table with fill factor of 70 or so (maybe less), wouldn't you avoid that initial burst of page splitting?
Yes. Just like if you have a table that has already gone through page splitting and the "natural fill factor" has taken over.
Well previously I was giving your position some weight because who wants undesireable page splitting. However, with a suitable fill factor, the page splitting should be avoided.
In which case:
If you insist on causing page splits by defragging your goodies, look instead at the average page fullness instead of fragmentation
I would like to reconcile the suitable fill factor against what you typed, ie., can we rebuild indexes without causing page splits? Seems if I select a decent fill factor, the net effect would be to reduce splits for a time after the rebuild, instead of causing them if I picked a fill factor that was greater than your "naturally occurring" fill factor, right?
Just thinking things out, I originally had considered this position of yours carried a good bit of weight until I discovered that its possible NOT to create page splits with a rebuild, my first reading of your posts regarding this is that I was actually causing page splits when in fact this might not be the case.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply