Many things can go wrong with your SQL Servers that degrade their performance and reduce user productivity. Tracking down the issues that are affecting their systems is one of the primary roles of corporate DBAs. The inability to pinpoint and correct the problems can lead to stressful days that necessarily force other work to be put on the back burner. If bad performance is impacting the user community, you can be sure it is also impacting the database team. And they would like it to stop!
There are many different aspects of an SQL Server that can be responsible for its lagging performance. It’s not just a matter of turning the volume up to eleven and shutting out the noise. A DBA must engage in serious troubleshooting and may need to make numerous attempts at locating the reason for the system’s diminished capability to satisfy its users. If the system in question is running business-critical applications, solving the mystery will quickly become the focus of a wider audience than the database team. This is usually not desirable for any of the individuals or teams involved.
Looking at the Code
The remedy to a badly performing SQL Server may be hidden in its SQL queries and code. Poorly constructed queries may perform adequately when a database is in the testing phase of development. As the system is used in a production setting, problems may begin to surface that had previously gone unnoticed. A slow but steady decline in response time can result in performance degradation that essentially makes a system unusable. All because of SQL queries that require optimization.
There is a large body of accumulated knowledge regarding the best way to write SQL queries to extract the best performance from the underlying SQL Server implementation. It points to specific techniques that DBAs and database developers should consider when writing or tuning their queries. Here are some characteristics of your SQL queries that should be the focus of performance optimization efforts.
- Improper indexing can be the source of major performance issues with SQL Server. Creating an index structure is a complicated task that requires input from the data’s business users who understand how it will be used. The initial indexing layout may need to be modified after the database is in use as new options become apparent. It doesn’t matter if you designed the initial structure or have been assigned to clean up a system built by others. Examining and optimizing the database indexes can lead to substantial performance increases.
- As the size of working data sets increase, so too does the time required to process them. Limiting their size by reworking SELECT statements with filters can go a long way toward speeding up query response time. Also, consider applying filters to the inner statement of sub-selects rather than the outer statements. Eliminating the use of SELECT * clauses in favor of queries that target more defined fields can have a dramatic impact on database performance.
- The selection of the fields and tables involved in a query has serious effects on its ability to return a timely response. Reviewing the code to remove unnecessary JOINS can reduce the amount of processing that needs to be done by the database. Performing calculations in JOIN and WHERE clauses slow down database response and can often be eliminated by the addition of columns to a table.
What Does the Doctor Say?
When you or a family member are feeling under the weather and not performing up to snuff, a visit to the doctor is often needed to diagnose and treat the problem. IDERA’s SQL Doctor offers similar benefits for the ailments affecting your SQL Server environment. It provides a tool that takes a holistic look at your SQL Servers and returns informative methods with which to increase the quality of their health and performance. One of the areas that the Doctor looks at is SQL query tuning.
Some specific ways that SQL Doctor helps identify performance gains that can be made by modifying SQL queries are:
- Checking indexes to uncover possibilities for optimization;
- Parsing T-SQL scripts to analyze custom queries without actually executing them;
- Exploring query plan statistics and delivering optimization recommendations;
- Finding problem queries and suggesting ways to improve their performance.
With the information returned in SQL Doctor’s diagnosis, a database team can address the issues that are plaguing performance from a position of strength. It can help you streamline your efforts to get your database running at peak efficiency. The tool handles the SQL Server implementations in your facility as well as in the Microsoft and Amazon cloud.