February 12, 2009 at 6:20 pm
I'm running SQL Server 2005 on Windows 2003. It runs fine for a few days but gets progressively slower and slower until the sqlsrver process is at 99%. Restarting the server fixes it and it runs fine for a day or two again, until it slows down again.
What could cause this? I know there's probably a lot of things, but where should I check first?
February 12, 2009 at 6:23 pm
You may have to check your statistics information - Run it on all the databases -
SELECT O.name AS
, O.name AS [Index Name],
I.rowmodctr AS [Rows Modified] FROM SYSOBJECTS o JOIN SYSINDEXES I ON O.id = I.id WHERE I.rowmodctr > 0
and O.xtype = 'U'
ORDER BY I.rowmodctr DESC
If the value is too high, run update stats immediately.
Do it as a first step, then check for fragmentation....
February 12, 2009 at 6:30 pm
Following the query to get report for Index defrag scan percentage from 10 to 99% for various indexes. (From the last reboot).
use
GO
Select [Object_id] AS ObjectId,
index_id AS IndexId,
avg_fragmentation_in_percent AS FragPercent,
partition_number AS PartitionNumber,
index_type_desc AS IndexType,
alloc_unit_type_desc AS AllocUnitType,
0 AS Online
FROM sys.dm_db_index_physical_stats (db_id(''), NULL, NULL, NULL, 'LIMITED')
WHERE
avg_fragmentation_in_percent > 10.0 AND
index_id > 0 and page_count > 32
ORDER BY ObjectId
It might be Disk fragmentation as well...if above things wont work, post it here..we can check other things..
February 13, 2009 at 2:26 am
Sudiendra (2/12/2009)
You may have to check your statistics information - Run it on all the databases -SELECT O.name AS
, O.name AS [Index Name],
I.rowmodctr AS [Rows Modified] FROM SYSOBJECTS o JOIN SYSINDEXES I ON O.id = I.id WHERE I.rowmodctr > 0
and O.xtype = 'U'
ORDER BY I.rowmodctr DESC
If the value is too high, run update stats immediately.
Do it as a first step, then check for fragmentation....
:w00t: Rebooting the physical server does not gather fresh performance statistics then this is not the problem.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 13, 2009 at 7:26 am
In addition to the above, you'll want to check the Activity Monitor in the GUI as well as employ Profiler to see if there is a specific process or query causing the issue.
And lastly, you'll want to check your autogrowth settings, your transaction log backups (make sure they're being done), etc. Since you're tuning your DB, you'll want to get this all out of the way to make sure that everything continues to run smoothly after fixing the indexes and statistics.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply