Wait status

Last Post 03 Apr 2007 10:31 AM by TRACEYSQL. 39 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Page 1 of 212 > >>
Author Messages
TRACEYSQL
New Member
New Member

--
03 Mar 2007 12:32 AM
The wait stats
wait type networkio
NETWORKIO0x800This waittype indicates that the SPID is waiting for the client application to fetch the data before the SPID can send more results to the client application.

I have some really large values in this NETWORKIO

Does this mean that the SQL is waiting on the network outside

All our Data is on our SAN.

Each minute i have network wait seconds 115.43 and
another time i have One hour i have 5082.58

I asked our network guys if there was something with the network and they asking me to give them the meaning of the NETWORKIO and why is SQL telling us this...what direction is it waiting on etc

Thanks
SQLUSA
New Member
New Member

--
03 Mar 2007 03:05 PM
Where do you get exactly these figures?

What are you doing? BULK INSERT?

Kalman Toth
SQLUSA: http://www.sqlusa.com
TRACEYSQL
New Member
New Member

--
04 Mar 2007 02:29 AM
The users were just running reports .....no bcp...
Figures i got from Quest Performance and then inside of sql processes sysprocesses
i saw the network packet size 4096 bytes....(Before i change this is there something i can produce first i.e amount of data per hour (i do have this figure).....

If i increase this figure does that mean more data can go across from SQL and put more strain on the SQL in buffer

Thanks.....
sgoodwi3
New Member
New Member

--
21 Mar 2007 05:42 AM
Network IO waits accumulate any time the SQL Server is trying to push data out to a client (or other server), and cannot push the as quickly as it wants to. It has nothing to do with IO. This wait statistic is a result of the thread waiting for network throughput. If you experience a lot (relative to the rest of the system) you have a network throughput issue. This may be a result of an inadiquetly sized network, problems with NIC or switch configuration, driver issue, or a host of other issues. The first thing you need to do is determine whether or not the connection to the server is capable of what it should be (1Gb/4Gb, whatever you have). If it's not you have a hw/sw problem. If it is you have a network sizing issue.

Steve
www.SQLEngineering.com
TRACEYSQL
New Member
New Member

--
21 Mar 2007 07:36 AM
Thank you for replying.

This has become very high lately.

I have mentioned to my network and they tell me there is nothing going on but im always getting this high values.

Do you know of a tool where i can monitor what leaves SQL and how long it takes across the network
and where the delay is perhaps

SQLUSA
New Member
New Member

--
21 Mar 2007 10:16 PM
The best tool for monitoring:

PerfMon

SQL Server Profiler

There is no magic tool. Any 3rd party tool based on these.

Kalman Toth
SQLUSA: http://www.sqlusa.com/register/
Good SQL Server 2005 Training
SQLUSA
New Member
New Member

--
21 Mar 2007 10:18 PM
Add

sp_monitor

to the list above.

What is it really doing to performance?

We need more details.

Kalman Toth
SQLUSA: http://www.sqlusa.com/register/
Good SQL Server 2005 Training
TRACEYSQL
New Member
New Member

--
22 Mar 2007 02:07 PM
I see if i can capture it in a period of hours doing sp_monitor
Thanks
TRACEYSQL
New Member
New Member

--
23 Mar 2007 02:22 AM
last_run current_run seconds
------------------------------------------------------ ------------------------------------------------------ -----------
2007-03-23 07:20:22.673 2007-03-23 07:20:44.533 22

cpu_busy io_busy idle
------------------------- ------------------------- -------------------------
116952(0)-0% 9802(0)-0% 349954(42)-190%

packets_received packets_sent packet_errors
------------------------ ------------------------ ------------------------
169382655(500) 217297719(500) 49(0)

total_read total_write total_errors connections
------------------- ------------------- ------------------- ------------------
20050977(3) 10859501(9) 0(0) 16173243(50)

SQLUSA
New Member
New Member

--
23 Mar 2007 11:25 AM
sp_monitor shows delta:

1. since server started (first time)
2. since last time executed (next times)

Kalman Toth
SQLUSA: http://www.sqlusa.com
SQLUSA
New Member
New Member

--
23 Mar 2007 11:26 AM
This shows the DELTA in (....) for 22 secs!

e. g. packets sent/received 500


Kalman Toth
Database Architect
TRACEYSQL
New Member
New Member

--
29 Mar 2007 01:40 AM
Im using this today its good frist thing in morning
last_run current_run seconds
------------------------------------------------------ ------------------------------------------------------ -----------
2007-03-29 06:37:08.333 2007-03-29 06:39:23.907 135

cpu_busy io_busy idle
------------------------- ------------------------- -------------------------
1133798(17)-13% 66374(3)-2% 1196792(240)-177%

packets_received packets_sent packet_errors
------------------------ ------------------------ ------------------------
847154177(89788) 876479267(89800) 142(0)

total_read total_write total_errors connections
------------------- ------------------- ------------------- ------------------
225008113(334) 106355164(21) 0(0) 494907(0)

Is there a way to capture this every 15 minutes and write to a file
The packets today look good thanks
TRACEYSQL
New Member
New Member

--
29 Mar 2007 12:36 PM
Let me take a look at that script.

The packets received and sent should they be identical on a given date and if they are not is it that is a network issue.

I showed to our network guys and they do not believe me when i say there is a problem on the network they keep blaming our ERP system.

Thank i get the script going.

Cheers
TRACEYSQL
New Member
New Member

--
29 Mar 2007 12:42 PM
I did not know you could do @@ and get the values.

I will get this working tomorrow.....so i just need to understand the packets part if they should always be equal and if this is a network issue.

Thanks
TRACEYSQL
New Member
New Member

--
29 Mar 2007 12:51 PM
Me again
llast_run Time sp_monitor was last run.
current_run Time sp_monitor is being run.
seconds Number of elapsed seconds since sp_monitor was run.
cpu_busy Number of seconds that the server computer's CPU has been doing SQL Server work.
io_busy Number of seconds that SQL Server has spent doing input and output operations.
idle Number of seconds that SQL Server has been idle.
packets_received Number of input packets read by SQL Server.
packets_sent Number of output packets written by SQL Server.
packet_errors Number of errors encountered by SQL Server while reading and writing packets.
total_read Number of reads by SQL Server.
total_write Number of writes by SQL Server.
total_errors Number of errors encountered by SQL Server while reading and writing.
connections Number of logins or attempted logins to SQL Server.

The packets (whats is this in bytes)....of data so i time this by 4096?
and total read and total writes is this in 8k

2007-03-29 06:37:08.333 2007-03-29 06:39:23.907 135

cpu_busy io_busy idle
------------------------- ------------------------- -------------------------
1133798(17)-13% 66374(3)-2% 1196792(240)-177%

packets_received packets_sent packet_errors
------------------------ ------------------------ ------------------------
847154177(89788) 876479267(89800) 142(0)

not sure what 89788 is meaured in and whether this is good or not i thinking they should be identical what i send and receive but the number is this how much can be sent i.e can i increase this 4096? is that network packet sizes.

total_read total_write total_errors connections
------------------- ------------------- ------------------- ------------------
225008113(334) 106355164(21) 0(0) 494907(0)

so is this 334 / 8 * 1024 to give me mb of data? for total read and write
SQLUSA
New Member
New Member

--
29 Mar 2007 04:41 PM
I beleive read/write are 8K blocks.

Kalman Toth
SQL Server 2005 Training - http://www.sqlusa.com/order2005grandslam
TRACEYSQL
New Member
New Member

--
30 Mar 2007 03:18 AM
I don't see in the script where it writes to the table? DBMONITOR
TRACEYSQL
New Member
New Member

--
31 Mar 2007 01:49 AM
Ok let me work on that....dts pump task...to the table.
I wish i had it working yesterday...about 20 users got kicked out of their application..some where good and carried on working...some people were local i.e by passed citrix.

We have users coming in to Citirx to ODBC to the SQL
Rather than just rebooting the servers for citrix i said let me do a trace on the ODBC and i could not even save the file i was hmmm....perhaps it the odbc...i believe it was as asked i rebooted it i do the save trace file in odbc.

Now how to fix...(I know this is off the subject but kind of based on performance network waits )
Do you know how to flush out ODBC each night...and when ODBC is talking to SQL at what stage does either one say hey thats enough data please wait.....Is it SQL server threads?...

ODBC sends request to SQL (does sql say i got enough thanks)...

Any help is appreciated.

TRACEYSQL
New Member
New Member

--
31 Mar 2007 01:27 PM
There was only 2 or 3 connections at the time and the ODBC i could not get to write...
If it was SQL processor wouldn't i got more messages from the application

Do you have any other suggestions to determine that SQL was completely bogged down at that time?
wouldn't the odbc be the answer?
TRACEYSQL
New Member
New Member

--
31 Mar 2007 03:44 PM
There was probrably one process running at the time..no blocks,,cpu was good, memory was good.
We have citrix....odbc.....

ERP users use citrix connect to ERP which then uses ODBC on citrix....we have 4 citrix machines.

All of sudden 10 or so people got disconnected...some users were fine...some of the users by pass citrix and go directly to the ERP from their own ODBC.

All users of local users were good.

After i went into one of the Citrix Servers to check ODBC i could not save a file to see what was going on....The users were clicking the buttons i.e enter po etc and nothing was going to the SQL Server.
So i can only assume it was the ODBC as i could not save the trace file.....after the reboot all was good.

I check sp_who nothing much going on,,,,,check sysprocesses, check sp_monitor knowing major....put on profiler i could see action

So im assuming the ODBC become flooded?

Thanks
You are not authorized to post a reply.
Page 1 of 212 > >>


Acceptable Use Policy