Don’t worry; this book isn’t really about math. I’m not going to trick you into doing any statistics homework or anything. When SQL Server refers to statistics, what does that mean?
Statistics, in this reference, means distribution statistics.
Great… so what does that mean? If you remember back in Chapters 7 and 8, we talked about indexes. Remember how we said that, for the B-Tree structure, there were different levels and how each level fed the next higher level? Distribution statistics is what defines those levels.
Distribution Statistics Explained
Let’s say that you have a table that is used to store user data. Almost everyone has a Users table in their database. When you’re in development, you may have five or ten test accounts that you use to test your functionality under different roles. And when you move to production, what happens? You all of a sudden get a huge influx of user accounts. But if your statistics are still showing that you have a very small number of records, and you actually have a very large number of records, the indexes would be outdated, and it would actually take longer to return the correct information. Distribution statistics allows SQL Server to both manually and automatically recompute and optimize the values for these indexes so that they return the correct information quickly.
When we discussed the B-Tree structure in Chapter 7, we had a pretty graphic that showed the various levels of the tree. Remember how they lined up nice and pretty? Well, what happens when records are moved around, deleted, updated, and inserted? Fragmentation of the physical files will occur, and the indexes will begin to drift. Does SQL Server do anything to automatically mitigate this? Yes. SQL Server will manually recompute these statistics as needed when some DML actions are performed (delete, update, and insert). This doesn’t take care of the fragmentation; that’s what our reorganize and rebuild tasks do, if you recall. But it does do a quick cleanup of the index.
Remember when you were younger and company would drop in unannounced to your crappy apartment? (Maybe it was just me.) How fast would you get it cleaned and how thorough of a job was it? The same principle applies here, believe it or not. It’s not a perfect job, it’s not 100% complete, or as thorough as a maintenance task designed for this exact task, but it’s enough to keep the indexes in shape for the next query.
Tip
The important thing to remember about statistics is that they constantly change.
Statistics won’t stay the same in a large database for very long. In order to provide the highest level of data integrity possible, we need to be able to return the requested data quickly and correctly. Recomputing and optimizing the statistics for the indexes and tables regularly goes a long way in helping achieve this goal.
Setting Up the Maintenance Task
To set up the task to update the statistics on the database, begin as normal. Right-click Maintenance Plans under the Management folder in SSMS, and choose Maintenance Plan Wizard, as shown in Figure 10-1.
Figure 10-1. Select Plan Properties
Change the default values to what you see in Figure 10-1 and then click the Change… button to set the schedule. You only want it to run once a day, so change the Occurs drop-down menu to Daily and click OK. Your schedule is now set to run at 12:00AM every day. Click Next to continue.
You are now shown a screen where you can choose the task you want to perform. Figure 10-2 details the tasks and shows the correct option for this area.
Figure 10-2. Select Maintenance Tasks
Choose the Update Statistics option, as shown in Figure 10-2, and notice the definition. Just as I summarized before, this task “ensures the query optimizer has up-to-date information about the distribution of data values in the tables.”
Click Next when you’re ready to move on. You will see what is shown in Figure 10-3.
Figure 10-3. Select Maintenance Task Order
Since we just have the one task in here, don’t worry about it and click Next.
You are then shown the default screen to define the task. It should look like Figure 10-4.
Figure 10-4. Define Update Statistics Task
This is where we want to define the parameters of our task. Choose your database from the drop-down menu, with Object set to Tables and Views.
If you recall from Chapters 6 and 7, leaving the Object set to Tables and Views lets us update the statistics on all available objects in the database. You are free to choose just Tables or View if you would like, but you will need to go in and change this setting if you ever want to make any changes to the maintenance plan later.
There are two other options under there:
Updateallows you to choose between All, Column, or Index statistics. Keep the default at All.
Scan typelets you define either a full scan (recommended) or you can also choose a sample size to run the scan under. What this means is that SQL Server takes a sample size of the specified number and extrapolates the extent of the scan from there. It doesn’t do a full scan, but it gets sort of close. The full scan option scans the entire catalog instead of just a sample of the data, and then updates the statistics based on the results of that scan.
I recommend leaving these two options set to the default values, as shown in Figure 10-5.
Figure 10-5. Define Update Statistics Task (completed)
Click Next when you are ready to move on.
Here is the old familiar interface where we define our reporting options. Set it up as you see in Figure 10-6 and click Next to move on.
Figure 10-6. Select Report Options
Now you see the summary screen shown in Figure 10-7. Review this, as always, just to be sure that you didn’t miss something.
Figure 10-7. Complete the Wizard
When everything is copacetic, click Finish and wait with baited breath for what is shown in Figure 10-8 to appear.
Figure 10-8. Maintenance Plan Wizard Progress
Once again, another maintenance plan is all set up.
Make sure you update the Job, like in previous chapters. I updated the name of mine to Update Statistics. Your Jobs folder should now look like Figure 10-9.
Figure 10-9. SQL Server Agent Jobs
That’s a lot of jobs in there! Don’t worry, I know this looks daunting right now, but I promise that this will all start to make more sense in the very near future.
Your Maintenance Plans folder should also look like Figure 10-10.
Figure 10-10. Maintenance Plans
Summary
Let’s take a quick look at what this chapter was all about.
We learned how a database uses statistics to recompute what it “knows” about a database.
We saw how, when properly managed, recomputing the statistics on a database works in conjunction with the rebuilding and reorganizing of the indexes to provide a heightened level of data integrity and query completion time.
We learned how to set up the task and went through the specifics of the task portions.
Great job! This was a quick chapter, but still very important. We are starting to wind down now, so keep going and finish up your maintenance plan learning.