Prologika Forums

Analysis Services Processing and CXPACKET Waits

Teo Lachev — Tue, 15 May 2012 02:12:14 GMT

Performance tuning – my favorite! This blog originated from a quest to reduce the processing time of an SSAS cube which loads some 2.5 billion rows and includes DISCINTCT COUNT measure groups. The initial time to fully process the cube was about 50 minutes on a dedicated DELL PowerEdge R810 server, with 256 GB RAM and two physical processors (32 cores total). Both the SSAS and database servers were underutilizing the CPU resources with SSAS about 60-70 utilizations and the database server about 20-30 CPU utilization. What was the bottleneck?

By using the sys.dm_os_waiting_tasks DMV like the statement below (you can use also the SQL Server Activity Monitor), we saw a high number of CXPACKET wait types.

SELECT dm_ws.wait_duration_ms,

dm_ws.wait_type,

dm_es.status,

dm_t.TEXT,

dm_qp.query_plan,

dm_ws.session_ID,

dm_es.cpu_time,

dm_es.memory_usage,

dm_es.logical_reads,

dm_es.total_elapsed_time,

dm_es.program_name,

DB_NAME(dm_r.database_id) DatabaseName,

-- Optional columns

dm_ws.blocking_session_id,

dm_r.wait_resource,

dm_es.login_name,

dm_r.command,

dm_r.last_wait_type

FROM sys.dm_os_waiting_tasks dm_ws

INNER JOIN sys.dm_exec_requests dm_r ON dm_ws.session_id = dm_r.session_id

INNER JOIN sys.dm_exec_sessions dm_es ON dm_es.session_id = dm_r.session_id

CROSS APPLY sys.dm_exec_sql_text (dm_r.sql_handle) dm_t

CROSS APPLY sys.dm_exec_query_plan (dm_r.plan_handle) dm_qp

WHERE dm_es.is_user_process = 1

The typical advice given to address CXPACKET waits is to decrease the SQL parallelism by using the MAXDOP setting. This might help in some isolated scenarios, such as UPDATE or DELETE queries. However, the SQL Sentry Plan Explorer showed that each processing query is highly parallelized to utilize all cores. Notice in the screenshot below, that thread 16 fetches only 14,803 rows.

Therefore, the CXPACKET waits were simply caused by faster threads waiting for other threads to finish. In other words, CXPACKET wait is just a coordination mechanism between the threads being parallelized. To confirm this, we set the SQL Server MAXDOP setting to 1. Surely, the CXPACKET waits disappeared but the overall cube processing time went up as well. In our case, the biggest benefit was realized not by decreasing the SQL Server parallelism but by increasing it, by increasing the maximum number of database connections. This resulted in decreasing the overall processing time some 20%.

You need to be careful here though. While increasing the connections to max out the CPU on the SSAS server will yield the biggest gain, it might also slow down other processing, such as reports that query the cube while the database is being processed. So, as a rule of thumb, target no more than 80% CPU utilization to leave room for other tasks.

SQL Server 2012 ETL Framework Features in the New SSIS Project Deployment Model

Teo Lachev — Sun, 29 Apr 2012 21:06:00 GMT

When moving to SQL Server 2012, ETL developers would need to rethink custom ETL frameworks. If you embrace the new project deployment model, SQL Server 2012 offers a lot of plumbing features out of the box. Jen Underwood wrote a great SQL Server 2012 ETL Framework Features in the New SSIS Catalog article about what supported and what's missing. I guess in most cases developers will find that they'll need to implement incremental features on top of the Microsoft provided toolset that are not supported, such as restartability and execution control, while letting SSIS 2012 handle logging and performance reporting. Another great resource for getting started with the new project deployment model is the SSIS Logging in Denali blog by Jammie Thompson.

We ran into an interesting snag when validating and executing packages using the new project deployment model with Windows integrated security:

Developer initiates the validation and execution in SSMS from his machine.
Packages are deployed to a dedicated SQL Server and packages use Windows integrated security to connect to a SQL Server database on a different server.
The source SQL Server database is on a different server.

