Archive for the ‘SQL Server 2014’ Category

SQL DBA – Quickly Clone a Database in SQL Server (2016 SP1 & 2014 SP2) with DBCC CLONEDATABASE command

December 22, 2016 1 comment

Have you ever been in a similar situation where your PROD database size is in GBs or TBs, and for a code-release validation or some performance-fix you need to restore it on an Dev or Test server? You know that taking backup & restore will take lot of time, but you have no other option.

We also face this many a times as our PROD database size ranges from 500 GB to 1-2 TB, and we end up waiting 4-5 hrs in this activity.

There are even third party tools, but they also take good time and have their own pros & cons.

Now SQL Server brings a new feature with SQL Server 2016 SP1 & 2014 SP2, i.e. DBCC CLONEDATABASE, to create a new database clone of an existing database within seconds. The new cloned database created is ReadOnly, with no data, but with Statistics.

With DBCC CLONEDATABASE feature you can generate a clone of a database in order to investigate a performance issue related to a Query or Workload.

Note: As best practice suggested by Microsoft product team this clone Database is not supposed to remain in PROD database, but can be moved to a Dev or Test box for further troubleshooting and diagnostic purposes.

–> Syntax:

DBCC CLONEDATABASE (source_db_name, target_clone_db_name)


–> The above statement creates Clone of the source database in following operations:

1. Validate the source database.

2. Get S lock for the source database.

3. Create snapshot of the source database.

4. Create an empty database by inheriting from “model” database, and using the same file layout as the source but with default file sizes as the “model” database.

5. Get X lock for the clone database.

6. Copies the system metadata from the source to the destination database.

7. Copies all schema for all objects from the source to the destination database.

8. Copies statistics for all indexes from the source to the destination database.

9. Release all DB locks.

–> Now let’s create a Clone Database on AdvantureWorks database:

-- With Stats

DBCC CLONEDATABASE ('AdventureWorks2014', 'AdventureWorks2014_CloneDB')


Database cloning for ‘AdventureWorks2014’ has started with target as ‘AdventureWorks2014_CloneDB’.

Database cloning for ‘AdventureWorks2014’ has finished. Cloned database is ‘AdventureWorks2014_CloneDB’.

Database ‘AdventureWorks2014_CloneDB’ is a cloned database. A cloned database should be used for diagnostic purposes only and is not supported for use in a production environment.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

–> I’ll create one more Clone Database without Stats:

-- Without Stats

DBCC CLONEDATABASE ('AdventureWorks2014', 'AdventureWorks2014_CloneDB_No_Stats')


–> Let’s check the file size of all 3 Databases:

– The 1st image shows the size of original AdventureWorks2014 database i.e. 207 MB

– And 2nd and 3rd shows the size of other two Clone Databases i.e. just 16 MB.





–> Now we will check the Execution Plans of a query on all these three databases:

-- Check Execution plan on below T-SQL query in all 3 databases:

	P.BusinessEntityID, P.Title, P.FirstName, P.MiddleName, P.LastName, 
	E.BirthDate, E.Gender, E.HireDate, E.JobTitle, E.MaritalStatus, 
FROM [Person].[Person] P
INNER JOIN [HumanResources].[Employee] E ON E.BusinessEntityID = P.BusinessEntityID
	SELECT TOP 1 DepartmentID
	FROM [HumanResources].[EmployeeDepartmentHistory] DH 
	WHERE DH.BusinessEntityID = E.BusinessEntityID 
INNER JOIN [HumanResources].[Department] D ON D.DepartmentID = EDH.DepartmentID


– On executing the above query on original AdventureWorks2014 database & AdventureWorks2014_CloneDB it shows same execution plan, like it shows:

1. Hash Match operator
2. 72% cost on Person PK
3. 18% cost on EmployeeDepartmentHistory PK

(click on the image to expand)

– But on executing the same query on AdventureWorks2014_CloneDB_No_Stats it shows different execution plan, here it shows:

