Performance Tuning

  • Comments posted here are about the content posted at temp

  • Don't forget to download the chapter 28 from the book. It's 48 pages about performance tuning.

  • When I try to execute

    select object_name(object_id),

     index_id,

     index_type_desc,

     avg_fragmentation_in_pct

    from sys.dm_db_index_physical_stats(db_id(),null,null,null,'limited')

    on my server, I get the following error:

    Server: Msg 102, Level 15, State 1, Line 5

    Incorrect syntax near '('.

    Anybody else having this problem?  If you can, please reply to bscrivener@hcad.org.  Thanks.

  • That error is because you are probably connected to a database that has compatibility level set to 8.0 (2000) instead of 9.0 (2005)

    Cheers,


    * Noel

  • Actually this is more of a web site comment.

    Because of the layout style there's no way for me to view the ends of all the sentances.  Even with IE full screen the right side is cut off.   This happens a lot.

    eg "Data warehouse designs, on the othe" [... hidden text...] "Star or Snowflake design" or "As ragmentation increases, data will become spread out over mo" [... hidden text...] "your query needs to retrieve, the slower the query."  Whole sentances inbetween are missing.

    An unfortunate disservice to an article the 2/3rds of which I could read looks very interesting.

    This is a great site and I've come here for a couple years but the unconstrained content area is really frustrating.  If this happens to you please join me in patient occasional reminders to the busy guys who run SSC.  I'll chime in again about it in a few months, this is my third one. 

    To their credit there is no other site on earth I'm willing to read via view source.

    just my 2c

     

    Skål - jh

  • None of the bullets in the article include one of the most important aspects of performance tuning there is... one, that if not present, will bring the most robust hardware and all other tuning methods right to its knees. It's usually overlooked, it's usually given the least time to accomplish, and it seems it's been "mastered" by only an elite relative few...

    ...writing good solid high performance set based scalable code to begin with. 😉

    --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)
    Intro to Tally Tables and Functions

  • I just started going through the free chapter... glad to see there's some consideration for writing good code in there... maybe there's more in the rest of the book, but most of the tuning recommendations revolve around hardware, indexes, and a couple of things to avoid like unnecessary ORDER BY's... all of which are mute points in the presence of real crap code.

    The chapter also seems to have a high implication for GUI interfaced code... not much on batch code where the really big performance problems creep in. That fact is exemplified by the demo of sp_ExecuteSQL which really only benefits the necessary repetative RBAR nature of GUI code.

    --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)
    Intro to Tally Tables and Functions

  • try this:

    "SELECT * FROM sys.databases WHERE compatibility_level < 90"

    and u'll see the database that has compatibility level set to 8.0

    a good day,

    Vio

  • noeld (10/25/2006)


    That error is because you are probably connected to a database that has compatibility level set to 8.0 (2000) instead of 9.0 (2005)

    Cheers,

    <img src='images/emotions/smile.gif' height='20' width='20' border='0' title='Smile' align='absmiddle'>

    Nope the DMV will work even its compatibility level set to 80.

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

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

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