Logical Reads

[Mitch]

Hello, I have a bottleneck in a stored procedure. In the QA environment, I
ran profiler and the stored procedure took over an hour and came back with
1.6 billion reads and row count 39. I copied the same databases to a
different server and ran the same sproc, this time it ran in about 10 seconds
with 14 million reads and row count 39.

Can anyone point me in the right direction to look at why the sproc runs so
differently in both environments? Why such large logical reads?

Thanks,
Mitch

[Greg Linwood]

Quite likely you've got different query plans between the servers.

Try running the queries again, using the Estimated Plan option in SSMS &
compare how the queries are being processed by SQL Server.

If you post the query plans to this forum, we might be able to help with
ideas on how to make them more consistent.

Regards,
Greg Linwood
SQL Server MVP

"Mitch" <Mi...@discussions.microsoft.com> wrote in message
news:5E273211-E379-40FA...@microsoft.com...

[Uri Dimant]

Hi
As Greg has already pointed in order to give qulaified solution we nee to
take a look both execution plans

"Mitch" <Mi...@discussions.microsoft.com> wrote in message
news:5E273211-E379-40FA...@microsoft.com...

[Andrew J. Kelly]

Also did you update the statistics after you copied it?

--

Andrew J. Kelly SQL MVP
Solid Quality Mentors

"Mitch" <Mi...@discussions.microsoft.com> wrote in message
news:5E273211-E379-40FA...@microsoft.com...

[Uri Dimant]

Andrew

>I copied the same databases to a
>> different server and ran the same sproc, this time it ran in about 10
>> seconds

The OP claims that after copying the db it runs much faster than before :-)

"Andrew J. Kelly" <sqlmvpn...@shadhawk.com> wrote in message
news:OGPYMNyq...@TK2MSFTNGP04.phx.gbl...

[Mitch]

To answer Andrew's question, no, I did not update the stats after restoring
the db's to the new server.

How do I post the execution plan? I am not seeing an option to attach a
document.

Our performance tools suggests that the following query is the bottleneck of
the process:

DECLARE @spa_option_bunt INT
DECLARE @count_total INT

SELECT @spa_option_bunt = spa_option_pc_business_unit_id FROM
omt.site_grouping WITH (NOLOCK)

SELECT
@count_total = COUNT(pp.id)
FROM
omt.statement_text st WITH (NOLOCK)
INNER JOIN
target_pc_ccs_service_code sc WITH (NOLOCK)
ON sc.id = st.pc_ccs_service_code_id
AND sc.pc_business_unit_id <> @spa_option_bunt
INNER JOIN
target_pc_product_pricing_plan_charge_version charge WITH (NOLOCK)
ON charge.id = sc.pc_product_pricing_plan_charge_id
INNER JOIN
target_pc_rule_qualification rq WITH (NOLOCK)
ON qualifying_output_id = charge.pc_product_pricing_plan_id
AND rq.pc_business_unit_id = sc.pc_business_unit_id
AND pc_rule_execution_category_id = 3
INNER JOIN
target_pc_product_pricing_plan pp WITH (NOLOCK)
ON pp.id = charge.pc_product_pricing_plan_id
INNER JOIN
omt.site_grouping_detail detail WITH (NOLOCK)
ON detail.pc_business_unit_id = st.pc_business_unit_id
AND detail.pull_data = 1
AND detail.spa_type = 'A'
LEFT OUTER JOIN
target_pc_text pt WITH (NOLOCK)
ON pt.guid = pp.row_guid
AND pt.pc_business_unit_id = pp.pc_business_unit_id
AND pt.pc_text_render_id = 11
LEFT OUTER JOIN
target_pc_text pt_child WITH (NOLOCK)
ON pt_child.guid = pp.row_guid
AND pt_child.pc_business_unit_id = rq.pc_business_unit_id
AND pt_child.pc_text_render_id = 11
WHERE
st.[name] COLLATE SQL_Latin1_General_CP1_CS_AS <>
COALESCE(pt_child.[text],pt.[text])

"Greg Linwood" wrote:

> .
>

[Erland Sommarskog]

Mitch (Mi...@discussions.microsoft.com) writes:
> How do I post the execution plan? I am not seeing an option to attach a
> document.

That depends on the newsreader or the web interface you use to access
this forum. There are plentiful of options, and we don't know them all.

If you UI does not permit you attach documents, you can always upload
the plans on a web site somewhere and post the link. Or get a better
newsreader. :-)

> Our performance tools suggests that the following query is the
> bottleneck of the process:

Having seen the query, I am not surprised.

If I am to guess, your query could benefit from one or two indexes. In the
meanwhile the optimizer does as it good as it can, but it can easily go
wrong.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Shin Zhang [MSFT]]

I would not suprise if you see a hash join based plan changed to nested loop
join based plan. That will tip-over the balance of the balance, and hence
caused additional reads in your query.

From the # of reads you are comparing, I suspect that's the issue. You can
use 'set showplan_text on' and copy past the text result, and we can do a
quick analyze of the plans.

Hope it helps,
--
Shin
MS SQL Server