Post reply

Database Performance slowness issue

  • November 29, 2018 at 9:54 pm

    #382376

    Hi,

    We are facing database performance slowness issue due to which data upload is very very slow.

    Please let us know what all health checkup needs to be done to check what is causing database performance bottleneck.

    It is aver huge database of 1 TB size and during data upload it is taking more time.

    Can you please help us to understand below queries?

    1. What all checks to be performed and find what is causing DB performance issue
    2. Is the problem is of I/O ( disk speed) , or CPU or memory.
    3. Are any table indexes to be rebuild 
    4. Is there any defragmentation of data
    5. Is the code/package/procedure cauisng issue.

    Please consider this request as urgent and share monitory scripts/link which can help me to find the root cause of the slowness in database performance

    Thanks & Regards,
    Santosh

  • November 29, 2018 at 10:46 pm

    #2015049

    santosh.mane - Thursday, November 29, 2018 9:54 PM

    Hi,

    We are facing database performance slowness issue due to which data upload is very very slow.

    Please let us know what all health checkup needs to be done to check what is causing database performance bottleneck.

    It is aver huge database of 1 TB size and during data upload it is taking more time.

    Can you please help us to understand below queries?

    1. What all checks to be performed and find what is causing DB performance issue
    2. Is the problem is of I/O ( disk speed) , or CPU or memory.
    3. Are any table indexes to be rebuild 
    4. Is there any defragmentation of data
    5. Is the code/package/procedure cauisng issue.

    Please consider this request as urgent and share monitory scripts/link which can help me to find the root cause of the slowness in database performance

    Thanks & Regards,
    Santosh

    We are using SQL sever 2012 and on Always On High Availability.

  • November 29, 2018 at 11:05 pm

    #2015052

    santosh.mane - Thursday, November 29, 2018 10:46 PM

    santosh.mane - Thursday, November 29, 2018 9:54 PM

    Hi,

    We are facing database performance slowness issue due to which data upload is very very slow.

    Please let us know what all health checkup needs to be done to check what is causing database performance bottleneck.

    It is aver huge database of 1 TB size and during data upload it is taking more time.

    Can you please help us to understand below queries?

    1. What all checks to be performed and find what is causing DB performance issue
    2. Is the problem is of I/O ( disk speed) , or CPU or memory.
    3. Are any table indexes to be rebuild 
    4. Is there any defragmentation of data
    5. Is the code/package/procedure cauisng issue.

    Please consider this request as urgent and share monitory scripts/link which can help me to find the root cause of the slowness in database performance

    Thanks & Regards,
    Santosh

    We are using SQL sever 2012 and on Always On High Availability.

    Also we have set database parameter "Cost Threshold for Parallelism" value to 5 , "Locks"  value to 0, "Max Degree of Parallelism" value to 6 and Query wait to 300

  • November 30, 2018 at 4:58 am

    #2015086

    santosh.mane - Thursday, November 29, 2018 9:54 PM

    Hi,

    We are facing database performance slowness issue due to which data upload is very very slow.

    Please let us know what all health checkup needs to be done to check what is causing database performance bottleneck.

    It is aver huge database of 1 TB size and during data upload it is taking more time.

    Can you please help us to understand below queries?

    1. What all checks to be performed and find what is causing DB performance issue
    2. Is the problem is of I/O ( disk speed) , or CPU or memory.
    3. Are any table indexes to be rebuild 
    4. Is there any defragmentation of data
    5. Is the code/package/procedure cauisng issue.

    Please consider this request as urgent and share monitory scripts/link which can help me to find the root cause of the slowness in database performance

    Thanks & Regards,
    Santosh

    What do you mean by upload is very slow. Is that a SQL query or front end upload.
    Suggestion would be find out which code is taking time by whoisactive or server side trace and tune one by one. You can also setup perfmon.

    You have to setup metric to capture some data to correlate.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • November 30, 2018 at 7:05 am

    #2015106

    muthukkumaran Kaliyamoorthy - Friday, November 30, 2018 4:58 AM

    santosh.mane - Thursday, November 29, 2018 9:54 PM

    Hi,

    We are facing database performance slowness issue due to which data upload is very very slow.

    Please let us know what all health checkup needs to be done to check what is causing database performance bottleneck.

    It is aver huge database of 1 TB size and during data upload it is taking more time.

    Can you please help us to understand below queries?

    1. What all checks to be performed and find what is causing DB performance issue
    2. Is the problem is of I/O ( disk speed) , or CPU or memory.
    3. Are any table indexes to be rebuild 
    4. Is there any defragmentation of data
    5. Is the code/package/procedure cauisng issue.

    Please consider this request as urgent and share monitory scripts/link which can help me to find the root cause of the slowness in database performance

    Thanks & Regards,
    Santosh

    What do you mean by upload is very slow. Is that a SQL query or front end upload.
    Suggestion would be find out which code is taking time by whoisactive or server side trace and tune one by one. You can also setup perfmon.

    You have to setup metric to capture some data to correlate.

    Data upload is taking time 

    Problem Statement:

    Under Current process, we have Scheduler   

    This scheduler runs more than 6times in a day. Scheduler is use to call stored procedure, last two week thiss cheduler process which used to take 20-30 minutes to finish now is taking 55 minsto 1.30 hours to complete.  We have optimize some of the procedure by adding Index or including temp table.

    But the problem is transactionstuck random on particular query and it take lot of time to complete.

    E.g. if first day query1 takes 2mins to complete another day it takes 7 -20 mins to complete and another day again it takes 2 mins to complete.

    As the problem occurs random.There is no particular pattern to identify which process will stuck current process. Ideally we have around 600K data in

    process overall there is 11m data in transaction table and around 15-16 stored procedure through which this data have been processed.

  • November 30, 2018 at 10:48 am

    #2015156

    This is a huge topic.

    First, I'd suggest capturing query behavior using extended events. You can see all the calls made to the database and determine which specific calls are causing the most pain. You should also start monitoring the wait statistics to see which part of the system is suffering, disk, memory, cpu. Then, once you identify the poorly performing queries, use the execution plans to figure out why they're running slow.

    Get a copy of my book for all the details on all this.

    Oh, and change that cost threshold. 5 is the default and it's ridiculously low. Go to my blog, linked below, to find ways to determine a better value.

    "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

  • November 30, 2018 at 4:57 pm

    #2015184

    santosh.mane - Friday, November 30, 2018 7:05 AM

    muthukkumaran Kaliyamoorthy - Friday, November 30, 2018 4:58 AM

    santosh.mane - Thursday, November 29, 2018 9:54 PM

    Hi,

    We are facing database performance slowness issue due to which data upload is very very slow.

    Please let us know what all health checkup needs to be done to check what is causing database performance bottleneck.

    It is aver huge database of 1 TB size and during data upload it is taking more time.

    Can you please help us to understand below queries?

    1. What all checks to be performed and find what is causing DB performance issue
    2. Is the problem is of I/O ( disk speed) , or CPU or memory.
    3. Are any table indexes to be rebuild 
    4. Is there any defragmentation of data
    5. Is the code/package/procedure cauisng issue.

    Please consider this request as urgent and share monitory scripts/link which can help me to find the root cause of the slowness in database performance

    Thanks & Regards,
    Santosh

    What do you mean by upload is very slow. Is that a SQL query or front end upload.
    Suggestion would be find out which code is taking time by whoisactive or server side trace and tune one by one. You can also setup perfmon.

    You have to setup metric to capture some data to correlate.

    Data upload is taking time 

    Problem Statement:

    Under Current process, we have Scheduler   

    This scheduler runs more than 6times in a day. Scheduler is use to call stored procedure, last two week thiss cheduler process which used to take 20-30 minutes to finish now is taking 55 minsto 1.30 hours to complete.  We have optimize some of the procedure by adding Index or including temp table.

    But the problem is transactionstuck random on particular query and it take lot of time to complete.

    E.g. if first day query1 takes 2mins to complete another day it takes 7 -20 mins to complete and another day again it takes 2 mins to complete.

    As the problem occurs random.There is no particular pattern to identify which process will stuck current process. Ideally we have around 600K data in

    process overall there is 11m data in transaction table and around 15-16 stored procedure through which this data have been processed.

    You keep saying "data upload".  What do you mean by that?  Are you trying to import from a file, copy data from one server to another, or ?????

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

Viewing 7 posts - 1 through 6 (of 6 total)

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