Eric Blinn looks at plan compilation time in SQL Server:
The query returns 4 rows. By including STATISTICS TIME we get extra information on the Messages output tab. We can see from the execution on my laptop that the optimizer took 6ms to compile a query plan and the actual query executed in only 1ms.
Run the query batch a few more times and notice that the parse and compile time drops to zero. This is because SQL Server keeps a list of compiled plans and tries to reuse them without having to recompile. In this case the optimizer has recognized that this query is exactly identical to one it has previously executed and it reuses the previously compiled plan. That list of plans is called the Plan Cache and will be covered in much more detail in a subsequent post.
This cost is something we tend to forget about, but can make a big difference for a user’s experience.