Stored Procedures - extremely slow

Stored Procedures - extremely slow

Post by Mark » Thu, 14 Aug 2003 19:18:17



I have a stored proc that calls another stored procedure that calls a 3rd SP
which all of a sudden takes 5x as long to complete.  I believe the cause to be
the underlying tables so I dropped the tables, keys, and indexes.  I also ran
sp_recompile, DBCC DBREINDEX, and UPDATE STATISTICS.  The same stored procedures
run on another server does not have this problem but the same stored procs on a
3rd server, that subscribes to the problem server, is also now just as slow.
Does anyone have suggestions as to what I should try next?

Thanks

Mark

 
 
 

Stored Procedures - extremely slow

Post by Erland Sommarsko » Thu, 14 Aug 2003 22:28:34



> I have a stored proc that calls another stored procedure that calls a
> 3rd SP which all of a sudden takes 5x as long to complete.  I believe
> the cause to be the underlying tables so I dropped the tables, keys, and
> indexes.  I also ran sp_recompile, DBCC DBREINDEX, and UPDATE
> STATISTICS.  The same stored procedures run on another server does not
> have this problem but the same stored procs on a 3rd server, that
> subscribes to the problem server, is also now just as slow. Does anyone
> have suggestions as to what I should try next?

There are plentyful of possible explanations, and with your vague
description it is very difficult to say.

First thing to check is run this:

   SELECT objectproperty(object_id('proc_name'), 'IsQuotedIdentOn'),
          objectproperty(object_id('proc_name'), 'IsAnsiNullsOn')

Both these should return 1. If they do not, reload the stored procedure
and make sure that you have SET QUOTED_IDENTIFIER ON and SET ANSI_NULLS ON
when you do this. This may have the effect of a miracle - or no effect at
all. This test covers the case that there is a indexed view or an index
on a computed column involved. These two features requires these two
settings to be on - together with four more settings. What is special
with these two settings is that they are saved with the stored procedure.

A more trivial explanation is that the stored procedure includes a
query which balances between a good and a bad query plan. SQL Server
uses a cost-based optimizer which uses statistics about the data.
Most of the time it does a good job, but every now and they it grabs
the wrong plan.

Use the Profiler to narrow down exactly which statement is cause the
problem. You can also use the Profiler to catch the query plan.

When you run a Profiler trace, include the SP:Recomile event. If you
have plenty of these, it may be the recompiles that kills you.

--

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

 
 
 

Stored Procedures - extremely slow

Post by Mark » Fri, 15 Aug 2003 18:30:31


Morning,

I do not have indexed views or an index on a computed column in this situation.
  I had quoted identifiers and ansi nulls off and tried them on.  I have already
run a trace and there was only one recompile but not on the stored procedure
that seems to be the problem.  Parameters are passed to a cursor in a stored
proc that seems to be the cause.  The stored procedure now takes 10x as long as
it took before.  Most of the events returned are SP:statementcompleted.
I also dropped and recreated the tables involved and it got worse.
Any other suggestions?

Thanks!



>>I have a stored proc that calls another stored procedure that calls a
>>3rd SP which all of a sudden takes 5x as long to complete.  I believe
>>the cause to be the underlying tables so I dropped the tables, keys, and
>>indexes.  I also ran sp_recompile, DBCC DBREINDEX, and UPDATE
>>STATISTICS.  The same stored procedures run on another server does not
>>have this problem but the same stored procs on a 3rd server, that
>>subscribes to the problem server, is also now just as slow. Does anyone
>>have suggestions as to what I should try next?

> There are plentyful of possible explanations, and with your vague
> description it is very difficult to say.

> First thing to check is run this:

>    SELECT objectproperty(object_id('proc_name'), 'IsQuotedIdentOn'),
>           objectproperty(object_id('proc_name'), 'IsAnsiNullsOn')

> Both these should return 1. If they do not, reload the stored procedure
> and make sure that you have SET QUOTED_IDENTIFIER ON and SET ANSI_NULLS ON
> when you do this. This may have the effect of a miracle - or no effect at
> all. This test covers the case that there is a indexed view or an index
> on a computed column involved. These two features requires these two
> settings to be on - together with four more settings. What is special
> with these two settings is that they are saved with the stored procedure.

