SQL 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 the tools and resources available out of the box for the majority of the SQL Server installations.
Disks are the slowest part of the entire subsystem. For SQL Server to perform optimally, monitoring and optimizing the SQL Server Disk sub-system is crucial. The Microsoft SQL Server Product team have precise 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 bottleneck
Here are the performance counters –
Disk Drive Placements:
Use separate drives for different purposes. (Log File, Data Files, Backup’s & TempDB)
Do not use the default allocation unit when formatting the Disk drives for SQL Server Log files & Data files:
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, *.bak
Always Toggle the maximum memory setting on the SQL Server Instance level properties. Ensure at least 2GB – 4GB of RAM is available to the OS
Note: 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.
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.
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 many 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 the various tools available for analyzing SQL Server performance.
Comments are closed.