1

To evaluate the migration of an on-prem SQL Server to a cloud-hosted VM, I want two fundamental pieces of information:

  1. The list of clients that access the on-prem DB Server. I'd like to know: client app name, client hostname, target database name and username.
  2. The amount of data trafficked for each such client for a given unit of time. For example, I'd like to know that client "SQLCMD" projected 2 MB of data from the DB in the last hour, or that it wrote 4k of data in the last hour.

I'm successfully gathering the data I want for the first item. I'm simply using the following event session:

CREATE EVENT SESSION [Connections] ON SERVER 
ADD EVENT sqlserver.connectivity_ring_buffer_recorded(
    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.username)),
ADD EVENT sqlserver.login(SET collect_options_text=(1)
    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.username)),
ADD EVENT sqlserver.logout(
    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'c:\xevents\connections')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

However I have not found a way to gather information for item #2 - total data trafficked per unit time. Since many cloud providers will bill on the basis of data ingress or egress, I'd like to gather this information to make cost estimates for running the migrated database in the cloud.

I spotted an article about SQL Server Performance Dashboard metrics. It claims that the dashboard can provide:

  • Network InterfaceBytes Sent/sec
  • Network InterfaceBytes Received/sec

If this is not directly what I need, it certainly is the foundation. The article claims that the dashboard uses Perfmon, DMV's, and Extended Events to attain such metrics. Unfortunately, the article neither shows examples of the above metrics, nor explains which approach (DMV's? XEvents?) offers it.

Does anyone know how I can obtain this information, preferably using just DMV's or XEvents?

There are multiple versions of SQL Server i'm targeting for this effort: 2012, 2016 and 2017.

The Performance Dashboard I already mentioned is now built-in to SSMS. That dashboard gives me total logical and physical reads and writes for the top queries (since the DB server came online?). That's cool, but not what I'm looking for; it is not clear how "logical" or "physical" IO can be converted into raw kilobytes of data sent/received. Also, I need this information aggregated preferably for all queries, not just the top 10.

I also notice that the SSMS standard report for "Activity - Top Connections" seems closer to my goal, because it indicates how many "packets" were sent or received per connection. I might be able to convert "packets" into an actual data-size metric per connection.

Any advice on how to proceed is appreciated.

  • What version of sql server? – Sir Swears-a-lot Jun 4 '18 at 8:06
  • @Peter I've edited the question to add version information. – Brent Arias Jun 4 '18 at 16:12
  • If theyre dedicated sql servers would the network traffic suffice? Could you use a network monitor tool? microsoft.com/en-us/download/details.aspx?id=4865 – Sir Swears-a-lot Jun 6 '18 at 9:18
  • There are two problems with network traffic data. First, sometimes there is red-tape preventing me from establishing or accessing that data. Second, assuming I can indeed access the network traffic data, then I want to correlate that data to information that only SQL Server has, such as the "client app name" which is connecting to SQL Server. To make that correlation, I need client IP address and port number info from SQL Server, which itself still needs an answer. – Brent Arias Jun 6 '18 at 14:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.