December 10, 2009 at 7:17 am
Our database began experiencing extreme slowness and we were surmising it is a slow disk speed. I took a backup of the database and installed it on a different server (with less memory, but faster hard drives). I then did a little experiment. Created a temporary table and inserted records into it:
DECLARE @Counter AS INT
DECLARE @EndCounter as INT
DECLARE @st datetime
SET @Counter=0
SET @EndCounter=5000
DROP table tempTest
CREATE TABLE tempTest
(
pkey INT, name varchar(100)
)
WHILE (@Counter<@EndCounter)
BEGIN
INSERT INTO tempTest(pkey,name) SELECT @Counter,'1234567890123456789012345678901234567890123456789012345678901234567890123456780'
SET @Counter=@Counter+1
END
On our test environment, it took 1 second, while it took 92 seconds in our production environment (not good).
I then did a quick little test on reading the data and it was faster in the production environment (2 seconds) than the test (3 seconds). I had also done the read test with larger amounts of data and the results were always faster in our production environment.
In a nutshell, our production environment has tremendously slower INSERT speeds, but faster read speeds. Could it just be the disk or is there something else I should look for?
December 10, 2009 at 7:19 am
Put this in wrong forum, but not sure how to move it!?
December 10, 2009 at 8:08 am
moved
December 10, 2009 at 9:58 am
I recently troubleshooted an issue where a simple activity logging table was taking 45 seconds to insert a new log record. After I took actions to reorganize the index as described in this MSDN article (though written to SQL 2000, I believe it still applies in 2005 and 2008, but is deprecated for the future), it went down to less than 1 second to insert.
http://msdn.microsoft.com/en-us/library/aa258286(SQL.80).aspx
To find out if this would be helpful, run DBCC SHOWCONTIG in the database, and find the table in question. If Logical Fragmentation is not 0%, or Scan Density is not close to 100%, your insert is walking through a lot of index pages to add a new entry.
December 10, 2009 at 3:27 pm
Not sure if this is your issue, but have you checked your disk subsystem? Are there any errors reported? High disk I/O from another process or job? Also, if your data files (particularly log files) are on a RAID 5 then you will most definitely have faster reads than writes, because of the redundant operations that are going on for the RAID. I would not think it would be anything that slow though...
Joie Andrew
"Since 1982"
December 14, 2009 at 2:24 am
One would tend to assume that lock contention was the issue, or maybe the waits are on log flushes? Hard to say without more information.
You will probably find the following Microsoft White Paper useful:
http://technet.microsoft.com/en-gb/library/cc966413.aspx
It is in Word format, and quite long, but well worth the time and effort required to read. Even if you just skim through it, it should point you in the right direction.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 15, 2009 at 9:54 am
1) the easy thing to answer is why reads are faster in production: more RAM, thus the table is being read out of memory - way faster than having to touch the disks.
2) As for why inserts are slower in production, there could be MANY causes. here is a partial list off the top of my head:
a) blocking
b) triggers
c) foreign keys
d) lots of indexes on inserted table
e) 1MB growth rate on data/log files
f) very slow tlog drive
g) CPU overload
have you done fileIO stall and waitstats analysis on the production box?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 15, 2009 at 1:23 pm
I suspect it is the hard drive; therefore, I've run some IO tests just on the drive itself and the write times on the disc are incredibly slow. I'm guessing the drive may be going bad. I'm going to change out the drive with a new one and see if it makes a difference.
December 15, 2009 at 2:33 pm
tscott (12/15/2009)
I suspect it is the hard drive; therefore, I've run some IO tests just on the drive itself and the write times on the disc are incredibly slow. I'm guessing the drive may be going bad. I'm going to change out the drive with a new one and see if it makes a difference.
I must say it concerns me that you are implying a SINGLE DRIVE is serving up data and log files here? Even if it is a single drive for just ONE of those types, it is a certainty that a single (rotating) drive cannot keep up with any reasonable production load these days. Hell, I think my 4yo daughter's computer has 3 drives in it. :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 17, 2009 at 7:19 am
We had an external networking company setup the machine for us. I didn't do my due diligence and assumed it was a RAID using several discs (can't really blame anybody but myself). We're now creating four different RAID drives for the machine - 1 for the operating system, 1 for the data files, 1 for the transactions logs, and 1 for the tempDB. It will certainly be much faster. Note to all others - trust but verify!
PS - I want your daughter's computer for my house.
December 17, 2009 at 8:38 am
tscott (12/17/2009)
We had an external networking company setup the machine for us.
:w00t:
Um - external to your campany, or external to accepted reality?
December 17, 2009 at 10:57 am
Trust? I don't trust any business entity - even my clients! As House said, "I don't ask WHY patients lie - I just assume they do". :hehe:
As for my daughter's computer, well, I married a doctor so could buy an airplane and enjoy the many other benefits that having a bit of extra money provides. Hmm, I suppose I have worked very hard to become a highly-sought-after SQL Server consultant partly for the same reasons! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply