Category Archives: SQL Server 2012

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

SQL Server 2012,2014,2016 – When a CU patch can break an existing functionality!!!

Recently MS released the following patches for SQL Server. The product team has identified that the patches can be potential CDC functionality breaker. The link below for the note from the product team. Worth a read before applying them on production boxes specially if CDC is active.

  • SQL 2012 SP3 CU8
  • SQL 2014 SP1 CU10
  • SQL 2014 SP2 CU4
  • SQL 2016 RTM CU5
  • SQL 2016 SP1 CU2

https://blogs.msdn.microsoft.com/sql_server_team/cdc-functionality-may-break-after-upgrading-to-the-latest-cu-for-sql-server-2012-2014-and-2016/

 

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

 

SQL Server – Integration Service – How To Extract Images From a SQL Server Table To a Folder Using SSIS

In this article we will go through how we can extract images from a table in SQL Server and copy it on a folder. To achieve this we need to use SQL Server Integration Service. Let’s go over the process step by step.

Step 1: Create a new SSIS project in BIDS
IE1

Step 2: Drag and drop the Data Flow Task from the SSIS Toolbox to the design surface

IE2

Step 3: Go to the Data Flow tab. Drag and Drop the following on the design surface.

ie3

Step 4: Right click on the connection manager area and create a new OLEDB connection. We will create a connection for the AdventureWorks2012 database and the Production.Photo table.

 

IE4 IE5

 

Step 5. Use the following command to select the data from the table

declare @Ipath varchar(100)= 'C:\document\'
SELECT [ThumbNailPhoto],
@Ipath+[ThumbnailPhotoFileName] AS Imagepath
FROM [AdventureWorks2012].[Production].[ProductPhoto]

 

IE6

 

Step 6. Double click on the export column transformation editor and select the following values

IE7

 

Step 7. Build the package and execute. You should find all the images in the selected folder

IE9

 

SQL Server 2012 – How to change the collation of SQL Server.

In this article we will see how we can change the collation of a SQL Server post installation. To achieve this we need to rebuild the master database. While rebuilding the master database, the process gives an option to change the collation. Let us understand the process step by step and change the collation of an already installed SQL Server.

Step 1: Take a backup of all your system databases and user databases.

Step 2: Script out all Logins.

In this example we will change the collation fromSQL_Latin1_General_CP1_CI_AI’ to ‘SQL_Latin1_General_CP1_CI_AS’
collate1

 

Step 3: Stop the SQL Server Service.

collate3

 

Step 4: Via Command prompt locate to the Binn directory of the SQL Server

collate2

Step 5: Run the below command

sqlservr -m -T4022 -T3659  -s”INST2012_1″  -q”SQL_Latin1_General_CP1_CI_AS”

Parameters:
[-m] Single User Mode
[-T] Trace flag turned on at startup
[-s] SQL Server Instance Name
[-q] New Collation

collate5

Step 6: Restart SQL Server and check the collation

collate6

Using TIMEFROMPARTS() – SQL Server 2012

This new function in SQL server 2012 helps in converting time parts to time. The syntax of this function is as follows

TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

The range of the parameters for TIMEFROMPARTS is as follows :
Hour -> 0-23.
Minutes -> 0-59.
Seconds -> 0-59.
Fractions -> 0-9999999.
Precision -> 0-7.

Let us understand this with an example.

DECLARE @hour INT, @min INT,@Sec INT,@frac INT;
SET @hour = 13
SET @min = 24
SET @Sec = 22
SET @frac = 45
SELECT TIMEFROMPARTS(@hour,@min,@Sec,@frac,2)

time1

This function requires a valid value for the Hour,Minute, Seconds, Fractions, Precision parameters. If any invalid value is passed then this function will return an error. If a Null value is passed on for the Precision parameter then it generates an error. For other parameters if a Null is passed then the output is also Null.

For example in the below code we pass an invalid value for the Hour parameter

time2

Now lets see what happens when we pass a Null value to the Fraction parameter

time3
 
 
 
 
 
 
   
   
   
      
If we fail to pass any one of the parameters then the following error message is returned.
time4

Using DATEFROMPARTS() – SQL Server 2012

DATEFROMPARTS() returns a date value for the specified year, month, and day.

Let us understand this with an example.

The SQL code for returning day, month and year parameters as a date would probably look something like this

DECLARE @Day INT = 07, @Month int = 03,@Year INT = 2014
SELECT CONVERT(datetime,CONVERT(varchar(10),@Year) + '-' +
CONVERT(varchar(10),@Month) + '-' +
CONVERT(varchar(10),@Day),103) AS TheDate

datetime1Now lets implement DATEFROMPARTS

Here is the code for the same

DECLARE @Day INT = 07,
@Month int = 03,
@Year INT = 2014
SELECT DATEFROMPARTS (@Year, @Month, @Day) AS TheDate

datetime2

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

Now lets watch the Live data
ee22

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.
ee24
Step 3 -> Start the session and fire some queries and observe the Live data in action.

%d bloggers like this: