Client Statistics / Network Statistics

I am trying to track down a slowly preforming query and am looking at the client statistics tab; specifically the Network Statistics and Bytes received from server.

I have two servers one running SQL Server 2000 and the other running SQL Server 2005, 64-bit.

The data on the servers is similar, some schema differences, some record differences -- but nearly 32 million records in one table.

The same query is taking longer on the new SQL Server 2005 machine. The main difference that I see on the client statistics tab is the bytes received from server is almost always triple on the new server.

Were there any changes in the information SQL Server 2000 and 2005 reports back? Could the 64-bit be the cause for the increase (might make sense if it were double, but not triple)?

Any other ways I can eliminate this metric from being the cause of the slow running queries?

Thanks, Richard

If I run a simple SELECT * FROM Orders against the Northwind database on SQL Server 2000 with Query Analyzer or on SQL Server 2005 (32-bit) with Management Studio I get 830 rows on each, and 157,065 bytes on 2000 vs 157,097 on 2005. (Get the Northwind database from here http://msdn2.microsoft.com/en-us/library/ms143221.aspx). Try it on your 64-bit server and see what you get. If you also get 157,097 then maybe you're using different data types in your database on the 64-bit server?|||

Hi Richard,

I don't know the answer to the question of client statistics but I would personally check the execution of both of your queries through Profiler. Look at READS and CPU for both versions and see if there is a difference internally to SQL Server before checking to see the results being sent back to the client.

The DURATION counter could be misleading in your situation because the time taken to transfer the data back to the client (and the client to close the resultset) will factor into duration (actually, the DURATION counter is usually misleading due to issues of locking, blocking, client conneciton remaining open, low bandwidth to server, Disk bottlenecks, etc).

In regards to 64-bit showing double the number of characters, everything I've read suggests that 64-bit is the same code base and that your client app should not notice any difference between connecting to a 32-bit SQL Server or a 64-bit SQL Server.

You didn't mention how many rows and columns were returned by each query but if your data and schema is not 100% identical on both servers then I would assume the issue is in the data being returned.

Jared

|||

I started with SQL Profiler and saw that the duration for my complex query was almost always slower on my new server. There are definite differences in the execution plans, but the number of rows returned is 23 and the fields requested are identical -- there are differences in the length of some of the varchars, but the actual data returned is identical.

Schema and rows shouldn't be a factor -- indexes and statistics are the same, execution plans are different and the "Bytes received from server" are different. Time statistics are slower.

I'm in the process on loading Northwind on these two servers so that I can compare with the other respondent. However, a DBA in another department ran a similar test against SQL Server 2000, SQL Server 2005, and SQL Server 2005, 64-bit and found that the Bytes received was greater (double to triple) on SQL Server 2005; reguardless of the 64-bit.

I'll run some more tests and post my results -- I just hope that I'm not chasing some difference in the "Client Statistics" tab between 2000 and 2005.

Thanks for your interest, Richard