InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 10:27:52 GMT20http://www.sqlservercentral.com/Forums/Topic1423535-2799-1.aspxIt's an estimated plan - the actual plan for [i]one[/i] of the queries would be much more informative. However, the following points spring to mind.Statistics appear to be out of date. Update statistics on the tables involved before running the stored procedure.The existing indexes on both source and target are [i]not [/i]helping. I'd recommend clustering each of the source tables using the columns used for joins to the merge target. The result is a fully-covering index - except it's the table.If the target clustered index isn't suitable to assist in the joins, then create a non-clustered index which is a better fit than TSV_IX_1. This index is very expensive to maintain and is used in only one of the four merge statements.When changing this amount of data, you may wish to rebuild rather than reorganize the indexes after the merge operation.Tue, 26 Feb 2013 07:26:31 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1423535-2799-1.aspxhi chris, find the updated execution plan.Tue, 26 Feb 2013 06:50:23 GMTsathiyan00http://www.sqlservercentral.com/Forums/Topic1423535-2799-1.aspxthanks, i think clustered index(primary key) in base_table is costing more.is it good idea to drop and create clustered index after merge statement.Mon, 25 Feb 2013 23:57:27 GMTsathiyan00http://www.sqlservercentral.com/Forums/Topic1423535-2799-1.aspxThanks. The plans for each of the MERGE statements are very similar. Here's what I suggest you do; pick one of the statements and work with it in a query window in a test environment. Ensure your statistics are up to date - some of the estimated row counts suggest they are not. Then run the query, capture the actual execution plan and post it here.Mon, 25 Feb 2013 08:01:46 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1423535-2799-1.aspxhifind the sameMon, 25 Feb 2013 07:04:51 GMTsathiyan00http://www.sqlservercentral.com/Forums/Topic1423535-2799-1.aspx[quote][b]sathiyan00 (2/25/2013)[/b][hr]hi , i attached actual execution plan of my proc..please find attachment(results.txt)...[/quote]Can you save it as a .sqlplan file and post, please?Mon, 25 Feb 2013 06:52:04 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1423535-2799-1.aspxhi , i attached actual execution plan of my proc..please find attachment(results.txt)...Mon, 25 Feb 2013 06:47:35 GMTsathiyan00http://www.sqlservercentral.com/Forums/Topic1423535-2799-1.aspxCan you please post the actual execution plans of all three merge statements.Mon, 25 Feb 2013 05:52:29 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1423535-2799-1.aspxHi all, Following is my stored procedure in that i merge three same structured table with base table.i created one non clustered index on all five column(col1,col2,col3,col4,col5) in each table(table_1,table_2,table_3,base_table). each table has around 4 to 5 crore records.any suggestions to improve the performance in sql server 2008 r2. PROCEDURE [dbo].[usp_merge_and_insert]ASBEGIN --Decalre all the variable in the begining DECLARE @error VARCHAR(1024) BEGIN TRY MERGE base_table AS tvsUSING (SELECT [col1] ,[col2] ,[col3] ,[col4] ,[col5] ,MAX([col6]) ,MAX([col7]) ,MAX([col8]) FROM table_1 GROUP BY [col1] ,[col2] ,[col3] ,[col4] ,[col5] ) AS tvs1ON tvs.col1= tvs1.col1 AND tvs.col2=tvs1.col2 AND tvs.col3=tvs1.col3 AND tvs.col4=tvs.col4 AND tvs.col5=tvs1.col5WHEN MATCHED THEN UPDATE SET tvs.co16=tvs1.col6 WHEN NOT MATCHED THEN INSERT([col1] ,[col2] ,[col3] ,[col4] ,[col5] ,[col6] ,[col7] ,[col8] ) VALUES( tsv1.[col1] ,tsv1.[col2] ,tsv1.[col3] ,tsv1.[col4] ,tsv1.[col5] ,tsv1.[col6] ,tsv1.[col7] ,tsv1.[col8]); --to merge table_2 with base table MERGE base_table AS tvsUSING (SELECT [col1] ,[col2] ,[col3] ,[col4] ,[col5] ,MAX([col6]) ,MAX([col7]) ,MAX([col8]) FROM table_2 GROUP BY [col1] ,[col2] ,[col3] ,[col4] ,[col5] ) AS tvs2ON tvs.col1= tvs2.col1 AND tvs.col2=tvs2.col2 AND tvs.col3=tvs2.col3 AND tvs.col4=tvs2.col4 AND tvs.col5=tvs2.col5WHEN MATCHED THEN UPDATE SET tvs.co16=tvs2.col6 WHEN NOT MATCHED THEN INSERT([col1] ,[col2] ,[col3] ,[col4] ,[col5] ,[col6] ,[col7] ,[col8] ) VALUES( tsv2.[col1] ,tsv2.[col2] ,tsv2.[col3] ,tsv2.[col4] ,tsv2.[col5] ,tsv2.[col6] ,tsv2.[col7] ,tsv2.[col8]); --to merge table_3 with base table MERGE base_table AS tvsUSING (SELECT [col1] ,[col2] ,[col3] ,[col4] ,[col5] ,MAX([col6]) ,MAX([col7]) ,MAX([col8]) FROM table_3 GROUP BY [col1] ,[col2] ,[col3] ,[col4] ,[col5] ) AS tvs3ON tvs.col1= tvs3.col1 AND tvs.col2=tvs3.col2 AND tvs.col3=tvs3.col3 AND tvs.col4=tvs3.col4 AND tvs.col5=tvs3.col5WHEN MATCHED THEN UPDATE SET tvs.co16=tvs3.col6 WHEN NOT MATCHED THEN INSERT([col1] ,[col2] ,[col3] ,[col4] ,[col5] ,[col6] ,[col7] ,[col8] ) VALUES( tsv3.[col1] ,tsv3.[col2] ,tsv3.[col3] ,tsv3.[col4] ,tsv3.[col5] ,tsv3.[col6] ,tsv3.[col7] ,tsv3.[col8]); END TRY BEGIN CATCH SELECT @error = ERROR_MESSAGE() RAISERROR(@error,16,2) END CATCHENDMon, 25 Feb 2013 04:32:58 GMTsathiyan00