April 2010 - Posts

Why do I bother with RAID 10 ?

Before I post anything I just want to clarify what I mean by RAID 10 , this is sets of mirrored pairs that have been striped as against a RAID 0 which has been mirrored.

I've just had a disk failure in the data array for one of my dev servers, it's an eight disk raid 8, no real worries, replace disk and off we go - but no - the HP engineers told me from the diagnostics ( done to ensure I got the right replacement under warranty ) that not only had a disk failed but I'd lost all the data on the array. Fair enough, once I'd replaced the disk all the database mdf files were inaccessible - not an issue as I have backups but it's still a bit of  a pain restoring 14 odd databases, the system databases were ona different array.

Now the point here is that in my time as a DBA I've only ever suffered a few disk failures and I've generally been using raid 10 since the days of SQL Server 6.0, every failure of a disk in a RAID 10 has resulted in the loss of the array/data, OK  this is only the 4th time in 16 years but it's still a pain. Now I've tested array builds - not for those of a nervous disposition - where I've pulled disks out of running arrays and replaced them to see what happens ! Happily the arrays rebuilt and no data was lost - I would say that on a large array if it's RAID 5 and the slower the disks the longer the rebuild will take, in fact a rebuild of a RAID 5 almost makes the array unusable and it may take many hours. RAID 10 in my experience suffers very little during the rebuild.

However I wonder if I really need to use RAID 10, especially for non production boxes, no the answer isn't to use RAID 5 as the write overhead is horrible, but my 1.2TB RAID 10 would be 2.4TB RAID 0 and I'd double up the iops/speed/throughput by having 8 spindles available insterad of 4 and I'd have more availble space - as long as I had backups I'd be in the same position as if I had 10.

Food for thought ?

1 Million IOPS

As a keen follower of storage performance I couldn't help but be drawn to this article in The Register  http://www.theregister.co.uk/2010/04/14/lsi_million_iops/ this morning.

I gave my 5 year old laptop a new lease of life with a SSD and in combination with the old drive made external managed to reduce the time of a demo query from 50 odd mins down to 6 mins.

I also have 4 Silicon Power 32GB SSDs set up as a raid 0 on my home server, an overblown PC.  http://www.futurestorage.co.uk/index.asp?selmanuf=Silicon%20Power&showcat=YES&selCategory=SSD  in some tests I ran these outperformed a very substantial SAN with 15k disks. OK The Silicon Power SSDs are consumer directed, e.g. cheaper than many, however I've found them pretty damn quick and I'd say probably at least equal in performance to a 15k scsi disk, I bought them mainly to use for video editing put in a raid 0 as they are so much quieter than a

Anyway back to LSI  http://www.lsi.com/DistributionSystem/AssetDocument/SSS_PB_LSI6200.pdf  and I have to say I've yet to see any of my female colleagues show that much excitement over a SSD < grin > , if you read the actual spec this isn't actually a disk, it's a Pci x 8 card - I'm not sure how you might set these up as a high speed storage unit or add redundancy.

The uptake of SSDs by storage vendors has I believe been limited in a sense by the iop bandwidth of the backplanes, in other words yes SSDs will give you high IOPS with few units, but at the likely cost of the backplane/bandwidth not being able to support.

As usual I had a look at the LSI device, the high iops are for 2k read/writes, -25% for 4k, but from a database point of view the use of Pci slots somewhat restricts the amount of storage, after all 1TB isn't much in database world. I was talking to one SAN vendor and they were suggesting that the likelyhood was to use SSDs ( the disk type this type ) almost as an extended cache with the software moving data from physical disk to SSD and back as demand required.

The one thing I couldn't discover was the price, usually SSDs are very expensive on a capacity vs price per disk, however as LSI point out you need some 2,500 standard disks to achieve the same IOPS, however you would get  1,125 times the raw storage, e.g. 1,125 TB so really the power and capacity cost comparison isn't really valid for as we all know that for databases it's more often the number of spindles required to get the performance rather than the capacity - in these cases SSDs may prove more cost effective where the actual database size is small, however I could witter on for ages trying to produce cost vs iops vs capacity calculations.

Would you expect this then ?

I've just been working through a database looking for log tables which can have data aged out, or deleted if you like. I was using SSMS for this and used the default right click select top 1000 rows as a quick view of the data.

Establishing that I had unwanted data in such a table I deleted based upon getdate()-30 , nearly 7,000 rows deleted, I then highlighted the top 1000 rows query and re-ran query, surpisingly I still got the same result set with 2006 dates in it, and 1,000 rows.

A quick count(*) from the table showed only 719 rows, a select * showed all data prior to 30 days ago deleted. Re-ran the original query, still 1000 rows with dates of 2006, all this in the same query window in SSMS

No worries then, open another query window, right click, top 1000 rows --- still 1000 rows returned and showing data of 7,00 odd rows. Table properties also showed 7,000 rows.

Back to my original query, a count (*) reports 719 rows.    I can only assume that SSMS caches any right click  Select TOP 1000 * queries and even another Query windows does not reflect the updated data set, which is a bit strange, I mean you'd normally expect a select to see data followed by a delete of some rows form the table, followed by a select would show, so to speak, that rows had actually been deleted - well I did.

So if anyone can come with a logical explnation of how I have just opened a new window in SSMS, run the select top 1000 from table and returned 1000 rows which don't exist in the table, followed by a select * in the same window which only returns the 719 rows and none of the rows the previous top 1000 returned I'd be really interested.

SQL 2008 SP1 CU6

Would you expect this error ?

Now I know why, but what I'm thinking is that if I create an error  should I get valid data returned?

To explain, I was browsing through the dmvs for queries which might benefit from tuning and I identified a query with two clustered index scans ( table scans ). I don't know all the schema off by heart and I was looking for a select by a LoginID column.

I assumed this would be numeric and promptly entered an integer value to examine the query plan, yeah I should have looked at the table definition first!  To my surprise there were two distinct events here, firstly the column LogonID actually was an email address, not numeric, secondly the error message returned a valid email address from the table, which I could then use to check the query properly.

So here's a roll your own example:-

 

create table dbo.test( NumKey int identity(1,1),blah nvarchar(500));
insert into dbo.test(blah)
values
('colin@mycompany.com'),
('fred@mycompany.com'),
('beert@mycompany.com'),
('sandra@mycompany.com'),
('jane@mycompany.com'),
('tarzan@mycompany.com'),
('auntiejill@mycompany.com'),
('marquisdesade@mycompany.com'),
('meerkat@mycompany.com');

select * from dbo.test where blah = 123;

.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } This will return the error, but with valid data - interesting eh?

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the nvarchar value 'colin@mycompany.com' to data type int.

Posted by GrumpyOldDBA with 2 comment(s)