How To Optimize A Query If We Have 3 Billion Data

Description

Fixing bad queries and resolving performance issues can involve hours (or days) of research and testing. Sometimes we can quickly cut that fourth dimension past identifying common design patterns that are indicative of poorly performing TSQL.

Developing pattern recognition for these easy-to-spot eyesores tin allow us to immediately focus on what is most likely to the trouble. Whereas operation tuning tin often be composed of hours of collecting extended events, traces, execution plans, and statistics, beingness able to identify potential pitfalls quickly tin can short-circuit all of that work.

While we should perform our due diligence and prove that whatsoever changes we make are optimal, knowing where to get-go can be a huge time saver!

For more information virtually Query optimization, meet the SQL Query Optimization — How to Make up one's mind When and If Information technology'southward Needed commodity

Tips and tricks

OR in the Bring together Predicate/WHERE Clause Across Multiple Columns

SQL Server tin can efficiently filter a data prepare using indexes via the WHERE clause or any combination of filters that are separated by an AND operator. By existence exclusive, these operations take data and slice it into progressively smaller pieces, until only our result set remains.

OR is a different story. Because it is inclusive, SQL Server cannot procedure information technology in a single performance. Instead, each component of the OR must be evaluated independently. When this expensive operation is completed, the results can and so be concatenated and returned normally.

The scenario in which OR performs worst is when multiple columns or tables are involved. We not but demand to evaluate each component of the OR clause, but demand to follow that path through the other filters and tables inside the query. Even if just a few tables or columns are involved, the operation tin can go heed-bogglingly bad.

Hither is a very simple example of how an OR tin cause operation to become far worse than y'all'd ever imagine it could be:

SELECT DISTINCT

PRODUCT . ProductID ,

PRODUCT . Proper name

FROM Production . Product PRODUCT

INNER Bring together Sales . SalesOrderDetail Item

ON Production . ProductID = Item . ProductID

OR PRODUCT . rowguid = Item . rowguid ;

The query is uncomplicated enough: 2 tables and a join that checks both ProductID and rowguid. Even if none of these columns were indexed, our expectation would exist a table scan on Product and a tabular array browse on SalesOrderDetail. Expensive, but at least something we can encompass. Here is the resulting functioning of this query:

Nosotros did scan both tables, but processing the OR took an absurd amount of computing power. 1.2 million reads were made in this attempt! Because that Product contains only 504 rows and SalesOrderDetail contains 121317 rows, we read far more data than the full contents of each of these tables. In improver, the query took about 2 seconds to execute on a relatively speedy SSD-powered desktop.

The take-abroad from this scary demo is that SQL Server cannot easily procedure an OR status beyond multiple columns. The best fashion to deal with an OR is to eliminate it (if possible) or break it into smaller queries. Breaking a short and simple query into a longer, more fatigued-out query may not seem elegant, but when dealing with OR problems, it is often the best choice:

SELECT

PRODUCT . ProductID ,

PRODUCT . Name

FROM Production . Product Production

INNER Bring together Sales . SalesOrderDetail DETAIL

ON PRODUCT . ProductID = Particular . ProductID

Matrimony

SELECT

PRODUCT . ProductID ,

Product . Proper name

FROM Product . Product Product

INNER Join Sales . SalesOrderDetail DETAIL

ON PRODUCT . rowguid = DETAIL . rowguid

In this rewrite, we took each component of the OR and turned it into its own SELECT statement. Wedlock concatenates the outcome prepare and removes duplicates. Here is the resulting operation:

The execution plan got significantly more circuitous, as nosotros are querying each tabular array twice at present, instead of once, but nosotros no longer needed to play pin-the-tail-on-the-donkey with the result sets as we did earlier. The reads have been cut down from one.2 million to 750, and the query executed in well nether a second, rather than in 2 seconds.

Notation that there are notwithstanding a boatload of index scans in the execution programme, merely despite the need to scan tables four times to satisfy our query, performance is much better than earlier.

Use caution when writing queries with an OR clause. Examination and verify that performance is adequate and that you are not accidentally introducing a functioning bomb similar to what we observed above. If you are reviewing a poorly performing application and run across an OR beyond dissimilar columns or tables, and so focus on that every bit a possible crusade. This is an piece of cake to identify query pattern that volition often lead to poor performance.

