Cloud

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

GO

 

2.  Then run this command on the tables (may take a while):

EXEC sp_MSForEachtable ‘DBCC DBREINDEX (”?”)’

GO

 

4.  Then, run this query (may take a while)

EXEC sp_MSForEachTable "UPDATE STATISTICS ? WITH FULLSCAN"

GO

One Response

  1. Curtiss March 28, 2013

Leave a Reply