As you've probably guessed it, this scenario requires configuring Kerberos delegation and registering SPNs for both the SQL Server hosting the SSIS packages and the SQL Server hosting the source database. That's because validation and execution happen under your login and a double-hop is required to delegate your credentials to the source SQL Server. This can be avoided by using standard security (username and password) to establish connections from your packages to the source SQL Server. This wasn't an option in our case because customer requirements dictated using Windows security to SQL Server.

I personally believe that the new Integration Services enhancements alone warrant upgrading to SQL Server 2012.

Reducing Analysis Services Index Processing Time

Teo Lachev — Sun, 15 Apr 2012 13:26:55 GMT

Scenario: Processing a 100 GB SSAS 2012 cube on a dedicated DELL PowerEdge server (128 GB RAM with 32 logical cores) reveals that 50% of the time is spent on processing the cube indexes and building aggregations with only 20% average CPU utilization and not even a small dent to the memory consumption. It was clear that SSAS doesn't utilize the server resources and additional tuning is required to increase the parallelism of this phase.

Solution: Following Greg Galloway's advice, we've changed the OLAP\Process\AggregationMemoryLimitMin to 2 and OLAP\Process\AggregationMemoryLimitMin to 3 to increase the parallelism of the index processing. This led to 25% reduction of the overall processing time and increased the CPU utilization to 70-80%. As to how we derived to these numbers, James Rogers has a great write-up. For the sake of preserving the precious formulas:

AggregationMemoryLimitMax=Ceiling(100/(<number processing cores>-2))

AggregationMemoryLimitMin=Ceiling(100/((<number processing cores>-2)*1.5))

In case you're curious about what these setting do, the amount of memory needed to build an aggregation is unknown up front. The engine has to estimate it based of certain factors (estimated rows, granularity attributes, measures, etc.) – and it uses the AggregationMemoryLimitMin value to ensure that if it's estimate is wrong, that at least there will be a sufficient buffer for underestimation. Similarly, if the estimate is wrong in the upward direction, it will use the AggregationMemoryLimitMax value to trim the max value downward.

Presenting at SQL Saturday

Teo Lachev — Mon, 09 Apr 2012 13:17:54 GMT

I have a talk "The Personal-Team-Organizational BI Conundrum" at SQL Saturday on April 14^th at 9 AM.

Confused about the BI alphabetical soup? Not sure which one makes sense for your organization? Join this session to learn you can use the Microsoft BI platform to address various analytical needs. Discover how to implement the Personal-Team-Organizational continuum on a single platform. I'll also discuss how Tabular and Multidimensional compare.

I hope you can make it. SQL Saturday is always a good show and this year we have great speakers and great BI content.

Report Builder Connectivity Issues

Teo Lachev — Wed, 28 Mar 2012 13:38:00 GMT

Issue: Previewing a Report Builder report connected to Analysis Services works in Report Designer but it fails in Report Builder 3.0 with the following error:

An existing connection was forcibly closed by the remote host

----------------------------

Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.

----------------------------

Further, SQL Profiler reveals that an Anonymous connection is attempted to Analysis Services even though you use an embedded report data source (using a shared data source on the server will run the report on the server while with an embedded data source results the report runs on the client).

Resolution: Check if Report Builder is connected to a report server running in SharePoint mode by examining the status bar. If it is, click the Disconnect link. Report preview should now work.

Finding Source Column Names in PowerPivot

Teo Lachev — Wed, 28 Mar 2012 13:29:00 GMT

Question: How do I find the source column name in PowerPivot if I've renamed and moved columns around?

Answer: Assuming that the table doesn't use a custom query to import data or it wasn't derived from an Excel linked table or Windows Clipboard, you can use the table properties to find the source column name as follows:

In the PowerPivot Window, click the table to select it.
In the Design ribbon tab, click Table Properties.
Make sure that the Column Names From radio button is set to Source.

VertiPaq Rebranded as xVelocity

Teo Lachev — Thu, 22 Mar 2012 12:40:07 GMT

Apparently, the name "VertiPaq" wasn't catchy enough so Microsoft rebranded it as xVelocity (probably got tipped by Comcast). This is just a name change, no new features.

Glossary

xVelocity: The SQL Server family of technologies that utilize in-memory columnar storage to achieve very high-performance in query processing.

xVelocity for Business Intelligence
Introduced as VertiPaq in the SQL Server 2008 R2 release, this in-memory columnar storage technology is the analytical engine that powered PowerPivot in that release, and is now in both PowerPivot and SQL Server Analysis Services in Tabular mode for the SQL Server 2012 release. xVelocity for BI consists of:

xVelocity In-memory Analytics Engine in SSAS 2012 Tabular Mode (used for enterprise-grade, scalable BI)
xVelocity In-memory Analytics Engine (previously called Vertipaq) in PowerPivot (used for self-service and team-oriented BI scenarios), available in PowerPivot for Excel and SharePoint Server

xVelocity for Data Warehousing
Memory-optimized columnar (columnstore) index technology for use with SQL Server Data Warehouses. Data is stored in column-wise fashion that can be used to answer a query just like data in any other type of index. A columnstore index appears as an index on a table when examining catalog views or the Object Explorer in Management Studio. The query optimizer considers the columnstore index as a data source for accessing data just like it considers other indexes when creating a query plan.

Exceptions

In cases where the VertiPaq name appears in the product (VertiPaqpagingpolicy parameter, VertiPaq SE event, etc.), please continue to use VertiPaq, or xVelocity (VertiPaq) if necessary to avoid confusion.

BIDS Helper 1.6 Beta Released

Teo Lachev — Tue, 20 Mar 2012 17:54:57 GMT

Fellow MVPs have just released the latest public beta build of BIDS Helper, which should one of the first utilities you install after you install SQL Server on your machine. Besides fixes and updates, this release adds support for SQL Server 2012 and new features specific to Analysis Services Tabular.

This beta release is the first to support SQL Server 2012 (in addition to SQL Server 2005, 2008, and 2008 R2). Since it is marked as a beta release, we are looking for bug reports in the next few months as you use BIDS Helper on real projects. In addition to getting all existing BIDS Helperfunctionality working appropriately in SQL Server 2012 (SSDT), the following features are new.

Analysis Services Tabular
Smart Diff
Tabular Actions Editor
Tabular HideMemberIf
Tabular Pre-Build

Hiding SQL Server 2012 Reporting Services Extensions in SharePoint

Teo Lachev — Thu, 15 Mar 2012 17:16:34 GMT

To continue my Report Server Settings in SQL Server 2012 SharePoint Integration Mode blog, here is another example of how to use the new PowerShell-based configuration mechanism to hide a rendering extension. In this case, the script finds an SSRS application called SQL Server SSRS (replace with the name of your SSRS application from SharePoint Central Admin) and hides the XML renderer so the XML export option doesn't appear in the Actions drop-down.

$apps = Get-SPRSServiceApplication | where {$_.name -like "SQL Server SSRS"}

Set-SPRSExtension -identity $apps -ExtensionType "Render" -name "XML" -ExtensionAttributes "<Visible>False</Visible>"

Can’t Activate Reporting Services Service in SharePoint

Teo Lachev — Thu, 15 Mar 2012 15:12:00 GMT

I upgraded SQL Server 2012 from RC0 to RTM today on a SharePoint application server which was configured for Reporting Services integration. The upgrade went with no errors but I got this error when requesting reports:

The requested service, 'http://amatltapp02:32843/1dacf49a2f7a4a6daa8db5768539893f/ReportingWebService.svc' could not be activated. See the server's diagnostic trace logs for more information.
- The requested service, 'http://amatltapp02:32843/1dacf49a2f7a4a6daa8db5768539893f/ReportingWebService.svc' could not be activated. See the server's diagnostic trace logs for more information.
And, when browsing directly the service:
After some digging out, we solved the issue by changing the Enable 32-Bit Applications property of the IIS application pool for the SharePoint web application to False.

Server Error in '/1dacf49a2f7a4a6daa8db5768539893f' Application.

The farm is unavailable.

Presenting at SQL Server Special Ops Tour Atlanta

Teo Lachev — Fri, 02 Mar 2012 21:59:42 GMT

I've been honored to be selected by Microsoft to present the Self-service BI using PowerPivot and Power View session for the SQL Server Special Ops Tour in Atlanta on Monday, March 5^th.

EVENT AGENDA
Agenda		Speaker
12:30pm – 1:30pm	Registration Opens
1:30pm – 2:30pm	SQL Server 2012 – The New World of Data	Dandy Weyn – FTE – Sr. Tech Prod Mgr
2:30pm – 3:15pm	Introduction to SQL Server 2012 AlwaysON – Availability Groups	Geoff Hiten – MVP – Community
3:15pm – 3:30pm	Break – drinks and light snacks
3:30pm – 4:15pm	Self-Service BI using PowerPivot and Power View	Teo Lachev – MVP – Community
4:15pm – 4:45pm	Understanding Microsoft Training & Certifications for Microsoft SQL Server 2012	Bob Taylor – FTE – Principal PFE
4:45pm – 5:15pm	In Summary	All speakers

Join us to celebrate SQL Server 2012!

Finding Reporting Services Service in SharePoint

Teo Lachev — Fri, 02 Mar 2012 21:47:00 GMT

SharePoint is a like an ocean – the further you go, the deeper it gets

Ancient proverb

Scenario: Configure SQL Server 2012 Reporting Services on a multi-server SharePoint farm with the following topology:

WEB1 – Web front-end Server 1

WEB2 – Web front-end Server 2

APP1 – Application server 1 that hosts the Central Administration site

APP2 – Needs SSRS 2012

Issue: I couldn't find the SQL Server Reporting Services Service Application option when I expanded the New button in Central Administration ð Manage Service Applications

Resolution: Although the SQL Server 2012 Reporting Services service and Reporting Services Add-in were installed on APP2 and APP3, the SQL Server Reporting Services service wasn't registered at a farm level. As a result, I couldn't find the SQL Server Reporting Services Service in Central Admin -> Manage Services on Server on none of the application servers. As it turned out, the SSRS service must be also registered on the application server hosting Central Administration (APP1 in my case) as Prash Shirolkar explains in his blog:

Remote in to APP1.
Install the Reporting Services add-in to get the SSRS PowerShell cmdlets. In my case, I installed only the add-in and not the Reporting Services service since I didn't want it on APP1.
Run the SharePoint 2010 PowerShell as admin and execute the three commands in the Install and Start the Reporting Services SharePoint Service in the Install Reporting Services SharePoint Mode as a Single Server Farm document:
1. Install-SPRSService
2. Install-SPRSServiceProxy
3. get-spserviceinstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance

Interestingly, the first two commands don't echo any output. The third command, however, should show you a message that the SSRS service has been provisioned. Now, you can go to Central Administration ð Manage Services on Server and you should be able to find the SQL Server Reporting Services Service. More importantly, you should be able to go to Central Administration ð Manage Service Applications, expand the New button and then click SQL Server Reporting Services Service Application to finalize the Reporting Services setup.

Possible alternative resolution path

Although I haven't tried it because the client woudn't allow farm configuration changes, another potential resolution path could be to temporarily switch the application server that hosts the Central Administration utility to one of the SSRS application server (APP2 or APP3) as explained in the blog post, "How to change Central Admin Host in SharePoint 2010" by Kirk Barrett. Then, you can register the SSSRS service on that server by executing the steps in item 3 above. Finally, switch back the Central Administration host server to the original server (APP1).

Presenting Personal BI with PowerPivot v2

Teo Lachev — Thu, 23 Feb 2012 17:52:35 GMT

I'll be presenting "Personal BI with PowerPivot v2" for the Atlanta BI Group on Monday, February 27th. Join us to learn when personal BI makes sense and why PowerPivot is the best tool on the market when it does. The focus will be on the new features of PowerPivot version 2 which launch officially in a week or so.

For more information and to register visit our Atlanta BI home page.

Announcing My New Book

Teo Lachev — Sat, 11 Feb 2012 14:55:00 GMT

My new book, Applied Microsoft SQL Server 2012 Analysis Services (Tabular Modeling), will start shipping in a week with all popular resellers, such as Amazon, B&N, etc. I've been working on it for a few months and I'm excited to have it done. While waiting to buy the book, check the following resources to get you started with PowerPivot and Tabular:

- A sample chapter – "Introducing Business Intelligence Semantic Model"

- Video demos – I recorded over three hours of video content for selected exercises in the book

An insightful tour that provides an authoritative yet independent view of this exciting technology, this guide introduces the Tabular side of the innovative Business Intelligence Semantic Model (BISM) that promotes rapid professional and self-service BI application development. Business analysts and power users will learn how to integrate data from multiple data sources, implement self-service BI applications with Excel, and deploy them to SharePoint. Business intelligence professionals and database administrators will discover how to build corporate solutions powered by BISM Tabular, delivering supreme performance with large data volumes, and how to implement a wide range of solutions for the entire spectrum of personal-team-organizational BI needs.

WHAT'S INSIDE:
Chapter 1: Introducing Business Intelligence Semantic Model

PART I: PERSONAL BI WITH POWERPIVOT FOR EXCEL
Chapter 2: Personal BI Basics
Chapter 3: Importing Data
Chapter 4: Refining the Model
Chapter 5: Analyzing Data
Chapter 6: Implementing Calculations

PART II: TEAM BI WITH POWERPIVOT FOR SHAREPOINT
Chapter 7: Team BI Basics
Chapter 8: SharePoint Insights
Chapter 9: Managing PowerPivot for SharePoint

PART III: ORGANIZATIONAL BI WITH ANALYSIS SERVICES TABULAR
Chapter 10: Organizational BI Basics
Chapter 11: Designing Storage and Security
Chapter 12: Managing Tabular Models

Enoy!

ProcessUpdate and Partition Scans

Teo Lachev — Tue, 07 Feb 2012 16:42:07 GMT

I'm working on minimizing the cube processing for a cube with some two billion rows in a fact table. I put together an ETL package that processes the dimensions with ProcessUpdate following by processing the latest partitions. I've noticed that processing one of the dimensions , which happens to be the largest (some 1 million rows) and most complicated dimension, resulted in partitions scans. The SQL Profiler showed the scans with the following events:

Finished processing the '<partition name>' partition

These partition scans don't result in SQL queries to the database and normally should execute pretty fast. In this case, however, the scans were taking plenty of time resulting in some 15 minutes delay in the incremental processing flow. With some help from T.K. Anand, it turned out that I had a design issue with that dimension. I discovered the issue by changing the KeyDuplicate error in the dimension ErrorConfiguration to True and fully processing the dimension. Dimension processing failed because of the attribute relationships implied a one-to-many relationship between two attributes but the data didn't support it. Somewhere along the design cycle, I turned off KeyDuplicate probably to get around the same issue in order to process the dimension successfully.

So, the moral of this story is:

Always check your dimensions with the Dimension Health Check feature of the BIDS Helper.
Don't turn off KeyDuplicate.

BTW, the problem with duplicate key errors is that the server can move members around and that would cause indexes and aggregations to require being rebuilt. For example, if you have the following members in the source data:

Attr1Key Attr2Key

55 32

55 35

The first time, the engine may store 32 as the related member. But the second time it sees the rows during ProcessUpdate, it would potentially choose 35 as the related member. And that's going to cause indexes and aggregations to need rebuilding.

