Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Missing Indexes on Temp tables Expand / Collapse
Author
Message
Posted Saturday, December 15, 2012 8:11 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 8:48 AM
Points: 90, Visits: 305
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?



Post #1396896
Posted Saturday, December 15, 2012 4:19 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:19 AM
Points: 4,320, Visits: 6,113
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 at GMail
Post #1396917
Posted Saturday, December 15, 2012 5:01 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 1,972, Visits: 2,918
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1396924
Posted Sunday, December 16, 2012 9:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 8:48 AM
Points: 90, Visits: 305
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?



Post #1397000
Posted Sunday, December 16, 2012 9:55 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 42,470, Visits: 35,541
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1397002
Posted Sunday, December 16, 2012 10:04 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 8:48 AM
Points: 90, Visits: 305
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?



Post #1397006
Posted Sunday, December 16, 2012 10:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 42,470, Visits: 35,541
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1397008
Posted Sunday, December 16, 2012 11:11 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 36,781, Visits: 31,237
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1397011
Posted Sunday, December 16, 2012 12:21 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:19 AM
Points: 4,320, Visits: 6,113
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 at GMail
Post #1397016
Posted Sunday, December 16, 2012 1:44 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 36,781, Visits: 31,237
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1397019
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse