Stored procedure creation terribly slow until reboot

5 Jan 2006 11:17 PM

Steve H

Greetings! We were running into an issue with creating a stored procedure
that was around 1400 lines long (calling other procs in the process). The
stored procedure creation would take over an hour to complete - note that
this is just the Create proc statement. The database had many tables, some
with quite a lot of data in them. When the database was restored to most
other servers, the create proc statement would complete in a few seconds. We
rebooted the database server that this was occuring on and afterwards, the
procedure would be created very quickly as well. There is still one other
server where we will be creating this procedure on and it still takes over an
hour to create. We were trying to triage why this is happening prior to
reboot.
Has anyone ever experienced this and if so, was there a suspected cause?

Thanks in advance!

5 Jan 2006 11:35 PM

Trey Walpole

What else is hitting the server when this is happening?
Creation would be slow if there are schema locks.

[assuming this is sql2000]
While a slow create is in process, check the current locks
Use the sp_lock system proc in query analyzer - you may not be able to
get in with enterprise manager.

look for locks with a mode that start with "Sch"

in BOL, look up "Understanding Locking in SQL Server" for a start for
more details

Steve H wrote:
Show quote

> Greetings! We were running into an issue with creating a stored procedure
> that was around 1400 lines long (calling other procs in the process). The
> stored procedure creation would take over an hour to complete - note that
> this is just the Create proc statement. The database had many tables, some
> with quite a lot of data in them. When the database was restored to most
> other servers, the create proc statement would complete in a few seconds. We
> rebooted the database server that this was occuring on and afterwards, the
> procedure would be created very quickly as well. There is still one other
> server where we will be creating this procedure on and it still takes over an
> hour to create. We were trying to triage why this is happening prior to
> reboot.
> Has anyone ever experienced this and if so, was there a suspected cause?
>
> Thanks in advance!

6 Jan 2006 2:22 PM

Steve H

Thanks for the response.

Yes, it is SQL 2000 (sorry). Really nothing else is hitting that particular
database. I was looking over locks and can't recall the specifics of the
locks, but the vast majority of the locks that were there were from the spid
creating the stored procedure. There were no blocked processes from the
sp_who2 output. At the server level, there was very little load on the box.

Thanks for your help.

Show quote

"Trey Walpole" wrote:

> What else is hitting the server when this is happening?
> Creation would be slow if there are schema locks.
>
> [assuming this is sql2000]
> While a slow create is in process, check the current locks
> Use the sp_lock system proc in query analyzer - you may not be able to
> get in with enterprise manager.
>
> look for locks with a mode that start with "Sch"
>
> in BOL, look up "Understanding Locking in SQL Server" for a start for
> more details
>
>
>
>
> Steve H wrote:
> > Greetings! We were running into an issue with creating a stored procedure
> > that was around 1400 lines long (calling other procs in the process). The
> > stored procedure creation would take over an hour to complete - note that
> > this is just the Create proc statement. The database had many tables, some
> > with quite a lot of data in them. When the database was restored to most
> > other servers, the create proc statement would complete in a few seconds. We
> > rebooted the database server that this was occuring on and afterwards, the
> > procedure would be created very quickly as well. There is still one other
> > server where we will be creating this procedure on and it still takes over an
> > hour to create. We were trying to triage why this is happening prior to
> > reboot.
> > Has anyone ever experienced this and if so, was there a suspected cause?
> >
> > Thanks in advance!
>