SQL SERVER 2016 – Comparing Execution Plans

New tools bring new capabilities and sometimes these are ingrained deep into the product that we can easily miss them out. And SQL Server 2016 is no different. After installing the CTP, I was pleasantly surprised to see this addition to help our DBA’s who are doing performance tuning and query tuning activities.

So what am I talking about? Think of a scenario wherein you are doing some sort of testing in your QA environment. You seem to be troubleshooting a production scenario and found that very scenario to be working very well in your test environment. Though you have taken the data from the production database to the test QA server less than a month back, you are still baffled with the question – why this difference?

The next requirement everyone looks at is to start comparing the execution plans between your production and test environment for the same query. Some of the execution plans on a production server can run to pages sometimes and are difficult to find difference visually.

SQL Server 2016 – Plan comparison

If you have the execution plan from your production environment, then go ahead and open it in SQL Server 2016 Management Studio and right click to get the following option:

compare plan 00 SQL SERVER 2016   Comparing Execution Plans

You can see that an interesting addition. The “Compare Showplan” needs to be selected. Now, select the plan that was generated from your test environment. This will make bring both the execution plans on a side-by-side view as shown below. Look at the Purple color on the nodes.

compare plan 01 SQL SERVER 2016   Comparing Execution Plans

This represents the fact that both the nodes are same but have some differences in some properties. Now it is upto us to right click and bring the properties tab.

compare plan 02 SQL SERVER 2016   Comparing Execution Plans

Here we can go ahead and compare each of the node properties to find where the discrimination between the plans are. This is awesome capabilities added in the new version.

I am sure you will find this feature of great use in query tuning exercises in your environments. Having said that, one thing I have seen interesting is, if you take a live “Actual Execution Plan” and then try to compare it with a saved .sqlplan file – it will come up with an error as shown below:compare plan 03 SQL SERVER 2016   Comparing Execution Plans

Do let me know if you would use this feature and if you have used this feature – what is some of the enhancements you wished this comparison operator had? I would like to learn from you for sure.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Installing and Getting Started With Semantic Search

After I wrote about the basics in the blog SQL SERVER – What is Semantics Search Inside SQL Server?, I had multiple requests to understand and get started with Sematic Search. So I thought to set the building blocks of working with the same. Where do we start? Well, here is something for you:

Similar to earlier versions of SQL Server, to use semantic search, you must complete installing “Full-Text” and “Semantic Extractions for Search” on the “Features to Install” page during setup of SQL Server. They are installed together as one feature.

You can run the following command to determine if full-text and semantic search are installed. A return value of 1 indicates that these components are installed; a return value of 0 indicates that they are not installed.


Language Statistical Database

In addition to installing the full-text and semantic search feature, statistical semantic search requires a language statistical database. This installs separately – I will outline the steps below.

This database contains the statistical language models required by semantic search. A single semantic language statistics database contains the language models for all the languages that are supported for semantic indexing.

The language used for the full-text index on a column determines the statistical language model used for semantic indexing. When you create a semantic index on a column, the index creation fails if you specify a language for which the language model is not available.

Note: Fewer languages are supported for semantic indexing than for full-text indexing. As a result, there may be columns that support full-text indexing, but that do not support semantic indexing.

Installing the Language Statistical Database

  • Locate the Windows installer package named SemanticLanguageDatabase.msi on the SQL Server installation media.
  • Run the SemanticLanguageDatabase.msi Windows installer package to extract the database and log file.
  • Move the extracted database file and log file to a suitable location in the file system. If you leave the files in their default location, it will not be possible to extract another copy of the database for another instance of SQL Server.
  • Note: When the semantic language statistics database is extracted, restricted permissions are assigned to the database file and log file in the default location in the file system. As a result, you may not have permission to attach the database if you leave it in the default location. If an error is raised when you try to attach the database, move the files, or check and fix file system permissions as appropriate.
  • Attach the database using the following command:

ON (FILENAME = 'C:\Microsoft Semantic Language Database\semanticsdb.mdf')

Registering the Language Statistical Database

After attaching the Language Statistical Database, you must register it so SQL Server knows to use it as the Language Statistical Database:

EXEC sp_fulltext_semantic_register_language_statistics_db @dbname = N'semanticsdb';

