December 2007 - Posts

New Best Practices Articles Published - Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques

 Carl Rabeler, Program Manager in the SQL CAT Best Practices Team publshed a new white paper:

Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques 

 
 
I quote from his blog (http://blogs.msdn.com/sqlcat/):
Many-to-many dimension relationships in SQL Server 2005 Analysis Services (SSAS) enable you to easily model complex source schemas and provide great analytical capabilities. This capability frequently comes with a substantial cost in query performance due to the runtime join required by Analysis Services to resolve many-to-many queries. This best practices white paper discusses three many-to-many query performance optimization techniques, including how to implement them, and the performance testing results for each technique. It demonstrates that optimizing many-to-many relationships by compressing the common relationships between the many-to-many dimension and the data measure group, and then defining aggregations on both the data measure group and the intermediate measure group yields the best query performance. The results show dramatic improvement in the performance of many-to-many queries as the reduction in size of the intermediate measure group increases. Test results indicate that the greater the amount of compression, the greater the performance benefits—and that these benefits persist as additional fact data is added to the main fact table (and into the data measure group).
The Microsoft Learning Portal for SQL Server 2008 is Now Available

The new SQL Server 2008 Learning Portal contains the latest MSL products including eBooks and e-Learnings, Instructor-lead Trainings and Certification Exams on SQL Server 2008. The site is branded with the same look and feel as the ‘Heros Happen Now’ Campaign to be better integrated with the on-going launch activities. We will continue to update the site as new products become available. So drive your customers and partners to the site to help get them ready for SQL Server 2008 today!

{ Go to the Microsoft Learning Portal for SQL Server 2008 }

Precision Performance for Microsoft SQL Server

RML Utilities

© Microsoft

by Keith Elmore and Bob Dorr

 

The Microsoft SQL Server support team uses several internally-written utilities to make it easier to work on a typical customer support case. These utilities may also be useful to database developers and system administrators who work with Microsoft SQL Server 2000 and Microsoft SQL Server 2005.  The utilities are collectively called the RML Utilities for SQL Server.

 

With the RML Utilities you can answer questions such as the following:

 

·         Which application, database or login is consuming the most resources, and which queries are responsible for that.

·         Whether there were any plan changes for a batch during the time when the trace was captured and how each of those plans performed.

·         What queries are running slower in today's data as compared to a previous set of data.

 

You can also test how the system will behave with some change (different service pack or hot fix build, changing a stored procedure or function, modifying or adding indexes, and so forth) by using the provided tools to replay the trace files against another instance of SQL Server.  If you capture trace during this replay you can use the tools to directly compare to the original baseline capture.

 

This release of the utilities supersedes the previous version, as described in KB 887057.  The previous release continues to be available to customers who do not have SQL Server 2005.  This release supports SQL Server 2005 and SQL Server 2000 trace file formats and should be used any time you have SQL Server 2005 in your environment.  This release contains important bug fixes, new features and performance enhancements over the prior release.

 

·         Supports SQL 2000 and SQL Server 2005

·         Quick Start Guide

·         Updated and Advanced Help Documentation

·         Stress Testing

·         Replay Capabilities

·         SQL Server Upgrade Assistance

·         Advanced Reporting and Performance Analysis

 

The utilities are useful for simulating application testing where using the real application is impractical or impossible.  For example, it may be difficult to generate the same user load in the test environment as you see in production.  You can capture another set of traces while performing the replay and compare the performance characteristics of the replay trace files to the original trace files.

The help file contains a Quick Start section that introduces you to each of the tools by walking through a brief exercise.  You can access the help file by going to Start | Programs | RML Utilities for SQL Server and selecting RML Help.xps.

 

To download the RML Utilities for SQL Server, visit the following Microsoft Web site

RML Utilities for SQL Server (x86) –  http://www.microsoft.com/downloads/details.aspx?FamilyId=7EDFA95A-A32F-440F-A3A8-5160C8DBE926&displaylang=en

RML Utilities for SQL Server (x64) –  http://www.microsoft.com/downloads/details.aspx?FamilyId=B60CDFA3-732E-4347-9C06-2D1F1F84C342&displaylang=en

 

SQL Nexus

The RML Utilities have undergone integration work with SQL Nexus so we can provide SQLDiag/PSSDiag, SQLNexus and the RML Utilities to our customers.   For more information on SQL Nexus visit: http://codeplex.com/Project/ProjectDirectory.aspx?ProjectSearchText=SQL%20Nexus

 

 

Product Improvement

These utilities are extensions to features already shipped with SQL Server.  As outlined in the Help file we continue to work closely with the SQL Server development team to incorporate ideas and designs.   Expect to see continued product advancements in several areas.  For example, we have had discussions with and provided details to several team.  Each team is using the information to make our products better.

 

·         Scalable Replay Team

·         Upgrade Advisor Team

·         SQL Trace and XEvent Teams

·         Performance Warehouse Team

·         Marketing Team

CU# 5 for SQL Server 2005 SP2 Released

Today (12/17/2007) we shipped the Fifth Cumulative Update for SQL Server 2005 SP2 On Time

·         Public KB Article

·         Hotfix Download Location

Some useful DMV queries

in the attached file

 

Do you have the latest SQL Server 2005 Books On Line (September 2007)?

Do you have the latest SQL Server 2005 Books On Line (September 2007)?

 

Just published.

Honestly, I don’t know why it is  September’s and not November’s BOL.

 

http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en

 

BTW, it’s 135 MB!

 

 

Speaking of which, you might want to download the SQL Server 2008 Books Online Community Technology Preview (November 2007)

http://www.microsoft.com/downloads/details.aspx?FamilyID=19db0b42-a5b2-456f-9c5c-f295cdd58d7a&DisplayLang=en

 

Search

Go

This Blog

Tags

Syndication