poor sql server performance

Question

poor sql server performance

mstephens1754

SSCommitted

Points: 1650
More actions
August 24, 2015 at 2:04 pm

#302272

Guys I have an interesting performance problem that I need help with. I am running SQL Server 2012 R2 on Windows 2012 R2 the system is running on ESXI. The server has 22 gigs of ram the system is also a 2x2 so I have 4 cores to work with. I am dedicating 10 to sql server and the rest is divided up for Analysis Services and the OS. Also I am running tempdb on separate drive. We run several cubes as well as databases on this system. We have nightly jobs that go out and build cubes. The business group that uses this server is saying that it is taking the cube jobs forever to run now. Basically a cube that used to build in 2 hours is running 10 plus hours after a reboot everything is fine. So I must ask where do I start to troubleshoot this problem.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Obvious question: What changed?? Note that with unknown storage and virtualization in play there are umpteen places you need to look for "changes".

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

mstephens1754 SSCommitted Points: 1650 More actions · Answer 2

I agree by it being virtual it creates a lot of places a problem could occur. I asked the users of the system if they had made any changes to the cubes or databases of course they said there had not been any major changes. I think I'm going to go into Vmware and pull a trend analysis on cpu and storage.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

If the reboot fixes the problem, I strongly suspect some pretty nasty parameter sniffing. My first step would be to clear cache prior to doing the cube rebuilds. If that "fixes" it, then it may be a case of parameter sniffing. It could also be a case of a few other things that I'd investigate but I'd try the clearing of the cache first.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mstephens1754 SSCommitted Points: 1650 More actions · Answer 4

Ok I will clear the SSAS cache before leaving tonight we have several cubes that kick off after hours.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 5

It may also be that the data has grown and there's simply not enough memory to do what you want.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)