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:
- If you may prefer direct help, rather than digging around here/elsewhere or via comments, I can help via my consulting services
- See that for more on how I can help a) over the web, safely and securely, b) usually very quickly, c) teaching you as we go, and d) with satisfaction guaranteed