December 2, 2009 at 7:41 am
Hi All,
Yesterday while the tivoli backups were running on my SQL Databases, i noticed that out of 30 GB memory that is allocated through AWE, one of the clustered indexes on a table was occupying 28GB
I was able to track that using the following query
SELECT TOP 25 obj.[name], i.[name], i.[type_desc], count(*)AS Buffered_Page_Count , count(*) * 8192 / (1024 * 1024) as Buffer_MB -- ,obj.name ,obj.index_id, i.[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.hobt_id AND au.type = 2 ) AS obj ON bd.allocation_unit_id = obj.allocation_unit_id LEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_id WHERE database_id = db_id() GROUP BY obj.name, obj.index_id , i.[name],i.[type_desc] ORDER BY Buffered_Page_Count DESC
There are 2 questions that i have here
1. Could this be because of some select query/Queries that were running previously and loading the clean buffers
2. Is this the default behavior when a SQl backup or 3rd party backup through SQL VDI is taken.
Appreciate if someone can expian how SQL server backup exactly works internally. Also if possible please throw some light on SQL VDI.
Links and articles would help me understand these internals.
Regards,
Mani
December 2, 2009 at 4:25 pm
Yesterday while the tivoli backups were running on my SQL Databases, i noticed that out of 30 GB memory that is allocated through AWE, one of the clustered indexes on a table was occupying 28GB
...
There are 2 questions that i have here
1. Could this be because of some select query/Queries that were running previously and loading the clean buffers
2. Is this the default behavior when a SQl backup or 3rd party backup through SQL VDI is taken.
...
Links and articles would help me understand these internals.
http://www.sqlbackuprestore.com/3rdpartybackupapps_vdi.htm
http://www.redbooks.ibm.com/redbooks/pdfs/sg246148.pdf
Do you have the same issue if you run the backup directly to disk first and then to tape?
What happens if you adjust the MAXTRANSFERSIZE setting in Tivoli?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply