SQL Server Performance

get rid of bookmark lookup?

Discussion in 'Performance Tuning for DBAs' started by flasponge, Jan 29, 2003.

  1. flasponge New Member

    i am a programmer by trade but have been pulled into a DB issue with SQL Server 2K. We have a database agnostic custom application which executes the below query per GUI refresh (quite often). It is a 3 table join with the following tables:

    FYI_WORKFLOW (10K rows)
    FYI_SYSDATA (19K rows)
    FYI_WFWORKLIST (100K rows)

    The customer has given us an execution plan as follows. We have added some indexes yet they are still seeing some 20-30 sec execution times on this query and it will only get worse as more items come into this OLTP system.
    I have attached the query execution plan (txt file, easiest way to view is via QueryAnalyzer)....Any help or comments would be greatly appreciated.
    unfortunately, the company I work for has no DBA's on staff....



  2. Chappy New Member

    can you supply the actual query? its difficult to see how the execution plan can be improved when its not accompanied by the query
  3. flasponge New Member

    Yes, I am sorry for not supplying that, you can find the actual query, the indexes on the table and the table definition at the following location:


    Plus in my original post I meant to say 20-30 SECONDS execution time and as you can imagine the user base is not pleased with that since the query is executed synchronously. Additionally, changing the query itself is not an option due to the constraints of changing C++ application that executes this query thus indexing is my only option. PLEASE HELP
  4. sqljunkie New Member

    First add/rearrange your indexes to get rid of the Clustered Index Scan!
    I'm not sure you want to get rid of the bookmark lookup. Have you looked at Perfmon yet? Perhaps you're out of memory or have a processor or disk bottleneck?
  5. bradmcgehee New Member

    While experimenting with indexes may help, your query has bigger problems. It includes "or", "in", and "is null", all of which often prevent existing indexes from being used. I don't have the time to evaluate every index you currently have, but you may want to review the webpages on this website that cover joins, indexes, clustered indexes, and non-clustered indexes for tips. But as I mentioned before, unless you can work on the query, adding indexes will help, but you still may very well have a performance problem.

    Brad M. McGehee
  6. Argyle New Member

    Run a profiler trace when the query is running and save the data. Then use the Index Tuning Wizard to see what it suggests.


Share This Page