Tag Archives: SSMS

Try and try again – SSMS

Came across this very interesting article which talks about connection resiliency logic in the SQL Server management studio. What this basically does is try to re-connect in case of a time out after sleeping for 10 seconds. The default value for retry is 1 and default interval is 10 seconds. This article from

White Paper on connection resiliency


I hope this article was useful. Have a good day ahead.




SQL Server – How to compare execution plans

One of the challenges for DBAs and Developers was to compare execution plans effectively. In the latest SSMS released by MS we have an option of comparing execution plans in a better readable format. This short article will explain that.

Step 1: Download the latest SSMS from MS website or If you have installed SQL server 2016 then this feature will be available.


Step 2: Install the downloaded package

Step 3: Execute a query and save the execution plan somewhere.

Step 4: Execute another query and wait till the plan is displayed.

Step 5: Right click on the displayed plan and choose the following option highlighted.

This will ask for the saved execution plan. Choose the saved plan.

Step 6: Click on open
The actions opens 2 windows which shows the difference between the 2 execution plans as depicted below. This is a very handy feature when we need to compare the execution plans for a SP or query pre and post change to gauge the effect in terms of performance.




I hope this article was useful. Have a good day ahead.

Extended Events in SQL Server 2012 – Part 2

In my earlier post on Extended events we discussed how we can use the SSMS GUI in SQL Server 2012 to create new extended events. In this article we will take a deeper dive into the SQL Server 2012 SSMS GUI and explore other extended events features.

How to add fields to a session

Step 1 -> Right click on the session we created. Click on properties
ee14Step 2 -> Click on Events -> Click on Configure
ee15Step 3 -> When we click on configure, the below window will appear
ee16Step 4 -> Now click on the first event ‘query_post_execution_showplan‘ and we will find a list of Actions on the right hand pane being displayed. These are the bunch of global fields from which we can choose and add to the events and the relevant data will be captured by the event trace. For this event we will choose ‘cpu_id’ and ‘database_name’.
ee17ee17Repeat the same for the next event
ee19Step 5 -> Now Click on the ‘Events Fields‘ tab and select the below as depicted. Click OK.
ee20ee21Now lets start the session and examine the Live data. For generating activity on the SQL Server we will run the below queries

USE AdventureWorks2012
SELECT * FROM Production.Product pp
WHERE pp.ProductID > 40
SELECT * FROM Production.BillOfMaterials Pb
WHERE pb.BillOfMaterialsID > 100

Now lets watch the Live data

How to add filters to a session

In this section we will add filters to the session we created. Filters allow you to fetch events based on the criteria provided.
Step 1 -> Right click on the session -> Click on Properties -> Click on Events -> Choose an event -> Click on the ‘Filter(Predicate)’ tab
ee23Step 2 -> From the Field tab choose a filter from the drop down. In our case we will choose duration and set a value in the Value field. Click on OK.
Step 3 -> Start the session and fire some queries and observe the Live data in action.

Extended Events in SQL Server 2012 – Part 1

In SQL Server 2008, Microsoft introduced a feature called Extended Events. Extended Events helped in collecting and analyzing event driven data about the SQL Server instance. Extended Events introduced lesser load than trace or profiler events on the server. But in SQL Server 2008 there was no GUI that allowed direct communication with the events. Complex T-SQL had to be written to fetch the information which was returned in xml format.

In SQL Server 2012, MS has introduced a GUI in the Management Studio (SSMS)  that allows working with Extended Events and viewing event related data a much simpler process. In this article we will understand and see step by step how we can create and manage Extended events via the GUI that is built in the SSMS.

Step 1: Connect to SQL Server 2012 instance -> Open Management Tab -> Open Extended Events -> Right Click on Sessions -> New Session
Step 2 -> In the new session dialog box type in a name of the session. In this case we will use ‘Session1’. For now we will not choose any template.

Step 3 -> Click on Events tab -> On the below window you will find many Events listed from which we need to pick an event or multiple events that we want to capture. Once selected we need to click on the button to add it. For this article we will use the Events
a) query_post_execution_showplan and b) sql_statement_completed
EE3AEE4Step 4 -> Click on Data Storage -> Here you specify how you can capture data so you can view it later. Here we will save it as Event File. Mention the path where we would save the file -> Click on OK.
EE5Step 5 -> Go to management studio -> Under sessions you will find New session listed. It would be in a stopped state as this is the default behavior. Right Click on the session and click on ‘Start Session
EE6EE7EE8Step 6 -> Now right click on the created session and select ‘Watch Live Data’. If there is no activity on the instance you will not see any data.
EE9EE10Step 7 -> Let us fire few queries on the AdventureWorks2012 database and watch the window. Following are queries that we would run on the database for testing. After running the queries go back and open the ‘Watch Live Data’ window again. Click on the Event data and observe the below.

USE AdventureWorks2012
SELECT * FROM Production.Product pp
WHERE pp.ProductID > 40
SELECT * FROM Production.BillOfMaterials Pb
WHERE pb.BillOfMaterialsID > 100

EE11EE12EE13In this article we have seen how we can start a new event trace from SSMS. In my next article we will explore more into the new GUI feature that MS has added to SQL Server 2012.

%d bloggers like this: