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.
Tip 4: Results Grid aggregates – Some people do a lot of calculations with their data, whether it’s sales data or whatever. You end up saving the query results to Excel and about five minutes later you have the totals, etc. With SSMSBoost, you can have your totals in seconds. Below I’m selecting 10 records from a sales related table. Say I want to get the SUM, MIN, MAX and Count of the SALEPRICE. All I do is slide my mouse down the column highlighting the cells and a pop-up will appear with that information:
This is probably my second-favorite feature of SSMSBoost; my favorite is automated crash recovery, and my third-favorite its snippet support. SSMSBoost has a free version and I use it myself. I try not to push many tools here on Curated SQL, but this is one worth checking out.
That is most difficult operation. At first, SSMS can’t show more than 5000 separate objects at the same time. In order to show more we have to construct “MULTIPOLYGON” or “GEOMETRYCOLLECTION”. That only the way to fit more objects into SSMS screen. However it is still limited.
In order to combine triangles in a single object we divide them in buckets (Line 106).
In this example I just making number of buckets approximately equal to a number of objects within each bucket. Making lower number of buckets will increase processing speed, but produce less colors. All objects in one collection will have the same color.
Also, I wrapped the last query in extra CTE to have more flexibility on results formation.
This is a fun post showing some of the power and limitations of geometry types in SQL Server and their display in SSMS.
One of the tips that I was super surprised that many people didn’t know is the Object Explorer Details. It allows you to delete multiple objects at once, script out multiple objects at once and just do some really cool stuff. How do I access this magic you are asking? When in management studio, click on View>>Object Explorer Details.
For those one-off jobs where you need to script out a dozen objects, this is very helpful.
Everyone knows you can use SHIFT + [Left/Right Arrow] to highlight text. But you can also use ALT + SHIFT + [Up/Down/Left/Right Arrow] to select a block of text or even make a vertical selection to insert a block of text on multiple lines. Or you can use ALT + [Mouse Drag] to make a block selection with your cursor.
Notepad++ works the same way. Every once in a while, I’ll run into a scenario in some tool which doesn’t implement Alt key functionality—especially certain non-Microsoft platform database products—and it will hurt a little bit inside.