53 reputation
16
bio website GuitarCats.com
location Canada
age 31
visits member for 2 years, 6 months
seen Oct 2 at 16:40

Programmer: C#.NET, MSSQL, T-SQL, javascript, nhibernate, objective-c, IOS5, MS MVC3, Spring.NET


Sep
24
awarded  Autobiographer
Sep
20
awarded  Notable Question
Aug
7
accepted Select statement takes long remotely due to long “client processing time”, but short locally
Oct
26
awarded  Popular Question
May
4
awarded  Teacher
May
3
awarded  Scholar
May
3
answered Select statement takes long remotely due to long “client processing time”, but short locally
May
1
awarded  Supporter
May
1
comment Select statement takes long remotely due to long “client processing time”, but short locally
After monitoring these stats in Perfmon, i noticed a few things. The total bytes/sec never goes up over 700K/s on any of the network cards. Even if I'm running a query that requests megabytes of data, this number stays at around 500K/sec. Our bandwidth is 100 MBPS, and we are not even getting to 1% usage of it. I am thinking there should be a limit configured somewhere that is forcing down the size of the packets, or limiting the transfer rate. The hardware interrupts / sec are at 700-2000. Output queue is empty. Network card usage peaks at about 4% at the highest.
May
1
comment Select statement takes long remotely due to long “client processing time”, but short locally
It is really interesting that even though DEV pings 53 MS, it only takes 11-22 seconds to run the query. Whilst, stage pings 1 MS, it takes over 1 minute to return the data. Dev is also much further away geographically. And stage is right there next to the prod box, and yet is taking much longer.
May
1
comment Select statement takes long remotely due to long “client processing time”, but short locally
4. The top column "Client Execution Time" is just the local time of the machine (IE: 15:30:00) 5. The problem occurs on any machine hitting the production DB server, including on our production web server. 6. The ping delay is <1 MS from the stage box to the prod SQL box. 7. Please see above. 8. Unfortunately the old network no longer exists.
May
1
comment Select statement takes long remotely due to long “client processing time”, but short locally
from our dev box tracert Prod_IP_Address 1 53 ms 52 ms 53 ms SQL2008 From the stage machine, the time is consistently over 1 minute. tracert Prod_IP_Address tracert: 1 1 ms <1 ms <1 ms SQL2008 From production web server: execution time is 53 seconds. tracert: 1 1 ms <1 ms <1 ms SQL2008
May
1
comment Select statement takes long remotely due to long “client processing time”, but short locally
2. The consumption time was close, if not the same as that of the same query ran locally from the PROD box. (I.E. 2 seconds) 3. That's right 1 min 8 seconds is the execution time. This time varies among different client machines. From our development machine (located much further away than the stage machine), I ran this query 8 times in a row, and the time ranged from 11 seconds to 22 seconds. (average 18 sec. )
May
1
comment Select statement takes long remotely due to long “client processing time”, but short locally
Thanks a lot for the info. My responses below. 1. Correct. Client tools also installed on prod, and the same query i mentioned takes 2 seconds to return all of the 30,000 records (totalling 4 MB size). By the way, the query I used is just an example. It is not a real business query. It's just a means to get 4 MB of data from a table. We currently have a performance problem reading several megabytes of data from any table with any query currently.
Apr
30
comment Select statement takes long remotely due to long “client processing time”, but short locally
This problem started since we moved datacenters, and the entire machine was re-installed (everything including SQL). We are with a very respectable hosting provider.
Apr
30
comment Select statement takes long remotely due to long “client processing time”, but short locally
Yes Microsoft SQL Server Management Studio 10.50.1600.1. 2008 R2
Apr
30
awarded  Student
Apr
30
comment Select statement takes long remotely due to long “client processing time”, but short locally
By the way, we tried copying the file of same size (4 MB) between the DB server and another box, and that took a second. So doesn't seem like a network issue.
Apr
30
asked Select statement takes long remotely due to long “client processing time”, but short locally