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.

Opinion: Don't buy hardware before a Proof of Concept

Just a short post today to call out something that I'm seeing again and again. It's where organizations purchase all their hardware and software platforms before they start to carry out a proof of concept. This is a very poor option.

I was reading the data strategy for a global company that I was doing consulting work for. They were proudly introducing the new strategy yet I was sitting looking at it, trying to work out what they were thinking. The first step of their plan was to buy everything they needed. The second step was to carry out a proof of concept to see how it would all work (presuming it would work suitably at all).

This is ridiculous.

In that case, I think what's happening is that the IT management wants to seem proactive, buying hardware and software platforms is what they are experienced at, and they want to look like they are "doing something".

Image by RawPixel
Image by RawPixel

Yet, invariably, this locks them into decisions that aren't in the best interests of the organization. Instead of making sensible decisions, they end up making decisions, based on what they have already committed to. And the more expensive that purchase was, the more they will try for years to justify the expenditure decision that they made. Every choice will later be taken, based upon how well it fits with their existing purchase.

Don't do this.

Do everything you can to carry out the proof of concept without buying anything that locks you into a decision path.

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:

http://sdutools.sqldownunder.com

Opinion: Take career risks while you can

In the 1980's and 1990's, part of my time was spent as a lecturer and tech services manager at a university. I particularly loved working with final year students and their project work. At our regular meetings though, I also often got into discussion with the students about their career plans, as they were about to graduate. What amazed me was how many super-bright students were looking to take incredibly boring jobs working on ancient technologies, in what were basically programmer graveyards, and when I asked them why they were intending to go there, invariably they'd tell me that they thought those jobs would be long term and low risk.

So a bright twenty-one year old student with no kids, no mortgage or other real commitments, and nothing much in the way of ties, was selling their soul for a low risk job.

Don't do this !

Take career risks while you can!

I understand that once you have a partner, kids, mortgage, etc., you don't have the freedom to try things. I've seen people I work with who are so tied to receiving a pay every fortnight that they can't make good decisions about their careers.

Image by Kevin Delvecchio
Image by Kevin Delvecchio

But if that's not you, don't sell yourself and your future short.

If you're worried about taking a risk, ask yourself what is the worst possible thing you can imagine happening, and then ask yourself if there is any way you could survive it, even if it's painful. And if you could survive it, don't hesitate to try. Anything that happens probably won't be as bad as you've imagined anyway. More importantly, you might just fly.

Image by The Nigmatic
Image by The Nigmatic

No-one flies day one, not even birds. While you can, just try things.

One of the saddest things I hear from older people is regret for the things they felt they could have done but didn't try.

Image by Ozan Safak
Image by Ozan Safak

Don't let it be you with the regrets.

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.

 

 

 

 

Book Review: Mistakes were made (but not my me) – Carol Tavris and Elliot Aronson

I've been listening to a lot of audio books lately and one that caught my eye was Mistakes were made (but not by me) by Carol Tavris and Elliot Aronson.

One of the mistakes that we all can make (and I've done it so many times myself) is to make a decision, find out it's wrong, but to then hang on to it for way too long.

It was fascinating (and probably a little depressing) how often I could recognize aspects of myself while reading this book.

There were many truly fascinating tales. The ones that most clearly stick in my mind though were about prosecutors who had pushed for a guilty plea in a murder case, later could not accept they were wrong even when the supposed victim turned up alive.

The book had a great discussion on cognitive dissonance.

The part of the book that I didn't love was the depressing section on the tragic stories about psychologists and the misconceived (and even evil) work around repressed childhood memories of sexual abuse. The point was well made but the book seemed to really belabor the point way too far.

Apart from that section which went on too long, I found the book fascinating.

Greg's rating: 7 out of 10

Note: as an Amazon Associate I earn from qualifying purchases but whether or not I recommend a book is unrelated to this. One day it might just help cover some of my site costs. (But given the rate, that's not really likely anyway 🙂