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 a
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 t
--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

 

Leave a Reply

Your email address will not be published. Required fields are marked *