Transaction log is full
Posted by Ravi Khanal on Sep 21, 2008
Transaction log is also major cause for slowing the Performance of SQL Server. As a Database Administrator you should monitor your transaction log and check whether it is full or not. Transaction log is a WRITE AHEAD LOG. So whenever there is any data modification in the database, then “before image” and “after image” of any data would be written in the transaction log. If the transactions that are making the data modifications is running or its not being committed then these logs in the transaction log would be the active portion of transaction log. The committed transaction log would be inactive log in the transaction log. The lazy writer thread wakes up after certain period of time and it would write those logs to the physical disks. But sometime if the transaction are too long and its doing lots of updates then it would make the transaction log full and you will get “Out of disk” error. If you are maintaing full Recovery mode and never taking any log backup then its another major cause for transaction log to become full. Transaction log is the important component of SQL Server for recovering from any disaster scenarios. So as a Database Administrator you should always have a close look on the Transaction Log.
So you should use some DBCC command on the regular basis to find out about the Transaction Log.
- DBCC SQLPERF(logspace)
This will tell you how much transaction log is being used.
- DBCC LOGINFO
This will tell you about the virtual logs inside the transaction log.
- DBCC OPENTRAN
This will show you the open transactions or the transaction that are not yet committed. Read the rest of this entry »
Tempdb best practices
Posted by Ravi Khanal on Aug 26, 2008
Tempdb database provides temporary space for various operations and is recreated every time SQL Server is restarted. It is a temporary database to store temporary tables, table variables, cursors, work tables, row versioning. Proper optimization of the tempdb database increases the performance of your SQL Server.
Some of the best Practices for tempdb are: Read the rest of this entry »
Tips for Performance Monitoring
Posted by Ravi Khanal on Jul 20, 2008
Performance Tuning is the most important job you should perform as a Database Administrator. The most important thing you should consider for monitoring the performance of the server are:
Troubleshoot the Slow running queries:
- Try to see whether a batch of commands are running or a single query is running.
- Examine the Execution plan by using SQL Profiler and SQL Query Analyzer.
- Verify the existence of the correct indexes by performing Index analysis option in SQL Query analyzer or Index tuning wizard. Check for the Table Scan or Index Scan or Index Seek. If there is Table Scan then there is no index in your table. If there is Index Scan then your index is not being used. If there is Index seek then your Index is performing good, check for other issues.
- Run the following DBCC Commands:
DBCC SHOWCONTIG(’JOBS’)
DBCC INDEXDEFRAG(NORTHWIND, ORDERS, CUSTOMERS )
DBCC DBREINDEX(AUTHORS, ‘clustered index’, 70)
-
Remove any Hints and re-evaluate the performance.
-
If a particular query is Slow when it is using Parallel execution, you can try forcing a non-parallel execution plan.
