Need to load a lot of test data? GO [n]

Happy Friday! Here’s a simple trick I love using in SQL Server Management Studio (SSMS).

I’m all about the demos today. Let’s jump into SSMS.

CREATE TABLE Test (ColumnA varchar(10))
GO

And now we insert some data.

INSERT INTO Test
SELECT 'Data'
GO 10

It’s really as simple as that. Hopefully this saves some wear and tear on your F5 key.

Is SQL Server using all of your server’s memory?

I’ve worked with a lot of great system administrators and developers. There’s one unusual thing about working with databases that’s often confusing for non-DBAs. This post is to help in those situations.

When an application is using 80-95% of a system’s memory, that’s concerning

Yes, even if it’s Chrome. Close some Chrome tabs please!

However, in the database world, your DBAs will tell you that it’s fine for SQL Server to consume lots of memory. They say that because databases want lots of memory by design. Databases use memory to store and retrieve data fast!

Your database administrators have control of how much memory is used by SQL Server

It’s right here, in SQL Server Management Studio. Right click on the server name, and go to properties.

Is 10,0000 MB the default setting?

No, I set that up for a very small server specifically for this demo. Don’t pick 10,000 just because I did.

However even in the latest version of SQL Server, the default setting is 2,147,483,647 megabytes. That does mean that SQL Server could eventually use up your entire system’s memory. Bummer.

What to do when SQL Server is using a lot of server memory

If you’re still worried about how much memory is being used by the SQL Server, check that setting. If your company has database administrators, talk to them. Check on the link for the default setting, there’s some general advice in there.

End of the post

The main moral of this story is: please don’t reboot your servers just because SQL Server is using a lot of memory. It will start to consume memory once it starts running again, and the process will repeat.

TempDB space isn’t free real estate

Using temp tables in SQL Server often seem like a great way to improve performance. But when everyone wants the same piece of land, you end up with contention or in the real world, very expensive houses.

TempDB contention example

To simulate a lot of sessions running at once, trying to use TempDB, I’m going to use the free SQLQueryStress tool written by Adam Machanic (Thanks Adam!).

Now I need a query that will consume a lot of TempDB space.

SELECT *
into #messages
FROM sys.messages

SELECT severity
FROM #messages
WHERE text = 'String or binary data would be truncated.'

I’ll run this query with 200 threads because I want a lot of contention.

Once I kick off that process, here’s a snippet of sp_WhoIsActive.

What’s the issue?

Well, the issue is PAGELATCH_UP on the tempDB data files, in that screenshot it’s files 5 and 9. There’s a lot of writing on the internet about the best way to solve TempDB contention. If you only have one or two files in TempDB but you have lots of CPU cores, adding more TempDB files might help.

What’s the solution?

In this example, we could filter when we load into the temp table (or not even use a temp table at all). That’s one of my proposed solutions.

Once you have decided on the right number of tempDB files, you might still see this issue. That’s why I picked an example that does searching extremely inefficiently. I want to emphasize that tuning your queries will often provide much bigger benefits than trying to use administration to fix bad T-SQL.

Let’s do two things to the query from earlier. We don’t need to load into a temp table, and we don’t need to SELECT *, we just need the severity column. Both of these improvements will reduce the total amount of resources used.

SELECT severity
FROM sys.messages
WHERE text = 'String or binary data would be truncated.'

Time to take another look at sp_WhoIsActive.

What’s happening now? They’re still running?

Yes, but instead of waiting for resources, they’re all executing simultaneously.

Thanks for reading, stay tuned!

Why NOLOCK could bite you

It’s no secret that a lot of DBAs don’t like the NOLOCK hint. Often, people will discuss the myriad of issues that can happen.

Introduction to NOLOCK

If you aren’t familiar with NOLOCK, it’s a query hint that can be applied to each table in a query. This hint tells SQL Server to change isolation level from Read Committed to Read Uncommitted. In plainer words, the query can now read data before and during the process of writing to the storage.

The main reason that this is bad is that your query can return bad data. That’s pretty bad.

But let me introduce you to another issue that can occur, which is blocking on the schema, AKA the wait type LCK_M_SCH_S.

What does LCK_M_SCH_S mean?

It means that the current session is trying to acquire a lock on the schema to make a change to an object in the schema. You might think that your environment doesn’t make changes to the schema too often, but there’s a few scenarios when that kind of lock is required.

