What's swimming in your bufferpool?

Published Mar 23 2019 05:01 AM 279 Views
Microsoft
First published on MSDN on May 18, 2007

When doing a performance investigation a useful thing to do is look at what data is present in the buffer pool.  This can be used to analyze impact of running a query on the state of data pages in buffer pool. By collecting the pre and post picture of buffer pool, you can see the cost of running a query in terms of physical IOs that happened. You may argue that this can be done by looking at statistics IO output; however if you are running a series of queries and want to see a consolidated data and not data about individual queries, this query is a great help.



The contents of the buffer pool can also reveal which pages are accessed most frequently by your applications and often reflect the actual I/O that is happening.  How can frequently access pages in memory also cause disk I/O?   When lots of different objects are accessed overtime the proportion of data in the buffer pool reflects the frequency of access. This happens because data pages of infrequently accessed objects get kicked out of main memory over time.



If you are not familiar with the buffer pool, it contains several types of objects such as data pages and plans.  For more information on the buffer pool see Buffer Management http://msdn2.microsoft.com/en-us/library/aa337525.aspx



The following query can be used to look at the contents of the buffer pool -



select


count(*)as cached_pages_count,


obj.name as objectname,


ind.name as indexname,


obj.index_id as indexid


from sys.dm_os_buffer_descriptors as bd


inner join


(


select       object_id as objectid,


object_name(object_id) as name,


index_id,allocation_unit_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_id as objectid,


object_name(object_id) as name,


index_id,allocation_unit_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 outer join sys.indexes ind


on  obj.objectid = ind.object_id


and  obj.index_id = ind.index_id


where bd.database_id = db_id()


and bd.page_type in ('data_page', 'index_page')


group by obj.name, ind.name, obj.index_id


order by cached_pages_count desc



An example of what it returns –



1.       Run following command to remove all clean data pages from the buffer pool –  (DO NOT TRY THIS COMMAND ON PRODUCTION MACHINES)



DBCC DROPCLEANBUFFERS



Running buffer pool analysis query had following results –



cached_pages_count ObjectName         IndexName              IndexId


------------------ ------------------ ---------------------- -----------


15                 sysobjvalues       clst                   1


3                  sysallocunits      clust                  1


2                  syshobtcolumns     clust                  1


2                  sysrowsetcolumns   clust                  1


2                  sysrowsets         clust                  1


2                  sysschobjs         clst                   1




2.       Run the following query on AdventureWorks database –



select * from Person.Address


where city like 'Bothell'



This is going to read from disk the data pages needed to execute the query. Run the buffer pool analysis query again to see the change.



cached_pages_count ObjectName         IndexName              IndexId


------------------ ------------------ ---------------------- -----------


278                Address            PK_Address_AddressID   1


15                 sysobjvalues       clst                   1


4                  sysmultiobjrefs    clst                   1


3                  sysallocunits      clust                  1


2                  syshobtcolumns     clust                  1


2                  sysrowsetcolumns   clust                  1




As you can see now there are data pages in buffer pool from the Address table. Additionally since only clustered index pages for Address table are present, no other indexes were used in the query.



Another tool which can help in this case is DBCC MEMORYSTATUS output. The advantage of the query in the entry is nice result set which can be stored in a temp table.




Authors:


Tony Voellm

Gaurav Bindlish
%3CLINGO-SUB%20id%3D%22lingo-sub-383282%22%20slang%3D%22en-US%22%3EWhat's%20swimming%20in%20your%20bufferpool%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-383282%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20May%2018%2C%202007%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EWhen%20doing%20a%20performance%20investigation%20a%20useful%20thing%20to%20do%20is%20look%20at%20what%20data%20is%20present%20in%20the%20buffer%20pool.%26nbsp%3B%20This%20can%20be%20used%20to%20analyze%20impact%20of%20running%20a%20query%20on%20the%20state%20of%20data%20pages%20in%20buffer%20pool.%20By%20collecting%20the%20pre%20and%20post%20picture%20of%20buffer%20pool%2C%20you%20can%20see%20the%20cost%20of%20running%20a%20query%20in%20terms%20of%20physical%20IOs%20that%20happened.%20You%20may%20argue%20that%20this%20can%20be%20done%20by%20looking%20at%20statistics%20IO%20output%3B%20however%20if%20you%20are%20running%20a%20series%20of%20queries%20and%20want%20to%20see%20a%20consolidated%20data%20and%20not%20data%20about%20individual%20queries%2C%20this%20query%20is%20a%20great%20help.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThe%20contents%20of%20the%20buffer%20pool%20can%20also%20reveal%20which%20pages%20are%20accessed%20most%20frequently%20by%20your%20applications%20and%20often%20reflect%20the%20actual%20I%2FO%20that%20is%20happening.%26nbsp%3B%20How%20can%20frequently%20access%20pages%20in%20memory%20also%20cause%20disk%20I%2FO%3F%20%26nbsp%3B%26nbsp%3BWhen%20lots%20of%20different%20objects%20are%20accessed%20overtime%20the%20proportion%20of%20data%20in%20the%20buffer%20pool%20reflects%20the%20frequency%20of%20access.%20This%20happens%20because%20data%20pages%20of%20infrequently%20accessed%20objects%20get%20kicked%20out%20of%20main%20memory%20over%20time.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIf%20you%20are%20not%20familiar%20with%20the%20buffer%20pool%2C%20it%20contains%20several%20types%20of%20objects%20such%20as%20data%20pages%20and%20plans.%26nbsp%3B%20For%20more%20information%20on%20the%20buffer%20pool%20see%20Buffer%20Management%20%3CA%20href%3D%22http%3A%2F%2Fmsdn2.microsoft.com%2Fen-us%2Flibrary%2Faa337525.aspx%22%20mce_href%3D%22http%3A%2F%2Fmsdn2.microsoft.com%2Fen-us%2Flibrary%2Faa337525.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20http%3A%2F%2Fmsdn2.microsoft.com%2Fen-us%2Flibrary%2Faa337525.aspx%3C%2FA%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThe%20following%20query%20can%20be%20used%20to%20look%20at%20the%20contents%20of%20the%20buffer%20pool%20-%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eselect%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ecount(*)as%20cached_pages_count%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eobj.name%20as%20objectname%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eind.name%20as%20indexname%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eobj.index_id%20as%20indexid%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Efrom%20sys.dm_os_buffer_descriptors%20as%20bd%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Einner%20join%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E(%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eselect%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20object_id%20as%20objectid%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eobject_name(object_id)%20as%20name%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eindex_id%2Callocation_unit_id%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Efrom%20sys.allocation_units%20as%20au%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Einner%20join%20sys.partitions%20as%20p%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eon%20au.container_id%20%3D%20p.hobt_id%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eand%20(au.type%20%3D%201%20or%20au.type%20%3D%203)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eunion%20all%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eselect%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20object_id%20as%20objectid%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eobject_name(object_id)%20as%20name%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eindex_id%2Callocation_unit_id%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Efrom%20sys.allocation_units%20as%20au%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Einner%20join%20sys.partitions%20as%20p%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eon%20au.container_id%20%3D%20p.partition_id%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eand%20au.type%20%3D%202%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E)%20as%20obj%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eon%20bd.allocation_unit_id%20%3D%20obj.allocation_unit_id%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eleft%20outer%20join%20sys.indexes%20ind%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eon%26nbsp%3B%20obj.objectid%20%3D%20ind.object_id%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eand%26nbsp%3B%20obj.index_id%20%3D%20ind.index_id%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ewhere%20bd.database_id%20%3D%20db_id()%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eand%20bd.page_type%20in%20('data_page'%2C%20'index_page')%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Egroup%20by%20obj.name%2C%20ind.name%2C%20obj.index_id%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eorder%20by%20cached_pages_count%20desc%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EAn%20example%20of%20what%20it%20returns%20%E2%80%93%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E1.%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Run%20following%20command%20to%20remove%20all%20clean%20data%20pages%20from%20the%20buffer%20pool%20%E2%80%93%26nbsp%3B%20(DO%20NOT%20TRY%20THIS%20COMMAND%20ON%20PRODUCTION%20MACHINES)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDBCC%20DROPCLEANBUFFERS%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ERunning%20buffer%20pool%20analysis%20query%20had%20following%20results%20%E2%80%93%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ecached_pages_count%20ObjectName%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IndexName%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IndexId%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E------------------%20------------------%20----------------------%20-----------%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E15%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20sysobjvalues%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20clst%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E3%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20sysallocunits%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20clust%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E2%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20syshobtcolumns%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20clust%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E2%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20sysrowsetcolumns%26nbsp%3B%26nbsp%3B%20clust%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E2%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3Bsysrowsets%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20clust%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E2%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20sysschobjs%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20clst%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%E2%80%A6%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E2.%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Run%20the%20following%20query%20on%20AdventureWorks%20database%20%E2%80%93%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eselect%20*%20from%20Person.Address%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ewhere%20city%20like%20'Bothell'%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThis%20is%20going%20to%20read%20from%20disk%20the%20data%20pages%20needed%20to%20execute%20the%20query.%20Run%20the%20buffer%20pool%20analysis%20query%20again%20to%20see%20the%20change.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ecached_pages_count%20ObjectName%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IndexName%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IndexId%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E------------------%20------------------%20----------------------%20-----------%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E278%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Address%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20PK_Address_AddressID%26nbsp%3B%26nbsp%3B%201%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E15%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20sysobjvalues%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20clst%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E4%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20sysmultiobjrefs%26nbsp%3B%26nbsp%3B%26nbsp%3B%20clst%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E3%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20sysallocunits%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20clust%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B1%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E2%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20syshobtcolumns%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20clust%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E2%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20sysrowsetcolumns%26nbsp%3B%26nbsp%3B%20clust%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%E2%80%A6%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EAs%20you%20can%20see%20now%20there%20are%20data%20pages%20in%20buffer%20pool%20from%20the%20Address%20table.%20Additionally%20since%20only%20clustered%20index%20pages%20for%20Address%20table%20are%20present%2C%20no%20other%20indexes%20were%20used%20in%20the%20query.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EAnother%20tool%20which%20can%20help%20in%20this%20case%20is%20DBCC%20MEMORYSTATUS%20output.%20The%20advantage%20of%20the%20query%20in%20the%20entry%20is%20nice%20result%20set%20which%20can%20be%20stored%20in%20a%20temp%20table.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EAuthors%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fblogs.msdn.com%2Ftvoellm%22%20mce_href%3D%22http%3A%2F%2Fblogs.msdn.com%2Ftvoellm%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20Tony%20Voellm%20%3C%2FA%3E%3C%2FP%3E%3CA%20href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fgauravbi%22%20mce_href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fgauravbi%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20Gaurav%20Bindlish%20%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-383282%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20May%2018%2C%202007%20When%20doing%20a%20performance%20investigation%20a%20useful%20thing%20to%20do%20is%20look%20at%20what%20data%20is%20present%20in%20the%20buffer%20pool.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-383282%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQLServerPerformance%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Mar 23 2019 05:01 AM
Updated by: