Opinion: Having staff stumble around is false economy

One thing that I see time and again on customer sites is staff who really don't know what they're doing in trying to solve a problem, or when they are trying to implement a new solution, yet their company just continues to pay them to stumble around while getting almost nowhere.

I'm not talking about someone who's taking longer to achieve something than an expert. I'm talking about staff who are really out of their depth.

Paying someone to do that rather than getting them help or training, so that they know what they're doing, is simply false economy. Implementing poorly designed solutions is even worse.

An even better option would be to pay someone to sit with them and mentor them while they are doing the work. I have a real preference for this as it allows the mentor to just fill in the knowledge gaps, and after all, it's the staff who will be there later when the solution needs to be looked after.

The real trick for a manager though, is to work out how to detect when this is happening. I see three problems in this area:

  • Often the staff involved will not be keen to highlight their own deficiencies
  • Some staff like to just poke around on a problem or idea as that might be "fun"
  • Often the manager won't have the technical knowledge required to be able to detect the skills shortage and might be blind-sided by the staff

And yes, I understand that at many companies, the training budget is a separate bucket to the payroll. But surely someone must be responsible for the overall profitability (or cost avoidance) at the organization.

I'd love to hear your thoughts.



SQL: Shrinking Files and Auto Shrink in SQL Server – Just Say No

I first started using SQL Server back in 1992 with version 4.2 I was running a software house and was hoping to use SQL Server as the back end of our updated application. 4.2 wasn't up to it. Versions 6 and 6.5 were closer but I still thought they needed far too much administration for me to be comfortable deploying them to most customer sites. Version 7 though, changed the game. Rather than having to manage devices (which was painful), we now had just standard operating system files, and even had options for automatically growing and shrinking the files.

Growing automatically was a big deal. Running out of disk space within SQL Server 6x devices was really painful and commonplace. Automatically shrinking was an interesting option that we tried on some sites.

In building version 7, Microsoft made a conscious effort to work out if there was a way to automatically set anything that a DBA normally needed to set themselves. Version 7 was the first version of SQL Server that we really felt comfortable deploying to customers who had no onsite IT people. We deployed it, and the sites just went quiet.

Awesome image by Kristina Flour

Awesome image by Kristina FlourFor a software house (ISV), quiet is good.

We tended not to use Auto Shrink. Manual shrink worked but we barely ever used it anyway and that didn't change through later versions.

However, Paul Randal noted that in 2005, a bunch of things made shrinking much, much slower. We started hearing more and more horror stories about shrinking.

To give you an example, I was at a client site a year back, and they'd decided to shrink a 2TB database back to 1.3TB. They started the shrink and within an hour, it said it was 64% complete. However, a week later (yes a whole week), it was 62.8% complete. They had contacted us because they thought it was never going to complete. Worse, they were now seeing deadlocks that didn't normally happen. The deadlocks stopped if they stopped the shrink operation.

This is a horrible outcome for them. And worse, even if it had ever completed, it would have so messed up the internal fragmentation within the database that they would have needed to rebuild all the indexes anyway.

What we did instead was to create a new database and migrate the data over. It took 2 hours and was super-clean. We did it in the right order to avoid the need for index rebuilds later.

Shrinking a SQL Server database isn't a good option right now. Auto Shrink is almost always an even worse choice.

Migrate the data to another database, or rebuild the data onto a new filegroup if that's an option.


Learning Mandarin: Assessing Progress with the HSK Exams

In my last post on learning Mandarin, I talked about how long it takes to learn the language. I discussed that like learning English, there is no real end point to the learning, only stages of achievement. So how can you assess where you're at? The answer is the HSK and HSKK exams. So what are they, and how do they match other definitions?

US State Department Proficiency Levels

The US State Department offers a definition of language proficiency here. It defines these levels:

0 – No Practical Proficiency (No practical speaking proficiency. No practical reading proficiency).

1 – Elementary Proficiency (Able to satisfy routine travel needs and minimum courtesy requirements Able to read some personal and place names, street signs, office and shop designations, numbers and isolated words and phrases).

2 – Limited Working Proficiency (Able to satisfy routine social demands and limited work requirements Able to read simple prose, in a form equivalent to typescript or printing, on subjects within a familiar context).

3 – Minimum Professional Proficiency (Able to speak the language with sufficient structural accuracy and vocabulary to participate effectively in most formal and informal conversations on practical, social, and professional topics Able to read standard newspaper items addressed to the general reader, routine correspondence, reports, and technical materials in the individual’s special field).

4 – Full Professional Proficiency (Able to use the language fluently and accurately on all levels pertinent to professional needs. Able to read all styles and forms of the language pertinent to professional needs).

5 – Native or Bilingual Proficiency (Equivalent to that of an educated native speaker. Equivalent to that of an educated native).

On that scale, I'm heading towards 3.

HSK Exams

A much more accurate scale is provided by exams that are run world-wide and controlled by the Chinese government. These are called the HSK exams (Hànyǔ Shuǐpíng Kǎoshì or 汉语水平考试 ).  Hànyǔ means "Chinese" (ie: language of the Han people), Shuǐpíng means "level", and Kǎoshì means "exam". HSK is a standardized proficiency test of Standard Chinese language, notably, for non-native speakers.

At level 1, you need to know 150 words (174 characters). It is described as being designed for learners who can understand and use some simple Chinese characters and sentences to communicate, and prepares them for continuing their Chinese studies.

At level 2, you need to know 300 words (347 characters) in total (not extra over level 1). It is described as being designed for learners who can use Chinese in a simple and direct manner, applying it in a basic fashion to their daily lives.

For both levels 1 and 2, all characters are provided, along with their pinyin representation. The tests only include reading and listening. They have no writing questions.

At level 3, you need to know 600 words (617 characters). It is described as being designed for learners who can use Chinese to serve the demands of their personal lives, studies and work, and are capable of completing most of the communicative tasks they experience when visiting Chinese areas.

At level 4, you need to know 1200 words (1064 characters). It is described as being designed for learners who can discuss a relatively wide range of topics in Chinese and are capable of communicating with Chinese speakers at a high standard.

At level 5, you need to know 2500 words (1685 characters). It is described as being designed for learners who can read Chinese newspapers and magazines, watch Chinese films and are capable of writing and delivering a lengthy speech in Chinese.

Notice that the number of words required increases exponentially. I've passed HSK 3, prepped for HSK 4 about a year and a half ago, and am comfortable with it. I just didn't get time to take it but will soon. For just over a year though, I've been prepping for HSK 5. It's far more of a challenge. Levels 3, 4, and 5 include writing tests. I'm fine with those on a computer, not so great with a pencil like they used at RMIT when I did the HSK 3 exam.

Note that HSK 5 is also the level normally required for foreigners who want to attend Chinese universities. To give you an idea of how much Chinese you would have learned at that point, here's an example question from a recent HSK 5 exam:

Above those, there is level 6 where you need to know 5000 words (2663 characters). That's described as being designed for learners who can easily understand any information communicated in Chinese and are capable of smoothly expressing themselves in written or oral form.

Level 6 includes a requirement to write an essay.

It would be awesome to ever get to level 6, but given each level is basically twice as hard as the previous, that might take me a while.

HSKK Exams

As well as the HSK exams, you can take HSKK exams. These are spoken conversation exams and have three levels:

Beginner (maps to HSK levels 1, and 2)

Intermediate (maps to HSK levels 3, and 4)

Advanced (maps to HSK levels 5, and 6)

I haven't taken any of these yet. I had a friend who did the HSK 3 exam with me and she took the Intermediate exam at the same time. She found it quite hard.

Should you do the exams and where are they?

If you have a real interest in learning Mandarin, I think you should combine that with testing, to make sure you really are getting somewhere. They are held all over the world, in most larger cities. They usually happen twice each year.

Previous exams are available as samples to let you test yourself.

Learning Mandarin

I'll write more soon on the best methods for learning. If you want to get a taste for it in the meantime though, my current favorite is Tutor Ming. If you decide to try it, click here and it's a bit cheaper for you, and for me.


Book Review: The Phoenix Project

I've been spending a lot of time lately doing DevOps related work, and I thought it was important to post a review of what is often considered the first "DevOps fictional book". I know that sounds like a geeky book but it's not quite as you might expect. The Phoenix Project: A Novel About IT, DevOps, and Helping Your Business Win by Gene Kim, Kevin Behr, George Spafford is a bunch of fun.

It's the story of Bill. He's an IT manager at Parts Unlimited. (And for those who've done much Microsoft-related DevOps work, yes that's the same name as in the Microsoft sample apps). Bill's day is starting badly when he's pinged by the company's CEO.

Phoenix is the name of a new critical company project  that's in trouble: it's way, way too late, and way, way over budget. Yes, we've all seen projects like that. The CEO wants it fixed and he wants Bill to do it.

The authors of this book are well-known in the DevOps communities but perhaps the surprising part is how well this book is written. It moves quickly and is very entertaining to listen to. I wasn't the only one in my family to listen to it, so that's a testament to it not being too geeky. It's mostly about the human processes involved.

Now even though there are setbacks, everything ends up going better than what I see in most companies, but it's still full of interesting lessons.