Wildcard String Searches

Cord searching efficiently tin can be challenging, and there are far more ways to grind through strings inefficiently than efficiently. For often searched cord columns, we need to ensure that:

Indexes are nowadays on searched columns.
Those indexes can exist used.
If not, can we employ total-text indexes?
If not, can we use hashes, northward-grams, or some other solution?

Without utilise of additional features or design considerations, SQL Server is not good at fuzzy string searching. That is, if I want to detect the presence of a string in any position within a column, getting that data will be inefficient:

SELECT

Person . BusinessEntityID ,

Person . FirstName ,

Person . LastName ,

Person . MiddleName

FROM Person . Person

WHERE Person . LastName Similar '%For%' ;

In this string search, nosotros are checking LastName for any occurrence of "For" in whatsoever position inside the string. When a "%" is placed at the outset of a string, we are making apply of any ascending index impossible. Similarly, when a "%" is at the cease of a string, using a descending index is too impossible. The in a higher place query volition consequence in the following performance:

Equally expected, the query results in a browse on Person.Person. The only way to know if a substring exists within a text column is to churn through every character in every row, searching for occurrences of that string. On a small-scale table, this may exist adequate, but confronting any large information prepare, this will be deadening and painful to look for.

There are a variety of ways to attack this situation, including:

Re-evaluate the application. Practise nosotros actually need to do a wildcard search in this manner? Do users really want to search all parts of this column for a given string? If non, get rid of this adequacy and the problem vanishes!
Can we apply whatever other filters to the query to reduce the data size prior to crunching the cord comparison? If we can filter past date, fourth dimension, status, or some other normally used type of criteria, we can perhaps reduce the data we need to scan downwardly to a small enough amount then that our query perform acceptably.
Can we do a leading string search, instead of a wildcard search? Can "%For%" exist changed to "For%"?
Is total-text indexing an bachelor option? Can we implement and utilize it?
Can nosotros implement a query hash or north-gram solution?

The first 3 options to a higher place are as much pattern/architecture considerations as they are optimization solutions. They ask: What else can we assume, change, or sympathise about this query to tweak it to perform well? These all crave some level of application knowledge or the ability to alter the data returned by a query. These may non be options bachelor to us, only it is important to get all parties involved on the aforementioned page with regard to string searching. If a tabular array has a billion rows and users desire to frequently search an NVARCHAR(MAX) cavalcade for occurrences of strings in any position, then a serious discussion needs to occur every bit to why anyone would want to practice this, and what alternatives are bachelor. If that functionality is truly important, so the business organization will need to commit boosted resources to support expensive string searching, or have a whole lot of latency and resource consumption in the procedure.

Full-Text Indexing is a characteristic in SQL Server that can generate indexes that allow for flexible string searching on text columns. This includes wildcard searches, but also linguistic searching that uses the rules of a given language to make smart decisions about whether a word or phrase are like enough to a column'southward contents to be considered a match. While flexible, Full-Text is an additional feature that needs to be installed, configured, and maintained. For some applications that are very cord-centric, it can be the perfect solution! A link has been provided at the terminate of this article with more details on this feature, what information technology tin can do, and how to install and configure it.

One final option available to usa can be a keen solution for shorter string columns. N-Grams are cord segments that can exist stored separately from the information we are searching and can provide the power to search for substrings without the demand to scan a big table. Before discussing this topic, it is important to fully understand the search rules that are used past an application. For case:

Are in that location a minimum or maximum number of characters allowed in a search?
Are empty searches (a tabular array browse) immune?
Are multiple words/phrases allowed?
Do nosotros demand to store substrings at the commencement of a string? These can be collected with an index seek if needed.

Once these considerations are assessed, nosotros tin can accept a cord column and break information technology into string segments. For example, consider a search arrangement where there is a minimum search length of 3 characters, and the stored word "Dinosaur". Hither are the substrings of Dinosaur that are three characters in length or longer (ignoring the first of the cord, which tin exist gathered separately & quickly with an alphabetize seek confronting this cavalcade):
ino, inos, inosa, inosau, inosaur, nos, nosa, nosau, nosaur, osa, osau, osaur, sau, saur, aur.

