Re: Scripting Performance after SP2 applied?

Hi Erland,

I am generating the scripts in question through the right-click menu -> All
tasks -> generate scripts.

I am interested to hear that you got good performance... this helps me put
blame my machine and/or configuration. If I can find some spare time, I might
fire up the profiler and see if there's any clues in what I capture there.

I agree that there is a lot of time spent setting up the scripting in the
first place, and that one second per table generation is way too slow. Like I
said before, prior to installing SP2, I was getting multiple tables generated
per second. The setup time was probably about the same, but the individual
generation was very speedy by comparison to SP2.

It's a bit disturbing as I'm only trying to generate DDL for 35 tables, with
barely any data in them at all. The database is small by anyone's standards.

As far as memory configuration, I have always set my max server memory set
to 384 MB - this is a small database on my machine as it's only for testing,
and with only 1GB of RAM, I don't want SQL to take more than 384. In fact,
this is making me think I should bring that number down further...

One other configuration bit of information: I have SQL 2000 installed as the
default instance, set to start manually, and SQL 2005 is another instance
that auto starts.

I may just nuke the entire install later this week (I have that luxury after
all) and try reinstalling all of the bits, just in case something else has
gotten gummed up by the SP2 upgrade.

Thanks for your input.

"Erland Sommarskog" wrote:

Chuck Urwiler (ChuckUrwiler@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
But it does seem slower now in SP2 with forced parameterization enabled
(an option I had no idea existed until your message was relayed to me by
Aaron) than under SP1, but that's somewhat subjective. As I recall,
generating a script under SP1 was very, very quick for me - the 35
tables would generate in a few seconds. With simple parameterization, it
was 15-20 seconds per table; now with forced parameterization, it's
about a second per table.

How do you script? With the Scripting wizard, do you just right-click in
the Summary window and say Script To?

I did a test on my box (Dev Edition, SP2 on WinXP) with a database with
1000 stored procedures and 200 tables. I first scripted with simple
parameterisation, using the scripting wizard. This took 2:28 minutes.
I then restarted SQL Server, and set the database to forced
parameterisation. This cut the time to 2:05.

But a lot of time is lost here when Mgmt Studio first determines which
object to script, and then goes on to load them in the Progress window. I
did not trace this, but I suspect that this time lost in setting up
collections, and not in running queries. Once it started scripting,
it was very swift with forced paramerization. (Or course two minutes to
set up 1200 objects is far too long time.)

A second per table is far too long time in my taste. That could indicate
that there is a problem with statistics/fragmentation on system tables.
But if it takes 15-20 seconds to compile the queries, the problem is
probably elsewhere.

How have you configured memory on your server? By default, SQL Server
ships with "max server memory" set to virtually unlimited. This is less
suitable on a developer box, where there are lot of other programs
running. Try to set it to 100-150 MB depending on much you have available.
One thing I have noticed is that when SQL 2005 is not able to get all
memory is configured for, it can be very slow.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

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

.

Relevant Pages

  • Re: How to Create Local Temporary Table
    ... I have worked with both Oracle and SQL Server for some time and have grown to appreciate how much more mature Oracle is than SQL Server. ... When most think of scripting in Windows, they think of DOS command line scripting. ...
    (comp.databases.oracle.server)
  • Re: Sql Management Studio - Script Db creates objects out of ourder
    ... Scripting depends on the dependency information in the database. ... flexible nature of SQL Server, ... and the a stored procedure kalle_sp that refers ... to nissetbl, SQL Server with store information about this. ...
    (microsoft.public.sqlserver.tools)
  • Re: SMSS SP2 Script Database objects error
    ... Determining objects in database 'NameHost' that will be scripted. ... query using ANSI outer join operators (LEFT OUTER JOIN, ... In the future versions of SQL Server, ... scripting, I find it difficult to believe that it would be that bad. ...
    (microsoft.public.sqlserver.tools)
  • Re: Any good, free tools for writing simple stored procedures?
    ... I use SQL Server Query Analyzer most of the time. ... but it does have a debugger, object browser, scripting, graphical query ... from the object browser into the editing window. ...
    (microsoft.public.sqlserver)
  • Re: Problem on scripting stored procedures
    ... Server's memory allocation. ... This means that each query gets compiled by SQL Server, ... I did a test with scripting 200 stored procedures in a database. ... the conditions of simple parameterisation. ...
    (microsoft.public.sqlserver.tools)
Loading