Voice of the DBA

Author: way0utwest

  • 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.

    verticalwindows

    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:

    verticalwindows_b

    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.

    verticalwindows_c

    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.

    verticalwindows_d

    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.

    verticalwindows_e

    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.

    verticalwindows_f

    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/

  • Five Minute Refactoring

    I’ve listened to, and been a part of, no shortage of arguments about the technical merits of a particular programming solution. I’ve seen two (or more) developers argue about the way to solve a particular problem for hours, even days at times. The Internet is full of discussions and debates in forums on the “best” way to write code for all sorts of specific issues.

    These arguments are sometimes referred to as religious wars for the devotion and belief that each proponent has in their solution. I tend to try and avoid becoming embroiled in any sort of zealotry as I am always cognizant of the fact that I need to be effective. I need to get things done and can’t spend a lot of time arguing about a solution, whether for or against it. i need to get work completed.

    When I was managing people I tried to limit the devolution of my staff into a never ending argument, but I ran across a better solution recently. Al Shalloway wrote a blog about limiting discussions to five minutes. He notes that two level-headed people ought to be able to come to a consensus in five minutes. Beyond that he says it’s likely that one of them is arguing about covering possibilities rather than current realities.

    That’s interesting, and like many rules, I’m sure there are exceptions. A manager can make a decision to continue a discussion if they really think that it is warranted. However as a default, the idea that a team should choose the easiest solution to implement if they can’t decide makes sense. It’s the least amount of work that might be wasted, and we want to avoid wasting time and work when we can.

    We won’t always agree, and despite our best intentions, we will make mistakes and we will make poor decisions. Don’t let indecision continue indefinitely and don’t rashly make decisions, but don’t let the fear of mistakes prevent you from choosing a path. Be decisive and move forward, adjusting as you learn more that helps you refine your solution.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.5MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • The Challenges of Space

    It wasn’t that long ago in my career when a survey showed the vast majority of SQL Server databases were < 10GB in size. These days I’m sure the average has risen as there are some very, very large SQL Server databases. I know a few people managing PB sized systems, which certainly will move the average upa bit. It’s likely, however, that the majority of databases are still very small, powering smaller applications with < 100GB.

    However almost every databases continues to grow, and as systems grow, new challenges come into play. While we can still store a 100GB backup file on a thumb drive or SD card, it’s still a significant amount of data to write, which can take more time than our clients would like. Archivals, backups, restore, all tend to come from non-flash media, so larger systems certainly mean longer RTOs if we have issues, even if we’re just restoring a copy of a database to extract some deleted information.

    However there can be more fundamental problems. I ran across a post from the CSS Engineers at Microsoft that talks about exhausting NTFS resources as more and more writes occur on a filesystem. I’d never heard of this, or expected it to be an issue, but apparently it’s occurring more often. I suspect even the NTFS developers hadn’t considered the scales to which we probably stress the file system daily. What is interesting is that this can occur with DBCC operations, database snapshots, and even bcp operations. There are workarounds, and certainly I’m sure that Microsoft has people working hard to increase the capacities of ReFS, which I expect will take over from NTFS, just as NTFS has largely displaced FAT.

    As we grow to larger and larger scales of data sizes, with multi-terabytes on laptops and petabytes on servers, I think we’ll need to adapt a number of our techniques and operations in the future. We might end up needing different types of backups (anyone considering lookup table backups or daily backups of a set of rows, like orders?), as well as the possibility of continuing to spread the load of our server across multiple drives. I just hope that Microsoft recognizes this and looks to ensure mount points work smoothly across all of their technologies and features. At some point, 26 drive letters won’t be enough for the majority of us.

    Steve Jones

  • The Rename Game

    As I’ve been talking about database development, Continuous Integration, and Continuous Deployment, I keep hearing complaints and concerns about the problems that occur when we make database changes. In particular, I often hear about breaking changes made to databases, that require some manual scripting work.

    It seems that the most common breaking change is adding a not null column to a table with existing data, but certainly there are other problematic issues. The other common change is renaming objects. In the past, I’ve rarely renamed anything once I’ve deployed tables to production, but I’ve run into quite a few developers that note they regularly change names. This week I’m wondering how common this is for most of the data professionals out there.

    How often do you rename objects?

    It could be tables, views, columns, constraints, whatever. If you rename anything in your database, I’m curious if it’s a regular event. Is this an event that causes you issues with your software development process, let us know. I’d also be curious with how you manage tracking and deploying the code from development through to production when you are making these types of breaking changes. Are things still scripted and able to be automated or do you force manual steps.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 1.9MB) podcast or subscribe to the feed at iTunes and LibSyn.