Chapter three--the second part--sixth article monitoring SQL Server mirroring

Text: Third--Part II--sixth Article monitoring SQL Server mirroring

Original source: http://blog.csdn.net/dba_huangzj/article/details/26846203

To optimize, first to monitor to see if there is a performance problem, if so, where. In order to start the real optimization, so this article with monitoring as the portal, in the previous article has provided some monitoring information

For the Monitoring section, this article describes the following:

1. Monitoring components
2. Warning threshold value
3. Database Mirroring Monitor
4. About system stored procedures for mirroring
5. Performance counters

1.1. Monitoring components:

• Database Mirroring State Table:

The database mirroring state is stored in the Dbm_monitor_data table in msdb and is created by the sp_dbmmonitorupdate system stored procedure whenever the database mirroring state changes. For example, in the previous domain environment to build the image, you can see the following results:

Start by adding the two machines to the registry manager in the form of step 6 in http://blog.csdn.net/dba_huangzj/article/details/27652857:

Then execute the following statement to see some valuable information:

SELECT  * from    msdb.dbo.dbm_monitor_data ORDER by Local_time DESC

Among them, the more valuable is role, various rate and so on.

• Database Mirroring State Job:

By default, runs every minute to update the Database Mirroring state table. Run intervals from 1 minutes to 120 minutes. This job actually calls the sp_dbmmonitorupdate system stored procedure. If the SQL Server agent is not enabled, the job will not run, which causes the data in the mirror state table mentioned earlier to become obsolete.

This job is created automatically when you start a database session using SSMS, but you can use the following system stored procedures to customize the job to see Books Online in more detail:

• Sp_dbmmonitoraddmonitoring: Creates a database Mirroring monitor job that periodically updates the mirroring state of each mirror library on the server instance. Note that if the cycle is too low, the client's response time may increase. For example, set the cycle to 1 minutes: EXEC sp_dbmmonitoraddmonitoring 1
• Sp_dbmmonitorchangemonitoring: Change the value of the database mirroring monitoring parameter, such as changing the update period to 5 minutes: EXEC sp_dbmmonitorchangemonitoring 1, 5;
• Sp_dbmmonitordropmonitoring: Stop and delete all database Mirroring monitor jobs on the server instance.

Warning Threshold value:

If you have used a mirror monitor, you will find a warning option,

Pay attention to the bottom right corner of the description, through the threshold, you can set a series of countermeasures, the following brief description of each threshold:

• Unsent log (unsent log): Error number 32042, set on the principal server, is important in high-performance mode, and can be used even in high-safety mode if database mirroring is paused.
• Not restored log (unrestored log): Error number 32043, set on the mirror server, excessive non-restored log means that the mirror server's I/O subsystem may be having performance problems that cause the mirror server to fail to keep up with the principal server's progress.
• The oldest unsent transaction (oldest unsent transaction): Error number 32040, set on the principal server, is especially important in high-performance mode.
• Mirror commit cost (Mirro commit overhead): Error number 32044, configured on the mirror server, sets the number of milliseconds the average transaction delay is committed. This value is the time that the principal server waits for the mirror server to confirm that it has redo. This time is meaningful in high-safety mode, because the principal server does not need to wait for the mirror server's acknowledgment information in high-performance mode.

The use of thresholds is described in detail in the section "Monitoring with other tools" and "system stored procedures for mirroring" in this article.

Database Mirroring Monitor: Open the database Mirroring Monitor:

You can open the mirror monitor in the following ways:

To register a mirrored database:

If mirroring is configured, there is some information after the monitor is turned on, but for some reason to configure the image library's information, you can use the ability to register the mirrored database,

Then register:

By default, SQL Server takes precedence over the Windows Authentication link, and after clicking OK, it completes adding work.

To perform a performance check using a monitor:

Mirror Monitor is a good tool to monitor the performance of the mirror, using this tool, we first want to look at the mirror state, generally "synchronized" or a short time of "synchronizing" is allowed, but in other states, you need to check whether there is a problem. The Monitoring window does not refresh in 30 seconds, and if you are not the sysadmin role but the dbm_monitor database role, you need to wait for the mirror monitor job to update itself. If it is sysadmin, it can be refreshed manually.

You can view historical information about the transfer of a mirror by tapping the history record.

We can try to look at the changes, write a dead loop in the principal server and insert the data, then look at the results of the monitor, and remember to stop the dead loop after the view:

You can see that the insertion process does produce log transfers.

About system stored procedures for mirroring:

Some of the system stored procedures are mentioned earlier, in fact, mirroring and even other highly available technologies provide a series of system stored procedures to achieve almost all of the GUI functionality, and as DBAs, it is necessary to use system stored procedures. Here are some of the main system stored procedures, and more detailed information on your own to view Books Online:

• Sp_dbmmonitorresults: By returning information monitoring of the Database Mirroring State table, it must be performed in the msdb library.

