Go Why does SQL Server use so much memory?

Original address: HTTP://SUPPORT.MICROSOFT.COM/GP/ANXIN_TECHTIP6/ZH-CN

Why does SQL Server use so much memory? SQL Server users often find that the SQL process uses a lot of memory. Most of this memory is used to cache the data the user wants to access in order to achieve optimal efficiency. How do you know what data is now being cached in memory? In fact, the database administrator to run a few queries, you can get the answer.

Who's taking my buffer Pool?

I was doing SQL Server 7.0 technical support when a customer asked me, "My SQL Server buffer pool is large, is there a way to know which objects eat my buffer pool memory?" For example, can you tell which database it is, which table, and which index occupies buffer pool? "I didn't find the answer to that question at the time, but I kept that in mind. This problem was solved until the SQL Server 2005 version appeared. The answer is to use the Dynamic view (DMV) sys.dm_os_buffer_descriptors. This DMV is very powerful. Based on SQL Server Books Online, this view is "returning information about all the current data pages in the SQL Server buffer pool." You can use the output of this view to determine the distribution of database pages within a buffer pool based on the database, object, or type. Specifically, this view returns the following properties of a 8K data page in buffer pool: (1) which database the page belongs to (2) which file (3) of the page belongs to the database and the page_id (4) type of the page. This can be used to determine whether the page is indexed or data page (5) How many rows of data are in the page (6) How much free space the page has. (7) Whether the page has been modified since it was read from disk. With the above information, we can easily count a few useful data, as follows.

1. The memory of Buffer pool is mainly occupied by that database?

SELECT Count (*) *8 as Cached_pages_kb,case database_id

When 32767 and then ' Resourcedb '

ELSE db_name (database_id)

END as database_name

From Sys.dm_os_buffer_descriptors

GROUP by Db_name (database_id), database_id

ORDER by cached_pages_kb DESC;

The results are as follows: From the above results you can see that the database AdventureWorks occupies about 30MB of buffer pool space. Note that the DMV does not return information about non-data pages (such as cache execution plans) in buffer pool. This means that the DMV does not return information for all pages in the buffer pool.

2. More specifically, which table or index in the current database occupies the most pool buffer space?

SELECT Count (*) *8 as cached_pages_kb

, Obj.name, Obj.index_id,b.type_desc,b.name

From Sys.dm_os_buffer_descriptors as BD

INNER JOIN

(

SELECT object_name (object_id) as name

, index_id, allocation_unit_id,object_id

From Sys.allocation_units as Au

INNER JOIN sys.partitions as P

On au.container_id = p.hobt_id

and (Au.type = 1 OR au.type = 3)

UNION All

SELECT object_name (object_id) as name

, index_id, allocation_unit_id,object_id

From Sys.allocation_units as Au

INNER JOIN sys.partitions as P

On au.container_id = p.partition_id

and Au.type = 2

) as obj

On bd.allocation_unit_id = obj.allocation_unit_id

Left joins sys.indexes b on b.object_id = obj.object_id and b.index_id = obj.index_id

WHERE database_id = db_id ()

GROUP by Obj.name, obj.index_id, B.name,b.type_desc

ORDER by cached_pages_kb DESC;

The output is as follows (partial): From the above results you can see that the table individual is buffered most in the pool memory, perhaps this is the hot table that is accessed frequently, or the larger table. Note that the buffer pages inside the pool are constantly changing. If you run the statement again, it might be another table that appears on the headline.

3. Size of the total number of pages modified in the buffer pool. This is relatively easy:

SELECT Count (*) *8 as cached_pages_kb,

CONVERT (varchar (5), Convert (Decimal (5,2), (100-1.0* (SELECT COUNT (*) from sys.dm_os_buffer_descriptors b where b.database_id=a.database_id and Is_modified=0)/count (*) *100.0)) + '% ' modified_percentage

, Case database_id span>

When 32767 and then ' Resourcedb '

ELSE db_name (database_id)

END as database_name

From Sys.dm_os_buffer_descriptors A

GROUP by Db_name (database_id), database_id

ORDER by cached_pages_kb DESC;

Results: From the above results can be seen, AdventureWorks database about 13.84% of the data is modified. If the majority (more than 80%) of a database is modified, this database writes very much. Conversely, if the ratio is close to 0, the activity of the database is almost read-only. The proportion of reads and writes is important for disk scheduling. Of course, there are other performance data to get the approximate percentage of the database read and write, which is limited to a few pages.

Go Why does SQL Server use so much memory?