Developers Choice: Programmatically identify LPIM and IFI privileges in SQL Server

Lock Pages in Memory and Instant File Initialization privileges are couple of configuration settings which every DBA, SQL Server consultant or enthusiast have it in their checklist to ensure they see a predictable performance for their SQL Server instance. While Lock Pages in Memory privilege information is logged in SQL Error log, Instant File initialization…


Developers Choice: USE HINT query hints

The use of Trace Flags has always been understood as a sort of ‘hack’, and a solution that is hard to manage and sometimes understand (namely because it depends on a number). Over the years, users have seen several knobs added to SQL Server to influence the Query Optimizer. These trace flags are documented in…

2

SQL Server 2016 SP1: Things you should know

With the release of SQL Server 2016 SP1, a number of programmability features which were only available in Enterprise Edition are now enabled in Standard, Web, Express and LocalDB editions of SQL Server 2016. These features will allow developers and ISVs with consistent programming experience to build applications which scale across all edition of SQL…


Developers choice: Columnstore index in Standard and Express Edition with SQL Server 2016 (SP1)

On 16th Nov 2016, Microsoft announced the support for ‘Consistent Programming Surface Area’ across all editions of SQL Server with few exceptions. Please refer to the https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released. The key value proposition is that it allows customers to use the same code base independent of the editions in SQL Server.  For example, An ISV developing an application to target customers…

9

Developers Choice: CREATE OR ALTER

Today we are starting a new blog post series entitled “Developers Choice”. In the first few posts we will be focusing on SQL Server 2016 SP1 enhancements that focus on the developer community, including all developer DBA’s. Following in the same reasoning of simplifying DDL statements that was started by the DROP IF EXISTS language…


New Showplan XML properties in SSMS October Release

Back in March we announced the availability of per-operator level performance stats for Query Processing (exposed in Showplan XML) with SQL Server 2014 SP2 and SQL Server 2016. However, SQL Server Management Studio (SSMS) did not expose this information, and so users had to look in the Showplan XML, in the context of the appropriate…

4

Columnstore Index: Why do I need to create clustered columnstore Index on In-Memory OLTP table for Analytics?

The columnstore index on In-Memory table(s) targets real-time operational analytics (HTAP) allowing customers to eliminate the need for ETL (Extract, Transform and Load) into a separate Data Warehouse.  For details on the scenario, please refer to https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/03/18/real-time-operational-analytics-a-short-video-on-the-value-proposition-in-sql-server-2016/  You can enable HTAP on disk-based tables by creating NCCI on one or more tables as shown in…

0

In-Memory OLTP: Is your database just in memory or actually optimized for memory?

In my many conversations with customers during Microsoft events, people often confuse between the terms ‘In Memory’ and ‘Memory-Optimized’. Many of us think that they are one and the same. If you continue reading this blog, you will realize that they are somewhat related but can lead to very different performance/scalability. To understand this, let us…

2

Columnstore Index: Which Columnstore Index is right for my workload?

While we all understand that columnstore index in SQL Server 2016 can deliver amazing query performance for analytic queries but how do you choose among multiple flavors of columnstore index available. The SQL Server 2016 offers the following choices Clustered columnstore index (CCI) Clustered columnstore index with one or more nonclustered btree indexes (CCI/NCI) Nonclustered…

0

Columnstore index: Why do we refer to it as In-Memory Analytics?

Columnstore index is part of In-Memory technologies released in SQL Server 2014 and SQL Server 2016. Please refer to https://blogs.msdn.microsoft.com/sqlserverstorageengine/tag/columnstore-index/ for a series of blogs on columnstore index. As the name In-Memory Analytics implies, you may think that columnstore index is entirely memory resident but it is not really the case for columnstore indexes created on…

0