Mashed Articles

This question had been asked in NG yesterday.I think it is a good exercise so I take a script too get the column usage from both sides (parent/child) written by Aaron Bertrand and modified a little bit to return the table/column that does not have an index on.

WITH fk_no_indexes
AS
(
SELECT
[constraint_name] = f.[name],
[child_table] = OBJECT_NAME(f.parent_object_id),
[child_table_id]=f.parent_object_id,
[child_column] = cc.name,
[child_column_id]=cc.[column_id],
[parent_table] = OBJECT_NAME(f.referenced_object_id),
[parent_column] = pc.name
FROM
sys.foreign_keys f
INNER JOIN
(
SELECTc.[object_id],c.name,c.column_id,ic.index_id
FROM sys.columns c INNER JOIN sys.index_columns ic
ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
) AS pc
ON
f.key_index_id = pc.index_id
INNER JOIN sys.foreign_key_columns fkc
ON f.[object_id] = fkc.constraint_object_id
AND pc.[object_id] = fkc.referenced_object_id
AND fkc.referenced_column_id = pc.column_id
INNER JOIN sys.columns cc
ON fkc.parent_object_id = cc.[object_id]
AND fkc.parent_column_id = cc.column_id
)
SELECT [constraint_name],[child_table],[child_column],[parent_table] FROM
fk_no_indexes WHERE NOT EXISTS
(
SELECT * FROM sys.index_columns i
WHERE i.[object_id]=[child_table_id]
AND [child_column_id]=column_id
)
ORDER BY
constraint_name,
child_table;

Hi everybody.
It seems like I am seeing more and more inquires from our clients asking for help solving performance related issues with rebuilding indexes. All of them (or almost all of them) have been using Maintanace Plan Rebuild/Reorganize Index Task. We have lots of clients who have pretty big databases(>200GB) and have not hired yeat a DBA:-).They used to use this task and specify all tables as well as all databases, moreover, one client used to run such tasks in the middle of work day. It leads to locks on tables and performance decreasing. I would also notice you to not cancelling the task as SQL Server will rolback the whole transactions and you are about to wait a lot of time. Just let the task to complete. I suggested instead of running the task, first, identify fragmented indexes on tables that have more than 1000 pages.

DECLARE @RebuildStatement nvarchar(4000)
DECLARE RebuildStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT ‘ALTER INDEX ‘+i.name+ ‘ ON ‘+
OBJECT_NAME(i.object_id)+’ REORGANIZE;’
FROM
sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, ‘DETAILED’) phystat inner JOIN sys.indexes i
ON i.object_id = phystat.object_id
AND i.index_id = phystat.index_id WHERE phystat.avg_fragmentation_in_percent > 40
and page_count>=1000

OPEN RebuildStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM RebuildStatements INTO @RebuildStatement
IF @@FETCH_STATUS 0 BREAK
EXEC(@RebuildStatement)
END
CLOSE RebuildStatements
DEALLOCATE RebuildStatements

The above SELECT generates a simple script to REORGANIZE (change to REBUILD) indexes and EXECUTES the dynamic sql. As you probaly know this script has to be run on SQL Server 2005/2008 and do not forget about really great feature such rebuilding indexes ONLINE. For more details please see BOL.

It is much easier in SQL Server 2005 to get row count per table.

SELECT
[TableName]=tbl.name,
[RowCount] = SUM
(
CASE
WHEN (pt.index_id < 2) AND (au.type = 1) THEN pt.rows
ELSE 0
END
)
FROM
sys.tables tbl
INNER JOIN sys.partitions pt
ON tbl.object_id = pt.object_id
INNER JOIN sys.allocation_units au
ON pt.partition_id = au.container_id
GROUP BY
tbl.name ORDER BY [RowCount]DESC;