May 9, 2008 at 12:07 pm
Hello,
Can anyone help me troubleshoot a gradual performance issue? The system is fine in the live database for a day or two, then all of a sudden things become slow to the point that we have to restart the sql service and then everything works again. The test database is not affected by any of this.
We're using SQL 2005 SP2 on a W2K3 server with 4GB of memory. The auto update/create stats are set to off on the live database. Maintenance is run every night to shrink the transaction log to 1GB, rebuild all indexes and update all statistics.
Thanks for your help!
KC
May 9, 2008 at 12:27 pm
A couple of things:
1. Autoupdate stats should probably be on, especially since you do not mentioning manually updating stats. If you have a highly transactional system then out of date stats could be part of the problem.
2. You do not need to shrink the TX Log unless you are running out of space on the log drive. Growing the log takes resources so properly sizing it to handle the load and taking regular backups should make it run better.
Are you running 64-bit? There is a known issue around memory management noted in these posts:
[url]http://support.microsoft.com/kb/918483">
http://support.microsoft.com/kb/918483
I stole those links from this thread.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 9, 2008 at 12:34 pm
Thanks Jack for your response.
We are doing an updating of the statistic as mentioned in my post, as part of the nightly maintenance.
You're correct that we are shrinking the TX log due to space issues. But it is set to 1GB which is sufficient for a days work for them without the log growing and affecting performance.
We're not running 64bit.
May 9, 2008 at 5:11 pm
Just curious - but how often are you running transaction log backups? If you are only running once a day (to shrink), then consider changing the system from full to simple recovery model.
If you are already using simple recovery model, then you need more space available for the log. Continually growing/shrinking the log file is causing some of your problems.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 14, 2008 at 5:39 am
We do full backup every night and a log backup the log once a night then shrink the file. i have doubled the size of the log to see if that helps.
what else could cause slow performance issues requiring a reboot, and that only one database is affected?
Thanks!
May 14, 2008 at 6:57 am
KC (5/14/2008)
We do full backup every night and a log backup the log once a night then shrink the file. i have doubled the size of the log to see if that helps.what else could cause slow performance issues requiring a reboot, and that only one database is affected?
Thanks!
If this is really your backup strategy then you should change the database to the Simple recovery model as Jeff has already suggested. Or, if you want to remain with the Full recovery model you should do regular TX log backups which will allow the log to "wrap" instead of grow.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 15, 2008 at 5:06 am
1) IMNSHO, the tlog has nothing to do with the described behavior. It is a red herring.
2) You need to find out why, very specifically, things are running slow. Analyze your waits and determine what waits are occurring and then address the cause of those waits. You mention sql2005, so get sql2005 troubleshooting performance problems and sql2005 performance tuning waits and queues. These are two documents to be found on microsoft's website. Of particular note is track_waitstats_2005.
3) also run dbcc memorystatus. (http://support.microsoft.com/kb/271624/). are you using litespeed or lots of extended sprocs or CLR? if so, you could be having issues with memtoleave.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply