I had the need to connect from the command line recently, and decided to make a quick post on using SQLCMD, as I had an issue.
SQLCMD is a command line utility that comes with SQL Server. I know many people don’t use command lines, but they are handy at times.
SQLCMD and OSQL aren’t things you typically need to use a lot, but sometimes you won’t have the ability to run Management Studio and it’s good to know that there are built-in alternatives.
The best features are the ones that you use all the time. SQL Server 2016 Management Studio’s bringing improvements in navigating around execution plans.
Click + Mouse Scroll: Zooming!
You can now make your plans bigger and smaller with this combo. It will zoom into the region where you have the mouse.
Click + Drag lets you move the plan
This is really handy for moving right to left.
Good for those times when SQL Sentry Plan Explorer isn’t available.
Above, the GUI incorrectly base the restore on a copy only backup. After using the timeline dialog to point to an earlier point in time, you can see that the GUI now has changed so it bases the restore on this potentially non-existing copy only backup. Not a nice situation to be in if the person doing the restore hasn’t practiced using the T-SQL RESTORE commands.
It’s important to be able to write the relevant T-SQL queries to restore your database, just in case you run into one of these issues.
As a quick tip, this on is one of these tool tips, that just makes your everyday much easier. Sometimes, more than often, you run into databases that contains a huge number of tables – all listed alphabetically. This can, at times, be cumbersome and annoying to browse. SQL Server Management Studio (SSMS) actually has a feature that will assist you in getting your job done, quicker; It’s called Filtering.
My most common filter criterion is by schema, but there are several filtering options available.
Gianluca Sartori (@spaghettidba) blogged about the issues you may have noticed with older versions of Management Studio on HiDPI, UQHD, or 4K/5K screens in his post, “SSMS in High-DPI Displays: How to Stop the Madness.” Essentially, Windows tries to scale things for your DPI settings and, depending on the technology used to render your fonts and dialogs, this can end up with ugly and sometimes unusable screens. Gianluca’s fix is to make a registry change and add a manifest file to the SSMS directory so that SSMS no longer tries to render Windows’ scaling changes. In the latest builds of SQL Server 2016, SSMS now acts as if the manifest file were in place. Take a look at the following image:
That’s music to my ears; I use a high-resolution laptop and before Gianluca’s solution, it was impossible to use SSMS. I’m looking forward to SSMS 2016, but probably won’t move until the add-ons I use are supported; I’ve grown to like them too much to make the jump, even on a trial basis.
Now, in the process of developing Plan Explorer, we have discovered several cases where ShowPlan doesn’t quite get its math correct. The most obvious example is percentages adding up to over 100%; we get this right in cases where SSMS is ridiculously off (I see this less often today than I used to, but it still happens).
Interpreting execution plans is not a trivial exercise, and this is an interesting look at how SQL Sentry developers (and supporters within the broader community) have worked on it through the years.
Simply put ASYNC_NETWORK_IO waits occur when SQL Server is waiting on the client to consume the output that it has ‘thrown’ down the wire. SQL Server cannot run any faster, it has done the work required and is now waiting on the client to say that it has done with data.
Naturally there can be many reasons for why the client is not consuming the results fast enough , slow client application , network bandwidth saturation, to wide or to long result sets are the most common and in this blog I would like to show you how I go about diagnosing and demonstrating these issues.
Dave goes on to explain this using Management Studio examples, but the information also applies to other client applications.
The next time you run a query (you might need to close all your query windows or restart SSMS, you usually do with this sort of change in SSMS) it’ll beep when the query is done.
Personally I’ve actually used this as an alarm clock when doing long running overnight deployments so that I’d get woken up when the script was done so I could start the next step. It’s also handy when you want to leave the room / your desk while queries are running.
I must have seen that screen dozens of times and never once noticed this checkbox.
If you work with SQL Server for a long time you’ve probably learn some Keyboard combinations to speed up your administration or development process.
The full list of SSMS Shortcut keys you can find in MSDN
I will try to re-categorize the most interesting ones
If you spend a lot of time in Management Studio, learning keyboard shortcuts will make your life easier.
Sometimes when using SSMS you will see a redline under a table or object name in your T-SQL. This means SSMS thinks the object doesn’t exist in the current database. Usually it’s right, but if you have just created the object, the query editor wont know as it’s local cache is not regularly refreshed. To force a refresh you can hit Ctrl + Shift + R but I always forget keyboard shortcuts. For this I like to add a button to the toolbar.
This is a good intro-level article on SSMS basics and some configuration options.