AS service hangs on cube build 'start'/ timeout on process cube

I apologize in advance for the duplication, I just found this forum and wanted to post my questions/concerns here. I had previously posted to the discussion group. If anyone has any advice, I would love to hear from you. We are struggling with performance issues, particularly with making changes to the cubes now that our warehouse has grown.

Thank you in advance,

~Shari

We have two apparent issues with Analysis Services (AS) as outlined below:

1. We have a rebuild of the nightly cube build and quite often the first step would hang (just run without processing).We would have to stop the job, restart Analysis Services and then start the job again, resulting in loss of processing time and an impact to our Production Reporting.As a workaround for this intermittent problem, we have adjusted the nightly job to include a stop and start of AS before the cube build begins.An outline of the current steps:

· net stop MSSQLServerOLAPService

· net start MSSQLServerOLAPService

· dtexec /DTS "\MSDB\SSAS_Full_Dim_Load_Prod" /SERVER serverName /MAXCONCURRENT " -1 " /CHECKPOINTING OFF/REPORTING V

· process cub files

· backup cube

We need to know why AS does not process cubes as it should from time to time.Do we have a configuration problem?Is our Production Server not sufficient enough for AS and the size of the cube?We have more than the minimum requirements but possibly we need more due to our expectation of the cubes and data.

Server Specifications à

Production

CPU = Quad 2.8 GHz Intel Xeon (Hyper-Threaded)

Memory = 20476 MB

OS – Windows 2003. SP1

SQL Server 2005

64 bit based on the x64 platform

2. We have been unable to deploy a copy of the Production cubes to a new server.We even tried deploying a smaller cube, to include a large dimension (ex: Account). We continually experience time outs with the larger dimensions.

a. Tried deploying from Visual Studio 2005

b. Attempted to Process the cube through SQL Server Management Studio

c. Attempted to Process the larger dimension only from SQL Server Management Studio

d. Attempted Parallel processing, Sequential – One Transaction, and Sequential – Separate Transaction.

e. Also tried changing the default timeout for the AS server.ForceCommitTimeout property was 30000, increased to 40000 and then again to 50000

f. Attempted to change the ExternalCommandTimeout property from 3600 to 7200. This was successful in the Disaster Recovery server which has nothing running (no users reporting).Although it did take over 5 hours to process.At that rate, it might take 2 days to rebuild the large cube that we have, with several partitions.

Disaster Recovery

CPU = Dual 3.00 GHz Intel Xeon MP (Hyper-Threaded)

Memory = 9728 MB

OS – Windows 2003 SP 1

SQL Server 2005

32bit

Errors -->

OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.

OLE DB error: OLE DB or ODBC error: Query timeout expired; HYT00.

Re: 1

I've seen a scenario where the service "pauses" while processing due to threading issues. When that happens, issuing an unrelated query on another session while processing is stalled can cause the processing job to resume. If you're seeing that behavior, there are some tuning parameters that might help. They're not settings I'd encourage randomly tweaking, though, so I'll hold off on making any recommendations until I hear back.

Re: 2

There are a lot of possible variables here, and the investigation might need to ge tpretty involved. I'd really recommend opening a support ticket to try to resolve that one.

|||

Brad,

Thanks for your post. We have not seen that an unrelated query will trigger processing, per se. Basically once it hangs, it hangs until it is forced to stop. And unfortunately our cube jobs run for about 4-5 hours at night and what might appear to be "processing" is really a hang and it is not realized until we come in the next morning and see that it is not finished yet. The drag is that we lost a process of that cube. As I mentioned, we have put a stop and start of the AS service in the job as a safety but I we feel like we shouldn't have to do this and are more interested in getting the parameters tuned so that AS runs without incident.

As for the second issue, yes my next step is a case with MS. I thought I would post here and see if I could hear from others who may have experienced similiar first.

Thanks again,

Shari

|||

When you say it hangs, I assume you're seeing no CPU consumption or I/O, correct? Could you tell me what the query pool and processing pool busy and idle threads numbers are (under MSAS 2005: Threads in perfmon)?

Also, you say the CPUs support hyperthreading. Is HT actually enabled, such that it looks like 8 CPUs? I ask because it affects the size of the thread pools.

- Brad