Create Statistics on Tables

Question

Create Statistics on Tables

viresh29

Ten Centuries

Points: 1111
More actions
April 17, 2014 at 2:21 pm

#396624

Hello All,
I am new with statistics in sql server. Can anyone tell me how to create it and update it on tables.?
Thanks
Viresh
Thanks:-)
Viresh
--------------------------------------------------------------------------
“ The future belongs to those who are virile, to whom it is a pleasure to live, to create, to whet their intelligence on that of the others. ”
— Sir Henri Deterding

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

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

viresh29 (4/17/2014)
Hello All,
I am new with statistics in sql server. Can anyone tell me how to create it and update it on tables.?
Thanks
Viresh

These three links should prove useful.

http://bit.ly/1h7OqYJ

http://msdn.microsoft.com/en-us/library/ms188038.aspx

http://technet.microsoft.com/en-us/library/ms187348.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

viresh29 Ten Centuries Points: 1111 More actions · Answer 2

Thanks for your reply I have already visited those websites but I need more information regarding statistics.

If you have real example links that would be really appreciated.

Thanks

Viresh

Thanks:-)

Viresh
--------------------------------------------------------------------------
“ The future belongs to those who are virile, to whom it is a pleasure to live, to create, to whet their intelligence on that of the others. ”
— Sir Henri Deterding

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

viresh29 (4/17/2014)
Thanks for your reply I have already visited those websites but I need more information regarding statistics.
If you have real example links that would be really appreciated.
Thanks
Viresh

What kind of information do you want? Is there something specific you are trying to do?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 4

This is an article[/url] that I wrote on statistics. Here's another[/url] answering a series of common problems about statistics. If you have specific issues, let us know.

"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

viresh29 Ten Centuries Points: 1111 More actions · Answer 5

Thanks Grant for your reply.

Actually I am never created statistics on table but in my company someone told me to create and work on statistics it will help you a lot to speed up query.As I am working on PDW I don't know how to create it in PDW because in PDW we don't have primary and foreign keys structure. We have distributed and replicate structure within nodes in PDW. That's why I was collecting information on internet to create and update statistics on our tables.

I read your links and those really helped me a lot. I will look forward to works with statistics in PDW.

Thanks once again.

Thanks:-)

Viresh
--------------------------------------------------------------------------
“ The future belongs to those who are virile, to whom it is a pleasure to live, to create, to whet their intelligence on that of the others. ”
— Sir Henri Deterding

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

PDW might be a little different. I'm not sure. I've never worked with it. You might be better off tracking down someone from Pragmaticworks. They're the real PDW experts.

"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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

PDW is different and you do have to create stats manually, but that's specific to PDW and not something you typically do on normal SQL databases. You'll need to read up specifically on articles related to PDW for more info.

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

viresh29 Ten Centuries Points: 1111 More actions · Answer 8

Thanks Grant and Gail,

I have been reading on internet regarding statistics on tables in PDW. I got this information that I am pasting here.

---------------------------------------------------------------------

Ideally, you should create stats on all the join columns, group by, order by and restriction. SQL Server PDW does not automatically create and update statistics on the Control node for every occasion when SQL Server creates or updates statistics on the Compute nodes:

– This will create stat for all columns on all objects

select ‘create statistics ‘ + b.name + ‘ on dbo.’ + a.name + ‘ (‘ + b.name + ‘)’

from sys.tables a, sys.columns b

where a.object_id = b.object_id and not exists (

select null from sys.stats_columns where object_id in (select object_id from sys.stats_columns group by object_id having count(*)>=1)

and object_id = b.object_id and column_id = b.column_id)

order by a.name, b.column_id;

--------------------------------------------------------------------

But I don't know this will work or not.

Please let me know If you have any material that I can follow for PDW.

That would be really helpful to me.

Thanks:-)

Viresh
--------------------------------------------------------------------------
“ The future belongs to those who are virile, to whom it is a pleasure to live, to create, to whet their intelligence on that of the others. ”
— Sir Henri Deterding

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 9

Again, for PDW, I strongly advise you track down information from PragmaticWorks. They're the experts. Here's their web site.[/url]

"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