I've passed 70-229 exam

Discussion in 'MCSD' started by Sergey Krutous, Nov 11, 2003.

  1. The most difficult was Tuning and Optimizing Data Access questions (I've
    received the least sectional rating there). It's no wonder in it because all
    articles in BOL on this subject are recomendational. Any optimization is
    actually an art.
    For example, if you want to ensure that indexes you have created are
    used by query optimizer you can use two ways:
    - ask Index Tuning Wizard to suggest creating of other indexes
    - use SET SHOWPLAN option in Query Analizer
    In exam I had to choose only one answer from theese options. I'm still
    don't know the right one.
    Or: a table had been heavily accessed with INSERT, UPDATE and DELETE
    queries. That means that:
    - their B-tree became unbalanced
    - existing indexes became defragmented
    - statistics on columns became out of date
    To tune better performance you can choose all of the following
    statements: DBCC DBREINDEX, DBCC INDEXDEFRAG and UPDATE STATISTICS, but exam
    question has only one right question.
     
    Sergey Krutous, Nov 11, 2003
    #1
    1. Advertisements

  2. Sergey Krutous

    Charlie Guest

    Hi,
    They want you to use the Tuning Wizard because it can handle analyzing
    multiple parameters at once.
    DBCC DBREINDEX would be your best answer because it would have the most
    direct and noticeable
    effect on performance.

    Charlie
     
    Charlie, Nov 11, 2003
    #2
    1. Advertisements

  3. It is generally recommended that you use Index Tuning Wizard because with
    SET SHOWPLAN option you will get index usage information for particular
    query (or queries). It would be rather difficult to analyse this
    information by hand to see wether creating index will do any good. Feeding
    Index Tuning Wizard with workload file would allow for more accurate and
    percise analysis of usefulness of query.
    Additional information (like Execution Plan statistics or DBCC SHOWCONTIG)
    can help to chose exactly one answer:
    If Estimated figures differ to much (e.g. Estimated Row Count vs. Row
    Count) you should UPDATE STATISTICS to prevent Query Optimizer from issuing
    incorrect estimations.
    If DBCC SHOWCONTIG says there is some fragmentation and you think it causes
    poor query performance you may go with DBCC INDEXDEFRAG.
    If fragmentation is significant it may be faster to reindex DBCC DBREINDEX
    rather than simply defragment it.

    Hope this helps.
     
    Dmitry Malenko, Nov 11, 2003
    #3
  4. Sergey Krutous

    Paisleyskye Guest

    Congrats on the pass!
     
    Paisleyskye, Nov 19, 2003
    #4
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.