Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

SQL Statistics

What command should I issue to update the Statistics on SQL tables ? Can it refresh automatically or I need to update it periodically ? What's the impact if statistics is not up-to-date ?

Tks
0
AXISHK
Asked:
AXISHK
  • 4
  • 2
  • 2
1 Solution
 
PortletPaulCommented:
0
 
PortletPaulCommented:
I'd like to suggest you look at this you may find it useful:

SQL Server Backup, Integrity Check, and Index and Statistics Maintenance

The SQL Server Maintenance Solution comprises scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, and SQL Server 2014. The solution is based on stored procedures, the sqlcmd utility, and SQL Server Agent jobs. I designed the solution for the most mission-critical environments, and it is used in many organizations around the world. Numerous SQL Server community experts recommend the SQL Server Maintenance Solution, which has been a Gold winner in the 2013, 2012, 2011, and 2010 SQL Server Magazine Awards. The SQL Server Maintenance Solution is free.
Ola Hallengren
http://ola.hallengren.com/

This conference video is a useful introduction as Ola explains his approach and answers questions: recommended video
0
 
Scott PletcherSenior DBACommented:
>> What command should I issue to update the Statistics on SQL tables ? <<
UPDATE STATISTICS


>> Can it refresh automatically or I need to update it periodically ? What's the impact if statistics is not up-to-date ? <<
Those are extraordinarily broad and general questions that can't be answered generally.  The correct answer can only be based on a specific case.  That is, each table's stats could have different answers for that q, depending on the specific usage of that specific table.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
AXISHKAuthor Commented:
what's the impact if the statistic is not up to date ?

Tks
0
 
PortletPaulCommented:
Good statistics allow the optimizer to accurately assess the cost of different query plans and then choose a high-quality plan.
Statistics Used by the Query Optimizer in Microsoft SQL Server 2008

Every Index has associated statistics, but statistics also exist for un-indexed columns (or sets of columns). If the statistics are "stale" (or "not fresh") then there is an increased chance of sub-optimal query plans being chosen.

In reality you need both fresh statistics and "tidy" indexes, but there are many factors to consider. I thoroughly recommend that video of Ola Hallengren - even if you are not using his scripts there are many tips and hints about stats and indexes in it.

There is also a myth about statistics I mind amusing;
Myth: You need to refresh statistics after rebuilding an Index
This isn't true.
If you do rebuild an index the relevant statistics are refreshed at the same time.

So, the 2 topics, Statistics and Indexes, are related but not the same.

btw: Scott Pletcher knows a big bundle more about SQL Server indexes than I do
0
 
Scott PletcherSenior DBACommented:
>>
There is also a myth about statistics I mind amusing;
 Myth: You need to refresh statistics after rebuilding an Index
This isn't true.
<<

Quite right!  In fact, you'd make SQL's stats less effective if you refreshed them after rebuilding an index.
0
 
AXISHKAuthor Commented:
Tks
0
 
PortletPaulCommented:
I do the work, Scott gets the points?
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now