Need replacement t-sql to obtain SHOWCONTIG values

Question

Need replacement t-sql to obtain SHOWCONTIG values

Express12

SSCoach

Points: 18514
More actions
May 30, 2017 at 8:04 am

#326212

This older query still executes fine as it refers to the "compatibility view" called sysindexes --- but I'd like to upgrade it now...
USE my_UserDB
GO
SELECT TOP 10 'DBCC SHOWCONTIG(' + CAST(id AS NVARCHAR(20)) + ')' + CHAR(10) + 'PRINT '' ''' + CHAR(10)
FROM sysindexes WHERE indid = 1 OR indid = 0 ORDER BY rows DESC
Using output gen'd from the SQL listed above, I can exec: DBCC SHOWCONTIG(1733581214) PRINT ' ' -- (where 1733581214 was gen'd from above statement as well)
to list fragmentation, scan density, etc...
Can someone supply a newer compatible SQL statement to pull from DMV's in SQL 2012, 2014, 2016?
Very much appreciated - thx in advance!
BT

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

I think the DMV you're looking for is called something like sys.dm_index_physical_stats. There'll be plenty of sample queries out there if you search for them.

John

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

John Mitchell-245523 - Tuesday, May 30, 2017 8:11 AM
I think the DMV you're looking for is called something like sys.dm_index_physical_stats. There'll be plenty of sample queries out there if you search for them.
John

sys.dm_db_index_physical_stats

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

Express12 - Tuesday, May 30, 2017 8:04 AM
This older query still executes fine as it refers to the "compatibility view" called sysindexes --- but I'd like to upgrade it now...
USE my_UserDB
GO
SELECT TOP 10 'DBCC SHOWCONTIG(' + CAST(id AS NVARCHAR(20)) + ')' + CHAR(10) + 'PRINT '' ''' + CHAR(10)
FROM sysindexes WHERE indid = 1 OR indid = 0 ORDER BY rows DESC
Using output gen'd from the SQL listed above, I can exec: DBCC SHOWCONTIG(1733581214) PRINT ' ' -- (where 1733581214 was gen'd from above statement as well)
to list fragmentation, scan density, etc...
Can someone supply a newer compatible SQL statement to pull from DMV's in SQL 2012, 2014, 2016?
Very much appreciated - thx in advance!

For things like this, a quick Google search for the syntax (DBCC SHOWCONTIG, in this case) takes you right to the answer. It's covered in the very first item listed by Microsoft "docs".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

Jeff Moden - Tuesday, May 30, 2017 2:16 PM
Express12 - Tuesday, May 30, 2017 8:04 AM
This older query still executes fine as it refers to the "compatibility view" called sysindexes --- but I'd like to upgrade it now...
USE my_UserDB
GO
SELECT TOP 10 'DBCC SHOWCONTIG(' + CAST(id AS NVARCHAR(20)) + ')' + CHAR(10) + 'PRINT '' ''' + CHAR(10)
FROM sysindexes WHERE indid = 1 OR indid = 0 ORDER BY rows DESC
Using output gen'd from the SQL listed above, I can exec: DBCC SHOWCONTIG(1733581214) PRINT ' ' -- (where 1733581214 was gen'd from above statement as well)
to list fragmentation, scan density, etc...
Can someone supply a newer compatible SQL statement to pull from DMV's in SQL 2012, 2014, 2016?
Very much appreciated - thx in advance!
For things like this, a quick Google search for the syntax (DBCC SHOWCONTIG, in this case) takes you right to the answer. It's covered in the very first item listed by Microsoft "docs".

For mapping the old base system tables, the page at https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/mapping-system-tables-to-system-views-transact-sql shows a bunch of them. Beyond that, just search for the individual name.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions