The Bit Bucket

In part 1 of this series of posts, I talked about pseudo locking and blocking issues that come from having queries running too long. I said that before you start fixing any locking and blocking issues, that you first need to make sure your queries are running fast. Otherwise, they're blocking other queries for no reason.

Lock escalation issues

Some queries, however, just need to run for a long time.

Most of the time, I find this with reporting queries. They're just doing a lot of work.

The problem is that even in the default isolation mode (read committed), SQL Server takes shared locks. When it gets to 5000 of these locks, it tries to escalate to a table lock. (Strictly, it might be a partition lock but that's a topic for another day)

And that's where the fun begins. Many people have had the experience of working with large transaction tables; someone's running a report down the end of the table and someone else is trying to modify the beginning of the table and gets blocked. Suddenly, you can't even insert current data.

No the reaction that people had to this in SQL Server 2000 days, was to just put NOLOCK on the queries. But that's not a great solution. You are basically giving up consistency for performance. It can lead to scenarios that are incorrect, and hard to explain.

Ever since SQL Server 2005, so many of these issues would be better fixed by using snapshot isolation level. Changing that in a session means changing the application, so for many customers, that's not helpful.

One possible solution

Instead of changing the application to use snapshot isolation level, SQL Server provides a database option called read-committed snapshot isolation (RSCI) that changes any read-committed queries into read-committed-snapshot queries i.e. it automagically applies snapshot isolation to read-committed queries, but just for the duration of the queries.

I've lost count of how many sites I've been to where just applying that database option fixed an enormous number of blocking issues.

What it means is that when you go to read data, and someone else is modifying it in a transaction, you see the data as it was before the transaction started, instead of waiting. Generally, that's awesome.

So why isn't it already on?

If it's such a great option, you might be wondering why it's not already turned on. Basically, that's for backwards compatibility. Some applications (I've seen very few of these) depend upon the old behavior. I'd suggest that these would be pretty badly written applications.

When we start new database applications today, we turn on RCSI for the database before doing anything else.

The other reason that it's not a default value is that it is going to slightly increase the impact on tempdb. Generally, we've not found that to be an issue.

In Part 3 of this series of posts, we'll move on to trickier situations.

 

When I'm writing utility scripts for SQL Server, I often need to make decisions or report on, details of the operating system that I'm running on. Unfortunately, SQL Server doesn't have views that return this sort of information.

In our free SDU Tools for developers and DBAs, we added a series of views to help.

OperatingSystemConfiguration has details of OperatingSystemRelease, OperatingSystemName, ServicePackLevel, OperatingSystemSKU, OperatingSystemSKUName, and LanguageName.

OperatingSystemVersions has details of OS_Family, OS_Version, and OS_Name.

Ever wondered what locale 1033 means? OperatingSystemLocales has details of OS_Family, LocaleID, and LanguageName.

OperatingSystemSKUs has details of OS_Family, SKU, and SKU_Name.

We periodically check the list of these within Windows and keep it up to date.

You can see two of the views in the image above and also see the views in this video:

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

http://sdutools.sqldownunder.com

 

We all see news articles talking about how company after company exposed private details of their clients, customers, and sometimes, even staff. In so many cases, a reasonable approach to encryption would have avoided these issues. Time and again though, I see people doing dodgy work on protecting their data; some even trying to roll their own protection.

Don't do this!

Encryption is one of the most important yet least understood technologies in SQL Server. It's improved so much over the years and 2019 makes it even better.

If you are a DBA or a database developer and you don't know your way around the encryption options in SQL Server, it's time to learn.

We've now made it easy. Take our new online and on-demand course: Protecting SQL Server Data with Encryption.

In no time, you'll be discussing and implementing encryption like a pro. You'll get a refresher on encryption terminology and concepts, then drill in keys, certificates, cell-level encryption, Transparent Database Encryption (TDE), extensible key management (EKM), and Always Encrypted (in 2016) and with secure enclaves (in SQL Server 2019).

And if you get into it by May 17th, you'll be able to use coupon code ENCRYPTNOW to get another 25% off the brand new course.

And like all our courses, we have video instruction, hands-on-labs with downloadable resources, quizzes to check your learning, and a certificate at on completion.

You'll find it, and our other courses here:

