performance monitoring: box

Showing posts with label box. Show all posts

Friday, March 30, 2012

Performance Tuning Help needed

This is a Sql 2000 server running on Win 2k. This is a 1
CPU box connected to a SAN.
I ran a profiler trace for 3 hours and collected all the
data. Some of the queries show a large amount of reads.
When I run those queries individually the # of reads is
fairly smaller. For example the trace file shows 36503
reads and the individual query plan shows 91 CPU reads.
Whats going on ? Is the profiler tracking all I/O activity
at that time ? I mean, is it tracking all tempdb usage and
any other I/O activity at this time. What would explain
this large drop in I/O ?
THanks"JackA" <anonymous@.discussions.microsoft.com> wrote in message
news:061701c3a972$5d6cc080$a301280a@.phx.gbl...
> Whats going on ? Is the profiler tracking all I/O activity
> at that time ? I mean, is it tracking all tempdb usage and
> any other I/O activity at this time. What would explain
> this large drop in I/O ?
I'm new to SQL Server so take what I say with a grain of salt.. But does
the profiler keep track of how many executions for each SQL statement?
Executed once the number of reads may be small but if it was executed 1000
times during that timeframe I would expect the number to be large.|||i assume you ran the query with the same parameters?
if so, then this large a difference in reads might
indicate the trace captured that query with a different
plan. if the choice of parameters can results in large
differences in rows involved, then depending on what
parameters were used when the query was last compiled,
different executes could get different plans.
a smaller difference on the order of several hundred or
even more than 1k might indicate compile or recompile.
>--Original Message--
>This is a Sql 2000 server running on Win 2k. This is a 1
>CPU box connected to a SAN.
>I ran a profiler trace for 3 hours and collected all the
>data. Some of the queries show a large amount of reads.
>When I run those queries individually the # of reads is
>fairly smaller. For example the trace file shows 36503
>reads and the individual query plan shows 91 CPU reads.
>Whats going on ? Is the profiler tracking all I/O
activity
>at that time ? I mean, is it tracking all tempdb usage
and
>any other I/O activity at this time. What would explain
>this large drop in I/O ?
>THanks
>.
>|||Jack,
are you comparing Profiler output to Query Analyzer query plan?
If so... the difference is "normal".
Trust the Profiler, if it shows 36000 reads, that's really happening
in the DB.
peksi
"JackA" <anonymous@.discussions.microsoft.com> wrote in message
news:061701c3a972$5d6cc080$a301280a@.phx.gbl...
> This is a Sql 2000 server running on Win 2k. This is a 1
> CPU box connected to a SAN.
> I ran a profiler trace for 3 hours and collected all the
> data. Some of the queries show a large amount of reads.
> When I run those queries individually the # of reads is
> fairly smaller. For example the trace file shows 36503
> reads and the individual query plan shows 91 CPU reads.
> Whats going on ? Is the profiler tracking all I/O activity
> at that time ? I mean, is it tracking all tempdb usage and
> any other I/O activity at this time. What would explain
> this large drop in I/O ?
> THanks|||Hi Jack
The profiler shows what's happening at run time, which can be different from
what happens when you run the query for a few reasons, eg:
(a) The level & type of SQL Server activity is different when you run the
query from when the same query was captured by the profiler.
(b) The security context from which you ran the query may be different from
the query picked up by profiler.
(c) General resource levels on the server may be different.
Capturing the execution plans & comparing them may reveal some explanation
of what accounts for the different levels of reads, but this only takes you
part way to resolving the actual problem.
HTH
Regards,
Greg Linwood
SQL Server MVP
"JackA" <anonymous@.discussions.microsoft.com> wrote in message
news:061701c3a972$5d6cc080$a301280a@.phx.gbl...
> This is a Sql 2000 server running on Win 2k. This is a 1
> CPU box connected to a SAN.
> I ran a profiler trace for 3 hours and collected all the
> data. Some of the queries show a large amount of reads.
> When I run those queries individually the # of reads is
> fairly smaller. For example the trace file shows 36503
> reads and the individual query plan shows 91 CPU reads.
> Whats going on ? Is the profiler tracking all I/O activity
> at that time ? I mean, is it tracking all tempdb usage and
> any other I/O activity at this time. What would explain
> this large drop in I/O ?
> THankssql

