Tag Archives: SQL Server Management Studio

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.

https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

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.
Bodhisatya

Advertisements

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
Ee1
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.
EE2

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
go
SELECT * FROM Production.Product pp
WHERE pp.ProductID > 40
go
SELECT * FROM Production.BillOfMaterials Pb
WHERE pb.BillOfMaterialsID > 100
go

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.


IIF – The InLine conditional Statement in SQL Server 2012

IIF is a new inline conditional statement in SQL Server. We can pass an expression that can be evaluated to either true or false to the function and it returns one value for true and another one for false.

Let us understand this with an example:

DECLARE @MyTeam nvarchar(40) = 'Manchester United'
DECLARE @YourTeam nvarchar(40) = 'Arsenal'
SELECT IIF (@MyTeam = @YourTeam, 'True Devils',
'False Gooners') AS YouAre

The output of the above as below. In this we have passed two different values.
IIF1Now let us pass two same values and see how this works:

DECLARE @MyTeam nvarchar(40) = 'Manchester United'
DECLARE @YourTeam nvarchar(40) = 'Manchester United'
SELECT IIF (@MyTeam = @YourTeam, 'True Devils',
'False Gooners') AS YouAre

IIF2
IIF is very useful where a straightforward comparison has to be made without writing case statements.

How to create a trace from sql server profiler

In this post i would like to demonstrate how to create and run a trace on a SQL Server.
Let us start by understanding what a trace does for us. It helps us gather events that are happening on the instance. These events gathered, help us analyze and understand the state of the SQL server or help us understand any specific issue that we are troubleshooting. Let us understand this with an example:

Step 1: Open SQL Server Management Studio(SSMS)
Step 2: Click on Tools -> Click on SQL Server Profiler
CT1Step 3: Once Profiler opens it will ask for the name of the SQL server on which you want to run the trace
CT2Step 4: In the next window we need to feed the profiler with what we want to capture such as events,template. We can also set a stop time for the trace to stop automatically. The general tab also allows to decide where we want to save the trace file i.e we can either save the trace file as a file or save the data as a table in a database. Apart from that the template drop down helps us decide what kind of data we would like to capture. In our example we will save the trace in a file. We also need to set the maximum file size and if a file would roll over in case the maximum limit is breached.
CT3Step 5: Now let us click on next tab “Events Selection”
CT4Step 6: Check the “Show all events” and “Show all columns” options. Scroll down to the “Stored procedures” events and check the below marked options. Then scroll down to the “TSQL” events and check the following options as below
CT5CT6Step 7: On the same tab we would see to buttons “Column Filters” and “Organize Columns”. Click on “Column Filters” and browse through the options it brings along. you might want to filter out any particular aspect of the system on which you want to collect data. In this example we would select the below
CT8Step 8: Click on OK and our trace is ready to run. Click on Run and you would see the trace capturing data
CT9Step 9 : To Stop the trace click on the stop button
CT11If we want we can save the definition of the trace as a T-SQL script. This is how we would achieve the same.
Step 1: Click on File -> Export -> Script Definition -> For SQL Server 2005 – 2008 R2
CT12Step 2: Save the SQL script in a desired Location
CT13In the next post i will demonstrate how to use this SQL script for later use for capturing data. I hope this demo was useful.

%d bloggers like this: