June 16, 2011 at 2:20 am
I create 2 database first database name is test_4 and have 4 file and another name is test_1 and one file
and ı try to insert test two database (50000) rows and time is same but when ı run under query
DBCC DROPCLEANBUFFERS
SET STATISTICS IO ON
SELECT * FROM TEST_INSERT
test_1 performance is better than test_4 but when ı look at the ıo statistics test_1 phisical read is higher than test_4
what is your comments??
test_4 :Table 'TEST_INSERT'. Scan count 1, logical reads 50000, physical reads 40, read-ahead reads 49976, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
test_1:Table 'TEST_INSERT'. Scan count 1, logical reads 50000, physical reads 60, read-ahead reads 49964, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
June 16, 2011 at 7:01 am
Multiple files, but a single drive? I'm not surprised that performance is better with a single file. You're asking SQL Server to do extra work to distribute the data, but you're not giving it advantages, extra spindles, to get that work done.
I'm not sure about the variation in reads. I'd have to dig into it on my own to get a good answer.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 16, 2011 at 7:04 am
Yes it can be possible.It is very logical......
June 16, 2011 at 7:05 am
But i want to ask one think what about cpu and read options Cpu can be used separate files?
June 16, 2011 at 7:09 am
when you created those two test databases did you create them the same size (size enough for the 50000 insert rows) or did you let them auto grow... and which inserts did you do first? How much space on the disk are the files taking up? Are the files fragmented?
File organization on the drive can mess up your testing so you need to know and consider these factors before you test.
The probability of survival is inversely proportional to the angle of arrival.
June 16, 2011 at 7:12 am
test_4 file size is :99+98+98+98 =393
test_1 file size is :393
June 16, 2011 at 7:15 am
ESAT ERKEC (6/16/2011)
But i want to ask one think what about cpu and read options Cpu can be used separate files?
Well, yes, CPU is used to determine what goes where, but the writes and reads still happen with disk drives. Assuming we're not talking SSD, you have spinning platters and a rocker arm moving back & forth as fast as it can. No matter how many CPUs you throw at that, it doesn't get any faster.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 16, 2011 at 7:17 am
Thanks...
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply