About zero cost plans in SQL Server
OK, first thing first, what is a “zero cost plan” in SQL Server? I did some digging around and found the following links, which I would encourage you to read before continuing with this post: http://msdn.microsoft.com/en-us/library/ms181055.aspx http://support.microsoft.com/kb/325607
If you’ve done this background reading, you should now be aware that the database engine will look to remove plans from the procedure cache when there is memory pressure in that area. And it will look to remove plans that have the lowest cost. And you should also have an idea of the types of statements that can create plans with zero cost:
- ad-hoc queries
- SET statements
- COMMIT TRAN statements
Why SolarWinds DPA uses trace flag 2861
Now, SolarWinds DPA will turn trace flag 2861 on by default (see article). The reason we do this is because of how we are collecting the text for the statements we have captured. We use the SQL handle with the fn_get_sql system function in order to retrieve the statement text. While the DPA polling process runs every second (by default, but configurable), the DPA process of retrieving SQL text only runs every two minutes (by default, not configurable at the current time). OK, so we know the database engine wants to remove these plans, and that DPA has enabled a trace flag that will keep these plans around longer than expected. So…what will that mean in terms of performance?
- If you have a system that is very, very busy and issuing many, many ad-hoc statements (like some type of ORM implementations such as nHibernate) that are of very short duration, you will likely see some memory pressure in the procedure plan cache (also known as “procedure cache bloat”). For some good info on this effect I will point you to Bart Duncan’s blog post, which links to some other posts that are worth reading.
- If you have a system that is already strapped for memory (say, running Win2003 with 4GB of RAM), then even the slight increase in memory consumption by using 2861 could be a concern.
Some cases where it makes sense to disable trace flag 2861
For a majority of DPA SQL Server customers, trace flag 2861 causes no issues whatsoever. For those with very busy systems (and/or running ORM implementations), they will see a slight increase in memory consumption and likely procedure cache bloat. For those customers we have always recommended turning off 2861. Please see this article for specifics on doing this.
We advise our customers that want to disable this trace flag to do so with then understanding that DPA will not be able to get the text for all of the statements it captures. But that is an easy trade off, because often those statements are being executed so frequently and in such a large volume that we are able to get the text eventually. The use of trace flag 2861 by DPA in order to aid in collecting the text of the SQL statements allows for DPA to have a far lighter load on a server than other tools that rely on the use of tracing in order to capture statements and text.
Test comment