After clarifying the terminology in my previous post, it is time to perform some use cases.
In this use case I will take a simple query, like this one:
1 2 3 4 5 6 7 8 9 10 11 12 13 14
use [AdventureWorks] SELECT TOP 10 [SalesOrderID] ,[SalesOrderDetailID] ,[CarrierTrackingNumber] ,[OrderQty] ,[ProductID] ,[SpecialOfferID] ,[UnitPrice] ,[UnitPriceDiscount] ,[LineTotal] ,[rowguid] ,[ModifiedDate] FROM [Sales].[SalesOrderDetail]
and will execute it once towards my local SQL Server. (I have a SQL Server 2008 Developer Edition installed on a desktop)
In this case the query is 316 bytes, which means that it will fit easily in one TDS packet and the data returned is 1518 bytes, which will also fit easily in one TDS packet.
(If you are wondering how I know all this – in this case I am using the Client Statistics in Management Studio to look at the TDS packets and bytes sent to and from the client. For more details there is a good post on Client Statistics on BrentOzar.com by Jess Schultz Borland and I also wrote about it long ago on SQLAuthority.com)
Unfortunately, there is no easy way to keep statistics of how long it actually takes to connect to SQL Server and how long before the data starts flowing to the application and how long it takes for all the data to come to the application.
In other words, the Connection Latency, Query Processing Latency and Data Receiving Latencymetrics are hidden behind the scenes and very few DBAs or IT professionals really care about them. (And as we will see very soon in these blog series – the problem can get complex quickly and as it gets complex – there are more and more chances that people will start noticing the difference.)
So, how do the latency metrics look like when I execute the above query on my local machine? Here are the results:
Connection Latency (ms) = 3
Query Processing Latency (ms) = 38
Data Receiving Latency (ms) = 2
Total Latency (ms) = 42
Now, the above results are in the case of ‘cold cache’ (i.e. the plan and data caches are flushed), but when we execute the test a second time, the results look like this:
Connection Latency (ms) = 3
Query Processing Latency (ms) = 1
Data Receiving Latency (ms) = 1
Total Latency (ms) = 4
So what is the time used for? Here is the detailed result from the ‘cold cache’ test:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 21 ms
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 16 ms
The bottom line is that data and plan caching matters for performance, but the connection latency is always an additional wait time (for the purposes of this test I have disabled the connection pooling on the application level, i.e. every time the connection is established as if it is for the first time).
In a later post we will explore the use case of multiple concurrent connections and also requests and datasets which do not fit in a single TDS packet.