I have been using these scripts or variations of them more and more lately. Since then, I have noticed a few cases in which they fail.

The following queries fail for the same reason: Fails when executed on a DB with a compatibility of 80 (likely anything < 90). This is regardless of running on SQL Server 2008. The fix for this is to run from a compatible DB such as master.

Page 57, Index fragmentation%
Page 96, Longest queries
Page 101, Longest blocked query
Page 103, Most CPU intensive
Page 116, Missing statistics
Page 72, Most fragmented indexes*
Page 106, Most IO intensive

* In addition to above, also fails if there are any offline DBs.
Also note that I have not exhaustively checked them all, though.

Changing the compatibility of a DB is often not realistic in a production environment. Nor is either enabling or removing an offline DB. Switching to a system DB is an easy workaround, though, but is a workaround instead of a solution to the problem.

Can anything be done to these scripts to improve the situation? If not, then perhaps a warning about possible failure should be included in the text.

Hi,

Thanks for your message.

The scripts have been tested on SQL Server versions 2005, 2008, and 2008 R2.

Setting the compatibility level below 90 (i.e. to SQL Server 2000 or earlier) means you are ‘simulating’ an earlier version of SQL Server. As you know, the DMVs belong to SQL Server 2005 onwards, so I think it is to be expected the DMVs may have problems in this case.

The problem with noting exceptions is, by their very nature, they are unusual events. Including all exceptions would probably result in a book 3 times as big and detract from the flow of the book. That said, I will consider including a note of this in the book.

Good to hear you’re experimenting with the scripts!

Thanks again
Ian

Message was edited by:
ian.stirk

Thank you for the response.

> Setting the compatibility level below 90 (i.e. to SQL Server 2000 or earlier) means you are ‘simulating’ an earlier version of SQL Server. As you know, the DMVs belong to SQL Server 2005 onwards, so I think it is to be expected the DMVs may have problems in this case.

Two things:
1) I am not actively setting a compatibility level below 90, but rather that production environments which are running on SQL Server 2005/2008 often do host DBs for earlier versions. It is generally not very realistic to change this level in a critical, production environment.
2) DMVs query data in the system tables maintained by SQL Server 2005/2008. SQL Server is even maintaining this DMV data for DBs with a compatibility level under 90.

Something as simple as USE master; GO would likely suffice to fix the problem. But that will clutter up the scripts as you say. Perhaps a little note regarding troubleshooting failures in the first or second chapter would suffice. The error messages are not very clear and it did take me a little while to realize the cause.

In any case, I encourage you to try a few scripts on such a DB to more clearly see what I am referring to.

Please also note my comment about most fragmented indexes (page 72) failing if there are any offline DBs.

Best regards.

Hi,

Thanks for your reply.

I think the problem with the database being offline is easy enough to fix. In essence an extra join is required to sys.databases, and a check for a state_desc of ‘ONLINE’. Some sample code for this is given below.

That said, I cannot reproduce this OFFLINE problem. If I take a database offline (on both a 2008 and 2005 SQL Server), and run the below code without the join to sys.databases, it still works... have I missed something?

I was hoping I could use a similar approach for checking the compatibility level (which is also recorded in sys.databases). However, it still fails. Let me investigate this a bit further...

I will make a note about this problem in the document.

Thanks for discovering this.
Ian


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
DB_NAME() AS DatbaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, ROUND(avg_fragmentation_in_percent,2) AS [Fragmentation %]
INTO #TempFragmentation
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE 1=2

EXEC sp_MSForEachDB 'USE [?];
INSERT INTO #TempFragmentation
SELECT TOP 20
DB_NAME() AS DatbaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, ROUND(avg_fragmentation_in_percent,2) AS [Fragmentation %]
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
INNER JOIN sys.databases d ON d.database_id = DB_ID()
AND state_desc = ''ONLINE''
WHERE s.database_id = DB_ID()
AND i.name IS NOT NULL
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
ORDER BY [Fragmentation %] DESC'

SELECT top 20 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC

DROP TABLE #TempFragmentation

Message was edited by:
ian.stirk

Thank you for the response.
I am glad that you were able to see the issue with compatibility level < 90.

Regarding the Fragmentation% query failing when there is an offline DB:
Let me investigate it a little more. While I can reproduce it on one system (Sys1), I cannot on another (Sys2). The script above unfortunately does not resolve the issue on Sys1, so there are likely other factors. I will update again if I find out anything else.
At this point, please do not waste your time updating the script. Apologies for the confusion.

Regards.