All SP's slow than corresponding query

I am working on a database the eventually hold a large amount of data.
For some reason, now, all my stored procedures that used to run quickly, now
a lot slower than the exact same as a query int he analyzer. The plans show
exactly the same statistics, but SP takes about 10 times longer.
Any ideas out there?
TIA1. Check the query plans to see if they are the same..
2. One common potential problem is using local variables in a where clause
in a SP... At the time the sp is optimized, the optimizer CAN see all of the
sp parameter values, but local variables do not get a value until run
time... Therefore the optimizer can NOT use index statistics on these
queries to determine the most useful index, which can lead to performance
problems in sps.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"DWinter" <dwinter@.attbi.com> wrote in message
news:uPOjaf6eDHA.560@.tk2msftngp13.phx.gbl...
> I am working on a database the eventually hold a large amount of data.
> For some reason, now, all my stored procedures that used to run quickly,
now
> a lot slower than the exact same as a query int he analyzer. The plans
show
> exactly the same statistics, but SP takes about 10 times longer.
> Any ideas out there?
> TIA
>|||Is it possible that they are recompiling for some reason? Check with
Profiler, you have SP:Recompile and some other events.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"DWinter" <dwinter@.attbi.com> wrote in message
news:uPOjaf6eDHA.560@.tk2msftngp13.phx.gbl...
> I am working on a database the eventually hold a large amount of data.
> For some reason, now, all my stored procedures that used to run quickly,
now
> a lot slower than the exact same as a query int he analyzer. The plans
show
> exactly the same statistics, but SP takes about 10 times longer.
> Any ideas out there?
> TIA
>|||Yes, the plan is the same, an no local variables used. When I say All, I
mean all stored procedures are slower. Something is wrong. Don't know if
some setting got set somehow or what.
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:%235dcMr6eDHA.1736@.TK2MSFTNGP12.phx.gbl...
> 1. Check the query plans to see if they are the same..
> 2. One common potential problem is using local variables in a where clause
> in a SP... At the time the sp is optimized, the optimizer CAN see all of
the
> sp parameter values, but local variables do not get a value until run
> time... Therefore the optimizer can NOT use index statistics on these
> queries to determine the most useful index, which can lead to performance
> problems in sps.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "DWinter" <dwinter@.attbi.com> wrote in message
> news:uPOjaf6eDHA.560@.tk2msftngp13.phx.gbl...
> > I am working on a database the eventually hold a large amount of data.
> > For some reason, now, all my stored procedures that used to run quickly,
> now
> > a lot slower than the exact same as a query int he analyzer. The plans
> show
> > exactly the same statistics, but SP takes about 10 times longer.
> >
> > Any ideas out there?
> >
> > TIA
> >
> >
>|||does the execution plan show a hash join or a hash match
operation?
if so, are the more than 10,000 rows involved in that op?
read BOL for join types, at some point the hash join
switches from in memory to grace or recursive.
the setting is different for RPC and SQL Batch.
From an ADO client program, if you append parameters, the
proc will show up in Profiler as RPC, anything you do in
QA shows up as SQL Batch.
if this is the case, there are a few things you can do to
avoid this
>--Original Message--
>I am working on a database the eventually hold a large
amount of data.
>For some reason, now, all my stored procedures that used
to run quickly, now
>a lot slower than the exact same as a query int he
analyzer. The plans show
>exactly the same statistics, but SP takes about 10 times
longer.
>Any ideas out there?
>TIA
>
>.
>|||It was a has issue. Sorry to inconvenience you guys.
I should have seen it earlier.
"Yovan Fernandez" <yfernandez@.sai-inc.com> wrote in message
news:mwm9b.2723$ev2.1916726@.newssrv26.news.prodigy.com...
> I have experienced this problem before on another DB i had, it has to do
> with Recompiling like Dejan said, one way to stop recompiling is to turn
> auto statistics off I really think having DB option AUTO_UPDATE_STATISTIC
on
> And when you said all are you including system sp or just user sps?
> Yovan Fernandez
>
> "DWinter" <dwinter@.attbi.com> wrote in message
> news:%23zA79w6eDHA.3216@.tk2msftngp13.phx.gbl...
> > Yes, the plan is the same, an no local variables used. When I say All, I
> > mean all stored procedures are slower. Something is wrong. Don't know if
> > some setting got set somehow or what.
> >
> > "Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
> > news:%235dcMr6eDHA.1736@.TK2MSFTNGP12.phx.gbl...
> > > 1. Check the query plans to see if they are the same..
> > > 2. One common potential problem is using local variables in a where
> clause
> > > in a SP... At the time the sp is optimized, the optimizer CAN see all
of
> > the
> > > sp parameter values, but local variables do not get a value until run
> > > time... Therefore the optimizer can NOT use index statistics on these
> > > queries to determine the most useful index, which can lead to
> performance
> > > problems in sps.
> > >
> > > --
> > > Wayne Snyder, MCDBA, SQL Server MVP
> > > Computer Education Services Corporation (CESC), Charlotte, NC
> > > www.computeredservices.com
> > > (Please respond only to the newsgroups.)
> > >
> > > I support the Professional Association of SQL Server (PASS) and it's
> > > community of SQL Server professionals.
> > > www.sqlpass.org
> > >
> > >
> > > "DWinter" <dwinter@.attbi.com> wrote in message
> > > news:uPOjaf6eDHA.560@.tk2msftngp13.phx.gbl...
> > > > I am working on a database the eventually hold a large amount of
data.
> > > > For some reason, now, all my stored procedures that used to run
> quickly,
> > > now
> > > > a lot slower than the exact same as a query int he analyzer. The
plans
> > > show
> > > > exactly the same statistics, but SP takes about 10 times longer.
> > > >
> > > > Any ideas out there?
> > > >
> > > > TIA
> > > >
> > > >
> > >
> > >
> >
> >
>|||> It was a has issue. Sorry to inconvenience you guys.
> I should have seen it earlier.
No inconvenince, we are glad you worked it out, that's why newsgroups are
for :-)
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org