Table variables can cause performance issues with joins when they contain a large number of rows. In SQL Server 2019, Microsoft has improved how the optimizer works with table variables which can improve performance without making changes to your code. In this article, Greg Larsen explains how this feature works and if it really does make a difference.… Read more
Most advice you see online about heaps is to avoid them. In this article, Uwe Ricken describes the basics of heaps so that you can determine when heaps are the best choice. … Read more
Microsoft introduced Columnstore with SQL Server 2012 and have improved the feature in every version since then. You may be wondering why they are different than traditional indexes and how they work. In this series, Edward Pollack explains the architecture of Columnstore indexes. In future articles in the series, he will describe best practices for Columnstore.… Read more
This marks the third year that Redgate have launched a survey to better understand how organisations monitor their databases. This year, 971 database professionals from around the world responded. Here are some of the key insights from the survey: Estates are growing more than ever Companies who reported over 1,000 SQL Server instances grew 9% … Read more
When the optimizer doesn’t estimate the correct amount of memory for a query, either memory is wasted that could be used for other processes or some operations will spill to disk. Microsoft has added Memory Grant Feedback to help overcome this issue. In this article, Greg Larsen explains what you need to know about this new feature. … Read more
Because of its many roles, tempdb is the source of performance issues in SQL Server, and there are recommended configuration changes to mitigate this. In this article, Fabiano Amorim demonstrates another issue with tempdb performance that you probably didn’t know about. … Read more
Microsoft introduced several features called Intelligent Query Processing for SQL Server in 2017 and 2019 that can improve performance without making changes to code. In this article, Greg Larsen demonstrates the feature Batch Mode on Rowstore. … Read more
Today, most organization are using solid-state drives for everything from laptops to enterprise database storage and virtual machines. In this article, Robert Sheldon explains how NAND flash solid-state drives work.… Read more
Microsoft has added a group of features called Intelligent Query Processing to SQL Server 2017 and 2019. In this article, Greg Larsen explains one of the features, Scalar UDF Inlining.… Read more
Accelerated Database Recovery is new with SQL Server 2019 and Azure SQL Database and used to decrease the time for rolling back large operations and database recovery. In this article, Forrest McDaniel explains how it works. … Read more
Microsoft continues to enhance the performance of SQL Server with new features. In this article, Monica Rathbun explains how to work with columnstore indexes, a different way to store tables that can drastically improve the performance of specific workloads.… Read more
While architecting cloud native applications, you need to ensure that your system is highly available, performant, scalable, fault tolerant, and has the capability to recover from a disaster scenario. In this article, Samir Behara discusses the options available when designing the database architecture to achieve scalability.… Read more
New releases of SQL Server arrive at a quick pace, and it's difficult to keep up with the many features introduced in each version. In this article, Phil Factor reviews a feature you may have missed, inline indexes. He covers the syntax and the many ways they can be used and then performs some performance tests to see if they can make a difference with table variables.… Read more
T-SQL window functions have been a fantastic addition to the T-SQL language. In this article, Kathi Kellenberger reviews how optimizations available in SQL Server 2019 can improve the performance of these functions.… Read more
Now that SQL Server 2019 is on the way, it’s time to start learning about the new capabilities. In this article, Greg Larson tests the new APPROX_COUNT_DISTINCT function for performance and accuracy.… Read more
T-SQL window functions, introduced in 2005 with enhancements in 2012, are great additions to the T-SQL language. In this article, Kathi Kellenberger explains what you need to know to get good performance when using these functions.… Read more
One of the most important tools for query tuning is the ability to view execution plans. They are even portable; you can send an execution plan to get advice from someone who doesn't have access to the underlying data. Have you even considered that sensitive information might travel along with the plans? Hugo Kornelis explains how execution plans affect data protection. … Read more
Developer shops may decide to use Read Committed Snapshot Isolation (RCSI) to reduce contention, and possibly improve performance, but it is not without its own pitfalls. In this article, Uwe Ricken describes a situation where long running transactions caused a severe performance degradation when RCSI was in use.… Read more
The query store gives us a novel way of identifying those queries that are causing performance problems when they are parameterized by SQL Server for reuse. Although it is relatively simple to ensure that certain troublesome queries avoid the problem, it is laborious to identify these queries. Additionally, Query Store gives us the means to fix the problem for groups of queries by means of plan guides without changing the DDL at all. Dennes Torres explains the details… Read more
Subscribe for more articles
Fortnightly newsletters help sharpen your skills and keep you ahead, with articles, ebooks and opinion to keep you informed.
Compliant Database DevOps
Standardize team-based development
Automate database deployments
Monitor performance and availability
Protect and preserve data
