Performance Myths: The query result cache | Times of server

Performance Myths: The query result cache

Visitor Author : Andy Mallon (@AMtwo)

I was as of late completing an instructional meeting when a designer remarked that it was OK to run a costly inquiry twice in light of the fact that on the second execution, SQL Server would utilize the “results reserve” and be “basically free”. It’s not the first occasion when I’ve heard somebody allude to a “results store” in SQL Server. This is one of those fantasies that is valid, which makes it considerably more trustworthy. In the event that you don’t know better, you may think SQL Server has a “results store” in light of the fact that the second execution of a question is frequently speedier.

SQL Server does not have a “results store” and the second execution isn’t “for all intents and purposes free.”

SQL Server has a “support reserve” and the second execution is “speedier, yet not free.”

The SQL Server cushion reserve holds information pages in memory, in the correct shape that they live on plate. The second execution won’t need to perform physical I/O tasks to fulfill the inquiry, since it can utilize the support reserve. Be that as it may, it has to play out every single other activity. Consider it like this: the second execution still executes the whole execution plan, including all the costly activities. It is quicker, yet not “for all intents and purposes free.”

How about we make a straightforward case to show that the second execution can even now be moderate and costly.

In the first place, I’ll make a table and put a few information in it. Envision this is some enormous table from a genuine framework you chip away at.

Make TABLE dbo.SomeBigTable (

ID INT IDENTITY(1,1),

PretendRowData NCHAR(4000),

Requirement PK_SomeBigTable PRIMARY KEY CLUSTERED (ID)

);

Embed INTO dbo.SomeBigTable (PretendRowData)

SELECT REPLICATE(N’x’,4000)

FROM sys.columns c1, sys.columns c2;

On my test database, this makes a 7.6 GB table. That’ll be bounty enormous to test at scale! How about we begin basic. To even the odds, I’ll get out the reserve:

– Start with a spotless reserve

DBCC DROPCLEANBUFFERS

At that point, I’ll run an inquiry three times that pulls back a solitary column. The principal execution will be moderate, at that point the following two will be quick right?

– Run the inquiry thrice; The second and third executions are free, isn’t that so?

SELECT PretendRowData

FROM dbo.SomeBigTable

WHERE ID = 1000000;

GO 3

Whoa, each of the three were super quick. On the off chance that I run these three executions in Plan Explorer, I see that the main execution reliably demonstrates a term of 1-2 milliseconds, and the following two executions demonstrate a span is clear. Plan Explorer indicates clear term when the execution time is sub-millisecond. That resembles the fantasy is valid all things considered! The outcomes store exists! Those executions were totally free!

How about we attempt again with a more costly question. I will compel a table output by applying a capacity to the ID section. This question gives precisely the same set as the first, it just uses an alternate execution intend to arrive. Once more, we’ll clear the reserve first, at that point run the question three times.

– Start with a spotless reserve, once more

DBCC DROPCLEANBUFFERS

GO

– Run the question thrice; The second and third executions are free, isn’t that so?

SELECT PretendRowData

FROM dbo.SomeBigTable

WHERE CONVERT(varchar(10),ID) = 1000000;

GO 3

This question is a considerable amount slower. Plan Explorer demonstrates that the execution time on the main execution was 11 seconds, and the following two take around 9 seconds. Speedier, however not free.

The question result is the same PretendRowData on every one of the six executions of the two kinds of this inquiry. On the off chance that there were a “results store” at that point no less than four of the executions ought to have originated from that reserve and been “for all intents and purposes free”. The second kind of the question plays out a table sweep, so it needs to peruse every one of the 7.6GB of information keeping in mind the end goal to restore the single-push result. Truth be told, Plan Explorer demonstrates that the critical CPU and (consistent) I/O cost is equivalent for every one of the three executions. The question is speedier, in light of the fact that the table is in memory in the support store, however it’s not really free.

On the off chance that you have costly questions where you need to utilize stored inquiry results, there are some incredible advances that are reason fabricated only for that. Two well known memory reserving items are Redis and Memcached. These memory-enhanced reserves scale on a level plane, influencing them to appropriate for offloading costly, visit, read-just inquiries from your generation database.

About the Author

Visitor Author : Andy MallonAndy Mallon is a SentryOne Product Manager and Microsoft Data Platform MVP. Andy’s experience is as a DBA supporting high-volume, profoundly accessible OLTP conditions with requesting execution needs. Over his vocation he has overseen databases in the human services, back, web based business, and non-benefit areas. Andy is the originator of BostonSQL, past coordinator of SQLSaturday Boston, and websites at am2.co.

<script>

Leave a Reply

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

Bitnami