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…
Performance
Query Store for Everyone
SQL Server 2016’s Query Store feature is, without a doubt, one of the most exciting new features for this version. The Query Store will keep track of current and past query execution plans which grants you deeper insights into the performance…
Heap tables in SQL Server
In the last post of this blog series, I discussed what a B-Tree index was and briefly explained its history. Before we dive into the indexes which SQL Server uses, it is important to set the foundation of data structures. The…
The ‘B’ in B-Tree – Indexing in SQL Server
Some people advocate we should count by twelve rather than ten. This is because you can easily count the twelve segments of your fingers by using your thumb as a pointer. With that method, you can count the number of…
Performance tuning backup and restore operations
The standard backup strategy seems to always include backups running at midnight, hoping that they complete before anyone starts using the applications. There is value in performing maintenance on off-hours. However, many applications do not have off-hours or our databases grow…
Do we care about development environment performance?
Recently, but not for the first time, I was confronted with a server administrator that didn’t want to allocate more than one vCPU to a SQL Server machine in a development environment. His exact words ring over and over in my…
Implicit Conversion Mine Field: Blowing SARGability away!
Implicit conversions are convenient. Implicit conversions are EVIL! When talking to professionals about implicit conversions, I’ve heard a few different comments, such as: (a developer) Don’t waste time converting if the engine does it for you. (a DBA) Sure it…
Do not use percentage based auto-growth
Having autogrowth enabled on your SQL Server instance is great! Even in IT shops where you diligently right-size your database files (which is a good idea), autogrowth is the perfect emergency feature. It will increase the size of your files, as needed, until…
Fast, light-weight, row counts
Often we need to know the number of records in a table. If you are looking for something very specific, such as the number of non-NULL values in a particular column, learn how to use COUNT() correctly with this post…
SQL Server SCHEMABINDING
SCHEMABINDING is an option that is available for objects in T-SQL which contain user defined code. Examples include, stored procedures, indexes, and functions. Straight from MSDN, SCHEMABINDING… Specifies that the schema is bound to the database objects that it references….