https://training.sqldownunder.com

 

 

 

If you look carefully at the following simple query:

you'll notice that I didn't just say FROM Cinemas, I said FROM dbo.Cinemas. The "dbo." part is the name of the schema. I talked about schemas in an earlier post. And all through these T-SQL 101 blog posts, I keep mentioning that you should always use the schema name as well as the object name.

By why?

The first reason is that whenever you write T-SQL scripts of any type, you need to be precise. When you say just Cinemas, SQL Server has to work out which Cinemas table you mean.

Now you might think that you only have one. But SQL Server doesn't know that when it starts to run your query, and it has to go to look to see if there are others.

Every user has a Default Schema associated with them. When you just say Cinemas, SQL Server first looks to see if there is a Cinemas table (or view) in your default schema. If it doesn't find one, it then looks in the dbo schema. That's why it seems to find it just fine when you simply say Cinemas.

Small as it is, why have it doing this extra work?

A worse situation is if you're writing a query to create the table. If you just say Cinemas, the table would be created in the default schema of the person running the script, not necessarily in dbo. That's not reliable query writing.

Stored Procedures and other objects too

Exactly the same issues relate to stored procedures. If you say:

EXEC SomeStoredProcedure;

How does SQL Server know if that's dbo.SomeStoredProcedure or Sales.SomeStoredProcedure ? Sales might be your default schema.

And the same issues arise when you create stored procedures. You want to write scripts that reliably create them where you want them.

But wait, there's more

