MICROSOFT SQL SERVER PERFORMANCE TUNNING

I’m writing this article to help everyone who whats “more juice” from a MS SQL SERVER INFRASTRUCTURE .

Instead of writing lots of articles i will update this post every week. I will mix lots of concepts in this post, ranging from Physical Infrastructure/ Operating System/ SQL SERVER PARAMETERS / T-SQL .

I will write a more detailed article related with SQL SERVER PERFORMANCE TUNING, but for now, i will just suggest the following tips for a better SQL SERVER ARCHITECTURE /  :

• CPU
  • DISABLE HYPERTHREADING – This will give better performance during high workloads, you can read this great article from Slava Oks’s ( http://blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx ).

• WINDOWS
  • Configure the NIC (Network Interface Card) for “MAXIMIZE DATA THROUGHPUT FOR NETWORK APPLICATIONS”  instead of “MAXIMIZE DATA THROUGHPUT FOR FILESHARING“, this will give an overall boost of 10-20 % .
  • Don’t forget that windows server is configured as a file-server out-of-the-box !
  • Configure Windows Server to favor BACKGROUND SERVICES and PROGRAMS !

• STORAGE INFRASTRUCTURE
  • Use different volumes for SYSTEM & PAGEFILE (KEEP A FIXED SIZE FOR SWAP).
  • Avoid RAID-5 ,  use and abuse from RAID-10, I understand that this is impossible mos of the timesw, because of the costs that are related with this type of configuration, performance has a price, and this one is quite expensive.
  • Change the registry key HKLM\CurrentControlSet\ Control\FileSystem\ContigFileAllocSize to 64, this will ajust the minimum contiguous file allocation to 64KB.
  • USE MULTIPLE LUNs / FILEGROUPS / DATAFILES.
  • USE DISKPAR to align LUN partition with the underlying disk clusters.
  • ENABLE MPIO – Multi-Path IO .
  • FORMAT the DATA Drives with NTFS 64kb block size ( SQL Server pages are 8192 bytes, so the default NTFS block size (4096) reads only ½ a page and effectively doubles the number of I/O operations. Also consider formatting the DATA drives in 64KB blocks, since SQL Server commonly does an eight page read-ahead to improve performance)
  • FORMAT LOG Drives with default block size ( 4kb ).

• ANTIVIRUS
  • Make sure the antivirus doesn’t scan SQL SERVER related processes and *mdf/*ldf/*ndf*/*mdb .

• SQL SERVER PARAMETERS
  • Pre-Allocate memory for SQL SERVER .
  • Consider changing the lock table for more than 5000 minimum locks ( default ) if the application makes many lock requests, this is particularly true in Biztalk Environments . A simple way to achieve this magic number, is to monitor the lock requests / second and give more 15 % than the maximum value observed .
  • Optimize TEMPDB, create one datafile for each processor core and put it on a separate LUNs.
  • Consider reducing MAXDOP ( MAX DEGREE OF PARALLELISM ) if you having lots of CX-PACKET WAITS.
  
  
• SQL SERVER PARTITIONING
  • This is a great feature available only in Enterprise Edition and is a great for VLBD, you can read an article that i wrote for sql-server-performance.com ( http://www.sql-server-performance.com/articles/reporting/Data_Warehouse_Partition_Strategies_p1.aspx ), there is also a portuguese version of it in https://netic.wordpress.com/2008/05/05/microsoft-sql-server-2005-partitioning/ .