Data Mining your performance metrics - uncover that nugget...

SQL Server

Data Mining is relatively new technology but it provides extremely powerful functionality for our Business Intelligence solutions. The typical Data Warehouse may be multiple terabytes so how do we sift through that data to uncover data patterns and valuable nuggets of information? Recently, I have been discussing "Performance" Data Warehouses. Can we apply the same strategies there too? Of course...

The Data Collector in SQL Server 2008 produces a Management Data Warehouse (MDW) containing performance metrics that can be analyzed as a whole, or drilled down to uncover specific performance issues. In SQL Server 2008 R2 we have the Utility Management Data Warehouse (UMDW) which does the same thing but as a central repository for multiple servers. Once we have set up this UMDW, how can we analyze the metrics contained in it?

Well, the answer is the same tools that our Business users use: tools like Reporting Services, Performance Point Server and for ad-hoc analysis: Excel. Microsoft has provided a neat Add-In for Data Mining via Excel 2007. It uses Analysis Services on the back-end. This means we can extract information from our UMDW and perform some Data Mining algorithms on the data to uncover some patterns and trends. Instead of Business data we are analyzing SQL Server performance metrics. This Add-In can also be used on data extracted from Profiler, Perfmon or straight from our favorite DMVs.

One of my students pointed me to a great web site that describes this concept: Brent Ozar's blog. He works for Quest Software and provides some awesome links for database performance and tuning topics. Check it out!

http://www.brentozar.com/sql-server-performance-tuning/data-mining-perfmon-profiler-data/

cheers
Brian

• Microsoft
• Add-in
• data mining
• Excel 2007
• performance
• SQL Server