Skip to content

2015🔗

Restoring a database that doesn't exist

When restoring a database that doesn't exist, say for instance when a client sends a database to you, you can't use the option to restore database, because there is no database matching to restore. To get around this you need to use the Restore Files and Filegroups option and then restore the database. Restore database doesn Restore files and filegroups Another option I found interesting was the support for loading database hosted on a fileshare. Brentozar has an article on hosting databases on a NAS that I found interesting. I haven't tried it yet, but think it has a great usage case for dealing with various databases loaded from clients. If you haven't read any material by him... then my question is why are you reading mine? His whole team is da bombiggity.... stop reading my stuff and head on over there!

SQL Sentry Pro Explorer is worth it...

{{< admonition type="info" title="Updated: 2017-04-21" >}} Another great bit of news from reviewing this older post I wrote... SQL Sentry Pro is now a free tool thanks to the generosity of the Sentry One team! It's a must have. Go download it for sure. {{< /admonition >}}

{{< admonition type="info" title="Updated: 2015-04-28" >}} I created a few autohotkey scripts and solved the problem of collapsing panes and a few other annoyances. This has improved my experience a little. - Also noted one major improvement that would help with tuning is aggregating the total IO, and stats, rather than only each individual statement. I've found the need to compare two very different plans to see the total writes/reads variation and the impact on IO, but I've having to utilize another tool for statistics IO parsing to run totals, and then come back to the SQL Sentry Plan explorer for other details. The SQL Sentry plan explorer tool could be improved by enhancing with totals/sums to better compare various runs of plans. I can make do without it, but it makes me have to do a lot of workarounds for now. {{< /admonition >}}

I'll post more later, but after a full day of query tuning on a difficult view, I'd definitely say the cost for PRO is worth it. I'm a fan of sql sentry (free), and decided recently to push for a license at work on this tool. Turns out it was well worth it. The ability to measure variance in plans with small changes without cluttering up SSMS without 20 versions was incredibly helpful and time saving. There are a few quirks that really bother me, but not enough to negate the benefits of this tool. Perks - Save a continual session on troubleshooting a query - Evaluate Logical IO easily in the same view - Save comments on each plan version run to identify the changes you made and what impact it had Negatives - Not integrated with SSMS or preferred text editor so the text editor extremely sparse on features. - No ability to easily sum logical IO and COMPARE to another plan, really you have to open two tabs and eyeball them. That is the biggest frustration, no easy comparison side by side without opening the same session and eyeballing. - NO KEYBOARD SHORTCUTS. GEEZ is that frustrating as you are trying to power through some changes, copy cells/io, and more. Overall: Love the product. Hope they enhance the producivity and efficient aspect more as that's the only area I'm seeing it's short in. Here are some screenshots from my work with it today. I additionally compared the final IO with http://statisticsparser.com/index.html Image SQL Tuning Session 1 Image SQL Tuning Session 2 Image SQL Tuning Session 3 Image SQL Tuning Session 4 Image SQL Tuning Session 5

Statistics Parsing

Never really enjoyed reading through the statistics IO results, as it makes it hard to easily guage total impact when you have a long list of tables. A friend referred me to: http://www.statisticsparser.com/ This site is great! However, I really don't like manually copying and pasting the results each time. I threw together a quick autohotkey script that will detect your clipboard change event, look for "scan count" keyword, and then open a "chrome app", paste the results and submit. Note that I have the option "window name enabled" at the bottom of the textbox on the webpage. If you don't the tabcount navigation might be a little off, so tweak this if you want.

Dev Tools: FARR2 Launching groups of files or apps at once

There are probably a common number of apps you pull up when you pull up your system. For example, I pull up my Trello board, outlook, XYplorer, Sublime text 3, Sql server management studio, and ketarin (app updater). Found that you can easily setup a simply alias and launch a group of apps or files at anytime by simply typing the keyword.

create an alias dialogue

This could easily launch a favorite group of files by adding to your startup or project list as you go. The right click on search results gives you this option on the fly.

add new entry via context menu on FARR2

Dev Tools: The File Searcher/Launcher to rule them all

Why does this not have more recognition? In the experimentation of various file management and launching apps, I've tried several (Launchy, Listary, etc), but none have offered the speed and customization of Find and Run Robot. This app is a life saver for the power user! Here is an example of how you can have a hotkey to immediately launch a customized google search. The group alias gives you extensibility to filter the text you are typing to identify this alias of "Google Me" as the result to use since we typed ? as the first part of the string (that is the anchor ^).

Edit Group Alias Options

Note the encoding is handled by $$u1 for the websearch, automatically correctly encoding spaces, semicolons, and other characters.

group alias list example

Here's the final result of what you'd start typing.

filtered group alias shows google search

The cool part about this is the ability to not only match the initial regex, but also to filter inside this pattern to provide lists of options inside our match. In this example, I wanted to list favorite website by typing "G" at the beginning of the string followed by the keyword to filter my websites. This can be accomplished by anchoring the beginning of the regex filter to ^g, then filtering with the $$1.

multiple options to launch inside an alias

Here is the initial filtered match based only on "G"

filtering the results of the alias further

And finally the magic happens when the letters after "g" are parsed to get the website I want. This allows one to launch favorite websites easily, and you could even customize the url or more based on what regex magic you work!

the final result with minimal keystrokes

All of these concepts apply to launching favorite apps and more. FARR2 has more customization than apps like launchy, symenu, and more, as it allows one to easily tweak the search "score" and add bonus points to items matching common folders or file types such as exe, xlsx, and more. Score model is pretty amazing.

score model

Example of customized options to boost certain valuable matches in search results.

boost search points for file types or folders

Finally, the killer feature for those fans of Everything search tool (Void) is the integration of the Everything search engine as an option to quickly search your entire computer in millseconds. You can easily setup a search filter with a space at the beginning so that all you have to do is type space and your search phrase and it will switch over to using the plugin search engine.

everything search engine integration

Why does this tool not get more recognization! What a life saver as you search through sql files, projects, and docs! Hope this helped point you in the direction of an amazing tool... post a comment if you try it out and tell me what you think! Find and Run Robot Help Find and Run Robot Download

Case of the Mondays... causing me to randomly redefine the Scope of Global

Today, I was reminded that global temp tables scope lasts for the session, and doesn't last beyond that. The difference is the scope of the global temp allows access by other users and sessions while it exists, and is not limited in scope to just the calling session. For some reason I can't remember, I had thought the global temp table lasted a bit longer. Remembering this solved the frustration of wondering why my adhoc comparison report was empty..... #mondayfail SQLMag article I referenced