1. Nested Loop operator
2. 92% cost on Person PK
3. 5% cost on EmployeeDepartmentHistory PK

(click on the image to expand)


Good news – Microsoft makes SQL Server “Developer Edition” free for developers !

April 1, 2016 3 comments

Microsoft on 31st March 2016 announced the free availability of Developer Edition of SQL Server, currently 2014. The Developer Edition is meant for development and testing only, and not for production environments or for use with production data.

SQL Server 2014 Dev Ed free

With SQL Server 2014 Developer edition developers can build any kind of application on top of SQL Server. It includes all the functionality of Enterprise edition, but is licensed for use as a development and test system, not as a production server.

So, with this edition you are getting the Database Engine as well as DW/BI capabilities ( i.e. SSIS /AS /RS) for free 🙂


[Download the SQL Server 2014 Developer Edition free here]

This is a very good news for Developers, as till now the SQL Server Express edition used to be the free database for entry-level development, which has lot of limitations like:

1. Only Database Engine, no DW/BI suit (absence of SSIS/RS/AS).

2. Max size of a DataBase is set to 10GB (but you can create multiple databases)

3. No SQL Agent

4. Single CPU utilization

5. Max 1 GB RAM allocation

6. Max 16 number of instances per server

Hence this is a very good deal that you can now get full featured suit of SQL Server with Database Engine as well as with all DW/BI capabilities for free to play, develop and learn with.

Exam 70-461, SQL 2014 update – Querying Microsoft SQL Server 2014

December 1, 2015 7 comments

In one of my previous post I talked about [Exam 70-461 for SQL Server 2012], and I really got good and big response, with ~250 comments. As SQL Server 2014 got released most of the folks asked me about the certification update.

So here in this post I’ll talk about the same exam 70-461 which is now updated for SQL Server 2014, and Microsoft has not changed the exam code for this version of SQL Server.

This updated certification exam is still based on SQL Server 2012 with new features of SQL Server 2014. Previously with SQL 2012 the exam was divided into 4 modules, please check my previous post, link above. Here with SQL 2014 update the exam is reshaped into 20 modules listed below:

1. Introduction to Microsoft SQL Server 2014
– SQL Server architecture | MSDN
– Editions | MSDN
– SQL Server Management Studio | MSDN

2. Introduction to T-SQL Querying
– T-SQL intro | MSDN
– Set based queries
– Predicate Logic | MSDN
– SELECT statement Logical Ordering | MSDN

3. Writing SELECT Queries
– Simple SELECT queries | MSDN
– Find & eliminate duplicates, and DISTINCT keyword
– Working with Columns
– CASE expressions | MSDN

4. Querying Multiple Tables

5. Sorting and Filtering Data
– Sorting data with ORDER BY clause | MSDN
– Filtering data with WHERE clause MSDN
– Filtering data with TOP keyword | MSDN
– Using TOP-OFFSET clause | MSDN | TechNet
– Working with NULL/unknown values

6. Working with SQL Server 2014 Data Types
– New SQL 2014 datatypes (Character, Date, Time) | MSDN
– Write Queries using different Datatypes | MSDN

7. Using DML to Modify Data
– Inserting Data | MSDN
– Modifying Data | MSDN
– Deleting Data | MSDN

8. Using Built-In Functions
– SQL 2014 new functions (Conversion, Logical)

9. Grouping and Aggregating Data
– Using Aggregate functions | MSDN
– Using GROUP BY clause | MSDN
– Using HAVING clause | MSDN

10. Using Sub-Queries
– Self-Contained Subqueries | TechNet
– Correlated Subqueries | TechNet
– Using EXISTS() predicate with Subqueries | TechNet

11. Using Table Expressions
– Using Views | MSDN
– Using Inline Table Values functions | MSDN | TechNet
– Using Derived Tables
– Using CTEs (Common Table Expressions) | MSDN | TechNet

12. Using Set Operators
– The UNION, UNION ALL Operator | MSDN
– Using APPLY (CROSS & OUTER) | TechNet

