THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Response time vs. resource consumption

I see a trend towards more and more focusing on response time; and less and less on resource usage (resource consumption). I've even seen blanket statements such as the only thing that matters is response time. I do not agree. I feel that by being a good citizen and consume as few resources and possible, we contribute to the overall welfare of the system.

For instance, I'm fortunate to have some 8 km (5 miles) to my main client. I can take the car, which often is about 15 minutes or I can bicycle, which is about 20 minutes. For many reasons, I prefer to bicycle. The thing here is that I compromise a little bit and accept a few more minutes when going by bicycle, but I feel I'm a better citizen and contribute to a more sustainable society. But not only that: ever so often, the traffic is congested, and now the car takes some 40-45 minutes (bicycle still 20 minutes). By using the bicycle I both consume less resources and I also have a higher degree of predictability. Now, is this analogy appropriate to database performance? I don't know, perhaps to some extent... But let me give you a database example, from real life, followed by a TSQL example:

I have a client who had this query which used to be quick (enough) and suddenly was very slow. Been there before, we know this can be just about anything. Anyhow, it was pretty quick for me to find the reason. The query had an ORDER BY and a FAST hint. The FAST hint tells SQL Server to return rows to the client as fast as possible, but possibly with a higher overall cost. The developer who added that FAST hint didn't really think that hard about it, and just "threw it in there". It sounds good, doesn't it? There was a non-clustered index (non-covering) on the sort column and also some restrictions (WHERE clause).

With the FAST hint, SQL Server used the index on the sort column to drive the query and for each row it did a "bookmark lookup" to fetch the row. This means a page access for each row, but rows can be returned to the client application immediately (think streaming). Without the fast hint, SQL Server first sorted the relevant rows into a worktable and then returned the rows in sorted order from that worktable. So we have a tradeoff between reading a lot of pages (possibly some from cache) or doing some work up-front to sort data and then just read that sorted worktable sequentially.

The worrying part here is that with a small table, it will fit in cache and the difference between the two might not be that drastic (either way). But as table grew larger, it won't fit in cache anymore and as we see logical I/O turning into physical I/O things really go south for the query with the FAST hint. This is what happened to my client. Table grew and a query which had OK response time suddenly was a disaster. If that FAST hint wasn't there in the first place, my client wouldn't have this slowness in the application over the two weeks it took until I had time to look over it and remove the FAST hint (I also added a couple of indexes, but that is beside the point).

Seeing is believing, right? At the end of this blog post, you will find TSQL that pretty much mimics my client's case. It populates a table with 800,000 rows and there's a non-clustered index on the sort column. We then try some variations to check response time, CPU seconds usage, I/O and duration. I measured response time using TSQL (as seen in the script). I also measured response time and the other metrics using Profiler.

The size of the table (clustered index on identity column) is 133MB and the non-clustered index to on the sort column is 11MB. This is a small table, but that makes things more manageable; and by setting the max server memory to a low value (60MB), we can still see the effect of logical vs. physical I/O.

We first run the query and have a filter that restricts to 4,000 rows out of 800,000 rows. Note that there's no index on the filter column.

  • The query without a FAST hint was very consistent. The response time was either 0.05 seconds (without clearing cache first) or 1.9 seconds (if we clear cache first). This was regardless of if we configured with 500MB or 50MB memory for sp_configure 'max server memory'.
  • The query with FAST hint was OK with memory setting of 500MB, so the table would fit in cache: 1.6 seconds to 4.4 seconds (depending on whether we empty cache before execution). But when we lower memory setting (think "large table"), the execution time jumped up to 73 seconds. That is a factor of between 48 and 1460.

Things got a bit different when we removed the WHERE clause to return all rows:

  • Query without FAST hint took between 10 seconds and 23 seconds (depending on whether we empty cache first) for a memory setting of 500MB. Lowering memory to 60MB made this one take between 23 and 41 seconds. Note that I here got some error messages from SQL Server regarding insufficient memory in the internal memory pool (possibly SQL Server now did some fall-back strategy for the query execution, which added to execution time).
  • The query with the FAST hint outperformed the one without for a memory setting of 500MB, with execution time between 2.2 and 5.6 seconds. Note that I configured SSMS to discard results so there is no processing of the returned 800,000 rows included here. With a memory setting of 60MB, we again bumped up execution time to some 74 seconds.

Here are the full numbers:

ms ms Profiler ms cpu io fast hint memconf cache clean rows returned
1930 2023 202 18782 0 500 1 4000
53 60 110 18768 0 500 0 4000
4403 4497 2075 2695310 1 500 1 4000
1616 1622 1622 2551439 1 500 0 4000
1930 1977 171 18768 0 60 1 4000
56 59 94 18768 0 60 0 4000
72426 72479 10888 5513944 1 60 1 4000
72663 72728 10983 5521626 1 60 0 4000
23336 23391 2105 31738 0 500 1 800000
10263 10269 2559 31574 0 500 0 800000
5663 5703 2386 2695368 1 500 1 800000
2226 2235 2028 2551439 1 500 0 800000
40966 40975 2620 31654 0 60 1 800000
22906 22913 2714 31629 0 60 0 800000
73676 73709 11045 5512080 1 60 1 800000
74513 74557 11778 5522556 1 60 0 800000

 

For the sake of completeness, I should add that having a good supporting index for the restriction (for the queries that had a restriction) made the query go equally fast regardless of memory config or FAST hint (in fact the FAST hint was irrelevant with a good index).

Here's the T-SQL if you want to play with it. As always, don't execute anything if you don't understand the code and the consequences of executing it!

EXEC sp_configure 'max server memory'500
RECONFIGURE
GO

USE master
GO
IF DB_ID('TestDb'IS NOT NULL DROP DATABASE TestDb
GO
CREATE DATABASE [TestDb] 
ON 
PRIMARY 
(NAME N'TDb'FILENAME N'C:\TDb.mdf'
,SIZE100MBMAXSIZE 200MBFILEGROWTH 30MB )
LOG ON 
(NAME N'TDb_l'FILENAME N'C:\TDb_l.ldf'
,SIZE 200MBMAXSIZE 500MBFILEGROWTH 20MB )
GO

USE testDb

CREATE TABLE t(c1 INT IDENTITY PRIMARY KEY CLUSTEREDc2 INTc3 INTfiller CHAR(150))

INSERT INTO (c2c3filler)
SELECT TOP(80000011'hi'
FROM sys.columns AS 
 
CROSS JOIN sys.columns AS b
 
CROSS JOIN sys.columns AS c

UPDATE SET c2 c1 20c3 c1 200

CREATE NONCLUSTERED INDEX ON t(c2)

--Size of table and indexes
EXEC sp_indexinfo 
--Can be found at http://www.karaszi.com/SQLServer/util_sp_indexinfo.asp


IF OBJECT_ID('tmp'IS NOT NULL DROP TABLE tmp
GO
CREATE TABLE tmp
(seq tinyint IDENTITY(1,1PRIMARY KEY NOT NULL
,
ms INT NOT NULL
,
ms_profiler INT NULL
,
ms_cpu INT NULL
,
io_ INT NULL
,
fast_hint bit NOT NULL
,
memconf smallint NOT NULL
,
cache_clean bit NOT NULL
,
rows_returned INT NOT NULL)
GO

-----------------------------------------------------------------------------------------
--Create procedures
IF OBJECT_ID('emptyCache'IS NOT NULL DROP PROC emptyCache
GO
CREATE PROC emptyCache AS
BEGIN
  CHECKPOINT
  DBCC 
DROPCLEANBUFFERS
END
GO

IF OBJECT_ID('do_it'IS NOT NULL DROP PROC do_it
GO
CREATE PROC do_it
 
@fast_hint bit@memconf smallint@cacheclean bit@rows_returned INT
 WITH 
RECOMPILE
 
AS
BEGIN
  DECLARE 
@dt datetime SET @dt GETDATE()
  
  
IF @fast_hint CAST(AS bit)
   
IF @rows_returned 4000
       
SELECT FROM WHERE c3 16 ORDER BY c2
   
ELSE --return all rows
       
SELECT FROM ORDER BY c2
  
ELSE --add FAST hint
   
IF @rows_returned 4000
       
SELECT FROM WHERE c3 16 ORDER BY c2 OPTION(FAST 20)
   
ELSE --return all rows
       
SELECT FROM ORDER BY c2 OPTION(FAST 20)

  
INSERT INTO tmp(msfast_hintmemconfcache_cleanrows_returned)
  
VALUES(DATEDIFF(ms@dtGETDATE()), @fast_hint@memconf@cacheclean@rows_returned)
END
GO



TRUNCATE TABLE tmp
-----------------------------------------------------------------------------------------
--Return 4000 rows
-----------------------------------------------------------------------------------------

--500 MB memory
EXEC sp_configure 'max server memory'500 RECONFIGURE
GO
--Without FAST
EXEC emptyCache
GO
EXEC do_it @fast_hint 0@memconf 500@cacheclean 1@rows_returned 4000
GO
EXEC do_it @fast_hint 0@memconf 500@cacheclean 0@rows_returned 4000
GO
--... with FAST
EXEC emptyCache
GO
EXEC do_it @fast_hint 1@memconf 500@cacheclean 1@rows_returned 4000
GO
EXEC do_it @fast_hint 1@memconf 500@cacheclean 0@rows_returned 4000
GO

--50 MB memory
EXEC sp_configure 'max server memory'60 RECONFIGURE
GO
--Without FAST
EXEC emptyCache
GO
EXEC do_it @fast_hint 0@memconf 60@cacheclean 1@rows_returned 4000
GO
EXEC do_it @fast_hint 0@memconf 60@cacheclean 0@rows_returned 4000
GO
--... with FAST
EXEC emptyCache
GO
EXEC do_it @fast_hint 1@memconf 60@cacheclean 1@rows_returned 4000
GO
EXEC do_it @fast_hint 1@memconf 60@cacheclean 0@rows_returned 4000
GO


------------------------------------------------------------------------------------
--Return all 800,000 rows
------------------------------------------------------------------------------------

--500 MB memory
EXEC sp_configure 'max server memory'500 RECONFIGURE
GO
--Without FAST
EXEC emptyCache
GO
EXEC do_it @fast_hint 0@memconf 500@cacheclean 1@rows_returned 800000
GO
EXEC do_it @fast_hint 0@memconf 500@cacheclean 0@rows_returned 800000
GO
--... with FAST
EXEC emptyCache
GO
EXEC do_it @fast_hint 1@memconf 500@cacheclean 1@rows_returneed 800000
GO
EXEC do_it @fast_hint 1@memconf 500@cacheclean 0@rows_returned 800000
GO

--50 MB memory
EXEC sp_configure 'max server memory'60 RECONFIGURE
GO
--Without FAST
EXEC emptyCache
GO
EXEC do_it @fast_hint 0@memconf 60@cacheclean 1@rows_returned 800000
GO
EXEC do_it @fast_hint 0@memconf 60@cacheclean 0@rows_returned 800000
GO
--... with FAST
EXEC emptyCache
GO
EXEC do_it @fast_hint 1@memconf 60@cacheclean 1@rows_returned 800000
GO
EXEC do_it @fast_hint 1@memconf 60@cacheclean 0@rows_returned 800000
GO

 

Published Monday, June 15, 2009 11:01 AM by TiborKaraszi
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Linchi Shea said:

> I feel that by being a good citizen and consume as few resources and possible, we contribute to the overall welfare of the system.

Doesn't 'overall welfare of the system' mean satisfactory response time, perhaps for other queries, or satisfactory throughput? I guess one could argue that in the end it's the response time (or the throughput) that really matters for a database system. Resource consumption matters to the extent that it affects response time/throughput, but never matters in itself. That is probably the context in which it is said that resource consumption doesn't matter.

June 15, 2009 11:14 AM
 

TiborKaraszi said:

Hi Linchi,

Yes, aboslutely. But if one only measure one's response-time one wouldn't know what effect that might have on the overall system throughput. I tend to see these type of statements in the context to only measure query time and not keeping an eye on I/O or CPU consumption.

June 15, 2009 11:37 AM
 

Alexander Kuznetsov said:

Hey Tibor,

Nice point! Yet I think that queries are not born equal - some have higher priority and as such must return soon even if it slows down other ones. Using your analogy, becides bicycles we need helicopters for medical emergencies too. I guess I'd post a detailed reply to your post soon.

June 15, 2009 12:13 PM
 

TiborKaraszi said:

Hi Alex,

Thanks :-). I'm not arguing that there are exceptions. I'm fine with that. We "experts" know this and are aware of the nuances when looking at execution plans and various query execution statistics. But imagine the crowd out there who aren't at that level. That is why I shy away from such blanket statements and the old "it depends" phrase need to be brushed up and polished yet another time.

For instance, in my case, I believe the client app was blocked until all rows were returned anyhow. I.e., they never took advantage of the asynchronous physical query execution. (I never verify that since removing the hint and adding a couple of indexes sorted this out anyhow....)

June 15, 2009 12:27 PM
 

Alexander Kuznetsov said:

When your query must always return in a certain amount of time, trusting the optimizer might not be the

June 16, 2009 11:11 PM
 

Mario said:

Tibor, nice post, but, eh, it still looks like you are looking for optimal response time, aren't you?

In your case response time turned bad because the query suddenly caused physical I/O to happen.

Personnaly I think normally optimizing queries to achieve 'best' response time, involves reducing resource utilization like CPU time or (physical) I/O.

Exceptions might be 'space for time' optimizations where you use memory to save time (cursor or db block caching), or parallization like using parallel queries, that might reduce response time, but at a great cost: bigger memory footprint, more CPU and I/O bandwith.

But if the end user pays for response time, why not throw the resources at it?

June 20, 2009 12:24 PM
 

TiborKaraszi said:

Mario,

I'm not sure I understand you. Of course response time is the ultimate. But we need to look at the collective here, which is why we also keep an eye on resource utilization. That was my point, and your below commane doesn't seem to contradict that:

<<Personnaly I think normally optimizing queries to achieve 'best' response time, involves reducing resource utilization like CPU time or (physical) I/O. >>

I never said there are no exceptions. Of course there are exceptions, but hopefully we should be aware of when those are used and why we use them.

<<But if the end user pays for response time, why not throw the resources at it?>>

If you mean HW resources, then the answer is simple: because you reach a point where you can't go any longer. The table is so large so even with all data in cache the in-cache memory access is too slow. Or the table is even larger and there's no budget to buy those 512 GB of memory. Etc.

June 20, 2009 3:20 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement