Why is my more powerful server slower than my laptop (same database, same query)?

[cpnet]

I have an SSAS database and when I run a query on it on my laptop, it
returns in about 1 second. When I run the exact same query on the same
database on the server it takes about 30 seconds. I'd like to know where
the best places to look for a performance bottleneck are.

The laptop is my development machine and typically has my email, VS 2008,
Anti-Virus etc. etc. running when I test its speed. Here's its specs:
- Win XP pro SP 3 (32 bit)
- SQL Server 2008 Developer Edition
- 4 GB Ram
- Intel Core 2 Duo dual core processor

The server has the following specs:
- It's running in a virtual machine
- the host OS is Windows 2008 (I'm not sure what version)
- the guest is Windows 2008 Standard
- SQL Server 2008 Enterprise Edition
- 32 GB Ram on the host, 8 GB on the guest
- Xeon Processor(s) (8 cores) on the host, 2 cores dedicated to the guest
- Almost nothing running except SQL Server and SSAS
- The test database was backed up from my laptop and restored to the server,
so I know it's the exact same database, partitions, aggregations etc.

When I watch a query run on either machine, a single query never seems to
use more than 50% of CPU time (I assume SSAS can't use more than one core
per query)? Both my laptop and the server have lots of free memory. When
I try to zip/unzip large files, the server is much faster so I think the
server is definitely capable of running things faster than my dev laptop.

What could make the server run a query 30 times slower than my laptop?

[Charles Wang [MSFT]]

Is your server also serving for other applications with heavy load on
network/disk etc? You can use Performance Monitor to monitor this. Another
reason is probably related to cache. What is the result if you perform a
full process of your cube on your server?

Best regards,
Charles Wang

[cpnet]

Hi Charles,

The server is only being used for SSAS (the DB Engine on the server is used
during processing of my SSAS database, but not when I'm running queries).
During my testing, I'm the only one running queries. While running my
query, the Server's CPU never goes above 50% for the SSAS process and the
total CPU usage generally doesn't go above 55% because nothing else much is
running. The server has 75% - 80% of it's memory free and network and hard
drive activity on the server are minimal.

My typical test process has been to reprocess the SSAS database, then
restart SQL Server and SSAS (to free up memory they've grabbed during
processing), then test the query by running it a few times.

The server is 2-3 times faster than my laptop at processing the database,
but running the query is still 20-30 times slower on the server.

[Charles Wang [MSFT]]

Hi cpnet,
Probably this is related to existing warm cache at your local server. To
isolate if this is the cause, I recommend that you run the XMLA script like
the following on your local server to clear the cache and then execute your
MDX query to see if it runs as fast as before:
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ClearCache>
<Object>
<DatabaseID>ASTest</DatabaseID>
<CubeID>Sales</CubeID>
</Object>
</ClearCache>
</Batch>

Best regards,
Charles Wang

[Aaron Kempf, MCITP: DBA]

I'd also tell us more about your processors..

You have a core 2 duo high end processor on your laptop?
You have a quad-core 1.6ghz on the server?

It's entirely possible that you're on a virtual server, etc

"cpnet" <cp...@newsgroup.nospam> wrote in message
news:%23WM2xBK...@TK2MSFTNGP02.phx.gbl...