Database Reference
In-Depth Information
Errors and Warnings
section and implement the necessary fixes. For instance, in the preceding
SELECT
statement,
you should not join every row from the
Production.ProductCategory
table to every row in the
Production.Product
table—you must join only the rows with matching
ProductCategorylD
, as follows:
SELECT p.[Name]
,c.
[Name]
FROM Production.Product AS p
JOIN Production.ProductSubCategory AS c
ON p.ProductSubcategoryID = c.ProductSubcategoryID ;
Even after you thoroughly analyze and optimize a workload, you must remember that workload optimization
is not a one-off process. The workload or data distribution on a database can change over time, so you should
periodically check whether your queries are optimized for the current situation. It's also possible that you may identify
shortcomings in the design of the database itself. Too many joins from overnormalization or too many columns from
improper denormalization can both lead to queries that perform badly, with no real optimization opportunities. In
this case, you will need to consider redesigning the database to get a more optimized structure.
Summary
As you learned in this chapter, optimizing a database workload requires a range of tools, utilities, and commands
to analyze different aspects of the queries involved in the workload. You can use Extended Events to analyze the big
picture of the workload and identify the costly queries. Once you've identified the costly queries, you can use the
query window and various SQL commands to troubleshoot the problems associated with the costly queries. Based
on the problems detected with the costly queries, you can apply one or more sets of optimization techniques to
improve the query performance. The optimization of the costly queries should improve the overall performance of the
workload; if this does not happen, you should roll back the change or changes.
In the next chapter, I summarize the performance-related best practices in a nutshell. You'll be able to use this
information as a quick and easy-to-read reference.