If nosotros were to create a separate table that stored each of these substrings (also known every bit n-grams), we can link those n-grams to the row in our big table that has the word dinosaur. Instead of scanning a large tabular array for results, we tin instead practice an equality search against the n-gram tabular array. For case, if I did a wildcard search for "dino", my search can be redirected to a search that would await like this:

SELECT

n_gram_table . my_big_table_id_column

FROM dbo . n_gram_table

WHERE n_gram_table . n_gram_data = 'Dino' ;

Assuming n_gram_data is indexed, then we will quickly return all IDs for our large table that have the give-and-take Dino anywhere in it. The northward-gram table only requires 2 columns, and we can bound the size of the n-gram string using our awarding rules defined to a higher place. Even if this table gets large, information technology would likely still provide very fast search capabilities.

The toll of this approach is maintenance. We demand to update the north-gram table every time a row is inserted, deleted, or the string data in it is updated. Also, the number of northward-grams per row will increase apace as the size of the column increases. As a result, this is an excellent arroyo for shorter strings, such as names, zippo codes, or phone numbers. It is a very expensive solution for longer strings, such as email text, descriptions, and other costless-form or MAX length columns.

To apace recap: Wildcard string searching is inherently expensive. Our best weapons against information technology are based on design and compages rules that allow us to either eliminate the leading "%", or limit how nosotros search in ways that allow for other filters or solutions to be implemented. A link has been provided at the finish of this article with more than information on, and some demos of generating and using north-gram information. While a more than involved implementation, it is another weapon in our arsenal when other options have failed usa.

Big Write Operations

Subsequently a discussion of why iteration tin can cause poor performance, nosotros are now going to explore a scenario in which iteration IMPROVES performance. A component of optimization not still discussed hither is contention. When we perform whatsoever operation against data, locks are taken confronting some corporeality of information to ensure that the results are consistent and practise non interfere with other queries that are being executed against the aforementioned information by others besides us.

Locking and blocking are good things in that they safeguard data from abuse and protect u.s. from bad effect sets. When contention continues for a long time, though, important queries may be forced to wait, resulting in unhappy users and the resulting latency complaints.

Large write operations are the poster-child for contention as they volition ofttimes lock an entire table during the time information technology takes to update the data, check constraints, update indexes, and process triggers (if whatsoever exist). How big is large? There is no strict rule here. On a table with no triggers or foreign keys, large could exist 50,000, 100,000, or 1,000,000 rows. On a table with many constraints and triggers, big might exist 2,000. The only mode to confirm that this is a trouble is to examination it, detect it, and respond accordingly.

In addition to contention, large write operations will generate lots of log file growth. Whenever writing unusually big volumes of information, keep an center on the transaction log and verify that you practise not risk filling it up, or worse, filling upwardly its physical storage location.

Notation that many large write operations will result from our own work: Software releases, data warehouse load processes, ETL processes, and other similar operations may need to write a very large corporeality of data, even if it is done infrequently. It is upwards to u.s. to identify the level of contention allowed in our tables prior to running these processes. If we are loading a large table during a maintenance window when no 1 else is using it, and then nosotros are costless to deploy using whatever strategy nosotros wish. If we are instead writing large amounts of data to a busy production site, then reducing the rows modified per operation would be a proficient safeguard against contention.

Mutual operations that tin result in large writes are:

Adding a new column to a table and backfilling it across the entire table.
Updating a column beyond an entire table.
Irresolute the data type of a column. Meet link at the end of the article for more info on this.
Importing a big volume of new data.
Archiving or deleting a large volume of one-time data.

This may not ofttimes be a functioning concern, but understanding the effects of very large write operations tin avoid of import maintenance events or releases from going off-the-track unexpectedly.

Missing Indexes

SQL Server, via the Direction Studio GUI, execution program XML, or missing index DMVs, will let u.s.a. know when in that location are missing indexes that could potentially aid a query perform better:

This warning is useful in that it lets us know that in that location is a potentially easy set up to better query performance. It is also misleading in that an additional index may not exist the best fashion to resolve a latency issue. The green text provides united states of america with all of the details of a new index, only we demand to practice a chip of work before because taking SQL Server'due south advice:

