Parallel mining model processing on multiple CPU host - is not possible ?

Question

 

Hi !

My situation is:

8 CPU 64bit 2008 R2 server. Sql Server 2008 R2 Enterprise 32 bit.

Relational SQL server has Analysis SQL server as linked server.

There is stored procedures in relational db, responsible for creation data mining models through transact sql, alike:

 

..
exec ('CREATE MINING MODEL .. blah blah..') at LinkedOlapServer
exec ('INSERT INTO MINING MODEL .. blah blah..') at LinkedOlapServer
..

Those procedures been run by multi threaded c# program, it says each thread run the same procedure, but creating different mining models. Each process cycle takes about 10 secunds, if run separatedly, without other process (training) actions.

But when i have seven simultaneous process (training) queries, it takes about 60 seconds each, obviously, the parallel trainings are consequential actually.

Whay it is sequential, and how it may be overcomed to be parallel, while working with OLAP server as linked server in the transactional relation engine ?

Thank in advance to all, i will be glad provide additional details, if needed

Answer 1

Hi Kosta,

Could you please make the question more clear? Have you executed the procedures directly on Data Mining engine? As far i know, in SQL Server Enterprise, all processing takes place in parallel. In SQL Server Standard, processing is serialized.

Regards,

Jerry

TechNet Subscriber Supportin forum
If you have any feedback on our support, please contact  [e-mail]

Answer 2

Thank you very much for the response !

Excuse me, I dont know what is "execute directly",

as i said before, on the transactional DB engine - there is linked OLAP server, provider "Microsoft OLE DB Provider for Analysis Services 10.0". The server edition is "Enterprise", not "Standard".

 

..
exec ('CREATE MINING MODEL .. blah blah..') at LinkedOlapServer
exec ('INSERT INTO MINING MODEL .. blah blah..') at LinkedOlapServer
..

 

And all the interaction with OLAP server is going through that linked server, so may be, i do not run DMX commands "directly" on the datamining engine, but through transact-sql engine.

This way should not affect performance, since in fact, if we take a look on the t-sql "Activity monitor", we will see several connections, corresponding to the number of simultaneous working threads, and linked servers is not supposed to have serialized (consequential) behavior in sending requests to the destination server, but just send commands.

I can add, there is no difference how much parallel threads are running and sending process (training) commands to DataMining engine, the CPU percentage of msdmsrv.exe never going above 13%. Just when there is more parallel requests for it, proportionally the longer it takes to process each mining model, and it looks like consequential (serialized?) processing, and not parallel.

I think the "13%" number is not accidental, since i have eight (8) core host server, 13% ~ 12.5% = 100% / 8.

So actually i can utilize only one core from he potentially eight, this is rather sad, because i should be eight times more productive, would i know how to configure the things