Product(s):	Bentley Raceway and Cable Management CONNECT Edition
Version(s):	10
Environment:	N/A
Area:	Installation_Configuration
Subarea:	N/A

Background

In order for database updates to perform well, you must ensure that your IO latency from the Bentley Raceway and Cable Management client machine and your SQL Server is good. This article will explain how to measure your current latency and judge whether it is performing well or not.

Steps

First, you must install Microsoft SQL Server Management Studio on the client machine if you don't already have it. You can download it from Microsoft.
Launch Microsoft SQL Server Management Studio and connect to the BRCM SQL Server database.
Select the New Query button to open a new query window and paste the following query in it:

SELECT

[ReadLatency] =

CASE WHEN [num_of_reads] = 0

THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,

[WriteLatency] =

CASE WHEN [num_of_writes] = 0

THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,

[Latency] =

CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)

THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,

[AvgBPerRead] =

CASE WHEN [num_of_reads] = 0

THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,

[AvgBPerWrite] =

CASE WHEN [num_of_writes] = 0

THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,

[AvgBPerTransfer] =

CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)

THEN 0 ELSE

(([num_of_bytes_read] + [num_of_bytes_written]) /

([num_of_reads] + [num_of_writes])) END,

LEFT ([mf].[physical_name], 2) AS [Drive],

DB_NAME ([vfs].[database_id]) AS [DB],

[mf].[physical_name]

FROM

sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]

JOIN sys.master_files AS [mf]

ON [vfs].[database_id] = [mf].[database_id]

AND [vfs].[file_id] = [mf].[file_id]

-- WHERE [vfs].[file_id] = 2 -- log files

-- ORDER BY [Latency] DESC

-- ORDER BY [ReadLatency] DESC

ORDER BY [WriteLatency] DESC;

GO

4. Select the Execute button to run the query which should produce a table like the one shown below:

5. Locate the entry for the BRCM database and read the latency value from the "Latency" field and compare your value with the following chart to determine if your latency is within an acceptable range:

Performance	Latency Range
Excellent	< 1ms
Very good	< 5ms
Good	5-10ms
Poor	10-20ms
Bad	20-100ms
Very Bad	100-500ms
Extremely Bad	> 500ms

If your performance is in the poor or worse ranges you will likely experience performance problems when executing the Update Database feature. In such circumstances, you should consult with your IT department to troubleshoot the concern and remedy the situation.

SQL Server IO Latency - BRCM

Background

Steps