SQL Server Performance Forum – Threads Archive
get rid of bookmark lookup?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 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…. [email protected] Thanks
Yes, I am sorry for not supplying the actual query and DDL, you can find the actual query, the indexes on the table and the table definition at the following location: http://rdjasondoyle.identitech.com/Music/TableInfo.txt 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
About Bookmark Lookups:
I have read that bookmark lookups can be avoided if you can create covering indexes, ie, the nonclustered indexes are looking up at the clustered for the columns, if you had these columns in the nonclustered indexes the bookmark lookups can be avoided. Ranga
Ranga has a good recommendation. See this url for more information on covering indexes: http://www.sql-server-performance.com/covering_indexes.asp ——————
Brad M. McGehee