Sql-server – Disk usage spikes: good, bad or indifferent

insertperformancesql server

I'm working on a project where we're receiving data from multiple sources, that needs to be saved into various tables in our database.

Fast.

I've played with various methods, and the fastest I've found so far is using a collection of TableValue parameters, filling them up and periodically sending them to the database via a corresponding collection of stored procedures, inside a transaction.

The results are quite satisfying. However, looking at disk usage (% Idle Time in Perfmon), I can see that the disk is getting periodically 'thrashed' (a 'spike' down to 0% every 13-18 seconds), whilst in between the %Idle time is around 90%. I've tried varying the 'batch' size, but it doesn't have an enormous influence.

Is this 'normal'? Given that the overall average throughput is ok, I could just leave it, but I'm a bit worried about exploding buffers.
Should I be able to get better throughput by (somehow) avoiding the spikes while decreasing the overall idle time

enter image description here

Other details:

The database is in Simple recovery mode, and pre-sized to 'big', the log file is not growing
The ldf and mdf files are on different disks (the mdf on a striped array, ldf on a 'normal' disk, all I had available)

Best Answer

Indifferent.

SQL Server batches updates - and especially dirty writes with so called checkpoints. This is pretty much normal behavior and only a problem when it turns into a problem (i.e. too large, slowing down the system etc.)

Related Solutions

Good, Bad or Indifferent: WHERE 1=1

The SQL Server ~~parser~~ optimizer has a feature called "Constant Folding" that eliminates tautological expressions from the query.
If you look at the execution plan, nowhere in the predicates you will see that expression appear. This implies that constant folding is performed anyway at compile time for this and other reasons and it has no effect on query performance.

See Constant Folding and Expression Evaluation During Cardinality Estimation for more info.

Sql-server – Database design: Dividing multiple identical tables, good or bad

You should use one table for this, unless you need more stringent constraints.

Each area has the same number of rows and the same number of seats per row.

Let's say there are 5 rows in every area, and 6 seats in every row. You'd want to use something along these lines.

create table seats (
  area char(1) not null check (area in ('A', 'B', 'C', 'D')),
  row integer not null check ( row between 1 and 5 ),
  seat integer not null check ( seat between 1 and 6 ),
  primary key (area, row, seat)
);

To select a single seat, put three values in the WHERE clause.

select *
from seats
where area = 'A' and
       row =  1   and
      seat =  2;

To use a table like this to model seat reservations, populate it with every possible area, row, and seat. Then set a foreign key reference to it.

create table reservations (
  performance_time datetime not null,
  party_name varchar(40) not null,
  area char(1) not null,
  row integer not null,
  seat integer not null,
  primary key (performance_time, party_name, area, row, seat),
  foreign key (area, row, seat) references seats (area, row, seat)
);

You can see all the seating for a performance with this query.

select s.area, s.row, s.seat, r.performance_time, r.party_name
from seats s
left join reservations r
       on r.area = s.area and
          r.row  = s.row  and
          r.seat = s.seat and
          r.performance_time = '2013-04-30 08:00 pm'

And you can get all the available seats for a performance with something along these lines.

with seating as (
  select s.area, s.row, s.seat, r.performance_time, r.party_name
  from seats s
  left join reservations r
         on r.area = s.area and
            r.row  = s.row  and
            r.seat = s.seat and
            r.performance_time = '2013-04-30 08:00 pm'
)
select *
from seating
where performance_time is null

By default, SQL Server will create a clustered index for a primary key constraint. You'll want to give careful thought to the order of columns in your primary key constraints, and consider adding other indexes as well. (Especially since your outputs will often need to be ordered by area, row, and seat.)

Related Question