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/