locked
scaling and peformance RRS feed

  • Question

  • Problem statement -Env- 2012 - I have a server and have 15 DB's on it, out of that 1 DB say 'Test', we are using and have 100 users accessing this 'Test' DB. Users access this DB data thru ssrs, excel and application. Only thru SSIS, data insertion is happening in this 'Test' DB. DB, SSIS & SSRS are residing on same server. We are anticipating that the number of users will go up by 1000. I have an option to add one more server if required. Also, I have concern when 1000 users logged in is there a DB or table lock? If so, how to handle?

    Please suggest how best I can do DB scaling? What are different factors I should consider? How can i control when 1000 users logged in as it is going to be performance and scalable issue.  Is it good idea to add one more server? My manager said, we need to come up with cubes/dimensions. Is this good idea? Or should i go data shreding or virtualizaion or add other server where I can chose mirroring or replication or always active/active. Please provide me a document or suggest what are all option should I consider?

    Wednesday, August 12, 2015 6:22 PM

Answers

  • Your question involves architecture, application design, and capacity planning.  One can make a career out of each if these individually.  I don't want to discourage your research but rather set expectations that you'll need to do a lot more than read a few whitepapers to adequately plan, design, develop, and implement such a system.  I want to mention a couple of very high-level points, though.

    To answer your question about the database lock, a shared database lock is acquired but is nothing to be concerned about from a performance and scalability perspective.  The load is not determined by the number of logged in users but the work they are performing.  A single user can bring a server to it's knees with a single query.  SQL Server features like the Resource Governor can mitigate this but, at the end of the day, hardware is ultimately the limiting performance factor.  Capturing performance metrics from your existing system may help plan what you need with no changes other than the number of users, assuming the workload profile is otherwise the same.

    OLAP and/or tabular modeling using SSAS may be part of the solution, and should be considered for what I'm guessing is a decision support system.  Much depends whether data must be real-time or can be older.  For a system of any considerable size using SQL Server, I recommend dedicating a server for the database engine, and running other processes like SSRS, SSAS, and perhaps SSIS too on other machines.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, August 13, 2015 1:47 AM
  • Also, I have concern when 1000 users logged in is there a DB or table lock? If so, how to handle?

    Hi,

    No that is really not necessary what you are talking i guess is if number of users increase there would be lock escalation and whole table would get locked. Lock escalation is basically internally decided by DB engine there are certain factors that sontribute to lock escalation but final decision is taken by DB engine. Every lock has memory associated with it so if large number of rows needs to be updated means large number of exclusive locks on rows which it tun means large number of memory for locks. Here db engine makes decision to escalate lock to table level because ofcourse memory is priceless and limited. So it not th users but number of connections on same table that would trigger what you are expecting

    Please suggest how best I can do DB scaling? What are different factors I should consider? How can i control when 1000 users logged in as it is going to be performance and scalable issue.  Is it good idea to add one more server? My manager said, we need to come up with cubes/dimensions. Is this good idea? Or should i go data shreding or virtualizaion or add other server where I can chose mirroring or replication or always active/active. Please provide me a document or suggest what are all option should I consider?

    If you are really worried about database and users the best thing is to move that database to dedicated server having capable hardware. Please note HA has nothing to do with Locing and blocks you need to make server exclusive there is no need to plan HA for this. HA is for database level or server level. What if your database is donw completely or Windows OS is corrupt in that case mirroring/logshipping and cluster would be handy (respectively)


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Thursday, August 20, 2015 6:46 AM

All replies

  •  

    Refer following

    http://searchsqlserver.techtarget.com/feature/Four-tips-on-boosting-SQL-Server-scalability

    http://highscalability.com/sql-server-2008-database-performance-and-scalability


    Vote As Helpful if it helps to solve your issue

    Thursday, August 13, 2015 12:31 AM
  • Your question involves architecture, application design, and capacity planning.  One can make a career out of each if these individually.  I don't want to discourage your research but rather set expectations that you'll need to do a lot more than read a few whitepapers to adequately plan, design, develop, and implement such a system.  I want to mention a couple of very high-level points, though.

    To answer your question about the database lock, a shared database lock is acquired but is nothing to be concerned about from a performance and scalability perspective.  The load is not determined by the number of logged in users but the work they are performing.  A single user can bring a server to it's knees with a single query.  SQL Server features like the Resource Governor can mitigate this but, at the end of the day, hardware is ultimately the limiting performance factor.  Capturing performance metrics from your existing system may help plan what you need with no changes other than the number of users, assuming the workload profile is otherwise the same.

    OLAP and/or tabular modeling using SSAS may be part of the solution, and should be considered for what I'm guessing is a decision support system.  Much depends whether data must be real-time or can be older.  For a system of any considerable size using SQL Server, I recommend dedicating a server for the database engine, and running other processes like SSRS, SSAS, and perhaps SSIS too on other machines.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, August 13, 2015 1:47 AM
  • Also, I have concern when 1000 users logged in is there a DB or table lock? If so, how to handle?

    Hi,

    No that is really not necessary what you are talking i guess is if number of users increase there would be lock escalation and whole table would get locked. Lock escalation is basically internally decided by DB engine there are certain factors that sontribute to lock escalation but final decision is taken by DB engine. Every lock has memory associated with it so if large number of rows needs to be updated means large number of exclusive locks on rows which it tun means large number of memory for locks. Here db engine makes decision to escalate lock to table level because ofcourse memory is priceless and limited. So it not th users but number of connections on same table that would trigger what you are expecting

    Please suggest how best I can do DB scaling? What are different factors I should consider? How can i control when 1000 users logged in as it is going to be performance and scalable issue.  Is it good idea to add one more server? My manager said, we need to come up with cubes/dimensions. Is this good idea? Or should i go data shreding or virtualizaion or add other server where I can chose mirroring or replication or always active/active. Please provide me a document or suggest what are all option should I consider?

    If you are really worried about database and users the best thing is to move that database to dedicated server having capable hardware. Please note HA has nothing to do with Locing and blocks you need to make server exclusive there is no need to plan HA for this. HA is for database level or server level. What if your database is donw completely or Windows OS is corrupt in that case mirroring/logshipping and cluster would be handy (respectively)


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Thursday, August 20, 2015 6:46 AM