Thanks for your ideas!

I'm not sure I follow the idea of reversing the order. Won't rowcount always return 1 after an INSERT? (i.e., One row affected).

Note that most of the...

GilaMonster (5/14/2008)

No. It won't. All it...

In reference to the idea of changing the Recovery Model, are you suggesting that this would reduce the frequency and/or duration of the checkpoints?

Thanks, Gail!

You've been extremely helpful throughout this entire post.

Jamie

One more question to help me understand what's happening.

The checkpoint process kicks in as a result of accumulated write activity, which needs to be flushed to the disk. The...

Thanks for this info.

We're currently on a single disk. We can live with the current situation as is, but if things get worse, I'll follow up on your advice. Thanks!

I...

Bingo!

I found the following perfmon counter:

SQLServer:Buffer Manager\Checkpoint pages/sec

It correlates exactly with the disk writes.

So indeed my problem seems to be related to the checkpoints.

Thanks to those of you who pointed...

Hi Gail,

Thanks for your reply. Do you know if there is any logging of the checkpoints, which would allow me to verify that indeed they are associated with what I'm...

Thanks to all of you who have shared your ideas and suggestions over the last few hours.

Some comments:

1. Some of you suggested that perhaps an external process is responsible for...

Thanks for the thought.

The DB was set to an initial size of nearly 2GB and an autogrow rate of 1MB. I changed the autogrow rate to 10%.

Unfortunately, this didn't make...

Please excuse my ignorance:

1. How do I run the Profiler?

2. What counters do you suggest I monitor with Perfmon?

Thanks,

Jamie

Thanks for the ideas.

AutoClose and AutoShrink are both set to False.

The performance problem is very brief, so it's hard to look for anything while it's happening. I see the problem...

Thanks to all for your suggestions.

Specifically, I'm curious about tables with non-clustered indexes. When I do a select, the table and the index need to locked. Likewise an update or...

Thanks for the comment. Actually I think it helps very much since I'm only asking for the first record which matches.

Indeed the problem had to do with locking:

I changed the original query to:

select top 1 Name from People with(READUNCOMMITTED) where Status = 0

And it returns a value immediately.

Thanks again for...