Unknown large data transfers between Windows 2019 Web server and SQL Server - how to identify what process is doing it ?

Question

My IT infrastructure personnel showed firewall logs capturing huge amounts of data transfer between Webservers(2 Windows 2019 servers load balanced)and Database server (SQLServer 2019 with multiple databases). We are talking 500 GB, 900 GB data transfer spikes in 30 mins interval approximately once in 5 days at random (happened twice in one week). When there is a data transfer spikes like these eCommerce sites hosted on the web server go down and they recover automatically without any intervention.

CPU & Memory on both web server and database server looks fine when this happens. The eCommerce sites that are hosted on the web server are not very high traffic sites(max 700 users). Checked the event logs and database server logs nothing unusual that jumps. Same set of jobs run on the servers everyday. Total size of all databases combined is 200 GB.

So how to find what is triggering the transfer of 900 GB from the database server to web server? Have no visibility into what this data is other than total bytes sent and received through firewall log.

Where do we start ? Any pointers would be highly appreciated. Our suspicion is a backup though we've checked the SQL jobs and can't find anything.

Have already checked for malicious activity - nothing we can find.

Answer 1

I had a similar case with a client many years ago. My first inspection with Trace revealed both this and that in bad habits, but not the explanation to the problem with high I/O spikes. An MVP colleague gave me the tip to look at sys.dm_db_index_logical_stats and check for high values of physical reads. Indeed one table stood out, and inspecting my trace, I found that there was a query that ran SELECT * without WHERE on this 8GB table. On a 10GB server...

So that is something you can try. But obviously, the cause may be something which does not display there. For instance, a backup would not. And as I understand it, the traffic is only between the SQL machine and the web server, but theoretically, it could be something outside SQL Server that sends this data.