Using SQL Server Compact Server Agent Statistics to Troubleshoot Connectivity
The Microsoft SQL Server Compact 3.5 Server Agent log contains synchronization statistics when LOGGING_LEVEL of 2 or 3 is specified. Each block of statistic data reports the connectivity operations that occurred during the last 15 minutes. It only reports historical data, not instant information. When you ping the SQL Server Compact 3.5 Server Agent by using the ?Stats parameter, such as http://sqlservername/sqlce/sqlcesa35.dll?stats, a Web page is displayed with current and historical statistical data about replication and Remote Data Access (RDA) synchronizations.
The Web page displays two tables that contain information for the current state and summary of information for up to every 15 minutes. The following tables describe the items that appear in the form of two tables when you ping the SQL Server Compact 3.5 Server Agent for statistical information.
Current State
The current state information is the status of all the values at the time the SQL Server Compact 3.5 Server Agent specific to the virtual directory was pinged.
| Item | Description |
|---|---|
TransferIN Threads Available |
Number of threads available to transfer all data from the device to the server. This property is a dynamically growing value starting at 1 up to the registry value Max_Threads_Per_Pool. |
TransferIN Threads Busy |
Number of transfer threads actively transferring data from devices to the server. |
TransferOUT Threads Available |
Number of transfer threads available to transfer all data from the server to the device. |
TransferOUT Threads Busy |
Number of transfer threads actively transferring data from the server to devices. |
Sync Threads Available |
Number of synchronization threads available to execute merge replication Synchronize(), RDA Pull(), Push(), or Submit SQL(). |
Sync Threads Busy |
Number of synchronization threads actively performing Synchronize(), Pull(), Push() or SubmitSQL() with SQL Server. |
Max Threads Per Pool |
Maximum number of threads for each pool, namely the transfer threads pool and the sync threads pool. The default value is 20 threads per pool and is controlled by the Max_Threads_Per_Pool registry key. |
Sessions Since Restart |
Number of sessions since the IIS computer was restarted. Every sync operation is a session. |
Total Active Sessions |
Number of sessions actively transferring or syncing data. Usually this number is equal to the number of Replication and RDA sessions that are actively running or are waiting in queues. |
Total Transfer Requests Queued |
Number of transfer requests waiting in the queue until a transfer thread is available to process the request. |
Total Sync Requests Queued |
Number of sync requests waiting in the queue until a sync thread is available to process the request. |
Stats Since <Time Period>
This table is a summary of data gathered up to 15 minutes between the date and time displayed above this table and the Current State table. For example, if the time above the Current table is 10:00:00 and the "Stats Since xxxx time" is 10:08:00, this table is showing statistics for the first eight minutes of the 15-minute interval.
For historical timing information, use the SQL Server Compact 3.5 Server Agent log. For more information, see Reading the SQL Server Compact Server Agent Log Statistics. When throughput information is desired at a specific point in time, using the ?Stats parameter is useful.
| Item | Description |
|---|---|
Total Replication Syncs |
Number of replication merges performed during this report period. |
Total RDA SubmitSQLs |
Number of remote data access (RDA) SubmitSQL() calls during this report period. |
Total RDA Pushes |
Number of RDA Push() calls during this report period. |
Total RDA Pulls |
Number of RDA Pull() calls during this report period. |
Average .IN File Size |
Average size, in bytes, of the .IN files. Files with an .IN extension are physical files created from the message data sent by the client. |
Average .OUT File Size |
Average size, in bytes, of the .OUT files. Files with an .OUT extension are physical files created from the message data sent from the server. |
Total Completed Operations |
Number of replication Synchronize(), RDA SubmitSQL(), Push(), and Pull() calls that were completed during this time period. |
Total Incomplete Operations |
Number of replication Synchronize(), RDA SubmitSQL(), Push(), and Pull() calls that started but were not completed during this time period. |
Total Sync Thread Time (seconds) |
Total time, in seconds, that all synchronization threads took to complete synchronization operations. This statistic does not include the time it took to transfer the messages to and from the SQL Server Compact 3.5 clients. |
Total Transfer Thread Time IN (seconds) |
Total time, in seconds, required to send all data to the server. Comparing this attribute to Total_Transfer_Thread_Time_OUT reveals where the greatest amount of time is spent between sending data to the server or sending data to the device. |
Total Transfer Thread Time OUT (seconds) |
Total time, in seconds, required to send all data from the server. Comparing this attribute to Total_Tranfer_Thread_Time_IN reveals where the greatest amount of time is spent between sending data to the server or sending data to the device. |
Total Sync Queue Time (seconds) |
Total time, in seconds, that client sync requests wait for a Sync Thread to become available to start the synchronization process with the server. |
Total Transfer Queue Time IN (seconds) |
Total time, in seconds, that a client waits in the queue for a Transfer Thread to become available to send data to the server from the device. |
Total Transfer Queue Time OUT (seconds) |
Total time, in seconds, that a client waits in the queue for a Transfer Thread to becomes available to send data from the server to the device. |
See Also
Tasks
Using Internet Explorer to Check the Server Agent Configuration (SQL Server Compact)
Using SQL Server Compact Server 3.5 Agent Diagnostics to Troubleshoot Connectivity