Video: How The Right Indexes Help SQL Server Make Better Use Of Memory

Sphinx For The Mahogany


Demoman!


USE StackOverflow2010;
SET NOCOUNT ON;
GO 

/*
--Basic index info
SELECT *
FROM dbo.WhatsUpIndexes AS wui
WHERE wui.table_name = 'Users'
OPTION ( RECOMPILE );
GO   

--What's in the buffer pool?
SELECT *
FROM dbo.WhatsUpMemory AS wum
WHERE wum.object_name = 'Users'
OPTION ( RECOMPILE );
GO 
*/

--CREATE INDEX ix_whatever ON dbo.Users (Reputation);
GO 

SELECT *
FROM dbo.WhatsUpIndexes AS wui
WHERE wui.table_name = 'Users'
OPTION ( RECOMPILE );
GO            


DBCC DROPCLEANBUFFERS;
CHECKPOINT;
DBCC FREEPROCCACHE;
CHECKPOINT;
GO 5


SELECT *
FROM dbo.WhatsUpMemory AS wum
WHERE wum.object_name = 'Users'
OPTION ( RECOMPILE );
GO 

    SET STATISTICS TIME, IO, XML ON;
    SET NOCOUNT OFF;

    --/*Select a count of everything*/
    SELECT COUNT(*) AS records
    FROM dbo.Users AS u
    WHERE 1 = (SELECT 1);

    --/*Select a count of one user*/
 --   SELECT COUNT(*) AS records
 --   FROM dbo.Users AS u
 --   WHERE u.Id = 22656 
	--AND 1 = (SELECT 1);


    --/*Select a count of rep > 100k*/
    --SELECT COUNT(*) AS records
    --FROM dbo.Users AS u
    --WHERE u.Reputation >= 100000
    --AND 1 = (SELECT 1);

    SET NOCOUNT ON;
    SET STATISTICS TIME, IO, XML OFF;

SELECT *
FROM dbo.WhatsUpMemory AS wum
WHERE wum.object_name = 'Users'
OPTION ( RECOMPILE );
GO 

Viewzo The Clown


CREATE OR ALTER VIEW dbo.WhatsUpIndexes
AS
SELECT   TOP 2147483647 
         s.name AS schema_name,
         OBJECT_NAME(ps.object_id) AS table_name,         
         i.name AS index_name,
         ps.row_count,
         ps.in_row_used_page_count,
         (ps.reserved_page_count * 8. / 1024.) AS reserved_MB,
         (ps.lob_reserved_page_count * 8. / 1024.) AS reserved_LOB_MB,
         (ps.row_overflow_reserved_page_count * 8. / 1024.) AS reserved_row_overflow_MB
FROM     sys.dm_db_partition_stats AS ps
JOIN     sys.objects AS so
    ON  ps.object_id = so.object_id
    AND so.is_ms_shipped = 0
    AND so.type <> 'TF'
JOIN     sys.schemas AS s
    ON s.schema_id = so.schema_id
JOIN sys.indexes AS i
    ON ps.object_id = i.object_id
    AND ps.index_id = i.index_id
ORDER BY ps.object_id, ps.index_id, ps.partition_number
GO  

CREATE OR ALTER VIEW dbo.WhatsUpMemory
AS
SELECT     TOP 2147483647 
           DB_NAME() AS database_name,
           SCHEMA_NAME(o.schema_id) AS schema_name,
           o.name AS object_name,
           i.name AS index_name,
           SUM(CASE WHEN au.type IN (1,3) 
                    THEN 1 
                    ELSE 0 
               END) * 8. / 1024. AS in_row_pages_mb,
           SUM(CASE WHEN au.type = 2 
                    THEN 1 
                    ELSE 0 
               END)  * 8. / 1024. AS lob_pages_mb,
           COUNT_BIG(*) * 8. / 1024. AS total_pages_mb,
           COUNT_BIG(*) AS buffer_cache_pages_total
FROM       sys.dm_os_buffer_descriptors AS obd
INNER JOIN sys.allocation_units AS au
    ON au.allocation_unit_id = obd.allocation_unit_id
INNER JOIN sys.partitions AS p
    ON ( ( au.container_id = p.hobt_id AND au.type IN ( 1, 3 ))
      OR ( au.container_id = p.partition_id AND au.type IN ( 2 )))
INNER JOIN sys.objects AS o
    ON p.object_id = o.object_id
INNER JOIN sys.indexes AS i
    ON  o.object_id = i.object_id
    AND p.index_id = i.index_id
WHERE      au.type > 0
AND        o.is_ms_shipped = 0
AND        obd.database_id = DB_ID()
GROUP BY   SCHEMA_NAME(o.schema_id), 
           i.name, 
           o.name
ORDER BY   COUNT(*) DESC;
GO

 



One thought on “Video: How The Right Indexes Help SQL Server Make Better Use Of Memory

Leave a Reply

Your email address will not be published. Required fields are marked *