SQL Server Performance Forum – Threads Archive
Urgent Performance Issue
Hi All, I’m experiencing some odd behavior on one of our new 64 bit SQL2K5 boxes. This box has 3 physical drives, one for the OS and applications, one for data files, and one for the logs. We’ve measured the disk performance with various utilities and it’s very fast for both read and write, much more so than my local desktop. So here’s the issue: – SELECT and UPDATE are lightning quick. I can do 1000 of them in about 30ms in the following example.– INSERT and DELETE are WAY slower than my local desktop. On my local desktop I can do 1000 of them in about 250ms, whereas on this new server it’s 4 seconds!
– When I look at perfmon for INSERT and DELETE the disk that the log file is on is maxed out for the duration of the operation. On my desktop, the disk utilitization is about 20%.
– One interesting thing to note is that when I run the same query in the tempdb on this new server, it’s faster than my desktop as I’d expect. I realize that tempdb doesn’t log as much as a regular database, but that still doesn’t account for a drop from 4000ms to about 100ms. Here’s my test script: CREATE TABLE dbo.TestTable
(
ID INTEGER IDENTITY(1, 1) PRIMARY KEY NOT NULL,
SomeText CHAR(1000),
SomeText2 CHAR(1000),
SomeNumber INTEGER
) SET NOCOUNT ON
DECLARE @Counter INTEGER
DECLARE @StartTime DATETIME SET @StartTime = GETDATE()
SET @Counter = 0 WHILE @Counter <= 1000
BEGIN
INSERT INTO dbo.TestTable(SomeText, SomeText2, SomeNumber) VALUES(‘asdfasdfasdf’, ‘asdfasdfasfasdf’, 99)
SET @Counter = @Counter + 1
END DECLARE @FinishTime VARCHAR(50) SET @FinishTime = CONVERT(VARCHAR(50), DATEDIFF(ms, @StartTime, GETDATE())) PRINT ‘Finished in ‘ + @FinishTime + ‘ ms’ – Now I know the first thing everyone is going to say is "just wrap this in a transaction so there’s only one begin/commit transaction issued". This isn’t an option because our application is an OLTP application and we can’t combine these operations. Also on my desktop, I monitored the transactions and there’s just as many begin/commit transactions as my server, yet the performance difference is staggering.
– The performance issue is only occurring on this one server, every other server and desktop I’ve tried this on is ok. Also, I can’t understand why select and update performance is so good, yet delete and insert is so bad?
– I should also mention that the data and log files are plenty big, so no autogrowing is happening. Does anyone have any ideas about what could be causing this? I’ve seen other posts on google about this, but nobody has gotten a good answer. Any help would be greatly appreciated!
Just a quick reply about this issue in case anyone else runs into it. Write caching is not enabled on our server so we believe that to be the problem. Does anyone know how to enable it? We have it enabled in Windows, but I believe it has to be enabled in BIOS as well.
Thanks
For anyone who might run into this extremely frustrating issue — it was because the write cache on our server was disabled (by default) because it was not hooked up to a battery backup unit. Once we got it hooked up and setup in BIOS the write performance went up by a factor of 10! Hope this saves some agony.
Thanks for the feedback.
It are always these "minor" things that make life stressfull ;(
]]>