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 [...]

Indexed Computed Columns and DBCC CHECKDB

Recently I spoke a couple times about using indices to solve some otherwise unsolvable performance problems, like turning non-searchable arguments into SARG ones. Now it’s time to point out a hidden cost of this solution — incredibly slow database consistency check. Problem Let me illustrate this problem with a sample database I used i.e. at [...]

Useful things about data recovery that every DBA should know. Part 4 — Restore planning

In the last part of this series we are going to find an answer to the ultimate question — what backup files should be used and in what order, to restore a database to their most current state? We will examine two cases: 1. When a database is being restored on the original SQL Server [...]

Useful things about data recovery that every DBA should know. Part 3 — Three differential backups tricks

In this article we are going to answers those questions: 1. Is differential backup a cumulative, or an incremental one? 2. Why you really should take a full backup just before switching any file group to read-only? 3. Do you really need a full backup to restart a broken log backup chain? If you missed [...]

Useful things about data recovery that every DBA should know. Part 1 — To what point in time full backup is being restored?

Every DBA took at least once full database backup. But do you happen to know to what point in time this backup would be restored? To the moment when backup started? To the time when it finished? Or maybe there is a third option? If you had chosen one of the first two answers, you [...]

The Case of VARCHAR(4), or when you should not use a variable length column?

Have you ever consider using VARCHAR(4) or similar data type? If so, you really should read this post. If not, please continue reading and find out why you were right. Don’t be cheap on data types SQL Server, at least until SQL Server’s 2014, reads and writes whole pages (8-KB blocks of data), neither rows [...]

I don’t have a SAN, so how many tempdb files do I need? Part 2 — PFS, GAM, and SGAM contention

In the previous article I don’t have a SAN, so how many tempdb files do I need? Part 1 — When splitting tempdb into to many files can be counterproductive to performance we saw that splitting tempdb into multiple files can be problematic. Now, it is time to have a closer look at the most [...]

I don’t have a SAN, so how many tempdb files do I need? Part 1 — When splitting tempdb into to many files can be counterproductive to performance

You can find some great and overwhelming load of not so good information about optimizing tempdb performance all over the Internet. This post has one purpose — to warn you of the consequences that you will face blindly following one of those “best practices”. Namely, if somebody tells you without even checking what your storage [...]

The Meaning of Stored Procedures. Part 8 — The case of “sp_” prefix and a geek riddle

Let me start the last part of the series with a simple question — What does the “sp_” prefix stand for? If your immediate answer is “system stored procedures, of course” you are only partially right. The better answer would be “sp stands for special prefix”. It might sound funny, but they are good reasons [...]

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 6 — NOCOUNT, ROWCOUNT and @@ROWCOUNT

If you remember, in the second part of this series (“Avoiding recompilations due to plan stability-related reasons”) I was talking about a drawback of setting plan reuse affecting SET option inside stored procedures. Nevertheless, this time I will show you two SET options that really should be set to on. We will start easily — [...]

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. [...]