|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Scripting Performance after SP2 applied?I have applied SP2 to my development machine, and am at build 3054. Prior to SP2, I was able to generate a script rather quickly for my databases. However, since SP2 was applied, it is excruciatingly slow, something on the order of 10-15 seconds per table. More specifically: 1) Right click desired database in Object Explorer 2) Select Tasks -> Generate Scripts... 3) Ensure database is selected on first page of wizard, click next 4) Leave script options at their defaults, click next 5) Select only the Table object types, click next 6) Select all tables (I only have 35), click next 7) Script to New Query window, click next 8) Click Finish. At this point, the list of tables is determined, and then each table is scripted. The amount of time between each table is around 15 seconds or so. I used to be able to have the entire set of 35 tables scripted in 15 seconds. Is this a known issue? I found nothing with a search but of course I might not have typed just the right keywords. Thanks. -Chuck I'm seeing about 2-3 seconds per table (for the database I tried, I have 145
tables). What are your options in Tools/Options/Scripting? Things set to TRUE that I think could have a drastic impact on script generation times: - Generate script for dependent objects (depending on the complexity of your schema) - Include IF NOT EXISTS clause - Script extended properties (depending on whether you have a lot) - Script permissions (again, depending on the complexity of your permissions) - Convert user-defined data types to base types (if you have any UDTs) - Any of the SCRIPT options at the bottom that aren't necessary Show quote "Chuck Urwiler" <ChuckUrwi***@discussions.microsoft.com> wrote in message news:089D6BC6-5A0B-4440-BF9D-E421349283D3@microsoft.com... > Hi there, > > I have applied SP2 to my development machine, and am at build 3054. > > Prior to SP2, I was able to generate a script rather quickly for my > databases. However, since SP2 was applied, it is excruciatingly slow, > something on the order of 10-15 seconds per table. > > More specifically: > 1) Right click desired database in Object Explorer > 2) Select Tasks -> Generate Scripts... > 3) Ensure database is selected on first page of wizard, click next > 4) Leave script options at their defaults, click next > 5) Select only the Table object types, click next > 6) Select all tables (I only have 35), click next > 7) Script to New Query window, click next > 8) Click Finish. > > At this point, the list of tables is determined, and then each table is > scripted. The amount of time between each table is around 15 seconds or > so. I > used to be able to have the entire set of 35 tables scripted in 15 > seconds. > > Is this a known issue? I found nothing with a search but of course I might > not have typed just the right keywords. > > Thanks. > -Chuck > Hi Aaron,
I failed to mention that I had already tried changing some of the options, but with no change in the performance. Gave it another shot anyway, and here's what's set for me: 1) Dependent objects set to false 2) If not exists set to false 3) Extended properties set to false (true was my default, but no change) 4) Permissions set to false 5) UDDTs to base types set to false 6) Table/view options only to script checks, FKs, PKs, and unique keys. Any chance yours was set the same way? My concern is that for the same set of options, the same schema, etc. the scripting performance went downhill by a very significant amount. It used to be very quick (the entire script generated within seconds) with everything else being the same (i.e., SP2 being the only difference). Now it's several minutes just to get some DDL out of my database. Since I can easily afford to do so, I'm considering uninstalling SQL altogether and reinstalling - perhaps something got gummed up with the SP2 update. But I wanted to explore any possible alternatives before going drastic. -Chuck "Aaron Bertrand [SQL Server MVP]" wrote: <snip>> I'm seeing about 2-3 seconds per table (for the database I tried, I have 145 > tables). > Hi Chuck,
Erland gave me this piece of advice that may help you: >>> I haven't tested scripting with SP2 as far as I can recall, but a tipis that setting the database in forced parameterisation, can speed up scripting considerably, since MgmtStudio uses inlined parameter values. See also http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=247968 Show quote >>> Aaron,
Thanks for that tip, the forced parameterization did make a difference. It's still troubling to me that it was *so* much faster prior to SP2. -Chuck Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > Hi Chuck, > > Erland gave me this piece of advice that may help you: > > >>> > I haven't tested scripting with SP2 as far as I can recall, but a tip > is that setting the database in forced parameterisation, can speed up > scripting considerably, since MgmtStudio uses inlined parameter values. > > See also > http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=247968 > >>> > > > Chuck Urwiler (ChuckUrwi***@discussions.microsoft.com) writes:
> Thanks for that tip, the forced parameterization did make a difference. Is it really slower in SP2 with forced parameterisation, than without in > > It's still troubling to me that it was *so* much faster prior to SP2. SP1? -- Erland Sommarskog, SQL Server MVP, esq***@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 >> It's still troubling to me that it was *so* much faster prior to SP2. I think he meant the delta before switching to forced parameterization, not > > Is it really slower in SP2 with forced parameterisation, than without in > SP1? after. But maybe it's still slower (just not as bad)? Erland,
Well, that's going to be tough for me to say with certainty, since I no longer have SP1 installed :) 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. So for me, it would seem SP2 did something on my machine here that has really affected scripting performance. And for what it's worth, this is developer edition under XP SP2. Show quote "Erland Sommarskog" wrote: > Chuck Urwiler (ChuckUrwi***@discussions.microsoft.com) writes: > > Thanks for that tip, the forced parameterization did make a difference. > > > > It's still troubling to me that it was *so* much faster prior to SP2. > > Is it really slower in SP2 with forced parameterisation, than without in > SP1? > > > -- > Erland Sommarskog, SQL Server MVP, esq***@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 > Chuck Urwiler (ChuckUrwi***@discussions.microsoft.com) writes:
> But it does seem slower now in SP2 with forced parameterization enabled How do you script? With the Scripting wizard, do you just right-click in> (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. 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, esq***@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 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. Show quote "Erland Sommarskog" wrote: > Chuck Urwiler (ChuckUrwi***@discussions.microsoft.com) 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, esq***@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 > Chuck Urwiler (ChuckUrwi***@discussions.microsoft.com) writes:
> As far as memory configuration, I have always set my max server memory Assuming that this machine has stuff that you typically find a development> 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. box like a web browser running and not forgetting the SQL Server management tools, I think you should reduce you server memory more. And check whether you have other stuff eating memory as well. (Web browsers are often hungry.) Seeing your local HW vendor for another 500 MB of memory if the box has room for it, may also be a worthwhile investment. -- Erland Sommarskog, SQL Server MVP, esq***@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'd consider that M$ standard bloatware!
Doesn't _EVERYTHING_ get slower? so they can jam a new version down our throats? I call for Ballmer to be fired and Ralph Nader to take over-- MS doesn't give a crap about their customers and we're not asking for SaaS we're asking for BUG FREE SOFTARE Show quote "Chuck Urwiler" <ChuckUrwi***@discussions.microsoft.com> wrote in message news:089D6BC6-5A0B-4440-BF9D-E421349283D3@microsoft.com... > Hi there, > > I have applied SP2 to my development machine, and am at build 3054. > > Prior to SP2, I was able to generate a script rather quickly for my > databases. However, since SP2 was applied, it is excruciatingly slow, > something on the order of 10-15 seconds per table. > > More specifically: > 1) Right click desired database in Object Explorer > 2) Select Tasks -> Generate Scripts... > 3) Ensure database is selected on first page of wizard, click next > 4) Leave script options at their defaults, click next > 5) Select only the Table object types, click next > 6) Select all tables (I only have 35), click next > 7) Script to New Query window, click next > 8) Click Finish. > > At this point, the list of tables is determined, and then each table is > scripted. The amount of time between each table is around 15 seconds or so. I > used to be able to have the entire set of 35 tables scripted in 15 seconds. > > Is this a known issue? I found nothing with a search but of course I might > not have typed just the right keywords. > > Thanks. > -Chuck >  |
|||||||||||||||||||||||
Other interesting topics