[dba-SQLServer] SQL Server - SSD / Rotating media - Side by side test results

jwcolby jwcolby at colbyconsulting.com
Sat Jul 2 12:29:27 CDT 2011


This morning I set out to do a little bit of real world testing to see what my SSD investment buys 
me.  The following are some results.  BTW if a SQL Server guru wants to actually gain remote access 
and run tests on my system I would welcome that.  I am obviously not very talented as a SQL Server 
DBA and so a real DBA who has an interest is welcomed to tune and test.

Anyway, I have a pair of databases that I will be testing with.  One is my infamous "database from 
hell" called HSID, containing 51 million records with about 600 fields.  The other is my 
HSIDAllAdults containing about 65 million Name and address fields.  HSIDAllAdults is child to HSID, 
IOW it has a foreign key which contains the PK of HSID.  Both databases have a clustered index on 
their autonumber PK.

So... I have both of these databases on a four SSD RAID 0.  I backed them up last night and restored 
them to the same name + _Rotatingmedia on my RAID6 volumes.  So I have identical databases on a 4 
SSD Raid 0 and a 6 disk RAID 6.

I am now doing some comparative A/B runs of rather standard queries - similar to things I do 
routinely.  I backed up the two databases last night just before upgrading the server.  I restored 
both tha HSID and HSIDAllAdults this AM to the same rotating media location I normally hold the 
databases.  I did not defrag the rotating media before doing the restore.  I include the counts so 
that we can be assured that the actual data is identical between the SSD and rotating media DBs.

HSIDAllAdults - has a pair of cover indexes each of which includes Address valid

DBCC DROPCLEANBUFFERS

SELECT     AddrValid, COUNT(PK) AS Cnt
FROM         dbo.tblAllAdultNameAddr
GROUP BY AddrValid

SSD: 12 seconds
ANK	635917
E  	2918652
INV	936058
MOV	112093
PO 	3780131
V  	59074768

Rotating: 52 seconds
ANK	635917
E  	2918652
INV	936058
MOV	112093
PO 	3780131
V  	59074768

DBCC DROPCLEANBUFFERS

SELECT     COUNT(_DataHSID.dbo.tblHSID.PKID) AS Cnt, dbo.tblAllAdultNameAddr.AddrValid
FROM         dbo.tblAllAdultNameAddr INNER JOIN
                       _DataHSID.dbo.tblHSID ON dbo.tblAllAdultNameAddr.PKHSID = 
_DataHSID.dbo.tblHSID.PKID
GROUP BY dbo.tblAllAdultNameAddr.AddrValid

SSD: 35 seconds
635917	ANK
2918652	E
936058	INV
112093	MOV
3780131	PO
59074768	V

DBCC DROPCLEANBUFFERS

SELECT     COUNT(_DataHSID_RotatingMedia.dbo.tblHSID.PKID) AS Cnt, dbo.tblAllAdultNameAddr.AddrValid
FROM         _DataHSID_RotatingMedia.dbo.tblHSID INNER JOIN
                       dbo.tblAllAdultNameAddr ON _DataHSID_RotatingMedia.dbo.tblHSID.PKID = 
dbo.tblAllAdultNameAddr.PKHSID
GROUP BY dbo.tblAllAdultNameAddr.AddrValid

Rotating: 1:00
635917	ANK
2918652	E
936058	INV
112093	MOV
3780131	PO
59074768	V

The following appears to be a table scan which would be a "worst case".  I just picked a field from 
HSID which we use occasionally.

DBCC DROPCLEANBUFFERS

SELECT     COUNT(PKID) AS Cnt, Household_Occupation_code
FROM         dbo.tblHSID
GROUP BY Household_Occupation_code

Rotating: 7:06
35481479	NULL
7143021	10
11480	11
9780	12
37452	13
115093	20
2266292	21
501715	22
23724	23
1039660	30
1325728	40
1183311	50
8271	51
70318	52
2566	60
33157	61
28595	62
15305	70
511464	80
739340	90
609317	91

Rotating media: 1:05
35481479	NULL
7143021	10
11480	11
9780	12
37452	13
115093	20
2266292	21
501715	22
23724	23
1039660	30
1325728	40
1183311	50
8271	51
70318	52
2566	60
33157	61
28595	62
15305	70
511464	80
739340	90
609317	91

DBCC DROPCLEANBUFFERS

SELECT     COUNT(PKID) AS Cnt, Narrow_Income_Band
FROM         dbo.tblHSID
GROUP BY Narrow_Income_Band

SSD: 8 seconds

13824508	NULL
3762511	1
1675853	2
1015899	3
2307736	4
1031640	5
2595759	6
1069374	7
2662509	8
1100049	9
1055216	A
1026910	B
4285629	C
941494	D
862906	E
831573	F
2443917	G
738328	H
676959	I
478582	J
423856	K
1168819	L
371413	M
333796	N
249064	O
204771	P
708189	Q
193265	R
189413	S
2927130	T

Rotating media: 10 seconds
13824508	NULL
3762511	1
1675853	2
1015899	3
2307736	4
1031640	5
2595759	6
1069374	7
2662509	8
1100049	9
1055216	A
1026910	B
4285629	C
941494	D
862906	E
831573	F
2443917	G
738328	H
676959	I
478582	J
423856	K
1168819	L
371413	M
333796	N
249064	O
204771	P
708189	Q
193265	R
189413	S
2927130	T

I am going to stop for now.  I have the rotating media copies and will leave them in place for 
awhile.  If any real DBA wants to do some testing let me know.  Obviously I have to know you.  :)
-- 
John W. Colby
www.ColbyConsulting.com


More information about the dba-SQLServer mailing list