Deep dive into statistics and why we need statistics

Statistics play an important role for the query optmizer to draw a optimizd execution plan , statistics are created against on indexed columns and and non indexed

column as default behaviour of sql server, but the point is how does sql server manage to update statistics when underlying data is updated are new data is inserted

lets see simple example when does sql server updates the statistics for indexed columns

SQL Server also updates the statistics

  • When a table has fewer than 500 rows and is increased by 500 or more rows
  • When a table has more than 500 rows and is increased by 500 rows + 20 percent of the number of rows

As a developer I was always curious to find when does sql server decides to for go Index seek or Index scan for a given table

DBCC SHOW_STATISTICS

Advertisements

One thought on “Deep dive into statistics and why we need statistics

  1. yard dumpster says:

    It’s truly a nice and useful piece of information. I am satisfied that you just shared this useful information with us. Please stay us up to date like this. Thank you for sharing.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s