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.

SSIS Performance tuning

Performance problems will raise due to bottlenecks .

Situation to find bottle neck

1. To create a package with optimization .

2. There is a package which is running such a long time .

This bottle neck can be at any  many levels .

1. Package Level

2. Source Level

3.Destination Level

4.Transformation Level

5.Data Flow Task Level

6.System Level

We can identify bottlenecks by using progress tab information or by using log providers.

Package Level Tuning 

1. Implement check points to have better restart ability of components in the package .

2. Disable Event Handler: EH decrease package performance so unnecessary event handlers should be removed or disabled .

3. Maximum Concurrent Executable : Increasing the nof executable will increase the parallelism of package and concurrantly execute in less time .

4. Maximum Error Count : Default '1' means it fails for single error in the package . If you increase the error count it doesn't fails the package until it reaches the count.

Data Flow Task Level Tuning Tips

1. Delay Validation (T/F) : True means the validation of component is delayed until the execution of  other component finished.

2. Default buffer max rows and size : Increase or decrease according to the volume of data loading i.e for more volume increase rows and buffer size . For less volume decrease rows and buffer size .

3. Engine Threads : Default it takes 10 if we increase more threads it runs more parallel and uses more processes to finish the data flow operations .

Note :  Thread is a part of process to do some task . More threads less data and less threads with more data decreases performance .

4. Run in Optimized Mode : If it is true then data flow avoids unnecessary transformations , conversions etc operations during package execution .

Source Level Tuning

In case of Flat File

1. Try to take the flat file local to the system .

2. Use the property "FastParse=True" so that the column uses faster , local neutral processing routines and avoids unnecessary conversions .

We can find fast parse option by right clicking on advanced editor --> Input and Output properties --> O/P columns --> Fast Parse

If the source is table or view

1. Create index on source table so that it retrieves the data faster.

2. Instead of taking a table , take a SQL Query or SQL Command as data access mode to get the required columns and rows of data .

3. At connection manager level click on ALL and change the packet size : 32767 instead of 0 . If packet size is zero means 4 KB of data will be transferred at a time . If packet size 32767 means 32 KB of data to be transferred at a time .

Destination Level Tuning Tips 

1. In case of flat file try to take the file local to the system .

2. In case of relational db table or view use data access method as SQL Command to load required data.

3. Use data access mode as fast load to load the data much faster .

4. If the table contains constraints , indexes and triggers then loading will be slow so we need to disable or drop them once the loading finished recreate or enable them.

To implement this there are many ways

Another way is event handling

on pre execute of data flow disable / drop functionality.
on post execute of data flow enable / recreate .

Transformation Level Tuning Tips

We need to identify either the transformation is synchronous or asynchronous .

Asynchronous transformation always takes time to run than synchronous.

Synchronous Transformation 

Here rows are processed as it is getting from the source .ex: derived column , data conversion etc.

Synchronous transformation occupies less memory and process less rows.

Asynchronous Transformation

In Asynchronous transformation until the retrieved rows system collects it doesn't perform any operation. It retrieves more memory and less rows. These transformations either blocks partially or fully so these transformations also called as partial blocking or fully blocking transformations ex sort,merge,aggregate etc.

if there are no bottle necks in the above all levels then we are having bottle necks at system level.


I hope the article was useful! and I need to let you know that this won’t stop here .keep in touch and feel free to ask any doubts related to this topic .

SSRS Performance Tuning

The below are the key points needs to consider for better reporting performance .

We can identify sluggish reports (slow running ) from query execution log view from Report Server db.

From execution log view we can identify where the report server spending most of time i.e data retrieval , report processing and rendering .

Data Retrieval Time :

1. Some of the Reports performance significantly improved by querying from OLAP Cube .

2. T SQL Query used in the report needs to be tuned and optimized for better performance.

3. If real time data is not desired then create snapshots.

4. Queries bringing large data set shouldn't be run for interactive report viewing but should be derived via subscriptions .

5. Don't retrieve unnecessary data.

6. Increase query or report timeout.

High Report Processing Time :

1. Avoid sorting and grouping of the Reports .

2. Data which is grouped and sorted from the SQL db shouldn't be grouped and sorted at the report layer .

3. Add page breaks for large reports to take advantage of on-demand report processing introduced in SQL 2008 .

4. Avoid using Globals!Totalpages function in any of the expression in the reports.

5. Avoid images and charts nested inside tablix .

6. Use Drill Through Reports i.e Summary Report --> Detail Report whereever feasible since it reduces the data set significantly .

7. Avoid blank pages .

RS Scaleout Deployment :

  For Large reports avoid running them interactively and schedule them as email subscriptions which can be run on separate server dedicated for processing subscription jobs .

Reduce N/W Latency :

1. Interactive Reportserver and B2B Production can moved to a single server to reduce the n/w latency .

2. Ensure the single server is much higher configuration since both report service and SQL Server are resource intensive application.

3. Max server memory should be capped to limit SQL Server memory to 40-50% of the total physical RAM .


I hope the article was useful! and I need to let you know that this won’t stop here .keep in touch and feel free to ask any doubts related to this topic .