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 Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 13
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.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3472 Visits: 560
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 Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 13
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
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24616 Visits: 4879
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 Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 13
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.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1170 Visits: 538
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 Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 13
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.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1170 Visits: 538
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
SSC Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 Visits: 138
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 Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 13
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
SSC Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 Visits: 138
@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
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