Post reply

Can splitting a database to several databses improve performance.

  • August 27, 2010 at 11:23 pm

    #223128

    I am developing a website with a very heavy database , it has three modules and all three module's database is independent of the each other.

    Currently i am using a single database.

    My question is that if i split my database into three database, can this improve the performance/Availability of the database???

    Please guide..

    Thanx in advance.

  • August 28, 2010 at 12:03 am

    #1213388

    It is possible to make it run faster by splitting the db into three.

    It is also possible to make it run faster by optimizing it and leaving it running within a single db.

    It is possible that no performance gain will be seen by splitting it up like that.

    Lastly, it is also possible that by splitting the database out it could run slower.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • August 28, 2010 at 12:12 am

    #1213389

    Thanks Jason

    So what should i do??

    im confused.....!:unsure:

  • August 28, 2010 at 8:42 am

    #1213424

    I would start with finding where the pain points in the app and database are. Once those have been identified, I would optimize the database (indexes, keys, constraints) and stored procedures. The application code could probably be optimized too.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • August 28, 2010 at 10:41 am

    #1213443

    ahmed.net (8/28/2010)

    Thanks Jason

    So what should i do??

    im confused.....!:unsure:

    This isn't meant to be as nasty as it's going to sound... my recommendation would be do get some design help. Get someone that knows how to design databases for a "heavy load". The hard part will be finding a person who actually knows how to do that instead of just some wanna-be that that thinks they're qualified as a DB architect/designer.

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

  • August 28, 2010 at 10:44 am

    #1213445

    CirquedeSQLeil (8/28/2010)

    I would start with finding where the pain points in the app and database are. Once those have been identified, I would optimize the database (indexes, keys, constraints) and stored procedures. The application code could probably be optimized too.

    In most cases, you'd be absolutely spot on, Jason. However, in this case, it's a new development project and the OP needs help with design... not performance tuning on existing objects and data. 🙂 I do agree that some of the methods behind such tuning efforts do come into play for consideration when designing a database so definitely no wasted breath here.

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

  • August 28, 2010 at 12:27 pm

    #1213462

    Presuming here you still want all 3 databases on the same server.

    Performance wise it is probably only going to help if you put each database on a different drive.

    availability wise it could help as if you lose one database the other two could still support the other two modules.

    Better bet probably to split up those objects that are accessed together, i.e tables that are joined a lot, clustered and non-clustered indexes for the same table. that means getting into multiple filegroups across multiple drives. so an increase in administration overheads.

    also administration wise 3 databases could be easier as you can split up backups, integrity checks and optimisations, so shorter run times and a smaller housekeeping window required. However if all data needs to be recovered to an identical point in time should a restore be necessary, then thats a strong argument for keeping all data in a single database.

    If there is any link at all between the modules, i.e. static data, stored procs, that would mean keeping the data together to avoid duplication, save memory. You could think about multiple schema, especially if this is a hosted database where you might be charged by number of databases as well as space.

    ---------------------------------------------------------------------

  • August 28, 2010 at 1:17 pm

    #1213477

    Good advice above. I would tend to only consider splitting things if I expected scalability to require me to have 3 servers in the future to support the load.

    Otherwise, I would do as Jason suggested. Find the pain points, the bottlenecks, see if you can solve them. Or get help solving them. I know good consultants if you need them.

  • August 30, 2010 at 10:35 pm

    #1214035

    Thanks ...:-)

  • August 31, 2010 at 7:37 am

    #1214282

    George has some excellent thoughts on this. Further, I agree with Steve about having 3 databases only if you anticipate moving them to different servers in the future.

    If your thoughts of going to 3 databases is based on performance (presumably meaning that you would be having the databases on different drives) - you can accomplish the same effect in one database by adding additional file groups, and then moving selected objects to them.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • August 31, 2010 at 10:40 am

    #1214472

    I agree....

    Database design has so many variables to take into consideration. Contingency for tables and indexes, as well as how often tables are UPDATED versus read from. If this is a OLTP based application, then indexing and physical storage are very critical.

    In my mid-level DBA opinion, you should begin by looking at hardware. Seperating filegroups to seperate physical drives is where you would begin. Next look at the indexing requirements for the given tables (i.e. Clustered versus Non-clustered). After some of the basic hardware configurations are detemined then you can begin to model the database. At this point, security and logical groupings of tables become a concern. You can use SCHEMA's and proper security to isolate the modules of your application in one database. Your developers will need to work with fully qualified table names if that is the route you take. I could go on and on about how to do this, but ultimately your server's hardware and security requirements should be where you start for determining the need for three databases. Three databases running on the same physical hard drive will perform no better than a single database on that same physical hard drive.

    Hope this is helpful....

    😀

  • August 31, 2010 at 11:29 am

    #1214504

    coolwebs (8/31/2010)

    Three databases running on the same physical hard drive will perform no better than a single database on that same physical hard drive.

    Well, the three databases will be in three separate files (okay, 6 if you count the log files), so this by itself could improve performance over one database. Which is why I mentioned earlier that you could have one database with 3 file groups...

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • August 31, 2010 at 11:41 am

    #1214509

    I would think that it matters how often the tables would be joined when they were being used. If they always have to be joined anyway, splitting them out would make less sense to me. (If they're always having to be joined, they are in effect operating as one table too - and I would think that splitting the tables would actually hurt performance.)

    Also, how many columns would be in each table if the table was split out? Wouldn't that matter as well?

    Please correct me if I'm wrong about this, I'm certainly no expert.

    The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

  • August 31, 2010 at 12:10 pm

    #1214523

    mtillman-921105 (8/31/2010)

    I would think that it matters how often the tables would be joined when they were being used. If they always have to be joined anyway, splitting them out would make less sense to me. (If they're always having to be joined, they are in effect operating as one table too - and I would think that splitting the tables would actually hurt performance.)

    Also, how many columns would be in each table if the table was split out? Wouldn't that matter as well?

    Please correct me if I'm wrong about this, I'm certainly no expert.

    If the tables are often joined, they should be in the same database, however, as both tables need to be read to satisfy the query, IO throughput, and thus performance, can be improved by placing those tables on different drives.

    ---------------------------------------------------------------------

  • August 31, 2010 at 12:14 pm

    #1214525

    george sibbald (8/31/2010)

    mtillman-921105 (8/31/2010)

    I would think that it matters how often the tables would be joined when they were being used. If they always have to be joined anyway, splitting them out would make less sense to me. (If they're always having to be joined, they are in effect operating as one table too - and I would think that splitting the tables would actually hurt performance.)

    Also, how many columns would be in each table if the table was split out? Wouldn't that matter as well?

    Please correct me if I'm wrong about this, I'm certainly no expert.

    If the tables are often joined, they should be in the same database, however, as both tables need to be read to satisfy the query, IO throughput, and thus performance, can be improved by placing those tables on different drives.

    You're right - the question was about splitting a database rather than a table. Sorry about that.:blush:

    The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

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

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