What does SQL Maintenance really do?

This one comes straight off the Microsoft Dynamics GP newsgroup (even the title given to this article).

Business Situation

During the course of a test upgrade the user came across a situation where records in a Smartlist table were not upgrading at all. In his attempts to have the upgrade bypass the problem, the user deleted the records from the SmartList table in question and the upgrade was able to succeed.

Nonetheless, the user attempted to run a few of the built-in SQL Maintenance routines found under Microsoft Dynamics GP > Maintenance > SQL and came back empty handed. This series of routines did not correct the damaged records and did nothing in aiding the upgrade, which brings us to the question on the subject:

What does SQL Maintenance really do?

This question can be answered by looking at what SQL Maintenance does not do. SQL Maintenance does not take any actions on table records, except of course, when you drop a table :-).















The routines found under SQL Maintenance are designed to perform preventative database maintenance and each option can be explained as follows:

Recompile

As a database is changed by such actions as adding indexes or changing data in indexed columns, the original query plans used to access its tables should be optimized again by recompiling them. This optimization happens automatically the first time a stored procedure is run after Microsoft SQL Server is restarted. It also occurs if an underlying table used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not happen until the next time the stored procedure is run after Microsoft SQL Server is restarted. In this situation, it can be useful to force the stored procedure to recompile the next time it executes

Another reason to force a stored procedure to recompile is to counteract, when necessary, the "parameter sniffing" behavior of stored procedure compilation. When SQL Server executes stored procedures, any parameter values used by the procedure when it compiles are included as part of generating the query plan. If these values represent the typical ones with which the procedure is called subsequently, then the stored procedure benefits from the query plan each time it compiles and executes. If not, performance may suffer.

When the Recompile option is selected in the SQL Maintenance window, GP forces the selected tables auto-stored procedures and triggers to recompile by executing the SQL Server sp_recompile statement.

Update Statistics

Update Statistics updates information about the distribution of key values for one or more statistics groups (collections) in the specified table or indexed view.

The Database Engine keeps statistics about the distribution of the key values in each index and uses these statistics to determine which index or indexes to use in query processing. Users can create statistics on nonindexed columns by using the CREATE STATISTICS statement. Query optimization depends on the accuracy of the distribution steps:

• If there is significant change in the key values in the index, rerun UPDATE STATISTICS on that index.

• If lots of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated by using the TRUNCATE TABLE statement and then repopulated, use UPDATE STATISTICS.

To see when the statistics were last updated, use the STATS_DATE function from SQL Server.
Drop Table

Removes one or more table definitions and all data, indexes, triggers, constraints, and permission specifications for those tables. Drop table will not remove the associated table views or auto-stored procedures, hence, it is recommended to run the Drop Auto Procedure option in conjunction with this option. The Drop Table option executes SQL Server DROP TABLE statement.

Create Table

The Create Table option creates a Dynamics GP table (that has been dropped with the Drop Table option or dropped from SQL Server) with the structure defined in the DYNAMICS.DIC dictionary file. When this option is executed, GP will check for the table existence and drop if it already exists. In addition, all table auto procedures (zDP_) will be dropped and recreated and security granted to the DYNGRP role.

Drop Auto Procedure

This option will remove all selected tables auto stored procedures. The zDP_ procedures aid Dynamics GP in retrieving, saving, updating, and deleting records from a table.

Create Auto Procedure

This option will create all selected tables auto stored procedures. If the procedures already exist, they are dropped and recreated.

Other Resources

Developing for Dynamics GP - "What do the zDP auto-generated stored procedures do?", by David Musgrave.

Developing for Dynamics GP - "What is Column desSPRkmhBBCreh?", by David Musgrave.

Microsoft Developer's Network (MSDN) - You can always search SQL Server Books Online for all topics outlined in this article.

Until next post!

MG.-
Mariano Gomez, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/