October 16, 2014 at 3:33 pm
Hi SQL Folks,
I have two development instances. First one with Intel Xeon E5540 (4 cores) 8MB cache, 12BG RAM, Win server 2008 R2, SQL server 2012. Second one with Intel Xeon E3-1245 V2, (4 cores, 8 logical) 8MB cache, 32GB RAM, Win server 2012, SQL server 2012.
I have run 4 jobs for MERGE-ing data between databases on the First instance, and 8 jobs for MERGE-ing data on the Second instance. On both environments CPU is rising to 70-80%. Jobs call one stored procedure (which calls set of other SPs) which works for two databases. Jobs run on every 10 seconds. Queries are dynamically created and executed with datetime in range.
It's a multi-tenant environment where tenants are databases and I need to run the solution for 20 tenants on a production. But so far it seems that it will be a tough deployment...
I'll be given a CPU E5-2667 (8 cores), 12MB cache, 96-128 GB RAM, Win server 2012, SQL server 2014 and a better storage (RAID 10, 15K RPM, later possibility for SSD disk).
What is the expected number of jobs I should be able to run so that the E5-2667 CPU reaches to about 70-80%?
What could you additionally suggest, propose, advise ... please you're welcome to share here... and better if you've already experienced something similar.
Igor Micev,My blog: www.igormicev.com
October 17, 2014 at 1:35 am
Additional info to add is that databases use Simple recovery model running on Failover cluster.
Igor Micev,My blog: www.igormicev.com
October 17, 2014 at 3:48 am
Hard to make suggestions based on simply suggesting the number of jobs that a system can support. I've seen systems support outrageously high numbers of jobs, but the jobs were relatively simple. It comes down to what your queries are doing and how they're doing it. How the tables, constraints, indexes, statistics, etc., are configured and maintained. How the optimizer is resolving your queries.
I will say, at 12gb and 32gb, those are fine numbers for a development machine, but for a production system, that's very small in this modern world. More memory almost always helps.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 17, 2014 at 4:10 am
Thanks for the reply!
In the beginning the CPU is planned to be less powerful until another machine is released, then it will be 24 cores with 128GB RAM.
Jobs do simple MERGE-ing. They execute queries always having datetime in a range. The range is small ~10 seconds as they run on 10 seconds and the data result sets are usually small.
We plan to introduce data purge of some of the tables and in general databases will be small, but their high number - 20 on the instance and the many jobs would be spending the resources.
I use Ola Hallengren's maintenance solution. Additionally maintenance will be done for Standard edition (unfortunately).
Maintenance is Reorganize with update stats on daily basis, and rebuild once per week.
Tables' design is very good, connected with FKs, and queries covered with indexes.
Performance tuning showed not many missing indexes.
Igor Micev,My blog: www.igormicev.com
October 17, 2014 at 8:44 am
With standard edition you are capped at 16 cores. It won't use any more than that.
October 17, 2014 at 9:16 am
mrdenny (10/17/2014)
With standard edition you are capped at 16 cores. It won't use any more than that.
Thanks, this is going to be considered.
It's the same for BI edition. The client insists on not paying licences for Enterprise edition...
I still hope some CPU with 16 cores (16-32 MB cache) will satisfy our jobs.
Any other remarks are welcome...
Thanks again!
Igor Micev,My blog: www.igormicev.com
October 17, 2014 at 9:19 am
Then dual socket 8 core machines are the best they need to buy, and there's no reason to go above 128 Gigs of RAM on Standard or BI edition. If those machines won't provide enough horse power, then they'll need to scale out to multiple machines.
Welcome to Enterprise problems and a SMB budget.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply