Collect and Store Historical Performance Counter Data Using DMVs
In my preparations for all my presentations for 2011 regarding Dynamic Management Views and Functions as well as my consolidation efforts of instances at work, I found myself mulling over the easiest way to capture some of the metrics I would be using for determining current load on my SQL Server instances and capacity planning issues in general. I wanted to get an idea for how busy my servers were as they were currently configured, how well (or not so well) they utilized their current resources and configurations, and what would be optimal moving forward.
Insight is always clouded by the tools you're given
We have a number of servers in our environment that are aging out of warrantee and our plans are to replace the hardware. The server team, as agents of change in this regard, need to work within the budgets they’ve been given. At the same time, as the SQL DBA and SME, it’s my responsibility to craft the best solution for serving SQL-based data to the customers. Two of the servers are the two nodes of our shared SQL Server 2005 Enterprise Edition database cluster. It was the first cluster that I ever installed as well as the first cluster our Server Team had built in the new clustering architecture that was Windows 2003. The budget is pushing the Server Team towards an HP blade solution that will allow us to add a third node and a much needed dedicated failover option for the cluster. We have significant SQL licensing available after our annual true-up however and I’m looking at the three-year plan and not an immediate fix that will allow us to use new hardware that is coming in soon and is targeted as our host servers for our newest VM environment. Under my plan we would re-purpose 4-5 of those much more expensive (but quite beefier) UCS B230 blades servers and consolidate not only the aged hardware slated for replacement, but perhaps as much as 60%-70% of our SQL Server 2005 and 2008 databases onto 5-8 instances scaled up on this new cluster. I need to make the case that this reduction in physical servers via consolidation onto perhaps 5 quite expensive servers saves us money over the long term to a significant enough measure that my Director will buy into the plan and rain down suitcases full of money like a larger and more-human version of Howie Mandel.
That is what led me to tap into my knowledge and love for the DMVs as a method for at least capturing memory, cpu, size, and some activity metrics from sys.dm_os_performance_counters which are the subject of the latest tip on mssqltips.com. I’ve followed up that article with ones on querying the the collected counter information for buffer pool and plan cache information. Those tips are in the can and ready for publishing. I’m also following those up with articles on adding and removing watched counters across all registered servers and analyzing database and log size and growth trending as well as observing cursor activity and its effect on memory consumption and the instance performance in general. The tip I’m referring to can be found here.