Plan Cache Tsunami

Plan Cache Tsunami

Introduction

Your SQL Server may be slow for a peculiar reason: a run-away query that pushes-out all other queries from the plan cache, like a huge wave. So called “tsunami query“. Tsunami query wipes other plans from the cache and makes your SQL running slow. Creating tsunami query is easy and usually not intentional: a developer concatenates values in SQL command string. As it “works on my machine”, it easily slips to production (nobody tests for this, right?), and the monster is loose! To see how damaging this is, please watch the video demo.

Inspecting the Plan Cache

PLEASE, run this query on all your SQL Server environments (prod, test, dev), and scroll down through resulting rows. It gives you a list of all plans in the plan cache:

Spot a query that repeats with changing literal value(s), for example:

Plan Cache Tsunami – The Demo

You found it! In the demo video, it took 23 seconds while using this bad “concatenation” method.

The fix

Luckily, the fix is easy: just use sql parameters (begins with “@” sign) to pass a value, do NOT concatenate! If you see a “+” or any other concatenation operator in the code, red-alert should be raised in your head and begin inspection. SQL command should be a monolithic string, without any concatenation. This is how plan cache looks like when query is correctly written, with parameters. Spot parameter name and high “usecounts”:

This time, 10 000 executions took 0.1 sec instead of 23 sec – over 200x faster! Isn’t that like a magic? To achieve the same you would have to buy a server with 200x more CPU cores, and still it would not be as fast as this, because of compile locks. So save the money, and write queries that are not “tsunami queries”!

Tagged with: , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.