A Solution for Collecting Analysis Services Performance Data for Performance Analysis at http://www.codeplex.com/SQLSrvAnalysisSrvcs
This codeplex solution automates the collection of SQL Server 2008 performance data from any or all of the following data sources ans stores the collected data into a single SQL Server 2008 relational database:
- SQL Server Profiler Analysis Services trace data
- Performance Monitor counters
- Analysis Services Dynamic Memory Views (DMVs)
This data is collected using an Analysis Services server-side trace, several Integration Services packages, a custom performance monitor collector in Management Data Warehouse, and Transact-SQL stored procedures. This codeplex project also include sample Reporting Services reports utilizing SQL Server stored procedures that correlate and analyze the collected data.These reports enable you to:
- Determine your slowest MDX queries for a specified time period
- Compare the performance of a specified query during different time periods
- Analyze a specific query to determine if it is storage engine or formula engine bound, to view the aggregations utilized, the number of subcubes and the number of partitions
- Analyze processing performance
- Correlate performance monitor counters with the execution of a specific query or processing operation
- Correlate DMVs with the execution of a specific query or processing operation
This solution was updated on 3/6/2009 and again on 3/31/2009 to:
- Consolidate all environment variables into a single script file - this dramatically simplifies installation and operation
- Fix bugs related to named instances - including, but not limited to the following - Performance Monitor counters for Analysis Services named instances must be entirely in capital letters in order to work in the Management Data Warehouse performance counter data collector.
About CarlRabeler
Carl is a Senior Program Manager on the SQL Server Customer Advisory Team, and is an Extended Customer Program Manager with the Reporting Services and Integration Services product groups. Carl works closely with internal and external customers to develop solutions and validate SQL Server business intelligence best practices for working with large scale BI solutions. Carl has worked with Microsoft SQL Server for over 13 years as a consultant, mentor, writer, speaker and trainer. Carl has written several SQL Server books and numerous technical white papers, has presented at many SQL Server conferences, and has provided mentoring and consulting services for numerous companies around the world.
|
sql, server, best practices, whitepapers, analysis services, data mining, olap, datawarehouse, datawarehousing, availability, clustering, capacity, collation, data types, data warehouse, database, design, index, mirroring, optimization, partitions, performance, precision, processing, querying, scalability, security, reporting services, integration services
|
|
