Home > Why DPA Uses SQL Server Trace Flag 2861 and Zero Cost Plans

Why DPA Uses SQL Server Trace Flag 2861 and Zero Cost Plans

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.
Thomas LaRock
Thomas LaRock is a Head Geek™ at SolarWinds and a Microsoft® Certified Master, Microsoft Data Platform MVP, VMware® vExpert, and former Microsoft Certified Trainer. He has over…
Read more

Tweets

SolarWinds's Twitter avatar
SolarWinds
@solarwinds

SolarWinds gives data pros the tools for DataOps success with Database Mapper and Task Factory. t.co/ouZywJn2NN

SolarWinds's Twitter avatar
SolarWinds
@solarwinds

Day three down! There’s still some time to chat with our experts tomorrow—come see us at Booth H8-A30. t.co/wIP3G4ynK4 #GITEX2021

SolarWinds's Twitter avatar
SolarWinds
@solarwinds

It’s Day Two at DTCC. Remember to visit us at booth 27 and find out more on how SolarWinds can help with IT and dat… t.co/uCMlSPxpAD