Verify the Language Statistical Database is installed and Registered

The following query returns a single row of information if the semantic language statistics database is installed and registered for the instance.

SELECT DB_NAME(database_id) DBName, * FROM sys.fulltext_semantic_language_statistics_database;

 SQL SERVER   Installing and Getting Started With Semantic Search

In subsequent blogs, I will try to take couple of examples to illustrate how these enabled features can be used in our applications.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Availability Group and the Six Critical Steps for DBAs to Follow – Notes from the Field #104

Mike Lawell SQL SERVER   Availability Group and the Six Critical Steps for DBAs to Follow   Notes from the Field #104

Mike’s, “now what am I supposed to do?”, face.

[Note from Pinal]: In this episode of the Notes from the Field series database expert Mike Lawell explains about Availability Group and the Six Critical Steps for DBAs to Follow.  A few weeks ago, I asked questions in conferance, how many people know about availability group. Only a handful of people raised their hands. I was expecting this as many have heard about AlwaysOn and Availability Groups but not everyone has implemented the same. Most of the time, there are no clear guidelines for the same. In this blog post Mike tells an amazing story about Six Critical Steps every DBA must know about Availability group. He makes this simple concept very easy.  Read the experience of  Mike in her own words.

Your boss has asked you to implement SQL Server AlwaysOn Availability Groups in your environment. Exciting as it may, you’re an accidental DBA with little to no experience implementing any high availability solution, let alone, a fairly new availability group.

The first thing you say is, fantastic! Then, hmmm, fantastic. You realize this is above your head, but it is a great opportunity to learn.

This happened to one of my clients, where the DBA barely had SQL Server Database Mirroring experience. The product had a required up time of 99.999% (yeah, right), that they were having problems maintaining. They wanted to improve their availability and business continuity.

The DBA had a lot of anxiety with implementing AGs in this environment without experiencing considerable downtime.

Fortunately they were able to engage our services to lead them through the process. We migrated from mirroring to AGs with approximately a minute of downtime into a 4 server multi-subnet environment to a new set of servers.

1.      Research

There are many environments where AGs will simply not work (or have a hidden disaster waiting). For instance, you should not use availability groups in SQL Server 2012 or 2014, if you’re doing cross database transactions or distributed transactions. If you have a failover during a distributed transaction, it is possible that your database will enter a suspect mode (btw, this changes in SQL Server 2016).

How many servers and replicas will you need? Which databases do you want in which AG? What might the licensing costs be? What should/can your quorum mode be? Do your applications even support AGs?

2.      Plan

Develop the high level plan to the best of your knowledge. Don’t worry about being wrong, you will miss something. Take the time to think of every application or service, etc. that will connect to the AG. Document all of the jobs, alerts, mail configuration, operators, everything you can think of (find a script to do it).

3.      Test the AG Build

Now that you have some idea of what is possible and you think you know what needs to be done, build an AG as a proof of concept in an isolated environment to test your theory of how it should work. Make sure you use the same drive letters as production. You will learn that you will miss important configurations, steps, etc. Tear the AG down then rebuild it, until you are familiar with the steps, write the steps you take down (every step). Do this until you’re confident you have all of the steps assembled and you haven’t missed anything. You will miss something, because most database environments are complex, and many DBAs don’t know all of the services connecting to their databases.

4.      Script the Implementation

104 doc SQL SERVER   Availability Group and the Six Critical Steps for DBAs to Follow   Notes from the Field #104This part really helps in minimizing downtime. You don’t want to be clicking through SSMS trying to get all of the configurations correct in production and risk additional downtime at failover or worse.

I built the AGs ahead of time in the production environment and added a test database so I could test the failover between machines before go live.

The scripts were PowerShell scripts pulling the configuration from a csv file. I learned how to script PowerShell from the internet. Make sure your configuration file is tested. I create a spreadsheet with the important data (created from a script run against production) about database name, data file location, and more. Don’t type the configurations if you can avoid it. This is where you get into trouble especially in complex environments. I have some examples of the PowerShell AG and SQLCMD Log Shipping scripts I used on my blog at SQLServerAssociates.com

5.      Test the Scripts

