Speeding up the All-up BI VPC

There's a lot of stuff on the MS BI VPC.  Server operating system.  SQL Servers & Analysis Services & Reporting Services.  Sharepoint.  IIS.  PerformancePoint.

It's a wonder it even starts up at all.

I'm going to focus on a few ways to speed up the All Up VPC, besides adding more RAM, CPU, and fast disks.

First, get the latest All up BI VPC from MS downloads.

All-up BI VPC

For folks who want to play with our Business Intelligence technologies and don't want to bother with installing everything, you can download a virtual machine image that has just about everything installed you could want, including demo content.

The "All-up BI VPC" has two parts, available at:

Part One (3.4 GB)

Part Two (1.0 GB)

Yes - *giga*bytes. These are compressed, and will unzip to a vhd file for Virtual PC that's over 20GB in size. To run the virtual PC, I highly recommend at least 1GB free memory (so your PC needs at least 2GB). If you can free up more, that's better. Also try to work on a PC with two hard drives, and put this on the non-system drive.

Philo's WebLog : All-up BI VPC

Then do some reverse-engineering of queries to see what is going on.

Step 1 - SQL Profiler - Analysis Services
I noticed that the AdventureWorks_Mfg Analysis Services database uses Proactive Caching.  Disabling this will help a tad, if you're not using it.  You can do this by browsing to the cube in Analysis Services manager and right-clicking on the measure group partition.

Step 2 - SQL Profiler - SQL Server
I noticed that the bcp_BizSystemGetAllApps script is called all the time, and having it return the b.OriginalBMO xml column is a big drag on performance.  I commented the column out of the proc's query with no ill effects (just yet... but beware.)

Step 3 - Add indexes
Add an XML index to the BizSystem table OriginalBMO column in the application database and watch the modeler fly.  Actually, add XML indexes to all XML columns.  Either it's in my head or this improved things dramatically.  No more remote scans...

select * from sys.objects o inner join sys.columns c on c.object_id = o.object_id where c.user_type_id = 241 will get you a list.  There are some in the application database and some in the PPSPlanningSystem database.

Note that any of these changes could break PerformancePoint.  Try at your own risk!

Step 4 - SQL Analysis Services - Under the properties of the bi-vpc, turn off the flight recorder.

Step 5 - SQL Server - Take offline any databases you are not using.  So long ProjectReal, you fake project!

Step 6 - Services - turn off Automatic Updates.  Disk Defragmenter. Office Sharepoint Server Search.  Office Document Conversions services, Windows Sharepoint Services Search.