Knowledge Sharing: Query is running slow from client sql server, Real Time Performance Issue

Real Time Performance Issue: Query running slow from client sql server

Today I am sharing one of the issue I faced for slow running query as a scenario

Specification: SQL Server is new build default instance where databases are migrated from named instance of same version.

DBA Input: DBA executed query from his own machine and queries are running 50 times slower then previous instance

Query Execution Client Statics: When query executed from DBA machine in

India

(Server located @ onsite location)

Server Name : ServerGP : Good performer

Result comes in 8 sec

CPU time = 1579 ms, elapsed time = 1211 ms.

Table 'Table1'. Scan count 17, logical reads 45, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ' Table2'. Scan count 25, logical reads 1030, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ' Table 3'. Scan count 25, logical reads 1030, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ' Table 4'. Scan count 25, logical reads 310, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ' Table 5'. Scan count 25, logical reads 108505, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ' Table 6'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Server Name : ServerBP : Bad performer

Result comes in 4 min 15 sec

CPU time = 1185 ms, elapsed time = 257225 ms.

Table ' Table 1'. Scan count 25, logical reads 45, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ' Table 3'. Scan count 25, logical reads 1030, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ' Table 2'. Scan count 25, logical reads 1030, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ' Table 4'. Scan count 25, logical reads 310, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ' Table 5'. Scan count 25, logical reads 108805, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ' Table 6'. Scan count 9, logical reads 99, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

When we execute query after login into box ServerBP (Bad performer)

Output resulted in 2 secs and and client statistics is also same as ServerGP (good performer server)

From Client statistics we can understand that (Time statistics) which is very high on ServerBP. This clearly mentions we have lot of wait time.

When I search there are 3 types of waits using below query:

/*******************************************************/

SELECT dm_ws.wait_duration_ms,

dm_ws.wait_type,

dm_es.status,

dm_t.TEXT,

dm_qp.query_plan,

dm_ws.session_ID,

dm_es.cpu_time,

dm_es.memory_usage,

dm_es.logical_reads,

dm_es.total_elapsed_time,

dm_es.program_name,

DB_NAME(dm_r.database_id) DatabaseName,

-- Optional columns

dm_ws.blocking_session_id,

dm_r.wait_resource,

dm_es.login_name,

dm_r.command,

dm_r.last_wait_type

FROM sys.dm_os_waiting_tasks dm_ws

INNER JOIN sys.dm_exec_requests dm_r ON dm_ws.session_id = dm_r.session_id

INNER JOIN sys.dm_exec_sessions dm_es ON dm_es.session_id = dm_r.session_id

CROSS APPLY sys.dm_exec_sql_text (dm_r.sql_handle) dm_t

CROSS APPLY sys.dm_exec_query_plan (dm_r.plan_handle) dm_qp

WHERE dm_es.is_user_process = 1

/*******************************************************/

1. CXPACKET: When a parallel operation is created for SQL Query, there are multiple threads for a single query. Each query deals with a different set of the data (or rows). Due to some reasons, one or more of the threads lag behind, creating the CXPACKET Wait Stat. There is an organizer/coordinator thread (thread 0), which takes waits for all the threads to complete and gathers result together to present on the client’s side. The organizer thread has to wait for the all the threads to finish before it can move ahead. The Wait by this organizer thread for slow threads to complete is called CXPACKET wait.

---------> We tried to run query with maxdop (1) but no luck

2. ASYNC_NETWORK_IO: This wait type is associated with either a calling application that is not processing results quickly enough from SQL Server or is associated with a network performance issue.

----------> This looks to be network issues between SQL server and client. From Client T-SQL keep on waiting on ASYNC_NETWORK_IO.

3. Premitive_xxx: use to indicate a worker is running code that is not under the SQL OS scheduling systems.

-----------> This doesn't seems an issue

Now we logged into server and check and it was enough to give us a shock that query is taking just 2 seconds. It is the same query which I was running from my local box on ServerBD.

Now we were sure that this is network issue , But network confirms that their results are good and server doesn’t have any network issue and now the ball is again in our court.

So bit more dive in sql network protocol ,We tried to connect to ServerBD using TCP connection

But it got failed and then we check