Monday, March 26, 2012

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
QueryJD
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

No comments:

Post a Comment