SQLSQL Server Performance Tuning February 21, 20166058 views0Share Performance Tuning By IG ShareTable of ContentsNote: If you buy something from our links, we might earn a commission. See our disclosure statement. ToggleSQL Server Performance TuningDisks:Performance Monitoring & Analysis: Individual disk latency requirements:Database >15ms, Logs> 2ms Tempdb> 2msFormat drives with 64k Cluster Allocation UnitAntivirus programs:Always Toggle the maximum memory setting on the SQL Server Instance level properties. Ensure at least 2GB – 4GB of RAM is available to the OSStorage Planning for TempDBEnable the Lock Pages in Memory Option (Windows)Max Degree of Parallelism SQL Server Performance TuningSQL Server Performance tuning is a big topic and sometimes it becomes difficult to decipher the problem. So, in this blog topic, I am going to talk about generic steps to ensure, your SQL Server is running optimally. There are various 3rd Party monitoring tools like SQL Sentry, Redgate etc, but today we are only going to discuss about the tools and resources available out of the box for the majority of the SQL Server installations.Disks:Disks are the slowest part of the entire subsystem. In order for SQL Server to perform optimally, monitoring and optimizing the SQL Server Disk sub-system is very important. The Microsoft SQL Server Product team have very specific numbers recommended by them for Optimal Disk Performance.Avg. Disk Sec/Read is the average time, in seconds, of a read of data from the disk. The following list shows ranges of possible values and what the ranges mean:Less than 10 ms – very good Between 10 – 20 ms – okay Between 20 – 50 ms – slow, needs attention Greater than 50 ms – Serious I/O bottleneckPerformance Monitoring & Analysis: You need to use *Performance Monitor* (Start>Run>Perfmon) and use the Data collector to capture the below mentioned counters. [Reference]You can use a time interval of 15 seconds or 30 seconds.Create a Data Collector Set from Performance MonitorSchedule Data Collection in Windows Performance Monitor (per day separate .blg file)Analyze through PAL tool at code plex Link: https://pal.codeplex.com/Here are the performance counters – Processor: % Processor Time Should average below 75% (and preferably below 50%).System: Processor Queue Length Should average below 2 per processor. For example, in a 2-processor machine, it should remain below 4.Memory—Pages/sec Should average below 20 (and preferably below 15).Memory—Available Bytes Should remain above 50 MB.Memory – Free System Page Table EntriesMemory – Pages Input/SecPhysical Disk—% Disk Time Should average below 50%.Physical Disk—Avg. Disk Queue Length Should average below 2 per disk. For example, for an array of 5 disks, this figure should average below 10.Physical Disk—Avg. Disk Reads/sec Used to size the disk and CPU. Should be below 85% of the capacity of the drive.Physical Disk—Avg. Disk Writes/ sec Used to size the disk and CPU. Should be below 85% of the capacity of the drive.Network Interface—Bytes Total/sec Used to size the network bandwidth.SQL Server: Buffer Manager— Buffer Cache Hit Ratio Should exceed 90% (and ideally approach 99%).SQL Server: Buffer Manager—Page Life Expectancy Used to size memory. Should remain above 300 seconds.SQL Server: Buffer Manager Lazy Writes/SecSQL Server: Buffer Manager Checkpoint Pages/SecSQL Server: Buffer Manager Page writes/secSQL Server: General Statistics— User Connections Used to size memory.SQLServer:General Statistics – Logins/secSQLServer:General Statistics – Logouts/secSQL Server: Databases— Transactions/sec Used to size disks and CPU.SQL Server: Databases—Data File(s) Size KB Used to size the disk subsystem.SQL Server: Databases—Percent Log Used to size the disk subsystem.SQLServer:SQL Statistics Batch Requests/SecSQL Server:Latches Average Latch Wait Time (ms)SQL Server:Locks Number of Deadlocks/secSQL Server:Locks Lock Requests/secSQL Server:Locks Average Wait Time (ms)Paging File %UsageDisk Drive Placements:Use separate drives for different purposes. (Log File, Data Files, Backup’s & TempDB)Individual disk latency requirements:Database >15ms, Logs> 2ms Tempdb> 2ms Do not use the default allocation unit when formatting the Disk drives for SQL Server Log files & Data files: Format drives with 64k Cluster Allocation UnitAntivirus programs:These programs can create issues with SQL Server functionality, and it is highly important to exclude them from their scan scope, by adding them to the exclusions list.File types to exclude:*.mdf, *.ndf, *.ldf, *.bakAlways Toggle the maximum memory setting on the SQL Server Instance level properties. Ensure at least 2GB – 4GB of RAM is available to the OSNote: The maximum memory setting is only for the SQL Server buffer pool, and does not include memory requirements for other SQL Server functions like replication Services, Agent Job Service etc. AdStorage Planning for TempDBSet the recovery model of TempDB to SIMPLE. This model automatically reclaims log space to keep space requirements small.Do not allow for TempDB files to automatically grow. This reduces the CPU overhead of managing a dynamic file growth.Have multiple data file for TempDB (Total Number of TempDB Primary Data File = Number of Processors available to SQL)Each data file should be of equal size.Try to keep each data files in separate disk drives for IO Parallelism.TempDB Data and Log files should be kept in faster disk drive (Preferably RAID 1 if possible)Use RAID-10 or SSD Disks.Pre-size TempDB files25% of largest DB size.Set Auto Growth to fixed size < 200 MBYou should have the same number of data files as the number of CPUs up to a maximum of 8.Enable the Lock Pages in Memory Option (Windows)This Windows policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk.Max Degree of Parallelism If you have a multi-core CPU which have a high number of cores (<8), you can change the MAXDOP setting to 1 (Recommended for SharePoint 2010 & 2013, MS CRM). Too man threads can delay the performance of the SQL Server. See image below:This should cover the basic Physical Server performance tuning best practices. Next we would discuss about the various tools available for analyzing SQL Server performance. Affiliate Disclosure: Faceofit.com is a participant in the Amazon Services LLC Associates Program. As an Amazon Associate we earn from qualifying purchases. Share What's your reaction? Excited 0 Happy 0 In Love 0 Not Sure 0 Silly 0
Office 365How to Fix Deleted Reoccurring Meeting Recreating Itself on Outlook 2016 By IGOctober 14, 2017
SQLSystem Center Deployment Requirements and Restrictions for SQL ServerSystem Center contains a suite of products and enterprises who have investments in them deploy ...
SQLHow To License Your SQL Server on Google CloudIn my previous post, we deployed SQL Server 2016 on a Windows Server 2012 R2 Operating ...
Enterprise TechHow to Run Microsoft SQL Server 2016 on Google Cloud Step by StepGoogle Cloud Services recently added Microsoft SQL Server to its offerings. Google’s move is primarily ...
SQLHow to Shrink SQL Server Database Log in Availability Groups using TSQLIn my previous posts, I have already spoken about the best practices of Shrinking Log ...