SQL Server 2019 CTP 2.0 New Features – Introducing the Page Cracker (AKA sys.dm_db_page_info)!

Microsoft Ignite 2018 is fading away in the rearview mirror, but the excitement over all the new features and the vision shared there is just beginning. You’re probably seeing lots of buzz around the new Big Data Clusters, data virtualization and Intelligent Query Processing among others, but I wanted to take some time on the…

0

Programmatically find SQL Server TCP ports

There are many instances in SQL Server management where one person would like to find all the TCP ports that SQL Server is binding to. Finding all the TCP ports that SQL Server is listening on might be for security related auditing, connectivity troubleshooting, or for some form of automation. One such example is used…


Community driven Enhancements in SQL Server 2017

While SQL Server 2016 runs faster, SQL Server 2017 promises to run even faster and empower customers to run smarter with intelligent database features like the ability to run advanced analytics using Python in a parallelized and highly scalable way, the ability to store and analyze graph data, adaptive query processing and resumable online indexing…


Getting more statistics information programmatically

As the building blocks which the Query Optimizer uses to know data distribution on tables, statistics are one of the key aspects in calculating a good enough plan to read data. So, when engaging in query performance troubleshooting, information about statistics is something you need to look at. Let’s say we need to look at statistics…

4

Developers Choice: Query progress – anytime, anywhere

Edit (4/23/2018): due to a possible random AV running your favorite monitoring stored procedure, please make sure you install KB 4078596 in your SQL Server 2016 and SQL Server 2017. Edit (9/24/2018): this feature is now enabled by default in SQL Server 2019. Trace flag not needed anymore! Also, new query hint query_plan_profile to enable…


Index Usage DMV behavior updated

Up until SQL Server 2008 R2, using  index usage stats entries in sys.dm_db_index_usage_stats to make some assumptions over index design and workload patterns was common. However, in SQL Server 2012 and higher versions, entries in this DMV were reset with every index rebuild, so this was no longer a viable way of tracking index usage…