> A more trivial explanation is that the stored procedure includes a
> query which balances between a good and a bad query plan. SQL Server
> uses a cost-based optimizer which uses statistics about the data.
> Most of the time it does a good job, but every now and they it grabs
> the wrong plan.

> Use the Profiler to narrow down exactly which statement is cause the
> problem. You can also use the Profiler to catch the query plan.

> When you run a Profiler trace, include the SP:Recomile event. If you
> have plenty of these, it may be the recompiles that kills you.

 
 
 

Stored Procedures - extremely slow

Post by Erland Sommarsko » Fri, 15 Aug 2003 21:46:32



> I do not have indexed views or an index on a computed column in this
> situation.
>   I had quoted identifiers and ansi nulls off and tried them on.  I have
> already run a trace and there was only one recompile but not on the
> stored procedure that seems to be the problem.  Parameters are passed to
> a cursor in a stored proc that seems to be the cause.  The stored
> procedure now takes 10x as long as it took before.  Most of the events
> returned are SP:statementcompleted. I also dropped and recreated the
> tables involved and it got worse.

You said cursor? Then try to rewrite the procedure into a set-based
operation. This may cause the procedure to run 10 times faster than
before.

Another suggestion that calls for less work, is include the keyword
INSENSITIVE before CURSOR. The default keyset-driven cursors can sometimes
lead to terrible query plans.

I'm afraid that with the miniscule amount of information that you have
disclosed, the chances for precise help are not that bright.

--

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

 
 
 

Stored Procedures - extremely slow

Post by Mark » Fri, 15 Aug 2003 22:56:34


I can try to rewrite the SP into a set-based operation but that doesn't explain
why, all of a sudden with no apparent changes, the SP is taking so much longer.
  I have included the SP to help provide more information.

CREATE PROCEDURE ExplodeBOM2

--
-- This procedure recursively explodes and inserts data from
-- the bill of materials into the 'IndentedBOM' table.  It does
-- so using a depth-first traversal of the bill of materials
-- data, inserting each item into the 'IndentedBOM' table as
-- it is read.
--








AS












    -- Cursor containing all bills at this particular level (top bill
    -- number is level 0) to be traversed for this particular bill #
    -- and revision.  Each recursion generates a new 'BOMLevel' cursor
    -- for inserting exploded bill of materials records into 'IndentedBOM'
    DECLARE BOMLevel CURSOR FOR
     SELECT a.ItemNo, a.ItemRev, a.Qty, a.FindNo,
            b.ItemDesc, b.StdUM, b.StdCost, b.LastCost
     FROM   stageBillDetail a JOIN stageItemMaster b ON a.ItemNo = b.ItemNo


     ORDER BY a.FindNo

BEGIN

    -- indent the level designator by preceding it with n hyphens,



    -- begin traversing cursor and inserting data into 'IndentedBOM'
    OPEN BOMLevel




       -- insert this record into 'IndentedBOM' BEFORE checking
       -- for deeper levels within this bill (depth-first traversal)
       INSERT INTO stageIndentedBOM



       -- check to see if deeper levels within this bill exist
       -- if so, recurse on this procedure using the item number and
       -- item revision as the bill number & bill revision



              FROM  stageBillMaster

          END /* IF */

          DEALLOCATE BOMLevel


          -- sequence number defines the sequence of all items in the depth-first
          -- traversal (exploded bom) for a given top-level bill.  NewSeqNo,
          -- similar to SavedFindNo, keeps the sequence

          -- recursive call (depth-first traversal)



          DECLARE BOMLevel CURSOR FOR
           SELECT a.ItemNo, a.ItemRev, a.Qty, a.FindNo,
                  b.ItemDesc, b.StdUM, b.StdCost, b.LastCost
           FROM   stageBillDetail a JOIN stageItemMaster b ON a.ItemNo = b.ItemNo



           ORDER BY a.FindNo

          OPEN BOMLevel

          -- an asterisk (*) should be substituted with the latest revision


              FROM  stageBillMaster


           END /*IF*/

       END /* IF */



    END /* WHILE */

    -- this sequence number is OUTPUT mode, so it will be returned to the calling
    -- procedure, where it will be used to pick up where it left off

    DEALLOCATE BOMLevel

END



>>I do not have indexed views or an index on a computed column in this
>>situation.
>>  I had quoted identifiers and ansi nulls off and tried them on.  I have
>>already run a trace and there was only one recompile but not on the
>>stored procedure that seems to be the problem.  Parameters are passed to
>>a cursor in a stored proc that seems to be the cause.  The stored
>>procedure now takes 10x as long as it took before.  Most of the events
>>returned are SP:statementcompleted. I also dropped and recreated the
>>tables involved and it got worse.

> You said cursor? Then try to rewrite the procedure into a set-based
> operation. This may cause the procedure to run 10 times faster than
> before.

> Another suggestion that calls for less work, is include the keyword
> INSENSITIVE before CURSOR. The default keyset-driven cursors can sometimes
> lead to terrible query plans.

> I'm afraid that with the miniscule amount of information that you have
> disclosed, the chances for precise help are not that bright.

 
 
 

Stored Procedures - extremely slow

Post by Erland Sommarsko » Sun, 17 Aug 2003 00:08:36



>I can try to rewrite the SP into a set-based operation but that doesn't
>explain why, all of a sudden with no apparent changes, the SP is taking so
>much longer.

It's not that I want to make snide remarks, but the question verges
of the kind "why is the sky blue"? A cost-based optimizer makes
it estimates from statistics, and sometimes it goes wrong. And this
can happen from one day or another because you pass some threshold
in the volumes.

Of course, someone might also have dropped an index.

Anyway, since there is a hierarchy involved, one single INSERT
statement is not easilly achieved. (But Joe Celko might discuss
possible strategies in his books.) Redeclaring a cursor again
and again is definitely not going help you to break the speed
limit.

If you change the cursor declaration to

    DECLARE BOMcursor CURSOR LOCAL STATIC

you don't have to decallocate before the recursive call, and you
don't have to save FindNo when you comes back. Already this is likely
to do wonders.

Here is a outline which iterates as long as there are levels
in the hierarchy.

CREATE PROCEDURE ExplodeBOM2

CREATE TABLE #temp (ident int IDENTITY,
                    -- columns as target table)

CREATE TABLE #bills (itemno  varchar(20) NOT NULL,
                     itemrev char(3)     NOT NULL)

INSERT #bills(itemno, itemrev)


WHILE EXISTS (SELECT * FROM #bills)
BEGIN
    -- indent the level designator by preceding it with n hyphens,



    DELETE #tmp

    INSERT INTO #tmp  -- You should add columns list here!

             a.FindNo, b.ItemDesc, b.StdUM, b.StdCost, b.LastCost,

     FROM   stageBillDetail a
     JOIN   stageItemMaster b ON a.ItemNo = b.ItemNo
     JOIN   #bills c ON a.BillNo  = c.itemno AND
                        a.BillRev = c.itemrev
     ORDER  BY a.FindNo
     OPTION (MAXDOP = 1)

    INSERT INTO stageIndendedBOM -- add column list!

               t.ItemRev, t.ItemDesc, t.StdUM, t.Qty, t.StdCost, t.LastCost
        FROM   #tmp

    DELETE #bills

    INSERT #bills (itemno, itemrev)
       SELECT ItemNo, MAX(ItemRev)
       FROM   #tmp t
       JOIN   stageBillMaster s ON s.ItemNo = s.BillNo
       WHERE  t.ItemRev LIKE '*%'
END

--

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

 
 
 

1. Extremely poor stored procedure performance after upgrade to 7.0

We just upgraded from 6.5 to 7.0 and are receiving absolutely terrible
performance from a series of stored procedures provided by a software
application. We've gone from about 45 seconds to over 8 minutes. The
procedures are pretty ugly, and we could look at re-writing them if
absolutely necessary, but I was wondering if there were any specific items
to look for that perform tremendously worse under 7.0. Applying SP2 made no
difference.

Thanks,

DvS

2. Job Posting

3. ODBC extremely slow

4. OLAP/BI Tool Evaluation Matrix

5. SAP/BW extremely slow

6. Column Level Permissions

7. extremely slow production load, pls help!!

8. connecting to MS Access?

9. SQL Server 2000 jdbc driver is extremely slow !!!!

10. Extremely slow Servers.

11. MMC Performance Extremely Slow

12. SAP/BW extremely slow

13. Pivot Table Extremely slow