I'm trying to figure out the best way to track SQL server performance over time. We're using perfmon and tracking many SQL and OS counters. We keep the .BLG files and load them into a database when we need to look at Hard performance metrics. We have also used RedGate SQL Monitor and Confio Ignite.
The issue with a lot of 3rd party tools is that we don't get the Raw data or have control over it. We get lots of nice charts that give a good overview of performance but I can't say with any confidence that say Disk Queue Length on our Transaction Log drive is down XX.XX% since last week. Or how frequently the metric is collected (every 15 seconds, 30 seconds etc.). Perfmon does a great job of this but it's cumbersome to relog the blg files. Once we import them into a database it seems to be difficult to add indexes to the tables that relog loads them into (CounterData and CounterDetails).
At the same time the server I want to monitor is sensitive and I don't want any negative impact during production hours. Should I just query the Performance DMV's every 15 seconds and store the data? Again I'm concerned about negatively impacting the server.
If anyone has any insights that would be amazing! Thanks!