Transactions basics – part 2: Isolation levels
Fair warning, this post is a little on the long side but bear with me, understanding the differences here is important. All SQL statements used in this post is in…
Transactions basics – part 1: Transaction Modes
In SQL (almost) nothing happens unless it is within a transaction. A transaction is a single unit of work where all contained changes are either completed successfully or rolled back.…
Common settings – Max Degree of Parallelism and Cost Threshold for Parallelism
I’m starting off a set of posts regarding settings I often adjust, what they do and suggested values for them when dealing with quite ordinary OLTP systems. In this first…
Antivirus and MSSQL – what to do
For every new installation of SQL Server there is always the same questions regarding antivirus solutions and their configuration on the SQL server. Antivirus software will always come at a…
Monitoring Metrics – AOAG specific monitoring metrics
For my writeups of monitoring metrics I will add what I see as necessary correlation metrics where relevant to have a more complete description of how I would use it…
Heaps 101
In my post about last page contention I proposed heaps as a possible solution for that problem. But before you start using heaps, there are a few consequences that must…
Last page contention and primary key considerations
Last page contention happens when many concurrent threads are trying to modify the same page of a rowstore index (clustered or non-clustered). Only one thread can add or change data…
Index Fragmentation (rowstore)
Rowstore Index fragmentation can be divided into two groups: internal fragmentation which is measured in page density and external fragmentation that relates to the logical order the pages themselves are…
Monitoring Metrics – PAGEIOLATCH_XX and associated metrics
For my writeups of monitoring metrics I will add what I see as necessary correlation metrics to have a more complete description of how I would use it. There are…
Fill Factor
Fill factor defines how much space on the page should be used to store data during (rowstore) index creation, index rebuilding or index reorganizing. It has traditionally been used to…