Voice of the DBA

Category: Blog

See Two Queries at Once in SSMS

I’m writing this post as a way to help motivate the #SQLNewBloggers out there. Read the bottom for a few notes on structuring a post.

One of the things that I’ve struggled with a bit in SSMS is sometimes comparing the results of two batches. I’m sure many of you have executed a query, then make a change, and execute it again, losing your results. Or you are testing something in two query windows and need to switch back and forth. Sometimes doing this, and only seeing one set of results (or checking if a query is finished) is cumbersome.

A few years ago I was watching Brent Ozar tune queries at an event and one of the things he wanted to do was compare two queries and their execution plans. He used vertical tab groups, which is a great way of seeing two things at once.

Here’s how my screen ended up during the comparison I was actually doing of three queries. I was checking credentials using a before, after, and with the DAC.

Things are a bit shrunk down as I wanted the image to be viewable. I actually had this full screen on a 30" wide monitor, and I could more easily see the queries and results from each window.

The easiest way to do this is to start with a query:

I want to change something, add a login, and test again, but I don’t want to lose my results. I’d also like to do an easy comparison. What I can do is go to the Window menu and get a new Vertical Tab Group. I could also do a Horizontal one, but comparing results is easier for me with vertical ones.

Once I select this, my selected window will move to a new tab group, and I’ll see two places where I can run code and visualize the queries and results at once.

I selected the left hand query, then clicked "New Query" to get a blank window. I then cut and pasted my code from the right to the left. This is exactly what you might want to do when tuning queries, keeping the original on the right while you work on the left.

Now I have two places to work on code. In my case, I wanted the before and after view of Server_principals as I checked some admin changes. I could do things and keep re-running the query in one of these windows, but keep the results from the other one visible.

SQLNewBlogger

Once again I was doing something else and realized the vertical window trick was handy. I killed the three windows I had, set up a new query, shot the screen, ran through the process with more screen shots.

Less than 10 minutes.

References

Watch Brent Tune Queries – http://www.brentozar.com/sql/watch-brent-tune-queries/

June 15, 2015
Using the DAC with SSMS
I’m writing this post as a way to help motivate the #SQLNewBloggers out there. Read the bottom for a few notes on structuring a post.

While troubleshooting another issue, I needed to connect to SQL Server with the DAC. I couldn’t remember the syntax, so I looked it up quickly and ran into this link: Diagnostic Connection for Database Administrators. I added the ADMIN: to my connection in SSMS for a query window and clicked Connect.

This took entirely too long and then I got this:

That error resulted in a rabbit trail for me to debug this, but it worked out and I’ve learned a few things. The main one is to be sure that I’ve read the documentation and errors correctly.

As I tried a few things, including SQLCMD, I realized I had issues. I searched and found that the DAC gets set to a specific port. I checked the error log to get the number.

With that, I added it to my connection dialog, sure that this would work, but of course, it didn’t.

Eventually I stumbled on the post about errors and realized I should have checked the SQL Browser earlier. I thought about it, but discarded that thought because I could connect in other ways. Mistake. Check networking first, and networking is where the browser comes in. Sure enough, it was stopped.

The properties were set to DISABLED, so I had to change that before I could start it. I assume you can do that, if not, poke around the properties until you find that setting. With that changed, I started the service.

Then I could connect. Here’s the SQLCMD version.

With this working, the first command dialog at the top of this piece worked from an SSMS query window.

SQLNewBlogger

This was longer. What I thought was a quick lookup turned into a troubleshooting exercise that lasted about 20 minutes as I searched, read, experimented, etc. At least I made a bunch of screenshots as I was experimenting, so I had lots of data and didn’t duplicate anything.

The actual writing was only about 10 minutes, most of that looking through screenshots and trying to organize my thoughts.

References
- Enabling the DAC – http://www.mssqltips.com/sqlservertip/1801/enable-sql-server-2008-dedicated-administrator-connection/
- The DAC: Why, When, How – http://www.brentozar.com/archive/2011/08/dedicated-admin-connection-why-want-when-need-how-tell-whos-using/
- Errors when Trying to Use DAC Connection in SQL Server – http://www.sqldbpros.com/2013/10/error-when-trying-to-use-dac-connection-in-sql-server/
June 11, 2015
Unexpected Windows 10

This was strange. I noticed a new icon in the taskbar today. When I hovered over it, I saw this:

I downloaded the Windows 10 beta in a VM, but not here. However apparently some update added this. If I click on it, I see this:

I hadn’t been reading about Windows 10 too much. Mostly I want it for the copy/paste in the command line, but apparently I need to download it to get the upgrade. I hadn’t heard this before, and I was concerned.

If I click the reserve upgrade, I see this:

I don’t need a download now. Apparently I’ll get an automatic one when the OS goes to RTM, which is fine. I assume I can install it elsewhere, but perhaps I’m limited to this device?

It’s hard to tell. This will be an interesting process. I went through the FAQ and it appears I could upgrade later. The upgrade I reserved is for this device, so I’ll need to do this separately on my laptop.

A new process for Windows, and I’m interested to see how it works. I’ve seen a few MS people have issues with Windows 10 in demos, so I’m a little concerned about upgrading now, but I am looking forward to seeing the new OS in July or August.

June 10, 2015
T-SQL Tuesday #67 – Extended Events for DBCC

It’s the second Tuesday of the month and time for another T-SQL Tuesday. This time it’s #67 from Jes Borland on Extended Events. You can read the invite, and whether you can participate today or not, write your blog and leave a comment for Jes on her blog.

If you want to see all the topics from the past, I have them on a post here.

If you want to host, contact the founder of T-SQL Tuesday, Adam Machanic. He chooses the hosts and is always looking for people that want to participate. You just need a blog and a good idea.

Who Ran DBCC?

I ran across a question recently from someone that said DBCC messages had appeared in the error log, but no one had run DBCC. That’s not possible as DBCC isn’t run without someone, whether that’s a human or a service, executing the DBCC command.

In the past, I might recommend a trace to track this, but not I’d choose an Extended Events (XE) session. I’ll show how to set up a simple session, though be aware I’m not an XE guru. I’m sure there are better ways to structure this, but it worked well for me.

You can start quickly building a specific XE session by right clicking the Sessions folder under Extended Events in SSMS under Management. I tend to pick the wizard for getting started, though be sure to save the script later.

From here we get the opening screen for XE, which I won’t show. The next screen asks for a name for the session. It doesn’t matter, but once you start using XE, you’ll start to get quite a few session, so it’s handy to pick something that’s simple and easy to understand later.

I can choose to start the session here when I’m done, but don’t worry if you’re not sure. You’ll get a chance again later.

The next step is to decide whether you use a template or build a session from scratch. I’m not sure if any of these templates will work well as I haven’t dug in. In my case, I decided to just choose a generic session.

Now I need to pick the events. There are a lot, but I went down to get the sql_statement_completed as my event. This will let me see the text of a call, which is what I need.

A couple notes here. One, you might want to grow this screen so that you can read what’s in the lower windows. I know there’s a lot of data here, but this screen doesn’t seem well defined.

The second note is that don’t forget to add the event with the arrow button. Until you have an event (or multiple events) in the right window, you can’t click "Next".

The next step is to get the data fields you need. Again, there are a lot, and I limited this to what I needed. I grabbed the client host and app, as well as the database. Then I grabbed the service_principal_name and sql_text as well. This should let me determine who’s running the command.

I’ve got lots of data here, but I only care about the execution of dbcc checkdb. This means I need to filter things, which is my choice with the next screen.

Here I need to click in the top line to get a place to add a filter. Then I select sql_text from the drop down and choose like for the operator and "dbcc checkdb" for the value. This should grab events that execute with a dbcc call.

I have to choose where to store this data. Since this is mostly a check for me, I can just use the ring buffer and keep data in memory. I lowered this to 10 events, but you can certainly pick what works for you. If you care about auditing, drop this in a file. For a small session like this, there’s not much data that’s going to be captured.

I get a summary screen, which lets me review settings. I haven’t shown it here, but once I pass that, I get the "Success" screen. This is where I can start the session, and actually watch live data if I want.

For this, I’ll watch the live data.

I decided to test this first and see if it captures anything else. I ran a few queries, and then this:

Still a blank, live data screen. Then I ran checkdb. I saw the results, and then ran it a few more times.

As you can see, I now can see that I ran checkdb against the EncryptionPrimer database.

This lets me do some quick auditing of what’s happening with my DBCCs. A smart DBA might even use this to ensure that DBCC is being run against some databases, perhaps on a spare machine.

June 9, 2015