Set Cache Size of Sequence

Question

Post reply

Set Cache Size of Sequence

robh0502

SSC Enthusiast

Points: 199
More actions
May 27, 2021 at 5:08 am

#3888531

Hi,
I've got the following version of SQL Server installed.
Microsoft SQL Server 2016 (SP2-CU16) - 13.0.5882.1 (X64) Jan 25 2021 21:40:32 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: )
I've been reading about creating sequences in SQL Server. However, can't find much material on what to set the cache size to. Does anyone know how to go about selecting a cache size in sequences?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16164 More actions · Answer 1

reading the manual helps - that is why it was written.

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver15

ScottPletcher SSC Guru Points: 101117 More actions · Answer 2

This depends on your use of the sequence. If you use the sequence often, you will want a larger cache. If less often, a smaller cache makes more sense.

Also, be aware that the larger the cache the larger the possible gap in numbers if SQL goes down for any reason. Numbers left in the existing cache will naturally be lost, and a new cache started when SQL comes back up.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

robh0502 SSC Enthusiast Points: 199 More actions · Answer 3

Thanks Scott. After I posted my question, I was thinking along the lines that you wrote.

robh0502 SSC Enthusiast Points: 199 More actions · Answer 4

Frederico, I read this document a few days ago. It doesn't get into what to set the cache size to. But, after I posted, I realized as Scott said that it depends on usage of the sequence. For loading large amounts of data at once like 10k records, set the cache to 10k. But for small inserts like once every few seconds, then a smaller cache size is better.

frederico_fonseca SSCoach Points: 16164 More actions · Answer 5

well. half that page is talking about cache sizes and management so in my opinion it was enough to guide me - note that cache management is only an issue with small cache and lots of concurrency asking for same sequence - other than that it is a minor I/O operation that would not really be noticed with average cache values - I have never had any need to increase it from the default value calculated by SQL.