February 6, 2013 at 3:56 am
Hi,
I've a customer that has a weird infrastructure implemented to store his customers databases.
He has 4 SQL Servers, with 1 Quad Core AMD CPU, +/-22GB RAM.
On each server he has an instance with approximately 1300 databases.
The total size of the databases is 140GB... Usually there are 600 to 800 connections to the SQL Server (each).
The worst part is the SQL Server machines are virtual and the databases files are on the virtual machine disk!!! :w00t:
Useless to say it has loads of PAGEIOLATCH_SH, ASYNC_IO_COMPLETION, CXPACKET, ASYNC_NETWORK_IO probably from having 2600 database files on the SAME virtual disk (the data files and log files and system files are all on the same disk...).
The CPU "pressure" from SUM(signal_wait_time_ms) / SUM (wait_time_ms) of the sys.dm_os_wait_stats is almost 25%.. loads of CPU pressure...
The databases are from an ERP application and the ad-hoc options isn't active on SQL and other parameters can be optimized, and the database has lots of unused indexes (that help the IO problems but not the main cause...).
My questions are:
1. Regardless the disk system (they can afford a "decent" SAN so I'll advise them do get on) is it best to have one SQL Server machine (physical) with 4 AMD CPU and 88GB RAM or 2 SQL Server (half CPU and memory each) on a cluster configuration? Nowadays they don't have server fail safe so a cluster would provide them that but the machine configuration would be a little "worst"..
2. I read on this forum, just can't find it now, that AMD CPU had issues with SQL Server (some AMD CPU parameter, BIOS configuration or SQL configuration, just don't know witch one .... ). (AMD Opteron(tm) Processor 6172)
Thanks,
Pedro
February 6, 2013 at 6:00 pm
An update,
Probably an instance with 88GB won't be "useful" since its Standard Edition (only supports 64GB).
But 2 instances with 44GB each should do the work...
Thks
Pedro
February 7, 2013 at 6:36 am
I hope it's 64Bit? What licences do "they" have :D?
Greetz
Query Shepherd
February 7, 2013 at 9:02 am
Standard Edition (limited to 64GB) and 64bits.
Pedro
February 7, 2013 at 9:18 am
I mean how many licences? Are these processor licences?
Greetz
Query Shepherd
February 7, 2013 at 12:23 pm
Not an issue.
If necessary to buy licenses they'll buy, but the hardware is "still".
Thanks,
Pedro
February 8, 2013 at 12:31 am
So if it's a prodution environment it's always better to use a cluster for high availibility. The momentary solution is not clustered, is it?
Greetz
Query Shepherd
February 8, 2013 at 3:08 am
No, they don't have server fail safe..
will a cluster active/passive with 2 CPU (8 cores) and 44GB but with the 4 instances, have performance gain over the actual structure (1CPU, 22GB RAM, 1 instance with 1000 databases)?
Thanks,
Pedro
February 8, 2013 at 3:17 am
No...not a performance gain, but an availability gain!
Why not a active\active cluster?
Greetz
Query Shepherd
February 8, 2013 at 3:31 am
humm.. it's a thought... thanks.
Pedro
February 8, 2013 at 4:24 am
If money ain't a problem...
Greetz
Query Shepherd
February 12, 2013 at 8:20 am
1600 databases in a single instance???? Who designed that?
My advice here before buying ANY hardware would be to consider database consolidation. I am willing to bet that an enormous amount of these databases could be replaced with sensible tables and security within a single database.
It would cost a great deal less than the SAN (for databases this size a SAN could be somewhat overkill) to get a competent consultant in and redesign the database design and it would be a lot easier to administer for the poor guy stuck with the job of looking after it all!
And the hardware will run it all efficiently I would guess.....
And the server won't be overloaded trying to process all the cross-database queries.....
And the company will have saved enough money to give the admin a bit of a payrise!
February 13, 2013 at 3:42 am
SQL Server can support even more, and only 100 are "active" at the same time.
It's an accounting office that takes care of the accounts of several customers, so each customer has a database.
Just one person working at the same time on a customer (database - this avoids deadlock since there's no concurrency).
There is no cross database quering... each database has it's data.
Thanks,
Pedro
February 13, 2013 at 7:39 am
Hi Pedro,
thanks! Now I understand a bit more. I have seen this happen on highly transactional databases where .mdf and .ldf files and tempdb all share the same space. Have you spread them over separate spindles?
Regards,
Kev
February 13, 2013 at 7:49 am
Its an Equallogic SAN with 24 disks.. unfortunately its RAID 50, but has 24 spindles...
but everything is on the same RAID, just one..
I already told them to change from 1 RADI50 with 24 disks to 4 RAID 10 with 6 disks each, same number of disks..
Still waiting for their answer but I already "know" what's going to be: "the SAN supports 2000 IO ops / s.."
To what I'l reply with a document from DELL with the recommendations for installing SQL on an EqualLogic SAN... (MDF, LDF and tempdb on separate RAID10..)
The problem is that they measured the IO ops / s outside the VM system, directly over the SAN, not inside the VM...On the VM I used a program that only gave 400 IO ops/sec...
But the DELL guys are "know it all" people.... the problem is on SQL Server, blá blá blá...
Just another struggle between DBA and admins... Fortunately SQL has the DMVs to say "where it hurts".. and IO is the spot 🙂
Thanks,
Pedro
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply