none
Find out which client IP and Query using highest network Bandwidth from MSSQL

    Question

  • I want to know which clients (IP or Hostname) are taking highest bandwidth from server and by what query they are taking high bandwidth.

    Field Names:

    Client Name/IP, Query, KB transferred, Date&Time of query executed, Time took to execute

    Is there anyway to get it done in SQL?

    Thanx Sanket

    Wednesday, July 02, 2014 7:51 AM

All replies

  • Hi Sanketgroup, Yes it is very much possible if I have understood your requirement. Please check below query: select t.hostname as 'Host Name', t.login_time as 'Login Time', substring(q.text,1,200) as 'CommandExecuted',sum(p.total_rows)*8 as'KBTransffered' , t.waittime as 'Wait Time',t.lastwaittype as 'Last Wait Type',t.last_batch as 'Last Batch', t.PROGRAM_NAME as 'Program Name', t.hostprocess as 'Host Process Name', t.nt_domain as 'Domain Name', t.nt_username as 'Domain User Name', t.net_address as 'Net Address', t.loginame as 'LoginName' from sysprocesses t inner join sys.dm_exec_query_stats p on p.sql_handle = t.sql_handle cross apply sys.dm_exec_sql_text(t.sql_handle) as q Group by t.hostname, t.login_time, q.text,p.total_rows, t.waittime ,t.lastwaittype, t.last_batch, t.PROGRAM_NAME,t.hostprocess,t.nt_domain, t.nt_username, t.net_address, t.loginame

    Santosh Singh

    Wednesday, July 02, 2014 8:20 AM
  • Hi Santosh

    This is not giving result as i want.

    Some detail explanation on what i want:

    Client Name/IP:  Clients IP address or Their PC name who are sending queries to SQL server

    Query: Actual full query which was sent by Client to SQL server

    KB Transferred: How many KB were transferred to Client on that particular query execution data set .

    Date&Time: this is will be exact date and time of query which was sent by client to SQL server

    Execution Time: How many seconds it took to send the result back to client.

    With your query, i am not getting any of above information.

    Thanx

    Wednesday, July 02, 2014 10:21 AM
  • Some pointers inline:

    You need to execute this query from master db.

    Client Name/IP:  Clients IP address or Their PC name who are sending queries to SQL server

    Comments: Under column ['Host Name'] you will get client name along with other details are coming out like Login, domain name, process, program name, etc for other information.

    Query: Actual full query which was sent by Client to SQL server

    Comments: Yes query is coming out of this query under column [CommandExecuted]

    KB Transferred: How many KB were transferred to Client on that particular query execution data set .

    Comment: Under column[KBTransffered], you should get the required data.

    Date&Time: this is will be exact date and time of query which was sent by client to SQL server

    Comment: There is login and last batch columns there you can see this information.

    Execution Time: How many seconds it took to send the result back to client.

    Comment: This information is something you need to figure out by login time and last batch time under their column.

    Let me know what error you are getting and which server you are executing this server on db server where all application servers are hitting.


    Santosh Singh

    Wednesday, July 02, 2014 10:41 AM
  • Some pointers inline:

    You need to execute this query from master db.

    Client Name/IP:  Clients IP address or Their PC name who are sending queries to SQL server

    Comments: Under column ['Host Name'] you will get client name along with other details are coming out like Login, domain name, process, program name, etc for other information.

    No, This shows only instance name/ SQL server name only. Does not show client name or IP. My server name is Master-SQL and this column have value "Master-SQL" in each row. It suppose to have client pc name or IP.

    Query: Actual full query which was sent by Client to SQL server

    Comments: Yes query is coming out of this query under column [CommandExecuted]

    I saw but it does no show actual query. it shows create procedure etc.

    KB Transferred: How many KB were transferred to Client on that particular query execution data set .

    Comment: Under column[KBTransffered], you should get the required data.

    May be

    Date&Time: this is will be exact date and time of query which was sent by client to SQL server

    Comment: There is login and last batch columns there you can see this information.

    Execution Time: How many seconds it took to send the result back to client.

    Comment: This information is something you need to figure out by login time and last batch time under their column.

    Let me know what error you are getting and which server you are executing this server on db server where all application servers are hitting.

    I am not getting any error, but not getting result what i want. I am running SQL 2012.

    And executing on DB server where all queries are coming from Desktop App clients.

    I want to monitor which query and client taking maximum bandwidth of my network in realtime.


    Santosh Singh

    Please see my comment in bold.
    Wednesday, July 02, 2014 10:59 AM
  • It shouldn't give different result as same query is working for me.

    Some pointers to check:

    1. Query, servername, login name, can be used to verify who is executing query, so please check that.

    2. Also in this query, we are not creating any procedure so this query is not coming in your report.

    3. Check also that who else are executing query in your system by login you can see that.

    4. Also if all queries servername shows your servername, then please validate there are no other servers connected and executing queries by sp_who2 or select * from sysprocessess table too.

    5. Turnaround time for query is something we need to check with login time and last batch time.

    --------------------

    If above things don't work for you then please run sql server profiler tool to get all possible data as you need.


    Santosh Singh

    Thursday, July 03, 2014 6:26 AM