Experience of SQL2005

Hi All,
My company is considering migrating from SQL2000 to SQL2005. Have anyone of
you already started using SQL2005? Any experience to share? Any new feature
that
excited you?
Thank you!
Jason LuJason Lu wrote:
> Hi All,
> My company is considering migrating from SQL2000 to SQL2005. Have anyone o
f
> you already started using SQL2005? Any experience to share? Any new featur
e
> that
> excited you?
> Thank you!
> Jason Lu
Plenty of people are using SQL 2005 and have been for some time. Take a
look at the site for feature lists and case studies:
http://www.microsoft.com/sql/default.mspx
If you are implementing a new application or planning some new
development then you should certainly be considering 2005. Migrating
just because it's there isn't necessarily a good idea though. First,
determine what features you need then decide whether and when to
upgrade.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thank you.
I would like to know if there is any impact on the current
application/database after migrating from SQL 2000 to SQL2005 by the
experience of migration
In order to utilize those new features in SQL 2005, such as BI Designer and
integration with VS.net 2005, we will migrate to SQL 2005 sooner or later.
The answer that I am trying to find out is should we migrate now or wait
until SQL2005 SP1.
"David Portas" wrote:

> Jason Lu wrote:
> Plenty of people are using SQL 2005 and have been for some time. Take a
> look at the site for feature lists and case studies:
> http://www.microsoft.com/sql/default.mspx
> If you are implementing a new application or planning some new
> development then you should certainly be considering 2005. Migrating
> just because it's there isn't necessarily a good idea though. First,
> determine what features you need then decide whether and when to
> upgrade.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Jason Lu wrote:
> Thank you.
> I would like to know if there is any impact on the current
> application/database after migrating from SQL 2000 to SQL2005 by the
> experience of migration
the few databases i converted to sql2005 were MUCH MUCH MUCH slower than
sql2000.
i had to drop all indexes and statistics, run the queries while tracing
with profiler, run the index tuning wizard, create all the indexes and
stats it said to create, then tweak those indexes some just to get
similar performance as sql2000. i won't be converting anytime soon.|||That was doing things the really hard way<g>. Chance are that if you simply
updated all the stats after you converted the db's you would have been fine.
Did you change the compatibility mode from 80 to 90?
Andrew J. Kelly SQL MVP
"ch" <ch@.dontemailme.com> wrote in message
news:43CC24DA.958BC523@.dontemailme.com...
>
> Jason Lu wrote:
> the few databases i converted to sql2005 were MUCH MUCH MUCH slower than
> sql2000.
> i had to drop all indexes and statistics, run the queries while tracing
> with profiler, run the index tuning wizard, create all the indexes and
> stats it said to create, then tweak those indexes some just to get
> similar performance as sql2000. i won't be converting anytime soon.|||I just came accross this post which had the same prolem as you had.
http://forums.microsoft.com/MSDN/Sh...190203&SiteID=1
"ch" wrote:

>
> Jason Lu wrote:
> the few databases i converted to sql2005 were MUCH MUCH MUCH slower than
> sql2000.
> i had to drop all indexes and statistics, run the queries while tracing
> with profiler, run the index tuning wizard, create all the indexes and
> stats it said to create, then tweak those indexes some just to get
> similar performance as sql2000. i won't be converting anytime soon.
>|||That's not my experience after having done several of these, including one
of the highest volume most performance sensitive applications that exists on
a SQL Server platform.
The vast majority of queries run faster. Some run about the same. Some
actually degrade. It happens with every new release. There are a lot of
new things the optimizer has to consider because of a lot of new features
and that sometimes has an impact.
The only time I've come across a query that degraded was due to a behavior
change between 2000 and 2005 that actually made perfect sense. After
investigating and rewriting the query lots of different ways, we figured out
that we were actually looking at a query that we were "lucky" enough to be
getting the most efficient plan from 2000 by plain and simple chance and it
degraded into a bad plan in 2005. The fix was to actually hand the proper
datatype to the function we were calling, because the optimizer in 2005 was
more sensitive to it and we wound up with a query that initially degraded
almost 10x to a query that showed an 80% performance gain by changing the
datatype of the function argument to what it should be.
The point being, this is why you TEST an application before upgading and if
the performance degrades take the time to understand whether it is a code
error in your application causing it, a feature you are using is replaced
with something much better, or if you truly have a query in the .0001% of
the cases which does actually degrade.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"ch" <ch@.dontemailme.com> wrote in message
news:43CC24DA.958BC523@.dontemailme.com...
>
> Jason Lu wrote:
> the few databases i converted to sql2005 were MUCH MUCH MUCH slower than
> sql2000.
> i had to drop all indexes and statistics, run the queries while tracing
> with profiler, run the index tuning wizard, create all the indexes and
> stats it said to create, then tweak those indexes some just to get
> similar performance as sql2000. i won't be converting anytime soon.|||Ask 50,000 people about which feature is compelling to upgrade and you'll
probably get 40,000 different answers. 2005 is a major step forward in
feature sets. Due to the massive set of new features, an argument could
really be made that it really is a new database engine. It all depends upon
what features you are currently using along with which features your
business needs that don't currently exist in SQL Server 2000.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Jason Lu" <Jason Lu@.discussions.microsoft.com> wrote in message
news:ED3B24F6-CE9A-43D8-B9F8-92C449A2E082@.microsoft.com...
> Hi All,
> My company is considering migrating from SQL2000 to SQL2005. Have anyone
> of
> you already started using SQL2005? Any experience to share? Any new
> feature
> that
> excited you?
> Thank you!
> Jason Lu
>