SQL-2012-logo www.oostdam.infoMicrosoft SQL Server is a great product to work with. Especially after the releases of version 2005, 2008 version and now we have the 2012 available. For me, it is the standard for all kinds of databases. The Object explorer, Tuning Advisor, the Query Analyser and T-SQL statements work great, and are a joy to work with. In this version there are even more and more improvements, so thats the main reason I will publish quit a number of articles about it in the next couple of weeks.  In these articles I like to share with you, some errors and "best practices" that came up during my work on a number of occasions. So, initially set up was for my own references, now I share it with you. Enjoy these articles!! Due to the fact that a big percentage of visitors of this page are international based, this article is in the english language. This article handles a simple query that you can run, in order to quickly check your index defragmentations. 

The problem is here how to check your (re)indexes within SQL:
Sure, your maintenance jobs will run normally, but when you are in doubt if rebuiding of indexes etc. actually takes place?
You should consider to run the Query like it stands below here, towards your database. 

PRINT'Look at the Index Information'

     DB_NAME(idxst.database_id) AS [database_name],
     OBJECT_NAME(idxst.object_id, idxst.database_id) AS [object_name],
     QUOTENAME(idxif.name) [index_name],
          WHEN avg_fragmentation_in_percent < 10 THEN 'LOW'
          WHEN avg_fragmentation_in_percent < 30 THEN 'MEDIUM'
          WHEN avg_fragmentation_in_percent < 50 THEN 'HIGH'
          ELSE 'EXTREME'
     END as fragmentation_indicator,
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL , 'LIMITED') AS idxst -- not using DETAILED or SAMPLED
INNER JOIN sys.indexes idxif ON idxst.object_id = idxif.object_id AND idxst.index_id = idxif.index_id
ORDER BY [object_name], [index_name]

Among lots of other information that can be usefull for you,
you are now able to see instantly if your database indexes are defragged or not.

If you have any questions about this article, or have an addition to it, do not hesitate to contact me.

Have fun using SQL Server 2012 !



Ben OostdamBen Oostdam has been working with Windows systems since 1993. Worked for several companies as a system administrator, and is currently a Senior Support Engineer and Trainer for a large company in the Netherlands specialized in System Center Solutions, Windows 7 & 8 and Windows Server products 2008 & 2012.

Disclaimer: The information contained in this website/article is for general information purposes only. The information is provided as is, by Ben Oostdam and while we endeavour to keep the information up to date and correct, we make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose. Any reliance you place on such information is therefore strictly at your own risk. In no event will we be liable for any loss or damage including without limitation, indirect or consequential loss or damage, or any loss or damage whatsoever arising from loss of data or profits arising out of, or in connection with, the use of this website. Through this website you are sometimes able to link to other websites which are not under my control. I have no control over the nature, content and availability of those sites. The inclusion of any links does not necessarily imply a recommendation or endorse the views expressed within them. Every effort is made to keep the website up and running smoothly. However, I take no responsibility for, and will not be liable for, the website being temporarily unavailable due to technical issues beyond our control. All entries in these articles, are my individual opinion, or from co-writers and they don't necessary reflect the opinion of my employer.


Friday the 7th, May 2021. All rights reserved.. // Oostdam WebDesign