By popular request, the SQL Customer Advisory Team has collected the lessons learned about tuning data loading into a new whitepaper.
It’s callled the “Data Loading Performance Guide” and you can find it on MDSN: http://msdn.microsoft.com/en-us/library/dd425070.aspx
In this paper, you can find all the tuning tricks they applied to achieve the ETL World Record and a full tuning methodology. They also provide a full overview of the bulk load methods and solutions to common data loading scenarios.
Carl Rabeler from the SQL Server Customer Advisory Team, Best Practices group have released a 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
Download it here: http://www.codeplex.com/SQLSrvAnalysisSrvcs
I found the tool at the great guys from SQLCat
