SQL: Is indexing bit columns useful in SQL Server?

One data type that's often been quite controversial is the bit data type. So a bit is where we have an individual value, which is either a zero or one. They're the only values allowed unless it's NULL.

Always keep in mind when you're working with bits in SQL server that they're aren't just 2 potential states, there are three. It can be zero, or one, or it can be NULL. That is, it can have no value at all.

A common misconception

Bit values can be indexed and contrary to many misconceptions that have been around over the years, indexing bits can be very, very useful.

Now I used to see a lot of Microsoft material, that said there is absolutely no point indexing bit columns because they can only have two values. But the issue is not the number of values that a column can have, the issue is how selective the value is.

Why did they think that?

Let me explain the thinking that led to this. Imagine I had a table that's holding the people who are attending a conference and we have an indication of whether or not they've previously attended the same conference. Let's say that 50% of them have been to the conference before. Now having an index on that column probably isn't going to be very useful, as that's not at all selective.

To be selective, you need something that's quite tiny in terms of the number of rows returned compared to the total number of rows in the table.

But let's consider a different situation. If I have a transaction table and it contains billions of transactions, 500 of which are unfinalized. I could have a column in the table called IsFinalized that is a bit data type. (My purist friends would argue that's not normalized properly but run with me on this for now). I would never use the index on that column to find all the ones that are finalized, because that's most of the table.

However, if I ever went looking for the unfinalized transactions, I most certainly would hope there's an index there to let me find those 500 unfinalized ones.

It would also make a good candidate for a filtered index, but that's a topic for another day.

Why is the idea so common?

The idea that indexing bits isn't useful started back in early versions of SQL Server. In SQL Server 7, you couldn't build an index on a bit column. In SQL Server 2000, you could do it via T-SQL but the tools like Enterprise Manager didn't give you the option. Even the SQL Server training materials from Microsoft said it wasn't useful.

So people got the idea that it wasn't useful. But that was never true.

Indexing bit columns can be very, very powerful where the data is selective.

Learn about indexing

Want to learn more about indexing? Take our online on-demand course now:
https://training.sqldownunder.com/p/sql-server-indexing-for-developers

 

Learning Mandarin: Chinese is a tonal language – what does that mean?

One of the challenges for newcomers learning any Chinese dialect (including Mandarin), is that the languages are tonal languages. In English, we tend to use tone for emphasis but it's not really used to change to a different word. 

For example, if I said "he really wants to go", if I draw out and more heavily the word "really", I can make it sound emphatic rather than casual.

The exceptions to this would be homophones, where we have words that are spelled the same but entirely different meanings. A simple example would be "lead" where you can "lead a horse to water" but also "go down like a lead balloon".

In Chinese, words that have the same Pīnyīn letters but different tones, are usually (but not always) entirely different words. A common example is "ma", which depending upon tones can mean a horse, a mother, a question mark, and many more things. Even when the tones are the same, it can mean many different things, and you need context to make sense of it.

Because of this, what I found difficult at first, is talking to Chinese people. The problem is that if they hear "ma" with the first tone, and "ma" with the third tone, they don't hear a badly pronounced word, they hear an entirely different word. There's no mental connection between the two.

In English, that's not so much of an issue. For example, if I said "there is lead in the petrol" but pronounced "lead" like the one in "lead a horse to water", an English speaker can still understand you, and will just think you have a lousy accent. We have a mental connection between those two words.

I find that Chinese speakers do not have this mental connection. It's probably because we relate the two different words to the same spelling, whereas "ma" and "ma" are often entirely different characters in Hanzi (or Chinese characters):

It's only in Pīnyīn that the letters seem related. To a speaker, they aren't related at all. Only words with the same letters and tones seem related this way.

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.

Shortcut: Saving and sharing SQL Server deadlock graphs

In an earlier post, I described how query plans could be saved as .sqlplan file, shared, and loaded again in SQL Server Management Studio (SSMS). It's also possible to extract them out of SQL Server Profiler or Extended Events Profiler.

This is useful, but the same applies to deadlock graphs. SQL Server 2005 added Deadlock graph as a type of event in SQL Server Profiler. (It's also part of Extended Events Profiler).

If I open a new trace in Profiler, I can add Deadlock graph to the list of events:

Then when a deadlock occurs, we can see an entry for it in the trace:

Now if you want to share that with someone else, you can right-click the deadlock graph event and extract the event data:

When you do this for a deadlock, the output is saved as a .xdl file extension.

You can then send it to someone else and they can load it in SSMS:

One important thing to note is that a deadlock graph is actually a list of deadlocks, not just one deadlock. When a deadlock graph is opened in SSMS, it only shows the first deadlock in the graph.

SDU Tools: List User Table and Index Sizes in SQL Server

I'm often wondering about how large different objects in my databases are. There are reports to provide that info, but I often want it in a programmatic form.  One of our free SDU Tools for developers and DBAs does just that. It's ListUserTableAndIndexSizes.

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) -> 'ALL' or comma-delimited list of schemas to list
@TablesToList nvarchar(max) -> 'ALL' or comma-delimited list of tables to list
@ExcludeEmptyIndexes bit -> 0 for list all, 1 for don't list empty objects
@ExcludeTableStructure bit -> 0 for list all, 1 for don't list base table (clustered index or heap)
@IsOutputOrderedBySize bit -> 0 for alphabetical, 1 for size descending

One row is returned for each user table or index.

The columns returned are SchemaName, TableName, IndexName, TotalRows, TotalReservedMB, TotalUsedMB, TotalFreeMB.

You can see it in action here:

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

http://sdutools.sqldownunder.com

Learn about indexing

Want to learn more about indexes? Take our online on-demand course now:
https://training.sqldownunder.com/p/sql-server-indexing-for-developers

 

SQL: Developers – Please add an Application Name to your SQL Server connection strings !

A pet dislike of mine is application developers creating connection strings, but without Application Names. That's painful because when  you view the processes in SQL Server Activity Monitor, or look at commands in traces from SQL Server Profiler or Extended Events Profiler, you see this:

If your connection just says .Net SqlClient Data Provider, no-one has any idea what application that is. If you are using another connection library, it might say a different name but the concept is the same. You are making management of the server much harder than it needs to be.

So what I want you to do instead, is to just specify Application Name=GregsAwesomeApp in your connection string, so that we see this:

Way better! Now we know which applications are associated with which connections. In that screenshot, you can also see that Microsoft has also been better lately in making sure all their applications have names.

OK, so now you say "what if someone renames the application?" To get around that, if you're a real star, consider dynamically retrieving the name of the running application, and adding that to the connection string as the application name. Either way, please set a meaningful value there to make it easier for the people managing the database server.

You'll also be way better off when you need to filter Profiler or Extended Events Profiler traces.

 

DevOps: Are you centralizing your log files?

System configurations are becoming more complex all the time. Each and every server, container, and key application and service today has log files that tell you a wealth about what's going on under the covers. But how accessible are those log files in your organization?

If you aren't using a log management tool, you probably should be.

Here are a few easy ones to get started with:

Azure Monitor

One thing that I do find frustrating with Microsoft tooling at present is the constant churn of product names. A while back, we had Application Insights that could collect details of what was happening within an app. The data for that was stored in a tool called Log Analytics, and it could also collect operating system logs and more. Agents were provided for on-premises systems.

Originally, these tools had different query languages but eventually, the query language for Log Analytics was one that's used. It's awesome to be able to write a query to simply find and filter log details.

For my SQL Server buddies, there were SQL Insights which has now morphed into SQL Server Intelligent Insights along with Azure SQL Database Intelligent Insights. These allow you to capture bunches of info about your SQL Server instances and databases so very simply.

I constantly visit client sites where they have purchased tools for this, and those tools aren't even close to being as useful as these Azure ones. And they don't just work with Microsoft servers and services.

Anyway, these have now all been bundled up again under the name Azure Monitor.

Azure Monitor also offers built-in integration with popular DevOps, issue management, ITSM and SIEM tools. You can use packaged solutions for monitoring specialised workloads, or build your own custom integration using Azure Monitor REST APIs and webhooks.

