Take the script I provide and execute it several times on any database. Take the best result (it returns the elapsed time in ms)
On my old laptop I get about 600ms. On the best PROD server I had 430ms - but it was with a standard RAID drives.
On any SAN EMC system it was a real nightmare. 800, 900, in some cases 1800ms!
Yes, I know why: because SAN EMC and other companies like it have a very good marketing. They manipulate with throughput numbers, but for SQL server, especially for the OLTP systems, reactivity is much more important.
They claim that 'our IO systems are so smart, that you can put LOG and FILES on the same drive'. And usually, there is no choice.
People who buy servers are hosting listen to that marketing blah-blah-blah and we, DBAs, need much later deal with high Disk Queue values.
If you can , provide the execution times of my script here. May be you have a good IO system and I was just so unlucky?
---------------------
set nocount on
GO
create table _Ptest (n int identity, k int not null, v varchar(128))
GO
insert into _Ptest (k,v) select 1, 'this is a test'
GO
declare @t datetime, @n int
set @t=getdate()
set @n=16
while @n>0 begin
insert into _Ptest (k,v) select k+n,v from _Ptest
set @n=@n-1
end
checkpoint
select datediff(ms,@t,getdate())
GO
drop table _Ptest
On my old laptop I get about 600ms. On the best PROD server I had 430ms - but it was with a standard RAID drives.
On any SAN EMC system it was a real nightmare. 800, 900, in some cases 1800ms!
Yes, I know why: because SAN EMC and other companies like it have a very good marketing. They manipulate with throughput numbers, but for SQL server, especially for the OLTP systems, reactivity is much more important.
They claim that 'our IO systems are so smart, that you can put LOG and FILES on the same drive'. And usually, there is no choice.
People who buy servers are hosting listen to that marketing blah-blah-blah and we, DBAs, need much later deal with high Disk Queue values.
If you can , provide the execution times of my script here. May be you have a good IO system and I was just so unlucky?
---------------------
set nocount on
GO
create table _Ptest (n int identity, k int not null, v varchar(128))
GO
insert into _Ptest (k,v) select 1, 'this is a test'
GO
declare @t datetime, @n int
set @t=getdate()
set @n=16
while @n>0 begin
insert into _Ptest (k,v) select k+n,v from _Ptest
set @n=@n-1
end
checkpoint
select datediff(ms,@t,getdate())
GO
drop table _Ptest