Performance improvement by indexing Temporary Tables

Oopps! Upgrade your browser pretty please.Oopps! Upgrade your browser pretty please.

As you probably already know, you can add indexes on temporary tables to improve the performance, but you can probably improve the performance even more. The index can be build with Fill factor 100 to increase the read performance, and you can also disable row and page locking to force Tablock on your index on the temporary table. This will remove the overhead of lock escalation and may improve the performance with approximate 25% for large temptables.

CREATE TABLE #tmp
(
ID INT
)
INSERT INTO #tmp SELECT * FROM sys.indexes

CREATE INDEX ix ON #tmp
(
ID
)
WITH (FILLFACTOR=100, ALLOW_PAGE_LOCKS=OFF, ALLOW_ROW_LOCKS=OFF)