13. Using Window Ranking, Offset, and Aggregate Functions
– Window functions with OVER() clause | MSDN
– More Window functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE) | MSDN

14. Pivoting and Grouping Sets
– Writing queries with PIVOT and UNPIVOT | MSDN | TechNet
– Using Grouping Sets | MSDN | TechNet

15. Querying data with Stored Procedures
– Creating Stored Procedures (SP) | MSDN
– Using Parameters with SPs | MSDN
– Working with Dynamic SQL | MSDN
   – sp_ExecuteSQL | MSDN

16. Programming with T-SQL
– Working with Variables, Batches | MSDN | TechNet
– Using IF conditions | MSDN
– WHILE loops | MSDN

17. Implementing Error Handling
– Using TRY/CATCH block | MSDN
– Working with Error Handling | MSDN
– Returning error information
– Raising user-defined errors and passing system errors | MSDN

18. Implementing Transactions
– Using Transactions | MSDN
– Use of “SET XACT_ABORT” with Transactions | MSDN | MSDN-2
– Effects of Isolation Levels on Transactions | MSDN

19. Improving Query Performance
– Factors affecting Query Performance | MSDN
– Checking Execution plan
– Optimize SQL queries, Query tuning | MSDN
– Optimize SQL Indexes, Index tuning | MSDN

20. Querying SQL Server Metadata
– Querying System Catalog Views | MSDN
– Querying System Catalog Functions | MSDN
– Using System Stored Procedures | MSDN
– Using Dynamic Management Objects (DMVs, DMFs) | MSDN

–> Check SQL Server 2014 videos here:

I’ll be updating the above post going forward and as I post something related to SQL Server 2012 & 2014.

Please provide your comments if you want me to talk about any of the above items, thanks !

Memory Optimized “Table Variables” in SQL Server 2014 and 2016

July 31, 2015 Leave a comment

SQL Server 2014 provided you these new features to create [Memory Optimized Tables] and [Native Compiled Stored Procedures] for efficient and quick processing of data and queries which happens all in memory.

It also provided you one more feature to create Memory Optimized Table Variables, in addition to normal Disk Based Table Variables.

This new feature would provide you more efficiency in Storing, Retrieving and Querying temporary data from and in memory.

Normal Table Variables are created in tempdb and utilize it for their entire life. Now with these new Memory Optimized Table Variables they will become free from tempdb usage, relieve tempdb contention and reside in memory only till the scope i.e. batch of a SQL script or a Stored Procedure.

Let’s see how to use these and what performance gain you get out of these tables.

–> Enable Database for supporting Memory Optimized tables: To use this feature your Database should be associated with a FileGroup. So, let’s alter the database.

USE [TestManDB]

-- Add the Database to a new FileGroup


	NAME = TestManDBFG_file1,
    FILENAME = N'E:\MSSQL\DATA\TestManDBFG_file1' -- Put correct path here

Otherwise, while creating Memory Optimized objects you will get below error:

Msg 41337, Level 16, State 100, Line 1
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.


You cannot create a Memory Optimized Table Variable directly with DECLARE @TableVarName AS TABLE (…) statement. First you will need to create a Table Type, then based upon this you can create Tables Variables.

–> Create a Table TYPE [Person_in_mem]

