02 April,2011 by Tom Collins
Implementing SSD on the SAN has advantages such as hardware redundancy – but also limitations – i.e the data must first navigate CPU to drives – with the various limitations.
An alternative is to plug the SSD directly into the PCIe bus. The benefit is that IO will bypass the drives and RAID controllers . This type of SSD has it’s own controller , with the bandwidth dedicated to the drive – avoiding SAN contention and freeing up bandwidth for SQL data and logs.
This should amount to a greater and faster number of IO operations per second .OCZ and Fusion-IO are two examples of manufacturers
This is not practical for all types of SQL Server files – for example , would you want the Primary filegroup on a drive that is not part of the hardware redundancy schema. ? There are risks involved in not choosing the right files to use on a SSD drive.
But there are files , such as the TEMPDB – where there is good performance improvement potential. To assist in deciding Monitor SQL Server TempDB usage - SQL Server DBA
Firstly , the TEMPDB is rebuilt every time the SQL Server restarts – no permanent data is maintained . The type of activity on TEMPDB is of a temporary nature – such as : temp tables, index rebuild, table variables, xml variables. TEMPDB is also a spillover space for sorting data, group by, snapshot isolation and large joins
This type of activity works well with SSD on the PCIe bus . If the server crashed and was not recoverable – TEMPDB could be rebuilt – with minimal risk .TEMPDB does not have to be represented onto another server – as there is no permanent data .
If redundancy is required for this type of drive – then a software RAID solution would need to be considered.
This solution would not work for clustering – as the clustering requirement is that all drives are remote to the server. You could potentially develop a workaround – but based on the SQL Server 2005 \ 2008 documentation - maintaining a local drive is not supportable.
This does not apply to LAN based SSD – which are considered to be remote
TempDB performance and strategy checklist
Shrink tempdb without sql server restart
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |