I copied a 2000 database onto 2005 on another box. Running the exact same SELECT query, with 7 tables joined, is running in less than 2 minutes on 2000 and almost 3 hours on 2005.
The 2005 box is "bigger and better". I've compared the sp_configure output. There are differences in max worker threads, cost threshold for parallelism, and priority boost. I don't think any one of these is significant. I checked the server properties and the database properties and, again, I did not see what I would consider any significant differences. The 2005 instance has 4GB memory, as opposed to 2GB, but not using that much. In fact, the Total Server Memory (KB) for the instance indicates around 1,230,000 for some reason.
We need to resolve this problem ASAP as it is a roadblock to conversion to 2005 so any help would be appreciated. What else can I check?
Upon further research, it really seems to have to do with a computed column in the join.
Any information on how we can improve performance on this column join? I cannot index this column.
|||using a user-defined function...
CREATE FUNCTION fn_normalize_sic (@.SIC varchar(8))
RETURNS varchar(2) AS
BEGIN
DECLARE @.SIC2 varchar(2)
SET @.SIC2 = (isnull(substring(@.SIC,1,2),'00'))
IF @.SIC2 = ' '
BEGIN
SET @.SIC2 = '00'
END
RETURN @.SIC2
END
Why would this column take so much longer to join on than in 2000?
|||Can you post the showplan text output for the queries in SQL 2000 and SQL 2005? This can be done in Query Analyzer by first running[code]
set showplan_text on
[/code]|||
2000:
|--Assert(WHERE:(If (NOT([Pass1059]) AND NOT(([Expr1058] IS NULL))) then 0 else If (NOT([Pass1061]) AND NOT(([Expr1060] IS NULL))) then 1 else If (NOT([Pass1063]) AND NOT(([Expr1062] IS NULL))) then 2 else If (NOT([Pass1065]) AND NOT(([Expr1064] IS NULL)
|--Nested Loops(Left Semi Join, WHERE:([Act1057]<>3)OUTER REFERENCES:([fc].[customer_id]), DEFINE:([Expr1074] = [PROBE VALUE]))
|--Nested Loops(Left Semi Join, WHERE:([Act1057]<>3)OUTER REFERENCES:([fc].[customer_id]), DEFINE:([Expr1072] = [PROBE VALUE]))
| |--Nested Loops(Left Semi Join, WHERE:([Act1057]<>3)OUTER REFERENCES:([fc].[customer_id]), DEFINE:([Expr1070] = [PROBE VALUE]))
| | |--Nested Loops(Left Semi Join, WHERE:([Act1057]<>3)OUTER REFERENCES:([fc].[customer_id]), DEFINE:([Expr1068] = [PROBE VALUE]))
| | | |--Nested Loops(Left Semi Join, WHERE:([Act1057]<>3)OUTER REFERENCES:([fc].[customer_id]), DEFINE:([Expr1066] = [PROBE VALUE]))
| | | | |--Nested Loops(Left Semi Join, WHERE:([Act1057]<>3)OUTER REFERENCES:([fc].[customer_id]), DEFINE:([Expr1064] = [PROBE VALUE]))
| | | | | |--Nested Loops(Left Semi Join, WHERE:([Act1057]<>3)OUTER REFERENCES:([fc].[customer_id]), DEFINE:([Expr1062] = [PROBE VALUE]))
| | | | | | |--Nested Loops(Left Semi Join, WHERE:([Act1057]<>3)OUTER REFERENCES:([fc].[customer_id]), DEFINE:([Expr1060] = [PROBE VALUE]))
| | | | | | | |--Nested Loops(Left Semi Join, WHERE:([Act1057]<>3)OUTER REFERENCES:([fc].[customer_id]), DEFINE:([Expr1058] = [PROBE VALUE]))
| | | | | | | | |--Clustered Index Update(OBJECT:([omeda-successories].[dbo].[o_t_fc_customer].[PK_o_t_fc_customer]), SET:([o_t_fc_customer].[category_id]=[fc].[category_id], [o_t_fc_customer].[orig_pricode_id]=[fc].[ori
| | | | | | | | | |--Collapse(GROUP BY:([fc].[customer_id]))
| | | | | | | | | |--Sort(ORDER BY:([fc].[customer_id] ASC, [Act1057] ASC))
| | | | | | | | | |--Split
| | | | | | | | | |--Compute Scalar(DEFINE:([Expr1019]=If ([o_t_d_orig_category].[category_id]<>NULL) then [o_t_d_orig_category].[category_id] else 4, [Expr1020]=If ([o_t_d_orig_pricode].[orig_pricode_i
| | | | | | | | | |--Top(ROWCOUNT est 0)
| | | | | | | | | |--Hash Match(Aggregate, HASH:([Bmk1000]), RESIDUAL:([Bmk1000]=[Bmk1000]) DEFINE:([fc].[customer_id]=ANY([fc].[customer_id]), [fc].[channel_id]=ANY([fc].[channel_id]), [fc].[
| | | | | | | | | |--Filter(WHERE:(checksum([fc].[customer_id], [fc].[channel_id], [fc].[class_id], [fc].[sic_id], [fc].[empsize_id], [fc].[orig_pricode_id], [fc].[category_id])<>checksum
| | | | | | | | | |--Hash Match(Right Outer Join, HASH:([o_t_d_orig_category].[category])=([o_t_customer].[original_category]), RESIDUAL:([o_t_customer].[original_category]=[o_t_d_or
| | | | | | | | | |--Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_d_orig_category].[IX_o_t_d_orig_category]))
| | | | | | | | | |--Hash Match(Right Outer Join, HASH:([o_t_d_orig_pricode].[orig_pricode])=([o_t_customer].[original_pri_code]), RESIDUAL:([o_t_customer].[original_pri_code]=[
| | | | | | | | | |--Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_d_orig_pricode].[IX_o_t_d_orig_pricode]))
| | | | | | | | | |--Hash Match(Inner Join, HASH:([o_t_d_class].[class])=([o_t_customer].[class]), RESIDUAL:([o_t_customer].[class]=[o_t_d_class].[class]))
| | | | | | | | | |--Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_d_class].[IX_o_t_d_class_class]))
| | | | | | | | | |--Hash Match(Inner Join, HASH:([o_t_d_channel].[channel])=([o_t_customer].[channel]), RESIDUAL:([o_t_customer].[channel]=[o_t_d_channel].[channel]))
| | | | | | | | | |--Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_d_channel].[IX_o_t_d_channel_channel]))
| | | | | | | | | |--Hash Match(Inner Join, HASH:([o_t_d_sic].[sic])=([o_t_customer].[sic2]), RESIDUAL:([o_t_customer].[sic2]=[o_t_d_sic].[sic]))
| | | | | | | | | |--Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_d_sic].[IX_o_t_d_sic_sic]))
| | | | | | | | | |--Hash Match(Inner Join, HASH:([o_t_d_empsize].[empsize])=([o_t_customer].[empsize]), RESIDUAL:([o_t_customer].[empsize]=[o_t_d_empsize].[
| | | | | | | | | |--Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_d_empsize].[IX_o_t_d_empsize_empsize]))
| | | | | | | | | |--Merge Join(Inner Join, MERGE:([o_t_customer].[customer_id])=([fc].[customer_id]), RESIDUAL:([fc].[customer_id]=[o_t_customer].[cust
| | | | | | | | | |--Compute Scalar(DEFINE:([o_t_customer].[class]=isnull([dbo].[fn_determine_class](Convert(dbo.o_t_customer.[company_name]), Conv
| | | | | | | | | | |--Clustered Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_customer].[PK_o_t_customer]), ORDERED FORWARD)
| | | | | | | | | |--Clustered Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_fc_customer].[PK_o_t_fc_customer] AS [fc]), ORDERED FORWARD)
| | | | | | | | |--Row Count Spool
| | | | | | | | |--Clustered Index Seek(OBJECT:([omeda-successories].[dbo].[o_t_fb_ordering_recency_comparision].[PK_o_t_fb_ordering_recency_comparision]), SEEK:([o_t_fb_ordering_recency_comparision].[customer_id]=[
| | | | | | | |--Row Count Spool
| | | | | | | |--Clustered Index Seek(OBJECT:([omeda-successories].[dbo].[o_t_fb_new_vs_existing].[PK_o_t_fb_new_vs_existing]), SEEK:([o_t_fb_new_vs_existing].[customer_id]=[fc].[customer_id]) ORDERED FORWARD)
| | | | | | |--Row Count Spool
| | | | | | |--Clustered Index Seek(OBJECT:([omeda-successories].[dbo].[o_t_fb_account_age_recency_comparision].[PK_o_t_fb_account_age_recency_comparision]), SEEK:([o_t_fb_account_age_recency_comparision].[customer_id]=[f
| | | | | |--Row Count Spool
| | | | | |--Clustered Index Seek(OBJECT:([omeda-successories].[dbo].[o_t_fb_order_sales_info].[PK_o_t_fb_order_sales_info]), SEEK:([o_t_fb_order_sales_info].[customer_id]=[fc].[customer_id]) ORDERED FORWARD)
| | | | |--Row Count Spool
| | | | |--Clustered Index Seek(OBJECT:([omeda-successories].[dbo].[o_t_fb_order_receipt_info].[PK_o_t_fb_order_receipt_info]), SEEK:([o_t_fb_order_receipt_info].[customer_id]=[fc].[customer_id]) ORDERED FORWARD)
| | | |--Row Count Spool
| | | |--Clustered Index Seek(OBJECT:([omeda-successories].[dbo].[o_t_fb_current_activity].[PK_o_t_fb_current_activity]), SEEK:([o_t_fb_current_activity].[customer_id]=[fc].[customer_id]) ORDERED FORWARD)
| | |--Row Count Spool
| | |--Clustered Index Seek(OBJECT:([omeda-successories].[dbo].[o_t_fb_product_receipt_info].[PK_o_t_fb_product_receipt_info]), SEEK:([o_t_fb_product_receipt_info].[customer_id]=[fc].[customer_id]) ORDERED FORWARD)
| |--Row Count Spool
| |--Clustered Index Seek(OBJECT:([omeda-successories].[dbo].[o_t_fb_shipping_recency_comparision].[PK_o_t_fb_shipping_recency_comparision]), SEEK:([o_t_fb_shipping_recency_comparision].[customer_id]=[fc].[customer_id]) ORDERED FORWARD)
|--Row Count Spool
|--Clustered Index Seek(OBJECT:([omeda-successories].[dbo].[o_t_fb_product_sales_info].[PK_o_t_fb_product_sales_info]), SEEK:([o_t_fb_product_sales_info].[customer_id]=[fc].[customer_id]) ORDERED FORWARD)
2005:
|--Assert(WHERE:(CASE WHEN NOT [Pass1104] AND NOT [Expr1103] IS NULL THEN (0) ELSE CASE WHEN NOT [Pass1106] AND NOT [Expr1105] IS NULL THEN (1) ELSE CASE WHEN NOT [Pass1108] AND NOT [Expr1107] IS NULL THEN (2) ELSE CASE WHEN NOT [Pass1110] AND NOT [Expr1109] IS NULL THEN (3) ELSE CASE WHEN NOT [Pass1112] AND NOT [Expr1111] IS NULL THEN (4) ELSE CASE WHEN NOT [Pass1114] AND NOT [Expr1113] IS NULL THEN (5) ELSE CASE WHEN NOT [Pass1116] AND NOT [Expr1115] IS NULL THEN (6) ELSE CASE WHEN NOT [Pass1118] AND NOT [Expr1117] IS NULL THEN (7) ELSE CASE WHEN NOT [Pass1120] AND NOT [Expr1119] IS NULL THEN (8) ELSE NULL END END END END END END END END END))
|--Merge Join(Left Semi Join, MANY-TO-MANY MERGE:([omeda-successories].[dbo].[o_t_fc_customer].[customer_id])=([omeda-successories].[dbo].[o_t_fb_product_sales_info].[customer_id]), RESIDUAL:([omeda-successories].[dbo].[o_t_fb_product_sales_info].[customer_id]=[omeda-successories].[dbo].[o_t_fc_customer].[customer_id]), PASSTHRU:([Act1102]<>(3)))
|--Nested Loops(Left Semi Join, PASSTHRU:([Act1102]<>(3)), OUTER REFERENCES:([omeda-successories].[dbo].[o_t_fc_customer].[customer_id]), DEFINE:([Expr1117] = [PROBE VALUE]))
| |--Merge Join(Left Semi Join, MANY-TO-MANY MERGE:([omeda-successories].[dbo].[o_t_fc_customer].[customer_id])=([omeda-successories].[dbo].[o_t_fb_product_receipt_info].[customer_id]), RESIDUAL:([omeda-successories].[dbo].[o_t_fb_product_receipt_info].[customer_id]=[omeda-successories].[dbo].[o_t_fc_customer].[customer_id]), PASSTHRU:([Act1102]<>(3)))
| | |--Nested Loops(Left Semi Join, PASSTHRU:([Act1102]<>(3)), OUTER REFERENCES:([omeda-successories].[dbo].[o_t_fc_customer].[customer_id]), DEFINE:([Expr1113] = [PROBE VALUE]))
| | | |--Merge Join(Left Semi Join, MANY-TO-MANY MERGE:([omeda-successories].[dbo].[o_t_fc_customer].[customer_id])=([omeda-successories].[dbo].[o_t_fb_order_receipt_info].[customer_id]), RESIDUAL:([omeda-successories].[dbo].[o_t_fb_order_receipt_info].[customer_id]=[omeda-successories].[dbo].[o_t_fc_customer].[customer_id]), PASSTHRU:([Act1102]<>(3)))
| | | | |--Merge Join(Left Semi Join, MANY-TO-MANY MERGE:([omeda-successories].[dbo].[o_t_fc_customer].[customer_id])=([omeda-successories].[dbo].[o_t_fb_order_sales_info].[customer_id]), RESIDUAL:([omeda-successories].[dbo].[o_t_fb_order_sales_info].[customer_id]=[omeda-successories].[dbo].[o_t_fc_customer].[customer_id]), PASSTHRU:([Act1102]<>(3)))
| | | | | |--Nested Loops(Left Semi Join, PASSTHRU:([Act1102]<>(3)), OUTER REFERENCES:([omeda-successories].[dbo].[o_t_fc_customer].[customer_id]), DEFINE:([Expr1107] = [PROBE VALUE]))
| | | | | | |--Merge Join(Left Semi Join, MANY-TO-MANY MERGE:([omeda-successories].[dbo].[o_t_fc_customer].[customer_id])=([omeda-successories].[dbo].[o_t_fb_new_vs_existing].[customer_id]), RESIDUAL:([omeda-successories].[dbo].[o_t_fb_new_vs_existing].[customer_id]=[omeda-successories].[dbo].[o_t_fc_customer].[customer_id]), PASSTHRU:([Act1102]<>(3)))
| | | | | | | |--Merge Join(Left Semi Join, MANY-TO-MANY MERGE:([omeda-successories].[dbo].[o_t_fc_customer].[customer_id])=([omeda-successories].[dbo].[o_t_fb_ordering_recency_comparision].[customer_id]), RESIDUAL:([omeda-successories].[dbo].[o_t_fb_ordering_recency_comparision].[customer_id]=[omeda-successories].[dbo].[o_t_fc_customer].[customer_id]), PASSTHRU:([Act1102]<>(3)))
| | | | | | | | |--Sort(ORDER BY:([omeda-successories].[dbo].[o_t_fc_customer].[customer_id] ASC))
| | | | | | | | | |--Clustered Index Update(OBJECT:([omeda-successories].[dbo].[o_t_fc_customer].[PK_o_t_fc_customer]), SET:([omeda-successories].[dbo].[o_t_fc_customer].[customer_id] = RaiseIfNull([omeda-successories].[dbo].[o_t_fc_customer].[customer_id] as [fc].[customer_id]),[omeda-successories].[dbo].[o_t_fc_customer].[channel_id] = RaiseIfNull([omeda-successories].[dbo].[o_t_fc_customer].[channel_id] as [fc].[channel_id]),[omeda-successories].[dbo].[o_t_fc_customer].[class_id] = RaiseIfNull([omeda-successories].[dbo].[o_t_fc_customer].[class_id] as [fc].[class_id]),[omeda-successories].[dbo].[o_t_fc_customer].[sic_id] = RaiseIfNull([omeda-successories].[dbo].[o_t_fc_customer].[sic_id] as [fc].[sic_id]),[omeda-successories].[dbo].[o_t_fc_customer].[empsize_id] = RaiseIfNull([omeda-successories].[dbo].[o_t_fc_customer].[empsize_id] as [fc].[empsize_id]),[omeda-successories].[dbo].[o_t_fc_customer].[category_id] = [omeda-successories].[dbo].[o_t_fc_customer].[category_id] as [fc].[category_id],[omeda-successories].[dbo].[o_t_fc_customer].[orig_pricode_id] = [omeda-successories].[dbo].[o_t_fc_customer].[orig_pricode_id] as [fc].[orig_pricode_id]))
| | | | | | | | | |--Collapse(GROUP BY:([fc].[customer_id]))
| | | | | | | | | |--Sort(ORDER BY:([fc].[customer_id] ASC, [Act1102] ASC))
| | | | | | | | | |--Split
| | | | | | | | | |--Top(ROWCOUNT est 0)
| | | | | | | | | |--Compute Scalar(DEFINE:([Expr1018]=CASE WHEN [omeda-successories].[dbo].[o_t_d_orig_pricode].[orig_pricode_id] IS NOT NULL THEN [omeda-successories].[dbo].[o_t_d_orig_pricode].[orig_pricode_id] ELSE (3079) END, [Expr1019]=CASE WHEN [omeda-successories].[dbo].[o_t_d_orig_category].[category_id] IS NOT NULL THEN [omeda-successories].[dbo].[o_t_d_orig_category].[category_id] ELSE (4) END))
| | | | | | | | | |--Hash Match(Aggregate, HASH:([fc].[customer_id]) DEFINE:(
| | | | | | | | | |--Filter(WHERE:([Expr1058]<>checksum([omeda-successories].[dbo].[o_t_customer].[customer_id] as
| | | | | | | | | |--Compute Scalar(DEFINE:([Expr1059]=CASE WHEN [omeda-successories].[dbo].[o_t_d_orig_category].[category_id] IS NOT NULL THEN [omeda-successories].[dbo].[o_t_d_orig_category].[category_id] ELSE (4) END))
| | | | | | | | | |--Hash Match(Right Outer Join, HASH:([omeda-successories].[dbo].[o_t_d_orig_category].[category])=(
| | | | | | | | | |--Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_d_orig_category].[IX_o_t_d_orig_category]))
| | | | | | | | | |--Compute Scalar(DEFINE:([Expr1060]=CASE WHEN [omeda-successories].[dbo].[o_t_d_orig_pricode].[orig_pricode_id] IS NOT NULL THEN [omeda-successories].[dbo].[o_t_d_orig_pricode].[orig_pricode_id] ELSE (3079) END))
| | | | | | | | | |--Hash Match(Right Outer Join, HASH:([omeda-successories].[dbo].[o_t_d_orig_pricode].[orig_pricode])=(
| | | | | | | | | |--Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_d_orig_pricode].[IX_o_t_d_orig_pricode]))
| | | | | | | | | |--Hash Match(Inner Join, HASH:(
| | | | | | | | | |--Filter(WHERE:(
| | | | | | | | | | |--Compute Scalar(DEFINE:(
| | | | | | | | | | |--Nested Loops(Inner Join)
| | | | | | | | | | |--Hash Match(Inner Join, HASH:([ch].[channel])=(
| | | | | | | | | | | |--Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_d_channel].[IX_o_t_d_channel_channel] AS [ch]))
| | | | | | | | | | | |--Hash Match(Inner Join, HASH:([es].[empsize])=(
| | | | | | | | | | | |--Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_d_empsize].[IX_o_t_d_empsize_empsize] AS [es]))
| | | | | | | | | | | |--Hash Match(Inner Join, HASH:(
Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_d_sic].[IX_o_t_d_sic_sic] AS
Compute Scalar(DEFINE:([Expr1061]=CONVERT_IMPLICIT(char(4),[omeda-successories].[dbo].[o_t_customer].[sic] as
Clustered Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_customer].[PK_o_t_customer] AS
| | | | | | | | | | |--Clustered Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_d_class].[PK_o_t_d_class] AS [cl]))
| | | | | | | | | |--Compute Scalar(DEFINE:([Expr1058]=checksum([omeda-successories].[dbo].[o_t_fc_customer].[customer_id] as [fc].[customer_id],[omeda-successories].[dbo].[o_t_fc_customer].[channel_id] as [fc].[channel_id],[omeda-successories].[dbo].[o_t_fc_customer].[class_id] as [fc].[class_id],[omeda-successories].[dbo].[o_t_fc_customer].[sic_id] as [fc].[sic_id],[omeda-successories].[dbo].[o_t_fc_customer].[empsize_id] as [fc].[empsize_id],[omeda-successories].[dbo].[o_t_fc_customer].[orig_pricode_id] as [fc].[orig_pricode_id],[omeda-successories].[dbo].[o_t_fc_customer].[category_id] as [fc].[category_id])))
| | | | | | | | | |--Clustered Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_fc_customer].[PK_o_t_fc_customer] AS [fc]))
| | | | | | | | |--Clustered Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_fb_ordering_recency_comparision].[PK_o_t_fb_ordering_recency_comparision]), ORDERED FORWARD)
| | | | | | | |--Clustered Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_fb_new_vs_existing].[PK_o_t_fb_new_vs_existing]), ORDERED FORWARD)
| | | | | | |--Row Count Spool
| | | | | | |--Clustered Index Seek(OBJECT:([omeda-successories].[dbo].[o_t_fb_account_age_recency_comparision].[PK_o_t_fb_account_age_recency_comparision]), SEEK:([omeda-successories].[dbo].[o_t_fb_account_age_recency_comparision].[customer_id]=[omeda-successories].[dbo].[o_t_fc_customer].[customer_id]) ORDERED FORWARD)
| | | | | |--Clustered Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_fb_order_sales_info].[PK_o_t_fb_order_sales_info]), ORDERED FORWARD)
| | | | |--Clustered Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_fb_order_receipt_info].[PK_o_t_fb_order_receipt_info]), ORDERED FORWARD)
| | | |--Row Count Spool
| | | |--Clustered Index Seek(OBJECT:([omeda-successories].[dbo].[o_t_fb_current_activity].[PK_o_t_fb_current_activity]), SEEK:([omeda-successories].[dbo].[o_t_fb_current_activity].[customer_id]=[omeda-successories].[dbo].[o_t_fc_customer].[customer_id]) ORDERED FORWARD)
| | |--Clustered Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_fb_product_receipt_info].[PK_o_t_fb_product_receipt_info]), ORDERED FORWARD)
| |--Row Count Spool
| |--Clustered Index Seek(OBJECT:([omeda-successories].[dbo].[o_t_fb_shipping_recency_comparision].[PK_o_t_fb_shipping_recency_comparision]), SEEK:([omeda-successories].[dbo].[o_t_fb_shipping_recency_comparision].[customer_id]=[omeda-successories].[dbo].[o_t_fc_customer].[customer_id]) ORDERED FORWARD)
|--Clustered Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_fb_product_sales_info].[PK_o_t_fb_product_sales_info]), ORDERED FORWARD)
UDF: [omeda-successories].[dbo].[fn_determine_class]
CREATE FUNCTION fn_determine_class (@.company_name varchar(255), @.sic char(4), @.dsf2_flag char(1) ) RETURNS char(1) AS BEGIN DECLARE @.class char(1) SET @.class = 'N'
IF @.dsf2_flag <> 'B' AND (@.company_name = '' OR @.company_name IS NULL) AND (@.sic = ' ' OR @.sic = ' 00' OR @.sic = '0000')
SET @.class = 'N'
ELSE SET @.class = 'B'
RETURN ( @.class )
Hi All--
I am having a similar issue and coincidentally in the same situation....Any resolution advice is greatly appreciated.
|||can u tell us what all are the steps you have done after the restoration of SQL 2000 datbase in sql 2005.
(a) did u run DBCC DBREINDEX for each table?
(b) have u updated the statistics ?
Madhu
|||Check to see that the index on o_t_fc_customer [Customer_ID] is valid, and the statistics are up to date. If there is no index, check to see that any statistics that were automatically generated InSQL 2000 on this column are generated in SQL 2005. It appears that this table and column are repeatedly involved in merge joins and clustered index scans, which are causing most if not all of your problem.|||You said copied the database, have you used RESTORE method or UPGRADE method from SQL 2000.|||The indexes were check for fragmentation and rebuilt where needed. I don't believe it's the indexes.
One of the underlying tables in the view's join has a column that is computed based on a user-defined function referencing another column in that same table. If I substitued a CASE statement in the join with the same logic instead of calling the UDF, my response time goes from hours to minutes. Does anyone know why?
|||The indexes were check for fragmentation and rebuilt where needed. I don't believe it's the indexes.
One of the underlying tables in the view's join has a column that is computed based on a user-defined function referencing another column in that same table. If I substitued a CASE statement in the join with the same logic instead of calling the UDF, my response time goes from hours to minutes. Does anyone know why?
|||I used the restore method to move my database from 2000 to 2005.
|||I'm finding a completely different plan between the 2 versions of SQL Server for the same database and data.
SQL:
SELECT c.customer_id,
cl.class_id,
s.sic_id
FROM dbo.o_t_customer c INNER JOIN
dbo.o_t_d_class cl ON c.class = cl.class INNER JOIN
dbo.o_t_d_sic s ON c.sic2 = s.sic
(Note that column sic2 on the o_t_customer table is defined by a UDF using the sic column. C.class is also defined by a UDF based on several columns.)
2000:
|--Parallelism(Gather Streams)
|--Hash Match(Inner Join, HASH:(
|--Parallelism(Distribute Streams, PARTITION COLUMNS:(
| |--Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_d_sic].[IX_o_t_d_sic_sic] AS
|--Parallelism(Repartition Streams, PARTITION COLUMNS:(
|--Hash Match(Inner Join, HASH:([cl].[class])=(
|--Parallelism(Broadcast)
| |--Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_d_class].[IX_o_t_d_class_class] AS [cl]))
|--Compute Scalar(DEFINE:(
|--Clustered Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_customer].[PK_o_t_customer] AS
2005:
|--Filter(WHERE:(
|--Compute Scalar(DEFINE:(
|--Nested Loops(Inner Join)
|--Nested Loops(Inner Join)
| |--Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_d_class].[IX_o_t_d_class_class] AS [cl]))
| |--Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_d_sic].[IX_o_t_d_sic_sic] AS
|--Table Spool
|--Compute Scalar(DEFINE:([Expr1006]=CONVERT_IMPLICIT(char(4),[omeda-successories].[dbo].[o_t_customer].[sic] as
|--Clustered Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_customer].[PK_o_t_customer] AS
The difference is the select statement running in minutes on 2000 and hours on 2005. Why?
|||A couple of things you want to do when upgrading from 2000 to 2005 is change the compatibility level to 90, and then run sp_UpdateStats on the database. You also should give the "Lock pages in memory" right (at the OS level) to the SQL Server Service account. Make sure that Priority Boost is turned off!
You did not say whether you are running 32 or 64-bit. Even with the /3GB switch, you are limited to 2GB of RAM for some items in terms of how memory is used, so 64-bit will let you take advantage of your 4GB or RAM.
http://glennberrysqlperformance.spaces.live.com
|||How do you give the locks pages in memroy Thanks
No comments:
Post a Comment