Are there any existing indexes that are similar to this one that could exist modified to cover this use case?
Do we need all of the include columns? Would an index on simply the sorting columns exist good enough?
How high is the impact of the alphabetize? Will it amend a query past 98%, or just v%.
Does this index already exist, but for some reason the query optimizer is non choosing it?

Frequently, the suggested indexes are excessive. For example, here is the index creation statement for the fractional plan shown above:

CREATE NONCLUSTERED Index < Proper noun of Missing Index , sysname , >

ON Sales . SalesOrderHeader ( Condition , SalesPersonID )

INCLUDE ( SalesOrderID , SubTotal )

In this case, there is already an index on SalesPersonID. Status happens to be a column in which the tabular array generally contains one value, which means that every bit a sorting cavalcade it would not provide very much value. The affect of nineteen% isn't terribly impressive. We would ultimately be left to ask whether the query is important plenty to warrant this comeback. If information technology is executed a million times a day, and then perhaps all of this piece of work for a 20% comeback is worth it.

Consider another alternative index recommendation:

Here, the missing index suggested is:

CREATE NONCLUSTERED INDEX [ < Proper name of Missing Index , sysname , > ]

ON [ Person ] . [ Person ] ( [ FirstName ] )

INCLUDE ( [ BusinessEntityID ] , [ Title ] )

This time, the suggested alphabetize would provide a 93% comeback and handle an unindexed column (FirstName). If this is at all a frequently run query, then adding this index would likely be a smart motion. Practise we add together BusinessEntityID and Title every bit INCLUDE columns? This is far more of a subjective question and we need to decide if the query is important enough to want to ensure in that location is never a cardinal lookup to pull those additional columns back from the clustered index. This question is an echo of, "How do we know when a query's performance is optimal?". If the non-roofing index is skillful plenty, then stopping at that place would be the right decision every bit it would relieve the computing resources required to store the actress columns. If performance is still not good enough, then calculation the INCLUDE columns would be the logical next stride.

As long as we remember that indexes require maintenance and tiresome downward write operations, we can arroyo indexing from a pragmatic perspective and ensure that we do not make any of these mistakes:

Over-Indexing a Table

When a table has too many indexes, write operations become slower as every UPDATE, DELETE, and INSERT that touches an indexed column must update the indexes on it. In addition, those indexes have upwards infinite on storage as well as in database backups. "Too Many" is vague, simply emphasizes that ultimately application performance is the fundamental to determining whether things are optimal or non.

Under-Indexing a Table

An nether-indexed table does not serve read queries effectively. Ideally, the almost common queries executed against a tabular array should do good from indexes. Less frequent queries are evaluated on a case-by-example need and indexed when benign. When troubleshooting a performance problem against tables that have few or no non-clustered indexes, and so the outcome is likely an nether-indexing one. In these cases, feel empowered to add indexes to improve performance equally needed!

No Clustered Index/Primary Key

All tables should have a clustered index and a primary key. Clustered indexes will almost always perform amend than heaps and will provide the necessary infrastructure to add non-clustered indexes efficiently when needed. A primary key provides valuable information to the query optimizer that helps it make smart decisions when creating execution plans. If yous run into a table with no clustered alphabetize or no primary key, consider these top priorities to enquiry and resolve before continuing with further research.

See the link at the end of this article for details on capturing, trending, and reporting on missing index data using SQL Server's built-in dynamic management views. This allows you lot to learn about missing index suggestions when you may non exist staring at your computer. It also allows you to see when multiple suggestions are made on a single query. The GUI volition only display the meridian proffer, only the raw XML for the execution programme will include every bit many every bit are suggested.

High Table Count

The query optimizer in SQL Server faces the same claiming as whatever relational query optimizer: It needs to notice a good execution plan in the face of many options in a very short span of time. It is essentially playing a game of chess and evaluating move after move. With each evaluation, information technology either throws abroad a chunk of plans similar to the suboptimal plan, or setting one aside as a candidate programme. More tables in a query would equate to a larger chess lath. With significantly more than options available, SQL Server has more than work to do, but cannot accept much longer to determine the programme to use.

