Clear all

SQLServer: Performance and Tuning

Posts: 482
Topic starter
Joined: 3 years ago

Hi Everyone, 

I am often asked about performance tuning of different platforms.  I just thought I'd share some resources with you where you can learn more about the art of Performance Tuning.  Please note: reading is one of the best ways to get your skills up to date, and don't forget...  Some of these skills can actually be translated to other platforms as well.  The knowledge in these articles isn't always limited to a single platform.

100 Million Row Load in SQLServer:

What I like about this article: many of the techniques can translate into: insert into select from techniques to move data using ELT.  Also, the fact that people believe (incorrectly) that SQLServer can't handle 100 million rows.   Well, it CAN.

- this article is interesting because it talks about the difference of using full blown managed Azure in the cloud, vs simply putting SQLServer on an Azure VM
Right from Microsoft itself:
Here's one that compares Azure to SQLServer 2019
(I know you are running on 2016 - but I've not found a comparison yet)
There are some really great observations in this article around clustering, clustered indexes, partitioning, moving to the Azure SQLServer cloud instance:
- some neat notes: throughout the article.
Another article: (similar to the top two)
Remember you have four choices:
SQLServer 2019 on prem (in a VM or not) on a Windows OS
SQLServer 2019 on prem (in a VM or not) on Linux OS (this one is much more scalable and performant)
SQLServer 2019 on Azure VM (using the Azure Operating System as mentioned in the top article)
Azure as a SQLServer DB (where the differences are outlined in the last article, fairly well)
The google search I ran:
In comparing SQLServer versions for performance, I found a few articles  here:
This article is interesting because it highlights major performance differences in SQLServer 2019
and it states in 2017 (compatibility mode too) that it has major performance increases in COLUMNSTORE indexing
In this article, it talks about differences in performance of SQLServer versions
one of the biggest differences introduced in SQLServer 2017 is "automatic database performance tuning"
so if you are running in "2016 compatibility or 2012 compatibility mode" you are not able to take advantage of any of these items.
in the 2016 version - they added "in-memory OLTP" operations, cool stuff.
A posting for performance that is interesting:
** remember: compatibility level is set on EACH "database property", it's not just one setting at the server level.
Sometimes, after changing compatibility levels in the properties, you need to recompile indexes, and/or update statistics
to get the optimizer to generate new plans with the "new compatibility levels"
AWESOME article:
Finally: Some performance tuning resources for SQLServer that should really help:
An article EVERYONE on your team should read:
Articles for your SQLServer DBA's:
I couldn't find any documentation per-say on this tool, but here's another tool that claims to offer data lineage across process flows.
Apparently Azure has one built in...