Post reply

Performance Tuning meeting

  • May 15, 2008 at 3:23 am

    #190605

    Good Morning,

    I need some advice on what to bring up at a meeting with some developers concerning the performance issues they have been experiencing, they said its to do with indexing issues (don't know exactly what their concerns are at the moment), but thats the information I have so far. Do I suggest monitoring the servers using perfmon or advice on issues such as clustered indexes, included columns etc.

  • May 15, 2008 at 3:31 am

    #815716

    Some ideas....

    * A review of fill factors is a good one for the agenda.

    * Profile for duration and review SQL and indexes on tables on the longest running procedures.

    * Check on statistics are they up to date.

    * You can use the index advisor wizard, personally I'd only use this as a guide and not treat the results as the way to go, but if indexing is poor, it can help give you a good place to start.

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • May 15, 2008 at 3:47 am

    #815718

    Thanks Carolyn, will let you know how thing go, if I need further help.

  • May 15, 2008 at 5:38 am

    #815765

    I'd suggest running Profiler and either capturing or generating execution plans for the longest running queries. You might also take a look at the dynamic management view dm_db_missing_index_details, *_Group_stats, *_Groups. You can also look at dm_db_index_usage_stats.

    Personal suggestion, certainly not one backed up by Premier Support at MS, stay the heck away from the Index Tuning Wizard. That thing will dig a hole real quick.

    "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

  • May 15, 2008 at 7:20 am

    #815851

    Thanks for that, also can you suggest a value to set for the fill factor, books online says anything between 0 to 100%, can I also set fill factor on a table (I only see the option to set it on the server).

  • May 15, 2008 at 7:30 am

    #815860

    Depends on the table. Heavy inserts tends to recommend a lower fill factor, while more static tables would probably have a higher fill factor.

    It's far more important though to make sure the indexes are adequate for the queries. I'll second Grant on checking with profiler to see where the slowness comes from. If you don't know what's slow, you will not be able to fix it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 15, 2008 at 9:05 am

    #815964

    Hi, just a quick one, if I set the appropriate events in profiler on a server (stored procedures and adhoc queries), can it capture all the previously run transactions on that server or do the users have to execute them after starting my trace to be able to trace all actions?

  • May 15, 2008 at 9:21 am

    #815978

    It only captures events as they occur. Nothing in the past.

    "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

  • May 15, 2008 at 3:23 pm

    #816173

    Might take a look at the performance dashboard and or the dm views on missing indexes and indexes used.

    Mark Johnson
    MCP, MCTS Sql Server 2005,MCTS Sql Server 2008, OCP

  • May 15, 2008 at 3:49 pm

    #816187

    fosco (5/15/2008)

    Good Morning,

    I need some advice on what to bring up at a meeting with some developers concerning the performance issues they have been experiencing, they said its to do with indexing issues (don't know exactly what their concerns are at the moment), but thats the information I have so far. Do I suggest monitoring the servers using perfmon or advice on issues such as clustered indexes, included columns etc.

    Lack of or wrong Indexes can certainly contribute to the problem... but I'd have them check the code for the following, as well, because no matter how well indexed something is, these will kill performance...

    Cursors (mostly because there's a WHILE loop involved).

    While Loops - need to be converted to SET Based

    Correlated Subqueries - They're not all bad, but most are and can be as bad or worse than any cursor or While loop you've ever seen. Can be thousands of times worse if the wrong type of join is included.

    Views of views, especially views with aggregations.

    Functions of functions

    Some functions all by themselves

    Unconstrained "Triangular" Joins[/b] - again, can be thousands of times worse than cursor or While loops.

    "Monster" joins

    Hidden RBAR on concatenations

    Improperly constructed Updates

    Here's a couple/three articles on some of these problems...

    http://www.sqlservercentral.com/articles/Performance+Tuning/62278/

    http://www.sqlservercentral.com/articles/Test+Data/61572/

    http://www.sqlservercentral.com/articles/T-SQL/61539/

    and just food for thought...

    http://www.sqlservercentral.com/articles/TSQL/62867/

    http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

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

  • May 16, 2008 at 8:48 am

    #816549

    I think this may be a case of reversed sequence, or, to use more IT-centric terminology, "premature optimization". Since you don't know yet (as of when the original post was written) what their concerns are, don't bother trying to figure them out before-hand. Go to the meeting, find out what the concerns are, handle them appropriately once you're clear on them. Ask for advice once you have specific questions, if you do.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • May 16, 2008 at 4:24 pm

    #816769

    Here's the questions that I generally ask:

    - Why do you thin that it is an Index problem?

    - Is everything slow, or are specific things slow?

    -- if specific: what are those things?

    - give me exampels of things that are too slow that I should be able to reproduce myself

    - how fast do thises things need to be?

    - How soon does this need to be addressed?

    - How important is fixing this? (ie., Important enough to buy new hardware, etc.?)

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • May 19, 2008 at 3:46 pm

    #817498

    Nicely Put RBarry.

    I would start a performance audit prior to meeting (if possible).

    Go into the meeting with as much knowledge as possible:

    http://www.sql-server-performance.com/articles/per/performance_audit_part3_p1.aspx

    Dont assume that the engineers are right (It may have nothing to do with indexes), but at the same time, dont assume they are idiots either....They may be spot on.

    gather the data that you can, prioritize the concerns and start wittling the issues down.

    If you need more help, feel free to email me directly, I've "Been there, done that"

    Cheers

    Greg Jackson

    PDX, Oregon

    Gregory A Jackson MBA, CSM

  • May 20, 2008 at 6:59 am

    #817825

    If your database is read only (low number of updates), use a fillfactor of 100%.

    If your database is write intensive (writes greatly exceed reads), use a fill factor somewhere between 50% and 70%.

    If your database is both read and write intensive, start with a fillfactor of 80% to 90.

  • May 20, 2008 at 7:25 am

    #817859

    If you substitute the word "table" for "database", I'd agree with that.

    --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 15 posts - 1 through 15 (of 17 total)

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