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.
How to delete duplicate rows?
Posted by Ravi Khanal on Jul 8, 2008
Duplicate rows in a T-SQL can be deleted by using either ROWCOUNT or TOP operator.
If we have a Table name Employee with columns EmpId, EmpLname, EmpFname. If we have some duplicate data for EmpId=1
Then the syntax for deleting duplicate data using ROWCOUNT would be
Set ROWCOUNT 1
DELETE FROM EMPLOYEE WHERE EmpId=1
Set ROWCOUNT 0
By setting the ROWCOUNT 1 we can delete only one duplicate row.
Similarly, we can do it using TOP as,
SELECT TOP(1) FROM EMPLOYEE WHERE EmpId=1
