SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Missing Indexes on Temp tables


Missing Indexes on Temp tables

Author
Message
ganeshmuthuvelu
ganeshmuthuvelu
SSC Veteran
SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)

Group: General Forum Members
Points: 256 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?



TheSQLGuru
TheSQLGuru
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16738 Visits: 8597
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
ScottPletcher
ScottPletcher
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10567 Visits: 7227
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)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
ganeshmuthuvelu
ganeshmuthuvelu
SSC Veteran
SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)

Group: General Forum Members
Points: 256 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?



GilaMonster
GilaMonster
SSC Guru
SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)

Group: General Forum Members
Points: 113854 Visits: 45440
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

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


ganeshmuthuvelu
ganeshmuthuvelu
SSC Veteran
SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)

Group: General Forum Members
Points: 256 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?



GilaMonster
GilaMonster
SSC Guru
SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)

Group: General Forum Members
Points: 113854 Visits: 45440
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

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


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)

Group: General Forum Members
Points: 114204 Visits: 41367
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
TheSQLGuru
TheSQLGuru
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16738 Visits: 8597
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)

Group: General Forum Members
Points: 114204 Visits: 41367
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search