SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Improving SQL Server 2016 Large Database Performance


Improving SQL Server 2016 Large Database Performance

Author
Message
henrik.thomsen.dk
henrik.thomsen.dk
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 16
Comments posted to this topic are about the item Improving SQL Server 2016 Large Database Performance
Kenneth Igiri
Kenneth Igiri
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3838 Visits: 575
Great Article. Particularly loved the trace flag tips.

Wondering about two things though:

1. Can we set optimize for ad-hoc workloads simply because we have a large database? I thought this should be based on the "owrkload" actually ... (no pun intended)

2. Because of compatibility issues, I have left some databases in 100 , 110 or 120 (DBMA pointed out potential risks of upgrade). How bad really is this?

Br. Kenneth Igiriwww.scribblingsage.comwww.igiribooks.comAll nations come to my light, all kings to the brightness of my risingSmooooth
henrik.thomsen.dk
henrik.thomsen.dk
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 16
Hi Kenneth.
1: Optimize for ad-hoc workloads recommendation is general, has nothing to do with the size of databases.
2: Nothing wrong with using older compatibility modes as long as your policies for calculating statistics are aligned with the platform. My point is that when you upgrade to 120 or later, you cannot just trust auto update stats for large databases. You need to check your query plans and probable set up maintenance jobs to calculate stats.
alen teplitsky
alen teplitsky
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27313 Visits: 4917
Why would anyone run any other application on a SQL server? Especially one with large databases like this?

I've had really good results with lock pages in memory. I've never done it on SQL 2016 but running older SQL on bare metal hardware it was almost a necessity. Otherwise all the HP crapware you have to run on HP servers take up memory and you'll see periodic errors about it being pooled out or something like that. Forgot the exact error. Haven't seen it on vmware with vmtools, but it can still happen.

When I ran into this problem we would get periodic issues with SQL and memory issues. Once we enabled lock pages in memory they went away.
henrik.thomsen.dk
henrik.thomsen.dk
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 16
To be honest, I'm passing on the recommendation about not locking pages in memory from Microsoft black belt team.
I haven't tested performance with and without this policy. I doubt that it had any impact on performance in our setup.
I agree that no other apps should run on a VM that has SQL Server installed serving large databases.
William Soranno
William Soranno
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1394 Visits: 540
Could you clarify the Maximum Server Memory formula?
My VM's for sql server run with a minimum of 48gb.
If I follow your recommendation, I would be taking 9gb away from sql server.
Usually I reserve 4-5gb for the OS.
Also, using your recommendation for my one server that has 96gb, I would need to reserve 15gb for the OS.
Why?

Bill Soranno
MCP, MCTS, MCITP DBA
Database Administrator
Winona State University
Maxwell 143

"Quality, like Success, is a Journey, not a Destination" - William Soranno '92
henrik.thomsen.dk
henrik.thomsen.dk
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 16
Again, I'm giving you the same lame answer: I'm repeating the recommendations from Microsoft black belt team.
I have not analysed memory consumption by the OS on our servers in detail.
We used the Azure L8s VMs with 64 GB RAM, giving 4 + (64 - 16) / 8 = 12 GB to the OS and 52 to MSSQL.
I have not tested a setup with just 4 GB for the OS and 60 GB for MSSQL, but I'm guessing that it would work just fine. I don't see this as the root cause for the performance issues that we experienced.
William Soranno
William Soranno
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1394 Visits: 540
Thank you for the reply.

Bill Soranno
MCP, MCTS, MCITP DBA
Database Administrator
Winona State University
Maxwell 143

"Quality, like Success, is a Journey, not a Destination" - William Soranno '92
Gerhard Pisch
Gerhard Pisch
Old Hand
Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)

Group: General Forum Members
Points: 391 Visits: 161
Thank you for sharing this interesting article!

But there is specially one thing that I can't understand.
Why update the statistics on saturday with fullscan and destroy the fullscan stats on monday (with sample 10%)
This makes no sense for me?

Gerhard Pisch
Advanced BI Developer
Austria
henrik.thomsen.dk
henrik.thomsen.dk
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 16
That is an error in the article! This is wrong:

For large databases, do not use auto update statistics. Update statistics via maintenance jobs instead. I recommend the following maintenance jobs:

  • Update statistics on work days with at least 10% sampling (100% for filtered indexes)
  • Update statistics every Saturday with fullscan

It should instead be:

For large databases, do not use auto update statistics. Update statistics via maintenance jobs instead. I recommend the following maintenance jobs:

  • Update statistics daily with at least 10% sampling (100% for filtered indexes)

Gerhard Pisch
Gerhard Pisch
Old Hand
Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)

Group: General Forum Members
Points: 391 Visits: 161
@henrik.thomsen.dk

This make sense!

I use incremental stats where possible (partitioned tables).
With a small script, which determines the amount of changed rows in the partitions. If there are enough changes I update the stats of the partition.
In the really big tables (gt. 3TB) 10% are a good measure for good plans (in our environment)

Gerhard Pisch
Advanced BI Developer
Austria
travisGatesMcGee
travisGatesMcGee
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 56
This seems to be totally totally wrong:
Your database volumes must have at least 25% free space.
The answer is "it depends". You can have 1% free space vs. 50% … depending on the situation and usage, etc. …. there will be no difference.
Other than that, I always would love to have 99% free space. … but it is not a good idea.

Of the biggest problems with SQL Server and giant database tables is that the Statistics is stuck on 200 rows. Hope one day, Microsoft will start using an index param, just like doing asc or descending, you will be able to pick the row or page count for stats.
200 rows works with small and big tables, but when it comes to tables over 10-20 billion … in some cases, good luck.
If anybody from Microsoft wants to contact me on that ….travisGatesMcGee @ Ho tm ail dot com

Jared Karney
Jared Karney
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56720 Visits: 3704
Couple of things here:
1. I would not blanketly disable auto update stats. In fact, I would "almost never" disable auto update stats. Its a good idea to update stats as often as you can manually; nightly, weekly, whatever... but there's no benefit to disabling auto stats unless you experience frequent updates that impair performance. Even in that case, you are probably better off leaving auto update on, enabling the asynch option. NOTE: This article is referring to SQL Server 2016 where TF 2371 changes are enabled by default. https://blogs.msdn.microsoft.com/psssql/2016/10/04/default-auto-statistics-update-threshold-change-for-sql-server-2016/
2. TF 4199 should NOT be enabled by default. If the compatibility level of the database is 130, it will enable all optimizer fixes up to RTM. If you need fixes released after RTM, use the database scoped option, not the trace flag.

Thanks,
Jared
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search