Posted on

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',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
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
Posted on

SQL Server – List all tables and the fragmentation percentage

This is another check you should perform once databases start to get large, as having fragmented indexes have a huge performance hit.

For more on what fragmented indexes are, read this fantastic article.

After significant trial and error we have come up with an SQL script that lists all indexes for every table and shows the percentage fragmentation for the selected database on your server in one go!

 

SELECT
a.database_id,
db_name(a.database_id) As DatabaseName,
a.page_count,
a.OBJECT_ID,
a.index_id,
b.name,
a.avg_fragmentation_in_percent
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a
INNER JOIN sys.indexes AS b ON a.OBJECT_ID = b.OBJECT_ID AND a.index_id = b.index_id
WHERE
a.database_id = DB_ID()
ORDER BY
a.OBJECT_ID

Posted on

SQL Server – checking if any tables don’t have a primary key

Its easily done, in the rush to create tables someone forgets to tell the auto-number field that it is also a primary key.

Its not an issue until the database gets big, at which point (as listed on Microsoft TechNet) the performance can suffer by anything between 13% and 460% – which is potentially crippling for your database.

Thankfully there is a simple SQL script you can run to double-check that there are no tables without a primary key.

SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,’TableHasPrimaryKey’) = 0
ORDER BY SchemaName, TableName;