Primitive Allocations
Before any pages can be allocated from the BPool, SQL Server must allocate the support structures required to manage it. The first of these that we'll talk about is a global variable to hold a reference to an instance of the class that defines the BPool. Because this variable has global scope, you can see it yourself using WinDbg and the public symbols that ship with SQL Server. Exercise 11.1 takes you through locating both the global variable and its host data type.
Exercise 11.1 Using WinDbg to Find the Buffer Pool
1. | Attach to your nonproduction SQL Server with WinDbg. |
2. | |
3. | Our next step will be based on two assumptions.
|
4. | At the WinDbg command prompt, type: .reload -f sqlservr.exe x sqlservr!* This will list all of the public symbols included in sqlservr.pdb, the program database (symbol) file for SQL Server. |
5. | Scroll to the top of the command window and click above the start of the output from the x command. Press Ctrl+F, type BPool, and press Enter. |
6. | You should find the first occurrence of a reference to the BPool class. This is actually a reference to one of its methods. We can deduce from this that SQL Server has a class named BPool. It's a fair guess that this is the data type of the object that stores the SQL Server buffer pool, but we'll establish that with a good degree of certainty in just a moment. |
7. | Now let's look for the global variable that we suspect stores the reference to the buffer pool. Scroll to the top of the output and repeat your search, this time specifying “bPool” for the search string (no quotes). Be sure to specify a case-sensitive search in the dialog. Because C++ is a case-sensitive language, it's common for developers to name an instance of a variable after its type using different case. We'll start by checking for that. |
8. | If your search was case-sensitive, it should not have found any symbols named “bPool,” so we need to keep looking. Another common tactic is to spell out a type name but abbreviate the names of instances of it or vice versa. Since we already know the type name is BPool, let's look for it spelled out as BufferPool. Repeat your search, this time specifying BufferPool as the search criteria. |
9. | You should find a symbol named sqlserver!BufferPool. Notice that this isn't prefixed by a class name and a pair of colons (::) as the BPool reference you found earlier was. This means that it's a global of some type. Based on the name alone, we can deduce that it's probably not a global function. Thus, it's likely a global variable and probably the one that stores the reference to the SQL Server buffer pool. |
10. | At this point, you could dump the contents of the BufferPool variable to the console using the dd command or something similar. The value of the variable itself isn't terribly useful to us at this point; I just wanted you to see that it was indeed a global. All of SQL Server's BPool functionality is wrapped up in the BPool class and in BufferPool, the single, global instance of it. |
11. | Scroll back to the top of the command window and repeat your search, this time using “DropCleanBuffers” as your search string. You should find an entry for BPool::DropCleanBuffers in the symbol list. |
12. | Readers of my previous books will recall the discussions of the DBCC DROPCLEANBUFFERS command. This command was once undocumented but is handy for releasing the clean buffers from the BPool in order to test a query with a cold cache without cycling SQL Server. Given that the BPool class has a member named DropCleanBuffers, might this be what the DBCC command calls? Let's set a breakpoint and find out. |
13. | At the WinDbg command prompt, type: bp sqlserver!BPool::DropCleanBuffers g |
14. | Now, switch over to Query Analyzer, connect to your server, and run DBCC DROPCLEANBUFFERS in the editor. Switch back to WinDbg. You should see that execution has stopped at your breakpoint. This tells us definitively that DBCC DROPCLEANBUFFERS is implemented via a method off of the BPool class named DropCleanBuffers. It also reinforces our assertion that the BPool class we see in the debugger is the actual data type of SQL Server's global buffer pool instance. |
15. | Type q in the WinDbg command window and press Enter to stop debugging. You'll need to restart your SQL Server. |
Page Arrays
I mentioned earlier that SQL Server makes up to 32 separate memory reservations to reserve the BPool. The BPool tracks these allocations in two parallel arrays—one array stores a list of pointers to the start of each region, the other stores a count of the 8K pages reserved in the region. Both arrays are private members of the BPool class.
BUF Array
SQL Server uses a special BUF structure to manage each page in the BPool. Before reserving the BPool, SQL Server calls VirtualAlloc to allocate an array of BUF structures from the MemToLeave region equal in size to the number of pages it will reserve for the BPool (including physical AWE pages). Each page in the BPool will have a corresponding BUF structure, as will each page of AWE memory allocated by the server, regardless of whether it has been mapped into virtual memory. Since each BUF structure is 64 bytes in size, this array isn't usually very large unless the server is using a significant amount of AWE memory.
Each page's BUF structure functions as a type of header for it. It stores information such as a pointer to the actual page in the BPool, the reference count for the page, the page's latch, and status bits that indicate whether the page is dirty, has I/O pending, is pinned in memory, and so on.
When the lazywriter traverses the pool looking for pages to free, this array of BUF structures is what it actually sweeps. We'll discuss the lazywriter further in just a moment.
Commit Bitmap
On startup, SQL Server allocates a bitmap from the default process heap that it uses to track committed pages in the BPool. The original reservations tracked by the page arrays are just that—reservations. As we discussed in Chapter 4, it's possible to reserve virtual memory address space without committing any physical storage to it. As each page in the BPool is committed, its corresponding bit in the commit bitmap is set.