SQL Server Performance – Time Spent on the Server vs Network

performanceperformance-tuningsql server

Is there a way i can track for a given query how much time was spent by SQL server vs time spent in transferring data in network? I am learning performance tuning, hence this question.

I am looking for a way to do it in SSMS but would like to know if there is any other tools/framework for the same.

Thanks.

Best Answer

I would use extended events for this to break down the wait stats per query. You can then see how much of it was waiting on "Network_IO" vs. mostly everything else. There are a lot of wait stats and most of them would be server side work but not all so make sure you review all the other significant wait stats and also the time it took to come up with the query plan.

I'd recommend either downloading a trial of the SQL Sentry Plan Explorer and put your query in there. It'll tell you right away how long it took, you'll want to do this from the client machine.

This is the link to SQL Sentry Plan Explorer. This is super easy to use and doesn't require any coding. Simply install the free product on the client machine you want to test from, activate the free pro mode for 2 weeks (I would recommend buying this tool, it's great), then look at your wait stats.

This one will provide you with the wait stats per query (From Paul's blog):

    — Drop the session if it exists. 
    IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'MonitorWaits')
        DROP EVENT SESSION MonitorWaits ON SERVER
    GO

    CREATE EVENT SESSION MonitorWaits ON SERVER
    ADD EVENT sqlos.wait_info
        (WHERE sqlserver.session_id = 53 /* session_id of connection to monitor */)
    ADD TARGET package0.asynchronous_file_target
        (SET FILENAME = N'C:\SQLskills\EE_WaitStats.xel', 
        METADATAFILE = N'C:\SQLskills\EE_WaitStats.xem')
    WITH (max_dispatch_latency = 1 seconds);
    GO

--Then use this:
SELECT xmv.map_key, xmv.map_value
FROM sys.dm_xe_map_values xmv
JOIN sys.dm_xe_packages xp
    ON xmv.object_package_guid = xp.guid
WHERE xmv.name = 'wait_types'
    AND xp.name = 'sqlos';
GO

-- Start the session
ALTER EVENT SESSION MonitorWaits ON SERVER STATE = START;
GO

-- Go do the query

-- Stop the event session
ALTER EVENT SESSION MonitorWaits ON SERVER STATE = STOP;
GO

And then I can see how many events fired:

SELECT COUNT (*)
FROM sys.fn_xe_file_target_read_file
    ('C:\SQLskills\EE_WaitStats*.xel',
    'C:\SQLskills\EE_WaitStats*.xem', null, null);
GO

13324

/* And then pull them into a temporary table and aggregate them (various ways of doing this, I prefer this one): */

— Create intermediate temp table for raw event data
CREATE TABLE #RawEventData (
    Rowid  INT IDENTITY PRIMARY KEY,
    event_data XML);

GO

-- Read the file data into intermediate temp table
INSERT INTO #RawEventData (event_data)
SELECT
    CAST (event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file (
    'C:\SQLskills\EE_WaitStats*.xel',
    'C:\SQLskills\EE_WaitStats*.xem', null, null);
GO

SELECT
    waits.[Wait Type],
    COUNT (*) AS [Wait Count],
    SUM (waits.[Duration]) AS [Total Wait Time (ms)],
    SUM (waits.[Duration]) – SUM (waits.[Signal Duration]) AS [Total Resource Wait Time (ms)],
    SUM (waits.[Signal Duration]) AS [Total Signal Wait Time (ms)]
FROM 
    (SELECT
        event_data.value ('(/event/@timestamp)[1]', 'DATETIME') AS [Time],
        event_data.value ('(/event/data[@name=''wait_type'']/text)[1]', 'VARCHAR(100)') AS [Wait Type],
        event_data.value ('(/event/data[@name=''opcode'']/text)[1]', 'VARCHAR(100)') AS [Op],
        event_data.value ('(/event/data[@name=''duration'']/value)[1]', 'BIGINT') AS [Duration],
        event_data.value ('(/event/data[@name=''signal_duration'']/value)[1]', 'BIGINT') AS [Signal Duration]
     FROM #RawEventData
    ) AS waits
WHERE waits.[op] = 'End'
GROUP BY waits.[Wait Type]
ORDER BY [Total Wait Time (ms)] DESC;
GO

-- Cleanup
DROP TABLE #RawEventData;
GO

This script from Paul Randall will provide you all the wait stats over a period of time:

Related Question