MS SQL Server - Execution plan diff from ASP.NET and QA

Author

Execution plan diff from ASP.NET and QA

Rich Miller

2005-03-30, 7:03 pm

I have a simple stored procedure that queries 1 table which holds 7.7
million records.

When I run the sproc from QA, it runs in 30 milliseconds.
When I run the sproc from within ASP.NET it runs in 20 seconds.

I ran profiler and found that the Execution plan is completely different
when run from ASP.NET vs. QA.

I am currently waiting for a call back from Microsoft, but in the meantime,
does anyone have any ideas?

Thanks.

Tibor Karaszi

2005-03-30, 7:03 pm

One possible reason if different SET options. But without the proc code it is difficult to say...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
http://www.sqlug.se/

"Rich Miller" <rooster575@hotmail.com> wrote in message news:OXPIhHUNFHA.508@TK2MSFTNGP12.phx.gbl...
>I have a simple stored procedure that queries 1 table which holds 7.7 million records.
>
> When I run the sproc from QA, it runs in 30 milliseconds.
> When I run the sproc from within ASP.NET it runs in 20 seconds.
>
> I ran profiler and found that the Execution plan is completely different when run from ASP.NET vs.
> QA.
>
> I am currently waiting for a call back from Microsoft, but in the meantime, does anyone have any
> ideas?
>
> Thanks.
>

Rich Miller

2005-03-30, 7:03 pm

can you elaborate on SET options.. I'm not sure what that means and how to
change it.

If it involves the parameters, they are provided and used in the query.
[not delared in the sproc and set to the input parameter values]

Thanks for your response.

"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
message news:%234f1bMUNFHA.1732@TK2MSFTNGP14.phx.gbl...
> One possible reason if different SET options. But without the proc code it
> is difficult to say...
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
> http://www.sqlug.se/
>
>
> "Rich Miller" <rooster575@hotmail.com> wrote in message
> news:OXPIhHUNFHA.508@TK2MSFTNGP12.phx.gbl...
>
>

Tibor Karaszi

2005-03-30, 7:03 pm

See Books Online, SET for descriptions for the SET settings. Some dev environment will turn on SET
settings for you, making the SET setting different between, say QA and ADO.NET.

Also, I would use Profiler to trace the TSQL execution of the proc from your .NET code and execute
that same code from QA to see if there is a difference.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
http://www.sqlug.se/

"Rich Miller" <rooster575@hotmail.com> wrote in message
news:eubptpUNFHA.2252@TK2MSFTNGP15.phx.gbl...
> can you elaborate on SET options.. I'm not sure what that means and how to change it.
>
> If it involves the parameters, they are provided and used in the query.
> [not delared in the sproc and set to the input parameter values]
>
> Thanks for your response.
>
>
> "Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in message
> news:%234f1bMUNFHA.1732@TK2MSFTNGP14.phx.gbl...
>
>

Rich Miller

2005-03-30, 7:03 pm

Thanks Tibor.. So far today your quite ahead of Microsoft. ;)

I'll let you know what I find.

"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
message news:%23WwgprUNFHA.568@TK2MSFTNGP09.phx.gbl...
> See Books Online, SET for descriptions for the SET settings. Some dev
> environment will turn on SET settings for you, making the SET setting
> different between, say QA and ADO.NET.
>
> Also, I would use Profiler to trace the TSQL execution of the proc from
> your .NET code and execute that same code from QA to see if there is a
> difference.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
> http://www.sqlug.se/
>
>
> "Rich Miller" <rooster575@hotmail.com> wrote in message
> news:eubptpUNFHA.2252@TK2MSFTNGP15.phx.gbl...
>
>

Rich Miller

2005-03-30, 7:03 pm

I ran;
DBCC USEROPTIONS
through an ASP.NET page and the results are:

textsize 2147483647
language us_english
dateformat mdy
datefirst 7
quoted_identifier SET
ansi_null_dflt_on SET
ansi_defaults SET
ansi_warnings SET
ansi_padding SET
ansi_nulls SET
concat_null_yields_n
ull SET

Through Query Analyzer the results are...
textsize 64512
language us_english
dateformat mdy
datefirst 7
quoted_identifier SET
arithabort SET
ansi_null_dflt_on SET
ansi_defaults SET
ansi_warnings SET
ansi_padding SET
ansi_nulls SET
concat_null_yields_n
ull SET

So I guess this is not my problem ??

"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
message news:%23WwgprUNFHA.568@TK2MSFTNGP09.phx.gbl...
> See Books Online, SET for descriptions for the SET settings. Some dev
> environment will turn on SET settings for you, making the SET setting
> different between, say QA and ADO.NET.
>
> Also, I would use Profiler to trace the TSQL execution of the proc from
> your .NET code and execute that same code from QA to see if there is a
> difference.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
> http://www.sqlug.se/
>
>
> "Rich Miller" <rooster575@hotmail.com> wrote in message
> news:eubptpUNFHA.2252@TK2MSFTNGP15.phx.gbl...
>
>

Andrew J. Kelly

2005-03-30, 7:03 pm

How are you calling it from ASP? There can be an implicit type conversion
that may be different between the two. Are you using the Parameters
collection and executing it as a sp or as text command?

--
Andrew J. Kelly SQL MVP

"Rich Miller" <rooster575@hotmail.com> wrote in message
news:On$kwWVNFHA.2356@TK2MSFTNGP14.phx.gbl...
>I ran;
> DBCC USEROPTIONS
> through an ASP.NET page and the results are:
>
> textsize 2147483647
> language us_english
> dateformat mdy
> datefirst 7
> quoted_identifier SET
> ansi_null_dflt_on SET
> ansi_defaults SET
> ansi_warnings SET
> ansi_padding SET
> ansi_nulls SET
> concat_null_yields_n
ull SET
>
> Through Query Analyzer the results are...
> textsize 64512
> language us_english
> dateformat mdy
> datefirst 7
> quoted_identifier SET
> arithabort SET
> ansi_null_dflt_on SET
> ansi_defaults SET
> ansi_warnings SET
> ansi_padding SET
> ansi_nulls SET
> concat_null_yields_n
ull SET
>
>
> So I guess this is not my problem ??
>
>
>
> "Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote
> in message news:%23WwgprUNFHA.568@TK2MSFTNGP09.phx.gbl...
>
>

Tibor Karaszi

2005-03-30, 7:03 pm

I see. I suggest again that you catch the call from Profiler and execute the very same from QA to
see if there is any difference.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
http://www.sqlug.se/

"Rich Miller" <rooster575@hotmail.com> wrote in message
news:On$kwWVNFHA.2356@TK2MSFTNGP14.phx.gbl...
>I ran;
> DBCC USEROPTIONS
> through an ASP.NET page and the results are:
>
> textsize 2147483647
> language us_english
> dateformat mdy
> datefirst 7
> quoted_identifier SET
> ansi_null_dflt_on SET
> ansi_defaults SET
> ansi_warnings SET
> ansi_padding SET
> ansi_nulls SET
> concat_null_yields_n
ull SET
>
> Through Query Analyzer the results are...
> textsize 64512
> language us_english
> dateformat mdy
> datefirst 7
> quoted_identifier SET
> arithabort SET
> ansi_null_dflt_on SET
> ansi_defaults SET
> ansi_warnings SET
> ansi_padding SET
> ansi_nulls SET
> concat_null_yields_n
ull SET
>
>
> So I guess this is not my problem ??
>
>
>
> "Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in message
> news:%23WwgprUNFHA.568@TK2MSFTNGP09.phx.gbl...
>
>

Sponsored Links