Category Archives: Sql Server 2016

DATEDIFF_BIG Function in Sql Server 2016

Sql Server, Sql Server 2016, , , , , , , , , ,

DATEDIFF_BIG is one of the new function introduced in Sql Server 2016. It gives the difference between the two dates in the units specified by the DatePart parameter and the returned unit is of type bigint. This function like DATEDIFF function returns the number of the specified datepart boundaries crossed between the specified startdate and enddate. The difference between these two functions is the return type. DATEDIFF functions return type is INT, whereas the DATEDIFF_BIG functions return type is BIGINT.

Syntax:

DATEDIFF_BIG ( datepart , startdate , enddate )

[ALSO READ] Difference between DATEDIFF and DATEDIFF_BIG functions in Sql Server

The request for this new function was submitted on the Microsoft connect site some time back in 2008 by Erland Sommarskog.

With DATEDIFF function for milliseconds the maximum difference between startdate and enddate is 24 days, 20 hours, 31 minutes and 23.647 seconds. For second, the maximum difference is 68 years. This is because the return type of the DATEDIFF function is INT and INT datatypes Min and Max value is: -2,147,483,648 to +2,147,483,647. But with DATEDIFF_BIG function the maximum difference is very high as the return type is bigint and it’s Min and Max value is: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

Example 1: Basic example

SELECT DATEDIFF_BIG(DAY, GETDATE(), GETDATE()+1) 'DateDiff Big'

RESULT:
DATEDIFF_BIG Basic example

ALSO READ: How to get difference between two dates in Years, Months and days in Sql Server

Example 2: Below example demonstrates how DATEDIFF and DATEDIF_BIG functions behave differently when the milliseconds difference between two dates is greater than the INT max (i.e. 2,147,483,647) value.

SELECT DATEDIFF(ms, '2015-12-01', '2015-12-31') 'DateDiff'

RESULT:

Msg 535, Level 16, State 0, Line 1
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

SELECT DATEDIFF_BIG(ms, '2015-12-01', '2015-12-31') 'DateDiff'

RESULT:
DATEDIFF_BIG Sql Example

So, if we know that sometime the difference between two dates is going to cross the INT max value then we have to use the DATEDIFF_BIG function

[ALSO READ]:

Live Query Statistics in Sql Server 2016

Sql Server, Sql Server 2016, , , , , , , , , , , , , , , , ,

Live Query Statistics is one of the new feature introduced in Sql Server 2016, it basically provides the real-time live execution plan of an active running query to the Developer/DBA.

This SSMS feature is very helpful in checking the query execution progress for a long running queries, currently we don’t know for a long running queries where it is stuck or which specific operator is taking long time or how much percentage is completed or approximately how much extra duration required to complete it etc. This feature provides a way to know all these at any given point during the query execution.
It also helps in debugging the queries without needing to wait for the completion of the query execution. As soon as the query execution starts we can see the real-time live execution plan with moving dotted lines between the operators, operator execution progress, overall query execution progress etc.

Like Actual Execution Plan option, this option can be enabled before the query execution starts, it can’t be enabled when the query execution is in progress.

Like Actual execution plan, it will have some additional performance overhead on the overall query execution. So, enable only when it is required to troubleshoot or debug the query issues. Because of this avoid enabling the live query statistics on all the session, even though we have an option to enable it for all the sessions using an extended event query_post_execution_showplan.

[ALSO READ] Compare Execution Plans in Sql Server 2016

Live query statistics feature in Sql Server 2016 provides various real-time query execution statistics like the below ones

  • Overall Query Execution Progress:

    It provides the overall query execution progress
    We can see the moving dotted-lines between the operators where execution is not yet completed. And the dotted-lines turns to solid lines once the operator/complete query execution completes. It is somewhat similar to the one we see in the SSIS data flow.

  • At operator level it provides the real-time insights like the below ones:
    • Operator execution progress
    • Operator execution elapsed time

    • In the operator property window we can see real-time Actual number of rows, Operator Status, Elapsed time and various estimated values when operator execution is in progress like Estimated Number of rows, Estimated CPU Cost, Estimated I/O cost, Estimated Row Size etc. To open the operator property window, right-clicking on any operator and select the Properties option from the context menu.

Note: This article is based on the Sql Server 2016 CTP 3.1. Some of the views mentioned in this article may change in the RTM. Will try my best to keep this article updated

To understand this feature with examples let us create a demo Database with Customer Table as shown in the following image with 200,000 records by the following script.

--Create a demo database
CREATE DATABASE SqlHintsLQSDemo
GO
USE SqlHintsLQSDemo
GO
--Create customer table
CREATE TABLE dbo.Customer( 
    CustomerId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name NVARCHAR(50),  PhoneNumber NVARCHAR(20),
    CreationDate DATETIME, ChangeDate DATETIME)
--Populate customer table with sample 200000 records
INSERT INTO dbo.Customer(Name, CreationDate, ChangeDate)
SELECT TOP 200000 NEWID(), GETDATE(), GETDATE()
FROM SYS.all_columns SC1
        CROSS JOIN SYS.all_columns SC2
GO

To get the live query execution plan, just like Actual Execution plan option we need to select the Include Live Query Statistics option as shown in the below image before executing the query

Include Live Query Statistics

Let us execute the following query and observe it’s live execution plan in the following gif animation

SELECT  TOP 200000 *
FROM dbo.Customer C1 WITH(NOLOCK) 
		INNER JOIN dbo.Customer O with(Nolock)
			ON C1.CustomerId = O.CustomerId

RESULT:
Live Query Statistics Example 1
*Double click on the image to get the enlarged view

Let us update the Customer table statistics by the following statement. This statement is updating the Customer table stats that this table has only one record.