CREATE TYPE dbo.Person_in_mem AS TABLE(
	 BusinessEntityID INT NOT NULL

	INDEX [IX_BusinessEntityID] HASH (BusinessEntityID) 

The Memory Optimized Table Type should have an Index, otherwise you will see an error as mentioned below:

Msg 41327, Level 16, State 7, Line 27
The memory optimized table ‘Person_in_mem’ must have at least one index or a primary key.
Msg 1750, Level 16, State 0, Line 27
Could not create constraint or index. See previous errors.


Ok, now as we’ve created this table type, now we can create as many Table Variables based upon this.

–> Now, create a Table variable @PersonInMem of type [Person_in_mem] that is created above:

DECLARE @PersonInMem AS Person_in_mem

-- insert some rows into this In-Memory Table Variable
FROM [AdventureWorks2014].[Person].[Person]

SELECT * FROM @PersonInMem

Here we successfully created a Table Variable, inserted records into it and retrieved same by the SELECT statement, and this all happened in memory.

Now how can we see we that how much benefits we got from this? What we can do is, we can create a separate Disk-Based Table Variable and do similar operation on it and compare the results by checking the Execution Plan.

–> Comparing performance of both In-Memory vs Disk-Based Table-Variables

– Enable the Actual Execution Plan and run below script to Create and Populate both:

1. In-Memory Table Variable

2, Disk-Based Table Variable

-- 1. In-Memory Table Variable
DECLARE @PersonInMem AS Person_in_mem

FROM [AdventureWorks2014].[Person].[Person]

select * from @PersonInMem

-- 2. Disk-Based Table Variable
	 BusinessEntityID INT NOT NULL

FROM [AdventureWorks2014].[Person].[Person]

select * from @Person


–> Now, check the Actual Execution Plan results below:

1. Check the Cost of INSERT operation with both the tables:

– It took only 8% cost to insert into In-memory Table Variable.

– But it took 89% cost to insert into a Disk-Based Table Variable.

> If You see the individual Operators in both the plans you will see that :
For @PersonInMem Table Variable the cost of INSERT was just 19% compared to the cost of INSERT for @Person Table Variable that was 92%.

SQL Server 2016 - Memory Optimized Table Variables 01

2. Check the Cost to SELECT/Retrieve rows both the tables:

– It took only 0% cost to retrieve rows from the In-memory Table Variable

– And it took 3% cost to retrieve rows from a Disk-Based Table Variable

SQL Server 2016 - Memory Optimized Table Variables 02

This proves that the INSERT and SELECT operations with Memory Optimized table are way more faster that normal Disk-Based tables.

Thus, using Memory Optimized Table Variables will provide you better performance for storing temporary data within memory and process with in Stored Procedure or your T-SQL Scripts.

Update: Know more about In-Memory tables:


Check & Like my FB Page

Getting started with SQL Server 2014 | Download and Install Free (Express) or Full version

May 19, 2015 9 comments

How to download SQL Server?

What are the available options and versions?

Isn’t there any Free version to play with, learn and practice SQL?

I get emails regarding these question many a times from my readers, sometime from my friends and old colleagues too. So I thought to collate all this information into this single post so that people won’t have to search here and there on internet and finally end up nowhere or to any junk sites.

Currently the latest version of SQL Server from Microsoft is SQL Server 2014 released last year on 1st April 2014, and its first Service Pack (SP1) was released just few days back (15th May 2015). You have following options to choose from:

1. SQL Server Full version contains the Database Engine with whole BI suit and is a paid version and you have to buy license key from your MSDN Subscriptions. It is also available for free download for evaluation of 180 days, but post that you have to buy a license key.

2. SQL Server Express is a Free version that can be used by anyone, like students, small setups and companies, etc. It is a lite version which contains the Database Engine with some limitations like: max size of a DataBase is set to 10GB (but you can create multiple databases), no SQL Agent, single CPU utilization & max 1 GB RAM allocation. I think this is a good package and configuration you are getting it for free. And a best option for students and for beginners in SQL if they want to learn and practice SQL Querying without investing anything.

–> Check this video on how to download SQL Server with above two options:

You can also directly download the Express bits from here:

– SQL Server 2014 Express and Tools (DB Engine with SSMS): x64 | x86

– SQL Server Management Studio (SSMS only) 2014: x64 | x86

– SQL Server 2014 Express and Tools with Advanced Services: x64 | x86

… with the Advanced Services option you get extra tools like Fulltext Search and Reporting Services.

After downloading the bits from Microsoft site you are now ready to install SQL Server.

–> Check this video on how to install SQL Server (its very easy, but just in case):

–> After install completes you can download the sample database AdventureWorks2014 for SQL Server 2014 from CodePlex and restore the Backup (.BAK) file to SQL Server, click on the image below: