Research SQL Server 2016CTP2 – 3/13

Technologie In-Memory w tym również indeks kolumnowy, to moja ulubiona tematyka, więc oczywiście postarałem się zbadać różne aspekty zmian i działania indeksu kolumnowego. Jest to w rzeczywistości trzecia wersja tego indeksu. Indeks kolumnowy w SQL Server jest dostępny od wersji 2012. W tej wersji był bardzo ograniczony np.: mógł być tylko, jako indeks dodatkowy i [...]

How to load data effectively?

With this post I would like to share with you some results of my own, limited data load performance tests. Even based on this narrow and platform-specific test general conclusion could be drawn, I think. Configuration Test server was HP Proliant DL580G8 equipped with 2 Intel E7 processors, 256 GB RAM and EMC VNX 5600 [...]

Max degree of parallelism and maintenance tasks

In this posts I would like to show you a huge drawback of another commonly seen “best practice” — the one about setting ‘max degree of parallelism’ server option to 1. What does this recommendation come from? As our machines grow bigger and bigger many database administrators and programmers find out that databases which used [...]

The Meaning of Stored Procedures Part 7 — No direct owners, name resolutions and the difference between EXECUTE AS SELF and EXECUTE AS OWNER

There is surprisingly much misunderstanding about schema-user separation, and about how name resolution as well as impersonation work in SQL Server. The ability to impersonate users by code modules was added in SQL Server 2005, along with database schemas (SQL Server 2000 only allowed context switching by executing SETUSER statement), but those features are still [...]

The Meaning of Stored Procedures. Part 5 — When plan reusing is a bad thing or how to implement conditional logic inside stored procedures.

It has been more than a month since the first article on this series was published, so let me revise what we have already discussed: 1. Part 1 — “Plan caching and reuse is a good thing” was all about execution plans caching and reusing. We proved, that recompilation not only takes a lot of [...]

The Meaning of Stored Procedures. Part 4 — When plan reusing is a bad thing or how to deal with “Parameter Sniffing problem”

Finally, after three articles in which I did my best to convince you that plan caching and reusing has massive positive impact on performance (that was the topic of the first article The Meaning of Stored Procedures.Part 1 — Plan Caching and Reuse is a Good Thing) and showed, what can be done to avoid [...]

The Meaning of Stored Procedures. Part 3 – Avoiding recompilations due to plan optimality-related reasons.

As you recall, in previous parts of this series we agreed that reusing execute plans might have tremendous impact on SQL Server performance, and saw how to avoid unnecessary recompilation by proper use of temporary tables and not changing cache-key SET options. If you missed those articles, they can be found here: The Meaning of [...]

The Meaning of Stored Procedures. Part 2 — Avoiding recompilations due to plan stability-related reasons.

In the previous article I tried to convince you that stored procedures are a proper way to avoid unnecessary compilations and recompilations. They may not be the fastest (in most cases prepared queries will be faster), but they are widely used, mainly because they are great from security and manageability perspectives. Unfortunately, stored procedures are [...]

The Meaning of Stored Procedures. Part 1 — Plan Caching and Reuse is a Good Thing.

Everybody knows that use of stored procedures offers a number of benefits over issuing T-SQL code directly from an application. One of the most common reasons for using stored procedures is as a security boundary — a user can be given access to execute a stored procedure without having permissions directly on the underlying objects. [...]