-1
select 
OBJECT_SCHEMA_NAME(IPS.object_id) 'schema',
  OBJECT_NAME(IPS.object_id) as table_name,index_type_desc,
  round(avg_fragmentation_in_percent,2) 'avg_fragmentation_in_percent',
  si.name
   from  sys.dm_db_index_physical_stats(db_id(DB_NAME()),null, 
   NULL, NULL , 'DETAILED') AS IPS inner join  sys.indexes si on 
   si.object_id=IPS.object_id
   where
   index_type_desc <> 'HEAP' and avg_fragmentation_in_percent >=50
   order by avg_fragmentation_in_percent desc
  • 1
    What kind of "verify" do you want? Logical correctness? Style? The things listed in Paul's answer below are useful, but you should also check your use of aliases - use IPS. in front of avg_fragmentation_in_percent, and use AS and don't use apostrophes in aliases. And check for case consistency... – Rob Farley Jan 3 '16 at 20:50
5

Your query has most of the main elements, but you are missing the important index_id join predicate from sys.dm_db_index_physical_stats to sys.indexes:

AND si.index_id = IPS.index_id

You might like to compare your query to the similar one in Glenn Berry's Diagnostic Information Queries for SQL Server 2008:

-- Get fragmentation info for all indexes above a certain size in the current database
--(Query 55) (Index Fragmentation)
-- Note: This query could take some time on a very large database
SELECT 
    DB_NAME(ps.database_id) AS [Database Name], 
    OBJECT_NAME(ps.OBJECT_ID) AS [Object Name], 
    i.name AS [Index Name], 
    ps.index_id, 
    ps.index_type_desc, 
    ps.avg_fragmentation_in_percent, 
    ps.fragment_count, 
    ps.page_count, 
    i.fill_factor, 
    i.has_filter, 
    i.filter_definition
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL , N'LIMITED') AS ps
INNER JOIN sys.indexes AS i WITH (NOLOCK)
    ON ps.[object_id] = i.[object_id] 
    AND ps.index_id = i.index_id
WHERE 
    ps.database_id = DB_ID()
    AND ps.page_count > 2500
ORDER BY 
    ps.avg_fragmentation_in_percent DESC 
OPTION (RECOMPILE);

In particular, you might want to limit the minimum page count because it is rarely worth worrying about small structures. Ideally, you would incorporate the features from both queries that suit your purposes.

Required attribution for the code above:

--******************************************************************************
--*   Copyright (C) 2014 Glenn Berry, SQLskills.com
--*   All rights reserved. 
--*
--*   For more scripts and sample code, check out 
--*      http://sqlskills.com/blogs/glenn
--*
--*   You may alter this code for your own *non-commercial* purposes. You may
--*   republish altered code as long as you include this copyright and give due credit. 
--*
--*
--*   THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
--*   ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
--*   TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
--*   PARTICULAR PURPOSE. 
--*
--******************************************************************************

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.