Sql-server – Bandwidth usage in Sql Server

monitoringsql server

Our compagny has a central Sql Server Database which is connected to many applications (Web, Backend/Frontend services, backoffice, ..) This database is hosted in a small country (for taxing advantages) where bandwidth is expensive.

Is there any way to have a detailed view of bandwidth usage ? such as how bytes are sent per application, per stored procedure, per login…

I want to monitor this at Sql Server because I can not do this at client side (too many clients and it is not suitable to alter client code for that)

Best Answer

You can snap sys.dm_exec_connections which will give you the net IO metrics for any given connection. The associated sys.dm_exec_sessions will identify the connection client (host, application). Since you need aggregated history and the view gives moment in time snapshot you will have to do the usual transformations to convert moment-in-time to aggregate history.

Related Solutions

Sql-server – How to automatically stagger transaction log shipping to minimize bandwidth usage peaks

DBCC SQLPERF (LOGSPACE) will return the size of the log file in MB & the current percentage used. From that you can calculate the current utilised portion of the log file in MB.

That by itself will not be sufficient to calculate log backup size, not least because you are using backup compression. However collecting this data over time & correlating it with the size of the backups might reveal a linear relationship.

You could insert a step into the Log Shipping backup job to record the size of the utilized portion of the log file and a another step after the backup to record the new size of the log file and the size of the backup.

Running this for a period and analyzing the results could allow you to estimate the size of the backup file.

Not the most straight forward method and to be honest it might not even work. If you are interested in giving it a go I would be interested in helping.

Sql-server – SQL Server memory usage

There are other memory consumers outside of the buffer pool. For instance, SQL Server will use memory for worker threads, multi-page allocations, linked servers, extended stored procedures, CLR, etc.

The buffer pool is never going to equate to 100% of memory consumed by SQL Server. It's a common misconception, as well as setting max memory will be the actual max memory SQL Server uses (not true).

EDIT: Here's how I think you could get non-buffer pool memory size. Note: wait for the input of other SQL Server professionals before taking this query as fact. I cannot guarantee that it is absolutely correct.

declare 
    @data_cache_size_mb decimal(12, 2),
    @plan_cache_size_mb decimal(12, 2),
    @total_mem_mb decimal(12, 2)

select
    @data_cache_size_mb = count(*) * 8 / 1024.
from sys.dm_os_buffer_descriptors

select
    @plan_cache_size_mb = sum(size_in_bytes) / 1024. / 1024.
from sys.dm_exec_cached_plans

select
    @total_mem_mb = sum(pages_allocated_count) * 8 / 1024.
from sys.dm_os_memory_objects

select
    @data_cache_size_mb as data_cache_size_mb,
    @plan_cache_size_mb as plan_cache_size_mb,
    @data_cache_size_mb + @plan_cache_size_mb as buffer_pool_size_mb,
    @total_mem_mb as total_instance_mem_mb,
    @total_mem_mb - (@data_cache_size_mb + @plan_cache_size_mb) as non_buffer_pool_size_mb

Best Answer

Related Solutions

Sql-server – How to automatically stagger transaction log shipping to minimize bandwidth usage peaks

Sql-server – SQL Server memory usage

Related Question