>>Hi, I'm Michael Otey.
I'm a SolarWinds contributor and president of TECA Inc.
and in this quick webcast, we're going to talk about
avoiding some of the common SQL server performance problems.
When you need look at performance in SQL server,
there is really, well, on the hardware side,
three main pillars that go into SQL server performance
and that is CPU, memory and the storage.
But these things work together
and they will have a big impact on performance for sure,
and we'll look at some of the ways they do that
but there are other areas as well.
Certainly, one of the key points that many people neglect
are the system power and BIOS settings.
We'll look at how you would set those
for maximum SQL server performance,
and then we'll dig into a few
of the most important SQL server configuration settings.
The configuration for TEMPDB can have
a big impact on performance.
We'll also look at some of the best practices for some
of the common SQL server configuration settings.
So, when you talk about hardware performance,
with the CPU faster is almost always better
but it's also to remember that today,
processor performance and speed is kind of plateaued out.
And nowadays, Intel and AMD,
they are increasing CPU performance by adding cores
but more cores don't solve every problem.
For instance, if your applications are single threaded,
they're only gonna use a single core.
Of course, the SQL server database engine itself
is multi-threaded so it can take advantage of multiple cores
but it's also important to realize especially
if you're running in a server consolidation environment
where you have a lot of virtual SQL server systems,
it's important not to overcommit the physical CPUs.
At least if you're concerned about performance,
you always wanna have CPU power available
for those virtual machines that are running.
And with memory, memory is one of the biggest factor
in a SQL server performance, that's for sure.
But more memory isn't always a silver bullet.
If you have more memory in your system
that will help a memory constrained system
without any doubts at all.
However, more memory won't fix poorly written queries,
it won't fix locking and latching kind of wait,
so more memory isn't always the solution.
But when you're checking into memory,
there's a few performance counters
you should have a look at.
First, there is the memory available counter.
This shows you how much memory is available for use
in your system, if you have some low values here
that could indicate that your system needs more memory.
Next is the memory paging per second
and that indicates how many pages are written to disk
due to faulting.
Here, a high value will indicate a lack of memory.
And then there's the buffer cache hit ratio.
SQL server uses the buffer cache to store results
of queries in and it will go to that buffer cache
before it goes to disk,
meaning it doesn't have to perform I/O
for a lot of operations.
This is critical for performance.
You definitely want your buffer cache hit ratio
to be very high, certainly above 90% is a good value.
If you have a lower value, that could definitely indicate
that SQL server can't grow the buffer cache
as big as it needs to.
In today's system, storage is often the biggest bottleneck
because you have high powered CPUs and systems
with lots of memory.
Some of today's server systems can have
up to 24 terabytes of RAM, that's an incredible amount.
So storage sometimes, especially if you're connected
to an older storage subsystem, it can be the bottleneck.
A couple of counters to look at when you're checking
out storage is your physical disk object,
and looking at the disk queue length.
This indicates how many I/O operations
are queued and waiting.
Typically, you want this value to be low,
certainly under two.
And then there's another one,
the physical disk object averages disks reads
and writes per second.
You definitely want these values to be very low.
Values of under 10 milliseconds are usually pretty good.
Values of higher than 20 milliseconds
could indicate a storage problem.
If you're using flash storage, you'll typically see this
often times below two milliseconds.
Now, I talked a little bit about BIOS settings.
One thing most servers come with the power settings
set to balanced.
You don't want that in a SQL server system,
you should definitely check out your hardware BIOS settings,
and then make sure you're using the high performance setting
and then Windows server itself also has a power setting
that you should check into.
And this is on the control panel,
go into hardware, power options.
By default, this is also set to balanced,
you should change this to high performance.
Then, let's talk about
a few SQL server configuration settings.
First, TEMPDB, this is sort of a work database
that's used for SQL server for joins
and a lot of other operations that it has to take care of
in the background.
Oftentimes, if you are experiencing page latch I/O delays,
it could be because you don't have
enough data files in TEMPDB.
TEMPDB was originally by default
a setup to use one data file.
Nowadays, a recommended best practice is just plain
to set this to about eight.
And the new SQL server 2016, 2017 setup,
it will take care of this for you
by suggesting more data files.
Best practice, unless of course you're on flash storage
and that can change things,
but consider separating TEMPDB from your other databases
and also consider putting TEMPDB onto flash storage.
The I/O patterns in TEMPDB can benefit from flash storage.
Finally, there's a few SQL server configuration settings
that you definitely should look into
that can impact performance quite a bit.
Maximum memory setting, by default SQL server
takes all the memory in the system.
Best practice is to set this to about 90%
of your total system memory, leaving about 10%% percent
or four gigabytes free.
Next, there's the maximum degree of parallel settings.
By default this is zero which allows SQL server
to use all the available cores during execution.
A best practice nowadays is typically to set this
to the number of cores you might have in a single CPU.
So for instance, if you have a dual socket system
where each socket has eight cores in it,
you might set this value to eight.
The cost threshold for parallelism,
this is how SQL server decides when it starts to split tasks
into multiple threads.
This has been set this way forever.
The default value is five which is really pretty low.
A best practice is to increase this setting
to about 25 to 50 depending on the type of workloads
that you're having.
OLTP can benefit from more values
where OLAP can benefit from higher values.
And finally, there's a Windows permission
that can make a difference when SQL server wants
to auto grow databases and/or create new databases,
and this is instant file initiation,
initialization I should say.
And you should set this by granting a SQL server
the perform volume maintenance tasks.
This is set also optionally in the SQL Server 2016
and 2017 setup process.
Anyway, let's have a quick look at these.
So, here's a SQL server VM that's running.
Let's bring up SQL server management studio,
and let's just dig into some of the server properties.
And we'll find some of these settings.
So first, let's look at memory,
and here you can see the memory is set
at the default setting.
This is a four gig system, so we're gonna set it in
at 90% of the system memory,
which is 3686.
And then we're going to pop down and look at processors.
There's nothing to change there, security connections,
we're going to leave that at the default, database settings.
Here, we probably should go in since this was
just newly built, it's using all the default locations
but you should definitely go in and adjust these
to run on different drives.
Certainly separating the data in log files
for at least HDD systems,
that would definitely be a good idea.
Then let's go into the advanced settings,
and as we go through, we can see
looking at cost threshold for parallelism,
that was one of them.
A good idea is to change this from somewhere to
from up to five to 25 to 50.
And we'll just take the more conservative approach
right here, and maximum degree of parallelism,
this is a system with the four cores per processor
so we'll set it at four.
So, that's our system settings.
And let's not forget our system power settings.
So let's go ahead and we'll open up control panel.
And from control panel, we'll go to system
and we'll go to a hardware,
and then power options.
And see here, the setting is set at the default
which is balanced, we do not want that.
We're gonna go ahead and set it to high performance,
and there you go.
Let's have a quick look at some of these settings using DPA
which makes it pretty easy to see
where our performance weights are coming from.
And here you can see we've fired off a workload,
and that workload has been running for a little while.
And you can see we have a couple of warnings
coming up right away, one in query, one in CPU.
So let's first go ahead and drill into our CPU warning,
and you can see that the utilization here is pretty high.
We're running at about 88% and that's pretty high.
So a little bit higher than we might want to look at,
and if we go back, we can look at our memory utilization.
Remember, there's a couple of counters out there
that we said were interesting.
So we can drop down, hit memory,
and you can see that our memory utilization
is not too bad, we're up at 75%.
That's not too bad, we're not having a memory problem.
Our buffer cache hit ratio is nearly at 100,
and our buffer cache has been growing steadily.
So we don't really have a memory issue here.
So, if we go back and we drill down into some of the queries
that are running and we look at the query weight condition,
here you can see that we are having one query in particular.
This one, if we hover over it, this purchase query
and it is taking almost all the time on our system.
So let's drill into that one.
And here you can see that our biggest weight
is in the write log.
So right now, we can tell that CPU,
we could use a little more CPU power, memory looks okay.
But our write log looks like we should move
the transaction log to a faster drive
and that would really help us in this situation.
And that's the end of this presentation,
thank you for watching.