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