Sql-server – Should an OFFLINE database be SET EMERGENCY before dropping

The hypothetical indexes created by the Index Tuning Wizard start with a name of "hind_%" and should not exist after the tuning has finished; they should all be removed. You can run the following script from the SQL Server Query Analyzer to remove any such indexes that may exist. You must log in by using an account that has either sysadmin or db_owner permissions, or is the owner of the object on which these statistics were created.

For example

DECLARE @strSQL nvarchar(1024) 
DECLARE @objid int 
DECLARE @indid tinyint 
DECLARE ITW_Stats CURSOR FOR SELECT id, indid FROM sysindexes WHERE name LIKE 'hind_%' ORDER BY name 
OPEN ITW_Stats 
FETCH NEXT FROM ITW_Stats INTO @objid, @indid 
WHILE (@@FETCH_STATUS  -1) 
BEGIN 
SELECT @strSQL = (SELECT case when INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1 then 'drop statistics [' else 'drop index [' end + OBJECT_NAME(i.id) + '].[' + i.name + ']' 
FROM sysindexes i join sysobjects o on i.id = o.id 
WHERE i.id = @objid and i.indid = @indid AND 
(INDEXPROPERTY(i.id, i.name, 'IsHypothetical') = 1 OR
(INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1 AND 
INDEXPROPERTY(i.id, i.name, 'IsAutoStatistics') = 0))) 
EXEC(@strSQL) 
FETCH NEXT FROM ITW_Stats INTO @objid, @indid
END
CLOSE ITW_Stats 
DEALLOCATE ITW_Stats

Above script is provided by Microsoft for more detail you can visit following link. http://support.microsoft.com/kb/q293177

I initially thought you were on to something here. Working assumption was along the lines that perhaps the buffer pool wasn't immediately flushed as it requires "some work" to do so and why bother until the memory was required. But...

Your test is flawed.

What you're seeing in the buffer pool is the pages read as a result of re-attaching the database, not the remains of the previous instance of the database.

And we can see that the buffer pool was not totally blown away by the detach/attach. Seems like my buddy was wrong. Does anyone disagree or have a better argument?

Yes. You're interpreting physical reads 0 as meaning there were not any physical reads

Table 'DatabaseLog'. Scan count 1, logical reads 782, physical reads 0, read-ahead reads 768, lob logical reads 94, lob physical reads 4, lob read-ahead reads 24.

As described on Craig Freedman's blog the sequential read ahead mechanism tries to ensure that pages are in memory before they're requested by the query processor, which is why you see zero or a lower than expected physical read count reported.

When SQL Server performs a sequential scan of a large table, the storage engine initiates the read ahead mechanism to ensure that pages are in memory and ready to scan before they are needed by the query processor. The read ahead mechanism tries to stay 500 pages ahead of the scan.

None of the pages required to satisfy your query were in memory until read-ahead put them there.

As to why online/offline results in a different buffer pool profile warrants a little more idle investigation. @MarkSRasmussen might be able to help us out with that next time he visits.