January 6, 2009 at 9:08 pm
I have a Bookmark Lookup in the query below. I assume that part of the query is satisfied with the clustered index.
When I run Explain Plan the Bookmark is on the PS_VCHR_ACCTG_LINE table.
SET STATISTICS PROFILE ON
SELECT A.FISCAL_YEAR, A.ACCOUNTING_PERIOD, A.BUSINESS_UNIT_GL, A.FUND_CODE, A.ACCOUNT, A.DEPTID, A.OPERATING_UNIT, A.PROJECT_ID, A.CHARTFIELD1, A.BUSINESS_UNIT, A.VOUCHER_ID, A.VOUCHER_LINE_NUM, A.DESCR, A.BUSINESS_UNIT_PO, A.PO_ID, A.LINE_NBR, A.CURRENCY_CD, C.VENDOR_ID, C.NAME1, B.INVOICE_ID, (CONVERT(CHAR(10),B.INVOICE_DT,121)), B.GROSS_AMT, D.INV_ITEM_ID, D.QTY_VCHR, D.UNIT_OF_MEASURE, D.UNIT_PRICE, A.MONETARY_AMOUNT, E.ITM_ID_VNDR, E.CATEGORY_ID, F.CATEGORY_CD, F.DESCR,C.SETID,F.SETID,F.CATEGORY_TYPE,F.CATEGORY_ID,(CONVERT(CHAR(10),F.EFFDT,121))
FROM PS_VCHR_ACCTG_LINE A, PS_VOUCHER B, PS_VENDOR C, ((PS_VOUCHER_LINE D LEFT OUTER JOIN PS_PO_LINE E ON D.BUSINESS_UNIT_PO = E.BUSINESS_UNIT AND E.PO_ID = D.PO_ID AND E.LINE_NBR = D.LINE_NBR ) LEFT OUTER JOIN PS_ITM_CAT_TBL F ON F.CATEGORY_ID = E.CATEGORY_ID AND F.EFFDT =
(SELECT MAX(F_ED.EFFDT) FROM PS_ITM_CAT_TBL F_ED
WHERE F.SETID = F_ED.SETID
AND F.CATEGORY_TYPE = F_ED.CATEGORY_TYPE
AND F.CATEGORY_CD = F_ED.CATEGORY_CD
AND F.CATEGORY_ID = F_ED.CATEGORY_ID
AND F_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10)) AND F.SETID = 'SET00' ), PS_SP_BU_FS_CLSVW B1
WHERE B.BUSINESS_UNIT = B1.BUSINESS_UNIT
AND B1.ROWSECCLASS = 'PHBU0316'
AND D.BUSINESS_UNIT = B1.BUSINESS_UNIT
AND ( A.DST_ACCT_TYPE = 'DST'
AND A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND A.VOUCHER_ID = B.VOUCHER_ID
AND B.VENDOR_SETID = C.SETID
AND C.VENDOR_ID = B.VENDOR_ID
AND A.FISCAL_YEAR = '2009'
AND A.ACCOUNTING_PERIOD BETWEEN '2' AND '3'
AND A.BUSINESS_UNIT = D.BUSINESS_UNIT
AND A.VOUCHER_ID = D.VOUCHER_ID
AND A.VOUCHER_LINE_NUM = D.VOUCHER_LINE_NUM
AND B.MATCH_ACTION = 'Y'
AND B.ENTRY_STATUS <> 'X'
AND A.ACCOUNTING_DT > '2007-07-12 00:00:00.000'
AND A.ACCOUNTING_DT < '2008-06-26 00:00:00.000' )
Here's the clustered index keys:
BUSINESS_UNIT, VOUCHER_ID, UNPOST_SEQ, APPL_JRNL_ID, POSTING_PROCESS, PYMNT_CNT, VOUCHER_LINE_NUM, DISTRIB_LINE_NUM, DST_ACCT_TYPE, CF_BAL_LINE_NUM, LEDGER, TAX_AUTHORITY_CD
In order to avoid the Bookmark Lookup would one need to cover on all keys not contained in the clustered index?
Does the outputlist columns in the explain plan determine what columns should be added as well?
Appreciate input
January 7, 2009 at 12:05 am
Bookmark lookups happen when SQL uses a nonclustered index to fetch rows and all of the columns that the query needs are not contained in the nonclustered index.
That clustering key is awfully large. Why so many columns? The usual recommendation for a clustered index is that it be as narrow as possible. It's the nonclusters that are often more useful when they're wide.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2009 at 8:25 pm
thnxs for your reply. Yes the clustered index definetly has many keys used. This was setup just when our Peoplesoft system went live 4 yrs ago. It may be hard to change this one as there are many jobs and Online processing using the tables and current indexes. Not much was documented. My only possible option may be an additional non-clustered index.
Looks like no fast way to get the right set of keys in testing (minus ones used in current non-clustered index) to remove the bookmark.
I assume that if I did create another non-clustered index I'd need to add all the columns in the Where clause, joins (minus keys used in current non-clustered index), also include coulumns in the Select?
January 7, 2009 at 11:21 pm
Peter D (1/7/2009)
It may be hard to change this one as there are many jobs and Online processing using the tables and current indexes. Not much was documented. My only possible option may be an additional non-clustered index.
As long as you only add columns to the end of the index, it shouldn't break anything
I assume that if I did create another non-clustered index I'd need to add all the columns in the Where clause, joins (minus keys used in current non-clustered index), also include coulumns in the Select?
If you want to cover a query, you'll need to have all the columns used in the where and joins and then any columns that are selected. It may not be possible to cover the query, since you're on SQL 2000 where there's a 16 column, 900 byte limitation on index keys.
Don't exclude columns that are in another index. SQL's unlikely (except on very large resultsets) to use two nonclustered indexes to get qualifying rows. It'll use the best one it can find, then do bookmark lookups to get the rest of the columns
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply