Re: AS2005 x64 vs x86 performance

I've traced with profiler the XMLA sent to AS. I've modified a role diving
it Admin priviledges. The management studio sent 2 XMLA to as wich total
execution time was around 15 sec. I was alone on the server.
Each execution made one execution unit 100%.

In the meantime the server was not able to respond queries sent to other
database. As soon as the XMLA was executed the queries get answered.

I have AS 2005 EE x64., 2x processor license.


Thanks,
Radu


--- first
<Alter AllowCreate="true" ObjectExpansion="ObjectProperties"
xmlns="http://schemas.microsoft.com/analysisservices/2003/engine";>
<Object>
<DatabaseID>ROEFIX_PROD</DatabaseID>
<RoleID>Einkauf</RoleID>
</Object>
<ObjectDefinition>
<Role xmlns:xsd="http://www.w3.org/2001/XMLSchema";
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";>
<ID>Einkauf</ID>
<Name>Einkauf</Name>
<Members>
<Member>
<Name>RRO\escharin</Name>
</Member>
<Member>
<Name>RRO\agut</Name>
</Member>
</Members>
</Role>
</ObjectDefinition>
</Alter>

<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">
<Timeout>0</Timeout>
<SspropInitAppName>Microsoft SQL Server Management
Studio</SspropInitAppName>
<LocaleIdentifier>3079</LocaleIdentifier>
<ClientProcessID>2076</ClientProcessID>
</PropertyList>

--- second
<Alter AllowCreate="true" ObjectExpansion="ObjectProperties"
xmlns="http://schemas.microsoft.com/analysisservices/2003/engine";>
<Object>
<DatabaseID>ROEFIX_PROD</DatabaseID>
<DatabasePermissionID>Einkauf</DatabasePermissionID>
</Object>
<ObjectDefinition>
<DatabasePermission xmlns:xsd="http://www.w3.org/2001/XMLSchema";
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";>
<ID>Einkauf</ID>
<Name>Einkauf</Name>
<RoleID>Einkauf</RoleID>
<Process>true</Process>
<ReadDefinition>Allowed</ReadDefinition>
<Read>Allowed</Read>
<Administer>true</Administer>
</DatabasePermission>
</ObjectDefinition>
</Alter>

<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">
<Timeout>0</Timeout>
<SspropInitAppName>Microsoft SQL Server Management
Studio</SspropInitAppName>
<LocaleIdentifier>3079</LocaleIdentifier>
<ClientProcessID>2076</ClientProcessID>
</PropertyList>




"Akshai Mirchandani [MS]" <akshaim@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:eU7qMyAsGHA.1272@xxxxxxxxxxxxxxxxxxxxxxx
It sounds like the difference is unrelated to query performance and disk
i/o etc.

My guess is that it is something environmental -- perhaps you have lots of
active sessions on the 64-bit server which causes transaction commit to be
an expensive operation because it has to refresh active sessions. Or the
cached data on the 64-bit server is much larger and it the commit
operation is releasing memory pages... Or perhaps you have lots of other
databases on the 64-bit server that are not present on the 32-bit server.

Have you tried to restart the AS process on the 64-bit box? Also, I would
suggest taking a look at the event log to see if there are unusual
errors/warnings reported there...

Another option to try would be to rename your current data folder and
start from a new data folder, restore the backup file and work from
there...

Thanks,
Akshai
--
Try out the MSDN Forums for Analysis Services at:
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=83&SiteID=1

This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for
newsgroup
purposes only.

"Radu Colceriu" <radu_colceriu@xxxxxxxxxxx> wrote in message
news:OCWuSv7rGHA.4580@xxxxxxxxxxxxxxxxxxxxxxx
Hi,

The query performance is quite good. My issues are related with XMLA
command execution (metadata operations) like create/modify aggregation
design, roles a.s.o.

The server is dual dual-core with HT .(8 virt exec units). When an XMLA
is executed one of the execution units goes 100% for some seconds
(10-20).
For example I've just redesigned the aggregations for a MOLAP partition
using the wizard. At the last step (pressing Finish) the window freeze
for (10-20) sec and the msmdsrv.exe process going 100% on one execution
unit. I've used the management studio on the server via remote desktop.
I've taked the backup of the OLAP database, restore-it to a PC (3.2Ghz
P4, x86) and make the same operation wich there takes 1-2 sec.
All XMLA commands are somehow very slow executed on the server. The SLQ
is SP1 build 2153. The Win2003 OS is up to date with SP1 and all online
updates available.

Thanks,
Radu

"Akshai Mirchandani [MS]" <akshaim@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:OlxE6e1rGHA.516@xxxxxxxxxxxxxxxxxxxxxxx
It is not accurate that a single partition query cannot be parallelized.
It is the formula engine that is serialized in evaluating calculations.
Some metadata operations will also be serialized (like
create/alter/delete).

