Adding tempdb files

Best Practices are -

1. Number of Physical tempdb files = number of processors
2. All tempdb files should have same initial size and unrestricted growth.

More best practices -

http://msdn.microsoft.com/en-us/library/ms175527.aspx

Syntax to add -

ALTER DATABASE tempdb
ADD FILE
(
NAME = tempdev5,
FILENAME = 'F:\SQLDatabases\tempdb\tempdev5.ndf',
SIZE = 14000MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 100MB
),
(
NAME = tempdev6,
FILENAME = 'F:\SQLDatabases\tempdb\tempdev6.ndf',
SIZE = 14000MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 100MB
),
(
NAME = tempdev7,
FILENAME = 'F:\SQLDatabases\tempdb\tempdev7.ndf',
SIZE = 14000MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 100MB
)

GO

Getting Performance Metrics

To get the information on File level fragmentation - Use following commands
Following command will analyze the file level fragmentation -

Defrag -a

Same defrag command can be used to defragment files.

To find missing statistics run below query - SELECT O.name AS [table name], I.rowmodctr AS [Rows Modified] FROM SYSOBJECTS o JOIN SYSINDEXES I ON O.id = I.id WHERE I.rowmodctr > 0 and O.xtype = 'U' ORDER BY I.rowmodctr DESC

Following the query to get report for Index defrag scan percentage from 10 to 99% for various indexes.

use
GO
Select [Object_id] AS ObjectId,
index_id AS IndexId,
avg_fragmentation_in_percent AS FragPercent,
partition_number AS PartitionNumber,
index_type_desc AS IndexType,
alloc_unit_type_desc AS AllocUnitType,
0 AS Online
FROM sys.dm_db_index_physical_stats (db_id(''), NULL, NULL, NULL, 'LIMITED')
WHERE
avg_fragmentation_in_percent > 10.0 AND
index_id > 0 and page_count > 32
ORDER BY ObjectId