performance help

the showPlan_text of my procedure gives a result of<br /><br /><br /><br /> | |--Bookmark Lookup(BOOKMARK<img src='/community/emoticons/emotion-6.gif' alt='' />[Bmk1003]), OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[DaidsesDev1_01].[Voyager].[STUDY_VERSION_PRODUCTS] AS [SEO]))<br /> | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />[SVE].[SVE_ID]))<br /> | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />[STU].[STU_NUMBER], [STU].[STU_ID]) WITH PREFETCH)<br /> | | |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[DaidsesDev1_01].[Voyager].[STUDIES].[STU_ID_PK] AS [STU]), WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />[STU].[STU_RECORD_STATUS_FLAG]='Active'))<br /> | | |--Filter(WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />(like([VOYAGER].[VOYAGER_AP_GET_PRODUCTIDS_FOR_STUDYVERSION_FUN](Convert([SVE].[SVE_ID]), 'COMPONENTPRODUCT'), [VOYAGER].[VOYAGER_AP_GET_LIKECLAUSESTRING_FOR_REPORT_FUN]([@p_In_COMPONENT_PRO_ID_LIST_Vch]), NULL) AND like([VOYAGER].[VOYAGER_AP_GET_PRODUCTIDS_FOR_STUDYVERSION_FUN](Convert([SVE].[SVE_ID]), 'COMBINATIONPRODUCT'), [VOYAGER].[VOYAGER_AP_GET_LIKECLAUSESTRING_FOR_REPORT_FUN]([@p_In_COMBINATION_PRO_ID_LIST_Vch]), NULL)) AND like([VOYAGER].[VOYAGER_AP_GET_PRODUCTIDS_FOR_STUDYVERSION_FUN](Convert([SVE].[SVE_ID]), 'CLASS'), [VOYAGER].[VOYAGER_AP_GET_LIKECLAUSESTRING_FOR_REPORT_FUN]([@p_In_TCL_ID_List_Vch]), NULL)))<br /> | | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[DaidsesDev1_01].[Voyager].[STUDY_VERSIONS].[SVE_ID_PK] AS [SVE]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[SVE].[SVE_ID]=[Voyager].[VOYAGER_AP_GET_LATESTSTUDYVERSIONID_GIVEN_STUDY_NUMBER_FUN](Convert([STU].[STU_NUMBER]))), WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />([STU].[STU_ID]=[SVE].[SVE_STU_ID] AND [SVE].[SVE_RECORD_STATUS_FLAG]='Active') AND [SVE].[SVE_DISPLAY_FOR_ALL_FLAG]=isnull([@p_In_DISPLAY_FLAG_Vch], Convert([SVE].[SVE_DISPLAY_FOR_ALL_FLAG]))) ORDERED FORWARD)<br /> | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[DaidsesDev1_01].[Voyager].[STUDY_VERSION_PRODUCTS].[SEO_SVE_ID] AS [SEO]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[SEO].[SEO_SVE_ID]=[SVE].[SVE_ID]) ORDERED FORWARD)<br /> |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[DaidsesDev1_01].[Voyager].[MILESTONES].[MIL_ID_PK] AS [MIL]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[MIL].[MIL_ID]=[STU].[STU_CURRENT_MILESTONE_MIL_ID]), WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />[MIL].[MIL_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)<br /><br /><br /><br />any one can suggest what to do with it to tune my proc, it is taking over a minute to run. I want to bring it down to &lt; 10 sec

Can you post your query? It will make analyzing the execution plan easier.

Roji. P. Thomas
http://toponewithties.blogspot.com

ALTER PROCEDURE [Voyager].[VOYAGER_AP_STUDIES_BYPRODUCTS_DS_PRO]
(
@p_In_APP_ID_Int INT
,@p_In_USER_ID_Int INT
,@p_In_COMPONENT_PRO_ID_LIST_Vch VARCHAR(4000) = NULL
,@p_In_COMBINATION_PRO_ID_LIST_Vch VARCHAR(4000) = NULL
,@p_In_TCL_ID_List_Vch VARCHAR(4000) = NULL
)
AS
BEGIN

DECLARE @p_In_DISPLAY_FLAG_Vch VARCHAR(10)

IF APPARCH.APPARCH_AP_GET_ROLEEXISTSGIVENUSERID_FUN(@p_In_USER_ID_Int) = 'No'
BEGIN
SET @p_In_DISPLAY_FLAG_Vch = 'Yes'
END

SET NOCOUNT ON

SELECT DISTINCT
DOCUMENTID = CASE
WHEN STU.STU_LEGACY_PROTOCOL_NUMBER_TEXT IS NULL
THEN CAST(STU.STU_NUMBER AS VARCHAR(15))
ELSE CAST(STU.STU_NUMBER AS VARCHAR(22)) + ' (' + STU.STU_LEGACY_PROTOCOL_NUMBER_TEXT + ')'
END
,STU.STU_ID
,SVE.SVE_ID
,STU.STU_NUMBER
,SVE.SVE_VERSION_NUMBER
--,VOYAGER.VOYAGER_AP_GET_STUDY_LOA_GIVEN_STU_ID_FUN(STU.STU_ID) AS LOA_LIST,
,VOYAGER.VOYAGER_AP_GET_STUDY_VERSION_FUN(STU.STU_ID,SVE.SVE_ID,SVE.SVE_VERSION_NUMBER) AS SVE_VERSION_DATE
,SVE.SVE_TITLE_TEXT
,STU.STU_CURRENT_STUDY_STATUS_TYPE
,STU.STU_CURRENT_STUDY_STATUS_DATE
,SVE.SVE_SHORT_TITLE_TEXT
,SVE.SVE_TARGET_ACCRUAL_NUMBER
,SVE.SVE_PHASE_TYPE
,MIL.MIL_MILESTONE_NAME AS CURRENT_MILESTONE
,STU.STU_CURRENT_MILESTONE_DATE AS CURRENT_MILESTONE_DATE
,VOYAGER.VOYAGER_AP_GET_CURRENT_ACCRUAL_GIVEN_STU_ID_FUN(STU.STU_ID,STU_ACCRUAL_DATA_COLLECTION_LEVEL_TYPE) AS CURRENT_ACCRUAL
,CONVERT(VARCHAR(10),VOYAGER.VOYAGER_AP_GET_MIN_DATE_GIVEN_STU_ID_FUN(STU.STU_ID),101) AS PARTICIPANT_ENROLLMENT_DATE
,VOYAGER.VOYAGER_AP_GET_ACCRUAL_DATE_FUN(stu.STU_NUMBER) AS ACCRUAL_AS_OF_DATE
,VOYAGER.VOYAGER_AP_GET_STUDY_TEAM_FUN(STU.STU_ID) AS STUDY_TEAM
,VOYAGER.VOYAGER_AP_GET_STUDY_PARTICIPATING_SITES_FUN(STU.STU_ID) AS REGISTERED_PARTICIPATING_SITES
,Voyager.VOYAGER_AP_GET_IND_NUMBERS_GIVEN_STU_ID_FUN(STU.STU_ID) AS IND_NUMBER
,Voyager.VOYAGER_AP_GET_ORGANIZATIONS_GIVEN_STU_ID_FUN(STU.STU_ID, STU.STU_NETWORK_FLAG) AS NETWORK
,Voyager.VOYAGER_AP_GET_STUDY_TEAM_CHAIRS_FUN(STU.STU_ID) AS [STUDY CHAIR]
,VOYAGER.VOYAGER_AP_GET_STUDY_TEAM_MEMBERS_BY_ROLE_FUN(STU.STU_ID, 'Medical Officer, Medical Monitor, Clinical Trials Representative') AS [MEDICAL OFFICER]
,Voyager.VOYAGER_AP_GET_PERSON_NAME_FUN(STU.STU_ID, 'Program Officer') AS [Program Officer]
,Voyager.VOYAGER_AP_IS_REGISTERED_SITE_FUN(STU.STU_ID) AS [REGISTEREDSITE]
,(CASE SEO.SEO_PRODUCT_CLASS_FLAG WHEN 'Product'
THEN VOYAGER.VOYAGER_AP_GET_PRODUCT_NAME_GIVEN_PRODUCT_ID_FUN(SEO.SEO_PRO_ID)
ELSE VOYAGER.VOYAGER_AP_GET_THERAPYCLASS_NAME_GIVEN_THERAPYCLASS_ID_FUN(SEO.SEO_TCL_ID) END ) AS PRODUCTNAME
,(CASE SEO.SEO_PRODUCT_CLASS_FLAG WHEN 'Product'
THEN VOYAGER.VOYAGER_AP_GET_PRODUCTMANUFACTURERS_FUN(SEO.SEO_ID) END) AS PRODUCTMANUFACTURERS
,(CASE SEO.SEO_PRODUCT_CLASS_FLAG WHEN 'Product'
THEN VOYAGER.VOYAGER_AP_GET_CONTRACTMANUFACTURERS_FUN(SEO.SEO_ID) END) AS CONTRACTMANUFACTURERS
,(CASE SEO.SEO_PRODUCT_CLASS_FLAG WHEN 'Product'
THEN VOYAGER.VOYAGER_AP_GET_PRODUCTSUPPLIERS_FUN(SEO.SEO_ID) END) AS PRODUCTSUPPLIERS
,SEO.SEO_STUDY_USE_TYPE
,Voyager.VOYAGER_AP_IS_REGISTERED_SITE_FUN(STU.STU_ID) AS [REGISTEREDSITE]
FROM STUDIES STU
LEFT OUTER JOIN MILESTONES MIL
ON MIL.MIL_ID = STU.STU_CURRENT_MILESTONE_MIL_ID
AND MIL.MIL_RECORD_STATUS_FLAG = 'Active'
INNER JOIN STUDY_VERSIONS SVE
ON STU.STU_ID = SVE.SVE_STU_ID
INNER JOIN STUDY_VERSION_PRODUCTS SEO
ON SEO.SEO_SVE_ID= SVE.SVE_ID
WHERE (VOYAGER.VOYAGER_AP_GET_PRODUCTIDS_FOR_STUDYVERSION_FUN(SVE.SVE_ID,'COMPONENTPRODUCT') LIKE VOYAGER.VOYAGER_AP_GET_LIKECLAUSESTRING_FOR_REPORT_FUN(@p_In_COMPONENT_PRO_ID_LIST_Vch))
AND (VOYAGER.VOYAGER_AP_GET_PRODUCTIDS_FOR_STUDYVERSION_FUN(SVE.SVE_ID,'COMBINATIONPRODUCT') LIKE VOYAGER.VOYAGER_AP_GET_LIKECLAUSESTRING_FOR_REPORT_FUN(@p_In_COMBINATION_PRO_ID_LIST_Vch))
AND (VOYAGER.VOYAGER_AP_GET_PRODUCTIDS_FOR_STUDYVERSION_FUN(SVE.SVE_ID,'CLASS') LIKE VOYAGER.VOYAGER_AP_GET_LIKECLAUSESTRING_FOR_REPORT_FUN(@p_In_TCL_ID_List_Vch))
AND SVE.SVE_DISPLAY_FOR_ALL_FLAG = ISNULL(@p_In_DISPLAY_FLAG_Vch, SVE_DISPLAY_FOR_ALL_FLAG)
AND SVE.SVE_ID = Voyager.VOYAGER_AP_GET_LATESTSTUDYVERSIONID_GIVEN_STUDY_NUMBER_FUN(STU.STU_NUMBER)
AND SVE.SVE_RECORD_STATUS_FLAG = 'Active'
AND STU.STU_RECORD_STATUS_FLAG = 'Active'
AND SEO.SEO_RECORD_STATUS_FLAG='Active'
--ORDER BY NETWORK,STU_CURRENT_STUDY_STATUS_TYPE,DOCUMENTID,PRODUCTNAME

SET NOCOUNT OFF
END

since this is SQL 2005, you should save the plan as XML (.sqlplan file extension),
then open the plan file with a text editor, paste the contents into a dedicated reply, ie, no other text

also, it is better to have the actual plan than the estimated plan, ie, run the query with plan

I work on sql 2005 management studio.
The database is sql 2000.
I was tring to run show_xml on and execute it, it fails,
I know show_xml is a new feature on sql 2005. can't I use it on sql 2000 database while connecting the sql 2000 database from sql server 2005 management studio?

How many rows this is returning <br /><br />|--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[DaidsesDev1_01].[Voyager].[STUDIES].[STU_ID_PK] AS [STU]), WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />[STU].[STU_RECORD_STATUS_FLAG]='Active'))<br /><br />Can we remove this bookmark lookup and add a composite / covered index<br />| |--Bookmark Lookup(BOOKMARK<img src='/community/emoticons/emotion-6.gif' alt='' />[Bmk1003]), OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[DaidsesDev1_01].[Voyager].[STUDY_VERSION_PRODUCTS] AS [SEO]))<br /><br />Are stats updated for the database