However, your description below is a little confusing to me -- do you
find that it is only the metadata operations (create/alter/process) that
are slow on the x64 box. Are queries fast? That may indicate network
issues if just processing is slow...

Are the number of databases on the x64 box equivalent to the 32-bit box?
And the environments? Perhaps Profiler would help narrow things down a
little more.

One thing you could try is to install a new x86 instance on the x64 box
and see how slow/fast it works -- if it is also slow, then that would
indicate an environment/hardware issue.

Also, keep in mind that 64-bit is not expected to necessarily give you
better performance -- but rather better scalability. In fact, an x64
application has to deal with double pointer sizes and this can cause
some perf degradation in some cases. In other cases, the larger physical
memory available makes better caching possible which helps performance
of queries.

Thanks,
Akshai
--
Try out the MSDN Forums for Analysis Services at:
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=83&SiteID=1

This posting is provided "AS IS" with no warranties, and confers no
rights
Please do not send email directly to this alias. This alias is for
newsgroup
purposes only.

"Radu Colceriu" <radu_colceriu@xxxxxxxxxxx> wrote in message
news:eLuV0b$qGHA.4336@xxxxxxxxxxxxxxxxxxxxxxx
It's sad to hear that the MDX queries on a single partition cannot be
paralelised :(

The disk sys on the xeon srv is a FC SAN where I have an array of 4 FC
HDD in RAID 10... quite fast.

What is bothering me are the XMLA commands. For example, saving
aggregations (XMLA) on the PC is almost twice as fast as on the XEON
server ... Xeon 2.8GHz, PC 3.2 GHz... so in this case GHz >
Mutiprocesor :(





"Jéjé" <willgart_A_@xxxxxxxxxxxxxx> wrote in message
news:eSz1rT$qGHA.4508@xxxxxxxxxxxxxxxxxxxxxxx
a standard MDX query against a non partitionned cube use only 1 thread
so 1 CPU.
if you have partitions or a query which ask for measures from
different measure group you should see more then 1 CPU to works at the
same time.

its true, sometimes a single PC is better then a Xeon server, but not
when you start to have multiple users or processes.

sorry to say this, but I also found SATA drives far better then SCSI
drives!!!
SCSI drives are good only when the controller has enough cache.
so take a look at your disks and test them.

"Radu Colceriu" <radu_colceriu@xxxxxxxxxxx> wrote in message
news:%23IxPW18qGHA.1852@xxxxxxxxxxxxxxxxxxxxxxx
HI..

I have an setup like
- 2xXeon 2.8 dual-core 8GRAM, Win2003 x64, SQL 2005 EE x64
- PC P4 3.2G 2 G RAM. Win 2003 x86 SQL 2005 EE x86

I don't know why but on the PC the AS is working *much* faster than
on the Xeon system.

Especially designig agregation/creating roles/ creating partitions
a.s.o. So it seems that the same XMLA is executed almost twice as
fast on the PC than on the dual xeon.

On the dual xeon maschine each time an XMLA is executed, one of the 8
(2xdualcore + hyperthreading) "cores" is 100% but all other are doing
nothing.

On the same maschines SQL is running much faster on the Xeon maschine
than on the PC.


Any ideeas?

Thanks,
Radu













.

Relevant Pages

  • Re: AS2005 x64 vs x86 performance
    ... cached data on the 64-bit server is much larger and it the commit operation ... My issues are related with XMLA ... executed one of the execution units goes 100% for some seconds. ... The disk sys on the xeon srv is a FC SAN where I have an array of 4 FC ...
    (microsoft.public.sqlserver.olap)
  • Re: AS2005 x64 vs x86 performance
    ... When you run Profiler against the server do you see any other activity? ... Each execution made one execution unit 100%. ... As soon as the XMLA was executed the queries get answered. ... The disk sys on the xeon srv is a FC SAN where I have an array of 4 FC ...
    (microsoft.public.sqlserver.olap)
  • Re: AS2005 x64 vs x86 performance
    ... My issues are related with XMLA command ... executed one of the execution units goes 100% for some seconds. ... I've used the management studio on the server via remote desktop. ... The disk sys on the xeon srv is a FC SAN where I have an array of 4 FC ...
    (microsoft.public.sqlserver.olap)
  • Re: AS2005 x64 vs x86 performance
    ... The server is not a productive one at this moment. ... My issues are related with XMLA ... command execution like create/modify aggregation ... The disk sys on the xeon srv is a FC SAN where I have an array of 4 FC ...
    (microsoft.public.sqlserver.olap)
  • Re: Connecting to Analysis Services 2000 using XMLA SDK 1.1
    ... directly to the AS2000 server using MDX Sample? ... this is not what a real XMLA application should be using. ... We will contact MSTR and talk to our resident MSTR expert with regards to ... MicroStrategy about the Connectivity till,we get the URL working since ...
    (microsoft.public.data.xmlanalysis)
Loading