UPDATE STATISTICS dbo.Customer
WITH ROWCOUNT = 1

Now re-execute the previous query and observer it’s live execution plan.

SELECT  TOP 200000 *
FROM dbo.Customer C1 WITH(NOLOCK) 
		INNER JOIN dbo.Customer O with(Nolock)
			ON C1.CustomerId = O.CustomerId
OPTION(RECOMPILE)

RESULT:
Sql Live Query Statistics Example 2

We can observe from this live execution plan that, all operators from the beginning are showing 99% completed, but in the previous example this % done was gradually increased from 0 to 100. So we can assume that some of the stats displayed in the live query statistics indirectly depends on the statistics which sql server maintains for the table, indexes etc

At this moment the live query statistics is not supported for the Natively Compiled Stored Procedure.

[ALSO READ]:

Compare Execution Plans in Sql Server 2016

Sql Server, Sql Server 2016, , , , , , , , , , , ,

Comparing two execution plans is one of the new feature which is getting introduced in Sql Server 2016. This will be one of the good addition to the Sql Server features set. Many a time we come across a scenario where we need to compare the two execution plans. For example some time we want to see what is the difference in the execution plan after making some changes to it. Sometimes we come across a scenario where we observe that some stored procedure is perfectly working fine in development/System test environment but not in the production environment. In such scenario comparing the Dev/QA execution plan with production execution plan gives us the clue on what is going wrong. Without this feature currently we open the two execution plans in separate window and then we manually compare, it is tedious to figure out quickly what is going wrong.

[ALSO READ] Live Execution Plan in Sql Server 2016

Note: This article is based on the Sql Server 2016 CTP 3.0. Some of the views mentioned in this article may change in the RTM. Will try my best to keep this article updated.

Let us understand this Execution Plan comparison feature in Sql Server 2016 with examples. Let us create a Customer table with 500,000 records by the following script

CREATE DATABASE SqlHintsJSONDemo
GO
USE SqlHintsJSONDemo
GO
CREATE TABLE dbo.Customer( 
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name NVARCHAR(50), 	PhoneNumber NVARCHAR(20),
	CreationDate DATETIME, ChangeDate DATETIME)
GO
INSERT INTO dbo.Customer(Name, PhoneNumber, 
                         CreationDate, ChangeDate)
SELECT TOP 500000 NEWID(),
 100000000 - ROW_NUMBER() OVER (ORDER BY SC1.object_id),
 GETDATE(), GETDATE()
FROM SYS.all_columns SC1
        CROSS JOIN SYS.all_columns SC2

Enable the actual execution plan in the SSMS window and execute the below query and save the execution plan as FirstExecutionPlan.sqlplan

SELECT *
FROM dbo.Customer WITH(NOLOCK)
WHERE PhoneNumber = '99750000'

RESULT:
First Execution Plan without Index on PhoneNumber

Now let us add a index on the PhoneNumber column by the following script:

CREATE INDEX IX_Customer_PhoneNumber 
  ON dbo.Customer(PhoneNumber)

Now re-execute the previously executed query to get the Customer record based on the PhoneNumber by selecting the actual the execution plan option in the SSMS window. And save the resultant execution plan as SecondExecutionPlan.sqlplan

SELECT *
FROM dbo.Customer WITH(NOLOCK)
WHERE PhoneNumber = '99750000'

RESULT:
Second Execution Plan with Index on PhoneNumber

Now open the first execution plan (i.e. FirstExecutionPlan.sqlplan) from the Sql Server Management Studio (i.e. SSMS). Then right click anywhere in the opened execution plan window and we can see a new menu option “Compare Showplan” as shown in the below image:

Load the first execution plan to compare

Select the Compare Showplan option and load the SecondExecutionPlan.sqlplan which we have saved previously to compare with the already loaded FirstExecutionPlan.sqlplan. Then the resultant ShowPlan Comparision window will be as shown below:

Execution Plan Compare View

In the ShowPlan Comparision window select Clustered Index Scan node from the First Execution Plan and Index Seek node from the Second Execution Plan and then right click on one of these nodes and go to Properties context menu option. This will open up a side by side comparison properties window as shown in the below image where we can compare the various properties of these two nodes.

Execution Plan Comparision with Property window

In the ShowPlan Comparision window if we want to see the execution plans in side-by-side view instead of top and bottom view, then right click anywhere in the window and click on the Toggle Splitter Orientation option to make it Side-by-Side or Top-And-Bottom view.

Let us execute the below Statement by selecting the actual execution plan option in the SSMS and save the execution plan as ThirdExecutionPlan.sqlplan. The difference between this select statement and the previous select is: here the SELECT statement has the TOP clause in it:

SELECT TOP 1 *
FROM dbo.Customer WITH(NOLOCK)
WHERE PhoneNumber = '99750000'

RESULT:
Third Execution Plan with Index on PhoneNumber

Close all the open windows and then open the SecondExecutionPlan.sqlplan from SSMS. Right click anywhere in the execution plan window and from the context menu select the option “Compare Showplan” and load the ThirdExecutionPlan.sqlplan. It will bring up the following ShowPlan Comparison window as shown in the below image.


Second and Third Execution Plan comparision

From the comparison window it is clear that matching nodes are highlighted in the same color, it means no change between the two nodes of the two different plans. And TOP node is not colored, because it is not matching with any other node.

If you select one of the matching node in one plan it will automatically marks the corresponding matching node in other plan also selected. For example here I have selected the Index Seek node in the first/top plan (i.e. selection is highlighted by blue border), it automatically selects the Index Seek node in the second/bottom plan as shown in the below image.


Second and Third Execution Plan comparision Node Selection
[ALSO READ]: