When Queries Zone Out in RS - The Unknown Element

Frequently the report developers will approach you with a problem: A query that runs lightening fast in SQL Server Management Studio completely tanks when the query is hosted by a Reporting Services report.

RS 2008 makes calls to the Data Source in a slightly different fashion than a call by other clients. One major difference is that parameters are called using Unicode. This can result in table scans if the destination objects are not in Unicode.

Besides checking for the usual issues (table scans, indexes, etc.), I've had good results with the inclusion of the Optimize for Unknown optimizer hint. This has restored performance for many queries, from minutes to seconds. Give it a try. See details below:

http://blogs.msdn.com/mssqlisv/archive/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature.aspx

Back in the Swim

This blog has been woefully neglected. Look for blogs on the following subjects in the new few weeks:

 

• Dimensional modeling tips for a Mart based upon Epic Clarity data.
• Use of SQL Server's Spatial fields - adding geospatial to a data mart.
• Geocoding data - on the cheap.