December 8, 2014 at 9:18 am
I've googled and researched many different tools, but haven't found one that would be flexible like I'm looking for.
I have a client who is having performance issues, and is asking for help. Unfortunately, as "consultant" to them my hands are tied on actually getting access to the database, and not even sure what type of monitoring is fully being used. Considering this less than ideal situation, I've looked for a portable/low overhead tool that would provide a "diagnostic" look into DMV's and related metrics captured by SQL Server to help identify items such as poorly performing queries, IO stats on files, etc. Again, a workaround for the limited access and red tape.... but gotta make the best of the opportunity.
Right now, my homegrown solution was a mass folder of DMV queries I have, and have a powershell script iterate through and export each to a html/xls report.
I was hoping someone might now of a tool that would better allow me to send, and it would run all the DMV queries needed as well as other general diagnostic information, and then allow export or saving this for my review. As an independent software vendor (ISV) this rare opportunity could help me get insight into true performance issues and usage by a client, as help tune as well as identify issues that may be conflicting with our product.
Does anyone know of something similar to SQLDiag that focuses on scrapping various metrics and information, but is user friendly and portable for a client? Might have to build something out myself, but was hoping I've missed a great portable tool besides sqldiag, ideally that is easier to parse through than what sqlnexus offers (it's been buggy for me). I need simple for client.
December 8, 2014 at 9:30 am
Use the Data Collector.
It's available in all editions (express excluded for lack of SQLAgent) and it's the evolution of all the tools you mentioned. Vast parts of SQLDiag and SQLNexus were included in the Data Collector.
It's easy to use and it has a fairly low performance overhead. It needs disk space for the MDW database, but you can also limit the size of the database lowering the default retention for collection sets.
It can be customized and extended to include additional DMV snapshots.
Setting up additional collection sets is not easy with the built-in tools (requires messing with XML) but there's a free tool that offers a GUI for the Data Collector and also provides a custom Collectory Type to capture Extended Events. The tool is named Extended T-SQL Collector and can be found here: http://extendedtsqlcollector.codeplex.com
Hope this helps
-- Gianluca Sartori
December 9, 2014 at 8:16 am
That's a great tool! I appreciate the info.
However, I need a more portable solution as the diagnostics I would run might be on sqlserver express all the way to 2012 enterprise. In addition, I can't depend on SSIS being installed (which is a requirement for that app)
Any other ideas? Something focused on providing back all DMV information for diagnosis and drilldown? Anyway to export all DMV metrics as a database so I could have client send me a database and let me drill into it from there?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply