But just to be sure lets profile for a minute where we really spend our CPU ticks.My favorite tool for a quick & dirty check is Kernrate (or Xperf if you prefer).The MERGE syntax just takes a bit of explaining, and Rob Sheldon is, as always, on hand to explain with plenty of examples.Starting with SQL Server 2008, you can use a MERGE statement to modify data in a target table based on data in a source table.(its up to you to revert them and save the planet when testing is done…) For example: – Enter the Bios Power options menu and see if you can disable settings like ‘Processor Power Idle state’.– In the Windows Control Panel, set the Server Power Plan to max.if you run SSAS on a separate server and you have to pull all the data from a database running on another box, expect the base throughput to be significant less due to processing on the network stack and round tripping overhead.The tricks that apply to the side by side processing also apply in this scenario: 1) Process the Partition processing baseline against the remote server.
When the SQL MERGE statement was introduced in SQL Server 2008, it allowed database programmers to replace reams of messy code with something quick, simple and maintainable.This Part 1 is about tuning just the processing of a single partition. Well to quantify the effective processing throughput, just looking at Windows Task Manager and check if the CPU’s run at 100% full load isn’t enough; the metric that works best for me is the ‘Rows read/sec’ counter that you can find in the Windows Performance monitor MSOLAP Processing object. looking back in history, the first SSAS 2000 cube I ever processed was capable of handling 75.000 Rows read/sec, but that was before partitioning was introduced; 8 years ago, on a 64 CPU Unisys ES7000 server with SQL- and SSAS 2005 running side by side I managed to process many partitions in parallel and effective process 5 Million Rows reads/sec (== 85K Rows read/sec per core).Today, with SSAS 2012 your server should be able to process much more data; if you run SQL and SSAS side by side on a server or on your laptop you will be surprise on how fast you can process a single partition; expect 250-450K Rows read/sec while maxing out a single CPU at 100%.I created the examples on a local instance of SQL Server 2008.To try them out, you’ll need to first run the following script to create and populate the tables used in the examples: As you can see, the script creates and populates the Book Inventory and Book Order tables.