Up next in 10
Everyone needs a list of what to double check in your environment to ensure your servers and databases are optimized in order to make SQL Server run faster, better and more efficiently. In this session, you will learn about what you need to review and implement to get a performance and configuration improvements. You will learn about best practice configuration, SQL Server surface area, memory optimizations, isolation levels, Tempdb and transaction log configuration.
Conference Website: https://globaltechconferences.com/event/women-data-summit-2021/
C# Corner - Community of Software and Data Developers: https://www.c-sharpcorner.com
C# Live - Dev Streaming Destination: https://csharp.live
#Azure #Data #WomenDataSummit #PerformanceTuning
Show More Show Less View Video Transcript
0:00
So thank you everybody for joining me once again here on C Sharp Corner
0:06
This is my performance tuning checklist. I'm going to be passing along a lot of tidbits that I use as a consultant when I'm going into your systems to make sure you have all your best practices aligned and a couple other tips that you can check within your environment
0:21
For those who don't know me, I'm Monica Rathbun. I'm a consultant with Denny Cherry and Associates
0:26
I am known as SQL Espresso inside the database community. You can find me on Twitter at SQL Espresso
0:38
I've got my blog where I talk about many of these things. You can follow up on that at SQL Espresso.com
0:44
I'm very involved in the community. You can hit me up with questions at any point in time
0:48
And you can also find my articles on C Sharp Corner. I share them there as well
0:54
What a great site. Hopefully you guys are taking advantage of that. So my presentation for you guys with this one
1:00
it's usually a 75 to a 90 minute session. I am going to do my best to squeeze it in in an hour
1:06
I will apologize if it's a little fast, but I will do my best to get you guys all the content
1:12
that's available. If not, I do have the presentation slides available on my website
1:18
Usually my presentation rules are in person. Interrupt me, ask questions. Please use the chat
1:25
and ask all the questions that you want. If I'm not able to answer them on camera
1:30
I will answer them in the chat after we finish up. So we're going to start with best practices
1:36
It's always really important to get the foundation of your environment as good as you can get it
1:41
before you really kind of dig into huge tuning in your procedures and other aspects of your
1:48
environment. Best practices is always where we need to start. And this is where we start our
1:53
checklist. We're going to go through these nine items and a couple more. And as we go through
1:58
I would expect and hope that you're going to go back to your environment and look at each and
2:03
every one of these and double check your system to make sure you have done this. This is the
2:08
simplest one I like to start with. And I find many people miss because a lot of us as DBAs
2:13
don't actually touch the Windows configuration. This is something outside of SQL Server that
2:20
makes a huge impact. And that is the power plan, not only on your server itself, but if you are in
2:26
a virtual environment, this would be your VM host, you want to make sure that you are set on high
2:33
performance power plan. Now by default, and lots of Windows admin automatically set your power plan
2:39
on balance. And what that does is it holds back some of that power. And it kind of just revs your
2:45
engine and like keeps it going and it's kind of a balanced thing, you want to make sure, I'm sorry
2:52
it actually revs your engine and then it stops, it revs your engine and then it stops and then revs your engine and stops when it comes to CPU. You actually only end up using 70% of your CPU
3:03
Look at that number. You end up losing 30% of your CPU power by being in the balanced power plan
3:12
mode. If you are in high performance, then that CPU is going to rev and you can actually keep it
3:18
up high and use it. You don't have to have that beginning up and down usage that you're going to
3:24
lose here. There's 70% of the CPU is only used in the balance power plan. So you have a degradation
3:30
in performance by 30% by not having this power plan. This is a huge thing. It's usually out of
3:35
the realm of the DBA, but you need to make sure you're double checking this again on your server
3:40
and on your VM host if you are hosted in a virtual environment
3:46
Very, very important, everyone. This is a big one to get you some more CPU power
3:51
Next, how many of you guys actually inherited a database server where every single service available in the install was chosen
4:01
What happens is each one of these consumes some of your resources on your box
4:07
you want to make sure you are only running services that pertain to your engine on the box
4:13
OK, if you have to share that box with SSIS, SSAS, your reporting services
4:19
each one of those is going to take resources away from your engine
4:23
And that's where I need the most resources that are available on this box
4:27
I don't want to share it. Of course, because of licensing, there are many times that we have to have SSIS, for instance, on the same box
4:34
We're going to take that into account when we do a couple of the configurations like memory to make sure we allocate accordingly
4:40
But if you're on your box right now, please take a look at your services. If you're running all of these services and they're not in use, you're not using it at all, please turn them off
4:50
Make sure you're not only turning them off, but switch it so that it's set to disable
4:55
Some of these will actually turn back on when you reboot your box if it's set to automatic
4:59
and we end up in the scenario again where we have multiple services running
5:03
that we do not need, okay? So take a peek at your services
5:07
and please do not install anything on your box except for the engine when applicable
5:11
because you do not wanna share resources with other things. So next, we're gonna talk about patching
5:19
Everybody talks about patching when it comes to security, right? We want to make sure there's no loopholes
5:24
We're on the latest patch. We don't want anybody actually getting into our system. But as a DBA, we need to go and think beyond that
5:31
We want to talk about performance. Performance is so important. And with every release of SQL Server, we end up with additional CUs or KBs
5:41
things like that that we have to use to patch our boxes. Well, many of the times those changes are all related to performance
5:49
There are bug fixes. There are lots of things that are actually corrected, changed, optimized within each one of these patches
5:56
And if you are not staying on the latest patch, you have performance issues because there are things that are fixed with every single patch
6:03
So please make sure you're on that not only for security, but performance. The best example we have is when SQL Server 2016 RTM came out
6:12
There was some issues with the optimizers that were changed as we went to SP1
6:16
We got much better performance than the optimizer. They made some big changes and we saw that improvement
6:22
So make sure if you're on 2016, you are on SP1 and above
6:26
You're not sitting at RTM. As soon as you make that jump, you're going to get a performance increase just by patching
6:32
There's a blog list here that actually shows all the available builds
6:38
Double check where you are on that patch level and see what you can do about upgrading it
6:42
Microsoft support. Every time you call Microsoft, it costs you a lot of money for them to assist you
6:47
If you are not on the right patch level, they can easily hang up the phone and say
6:51
you are not in support right now. We can't help you. And therefore you wasted your money
6:56
So make sure when it comes to performance and everything, you keep that in mind. If you ever
7:00
have to call Microsoft for support, you have to be on the proper patching level in order for them
7:04
to help you. Okay. Make sure it's one that's supported by them. Things outside the DBA realm
7:09
that we have to think about. Everything touches SQL server, right? We have storage. We have
7:15
controllers, we have the network, we have all of these pieces. You've got to make sure those stay in line with patching as well
7:24
You could have an issue with the latest VM patch Maybe you supposed to be on that Maybe there some issues with some NIC cards All of these other components that play a part of SQL Server also have to have patching
7:35
I actually have run into scenarios where the outside components were not patched
7:40
and that's actually what caused my problems with inside and my latency inside SQL Server
7:45
So take all of those accounts if you would, and think about patching more than just security
7:51
So memory, memory, memory, memory. SQL Server, we want to maximize memory, want to use it
7:58
We want to actually have all of our data, as much data pages in cache and in memory, right, in cache for reuse
8:05
We want to be able to get to our data as fast as possible. So properly allocating our memory to the SQL Server engine helps us from having to go to disk, go to storage
8:15
which could be a bottleneck in getting our data back to our users. So we want to make sure we have this configured correctly
8:22
If you're looking at your box right now and you go to the server properties and look at memory
8:26
and you see this really large number, two, four, one, seven, eight, six, four, seven
8:31
If you're looking at this number, then you have not properly configured your memory
8:37
You wanna make sure you take care of that. That number can easily be set correctly
8:43
for your environment by using, there's a script out there by Jonathan Cahias
8:47
If I had that, I think I have that in my reference that you can run and it'll actually look at and evaluate your system. There's some math that go
8:55
along with this. And because we don't have time, I'm not going to go into that math. But it'll actually look and tell you how much you should be allocating to your engine. There's two pieces to
9:03
this. And depending on your services, a third piece, but two pieces is, is that you want to
9:07
make sure you have enough memory allocated to your operating system. And then the rest of the memory
9:12
allocated to SQL Server or whatever services that are still out there. So let's take an example. Say
9:19
I have 32 gigs on my box. Maybe I actually want to go ahead and leave four gigs to the OS
9:25
And then the rest, the 28 gigs would be my max memory setting here. That means four gigs is going
9:31
to be able to be used by the server itself. And then the rest is going to be allocated to SQL
9:36
server. When it reaches that 28 gig limit, it won't use anymore. Okay, so that's kind of how
9:42
it works. Now, guess what? Some of us have multiple instances, right? If you have multiple instances
9:47
on your box, you need to make sure you haven't over allocated that 32 gigs. Okay. If I have two
9:53
and I run Jonathan script, or I go ahead and put 28 gigs in one instance, and I put 28 gigs in
9:59
another, guess what? I just over allocated my memory. So if you have multiple instances, I
10:04
I really want you to go back and check it, add up the amount on each server, each instance of total
10:11
memory that you've allocated with inside your engine properties here. Okay. Make sure you have
10:16
not over allocated and you leave enough for your Windows box. So you do not starve it and get a ton
10:21
of low Windows memory errors. Okay. Next, when we have those extra services, did you know, and I
10:27
catch this a lot when I'm teaching this in person, that SSIS, SSAS, SSRS, all of those extra services
10:33
that are running do not share this max memory number that you have set here. That number is
10:39
solely for the engine. So if I have put the 28 gigs here as my max memory setting and I've left
10:45
four gigs to the OS, guess what? Those four gigs are actually being shared with SSIS, SSRS, SSAS
10:52
Have you ever gotten a lot of memory alerts or had SSIS actually be running some imports and it
10:59
runs really, really slow or you end up with some performance issues on your Windows box while those
11:05
are running? It's probably because it doesn't have enough memory and you've actually starved it
11:09
Make sure you allocate enough to these services as well that it's going to share with the OS
11:15
So maybe in this instance, I should put 24 gigs for my max memory here, which will allow more available for these services and my OS to run
11:24
OK, I do have locked pages and memory noted here. I'm not going to go into it because we don't have time
11:30
I want you to research those terms. Find out if it's applicable to be turned on in your environment
11:35
There's some good things and bad things about it. It's not something that you should turn on automatically
11:39
So please take a look and just know you should know about that. Also, if you are running in a VM environment and you are on a host, there is this thing called a balloon driver that you need to know about
11:50
What happens is on a host, you share that host with several different VMs
11:55
If at one point your another VM needs more memory or it needs some more CPU or it needs some more resources available for it to run its load, even short term
12:06
the host will allocate and pull some memory or CPU or whatever it is from another VM
12:14
So it can use it to do what it needs to do and then might give it back. Guess what
12:18
When it comes to SQL Server, I'm stingy. I don't want that balloon driver to touch my box
12:23
So make sure when you have that set up on a VM that when I ask for my 32 gigs
12:29
I'm going to ask my admin to set a memory reservation. And what that means is that the balloon driver is not allowed to touch my memory
12:39
Okay. It cannot borrow from my memory. It cannot reallocate to any other VM on there
12:44
It can't touch it. That's very important. I've run into several over allocated VMs where it's actually stealing resources from my SQL server
12:52
And I don't want that to happen. It's a very simple checkbox. Set the memory reservation for the total amount that you need for your server
12:58
Please go back and double check that. Next. Depthie went really in depth on this. So I'm going to go over it a little quick. If you didn't watch
13:08
her session, I'm going to touch just the best practices on this. You can go back and watch her
13:12
session later. But I'm going to remind you that one, it should be pinned to the highest disk
13:17
possible. You want TempDB to run as fast as possible. Put it on SSDs, put it on its own disk
13:23
Don't share it with any other things. So you don't end up with a storage bottleneck or any
13:28
other kind of throttling that could happen on that disk. Okay. Put it separately. Multiple files
13:33
She talked about that. It's all about contention. You want it to actually be able to round robin
13:38
through all of those S gam pages, the gam pages, all of that. You want it to round robin through
13:43
all of your files. So when you're actually setting up your SQL server, if you're in 2016 or later
13:49
as you go through that GUI, it starts to give you the option for multiple files. That's how
13:54
important it is. Okay, we want to make sure you're doing that when you do that, you want to make sure
13:59
they're sized exactly the same. What happens is, is guess what? So look at this one, all of these
14:05
if you go to your environment, we'll start again, if we go to your environment, and you have one of
14:09
your files larger than any of the other, all of your workload is going to go there. Because guess
14:15
what, it has the most space SQL Server thinks, hey, my file one is 25 gigs right now. And my file
14:22
two, three and four or only 10. I'm going to throw all of my workload on file one. Then we end up with
14:28
contention that she talked about. You don't want that. You want to make sure all your files stay
14:32
in line. You have multiple files, like she reiterated one per CPU core up to eight files is a good start
14:40
When you go over eight, it starts to get a little bit of throttling going on. Too many files can give
14:44
too many choices and you end up with a lot of a lot of other issues that can come along with this
14:49
But again multiple files keep them all the same size make the auto growth all exactly the same size You want them all to grow in tandem which is where that trace flag 1118 and 1117 and that pre come into play to make sure that happens If you go into
15:09
your environment and you have one file larger than the other, you have contention, you have
15:13
performance issues, and you need to fix that. Make sure they're all in line, okay? We talked about
15:19
making sure you're up on your patching. She talked about this as well. There was a metadata fix
15:24
There was some contention going on behind the scenes that Microsoft found and fixed for all of us
15:30
When you go to 2016 CU8, 2017 CU5, if you are on those CU levels or above, you don't have to worry about this contention
15:40
This is something that we were all experiencing and was easily corrected on a patch
15:46
So keep these kind of things in mind when you're coming to performance. It's a good example of that
15:51
These are, again, the items that I want you to look at in TempDB. We're not going to go way into this because she already covered this in the last topic
15:57
But here's another one. Trace flag 2453. Turn it on. Write it down
16:02
Make sure you go back to your environment and turn it on. So what this does is if you're using table variables, that's the at table
16:10
When the SQL server goes and creates an execution plan, you'll note that it actually thinks there's an estimated number of rows of one
16:19
It will allocate your memory. it will allocate needed resources based on a row count of one. Okay. But what happens is because it
16:26
only thinks one row comes in, it doesn't grant enough memory and it doesn't grant enough
16:30
resources to move your larger number of rows through. And you get a bad execution plan. You
16:36
may have bad joins. You may have other bad operators that it chooses as well as not enough
16:41
memory to make all of the decisions that needs to do and move those records along. 2453 does a
16:48
pre-compile and we'll actually use the actual number of rows to grant all of your resources
16:54
So your stuff goes much, much, much faster. In 2019, they fixed this with table variable deferred
17:01
compilation. I'm not going to go into that, but if you are not on 2019, trace bag 2453 is a must
17:06
inside your system. Okay. Next. Here's a couple other settings that are now done as defaulted
17:15
prompts in 2019, anytime something becomes defaulted or actually gets set in your setup
17:22
GUI inside SQL Server, you probably want to take note and do these things in lower environments
17:28
The max DOP is one of the max degree of parallelism and it's tandem partner, I call
17:33
the cost for threshold of parallelism. So what this does, it's all about how many threads are
17:39
going to be allocated when it's working through a node or operator inside your execution plan to
17:44
move your data along. In your max DOP, what you like to set this variable at, if it's sitting at
17:50
zero, then you haven't changed it. If your cost is sitting at five, you haven't changed it. These
17:56
are items you need to change. So what this does is tells the server how many threads I can give
18:03
for whatever operator it is. So you want to make sure you set that correctly and you don't over
18:07
allocate threads or you're going to end up with some things called SOS scheduler yield or CX
18:12
packet weights, there's things that actually happen with these two pieces. The max degree of
18:18
parallelism, if you have eight cores or less, let's talk about that as an example. So say I have an
18:23
eight core box. I like to say I'm going to give it half of those. So I will set my max DOP to four
18:29
I can set it up to eight, but I usually try to go with four. So what happens is of those eight
18:34
I'm allowed four for that operator to move that data along and go parallel. Okay. So what happens
18:41
from there is it says, hey, let me look at the cost. If it's set at five, again, you haven't
18:46
tweaked this. A good number to start is 35 to 50. If my cost of that operator is above 50
18:51
then I'm going to allow it to go parallel, which means in this case, I'm going to allow it to use
18:55
four threads to do what it needs to do. And then it's got to bring those four threads back together
19:00
before it goes on to the next operator. Hopefully you guys are following what I'm talking about
19:05
So what happens when it says, okay, I've got four threads, I'm going to move to the next operator
19:10
when it comes back together and brings the four threads back to the next operator
19:15
it's going to create what's called a CX packet weight. A CX packet weight is bringing those four together before it can move on
19:22
So if you see those CX packet weights, maybe it's not definite, but maybe you should look and see what your cost is
19:28
Do I need to bump that number up? Do I need to bump that number down? Take a look
19:33
If you're going to change your max DOP and allow it to do more threads
19:37
then remember that when you make this change, don't do it live in production because it will flush your procedure cash
19:44
If you do that, you're just going to get a performance hit as all of those procs get back into
19:49
cash for reuse. So just keep that in mind, but take a look at these two numbers
19:54
If you have too many cores is look at this. It says
19:58
it says if it has late eight logical cores, you want to do eight or less
20:03
right? When you start to go above eight, there are instances where you might have 32 cores on your box and you might
20:08
want to do 16, but the more cores you allow, more threads you allow to use
20:13
the more CX packet wait time you're going to have because you got to bring them all together
20:17
and you can end up with some throttling or some overhead on that. But just keep in mind
20:21
max DOP, my rule of thumb is half of my cores up to the total number of cores
20:27
There's some things with NUMA that I always get questions on. I'm not going to go into that
20:30
You can do some research on that but I try to do half and then cost again
20:35
35 to 50 is what you want to start at and then move on from there. Next
20:45
This one, this one is another one that I catch all the time and it's a big one
20:50
It's another thing that's added inside the default setup when you go to 2019
20:57
and it's instant file initialization. It is outside of the SQL Server DBAs realm
21:02
so it's a conversation you should have with your server admins, and all this is is a permission and it's so important
21:08
And I'm about to show you why it's so important. So if you look at your local policy here inside your SQL server, you need to make sure under local policy that your SQL server service account
21:21
whatever is running SQL server, has the rights to perform volume maintenance tasks
21:26
Or if you have an AD group policy, make sure it's there. What this allows you to do is when you create a file, you restore a file, you add a data file, you have an auto growth event
21:37
you create a database on the disk. It actually has to zero out the space it's going to need to do
21:44
its operation, add the log, do whatever growth it is, create a new database. It kind of zeros
21:50
that out on the disk. And then it starts to write your information. That actually takes a lot of
21:55
time depending on how big of the file or growth event that occurs. Instant file initialization
22:01
IFI, will actually skip that step. As soon as I need to write to that, I need to grow or whatever
22:06
needs to do, bam, I can do it. I can skip the zeroing out operation
22:10
If you don't have this, it's possible that your restores are taking a long time
22:15
You're creating a database can take a long time. Your restarts, because tempDB has to grow
22:21
and be created, can actually take a long time. Let look at this example here And make sure I on the right one I may have to go back one slide There we go Let look at this So here an example of without IFI
22:35
it's really, really simple. I am creating a five gigabyte database just to
22:39
create database and have it set to five gigs. Without instant file initialization
22:45
it is zeroing it out and it is taking me 1800 milliseconds
22:49
As soon as I turn on instant file initialization and I create that five gig database
22:54
Look, it's three times as fast. So imagine if your tempDB is set up with multiple files, each one 25 gigs
23:02
When it first starts up and it has to create each one of those files of 25 gigs
23:06
that can actually slow your restart process. If instant file initialization is there, that goes much faster
23:13
And you'll see you can actually restart your server faster. You can do your auto growth events faster
23:17
You can do your restores faster as well. So keep that in mind
23:21
That's another setting you should be checking on your box. So next, if it goes, I'm clicking here
23:32
We'll go past this. Sorry about that. I'm running on three different screens here
23:37
Okay, we are back on track. So let's talk about blocking. All of us have experienced blocking
23:45
And many of us, I hope I didn't get everybody dizzy with that. Screen's going back and forth
23:49
I apologize. So blocking, everybody has blocking. It is normal throughout SQL server
23:54
It's supposed to happen. Readers block writers and writers block readers. But there are options available to us
24:01
And I call this the magic button inside SQL server. It is called RCSI or Read Committed Snapshot Isolation
24:11
And what this does is this actually creates row versions, a version store inside tempdb
24:17
Notice I just said inside tempdb. First thing you should be making sure is that your tempdb is set and configured correctly before you enable this
24:25
OK, because it actually takes more space inside tempdb. It needs to go fast and we want to make sure we've set up for that first
24:32
OK. All right. So what this is, is it allows it to create a row version inside tempdb
24:38
So your readers can go to the road version while the writers can actually still write at the same time
24:45
So readers don't block writers and writers no longer block readers. I can tell you there's two options
24:50
allow snapshot isolation and read committed snapshot isolation. Both of these play a part
24:56
It is on a database level, and it is the default isolation level in Azure SQL DB
25:03
So if something's defaulted, which I talked about earlier, if something's defaulted
25:07
it's usually something that you probably should turn on. I call this the magic button because I can tell you exactly
25:13
when you've experienced a ton of blocking when this has been turned on because all the blocking disappears
25:18
when all the, I should say, all the normal blocking disappears. Your readers blocking writers and writers blocking readers, okay
25:25
Because it will actually go to the version store and allow these transactions to happen
25:29
still maintaining your asset and will allow your readers to actually get the data that it needs
25:36
Now, I have a friend, her name is Kendra Little. Kendra Little has a fantastic blog on this
25:42
So you can take a look. I do have the link to it as my final slide here and I can get that to you
25:47
But between the two options, allow snapshot isolation allows it to read the version store and read committed snapshot isolation creates the version store
25:55
So definitely take a look at those. Read deeper into it. We don't have a lot of time for that today
26:02
But definitely take a look at this option. If you have reports that are reading from production, if you have select statements being done by your application on production
26:12
as well as doing inserts, update, deletes, which we all do. you probably can actually take a big performance gain by turning this on
26:21
And again, if its isolation level is defaulted in Azure SQL DB
26:25
this is definitely something you should be looking at in your environment. I have seen huge, massive performance gains and reductions in blocking just by turning this on
26:38
Okay, virtual log files. This is something that not everybody knows about. I wish I had an hour to talk about them
26:43
it has to do with your log file, your transaction log. I do have several blogs on this so you can
26:49
take a look. But in general, I want you to go, there is several queries on this. I do have one
26:55
on my blog that you can do. I want you to go and look at all of your databases and see how many VLF
27:01
virtual log files you have in your environment. If you have a virtual log file count under 300
27:07
you're probably in a really good state. If it's over 300, and I have seen hundreds of thousands
27:13
especially inside tempDB be created, then you have an issue and you have a performance issue
27:18
If you're looking at this picture here, it's very, very simple. And I'm just going to
27:23
give you a high level of how this works is inside your transaction log, you're going to have several
27:28
different segments, virtual log files. These virtual log files are created every time you
27:33
have a growth event, there's math involved for each several megs that you get created several
27:40
different virtual log files can be added to your log file. We'll show it like buckets. If you think
27:46
of these things as buckets, when I have a transaction that needs to be written to the log
27:50
file, it finds a segment inside this bucket to write and do its work. So it does that round robin
27:55
thing through all of these virtual log files. You can imagine if I have hundreds of thousands of
28:00
log files, if I have a thousand log files, that's a lot of buckets it has to go through as it's going
28:06
through our transaction log, right? We want to minimize that full trip around finding those
28:11
empty buckets and where it's supposed to start. Okay. So if you've got larger than 300, then how
28:16
do you fix it? One, you've got to shrink your log file down and then you need to regrow it. You need
28:22
to set its default growth at whatever's ideal for you. Maybe I used to have it set at a hundred megs
28:29
Maybe it needs to be a gig. If I have it set as a gig, then I'm going to have less log file
28:34
less virtual log files. If I set my auto growth, if my auto growth is sent to five megs, I'm going
28:41
to get a ton of VLFs every time it needs to grow. If I set it to 250, I'm going to have less. So you
28:48
really need to look at what your auto growth setting is. Please do not set it as a percent
28:53
You need to have it as a number that makes sense for your environment and how much data you're
28:57
pushing through in your environment. So if I have it at a larger number, then I'm going to have less
29:03
virtual log files in my system. So take a look, run the script that I have on my site
29:09
or you have the DBCC log info, or in 2016 SP2, they have a new DMV that you can pull to pull
29:16
through log stats and get those counts. If your counts are high, you need to make a correction
29:21
because this is actually a big performance hit that you get on your log file by not having these
29:27
set. This is something that you should be looking at probably quarterly, like you do maybe your
29:32
index maintenance, things like that. This should be one of the things you look at and try to keep in track
29:36
Okay. There's tons of information out there. Again, I have a blog on it with a script you can use to check your environment as well
29:45
So ugly babies, if you've seen me talk before, I try to talk about this every time because it's so important to performance
29:52
Make friends, please make friends with your network admin, your storage admins because performance is reliant on
29:59
other components outside of your SQL server engine. We all know we have that best configured
30:06
as possible, but maybe the network is not configured correctly. Maybe the storage
30:10
is not configured correctly. There's lots of things that are outside our realm
30:15
in the hands of other admins that actually make a performance impact for us
30:21
So I like to say, keep your admins drunk, fat, and happy, right? If you come into a new environment
30:26
Maybe you need to buy donuts for the network group, or maybe you need to take the head network admin out for pizza or something
30:36
before you ask them to make changes to their VM and reserve the memory that I told you you need to do
30:42
There's lots of things that you need to consider when you are talking performance inside a SQL server
30:49
A lot of things having to do with storage. Storage happens to be one of the worst bottlenecks for SQL server
30:55
If it's not configured correctly, if you're not on the fastest disk, if they have not allocated things to you correctly, which we will talk about here in a minute, that's an ugly baby scenario
31:05
And we've all run into issues where somebody's come to us and blamed the DBA for a performance issue
31:11
It's always the SQL server, always, always the SQL server. That's an issue, right
31:16
Well, it's very easy for us to run different metrics in our environment
31:20
We have lots of tools out there to show what the latency is in our environment and where that latency is coming from
31:26
We can look at weight stats. SOS scheduler yields can be a throttling thing that happens on a VM when it comes to comes to the CPU allocations
31:37
Right. There's lots of things we can actually give numbers that can prove these other items are a problem
31:42
So make sure you're working with your admins because you're going to be calling their babies ugly, letting them know what's wrong in their system
31:49
and you need them to fix it, okay? So make sure it's a huge thing
31:53
when it comes to performance. Some of the ugly babies I just kind of mentioned
31:57
is on your VM side, right? When you're in a virtual environment
32:01
you're sharing that host with several different other VMs. You don't have purview
32:07
You can't see into how they've set up that host. Luckily, if you've got a nice admin
32:11
and you've actually bought him or her pizza and taking them out for lunch and ask them
32:17
Hey, can we give me read-only view to see what's going on in my host, right
32:25
You'll be able to identify things as too many. Maybe you have too many VMs on one host
32:31
They're using that balloon driver and it's stealing resources from you. You'll be able to see that
32:37
What happens on one VM can really mess up another. Maybe they are moving you from one host to another and you don't know this
32:45
Maybe they shift you daily. And that happens to coincide with data imports that you're doing
32:50
And you find out that that's the problem. They're actually moving you from one host to another for whatever maintenance reason
32:57
And that's causing a problem. These are the things you need to look at
33:01
Over allocation of CPUs. How do you know how much CPUs have been allocated on that host
33:08
For example, I had a really large retail client that went down. Their whole system went down
33:13
And if you recall, there was that patching that happened for security, that Spectre meltdown patching that happened
33:20
What actually occurred was we had a box that CPU was through the roof
33:28
It was at 100%. It was unresponsive. We couldn't do anything. I found out it was actually on a host
33:34
And they said, we haven't touched that host. There's no reason why it has an issue. Come to find out, the night before, they installed the VM and Spectre patches
33:42
and it took away the hyper-threading. If you don't know what hyper-threading is
33:47
if I have 32 cores on my box and it's hyper-threaded, then I really have 64 cores, threads
33:55
that they are able to allocate for all of their VMs. Well, guess what
34:00
When they put those patches in, it took away hyper-threading. So I was back to the 32 cores for that entire VM
34:08
Looking at what they did, they actually allocated 96. 96 cores, 96 CPUs, 96 virtual CPUs to the VMs that were on that host. They completely over allocated
34:24
So once they put that patching in, we had the 32 put to the 96. They were all trying to share and
34:31
get to those 32 threads. And we couldn't see, we couldn't get it fixed because this had happened
34:36
I didn't know this as a DBA. I happened to ask the right questions. I was seeing the SOS scheduler
34:42
yield weight and my CPU ready time on the VM host was very, very low. So I knew that there was an
34:51
issue on the host. These are things that you can run into. The other thing, thin provisioning
34:57
What's thin provisioning? So let's say I go to my admin. This is a storage admin. And I say
35:02
hey, I'm going to create a new temp DB. I need 500 gigs of space on a T drive because I want to
35:09
put it on a separate drive. They said, Monica, no way are you going to use 500 gigs for a temp DB
35:15
I said, yes, I am. I'm going to go ahead and create those. And I want 500 gigs. He said, okay
35:21
I will give you 500 gigs. He gives me a T drive. And on the T drive, I see 500 gigs. But what they
35:26
really did is they thin provisioned my drive space. What that means is maybe it only allocated 25 gigs
35:35
And then when I need more, it will give me another 25 and then another 25 and another 25. And it will
35:40
actually grow to give me the full 500 over time. Well, every time that growth event happens, I get
35:46
a performance hit inside SQL server. So when I ask for that 500 gigs in the beginning, I need to ask
35:52
for 500 gigs thick provisioned, thick provisioned. That means I want all 500 gigs at one time
35:59
I don't want it thin provisions where I get segments as it grows and gives me that
36:04
If they decide that no, they are not gonna do that, then what am I gonna do? I'm gonna go around them and I'm gonna go ahead
36:10
and set my default sizes on TempDB to 100 gigs a piece
36:14
or 125 gigs a piece. And I'm gonna take all 500 gigs at one time
36:18
That way I don't incur a performance issue when those growth events happens. So have the conversations with them. There is a thick
36:27
provision, which is what you want. Thin provision is the sneaky little thing that they'll try to do
36:31
and only give you a little bit of space okay So have those conversations Make sure you getting it thick provisioned The next thing I want to talk about is compression Compression is a great thing It allows me to actually compress the data on a page
36:47
which allows me to get more data on that page, which means what? More data into memory
36:51
The more data into memory I can do, the more quicker I can get my data
36:57
and get that data back to my users or my application. We want to keep as much data in memory as possible
37:04
So we want to go ahead and compress page compression and row compression
37:10
Page compression is actually going to compress everything on that page. Row compression is going to take care of the extra space you might have on a row
37:17
So if you have Varchar 100, for instance, and you're only using 25 out of that
37:24
it would compress and bring down the empty space and get that all taken care of
37:29
and take it away for where it doesn't need it. OK, so that's the difference between page and row compression in a small summary here
37:38
OK, so we want to actually definitely use that. It saves space on a disk and it actually will cost you a little more in CPU speed
37:46
But what you get back is totally worth it if I can get that data back faster
37:51
Now, here's where the ugly baby comes in. A backup compression, backup compression
37:57
Backups are so important, but the most important part is the restore
38:01
right? The restore. You want to be able to restore your data as fast as possible and as efficiently
38:08
as possible, especially when you're in a scenario that brings the entire environment down. What
38:13
comes into play here is when your storage admin comes to you and say, hey, Monica, you're not
38:18
allowed to use compression. If your storage admin comes to you and say you're not allowed to use
38:23
compression, you need to have a conversation. The reason why they're telling you you're not
38:28
allowed to use compression is because they are doing compression and de-duping on the sand side
38:35
What that does is allows them to store less data on the sand, get more space out of the sand
38:40
and it uses things called pointers to actually point to the data and not bring over duplicate
38:46
data. So the actual duplicate data becomes a pointer to what it needs. So it actually
38:52
saves them a ton of space. But what happens for you as a DBA? When I actually have a backup that's
38:59
going to a deduped storage array, the disk, then I have a restore that now has a ton of pointers
39:07
What happens when I actually have to restore that data? All of those pointers have to come
39:12
back together. All that data has to be rehydrated into its form before I can restore
39:18
So we've had scenarios where I have a native SQL backup. I've stored it on a deduped storage array because I wasn't allowed to compress it
39:30
And they've started doing the compression on the storage array. It took 24 hours to restore that data
39:37
Now, if I had a native SQL backup saved to the cloud or saved someplace else that I compressed
39:44
that was not on a deduped SAN, it took one third of the time, one third of the time. So if I have
39:51
a downtime scenario, I would rather take two hours to do a restore than 12 hours to do a restore
39:58
Okay. Or, or whatever it is math wise, but you get the point. If I have to restore more in a
40:04
critical downtime, I do not want to have all that deduplicated data rehydrated before I can do my
40:10
restore. So my point is, if your SAN admin says you cannot compress your data, or please don't
40:17
compress your backups, or you can no longer do that, or that they are deduplicating the data and
40:22
doing the compression on the SAN side, you need to have a conversation with them and make sure
40:27
that you are putting your backups elsewhere into the cloud or on something that's not deduped. So
40:33
you can do that restore properly. If they say you cannot do that, then I want you to do a test
40:38
restore your data with the deduped restore and restore your data with a
40:45
your regular native backup and show them the time difference. If you can get somebody to sign off on that in writing and that's okay with
40:52
them, fine, but definitely take the time to get somebody to know that before you actually
40:57
say, okay, and don't do the compression on your side. Okay. It's really important. So let's talk about this statistics
41:05
Statistics is the magic sauce of everything. Every query that you put through, the optimizer needs to know how many rows to expect, your estimated versus actual rows
41:16
We've talked about this several times. We all know this. The closer in sync the estimated rows to the actual rows are within your environment, the better
41:25
The better the optimizer can choose which joins to do, what memory grant to give
41:30
And your actual query performance can be much improved. Well, you want to make sure you keep these up to date
41:37
It's your job as a DBA to do that. I recommend not only doing your index maintenance, but statistics maintenance
41:44
Make sure that you're running a job, even if it's update statistics all. There's several different things
41:49
My friend Erin Stiletto has a fantastic session on this. I highly recommend her blog when it comes to statistics
41:56
Keep these up to date. Make sure you're running the statistics updates daily, nightly, whatever it is, depending
42:02
on how often your data changes. OK, on a database level, there is also a update statistics
42:08
If you're using the auto statistics, which most of us are and set by default, there are two options you have available
42:15
There is asynchronous and synchronous. I highly recommend you move this to asynchronous
42:20
which means my data can continue to be read while my statistics are updated when it has availability to update
42:28
When it gets an auto statistic update, you've hit the threshold of rows changed
42:32
it wants to give you a new statistic and generate that new statistic
42:36
But when that happens, things kind of pause and a wait is created in the background for
42:41
that statistic to update. You can circumvent that wait by changing it to asynchronous, which will allow your data
42:47
load to happen. And then your update stats will wait. And then it will run when it has the availability to do so
42:54
It's a small little tweak. I do have a blog on this as well. Take a look at that and make the change if it's applicable to you
43:01
If you're dealing with large tables, this is another trace flag you need to write down
43:05
It's 2371 and it is defaulted in 2016. Remember I said if it defaulted you probably want to take note If you below 2016 you need to turn on 2371 And what that allows it to do is it changes the algorithm of how statistics updates happen Those auto statistics are important
43:25
If I've got a table that has millions and millions of rows, and it needs a 20% change
43:30
plus whatever the algorithm says, how many rows it needs to do before it triggers an auto stats
43:35
update, you could be waiting a really long time and your statistics can get stale, which means
43:40
your estimated versus actual rows can get way out of sync. We don't want that to happen. 24, I'm sorry
43:46
2371 changes that to a sliding scale depending on how big your table is and will change that
43:52
algorithm which will trigger that auto statistics updates quicker. So definitely put that down
43:58
There's no adverse effect to this. I haven't found any. I don't know of any. And again, it's defaulted
44:04
in 2016 and higher. So pay attention to it. Those developers out there, if you're missing us
44:09
if you're looking at your execution plans, it could actually tell you there's no statistics
44:14
If it tells you it's no statistics, then you probably want to make a statistic
44:18
on the column it's looking for. And or if you know that you're gonna be using
44:23
a particular field or a multi-field combination when you're doing your reads
44:27
maybe adding a statistic and giving the optimizer a little more information about those rows
44:32
can be helpful to your performance for that query. So take a look at that
44:38
You can actually right click on your database, create statistic and create your own statistics
44:42
Something a lot of people do not do, but can do. Indexes. I'm going to take a drink real quick
44:47
I think we have 20 minutes left. So indexes. Indexes are so important
44:53
We all love indexes. They're magic. We talk about in where I work in Denny Cherry and Associates
44:59
We say indexes are magic and we actually have stickers for it because they are magic
45:04
Everybody wants to add an index to make things go faster. But making sure you have the proper indexes and you are maintaining the health of your current indexes is what really matters
45:15
You want to make sure you are using your IO on your system effectively
45:19
If you are actually writing to indexes that you're not using, if you have duplicate indexes out there and you're writing multiple times that you shouldn't be
45:27
you need to actually make sure as a DBA that you are not doing that and you are properly using your
45:33
IO and not wasting it. So I want you to go and script out all of your indexes. There's plenty
45:39
of queries out there to do so. SQL skills has some queries. I have some queries. We all do
45:44
script out all of your indexes. I want you to put them into Excel. I want you to sort them
45:49
in quarter and column order and included column order and go through all of them. It's not rocket
45:54
science and see which ones are duplicated. Check and see if you have the same index and the only
45:59
thing that's different is the included columns are different. That's an easy consolidation
46:04
Bringing that to one index will save you IO and it actually gives the optimizer better choices
46:09
for it to actually have the proper plan for you. So go through quarterly at least. Look at all your
46:16
indexes. You can actually run scripts that tells you seeks versus scans and updates. If I have an
46:22
index that has very little seeks, lots of scans, maybe I need to look and see if that index is the
46:28
proper order. What if I have zero seeks, zero scans, and a ton of updates? Guess what? That
46:34
index is not being used. Maybe I need to script that off, disable it, and get rid of it. I will
46:40
save an IO. Now that leads me to missing indexes. Missing indexes. Everybody's seen an execution
46:46
plan where it actually says you're missing an index and will tell you how much improvement that
46:51
has. Guess what? Please don't do that. I want you to go ahead and look at the query you're running
46:57
and look at the index that it's actually told you to do. First step when it does that is to
47:03
script out that index and look and see if you have an index that already exists. Lots of times
47:08
all you have to do is add an additional included column and you fix your problem. If I actually
47:13
scripted it and executed it and created that missing index, I end up in a duplicate index
47:17
situation. I wasted IO. Don't do that. Also, the way it gives you the missing indexes is it will
47:24
actually put them in table order. So I need to know my query enough to know what order to put
47:29
those fields in. Don't just right click and add these indexes because you're going to end up with
47:33
duplicates and wasted IO. I can't say that enough. Secondly, let's talk about covering indexes
47:39
What is a covering index? A covering index is I am covering everything in my select, my joins
47:45
my where's, my group by's. I've got it all in one index. So therefore, I can actually stay away from
47:51
these things called key lookups or RID lookups, which I'm going to talk about here in a minute
47:56
I want to make sure that I cover my queries that are run multiple times. Maybe I have a query that
48:02
runs hundreds of thousands of times an hour. I want to make sure I have the most efficient
48:06
index possible and I have the included columns that I need. I have a lot of people ask me
48:12
Should I include if it asks that missing index, remember I scripted it out first before I actually run it
48:18
If it tells me to include all the columns in my table, I might want to take a step back and see what exactly is my query doing and do
48:26
I need to actually have all of those columns? Probably not. There's a possibility that you might, but probably not
48:35
So take a look at those first. Again, when I'm talking about all of this
48:39
It's very, very important for you as a DBA to know what you're creating and executing when it comes to indexes
48:45
OK, so let's go to the next thing. If it will go multiple screens don't always work, everybody
48:57
OK, so here's my final one. Simon, I think we still have we've finished at 1150
49:03
I'm going to make sure that we don't go too over for you because I don't want to impede on Kathy
49:10
because I know she has a great session later. But when it comes to execution plans, this is what we
49:15
care about. A lot of us as a performance tuning know we need to go to the execution plan level
49:21
to really see what's going on by the optimizer and how it has chosen to run our data load
49:27
Some of us are new at performance tuning. We may not know what a nested loop is. We may not know
49:32
what a merge join is. We don't know what to do with each one of these operators
49:37
and how to make things faster. So I'm giving you a little bit
49:42
of a checklist of what to do when it comes to execution plans
49:50
Double check your needs, your index needs which we just talked about Sorts Why are you sorting Sorts are the highest used thing Sorry Sorts are the most expensive thing inside of an execution plan Why are you
50:09
sorting? Have you written a store procedure? A store procedure that goes select, order by
50:14
select, order by, select, order by as you've done your development. um so what i want you to do when you're doing your your writing and your development work
50:24
is if you're going to test and you're going to sort as you do your development make sure you
50:28
remove those order buys from any statement that you don't need before you move to production
50:33
if you can sort at the very end one time that's what you want to do because again these are very
50:39
very expensive things to do guess what if you're using that store procedure for a report and they're
50:45
actually going to be sorting in the report anyways, why are you sorting inside SQL Server
50:49
Why are you having this query do the sort for you when they were going to resort it several
50:54
different ways in the report? Take that sort out. You'll see that things run much faster because
50:59
sorting, one, if it's not able to sort in memory, it might sort and spill to temp DB
51:04
And or again, it is the most expensive thing you're going to see inside an execution plan
51:09
usually. So ask the question, what's happening with the data? Is the application sorting it
51:13
is a report sorting it and see if it can be done elsewhere. Key lookups. Now, key lookups
51:20
everybody has them in your system and I want you to know where they are in your environment. Key lookups can be death by a thousand cuts. I can go into a system, clean up all the key lookups
51:29
and improve their performance drastically. It can be like a brand new box by just getting rid of
51:35
these key lookups. You'll note here in the picture, I've got a query. It goes against order lines
51:41
And all I'm doing is I'm selecting column A, B, and C where column C equals one
51:48
Okay, let's say that. So I have a column C index. This actual order lines, this seek is happening on column C
51:56
It's in my where clause. It knows exactly where to go to get the data. A seek is great
52:00
That's what you want. But it does a key lookup to grab column A and B
52:05
Column A and B was part of my select statement. So it goes and finds C
52:09
then it goes back to my actual primary key. You can see that here. Goes to my primary key
52:14
finds a match, and then brings all the other columns along A and B. If I hovered over key
52:20
lookup, it will say output list A and B. So now I've got A, B, and C using these two items. It
52:26
brings it to a nested loop to bring it back together before it goes to my select statement
52:31
So what did I do? Instead of just going from a seek to a select, I've made an extra read
52:36
over to my primary key to take the extra column I have in my select statement. I can fix this
52:43
really, really easily. This might happen. Maybe I have a store procedure that I have 10 key lookups
52:48
in. Well, that store procedure is run 10,000 times an hour. You can see how all of those extra trips
52:53
to my primary key can cause a ton of overhead and can slow things down. Easy fix. I highlight over
53:00
the key lookup operator. It tells me the output list. I go and I modify that order lines index
53:08
that's used in the seek right above it. All I have to do is add the included column A and B
53:14
Again, that's included column A and B. And guess what's going to happen? It covers my index. It
53:20
covers my statement, my query statement. And I get just a seek and a select and that's it
53:24
So I've saved a ton of IO, a ton of round robins out to my primary key to get extra data by covering my select statement in with included columns
53:36
And I get rid of these key lookups. The death by a thousand cuts can happen really, really, really easily in your environment
53:43
And there's an easy way to find key lookups. Again, Jonathan Cahias has a fantastic, fantastic blog and script that you can run to actually get all the key lookups in your environment
53:54
and start to tackle those. I have a blog that links to his, that it's actually my number one blog
54:00
is what's a key lookup where you can actually find out in your system
54:04
what key lookups you have and easily fix those and give yourself a huge performance thing
54:09
Let's talk about TempDB one more time. Everybody knows what a spill to TempDB is
54:15
What a spill to TempDB is, is it did not grant enough memory
54:19
for me to do whatever operation that had to happen. my estimated versus actuals were really skewed in my row counts. And so therefore it didn't give
54:29
me enough memory to do whatever operation it is. So it spilled to disk, spilled to temp DB because
54:35
temp DB is not in memory. It's in disk. So it spilled to temp DB to do its workload. Okay. So
54:41
when I go to buy temp DB, it's on disk. Guess what? My disk could be a bottleneck. I want to
54:46
stay away from this. So you don't want to have a spill to temp DB. How do I fix it? One, I make
54:52
sure my statistics are up to date so my estimated versus actuals are correct. That's the number one
54:57
thing. There's several things coming in 2019, memory grant feedback, stuff like that that
55:04
actually helps us stay away from these spilled attempt DBs. But take a look, do some research on
55:09
this, know what they are, know what you need to do to avoid those. You want to stay away from your
55:14
bottleneck, which is your disk and temp DB. All right. So that's everything. I tried really hard
55:23
to give you guys this whole session. I know I really went fast, but I want to make sure you
55:28
guys got all the information you need. My blog is here. I'm on Twitter. You can send me any
55:33
questions you have about anything in this checklist. I do have some reference links here
55:38
that we can copy these links out. Sorry about that. We can copy these links out for you
55:44
and I'll make sure I have them and put them into the chat for you guys
55:49
All of these is what I talked about. How much memory, that's how you get your max memory
55:54
My what is a key lookup. Let's talk about compression. Remember that async versus sync update statistics
56:01
I wanna make sure you guys have that. And remember the magic button I like to talk about
56:06
which is that RCSI, which will get rid of a lot of your blocking. So here's all the references
56:10
I will make sure you have it. Thank you so much for coming. I'm going to turn it back over to Simon
56:17
I know Kathy is here. I'm very excited to watch her session. She has a really great one
56:22
She's a fantastic speaker. Thanks again, C Sharp, for having me
#Arts & Entertainment