Recommended Hardware Specs

Question

Recommended Hardware Specs

Bregan-283649

Grasshopper

Points: 24
More actions
June 24, 2008 at 3:33 pm

#183972

Does Microsoft have a recommendation for hardware based on the database size, transactions/s, and concurrent user count, or user connections?
I'm basically looking for memory and processor requirements, but would like some concrete documentation if its available. Couldn't find much searching.

Viewing 15 posts - 1 through 14 (of 14 total)

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

I think the only thing concrete is what the minimum installation requirements/recommendations are and what the maximum is. The rest is up to you to figure out based on what you're doing and what your budget is. I always try to max out memory and balance out processing power with disk spindles, depending on budget and intended use.

--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)

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 2

pretty hard to do since your schema will play a big part in performance

unless there are support problems i would look at x64 hardware and software. HP Proliant DL 380 G5 servers start at $1000 and can scale up to dozens of TB of storage and 64GB of RAM

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

Heh... with all the problems I've seen on forums with 64 bit technology and SQL Server, so far... I'm going to wait for SQL Server 2011 before I recommend 64 bit technology. 😀

--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)

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 4

what problems? we've been using for a year and it's been OK

if it wasn't for 64 bit SQL we would have to go to 64bit Oracle because the kind of work we do made 32 bit sql cry

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

SQL Noob (7/25/2008)
what problems? we've been using for a year and it's been OK
if it wasn't for 64 bit SQL we would have to go to 64bit Oracle because the kind of work we do made 32 bit sql cry

I don't remember the precise problems mostly because I don't have 64 bit and kind of blew them of as "non-problems" because I don't have 64 bit. I only remember me saying to myself "I've never had that problem in 32 bit."

That's kind of a moot point if you've never had any problems with 64 bit and you've been using it for a year. It may be that a lot of folks just don't know how to use 64 bit and get balled up with trying to use 32 bit drivers in a 64 bit box. For example, I don't believe any of the current JET drivers will work on a 64 bit box.

What I am very interested in is what you good folks are doing that make "32 bit sql cry". I'm getting ready to build a new box and had mostly discounted 64 bit as "not ready for prime time" but, based on your good comments, may have to reconsider.

Thanks.

--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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

My former company has a number of 64 bit servers, running windows 2003 or windows 2008 and SQL 2005 and they are rock solid. I would not recommend IA64, there isn't enough software that runs on it and it can be finicky, but the X64 stuff's lovely.

The one thing that you must, must do on a x64 server is set the max memory. Because the addressing is flat and there's no need for AWE or the like, SQL can and sometimes will take all the memory on the server and starve the OS.

At this point, I'd be happy to recommend x64 to anyone looking at a new SQL box. The only issue of concern is drivers.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

Seems to be the max memory thing was what most of the problems I read about were... it's a good idea to set that even on a 32 bit machine, from what I recall.

I have a fairly large project comining up and I'd still really like to know what SQL Noob is doing that makes a 32 bit machine cry... they haven't bought a machine, yet, and it may be worth knowing. 🙂

--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)

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 8

we have some very large customers and when we run bills for them some select queries return anywhere from 1 million to 7 million rows and there is an order by at the end. Took hours to run on our old Proliant DL 760's and only a few minutes on the new DL 380 G5's with 32GB of RAM.

last year it got to the point where some of the billing processes would take 12 hours or more to run and that was unacceptable. our largest billing cycle takes around 10 hours to run on x64. on 32bit systems it would probably take days.

for maintenance it used to take around an hour to rebuild an index on a table with 250 million rows or more. with x64 and a new SAN it takes a few minutes. we also just moved an archive db with billing data to x64. used to take over 12 hours to rebuild an index there on tables with a billion rows or so. now it's an hour or so and this is on SATA drives.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

Thanks for the feedback...

Are these records "CDRs"? Does the billing process have anything to do with telephony, DSL, or VoIP?

And, when you say it returns 1 to 7 million rows, return to where?

--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)

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 10

I wouldn't recommend 64-bit with SQL 2000, but if you are 2005 or 2008, it should work fine.

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 11

we have 2 main db's for billing. one that holds the call records and the other the billing data. the CDR's and other customer data is loaded into the first one via a homegrown app. then there are processes that run to grab that data and transfer it into the billing db.

the billing app is another homegrown app which runs on a 64bit jvm and separate server. it grabs the data from the billing db, works on it and then updates and inserts a lot of rows to make a customer bill. it will select customer data, work on it and then spit data back out. for some of the larger customers these selects are anywhere from 1 million to 7 million or more rows with and order by at the end. i did some rough math last year and it comes out to around 1MB per row. the largest bill cycle generates around 55 million commands that need to be replicated and that's where the big problem is now. we have 64 bit pub, sub and distributor and it takes a day or so after the end of the cycle to replicate it. one month i had it down to 2 hours and still trying to figure it out where the problem is.

when we started running these customers through 32 bit sql 2005 last year it brought it a standstill

then a few times a month we archive data from these db's to an archive db

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 12

Interesting, indeed. I'm curious... what do you mean by "1mb per row"? Do you mean that you calculated that you have to "touch" 1MB to process each CDR?

--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)

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 13

when we had performance problems last year i looked at the schema and the data and approximated that each row of data returned approximately 1MB worth of data. was curious to see how much data we were dealing with because i thought it may be memory problems. the devs gave us the select for a customer with a 2 million rows. ran very fast without the order by and this made me guess it was memory.

this was in the billing db after the original cdr had already been processed and all

in my testing i ran the selects for a bunch of big customers and found that around the 1.5 million row mark an index seek became a clustered index scan or index scan. so earlier in the year we also changed the schema and made the column in the where clause of the select query a clustered index. made things faster as well and now pretty much all selects no matter how large are all clustered index seeks

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 14

Thanks again...

You keep saying that a "ROW" returned about 1MB of data... what is a "ROW" in this case?

--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)