Re: Performance issues with stored procedure

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Wayne Snyder (wayne.nospam.snyder_at_mariner-usa.com)
Date: 06/28/04 

Date: Mon, 28 Jun 2004 08:00:22 -0400

On thing you might try is determining how much time that 32m row lookup is
taking... Measure both in and out of the proc. 

-- 
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Kalle Dahlberg" <kalle@newsgroups.nospam> wrote in message
news:E314ABBD-E310-4205-8EBD-7F19733ADDE8@microsoft.com...
> Hello!
>
> We are running SQL Server 2000 (8.00.878) on Windows 2000.
>
> I am having a bit of trouble with a stored procedure. It runs fine for a
while, then slows down a lot over time. If recreated, it runs fine again for
a while, after which it slows down again.
>
> I have tried a lot to find out where the problem lies such as:
> - Checking the query plan
> - Creating the proc with recompile
> - Running DBCC FREEPROCCACHE etc.
>
> Restarting the server also seems to do the trick.
>
> The proc is not very advanced, it involves creating a temp table, filling
it with data, a little manipulation and then returning the entire temp
table. A couple of UDF's are used when filling the temp table.
>
> Any ideas as to what can cause thie behaviour?
>
> Regards,
> Kalle Dahlberg

Relevant Pages

  • Re: Temp Table and SP Advise
    ... > I have a little scenario here, i am need of inserting some rows into a ... > temp table which will be returned by a procedure... ... > I am planning to make a proc A and a temp table in proc A. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: the necessity of drop temp table code
    ... But what did you see after you were done with the proc? ... If we can't trust SQL Server to do the right thing here, ... on the safe side. ... Because I have been bitten by at least one case where deferred temp table ...
    (microsoft.public.sqlserver.programming)
  • Performance issues with stored procedure
    ... If recreated, it runs fine again for a while, after which it slows down again. ... The proc is not very advanced, it involves creating a temp table, filling it with data, a little manipulation and then returning the entire temp table. ...
    (microsoft.public.sqlserver.server)
  • Re: Why Am I Getting This Error
    ... of your proc and then simply do an INSERT SELECT to populate it. ... Columnist, SQL Server Professional ... I have an SP in which I want to fill a temp table with different values ...
    (microsoft.public.sqlserver.programming)
  • Re: T-SQL is not quite there yet
    ... no, the choice is between INSERT-EXEC ... results ought to be called functions, not "stored procedures". ... SQL Server is a server- ... gains only by putting keys on temp tables that had none. ...
    (microsoft.public.sqlserver.programming)