Painfully slow scripting

Using SqlExpress 2005, I have a database with over 1000 stored procedures
for a project still in development. From time-to-time I need to script out
the stored procedures for backup or transfer to another system. For the past
year, this has been a relatively quick scripting process, but lately it has
become excrutiatingly slow. There are no issues with the OS or the hardware
and all other software runs normally. Firewall is on, virus protection and
scan is up-to-date and no goofy pop-up issues nor any apparent malicious
software. Any thoughts on this issue would be appreciated.I think that Erland (I believe it was) had some improvements by setting forced parameterization (see
ALTER DATABASE).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Earl" <earl@.nospam.com> wrote in message news:uZLfdu5%23HHA.4200@.TK2MSFTNGP04.phx.gbl...
> Using SqlExpress 2005, I have a database with over 1000 stored procedures for a project still in
> development. From time-to-time I need to script out the stored procedures for backup or transfer
> to another system. For the past year, this has been a relatively quick scripting process, but
> lately it has become excrutiatingly slow. There are no issues with the OS or the hardware and all
> other software runs normally. Firewall is on, virus protection and scan is up-to-date and no goofy
> pop-up issues nor any apparent malicious software. Any thoughts on this issue would be
> appreciated.
>|||Earl (earl@.nospam.com) writes:
> Using SqlExpress 2005, I have a database with over 1000 stored
> procedures for a project still in development. From time-to-time I need
> to script out the stored procedures for backup or transfer to another
> system. For the past year, this has been a relatively quick scripting
> process, but lately it has become excrutiatingly slow. There are no
> issues with the OS or the hardware and all other software runs normally.
> Firewall is on, virus protection and scan is up-to-date and no goofy
> pop-up issues nor any apparent malicious software. Any thoughts on this
> issue would be appreciated.
Yes, as Tibor set, you can gain speed by saying
ALTER DATABASE db SET PARAMETERIZATION FORCED
this setting causes SQL Server to parameterize every query sent to it.
That is, a query like:
SELECT * FROM Orders WHERE OrderStatus = 'Bad' AND OrderDate < '20070101'
will be put into the cache as:
SELECT * FROM Orders WHERE OrderStatus = @.1 AND OrderDate < @.2
This measure can be a big speed winner with an application that generates
SQL with inlined parameter values, despite this being bad practice. And Mmgt
Studio is such an application. It's certainly embarrassing that Microsoft
themselves cannot adhere to good practice.
The reason this is a winner is that when the same query is repeated all
over again, just with different parameter values, SQL Server does not have
to spend time on compiling the queries.
But you should not have forced parameterization with a well-written
application that uses parameterised queries or stored procedures, since
SQL my parameterise where it shouldn't.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx