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.
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

GO
 /*******************************************************/
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 

 
/*******************/
select * from sys.dm_exec_connections
/*****************/

And in result we can see  net transport as namedpipe instead TCP.









We check Configuration manager and here all 3 protocols are enabled

1.    Shared Memory
2.    Named Pipes
3.    TCP

But Named pipe was overwriting TCP  (TCP PORT = 15000)
So we changed TCP Port from 15000 to 1433








 Restart Sql service . And now query from DBA machine is also taking just 2 secs



1 comment: