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

2018-12-26

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.

2018-12-24

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.

2018-12-20

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.

2018-12-20

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.

2018-12-19

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:

2018-12-19

I’ve mentioned before that SQL Server Management Studio (SSMS) is a good tool for analyzing queries, as much as for executing them.

In SQL Server 2005, query plans had missing index details added. When a query plan was created, SQL Server recorded that it thought it could have executed the query better, if only you’d provided it with appropriate indexes. But at that point, the suggestions weren’t very good, and the tools didn’t show them.

2018-12-13

Years ago, it was common to need to print checks (cheques) from computer systems. Fortunately checks have now pretty much disappeared in most countries. One of the challenges with printing a check, was that you needed to convert an amount of money into words. Even though checks are on the wane, it’s still important to do that on some contracts, etc.  In our free SDU Tools for developers and DBAs,  we added a function NumberAsText to do just that.

2018-12-12

When I’m running SQL Server Advanced T-SQL classes, we spend time discussing cursors. The general message is that most of the time, but not always, cursors are the wrong answer. The problem with cursors is that instead of telling SQL Server what you want it to work out (ie: declarative query), you are telling it how to work that out (ie: procedural query).

You’ll hear people say to never use cursors. This is also incorrect. There are occasions where I do use cursors. Mostly this is when I’m building utilities that do things like scripting objects in the database. I’d almost never use a cursor though, in general data processing running against SQL Server. You don’t want to be doing row by row processing against the server.

2018-12-12

I used to do a lot of work at the operating system and network level. I was always fascinated watching people use network trace tools when they were trying to debug a problem. The challenge was that they had no idea what was normal activity on the network, and what wasn’t.

The end result of this is that they’d then spend huge amounts of time chasing down what were really just red herrings.

2018-12-11