Specifies the database for which to return mirroring status.
Specifies the quantity of rows returned:
0 = Last row
1 = Rows last two hours
2 = Rows last four hours
3 = Rows last eight hours
4 = Rows last day
5 = Rows last two days
6 = Last 100 rows
7 = Last 500 rows
8 = Last 1,000 rows
9 = Last 1,000,000 rows
Specifies that before returning results the procedure:
0 = Does not update the status for the database. The results are computed using just the last two rows, the age of which depends on when the status table was refreshed.
1 = Updates the status for the database by calling sp_dbmmonitorupdate before computing the results. However, if the status table has been updated within the previous 15 seconds, or the user is not a member of the sysadmin fixed server role, sp_dbmmonitorresults runs without updating the status.
1 = Principal
2 = Mirror
0 = Suspended
1 = Disconnected
2 = Synchronizing
3 = Pending Failover
4 = Synchronized
0 = Unknown
1 = Connected
2 = Disconnected
Amount of log generated since preceding update of the mirroring status of this database in kilobytes/sec.
Size of the unsent log in the send queue on the principal in kilobytes.
Send rate of log from the principal to the mirror in kilobytes/sec.
Size of the redo queue on the mirror in kilobytes.
Redo rate on the mirror in kilobytes/sec.
Total delay for all transactions in milliseconds.
Number of transactions that are occurring per second on the principal server instance.
Average delay on the principal server instance for each transaction because of database mirroring. In high-performance mode (that is, when the SAFETY property is set to OFF), this value is generally 0.
Time at which the row was recorded by the database mirroring monitor. This is the system clock time of the principal.
Approximate system-clock time of the principal to which the mirror database is currently caught up. This value is meaningful only on the principal server instance.
System clock time on the local server instance when this row was updated.
grab the sql query plan handle from here:
SELECT * FROM sys.dm_exec_requests;
then plug it into here:
FROM sys.dm_exec_query_plan ( 0x060007008561093A4003D1EE030000000000000000000000 )
Beware of adding/inserting with parallel processes – to avoid duplicate inserts you need to keep a range lock open when you check for the existence of the ID
CREATE PROCEDURE dbo.Insert_Or_Update_Foo
SET NOCOUNT, XACT_ABORT ON
IF EXISTS(SELECT * FROM dbo.Foo WITH (UPDLOCK, HOLDLOCK) WHERE ID = @ID)
SET bar = @bar
WHERE ID = @ID
INSERT INTO dbo.Foo (ID, Bar)
VALUES (@ID, @Bar)
also consider sp_getapplock
to develop… use of merge on SQL2008