How do I update STATISTICS on a column

Question

Post reply

How do I update STATISTICS on a column

mw_sql_developer

SSCoach

Points: 19445
More actions
January 11, 2016 at 2:53 pm

#307588

Hello
I have an index on a certain column in a table A.
Does any one knows how to tell SQL Server to update the statistics on the column ?
Please 😛

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

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

JeremyE SSCoach Points: 16201 More actions · Answer 1

https://msdn.microsoft.com/en-us/library/ms187348.aspx

mw_sql_developer SSCoach Points: 19445 More actions · Answer 2

mw_sql_developer

SSCoach

Points: 19445

January 11, 2016 at 3:27 pm

#1851181

USE dbx;

GO

UPDATE STATISTICS tabA.ColB;

GO

😛

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 3

Check out the following link may be this will help you: https://technet.microsoft.com/en-us/library/aa260645(v=sql.80).aspx

MadAdmin SSChampion Points: 11261 More actions · Answer 4

mw112009 (1/11/2016)
UPDATE STATISTICS tabA.ColB;
GO

The above is the syntax for updating all stats on a table like below

update statistics schema.table

If you want to update statistics it is

update statistics schema.table.STATISTICSNAME

So create statistics which contains the column and give it a name, then use that name when updating statistics.

So I think the problem may be that you want to find the statistics name that contains the column name that needs to be updated?

Or perhaps the index that has its first key as the column name?

You can do this with below that I cobbled together quick

select o.[object_id] as [ID],SCH.name, o.name as [Table], c.name as [Column],

s.name as statsname,stats_date(o.object_id,i.indid),rowcnt,

rowmodctr --changes since last statsupdate

from sys.objects o

inner join sys.schemas SCH on SCH.schema_id = o.schema_id

inner join sys.columns c on o.[object_id] = c.[object_id]

left join sys.stats s on s.[object_id] = o.[object_id] and s.has_filter = 0

left join sys.stats_columns sc on s.stats_id = sc.stats_id and s.[object_id] = sc.[object_id] and sc.column_id = c.column_id

left join sysindexes I on o.object_id = I.id and i.name = S.name

where sch.name = 'Myschema' and o.name='mytable' and c.name = 'mycolumn'

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

mw_sql_developer SSCoach Points: 19445 More actions · Answer 5

What I need is to update statistics on just one column. That column is used by an index.

MadAdmin SSChampion Points: 11261 More actions · Answer 6

mw112009 (1/12/2016)
What I need is to update statistics on just one column. That column is used by an index.

I am assuming you got the index name from the query I attached.

So then you do this.

update statistics schemaname.tablename.indexname with(this could be sampling or fullscan.)

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

mw_sql_developer SSCoach Points: 19445 More actions · Answer 7

Cant I just use the following:

UPDATE STATISTICS tabA.ColB;

GO

mw_sql_developer SSCoach Points: 19445 More actions · Answer 8

Sorry,

I got that (previous ) wrong.

Done!

I used the following:

UPDATE STATISTICS table_name

MadAdmin SSChampion Points: 11261 More actions · Answer 9

ok, so you didnt want to update a particular statistic sniper rifle style.

That command will rebuild all statistics on the table, i.e. shotgun.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]