Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create Statistics on Tables Expand / Collapse
Author
Message
Posted Thursday, April 17, 2014 2:21 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 26, 2014 6:54 AM
Points: 28, Visits: 178
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
Post #1562851
Posted Thursday, April 17, 2014 2:32 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 12,946, Visits: 12,355
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 Moden's 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)
Post #1562852
Posted Thursday, April 17, 2014 2:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 26, 2014 6:54 AM
Points: 28, Visits: 178
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
Post #1562863
Posted Thursday, April 17, 2014 3:45 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 12,946, Visits: 12,355
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 Moden's 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)
Post #1562871
Posted Thursday, April 17, 2014 8:23 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,752, Visits: 28,150
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1562899
Posted Friday, April 18, 2014 8:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 26, 2014 6:54 AM
Points: 28, Visits: 178
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
Post #1563062
Posted Friday, April 18, 2014 12:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,752, Visits: 28,150
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1563128
Posted Friday, April 18, 2014 12:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 39,910, Visits: 36,250
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 2008, MVP
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

Post #1563132
Posted Friday, April 18, 2014 1:01 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 26, 2014 6:54 AM
Points: 28, Visits: 178
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
Post #1563144
Posted Friday, April 18, 2014 3:34 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,752, Visits: 28,150
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1563190
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse