Are there any special flags we're supposed to flip when we do a SQL 2008
installation? We've installed SQL 2008 on the same server as our production
SQL 2000 server and we're seeing some extremely pathetic results running
queries in SQL 2008. For example, I have a query that is relatively simple
that joins 4 large tables together. I just ran it in SQL 2000 and it ran in
1 min 1 sec. It's been going for 45 minutes in SQL 2008 and it's still
going. The dbs in both 2000 and 2008 are on the same arrays; tempdb is
properly sized and made up of the same number of devices/sizes on both 2000
and 2008. At this point we strongly suspect something is wrong with the db
or our SQL installation but we're not sure where to look. I ran a db
reindex on the entire db and it hasn't helped. Does anyone have any insight
into where the "turbo" switch is in 2008?

Thanks, Andre

Have you updated statistics? The next step is to take a look at the
execution plans and see where they are different. In most cases, the
queries are using old/stale statistics and generating inappropriate
execution plans.

"Andre" <no (AT) spam (DOT) com> wrote

I rebuild indexes in the maintenance plan, and from what I read, rebuilding
indexes also updates stats. So do I still need to update stats after an
index rebuild?

Andre

Andre (no (AT) spam (DOT) com) writes:
Rebuilding indexes also updates statistics.

There is no turbo switch, you have to dig into the query plans for the
queries performing differently. Keep in mind that SQL Server uses a
cost-based optimizer that from *statistics* sampled about the data makes
*estimates* what is the best execution plan. Statistics can lie, estimates
can be wrong. Each new version of SQL Server brings changes to the
optimizer, often to the better, but the cases you notice is when it
fails in the new version when it didn't in the old version.

If you compare the execution plans between SQL 2000 and SQL 2008, you
will find considerable differences.




--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Well the fact that you have both instances on the same machine is a red flag
to start with. You can't compare apples to apples if you are now sharing
resources with another instance unless you have very good control of the
environment. How much memory is allocated to each instance? Is there
anything going on one instance while the query is running on the other etc.
I wouldn't call a query simple if it joins 4 large tables and takes over a
minute and you call that good. Chances are that it does not have proper
indexes to support it and it has less than an optimal plan to begin with.
The optimizer has improved in 2008 but that means that it can actually
decrease performance on queries that are not properly tuned to begin with.
Are the two plans the same or are they different? If they are different what
is different about them. Is this query in a stored proc or is it adhoc? Can
you post the actual query?

--

Andrew J. Kelly SQL MVP
Solid Quality Mentors

"Andre" <no (AT) spam (DOT) com> wrote

Red flag perhaps but we're a small shop and don't have extra servers sitting
around waiting to play with new software. Additionally we wanted to see
what performance will be like in 2008 on our Prod box. We're running all of
our 2008 testing in off-hours, when we know SQL 2000 is quiet.

I completely agree with you about calling 1 minute good. My goal is always
for sub-second response time, but there are a number of variables going on
here. Simply due to workload we have prioritized some items ahead of
others. We knew, or hoped, that going to SQL 2008 will improve things, and
we're also planning a total db redesign.

I ended up running my query thru the db tuning engine. It recommended one
index and several statistics. After I had applied these the query now runs
in 3 seconds. While this is great, and a huge improvement from SQL 2000, it
just means that we have a lot of work ahead of us trying to tune all of our
existing procs.

To answer a question you had, all of our work is done in stored procs. We
happened to rip the query out of one that was taking forever so we could
analyze it. My comparisons times that I posted were for a raw query run on
both SQL 2000 and 2008, but we never pass dynamic sql to the db. This was
just done for testing/tuning purposes.

Thanks for your response Andrew. I value your input and enjoy your
articles.

Andre

Yes unfortunately there will simply be some queries / sps that will need to
be further tuned to get the most out of SQL2008. But that doesn't mean you
have to tackle them all at once. I think you are headed in the right
direction by looking for the ones that get run often and do lots of reads.
Hit the top 5 of those and you will most likely fix 80 to 90% of you biggest
perf problems. It's not uncommon at all for shops to fix just a few procs
and see performance gains in the hundreds of %. So don't get caught in that
trap of thinking everything needs attention. Usually it really is just a
handful that make the majority of the trouble. Tackle them one at a time as
resources permit. Good luck.

--

Andrew J. Kelly SQL MVP
Solid Quality Mentors

"Andre" <no (AT) spam (DOT) com> wrote

Good to know, and good advice, thank you.

Andre

Another idea for what it's worth. Since these two SQL Servers are on the
same computer, perhaps your SQL Server 2000 has a grabbed a lot of memory
and SQL Server 2008 isn't getting much.Your old SQL Server could be using
it's large memory for a sort (for example) and your new SQL Server is
sorting on disk. Now that you have added an index, the new SQL Server
doesn't have to sort the results anymore so it has faster results. Of
course, that implies that the old SQL Server could benefit from the index,
too. Just a thought.
--
Rick Byham, MSFT
(Implies no warranty or rights)


"Andre" <no (AT) spam (DOT) com> wrote