CODEWITHSUNDEEP
sqlsql-serversql-server-2014
iefpw
iefpw

Reputation: 7042

How useful is database statistics

How useful is database statistics in terms of query performance?

I did a database engine tuning on SQL Server 2014 query and it said that I can have 79% improvement in query processing and the recommendations were create 5 statistics and 1 index.

In terms of performance, how would the 5 statistics improve the query performance in terms of percentage wise compared with 1 index in this case?

Upvotes: 3

Views: 1118

Answers (2)

Ahmed Mustafa
Ahmed Mustafa

Reputation: 29

In simple words Statistics will create summary of value-trend of those columns while index will actually create some data structure (normally B-tree) to avoid all value scan in its columns.

Regarding performance, usually all of your performance will come by index and the statistics will only provide help in avoiding blunders during sub-steps of query execution (execution plan).

Upvotes: 0

Michael Green
Michael Green

Reputation: 1491

Statistics are vital to query performance. Without them the optimiser is just guessing which permutation of pathways into the data will be most efficient. Every access to every table becomes no better than a table scan.

They are so important that SQL Server creates them on the fly for ad hoc queries. If you do a SELECT * FROM MyTable WHERE ThisColumn = 'SomeValue' it will create statistics on ThisColumn. As data is amended in your table the statistics eventually become "stale". At this point the optimiser tends to ignore them and start to produce really bad plans. Performance drops off a cliff. Queries which used to take seconds now take minutes.

As for these 5 statistics on these particular table(s) - I have no way of telling. Test it and see. Remember there are no free lunches, however. It takes CPU, memory and IO to create and maintain statistics. The more of them you have the more overhead they become; a lot like indexes.

Here' a good article which covers it.

Upvotes: 4

Related Questions