Changing Query Store Report Interval

Arun Sirpal wants to change the report interval for a Query Store report:

While not specific to SQL Server 2019 (I was using this version to do some testing) I was struggling to find how to change the time period of analysis for the Query Store reports within SSMS.

This is not a ground breaking post but hopefully a helpful one! So, I load up the “Top 25 resource consumers” report and by default it will show data for the past hour. So what do you do, or should I say what do you click to change the time interval for the report?

Read on for the two screenshots which answer this question for you.

Stats Time Versus Plan Operator Time

Erik Darling explains why SET STATISTICS TIME ON can give you different timing results from what the execution plan states:

Here are the relevant details:

SQL Server Execution Times:
CPU time = 3516 ms,  elapsed time = 3273 ms.

What looks odd here is that CPU and elapsed time are near-equal, but the plan shows parallelism.

Thankfully, with operator times, the actual plan helps us out.

The query itself ran for <900ms.

The answer makes perfect sense.

SSMS Query Plans and Arrow Sizes

Brent Ozar clarifies what arrow sizes actually mean in execution plans:

That means the entire concept of the arrow is made up by the rendering application – like SQL Server Management Studio, Azure Data Studio, SentryOne Plan Explorer, and all the third party plan-rendering tools. They get to decide arrow sizes – there’s no standard.

SSMS’s arrow size algorithm changed back in SQL Server Management Studio 17, but most folks never took notice. These days, it’s not based on rows read, columns read, total data size, or anything else about the data moving from one operator to the next.

There’s an answer, but it’s not particularly intuitive. I think SentryOne Plan Explorer has the upper hand on this one.

Scripting Objects with SSMS

Michelle Haarhues shows two ways to script out database objects in SQL Server Management Studio:

There are a multitude of reasons why users script existing objects within SQL Server.  Depending on the reason will dictate whether you are scripting one object, a few objects, or the entire database.  There are different tools within SQL Server Management Studio (SSMS) that will help you create object scripts.

Click through for the two methods.

SQL Server Management Studio 18.1 Now Available

Dinakar Nethi takes us through some changes in SQL Server Management Studio version 18.1:

We’re excited to announce the release of SQL Server Management Studio (SSMS) 18.1. It’s been just over a month since we released SSMS 18.0. While we brought in many fantastic capabilities, we also regressed some functionality for some of our users. We are happy to share that we’ve fixed those and are also bringing in some new features along with bug fixes.

The big thing for a lot of people is that database diagrams have returned. I was never the biggest fan of those, but there was enough of an uproar to bring them back.

Generating Scripts from SSMS

Jeff Mlakar shows how you can use Management Studio to generate scripts for database objects:

Sales.SalesOrderDetail looks like a good choice. Let’s generate a script for that table, all associated objects, and its data.

The safest way to create structure including all indexes, keys, defaults, constraints, dependencies, triggers, etc. is to use SSMS Generate Scripts.

I would also recommend becoming familiar with the Powershell command to generate scripts and what dbatools has on store.

Choosing Between Management Studio and Azure Data Studio

Brent Ozar gives us the lay of the tooling land:

Every time there’s a new release of SQL Server or SQL Server Management Studio, you can grab the latest version of SSMS and keep right on keepin’ on. Your job still functions the same way using the same tool, and the tool keeps getting better.

And it’s free. You don’t have to ask the boss for upgrade money. You can just download it, install it, and take advantage of things like the cool new execution plan est-vs-actual numbers (which also cause presenters all over to curse, knowing that they have to redo a bunch of screenshots.)

I spend a lot of time jumping back & forth between SQL Server and Postgres, and lemme just tell you, the tooling options on the other side of the fence are a hot mess.

Yeah, Management Studio is the best of the bunch. I’m using Azure Data Studio more at home but still need a couple of plugins to use it often at work. And those two beat pretty much every other tool I’ve ever worked with.

SSMS Grid Options

Michelle Haarhues provides details on the different options for sending results to a grid in SQL Server Management Studio:

What is Results to Grid and what can it do for you?  Results to Grid are Query Results options in SQL Server Management Studio (SSMS) that can help users customize their query results in a variety of ways that can help make users more efficient.  Some of these might be little changes, but when used often throughout the day, they can make a big difference.  Once you change the setting, you will need to open a new query window for the change to go into effect

The two I wish were on by default are column headers when copying or saving results, and retaining CR/LF on copy or save.

Connecting with Read Intent

John McCormack shows two ways to connect to an Availablity Group listener with read-only intent:

SQLCMD
The -Kreadonly switch is your key to success here but remember to also specify the database using -d. When not set (and with an initial catalog of master for my login), I found I always got the primary instance back during my check. This simple omission cost me hours of troubleshooting work, because I was convinced my listener wasn’t working correctly. In fact, I just wasn’t testing it correctly.

There’s some good information in here for sqlcmd and for SQL Server Management Studio.

SSMS Autorecover

Michelle Haarhues takes us through auto-recovery in Management Studio:

Periodically there is a crash, power surge, or sudden reboot of your computer.  Of course, you had SQL Server Management Studio (SSMS) open and you were working on something important.  That seems to be the only time there is a crash/reboot.  You can lose work that that was open in SSMS but has not saved.  There is an AutoRecover feature in SSMS so all may not be lost.  

Read on to learn more about auto-recovery. It only restores on crashes, though there are third-party plugins you can get to restore after start. Azure Data Studio restores on restart as well, so kudos to the ADS team for that.

Categories

July 2019
MTWTFSS
« Jun  
1234567
891011121314
15161718192021
22232425262728
293031