OBJECT_Name parameters and dbid
- by steveh99999
If you've been using SQL Server for a long time, you may have been used to using the OBJECT_NAME system function in the past - especially useful when converting table IDs into table names when querying sysobjects and sysindexes..... However, if you're an old-school DBA - did you know since SQL 2005 service pack 2 it accepts a second parameter ?
database_id..
For example, this can be used to summarize some useful information from sys.dm_exec_query_stats.
When reviewing SQL Server performance - it can be useful to look at the most heavily used stored procedures rather than inefficient less frequently used procedures.
Here's a query to summarize performance data on the most-heavily used stored procedures across all databases on a server :-SELECT TOP 20 DENSE_RANK() OVER (ORDER BY SUM(execution_count) DESC) AS rank, OBJECT_NAME(qt.objectid, qt.dbid) AS 'proc name', (CASE WHEN qt.dbid = 32767 THEN 'mssqlresource'
ELSE DB_NAME(qt.dbid)
END ) AS 'Database',
OBJECT_SCHEMA_NAME(qt.objectid,qt.dbid) AS 'schema',
SUM(execution_count) AS 'TotalExecutions',SUM(total_worker_time) AS 'TotalCPUTimeMS',
SUM(total_elapsed_time) AS 'TotalRunTimeMS',
SUM(total_logical_reads) AS 'TotalLogicalReads',SUM(total_logical_writes) AS 'TotalLogicalWrites',
MIN(creation_time) AS 'earliestPlan', MAX(last_execution_time) AS 'lastExecutionTime'
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE OBJECT_NAME(qt.objectid, qt.dbid) IS NOT NULL
GROUP BY OBJECT_NAME(qt.objectid, qt.dbid),qt.dbid,OBJECT_SCHEMA_NAME(qt.objectid,qt.dbid)
