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

Indexed Views. Part 1. The Benefits

Indices serve three main purposes: 1. They are used to limit (or minimize) the amount of data being read to execute queries. 2. They can eliminate costly operators (like sort or aggregate) from query execution plans. 3. Finally, they can tremendously improve concurrency. On the other hand, indices aren’t free — not only they take [...]

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