Can an Index improve tempdb high usage ?

Question

Can an Index improve tempdb high usage ?

Lou

SSCrazy

Points: 2929
More actions
June 4, 2022 at 12:37 am

#4044071

Hi All,
Imagine a scenario where a datawarehouse database is working and, at the same time, limited disk space in our tempdb system database.
When any process runs that loads data into memory, the tempdb alert reaches the maximum size.
Is it worthiness to add an index to reduce tempdb usage ?. But, of course, this is only a first step, as query optimization should be the primary target, but not possible for now.
All the tables look like they may receive the benefits of having indexes because none of them have one.
Thanks for your ideas
Regards,
June 4, 2022 at 5:22 am

This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101122 More actions · Answer 1

If none of your tables have indexes, you should start there, and fast!

First, review the missing index stats to see which column(s) should be used in the clustered index. That is by far the single most important index on any table for getting best overall performance.

As part of that, for larger tables, you need to see whether page compression is worthwhile. You can use proc sys.sp_estimate_data_compression_settings to do that. Normally it is worthwhile for nearly all large tables.

After deciding the clus index for each table, you can analyze if any nonclus index(es) is(are) needed, and create those.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 2

Lou wrote:

Hi All,
Imagine a scenario where a datawarehouse database is working and, at the same time, limited disk space in our tempdb system database.
When any process runs that loads data into memory, the tempdb alert reaches the maximum size.
Is it worthiness to add an index to reduce tempdb usage ?. But, of course, this is only a first step, as query optimization should be the primary target, but not possible for now.
All the tables look like they may receive the benefits of having indexes because none of them have one.
Thanks for your ideas
Regards,

Scott is absolutely correct. Also, how many GB in the largest table in your DW, how many files do you have in TempDB, and what are their sizes?

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18362 More actions · Answer 3

Does the DW process create temporary tables or is the tempdb usage just from sorts and joins?

Lou SSCrazy Points: 2929 More actions · Answer 4

Thanks for your responses,

We have more sorts and joins.

Thank you

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 5

Lou wrote:

Thanks for your responses,
We have more sorts and joins.
Thank you

A lot of that can be resolved by indexes in most cases. In some cases, the indexes won't actually be used and in other indexes just makes your accidental cross joins consume TempDB faster. 😀

Like I asked, how big is your largest table in GB and how big is your TempDB in GB?

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lou SSCrazy Points: 2929 More actions · Answer 6

Jeff,

My largest table has 20 million records and uses around 20 gigs but my tempdb maximum size is 192 gigs.

Regards,

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 7

The right indexes will help in some cases. But honestly, you'll have to evaluate each. It's going to be the same as dealing with the query tuning. It's all of a package really.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

jonau1 Grasshopper Points: 19 More actions · Answer 8

It depends.

If your query does a full scan of those tables because it needs all data from those tables then index are useless.

If the optimizer determines that full table scan is the most efficient way because your query requires most of data of those tables then again index are useless.

It's all depends on your query.

This reply was modified 2 years, 6 months ago by jonau1.
This reply was modified 2 years, 6 months ago by jonau1.

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 9

Lou wrote:

Jeff,
My largest table has 20 million records and uses around 20 gigs but my tempdb maximum size is 192 gigs.
Regards,

That's a rather huge TempDB considering the relatively small size of your largest table. Indexes can certainly help full heap scans but then you're also going to have to look at both the performance and the IO to further figure things out. I strongly suspect that you may have some accidental many-to-many joins that folks use DISTINCT or GROUP BY to overcome duplicate rows problems.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)