Real time performance analysis of Stored Procedures

By assuming that there are no Hardware issues with SQL Server, but still SQL Server performance is not up to the mark, following query can provide some useful information for troubleshooting.

select a.*, p.query_plan , p.number
from sys.dm_exec_procedure_stats as a
cross apply sys.dm_exec_query_plan(a.plan_handle) as p
where a.object_id=object_id('[sch].[User_SP]')
There are some very useful columns returned by this query, those are:

cached_time : Recent Recompile Time
last_worker_time : Execution time in micro seconds
last_physical_reads: Number of pages read from physical storage system.
last_logical_reads: Number of pages read from buffer, instead of physical storage.
query_plan: XML representation of query plan, which was used during recent sp run.

The tables in query just keeps the last event i.e. only one record. Adding this query in job allows to store the information in temporary table and after couple of minutes you can get enough details for investigation. For single procedure, more than one query plan may exist. You may compare query plans to find out the differences.

In one of the real time scenarios, I realized that number of logical reads were few millions, for just returning 100 rows. There was issue with one of the join that caused more number of logical reads.

This is not the end, you may also compare physical reads with logical reads. Also consider frequency of procedure being recompiling. Too much recompiling may cause overhead to CPU.

Note: Logical Reads over Physical reads are good. More logical reads shows that the query is getting it’s data from buffer, more physical reads are sign of potential IO bottleneck.

How to enable Replication on Publisher (SQL 2008)

Introduction to Replication.

Before enabling Replication on Publisher you must decide where you want to run the distribution database. Distribution database keeps track of logs to be replicated among subscribers.
Keeping distribution database on same server as publisher may cause IO bottlenecks, depending upon hardware configuration and existing load on server.
Follow the steps to below to enable Distribution and Replication. These steps will add distribution server on selected server.
1. Open SQL Server Management Studio, Connect to SQL Server and Expand SQL Server where you wish to enable publishing and distribution.


2. Right click on Replication in Object Explorer, then select Configure Distribution. This will start “Configure Distribution Wizard”.


3. In case distribution is already setup somewhere, then select appropriate option,


4. In the following screenshot, notice that Snapshot Folder location is a network location. It is a good idea to keep it as network location, so that sql server agent can running on another server can access this location in case of pull subscription.


5. Enter the location to create data files,

6. From the following screen, you can add publisher server to distribution list. Click on Add button and select “Add SQL Server Publisher” to add SQL Server publisher. There is one more option available to add Oracle Publisher too. You may add more than one publisher here.


7. From the below screen select password for remote connection to distribution database.


8. From the following window, you can either select to Configure distribution, or generate the t-sql to configure distribution manually or both.


9. Read the summary of actions below before configuring the distribution.


10. Click Finish to complete the Configuration.