SQL script to Select Fragmentation

• dEVafri

  SSC Enthusiast

  Points: 150

  More actions

  May 22, 2017 at 12:46 pm

  #323713

  Hi,

  This SQL script is to select fragmentation tables > 5%, but it's not working.  Any idea?

  SELECT si.name, sn.name, SUM(ps.avg_fragmentation_in_percent) AS 'fragmentation'
FROM sys.dm_db_index_physical_stats(5, OBJECT_ID(''), NULL , NULL, N'LIMITED') AS ps
JOIN sys.dm_db_index_operational_stats(5, OBJECT_ID(''), NULL , NULL) AS os
  ON ps.database_id = os.database_id AND ps.[object_id] = os.[object_id]
  AND ps.index_id = os.index_id AND ps.partition_number = os.partition_number
  INNER JOIN sys.sysobjects si on ps.object_id = si.id
  INNER JOIN sys.sysindexes sn on ps.object_id = sn.id and ps.index_id = sn.indid
WHERE avg_fragmentation_in_percent >= 5 --5 to 30 needs reorg, 30 plus needs rebuild
  AND ps.index_id > 0 -- ignore heaps
  AND ps.page_count > 8 --8 from recommendation
  AND ps.index_level = 0 -- leaf-level nodes only, supports @scanMode
GROUP BY ps.database_id, QUOTENAME(DB_NAME(ps.database_id)), si.name, sn.name, ps.[object_id],
  ps.index_id, ps.partition_number ORDER BY si.name, sn.name OPTION (MAXDOP 2)


• Lowell

  SSC Guru

  Points: 323546

  More actions

  May 22, 2017 at 12:56 pm

  #1943635

  your code for the function [sys].[dm_db_index_physical_stats] is hardcoded to database_id = 5, is that right?
   i prefer to use the db_id and current database context.
  object_id('') will resolve to null, just put null; for me, instead of a placeholder function, i prefer to use an explicit null.
  i also never use old views like sysobjects and sysidnexes, but the newer stuff instead.
  since you grouped by index_id, object_id, you effectively are not grouping at all.
  here's how i would do it:

  SELECT
dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') 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()
AND indexstats.avg_fragmentation_in_percent >=5.0
AND [indexstats].[index_id] > 0 -- ignore heaps
AND [indexstats].[page_count] > 8 --8 from recommendation
AND [indexstats].[index_level] = 0
ORDER BY indexstats.avg_fragmentation_in_percent desc

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• dEVafri

  SSC Enthusiast

  Points: 150

  More actions

  May 23, 2017 at 9:21 am

  #1943824

  Hey Lowell,

  Thank you for your help!  db_id is actually 7.  After I changed it, it worked.  I actually like your query, so I'll be using it since it also provides the schema.