Each table added to a query increases its complexity past a factorial corporeality. While the optimizer will generally make skillful decisions, fifty-fifty in the face of many tables, we increment the risk of inefficient plans with each table added to a query. This is not to say that queries with many tables are bad, but that nosotros demand to use circumspection when increasing the size of a query. For each ready of tables, it needs to make up one's mind bring together gild, bring together blazon, and how/when to employ filters and aggregation.

Based on how tables are joined, a query will fall into ane of two basic forms:

Left-Deep Tree: A join B, B bring together C, C join D, D join E, etc…This is a query in which near tables are sequentially joined one later on another.
Bushy Tree: A join B, A join C, B bring together D, C bring together Eastward, etc…This is a query in which tables co-operative out into multiple logical units inside each co-operative of the tree.

Hither is a graphical representation of a bushy tree, in which the joins branch upwards into the result gear up:

Similarly, hither is a representation of what a left-deep tree would look like.

Since the left-deep tree is more naturally ordered based on how the tables are joined, the number of candidate execution plans for the query are less than for a bushy tree. Included above is the math behind the combinatorics: that is, how many plans will exist generated on boilerplate for a given query type.

To emphasize the enormity of the math behind table counts, consider a query that accesses 12 tables:

iii

five

viii

nine

thirteen

xvi

twenty

thirty

SELECT Meridian 25

Product . ProductID ,

Product . Name Equally ProductName ,

Product . ProductNumber ,

CostMeasure . UnitMeasureCode ,

CostMeasure . Name As CostMeasureName ,

ProductVendor . AverageLeadTime ,

ProductVendor . StandardPrice ,

ProductReview . ReviewerName ,

ProductReview . Rating ,

ProductCategory . Name AS CategoryName ,

ProductSubCategory . Name Equally SubCategoryName

FROM Production . Product

INNER JOIN Production . ProductSubCategory

ON ProductSubCategory . ProductSubcategoryID = Production . ProductSubcategoryID

INNER Bring together Product . ProductCategory

ON ProductCategory . ProductCategoryID = ProductSubCategory . ProductCategoryID

INNER Bring together Production . UnitMeasure SizeUnitMeasureCode

ON Product . SizeUnitMeasureCode = SizeUnitMeasureCode . UnitMeasureCode

INNER JOIN Product . UnitMeasure WeightUnitMeasureCode

ON Product . WeightUnitMeasureCode = WeightUnitMeasureCode . UnitMeasureCode

INNER Bring together Production . ProductModel

ON ProductModel . ProductModelID = Product . ProductModelID

LEFT JOIN Product . ProductModelIllustration

ON ProductModel . ProductModelID = ProductModelIllustration . ProductModelID

LEFT JOIN Production . ProductModelProductDescriptionCulture

ON ProductModelProductDescriptionCulture . ProductModelID = ProductModel . ProductModelID

LEFT Join Product . ProductDescription

ON ProductDescription . ProductDescriptionID = ProductModelProductDescriptionCulture . ProductDescriptionID

LEFT JOIN Production . ProductReview

ON ProductReview . ProductID = Product . ProductID

LEFT Join Purchasing . ProductVendor

ON ProductVendor . ProductID = Product . ProductID

LEFT Join Production . UnitMeasure CostMeasure

ON ProductVendor . UnitMeasureCode = CostMeasure . UnitMeasureCode

Society Past Product . ProductID DESC ;

With 12 tables in a relatively busy-fashion query, the math would work out to:

(2n-2)! / (northward-1)! = (2*12-1)! / (12-1)! = 28,158,588,057,600 possible execution plans.

If the query had happened to be more than linear in nature, then we would have:

n! = 12! = 479,001,600 possible execution plans.

This is merely for 12 tables! Imagine a query on 20, 30, or 50 tables! The optimizer can oft slice those numbers down very chop-chop by eliminating entire swaths of sub-optimal options, simply the odds of it being able to practise so and generate a expert programme decrease as table count increases.

What are some useful ways to optimize a query that is suffering due to as well many tables?

Move metadata or lookup tables into a divide query that places this data into a temporary table.
Joins that are used to return a single constant can be moved to a parameter or variable.
Break a large query into smaller queries whose data sets can afterward exist joined together when set.
For very heavily used queries, consider an indexed view to streamline constant access to of import information.
Remove unneeded tables, subqueries, and joins.

Breaking upward a big query into smaller queries requires that there volition exist no information alter in between those queries that would somehow invalidate the result fix. If a query needs to be an atomic set, and then you may need to use a mix of isolation levels, transactions, and locking to ensure data integrity.

More often than non when we are joining a large number of tables together, we can break the query up into smaller logical units that tin be executed separately. For the example query earlier on 12 tables, we could very hands remove a few unused tables and split up out the data retrieval into two carve up queries:

eight

eleven

xiv

fifteen

eighteen

twenty

SELECT TOP 25

Production . ProductID ,

Product . Name AS ProductName ,

Production . ProductNumber ,

ProductCategory . Name Equally ProductCategory ,

ProductSubCategory . Name As ProductSubCategory ,

Production . ProductModelID

INTO #Product

FROM Product . Product

INNER JOIN Production . ProductSubCategory

ON ProductSubCategory . ProductSubcategoryID = Production . ProductSubcategoryID

INNER JOIN Product . ProductCategory

ON ProductCategory . ProductCategoryID = ProductSubCategory . ProductCategoryID

Club BY Product . ModifiedDate DESC ;

SELECT

Product . ProductID ,

Product . ProductName ,

Production . ProductNumber ,

CostMeasure . UnitMeasureCode ,

CostMeasure . Name Every bit CostMeasureName ,

ProductVendor . AverageLeadTime ,

ProductVendor . StandardPrice ,

ProductReview . ReviewerName ,

ProductReview . Rating ,

Product . ProductCategory ,

Production . ProductSubCategory

FROM #Product Product

INNER JOIN Production . ProductModel

ON ProductModel . ProductModelID = Product . ProductModelID

LEFT JOIN Product . ProductReview

ON ProductReview . ProductID = Product . ProductID

LEFT JOIN Purchasing . ProductVendor

ON ProductVendor . ProductID = Product . ProductID

LEFT JOIN Production . UnitMeasure CostMeasure

ON ProductVendor . UnitMeasureCode = CostMeasure . UnitMeasureCode ;

Drib TABLE #Product ;

This is only ane of many possible solutions, but is a way to reduce a larger, more than complex query into two simpler ones. As a bonus, nosotros tin review the tables involved and remove whatsoever unneeded tables, columns, variables, or anything else that may not exist needed to render the data we are looking for.

Table count is a hefty contributor towards poor execution plans equally information technology forces the query optimizer to sift through a larger outcome set and discard more potentially valid results in the search for a swell plan in well under a 2d. If you are evaluating a poorly performing query that has a very large tabular array count, endeavor splitting it into smaller queries. This tactic may non e'er provide a significant improvement, but is often effective when other avenues have been explored and at that place are many tables that are existence heavily read together in a single query.

Query Hints

A query hint is an explicit direction by united states of america to the query optimizer. We are bypassing some of the rules used by the optimizer to force information technology to bear in ways that information technology unremarkably wouldn't. In this regard, it's more of a directive than a hint.

Query hints are oftentimes used when nosotros accept a performance problem and adding a hint quickly and magically fixes it. There are quite a few hints available in SQL Server that bear upon isolation levels, join types, tabular array locking, and more. While hints tin can have legitimate uses, they nowadays a danger to performance for many reasons:

Future changes to the information or schema may result in a hint no longer being applicable and condign a hindrance until removed.
Hints can obscure larger problems, such equally missing indexes, excessively large data requests, or broken business logic. Solving the root of a problem is preferable than solving a symptom.
Hints can consequence in unexpected behavior, such as bad information from dirty reads via the utilise of NOLOCK.
Applying a hint to address an edge case may cause performance degradation for all other scenarios.

The general dominion of pollex is to apply query hints as infrequently as possible, only after sufficient research has been conducted, and simply when we are sure there will exist no sick furnishings of the alter. They should be used every bit a scalpel when all other options neglect. A few notes on commonly used hints:

