SQL Server Performance

slow running sysindex, syscolums query

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by spencepj, Aug 12, 2008.

  1. spencepj New Member

    hi, we support an app running on SQL 2005 std for a customer and certain screens can take up to 20 seconds to refresh. Done some profiling and the following is run approx 8 times:
    select x.name, x.indid, x.status, INDEX_COL('appowner.TRAINEE', x.indid, c.colid)
    from sysindexes x, syscolumns c where x.id = object_id ('appowner.TRAINEE')
    and indid between 1 and 254 and x.id = c.id and c.colid <= x.keycnt
    order by x.indid, c.colid ASC

    Now this returns 280 rows, about 50 rows for index's, 220+ rows for system statictics. In the execution plan, it estimates a subtree of 0.019. However in reality the query is taking around 1.5 seconds:-
    CPU - 1328
    Reads - 1757
    Writes - 0
    Duaration - 1433
    Stats are set to auto update and auto create. Index's and stats are updated nightly on all system and live DB's. I cant change the app - any advise on speeding this query up?
    TIA Paul
  2. Luis Martin Moderator

    Welcome to the forum!.
    1) Pick that (those) queries and paste into Management Studio.
    2) Write: Set statistics io on before the query.
    3) Write: Set statistics io off after the query.
    Check results, and look read ahead. If any table has a lot, may be you will need some indexes to get more performance.
    Also check execution plan.
    With that you can find if any indexes are necessary.
    To check, run DTA with that query.
    If you, or DTA, find a good index this is not: "change the application", just doing you job.
  3. satya Moderator

    Welcome to the forums.
    Why querying the system tables directly?
    Can you change teh code?

Share This Page