Learning Mandarin: What do the Chinese Lunar Rover and Mid-Autumn Festival have in common?

中秋节 (or Zhōng Qiū Jié) is the mid-autumn festival and it's this weekend. (Mid-autumn in China -> Northern Hemisphere).

It's also called the Moon Festival. In fact, it's celebrated on the 15th day of the 8th lunar month. Similar to the way that the date for Easter moves around, the mid-autumn festival will normally end up some time in September or October each year.

For many Chinese, this is a time for family reunions. The round shape of the full moon symbolizes this.

Chinese have long held the moon in great esteem. The legend of Chang ‘E is based around the wife of a tyrannical ruler who has lived on the moon since being flung out a window by her nasty husband. He was the king at the time. She is said to have lived there with her friend, a giant jade rabbit.

This legend is so embedded in the culture that the country named its lunar orbiters after it: Chang'e I, Chang'e II, and Chang'e III. In fact, their lunar rover was named Jade Rabbit (玉兔 or yù tù).

The traditional food for this festival is Moon Cake (月饼 or yuèbǐng). When I first tried this, I can't say I loved it but it has now grown on me somewhat. It's basically a very sweet and salty desert pastry filled with lotus seed paste or red bean paste. It might have a center of salted egg yolk (looking a bit like the moon). Because it's not to everyone's taste, many other flavors of moon cake are now made.

Anyway, this year the festival is this weekend so 中秋节快乐 (happy mid-autumn festival) to my Chinese friends !

Shortcut: Disable certificate revocation checking to start SSMS faster

If you have ever started SQL Server Management Studio in an isolated environment (ie: one with no external Internet connectivity), you'll find that it's slower to start.

That's because SQL Server uses signed assemblies, and whenever an application with signed assemblies starts, it needs to check whether or not the certificate that they were signed with has been revoked. It's not good enough to just check if it's a valid certificate.

Certificates include a CRL (Certificate Revocation List) and this tells an application that's trusting the certificate where to check for a list of revoked certificates.

The problem is that when you try to locate a server in an isolated environment, you might see a delay of around 40 seconds as the DNS timeout occurs.

If you have an environment like this, you might decide that it's safe to turn off this revocation checking. That's a call you need to make, and if in doubt or don't understand the issues, leave it on.

I often run across this though as I have isolated virtual machines running in Hyper-V on my laptop. SSMS isn't going to be able to look these details up, nor is any other application running within the virtual machine.

Turning this off is a registry setting but can be done via your browser settings. For example, in Internet Explorer, look in Settings, Internet Options, then Advanced. Scroll down to find it:

Keep in mind that if you disable this, it applies to all checking of certificates on the machine. As I said, if in doubt, don't do it.                   

Shortcut: Using Activity Monitor in SQL Server Management Studio

This is a quick tip but an important one. I see many people using SQL Server Management Studio (SSMS) and they aren't aware of Activity Monitor.

While there are many clever things that we can do with queries, to interrogate the health of the system, don't forget that there is quite a bit of useful information in Activity Monitor, and it's easy to get to.

There are two basic ways to launch Activity Monitor. The first is to right-click the server in Object Explorer:

The other common way to launch it is from the Toolbar:

Note that if you connect to more than one server in Object Explorer, Activity Monitor will connect to whichever one you have selected any object from within.

Activity Monitor puts a bit of a load on the server that it's connected to but I generally don't find it too bad. However, please don't leave it running and go on using other tabs. I've been to sites where there are many copies of it running all the time from several users. Don't do that.

I don't find most of the graphs at the top very useful, apart from perhaps the processor time.

It will show you if the server is running flat out.

The list of Processes is more interesting. If you right-click any session, you get these options:

The Details link will show you the last command executed on that connection. Take note that this doesn't mean it's still running. You can also kill the process (obviously carefully), and you can connect SQL Server Profiler to the server and filter the session immediately, to see what it's doing.

The columns are filterable.

They show you a list of values currently in that column, plus an All, and a choice of Blanks (rows with no value in this column) or NonBlanks (rows with anything in this column). They start as All.

For a simple example of using this though, we could pick sessions that have any type of command running, by choosing Task State of RUNNING.

One that I often use this view for is to look for blocking issues. Every process that's blocked by another process will tell you that. Generally, what I'm looking for is the head of a blocking chain ie: who's the main culprit that's blocking everyone.

For that, I look for a value of 1 in the Head Blocker column. Unfortunately, the way it's designed, you can't select that value until there is a row with that value.

The Application Name, Database Name, and Login can all be pretty useful as well.

The Resource Waits section is only mildly interesting.

The information there is at a bit of a coarse level to be really useful to me. Note that on this system, Buffer I/O is top of the list, but the cumulative wait time (since the server restarted) is small. Over time, if the system has been up for a long time, you can start to get a feel for the main waits in here, but be aware that there are a lot of values that can appear in here, without actually being an issue.

The Data File I/O list is a little more interesting:

This will show you how busy each data and log file is, for all databases. I generally sort it by Response Time (ms) descending. The value here is then basically the latency for the I/O on that file. In this example, it's 8 milliseconds. That's ok.

The Recent Expensive Queries list is interesting. The information is available from the system DMVs but this puts some useful data in an easy to get location:

It keeps updating this over time. Note that this won't be showing you queries currently running, just ones that were expensive and finished recently. If you right-click one, you can either look at the query text, or check out the execution plan that was being used.

The final section with Active Expensive Queries will only have data if you're using Live Query Statistics. I'll write about it another day.

Shortcut: Using the built-in web browser in SQL Server Management Studio

SQL Server Management Studio (SSMS) is a flexible tool. One thing that often surprises people is that it hosts a version of Microsoft Internet Explorer, right inside the application.

Why would SSMS have a web browser I hear you ask?

Well this web browser lets browse URLs, and reference links, without leaving the tool.

You can open it directly by choosing Web Browser from the View menu:

On my machine, it's under the Other Windows section. In fact, it's the only window there on my machine, which makes you wonder why it has a separate section in the first place.

It opens like any other tab:

In SSMS query tabs, you'll notice that there is an auto-detection of URLs and auto-formatting/linking of them:

If I execute that as a query, it doesn't get hyperlinked in the results:

Or on the Messages tab:

If you hover over the URL within the query though, you'll see that you can Ctrl-Click to open it:

If you use Ctrl-Click the link, it will open in the embedded web browser:

I'm hoping there will be an option to get a much more up-to-date browser though as support for Internet Explorer is waning everywhere.

Shortcut: Play a sound when a query completes in SSMS

In a previous post, I mentioned that when a long running query completes, I might not have been waiting around for it, and so I wanted to know when it completed.

But sometimes I do wait around for a query to complete, yet I'm distracted by other things and don't realize that the query has actually completed. That's not surprising because if a query takes a long time, I'm probably going to go on with other work while that's running.

So I want to get a prompt when the query finishes.

SQL Server Management Studio (SSMS) does provide an option for this. In Tools, Options, Query Results, there is an option to Play the Windows default beep when a query batch completes.

I do wish it was a stronger option than this but at least it's a start.

What I'd particularly like would be:

  • Ability to play a different sound, not just the default beep.
  • Ability to enable/disable this on a specific query window once a query is already running.

Having this on all the time would be quite annoying, so I'd be pretty selective about using it in its current form.

SDU Tools: Format Datatype Name in T-SQL

We've been building tools to create scripts for various SQL Server T-SQL objects for a long time. Part of scripting a table is the scripting of the data type. That means its datatype name, precision, scale, and maximum length.

In our free SDU Tools for developers and DBAs, we added a scalar function called FormatDataTypeName ot make that easy. It takes the following parameters:

@DataTypeName sysname – the name of the data type
@Precision int – the decimal or numeric precision
@Scale int – the scale for the value
@MaximumLength – the maximum length of string values

You can see the outcome in the main image above.

You can see FormatDataTypeName in action here:


To become an SDU Insider and to get our free tools and eBooks, please just visit here:


SQL: Adding many single column SQL Server indexes is usually counterproductive

I've just finished delivering a bunch of presentations across New Zealand, and one of the sessions that I've delivered in several places is Things I Wish Developers Knew About SQL Server. In that session, I mentioned briefly that most single column SQL Server indexes that people create are at best pointless, and at worst counterproductive.

I often see people making a poor attempt at indexing, and when they don't know what they need to do, they often add individual indexes on many columns in their tables. This isn't a good idea.

After those sessions I had a few emails from people puzzled about my comments. In particular, one had thought he'd heard that if you had a whole bunch of indexes like that, that SQL Server would mix and match what it needs out of those indexes.

However, if you look at the usage statistics for those indexes, chances are they are never seeked, looked-up, or scanned. They are only updated (ie: causing work to need to be done for no good outcome).

There are times when SQL Server will decide to read data from more than one index on a table but they aren't common situations. Even when it does this, it's rarely a particularly selective (ie: desirable) outcome. Yes, I can construct queries where it would do that, but they aren't common queries.

