Post reply

Planning for a server

  • November 8, 2017 at 12:57 am

    #341205

    Hi, this is a very general question, and I need a baseline idea of where to go. Getting into too many technicalities will drag it out and I need a rough guideline asap, this is how our system is currently. Please ask questions, but if I go into detail it will take me two days to explain the whole setup.

    Production Instance 

    1. Production, 
    2. Daily Reporting, 3rd Parties etc, (copy of DB1)
    3. Monthly Reporting (Copy of DB1 at Month End)

    Test Instance -

    1. Test

    Now multiply this by 10 clients at same site, database sizes ranging from 700Gb to 3Tb each per single db

    My questions or requests for advice or ideas for exploring are

    1. Number of servers
    2. Would you have each client's data files for example on its own raid controller, It's not a huge company that can just give one controller per DB for example
    3. I thought about moving the 3rd DB onto it's own instance, but then it only gets used about 15 days in a month. DB1 and 2 are closely linked, example reads from 2 updates to 1
    4. Another question, not as important - consolidated reporting needs to be taken into account

  • November 8, 2017 at 4:06 am

    #1967126

    So, general advice, not specifics.

    Measure the performance of each of these systems. Get the wait statistics and break that down by database. Best way to do this is using Extended Events. It's also the best way to get the query metrics. The measurements on what is consuming resources and what is causing waits will best enable you to make these decisions. For example, you asked if you should break apart the I/O. Well, are you experiencing I/O waits. If so, drill down to determine where they come from and then the answer is probably yes. If not, then the answer is no, look elsewhere for a solution.

    Measuring your performance and using the data to make the decision is the best general advice I can give.

    "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

  • November 8, 2017 at 6:03 am

    #1967140

    Thanks for that. We have other systems on the same databases at other clients. Here it is just meg scale. Unfortunately this is future planning for a client being converted from Adabas to SQL, We have an idea of performance issues currently, with a database of 600 tables etc, so its really huge, jobs running for 12 hours, so performance is a must, I do think splitting their data drives would be a must. We already insist on 15k or or higher performance disks for drives. Probably 1000 to 1500 concurrent users.

    We did a spec before and tended to go for 4 servers, I am just inclined to go for splitting data drives which we have never done, also more instances, but this is a performance issue in writing to other instances?

  • November 8, 2017 at 6:25 am

    #1967145

    Andre 425568 - Wednesday, November 8, 2017 6:03 AM

    Thanks for that. We have other systems on the same databases at other clients. Here it is just meg scale. Unfortunately this is future planning for a client being converted from Adabas to SQL, We have an idea of performance issues currently, with a database of 600 tables etc, so its really huge, jobs running for 12 hours, so performance is a must, I do think splitting their data drives would be a must. We already insist on 15k or or higher performance disks for drives. Probably 1000 to 1500 concurrent users.

    We did a spec before and tended to go for 4 servers, I am just inclined to go for splitting data drives which we have never done, also more instances, but this is a performance issue in writing to other instances?

    I'm not sure I understand the last question. You'll be writing/reading data across servers? Yes, that can be an issue. Linked servers are notorious for poor performance. Data migration, replication, availability groups, that's one thing. Queries across servers is another. In that case you may be better off keeping the databases together. This assumes I understand the question, which I'm not sure I do.

    As to splitting storage, it's very common to do this. I still can't make suggestions for specifics based on what we've been talking about. It is safe to say, more data paths is better than fewer data paths in most circumstances.

    "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

  • November 8, 2017 at 1:09 pm

    #1967245

    Grant Fritchey - Wednesday, November 8, 2017 6:25 AM

    Andre 425568 - Wednesday, November 8, 2017 6:03 AM

    Thanks for that. We have other systems on the same databases at other clients. Here it is just meg scale. Unfortunately this is future planning for a client being converted from Adabas to SQL, We have an idea of performance issues currently, with a database of 600 tables etc, so its really huge, jobs running for 12 hours, so performance is a must, I do think splitting their data drives would be a must. We already insist on 15k or or higher performance disks for drives. Probably 1000 to 1500 concurrent users.

    We did a spec before and tended to go for 4 servers, I am just inclined to go for splitting data drives which we have never done, also more instances, but this is a performance issue in writing to other instances?

    I'm not sure I understand the last question. You'll be writing/reading data across servers? Yes, that can be an issue. Linked servers are notorious for poor performance. Data migration, replication, availability groups, that's one thing. Queries across servers is another. In that case you may be better off keeping the databases together. This assumes I understand the question, which I'm not sure I do.

    As to splitting storage, it's very common to do this. I still can't make suggestions for specifics based on what we've been talking about. It is safe to say, more data paths is better than fewer data paths in most circumstances.

    Most of the processing is on the single databases, but there is a level f integration between the databases as well, lets say 90% of the actual processing is  done on the local db but there will always be queries across  to other DB which may be on other instances or servers, even though this is maybe at a 5 to 10% level, there is future growth in this area as more third parties are capturing work directly, querying one database and writing to another for example. The options are almost limitless. A year back there were about 12 3rd Parties, now more an more are getting in line, from more diverse environments as well, so it will get tricky. Say oir Cash Office is based on one server, but these records need to be posted to the 10 main DBs every day.

    I do then think we would need to think of a single environment with multiple raid controllers per database, less used databases can be used by raids with slower disks for example, but in effect try to go for a single platform then

  • November 8, 2017 at 1:42 pm

    #1967251

    Andre 425568 - Wednesday, November 8, 2017 6:03 AM

    Thanks for that. We have other systems on the same databases at other clients. Here it is just meg scale. Unfortunately this is future planning for a client being converted from Adabas to SQL, We have an idea of performance issues currently, with a database of 600 tables etc, so its really huge, jobs running for 12 hours, so performance is a must, I do think splitting their data drives would be a must. We already insist on 15k or or higher performance disks for drives. Probably 1000 to 1500 concurrent users.

    We did a spec before and tended to go for 4 servers, I am just inclined to go for splitting data drives which we have never done, also more instances, but this is a performance issue in writing to other instances?

    If you have jobs running for 12 hours, I suspect the issue is mostly not hardware.  If "performance is a must", my first investment would be in finding and fixing the bad code because that's where the true performance will be.  Bad code will drag virtually any level of server down.

    For example, in May of 2015, we went from some 8 year old hardware with only 16 CPU Core and only 128GB of RAM and on a SAN with 15K drives to 32 CPUs, 256GB of RAM, and all databases fully loaded on SSDs.  Some of the batch jobs ran 2X faster but most had no change and I was surprised to actually see any improvement.  I've finally convinced them to work on the code and now we're reworking some of the code... and seeing a 70X to 1000X improvement in all cases.

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

  • November 8, 2017 at 3:29 pm

    #1967266

    Jeff Moden - Wednesday, November 8, 2017 1:42 PM

    Andre 425568 - Wednesday, November 8, 2017 6:03 AM

    Thanks for that. We have other systems on the same databases at other clients. Here it is just meg scale. Unfortunately this is future planning for a client being converted from Adabas to SQL, We have an idea of performance issues currently, with a database of 600 tables etc, so its really huge, jobs running for 12 hours, so performance is a must, I do think splitting their data drives would be a must. We already insist on 15k or or higher performance disks for drives. Probably 1000 to 1500 concurrent users.

    We did a spec before and tended to go for 4 servers, I am just inclined to go for splitting data drives which we have never done, also more instances, but this is a performance issue in writing to other instances?

    If you have jobs running for 12 hours, I suspect the issue is mostly not hardware.  If "performance is a must", my first investment would be in finding and fixing the bad code because that's where the true performance will be.  Bad code will drag virtually any level of server down.

    For example, in May of 2015, we went from some 8 year old hardware with only 16 CPU Core and only 128GB of RAM and on a SAN with 15K drives to 32 CPUs, 256GB of RAM, and all databases fully loaded on SSDs.  Some of the batch jobs ran 2X faster but most had no change and I was surprised to actually see any improvement.  I've finally convinced them to work on the code and now we're reworking some of the code... and seeing a 70X to 1000X improvement in all cases.

    Jeff is always right. In addition, it's not a bad idea to look at the databases that you do control to ensure they have good structure, enforced referential integrity, good choices on clustered indexes, etc.

    "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

  • November 8, 2017 at 7:04 pm

    #1967284

    Grant Fritchey - Wednesday, November 8, 2017 3:29 PM

    Jeff is always right.

    Please, someone please tell the love of my life that. 😉

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

  • November 9, 2017 at 1:54 am

    #1967312

    Jeff Moden - Wednesday, November 8, 2017 7:04 PM

    Grant Fritchey - Wednesday, November 8, 2017 3:29 PM

    Jeff is always right.

    Please, someone please tell the love of my life that. 😉

    Don't be silly Jeff, you know we can't do that, it'll never work 😛

    Far away is close at hand in the images of elsewhere.
    Anon.

  • November 9, 2017 at 3:44 am

    #1967329

    Thanks all, we are looking at performance issues with better indexes etc

Viewing 10 posts - 1 through 9 (of 9 total)

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