Opinion: Corporate Compliance Isn't Training

I spend a lot of time mentoring on client sites, and many of the clients are large organizations. Often these organizations require me to attend "training" on a regular basis, to satisfy their corporate compliance goals.

I don't mind doing this at all, even though the course on conflicts of interest, or handling private or sensitive data, at company A is invariably almost word for word the equivalent course that I do at company B, and company C.

The ones that I really don't like though, are the ones where the corporate IT security is spelled out like it's obvious, and yet I know that what they're pushing doesn't meet any of the current guidelines that have been created from serious research into the topics. For example, the NIST guidelines on passwords would be a good start.

Training should involve learning something.

The vast majority of staff at the organizations wouldn't learn anything from these "courses" and invariably, the questions that they need to get say 80% correct on, are so mind-numbingly obvious, that I see many staff not even paying attention when the videos are playing, and just quickly answering the questions at the end, to keep their managers happy.

But my biggest issue is that for many companies, almost all the corporate training budget is now going to these "courses". My take on this is that the cost of delivering this material should be in a "corporate compliance" budget, not in anything that pretends to be a "training" budget.



SQL: When Performance Tuning SQL Server, Fix the Problem, not the Symptom

I spend a lot of time working with teams of DBAs and developers who need to fix performance problems. One of the real issues that I see time and again though, is people fixing the symptom, not the problem.

Many DBAs spend their time tracing query activity, working out which queries the system is spending a lot of time on, and then working out how to make those important queries run faster.

Automated tuning systems like those being put into SQL Server currently achieve much the same outcome.

But I'd always contend that if that's all you can consider, you are often working on the wrong problem.

I was at a site some years back, where I found a single query that was loading a list of countries, but was being executed 12,000 times per minute and the system seemed constantly busy executing the query.

Some DBAs that I have worked with, would have just ignored the query. Each individual execution of the query took very few resources, and it wouldn't have made it through their filter of queries that need looking at, because it ran fast.