In this result, there are some columns to illustrate:

role ? 1:principal
? 2:mirror

Mirroring_state? 0:suspended
? 1:disconnected
? 2:synchronizing
? 3:pending Failover
? 4:synchronized
Witness_state? 0:unknown
? 1:connected
? 2:disconnected

• Sp_dbmmonitorchangealert: Used to add or modify a specific warning threshold value. The following statement creates a threshold that warns when the "oldest unsent transaction" in the ADVENTUREWORKS2008R2 library exceeds 20 minutes.

  USE msdb GO EXEC Sp_dbmmonitorchangealert @database_name = N ' adventureworks2008r2 ', @alert_id = 1, @thresho LD = $, @enabled = 1 

  
  
  

• Sp_dbmmonitorhelpalert: Returns the threshold information in a specific library. such as:

  USE msdb GO EXEC Sp_dbmmonitorhelpalert @database_name = N ' adventureworks2008r2 ', @alert_id = 1 

  
  
  

  /p>

• Sp_dbmmonitordropalert: Removes the warning threshold value by setting the threshold to null, such as:

  use msdb GO EXEC sp_dbmmonitordropalert @database_name = N ' Adventur Eworks2008r2 ', @alert_id = 1 

  
  
  

Performance counters

Performance counters are important monitoring tools in the foreseeable future (2020 years before the personal estimate), and rely on powerful features for performance analysis, warning delivery, and so on, with a large number of DBAs for daily administration. For database mirroring, the counters are mainly concentrated in: Sqlserver:database mirroring objects, some of the commonly used counters are listed below:

Counter Name	Explain
Bytes received/sec	The number of bytes received per second.
Bytes sent/sec	The number of bytes sent per second.
Log Bytes received/sec	The number of log bytes received per second.
Log Bytes Redone from Cache/sec	The number of redo log bytes obtained from the mirrored log cache in the last second. This counter is used only on the mirror server. This value is always 0 on the principal server.
Log Bytes Sent from Cache/sec	The number of send log bytes obtained from the mirrored log cache in the last second. This counter is used only on the principal server. This value is always 0 on the mirror server.
Log Bytes sent/sec	The number of log bytes sent per second.
Log Compressed Bytes rcvd/sec	The number of compressed bytes received for the log in the last second.
Log Compressed Bytes sent/sec	The number of compressed bytes of the log sent in the last second.
Log Harden Time (MS)	The time, in milliseconds, that the log block waits for the disk to be forcibly written in the last second.
Log Remaining for Undo KB	The total number of log bytes (KB) waiting to be scanned by the new mirror server after the failover. This counter can only be used on the mirror server during the undo phase. When the undo phase is complete, the counter is reset to 0. This value is always 0 on the principal server.
Log scanned for Undo KB	The total number of bytes of log (in kilobytes) that have been scanned by the new mirror server since the failover began. This counter can only be used on the mirror server during the undo phase. When the undo phase is complete, the counter is reset to 0. This value is always 0 on the principal server.
Log Send Flow Control Time (ms)	The time, in milliseconds, that a log stream message waits for a send flow control in the last second. In database mirroring, sending log data and metadata to a mirror partner is the most data intensive operation and may monopolize the database mirroring and Service Broker send buffers. Use this counter to monitor the use of this buffer for database mirroring sessions.
Log Send Queue KB	Total number of bytes of log that have not been sent to the mirror server (KB).
Mirrored Write TRANSACTIONS/SEC	The number of transactions written to the mirror database in the last second and waiting for the log to be sent to the mirror database for submission. This counter is incremented only if the principal server is sending log records to the mirror server.
Pages sent/sec	Number of pages sent per second.
Receives/sec	The number of mirrored messages received per second.
Redo bytes/sec	The number of log bytes rolled forward in the mirrored database per second.
Redo Queue KB	The total number of bytes (KB) of the mirror log that is currently applied to the mirror database for roll forward operations. This data is sent from the mirrored database to the principal database.
Send/receive Ack Time	The time, in milliseconds, that the message waits for a partner acknowledgment in the last second. This counter is useful when addressing issues that may be caused by network bottlenecks, such as unexplained failovers, large send queues, or long transaction latency. In these cases, you can analyze the value of this counter to determine whether the problem is caused by the network.
Sends/sec	The number of mirrored messages sent per second.
Transaction Delay	The delay time to wait for an unterminated commit acknowledgement.

This DMV can also be queried in ssms via sys.dm_os_performance_counters, such as:

SELECT object_name,        counter_name,        instance_name,        cntr_value from Sys.dm_os_performance_counters WHERE object_name like '%mirror% '

Through the method described above, the database mirroring status, thresholds and other aspects of monitoring, can be as fast as possible to respond to the problem of mirroring.

Chapter three--the second part--sixth article monitoring SQL Server mirroring