Monthly Archives: January 2016

SQL 2016 – Temporal Tables the way we are most likely to end up using it

By | January 7, 2016

In this post I am quickly going show how to convert the existing tables into temporal tables. This is a common requirement I expect we will all encounter when we migrate to SQL 2016 at some point in the future. In this example a take an existing OLTP table and create a history table for… Read More »

SQL Cuppa Sessions

By | January 7, 2016

If you have been following me on LinkedIn recently you must have seen this update. Looking forward to having some fun SQL conversations, especially on what people think of SQL 2016 so far. One of the questions I have been asked is “How will we recognize you”? (I don’t have a lot of pics on… Read More »

SQL 2016 – TempDB Optimizations are enabled by default Woot!!

By | January 7, 2016

Last year I had spent a decent amount of time writing about tempdb. With SQL 2016 a lot of those point are validated and enabled by default going forward. Starting with SQL 2016 additional tempdb files will be created during install time and the DBA has the option of preventing this if needed. Additionally there… Read More »

SQL 2016 – sys.dm_exec_query_stats upgraded version

By | January 6, 2016

This is one the most frequently used DMVs for DBA when troubleshooting performance issues and MS has been adding more columns to the DMV with every release. In SQL 2016 there are an additional 26 columns being added. The best part is that these 26 columns help break down the CPU and Memory utilization even… Read More »

Sample Script for Partitioning

By | January 5, 2016

First we create the database CREATE DATABASE PARTITIONSAMPLE GO — SET THE CONTEXT TO PARTITIONSAMPLE DATABASE USE PARTITIONSAMPLE GO — ALTER THE DATABASE TO ADD A NEW FILE GROUP , THIS ALLOWS US TO ADD ADDITIONAL DATA FILES WHICH IN TURN — WILL ALLOW THE DATABASE TO BE SPLIT OVER MULTIPLE DISK , REDUCNG DISK… Read More »

SQL 2016 – Updatable Non Clustered Column store indexes

By | January 4, 2016

This might some as a big relief for those who use ODS for Reporting. Previously we have the following limitations with the below types of indexes Clustered Index – good for fetching atomic rows within OLTP Non Clustered Index – Good for fetching a set of rows and columns within OLTP Non Clustered Columnstore Index… Read More »

SQL 2016 – DATEDIFF_BIG

By | January 4, 2016

I don’t know about most guys out there but I personally feel this was a great addition to SQL 2016. I use the datetime2 function quite a bit when analyzing performance issues. This trend has started only recently in my case as we started using in memory OLTP tables and Columstore indexes etc. and were… Read More »

SQL 2016 – Compress and Decompress – with a catch! Compress doesn’t always save space

By | January 4, 2016

While working at Thomson Reuters I was part of the Search and navigation team and we had a number of reports created around the data being analyzed from the FAST search engine. The problem with analyzing the data was that the search phrase was often very long since it included the actual user string as… Read More »

SQL 2016 – Basic Availability Groups

By | January 1, 2016

With database mirroring out of the picture a question on most SMEs mind was how can we get HA on our SQL machines now? Database mirroring was a favorite for most small businesses since it allowed the user to have multiple usable database engines and therefore was able to justify the cost of using SQL… Read More »