Other (better) DBAs might identify it as an important query because they aggregated the impacts of the queries. Even better if they aggregated the normalized version of the query. (See our free course on doing this if you aren't familiar with it). They would then spend their time working out if they could improve the performance of the query by indexing, looking at wait stats, reducing blocking, and other techniques.

This is all good stuff, but they're all still missing the real problem which is why is the application asking the same question 12,000 times a minute?

That's the actual problem that needs solving.

Awesome image by Annie Spratt
Awesome image by Annie Spratt

To really solve performance problems, you need to be prepared to step back, look at the bigger picture, and be prepared to go back into the application code with the developers. It's worth noting that many 3rd party application vendors won't be keen on you doing that, but it's the only way to actually solve the problems.

Now, in this case, it's pretty clear the list of countries isn't changing. (It's a few years since we got a new one in South Sudan).

Awesome image by Kyle Glenn
Awesome image by Kyle Glenn

So why on earth (pun intended) is the application constantly asking for this? Surely it should be caching that data.

And of course, when I checked with the development team, they had been caching the data. What went wrong though, is they had a typo in the caching code. Every time they checked the cache, they thought the values weren't there, so they reloaded them.

That was the real problem that needed to be solved. And no amount of fiddling with indexes, wait stats, locks, etc. was going to have a noticeable impact.


Learning Mandarin: Heating and Cooling Foods

A few days ago, my wife and I visited a local restaurant that we quite like. It's called 秋月亮 (Qiū yuèliàng) or Autumn Moon. The lady who runs it has been very friendly to us. Before our meal, she asked if we wanted anything to drink. My wife asked for water but I asked for water and some tea (green tea). I wanted some water first, then I was going to change to drinking tea.

She looked at me strangely and said "No".

Now that wouldn't happen in most other types of restaurant. "I won't sell you what you asked for off the menu" wouldn't go down well with most managers.

But she immediately looked across at my wife (who is of Chinese appearance) and she said "You know". She was implying that my wife would understand that it was obvious that I shouldn't have a cool drink and a hot drink together. She was worried how this would affect me. It might upset my stomach.

This concept is pretty ingrained in Chinese culture, but generally they aren't talking about things that are actually "hot" and "cold", they are talking about foods that they consider "heating" and those that they consider "cooling".

This comes from teachings on warm and cool foods from traditional Chinese medicine. Ancient Chinese decided that a lot of chronic ailments that our bodies suffer is caused by an imbalance in our internal temperature and energy. They believe this affects our organs.

A whole range of foods is considered to be "warm". Generally they are foods that improve circulation and nourish our energy levels. This is everything from butter, chicken, and prawns, to walnuts and glutenous rice.

Cool foods are considered to "calm" the blood, clears toxins and reduce heat. This would include cheese, apples, bananas, and salt.

There are also a group of foods that are considered "neutral" like beef, milk, figs, and grapes.

Basically from birth, many Chinese are taught what increases or decreases "heat" in their bodies.


Basic Photo Viewer in Windows 10 – Where have you been?

I teach SQL Server, BI, Azure, and AI classes on a fairly regular basis, and one thing I love to do is to show attendees images (or photos) of where the application of the technology has gone very right or very wrong. Ever since I'd installed Windows 10 though, that became much harder.

The Photos app that's installed with Windows 10 must have someone who loves it, but that's not me. There seems to be no way to just have it automatically maximize the images, so I'm always showing them, then having to resize them.

What I wanted is an app where I can double-click an image, and it would display it for me, maximized to whatever screen real estate I had available. The old Windows Photo Viewer did that to a reasonable extend, but the new Photos app in Windows 10 just seemed to have no way to do that simple task. I've seen a lot of articles by people desperate to reinstall the old Windows Photo Viewer. Microsoft has been making that harder and harder and even though you can get it working, it doesn't integrate with other things very well.

So the other day, I figured I'd just have to write an app that did what I wanted but thought I'd check the Windows Store first, and I'm glad I did. There's an awesome app called Basic Photo Viewer, and for those that hate paying for apps, even better, it's free. It has an option to upgrade to Pro but there's nothing in that for me at present.

It worked so well, I was wondering where it had been all my life. Well at least since Windows 10.

I set it as my default program for viewing images in Windows, and when I double-click an image file now, I see this:

There's a nice clean image. If I click the mouse up near the top of the screen, the menu bar appears. The top left has this:



And the top right has this:

There were a few settings that I changed. I chose to hide all pro features (or oddly it lets you set things you can't use), and I chose to have a clean screen by using this option:

The app can also do things like display a slide show by showing all files in a particular folder with a configurable delay, etc. but for me, I just wanted a simple program that displays an image as well as it can, when I double-click the image.

I can't believe I put up with the other app for so long, and didn't go looking for a better one.

Highly recommended !






Shortcut: Set query shortcuts for favorite stored procedures in SSMS

In an earlier post, I mentioned how useful the F1 key is. On its own, it provides syntax help, but when you highlight an object and hit Alt-F1, you get to see metadata about the object.

Under the covers, this just runs the sp_help stored procedure. Alt-F1 has been mapped to that.

You can see where this is configured, change it if required, and/or configure other procedures as well.

In Tools, Options, Environment, Keyboard, then Query Shortcuts, you can see this:

Here we can see how the Alt-F1 command is configured. By default, only three shortcuts are configured. It's easy to configure another one for your own use.

SDU Tools: List Indexes in a SQL Server Database

When I'm working with a SQL Server database, I often need a simple list of all the indexes that are already in place. I want it in a concise form. In our free SDU Tools for developers and DBAs,  we added a procedure ListIndexes 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
@SchemasToList nvarchar(max) – a comma-delimited list of schemas to include (ie: 'Sales,Purchasing') or the word 'ALL'
@TablesToList nvarchar(max)- a comma-delimited list of tables to include (ie: 'Customers,Orders') or the word 'ALL'

One row is returned for each index.

The columns returned are SchemaName, TableName, IndexName, KeyColumnList, and IncludedColumnList as shown below:

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: 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 🙂