We make your SQL Server faster! › Inside SQL Server

Torsten Strauß Microsoft Most Valuable Professional for Data Platform

ABOUT ME

Microsoft SQL Server Expert

My name is Torsten Strauß, a Microsoft Most Valuable Professional for Data Platform from Germany. Around the turn of the millennium I discovered my passion for Microsoft SQL Server and since then I have specialized in performance tuning, troubleshooting and T-SQL programming. For years I have been consulting medium-sized but also DAX companies in cooperation with Sarpedon Quality Lab® on all aspects of Microsoft SQL Server. You can meet me at international and national conferences and user groups, where I regularly share my knowledge and experience with the data platform community.

TRAINING

Individual Training For Individual People

You can do amazing things with Microsoft SQL Server – sometimes it just takes a little support. As a Microsoft Certified Trainer and Microsoft Certified Solutions Expert, I offer professional training on SQL Server performance optimization, query tuning, and data integration with Microsoft Integration Services. To keep up to date with this fast-changing technology, I regularly attend conferences and trainings from other experts myself.

BLOG

Learning By Sharing

In my experience, the best way to learn is by sharing your knowledge with others. Feel free to read my blog posts, ask questions and leave feedback. Just drop me a message if you’re interested in a particular topic you’d like me to write about.

Show all Blog Posts

Revival of the Special Interest Group SQL Server Internals

2020-06-04 Microsoft SQL Server 0 comments

2020-06-04 Microsoft SQL Server SQL Server Internals, Special Interest Group SQL Server Internals 0 comments

Frank Geisler, Uwe Ricken and Torsten Strauß revive the SIG - SQL Server Internals Group after a long break. As before, we focus on the SQL Server Engine only, which has been expanded with several features in the SQL Server 2019 and thus certainly offers interesting topics. If you are interested in a certain topic, just send us an email or talk to Frank, Uwe or me directly.

Design principles of the in-memory OLTP engine – Avoid CPU and memory overhead for interpreted T-SQL

2020-04-06 Microsoft SQL Server 0 comments

2020-04-06 Microsoft SQL Server SQL Server Internals, SQL Server In-Memory OLTP 0 comments

In this blog, I explain why the in-memory OLTP engine is significantly faster than the traditional on-disk engine. T-SQL is interpreted code with high CPU and memory overhead when SQL Server has to compile the command before it can be executed by the engine. In-memory OLTP reduces the number of recompilations, which may lead to much faster query execution with less impact on CPU and memory.

Design principles of the in-memory OLTP engine – Avoid physical reads

2019-12-30 Microsoft SQL Server 2 comments

2019-12-30 Microsoft SQL Server SQL Server Internals, SQL Server In-Memory OLTP 2 comments

Microsoft introduced in-memory OLTP in SQL Server 2014, advertising that queries can be up to 100 times faster. In this blog series I will explain the design principles of the in-memory OLTP engine to explain why it has the potential to be significantly faster than the traditional on-disk engine by skipping the complexity of handling the data pages in the buffer pool and totally eliminates logical and physical reads which can result in much faster query execution and less CPU impact.

The limitations of the missing index feature – It does not suggest filtered indexes

2019-07-07 Microsoft SQL Server 0 comments

2019-07-07 Microsoft SQL Server SQL Server Query Tuning, SQL Server Missing Index Feature 0 comments

This will be my last blog about the limitations of the missing index feature. This time I will demonstrate that the missing index feature will not suggest filtered indexes. Filtered indexes reduce the index size and thus the storage allocation in the data file is reduced. Rebuilding or reorganizing these indexes also requires fewer resources. So it's worth creating filtered indexes when possible.The following example will prove that the missing index feature will not consider creating filtered indexes.

The limitations of the missing index feature – It suggests creating indexes on imprecise and not persisted columns

2019-04-22 Microsoft SQL Server 0 comments

2019-04-22 Microsoft SQL Server SQL Server Query Tuning, SQL Server Missing Index Feature 0 comments

This will be my penultimate blog about the limitations of the missing index feature and this one will be short.Before we start, let us clarify some terms so you can better understand the table we are going to create.The data types real and float are imprecise data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.Non deterministic columns are computed columns which may return different results each time they are called.

The limitations of the missing index feature – It reports only include columns for some queries

2019-02-10 Microsoft SQL Server 0 comments

2019-02-10 Microsoft SQL Server SQL Server Query Tuning, SQL Server Missing Index Feature 0 comments

In the last post you saw that the missing index feature does not consider the order for columns to be used in an index.In this blog I will explain another limitation of the missing index feature - it reports only include columns for some queries.SQL Server supports two kind of row store indexes - the clustered index and then non-clustered index. It is important to understand the difference between these two indexes in order to implement the right index type to improve reading performance.

The limitations of the missing index feature – It does not specify an order for columns to be used in an index

2018-12-31 Microsoft SQL Server 4 comments

2018-12-31 Microsoft SQL Server SQL Server Query Tuning, SQL Server Missing Index Feature 4 comments

In the last post you saw that the missing index feature does not consider trivial plans.In this blog I will explain another limitation of the missing index feature - it does not specify an order for columns to be used in an index.SQL Server can only benefit from a row store index if the key columns are in the correct order. The order of the columns of an index determines whether SQL Server chooses an index scan or seek. Scanning an index simply means that the storage engine of SQL Server will read all index pages and filter the desired records afterwards (residual predicate)

The limitations of the missing index feature – It does not consider trivial plans

2018-10-12 Microsoft SQL Server 0 comments

2018-10-12 Microsoft SQL Server SQL Server Query Tuning, SQL Server Missing Index Feature 0 comments

In the last post you saw that the missing index feature does not gather statistics for more than 500 missing index groups.This time I am going to reveal another limitation which is not even documented - the missing index feature does not recommend missing indexes for trivial queries. SQL Server has to create an execution plan before executing a query because it has to evaluate the different available physical iterators depending on the cardinality estimation, supportive indexes, available hardware resources and instance settings to find a good enough plan.

The limitations of the missing index feature – It cannot gather statistics for more than 500 missing index groups

2018-10-06 Microsoft SQL Server 0 comments

2018-10-06 Microsoft SQL Server SQL Server Query Tuning, SQL Server Missing Index Feature 0 comments

Implementing the right indexes will improve the execution time and IO reads for a Select, Update or Delete. Unfortunately sometimes it is not that easy to identify missing indexes without having a deep understanding about Query Execution Plans. That is why SQL Server gives us a hint about missing indexes in an Execution Plan.Sounds beneficial but in this blog post I will proof that it is not always a good idea to rely on these index recommendations.According to Microsoft, there are the several Limitations for Using the Missing Indexes Feature.

MEET ME

Let’s Get To Know Each Other

To date, I have given more than 200 presentations at international and national conferences and user groups on Microsoft SQL Server performance optimization and hope to give many more. I look forward to meeting you in person at a future event.

Upcoming Events

EVENTS IN THE PAST HALF YEAR

inside-sqlserver recommends Sentry One to make your SQL Server faster

Share the passion