SQL Server check table fragmentation level

If you are using SQL Server, and discover that what should be a simple select query, or particularly multiple select queries that are unioned together with a sort are running slowly, then the issue is your table indexes. This is particularly the case when using Date and DateTime fields for sorting.

If you are trying to sort a table or multiple tables by Date, then it is a good idea to add an index on the Date column, it makes things significantly faster.

Here is the SQL Query to check all tables in your database and the levels of fragmentation of each:

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
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

500 daily visitors!

500 Visitors a day!

Wow, what a milestone – my blog now receives over 500 unique visitors every day (well the vast majority of days anyway)! Its incredible to think that there is that much information on here that people find interesting!

The top post on my blog is (and has been for some time) Calibri and Cambria fonts for Mac, added back in 2013 as part of my move to the world of Mac. Since I moved to the world of brushed aluminium, I made the decision some 3 months later to move the software company I founded back in 2006 (ISArc) to now use everything Apple too. Its been a great journey, and the more we get into the world of Mac the more we find out new and interesting things about it. For example, did you know that pressing the Command + Up Arrow when in Finder takes you up a folder? So very, very useful to know! Moving to Mac’s brings with it several challenges, particularly when you get used to everything just working, most particularly when it comes to having to support older systems that we originally wrote in ASP.Net.

Our latest project is called Employability Support and is light-years ahead of where our systems would have been if we had stayed developing in the .Net environment mostly because of the wealth of technology out there that is now available that was not available for .Net. Our new systems use a great combination of PHP, JQuery and CSS to do all database calls asynchronously meaning a much better and smoother user experience.

My personal adventure in programming and computing continues and I am now involved in a local CoderDojo helping local kids to get inspired by technology and programming (and playing Minecraft). Its a great source of encouragement when you see 10 year olds writing mobile apps, and makes me realise just how important it is for me to stay up to date with technologies as they change.