Let’s say we have a production incident, and we need to drop an index. Let’s try to do that while there’s a query running on this table (using StackOverflow 2010).

SELECT * 
FROM Comments (NOLOCK)

On my machine, this query takes almost a minute to run. While this query runs, let’s create an index on this table.

CREATE NONCLUSTERED INDEX 
ix_test ON Comments(Score)

Taking a look at activity using sp_WhoIsActive, we can see that creating the index and running the query are compatible.

But here’s the tricky part: even though we could create the index, we can’t get rid of it now! Let’s try to drop it, and I’ll re-run the same Comments query first.

I also start a new “SELECT *” query, but it looks like now that I’m dropping an index, that new query is now waiting. Both queries haven’t done any work yet, just waiting for session 59 to finish.

What’s the solution?

Well, honestly there’s no easy fix once your environment has queries with NOLOCK applied in Production. You can try to avoid this issue by never dropping an index in Production, but what’s to stop another DBA or someone with sysadmin rights from creating an index or two?

T-SQL Tuesday #104: Index column script

[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 columns=”” column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” class=”” id=””]

Introduction

Bert Wagner (blog) is hosting this month’s T-SQL Tuesday, and the topic is code that you’ve written that you’d hate to live without.

I’ve written scripts for myself that I keep in my folder of Toolbox scripts, so I had a few choices. I decided to deliver a script that I find myself using almost daily. This script was written to solve a specific problem: There’s no good way to display indexes and their columns, built into SQL Server.

