SQL Server Performance Analysis
PART I: "How To" get down to whatever is bringing SQL to its knees...
Having worked with SQL Server for a few years now, I know that one of the most problematic issues is performance. There are many ways however of analyzing SQL Server performance and pinpointing the bottleneck that is causing the issues.
The main tool used for analyzing performance (through Microsoft Support) is PSSDIAG, but the public version is mostly known as SQLDIAG which comes with SQL Server 2005.
Note: PSSDIAG is still available for download for SQL Server 2000 and SQL Server 7 : https://support.microsoft.com/kb/830232
This tool is a command prompt utility and can be configured to collect the following from the SQL Server:
· Windows Performance Logs
· Windows Event Logs
· SQL Server Profiler Logs
· SQL Server blocking information
· SQL Server Configuration information
The tool is quite simple to use, and there are a few KB articles and MSDN article to look at should you have any issue (some will be posted at the end of this blog) however the important part of all this is the information we get from the data for this is what will tell us what is wrong with our SQL Server.
As it will be most likely the DBA's running this tool, and they know their SQL Servers, I will post this blog however as "an outsider" looking into the server in order to jot down important aspects. This will be a "light approach" but i will try to go as in-depth as possible.
Boot.ini
This file is important in order to see what parameters are placed within. The main parameters that can impact SQL are:
· /3GB
· /PAE
· /USERVA
This will affect the amount of memory attributed to the application side (SQL Server) and how much memory is left for the Kernel. If you have a 16 GB Ram server and you have both /PAE and /3GB - you may (to not say you will) have issues with your server as 1 GB is not enough for Kernel memory, especially since your limiting memory for the PTEs (system page table entries).
Also looking at the parameter(s) enabled in the boot.ini file, and which version of the OS we are using (x64/x86), we will automatically know certain attributes/permissions that need to be given to SQL <describer later>
Note: Windows Server 2008 no longer has boot.ini: https://technet.microsoft.com/en-us/library/cc721886.aspx
More information:
Physical Address Extension - PAE Memory and Windows
https://www.microsoft.com/whdc/system/platform/server/PAE/PAEdrv.mspx
A description of the 4 GB RAM Tuning feature and the Physical Address Extension parameter
https://support.microsoft.com/kb/291988
Using the /Userva switch on Windows Server 2003-based computers that are running Exchange Server
https://support.microsoft.com/kb/810371
Blog Posts:
https://blogs.msdn.com/chadboyd/archive/2007/03/24/pae-and-3gb-and-awe-oh-my.aspx
https://blogs.msdn.com/slavao/archive/2005/01/29/363181.aspx
https://blogs.msdn.com/slavao/Default.aspx?p=3
https://www.eraofdata.com/blog/tag/awe-enabled/
SP_CONFIGURE
This is also an important file to look at as there are quite a few important points to mention here. Once the sp_configure is run with the advanced option set to 1, we can look at all the configuration option of SQL Server.
(I will not discuss all options of SP_CONFIGURE but only those that are mostly misconfigured):
Max Degree of Parallelism:
The value configured under this option indicates how many CPUs will be used when a query passes the (default) 5 second threshold and needs to be run in parallel. 0 indicates all CPUs. Many maintain this value however the recommended value should always be the number of physical cpus (hyper threading needs to be excluded - hence if you have 2 physical cpus and 4 cpus due to hyper threading, the value to work with is still 2) and divide this number by 2. Hence if you have a SQL Server with 4 CPUs (physical), maxdop should be configured to 2.
Links:
max degree of parallelism Option
https://msdn.microsoft.com/en-us/library/ms181007.aspx
Degree of Parallelism
https://msdn.microsoft.com/en-us/library/ms188611.aspx
Blogs:
https://blogs.msdn.com/slavao/comments/492119.aspx
Max Server Memory:
This value is mainly found on most SQL Servers with the default value which equals to all memory on the box. Knowing that SQL Server will attempt to use all memory if needed and if available, it is always wise to restrict it to a certain value. This value is normally:
TOTAL SERVER MEMORY - 2 GB unless you are using /3GB or /USERVA.
Blogs:
https://blogs.msdn.com/slavao/
https://blogs.msdn.com/psssql/archive/2008/03/03/sql-server-working-set-trim-problems-consider.aspx
Priority Boost:
This option should not be on: enabling priority boot on SQL Server can cause issues affecting your performance. It is recommended that this value be turned off.
Blogs:
Max Worker Threads:
In SQL Server 2000 the value setting is 255. In SQL Server 2005, it is 0. These values do not need to be changed as SQL Server pools the threads as needed / used. This does not mean you can only establish (for example) 255 user connections. It mainly means that 255 queries can run concurrently, but this is multiplexed down to the number of available CPUs, so the concurrent nature is only perceived, regardless of the number of configured worker threads.
How to: Configure the Maximum Number of Worker Threads
https://msdn.microsoft.com/en-us/library/ms190219.aspx
max worker threads Option
https://msdn.microsoft.com/en-us/library/ms187024.aspx
Blogs:
https://blogs.msdn.com/khen1234/archive/2005/11/07/489778.aspx
https://blogs.msdn.com/boduff/archive/2008/05/17/configuring-max-worker-threads-in-sql-2005.aspx
Next Blog Post: Part II - SQL Error Logs....