SQL Server Contextual Series three query process tracking

When we read and write database files, these process activities trigger some run-time events when a file is read, written, or an error occurs. From a user's point of view, some of the time will be concerned about these events, especially our debugging, audit, service maintenance. For example, when database errors occur, column data is updated, CPU usage is high, and so on, tracking these states is very useful.

This section covers key areas of the event system: Triggers, Event notifier, change tracking, SQL Trace, extended events, and more. These events have a similar purpose: to respond to or log events that occur. But each of these events works in a different way. Basics: Triggers and event notifierThere are many triggers, and data manipulation Language (DML) triggers is defined to fire an action when data is inserted and updated; Defined when an action is fired when a server or database-level activity occurs, such as logging in or deleting a table. A DML trigger can fire an event in place of a trigger, or when the event is complete but not committed. DDL triggers can only be configured as events have been completed but things have not been committed. The event notifier and DDL trigger are similar, but the important point is that it does not require any transaction, as a result to support non-event events. For example, a user disconnects from a database connection. Change Tracking   Let's start with an example: some systems need to cache the database locally, and keep an eye on whether the database data is updated, keeping the cache and the database in sync at all times. This situation is more implemented by using triggers or timestamps. But this design can often lead to problems. For example, timestamps are often invalidated if the timestamp column is counted when data is inserted instead of at commit time. The problem with this is that if a big data is plugged in while other small data is plugged in, the big data commit is slower than the small data commit. Therefore, the natural ordering of timestamps is affected.    Triggers can solve some problems, but it also increases the time to commit things. Change Tancking is designed to track data changes in single-or multi-table. Make it easy for users to change the order in which they are triggered.    It acts as part of a synchronization tracking change occurs. Change tracking is based on the idea of baseline work. The data client first requests that the current state of all rows in the tracking table also get the version number of each row. The baseline version number is the current largest version number known to the system.    It will be synchronized until the next Synchronizer is requested, and when this request occurs, the baseline version number is sent back to the change tracking system by determining which lines of the system have been modified since the first request, by comparison to the version of the trace table fission and the version of the baseline table side. SQL Server 2008 includes two similar techniques to support synchronization: Change tracking and change Data Capture. Change tracking supports offline applications and also supports certain connected apps as well as others that do not require real-time notifications when data is updated. Change Tracking Configuration     There are two configuration levels for changing the tracking configuration, one for the database configuration level and the other for the table configuration level.     1) database configuration level. You can use the SQL command ALTER dtabase to modify the change tracking configuration, the database default is to start the tracking configuration, we can execute the following command to start the trace:    ALTER database heavi_case1    Set change_tracking = on;    Description: After executing this statement, the two changes are tracked once the table-level trace is started: The first one, a hidden system table will be used to track the target database; A cleanup task will begin to evaluate whether the remaining row data is left in the related table.     a) hidden system tables     This hidden table is called the commit table, which generates a row of data for each database submission that appears in a change tracking. When something happens, an equal amount of something is marked, which contains a unique identifier, a sequence identifier (Commit Sequence NUMBER,CSN). The CSN is inserted into the commit table along with the thing markers, date sequence information, start time, and other data. This table is primarily used to provide data to change tracking (Tracking) and to help make decisions about which changes will be synchronized when a user requests an update.     We can view the columns and indexes of the commit table through the Sys.all_columns catalog view. The historical name of the Commit table is called Sys.syscommitab. We can execute the following statements:    SELECT *    from sys.all_columns    WHERE object_id = object_id (' Sys.syscomm Ittab '); the execution results are as follows:        Sys.syscommitab has two indexes: a clustered index on the commit_ts and xdes_id columns, and a nonclustered index on the xdes_id.     It must be stated that the commit table value stores the thing information without storing the actual row data. These related data are stored in a partitioned system table. Because one thing can federate multiple tables and each table may have more than one row of data, if all are stored in a commit tableThen the amount of data can be very large. All columns in the Commit table can be queried by the dynamic management attempt (DMV) sys.dm_tran_commit_table In addition to the Dbfragid.     B) Internal cleanup tasks     Once commit generates data, the data will occupy a certain amount of memory in the database. The change tracking contains features that initiate internal tasks to handle what is not needed for historical data. The following SQL can be used to configure:    ALTER DATABASE heavi_case1    SET change_tracking = on    (auto_cleanup=o N, change_retention=1 hours);    if available, the memory task executes every 30 minutes, evaluating which transactions need to be removed (by subtracting the retention time from the current time to get a difference, and then passing an interface from the commit The table finds the thing ID with a time range greater than the difference, and finally removes things from the commit table and other hidden tables through the object ID.     Cleanup task configuration is available through the following SQL query:    SELECT * from sys.change_tracking_databases;  2) Table-level configuration     To start a table-level configuration for a table. Can be configured via ALTER TABLE and enable change_tracking. For example, we start the configuration for the Hvi_order table:    ALTER table hvi_order enable change_tracking;      If change tracking starts a database-level configuration, there are two changes when running this statement. First, the database creates an internal table that tracks the row data things of the target table. Second, a hidden column is added to the target table, which tracks the special row changes through the object ID.       created internal table naming rules for Sys.change_tracking_[object_id],object IDs can be found through the Sys.all_objects catalog view. or through Sys.internal_tables lookup (condition is internal_type equals 209）。 The SQL statements are as follows:    SELECT DISTINCT * from sys.all_objects where parent_object_id in    (Select object_id fr Om sys.all_objects where [name] like ' Hvi_order ') # I write so easy to identify     SELECT * from Sys.internal_tables where pare nt_object_id in    (select object_id from sys.all_objects where [name] like ' Hvi_order ')     above we're talking about class. Do not add additional 8-byte columns to the miscellaneous target table records the latest thing ID. If change tracking is closed, the column is also removed. Change Trace run-time behaviorChange tracking each DML operation behavior that is appended after the start of the table level.    You can also allow data users to find out which rows have changed and need to be synchronized through changetable.   Next we insert a row of data into the Hvi_order table and view the execution planning process.    SQL statements: INSERT INTO Dbo.hvi_order (Createtime, cus_id) VALUES (' 2015/11/1 ', 2); Query Analysis Process: We perform an analysis process by inserting data against a table that does not have a boot table-level trace: As you can see, when you start a table-level trace, the execution process passes an internal change table if there is an input to insert the update. Tracking and profiling (not understand Chinese meaning)Query tuning, optimization, and general troubleshooting can be generated through the visible process. SQL Trace is a powerful tool used to provide queries about what the database engine is doing. With the trace tool, we can monitor 180 events. In these events, we can view details of the user's login and lock activity through a special session ID (SPID). This data is generated through SQL Server Profiler, as well as a series of server-side stored procedures and. NET classes, and can provide you with the flexibility to roll back custom workarounds when a problem occurs.    SQL Trace is a database engine technology and one thing to understand is that often the client Configuration tool simply encapsulates the functionality of the server. At the time of tracing, when various activities appear in the database engine, we monitor various events. For example, every activity in which a user logs on or executes a query will start the event to be captured. And these events are captured through the database Engine code. In other words, special code has been added to the execution path through which the event is started. For example, a query case, when the query starts we can collect data, including query execution time, CPU time. SQL Server Contextual Series directory structure:

One of the SQL Server contextual series catalog

SQL Server Context Series II Framework and configuration

Three frameworks and configurations for the SQL Server contextual series

SQL Server contextual series four databases and files

SQL Server sequence of 5th logs and recovery

Six tables of SQL Server contextual series

SQL Server Contextual Series Seven index

SQL Server Contextual series of eight more special storage

SQL Server contextual series nine query optimization

SQL Server Contextual series ten plan cache

SQL Server Context series XI. transactions and concurrency

         

SQL Server Contextual Series three query process tracking