SQL DBA : My Query or Application is running slow , How you will trouble shoot it.

Query / Application is running slow:

In General we will face this issues due to poor hardware and poorly / badly written SQL Code . In order to find the solution we need to follow the below mentioned methods to trouble shoot slow running query / application in our day to day activities.

1. Check any open transactions are there by using

DBCC OPENTRAN

2. Check whether any lock types are in the tables

 SP_LOCK or select * from sys.dm_tran_lock[DMV]

3. Check for any blockings are there?

If yes then according to your project process we find blocking's and speak to apps team to confirm which SPID required to kill with approvals via email.

KILL SPID

If no blocking's then?

3. Check for any deadlocks

NOTE: By default SQL Server db engine will not capture any deadlock information DBA team need to enable trace flags.

DBCC Traceon(1222 or 1204, -1)

4. Check for any query execution plan or cost based plan.

Execution plan display:

1.Physical operation (Scan type)

2.Logical Operation

3.Estimated I\O COST

4. Estimated CPU COST

5.Estimated number of executions

6.Estimated number of rows.

7.Cache Size 

6.Check whether any indexes are created on the table or not. If no then inform your dev\apps team to suggest create index which improves performance of query.

7.If Indexes presents then check for FRAGMENTATION LEVEL on the tables.

Select * from sys.dm_db_index_physical_stats (From SQL Server 2005)

or DBCC SHOWCONTIG (Upto 2000 version of SQL Server)

If We FRAGMENTATION level

1. <5 : No action and indexes are good.

2. If >5 and <30 : Index need to reorganize

Alter index <Index Name > on table name reorganize

3. If >30 :  Index need to Rebuild

Alter index <Index Name > on table name Rebuild

If no FRAGMENTATION

8. Check for any missing indexes by using

select * from sys.dm_db_missing_index_details

> Check for unused indexes

select * from sys.dm_db_index_usage_status

9. Check CPU and memory utilisation

10. Run profiler or perfmon tool to capture events or counters depending on type of parameters.

11. Check any disk or I\O or Network related issues.

12. Check for statistics update in case if the result is varying only for few where conditions hence Statistics might be outdated due to bulk loads so due to this optimizer is not able to make the best optimum execution plan .

13. If you are not able to justify then run the DTA (Database Tuning Advisory ) to get estimated performance improvement suggestions.