tsunami

log in
history

SQL2005: bandwidth logging

Luke Breuer
2010-04-20 20:49 UTC

introduction
It can be desirable to know very granular bandwidth usage data for your SQL Server. Perhaps you want to find cases where extremely large results sets are returned, or how much bandwidth different databases require. It can also be useful to view users' usage in terms of bytes sent and received, especially if their connection speed is sufficiently limited. The use case that actually spurred my creation of zs_Bandwidth_Log was the following:
  1. a remote client would start pulling 1mil rows
  2. someone locally would then issue, say, an alter table statement
  3. then, anyone else who wanted to access the “to-be-altered table” would have to wait for both the slow transfer of 1mil rows, as well as the alter table statement
(Some would argue that we should not execute alter table commands during business hours, but it does allow for a kind of rapid development that few companies can provide.)
caveats
The data inserted into zs_Bandwidth_Log has two peculiarities:
  1. it stores blocking information, which has nothing to do with bandwidth
  2. it stores continually-accruing values, such that a fancy self-join is needed to pull out incremental changes
The first peculiarity is due to reasons stated above. It would probably be better to put locking issues into a separate table, with more detailed information. (As is, not a whole lot of debugging can actually be done.)

The second peculiarity is not strictly required — the "fancy self-join" is done in the script that populates the table, so the calculation could have been done there. I believe I didn't do this because:
  1. I wasn't sure if these values would never decrease.
  2. when the insert is run for the first time (or after a long delay), the first rows of values might be very large, and would look out of place

Having run this code for multiple years on a production system, #1 hasn't occurred. #2 doesn't matter either, in the long run. So, at some point, I should probably switch. However, I am going to keep the code below in its current state until I do. It should be fairly easy to change it yourself.
SQL code
table definition
drop table dbo.zs_Bandwidth_Log
create table dbo.zs_Bandwidth_Log (
    session_id            smallint            not null,
    login_name            nvarchar(128)       not null,
    host_name             nvarchar(128)           null,
    client_net_address    varchar(48)             null,
    read_bytes            bigint              not null,
    write_bytes           bigint              not null,
    blocking_session_id   smallint                null,
    connection_id         uniqueidentifier    not null,
    input_date            datetime            not null,
    
    primary key clustered (input_date, connection_id)
) 
insert statement
(This can be put in a job that runs every minute.)
with Snapshot as (
    select  s.session_id, 
            s.login_name,
            s.host_name,
            c.client_net_address,
            read_bytes = isnull(cast(num_reads as bigint) * net_packet_size, 0),
            write_bytes = isnull(cast(num_writes as bigint) * net_packet_size, 0),          
            w.blocking_session_id,
            c.connection_id
    from    sys.dm_exec_connections c
    inner join sys.dm_exec_sessions s on s.session_id = c.session_id
    left join (
        select  session_id,
                blocking_session_id = max(blocking_session_id)
        from    sys.dm_os_waiting_tasks
        where   blocking_session_id != session_id
        group by session_id
    ) w on w.session_id = s.session_id
), Existing as (
    select  connection_id,
            read_bytes,
            write_bytes,
            blocking_session_id,
            row_number = row_number() over (partition by connection_id order by input_date desc)
    from    zs_Bandwidth_Log
    where   input_date > getdate() - 14 -- using datediff, we don't get an index seek
)
insert  zs_Bandwidth_Log
select  s.*, getdate()
from    Snapshot s
left join Existing e on e.connection_id = s.connection_id and e.row_number = 1
where   e.connection_id is null
    or  e.read_bytes != s.read_bytes
    or  e.write_bytes != s.write_bytes
    or  isnull(e.blocking_session_id, 0) != isnull(s.blocking_session_id, 0)
recent data, per 10-minute interval
(Note that you would need to put in the proper IP address if you want to differentiate between intranet traffic and internet traffic.)
with Data as (
    select  login_name, host_name, client_net_address,
            read_mb = read_bytes / 1048576.0,
            write_mb = write_bytes / 1048576.0,
            blocking_session_id,
            input_date = dateadd(minute,      -datepart(minute,      input_date) % 10,
                         dateadd(second,      -datepart(second,      input_date), 
                         dateadd(millisecond, -datepart(millisecond, input_date), 
                            input_date))),
            is_remote = case when client_net_address like '0.0.0.%' then 0 else 1 end,
            row_number = row_number() over (partition by connection_id order by input_date),
            connection_id
    from    zs_Bandwidth_Log 
    where   client_net_address != '<local machine>'
), Grouped as (
    select  cur.input_date,
            local_read_mb =   sum(case cur.is_remote when 0 then cur.read_mb  - isnull(prev.read_mb,  0) else 0 end),
            local_write_mb =  sum(case cur.is_remote when 0 then cur.write_mb - isnull(prev.write_mb, 0) else 0 end),
            remote_read_mb =  sum(case cur.is_remote when 1 then cur.read_mb  - isnull(prev.read_mb,  0) else 0 end),
            remote_write_mb = sum(case cur.is_remote when 1 then cur.write_mb - isnull(prev.write_mb, 0) else 0 end),
            blocking_sessions = count(cur.blocking_session_id)
    from    Data cur
    left join Data prev on prev.connection_id = cur.connection_id and prev.row_number = cur.row_number - 1
    group by cur.input_date
)
select  top 200 *
from    Grouped
where   input_date > '4/1/10'
order by input_date desc
usage by a particular person
with Data as (
    select  login_name, host_name, client_net_address,
            read_mb = read_bytes / 1048576.0,
            write_mb = write_bytes / 1048576.0,
            blocking_session_id,
            input_date,
            row_number = row_number() over (partition by connection_id order by input_date),
            connection_id
    from    zs_Bandwidth_Log 
    where   client_net_address != '<local machine>'
    --order by connection_id, input_date
), Grouped as (
    select  cur.input_date,
            cur.login_name,
            cur.host_name,
            cur.connection_id,
            read_mb =   cur.read_mb  - isnull(prev.read_mb,  0),
            write_mb =  cur.write_mb - isnull(prev.write_mb, 0)
    from    Data cur
    left join Data prev on prev.connection_id = cur.connection_id and prev.row_number = cur.row_number - 1
)
select  *
from    Grouped
where   input_date between '4/7/10' and '4/8/10'
    and login_name = 'DOMAIN\user'
order by connection_id, input_date