SQL2016 -Configurations for DWH-Performance

Hi,
I have to configure a new SQL2016 server. What are the key performance settings for optimal performance in DWH mode?

  • mulitple Filegroups / Files
  • separate Storage for UserDB, tempdb, logfiles etc.
  • traceflag 1117/1118
  • sp_configure (any performance-Settings needed ?)

It is only the server configuration - not indexes, performant SQL, server hardware ....

Thanks
Regards
Nicole‌‌

There's probably lots of things, but a couple that spring to mind:

Configure TEMPDB for multiple files, according to the number of CPUs. I've read Yeahs/Nays on this though ...

We use a script to extend all Log files, before use, to minimise VLFs (based on a script by Kimberly Tripp)

Hehehe ... I reckon you'll need to be able to influence the Hardware to get the split of DB Files as you want it :slight_smile:

You don't need to mess with Trace Flag 1118 anymore. Its functionality is automatically enabled in 2016.

Contrary to what others recommend, I wouldn't set Trace Flag 1117 on anything other than a multi-file TempDB (and, IIRC, 2016 allows for database level Trace Flags now) and then someone they don't have better control over their code before I used it even there.

Given the way that SANS operate nowadays, supposed "separate storage" for MDFs/LDFs and TempDB is unlikely because it frequently won't actually be "separate storage" due to the size of today's disks. It is, however, a good idea to still have such things on separate drive letters just to make things a bit easier to manage.

The hardware keys to performance are still disk speeds and the pipe that gets you to the disks, memory (a truly worthy investment), and a good network infrastructure. That will give good code a chance. Bad code will still kill any system. When we did a massive upgrade to new hardware, it gave us a 2X improvement in performance (and that's with a shedload of SSD storage). Everyone cheered. Me too because I've seen hardware upgrades go sour.

I know you don't want to hear this but 2X is really nothing in the big performance picture compared to what you can do with fixing bad code. 60, 100, 1000, even 10,000X improvement is possible in the code. After your hardware upgrade, find your top 10 worst queries and fix them. Remember the worst queries are NOT always the longest running queries. :wink: After that, make it a habit to stay on top of bad queries and fix a couple every month so they don't grow on you. 2X hardware improvements are great but they're nothing to what you could do with code.

2 Likes

+100 (because it said "+1" was too short a message :slight_smile:)

+200!