[/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″ max_width=”” style_type=”none” stylecolor=”” hover_type=”none” bordersize=”” bordercolor=”” borderradius=”” align=”none” lightbox=”no” gallery_id=”” lightbox_image=”” alt=”” link=”https://bertwagner.com/2018/07/03/code-youd-hate-to-live-without-t-sql-tuesday-104-invitation/” 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][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=”1_1″ layout=”1_1″ 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 columns=”” column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” class=”” id=””]

Requirements to run this script

You must be on SQL Server 2008 and above. Sorry, this script relies on the FOR XML PATH clause. You need access to the DMVs sys.indexes and sys.index_columns, and you must be using the right database context, i.e. USE [StackOverflow].

Here’s the script

[fusion_syntax_highlighter theme=”” language=”sql” line_numbers=”” line_wrapping=”” copy_to_clipboard=”” copy_to_clipboard_text=”Copy to Clipboard” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” font_size=”” border_size=”” border_color=”” border_style=”” background_color=”” line_number_background_color=”” line_number_text_color=”” margin_top=”” margin_left=”” margin_bottom=”” margin_right=””]U0VMRUNUIGkubmFtZSBhcyBJbmRleF9OYW1lCiAgICAsT0JKRUNUX05BTUUoaS5vYmplY3RfaWQpIGFzIFRhYmxlX05hbWUKICAgICxpLnR5cGVfZGVzYyBhcyBJbmRleF9UeXBlCiAgICAsU1RVRkYoKAogICAgICAgIFNFTEVDVCAnLCcgKyBDT0xfTkFNRShpYy5vYmplY3RfaWQsIGljLmNvbHVtbl9pZCkKICAgICAgICBGUk9NIHN5cy5pbmRleGVzIGFzIGluYwogICAgICAgIElOTkVSIEpPSU4gc3lzLmluZGV4X2NvbHVtbnMgYXMgaWMgb24gaW5jLm9iamVjdF9pZCA9IGljLm9iamVjdF9pZAogICAgICAgICAgICBhbmQgaW5jLmluZGV4X2lkID0gaWMuaW5kZXhfaWQKICAgICAgICBXSEVSRSBpc19pbmNsdWRlZF9jb2x1bW4gPSAwCiAgICAgICAgICAgIGFuZCBpbmMub2JqZWN0X2lkID0gaS5vYmplY3RfaWQKICAgICAgICAgICAgYW5kIGluYy5pbmRleF9pZCA9IGkuaW5kZXhfaWQKICAgICAgICBPUkRFUiBCWSBrZXlfb3JkaW5hbAogICAgICAgIEZPUiBYTUwgUEFUSCgnJykKICAgICAgICApLCAxLCAxLCAnJykgQVMgS2V5X1ZhbHVlcwogICAgLFNUVUZGKCgKICAgICAgICBTRUxFQ1QgJywnICsgQ09MX05BTUUoaWMub2JqZWN0X2lkLCBpYy5jb2x1bW5faWQpCiAgICAgICAgRlJPTSBzeXMuaW5kZXhlcyBhcyBpbmMKICAgICAgICBJTk5FUiBKT0lOIHN5cy5pbmRleF9jb2x1bW5zIGFzIGljIG9uIGluYy5vYmplY3RfaWQgPSBpYy5vYmplY3RfaWQKICAgICAgICAgICAgYW5kIGluYy5pbmRleF9pZCA9IGljLmluZGV4X2lkCiAgICAgICAgV0hFUkUgaXNfaW5jbHVkZWRfY29sdW1uID0gMQogICAgICAgICAgICBhbmQgaW5jLm9iamVjdF9pZCA9IGkub2JqZWN0X2lkCiAgICAgICAgICAgIGFuZCBpbmMuaW5kZXhfaWQgPSBpLmluZGV4X2lkCiAgICAgICAgT1JERVIgQlkga2V5X29yZGluYWwKICAgICAgICBGT1IgWE1MIFBBVEgoJycpCiAgICAgICAgKSwgMSwgMSwgJycpIEFTIEluY2x1ZGVkX0NvbHVtbnMKICAgIEZST00gc3lzLmluZGV4ZXMgYXMgaQogICAgV0hFUkUgb2JqZWN0X25hbWUoaS5vYmplY3RfaWQpID0gJ1Bvc3RzJwo=[/fusion_syntax_highlighter]

Results

Explanations and DMVs

So why is this script so useful to me? Well, I deal with tables that have wide indexes with many columns. In the picture above, I’m looking at the IX_ViewCount index. If I queried just the DMVs without the extra formatting, there would be five rows, one for each column.

Limitations

I use this script for Clustered and Nonclustered indexes. I haven’t tested it with more complex index types, like Columnstore or In-Memory, so there could be limitations there. I also didn’t include index size in pages or bytes, but it wouldn’t be too difficult to add.

[/fusion_text][/fusion_builder_column][/fusion_builder_row][/fusion_builder_container]

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

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.

Query Store Internals

Query Store Internals: Indexes on the system views

Foreword

The Query Store is a great new feature available starting in SQL Server 2016. I started using it a few months ago, and learned a lot about it from Erin Stellato (blog | twitter)! I got a chance to speak with her at SQL Saturday Cleveland, where we discussed the indexes in this post. Thank you Erin for all your help with this post and understanding the Query Store! To anyone reading this post who wants to learn more, I highly recommend Erin’s blog posts and Pluralsight courses on Query Store.

Structure of the Query Store

The easiest way to see what’s stored in the Query Store is to query the catalog views. Behind the scenes of catalog views, data is stored in system tables . There are many benefits to this, starting with the fact that your performance data will survive a reboot. If we want to see these tables, we can search for them by looking for clustered indexes or heaps, that store this data. But when we query for these tables or their indexes, nothing shows up:

(Query taken from Microsoft’s documentation on sys.indexes)

SELECT i.name AS index_name

,COL_NAME(ic.object_id,ic.column_id) AS column_name

,ic.index_column_id

,ic.key_ordinal

,ic.is_included_column

FROM sys.indexes AS i

INNER JOIN sys.index_columns AS ic

ON i.object_id = ic.object_id AND i.index_id = ic.index_id

WHERE i.name like ‘%query_store%’

order by i.name

Another approach

Let’s try looking for just one view. Here’s a query store catalog view-:

sys.query_store_runtime_stats (and more from Microsoft)

Same query, different keyword:

SELECT i.name AS index_name

,COL_NAME(ic.object_id,ic.column_id) AS column_name

,ic.index_column_id

,ic.key_ordinal

,ic.is_included_column

FROM sys.indexes AS i

INNER JOIN sys.index_columns AS ic

ON i.object_id = ic.object_id AND i.index_id = ic.index_id

WHERE i.name like ‘%runtime%’

order by i.name

Now we’re talking!

It looks like internally Query Store is referred to as plan_persist. That makes sense, thinking about how the Query Store persists query plans to your database’s storage. Let’s take a look at those catalog views vs their clustered and nonclustered indexes. I’ve modified the query a bit at this point, to group together the key columns.

SELECT i.name AS index_name ,

CASE when i.index_id = 1 THEN ‘Clustered’

WHEN i.index_id = 0 THEN ‘Heap’

ELSE ‘Nonclustered’ END as TypeOfIndex,

STUFF((

SELECT ‘,’ + COL_NAME(ic.object_id,ic.column_id)

FROM sys.index_columns as ic

WHERE i.object_id = ic.object_id AND i.index_id = ic.index_id

and is_included_column = 0 FOR XML PATH(”)),1,1,”)

AS [KeyColumns]

FROM sys.indexes AS i

WHERE i.name like ‘%plan_persist%’

Results:

Catalog View Name	Index Name	Key Columns	Type of Index
sys.query_store_query_text (Transact-SQL)	plan_persist_query_text_cidx	query_text_id	Clustered
sys.query_store_query (Transact-SQL)	plan_persist_query_cidx	query_id	Clustered
sys.query_store_plan (Transact-SQL)	plan_persist_plan_cidx	plan_id	Clustered
sys.query_store_runtime_stats (Transact-SQL)	plan_persist_runtime_stats_cidx	plan_id,runtime_stats_interval_id,execution_type	Clustered
sys.query_store_runtime_stats_interval (Transact-SQL)	plan_persist_runtime_stats_interval_cidx	runtime_stats_interval_id	Clustered
sys.query_context_settings (Transact-SQL)	plan_persist_context_settings_cidx	context_settings_id	Clustered
Undocumented	plan_persist_query_hints_cidx	query_hint_id	Clustered
Undocumented	plan_persist_query_template_parameterization_cidx	query_template_id	Clustered
sys.query_store_wait_stats (Transact-SQL)	plan_persist_wait_stats_cidx	runtime_stats_interval_id,plan_id,wait_category,execution_type	Clustered
	plan_persist_query_text_idx1	statement_sql_handle	Nonclustered
	plan_persist_query_idx1	query_text_id,context_settings_id	Nonclustered
	plan_persist_plan_idx1	query_id	Nonclustered
	plan_persist_runtime_stats_idx1	runtime_stats_id	Nonclustered
	plan_persist_runtime_stats_interval_idx1	end_time	Nonclustered
	plan_persist_query_hints_idx1	query_hint_id	Nonclustered
	plan_persist_query_template_parameterization_idx1	query_template_hash	Nonclustered
	plan_persist_wait_stats_idx1	wait_stats_id	Nonclustered

Knowing this, we can query the Query Store using predicates supported by indexes! We can also see the Query Store indexes if we request actual Execution Plans.

Here’s my query:

select * from sys.query_store_query

where query_id = 5

Here’s the execution plan:

And here’s the predicate that was applied:

Looking at this execution plan, the object is “plan_presist_query_cidx”, which is the index from above. This shows that Query Store uses these “plan persist” indexes, as the indexes behind the catalog views.

So what’s the point?

Well, Query Store size is user defined, so we can control how much space is consumed by the data in total. As our Query Store gets bigger, queries against the Query Store tables will start running slower unless we use the indexes properly. It’s important to know these indexes if you’re going to be querying Query Store regularly.

Final note (don’t do this in production)

As a cool side note, it’s possible to query the system tables directly if you’re connected to a SQL Server using the Dedicated Admin Connection (DAC). Please use only use the DAC on a test or local server for this example:

The connection prefix, “ADMIN:” shows that I’m connected with the DAC.

TSQL Tuesday #96: Folks Who Have Made a Difference

Well, this is my first T-SQL Tuesday! Here’s the original post: link. I’m very excited to participate. The host for this month is Ewald Cress (blog | twitter ) and the subject is talking about people who’ve made a difference. I’ve been working with SQL Server for a few years and I have many people to be grateful for! I thought I’d start by sharing the story that I use in my presentation, Reading Execution Plans Successfully.

My first SQL job was a Junior BI Developer, right out of college. I was learning from Treye Nekola , a Sr Database Administrator. At the time, I could write a SELECT statement, but I had trouble with JOINs and WHERE clauses. Sometimes my queries would run for minutes a time, even with NOLOCK hints they were taking a while. Treye had the wisdom to call me out on it, and I said something very foolish. I said, “I don’t care how long my query takes as long as it gets results.” Treye just looked at me, knowing how stupid that sounded. I let it go, because I was embarrassed. He was right, and my queries needed work. Over the next months, Treye showed me why my queries were poorly written, and with a godly amount of patience, he set me on the right path. I will forever be grateful to Treye for being an awesome mentor and friend!

The next person I’d like to appreciate is Brent Ozar. I doubt Brent knows much about me, but his material and training has seriously improved my skills. Treye showed me his blog, and I started to learn about the SQL Server online resources. Brent’s also the person who inspired me to start presenting. At PASS Summit 2016, Brent had a session titled 500-Level Guide to Career Internals. At this session, Brent encouraged the audience to start blogging and speaking. I haven’t blogged as much as I should, but I did take his advice on speaking. It has seriously improved my life to speak because I get to teach people and meet tons of really excellent people in the database world. It is so rewarding to teach people a new skill or fact.

And finally, I appreciate Andy Yun. I think I first met Andy at SQL Saturday Iowa City. We were both speakers and spent time talking in the speaker room. We parted ways for a while, but Andy mentioned he was planning on making a trip to the Iowa SQL user groups so I was sure to see him again. We met up before his presentation in Des Moines, Iowa, where I told him that I was taking a job in Illinois, and I was looking for advice. By sheer luck, it was the exact same city that Andy was living in! I had no idea he lived in the exact same suburb as I was headed to. Since that day, Andy has been a close friend and mentor to me. I’m incredibly grateful for the guidance and help he’s given me, in life and career. I say without hesitation that Andy has made a massive difference in my life.

I would like to make some honorable mentions to some people I couldn’t fit in this post, and I’m sure there’s many more that I couldn’t fit in this post.

Thank you for being great people:

Ted Schnieders

Bill Andersen

Nicole Loyd

Fred Botts (leader of the Des Moines SQL Server User Group)

Thoughts on Performance Tuning methodology

This post is going to be a change of pace for the blog. I want to talk about how I approach performance tuning, specifically when talking about queries. This won’t be a tutorial, just somewhat of a journal post.

The other day, I was working on optimizing a query in a development environment with another database administrator (DBA). We were comparing execution plans for a new index and looking to see if our query wanted the index. We created several variations of the index and measured whether SQL Server would use the index when running a SELECT statement against the table. Once the query used the index, we decided to look at SQL Server’s IO Statistics . We compared several variations of the index before I noticed that we had been running a SELECT * against the table.

I think this was an example where we should have been testing with an actual workload. My thought process is that a SELECT * is usually not a good representation of the table. If a table is very wide (meaning it has many columns), SQL Server may choose to scan the Clustered Index of a table instead of using a well-designed Nonclustered Index. In the system I was working on, most queries will have a specific set of columns that they want to load. We didn’t keep our testing consistent with the actual system workload.

I’m going to try to make a demo of this scenario in a future blog post. In the mean time, here’s a list of the things that I think are important for consistent testing of performance:

Same version of SQL Server, or at least set to similar server settings
Table structure including data types
Same indexes
Same set of data used for testing
If possible, same system memory and CPU
If possible, similar existing Execution Plan Cache

I listed the last two as possible because sometimes it’s not possible to have the same hardware in Dev as Prod, and I don’t think it’s realistic to expect that on larger SQL Servers. In this case, simply generating an Estimated Execution Plan from the production environment can be good enough.

Using Live Query Statistics on older SQL Servers

First, thanks to everyone who attended my session at SQL Saturday #634 Iowa City! I spoke about execution plans and how to begin reading them. Here’s a link to my session.

I had an attendee come up afterwards who mentioned that Live Query Statistics could be used with the newest version of Management Studio to watch query execution plans live on older versions of SQL Server. I was completely unaware of this, and this feature is really awesome. Live Query Statistics allows you to watch what indexes or actions SQL Server is doing while your query executes. This works really well if your query runs for one or more minutes and you want to see what is taking this much time.

Once I learned this, I was curious what versions this feature can be used on. I figured there were some limitations, so I made sure I had the latest release of Management Studio (Standalone), at the time of this post, it’s 17.1. It appears to me that the oldest version of SQL Server where this will work is SQL Server 2014, based on a test I ran locally and documentation.

Here’s the screenshot, directly from Microsoft’s documentation on Live Query Statistics:

This is super cool since it’s a lot of work to upgrade a SQL Server, and this lets me use these features without upgrading (yet).