A further complication is around query plan caching. If SQL Server caches a query plan for accessing the dbo.Cinemas table (once it's found it), and you run a query that's for just the Cinemas table, it doesn't already know if it can use the same query plan, until it works out which object you're talking about.

In many places in the T-SQL documentation, you'll find you're advised to use two-part object names. They're not joking. For these and other reasons, please just get used to doing this.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

According to Wikipedia, Betteridge's law of headlines is an adage that states: "Any headline that ends in a question mark can be answered by the word no." In this case, they're correct because the answer is no. But as with most things in this industry, there are subtle issues with that answer.

In SQL Server, a view is just a SELECT statement that's been given a name. That's pretty much it. No data is stored, and every time you access the view, the query optimizer expands the view into the surrounding query. By the time you get to an execution plan, the view is nowhere to be found.

Let's see an example:

The WideWorldImporters database has a view called Website.Customers. I've selected from it and then asked for an estimated execution plan. If you look all through the plan below and even search the XML for the plan, you'll find that the name Website.Customers isn't part of the plan. That's because SQL Server takes the T-SQL code from the view and pushes it back (i.e. inlines it) into the surrounding query, which is then optimized.

So what are indexed views?

An indexed view is a curious beast. It's created by adding a clustered index to the view. That makes it actually store the data. There are lots of rules and limitations surrounding indexed views, and we'll talk about them another day, but the important concept for now is that they hold the data, it's updated automatically as the underlying data changes, and the view itself can supply the data in a query.

In the AdventureWorks database, there is a view called Person.vStateProvinceCountryRegion. Note that it has an index:

Indexed Views and Query Plans

Note the following query plan:

You can see that the query retrieved the data from the view. It did not expand the view. If we'd like it expanded, we can request that:

So what about Enterprise Edition?

The query optimizer considers indexed views in Enterprise Edition, Evaluation Edition, and Developer Edition. In other editions, the views are expanded before optimization. So there is a perception that Standard Edition and Express Edition cannot use indexed views.

However, if you always specify a NOEXPAND hint in your queries, you can use indexed views just the same in both Standard Edition and Express Edition.

And it doesn't cause any issue on any edition. It still works as expected on Enterprise Edition too.

I get lots of requests to help customers with their SQL Server systems. Many, many requests start with customers asking me if I can help them fix their blocking issues. Most customers aren't actually aware of true blocking issues. Invariably what they mean, is that they've started to have deadlocks and things are blowing up.

Is blocking really the issue?

I always start by working out if they really have a blocking issue. When I see performance problems, after eliminating obvious hardware and configuration issues, I'd say the issues fall into these buckets:

• Application Design 50%
• Indexing 35%
• Blocking/concurrency 10%
• Other tricky stuff 5%

While wait statistics and other intricate details are interesting and geeky, don't mess your head with them at this point. The number 1 cause of blocking issues of any type (including deadlocks), is actually query performance. If your queries are running much longer than they should be, you have far more chance of them blocking each other.

So often, once I fix the query performance issues, all the blocking problems have vanished.

Fixing the application design issues is hard, and often political, but fixing the indexing issues is usually a lot easier.

To fix the query performance issues caused by indexing, you can do the following:

• Find out which queries are causing the problems (it's not worth wasting your time on the others)
• Fix the obvious indexing issues

If you don't know how to do that, I have a free course (4 Steps to Faster SQL Server Applications) that walks you through an effective process. It's online, and on-demand so you can do it right now. It'll only take a few hours.

You'll find it here:

https://training.sqldownunder.com/p/4-steps-to-faster-sql-server-applications

That covers the really obvious issues. For trickier indexing issues, we have another course (SQL Server Indexing for Developers). It's not free but it's great value with helping you understand SQL Server indexing. You'll find it here:

https://training.sqldownunder.com/p/sql-server-indexing-for-developers

In Part 2 of this series of posts, I'll assume you've already fixed these issues and your queries aren't running slow.

 

I can't tell you over the years how many times I've gone searching for details of a SQL Server build and/or related knowlegebase article. There are some sites that provide much of this, but I'd like to have it in a programmatic form. I can easily then use it in utilities and in my SQL scripts.

In our free SDU Tools for developers and DBAs, we added a view and a number of functions to help with this. The primary one is a view called SQLServerProductVersions.

We constantly keep this up to date as each CU (cumulative update) is released, and we've started adding in all the related knowledgebase articles for each build.

It's shipped as a view that returns the following columns: SQL Server Version, Base Level, Patch Level, Major Version Number, Minor Version Number, Build Number, Release Date, Core KB Article Name, Core KB Article URL, Additional KB Articles.

You can see the view in the image above and also see the view and a number of related functions, in this video:

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

http://sdutools.sqldownunder.com

 

I'd love to be running Service Broker classes more often. Service Broker is one of the most powerful, yet least understood technologies in SQL Server. Many of our enterprise clients are using it and loving it.

What we tend to find though, is that we get requests from a number of interested people, spread over time, and locations. But not enough to run in-person classes in those locations.

So we decided to fix that, for this course and for a number of our other courses that have similar demand (i.e. Spatial, Replication, and more).

Our brand new SQL Server Service Broker for Developers and DBAs course is now out the door.

Now you can learn about it whenever you want.

If you've been wondering if you should look into Service Broker or aren't even sure what it is, now's a great time to find out. Coupon SBROKERINTRO will get you 25% off the brand new course until the end of the month.

And like all our courses, we have video instruction, hands-on-labs with downloadable resources, quizzes to check your learning, and a certificate at on completion.

You'll find it, and our other courses here:

https://training.sqldownunder.com

 

 

 

If you look carefully at the following two queries in the image below:

you'll notice there are a couple of differences. In the first query, the word Description is blue, and in the second, it isn't blue but it has square brackets around it. The second is an example of quoting or delimiting an identifier.

In this case, the reason that the word was blue in the first query is that somewhere in SQL Server, this word is part of the syntax of the language itself. That's much the same as SELECT or ORDER BY. So SQL Server Management Studio (SSMS) color-codes it the same way it does for the word SELECT.

This query would work fine without the quoting, but if I tried to have a column called ORDER or FROM, SQL Server would complain. I'd suggest that doing that is often a pretty lousy idea anyway. But if I did really want to do it, surrounding the name with square brackets would allow me to do it.

Now it's not just reserved words where this applies. If I have an object name with spaces in the middle of it, I have to quote the name. For example [Ordered By] could be a column name. Generally I try to avoid spaced in column and other object names.

There are always exceptions

There are two exceptions to this though:

In dimensional data warehouses, I try to have non-key (i.e. displayable) columns human readable, and that means they'll often have spaces in them. I don't want to be renaming them before they're added to every report, spreadsheet, Power BI dataset, analytic data model and so on.

The other situation is that if I'm using Service Broker, it's pretty common to use names that look like URLs for the names of message types, and contracts. But that's an advanced topic that we'll have to talk about another day.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.