Friday, March 23, 2012

Performance Question

I currently have SQL 2000 Standard on a box with a Single 2.4GHz Xeon HT
processor. Would my performance be better served by adding the 2nd CPU or
upgrading the current CPU to a 3.2GHz?
Any thoughts are welcome,
ScottGenerally I would go with a multi proc environment as you could
simultaneously serve mulitple requests faster although individual request ma
y
be served faster by increasing the clock speed of the single processor.
Licensing would also be a factor in my decision.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Scott Cadreau" wrote:

> I currently have SQL 2000 Standard on a box with a Single 2.4GHz Xeon HT
> processor. Would my performance be better served by adding the 2nd CPU or
> upgrading the current CPU to a 3.2GHz?
> Any thoughts are welcome,
> Scott
>
>

Wednesday, March 21, 2012

Performance problem: 2000 vs. 2005

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:( Coffee .[customer_id]=ANY([omeda-successories].[dbo].[o_t_customer].[customer_id] as Coffee .[customer_id]), [ch].[channel_id]=ANY([omeda-successories].[dbo].[o_t_d_channel].[channel_id] as [ch].[channel_id]), [cl].[class_id]=ANY([omeda-successories].[dbo].[o_t_d_class].[class_id] as [cl].[class_id]), [es].[empsize_id]=ANY([omeda-successories].[dbo].[o_t_d_empsize].[empsize_id] as [es].[empsize_id]), Sleep .[sic_id]=ANY([omeda-successories].[dbo].[o_t_d_sic].[sic_id] as Sleep .[sic_id]), [omeda-successories].[dbo].[o_t_d_orig_pricode].[orig_pricode_id]=ANY([omeda-successories].[dbo].[o_t_d_orig_pricode].[orig_pricode_id]), [omeda-successories].[dbo].[o_t_d_orig_category].[category_id]=ANY([omeda-successories].[dbo].[o_t_d_orig_category].[category_id])))
| | | | | | | | | |--Filter(WHERE:([Expr1058]<>checksum([omeda-successories].[dbo].[o_t_customer].[customer_id] as Coffee .[customer_id],[omeda-successories].[dbo].[o_t_d_channel].[channel_id] as [ch].[channel_id],[omeda-successories].[dbo].[o_t_d_class].[class_id] as [cl].[class_id],[omeda-successories].[dbo].[o_t_d_sic].[sic_id] as Sleep .[sic_id],[omeda-successories].[dbo].[o_t_d_empsize].[empsize_id] as [es].[empsize_id],[Expr1060],[Expr1059])))
| | | | | | | | | |--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])=( Coffee .[original_category]), RESIDUAL:([omeda-successories].[dbo].[o_t_customer].[original_category] as Coffee .[original_category]=[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])=( Coffee .[original_pri_code]), RESIDUAL:([omeda-successories].[dbo].[o_t_customer].[original_pri_code] as Coffee .[original_pri_code]=[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:( Coffee .[customer_id])=([fc].[customer_id]))
| | | | | | | | | |--Filter(WHERE:( Coffee .[class]=[omeda-successories].[dbo].[o_t_d_class].[class] as [cl].[class]))
| | | | | | | | | | |--Compute Scalar(DEFINE:( Coffee .[class]=isnull([omeda-successories].[dbo].[fn_determine_class]([omeda-successories].[dbo].[o_t_customer].[company_name] as Coffee .[company_name],[Expr1061],[omeda-successories].[dbo].[o_t_customer].[dsf2_flag] as Coffee .[dsf2_flag]),'N')))
| | | | | | | | | | |--Nested Loops(Inner Join)
| | | | | | | | | | |--Hash Match(Inner Join, HASH:([ch].[channel])=( Coffee .[channel]), RESIDUAL:([omeda-successories].[dbo].[o_t_customer].[channel] as Coffee .[channel]=[omeda-successories].[dbo].[o_t_d_channel].[channel] as [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])=( Coffee .[empsize]), RESIDUAL:([omeda-successories].[dbo].[o_t_customer].[empsize] as Coffee .[empsize]=[omeda-successories].[dbo].[o_t_d_empsize].[empsize] as [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:( Sleep .[sic])=([Expr1062]), RESIDUAL:([Expr1062]=[omeda-successories].[dbo].[o_t_d_sic].[sic] as Sleep .[sic]))
Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_d_sic].[IX_o_t_d_sic_sic] AS Sleep ))
Compute Scalar(DEFINE:([Expr1061]=CONVERT_IMPLICIT(char(4),[omeda-successories].[dbo].[o_t_customer].[sic] as Coffee .[sic],0), [Expr1062]=CASE WHEN isnull(substring([omeda-successories].[dbo].[o_t_customer].[sic] as Coffee .[sic],(1),(2)),'00')=' ' THEN '00' ELSE isnull(substring([omeda-successories].[dbo].[o_t_customer].[sic] as Coffee .[sic],(1),(2)),'00') END))
Clustered Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_customer].[PK_o_t_customer] AS Coffee ))
| | | | | | | | | | |--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.

|||

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:( Sleep .[sic])=( Coffee .[sic2]), RESIDUAL:( Coffee .[sic2]= Sleep .[sic]))
|--Parallelism(Distribute Streams, PARTITION COLUMNS:( Sleep .[sic]))
| |--Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_d_sic].[IX_o_t_d_sic_sic] AS Sleep ))
|--Parallelism(Repartition Streams, PARTITION COLUMNS:( Coffee .[sic2]))
|--Hash Match(Inner Join, HASH:([cl].[class])=( Coffee .[class]), RESIDUAL:( Coffee .[class]=[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:( Coffee .[class]=isnull([dbo].[fn_determine_class](Convert(dbo.o_t_customer.[company_name]), Convert(dbo.o_t_customer.[sic]), dbo.o_t_customer.[dsf2_flag]), Convert('N')), Coffee .[sic2]=[dbo].[fn_normalize_sic](dbo.o
|--Clustered Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_customer].[PK_o_t_customer] AS Coffee ))

2005:

|--Filter(WHERE:( Coffee .[class]=[omeda-successories].[dbo].[o_t_d_class].[class] as [cl].[class] AND Coffee .[sic2]=[omeda-successories].[dbo].[o_t_d_sic].[sic] as Sleep .[sic]))
|--Compute Scalar(DEFINE:( Coffee .[class]=isnull([omeda-successories].[dbo].[fn_determine_class]([omeda-successories].[dbo].[o_t_customer].[company_name] as Coffee .[company_name],[Expr1006],[omeda-successories].[dbo].[o_t_customer].[dsf2_flag] as Coffee .[dsf2_flag]),'N'), Coffee .[sic2]=[omeda-successories].[dbo].[fn_normalize_sic]([omeda-successories].[dbo].[o_t_customer].[sic] as Coffee .[sic])))
|--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 Sleep ))
|--Table Spool
|--Compute Scalar(DEFINE:([Expr1006]=CONVERT_IMPLICIT(char(4),[omeda-successories].[dbo].[o_t_customer].[sic] as Coffee .[sic],0)))
|--Clustered Index Scan(OBJECT:([omeda-successories].[dbo].[o_t_customer].[PK_o_t_customer] AS Coffee ))

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

Performance problem: 2000 vs. 2005

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.

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:

2005:

|||

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 runDBCC 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.

|||

The indexes were check for fragmentation and rebuilt where needed. I don't believe it's the indexes.

|||

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:

2005:

The difference is the select statement running in minutes on 2000 and hours on 2005. Why?

|||

http://glennberrysqlperformance.spaces.live.com

|||How do you give the locks pages in memroy Thanks

Friday, March 30, 2012

Performance Tuning Help needed

Friday, March 23, 2012

Performance Question

Wednesday, March 21, 2012

Performance problem: 2000 vs. 2005

Performance problem: 2000 vs. 2005

performance monitoring

Blog Archive

About Me