SQL Server 2016 Live Query Statistics provide a way to look into the execution plan to see what parts of the query are currently running. The advantage of this feature is that for a slow query, instead of having to wait for it to complete before the execution plan is available, it's now possible to view the execution plan in real time. Sometimes execution plans can be … [Read more...] about SQL Server 2016 New Features – Live Query Statistics
performance
SQL Server 2016 New Features – Query Store
In this series of posts covering the new features in SQL Server 2016, I'm moving on to looking at the Query Store. This tool provides a way to track query execution plan performance over time to help assist troubleshooting efforts. Figuring out why the database has suddenly started running slowly can be at times difficult to diagnose quickly. The query store is an extra tool … [Read more...] about SQL Server 2016 New Features – Query Store
How to Find I/O Usage Per Database in SQL Server
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 … [Read more...] about How to Find I/O Usage Per Database in SQL Server
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