Our staff are highly skilled when it comes to Microsoft SQL Server database design and administration and aim to incorporate this experience into all our developments and deployments.
However, not all of our client systems are under our control from their inception. We sporadically encounter situations where client systems are found to be exhibiting performance problems, and in these instances we aim to work with our clients to first triage and then improve the situation. Here we detail, from our experience, five of the most common causes of SQL Server performance issues.
1) Missing or improper maintenance jobs.
While most clients will configure maintenance jobs for backup purposes in an effort to adhere to business Recovery Point Objectives (RPO) and Recovery Time Objectives (RTO), it is not uncommon for other maintenance tasks to be misused or overlooked altogether. All SQL Server databases require periodic tuning to ensure that built-in functionality is at an optimal level, and maintenance tasks such as update statistics, reorganise indexes and rebuild indexes can all play their part in achieving better performance.
2)Default SQL Server configuration.
There is often a lot of care and attention that goes into planning a SQL Server deployment, but if the SQL Server is not configured to correctly utilise the available infrastructure then even the best plans can be wasted. Changing just a few of the configuration defaults such as the tempdb location, maximum server memory, or the max degree of parallelism (MAXDOP) can ensure your database server makes full use of the available hardware benefits.
3)An ever-increasing database size.
Most databases will grow over time, and while such growth is normally expected and planned for at design time, many databases will simply exceed expectations of usage and longevity. Initial sizing of database files is important, but equally important is ensuring that any auto-growth settings are appropriately configured and controlled for the periods beyond expected usage. Archiving solutions designed within applications may also help systems longer term, depending on business requirements around data retention and availability.
4)Scale up and scale out.
Although usage and longevity may impact the amount of data stored in a database, it can also impact the amount of queries run against that data. Though such queries won’t necessarily impact the database size, they can have other negative impacts depending on their frequency and complexity including locking of data and overall query speed. One solution may be to simply scale up the hardware on which the SQL Server instance is installed, however other solutions may see scaling out of the data through replication to dedicated servers for reporting purposes or even more complicated data warehousing solutions for better overall reporting performance and further data analysis.
5)Cached execution plans and parameter sniffing.
These features are actually designed to improve performance by ensuring that queries are run in the most efficient manner possible. However, when creating these plans SQL Server makes the assumption that the query will be reasonably consistent. As such, the first time the execution plan is generated the values used try to ensure the plan is the most efficient it can be. But in instances where values may change dramatically or can lead to drastically different sizes of results, this assumption can lead to the queries being inefficient on subsequent runs. Fortunately, in such scenarios there are means that, when appropriately applied, will ensure the query recompiles the execution plan each time.
