Indexes are good, except when they aren’t. Everything that you do in SQL Server has trade offs. Usually those tradeoffs are easy to see, unless they aren’t.
Indexes are generally a good thing. They make performance of queries within the database engine go faster, often a lot faster. Indexes on temp tables are also usually a good thing, unless you built them incorrectly then as one client of mine just found out things can get very bad very quickly.
This client had just upgraded from SQL Server 2008 R2 to SQL Server 2014 and the Monday after we did the upgrade (the first full business day running on SQL Server 2014) things fell apart, fast. We saw huge amounts of locking and waits on tempdb. The waits were reported as PAGELATCH_IO waits, but the disks according to perfmon had a 1-2ms response time. So it was something happening in memory. All the sessions were locking on a specific page in tempdb, 2:1:128. I looked at the page with DBCC PAGE and found that it was part of sysobjvalues. This table is used in storing information about temporary objects.
With the help of a Microsoft developer who looked through some minidumps from the SQL Server he was able to identify the code pattern that was causing the problem. The root of the problem was that temp tables weren’t being properly cached in the tempdb database. The reason for this is that the tables were being created without any indexes then a clustered index was being added to the temp table after the fact. In this case the code looked something like this:
CREATE TABLE #t1 (c1 int)
CREATE UNIQUE CLUSTERED INDEX I ON #t (c1) WITH IGNORE_DUP_KEY
INSERT INTO #t1 (c1) SELECT * FROM @Something
We were able to resolve the issue by removing the clustered index and making the column c1 a primary key, then doing a distinct insert into the table. Long term the developers are going to clean up the data within the .NET layer so that we know that distinct values are coming into the table so that we can remove the distinct. The new temporary code looks like this:
CREATE TABLE #t1 (c1 int primary key, c2 int, c2 int)
INSERT INTO #t1 (c1) SELECT DISTINCT c1 FROM @Something
Finding the problem code was pretty easy. The Microsoft developer was able to give me the names of a couple of stored procedures. The rest I was able to find by searching through sys.sql_modules looking for anything with “%WITH%IGNORE_DUP_KEY%” or “%CREATE%INDEX%” in the object code. After fixing these the system started performing MUCH, MUCH better.