MS SQL Server - Upgrade to SQL 2005

Author

Upgrade to SQL 2005 - HIGH CPU

jking@mlspin.com

2006-03-26, 3:23 am

Hi,
We recently upgraded our SQL 2000 Server to SQL 2005.
Before the upgrade, our CPU ran at 50-70%. Now its pegged between
90-100%?!?!

SQL Server hardware -> 8 x 3.0GHZ XEON + 32GB MEMORY
Database File: ~ 15 GB

We already rebuilt the indexes and updated stats.

AWE is enabled, *BUT* before the upgrade, when the SQL Server started,
it would 'suck up' all the memory we allocated for it. After the
upgrade, it s...l...o...w...l....y consumes memory.

Any ideas??

Andrew J. Kelly

2006-03-26, 9:23 am

In 2005 AWE is dynamic so it will only consume memory when it needs it.
Since you have 32GB and only 15GB db it should not use it all unless you are
doing lots of work in tempdb. Since all your data should be in memory there
should not be anything to hold back the processors from doing a lot of work
when called for but it sounds like you need to optimize your db. That seems
like an awful lot of CPU for such a small db. Have you looked to see how
efficient your query plans are?

--
Andrew J. Kelly SQL MVP

<jking@mlspin.com> wrote in message
news:1143342276.082551.252390@t31g2000cwb.googlegroups.com...
> Hi,
> We recently upgraded our SQL 2000 Server to SQL 2005.
> Before the upgrade, our CPU ran at 50-70%. Now its pegged between
> 90-100%?!?!
>
> SQL Server hardware -> 8 x 3.0GHZ XEON + 32GB MEMORY
> Database File: ~ 15 GB
>
> We already rebuilt the indexes and updated stats.
>
> AWE is enabled, *BUT* before the upgrade, when the SQL Server started,
> it would 'suck up' all the memory we allocated for it. After the
> upgrade, it s...l...o...w...l....y consumes memory.
>
>
> Any ideas??
>

jking@mlspin.com

2006-03-26, 1:23 pm

We looked at our query plans, but we have very simple queries (i.e.
selecting by primary key or joining 1 table). We do have a lot of
traffic...maybe serving up about 500,000 + queries per hour...
When we run test queries with no traffic load (i.e. shutting off a
customers) results come back 50% faster. But with moderate load, the
server maxes out CPU..when it maxes out, we are servicing about 25% of
the load it took to max out SQL 2000.

Andrew J. Kelly

2006-03-26, 8:23 pm

What is the MAXDOP set to? If it is the default of 0 then you should think
about setting it to something fixed like 2. You can adjust up or down from
there as needed. This will give you more concurrency if you are getting
lots of parallel plans. What is you MAX Memory set to?

--
Andrew J. Kelly SQL MVP

<jking@mlspin.com> wrote in message
news:1143397767.623482.167880@z34g2000cwc.googlegroups.com...
> We looked at our query plans, but we have very simple queries (i.e.
> selecting by primary key or joining 1 table). We do have a lot of
> traffic...maybe serving up about 500,000 + queries per hour...
> When we run test queries with no traffic load (i.e. shutting off a
> customers) results come back 50% faster. But with moderate load, the
> server maxes out CPU..when it maxes out, we are servicing about 25% of
> the load it took to max out SQL 2000.
>

Linchi Shea

2006-03-26, 8:23 pm

Higher CPU consumption is not necessarily a bad thing in itself. The key
question is, are you getting better throughput and response time on your
business transactions?

Linchi

"jking@mlspin.com" wrote:

> Hi,
> We recently upgraded our SQL 2000 Server to SQL 2005.
> Before the upgrade, our CPU ran at 50-70%. Now its pegged between
> 90-100%?!?!
>
> SQL Server hardware -> 8 x 3.0GHZ XEON + 32GB MEMORY
> Database File: ~ 15 GB
>
> We already rebuilt the indexes and updated stats.
>
> AWE is enabled, *BUT* before the upgrade, when the SQL Server started,
> it would 'suck up' all the memory we allocated for it. After the
> upgrade, it s...l...o...w...l....y consumes memory.
>
>
> Any ideas??
>
>

John Bell

2006-03-26, 8:23 pm

Hi

With the ability to view perfmon counters in SQL Profiler you should be able
to track what is happening when this high CPU occurs. You may want to check
for any hints that you are using and see if the engine will do better without
them, also check disc fragmentation and see what DTA suggests.

John

"jking@mlspin.com" wrote:

> Hi,
> We recently upgraded our SQL 2000 Server to SQL 2005.
> Before the upgrade, our CPU ran at 50-70%. Now its pegged between
> 90-100%?!?!
>
> SQL Server hardware -> 8 x 3.0GHZ XEON + 32GB MEMORY
> Database File: ~ 15 GB
>
> We already rebuilt the indexes and updated stats.
>
> AWE is enabled, *BUT* before the upgrade, when the SQL Server started,
> it would 'suck up' all the memory we allocated for it. After the
> upgrade, it s...l...o...w...l....y consumes memory.
>
>
> Any ideas??
>
>

Jim King

2006-03-26, 8:23 pm

Andrew -

We've played with MAXDOP, the Threshhold, etc. to no avail.
Maxmemory is set to 2147483648 (default) The server has allocated, at
most, 10.9GB of the 32GB available. 0 pages/sec on the swapfile. With
a 10-15BG database, as with SQL 2000, whatever work it was doing is
being done in RAM.

Jim King

2006-03-26, 8:23 pm

Linchi -

What we had experienced with low concurrent users was a near doubling
of efficiency - some scripts that do SELECT and send out 90,000+ emails
for a nightly update type of module used to run in 6 hours pointed to
the SQL2000 version, and ran in only 3 hours pointed at our SQL 2005
box. We were initially thrilled at this level of improvement.

Once the morning rush came in (real estate agents - we're an MLS) the
system made it to about 1100 concurrent users, then spiked the cpu at
90-100% and everything came to a screeching halt. When the DB was on
our SQL 2000 box, we handled 4400+ users (just this past Friday) before
things even began to slow down.

We backed-up, restored to the 2005 box, ran UPDATE STATISTICS WITH
FULLSCAN on every table, rebuilt the indices, and performed a DBCC
UPDATEUSAGE on the database. Still ran like slop - only with high
concurrent connections from the 8 IIS servers hitting it.

Jim King

2006-03-26, 8:23 pm

John -

Threads are low (context switching between 11000 and 15000 per second).
The drives are brand new, clean, formatted 146GB 15000rpm drives,
RAID1, with nothing else on them but this one database.

We've tried disabling HyperThreading in BIOS, and just about every
other thing we could find regarding SQL2000/2005 performance. Right
now, we're just finishing up moving the entire thing back to the 2000
box... WHICH, let me tell you, is no easy task, since you can't
backup/restore to an earlier version OR use the Copy Database command.
We scripted the database structure and indices, then had to just Export
each table... brutal, even for just 10GB.

At this point we're going to just do what we've always done with new
Microsoft products: Wait for SP1.

John Bell

2006-03-27, 3:23 am

Hi Jim

Do you still have the 2005 system available to test? If you have a profiler
trace you can either use the replay facility to play back the actions or the
ostress utility. You may also want to look at SQLIOStress. If you have
Loadrunner or IBM Rational Performance Tester then these can be used to
stress the system. You may also want to check things like disc sector sizes
and it is not clear if you have separated data and log files, but these
should be on different sets of spindles.

John

"Jim King" wrote:

> John -
>
> Threads are low (context switching between 11000 and 15000 per second).
> The drives are brand new, clean, formatted 146GB 15000rpm drives,
> RAID1, with nothing else on them but this one database.
>
> We've tried disabling HyperThreading in BIOS, and just about every
> other thing we could find regarding SQL2000/2005 performance. Right
> now, we're just finishing up moving the entire thing back to the 2000
> box... WHICH, let me tell you, is no easy task, since you can't
> backup/restore to an earlier version OR use the Copy Database command.
> We scripted the database structure and indices, then had to just Export
> each table... brutal, even for just 10GB.
>
> At this point we're going to just do what we've always done with new
> Microsoft products: Wait for SP1.
>
>

Sponsored Links