I was reading through documentation on the DMV sys.dm_db_index_physical_stats and found that there are a couple of things you need to be aware of when using this DMV. First, we know that there are parameters for the DMV.
Parameters
- Database Id
- Object ID
- Index Id
- Partition Id
- Level of interrogation (DEFAULT, LIMITED, DETAILED, SAMPLED, NULL = LIMITED).
The interesting thing is that when any of the first 4 parameters are NULL, the default is to evaluate ALL of the items in that parameter. For example, if you specify NULL in the Database ID then it will evaluate ALL databases in the server. If Object ID is NULL then it will evaluate all objects. So I knew all that, but here is what I did not realize, quoting from the documentation:
“passing values that are not valid to these functions may cause unintended results. For example, if the database or object name cannot be found because they do not exist or are spelled incorrectly, both functions will return NULL. The sys.dm_db_index_physical_stats function interprets NULL as a wildcard value specifying all databases or all objects.”
“Additionally, the OBJECT_ID function is processed before the sys.dm_db_index_physical_stats function is called and is therefore evaluated in the context of the current database, not the database specified in database_id. This behavior may cause the OBJECT_ID function to return a NULL value; or, if the object name exists in both the current database context and the specified database, an error message may be returned. The following examples demonstrate these unintended results.”
Now that is nice, but here is what it means. If you are specifying a database id for use in this function and the database id is not the “current” database, then if the OBJECT_ID() function is used, it will evaluate it in the “current” database and NOT the database that is represented by the database id in the statement. If the object does not exist in the database, then it will result in NULL and will then proceed to do an evaluation on all objects in the database represented by the database id.
The other caveat is that if in the current database there is an Object by that name that results in an object id being returned, when the function begins, it will not be able to find the object id returned and will actually error. Take a look at the examples below and notice that you will want to be in the database that you intend to use if you are specifying anything beyond the database id. Don’t get caught with NULL in the statement without you knowing about it.
[code lang=”SQL”]use master
GO
SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(‘AdventureWorks2012’), OBJECT_ID(‘Person.Person’), NULL, NULL, ‘LIMITED’)
— Results were for 190 rows
/*
** What happens here is that it evaluates OBJECT_ID(‘Person.Person’) to NULL
** because it does not exist in master as shown below.
*/
use master
GO
SELECT OBJECT_ID(‘Person.Person’)
— Result is NULL
GO
use AdventureWorks2012
GO
SELECT OBJECT_ID(‘Person.Person’)
— Result is 1765581328
/*
** Let’s create a table in master of the same name
** as in the AdventureWorks2012 database.
*/
use master
GO
CREATE SCHEMA Person AUTHORIZATION dbo
GO
CREATE TABLE Person.Person (id int)
GO
SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(‘AdventureWorks2012’), OBJECT_ID(‘Person.Person’), NULL, NULL, ‘LIMITED’)
GO
— You get the error below
/*
Msg 2501, Level 16, State 40, Line 3
Cannot find a table or object with the name ‘.StateProvinceCountryRegion’. Check the system catalog.
*/
[/code]