We have an application that initially used SQL 2000.  We have a client
that insisted on SQL2005.  Now when our application runs (for example)
a stored procedure (and most other parts of the program) on the SQL2005
database it takes a lot longer than (3 or 5x longer) it does on the
same database in SQL2000.  The application is written in VB6 and the
user uses Citrix to connect to the application.

Any advice would be great! - Robb

Did you upgrade the database from 2000 to 2005?

If so, did you update statistics, defrag indexes, ensure that compatibility
mode is set to 90 and not 80?

I have found the opposite... SQL 2005 is much faster than 2000.

A




<RobbMic***@gmail.com> wrote in message

Show quote

Hide quote

yes the database was upgraded

AND ... "did you update statistics, defrag indexes, ensure that
compatibility
mode is set to 90 and not 80?"

Show quote

Hide quote

I changed the compatibility mode to 90 and it got much faster.

Do you have any links on updating stats and defragging indexes?

(RobbMic***@gmail.com) writes:
sp_updatestats, UPDATE STATISTICS, DBCC DBREINDEX, maintenance plans.
All is in Books Online.

After upgrading from SQL Server 2000 to 2005, we found that Inserts are a
little bit faster (by around 5% to 10%), but Selects are MUCH slower (by
around 50% to 100%).  And that's after setting the compatibility mode to 90,
updating the statistics, and rebuilding the indexes.

--Rob Robrts

<RobbMic***@gmail.com> wrote in message

Show quote

Hide quote

Well, that hasn't been my experience in a 1.5 TB system that moved to SQL
Server 2005 over the past 6 months.

Do you still have both systems or did you do an in-place upgrade?  If the
former, have you compared execution plans on such queries to figure out why
the change?  Have you looked at execution plans at all to see where the
bottleneck is?  "SELECTs are 50% to 100% slower" is pretty vague and
general.  What is slower?  Seeks, scans, merge loops, ...?  My money is on
something other than and/or in addition to the upgrade that is causing the
slowness.

Show quote

Hide quote

Aaron,

We did an in-place upgrade, so unfortunately we no longer have SQL Server
2000 available to test against.  My statement about SELECTs being 50% to
100% slower is based on doing before-and-after testing.  Before doing the
upgrade I ran a number of reports from an ASP.NET 2.0 application that uses
one of the databases on the server.  I timed how long the reports took to
run, and I saved the results of the reports.  After doing the upgrade I ran
the same reports, again timing them and saving the results.  I compared the
before-and-after results to make sure they were identical, which they were.
But in every case the reports took around 50% to 100% longer to run on SQL
Server 2005 than they had on SQL Server 2000.  I have not gone back and
looked at the execution plans to see where the bottlenecks are because I
haven't had time to.  But before doing the upgrade, I didn't expect that I
was going to have to go back and examine all of my indexes and stored
procedures to try to get everything to perform as well on SQL Server 2005 as
it had on SQL Server 2000.

--Rob Roberts

A .NET Framework error occurred during execution of user defined r
sql statement (how to)
Update Information in SQL from a VBS script
Return a City in a column on max count from another column in a group by
SQL statement increase question