Execute the following Microsoft SQL Server Transact-SQL (T-SQL) script in Management Studio (SSMS) Query Editor, SQLCMD or other client software to test the speed of the tempdb disk. The script cycles 10 times. You can change the @loop and @NoOfInserts variables to customize the disk performance test.

USE tempdb  -- replace it with db to be tested

GO

CREATE TABLE DiskSpeedTest(DiskSpeedTestID int, Tag char(10),

TextData char(8000), [TimeStamp] datetime)

GO

SET NOCOUNT ON

DECLARE @loop int

SET @loop = 0

WHILE (@loop < 10)

BEGIN -- while

      DBCC dropcleanbuffers

      DECLARE @StartTime datetime, @EndTime datetime, @i INT

      DECLARE @NoOfInserts int, @Buffer char(8000)

      SET @Buffer = REPLICATE ( 'x' , 8000 )

      SET @NoOfInserts = 25000

      SELECT @i = @NoOfInserts

      SET @StartTime = getdate()

      WHILE @i > 0

      BEGIN -- begin while

            INSERT INTO DiskSpeedTest

            SELECT @i , 'SQLUSA.com' , @Buffer , getdate()

            SELECT @i = @i - 1

      END -- while

      DBCC dropcleanbuffers

      SET @EndTime = getdate()

      SELECT 'Inserts per Second' = @NoOfInserts/datediff(ss, @StartTime, @EndTime)

      TRUNCATE TABLE DiskSpeedTest

      SET @loop=@loop+1

END -- end while

DROP TABLE DiskSpeedTest

GO