Tuesday, February 10, 2015

SQLSentry - a possible gotcha for high-transaction boxes

Blogging it here for my own edification.

We ran into a problem last year where our performance on a box, monitored by SQLSentry, sucked. 

With the help of SQLSentry Support, we were able to find the problem.  Our code uses lots of functions, and their default statement-level trace slowed things down quite a bit - on one box, parsing was cut by 50%.

Note that this is NOT an inherent problem with SQLSentry, but with the level of detail they were collecting via the trace.

(More here: http://sqlblog.com/blogs/linchi_shea/comments/14682.aspx)

From their support (paraphrased and edited for clarity):

The easiest way to test if this helps in your case is just to turn it off and observe.

To disable it, you can go to View-> Settings, then change "Computer Settings" to "SQL Server Settings" , then change "SQL Server Connection" to "Top SQL Settings". [on SQLSentry 8, it's "Top SQL Source"]

Now, set "Collect Statement Events" to False.You will probably need to change "Inherit From Parent (Global)" to False in "Collection Settings", otherwise it won't let you change the value.  Finally, click on another server in the left-side drop down (which changes context) to make sure it's saved.  

" What you will be losing here is really just the statement level detail for cached plans that SQL Sentry is collecting. It is nice to have that level of detail, but also not an absolute necessity."