If you have ever done performance tuning you know it can be a bit of an art and you need your detective hat on. I was recently working on a server that was performing poorly. After looking at resource monitor it was clear that the CPU usage was unusually high. As it turns out SQL Server was utilizing most of the CPU. The hunt was on. I grabbed my magnifying glass and followed the trail. While I could run sp_who2 to find some relevant info, I instead ran the query below. sys.dm_exec_requests returns information about each request that is executing within SQL Server. sys.dm_exec_sql_text returns the text of the SQL batch that is identified by the specified sql_handle.

-- check for queries running. sort by cpu time
SELECT a.session_id, db_name(a.database_id) as db_name, a.start_time, a.command, a.status, a.cpu_time, a.total_elapsed_time, a.reads, a.writes, b.text as query
FROM sys.dm_exec_requests a
OUTER APPLY sys.dm_exec_sql_text(a.sql_handle) b
WHERE a.session_id > 50 -- filter out background tasks
and a.session_id <> @@spid -- filter out this query session
order by a.cpu_time desc

This query shows open queries sorted in descending order by CPU time. I was able to nab a few culprits in the act. I copied the queries to new windows and checked the execution plans. After some intense interrogation it was clear that several tables needed an index and the sub-queries should be turned into joins. Updates were put in place, and the CPU usage went way down. Case closed!

If a database becomes orphaned and has no database owner, you will get the following error message when you try to view the database properties in SSMS:

Cannot show requested dialog. (SqlMgmt)
Property Owner is not available for Database ‘[database]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights.
(Microsoft.SqlServer.Smo)

You can use the following code to see orphaned databases.
SELECT databases.NAME AS DB_Name, server_Principals.NAME AS User_Name
FROM sys.[databases]
LEFT OUTER JOIN sys.[server_principals]
ON [databases].owner_sid = [server_principals].sid

To assign a new owner:
EXEC sp_changedbowner 'newuser'

If you get the following error message:

Msg 15110, Level 16, State 1, Line 1
The proposed new database owner is already a user or aliased in the database.

Open up the user’s Login Properties window under Security\Logins and uncheck the Map checkbox for the database and click OK. Basically the code won’t assign them as the new owner because it thinks they are already associated with that database. Now run the sp_changedbowner command again and it will work.