This is the most important step as even the best plan must be tested. It must work repeatedly in your development environment without errors. Don’t compromise with this step. If you want to avoid extended downtime because you received an error that you’ll have to research to fix, test your scripts.

6.      Know your Environment

You must be able to talk confidently about AGs and defend your configuration to management. You will need to document your environment for management, operations, and other DBAs (including yourself). Use Visio diagrams for visuals.


Migrating your environment to AGs can be a great learning experience, but don’t be afraid to engage a consultant to help you do it right. The justification is usually very easy when talking to management when you equate downtime to revenue lost.

If you want to get started with SQL Server with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – What Resource Wait Are We Seeing?

I have been a big supporter of the Activity Monitor capability inside SQL Server Management Studio. And I have written a number of articles before in this blog. Please take a moment to look at:

SQL SERVER – Activity Monitor to Identify Blocking – Find Expensive Queries

One of the readers wrote to me on this blog and asked me something simple and I was surprised I had not touched on it before. His mail was:

Hi Pinal,

I have seen your blog around Activity Monitor and as a budding DBA, I am still learning the tricks of the trade. I use many scripts from this site and they have helped me a number of times in my career.

In Activity monitor, I notice a large number of PAGELATCH waits. If I look at processes, I sometimes see a large number of processes waiting for PAGELATCH_SH and PAGELATCH_UP on resources 2:1:3 and sometimes on 2:1:1. This shows contention is placed on SGAM and PFS pages.

Do you have a script written somewhere to find what type of disk resource is under contention? After reading a number of web search I can see this is tempdb contention because of database id of 2.

I am sure you can help me somehow.


Blog reader

I personally didn’t see much of a problem in his request. And I was not aware if this script existed in this blog. So I went ahead to write the same. This is a query based on the sys.dm_exec_requests, sys.dm_exec_sessions and sys.dm_exec_qury_plan.

SELECT es.session_id, DB_NAME(er.database_id) AS [database_name],
OBJECT_NAME(qp.objectid, qp.dbid) AS [object_name], -- NULL if Ad-Hoc or Prepared statements
WHEN pageid = 1 OR pageid % 8088 = 0 THEN 'Is_PFS_Page'
WHEN pageid = 2 OR pageid % 511232 = 0 THEN 'Is_GAM_Page'
WHEN pageid = 3 OR (pageid - 1) % 511232 = 0 THEN 'Is_SGAM_Page'
ELSE 'Is Not PFS, GAM or SGAM page' END
(SELECT CASE WHEN er.[wait_type] LIKE 'PAGE%LATCH%' AND er.[wait_resource] LIKE '%:%'
THEN CAST(RIGHT(er.[wait_resource], LEN(er.[wait_resource]) - CHARINDEX(':', er.[wait_resource], LEN(er.[wait_resource])-CHARINDEX(':', REVERSE(er.[wait_resource])))) AS INT)
ELSE NULL END AS pageid) AS latch_pageid
) AS wait_resource_type,
er.wait_time AS wait_time_ms,
SELECT qt.TEXT AS [text()]
FROM sys.dm_exec_sql_text(er.sql_handle) AS qt
FOR XML PATH(''), TYPE) AS [running_batch],
CASE WHEN er.statement_start_offset = 0 THEN 0 ELSE er.statement_start_offset/2 END),
CASE WHEN er.statement_end_offset = -1 THEN DATALENGTH(qt2.TEXT) ELSE er.statement_end_offset/2 END - (CASE WHEN er.statement_start_offset = 0 THEN 0 ELSE er.statement_start_offset/2 END))) AS [text()]
FROM sys.dm_exec_sql_text(er.sql_handle) AS qt2
FOR XML PATH(''), TYPE) AS [running_statement],
FROM sys.dm_exec_requests er
LEFT OUTER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id
CROSS APPLY sys.dm_exec_query_plan (er.plan_handle) qp
WHERE er.session_id <> @@SPID AND es.is_user_process = 1
ORDER BY er.total_elapsed_time DESC, er.logical_reads DESC, [database_name], session_id

The output will help you in analyzing the typical wait resource type and if you are having tempdb contention this should show up here. For a matter of fact, if you have other contentions on disk, that would show up too.

Do let me know if this script was helpful and I will be more than happy to extend the same. Or if you extended the script, please make sure to share the same via comments.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – How to Create Linked Server to PostgreSQL?

The databases world is expanding and I have been fortunate enough to learn and share my experiences around databases like MySQL, PostgreSQL apart from working with SQL Server. I always try to expand my horizon and try various database products. In case you don’t know, I have course on Pluralsight about PostgreSQL


It is always interesting to integrate various products and make them work seamlessly. Due to my knowledge of both database products, I have been asked one question very frequently.

How can I create linked servers in SQL connecting with Postgres?

Though this question looks simple and easy – I thought of writing a note to show you the actual steps to achieve the same. This blog shows simple example about creating linked server. There is a provider called PGOLEDB which can be used for this purpose.

Create a Database and Table in PostgreSQL

  1. Create database:
  1. Once database is created, change the connection, create table and insert some data.
ID integer NOT NULL, 
Name varchar(128) NOT NULL
insert into MyTable values (1, 'Pinal Dave');
  1. Verify that we have data in table
    Select * from MyTable

    PG Linked 01 SQL SERVER   How to Create Linked Server to PostgreSQL?

    Create a Linked Server in SQL Server

    1. Go to http://www.pgoledb.com and choose “Download” from menu bar.
      PG Linked 02 SQL SERVER   How to Create Linked Server to PostgreSQL?
    2. Go to the page and choose “PGNP OLEDB Providers for Postgres, Greenplum and Redshift” as shown below.
      PG Linked 03 SQL SERVER   How to Create Linked Server to PostgreSQL?
    3. Install it and then we should see provider it in SSMS
      PG Linked 04 SQL SERVER   How to Create Linked Server to PostgreSQL?
    1. Modify and run below script in SQL Server. You need to change Server Name, port etc.
      -- Change parameter for provider Allow In Procees = true / DynamicParameters = true
      EXEC MASTER.dbo.sp_MSset_oledb_prop N'PGNP'  ,N'AllowInProcess',1
      EXEC MASTER.dbo.sp_MSset_oledb_prop N'PGNP'  ,N'DynamicParameters',1
      DECLARE @name NVARCHAR(4000);
      DECLARE @provider NVARCHAR(4000);
      DECLARE @servername NVARCHAR(4000);
      DECLARE @port NVARCHAR(4000);
      DECLARE @db_name NVARCHAR(4000)
      -- destination postgres database
      SET @name = N'SQLAuth_PG';
      SET @provider = N'PGNP';
      SET @servername = N'localhost';
      SET @port = 'PORT=5432;'
      SET @db_name = N'sqlauthority';
      -- create linked server
      EXEC MASTER.dbo.sp_addlinkedserver @server = @name
      ,@srvproduct = N'PGNP'
      ,@provider = N'PGNP'
      ,@datasrc = @servername
      ,@provstr = @port
      ,@catalog = @db_name
      -- username and password for postgres
      EXEC MASTER.dbo.sp_addlinkedsrvlogin @rmtsrvname = @name
      ,@useself = N'False'
      ,@locallogin = NULL
      @rmtuser = N'postgres'
      ,@rmtpassword = 'sa'
      -- set up Extended properties of the Linked Server
      EXEC MASTER.dbo.sp_serveroption @server = @name
      ,@optname = 'data access'
      ,@optvalue = 'true'
      EXEC MASTER.dbo.sp_serveroption @server = @name
      ,@optname = 'use remote collation'
      ,@optvalue = 'true'
      EXEC MASTER.dbo.sp_serveroption @server = @name
      ,@optname = 'rpc'
      ,@optvalue = 'true'
      EXEC MASTER.dbo.sp_serveroption @server = @name
      ,@optname = 'rpc out'
      ,@optvalue = 'true'
    2. Once completed, we should be able to see linked server created as shown below.
      PG Linked 05 SQL SERVER   How to Create Linked Server to PostgreSQL?

    Test the Linked Server

    Here are the test queries which I have used based on earlier script.

    SELECT [id],[name]
    FROM   [SQLAuth_PG].[sqlauthority].[public].[mytable]
    SELECT *
    FROM OPENQUERY(SQLAuth_PG, 'select id, name from mytable');

    PG Linked 06 SQL SERVER   How to Create Linked Server to PostgreSQL?

    Have you ever created any other 3rd party linked server in your environments? What methods have you used? Will you be kind enough to share the same via comments?

    Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – New features in SQL Server 2016 Setup Wizard

