SQL Performance Tuning

Where should we start? You should monitor the system. Try to isolate the problems. Is it only one bad query, or is it only one peace of functionality ... or may be a general problem that is going on? Hardware issue or software issue? So, what is causing the high pressure?

What are the hardware components that are restricting performance. We all know that:

• Disk I/O
• CPU
• Memory
  
• Network

How do we measure the disk subsystem:

• Use physical disk instead of logical disk.
• The sec/transfer <>
• The transfers/sec <120>
• The current disk queue length < (2* #disks)
• Disk bytes/sec < (10 MB/sec per disk)

Then about some RAID configurations. Writing is slow in RAID5. Off course, it depends on the number of disks, but in general, it's too slow. RAID10 is best. Good speed, and very good write speed. Therefor, this is what is recommended:

	RAID 1	RAID 5	RAID 10
DB files	avoid as generally not enough drives	acceptiable if low percentage of writes (which is seldom the case in NAV, so let's try to avoid!)	best performance
Log file	General Recommendation	avoid because of high cost of write I/O	best performance and use if RAID 1 shows pressure
Tempdb	General Recommendation	avoid because of high cost of write I/O	best performance and use if RAID 1 shows pressure
Master / MSDB		General Recommendation

Now, what could be causing the I/O cost on the disks? This could be caused by memory pressure, or excessive paging (also due to too low memory) or poorly designed queries (scans on large tables, missing key indexes, ...), high write I/O's to a RAID 5 volume, high usage during peak times, ... . So it could be hardware, or software.

Next, how do we measure the CPU?

• % processor time
  
• % privileged time
  
• Processor queue length
  
• Context switches/sec
  

So, what causes CPU bottelnecks? Compiles/recompiles of execution plans, hash joins, aggregate functions, data sorting, disk I/O activity (paging), other applications/services, screen savers, ... .

What about the memory?

• Set to dynamically allocate or raise max
• Increase physical RAM
• Evaluate high read count queries
• /3GB switch in boot.ini
• /PAE switch in boot.ini + AWE enabled in SQL. If you don't put AWE in SQL, SQL won't use the extra RAM that /PAE makes available. So remember that!

Operating systems based on Microsoft Windows NT technologies have always provided applications with a flat 32-bit virtual address space that describes 4 gigabytes (GB) of virtual memory. The address space is usually split so that 2 GB of address space is directly accessible to the application and the other 2 GB is only accessible to the Windows executive software.

The virtual address space of processes and applications is still limited to 2 GB, unless the /3GB switch is used in the Boot.ini file. The following example shows how to add the /3GB parameter in the Boot.ini file to enable application memory tuning:

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(2)\WINNT
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINNT="????" /3GB

No APIs are required to support application memory tuning. However, it would be ineffective to automatically provide every application with a 3-GB address space.

Executables that can use the 3-GB address space are required to have the bit IMAGE_FILE_LARGE_ADDRESS_AWARE set in their image header. If you are the developer of the executable, you can specify a linker flag (/LARGEADDRESSAWARE).

To set this bit, you must use Microsoft Visual Studio Version 6.0 or later and the Editbin.exe utility, which has the ability to modify the image header (/LARGEADDRESSAWARE) flag

However, Windows 2000 Advanced Server supports 8 GB of physical RAM and Windows 2000 Datacenter Server supports 32 GB of physical RAM using the PAE feature of the IA-32 processor family, beginning with Intel Pentium Pro and later.

Physical Address Extension. PAE is an Intel-provided memory address extension that enables support of up to 64 GB of physical memory for applications running on most 32-bit (IA-32) Intel Pentium Pro and later platforms. Support for PAE is provided under Windows 2000 and 32-bit versions of Windows XP and Windows Server 2003. 64-bit versions of Windows do not support PAE.

PAE allows the most recent IA-32 processors to expand the number of bits that can be used to address physical memory from 32 bits to 36 bits through support in the host operating system for applications using the Address Windowing Extensions (AWE) application programming interface (API)

Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL Server allows SQL Server 2000 to utilize more than 4 GB memory. Without the /PAE switch SQL Server can only utilize up to 3 GB of memory.

Note To allow AWE to use the memory range above 16 GB on Windows 2000 Data Center, make sure that the /3GB switch is not in the Boot.ini file. If the /3GB switch is in the Boot.ini file, Windows 2000 may not be able to address any memory above 16 GB correctly.

When you allocate SQL Server AWE memory on a 32 GB system, Windows 2000 may require at least 1 GB memory to manage AWE.

Example

The following example shows how to enable AWE and configure a limit of 6 GB for the max server memory option:

sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO      




SQL Server specific counters in Performance Monitor: 

• Missing indexes: full scans/sec
• Blocking:
  • Total latch wait time (ms)
  • Lock timeouts/sec
  • Lock wait time (ms) - this is the counter Chad uses the most.
  • Number of deadlocks/sec
• Miscellaneous
  • User connections
  • Batch requests/sec
  • SQL re-compilations/sec (lower is better)

These should be the top three performance issues

• High disk latency
  • Wrong RAID
  • Queries with high I/O costs
• Execution plan recompiles
  • Query design
• High blocking lengths
  • Long transactions
  • Excessive locking