November 1, 2013 at 5:11 am
I am a software engineer, and we recently got new machines. Our previous machines were about 5 years old, but we've noticed an extreme degradation in sql server performance.
System:
Windows 7 Enterprise
Intel Xeon ES-1620 3.6ghz
16gb ram
single scsi drive (not sure the exact specs) (write cache enabled)
Sql Server 2008 (not r2)
For example, I ran 50k of the insert statements structured like the one below. It took 19 minutes to complete. When I wrapped all of the insert statements in a transaction, it takes 26 seconds.
New table, no data before inserts. Primary key on [Id], no other index.
INSERT [PerformanceFw23861].[ParentBs1] ([Id], [FirstName], [LastName], [AddressLine1], [AddressLine2], [City], [State], [Version]) VALUES (N'3384f669-ead8-4dfa-85a4-a268009b5131', N'Georgina', N'Gladis', N'afdbf6c6-6172-4be4-b4ed-e27f66de92a2', N'f77cda4b-4e3c-4701-901c-1d6791104c06', N'5912f17d-90ce-4f95-89e5-ec64cdcbef1a', N'AL', 1)
We run a significant amount of integration tests that hit our local sql server, and it takes about 20 minutes now, compared to 4 minutes before. It seems to be disk related, but I am trying to figure out if there is anything we can do to fix it, or at least improve it.
Any help, or things to diagnose, would be greatly appreciated. It is driving us nuts 🙂
Thanks all!
November 1, 2013 at 5:24 am
Write latency on the log drive.
When you have the inserts as separate transactions (no user-defined transaction), each one has to flush the log to disk before it's complete. When you wrap the inserts in an explicit transaction, you get one log flush to disk at the end of the transaction.
Look at the write performance of that drive.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 1, 2013 at 6:04 am
That makes sense.
Just did a benchmark:
Random Read: 0.378 MB/s
Random Write: 1.083 MB/s
My external usb drive gets faster numbers.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply