Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2001
    Location
    melbourne, fl
    Posts
    1

    Question Unanswered: Index Question: Get Rid of Bookmark LU?

    Hello,
    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 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 (rpt 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....

    jason.doyle@identitech.com

    Thanks
    Query
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11

    Re: Index Question: Get Rid of Bookmark LU?

    JD
    I would not worry quite as much about the bookmark lookups. These are signs of indexes that are getting used (in this case NDX_WFWORKLIST6). The problem will be with the Clustered Index Scans. Since a table is sorted by the clustered index, scanning the index is really just a full table scan. Both Workflow and SysData are getting full table scans according to the attachment.

    Can you supply the index columns on these two tables?

    I see the where clause on Workflow specifies BatchDate, SSN, Routing, and WF_INITID. The "not equal to" on WF_INITID can squelch index usage in some cases. How many rows do these restrictions return? If it is a significant portion of the table, you may be sentenced to getting full table scans for quite a while.

    That is about all I can think of looking at the query plan. If at all possible, can you post the full query text along with the indexes?

    ~Matt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •