Performance

Linked Server Elimination

Linked Server Elimination

SQL Server has a feature for partitioning tables and indexes. Partitioning can be implemented at many levels, however. You can create multiple tables for one logical data set, you can split the set into multiple databases, and you can even…

Inside the XEvent Profiler

Inside the XEvent Profiler

XEvent Profiler is a new feature of SQL Server Management Studio v17.3. I applaud the SQL Server Tools Team for this feature. I feel like it will go a long way towards putting the legacy SQL Profiler to bed. Sometimes…

DBCC CLONEDATABASE

DBCC CLONEDATABASE

Often I hear developers claim that they need production data in order to properly tune their queries. There are a couple of reasons that they believe this. The most important one is likely because the execution plans generated in a…

DBCC OPTIMIZER_WHATIF: Spoofing production hardware

DBCC OPTIMIZER_WHATIF: Spoofing production hardware

Performance tuning in a development environment can be difficult. Processor core count and memory are important factors in execution plan generation. The undocumented command DBCC OPTIMIZER_WHATIF will alter the optimizer’s perception of its server resources. Syntax You can use the DBCC…

Unique Indexes and Computed Columns in SQL Server

Unique Indexes and Computed Columns in SQL Server

In the last post of this blog series, I discussed what a non-clustered index is and how to choose an ideal non-clustered index key. In this post, I will discuss indexes on computed columns and unique indexes. Computed columns Computed…

Non-Clustered Indexes in SQL Server

Non-Clustered Indexes in SQL Server

In the last post of this blog series, I discussed what a clustered index is and how to choose an ideal clustered index key. In this post, I will describe the non-clustered index and offer design tips as they relate…

A better way to search dates

A better way to search dates

SARGability is the ability of the query to properly search the arguments that you pass it. When your query is not sargable you cannot properly use your available indexes. A few months ago I wrote about sargability in detail. Today I want…

Clustered Indexes in SQL Server

Clustered Indexes in SQL Server

In the last post of this blog series, I discussed what a heap is and how it impacts query performance. In this post, I will describe clustered indexes and how they differ from a heap. They are indexes but also…