Performance
-
MaxDOP Added to SQL 2019 CTP3.0 Setup
You can now set MaxDOP during SQL 2019 CTP3.0 setup. MaxDOP is the sledge hammer to Cost Threshold’s tack hammer (also MaxDOP database scoped configuration in SQL 2017+) and getting MaxDOP set correctly can be an art that is highly dependent on your workload. However, there are some best practice guidelines we can follow. Remember…
-
Verify Allocation Unit Size for SQL Server
Why Do I Care? Everything in SQL Server is stored on disk in 8K pages. The Microsoft recommended best practice is to format using a 64K allocation unit size. Processes such as the checkpoint and Lazy Writer try to write in extents when possible. Extents are a collection of 8 pages and since each page…
-
Implementing Buffer Pool Extension
Buffer Pool Extension was released in SQL 2014 so it’s not new. It is also not advertised very much, but that’s not for lack of usefulness. RAM is far faster than SSD and certainly arm based disks, but most databases are too big to cache into RAM. BPE allows us to extend the buffer cache…
-
Query Store Questions From The Field
I have had several Query Store questions come in from customers lately. Some of the questions stemmed from things read on the internet so this video is aimed at both answering the questions and debunking some false information you might find out there. We want concrete answers to the following questions, so we’ll demonstrate them…
-
Trace Flag 1117 Growth and Contention
In this video I talk about Trace Flag 1117 and how it was designed to help keep data file growth with multiple data files in TempDB consistent. You’ll see how growth occurs if the files are disproportionate and also the effect it has on TempDB contention with SQL Server special pages (PFS, GAM, SGAM). For…
-
8 Hours of Free Performance Training
How could you possibly argue with getting 8 straight hours of SQL Server Performance training for FREE? You can’t! Go check out the annual Performance Palooza hosted by the Performance Virtual Chapter on July 23rd, 2015. This is a virtual online event open to the public and it’s absolutely free. We’ll have 8 back to…
-
Local TempDB on SQL 2012
This post has been sitting in the draft folder for way too long, March of last year to be precise. So although this is no longer new information, it has not been talked about all that much. I’m talking about the new feature in SQL Server 2012 that allows you to put TempDB on local…
-
Reinstall SQL Server Performance Counters
I’ve had several instances over the last few months where I had to reinstall the SQL Server Performance Counters. It sounds odd to have them magically disappear, but I’ve found a 3rd party monitoring product to be the culprit. First we need to unload the counters just to make sure everything is removed and we…