Infrastructure at your Service

Tag

SQL Server 2014 Archives - Page 5 of 7 - Blog dbi services

Microsoft Team

SQL Server 2014: sys.dm_exec_query_profiles, a new promising feature

By | Application integration & Middleware | 4 Comments

Among the hidden features provided by SQL Server 2014 there is a very interesting dynamic management view named sys.dm_exec_query_profiles. This feature will help the database administrators and experienced developers to troubleshoot long running queries in real-time. I’m pretty sure you had to deal with the following questions: When will this query finish? What percentage of total workload is performed by this request? Which steps are the longest? Before SQL Server 2014 it was impossible to…

Read More
Microsoft Team

SQL Server 2014: Availability groups failover impossible with Failover Cluster Manager

By | Hardware & Storage | 2 Comments

A couple of weeks ago, I worked for a customer that wanted to implement SQL Server 2012 (and not SQL Server 214) AlwaysOn with availability groups. During our tests we performed a bunch of failover tests and the customer tried to perform a failover of one of the installed availability group by using the failover cluster manager (FCM). Of course, I told him this is not best practice because the failover cluster manager is not…

Read More
Microsoft Team

Tempdb enhancements with SQL Server 2014

By | Application integration & Middleware | No Comments

SQL Server 2014 is definitively designed for performance and I will try to demonstrate it during this blog post. I like to talk about hidden performance features because generally it does not require any changes for applications unlike in-memory tables for instance (aka hekaton tables). Since SQL Server 2005 version some improvements have been made for tempdb. Tempdb caching is one of them and allows to reduce the page allocation contention. Basically to create a…

Read More
Microsoft Team

SQL Server 2014: SELECT INTO operators can run in parallel

By | Application integration & Middleware | No Comments

A couple of weeks ago, I had to provide some best practices in term of architecture for a “heavy writes” environment that consists in importing data from different sources into SQL Server tables. At the customer place, I was asked about interesting new SQL Server features that could be used to increase the performance speed of the data import process. Indeed, SQL Server 2014 provides an interesting enhancement of SELECT INTO statement that is often…

Read More
Microsoft Team

SQL Server 2014: In-Memory tables, Bw-Tree, and storage

By | Database management | No Comments

SQL Server 2014 introduces hash indexes with in-memory optimized tables. I described some of their aspects in a previous blog post.  These indexes are very efficient with lookup search operations but have some limitations with operations like range scans, inequality predicates or scan order operations. These limitations are linked to hash index design that stores rows in the index in a random order. Fortunately, nonclustered indexes for in-memory optimized tables (aka range indexes or Bw-Tree)…

Read More
Microsoft Team

SQL Server 2014: Buffer pool extension – an interesting new feature

By | Database management | One Comment

Buffer pool extension (aka BPE) is an interesting feature of SQL Server 2014. Database administrators already know that memory is one of the key assets available for SQL Server. This is especially true for buffer pool memory, which plays an important role in storing data page and reducing the I/O demand. Nowadays, even if the memory has become cheaper, we are often facing limitations such as mid-size hardware configurations and increasing memory. All this can…

Read More
Microsoft Team

SQL Server 2014: Deleting files of a hekaton table

By | Database management | No Comments

A recurrent question I have often heard about Hekaton objects is the following: Is an accidental deletion of the compilation files of a hekaton table on the file system irreversible and could this compromise the execution of SQL Server? To check the SQL Server behaviour in such situation, we can perform the following test with an in-memory optimized table: CREATE TABLE [dbo].[StorageTestTable] (        [c1] [int] NOT NULL,        [c2] [char](100) COLLATE Latin1_General_100_BIN2 NOT NULL,…

Read More
Microsoft Team

SQL Server 2014 : SELECT ALL USERS SECURABLES & DB admins

By | Database management | No Comments

Microsoft will introduce four new security permissions in SQL Server 2014. One of them called SELECT ALL USERS SECURABLES is the subject of this post.  As explained by Microsoft SQL Server 2014 will allow a database administrator to manage data without seeing sensitive data or personally identifiable information. We can achieve a greater compliance but we must take care what is said because we could be wrong about the terms “manage without seeing sensitive data”….

Read More
Microsoft Team

SQL Server 2014: Hekaton memory optimized tables, hash indexes, and bucket counts

By | Application integration & Middleware | One Comment

For my first blog post at dbi services, I think it could be a good opportunity to begin by discussing around SQL Server 2014, Hekaton memory optimized tables, and hash indexes. When you create a memory optimized table you have to consider the number of buckets that you have to reserve for its associated hash index. It’s an important aspect of configuration because after creating your table, you cannot change the number of buckets without…

Read More