This is regarded as a seminal book related to DevOps (even though it's fiction).

Bottom line: If you've never read this or listened to it, and you're into either DevOps, or just into trying to get things done in an organization, it's time to do so.

Greg's rating: 9 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 🙂

Shortcut: Multi-server queries in SQL Server Management Studio

In an earlier post, I mentioned that you can create a registered list of servers, either in Local Server Groups or stored in a Central Management Server.

What I didn't really talk about though, is what you can do with these groups of servers, rather than just executing queries on an individual server.

I've created three local server groups, for my development, UAT, and production servers.

The Development Servers group has three database servers in it. If I right-click the group, rather than any individual server, we get these options:

I'll talk about policies another day but notice that you can import or check (evaluate) policies across a whole group of servers.

But the option that interests me today is the New Query option. When you click this, it opens a query window for the group of servers.

The window color at the bottom has changed from the default, and in the bottom right, we can see that the window is connected to the local server group:

If I type the query SELECT @@VERSION; and click Execute, I see this:

Even though this looks like a single result set, this is just a SQL Server Management Studio (SSMS) trick. Under the covers, it has run the query individually against each server. It has just presented the results to us as though they are a single set of results.

We can configure several things about how this happens. In Tools, Options, Query Results, SQL Server, Multi-server Results, we have these options:

We could add a column that shows our login name for each server to the results. We could remove the server name if required, although that doesn't seem very useful. And we could choose to not have the results merged. If we do that, SSMS returns a separate result set for each server.

You can also change the color of the bar below, in this location:

Multi-server queries were an interesting addition to SSMS. They are useful for a relatively small number of servers. As the number of servers increases, they would become more fragile, and you might want to consider using a 3rd party tool that works out which servers have or haven't had the query run, retry options, etc.

SDU Tools: List Incoming Foreign Keys in a SQL Server Database

One of the challenges when you have foreign keys in place, is that there are restrictions on what you can do to tables that are the targets of the foreign keys. What's often needed though is a list of all references to a particular table or set of tables.

In our free SDU Tools for developers and DBAs,  we added a function ListIncomingForeignKeys to do just that, and to provide their details in a form that's easy to consume programmatically if you need that.

You can see how to execute it in the main image above. The procedure takes these parameters:

@DatabaseName sysname – This is the database to process
@ReferencedSchemasToList nvarchar(max) – a comma-delimited list of schemas to include (ie: 'Sales,Purchasing') or the word 'ALL'
@ReferencedTablesToList nvarchar(max)- a comma-delimited list of tables to include (ie: 'Customers,Orders') or the word 'ALL'

One row is returned for each foreign key.

The columns returned are SourceSchemaName, SourceTableName, ForeignKeyName, SourceColumnList, ReferencedSchemaName, ReferencedTableName, ReferencedColumnList, IsNotTrusted, IsDisabled.

You can see it in action here:

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


Opinion: Design the Business Model, not just the App

I posted the other day about how the pricing of apps has become silly. Most apps are priced so low that there really isn't much income but worse, most don't have a business model for the authors.

I'm sure that many smartphone app developers just think they can offer an app and get a bunch of money in quickly. They don't seem to have thought about what happens beyond that point.

But for both them and the users of the apps, there needs to be an actual business model. 

So many apps require ongoing back-end servers to function, yet they have no recurring funding model within the apps. Users will expect the app to keep working across operating system upgrades to their phones, even though upgrades to the apps will be required for them to still work well. Finally, ongoing bug fixes and security patches need to be funded somehow.

So many vendors ask you for $3 for purchase, yet have no ongoing income to fund back-end services and upgrade coding. They are typically depending on one of two things:

  • There will be a constant stream of new people buying their app so they'll have a good flow of income
  • They'll sell upgrades to the apps

Selling upgrades might work where functionality is added but will be a much harder sell when it's just fixing compatibility issues with later OS versions. Users will see that What I've noted lately though, is that a number of them are starting to realize they really have no ongoing business model at all. Here's an example:

I love the FlightRadar24 on the iPhone. It was cheap to buy and it just kept working, even though they didn't get income from me. Recently though, they offered an upgrade to a new version, but the subtle change was that for the upgrade fee, they'd convert your existing permanent license to a three month subscription for the new version. They're now trying to move existing permanent licensees to a subscription model.

Now they might be really clever and have planned to do all this in the first place, but I suspect that it's more likely they've realized they need ongoing income.

Image by RawPixel
Image by RawPixel

If you're going to design an app, you need to also design the business model. It's much harder to fix that later.

An MVP (minimum viable product) needs to be viable.

SQL: What is REFERENCES permission and why does it matter?

I was at a client site the other day and they were puzzled by SQL Server object permissions. In particular, they couldn't understand what the point of the REFERENCES permission is. It seemed pointless to them. It's not. Let me explain why.

Keep in mind that if all the objects in the database have the same owner, then this isn't an issue. But not all databases are like that. Many have different teams that control different parts of a large database.

Imagine that the Accounting team owns a Customers table. The Marketing team wants to create a LoyaltyPoints table and wants to add a foreign key to the Customers table. What permission do they need?

REFERENCES is the permission required.

But why do they need permission at all? It's because after they create the foreign key, the Accounting team is now restricted in what they can do with their Customers table. For example, they can no longer truncate the table. If they want to drop and recreate the table, that won't work. Before they can delete a row in the Customers table, it's now a requirement that no related rows exist in the LoyaltyPoints table.

Because the owner of the table will be restricted by the foreign key, they need to grant at least REFERENCES permission to anyone who wants to create a foreign key to their tables.

Learning Mandarin: How long does learning it really take?

If I've convinced you that learning Mandarin is worthwhile (that's how 1 in 7 people communicate world-wide), the next question I often get from people is about how long it takes to learn. Now if someone asked you that about English, what would you say? There's really no end point to the learning.

