Share via


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

https://blogs.msdn.com/sqlprogrammability/archive/2007/01/20/trouble-shooting-query-performance-issues-related-to-plan-cache-in-sql-2005-rtm-and-sp1.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/slavao/archive/2006/11/13/q-a-does-sql-server-always-respond-to-memory-pressure.aspx

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:

https://blogs.msdn.com/sqlpfe/archive/2009/01/09/boost-priority-sp-configure-option-and-sql-server-failover-clustering.aspx

 

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....