Microsoft SQL Server Best Practices - Part 5

Part 5 – Administration Best Practices

Do not run SSMS on the SQL Server

It is an industry best practice to not run your SQL Server Management Studio (SSMS) sessions on the same server that is running your SQL Server Database Engine. The reason for this is so that server resources are not tied up by Administrator sessions running on the server. Many administrators will log directly on to the SQL Server utilizing a console session to launch SSMS and consume server memory that is better consumed by the SQL Database Engine. Some administrators will leave their console sessions open and consuming server resources for extended periods of time and not all servers are configured to automatically shut down idle console sessions. To help with this best practice Microsoft separated the install of SSMS from the SQL Server installation starting with SQL version 2016. Having to download SSMS separately to install it will help remind you that you should not be installing it directly on the server unless you absolutely have to. 

Enable Remote Dedicated Administrator Connection -1

SQL Server includes a Dedicated Administrator Connection (DAC) which is enabled by default. This connection reserves a thread for use in emergencies so that you can connect to SQL and run queries when critical performance issues are occurring. While it is enabled by default it only accepts connections which originate from the SQL Server so in situations where performance issues prevent logging in to a console session on the SQL Server you should enable the ability to connect Remotely. 

Don't Shrink Database Files

It is an industry best practice to not regularly shrink your database files. There is a time and place for shrinking a database file and that should be done on an as-needed basis. If you regularly shrink the files, then they will get fragmented as they grow again. Best performance in SQL is achieved if the data and log files are sized appropriately and allowed to clean up and re-use space in the internal file structure as needed. There is no need to shrink the files unless you have deleted a significant amount of data that will not be reloaded to the database, or you are fixing VLFs in the log file.

Monitor Performance

It is an industry best practice to monitor the performance of your SQL server for a variety of reasons. You can configure alerts to let you know when certain counters go over or under a specified threshold so that you can quickly resolve or prevent issues. You can record performance over time to determine when workload has changed on the SQL server to help with capacity planning, to help determine when maintenance is needed due to performance changes or to help identify bottlenecks and perform root cause analysis of SQL issues. You can utilize the OS level Performance Counters listed below or you can utilize the internal SQL Dynamic Management Views (DMVs) to collect much of the same information.

Here is my list of industry recommended SQL specific Performance Counters that should be monitored:

•    System – Processor Queue Length

◦   If the Processor Queue Length is averaging 1 or higher (except during the full backup window) then tasks are waiting on CPUs to be available and could indicate a CPU bottleneck.

•    Processor – % Processor Time (for each individual processor)

◦   Instead of using the _Total counter it is better to see how each individual processor is performing. A single processor could be maxed indicating an issue with a single process that needs to be tuned.

•    Paging File – % Usage

◦   If the Paging File % Usage is averaging 1% or more then the server is swapping out memory to disk. To resolve, determine if SQL can be set to use less memory or add additional physical memory. Also enabling Lock Pages in Memory can keep SQL data from being paged out to disk.

•    Memory – Available Mbytes

◦   If the Memory Available Mbytes has fluctuations of several hundred megabytes or more then memory is being adjusted dynamically or other applications are being run. Correlate fluctuations with disk activity to determine what might be occurring. If this counter drops below 100 megabytes, then that indicates the server OS is getting low on memory. To resolve determine if SQL can be set to use less memory or add additional physical memory.

•    Physical Disk – Avg. Disk sec/Read

•    Physical Disk – Avg. Disk sec/Write

◦   If there is no page file usage and no memory issues then review disks for I/O latency. Avg. Disk sec/Read and Write shows the latency of each type of operation. Latency should always be evaluated in tandem with SQL wait statistics. According to industry best practices SQL Server disk write latency should generally be 20 ms or less. Reads may be a problem if they exceed 50-100 ms. Read latency should generally be 15 ms or less on the Data file volumes, 10 ms or less on the Transaction Log file volumes and 5 ms or less on the TempDB file volume(s).

•    Physical Disk – Disk Reads/sec

•    Physical Disk – Disk Writes/sec

◦   Disk Reads and Writes/sec represent the average of the total number of individual disk I/O requests completed over a period of one second during the capture interval. This counter can be used to determine the workload of disk I/O on the server.

•    SQLServer: Buffer Manager – Page life expectancy

◦   Page life expectancy can indicate if SQL has enough memory available to perform optimally. Lower values during regular operations suggest a need to install more memory. A realistic value for expected Page life can be determined by performing this equation (DataCacheSizeInGB/4GB *300). This value is used as a baseline but realistically memory does not need to be added until it goes below 180 seconds for a sustained period of time.

◦   DataCacheSizeinGB can be determined by running the query below and adding up the total used for all databases. This value will grow in size as the SQL memory buffer is used and should be run after the buffer has time to fill.

▪   SELECT count(*)*8/1024/1024 AS 'Data Cache Size(GB)' ,CASE database_id WHEN 32767 THEN 'RESOURCEDB' ELSE db_name(database_id) END AS 'DatabaseName' FROM sys.dm_os_buffer_descriptors GROUP BY db_name(database_id) ,database_id ORDER BY 'Data Cache Size(GB)' DESC

•    SQLServer: General Statistics – User Connections

◦   The user connections counter identifies the number of different users that are connected to SQL Server at the time the sample was taken. Watching this counter over time can provide understanding for baseline user connection numbers. Once high and low water marks during normal usage of the system have been determined, times when this counter exceeds the high and low marks can be identified. If the value of this counter goes down and the load on the system is the same, then there could be a bottleneck that is not allowing the server to handle the normal load. Keep in mind though that this counter value might go down just because less people are using the SQL Server instance. When connection pooling is utilized, this number may not indicate the actual number of users connected to the system.

•    SQLServer: Memory Manager – Memory Grants Pending

◦   This value shows the total number of SQL server processes that are waiting for workspace in memory. The recommended value should be 0. To resolve first look into query tuning and workload optimization before determining if SQL can be set to use more memory or adding additional physical memory.

•    SQLServer: SQL Statistics – Batch Requests/sec

◦   Batch Requests/Sec measures the number of batches SQL Server is receiving per second. This counter is a good indicator of how much activity is being processed by the SQL instance. The higher the number, the more queries are being executed. Like many counters, there is no single number that can be used universally to indicate the server is too busy. This counter should be reviewed over time to determine a baseline number for the environment.

•    SQLServer: SQL Statistics – Compilations/sec

◦   The SQL Compilations/Sec measure the number of times SQL Server compiles an execution plan per second. Compiling an execution plan is a resource-intensive operation. Compilations/Sec should be compared with the number of Batch Requests/Sec to get an indication of whether or not complications might be hurting performance. To do that, divide the number of batch requests by the number of compiles per second to provide a ratio of the number of batches executed per compile. Ideally there should be one compile per every 10 batch requests.

•    SQLServer: SQL Statistics – Recompilations/sec

◦   When the execution plan is invalidated due to some significant event, SQL Server will re-compile it. The Re-compilations/Sec counter measures the number of times a re-compile event was triggered per second. Re-compiles, like compiles, are expensive operations and should be minimized. Ideally this counter should be less than 10% of the number of Compilations/Sec.

•    TempDB Monitoring:

◦   SQLServer: Databases \ Log File(s) Size (KB) \ TempDB

▪   The Log File(s) Size represents the total size of the log file(s) and can be used to track log file growth over time. You can monitor this size for the TempDB to catch rapid growth of the file that could utilize all the space on a volume and cause SQL to crash.

◦   SQLServer: Databases \ Log File(s) Used (KB) \ TempDB

▪   The Log File(s) Used represents the used space within a log file. You can monitor this usage for the TempDB to catch rapid growth of the usage within the log file that could cause growth and affect performance or lead to utilizing all the space on a volume.

◦   SQLServer: Transactions \ Free Space in tempdb (KB)

▪   The Free Space in TempDB represents how much free space the TempDB files have and can be used to monitor TempDB usage and growth over time.

◦   SQLServer: Transactions \ Version Store Size (KB)

▪   The Version Store Size represents the size of the stale data stored in the TempDB Version Store when using isolation levels and can be used to identify when long running queries are occurring.

◦   SQLServer: Transactions \ Version Generation Rate (KB/s)

▪   The Version Generation Rate shows the amount of data being added to the Version Store and can be used to determine how fast the Version Store is growing.

◦   SQLServer: Transactions \ Version Cleanup Rate (KB/s)

▪   The Version Cleanup Rate shows the amount of data being cleaned up in the Version Store and can be used to determine if the Version Store is not being cleaned up due to long running queries or locking and blocking.

References:

1 Remote admin connections Server Configuration Option - SQL Server | Microsoft Docs