February 26, 2011 at 2:09 pm
Dear All,
I have a Database that is set to a Simple Recovery Model, some days there are queries that are run (more like transactional queries), I find that after they are run the Server becomes drastically very slow.
I want to find out a way of knowing:
1. The size in which the Transaction Log file is set
2. When to truncate the log files in order to free up space, therefore the the server performance is "good enough"
3. If truncation of log files often is a good practice
4. How often, if any should I be Backing up the log files.
If there are any other areas I should be looking at to improve the Server performance, I would be very grateful if you would pass on your knowledge.
Thank you.
February 26, 2011 at 2:41 pm
tt-615680 (2/26/2011)
1. The size in which the Transaction Log file is set
Query sys.database_files
2. When to truncate the log files in order to free up space, therefore the the server performance is "good enough"
Log space and log truncation has nothing to do with performance
3. If truncation of log files often is a good practice
No. If you're in simple recovery you don't need to do anything to the log. SQL will auto-truncate on checkpoint. If in full recovery you should never explicitly truncate the log as you will break the log chain and defeat the point of being in full recovery.
4. How often, if any should I be Backing up the log files.
In simple recovery you can't.
Please read through this: http://www.sqlservercentral.com/articles/64582/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 26, 2011 at 3:14 pm
GilaMonster (2/26/2011)
tt-615680 (2/26/2011)
1. The size in which the Transaction Log file is setQuery sys.database_files
2. When to truncate the log files in order to free up space, therefore the the server performance is "good enough"
Log space and log truncation has nothing to do with performance
3. If truncation of log files often is a good practice
No. If you're in simple recovery you don't need to do anything to the log. SQL will auto-truncate on checkpoint. If in full recovery you should never explicitly truncate the log as you will break the log chain and defeat the point of being in full recovery.
4. How often, if any should I be Backing up the log files.
In simple recovery you can't.
Please read through this: http://www.sqlservercentral.com/articles/64582/
Thank you for your reply, I really appreciate it. So if I'm running a long running query, which slows down the server performance?
I have few suggestions but they might not be the correct way of resolving my ongoing issue:
1. DBCC Checkdb or DBCC Checktable
2. re-index large tables (if so, should this be done often)
3. Updatestats
4. Kill the process that is taking up the most CPU and restart SQL Session.
Thank you
February 26, 2011 at 4:35 pm
None of the above.
CheckDB checks for database corruption, and it's a very CPU and IO intensive process. Run that and you will degrade performance. Same with index rebuilds.
If a query is performing badly you need to identify why it's slow. Is it written inefficiently? Are there missing indexes? Is it blocked? What's it waiting for?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply