Opinion: Treat Staff like Adults

There's a nasty trend that I've seen at a number of sites in recent years. It's the tendency to try to block and or censor anything that the company thinks might be an issue. Some companies are so concerned about their IP (intellectual property) that they even try to stop any potential leak of that property.

While on the surface, that all might seem to make sense, it's not sensible. It's unproductive.

  • When staff can't look up the syntax of things like BULK INSERT because the system flags it as a shopping site (which is forbidden), you've stopped people working effectively.
  • When staff can't look up how to work efficiently with threads because the discussion they want to read happens to be about threading in games (and gaming sites are blocked), you've stopped people working effectively.
  • When staff can't look at content from a presentation because it's shared on a site like SlideShare (and sharing sites are blocked), you've stopped people working effectively.
  • When staff systems work at one third of the normal speed because of all the "protective" code that you've added (I have a personal dislike for the impact of tools like CyberArk), you've stopped people working effectively.
  • When staff can't download a presentation or code sample because it's on a Google Drive, Azure Storage Account, etc. (because they could be used for sharing files), you've stopped people working effectively.

And so on, and so on.

Automated censorship has never worked effectively yet. (Machine Learning and AI might change that).  And this is no different.

Rather than try to guess and block all the things you fear staff might look at or use, please consider treating them as adults. A better set of rules for technical staff like developers is:

  • We recognize that you are a professional staff member.
  • We don't block what you do.
  • We will, however, monitor all your usage.
  • You might be required to justify your usage.
  • We've detailed the types of things that are never allowed (that don't fit with the company ethos or the law) and some do attract significant penalties.

These auto-censoring and blocking tools that try to keep you safe end up just causing angst and frustration, don't work very effectively, and from what I've seen, almost always end up with staff finding ways to circumvent them.

Worst of all, treating the staff like children won't make them love your company. Neither will anything that keeps blocking them from doing their jobs.

SQL: Best Way to Scale SQL Server Database Performance

I see so much written about how to scale SQL Server systems, and this generally starts with needing to improve SQL Server database performance. When I read articles from the SQL Server field support teams with titles like Top 10 Performance Problems for SQL Server, I often just smile.

The problem is one of perspective. If you are looking at the performance problems that are brought to the support teams to solve, you get a very, very skewed view of what's typical.

That's because most performance problems are dealt with by database developers, DBAs, application developers, etc. long before they'd go anywhere near product support. So the product support team never sees the most common problems.

If I had to rate performance problems by cause, I'd suggest the following:

  • 40% of issues are related to poor application design
  • 25% of issues are related to poor indexing
  • 20% of issues are related to how the database framework that the application has used talks to the database (either by design or by how it's been configured)
  • 10% of issues are related to poor concurrency design (blocking issues)
  • The remaining 5% are odd things, and generally the ones that go to product support.

But if I had to provide my #1 tip for improving database performance and increasing scale, it's this:

Stop talking to the database!

It's that simple. So many applications that I work with are very, very chatty.

A year back, I stepped through the login process of a web application. A single login ended up causing over 400 calls to the database. That should probably be one or two.

I've seen processes that send 55 million calls to the database and run in half an hour. But the same process runs in 2 minutes when the number of database calls is cut to 4000.

I've seen windows apps that execute 90,000 remote procedure calls before the first screen comes up. (Loading 90,000 customers one row by agonizing row at a time). It's a tribute to SQL Server that the application started in about 30 seconds, but that should have been one call, not 90,000.

I've recently seen frameworks that have been misconfigured. For every select of a single row of data, the framework executed this:

  • Create a cursor for the command
  • Open the cursor
  • Fetch from the cursor
  • Fetch again from the cursor (this one failed)
  • Close the cursor
  • Re-open the cursor
  • Query for the parameter metadata
  • Close the cursor

And that was done every single time that a single SELECT should have been sent. The problem is that the developers using frameworks like this are blissfully aware of the commands being generated for them in the background.

I've seen caching errors where an application ran the same query with the same parameters over 12,000 times per minute. The developers were unaware of this. (They had a typo in the caching code but it "looked like it worked").

The bottom line is that you need to spend time looking at how your applications interact with the database. If you execute 400 commands every time someone logs on, you will never get any real scale.

I'll say it again. The best way to scale a SQL Server database (and to get better performance from it) is to stop talking to it incessantly.

 

DevOps: Microsoft Professional Program for DevOps

In the second half of 2016, I enrolled in the Microsoft Professional Program for Data Science, and completed it in early 2017. I have to say that I really enjoyed it overall. It was a bit challenging at times but I don't regret doing it.

If you want to get the certification, you need to enroll in the verified option for each course. Nowadays, that's pretty much $99 USD per course. You can do it for free, and if you're tight on funds, perhaps that's what you should do. I like to support the concept, and like to support both Microsoft and edX for creating these options. They are doing amazing work, so while I hear people say to just do the courses and not contribute to them, I can't say that I agree.

edX and their partners offer an incredible range of world-class courses that you can take for free, but if you want them to continue, you should consider contributing. And that applies to the non-Microsoft ones too.

I think that programs like these are more likely to be the real future for Microsoft certification in general.

Earlier this year, Microsoft created a Professional Program for DevOps. I've had an interest in DevOps for a long time, and I got the opportunity to help create one of the courses DevOps for Databases with the inimitable Steve Jones from Redgate Software. Databases are a specifically-challenging area for DevOps.

A few months back I decided to start pursuing this professional program as well. I've got one course to go (the container one) before the final capstone project. I can finish that container course in the next three months, but unfortunately the capstone project won't be available until April.

Here's the overall program:

Over the last few weeks, I've been involved in enhancing the existing Monitoring and Testing courses, and am looking forward to seeing how people find the updated versions.

To support my continuing interest in DevOps, in the upcoming weeks, you'll see DevOps-related posts from me.

 

Shortcut: Creating T-SQL Templates in SQL Server Management Studio (SSMS)

A few weeks back, I mentioned how useful templates can be. I said that I'd discuss how to create them later. Well that's today.

I thought I'd take dropping a database as an example. The supplied template doesn't work for me, so let's create a new one.

Note: SQL Server 2016 SP1 introduced DROP DATABASE IF EXISTS but I find that option quite useless. It fails if anyone is connected to the database. And to disconnect people beforehand, you need to first check if it exists, so the statement is pointless.

Let's start by opening the Template Explorer (from the View menu, click Template Explorer as it's not open by default).

Next we look for a DROP DATABASE option, and right-click it.

When we click Open, we see the supplied template:

Importantly, note the text here that's shown between the less than and greater than operators. This is an example of a parameter to the template.  Parameters look like:

<ParameterName, DataType, DefaultValue>

To create a new template, we could right-click the Database node in Template Explorer, and click New, then Template:

But to modify an existing one, it's easier to right-click the one we want to modify (ie: Drop Database), and click Copy. Then right-click where we want it (ie: the Database folder), and click Paste. That'll give us  a copy of the template as a starting point.

Rename the new template to Drop Database - GL, right-click it, and click Edit.

I've changed the template as follows:

First up, I've used semicolons as statement terminators. We've been asked to use them since SQL Server 2005 so I have no idea why the existing templates (and much sample code from the SQL Server team) doesn't include them.

I've removed the comment headings. Otherwise, I'd need to do that every time. I've quoted the [name] column as I like to see the column names colorized properly, and this name would otherwise be blue. I've also aliased the table.

Next, to try to get around dropping a database with people already connected, I've altered it to single user, with an option to kick off all but a single user.

From the File menu, I click Save, then close the template window.

Now let's try it. From Template Explorer, I right-click Drop Database - GL, and click Open, and my new template appears. Then, on the toolbar, I click the option to let me enter the parameters:

I enter the parameter value:

After I click OK, my template is ready for use:

The parameter value has been replaced wherever it had been used.

