We are running SQL 2005 AS and have started experiencing a long runtime, but
only at night.
We can run from BIDS, or our batch process during the day in less than 2
hours. At night, we started seeing 6 to 36 hour run times.
When processing during the day, we see lots of IO. At night, it starts out
fine, gets almost done, then we see the perf counters lay flat. We have
plenty of disc (local and SAN), memory does not seem to be an issue, and we
get no errors during processing.

SQL 2005
AS 2005
W2003 x64 SP1

It seems like it is hanging on one dimension when this happens, about half
way through. I have one of the LAN admins trying to troubleshoot with
perfmon, but he's not finding anything.

Any ideas on how to find what our server seems to be waiting for?
This has been running for months, and we used to get 2 hour or less runtime
night or day.

--
Thanks,
Greg E

what kindof storage -- Molap Realtime, Molap Scheduled; etc?

Molap. After pulling new data into the base and processing all new add,
changes, and deletes, we do a full process on the cube.

Running from BIDS (interactive) or our 2 step batch file - base warehouse in
one, AS in the second, during the day results in the 2 hour or less time. The
batch processes are kicked off through a remote command from an AS400. Batch
process number 1 (all SQL activities) is consistant night an day. Number 2 is
dimension and cube builds. We have run several tests - from BIDS, manually
launching the batch files, as well as launching through remote command. Same
2 hour or less results during the day.

We have the cube data on local disc, and go to the SAN for the base
warehouse data. If we compare overall SAN activity during the day vs.
overnight, it is higher during the day than at any point during the night. So
we don't see this as a constraint.

Looking at the timestamps on all the files used to build the cube, we have 1
dimension that we see a time gap of several hours or more on. We are going to
take this dimension out tonite and see if it finishes quicker (or rather
finishes like a run during the middle of the day).

One of our LAN admins has been looking at all kinds of perfmon data, but
does not see anything unusual. Plenty of disc (local and SAN), memory seems
fine, etc. All we see is activity flat lining for a period of time when the
cube is almost complete. This flat line equates to the extended run times we
are experiencing.
--
Greg E


"aaron.kempf (AT) gmail (DOT) com" wrote:

Has that dimension experienced many changes lately or an increase in size?


"Greg E" <gte (AT) newsgroups (DOT) nospam> wrote

Hello,

You may want to use profiler to trace the processing of AS to see if you
could find any clue

Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===


This posting is provided "AS IS" with no warranties, and confers no rights.

I presume you schedule this process.
have you try to change the schedule time? like a time in the day, when you
can monitor more closely what's appends (without doing an interactive
process)

maybe during the night you have a lock on some tables due to other schedules
on the server or on the SAN.
if at the same time you do the process the SAN replicate or backup some data
(or do any IO intensive task) this could cause issues.


"Greg E" <gte (AT) newsgroups (DOT) nospam> wrote

Other dimensions have, but this one has not. It is one of the larger
dimensions.
--
Greg E


"Jesse O." wrote:

We ran during the day to validate our cube can process in the time we expect.
We don't want to process during the day as the server becomes very
unresponsive for the users. They also are expecting 'as of yesterday close'
to be available in the morning.
We removed the dimension it seemed to get stuck on. Processing time was 5
hours. On my initial look at it this morning, it appears there was a similar
window of no activity. I'll get with the LAN admin and see what the perfmon
counters say.
They have looked at backups and SAN activity, but have seen no contentions.

Per Peter's post, we have watched it run during the day, and everything
appears fine. Is there any way to schedule profiler and have it log activity?
Since this only seems to be happening in the middle of the night, it would be
nice if we didn't have to be here to watch.

Thanks,
Greg E


"Jeje" wrote:

Hello Greg,

You could run profiler90 /? to see the swithch of the profiler. For example
you could use the following command to start profiler

profiler90 /A sha-petery-2003\sql2005 /O c:\trace.trc /E

/M switch could use to indicates stop time of profiler

You could schedule the command to run on a time during the night

Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===



This posting is provided "AS IS" with no warranties, and confers no rights.