Record Attendance for Atlanta BI Group Last Night

Teo Lachev — Wed, 01 Feb 2012 01:16:00 GMT

We had the pleasure to have some 70 people attending our January 30^th, 2012 meeting of the Atlanta BI group. Our sponsor, Matrix Resources, was kind enough to give us the auditorium. FisionIO sponsored the meeting. Phil Per-Lee did us a "Prototypes with Pizza" presentation, titled Connecting the Dots. And, Carlos Rodrigues rocked the stage with the main presentation about dimensional modeling.

I've uploaded pictures to the Photo Gallery section of our website and the slides to the Resources section. We've got some cool presentations lined up for next few months. Check our Calendar section to see what's coming.

Downloading Multiple Files from SharePoint using WebDav

Teo Lachev — Thu, 19 Jan 2012 15:06:57 GMT

I had to download many ASP.NET pages from the SharePoint Pages library that were used to wrap up the Reporting Services ReportViewer webpart. I wanted the pages as files, so I could deploy them to another SharePoint server. Unfortunately, SharePoint doesn't support selecting and downloading multiple files. But, it does support the WebDAV protocol.

Open Windows Explorer and type in the following URL:
\\<SharePoint site>\<library>
Example: \\elitex\Pages, where elitex is the SharePoint server and Pages is the document library.
Windows Explorer shows all documents in the library. From there on, just copy the files you need to another folder.

The only caveat is that you need to make sure the WebClient Windows service is running on your laptop. If you're trying to connect from Windows 2008 Server, then make sure the Desktop Experience role is installed as well, which installs that WebClient service.

Subscription and Alerts Issues with Analysis Services in SQL Server 2012

Teo Lachev — Tue, 17 Jan 2012 13:08:00 GMT

As you probably now, Reporting Services doesn't allow you to create subscriptions with data sources that use Windows security because subsbscriptions are run in unattended mode. Moving to SQL Server 2012, we'll add Data Alerts to the list. This presents an issue if you use Analysis Services which only supports Windows security. The only option is to use Stored Credentials with the "Use as Windows credentials" checkbox checked. You won't able to pass the user identity by checking "Set execution context to this account". As with previous releases, "Set execution context to this account" works with the SQL Server data but it doesn't work with the Analysis Services provider.

I raised this issue to Microsoft and I posted a bug report. The issue is under investigation but it's unlikely to get fixed before SQL Server 2012 ships. Please vote!

Online PowerPivot Class

Teo Lachev — Fri, 13 Jan 2012 13:40:36 GMT

I am teaching my online Applied PowerPivot class for personal and team business intelligence on Jan 24^th and 25^th and there are still seats available.

Happy New Year 2012!

Teo Lachev — Sat, 31 Dec 2011 23:08:00 GMT

As 2011 is winding down, it's time to reflect on the past and plan for the future. 2011 has been a very exciting year for Microsoft BI and me.

Gartner positioned Microsoft as a leader in the 2011 Magic Quadrant for Business Intelligence Platforms.
Although SQL Server 2012 will technically ship early next year, we can say it's a done deal as it's currently in a release candidate phase. The most important news from a BI perspective is the evolution of the Business Intelligence Semantic Model (BISM), which an umbrella name for both Multidimensional and Tabular models.
The Tabular model provides us with a nice personal (PowerPivot for Excel)-team (PowerPivot for SharePoint)-organizational (Analysis Services Tabular) continuum on a single platform.
Power View extends the BI reporting toolset with a sleek web-based reporting tool for authoring highly interactive and presentation-ready reports.
In its second release, Master Data Services (MDS) comes out of age and now allows end users to use Excel to manage master data. The newcomer, Data Quality Services (DQS), complements MDS nicely in the never-ending pursuit for clean and trusted data. Integration Services has also nice enhancements. Finally, columnstore indexes will help to aggregate large datasets, such as the scenario I mentioned in this blog.

Looking forward to 2012 and beyond, here is my top 5 BI wish list:

Extending the Tabular capabilities with more professional features, such as scope assignments, role-playing dimensions, MDX query support, and so on, to enhance its reach further in the corporate space. Ideally, I expect at some point in future unification of Multidimensional and Tabular so BI pros don't have to choose a model.
Extending Power View to support multidimensional cubes. Further, in the reporting area, I expect an embeddable web-based OLAP browser (it's time for Dundas Chart to come back to live) and an improved MDX query designer (no, I haven't lost hope for this one).
Enhanced Excel BI capabilities so Excel becomes the BI tool of choice. This includes supporting PowerPivot natively and overhauling the reporting capabilities beyond the venerable PivotTable and PivotChart. Ideally, what I am hoping for is decoupling Power View from SharePoint and integrating it with Excel and custom web applications. Power View is too cool to be confined onlyin SharePoint.
Extending Microsoft Azure with BI capabilities to let solution providers host BI models in the cloud.
Bringing BI to mobile devices.

On the personal side of things, I've been fortunate to stay healthy and busy (very busy). The Atlanta BI group, which I am leading, has grown in size and we now enjoy having 40-50 people attending our monthly meetings. For the past few months, I've been working on my next book, Applied Microsoft SQL Server 2012 Analysis Services (Tabular Modeling), which I expect to get published in March. And, my consulting business has been great!

I wish you a healthy and prosperous year! I hope to meet many of you in 2012. Meanwhile, you can find me online at the usual places: www.prologika.com | blog | linkedin | twitter.

Happy New Year!

Using the Hash Group Hint to Speed up ColumnStore Indexes

Teo Lachev — Wed, 21 Dec 2011 03:52:00 GMT

As I mentioned in my blog post on this subject, I've found a good use of SQL Server 2012 columnstore indexes to speed up significantly ETL processes that need to aggregate large datasets. But we run into a snag, which got promoted to a bug by Microsoft Support Services. Under some conditions, SQL Server would create a plan that uses a "stream aggregate" operator instead of the more efficient hash match aggregate. This is illustrated by the following plans.

This plan uses the stream aggregate and the query is much slower. If you hover on the Sort predicate, you will see a warning that the sort will spill data to tempdb.

By contrast, this plan uses the Hash Match predicate and the query is about three times faster.

As I mentioned, this appears to be a bug with SQL Server 2012, which might not get fixed in RTM. Meanwhile, force your queries to use the HASH GROUP query hint to force SQL Server to use a hash match with columnstore indexes.

GROUP BY Organization_Id,

Item_Profile_Id,

...

OPTION (HASH GROUP)

Upgrade Issues with PerformancePoint 2010 Filters

Teo Lachev — Fri, 16 Dec 2011 01:17:00 GMT

Issue: Migrate a PerformancePoint dashboard from MOSS 2007 to SharePoint 2010. We used the Import PerformancePoint Content feature to import existing dashboards from the PerformancePoint database and this saved us a lot of effort. However, filters failed with this rather obscure error:

After tracing with SQL Profiler and looking at the Windows Event log, we saw the following almost as useless error):

Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: Query (1, 7) Parser: The syntax for '{' is incorrect.

In this case, the dashboard is using a Time Intelligence filter mapped to an Analysis Services dimension.

Resolution: The upgrade process has mapped the filter's formula in the connection to the scorecard. We fixed the issue by mapping the filter data source (not formula), as shown in the screenshot below. In this case, the CompareDay is the data source name.

Columnstore Indexes To Speed ETL

Teo Lachev — Thu, 08 Dec 2011 00:48:00 GMT

SQL Server 2012 introduces columnstore indexes. Using the same in-memory VertiPaq engine that powers PowerPivot and Analysis Services Tabular, columnstore indexes can speed up dramatically SQL queries that aggregate large datasets. For a great introduction to columnstore indexes, see the video presentation, "Columnstore Indexes Unveiled" by Eric Hanson. I personally don't see columnstore indexes as a replacement of Analysis Services because an analytical layer has much more to offer than just better performance. However, in a recent project we've found a great use of columnstore indexes to speed up ETL processes.

Issue: Perform an initial load of a snapshot fact table for inventory analysis from another fact table with one billion rows. For each day, extract some 200 million rows from the source fact table and group these rows into a resulting set of about 300,000 rows to load the snapshot fact table for that day. The initial estimates indicated that that the extraction query alone takes about 15 minutes when using a clustered index. And, that's just for one day. Given this speed, we estimated the initial load could take weeks.

Solution: We upgraded to SQL Server 2012 and created a columnstore index on selected columns from the source fact table. We excluded high-cardinality columns that were not used by the extraction query to reduce the size of the index. Creating the index on the source table (1 billion rows) took about 10 minutes and this is very impressive. The disk footprint of the index was about 4GB. We ran the same extraction query and saw a five-fold performance improvement. This query, which would previously run for 15 minutes with a B-tree clustered index, would now finish in 3 minutes with a columnstore index.

When testing your queries with columnstore indexes, it is important to make sure that the query executes in Batch mode. As you can see in the screenshot below, the query uses the columnstore index and the execution mode is Batch. If it says Row, the query performance degrades significantly. Watch Eric Hanson's presentation to understand why this happens and possible workarounds.

When I did initial tests to test the columnstore index, I ran into a gotcha. I tested a simple "SELECT SUM(X) FROM TABLE" query to find that it executes in a Row mode and the query took about 2 minutes to finish. As usual, the first thing I try doesn't work. As it turned out, currently a columnstore index doesn't support batch mode with scalar aggregates. You need to rewrite the query with a GROUP BY as Eric Hanson explains this in more details in his blog, "Perform Scalar Aggregates and Still get the Benefit of Batch Processing". This is rather unfortunate because every ad-hoc report starts with the end user dropping a measure and the report tool generating such queries.

When testing the performance gain, it's useful to compare how the same query would perform without a columnstore index. Instead of having two tables or dropping the index, you could simply tell SQL Server to ignore the columnstore index, such as:

SELECT … FROM…WHERE…GROUP BY…

OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX)

To sum up, when you are pushing the envelope of traditional B-tree indexes and queries aggregate data, consider SQL Server 2012 columnstore indexes. The query optimizer would automatically favor a columnstore index when it makes sense to use it. Columnstore indexes require a read-only table but the cost of dropping and recreating them is not that high. Or, you could add them to speed up the initial DW load and drop them once the load completes.

What's New in Power View RC0

Teo Lachev — Wed, 23 Nov 2011 01:29:00 GMT

I was just about to write a blog about the new features in the Release Candidate (RC) build of Power View and I saw that Robert had written a great blog already. It's great to see the product coming along so nicely. Can't wait to be able to use multidimensional cubes as data sources!

One feature that stirred a lot of excitement and deserves more attention is Power View export to PowerPoint. When you export a report to PowerPoint, initially you get static slides that show images of the report pages.

Don't be fool however as you can click the Click to Interact button to render the report live. The interactive mode preserves all report interactive features. For example, in the screenshot below I am playing the scatter chart animation.

Behind the scenes, the interactive mode uses a Silverlight alternative hosting control (right-click the object on the slice and click View Code) that points to the deployed report on the SharePoint server. Therefore, a live connection and permissions to SharePoint are required to preview the report inside PowerPoint.

UPDATE 11/29/11

As explained in the SQL Server 2012 RC0 release notes, the pre-release bits of Silverlight 5 are not available in 64-bit. The RTM release will include a 64-bit installer and it looks like it be available pretty soon. Meanwhile, trying to interact with a Power View report exported to PowerPoint 64-bit wil fail with the error "Some controls on this presentation can't be activated. They might not be registered on this computer."

SQL Server 2012 RC is Here

Teo Lachev — Fri, 18 Nov 2011 13:06:26 GMT

The public release of Microsoft SQL Server 2012 RC0 is now available.