In this world of change, the SQL Server product is also changing. One thing which is very clear is that Microsoft is listening to feedbacks from customers and enhancing the setup experience also. Some of the classic feedback implementation can be seen in the setup of the next version of SQL Server 2016. In this blog post, I will try to bring out some of the enhancements that are being incorporated in the Setup UI. Some are critical and worth making a note.

Here are the few things which I noticed in SQL Server user interface.

SQL Setup allows user to add Database Engine Service SID into “Perform Volume Maintenance Task” privilege under security policy. This helps in instant file initialization

sql2016 setup 01 SQL SERVER   New features in SQL Server 2016 Setup Wizard

Here is the new parameter added in command line for SQL Setup.


This may lead to information disclosure as it could allow deleted content to be accessed by an unauthorized principal.

TempDB Configuration

In SQL Server 2016, we have the option to specify TempDB file specifications during setup itself. Most of the companies have “post deployment script” and one of the steps is to optimize TempDB files. It’s a tab called “TempDB” under “Database Engine Configuration”

sql2016 setup 02 SQL SERVER   New features in SQL Server 2016 Setup Wizard

Since UI has it, there is also a parameter in command line as below:

Parameter Meaning
SQLTEMPDBFILECOUNT The number of Database Engine TempDB files
SQLTEMPDBFILESIZE Initial size of a Database Engine TempDB data file in MB.
SQLTEMPDBFILEGROWTH automatic growth increment of each Database Engine TempDB data file in MB
SQLTEMPDBLOGFILESIZE initial size of the Database Engine TempDB log file in MB.
SQLTEMPDBLOGFILEGROWTH automatic growth increment of the Database Engine TempDB log file in MB.
SQLTEMPDBDIR Directories for Database Engine TempDB files.
SQLTEMPDBLOGDIR Directory for the Database Engine TempDB log files

Hopefully, we would see more feedback embedded in the product by default.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Interview Question of the Week #046 – How @@DATEFIRST and SET DATEFIRST Are Related?

Question: How @@DATEFIRST and SET DATEFIRST are related?

Answer: The master database’s syslanguages table has a DateFirst column that defines the first day of the week for a particular language. SQL Server with US English as default language, SQL Server sets DATEFIRST to 7 (Sunday) by default. We can reset any day as first day of the week using


This will set Friday as the first day of the week.
@@DATEFIRST returns the current value, for the session, of SET DATEFIRST.

----This will return result as 1(Monday)
SET LANGUAGE us_english
----This will return result as 7(Sunday)

In this way @@DATEFIRST and SET DATEFIRST are related. When I learned about this feature I was very glad as our company has started to server global clients and simple feature like this helps a lot to avoid confusion.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Inserting into ColumnSet with SPARSE Columns

There are a number of blogs around using SPARSE columns here. Here are few of them for reference and can be used as a great learning experience.

SQL SERVER – 2008 – Introduction to SPARSE Columns

SQL SERVER – 2008 – Introduction to SPARSE Columns – Part 2

SQL SERVER – Errors and Limitations of Working with SPARSE Columns in SQL Server

Though such capabilities exist inside the product, I hardly see people using this feature effectively. Recently, one friend from a product based company called me to check if I had written on this topic. I explained that there are tons of documentation and it is great to see them use it. He was saying, they had a table which had close to 1000’s of column as they are using it like a generic table and wanted to know if there is an easy way to update or insert into this SPARSE table directly from ColumnSet?

The short answer is, YES. It is quite possible to insert using the ColumnSet column directly. I sent him an example and thought it would be of great use for folks here too.

Let us create our table for the experiment:

USE tempdb;
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DocStore_With_ColumnSet')
-- note the use of the SPARSE keyword and the XML column_set
CREATE TABLE DocStore_With_ColumnSet
ProductionSpecification VARCHAR(20) SPARSE NULL,
ProductionLocation smallint SPARSE NULL,
MarketingSurveyGroup VARCHAR(20) SPARSE NULL,
MarketingProgramID INT SPARSE NULL,

We can insert into different values columns as shown below:

