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


Create Statistics on Tables


Create Statistics on Tables

Author
Message
viresh29
viresh29
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 339
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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64145 Visits: 17974
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
viresh29
viresh29
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 339
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
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64145 Visits: 17974
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100117 Visits: 33014
This is an article that I wrote on statistics. Here's another 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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
viresh29
viresh29
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 339
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
Grant Fritchey
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100117 Visits: 33014
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GilaMonster
GilaMonster
SSC Guru
SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)

Group: General Forum Members
Points: 230284 Visits: 46344
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
viresh29
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 339
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
Grant Fritchey
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100117 Visits: 33014
Again, for PDW, I strongly advise you track down information from PragmaticWorks. They're the experts. Here's their web site.

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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
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