Problem on scripting stored procedures

I have tried to script all of stored procedures in one database on SQL server
2005 then it seems like get stacked. To script one stored procedure has no
problem however when try to script all stored procedures SSMS never respond.
I have never experience this problem on SQL server 2000.
Any help?
M. Matsuda (MMatsuda@.discussions.microsoft.com) writes:
> I have tried to script all of stored procedures in one database on SQL
> server 2005 then it seems like get stacked. To script one stored
> procedure has no problem however when try to script all stored
> procedures SSMS never respond.
> I have never experience this problem on SQL server 2000.
> Any help?
How many procedures are there in the database? There were performance
issues during the beta, but it appears to behave decently now.
One possibility is blocking, if someone has submitted:
BEGIN TRANSACTION
go
CREATE PROCEDURE ...
and never committed the transaction. You can use sp_who to determine if
you have any blocking in the database. If there is a non-zero value in
the Blk column, in means that the spid in Blk blocks the spid on that
line.
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
|||There are 193 stored procedures and same process on SQL 2000 ends less than
30 second. There is no other user using SQL 2005 except myself. (Because this
SQL 2005 is used as application development purpose.)
When script one stored procedure SSMS promptly responded and showed dialog
where to save however when select all stored procedures it did not show the
dialog more than 10 minits.
Performance monitor showed 100% processor time during being stalled.
"Erland Sommarskog" wrote:

> M. Matsuda (MMatsuda@.discussions.microsoft.com) writes:
> How many procedures are there in the database? There were performance
> issues during the beta, but it appears to behave decently now.
> One possibility is blocking, if someone has submitted:
> BEGIN TRANSACTION
> go
> CREATE PROCEDURE ...
> and never committed the transaction. You can use sp_who to determine if
> you have any blocking in the database. If there is a non-zero value in
> the Blk column, in means that the spid in Blk blocks the spid on that
> line.
>
> --
> 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
>
|||M. Matsuda (MMatsuda@.discussions.microsoft.com) writes:
> There are 193 stored procedures
That's not an extraordiary lot.

> There is no other user using SQL 2005 except myself.
That does not preclude blocking, if that is what you were thinking.

> When script one stored procedure SSMS promptly responded and showed
> dialog where to save however when select all stored procedures it did
> not show the dialog more than 10 minits.
> Performance monitor showed 100% processor time during being stalled.
Hm, is Mgmt Studio and SQL Server on the same machine? How much memory
is there in the box? How much memory does SQL Server actually have?
What I have noticed with SQL 2005 is that if it falls down 30-35 MB in
memory, the simplest queries can take over 10 seconds.
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
|||SQL 2005 installed in Netfinity 5000 with 896MB RAM (P3 500M HZ) + Windows
2003 Server. SSMS and SQL 2005 reside on same machine.
Before upgrade to SQL 2005, SQL 2000 run on same machine did not have any
problem. Even SQL 2000 + Windows XP installed on notebook (Think pad 512MB
RAM) + Visual Stuido 2005 run on same time does not have any problem to
perform this process.
"Erland Sommarskog" wrote:

> M. Matsuda (MMatsuda@.discussions.microsoft.com) writes:
> That's not an extraordiary lot.
>
> That does not preclude blocking, if that is what you were thinking.
>
> Hm, is Mgmt Studio and SQL Server on the same machine? How much memory
> is there in the box? How much memory does SQL Server actually have?
> What I have noticed with SQL 2005 is that if it falls down 30-35 MB in
> memory, the simplest queries can take over 10 seconds.
> --
> 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
>
|||I don't know how you can get 896MB of memory, I suspect it is really 512MB.
But in any case you are not meeting the minimum hardware requirements on CPU
for SQL2005 let alone the recommended. As such I would expect it to be
somewhat slow.
http://www.microsoft.com/sql/editions/developer/sysreqs.mspx
Andrew J. Kelly SQL MVP
"M. Matsuda" <MMatsuda@.discussions.microsoft.com> wrote in message
news:A207A819-2286-4552-9EE4-3F4918687303@.microsoft.com...[vbcol=seagreen]
> SQL 2005 installed in Netfinity 5000 with 896MB RAM (P3 500M HZ) +
> Windows
> 2003 Server. SSMS and SQL 2005 reside on same machine.
> Before upgrade to SQL 2005, SQL 2000 run on same machine did not have any
> problem. Even SQL 2000 + Windows XP installed on notebook (Think pad 512MB
> RAM) + Visual Stuido 2005 run on same time does not have any problem to
> perform this process.
> "Erland Sommarskog" wrote:
|||Sounds like you are blaming that hardware has not meet minimum requirement of
SQL 2005. However this system has PIII 600MHZ 896MB RAM (Windows 2003
recognize 896MB). It meets minimum requirement of 32bit SQL 2005.
Would you tell me what a expected time to finish this process on miminum
hardware requirement?
Thanks in advance
"Andrew J. Kelly" wrote:

> I don't know how you can get 896MB of memory, I suspect it is really 512MB.
> But in any case you are not meeting the minimum hardware requirements on CPU
> for SQL2005 let alone the recommended. As such I would expect it to be
> somewhat slow.
> http://www.microsoft.com/sql/editions/developer/sysreqs.mspx
> --
> Andrew J. Kelly SQL MVP
> "M. Matsuda" <MMatsuda@.discussions.microsoft.com> wrote in message
> news:A207A819-2286-4552-9EE4-3F4918687303@.microsoft.com...
>
>
|||M. Matsuda (MMatsuda@.discussions.microsoft.com) writes:
> SQL 2005 installed in Netfinity 5000 with 896MB RAM (P3 500M HZ) +
> Windows 2003 Server. SSMS and SQL 2005 reside on same machine. Before
> upgrade to SQL 2005, SQL 2000 run on same machine did not have any
> problem. Even SQL 2000 + Windows XP installed on notebook (Think pad
> 512MB RAM) + Visual Stuido 2005 run on same time does not have any
> problem to perform this process.
It is not very impressing hardware. And, yes, this problem with SQL Server
being very slow when it's low on memory is much more apparent with SQL 2005
than SQL 2000.
Did you use Task Manager to see how much memory SQL Server has when
performing the scripting operation?
If you have other processes running, for instance a web browser, try closing
these and see if it helps.
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
|||Our tight budget won't allow me to upgrade fancy hardware as you may have and
still need to deal with limited resources and so do my small business clients
too.
I had a chance to test same process on IBM XS235 Xeon 3.06GHZ with 1.5GB RAM
relatively enough spec for SQL 2005 however it still took 7-8 minutes to get
response from SSMS. If this is ideal time to finish this process, I may need
to stick SQL 2000 for a while and recommend stay with SQL 2000 to my clients
for time being.
Thank you for your assistance.
"Erland Sommarskog" wrote:

> M. Matsuda (MMatsuda@.discussions.microsoft.com) writes:
> It is not very impressing hardware. And, yes, this problem with SQL Server
> being very slow when it's low on memory is much more apparent with SQL 2005
> than SQL 2000.
> Did you use Task Manager to see how much memory SQL Server has when
> performing the scripting operation?
> If you have other processes running, for instance a web browser, try closing
> these and see if it helps.
> --
> 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
>
|||M. Matsuda (MMatsuda@.discussions.microsoft.com) writes:
> Our tight budget won't allow me to upgrade fancy hardware as you may
> have and still need to deal with limited resources and so do my small
> business clients too.
Fancy and fancy. A 500 Mhz machine a certainly to reqard as an antiquity
today.

> I had a chance to test same process on IBM XS235 Xeon 3.06GHZ with 1.5GB
> RAM relatively enough spec for SQL 2005 however it still took 7-8
> minutes to get response from SSMS. If this is ideal time to finish this
> process, I may need to stick SQL 2000 for a while and recommend stay
> with SQL 2000 to my clients for time being.
I tried the exercise at home on a Pentium4 2.8 GHz with hyperthreading
and 1.5 GB of memory. I selected 275 procedures, and took about the time you
mentioned to script them. I noticed that SQL Server was eating a lot
of memory, around 440 MB, as well as CPU. For some reason that I don't
understand, Windows Explorer was also consuming CPU.
In this experiment, I selected the procedures from the Summary view.
The next thing I did was to use the scripting wizard. Right-click the
database node in Object Explorer and select Tasks->Generate Scripts.
It took about a minute for me to make the selection. (This database
has over 4000 thousand procedures, so I could not use Select all.)
But once started, the wizard completed within a minute. It may be
because SQL Server now had all it neded in memory. I need to play
with this a little more.
I agree with you that the performance is not satisfactory.
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