Performance of TempDB

                      Recently I was facing issue related with performance.Same operation we was performing on one server,it was taking more time to complete as compare to other server.After dig  analysis I found root cause which was really interesting .I would like to share here on my blog .
                  Root cause was files mapping for tempdb .On new server we found tempdb is mapped to only one file .Old system tempdb is mapped to more than one file.I will explain how I got the information.
Please refer following query

SELECT name AS FileName,
       SIZE*1.0/128 AS FileSizeinMB,
            CASE max_size
                WHEN 0 THEN 'Autogrowth is off.'
                WHEN -1 THEN 'Autogrowth is on.'
                ELSE 'Log file will grow to a maximum size of 2 TB.'
            END Autogrowth,
            growth AS 'GrowthValue',
            'GrowthIncrement' = CASE
                                    WHEN growth = 0 THEN 'Size is fixed and will not grow.'
                                    WHEN growth > 0
                                         AND is_percent_growth = 0 THEN 'Growth value is in 8-KB pages.'
                                    ELSE 'Growth value is a percentage.'
                                END
FROM tempdb.sys.database_files;

These query is very important to understand -
tempdb.sys.database_files : will give us list of file which are mapped to tempdb.

So the solution which I used to improve performance - We have added more file to tempdb
e.g.
         Important thing  data file (.mdf)    and log file  (.ldf)  should not be present on same disk.It should be present on different disk also auto increment  option for size should be on.

ALTER DATABASE tempdb
ADD FILE (NAME = tempdev2, FILENAME = 'W:\tempdb2.mdf', SIZE = 256);
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev3, FILENAME = 'X:\tempdb3.mdf', SIZE = 256);
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev4, FILENAME = 'Y:\tempdb4.mdf', SIZE = 256);

For imformation :
  What exactly tempdb stores?

• Global (##temp) or local (#temp) temporary tables, temporary table indexes, temporary stored procedures, table variables, tables returned in table-valued functions or cursors.
• Database Engine objects to complete a query such as work tables to store intermediate results for spools or sorting from particular GROUP BY, ORDER BY, or UNION queries.
• Row versioning values for online index processes, Multiple Active Result Sets (MARS) sessions, AFTER triggers and index operations (SORT_IN_TEMPDB).
• DBCC CHECKDB work tables.
• Large object (varchar(max), nvarchar(max), varbinary(max) text, ntext, image, xml) data type variables and parameters.