Performance Problem

  • Hi,

    In one of our servers all inserts in all databases takes too much time (almost application hangs). As I'm new in DBA field, please let me know what details you need to recognize the problem.

    Maybe helpful to add that SQLTRACE_BUFFER_FLUSH takes 99.9% waiting

    Thanks

  • HI,

    Can you give some more information related to your server including the hardware configuration, sql server version and service pack, no of databases along with the sizes, no of users etc.

    Rohit

  • Sure 🙂

    hardware configuration: Win Server 2003 and I don't know more cause I don't have enough permission to get access to it

    sql server version: SQL 2005 Enterprise Edition and SP1

    no of databases: 4 DBs with almost 13GB each and 8 DBs with less than 100MB each

    Everyday, around 100 DB update are performed on each DB scheme.

    Besides, each DB has around 8 users. No DB mirroring or shipping log is defined; only auto shrink is true and an automatic backup at midnight when nothing is done on servers is performed.

  • The first 3 things I'd do is...

    1. Update to sp2 and apply the latest cumulitive update (#7, I believe).

    2. Turn off autoshrink. If it keeps growing, it's because it needs to. Set it to a reasonable size and leave it alone. Everytime it grows, it frags your database and, possibly, the underlying file on the operating system. Set it, degrag the DB, and get the boys in OPS to defrag the hard disk. Don't ever use a percentage growth... set it to some reasonable fixed value.

    3. Same thing with TempDB... set it to something large on boot up. We have a tera-byte database and we set TempDB to 12 gig. It takes 73 fragments using the defaults of 10% growth and 1 meg starting size just to grow to 1 Gig. Avoid that and set TempDB to be a couple of gig on bootup.

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

  • Thanks a million Jeff 🙂

  • You bet... let us know how you make out on this problem.

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

  • I've just started to do as your comments step by step though I have to wait for another department to install SP2.

    I'll surely update you of the result.

    By the way, is there any formula to calculate the proper initial size for tempdb?

  • Some have tried to come up with a formula... I've not been very successful at it because it really isn't database size dependent. It's impacted more by what the largest query size is (internal number of rows spawned) and coding technique/style. If folks use a lot of cursors and single queries with a bazillion joins, then you'll need bigger than average. Same goes with folks that incorrectly over use temp tables and table variables (although their usage is key to "Divide'n'Conquer" performance in many cases). The easiest way for me, so far, is I'll usually start one out at 1 or 2 gigs depending on a "gut feeling" about the company... I'll keep track of the size for a month (gotta have month end usage in there somewhere). Then, again, on a gut feeling, I'll add 10 or 20% to that to accommodate a couple of months of potential growth and set the intial size to that. For growth, (another gut feeling), I set it to either 250 MB or 500 MB. I do the same with the LDF for TempDB.

    So far, I've been pretty lucky... not with the size but with convincing the customer of how worth it it is to dedicate the right amount of disk space to TempDB. Too many folks try to conserve disk space used by TempDB and it kills them when it continues to grow back to what it needs to be. Setting TempDB to be "right sized" on boot up is the second best thing you can do to an SQL Server... adding the right amount of memory is the first but not by much.

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

  • Hope following will help to resolve your issue.

    http://www.devprise.com/2007/12/10/sqltrace_buffer_flush-performance-metrics-on-sql-server-2005/

    http://www.themssforum.com/SQLServer/SQLTRACEBUFFERFLUSH/

    http://sqlblog.com/blogs/linchi_shea/archive/2007/01/16/performance-impact-of-enabling-page-checksum-and-default-trace.aspx

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • can you enquire about the hardware config. from the relevant department?

    Also, what are the access rights assigned to you?....and are you the DBA?

    It is very important before starting any sort of performance tuning, because I don't want the DBA in trouble( evn if it is you, you will be in trouble).....also few activities might hang the server because of low processing power and RAM, causing problem again, wherein you need to use the DAC....a dedicated connection for the admin.....available from 2005 onwards....

    So, before doing a R & D, check-out the hardware configuration........

    Chandrachurh Ghosh
    DBA – MS SQL Server
    Ericsson India Global Services Limited
    Quality is not an act, it is a habit.

  • Get and read these two documents: http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc and http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx.

    Setup and execute track_waitstats_2005, which can be found in the above. It will help you narrow down what is causing the delays.

    Also look up the use of sys.dm_io_virtual_file_stats to see if you have I/O issues (most servers I review for my perf-tuning clients do have I/O problems).

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Chandrachurh Ghosh (4/28/2008)


    can you enquire about the hardware config. from the relevant department?

    Also, what are the access rights assigned to you?....and are you the DBA?

    It is very important before starting any sort of performance tuning, because I don't want the DBA in trouble( evn if it is you, you will be in trouble).....also few activities might hang the server because of low processing power and RAM, causing problem again, wherein you need to use the DAC....a dedicated connection for the admin.....available from 2005 onwards....

    So, before doing a R & D, check-out the hardware configuration........

    Well.. my story is as follows 🙂 I'm just employed and am supposed to be DBA (I need gaining more knowledge as well as experience) since it's my first weeks of working in this co., I don't have access to main servers yet but since one of my colleagues has informally asked me resolve this problem, I'm trying to help him. Thanks for your comments I'll try to inquire hardware config. as well.

  • Thank you SSChasing Mays and SSC Veteran for the links 🙂 I'll go through them and let you know the result

  • While you're at it - make sure auto-close is turned off as well...

    I've had a few installs that somehow "like" to create new databases with autoclose turned ON....not a good thing (it will shut the database down every time it's not being actively used - very very bad for performance).

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • auto close is turned off :rolleyes:

Viewing 15 posts - 1 through 15 (of 64 total)

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