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
SQL Server Performance
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
Using DBCC FREEPROCCACHE to Clear SQL Server Procedure Cache
It is possible to clear out the entire SQL Server procedure cache using DBCC FREEPROCCACHE The procedure cache is where SQL Server will cache execution plans after they have been compiled. The benefit of this caching is that there is no need for the execution plans to be compiled at run time. This compiling operation typically consumes resource and slows down the execution … [Read more...] about Using DBCC FREEPROCCACHE to Clear SQL Server Procedure Cache