Understanding SQL Server Statistics: A great, free, relatively brief ebook
Note: This blog post is from 2015. Some content may be outdated--though not necessarily. Same with links and subsequent comments from myself or others. Corrections are welcome, in the comments. And I may revise the content as necessary.If you want to understand the important concept of SQL Server statistics (a mechanism within SQL Server which tracks metadata about your data, and which can significantly affect query performance if not managed well), I think you can do no better than than to spend an hour or so reading the free 40-page e-book, SQL Server Statistics, written by Holger Schmeling and offered by Red-gate.
The book is a quick read, and really well done, including a discussion of what statistics are, why they're important, how to diagnose trouble with them, and how to use the SSMS interface and/or SQL statements/commands to better understand them.
There are plenty of screenshots and it's written in a tutorial manner. And while it's from 2010 and covers only up to SQL Server 2008, most of the concepts apply just as well to SS 2012 and beyond, and of course many organizations are still running on SS 2008 or even earlier!
Most important, Schmeling explains why it's important that you take responsibility to both create and maintain statistics (rather than leave it as something that "the database" should handle. And if you are leaving it as something "the db should handle", you can use this to make sure that person is doing their job with respect to statistics.)
When I help people troubleshoot CF servers, it's not unusual to find that the root cause problem is in the database itself. And sometimes the problem is not in your code, nor in your data itself, nor in table locking or index problems, which are things many experienced folks may presume are the issue, but the problem is often simply in outdated statistics. And it an be such an easy problem to identify and resolve, as the book discusses.
Schmeling's e-book is just a great resource that I am happy to be able to point folks to when I suggest statistics may be their problem, and they want to learn to understand things better.
Indeed, if you are responsible for SQL Server, whether as a developer or DBA, you would do yourself a favor to be familiar with all the books available (in print or as free ebooks) in the library of RedGate SQL Server books, which has sections for both developers and DBAs.
(As a closing thought, while some have long asserted that DB problems are the primary cause of CF troubles, I really don't find that to be the case at all. There are just as often many other explanations, from poor configuration of CF, to poor configuration of the database or web server, to unexpected traffic to your web application, to slow communications out of CF to other servers, to unexpected applications running on the servers causing trouble, and much more. If you face CF server troubles and don't find the answer among the many resources I offer here on carehart.org, reach out to me for my consulting services, and I can nearly always help you find and resolve the problem, while helping you understand things in the process.)
For more content like this from Charlie Arehart:
Need more help with problems?
- Signup to get his blog posts by email:
- Follow his blog RSS feed
- View the rest of his blog posts
- View his blog posts on the Adobe CF portal
- If you may prefer direct help, rather than digging around here/elsewhere or via comments, he can help via his online consulting services
- See that page for more on how he can help a) over the web, safely and securely, b) usually very quickly, c) teaching you along the way, and d) with satisfaction guaranteed
For more, see the post:
FWIW, in the ebook above, the author did offer a small set of SQL that would report the state of ALL statistics for a given table. Stuart's code in the blog entry tries to make it easier to evaluate things, as it shows a) info on all dbs, not just a selected one and b) it shows just the oldest stat for each DB, as well as an assessment of what percent of others are not recent.
Both those tweaks help you more quickly assess the state of stats in your server, but if you want the full stat details, see the query on p13 (of the edition available when I posted the entry), under the section, "Obtaining information about statistics using Transact-SQL".
It covers the evolution of SQL Server statistics update processes and features from SQL Server 7, forward. Interesting to get that perspective, as sometimes people operate from old info and don't realize there was some significant change years ago that should change their assumptions about an aspect of the topic.