July 1, 2009 at 12:01 pm
I’m setting up a super database box: it’s a Dell R710 that has 48 gig of ram and two quad-core CPUs that have hyperthreading – when you open Task Manager and look at processes, it shows 16 CPUs! Unfortunately we’re only buying a single Enterprise CPU license, so I’ll have to throttle the installation so that it will only have access to 8.
We’re doing a consolidation project that will eliminate a dozen or so smaller servers, this new box is going to be hosting around 50 databases when all is said and done. These 50, as currently planned, will be running in a single instance to maximize available memory. Nothing else will be running on this box except anti-virus. No data warehousing, no full text search, no DTC. There is a possibility that we’ll have more than one instance, it depends on individual security requirements (i.e. maybe a stupid commercial app where the SA password must be XYZ), but I don’t expect there to be more than 2 or 3 instances.
The box will be running 2008 Enterprise x64, we’re upgrading from 2000. It’s possible that some databases will be maintained at 2000 or 2005 compatibility level.
The disk arrangement is as follows:
C: OS (local disk)
D: SQL Server program, system databases, and TempDB
E: Databases
F: Logs
G: Backup devices
This server is connected to a storage array for drive letters after C: which is going to be something like mirrored RAID 10 spread across 24 or 48 disks (it hasn’t been unboxed yet), the data will be going through four gigabit Ethernet links that use jumbo frames and round-robin routing between the links. Unfortunately it can’t be carved up so that each drive letter has a dedicated set of drive spindles. Most of the databases are not particularly high utilization and this server will not be hosting our ERP system.
I think, with this many databases, that I should definitely create multiple temp databases, but I really don’t know how heavily these canned apps hit tempdb. My thought is that with so many databases on one instance that the odds of tempdb contention go up sharply. My question is: what do you think would be a proper number, and how do you think they should be deployed? I’m thinking four, located on each of the SQL drive letters.
Also, I’m not sure that I’m gaining anything with having the logs and databases on separate drive letters. I definitely want backups on its own drive in case somehow it runs out of drive space it won’t halt the server, just backups won’t run.
Thoughts?
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
July 1, 2009 at 12:12 pm
I assume you mean multiple TempDB files, seeing as there can only be one TempDB database.
I would start with 4 files for TempDB, all on the same drive. Placing any part of TempDB along with the other databases could lead to IO contention between the two. If you can put the data files on to a dedicated drive and leave the log with the other system databases it would be better than having both with the system databases.
4 files reduces the chance of contention on the allocation pages. If you see that there's IO contention as well, it should be easy to move the TempDB files onto separate drives
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
July 1, 2009 at 12:13 pm
I was reading your post and wondered if you meant database files instead of tempdb's because there is only one tempdb for an instance of sql, you might have more than one file but something is tickling the back of my mind about tempdb and model needing same structure.. Might be an old requriement. With a single tempdb you could have contention on the tempdb system objects, this will likely NOT be mitigated by additional files on different volumes. You could put tempdb on a RAID 1 (striping) volume accross as many spindles as possible, that should help..
CEWII
July 1, 2009 at 12:15 pm
Also, I remember something about hyperthreading and sql being a bad thing.. You might want to use processor affinity to only use REAL cores or turn HT off entirely.
CEWII
July 1, 2009 at 2:00 pm
You really need to verify that you can modify SQL Server and still be within your license. I do not believe that you can 'throttle' SQL Server in the manner you are thinking of and still be compliant.
You may need to remove one of the cores to be compliant, if you are only going to be purchasing a single processor license.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 1, 2009 at 2:05 pm
Also note that if you are using Enterprise Edition in a non-virtual instance, you must license the number of CPUs in the server available to the operating system, whether you use them for SQL Server or not. SQL Server 2005/2008 per-processor licensing does not work like SQL Server 2000 per-processor licensing.
http://www.microsoft.com/sqlserver/2008/en/us/licensing-faq.aspx
"...A processor license gives you the right to install any number of copies of SQL Server 2008 in a single physical or virtual operating system environment, as long as you have purchased processor licenses for all of the physical processors on that computer. If you have made a processor inaccessible to all operating system copies on which the SQL Server software is set up to run, you do not need a software license for that processor."
-Eddie
Eddie Wuerch
MCM: SQL
July 1, 2009 at 2:30 pm
Using Processor Affinity they may be able to configure SQL Server to use only the cores in one physical processor which should keep them legal.
July 1, 2009 at 2:39 pm
Lynn Pettis (7/1/2009)
Using Processor Affinity they may be able to configure SQL Server to use only the cores in one physical processor which should keep them legal.
I don't think this is valid - because the license clearly states that if the processor is accessible to the OS, it needs to be licensed for SQL Server on that system. Like I said - they really need to follow up on this issue to make sure they are compliant/legal.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 1, 2009 at 2:53 pm
I completely agree guys, I was only bringing up processor affinity for performance reasons, not licensing but the licensing discussion is really good too..
CEWII
July 1, 2009 at 3:40 pm
Eddie Wuerch (7/1/2009)
Also note that if you are using Enterprise Edition in a non-virtual instance, you must license the number of CPUs in the server available to the operating system, whether you use them for SQL Server or not. SQL Server 2005/2008 per-processor licensing does not work like SQL Server 2000 per-processor licensing.http://www.microsoft.com/sqlserver/2008/en/us/licensing-faq.aspx
"...A processor license gives you the right to install any number of copies of SQL Server 2008 in a single physical or virtual operating system environment, as long as you have purchased processor licenses for all of the physical processors on that computer. If you have made a processor inaccessible to all operating system copies on which the SQL Server software is set up to run, you do not need a software license for that processor."
-Eddie
I think the second sentence covers what we're going to do, but I'll definitely give MS a call and verify. Otherwise, we might have to totally disable one of the CPUs, and that would just suck.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
July 1, 2009 at 3:46 pm
That second sentence seems pretty clear to me, I don't think they will be able to use that second processor legally without additional licensing..
CEWII
July 22, 2009 at 8:20 am
I thought I'd replied to this, apparently I had not. It's been crazy around here.
In short, Eddie et al were absolutely correct. I called Microsoft, and it is indeed one license per processor available to the operating system, the affinity mask doesn't change the licensing requirement. When I spec'd the box I showed one CPU, unfortunately the guy who bought it ordered it with two, so now they're arranging to buy another license.
Fortunately we're in a new fiscal year than when we bought it, so money is slightly more loose.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
August 16, 2009 at 6:45 am
Generally as a rule of thumb you would have 1 tempdb file, per processor so for a single quad core, you would have 4 files, if you were taking it to the nth degree, these would also be in different file groups and different drives so you would have dedicated spindles per file. this would also help if any of your databases used partitioning at any level.
The fact that you have 48GB ram on a 64bit box, means that a lot will be be cached and will run in memory, if the database are low in I/O, you would get away with no re-configuration and things will run pretty well without any sort of optimisation. the main reason, things are split out, is to keep random and sequential I/O on separate drives.
Depending on the workload, I would try keeping the tempdb on a separate drive and just have multiple tempdb files. it is the easiest solution and can be changed at a later date if I/O thresholds become a problem. so your existing layout could be used, just create additional tempdb files, remember to create them with the same size.
~Silverfox~
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply