Sql Server Performance Tuning

If you look at any DBA job posting youll see that SQL Server performance tuning is always at the top of the list of required skills

. I think that performance tuning is really one of the most difficult but also most fun things to do as a DBA; difficult because there is a huge list of factors to take into account and fun because its almost like a criminal investigating where you are tracking down clues. So what does it take to get good at it? Ill try to answer this in multiple blog entries which Ill post in the order that I usually approach a performance problem.

Know your OS:

You need to know your operating system really well because the investigation of a performance problem usually starts at the OS level. The first things I usually check are the system and application event logs. In the system log, look out for critical errors relating to hardware such as faulty disks. In the application log, you may see some errors coming from SQL Server that will give you a clue in the right direction.

The next thing I do is look at the performance counters on the server. You really need to know Performance Monitor (or perfmon) well and should take the time figuring out how to set use it. When you start up perfmon, the amount of performance counters available is really overwhelming, the ones I usually start with are:

SQL Server: Buffer Manager Page Life Expectancy

Aka PLE, it is the king of SQL Server performance counters as far as Im concerned. A lot of novices will monitor SQL Servers memory use by looking at the amount of RAM the SQL Server executable (sqlservr.exe) is using. The problem with this is that SQL Server will slowly ramp up its memory usage to the maximum amount it has been configured to use and never release it. So if you didnt know this, you might think that SQL Server had a memory leak. Even if SQL Server is just sitting around doing nothing, it could be that it is consuming most of the memory on the server. PLE allows you to look inside SQL Servers memory buffer pool and see if it has internal memory pressure or not. The counter will tell you how many seconds SQL Server is able to keep a page of data (or 8KB) in memory. If there is memory pressure, then it will constantly need to free pages to read in more data, so the lower this number is, the more internal memory pressure SQL Server has. The generally accepted threshold for PLE is 300 meaning that it should not be below 300 for extended periods.

PhysicalDisk: Avg. Disk Sec/Read and Write:

This is sometimes referred to as disk latency. It measures the amount of time that it takes to write or read data from disk or perform a single I/O. It is good to cross check with the counter Disk: Avg. Disk Bytes/Read and Avg. Disk Bytes/Write because the size of a disk read or write can have a direct impact on the time it takes to do the read or write. . The general recommendations on this counter is that it should be lower than 20 milliseconds. If you are seeing high numbers for this counter then it could mean that your disks are a bottleneck.

Memory: Available Mbytes

This counter shows you how many megabytes of memory are free on the server. Generally speaking it is good to always have more than 1GB of memory free but it really depends on what other programs are running on the server and the total memory that is installed. If this is not the case then it can happen that the OS pages out SQL Servers memory. I didnt used to look at this counter so much until I started getting calls from clients who complained about SQL Server being unresponsive (more on this in my blog post about SQL Server configuration and Build version)

Processor: %Processor Time

If you are seeing extended periods where this counter is above 80% then CPU can be a bottleneck on your server. The first thing to check is whether SQL Server is using the CPU or if it is another process. You can do this by checking the counter Process: % Processor Time for the sqlservr instance. I havent come across this problem nearly as much as I have come across low PLE or high disk latency

I usually start off with the performance counters above and then drill down to other ones. For example, if I see that % Processor Time is high, I will first look if SQL Server is using the CPU and if this is the case I will look at the amount of re-compiles that are being done by SQL Server. The trick in all of this is to know how all the pieces fit together. For example, you may see that the disk latency is really high and think that you have a disk bottleneck. But in fact, what is happening is that the OS has paged out SQL Servers memory to disk and this is causing the heavy load on the disks. To keep the OS from paging out SQL Servers memory, you are better off ensuring that the OS has enough memory available by checking the counter Memory: Available Mbytes. This is where experience really helps.

by: boyetapreston

POST COMMENT

Performance Chips For Mazda Mazda Rx8 Engine Performance & Main Features High Performance Waterproof Boot From Timberland Car Repair Vancouver -enhance The Performance Of Your Car Computer Repair - Peak Performance Is Important Hp C7973a, Lto-3 Tape Satisfies The High Performance Demands Of Ultrium 960 Full Height Drive Cat Back Exhaust - For Boosting The Performance Of An Engine Performance Metrics For Employee Evaluations Managing Supplier Performance Key To Supplier Relationship Management Car Repairs For Smooth Performances Vertical Doors - Style That Trumps Performance Htc Titan: Bigger Package, Better Performance Linking Pay To Performance: Cascading Goals