Do it your self perfmon

At SQL PASS I attended Adam Machanic pre-con one day class of 

No More Guessing! An Enlightened Approach to Performance Troubleshooting [DBA-399-P] and what a blast. I really liked the well structured way of storing the recorded samples. With the risk of being #sqlsue I will try to lay out his idea on the database structure. He has two timing tables. Dates which holds all dates from today and until 2020. One record for each date with a unique dateid (int) and then all the components of the date as a separate column like mount, day, year, quater, week, day number in week and year and so on. The same with the time. That table holds a record for each 15 seconds on a day. And the smart thing is that all times has been marked with a segment of day like morning, busy period and so on. And a 15 min segment time.  All recorded data is then stored with a dateid and a timeid. This means that your are able to easily see how all parameters has performed on exactly this time of day the last month to see if today or any other day behave abnormally. Or your can see how the behavior is the last day of the month compared to all the other last day of month. And so on. I have done a lot of perf mon recording but that time layout never struck me. I always had to deal with the slow extraction of datetime and between. So that I really liked.  He also had a beta version of his datacollector which can monitor and store data in that format. It works very well but wasn't that good on a lot of instances on the same server - which I have. You could set it up to use instances but that meant adding a counter for each server instance instead of just adding the instances and have the collector pick it up. Adam will make this in a up-coming version. But I though it was fun to make the data collector my self. That is actually my main work programming data collectors for everything from radars to stuck exchanges. So this should be fun. Actually it's quite easy to get perfmon data from the server using C#.                      // Create the counter                     Counter = new PerformanceCounter(CategoryName, CounterName, InstanceName, MachineName);                     // "Prime the pump" by getting the first value                     Counter.NextValue(); PerformanceCounter is part of System.Dianostics and you just give it like Category like "PhysicalDisk" Counter like "Avg. Disk sec/Read" Instance like "1 C:" Machine like "YourServer" Note instance has nothing to do with SQL Server instance. You can just use PerfMon to see the categories, counters and instances. Then every time you call Counter.NextValue() you get the current value. Create as many counters as you like. I have 192 counters in total for all my instances (4) and it takes like 0.2 secs to sample and the data is send to database all in all around 0.4 secs every 15 seconds for the counters and a few wait_stat, file_stat and query_stat queries. If you want to get all the instances for the counters for a given category use PerformanceCounterCategory cat = new PerformanceCounterCategory(CategoryName, MachineName); cat.GetInstanceNames() Note that all counters in the given category always have the same instances. If you don't want to go into the more complex thread programming you could just use a Timer with a callback function to fire every 15 seconds and you are on your way. No mater how you do it - it's a really good idea to have a baseline sample. Because when things go bad you actually can see where it goes wrong just by comparing it to your baseline. PerfMon don't tell you how it usually is only how it is now. And you need as least a month of good sampling data to establishing a good baseline. So better get started. But the really genius is in storing the records with that very well segmented date and time fragmentation. That really gives you a heads up on exactly how everything looks this time of day. So thanks to Adam for sharing his database layout and hints for baseline.