MICROSOFT SQL SERVER PERFORMANCE TUNNING

6 comments

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 /  :

  • 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
Advertisements

6 comments on “MICROSOFT SQL SERVER PERFORMANCE TUNNING”

  1. 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
    Keep up
    PL

    Like

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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s