INSERT DocStore_With_ColumnSet (DocID, Title, ProductionSpecification, ProductionLocation)
VALUES (1, 'Tire Specs 1', 'ABC321', 27)
INSERT DocStore_With_ColumnSet (DocID, Title, MarketingSurveyGroup)
VALUES (2, 'Survey 1234', 'Female 25 - 35')
-- Note that the Special_XML_Column column is correctly populated based on the columns that are populated
SELECT * FROM DocStore_With_ColumnSet

Can we add to the ColumnSet directly? Here is what we can do:

-- you can add data directly by specifying the xml
INSERT DocStore_With_ColumnSet (DocID, Title, Special_XML_Column)
VALUES (3, 'Specification 2', '<ProductionSpecification>AXZ7R242</ProductionSpecification><ProductionLocation>18</ProductionLocation>')

We can see the success of the insert statement. Let us next try to check if the values are inserted into our ColumnSet:

SELECT * FROM DocStore_With_ColumnSet

If this was not enough, we can also use this technique to update the columnset values too. A typical example for this is shown below:

-- now let us update some of the data
UPDATE DocStore_With_ColumnSet
SET Special_XML_Column = '<ProductionSpecification>ZZ456Z</ProductionSpecification><ProductionLocation>18</ProductionLocation>'
WHERE DocID = 3;

You can see again by querying that the values have been inserted successfully. This was a simple example to how we can use columnsets to insert data into a sparse table rather can doing a qualification of each columns. Let us next try to clean this table:

-- Cleanup
DROP TABLE DocStore_With_ColumnSet

As I conclude, would like to know how many of you have used SPARSE columns in your application and what were some of the scenarios you were able to lighten up using this feature?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – InMemory Table Creation Error Message: Msg 41337

I had recently gone to my hometown to visit my parents as it was holidays for my daughter because of festival. These are relaxing times and hardly try to get in front of my computer during these 2-3 days of family time. But who said that happens always. In this recent trip, I had one of my cousins visit our place who was incidentally studying his engineering degree. We suddenly got into a conversation and I started talking about InMemory databases and how they can be made resilient. Though this topic becomes a great conversation starter to college students, he was baffled with the fact that we can have InMemory databases that are ACID compliant.

He got to his home and said he would play around with this concept because he had a SQL Server 2016 evaluation version. I said there are many more enhancements to talk but wanted him to do his bit of search and homework before I taught him some really cool tricks. That night he sent me a mail stating he was getting some error while creating an InMemory table and was not sure what to do. I asked him to send me the error to take a first look:

Msg 41337, Level 16, State 100, Line 2
Cannot create memory optimized tables. To create memory optimized tables, the database must have a MEMORY_OPTIMIZED_FILEGROUP that is online and has at least one container.

It was clear from the message the source of the problem. He had created a normal database without the InMemory filegroup.

I sent him the script to get rid of the error message and is shown below:

-- Create the Database
-- Note the filegroup of type MEMORY_OPTIMIZED_DATA
( NAME = N'InMem_OLTP_data', FILENAME = N'C:\DATA\InMem_OLTP_data.mdf',size = 50MB)
( NAME = N'InMem_OLTP_log', FILENAME = N'C:\DATA\InMem_OLTP_log.ldf',size = 10MB)
--- Step 2 - Can we create this table?
CREATE TABLE [Customer](

As you can see we will get the exact error as mentioned earlier.

Msg 41337, Level 16, State 100, Line 2
Cannot create memory optimized tables. To create memory optimized tables, the database must have a MEMORY_OPTIMIZED_FILEGROUP that is online and has at least one container.

As mentioned, let us add the InMemory Filegroup so that we can create the tables without a glitch:

-- Step 3 -- Add a Filegroup that is MEMORY_OPTIMIZED.
ADD FILE (NAME = InMem_OLTP_InMemory, FILENAME = N'C:\Data\InMem_OLTP_mopt')

Now if we create the table as mentioned in Step 2 above, there will not be any error. I don’t blame my cousin for this because he was a novice and was not able to understand what a container is when it comes to InMemory tables. Though this was a simple error that most of you would easily be able to solve, it can surely be a great source troubleshooting for people like my cousin.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – T-SQL Window Function Framing and Performance – Notes from the Field #103

Kathi SQL SERVER   T SQL Window Function Framing and Performance   Notes from the Field #103[Note from Pinal]: In this episode of the Notes from the Field series database expert Kathi Kellenberger explains about T-SQL Over clause. Kathi is an amazing instructor, she was the SQL author I have read in my early career. The reason, I love SQL Server because her writing has instigated love for this technology in me. Today she brings a relatively interesting topic for database experts. Read the experience of  Kathi in her own words.

In my last post to SQL Authority, I explained how to add frames to T-SQL window functions where it is supported. I broke down the syntax and showed where things can go wrong if you rely on the default frame instead of explicitly specifying the frame. Be sure to read that article if you are new to frames or just need a refresher.

When creating a running total, for example, It’s very easy to just leave out the frame, and, in many cases, the results will be what you want. There is another downside to using the default frame, however, and that is a pretty big performance penalty. The default frame, when one is not specified, is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. If the ORDER BY expression in the OVER clause produces unique values, you will get a valid running total.

In order to perform the window function calculation, such as a running total, SQL Server creates a worktable and populates it with each partition. When RANGE is used, that worktable is always created in tempdb. When ROWS is used instead by specifying the frame, the worktable is created in memory most of the time. The worktable created in memory has no I/O, locking, or contention issues, so it performs much better. Unfortunately, when you look at the graphical execution plan, you won’t see much of a difference between ROWS and RANGE. You can see the difference, however, if you look at STATISTICS IO.

To see the difference for yourself, run this script against the AdventureWorks database. Be sure to enable the Actual Execution Plan before running it as well.

PRINT 'Default frame';
SELECT CustomerID, SalesOrderID, TotalDue,
SUM(TotalDue) OVER(PARTITION BY CustomerID ORDER BY SalesOrderID) AS RunningTotal
FROM Sales.SalesOrderHeader;
PRINT 'ROWS frame';
SELECT CustomerID, SalesOrderID, TotalDue,
) AS RunningTotal
FROM Sales.SalesOrderHeader;

First, take a look at the execution plans as shown in the Figure 1. The two queries did not produce identical plans, but the relative cost for each is 50%. So, at least the execution plans report that the queries perform the same.

103 1 SQL SERVER   T SQL Window Function Framing and Performance   Notes from the Field #103

Figure 1: The graphical execution plans

Now take a look at the messages tab. Here you will see a big difference as shown in Figure 2. The query using the default frame reported a very high number of logical reads from a worktable. The second query, which uses ROWS, reports 0 reads for the worktable. If you turn on Statistics Time, you will also see that the second query runs faster.

103 2 SQL SERVER   T SQL Window Function Framing and Performance   Notes from the Field #103

Figure 2: The statistics IO

It is often difficult to show performance differences with AdventureWorks tables. They are just too small. Adam Machanic, a SQL Server MVP, published a script that creates a couple of large tables based on AdventureWorks data. One of those tables, bigTransactionHistory, contains 30 million rows. I used bigTransactionHistory to do some performance testing to see if there really was a time difference that could be measured between ROWS and RANGE. For the test, I created a couple of smaller tables, subsets of Adam’s table, which contained 15 and 7.5 million rows respectively. I turned off the grid results so that only the server time would be measured. In addition to comparing ROWS and RANGE, I also tested two traditional methods: using a self-join subquery and a cursor. You can see the results of my tests in Figure 3.

103 3 SQL SERVER   T SQL Window Function Framing and Performance   Notes from the Field #103

Figure 3: The results of testing running totals

Using an accumulating window aggregate function (WF) outperformed the traditional techniques even with the default frame. You can see, however, that specifying a Rows frame was incredibly fast.

Whenever frames are supported (FIRST_VALUE, LAST_VALUE and accumulating window aggregates), make sure that you always specify the frame. Not only can you avoid some logic problems caused by RANGE frames, you will almost always see a performance boost by using ROWS. Remember: Use ROWS. Use ROWS. And use ROWS.

notes 82 3 SQL SERVER   T SQL Window Function Framing and Performance   Notes from the Field #103If you would like to learn more about T-SQL window functions, be sure to check out my latest book Expert T-SQL Window Functions in SQL Server or my Pluralsight course T-SQL Window Functions.

If you want to get started with SQL Server with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)