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.
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') WITH NO_STATISTICS, NO_QUERYSTORE
–> 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: SELECT P.BusinessEntityID, P.Title, P.FirstName, P.MiddleName, P.LastName, E.BirthDate, E.Gender, E.HireDate, E.JobTitle, E.MaritalStatus, D.Name FROM [Person].[Person] P INNER JOIN [HumanResources].[Employee] E ON E.BusinessEntityID = P.BusinessEntityID CROSS APPLY ( SELECT TOP 1 DepartmentID FROM [HumanResources].[EmployeeDepartmentHistory] DH WHERE DH.BusinessEntityID = E.BusinessEntityID ORDER BY StartDate DESC) EDH 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
– 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
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.
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 🙂
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.
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:
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
8. Using Built-In Functions
– SQL 2014 new functions (Conversion, Logical)
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 !
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] GO -- Add the Database to a new FileGroup ALTER DATABASE [TestManDB] ADD FILEGROUP [TestManFG] CONTAINS MEMORY_OPTIMIZED_DATA GO ALTER DATABASE [TestManDB] ADD FILE ( NAME = TestManDBFG_file1, FILENAME = N'E:\MSSQL\DATA\TestManDBFG_file1' -- Put correct path here ) TO FILEGROUP TestManFG GO
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 ,FirstName NVARCHAR(50) NOT NULL ,LastName NVARCHAR(50) NOT NULL INDEX [IX_BusinessEntityID] HASH (BusinessEntityID) WITH ( BUCKET_COUNT = 2000) ) WITH ( MEMORY_OPTIMIZED = ON ) GO
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 INSERT INTO @PersonInMem SELECT TOP 1000 [BusinessEntityID] ,[FirstName] ,[LastName] FROM [AdventureWorks2014].[Person].[Person] SELECT * FROM @PersonInMem GO
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 INSERT INTO @PersonInMem SELECT TOP 1000 [BusinessEntityID] ,[FirstName] ,[LastName] FROM [AdventureWorks2014].[Person].[Person] select * from @PersonInMem -- 2. Disk-Based Table Variable DECLARE @Person AS TABLE ( BusinessEntityID INT NOT NULL ,FirstName NVARCHAR(50) NOT NULL ,LastName NVARCHAR(50) NOT NULL ) INSERT INTO @Person SELECT TOP 1000 [BusinessEntityID] ,[FirstName] ,[LastName] FROM [AdventureWorks2014].[Person].[Person] select * from @Person GO
–> 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%.
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
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:
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:
… 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: