Quick Fix: System Center query performance or general slowness – Database Optimizations
Sometimes we get wrapped up in day to day operations and don’t notice that an application such as System Center can quickly come to it’s knees. All of a sudden you are left with an application that is cumbersome or hard to use. The slowness could be for various reasons, but a good start is always the Hardware and Database layers.
Recently I came across a poorly performing application and noticed that it would run slowly from a workstation or from the database server. I ran the first query below to check for fragmentation of the database, then ran the a reindex followed up by an update statistics.
Important: Take utmost care with these queries and ensure to monitor performance of the DB Server\Instance while running. I typically shut down all Services\Connections to the Database Server while the optimizations take place.
1. Open SQL Server Management Studio, run the query below to check fragmentation:
SELECT a.index_id, name, avg_fragmentation_in_percent FROM
sys.dm_db_index_physical_stats (NULL,NULL, NULL, NULL, NULL) AS a JOIN sys.indexes
AS b ON a.object_id = b.object_id AND a.index_id = b.index_id Where
avg_fragmentation_in_percent > 30
2. Then run this command on the tables (may take a while):
EXEC sp_MSForEachtable ‘DBCC DBREINDEX (”?”)’
4. Then, run this query (may take a while)
EXEC sp_MSForEachTable "UPDATE STATISTICS ? WITH FULLSCAN"