SQL Server will at times read an entire index (ie: an index scan). Usually when it does this, it's because it wants the value from every row, and the index happened to contain the required column. It's way less work to read the whole index than it is to read the whole table.

Rather than a large number of single column indexes, what you generally need is a set of indexes that cover the most important queries. You can't cover every query but you can do the most important ones.

Another key skill is being able to merge multiple indexes into a single index, without adversely affecting performance much. We call this, along with removing duplicate and subset indexes, "rationalizing" your indexes.

If you'd like to learn more about how to do this work, we have just released a new online on-demand course:

Designing Effective Indexes for SQL Server

We think it's great value normally but until the end of September, it's offered at a 30% discount.

If you need to find out which queries are causing you issues, and that you should focus on, we also have a new free online on-demand course:

4 Steps to Faster SQL Server Applications

I'd encourage you to take a look at them. You'll find these courses, and our other upcoming courses at https://training.sqldownunder.com.





Shortcut: Change connection in SQL Server Management Studio

I commonly run into a few connection-related scenarios:

  • I'm working on a large query and need to run it against several servers, not concurrently, but one after the other.
  • I've just made a database connection, and got my query ready, only to discover that I've connected to the wrong server.

Either way, what I've seen people do in these scenarios is to:

  • Select all the text in the current query
  • Copy it
  • Open a new query window
  • Paste the code

That's all good but SQL Server Management Studio (SSMS) has had a simpler way of doing this for quite a while.

If you right-click in the middle of a query window, you can choose to change the connection. Here's an example. I opened a new query window and entered a query, only to find that it connected to the Azure server that I was working with earlier. The easiest option is to right-click and choose to change the connection:

After you click Change Connection, you can log on again:

And then I can continue in the same query window as before. This is particularly useful if I need to run some code against a test server, and once I've decided that it was correct, I can just change the connection and connect to the "real" server.

At the bottom-right of your query window, you can always see which server you are connected to:

And at the bottom-left, you can see your connection state:

SDU Tools: Extract trigrams from strings in T-SQL

Fellow MVP Paul White and others have discussed the indexing of small segments of strings, to make fast indexes that I think are somewhat like full-text indexes but potentially more efficient. Three character substrings are common, and are known as trigrams.

I've experimented with these at a number of site and seen really interesting (good) results.

In our free SDU Tools for developers and DBAs, we added a table-valued function ExtractTrigrams to help with this. You can pass it a string, and it will pull it apart for you, and return the set of trigrams. This would make it much easier for you to experiment with these types of indexes.

You can see ExtractTrigrams in action here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:


Opinion: Don't have blind faith in hardware

There was a discussion the other day (on one of my lists), where someone pointed out that over many years, they had made tape backups, sent them via secured transport, and then paid for high-tech storage. And when they went to restore one of the tapes recently, there was nothing on the tape.

Over the years, I've lost count of the number of times I've heard stories like this. Long ago, I realized that you must never trust hardware.

There are just too many situations where you can get fooled. I'll give you a couple of examples. (I have many, many more).

I used to do maintenance on mini-computers at HP in the mid-1980's. Tape drives were some of the most fascinating pieces of equipment that I used to work on. The capstan-based devices were something, but the high speed vacuum drives were really wonders of engineering.

This shot from the movie Terminator shows one of the tape drives that I'm talking about, and some of our disk drives that were used with those systems:

I loved the way this guy was poring over the tape drive:

No idea what he would have been thinking about when looking at the drive.

But it reminded me of one of the HP tape drives I worked on where the erase head was stuck hard on. That meant that as the tape went through the drive, it first erased it as planned, then wrote to it, then read it back to make sure it was written correctly. All good to that point. But when it got to the end of the tape and rewound, it erased the entire tape.

I also saw a cartridge tape drive that was a multi-track drive. It had a problem where it wasn't stepping between tracks. So it would write one track, turn around and not step to the next track when it was supposed to, and then write straight back over the same track it just wrote, again and again.

You simply can't trust hardware. No matter how sophisticated it is, no matter how much error checking it has, etc. there's always something unplanned that can happen.

Unless you are periodically restoring your data (wherever it's stored), you have no idea if it's any good.

And importantly, you need to restore it on another system, not the one that you created the backups on. You don't want to find out later that the only device that could restore your backups was the one that created them, and it's now died.

Finally, you need to do a complete rebuild periodically. I've seen far too many systems where even when the restore was successful, people find that there was something else that should have been backed up but wasn't, and they now need it.

Don't be any of these people. You can get burned.