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

x

We use cookies to ensure the best possible experience on our website. Detailed information on the use of cookies on this site is provided in our Privacy and Cookie Policy. Further instruction on how to disable our cookies can be found there.