Post reply

SQL Server Load

  • December 2, 2020 at 3:29 pm

    #3816070

    Hi,

    The application team is pushing more than 7 lakhs records per day to SQL Server. The SQL Server version is 2016 with ALWAYS ON. During one point of time the SQL Server hungs and I need to failover manually.

    1. How much load can SQL Server withstand
    2. How much connections can it take at a time

  • December 2, 2020 at 11:44 pm

    #3816290

    It depends:

    • Memory: How much does server have? How much is dedicated to SQL Server?
    • disk I/O and topology:  How fast are your disks?  Local or SAN? RAID array(s)?
    • network I/O: How fast is your network?
    • Volume of data/# of columns: 24 byte row swill be much faster than10 megabyte rows  (e.g. w/ varchar(max) or varbinary columns)
    • Size of transactions/batches:  Single row at a time?  thousands of rows at a time?
    • Indexes?
    • Foreign keys?
    • Check constraints?
    • Are inserts minimally logged?
    • Locking/blocking from other processes?
    • Implicit data conversions?
    • "Normal" persisted table or in-memory? (Probably persisted)

     

  • December 3, 2020 at 12:40 pm

    #3816459

    A failover would only fix an overloaded server by forcing people off of it. In short, that's no fix at all. You need to monitor the server and the resources used in it in order to understand the load it's under and where the pain points of that load are. Then, adjust the structures, code and server settings to deal with that load. It's possible you need to get more, bigger, better, hardware. However, without knowledge of what's happening on the system, it's impossible to know.

    How much load can SQL Server sustain? Probably more than any of us can easily generate.

    However, to do that, you have to set up the servers correctly. You have to design the databases well. You have to write code that's going to perform. You have to choose your indexes appropriately. You have to ensure that you're maintaining your statistics. And, finally, you have to have monitoring in place to understand where you're experiencing pain.

    "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

  • December 3, 2020 at 1:28 pm

    #3816487

    7 lakhs per day is not a number for a SQL Server.

    i regularly perform performance tests, when 10 mil records are inserted into some table. And if it takes an hour - it's bad.

    You need to look not so on the number of rows but on the data on those rows and how it's handled in the process.

    The right questions are asked above - try to answer them one by one. At some stage you'll find the point of failure.

    _____________
    Code for TallyGenerator

Viewing 4 posts - 1 through 3 (of 3 total)

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