Reading the SQL Server Compact Server Agent Log Statistics

  • Article

The Microsoft SQL Server Compact 3.5 Server Agent log contains statistics when LOGGING_LEVEL 3 is specified. The statistics portion of the log is located between the opening tag (<STATS) and the closing tag (/>) of the log. Each block of statistic data reports the SQL Server Compact 3.5 connectivity operations that occurred during the last 15 minutes.

Statistics are generated whenever the cleanup thread recognizes that 15 minutes have passed since the last report. Statistics are generated only when there has been activity during the 15-minute interval. The statistics report is useful in monitoring performance.

The following statistical report shows a single synchronization (merge replication):

<STATS Period_Start="2002/07/13 15:27:32" Period_Duration="954" Syncs="1"
SubmitSQLs="0" RDAPushes="0" RDAPulls="0" AVG_IN_File_Size="332" 
AVG_OUT_File_Size="90203" Completed_Operations="1" 
Incomplete_Operations="0" Total_Sync_Thread_Time="3" 
Total_Transfer_Thread_Time_IN="0" Total_Pool_Transfer_Time_OUT="0" 
Total_Sync_Queue_Time="0" Total_Transfer_Queue_Time_IN="0" 
Total_Transfer_Queue_Time_OUT="0" />

The following table lists and describes the statistics log attributes.

Attribute Description

Period_Start

Start of the STATS period (in datetime format).

Period_Duration

Time that this report covers (in seconds).

Syncs

Number of bidirectional replication merges performed during this report period.

SubmitSQLs

Number of remote data access (RDA) SubmitSQL() calls during this report period.

RDAPushes

Number of RDA Push() calls during this report period.

RDAPulls

Number of RDA Pull() calls during this report period.

AVG_IN_File_Size

Average size of the .in files (in bytes). Files with an .in extension are physical files created from the message data sent by the client.

AVG_OUT_File_Size

Average size of the .out files (in bytes). Files with an .out extension are physical files created from the message data sent from the server.

Completed_Operations

Number of Sync, SubmitSQL, RDA Push, and RDA Pull calls that were completed during this time period.

Incomplete_Operations

Number of Sync, SubmitSQL, RDA Push, and RDA Pull calls that started but were not completed during this time period.

Total_Sync_Thread_Time

Total time that all synchronization threads (sync) took to complete synchronization operations (in seconds). Sync threads are members of a pool of threads that process messages from SQL Server Compact 3.5. This statistic does not include the time it took to transfer the messages to and from the SQL Server Compact 3.5 clients.

Total_Pool_Thread_Time_IN

Total time required to send all data to the server (in seconds). Comparing this attribute to Total_Pool_Thread_Time_OUT reveals where the greatest amount of time is spent, either sending data to the server or sending data to the device.

Total_Sync_Queue_Time

Total time that sync requests waited in the sync queue before being processed by the server (in seconds).

Total_Pool_Queue_Time_IN

Total time that messages from clients waited in the queue before being processed by the SQL Server Compact 3.5 Server Agent (in seconds).

Total_Pool_Queue_Time_OUT

Total time that messages from SQL Server waited before being processed by the SQL Server Compact 3.5 Server Agent (in seconds).