July 14, 2011 at 11:20 am
So I have 4 servers set up. A pair of relational DW servers, and a pair of SSAS cube servers.
The pair is a replicate of each other, all settings / table structures are identical. And they process the same source file independently. This gives me a very flexible high availability system that works well.
They have always performed similarly, until lately. Both DW servers finish the relational ETL about the same time, and begin cube processing. However, one of the cube recently takes about 2 hours more to process than before.
Historically both takes about 2.5 hours to process the cube. Now, one of the server is still taking 2.5 hours, yet the other one is taking close to 5 hours!!!!
All index are rebuilt, we tried rebooting the servers, they should perform very similarly. Yet pretty consistently lately, one of them are taking extra long. And it's all in the cube processing part (relational ETL still finish about the same time)
How can I troubleshoot from here?? I am running out of ideas.
thanks
July 14, 2011 at 12:30 pm
You've looked at the software, now time to investigate the hardware.
There are four bottlenecks: Network, CPU, Memory, and Disk.
Network is unlikely here.
CPU is possible, especially if you're in VMs.
Check your memory settings on each machine and in particular check the slow machine for other apps running simultaneously competing for memory.
Most likely these are on different sections of the SAN. You'll need to speak with your SAN person and discuss disk concurrency and cache sharing issues and see if they can monitor the difference between the two LUNs for you.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 14, 2011 at 12:38 pm
hmm... CPU is identical on both servers.. Memory is the same as well
Available memory during run time seems similar
We have 72 GB on BOTH DW servers, and 32GB on BOTH SSAS servers.. DW for both are the same... all occupied by SQL Server.
Cube are both about half occupied by SSAS, 40% free.
Disk, they are actually both direct attached storage. Same configuration. RAID 10 SAS drives. We defrag both. Both have similar free disk space.
I am also wondering if the slow down is more caused by DW server.. or SSAS server.. during processing. I am tempted to reinstall SSAS on the cube servers.. it won't be too time consuming (given i back up all the cubes, reinstall, then restore). However, the same can't really be said for the DW servers.
July 14, 2011 at 2:37 pm
do a 'sp_who2 active' while the cubes are processing to see which queries are executing and for how long. Compare between both servers to see if its getting hung up somewhere. You can also look at Activity Monitor or do a sql trace to see what is going on, and compare between servers.
July 17, 2011 at 12:05 pm
boxta (7/14/2011)
hmm... CPU is identical on both servers.. Memory is the same as wellAvailable memory during run time seems similar
We have 72 GB on BOTH DW servers, and 32GB on BOTH SSAS servers.. DW for both are the same... all occupied by SQL Server.
Cube are both about half occupied by SSAS, 40% free.
Disk, they are actually both direct attached storage. Same configuration. RAID 10 SAS drives. We defrag both. Both have similar free disk space.
Well, that goes from puzzle to mystery. Sorry I awol'd a while. Let me think on it a bit. In the meanwhile, a trace against the two DW servers for the SSAS interface pulling actual plans might help track it down.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 17, 2011 at 8:03 pm
Dumb question, perhaps... but has anyone checked in and made sure you don't have disk failures on either the DW, the SSAS server, or wherever you store your cubes? A straight doubling of the time inspired me to wonder if you lost half a mirror.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 20, 2011 at 12:33 pm
Hi all.. thanks for all the suggestions so far.. but it's still a mystery..
i have done a network test, by copying large files between DW1-CUBE1 and DW2-CUBE2, and both perform about the same.
I have done a cross processing test, by configuring the cube to process using the other DW server. So processing goes DW2-CUBE1 and DW1-CUBE2. And CUBE1 is STILL much slower. So I suspect it's the cube problem, not the relational DB problem
I did a perf mon / resource monitor during processing for both. And I can see that CUBE1 is not technically "hung" per se. But I can see the rows read / sec, etc are all just slower on CUBE1 across the board.
And event viewer doesn't seem to have any fatal errors....
So... still mystery..
i am really tempted to just back up all the cubes.. and reinstall SSAS on the cube server... given it's just SSAS on the server, I think reinstalling / rebuilding it shouldn't take too long.
.....
anyone has an idea? thanks
July 27, 2011 at 3:47 pm
We are just currently experiencing the same issues. 3 SSAS 2008 servers. 1 Production, 1 QA, 1 Dev. SSAS Database on Production now taking 2x as long to process as a week ago. Restore db to QA or Dev and processing takes 1/2 as long. Happened after installation of some patches. No resolution found yet. Have run performance monitoring on server, but nothing standing out yet.
Thanks,
Toby
July 27, 2011 at 4:53 pm
toby.cihla (7/27/2011)
We are just currently experiencing the same issues. 3 SSAS 2008 servers. 1 Production, 1 QA, 1 Dev. SSAS Database on Production now taking 2x as long to process as a week ago. Restore db to QA or Dev and processing takes 1/2 as long. Happened after installation of some patches. No resolution found yet. Have run performance monitoring on server, but nothing standing out yet.Thanks,
Toby
the mystery continues... i still haven't solved my issue.. i just kinda gave up... and decided to process the main partition first .. which makes the most important data available to my user earlier... and then continue to process the other partitions / measure groups in the cube .. this buy me a bit of time to figure this out... as i am not in a time pressure anymore.
but still........ good luck to all
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply