| The Complete Weekly Roundup of SQL Server News | Hand-picked content to sharpen your professional edge |
| How SQL Server Can Just Go Faster It’s rare to get a call from a customer telling you that the application is fast today and thanks for taking such good care of the database. Instead, you are more likely to hear complaints when things go wrong like slow running queries and timeouts. There is a lot to consider when trying to figure out performance issues, from hardware resources to server settings to query and index tuning. Improving performance can be a lot of work and requires some expertise. In recent editions of SQL Server, Microsoft has added features that make things better (i.e., go faster) without requiring code changes or even spending a lot of time figuring out what to do on your own. Beginning with 2017, Microsoft began adding to a feature set called “Intelligent query processing” that overcomes some common problems. These features can make queries run faster without changes to code. Note that the compatibility level of your database may need to be adjusted, and that some of the features are only available with Enterprise edition. Here’s a list of the features released so far: 2017 Adaptive joins (batch mode): The optimizer can choose the join type (i.e., hash join or nested loop) during query execution based on row count. The feature applies only to queries using a columnstore index. This is an Enterprise Edition feature. Interleaved Execution for multi-statement table valued functions (MSTVFs): This feature enables the optimizer to determine the cardinality of MSTVFs during query execution instead of assigning a guess of 100 rows. Memory Grant Feedback (batch mode): When the optimizer has determined to use batch mode, it can adjust the memory grant used in subsequent executions of the query. This decreases the likelihood of spilling to disk or underusing the memory assigned. In 2017, batch mode is used only on queries that involve a columnstore index. This is an Enterprise Edition feature. 2019 Approximate Count Distinct: Use a new function called APPROX_COUNT_DISTINCT instead of COUNT(DISTINCT) when responsiveness is more important than precision. Batch Mode on Rowstore: Queries that do not involve a columnstore index can now take advantage of batch mode. This is especially helpful when calculating aggregations over large numbers of rows. This is an Enterprise Edition feature. Memory Grant Feedback (row mode): This feature extends the memory grant feature to queries that do not include a columnstore index. This is an Enterprise Edition feature. Scalar UDF Inlining: This feature removes some of the pain when including a multi-value UDF in a query by making the operation set-based instead of iterative. Table Variable Deferred Compilation: Allows the optimizer to correctly estimate the number of rows in a table variable instead of estimating 1 row. I have to admit that some of these optimizations make me want to say “don’t do those bad things!” I’ve seen both scalar and multi-statement table valued UDFs cause so many problems over the years, and the optimizations won’t fix everything that you can throw at them. Today’s Microsoft is serious about SQL Server, and I can’t wait to see what’s next! Kathi Kellenberger Join the debate, and respond to the editorial on the forums | The Weekly News | All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit. |
AI/Machine Learning/Cognitive Services |
This is Lesson 18 in the Data Science in 90 Seconds video blog series from host Laura Kahn.... |
Let me start with an apology for being missing so ... |
Artificial Intelligence (AI) is changing how busin... |
Paul Randal walks through an installation error wi... |
John Morehouse has an interesting use case for res... |
One thing that I seem to come across often enough ... |
Keeping their SQL Server instances under control i... |
Metadata changes, like modifying a clustered index... |
There can be several reasons that cause Database L... |
I bet you wanna know whether your storage is hot o... |
Microsoft announced Azure Arc at Ignite 2019 provi... |
Does your server look like this? Many of us have i... |
Accelerated Database Recovery is new with SQL Serv... |
When SQL Server requests storage space from the op... |
When Azure SQL Database introduced the serverless ... |
I recently posted that I was doing my annual Azure... |
How Error Handling Works in PowerShell 7 Among the... |
Data Mining / Data Analysis |
How does an organization help the self-serve advanced analytics model grow and thrive?... |
Database Design, Theory and Development |
Daniel Hutmacher shows a couple things you can cha... |
DevOps and Continuous Delivery (CI/CD) |
I enjoying maintaining open source GitHub reposito... |
Recent work has identified that classification models implemented as neural networks are vulnerable to data-poisoning ... |
A step-by-step approach to using Support Vector Ma... |
Performance Tuning SQL Server |
I was asked a very interesting question about stat... |
PowerPivot/PowerQuery/PowerBI |
The default behaviour of the slicer in Power BI is... |
Time for a short "professional development" post t... |
One of my favorite presentations is Revenge the SQ... |
Tell It To The Judge A common dilemma is when you ... |
In most scenarios when working with data, users do not require all the data in each instance of a query. T-SQL (and SQL of course) ... |
If you need to convert strings to all upper case o... |
Watch this week’s episode on YouTube. At first I... |
TL;DR: No. A customer recently brought up an inter... |
Virtualization and Containers/Kubernetes |
Throughout the pre-release of SQL Server 2019, I w... | This email has been sent to newsletter@newslettercollector.com. To be removed from this list, please click here. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. Note: This is not the SQLServerCentral.com daily newsletter list, and unsubscribing to this newsletter will not stop you receiving the SQL Server Central daily newsletters. If you want to be removed from that list, you can follow the instructions on the daily newsletter. |
|
|
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -