Optimized query statistics make the queries that run on your database perform faster. The query optimizer runs automatically, so in most cases the query plan, as shown in Figure 9.23, will already contain the most optimal plan. The statistics used to optimize queries are used during the compilation of the query prior to execution. When the same queries are executed multiple times, the same compiled query using the same statistics is used to complete the operation. If the standard interval between the automated update of statistics is not keeping up with the speed of change on the table or view, then you can manually update the statistics. When the query statistics are updated, whether manually or automatically, the queries must be recompiled to reuse the new statistics. This has an impact on performance, so try not to update too often.
To manually update the statistics used to build the query plan and statistics, you can use the UPDATE STATISTICS SQL command or a system stored procedure named sp_updatestats. The following example uses UPDATE STATISTICS to perform this update on the [dbo].[READING] table:
UPDATE STATISTICS dbo.READING;
GO
FIGURE 9.23 Monitoring and update statistics execution plan
To view the statistics, you can use the following DBCC command:
DBCC SHOW_STATISTICS (“dbo.READING”, _WA_Sys_00000004_671F4F74);
Figure 9.24 shows the results.
FIGURE 9.24 Monitoring and update statistics view statistics
Notice that both of these commands, UPDATE and SHOW_STATISTICS, were executed using Microsoft SQL Server Management Studio against the dedicated SQL pool. Keep in mind that the global endpoint is available for connection from any supported client utility. All SQL Server features are not built into all of the different database management tools, so if you cannot perform a task from one tool, try it from another. Lastly, this kind of activity would best be analyzed and executed by a DBA who has experience with this kind of activity. This is a very specialized skill set and requires some significant experience to view the output of these commands, determine actions based on the numbers, and manage the impact of modifying them. As with security‐ and networking‐related issues, it is always a good idea to consult a specialist.
