You’ve got one SQL Server hosting a bunch of databases, and performance is bad.
Which database should you focus on first?
Here are a few ways you can pick – but keep in mind they may produce different answers, especially when you’ve got several databases that suck, or different ways of sorting the culprits.
Option 1: check the plan cache.
As SQL Server executes queries, it tracks which queries it sees the most often, plus helpful metrics like how much CPU time they used, how much data they read, number of times they spilled to disk, etc.
To find out which queries are using the most CPU power, install sp_BlitzCache, and then run it:
By default, it sorts by CPU, finding the most CPU-intensive queries. You can also use different sort orders like these, and check the documentation for more:
EXEC sp_BlitzCache @SortOrder = 'duration';
EXEC sp_BlitzCache @SortOrder = 'reads';
The results will show you the top most resource-intensive queries, and the very first column shows the database name.
- The database name is just the context where the query ran. If you’ve got cross-database queries, or people are running queries from tempdb, you won’t really see which objects they’re accessing.
- The data can be somewhat transient when your server is under heavy memory pressure, like trying to host 1TB of data on a VM with 16GB RAM.
- If your app is using unparameterized SQL, the results won’t be as helpful.
Option 2: check the file statistics.
SQL Server tracks which files get the most reads & writes, and you can query that with the system function sys.dm_io_virtual_file_stats:
SELECT d.name AS database_name, f.*
FROM sys.dm_io_virtual_file_stats(NULL, NULL) f
INNER JOIN sys.databases d ON f.database_id = d.database_id
ORDER BY f.num_of_bytes_read DESC;
Here, I’m sorting them by which files have had the most data read since the server was restarted. (It can also be reset when the database is restored, taken offline/online, etc.)
- These numbers don’t include data retrieved from cache.
- These DO include system tasks like backups, corruption checks, and index rebuilds.
- The query above is pretty vanilla – for more details like latency, check out sp_BlitzFirst.
Option 3: check which pages are cached.
If one database’s pages are dominating SQL Server’s memory, then queries in that database are probably the ones repeatedly reading that same data over and over.
To find out which database’s data is using up the most cache, use the sample query from Books Online:
SELECT COUNT(*)AS cached_pages_count
WHEN 32767 THEN 'ResourceDb'
END AS database_name
GROUP BY DB_NAME(database_id) ,database_id
ORDER BY cached_pages_count DESC;
- Querying this data is sloooow, and gets slower the more memory that you have. Querying this data on >64GB RAM can take several minutes.
- This data is extremely transient – it changes all through the day as people run different queries, causing different pages to go in & out of cache.
- This doesn’t necessarily mean that the queries reading the most data are the ones causing the biggest problems.