select count(*) in a partitioned table takes 100%cpu

Question

select count(*) in a partitioned table takes 100%cpu

tejas parikh

Old Hand

Points: 321
More actions
June 26, 2009 at 2:03 pm

#201848

Hi. I've a partitioned table based on 6 months. each partition has 2 files(1fg).
All I'm running is a simple count(*) below. The cpu usage goes to a 100% right away. Is it expected or what can i do to rectify it?
select count(*) from db1.dbo.table1 with (nolock)
where col1<'2008-03-26' table1 is partitioned on col1.col1 is a datetime field
Server has 4gb of RAM and 2 CPU's (Intel xeon 2.8ghz)
Tej

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

tejas parikh (6/26/2009)
Hi. I've a partitioned table based on 6 months. each partition has 2 files(1fg).
All I'm running is a simple count(*) below. The cpu usage goes to a 100% right away. Is it expected or what can i do to rectify it?
select count(*) from db1.dbo.table1 with (nolock)
where col1<'2008-03-26'
table1 is partitioned on col1.col1 is a datetime field
Server has 4gb of RAM and 2 CPU's (Intel xeon 2.8ghz)
Tej

Do you have an index on col1?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

tejas parikh Old Hand Points: 321 More actions · Answer 2

yes, clustered on col1. partitioned on the scheme used to partition table

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

In that case, if the CPU spikes for more than an hundred milliseconds or so, I'd have to say something is wrong.

Can't help without a bit more information:

1. How many rows are you talking about in the tables?

2. What count are you expecting?

3. What does the clustered index actually look like?

4. When was the last time the clustered index was rebuilt on both tables?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

John.Sansom SSCertifiable Points: 7335 More actions · Answer 4

Afternoon,

I may be jumping the gun a little here but in addition to Jeff's requested information, some details regarding your disk subsystem may also be useful.

For example, are you using a SAN, number of LUN's, number of disks etc.

You see, I am wondering if you are only using a single disk drive to store your data files and are thereby causing the disk queue to saturate. You can check your disk queue length by using the Windows PerfMon.

Cheers,

John Sansom (@sqlBrit) | www.johnsansom.com