CPU spike with SQL Server 2005 Express Edition

Hello,
Any help is greatly appreciated.
I've recently upgraded my MSDE 2000 to SQL Server 2005 Express Edition
on my Windows 2003 server SP1. I've noticted that running a single one
table query against the new sql engine causes the sqlservr.exe process
to consume 98% of CPU on the server. The spike usually lasts for a
20-30 seconds and I can't figure out why. The query is a simple select
query against one table with only 3000 records.
The server is a one proc machine 2.0 GHz celeron, with 1 GB of RAM.
Again, any suggestions are appreciated.
Thank you.support@.holylandmarket.com wrote:
> Hello,
> Any help is greatly appreciated.
> I've recently upgraded my MSDE 2000 to SQL Server 2005 Express Edition
> on my Windows 2003 server SP1. I've noticted that running a single one
> table query against the new sql engine causes the sqlservr.exe process
> to consume 98% of CPU on the server. The spike usually lasts for a
> 20-30 seconds and I can't figure out why. The query is a simple select
> query against one table with only 3000 records.
> The server is a one proc machine 2.0 GHz celeron, with 1 GB of RAM.
> Again, any suggestions are appreciated.
> Thank you.
>
What does the execution plan for the query look like? What is the
heaviest part of the plan?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||It looks like the index scan is the heaviest part of the plan. The
problem is I get high CPU spikes regardless of the query I run.
Again the db is very small with very little data in it. I have the
same db running on Windows XP and I don't see any CPU spikes.
Isam
Thanks.
Tracy McKibben wrote:
> support@.holylandmarket.com wrote:
> What does the execution plan for the query look like? What is the
> heaviest part of the plan?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Isam wrote:
> It looks like the index scan is the heaviest part of the plan. The
> problem is I get high CPU spikes regardless of the query I run.
> Again the db is very small with very little data in it. I have the
> same db running on Windows XP and I don't see any CPU spikes.
>
You say the spike lasts for 20-30 seconds - how long does the query run?
Do you have auto-close enabled on this database?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The query comes back in sub second but the process is still pegged.
Auto Close is set to True on the db.
Isam
Tracy McKibben wrote:
> Isam wrote:
> You say the spike lasts for 20-30 seconds - how long does the query run?
> Do you have auto-close enabled on this database?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Isam wrote:
> The query comes back in sub second but the process is still pegged.
> Auto Close is set to True on the db.
>
Turn that off and see if the spike goes away...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I can still see it spike. Turning auto close off did not change much.
Isam
support@.holylandmarket.com wrote:
> Hello,
> Any help is greatly appreciated.
> I've recently upgraded my MSDE 2000 to SQL Server 2005 Express Edition
> on my Windows 2003 server SP1. I've noticted that running a single one
> table query against the new sql engine causes the sqlservr.exe process
> to consume 98% of CPU on the server. The spike usually lasts for a
> 20-30 seconds and I can't figure out why. The query is a simple select
> query against one table with only 3000 records.
> The server is a one proc machine 2.0 GHz celeron, with 1 GB of RAM.
> Again, any suggestions are appreciated.
> Thank you.