T-SQL Tuesday #100: The Future of SQL Server

[fusion_builder_container hundred_percent=”no” hundred_percent_height=”no” hundred_percent_height_scroll=”no” hundred_percent_height_center_content=”yes” equal_height_columns=”no” menu_anchor=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” background_color=”” background_image=”” background_position=”center center” background_repeat=”no-repeat” fade=”no” background_parallax=”none” enable_mobile=”no” parallax_speed=”0.3″ video_mp4=”” video_webm=”” video_ogv=”” video_url=”” video_aspect_ratio=”16:9″ video_loop=”yes” video_mute=”yes” video_preview_image=”” border_size=”” border_color=”” border_style=”solid” margin_top=”” margin_bottom=”” padding_top=”” padding_right=”” padding_bottom=”” padding_left=””][fusion_builder_row][fusion_builder_column type=”2_3″ layout=”2_3″ spacing=”” center_content=”no” link=”” target=”_self” min_height=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” background_color=”” background_image=”” background_position=”left top” background_repeat=”no-repeat” hover_type=”none” border_size=”0″ border_color=”” border_style=”solid” border_position=”all” padding_top=”” padding_right=”” padding_bottom=”” padding_left=”” dimension_margin=”” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=”” last=”no”][fusion_text]

I’m happy to say this is my second T-SQL Tuesday post! The topic this week is looking forward to many years in the future, at T-SQL Tuesday #200, or blogging on a new feature that might come out on June 2026. Thanks to Adam Machanic for starting the T-SQL Tuesday idea and for the topic this week. His round-up post can be found here, or simply click the logo on the right side. The post below is the second option, I’ll be blogging on something that hasn’t happened. This post is all science fiction!

Query Optimizer on Github

Microsoft started to embrace open sourcing some of their applications way back in the 2010s. It took a while to get here, but finally the query optimizer has been open sourced. I’ve spent the past couple days immersed in the internals and I was surprised to find that Compatibility Mode 130 is still supported! If you enable trace flag 42, you can still run your SQL Server 2016 workloads on SQL Server 2024!

Reverting your Compatibility Level to 130

Ever since we’ve upgraded to SQL Server 2024, our queries just run slower so I’ve applied trace flag 42 to our production instances. I think I found the issue though. We were using the legacy Query Store which had forced plans, and the new 2024 Query Store is dynamically deciding which plans should be forced, at run time. With the changes I’m merging, we can exclude certain queries from the Query Store AI. At least these days, we get weekly cumulative updates, so we can just fix things in one week. It’s hard to believe, but we used to wait a month to apply a new SQL patch! And you had to reboot to apply changes! It all seems so draconian.

[/fusion_text][/fusion_builder_column][fusion_builder_column type=”1_3″ layout=”1_3″ spacing=”” center_content=”no” link=”” target=”_self” min_height=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” background_color=”” background_image=”” background_position=”left top” background_repeat=”no-repeat” hover_type=”none” border_size=”0″ border_color=”” border_style=”solid” border_position=”all” padding_top=”” padding_right=”” padding_bottom=”” padding_left=”” dimension_margin=”” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=”” last=”no”][fusion_imageframe image_id=”11995″ style_type=”none” stylecolor=”” hover_type=”none” bordersize=”” bordercolor=”” borderradius=”” align=”none” lightbox=”no” gallery_id=”” lightbox_image=”” alt=”” link=”http://dataeducation.com/invitation-t-sql-tuesday-100-looking-forward-100-months/” linktarget=”_self” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=””]https://www.dba-art.com/wp-content/uploads/2017/11/T-SQL-Tuesday-Logo.jpg[/fusion_imageframe][/fusion_builder_column][/fusion_builder_row][/fusion_builder_container]

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close