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 / :
- 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 ).
- 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 ).
- 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/ .
6 comments on “MICROSOFT SQL SERVER PERFORMANCE TUNNING”
és o maior
Thanks for the time and effort you put into your blog and detailed information you offer! I will bookmark your blog now. Thumbs up!
Paulo, gostei do blog.
Só dois reparos aqui, quanto ao CPU, já deixou de ser um verdadeiro “dogma”, e voltou a estar em aberto a regra do “try & buy” ou seja, experimentar para ver, se estamos a falar de um WS2008 R2. _http://sqlblog.com/blogs/joe_chang/archive/2010/03/23/hyper-threading-comments.aspx
Quanto á TempDB, só acrescentaria que com mais de 16 data files começa a ser mais penalizador face aos ganhos que se obtém ou seja, 1 data file por core, até esse limite, a partir daí o SQL não tira partido.
_http://msdn.microsoft.com/en-us/library/ms175527.aspx e _http://technet.microsoft.com/en-us/library/cc966545.aspx
My 2 cents
When you consider “consider formatting the DATA drives in 64KB blocks”, I’m supposing you are talking about a High Reading System. Am I Correct? Bescause if we are talking about a High Transaction System, the biggest data block may generate high contention.
Hello Paulo, it is a general and known recommendation to format DATA drives with an allocation unit of 64k.