The Bit Bucket

中秋节 (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 !

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.                   

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.

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.

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.

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

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.