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.
