August 29, 2017 at 10:16 am
Hi all.
When I run a 'Select * from product', I get 28k rows. On my local machine - it takes 4 seconds. On the network, it takes 12 seconds. Can anyone give me ideas of where to look?
The network is comprised of a Dell SAN, 10G switch , 10G cards. The SQL Server and the Windows machine hosting SSMS are both Virtual machines .
Thank you,
Mike
August 29, 2017 at 10:39 am
mike 57299 - Tuesday, August 29, 2017 10:16 AMHi all.When I run a 'Select * from product', I get 28k rows. On my local machine - it takes 4 seconds. On the network, it takes 12 seconds. Can anyone give me ideas of where to look?
The network is comprised of a Dell SAN, 10G switch , 10G cards. The SQL Server and the Windows machine hosting SSMS are both Virtual machines .Thank you,
Mike
So your local machine is the server?
How long does it take to return a single row?
select * from product where <<primary key>> = <<value>>
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 29, 2017 at 12:00 pm
Yes, my local machine is the server. So the 4 seconds is getting the data on the same notebook where the data is stored and SQL is running.
However, I get 12 seconds even if i log on to the SQL server VM and run SSMS on the SQL VM. To me, it appears to be a function of a slowdown or issue with network transport.
If i get only 4 columns, I can get the data back in 2 seconds (all 28k rows). A single row is back in instantly - 0 seconds.
Does that help?
August 30, 2017 at 8:48 am
mike 57299 - Tuesday, August 29, 2017 12:00 PMYes, my local machine is the server. So the 4 seconds is getting the data on the same notebook where the data is stored and SQL is running.
However, I get 12 seconds even if i log on to the SQL server VM and run SSMS on the SQL VM. To me, it appears to be a function of a slowdown or issue with network transport.If i get only 4 columns, I can get the data back in 2 seconds (all 28k rows). A single row is back in instantly - 0 seconds.
Does that help?
I suppose you mean to say , when you run the query from the SQL Server VM (which is not the actual server) it takes 12 seconds and when you from your local machine (which is the server as u said) it takes 4 seconds. However is the combination , the elapsed time when running from a different machine will naturally be more depending on the size of the data and network capacity (causes the ASYNC_NETWORK_IO wait type) .The decrease in time when you decrease the data to be fetched is understandable . Please work with the same result set to analyze performance efficiently. To fix the abnormal delays , please check if there is any network discrepancy/latency involving your network admin .
August 30, 2017 at 2:53 pm
How would I check for network discrepancy/latency? Also, why would the following happen:
Use master -- note: No table named product
select * from xxx.dbo.product - 8 seconds
use yyyy -- note: product table only has 200 records
select * from xxx.dbo.product - 9 seconds
use xxxx
select * from product -- 12 seconds
use zzz -- has 32k records
select * from xxx.dbo.product - 14 seconds
Thank you.
August 30, 2017 at 4:36 pm
To check your network latency, you'd talk with your network administrator.
How's the query performance if you run it from a different workstation? When I say that, I mean on different hardware.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply