XMLA query is very slow
I have two different machines with different hardware configuration. Executing XMLA "create" command in powerful machine takes more time than machine with less memory and processing power.
Machine1(takes 4 second to execute xmla command to create olap database including cubes, measures and dimensions):
CPU: 8 Core 2.66 GHz Intel Xeon 64 bit
Memory : 8190
OS Version : Windows 2003 Standard x64 Edition Service Pack 2
SQL Server 2005 - 9.00.3042.00 - SP2    Developer Edition (64-bit)
Microsoft SQL Server Management Studio      9.00.3042.00
Microsoft Analysis Services Client Tools          2005.090.3042.00
Microsoft Data Access Components (MDAC)    2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML                                           2.6 3.0 6.0
Microsoft Internet Explorer                             7.0.5730.13
Microsoft .NET Framework                              2.0.50727.1433
Operating System                                         5.2.3790

Machine2(takes 18 second to execute xmla command to create olap database including cubes, measures and dimensions):
CPU: 16 Core 2.40 GHz Intel Xeon 64 bit
Memory : 16381
OS Version : Windows 2003 Standard x64 Edition Service Pack 2
SQL Server 2005 - 9.00.3042.00 - SP2    Developer Edition (64-bit)
Microsoft SQL Server Management Studio       9.00.3042.00
Microsoft Analysis Services Client Tools          2005.090.3042.00
Microsoft Data Access Components (MDAC)    2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML                                           2.6 3.0 6.0
Microsoft Internet Explorer                             7.0.5730.13
Microsoft .NET Framework                               2.0.50727.3082
Operating System                                          5.2.3790


At software level, everything is same except .NET framework version. Even though machine2 has more horsepower than machine1, creating olap objects using xmla command is much slower in machine2. While creating olap objects using xmla command, the database is locked out and it cannot be accessed until xlma command execution completes. In machine1, this issue is not noticed because lock time is very less.

Any idea why xmla query execution is slow in machine2?

Thanks,
January 20th, 2010 7:06pm

We are having the same issue:

The powerful machine takes 180 seconds to run a simple XMLA create partition

while the simple machine takes only 2 seconds for the same XMLA script which is the normal time it should take

Please advise what may be the cause

Thanks


Free Windows Admin Tool Kit Click here and download it now
August 13th, 2015 7:43am

There are at least 2 likely possible causes for this that come to mind

1) Existing long running queries.

If there are users running long running queries (that take 180 seconds or more for example) then the CREATE/ALTER command may have to wait for those queries to complete before it can get an exclusive lock on the database to do it's work. If you open another SSMS window before running the create you should be able to run SELECT * FROM $SYSTEM.DISCOVER_LOCKS and you will see the create session has locks with a status of 0 which means that the lock is pending (not yet granted)

2) Issues with the disk sub-system

CREATE commands need to write the structure of the new database to disk. If you are altering an existing database then the new structure is temporarily written to a "shadow" copy of the database, then once that is complete the existing files are deleted, then the "shadow" version is marked as the current version and the transaction is committed.

So if you are altering a large database with a lot of data then it can sometimes take a long time for the old files to be deleted or for the data to be copied over to the new "shadow" copy (it depends on what sort of structural changes you have made as to which files are deleted and which are copied)

So I would suggest starting by looking at the disk counters in perfmon to see what the read and write times are like and if you are getting any queues build up.

It could be that there is more work that needs to be done on your bigger server, but if not I would be look for faults/configuration issues in storage system. You could maybe use a tool like SQLIO to benchmark the disk performance of both systems.

August 13th, 2015 9:11pm

Just to add a very small point. The "powerful" machine has a slower cpu. The fact that it has twice as many cpus wouldn't matter if the machine isn't busy. Creating a db (without processing) is largely single threaded. You haven't said above if the cube is processed during creation?

Note, the cpu on the "powerful" machine is only 10% slower, so it would not account for the difference in speed on its own. But it will be contributing. Also note, you haven't mentioned hyperthreading (I presume non) and you haven't mentioned L2 cache. Again, I presume the same?

Hope that helps,

Free Windows Admin Tool Kit Click here and download it now
August 18th, 2015 1:03am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics