SQL Server tempdb and solid state drives

02 April,2011 by Jack Vamvas

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

Read More

TempDB performance and strategy checklist

Shrink tempdb without sql server restart


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

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.

Working...

Post a comment on SQL Server tempdb and solid state drives


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer