Designed to be a full-featured database, the SQL Server is a relational database management system (RDBMS) with the primary function of storing and retrieving any data requested by other software applications. Microsoft has developed over a dozen different editions of the SQL Server in order to cater to different audiences and manage different workloads. From small, single-machine applications to large, cloud-based applications, the SQL Server supports different types of data while also allowing users to define and utilize various composite types.
By following these simple SQL Server performance tips, you can ensure your database is running as efficiently as possible, so it will continue optimal management of varying workloads.
Assessing and Analysing a Query
In order tune your SQL Server effectively and isolate the root cause of your slowed database, you’ll need to have clear visibility into the layers of information ingrained in expensive queries. This will require you to know top SQL statements, wait types, SQL plans, blocked queries, resource contention, and the effects of missing indexes. Accessing a basic query is the best way to know exactly what you’re dealing with.
- Use SSMS to hover over query elements to ensure that you’re indeed operating on a real table, not a view or table-valued function as these have different performance implications. Check the row count in your table by querying the DMVs.
- Use this query to also examine the WHERE and JOIN clauses and note the filtered row count. If the majority of the table is returned without any filters, this could be a red flag and significantly slow a query down.
- Based on the table’s row count and the returned filters, you will know exactly how many rows you’ll be working with. This row count is also referred to as the actual, logical set. Consider using an SQL diagramming tool to access queries and query selectivity.
- Closely examine the SELECT* or scalar function to determine if the query has extra columns. The more columns returned, the less optimal it will become for your database to use the index operations needed to conduct an execution plan.
Discovering Flaws in a Written Query
When tuning your SQL Server, knowing how to properly use constraints is a life saver. Review existing keys, constraints, and indexes to ensure there is no duplication of effort or overlapping of indexes. Get information about these indexes by running the sp_helpindex stored procedure.
Estimated plans use estimated statistics in order to determine the estimated rows, whereas actual execution plans use actual statistics during runtime. Compare the actual and estimated plans, and be sure to note any differences. If you don’t record these results, you won’t be able to determine the true root cause of an underperforming database, nor will you be able to track the impact of your changes.
Start adjusting your query based on your findings from the previously run query. Make small, single changes at a time - starting with the most expensive operations first. By making too many changes at once, you’ll risk nullifying your efforts. Run the query after each change and, again, be sure to record results. Continue this process until the logical I/Os provide satisfactory results.
If you’re not satisfied after several query adjustments and re-runs, consider altering the code, or adding or adjusting indexes if code alteration is not possible. Consider adjusting existing indexes, covering indexes, or filtering indexes for improvements. After you’ve made adjustments, rerun the query and record results.
During this process, be sure to keep an eye out for frequently encountered inhibitors of performance, such as:
- Code first generators
- Nested views
- Abuse of wildcards
- Scalar functions
- Row-by-row processing
Uncovering Hidden Opportunities for Improvement
Though this manual tuning process is effective in improving the performance of your SQL Server, it can often take a significant amount of time and effort. However, with the use of a database performance monitoring tool, you can optimize your query tuning process and even uncover hidden opportunities for improvement that you may have not otherwise seen. Consider using a continuous database performance monitoring solution, like SolarWinds Database Performance Analyser, to consolidate performance information in a single, secure location.
With these tips and the help of reliable performance optimization tools, you can ensure your SQL Server is operating at the highest level possible.