RESTORE DATABASE - BUFFERCOUNT

Question

RESTORE DATABASE - BUFFERCOUNT

Jared

SSC Guru

Points: 61793
More actions
July 22, 2014 at 12:58 pm

#310148

Anyone have a good description of what the BUFFERCOUNT option does in a RESTORE DATABASE command? Can't find anything online (except that increasing it sometimes helps) and nothing in my internals books.
Jared
CE - Microsoft

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

http://sirsql.net/blog/2011/9/26/optimizing-database-restores.html

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Robert Klimes SSCoach Points: 17865 More actions · Answer 2

Specifies the total number of I/O buffers to be used for the restore operation. You can specify any positive integer; however, large numbers of buffers might cause "out of memory" errors because of inadequate virtual address space in the Sqlservr.exe process.

from the Restore arguments Books online page

http://msdn.microsoft.com/en-us/library/ms178615.aspx

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

Jared SSC Guru Points: 61793 More actions · Answer 3

Thanks Gail and Bob, but what I am really curious about is how that works; i.e. is the database restored into memory and then written to disk? It just doesn't make sense to me...

Jared
CE - Microsoft

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

No, of course not. If that was the case, how would a restore ever work for a database larger than physical memory?

They're buffers, in the normal computer science definition of the word (http://en.wikipedia.org/wiki/Data_buffer). There's nothing fancy or unique about them, they are simple buffers, the buffercount defines how many are and the buffersize defines how big they are.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jared SSC Guru Points: 61793 More actions · Answer 5

GilaMonster (7/22/2014)
No, of course not. If that was the case, how would a restore ever work for a database larger than physical memory?
They're buffers, in the normal computer science definition of the word (http://en.wikipedia.org/wiki/Data_buffer). There's nothing fancy or unique about them, they are simple buffers, the buffercount defines how many are and the buffersize defines how big they are.

I guess I thought of it more as a pass through/middleman. So, increasing the amount of buffers and size is like increasing the lines open at an amusement park? More lines=more people in a shorter amount of time? Of course... if there are to many lines, the entry way can get crowded. Am I close?

Jared
CE - Microsoft