Sluggish applications? Frustrated users? When last did you review your application database server?
“We recently upgraded our company-wide ERP system, utilizing up-to-date hardware along with each of the software constituents being upgraded. The previous system was only a couple of years old, with our vendors strongly advising on upgrading as this would address numerous system limitations, but most importantly our performance issues. With less than 2 months post go-live, the system continues to underperform, with a general slowdown being reported across the board. Our budget has been exhausted and we appear to be in a worse position than had we not upgraded. What now?”
Sound all too familiar? Fear not, there is light at the end of the tunnel. Many organizations invest copious amounts of money and time in undertaking system modifications and upgrades, yet omit to ensure that on-going basic maintenance and management thereof is adequately addressed, especially in relation to their database servers. For many applications, Microsoft SQL Server is the database platform of choice, with SQL Server delivering performance in line with that of a high performance sports car. Overtime if not adequately maintained, the efficiency starts to degrade.
This is where Open Box Software can assist. To get you started, here are a handful of recommendations (in no particular order) that are commonly not observed when configuring and maintaining a SQL database server. Collectively, each of these can negatively impact overall performance, regardless of the hardware/software being utilized:
- Understanding Wait Statistics – whenever SQL Server operations are held up for whatever reason, this information is accessible in the form of wait statistics. Multiple types of wait statistics exist and in order to identify issues you need to understand what it is that SQL server is waiting on at that given moment. Typical counters to analyze include: CXPACKET, ASYNC_IO_COMPLETION, IO_COMPLETION, etc.
- Configuring Your Disks – data and log files should reside on separate physical drive arrays whenever possible, with this being applicable to both local and SAN based disks. For SAN disks, the LUN’s presented to the server is not representative of different underlying physical drive arrays. With I/O access across data files being mostly random compared to the transaction log which is sequential, separation of these files assists in maximising throughput.
- Locating I/O Bottlenecks - I/O bottlenecks are one of the key reasons why performance suffers in SQL Server. There are three checks one can perform namely, checking whether high PAGEIOLATCH or WRITELOG waits are being encountered within your wait statistics; making use of the sys.dm_io_virtual_file_stats() DMF to locate any areas in which excessive physical I/O or stalls on that I/O are being recorded and lastly using Windows Performance Monitor to analyze the Avg. Disk sec/Read and Avg. Disk sec/Write counters to help identify areas of high latency.
- Troubleshooting CPU Bottlenecks – insufficient CPU resources can severely hamper performance. CPU utilization can usually be reduced by configuration changes and query tuning before requiring additional processors. Ways to identify this include using Windows Performance Monitor to monitor the % Processor Time counter; analyzing the ratio of SQL Recompilations/sec to Batch Requests/sec or simply using the sys.dm_exec_query_stats DMV to identify the cached batches/procedures responsible for high CPU utilisation.
- Identifying Memory Constraints – like insufficient CPU resources, so too can the lack of memory resources reduce performance. Memory contention can be a result of numerous causes and is able to be identified via using Windows Performance Monitor to analyze the Available Mbytes; SQL Server Buffer Cache Hit Ratio as well as the SQL Server Page Life Expectancy counters.
- Identifying Problematic Queries – being able to identify problematic queries, and tuning them, can significantly heighten performance. The sys.dm_exec_query_stats DMV contains aggregated performance statistics associated with each cached query plan. This information can easily be ordered in several ways, depending on the type of resource usage you want to concentrate on, for example most CPU bound queries, most I/O intensive queries, most recompiled queries, etc.
- Index Maintenance – fragmented and invalid index structures along with outdated statistics are a common cause of poor query performance. Index structures should be maintained via appropriate maintenance routines that align with the given workload, which by default is not configured out of the box. In addition, index structures should be reviewed on a frequent basis, ensuring the validity and efficiency thereof as well as any missing index structures. The sys.dm_db_index_operational_stats() DMF can provide valuable information around index usage.
- Database Log Files – the importance of the transaction log file is often underestimated in relation to performance. Ensure adequate free space exists within the transaction log file in order for normal operations to occur without requiring an auto-grow operation. Growing the log file can be extremely time-consuming and can force operations to wait until the growth is complete before being able to proceed. By ensuring there is always plenty of free space available within the log file, these performance hits can be avoided altogether.
To get you started, we have compiled a diagnostic toolset for SQL Server that comprises a series of scripts focussing on each of the above mentioned areas.