SQL Server User experience monitor

sluintel

I am trying to create a custom SQL template for my organization using the SAM SQL user experience monitor as AppInsight monitors haven't been very reliable for us. One of the things that I would like to track using my monitor would be the longest running queries by cpu time, duration and the number of executions. I would also like to store the execution plan and display all the information in a table format.

However, it seems that with the SAM monitors, you can only get 1 row and 2 columns back as a result set, the first column being the statistic and the 2nd for the message. Is there a way I can return a table back using a SQL User experience monitor in SAM?

mesverrum

Have you noticed the appinsight tab called Queries?  Seems like it has almost everything you want.

Outside of that, using the SQL experience monitor you wont be able to build what you want, it is intended as a simple check.  Getting more elaborate and using a script to call SQLcmd.exe and parsing the results of that into a table would get you a little further along, but custom script monitors can only accept a maximum of 10 values, which would still only be 2 columns, one of which has to be an integer and the other is a string.  Getting even more elaborate you could string together a bunch of those custom script monitors each looking at different columns and use SWQL to fuse them back together a table but you would have to be pretty dedicated to make that happen.  Makes more sense to let Solarwinds tell you that things are running long and then go into sql to do the more in depth analysis.