NOLOCK: In the consequence that information is locked, this tells SQL Server to read data from the last known value available, also known every bit a muddy read. Since information technology is possible to employ some old values and some new values, data sets can contain inconsistencies. Do not use this in any place in which data quality is important.
RECOMPILE: Adding this to the terminate of a query volition upshot in a new execution plan existence generated each fourth dimension this query executed. This should not be used on a query that is executed frequently, as the cost to optimize a query is non trivial. For infrequent reports or processes, though, this tin can be an effective way to avoid undesired plan reuse. This is often used equally a bandage when statistics are out of appointment or parameter sniffing is occurring.
MERGE/HASH/LOOP: This tells the query optimizer to utilize a specific blazon of join as function of a join operation. This is super-risky equally the optimal bring together will modify as information, schema, and parameters evolve over time. While this may set up a problem right now, it volition introduce an element of technical debt that will remain for every bit long every bit the hint does.
OPTIMIZE FOR: Tin specify a parameter value to optimize the query for. This is often used when nosotros want performance to be controlled for a very mutual use case so that outliers do not pollute the plan enshroud. Similar to join hints, this is fragile and when business organization logic changes, this hint usage may go obsolete.

Consider our name search query from before:

SELECT

e . BusinessEntityID ,

p . Championship ,

p . FirstName ,

p . LastName

FROM HumanResources . Employee e

INNER JOIN Person . Person p

ON p . BusinessEntityID = e . BusinessEntityID

WHERE FirstName Like 'E%'

We can forcefulness a MERGE JOIN in the bring together predicate:

SELECT

e . BusinessEntityID ,

p . Championship ,

p . FirstName ,

p . LastName

FROM HumanResources . Employee east

INNER MERGE Join Person . Person p

ON p . BusinessEntityID = e . BusinessEntityID

WHERE FirstName Similar 'E%'

When nosotros do so, we might find better performance under sure circumstances, but may also observe very poor operation in others:

For a relatively uncomplicated query, this is quite ugly! As well note than our join type has limited alphabetize usage, and as a result we are getting an index recommendation where we probable shouldn't need/desire i. In fact, forcing a MERGE JOIN added boosted operators to our execution program in order to appropriately sort outputs for use in resolving our result gear up. We tin force a HASH JOIN similarly:

SELECT

eastward . BusinessEntityID ,

p . Championship ,

p . FirstName ,

p . LastName

FROM HumanResources . Employee eastward

INNER HASH JOIN Person . Person p

ON p . BusinessEntityID = e . BusinessEntityID

WHERE FirstName Similar 'Due east%'

Again, the plan is not pretty! Note the alarm in the output tab that informs u.s. that the join order has been enforced by our join option. This is important as it tells is that the join type we chose also limited the possible ways to society the tables during optimization. Essentially, we have removed many useful tools available to the query optimizer and forced it to work with far less than it needs to succeed.

If we remove the hints, and so the optimizer volition choose a NESTED LOOP join and get the following performance:

Hints are often used every bit quick fixes to complex or messy problems. While there are legit reasons to use a hint, they are generally held onto as last resorts. Hints are additional query elements that require maintenance and review over fourth dimension as application code, information, or schema change. If needed, be sure to thoroughly document their use! It is unlikely that a DBA or developer will know why you used a hint in 3 years unless you document its demand very well.

Conclusion

In this article we discussed a diverseness of common query mistakes that tin lead to poor performance. Since they are relatively easy to place without all-encompassing research, nosotros can use this knowledge to improve our response fourth dimension to latency or performance emergencies. This is only the tip of the iceberg, only provides a neat starting bespeak in finding the weak points in a script.

Whether by cleaning upwardly joins and WHERE clauses or by breaking a large query into smaller chunks, focusing our evaluation, testing, and QA process will amend the quality of our results, in addition to allowing us to complete these projects faster.

Anybody has their own toolset of tips & tricks that allow them to work faster AND smarter. Do you have any quick, fun, or interesting query tips? Let me know! I'yard always looking at newer ways to speed upward TSQL and avoid days of frustrating searching!

Writer
Contempo Posts

Ed has 20 years of experience in database and systems assistants, developing a passion for functioning optimization, database blueprint, and making things go faster.He has spoken at many SQL Saturdays, 24 Hours of Laissez passer, and Laissez passer Summit.This pb him to organize SQL Saturday Albany, which has become an annual event for New York's Uppercase Region.

In his gratuitous time, Ed enjoys video games, sci-fi & fantasy, traveling, and being as large of a geek as his friends volition tolerate.

View all posts by Ed Pollack