How to remove spills

Question

hi,

1 ) I have seen many types of spills in sqlserver plan (2012).
so please tell me in how many ways we can remove these spills

yours sincerely

Answer 1

Hi @rajesh yadav ,

Presence of spills indicate potential performance problems as a spill involves disk reads and writes and is many times slower than the corresponding in-memory-only operation. They also add overload to tempdb and may cause contention.
There are some common types of spills:
Hash Warning Event:
This is one of the most common spills, create or update the statistics on the column involved in the join is the most effective way to reduce the occurrence of events.
One of the following you can do:
Make sure that statistics exist on the columns that are being joined or grouped.
If statistics exist on the columns, update them.
Use a different type of join.
Increase available memory on the computer.

Sort Warning Event:
The solution is usually to add a covering index that provides the desired order.

Exchange Spill Event:
There are several ways to avoid exchange spill events:
Omit the ORDER BY clause if you do not need the result set to be ordered.
If ORDER BY is required, eliminate the column the participate in the multiple range scans from the ORDER BY.
Using an index hint, force the optimizer to use a different access path on the table in question.
Rewrite the query to produce a different query execution plan.
Force serial execution of the query by adding the MAXDOP=1 option to the end of the query or index operation.

The tempdb spills are easily detectable and reasonably explained in the product documentation
you can refer to https://blog.sqlauthority.com/2020/01/12/what-is-tempdb-spill-in-sql-server-interview-question-of-the-week-259/

Answer 2

Good day,

There can be several reasons for the server to spill to the tempdb and the solution is according to these. Here some options for example but for full tutorial it is recommend to search for blogs/articles as we cannot write a full blog here in a forum's response :-)

SQL Server spills data to TempDB when the query was not granted enough memory to finish the operation. So the question is why he query was not granted enough memory

I get error when posting the answer. I attach a text file with the answer123373-qna-bug.txt

Answer 3

What spills?