Papertrail

Another interesting offering from our friends at SolarWinds, is Papertrail. Their claim is "Frustration-free log management. Get started in seconds.
Instantly manage logs from 2 servers… or 2,000". Papertrail seems to be gaining a stronghold in the Linux, MySQL, Ruby, Apache, Tomcat areas along with many others.

In the end, if you aren't using one of these types of tools, you probably should be.

 

Shortcut: Sharing query plans in SQL Server Management Studio

Currently, SQL Server query plans are stored as XML. You can see what they look like by right-clicking in any query plan in SQL Server Management Studio (SSMS), and clicking Show Execution Plan XML:

That will return a whole bunch of XML like this:

It's important to understand that when SSMS is showing a graphical execution plan, it's just graphically rendering some XML like the plan above.

The Properties window in SSMS is also showing details extracted from that same XML.

Prior to SQL Server 2005, it was very difficult to share a graphical query plan with anyone else. You'd have to pop up the details of each operator one by one and take screenshots. Nasty.

Since SQL Server 2005, query plans can be stored as XML files, with a .sqlplan file extension. If you save a plan, that's the default file extension. And because SSMS is set in Windows as being associated with this file type, you can just open a .sqlplan file and see the full graphical plan in SSMS, including having all the popups working.

Podcast: SQL Down Under Show 74 with guest Argenis Fernandez

Hi Folks, whenever a new version of SQL Server is starting to appear, I try to have a series of podcasts that discuss what's happening with it. Now, SQL Server 2019 isn't that far off so I thought it's time to start discussing the features in it, in depth.

First up, it's Argenis Fernandez (@DBArgenis) from the SQL Server team. Argenis provides an awesome discussion on the use of non-volatile memory systems and how he sees them as a game changer.

You'll find the show (and previous shows) here: https://sqldownunder.com/pages/sql-down-under-podcast

And if you've subscribed to the SQL Down Under podcast in iTunes or your favorite podcast app, it should now already be winging its way to you.

Enjoy !

SDU Tools: List Unused Indexes in a SQL Server Database

SQL Server databases are often littered with indexes that aren't needed. In many cases, people won't even remember why they were created in the first place. So I'm often checking out indexes that might be candidates for removal. One of our free SDU Tools for developers and DBAs does just that. It's ListUnusedIndexes.

You can see how to execute it in the main image above. The procedure takes just one parameter:

@DatabaseName sysname – This is the database to process

One row is returned for each unused index, since the last time that the SQL Server instance was restarted.

The columns returned are SchemaName, TableName, IndexName, IsUnique.

You need to be careful about ever removing unique indexes, so this procedure tells you if they are unique or not. Note that you also shouldn't be removing indexes that are from 3rd party applications. Doing so might break your vendor support agreements. But this procedure will find you indexes that might be considered for removal.

You can see it in action here:

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

http://sdutools.sqldownunder.com

Learn about indexing

Want to learn more about indexes? Take our online on-demand course now:  https://training.sqldownunder.com/p/sql-server-indexing-for-developers

 

Learning Mandarin – Nouns, Verbs, Adjectives, and Adverbs

In Mandarin, the word for word is  (Cí). It's pronounced somewhat like "tser". An example of its use is in the word 词典  (Cídiǎn) which is the word for a dictionary.

Individual characters are called   (Zì) but that can also be used for a word. Mandarin characters would be 汉字  (Hànzì) where 汉  (Hàn) represents the Han people ie: most of the Chinese.

Most of the parts of English speech have fairly direct equivalents. A Noun is called 名词  (Míngcí) which pretty much means "name word".

A verb is called 动词  (Dòngcí) which is close to meaning a "move word" or perhaps a "movement word".

An adjective is called 形容词  (Xíngróngcí) which is a little more complex but basically a "describe word". on its own is a shape.

An adverb is called 副词  (Fùcí) which is close to a "assistant word", a "secondary word", or an "auxiliary word".  on its own is means secondary, auxiliary, deputy, assistant, etc.

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.