As a side note, this is still not 100% bulletproof. Note that the command is executed in the master database, and so there is no guarantee that the single user will be you. You could use RESTRICTED_USER but again, that might have the same issue. What is really needed is this:

DROP DATABASE IF EXISTS GregLowTemp WITH ROLLBACK IMMEDIATE;

But sadly, that command doesn't exist. There really should be a way to reliably drop a database by standard T-SQL. Many people need to write drop and recreate scripts.

Finally, I'll now point out that this code would be better as a snippet than as a template. Templates work well when they provide an entire script. This code is likely to be used as part of another script. In a later post, I'll show you how to make this a snippet instead.

 

 

 

 

 

 

 

 

 

 

SDU Tools: Determining Leap Years in T-SQL with IsLeapYear

Is 2018 be a leap year?

Was 2000 a leap year?

Will 2100 be a leap year?

When I was a young student at school, we learned that leap years were every four years. Then as I got older, I learned that there was more to it than that.

It's important to understand that any calendar is just an approximation, and there is a lot more to calendars than most people realize.

For those that would really like to get some background, my favorite calendar-related site is Claus Tøndering's Calendar FAQ. It's a wonderful collection of details of calendars and their history. In addition, for those that are into coding, he has sample program code to determine things like when is Easter next year?

Currently the rule for determining leap years is:

  • Every fourth year
  • Except if the year is divisible by 100 but not by 400

So, to answer the questions above, 2018 is not a leap year, 2000 was a leap year, but 2100 is not a leap year. (It's the last one that usually surprises people).

Apparently the scientists are still discussing where or not there should be a 4000 year rule. Can't say that I care as I won't be here.

We added a simple tool to do these calculations:

You can see it in action here:

For more information on our free SDU Tools, please visit:

http://sqldownunder.com/sdu-tools

 

 

 

 

SQL: Auto Page Repair in SQL Server ? (Or Not?)

Database mirroring was added in SQL Server 2005. One of the features added to it in SQL Server 2008 was auto page repair. When SQL Server 2012 was released, Availability Groups also offered auto page repair.

Just how useful is this feature though?

I'll start by saying that it can't hurt.

When SQL Server is reading a page on the primary replica and receives an unrecoverable I/O error (typically but not always, an error 823 for a checksum error), it will try to repair the page when:

  • A secondary synchronous replica exists
  • The secondary replica is synchronized

It will try to recover a copy of the page from the secondary replica and rewrite it on the primary. This means that when you execute a query on the primary, and it returns an I/O error, that retrying the same query a short time later might actually work.

In the meantime the page is added to the suspect pages table in msdb, and if you try to query it while the auto page repair is being attempted, you'll get an error 829 that tells you the page is in recovery.

Did the page get repaired?

Maybe.

Let's start by saying that if a storage subsystem is returning I/O errors, it's likely that you have a problem that really needs fixing.

Generally, auto page repair is more like a bandage or a band-aid that's been applied.

However, storage subsystems are often now self-healing: they recognize that an I/O error occurred in one location, and automatically map that block to another underlying location. So when SQL Server then rewrites the original data, there is a chance that it will have actually fixed the problem.

It's also worth keeping mind, that as storage systems get larger and larger, we're starting to test the standard error rates of many underlying I/O devices. One unrecoverable error every so often is actually deemed acceptable. The proportion is really very, very low. But as we start moving larger and larger amounts of data around, we can start to hit these numbers.

I suspect that as storage gets even larger, we will start to be aware of these issues more and more.

What about the secondary replica?

The same logic applies. The secondary can attempt to retrieve a copy of the page from a synchronized primary replica. This could occur when the secondary replica is being read. In mirroring, this could have been if we had made the replica readable via database snapshots, and in Availability Groups, this could just be a readable secondary replica that hits an I/O error on its storage.

So is it a good thing?

Of course. It's a help, but it's important to understand that if pages are appearing in the suspect pages table in msdb, you have an underlying problem that needs to be resolved.

Opinion: If you can’t type, you are writing worse code than if you could

Let me make a potentially bold statement:

People who can’t type write worse code than they could be writing

I’m sure that will upset some people (probably those who can’t type or who are two or four finger typists) but it’s a conclusion that I’ve come to over many years. Coding is clearly not the same thing as typing but the reason is simple:

To write good code, you need to be prepared to constantly refactor and rework the code that you write, and if you can’t do that quickly, you’ll be more reluctant to do it.

Time and again, I’ve seen people hanging onto code that should simply have been reworked, and it’s often because doing so it seen as too hard.

It's never been easier to learn to type. There are so many applications that can help you to learn, and there are no doubt a large number of online sites to help you do that.

When I worked at a university, I was in charge of a large amount of equipment used by students. Every day, I saw students struggling to get their work done. Worse, they were stopping other students from using the same systems.

I always thought that in the first week of first year, one of the best things we could have asked the students to do, was to learn to type. There's nothing else very useful they could have done in that week, so they might as well have done something that would benefit their whole careers during that dead time. And it would have reduced the amount of equipment that the university needed to purchase.

Sadly, it was made clear to me that universities don't get involved in things like typing.

Any why am I raising this today? It’s the day that many people make New Year’s Resolutions.

If you write code for a living (application developer, database developer or whatever), you owe it to yourself to remove friction between yourself and the computer. Invest in yourself. It’s the professional thing to do.

Happy New Year to all my readers (whether or not you can type).

澳大利亚新年快乐!

SDU Tools: Converting T-SQL Strings to Snake Case and Kebab Case

In a recent blog post, I described converting strings to Proper Case and Title Case. And more recently, I described how to convert them to Pascal Case and Camel Case.

The final option (for now) in this set is conversion to Snake Case and Kebab Case.

In Snake Case, all words are lower-cased, then all spaces between words are replaced by underscores.

You can see it in this example:

Kebab Case is similar except that the first letter of each word is capitalized, and hyphens are used instead of underscores:

You can see them in action here:

For more information on our free SDU Tools, please visit:

http://sqldownunder.com/sdu-tools

 

 

 

 

Shortcut: Using Colors to Avoid Running Scripts Against the Wrong Server

Everyone who's worked with SQL Server for any length of time, has had the experience of executing a T-SQL script, and then noticing, with horror, that they've just executed the script against the wrong server.

You know the feeling. It even happens at Christmas time, just when you were hoping to get away from work for a few days, or when you are the unlucky one who's doing on call work.

Many of these surprises would be avoided if there was something that gave you a visual clue that you were connected to the wrong server.

SQL Server Management Studio (SSMS) has had an option to color code connections to servers for quite a while. The solution isn't perfect and isn't as good as Mladen Prajdić's SSMS Tools Pack:

http://www.ssmstoolspack.com/

(If you're keen to pay for an add-in, I do recommend that one)

For many people though, the colorizing provided by SSMS is just enough. And it's easy to use.

When you open a new database connection, you can click Options:

This opens a larger dialog that allows you to specify a color for the connection:

I've chosen Red to warn me that this is a production server.

Then, you'll see the color bar at the bottom of each script window in SSMS:

 

 

 

 

SDU Tools: Show SQL Server Backup Completion Estimates

When you first start working with SQL Server, you're often working with smaller databases and every backup seems to happen quickly. SQL Server is fast at creating backups.

When you get to larger databases, backups start to take a while. Fortunately, backup is one of the commands that sets a percentage complete value that can be seen in the sys.dm_exec_requests system view.

And when the databases get even larger, the question quickly becomes:

When will my backup finish?

The good news is that we've built a tool to make that easy to work out.

One of the SDU Tools is ShowBackupCompletionEstimates. It shows you any currently executing backups, how complete they are, when they started, how long they've taken so far (in seconds), and when we estimate that it will complete. Because it can cope with more than one backup running at a time, we also added the last SQL statement that was executed in the session, to make it easier to work out which is which.

Here's an example of its output (wrapped for clarity):

You can see it action here:

For more information on our free SDU Tools, please visit this site:

http://sqldownunder.com/sdu-tools