It's the same even just with words. While it's easy enough to learn the English alphabet, knowing the 26 letters doesn't actually give you an ability to understand a particular word. If I wrote Brobdingnagian, you might be able to guess how it's pronounced (because you've learned pronunciation rules over the years) but I'm guessing you probably don't know what it means.

So if I rephrase the question and ask you how long it would take to know all the words in English, again the answer is "forever". And it's the same in Mandarin.

So a better question is how long it takes to get to a particular level of proficiency/fluency. In a later post, I'll discuss using the HSK exams to accurately measure this but first I wanted to give you some rough ideas.

I've read US State Department reports that say that if you stopped every other thing in your life and just learned Mandarin, in about 2200 hours you'd be pretty fluent. At 5 hours a day, that'd be 88 weeks, or just under two years, presuming you had weekends off. At 8 hours a day (which would be quite insane), it'd be about 55 weeks, or just over a year.

But it's hard to imagine anyone being able to do that, so if we allowed 1 hour per day, and added an allowance for forgetting things between those hours (because life intervenes), it'd be around 8 or 9 years of part-time learning.

I've spent about 6 years learning on and off up till now. And I'd say that's pretty close to correct.

My reading is reasonable but I only know around 1800 characters. You really need around 2500 to be able to read newspapers, etc. with confidence.

My speaking is ok. I can describe a wide variety of topics fairly confidently. I still mess up sentence structure at times though. You'll often hear Asian people messing up the order of their English sentences. That's me in reverse.

My listening is improving. I can quite happily hold conversations with teachers for an hour at a time without English (apart from when translating something), but their language quality is good, and they aren't always speaking at native speed. I still often hear things, struggle to know what was said, but if I heard it repeated slower, I was actually able to understand it.

So for me, it's a work in progress.

I can't tell you though, how much I've enjoyed learning it, and how it's opened up another world to me, one that's right there but as Westerners, we don't see.

I intend to just keep learning more and more, and improving year by year. Each week, I probably dedicate about 5 or 6 hours to doing so but I also take advantage of every opportunity to read signs, menus, etc. and to speak to native Mandarin speakers.

Learning Mandarin

I'll write more soon on the best methods for learning. If you want to get a taste for it in the meantime though, my current favorite is Tutor Ming. If you decide to try it, click here and it's a bit cheaper for you, and for me.


Book Review: 1984 – George Orwell

One of the things that using Audible has let me do lately is to get through additional books that I'm interested in. Part of that has been to go back through real classics. They don't come more classic than 1984 by George Orwell.

When I was at high school, this was one of Orwell's books that I read. Listening to it again now though, two things struck me:

  • How much I'd forgotten of the detail in it
  • How chillingly relevant it is today

The first two chapters in particular had me simply amazed at how prescient Orwell was. So many aspects of what he described that were clearly intended to be horrific and unthinkable at the time, and yet are so close to the current reality in many places that it's quite chilling to listen to.

The book describes Winston Smith living in a dystopian world that is constantly at war, barraged by endless propaganda, and surveillance. Smith is nobody special but is a member of the party, unlike the proles who he begrundgingly seems to admire somewhat.

If anything, it's quite depressing in a way, that humanity has learned so little since this was written in the late 1940's. Obviously, he'd just come through the second world war period, and I can't imagine how bleak that would have been. I'm sure the Nazi regime would have heavily influenced his thinking.

It's fascinating to see how Smith thinks he's a real rebel, yet the party is even more clever all the time.

The quality of the writing is amazing and it's clear to me why this is regarded as such a classic. Apparently, he had tuberculosis while writing most of this book.

Bottom line: If you've never read this or listened to it, it's time to do so.

Greg's rating: 10 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 🙂