Statistics for temp table

Question

Statistics for temp table

Latheesh NK

SSCertifiable

Points: 6559
More actions
March 21, 2011 at 2:57 am

#234336

Can I create statistics for temp table explicitly?
One of my proc is showing Clustered index scan for a temp table with no column statistics inturm casuing a parallelism.
Any suggestions?

Viewing 15 posts - 1 through 15 (of 21 total)

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Yes, you can create stats manually, though an index should already have statistics on it.

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

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 2

If you're getting an index scan, you should try to understand why. Is there something in the code, such as a function on the column, that is causing the scan? is the table very small so scanning the pages is as fast as a seek? Usually understanding why something is occurring is the first step towards fixing it.

On parallelism, what's your system's cost threshold for parallelism? The default, 5, is way too low for most OLTP systems in my opinion.

"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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Grant Fritchey (3/21/2011)
If you're getting an index scan, you should try to understand why. Is there something in the code, such as a function on the column, that is causing the scan? is the table very small so scanning the pages is as fast as a seek? Usually understanding why something is occurring is the first step towards fixing it.
On parallelism, what's your system's cost threshold for parallelism? The default, 5, is way too low for most OLTP systems in my opinion.

What should it be set to?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

Ninja's_RGR'us (3/21/2011)
Grant Fritchey (3/21/2011)
If you're getting an index scan, you should try to understand why. Is there something in the code, such as a function on the column, that is causing the scan? is the table very small so scanning the pages is as fast as a seek? Usually understanding why something is occurring is the first step towards fixing it.
On parallelism, what's your system's cost threshold for parallelism? The default, 5, is way too low for most OLTP systems in my opinion.
What should it be set to?

It depends.

😀 Sorry, you were asking for that....

Seriously, it should be set to a value that, for your system, keeps queries that should be serial serial and lets those that benefit from parallelism parallel.

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

GilaMonster (3/21/2011)
Ninja's_RGR'us (3/21/2011)
Grant Fritchey (3/21/2011)
If you're getting an index scan, you should try to understand why. Is there something in the code, such as a function on the column, that is causing the scan? is the table very small so scanning the pages is as fast as a seek? Usually understanding why something is occurring is the first step towards fixing it.
On parallelism, what's your system's cost threshold for parallelism? The default, 5, is way too low for most OLTP systems in my opinion.
What should it be set to?
It depends.
😀 Sorry, you were asking for that....
Seriously, it should be set to a value that, for your system, keeps queries that should be serial serial and lets those that benefit from parallelism parallel.

Thanks Gail but I was talking to Grant ;-).

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 6

Ninja's_RGR'us (3/21/2011)
GilaMonster (3/21/2011)
Ninja's_RGR'us (3/21/2011)
Grant Fritchey (3/21/2011)
If you're getting an index scan, you should try to understand why. Is there something in the code, such as a function on the column, that is causing the scan? is the table very small so scanning the pages is as fast as a seek? Usually understanding why something is occurring is the first step towards fixing it.
On parallelism, what's your system's cost threshold for parallelism? The default, 5, is way too low for most OLTP systems in my opinion.
What should it be set to?
It depends.
😀 Sorry, you were asking for that....
Seriously, it should be set to a value that, for your system, keeps queries that should be serial serial and lets those that benefit from parallelism parallel.
Thanks Gail but I was talking to Grant ;-).

It depends.

"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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Yous guys are made for each other... you'd make a nice couple!! 😀

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 8

Ninja's_RGR'us (3/21/2011)
Grant Fritchey (3/21/2011)
If you're getting an index scan, you should try to understand why. Is there something in the code, such as a function on the column, that is causing the scan? is the table very small so scanning the pages is as fast as a seek? Usually understanding why something is occurring is the first step towards fixing it.
On parallelism, what's your system's cost threshold for parallelism? The default, 5, is way too low for most OLTP systems in my opinion.
What should it be set to?

Because of how the optimizer costs the plans, something higher. Personally, I set it to between 25 & 35 on my production OLTP systems. If a plan has a cost beyond that number, I'm fairly certain that parallelism might help.

But it's like Gail said, you have to test it. I just prefer setting that number really high to turning off parallelism completely, which a lot of people do.

"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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Thanks for the info.

I ask because I support Ms Dynamics Nav which as you may know is extremely rbar, so this info can come in handy in the future.

Thanks for the more detailed answer !

Latheesh NK SSCertifiable Points: 6559 More actions · Answer 10

DOP setting is 4. We do not wanna set to 1 as am not finding any other occurances. Even I find something, I will try to make not to happen by working on the index or code.(our's OLTP system)

Here the case is like the below:

1. Temp table has 5000 records. It shows no clumn stats in operator. (only for fetching from this table it shows parallelism) and followed by the below

2. The execution again shows a Split and filter for updating two indexes.(This I can see only in production. I am not able to see this in my development server. I do not have any clue....:hehe:

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

sqlchanakya (3/21/2011)
DOP setting is 4. We do not wanna set to 1 as am not finding any other occurances. Even I find something, I will try to make not to happen by working on the index or code.(our's OLTP system)
Here the case is like the below:
1. Temp table has 5000 records. It shows no clumn stats in operator. (only for fetching from this table it shows parallelism) and followed by the below
2. The execution again shows a Split and filter for updating two indexes.(This I can see only in production. I am not able to see this in my development server. I do not have any clue....:hehe:

we were talking about cost threshold, not max DOP.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 12

sqlchanakya (3/21/2011)
1. Temp table has 5000 records. It shows no clumn stats in operator. (only for fetching from this table it shows parallelism) and followed by the below
2. The execution again shows a Split and filter for updating two indexes.(This I can see only in production. I am not able to see this in my development server. I do not have any clue....:hehe:

Execution plan please, table definitions and any index definitions also.

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