locked
I am using the sqlserver as a backend but around 2000- 4000 users will perform the operations at a time how we can tune the process RRS feed

  • Question

  • I am using the sqlserver as a backend but around 2000- 4000 users will perform the operations at a time  These many users can perform the same operation on same tables if these many users are connecting the database it will go down the performance eventhough tuned the services. Is there any way to implement clustered environment in sqlserver
    Monday, March 14, 2011 6:29 PM

Answers

  • I think this question may be better suited to the Database Engine forum (http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine) rather than Database Design.

    Clustering in SQL Server is targeted at high-availability rather than to address performance issues.  The SQL Server Scalable Shared database technology allows you to leverage SAN technologies along with multiple instances in different servers to distribute a read-only workload (e.g. reporting) onto multiple servers.  See http://technet.microsoft.com/en-us/library/ms345392.aspx.

    A single adequately-sized server can easily handle 2000-4000 OLTP users with proper index and query tuning.  But it takes only a few rogue queries to cause performance issues that affect all users.  This is one reason why you might consider a separate instance for reporting or schedule intensive queries during off-peak hours.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Stephanie Lv Thursday, March 17, 2011 2:47 AM
    • Marked as answer by Alex Feng (SQL) Thursday, March 24, 2011 5:24 AM
    Tuesday, March 15, 2011 2:37 AM
    Answerer
  • In addition,  your main concern would be blocking/locking  ,in order to perevent much locking take a look at new Transaction isolation levels to be introduced in SQL Server 2005

    SNAPSHOTSNAPSHOT

    READ_COMMITTED_SNAPSHOT


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Stephanie Lv Thursday, March 17, 2011 2:47 AM
    • Marked as answer by Alex Feng (SQL) Thursday, March 24, 2011 5:24 AM
    Tuesday, March 15, 2011 7:37 AM

All replies

  • I think this question may be better suited to the Database Engine forum (http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine) rather than Database Design.

    Clustering in SQL Server is targeted at high-availability rather than to address performance issues.  The SQL Server Scalable Shared database technology allows you to leverage SAN technologies along with multiple instances in different servers to distribute a read-only workload (e.g. reporting) onto multiple servers.  See http://technet.microsoft.com/en-us/library/ms345392.aspx.

    A single adequately-sized server can easily handle 2000-4000 OLTP users with proper index and query tuning.  But it takes only a few rogue queries to cause performance issues that affect all users.  This is one reason why you might consider a separate instance for reporting or schedule intensive queries during off-peak hours.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Stephanie Lv Thursday, March 17, 2011 2:47 AM
    • Marked as answer by Alex Feng (SQL) Thursday, March 24, 2011 5:24 AM
    Tuesday, March 15, 2011 2:37 AM
    Answerer
  • In addition,  your main concern would be blocking/locking  ,in order to perevent much locking take a look at new Transaction isolation levels to be introduced in SQL Server 2005

    SNAPSHOTSNAPSHOT

    READ_COMMITTED_SNAPSHOT


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Stephanie Lv Thursday, March 17, 2011 2:47 AM
    • Marked as answer by Alex Feng (SQL) Thursday, March 24, 2011 5:24 AM
    Tuesday, March 15, 2011 7:37 AM