[Looking for Charlie's main web site?]

Understanding SQL Server Statistics: A great, free, relatively brief ebook

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. book cover

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.)

Comments
As a follow-on to this post from a few months ago, just last week there was a blog post by inveterate SQL Server blogger. Pinal Dave, who had guest blogger Stuart Ainsworth offer some nice code to review all your tables in a DB and report on how outdated their statistics may be.

For more, see the post:

http://blog.sqlautho...

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".
# Posted By Charlie Arehart | 6/10/15 1:24 PM
Here's another useful resource related to management of SQL Server statistics, which may be of interest to readers of this post:

https://sqlperforman...

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.
# Posted By Charlie Arehart | 10/23/17 2:10 PM
Copyright ©2018 Charlie Arehart
Carehart Logo
BlogCFC was created by Raymond Camden. This blog is running version 5.005.
(Want to validate the html in this page?)

Managed Hosting Services provided by
Managed Dedicated Hosting