June 10, 2008 at 9:54 am
Another interesting detail.
SET STATISTICS IO ON
gave these results:
PROD (SQL Server 2000)
-----------------------
(2682 row(s) affected)
Table 'luRRs'. Scan count 1, logical reads 52, physical reads 0, read-ahead reads 43.
Table 'LuPersons'. Scan count 1, logical reads 33, physical reads 0, read-ahead reads 0.
Table 'LuBranch'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0.
DEV (SQL Server 2005)
----------------------
(2679 row(s) affected)
Table 'Worktable'. 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 'luRRs'. Scan count 1, logical reads 53, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LuPersons'. Scan count 1, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LuBranch'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
What is this Table 'Worktable'?
Is it because it's SQL 2005?
Other than that Logical Reads are identical.
I'm afraid I'm digging in the wrong direction.
The fact that locally the query executes fine
on DEV (SQL Server 2005) , elapsed time > 300 ms, is the key I think.
I think it narrows down the circle of possible
causes. Correct me if I'm wrong.
To me it means that the performance problem is outside SQL Server
box. All IO,Latch,Lock problems scenarios
are eliminated and I only have to look
at the the part where the query results
are being transported back to the client machine.
In other words NETWORK.
I already did some test downloads/uploads
to and from that server and here is an interesting thing.
Download: 8 MB = 4 sec
Upload: 8 MB = 1.37 min
All uploads fail with "Cannot copy file or folder. Path is too deep" error.
The file gets uploaded but always with this error at the end of copying.
Guys at http://forums.enterpriseitplanet.com/printthread.php?t=2109
say
"...This is a network connectivity issue, could be MTU size, network speed, half / full duplex setting, 10/100/1000/Auto setting on your NIC and switch."
I think I need to talk to the Network guys.
I should have done these tests at the very beginning.
Sorry guys for waisting your time here.
June 10, 2008 at 12:14 pm
riga1966 (6/10/2008)
What is this Table 'Worktable'?
Is it because it's SQL 2005?
It's a temp table created and used by the query processor to store portions of the result set during processing. Most likely either a hash match or hash join.
Have you got any results for the wait type? If you can't catch it in sys.dm_exec_requests cause it's too fast, you can use the dmv sys.dm_os_wait_types. Capture the values before you run the query and after (it's cumulative stats since the server was restarted) and see where the wait time was.
The reads maybe almost the same, but the presence of the work table indicates that the execution plan for the two (dev and prod) is different.
Can you run the query on both environments with the execution plan enabled, save the plans in their xml format (.sqlplan), zip and attach to your post?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 10, 2008 at 1:02 pm
Guys!
The problem was that on the Network Switch for the port the server was connected to Speed was hard-coded and there was some fixed value.
I think it was 100 Mbps
On the actual Network Card on the server it was set to "Auto".
Network guys explained that it's a conflict and it caused lots of
network packets bounce back.
Once they changed it to "Auto" on the switch
I got excellent speeds:
SET STATISTICS TIME ON
CPU time = 15 ms, elapsed time = 64 ms.
CPU time = 47 ms, elapsed time = 62 ms.
CPU time = 63 ms, elapsed time = 68 ms.
To be a good SQL Server Developer you need good relationships with network guys ... 🙂
Thank you all guys for help.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply