Sql-server – SQL Server query disk write/sec limit

sql server

I am facing an issue which it looks like the server is hitting disk write limit. But after some test, I realize it wasn't. I was running the following query:

DECLARE @MyCounter int;
SET @MyCounter = 0;

SET NOCOUNT ON;

WHILE (@MyCounter < 10000)
BEGIN
   INSERT INTO TestTable VALUES ('testing text', 99)
   SET @MyCounter = @MyCounter + 1;
END

The following shows some print screen and explanation of my problem:

At 1st, I opened 1 query window and run the query, then I checked Performance Monitor:

enter image description here

It seems like I'm hitting limit of around 120 Disk Writes/sec.

Then, I opened another query window and run the same query, now I got the following Performance Monitor screen:

enter image description here

Ok, Disk Writes/sec limit seems increased to 240.

And again, I opened 1 more query window and run the same query again, I got the following Performance Monitor screen:

enter image description here

As expected, Disk Writes/sec increased to around 360.

My question is: Is SQL Server / Windows server / any other thing limiting this Disk Writes/sec ? Thanks in advance.

I'm using SQL Server 2014 in Windows Server 2012 R2 64-bit running on Dell PowerEdge 2900 server. This server has just been formatted.

Best Answer

You don't say but I'm guessing that your log files are on your D: drive and what you're seeing here is writes to the transaction log files, not to the database files i.e. to ldf not to mdf. You won't see the mdf files written until the DB hits a CHECKPOINT, which could be some minutes later.

The linear increase is good and what you'd hope for. That you can get from 120 to 240 and 360 shows that the disk subsystem is capable of these rates. Whether the absolute number is as good as you would hope for or not, that's another matter. I would suggest that the nature of the test you've chosen is itself limiting the whole system throughput. Now you have populated tables you can try

INSERT INTO TestTable
SELECT TOP 10000 * FROM TestTable;

and see what rate that gives you.

Related Question