Up next in 10
Performance Tuning, Getting the Biggest Bang for Your Buck by Monica Rathbun | SQL Server Conference
Oct 30, 2023
Everyone wants to know if there are magic buttons you can push to make SQL Server run faster, better and more efficiently. In this session we will go over some of my go-to performance tricks that you can implement to get the biggest improvement with the least amount of change. When it comes to performance tuning, every second counts. We will cover memory optimization, isolation levels, trace flags, statistics, configuration changes and more. I’ll go over real life scenarios we come across as consultants and the changes we made to fix them.
About Speaker
Monica Rathbun lives in Virginia, is a Microsoft MVP for Data Platform, Microsoft Certified Solutions Expert and VMWare vExpert. She has nearly two decades of experience working with a wide variety of database platforms with a focus on SQL Server and the Microsoft Data Platform. She is a frequent speaker at IT industry conferences on topics including performance tuning and configuration management. She is the Leader of the Hampton Roads SQL Server User Group. She is passionate about SQL Server and the SQL Server community, doing anything she can to give back. Monica can always be found on Twitter (@sqlespresso) handing out helpful tips. You can find Monica blogging at sqlespresso.com.
Conference Website: https://www.2020twenty.net/sql-server-virtual-conference
C# Corner - Community of Software and Data Developers
https://www.c-sharpcorner.com
#SQL #performance #tuning #conference #sqlserver
Show More Show Less View Video Transcript
0:00
Okay, so everybody can see my screen. Now I just want to give everybody a heads up. This is normally
0:09
a 75 minute session. I am going to do my best to get it within the about 40 minutes that I have
0:17
since we ran over a little bit more. And those who know me already know that I am SQL Espresso for a
0:23
reason. I talk really fast. I'm always hyped up on coffee. So just hold on to your seats. It may go
0:29
a little faster than normal. I will have my slide decks completely available for you. So if you miss
0:35
anything, you're welcome to just go grab those on my website, or I'm sure C Sharp will have those
0:41
for you as well. So let's go ahead and get started. We're here for performance tuning, getting big
0:46
bang for your buck. I do this session as a checklist for you to take back these items slide by slide
0:53
to go into your environment and look and see where you stand. Is there any tweaks you can do
0:58
performance-wise to get a bigger bang for your buck, exactly why I titled this, as you make these
1:05
changes in your system and get a performance boost. Again, he already introduced me, but here's all my
1:11
contact information. I'm a Microsoft MVP. I'm very involved in the community, and I've been working
1:16
with C Sharp now for several years. So if you haven't visited their website, definitely go take
1:21
a look at all the information with SQL Server that they have out there. Normally, this is my
1:26
presentation rules. I want everybody to learn from each other. So feel free to get into the chat and
1:32
ask questions, talk to each other, talk about the things that I'm talking about, whether they worked
1:37
in your environment or not. Given our time frame, I may not be able to answer all of your questions
1:42
but I will be scrolling through that chat after we're done and try to follow up with anything that
1:47
I might have missed. So definitely take advantage of this and let's get into a conversation
1:52
As with anything, your best practices is where you're really going to get that fundamental performance boost, making sure that everything in your system is properly configured and you're thinking outside of the box of SQL Server
2:07
So we're really going to start there. And this is where we begin our checklist
2:10
So let's get started. And I can't, can't, can't stress this one enough
2:16
It's very, very simple. It has nothing to do with SQL Server configuration in and of itself
2:22
This is all about your Windows power plan, okay? I need to make sure that you are checking
2:29
not only on your box, your server, but you're also checking on a host
2:34
when you're going into a virtual platform, right? We wanna make sure you are set on high performance
2:40
not balanced. Balanced is the default when you actually set up a server, it's set to balance
2:46
If you have that set on balance, you are only using 70%
2:51
Look at that number, 70% of your CPU's power. You have already lost 30% of your CPU capabilities by just not having this power plan set correctly on your server
3:07
So what I need you to do is go back to each one of your environments and check this
3:12
If you don't have the ability to look on your host itself, have a conversation with somebody and have them check this
3:19
It is really important for SQL Server to be optimal in its CPU usage by having this plan set accordingly
3:27
Don't lose 30% off the bat when it comes to your power plan, okay? There's lots of information out there about this
3:34
You can actually do test runs using CPU-Z, if you look that up
3:38
and you can actually see the clock speeds on your CPU and do comparison
3:43
And I said before, if you saw my tweet, you might see Katie show up on my desk. this is Piper, a different one. So we can just ignore her as we go along, but you never know
3:50
what's going to pop up on my desk here. Okay. The next one services. This is a big one. And I have
3:56
to laugh because this happens all of the time. How many of you guys have gone and looked at your
4:02
servers or in actually inherited a server that when you look at the services, somebody went through
4:10
and enabled every single SQL Server option when they installed it, right
4:16
They hit every single checkbox and they installed it, right? What you really want to have when you have a SQL Server and you're doing OLTP
4:25
you really only want your engine installed on your box. If you have SSIS, SSAS, SSRS running, that is taking away resources from your engine
4:37
It may be possible at one point in time, you had planned to put SSIS on your box and plan to use it
4:42
And that was years ago, and you're really not using it anymore. Do yourself a favor, go through your services
4:48
check on all of your boxes, and make sure you're only running the SQL services that is actually needed
4:54
We're going to talk about some of the implications of having other services besides the engine running on your box
5:01
So we'll see that in a little bit when it has to do with memory and things like that
5:05
But do yourself a favor and check. Please don't run these additional services on the same thing as your engine
5:11
Now, we know because of licensing, we get into a situation where you have to have SSIS running on the same box
5:18
If that's the case, we're going to take into consideration some other things that we need to talk about as we move along
5:25
But take the time, go through this checklist item, look in your system, and really evaluate what services you're running and why you are running those
5:33
If you're able to move them off, even better, you're going to get a performance gain
5:38
Now, let's talk about patching. And I want you to talk about patching outside of just the scope of security, right
5:44
Everybody keeps their boxes patched because they're worried about the latest and greatest
5:48
virus or ransomware or whatever else that we have that comes through that can impede
5:54
on our system. I want you to start thinking about performance gains you get through patching, right
6:00
When we installed SQL 2016, there were issues. We actually get a performance boost by going simply to SP1
6:08
They actually made some bug fixes that were with the initial deployment
6:13
and that was fixed in SP1. Just by doing that upgrade, you get the boost
6:18
Again, going from older versions of SQL Server to newer versions of SQL Server gets you things
6:24
But also think about your CUs, right? Now we're doing CU patching
6:28
With every single one of those CUs, if you ever looked at the documentation behind there
6:33
you'll see a list of bug fixes that are happening. And you could be actually having performance issues based on one of these bugs that is simply fixed by keeping up with the latest CUs
6:45
Also, when you're thinking about patching, think outside SQL Server, right? Are your NIC cards being updated to the right patch
6:54
Is the VM on the latest and greatest? Are there Windows patches that need to happen
6:59
There are a lot of things that actually go with SQL Server and actually have implications with SQL Server
7:05
if they are not properly passed. So start talking about those drivers on the VM
7:10
Start talking about the NIC cards and other pieces when you're trying to really gain performance
7:15
with inside your system. I've got a link here to the build list so you can look and see what the latest CU is
7:21
if you're not familiar with what they are right now. I encourage you to take a look at what version you at do an inventory in your system and see if you able to apply some of those patches within your environment and get the gains that go with it So this is a big one
7:36
And usually I find that people tend to pay attention to this one more than what they used to do
7:44
What I want you to pay attention to here first is that box that I have in the red square
7:49
If your max memory looks like this number, 214-748-3647, then you need to stop what you're doing and set this max value. This max value tells
8:01
SQL Server, if I have a box that has 32 gigs on it, and SQL Server wants to use all of it
8:09
there's nothing to say that it can't take memory from Windows, and you end up in a starved
8:15
situation. If you set this max memory, let's say we leave old school thought of four gigs to the OS
8:22
so I have 2,800 listed here or 28,000 listed here, and I give 28 gigs to a SQL server
8:29
then it's going to max out at that 28 gigs. And you don't run into a starvation situation
8:33
when it comes to your Windows environment. So you want to make sure you set that
8:38
Now, how do you know what's the best number out there? There are some scripts that you can actually run
8:43
and it will give you, Jonathan Kahias from SQL Skills has a fantastic blog about this
8:49
You run the script and it will give you the exact number to put in there based on your environment. So I encourage
8:55
you to take a look at that. Also, when we're talking about max memory, keep in mind when you
9:00
have multiple instances on your box, I've run into situations where somebody put a default instance
9:06
they did the right thing, and they set 28 gigs for their max memory. Somebody went behind them
9:11
added another instance, a named instance. They did their research, they did 28 gigs of memory
9:17
on that instance. What happened? I now have way too much allocated memory, and I have both servers
9:24
that are both instances that are having issues because I allocated more than the memory was on
9:30
that actual box. So double check what your multiple instances are. Make sure they're allocated
9:34
accordingly, and you're not going over and over allocating that. Look and see my next item there
9:40
is SSIS, SSRS, and SSAS. When it comes to max memory, did you know, and I find this kind of
9:46
catches some people that these three other services do not share that max memory option
9:53
It's actually using the Windows memory. So if I only left four gigs to the Windows operating system
9:59
and I have SSIS and SSAS running on my box, guess what? They're taking away from that four gigs and
10:06
really could be causing issues on my box. So definitely take a look and see what exactly
10:12
you're running service wise and keep in mind, maybe you have to lower that max memory inside
10:17
SQL Server and the configuration because you have these other services running. Give enough to your
10:22
Windows OS to make sure these are manageable. Now, how do you figure out what that value is
10:28
You really kind of need to dig in. I like to watch in task manager or look and see what kind of memory
10:35
consumption each one of these are getting. When I've got my big data loads happening, things like
10:39
that. I can't give you an actual answer. I'm sure there's somebody who's blogged about it on what it
10:44
should be. My goal here is to let you know, take a look at it, think about it, and go from there
10:51
Because of time, I'm skipping this next lock pages in memory. I encourage you to take a look and
10:55
research that, the good and the bads. But take a look at my next item. This is all about the VM host
11:01
right? Again, this is normally something that is out of the purview of a normal DBA, unless you've
11:07
got a really good relationship with your admin. But I want you to make sure and ask the question
11:12
when you ask for memory be allocated to your SQL Server VM, make sure they set it the memory
11:21
reservation, right? It's a checkbox, set it as a memory reservation. If I ask for 32 gigs
11:27
I want to make sure I'm getting 32 gigs and the balloon driver that controls the allocation
11:32
on that host doesn't steal my 32 gigs away from me when another VM needs help
11:40
This balloon driver is there to actually allocate the load. Again, when something is starving for resources, it can borrow it from somebody else
11:47
and make sure that it can continuously keep all those VM running. When it comes to SQL Server, I'm selfish
11:52
Don't touch my box once you set it up. Make sure you ask for it to be set as a memory reservation
11:58
Therefore, that balloon driver can't get a hold of your memory, okay? The next thing you want to do is make sure they don't over allocate that memory on that box
12:07
I've run into situations where maybe that box only has 32 gigs to share with all the VMs
12:13
But because they know about this balloon driver, they've over allocated it and made it 48 gigs
12:19
And they expect the balloon driver to level all of that out. So it's something you need to keep in mind
12:24
And if you can get preview or purview to your vSphere or whatever it is, you can look inside the configuration inside your VM environment
12:33
See if you can get that. We'll talk a little bit about that in a minute. But that's memory
12:37
And in a nutshell, we can dive into this really far. But this is just a checklist item for you to go and review these things with inside your configuration
12:46
Now, this is TempDB. I have a whole big session on this. We can deep dive into this forever
12:51
But I want you to hit the highlights. and the highlights is this, is that TempDB really is a global resource
12:57
It's used by everybody with really, really high concurrency, right? And you want to make sure
13:03
you don't end up with contention inside your system. So what you want to do is make sure
13:07
one, it's on the fastest as possible. You know, nowadays with flash SSDs
13:12
this is less of a problem than what it used to be when we had a bunch of spinning disks behind it
13:16
But the one thing I really, really, really want to focus on here is multiple files
13:21
and they've actually helped us when we went to 2016 in the initial setup
13:25
They actually ask us how many files and recommend the amount of files to be created for your system
13:31
But you want your files to work in a round robin format, right
13:36
Without getting into a deep dive, you want to look at this. I've got them all set up in the exact same file size
13:43
And you'll notice my auto growth is exactly the same. You're going to do a minimum of a file per CPU core up to eight
13:51
When it comes to SQL Server, you end up with a cost threshold of where the cost benefit might overflow if you've got too many files out there for it to pick from
14:00
So a good rule of thumb is one per CPU core. Want to make sure every single file is the same size
14:06
Why? Because the way the transactions come through, it's going to look for free space
14:10
It's going to look where where's the biggest spot I can go do my work. If you have file one significantly larger than the other three files, guess what's going to happen
14:19
Everything's going to rush to file one and you end up with a concurrency issue
14:23
Don't put yourself in the situation. Go back tomorrow or Monday and look and see if you have these out of whack
14:29
If one is bigger than the other, you have contention, you have a performance issue
14:33
Make sure they're all sized exactly the same and make sure they grow exactly the same because you want to make sure they stay in sync
14:40
that's where prior to 2016, these two trace flags come into play. Trace flag 118 has to do with
14:47
mixed extents, and that's kind of in the background. But 1117 is what you want to really take into
14:53
account That actually will make it so anything in that files those files if one grows they all will grow Okay so those temp dev through temp four if I have a growth event they will all grow by 64 megs or whatever the increment it needs to move them along and keeps everything going smoothly in that round robin
15:11
OK, so just double check your environment. If you ever get one that bloats over the size of the other and gets really, really big, take the time to realign them and get that fixed
15:21
Again, I told you about CUs, right? There was some heavy metadata contention that actually happened within 2016 and 2017 in the background
15:30
Me as a DBA, I probably don't know anything about this. But just by going to CU8 for 2016 and CU5 for 2017, I've fixed a problem that I didn't even know existed, and my temp DB is happier for it
15:42
Okay, so keep that in mind. Also, when you're using temp variables
15:46
if you're using temp variables inside your temp DB or table variables inside temp DB
15:53
make sure you have trace flag 2453 set on. And what that allows you to do is when you
15:59
if you look at your table variables, you'll notice that an estimated row count is usually equal to one
16:06
Even though you might have a thousand rows or a hundred thousand rows coming through
16:09
it expects one. So it doesn't necessarily give you the resources it needs to move those records along throughout each node inside an execution plan
16:19
And the optimizer doesn't grant you the proper resources to handle that large record load because
16:24
it expects one. If you use 2453 with inside your table variables, it actually will do a recompile
16:32
and give you a better estimated value so it can plan for what memory grants it needs, what joins
16:38
it needs to use based on the row count that's coming through. Now, what's really great about
16:42
this is in 2019, they introduced something to fix this for us, which is a deferred table variable
16:48
decompilation or something. I can't think of it right off the bat. But there is 2019, there's an
16:53
option that fixes for us. But if you're in below that, 2453 is a good thing for you to actually be
17:00
doing in your system. Okay, so this is my favorite slide. And this is the most important thing you can
17:07
do for performance. And people laugh at this when I talk about it. Your job is contingent on so many
17:14
other people's jobs, the way they configure their storage, the way the network is designed
17:19
the way they're handling your virtual environment, things like that. It is important for you
17:24
and this is my opinion, to keep your admins, network storage, etc., drunk, fat, and happy
17:30
right? That's how I like to do it. Because at some point in time, you're going to tell them
17:35
their babies are ugly. You're going to tell them their storage is a bottleneck. You're going to
17:40
tell them and prove to them their VMware configuration is not right or however they
17:45
have their systems is not correct because you can prove in numbers inside the SQL server with
17:50
different counters on the box, lots of different things that show where your bottleneck is
17:56
where the latency is coming. And you've tuned SQL server really well that you can actually
18:02
eliminate SQL Server as a problem. And it's one of these outside resources that are the issue
18:07
So what does it feel like when somebody says SQL Server is the problem? Man, it hurts me because
18:12
SQL Server is my baby, right? It's what I like to take care of. I know I do it really well
18:16
I don't like when somebody comes at me and says SQL Server is the problem. The same thing happens
18:21
when it comes to your storage and network admins. Definitely build the best relationship you can
18:25
right off the bat, right? I like to bring donuts into these other departments. I like to take the
18:30
manager out maybe for pizza or invite them to lunch or do something because I need them to know
18:36
that I understand I need them and they don't need me, right? Having that great relationship makes it
18:41
easier for me as a DVA to ask for more storage, to ask them to give me purview inside vSphere
18:48
or VMware so I can see what the host actually is doing. And I can work with them on what's the next
18:54
design for disaster recovery. What's the next thing we can do to get SQL Server to run faster
18:59
and eliminate the storage latencies? Can I get involved in the conversation
19:02
to help determine what kind of storage we're going to pick up next for SQL Server
19:07
All of this kind of stuff is really great for my environment. So I want to make sure I remember
19:13
that I make sure that I understand their worth. Hopefully that comes across
19:17
because you're going to have to tell them that their baby is ugly. Believe me, I've been in situations
19:21
where I've gone head on with network admins and it was just a disaster
19:26
because nobody likes to be told how to do their job and what's wrong with their stuff
19:30
So definitely please keep that in mind. Okay, so let's talk about virtual environments
19:34
I see some questions on here with virtual environments. Virtual environments can really be a great thing
19:40
It can really also cause you issues that you have no control over
19:44
And that big one being your over-provisioned host, right? You have no control how many VMs are actually on that host
19:52
You have no control of what type of VMs are on that host, right
19:57
Maybe they're running Exchange or whatever they're using for Office or whatever they're using as a file system
20:03
Maybe they have, there's tons of stuff that they could be running on a box that is very, very resource consumption-wise that you don't want interfering with SQL Server
20:14
If you can't get a dedicated host for SQL Servers only, you have no control of what's going on there
20:20
So you want to make sure those hosts are not overcommitted, right
20:24
You want to know if they're moving your VM to another host
20:28
Maybe you're going to run into issues because they've actually motioned you over to another host for some reason
20:34
And now that you're not functioning well and that's out of your control. They can over allocate those CPUs
20:40
They can over allocate that memory. I actually had a client. Remember the days of Spectre and Meltdown, how that took away hyper threading when they did a fix
20:50
They had a box that had only 32, I'm sorry, 16 CPUs on there and they hyper threaded it
20:58
So it really made it 32. But they, when I looked at it, over allocated to 75 CPUs were allocated on that box
21:09
That was really 16. When they put in the patching for these two things, it took away the hyper threading
21:15
So they ended up with 16 CPUs, but allocated 72, which meant nothing was running
21:21
That was out of the purview of SQL Server. I knew this happened because I watched my SOS scheduler yield weights inside SQL Server
21:29
and I was able to look at the CPU ready time. Those are two indicators that you can look at for over-allocation when it comes to CPU
21:36
But that was out of the realm of my SQL Server purview, right
21:41
So the next thing you want to talk about is thin provisioning. This is the sneaky little thing that storage admins do
21:48
Say I want to create a new temp DB and I want 500 gigs. I go to my storage admin
21:53
I've actually bought them donuts that morning before I asked the question. And I said, I want 500 gigs for a new T drive for temp DB
22:00
And they're like, oh, Monica, you really don't need 500 gigs. And I said, yes, I do
22:04
I'm going to allocate 125 for four files for temp DB, 500 gigs
22:09
I'm definitely going to need that. And they said, okay, we'll give you 500 gigs
22:13
So he presents a drive to me that's 500 gigs. But what he really did was thin provisioned that drive
22:20
which meant he is actually only going to give me maybe 25 gigs or 50 gigs at a time in the background And when I have a growth event it will grow and give me another allocated amount 50 or 25 Well I don know when that allocation is going to happen
22:35
So I'm going to incur a performance hit every single time I get a growth event
22:40
depending on what allocation amount they then provisioned me. Okay. So I want you to go back
22:45
to your environment and say, is my storage thick provisioned? Have you really given me that 500
22:51
gigs. If not, ask them. It's a little checkbox when they configure it, right? Ask for thick
22:57
provision. And if they say no, what I want you to do, especially when it comes to TempDB, is go
23:02
ahead and grow out your TempDB. Take all that space in one chunk. You may not use it all right
23:07
away, but you've got that space. Just go ahead and take it. So that way you are not incurring
23:11
a performance hit when it comes to those growth events as they happened. So that's just a quick
23:18
note. All right, I got about 20 minutes. I think we're going to be good, hopefully. All right, compression. Let's talk about compression. Rope compression, page compression. There was a really
23:26
great session given by Bob yesterday or day before. If you go to 8KB website, he's got a fantastic
23:36
deep dive into Bob Pastry, into compression. But what you really want to do is by compressing data
23:43
you're actually able to get more data on that page and more space to be able to put more data within memory at the time you need it
23:54
Right. So you can do page compression and road compression and able to get a lot more data back rather than wasting space with different bits that are being taken up or bytes that are taken up by space
24:06
not really being used. So it saves on disk space, which is a great win for your storage admin
24:12
It actually costs a little CPU overhead, but it's worth it when it comes to being able
24:18
to retrieve your data faster and get more. So definitely take a look at that
24:23
There's lots of queries that you can run that gives you the actual estimated compression
24:29
that you'll get from your data. So you can see that it's a big win
24:33
One thing I want you guys to keep in mind is that when it comes to backup compression
24:40
There's a thing called deduplication. When your SAN admin tells you you cannot use backup compression
24:46
because we dedupe on the SAN and it saves them a bunch of space and stuff
24:52
I need you to call that a red flag. I need you to halt that and ask some questions
24:57
because if they're telling you you cannot use compression, that means they are actually deduping
25:03
that backup file on the SAN and you end up with all of these pointers in there
25:09
That's what deduping is. It looks for duplicate data and it creates a bunch of pointers and actually is able to save space on the sand
25:15
But what happens when you have to restore that data? And this is where you need to think and talk
25:20
When you do a backup, the backup is fine, right? Runs normal time, same as the native
25:24
You're good to go. But as you restore, do a side by side comparison
25:29
If you were able to do a native backup and you put it to the cloud or you put it to local or something like that and you restore it
25:35
you'll see maybe it actually was two hours for it to restore. If you do it with a de-duped backup
25:43
on a compressed SAN, right, if you do that, it's going to take approximately three to six times
25:50
potentially to do that restore. So when you're in a catastrophic situation and you have to restore
25:55
something because somebody did a select or a update or a delete with no where clause and you
26:02
have to restore that data. I can't wait three to six times more for that restore to happen
26:07
So do side-by-side tests. Do your research when it comes to that. It's a red flag you need to
26:12
talk about when it comes to backup compression. If they say we are going to de-dupe and you cannot
26:17
use it, see if you can maybe push your backups to the cloud. Make that an option so you can get
26:23
them off that sand or get somebody to sign off in writing that it's okay that it's going to take
26:28
you three to six times longer to actually get those restores back
26:33
Okay. Just keep that in mind. Now here's a magic button. And I say this because it truly is a magic button
26:40
And it's so important that it's now the default isolation level inside Azure SQL DB
26:46
So we all have had blocking in our environment, right? Readers block writers and writers block readers
26:52
It's the natural way of things happening. If somebody is trying to do an update or an insert or delete on a row that's being read
26:58
say by a report and a simple select statement, we end up with a blocking scenario. I guarantee you
27:05
if you put in allow snapshot isolation and read committed snapshot isolation
27:10
your blocking is going to go away and you're going to know exactly when this has been put on
27:15
There is lots of blogs out there. Kendra Little has a fantastic blog about these two options. I
27:21
encourage you guys to take a look and look it up. I wish I had the link for me here, but I don't
27:25
unfortunately. But it's RCSI. As soon as you turn this on, readers don't block writers and writers
27:31
don't block readers because it begins to use a version store inside TempDB. And you're able to
27:37
read the versions of these rows while other transactions are still being able to actually
27:43
do what they need to do. And you eliminate blocking almost completely when this scenario occurs
27:49
So if you've got people doing general select statements against a highly OLTP environment or you have reports going against production, things like that, I highly encourage you to take a look at it because it is simply a magic button that will help you get rid of a ton of concurrency or blocking that's happening that is slowing your system down
28:09
So take a look at that. Again, when things start to go default, it's a really big flag that says maybe I should be looking at this for my environment
28:19
So definitely take a look at that. Statistics. Now, my friend Erin Stiletto is up later this afternoon
28:25
and she's going to deep dive into statistics. So I'm going to run this pretty quickly
28:29
But what I really want you to make sure you know is that statistics is the magic sauce, right
28:34
It's how the optimizer gets the information it needs for that estimated amount of rows
28:39
and allows it to make determinations on what kind of data to expect
28:43
and how to figure out what the operators are for you to specifically get your data through
28:48
as fast as possible. Keep these up to date. If you're doing index maintenance, fantastic
28:54
Start thinking about statistic maintenance. How often are you updating your statistics
28:58
Have you deep dived and actually looked to see when was the last time your statistics
29:02
were updated on particular rows? Maybe change this from asynchronous to asynchronous
29:09
When you get an auto statistic update, there's a slight pause in the system
29:12
while those statistics update. If you move it to asynchronous, you will actually halt the statistics update
29:20
You're able to do it when there's an availability for that update to happen
29:25
It makes it a little more smoother and you get a performance game. You can take a look at that and find out more information
29:30
But here's the big one to walk away with. Trace flag 2371. All of us have really large tables
29:36
over a million rows, right? Maybe 10 million rows. Maybe we're into the billion of rows
29:40
Statistics are actually auto updated based on an algorithm in a sliding scale, right
29:45
20% change on that row plus whatever the algorithm actually is, it will actually trigger a statistics update
29:53
Well, if I've got a table with 10 million rows and I don't do a ton of changes to it, but it's enough changes where statistics..
29:59
matter, I'm not going to trigger a statistics update, right? I'm not going to get that auto
30:04
update. But if I had 2371 actually in there, it will change the algorithm to a sliding scale that
30:12
works with larger tables, and you'll get more frequent statistics updates, right? This on top
30:17
of regular maintenance of your statistics is what you really want to try to strive for. So take a
30:22
look at 2371 and see if that works for your environment. There's no downside to that that
30:27
I'm aware of or have ever heard of, but Erin might mention more as she moves along through
30:32
her session. Definitely stick around for that. And did you know you can make your own statistics
30:37
If you as a developer know a field's going to be called all the time or you're going
30:41
to use the data a certain way, take a look and see if it actually benefits from making
30:46
your manual statistic. And you'll notice sometimes inside execution plans that it'll say if you hover over one
30:53
of the operators, it might say missing statistics. Ding, ding, ding. that's a big hint that maybe you should manually create a statistic because it's looking for one
31:01
So just keep that in mind as you move forward. Also, 2371, look at this. It's another default
31:09
in 2016. They actually handled this for you. Again, if something goes default, it's something
31:14
that you probably should look at in your previous versions and see if it's applicable to your
31:19
environment. So let's talk about these two real quick. I've got about 10 minutes. We'll hopefully
31:24
move through this. So max DOP, max degree of parallelism and cost for threshold of parallelism
31:31
These two work in tandem. And if in your environment, these are set to zero and five
31:36
then we need to have a conversation because that means this hasn't been touched. And you've got an
31:40
opportunity to do some performance gains. I want your max DOP to be looked at as eight or below
31:47
depending on how many logical processes you have, just a cut and dry, right? If I've got eight
31:53
logical processors, I usually set my value to four. And then or I can go up to eight, right? So it just
32:00
depends. But I want you to definitely take a look at this number, it shouldn't be set to zero in your
32:04
environment. Keeping in mind, if you're using SharePoint inside SQL Server, that value is going
32:09
to be a maxed off one, and there's nothing you can do about it. That means everything is going serial
32:13
right? When it comes to max degree of parallelism, this is how many threads you're letting the system
32:19
used when it goes parallel, right? When it determines based on the cost for parallelism
32:23
right? That's the second half. When it actually determines that it's hit that cost and it should
32:29
actually thread out to be a parallel operation, you want to tell it how many. So in my case
32:35
I have eight cores. I chose to set my max stop to four. When I go parallel, I actually will say
32:41
four threads are allowed to go parallel. This kind of helps when it comes to CPU allocation
32:46
and it comes to CPU pressure, things like that. I know you guys have seen CX packet weights
32:52
These are normal weights inside SQL Server, but it can actually present a problem
32:56
when you have too many things going parallel or you have too many threads involved in the operation, right
33:02
So you want to deal with this as a DBA You want to look and see what threshold you want to give I like to start out between 35 and 50 for my cost threshold And that says it must hit that threshold before it goes parallel Otherwise it going to run serial right And then you just get the one thread through So definitely take a look at these things You want to make sure your CX packet weights just so everybody understands what that is
33:25
So say I have four threads and the four threads going through that operator is doing that, that operation
33:31
As soon as it's done going parallel, it's got to come back together, right? To move on to the next thing where it's not parallel
33:36
So that is called a CX packet wait. It's waiting for those four to bring that operation back together and move on to the next thing
33:44
That is what you're trying to control as a DBA and making sure you're not going too wide with too many threads or consuming too much resources for that operation
33:53
So remember these two work in tandem. And again, it's so important that in 2019, in the install, it prompts you to actually set these values
34:03
So this is another thing you definitely need to check in your environment. If it's zero for MacStop and five for cost, these have never been set in your environment and you need to do something
34:13
So this next one, again, is outside SQL Server. Let's see if we can make this in time
34:19
Outside SQL Server. It's called IFI, Instant File Initialization. All this does is it's a permission for the SQL server service account that needs to be
34:31
done inside the local policy of the server that you're on. And all you have to do is make sure it performs volume maintenance task is granted
34:40
Now, if you do this in a group policy or however you handle it, you want to make sure this happens
34:46
So what it does is, I know I'm going to get this backwards. I do it every time
34:49
But let's say when you're growing out a file in the file system, it wants to go and write in that file
34:57
I think it's right ones, I think, or zeros. It's either one or zero and I get it backwards every time
35:02
But it's going to write ones all the way through. And I know somebody's going to say it's zeros, but it's going to be zeros
35:09
Let's go with zeros. It's going to write zeros all the way through that file in first as the first step
35:15
right? It's going to zero it out and say, if it's a hundred gigs, it's going to zero out through that
35:20
hundred gigs. But instant file initialization means it doesn't have to go through that process
35:25
and mark that space on that disk. It's just going to instantly give you it. And I'll show you in the
35:31
next screen. This makes a difference when you add files, when you do growth, when you create
35:34
a database, things like that. But look at this screen without IFI set that permission associated
35:42
with it, it took to create a five gig database, 1800 milliseconds, 1800 milliseconds. As soon as
35:50
I turn on IFI, look at that. It's three times less. It only took 600 milliseconds. Okay. So I
35:56
want you to definitely turn this on. There's no downfalls for it. And it's an instant win, right
36:03
So just make sure this permission is actually already set in your environment. There's queries
36:07
you can run that checks for IFI in your system, definitely take a look. And I believe it's
36:12
now part of the install if I remember correctly on later versions It asks if you want it to grant IFI permissions as part of the install So definitely take a look at that in your environment Now indexes
36:28
Indexes is all about maintenance of those, right? Everybody does index maintenance
36:33
you deal with the fragmentation, but it's not really a set it and forget it thing
36:37
I need DBAs to go a step further. I need you quarterly, monthly, at some point in time, yearly
36:43
part of your maintenance, you need to be going through to look for index usage, run scripts
36:49
there are plenty of them out there to determine what seeks, what scans, how many writes are
36:53
happening on these indexes, because you're wasting IO and all of the writes that are happening
37:00
whenever you're actually writing to indexes that are never even used. Okay, you need to look at
37:05
that write overhead and that space usage to maintain those indexes, right? Not just index
37:10
fragmentation and rebuilds and reorgs. Are you using them? Take a step and look for that. Look
37:16
for duplicate indexes. Are you writing things twice? If you don't know exactly what qualifies
37:21
as a duplicate index, there's plenty of blogs out there associated with that. Kimberly Tripp from
37:27
SQL skills has some really great scripts to help you find the duplicates in your environment
37:31
You can actually take the time to export all of your indexes out into an Excel spreadsheet
37:36
simply sort it and you can easily reveal the amount of duplicates that you have in your environment
37:43
and actually see if you can consolidate those. If you have an index that has field A and B
37:49
and it includes C, and then you have an index that is A and B, but it includes D and E
37:56
that's a duplicate index. Those include statements can be consolidated and you're down to one index
38:01
Start thinking about this kind of cleanup in your environment and save yourself some resources
38:06
Look for missing indexes. We all want to right click on a missing index hint
38:11
that happens inside of an execution plan. And we automatically want to create those indexes
38:16
Don't do that, right? Don't do that. Take a step further. Look at what index is asking you
38:22
Check out and see what indexes you already have. See if it's something that you can just add
38:27
another included field to it. See if it's something that really is going to give you
38:30
the gain and worth the right to get that speed for reads, okay
38:34
Double check everything. and make sure the actual key fields are in the right order for what your query is actually asking
38:42
for. Dig into that statement before you create those indexes. Like I said, you want to consolidate
38:47
and you want to have covering indexes, okay? Cover your queries so you don't end up with what we're
38:54
about to see in the next scenario. I've got about three minutes. I think we're going to make it, you guys. Okay, execution plans. If you know nothing about execution plans or tuning
39:02
These are the things I want you to look at at a bare minimum. Index needs, we already talked about that
39:07
Is it missing indexes? Are you getting a scan when you should be doing a seek
39:12
Is there anything more granule you can do with inside that index to get better performance
39:16
and more usage, right? Sorts. Sorts has got a huge overhead inside SQL Server when we doing those sorts Why are you sorting Can you sort in a report and offload all of that overhead Are you a developer that when you actually created that store procedure every
39:36
single select statement that you wrote, you put an order buy in so you could double check your work
39:40
And then when you deployed it, you have five order buys inside that actual proc when you only needed
39:46
one at the very end. Take a look. Sorts are really expensive. Sorts can spill to TempDB and use TempDB
39:53
resources, which gets you to have to go on disk instead of memory. That is a performance hit
40:00
Definitely take a look and see if your sorts can be pulled out if you have too many and what exactly
40:04
you're doing with them. Now, key lookups. I want all you guys to look and see what a key lookup is
40:09
I have a blog on my website if you want to read more, but a key lookup can create death by a
40:14
thousand cuts inside your environment. You can really clean up your performance and clean up
40:19
your CPU issues, lots of different things, and reduce reads based on fixing these key lookups
40:25
And it's not rocket science. So what you do is if you look at this
40:30
it's got a select and nested loop and index seeks are fantastic, but I have this key lookup
40:35
So what happened was, is that it went to that index order lines
40:39
and it said, hey, what a great index. But my select statement had field A and B, right
40:45
It had A and B where C, right? But my index right here only had C indexed
40:53
Everybody following me? I hope you are. So what happens is that key lookup goes back to my primary key
40:59
It takes another trip to the data and pulls A and B field
41:03
because that's what it wants to return in that select statement. I can actually go straight from the seek to that select right there
41:10
and skip that nested loop if my index had included columns of A and B
41:16
That's called a covering index. and I get rid of this key lookup extremely easily
41:21
If I hover over key lookup, it says what the output fields were
41:25
Guess what? It gave you a clue on how to fix it. Take those output fields
41:29
and that is what you're gonna put inside as included columns for that index that was seeked
41:34
Okay, so definitely take a look. Spills to temp DB. We kind of just talked through it
41:38
I do know that Buck is coming up very, very soon. I've got two minutes still to go
41:42
before I hand it off to him. So I want you to just take a look through this
41:46
slide deck. I do have the full slide deck on my website, and I think I might have a video or two
41:53
of when I've given this before. But definitely take a look at all of these inside your environment
41:58
I'm sorry we rushed through today. I know there's a lot of information here we could have dived a
42:02
little bit more into. But take this as a guide, go back to your environment, and really do a
42:08
checklist. Really take a look and see what it is that you can do and how you can gain some
42:15
performance just by these items. And I guarantee you, if you implement these, you're going to see
42:19
big gains right off the bat. These are things that you should be checking every environment for
42:24
and you'll be good to go. All right, Simon, I think I made it on time