Unanswered: Index Question: Get Rid of Bookmark LU?
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:
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....
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?