Application SP Response time differs on user login

  • 6pravin9@gmail.com

    SSC Enthusiast

    Points: 188

    I have multiple parameterized SP for our Application.

    When I Executes those SP individual it will returns output within 1 Sec. But when we are running Test Load with multiple user around 100 or 200 users same time , Same SP Response Time is Over 3-4 Sec.responce

    If i Execute same SP with same Parameter Individual response time is less then 1 Sec.

    responce1

  • andreas.kreuzberg

    SSCertifiable

    Points: 6055

    Hi,

    what are the main waits stats if you are testing with many users?

    And what is your Max Degree for Parallelism and Cost Threshold for Parallelism.

    Maybe your SQL Server won't work parallel?

    Kind regards,

    Andreas

  • Grant Fritchey

    SSC Guru

    Points: 396618

    Changes to the connection strings? ANSI settings? Differences in compiled execution plans? As above, wait statistics?

    These are just a few of the possibilities. Also, you running a batch versus your code, how is the code constructed and what is it doing to make the call?

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Jeff Moden

    SSC Guru

    Points: 996847

    6pravin9@gmail.com wrote:

    I have multiple parameterized SP for our Application.

    When I Executes those SP individual it will returns output within 1 Sec. But when we are running Test Load with multiple user around 100 or 200 users same time , Same SP Response Time is Over 3-4 Sec.responce

    If i Execute same SP with same Parameter Individual response time is less then 1 Sec.

    responce1

    Posting graphical code sucks because we can't copy any of the code to tell you what might be a problem.

    Also, what's with all the trailing underscores?  This isn't VB code.  Well, not unless you're using VB code to generate the stored procedure.

    I'll also tell you that I'd never let this code fly through a peer review because of the hardcoded years that you're going to have to change every year.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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