By: Tim Ford | Updated: 2009-06-04 | Comments (3) | Related: > Functions System
Problem
Here is a quick tip that I did not see covered here at www.mssqltips.com and found quite useful when building ad-hoc Transact-SQL code. You see, I tend to do a great deal of meta-data (or is it metadata) mining from the system compatibility views and system catalog views in order to build dynamic ad-hoc T/SQL code to use for various administrative processes.
Since SQL Server 2005 was released though we finally see the SQL Server Professionals embracing the structures associated with schemas that Oracle Professionals had been using for years. Sure, the schemas (aka owners) construct has been around in releases prior to SQL Server 2005, but most SQL Developers and DBAs avoided anything other than the default "dbo" schema like it was that weird cousin you would see at the family picnic each summer that would be chewing the bark off the oak trees and talking to the squirrels.
SQL Server 2005, however, brought schemas to the mainstream, and the more-organized, and security-conscious SQL Professionals started incorporating schema reliance into their databases. This, therefore, made it necessary to incorporate the coding for such use into the dynamic T/SQL I was developing. There are ways of deriving this information from JOINs between catalog/compatibility views, but there is something even better: a function that returns this information without need for all that nasty code.
Solution
In this tip I'll use a simple query against the sys.dm_db_index_usage_stats Dynamic Management View (DMV). The results of this view denote the usage of all indexes (usage is broken down into system versus user seeks, scans, lookups, and updates) on the SQL Server instance. The first version of this query ignores schema all-together. We will examine why this is a practice I don't recommend after we examine the output.
SELECT DB_NAME(SDDIUS.[database_id]) AS [db name], OBJECT_NAME(SDDIUS.[object_id]) AS [Table/View], SDDIUS.[index_id], SDDIUS.[user_seeks] + SDDIUS.[user_scans] + SDDIUS.[user_lookups] AS [user reads], SDDIUS.[user_updates] AS [user writes] FROM sys.[dm_db_index_usage_stats] SDDIUS WHERE DB_NAME(SDDIUS.[database_id]) = 'Northwind' ORDER BY OBJECT_NAME(SDDIUS.[object_id]), SDDIUS.[index_id]
The query returns 22 rows. These rows represent all activity since the last time the SQL Server service was restarted. (This information only resides in cache. See previous tips at the end of this article for steps on how to persist this data for index optimization practices.) Those of you familiar with the sample Northwind database may already see what I am about to point out: there is a mix of system and user object indexes in the result set. The objects starting with "sys..." are system-created tables or views. I would prefer to exclude these from the results.
There is also another matter - specifically concerning the user-created objects' indexes. Do you see any "duplicates"? Look closely. Rows 2 and 3 both reference the Customers table's index_id of 1. However, the combination of object_id and index_id is unique in this DMV. So how could this be? Simple - the tables are in different schemas. Let's look at what we can see once we include schema information in the result set.
The following query uses multiple joins (via the sys.sysobjects and sys.sysusers System Compatibility Views) to return schema information for each object in the result set. I've also proceeded to limit the results to only objects not owned by the sys schema.
SELECT DB_NAME(SDDIUS.[database_id]) AS [db name],
SSU.[name] AS [schema],
OBJECT_NAME(SDDIUS.[object_id]) AS [Table/View],
SDDIUS.[index_id],
SDDIUS.[user_seeks] + SDDIUS.[user_scans] + SDDIUS.[user_lookups] AS [user reads],
SDDIUS.[user_updates] AS [user writes]
FROM sys.[dm_db_index_usage_stats] SDDIUS
INNER JOIN [sys].[sysobjects] SSO
ON SDDIUS.[object_id] = SSO.id
INNER JOIN sys.[sysusers] SSU
ON SSO.[uid] = SSU.[uid]
WHERE DB_NAME(SDDIUS.[database_id]) = 'Northwind'
AND SSU.[name] <> 'sys'
ORDER BY OBJECT_NAME(SDDIUS.[object_id]), SDDIUS.[index_id]
Now you can see what was only inferred via familiarity with the design of the database and knowledge of the specific DMV, that there are actually two objects in the result set that are named identically, but owned by different schemas. You may balk at this design strategy, but it is quite common and is considered a best practice when dealing with similar designs that could, in fact, be touched by different logical user groups in an organization.
Now that we see reasons for displaying schema information in your result sets when dealing with SQL Server metadata let's take that final step and look at the OBJECT_SCHEMA_NAME() function. The previous example required you to understand the relationships between multiple system objects in order to return schema information with the result set. OBJECT_SCHEMA_NAME() renders that knowledge requirement moot, and simplifies the query in the process.
SELECT DB_NAME(SDDIUS.[database_id]) AS [db name], OBJECT_NAME(SDDIUS.[object_id]) AS [Table/View], OBJECT_SCHEMA_NAME(SDDIUS.[object_id], SDDIUS.[database_id]) AS [schema], SDDIUS.[index_id], SDDIUS.[user_seeks] + SDDIUS.[user_scans] + SDDIUS.[user_lookups] AS [user reads], SDDIUS.[user_updates] AS [user writes] FROM sys.[dm_db_index_usage_stats] SDDIUS WHERE DB_NAME(SDDIUS.[database_id]) = 'Northwind' AND OBJECT_SCHEMA_NAME(SDDIUS.[object_id], SDDIUS.[database_id]) <> 'sys' ORDER BY OBJECT_NAME(SDDIUS.[object_id]), SDDIUS.[index_id]
The results are identical, but we eliminate the additional (and truly unnecessary) joins to sys.sysobjects and sys.syssusers. We also make the code more readable. The OBJECT_SCHEMA_NAME() function accepts two parameters: an object_id and an optional database_id. As you can see from my code, I specified the database_id. Therefore this query would execute properly no matter which database's context I execute it under. If I was always going to run this query from within the Northwind database I could exclude the database_id parameter from the function call as the function will automatically assume the database_id parameter is for the database currently in context if left unspecified.
I strongly advocate use of the OBJECT_SCHEMA_NAME() function when attempting to return schema/owner information for objects. I also require presentation of schema/owner information whenever identifying objects in the databases in our organization. Imagine the difficulties that could be encountered if an index is dropped or created on the incorrect table simply because the correct table was not identified completely via a query similar to what I presented here, but only in a full production optimization review.
Next Steps
- Examine how to persist cached sys.dm_db_index_usage_stats DMV data.
- Examine the results of the sys.dm_db_index_usage_stats DMV on your administered databases. You may be shocked at how many indexes are being written to, but never read from!
About the author
Tim Ford is a Senior Database Administrator with MindBody.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2009-06-04
