concept statistic in category sql

This is an excerpt from Manning's book SQL Server DMVs in Action: Better Queries with Dynamic Management Views.
When a query is run, the optimizer inspects the relevant tables, row counts, constraints, indexes, and data statistics to determine a cost-effective way of fulfilling the query. Statistics describe the range and density of the data values for a given column. These are used to help determine the optimal path to the data. This information is used to create a plan that’s cached for reuse. In essence, statistics can greatly influence how the underlying data is queried.
When the data in the table changes, the statistics may become stale, and this may result in a less-efficient plan for the available data. To get around this, the statistics are typically updated automatically, and any necessary plans are recompiled and recached.
For tables with more than 500 rows, a 20% change in the underlying data is required before the statistics are automatically updated. For large tables, containing, for example, 10 million rows, 2 million changes would be necessary before the statistics are recalculated automatically. If you were to add 100,000 rows to this table on a daily basis, it would require 20 days before the statistics are updated; until that time you may be working with stale statistics and a suboptimal plan. Because of this, it’s often advisable to update the statistics more regularly using a scheduled job. I’ve experienced many occasions when queries have been running slowly, but they run almost instantaneously when the table’s statistics are updated.
In many ways, especially for larger tables on mature systems, I feel statistics are a critical element in the efficiency of database systems. When you run a query, the optimizer looks at the columns you join on, together with the columns involved with your WHERE clause. It looks at the column data’s statistics to determine the probabilities involved in retrieving data based on those column values. It then uses these statistical probabilities to determine whether an index should be used and how it should be used (for example, seek, lookup, or scan). As you can see, having up-to-date statistics is important. Later in the book (chapter 3, section 3.10, “Your statistics”), I’ll show you a SQL script to determine whether your statistics need to be refreshed.
Where possible, especially for important queries, you should consider creating statistics on the identified column. This will enable the optimizer to make better decisions about the underlying data used by your queries. For example, to create a statistics object named EmailAddressStats on a column named EmailAddress in a table named dbo.Contact, with 5% sampling, use the following command:
CREATE STATISTICS EmailAddressStats ON dbo.Contact (EmailAddress) WITH SAMPLE 5 PERCENTCreating statistics data takes time, especially on large tables. Because of this, we often obtain statistics based on a sample of data. In the example given here, 5% of the rows in the underlying table were examined to obtain the statistics for the column EmailAddress.
Up-to-date statistics are often vitally important to the performance of SQL queries, especially on larger tables. A script in a previous chapter detailed how to obtain the date the statistics were last updated along with how much the data has changed since the statistics were last taken. You can use the output from this script to automatically update the relevant statistics, rather than wait for SQL Server to automatically update the statistics after 20% of the data has changed. An example of this is given in chapter 10.

This is an excerpt from Manning's book SQL Server MVP Deep Dives.
The main benefit of declarative coding is its raw performance. For one thing, SQL Server has been heavily optimized toward processing declarative code. But also, the query optimizer—the SQL Server component that selects how to process each query—can use all the elements in your database (including indexes, constraints, and statistics on data distribution) to find the most efficient way to process your request, and even adapt the execution plan when indexes are added or statistics indicate a major change in data distribution.
Listing 7. Measuring the usefulness of indexes
USE [your_database]; GO IF OBJECT_ID('dbo.AB_MeasureIndexUsefulness', N'P') IS NOT NULL DROP PROCEDURE dbo.AB_MeasureIndexUsefulness; GO CREATE PROCEDURE dbo.AB_MeasureIndexUsefulness AS BEGIN SET NOCOUNT ON; SELECT 'These indexes have collected statistics for the past ' + RTRIM(uptime) + ' minutes (the last time SQL started)', sqlserver_start_time FROM AB_Utility.dbo.AB_Uptime(); WITH calced AS ( SELECT [object_id], index_id, reads = user_seeks + user_scans + user_lookups, writes = user_updates, perc = CONVERT(DECIMAL(10,2), user_updates * 100.0 / (user_seeks + user_scans + user_lookups + user_updates)) FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID() ) SELECT [status] = CASE WHEN reads = 0 AND writes = 0 THEN 'Consider dropping : not used at all' WHEN reads = 0 AND writes > 0 THEN 'Consider dropping : only writes' WHEN writes > reads THEN 'Consider dropping : more writes (' + RTRIM(perc) + '% of activity)' WHEN reads = writes THEN 'Reads and writes equal' END, [table] = AB_Utility.dbo.AB_GetTwoPartName( c.[object_id], DB_ID()), [index] = i.Name, c.reads, c.writes FROM calced AS c INNER JOIN sys.indexes AS i ON c.[object_id] = i.[object_id] AND c.index_id = i.index_id WHERE c.writes >= c.reads; END GO