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:

Learn about indexing

Want to learn more about indexes? Take our online on-demand course now:


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.


Learning Mandarin: What is pinyin?

I've mentioned before that the Chinese language uses tens of thousands of characters. It's the same for either simplified or traditional Chinese. That can make it hard for someone learning the language, as there seems to be an endless list of characters to learn. You also might not have thought about it, but how do you type all those characters on a standard computer keyboard? The answer to both those questions is Pīnyīn (拼音). For people who are familiar with western character sets,  Pīnyīn provides a way to represent the Chinese words with familiar characters.  This makes it easier to type them on a keyboard as well. In the main image above, I told Google Translate that I was going to type Chinese characters. Instead, I typed the characters pinyin into it, and it showed me things that I could have meant. Here's another example. I'll type ma: It shows me the characters and I can choose which one I want by just typing the number. The first one is a horse, the second one is like a question mark, the third one is a mother, and so on. Tones are really important and while each of those is ma, they don't all have the same tone. We'll talk about that more another day. We don't need to enter the tone to find the character. While the Pīnyīn characters look familiar, and many are pronounced as you'd expect, you do also have to learn how to pronounce them. For example, the character is written as Xīn but is pronounced more like "shin". Additional characters also change things. For example, while  is written as  and pronounced like "chee", the character  is written as Qiě and pronounced more like "chair". There are also some sounds that we really don't have. For example, the character  is written as Céng but the "c" is pronounced more like "ts", so this is like "tseng". If you try to learn this, it won't take that long to get to a point where you can read Pīnyīn fairly easily. Because Japanese has a lot of Chinese characters (they call them 漢字 or Kanji), they also adopted a way of writing using our familiar characters. It's called ローマ字 or Romaji. It works much the same:


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.

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.


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:

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:

Learn about indexing

Want to learn more about indexes? Take our online on-demand course now:


Never lose hope – it's all around you

It's that time of year again when people look philosophically back at the current year, and start to think about how they'll improve their situation in the new year. But for many, this is a very hard time of the year.

I've had a few friends this year who seem to have almost lost hope when they've ended up in poor situations.

I just wanted to make a short post to encourage you all to never lose hope. Alexander Pope said:

Hope springs eternal in the human breast: Man never is, but always to be blest.

No matter how bad you think your situation is. There is hope. It's in you and all around you. I loved the awesome image from Stephen Walker that I've used above. You just need to recognize it.

In many cases, you really just need to talk the situation through with others. Don't be afraid to do so. We're here to listen.

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.