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:
|
1 2 3 4 5 |
SELECT t.dbid, text=LEFT(t.text, 300), text_end=RIGHT(t.text, 300), p.usecounts, t.objectid, p.objtype, p.plan_handle, size_KB=p.size_in_bytes/1024 FROM sys.dm_exec_cached_plans p CROSS APPLY sys.dm_exec_sql_text (p.plan_handle) t ORDER BY LEFT(t.text, 300) |
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”!
Leave a Reply