December 15, 2012 at 8:11 am
The DMV dm_db_missing_index_details has suggestions on missing indexes on permanent tables but I need some suggestions on ways to identify the missing indexes on temp tables. For example, a temp table say "#dynamic" is created at run time and many dynamic columns are added to it by the end user. The end user also creates custom procedures that gets executed on the data available in "#dynamic" table. Inside these custom procedures, if a index is needed on "#dynamic.dynamic_colA" and the user did not create the index, the performance of the custom procedure obviously slows down.
I am trying to determine if there's any way I could use the " dm_db_missing_index_details " or some other DMV to capture such missing index suggestions and present it to the user so that the end user can add those indexes on the "#dynamic" table for better performance. My plan will be to use the DMV (or something similar) to run in the same session the "#dynamic" table is created to capture the missing index information but wondering if there's anything that SQL Server 2008 R2 provides for my needs on temporary tables .
Any suggestions?
December 15, 2012 at 4:19 pm
I can count on 1 hand the number of times in 15+ years of SQL Server consulting that I have come across client actions involving temp tables where an index was helpful. I can't count the number of times I have made queries FASTER by REMOVING indexes that clients had put on temp tables. If you are not iteratively hitting the temp table quite a few times the cost of building the index is almost never worth it.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 15, 2012 at 5:01 pm
I disagree completely.
I've repeatedly gotten huge performance gains sometimes by indexing temp tables properly. They are tables like any other table, after all. For large tables, I've seen response time go from minutes to sub-second just from proper indexing.
However, discovering which temp table need indexes, and what those indexes should be, can be very difficult, as you've noticed yourself.
I suggest scanning the stored procs (or other code) looking for temp tables involved in joins. Address those first, including any other WHERE conditions on the joined tables.
Then go back and look at WHERE clauses on temp tables that aren't joined.
AFAIK, SQL itself does not keep missing index stats on temp tables at all.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 16, 2012 at 9:34 am
Indexing on large temp. tables is a must.
I want to be absolutely sure that there is no way in SQL Server 2008 R2 to find out the missing indexes on such temp. tables while they are still alive in the session that created them.
Because these tables are user driven and not created by the application, it is not possible to create the necessary indexes before hand unless the user creates it. It therefore needs to be discovered and suggested to the user.
Any ideas or am out of luck?
December 16, 2012 at 9:55 am
Get the user to run then and send you the execution plans. Analyse and see if the index suggestion in there (if any) is sensible. Otherwise analyse the plan and see if indexes will help.
Oh, and I've had major performance gains from adding indexes to temp tables as well as smaller performance gains from removing useless indexes. Much as with physical tables, useless indexes are useless and will degrade performance, useful indexes can give massive improvements. It's not a case of 'Thou shalt always add indexes to temp tables.', nor is it a case of 'Thou shalt never add indexes to temp tables.'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 16, 2012 at 10:04 am
But the plan is to let the application suggest the indexes and not come to me.
Can anyone confirm that SQL Server 2008 R2 provides no way to find these out?
December 16, 2012 at 10:35 am
ganeshmuthuvelu (12/16/2012)
Can anyone confirm that SQL Server 2008 R2 provides no way to find these out?
The missing index information should be in the execution plan.
Even with normal tables, you can't just automatically create whatever the missing index DMVs suggest, unless you want a huge amount of redundant and unnecessary indexes. For temp tables you just need the additional step of saving the exec plan and then checking the missing index info from there (or analysing the plan if the info is not there) instead of the missing index DMVs.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 16, 2012 at 11:11 am
I guess I'd have to say, "It Depends". 😉
I don't know how others use Temp Tables but I follow a cardinal rule there. Only populate the table with what is absolutely necessary. What that means in most cases for me is that the time spent adding an index is usually time wasted because any query I write against the Temp Table, even when joined to other temp tables, will cause either and index scan or a table scan. While the index scan is usually more efficient than a table scan, it will, many times, cost more in resource usage and duration to build the index than the benefits the index returns. This is especially true if the Temp Table was built using the very high performance SELECT/INTO method.
There are always exceptions, of course, but, like I said, usually not for me because of the way that I build Temp Tables.
To wit, I have to say that I agree with Mr. Boles on this one. I've found that removing indexes from Temp Tables helps a lot but not necessarily just because the indexes were removed. It's normally because people abuse Temp Tables by storing too much of the wrong stuff in them and then have to use indexes to compensate. Whittling the Temp Table down to contain only what's necessary frequently (usually, for me) means that you don't actually need indexes for performance.
Heh... for those getting ready to jump all over those comments, remember that your milage may vary depending on what you actually put into the Temp Table. Sometimes it might even be better to have two index-less Temp Tables containing similar but different information than it is to try to jam it all into one. "It Depends".
Of course, if your query is going to use several hundred MB of Temp DB space and will be executed concurrently by many users, you might be doing something wrong to begin with. "It Depends". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
December 16, 2012 at 12:21 pm
My statement about making "queries" faster is about the ENTIRE process. The act of populating a temp table with an index already on the temp table before data is added requires not-insignificant work to populate the index(es) as the data is added. This is especially true of the clustered PK scenario. And adding an index after data population requires a full scan of the temp table and the actual effort required to create and store the index. The VAST majority of the times I have seen this done the table is hit ONCE in a join or some other query type and the cumulative cost/duration/effort of adding the index made the entire process run slower.
Gonna stick by my statements here - in my experience most indexes on temp tables do not make for overall faster/more efficient executions (outside of my exception about iterative hits on said temp table where you get an index seek or other significant benefit).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 16, 2012 at 1:44 pm
TheSQLGuru (12/16/2012)
My statement about making "queries" faster is about the ENTIRE process. The act of populating a temp table with an index already on the temp table before data is added requires not-insignificant work to populate the index(es) as the data is added. This is especially true of the clustered PK scenario. And adding an index after data population requires a full scan of the temp table and the actual effort required to create and store the index. The VAST majority of the times I have seen this done the table is hit ONCE in a join or some other query type and the cumulative cost/duration/effort of adding the index made the entire process run slower.Gonna stick by my statements here - in my experience most indexes on temp tables do not make for overall faster/more efficient executions (outside of my exception about iterative hits on said temp table where you get an index seek or other significant benefit).
To make a much longer story shorter, that's been my experience, as well. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
December 17, 2012 at 8:34 am
TheSQLGuru (12/16/2012)
My statement about making "queries" faster is about the ENTIRE process. The act of populating a temp table with an index already on the temp table before data is added requires not-insignificant work to populate the index(es) as the data is added. This is especially true of the clustered PK scenario. And adding an index after data population requires a full scan of the temp table and the actual effort required to create and store the index. The VAST majority of the times I have seen this done the table is hit ONCE in a join or some other query type and the cumulative cost/duration/effort of adding the index made the entire process run slower.Gonna stick by my statements here - in my experience most indexes on temp tables do not make for overall faster/more efficient executions (outside of my exception about iterative hits on said temp table where you get an index seek or other significant benefit).
If the data is only being read/used once, why is it being stored in tempdb at all??
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 17, 2012 at 9:06 am
ScottPletcher (12/17/2012)
TheSQLGuru (12/16/2012)
My statement about making "queries" faster is about the ENTIRE process. The act of populating a temp table with an index already on the temp table before data is added requires not-insignificant work to populate the index(es) as the data is added. This is especially true of the clustered PK scenario. And adding an index after data population requires a full scan of the temp table and the actual effort required to create and store the index. The VAST majority of the times I have seen this done the table is hit ONCE in a join or some other query type and the cumulative cost/duration/effort of adding the index made the entire process run slower.Gonna stick by my statements here - in my experience most indexes on temp tables do not make for overall faster/more efficient executions (outside of my exception about iterative hits on said temp table where you get an index seek or other significant benefit).
If the data is only being read/used once, why is it being stored in tempdb at all??
I've seen plenty of situations where dumping data into a temp table, then using the temp table, is more efficient than complex sub-queries.
You only use the data once, but it splits up the execution of the final operation into smaller chunks, and the optimizer does a much better job on those than on a single mother-of-all-queries type operation.
Basically, a temp table pre-populated, can often outperform complex CTEs, cross-applies, etc., simply by reducing the complexity the optimizer has to deal with.
In those cases, a reasonable clustered index on the temp table can sometimes help, but also sometimes not.
I've gone both ways on temp tables. Some need indexing. Some need a lack of indexing.
For example, I recently wrote an ETL process that includes very complex business rules. The main query has 18 sub-queries, using Cross or Outer Apply, rooted on data in a single table. Two of the sub-queries were so complex that I moved them to temp tables. Just splitting those 2 out of the main query, took total execution time from over an hour to under a minute. Since the process is supposed to run hourly, that's a critical improvement. But they're both technically "temp heaps", since they're completely unindexed. Tried adding a clustered index to each, but it didn't improve anything at all.
On the other side, I once wrote a very complex proc that compared an order to a complex set of "business suggestions", based on heuristic patterns. To simplify the code, and to deal with what would otherwise have been very aggressive locking issues, I used temp tables with some serious indexing, instead of the primary tables. This allowed the order data to be compared to "the rules" as they were at the time the check was initiated, without having to lock the whole database. The indexes used in that process were critical to the speed and efficiency of the whole thing. Lots of indexes on dozens of temp tables. Took the proc from several minutes total run-time, down to about 5 seconds, and got rid of numerous lock/block/deadlock issues at the same time.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 18, 2012 at 7:31 am
I'll second what GSquared just said.
Sometimes, the optimizer needs a little help. Sometimes that help is to just very quickly isolate a much smaller subset in a temp table (which the optimizer sometimes does on its own, BTW. They're called "Work" tables and you can see them with SET STATISTICS IO ON). It can make all the difference in the world.
Probably the greatest example that I can personally remember was with a year-end query that some folks had written at a previous company. It was so resource intensive that they actually asked me to build a separate instance of the database to run the bloody query because it would paralyze the server for a good 45 minutes.
I got it down to 8 seconds that made only a "pip" on CPU usage by isolating one part of the query to a Temp Table and then joining on that Temp Table. To be sure, the data in the Temp Table was only used once.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 18, 2012 at 8:18 am
I am the OP here. It appears that the thread became a discsussion for whether indexes are needed on temp tables or not. I can say for sure that the answer is "yes, it is needed", particularly when large volume of data is on the temp tables and joins are done.
Now, if you all can be kind enough to read my original question and help me find a way to make SQL Server find out such missing indexes that would help performance, it will be helpful. I have summaized it again below.
1) Let's say "#t1" is created in session id 100 and #t1 has no indexes on any columns and has 100,000 rows in it.
2) The end user has his "own, custom" stored procedure that works on the data in the #t1 and joins #t1 with another perm. table say "MainData".
3) The temp. table "#t1" and the stored procedure are created by the end user.
4) Whereas the perm. table "MainData" (part of the application) is properly indexed, the "OrderID" column in "#t1" is not indexed which is used in the join with "MainData" and this causes slow performance. If the #t1 is indexed on "OrderID", the performance is great.
5) Now, at the end of the SP execution or before, I would like SQL Server to say, index on "OrderID" column in "#t1" is "recommended".
6) Again, please do note that the temp. table "#t1" is dynamic in nature, and the application will have no knowledge about this and that's the reason the index on "OrderId" can be "pre" created. Therefore, I want the app to suggest to the user that index on "OrderID" column in "#t1" is recommended.
I can make the application run some queries at the end (in the same session id 100 where the #t1 is created) to make the index suggestions to the user.
Any ideas?
December 18, 2012 at 8:26 am
I'll just repeat what I said earlier
Even with normal tables, you can't just automatically create whatever the missing index DMVs suggest, unless you want a huge amount of redundant and unnecessary indexes. For temp tables you just need the additional step of saving the exec plan and then checking the missing index info from there (or analysing the plan if the info is not there) instead of the missing index DMVs.
In other words, any form of automatic index creation, automatic index recommendations without testing, is a very bad idea and is more likely to lead to reduced performance overall.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply