If you're looking at performance and trying to understand I/O on a per database level in SQL Server, sadly there is not a report in Management Studio that you can run from those available. It will need some T-SQL to pull out this information from an existing DMV - sys.dm_io_virtual_file_stats. The information returned by the DMV is data which has been recorded since the SQ … [Read more...] about How to Find I/O Usage Per Database in SQL Server
How to Get SQL Server Developer Edition for Free
SQL Server Developer Edition - whether you are wanting to learn the product at home to advance your career or you need a copy for your enterprise to test and develop on, it's historically been a good investment for relatively little cost. Personally for home use, I have always downloaded the evaluation versions of SQL Server that expire after 6 months. That's kind of … [Read more...] about How to Get SQL Server Developer Edition for Free
How to List CPU Usage Per Database in SQL Server
As a DBA there may be a time when you want to try and ascertain how much CPU time each of your databases is consuming. This is useful to complement high CPU investigations or to just try and understand which of your databases overall is the highest CPU consumer over time. The out of the box reporting that Management Studio provides is sadly missing this report so we have to … [Read more...] about How to List CPU Usage Per Database in SQL Server
How to Find Buffer Pool Usage Per Database in SQL Server
As a DBA it's important to understand what the buffer pool is doing and which databases are using it the most. Data in SQL Server is stored on disk in 8k pages. The buffer pool (Aka "buffer cache") is a chunk of memory allocated to SQL Server. It's size is determined by the minimum and maximum memory settings in SQL Server memory options: sp_configure 'min server memory … [Read more...] about How to Find Buffer Pool Usage Per Database in SQL Server
Revoking Access for All Users of a Database in SQL Server
I love Management Studio for SQL Server but sometimes it just doesn't cut the mustard. I had a scenario recently where I needed to revoke access for all users from a database. I could delete them all but that would be more effort than necessary. I just wanted to deny access to a specific database. To my knowledge, there is not a quick way to do this and so I had to write s … [Read more...] about Revoking Access for All Users of a Database in SQL Server