Fix tempdb Performance issue with Best practices in MSSQL server Part 1@MicrosoftDeveloper

Introduction to TempDB
The life of a temporary object
TempDB Architecture
How queries affect TempDB
Monitoring TempDB
Weird quotes
Cat pictures
What is tempdb used for
Internal Objects
Joins and aggregations
Cursors
INSTEAD OF Triggers
Version Store
DML Triggers
Online Index Rebuilds
Snapshot Isolation
Query Operators
Sorts
Hashes
Spools
Temp Tables AND Table Variables
Well it depends…

As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.
Work tables are internal structures used for a number of purposes:
Service Broker
DBCC CheckDB
Cursors
Group By, Union, and some Order By statements
Spools
*Work tables are allocated to mixed extents
Work tables can be cached to improve performance
Work tables have a negative objectid
Why TempDB
The TempDB system database plays an important role in SQL Server performance tuning process. Because it is used as caching storage to store different types of user database objects and to store the system internal objects in order to speed up the SQL Server Database Engine related processes.

Due to this vital role that the TempDB system database plays in enhancing SQL Server instance overall performance, it is very important to apply the initial size, auto-growth and location best practices on the TempDB database data and log files. Applying these best practices in a way that fits your SQL Server instance workload will prevent expanding the TempDB database data and log files very frequently, taking into consideration that the file expands process is an expensive process in which SQL Server Engine will request extra space from the operating system, that will perform zeroing on that space before allocating it to SQL Server Engine. You can imagine the time and resources required for this allocation process.

If you already apply all best practices and an unexpected growth operation occurred, you can speed up the allocation process by using the database instant file initialization option, in which the operating system will trust the SQL Server service and allocate the required space without performing the time and resources wasting operation on the allocated space.

Monitoring parameters of Tempdb
Measuring tempdb performance| Perfmon Counters

Access Methods – Workfiles Created/Sec
Access Methods – Worktables Created/Sec
Access Methods – Worktables From Cache Base
Access Methods – Worktables From Cache Ratio
Cursor Manager By Type – Cursor Worktable Usage
TSQL Local Cursor
TSQL Global Cursor
API Cursor

Cursor Manager Total – Cursor Conversion Rate
General Statistics – Active Temp Tables
General Statistics – Temp Tables Creation Rate
General Statistics – Temp Tables For Destruction
Logical Disk - Avg Disk Bytes/Read
Logical Disk - Avg Disk Bytes/Write
Logical Disk - Avg Disk sec/Read
Logical Disk - Avg Disk sec/Write
Transactions - Free Space in TempDB(KB)
Transactions - Transactions
Transactions - Snapshot Transactions

Transactions - Version Cleanup Rate (KB/s)
Transactions - Version Generation Rate(KB/s)
Transactions - Version Store Size(KB)

SELECT
SUM (user_object_reserved_page_count)*8 as Usr_Obj_kb,
SUM (internal_object_reserved_page_count)*8 as Internal_Obj_kb,
SUM (version_store_reserved_page_count)*8 as Version_Store_kb,
SUM (unallocated_extent_page_count)*8 as Freespace_kb,
SUM (mixed_extent_page_count)*8 as MixedExtent_kb

High % suggests majority of space is taken up by version store. Look to curtail long running queries.