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,
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
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.
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,