This thing has been doing mine head in so I’m going to vent; some of you who know me know my opinion on SAN's - overpriced fads where DASD performs better, is cheaper etc....
Anyway, get this; SQL 2005 on x64 Windows 2003 R2, running a simple INSERT that inserts 8KB of data into a table (see script below).
Running the script where the database is on the SAN I’m only getting 12MB/sec write performance, SAN utilisation 80% and that’s the only thing running on the SAN!!
Running it against a database that is on local DASD SCSI disks – a mirrored pair through a PERC controller with battery backed cache I’m getting 30MB/sec.
The checkpoint process is fine, so its just the SAN not handing the 8KB writes that the log is doing.
Trying to find any support information on EMC’s site is laughable; perhaps they just don’t get it – is this the great information lock in? When you have a problem with their kit you have to pay somebody to help you; I thought those days in IT where long gone….
“Based on the information that you have provided and the information available in our records, we have not been able to identify your company as having the necessary relationship with EMC to provide you with full access to Powerlink. However, you will be able to retain a limited-access Powerlink account.”
The limited-access is so limited it hasn’t got any options apart from asking you to upgrade to another account.
Anyway, we are going to rebuild the SAN using guidance from the supplier so I’ll report back in a couple of weeks and let you know how it went…
Out of interest, perhaps people could run the script below and report how many MB/sec write they are getting and whether its DASD or SAN….
create database testio
on primary ( name=testio, filename='g:\testio.mdf', size=10gb )
log on ( name=testio_log, filename='g:\testio.ldf', size=10mb,
filegrowth=10mb )
go
use testio
go
create table testio (
blah char(8000) not null
)
go
declare @i int
set @i = 1
set nocount on
while @i <= 100000
begin
insert testio values( cast( getdate() as char(8000) ) )
set @i = @i + 1
end
23 Oct 2006 -> Check out a more recent entry on Server Commissioning (Transaction Log) for a more detailed explanation and tests and results as to whats going on.