The Bit Bucket

When I need to display a potentially large number of rows on a screen, I often need to show them in pages. This is called paginating the rows.

For example, if I’m showing bank transactions, I might want to show 25 per page. The challenge is that I also need to be able to ask for a specific page number. So my query becomes: give me page 7 where there are 25 rows per page. In that case, I want to skip the first 150 rows, then ask for the next 25 rows.

2019-05-13

One of my Kiwi buddies who specializes in Power BI is Reza Rad. I was pleased to see he had a set of eBooks now on Power BI but was especially happy to see he had a book called Pro Power BI Architecture.

There are lots of books around to discuss how to use Power BI but there’s been a real lack of books on architecting solutions using Power BI. So if you want to learn to develop dashboards or reports, this isn’t the book for you. Reza has other books for that.

2019-05-10

This is part 4 in a series of posts:

• Part 1 covered being sure there is a locking and blocking issue
• Part 2 covered the impact of RCSI
• Part 3 looked at the impacts of indexing on locking and blocking

Today, though, I want to look at the next potential issue, and that’s deadlocks.

Deadlocks

I often hear people interchangeably using the words lock, block, and deadlock.

Locks are a standard feature of systems that are designed for concurrency. Nothing nasty to see here. When they become a problem is in two situations:

2019-05-09

A while back I wanted to do some testing of Azure Database for PostgreSQL and I needed a database to work with. So I thought I’d try to migrate a basic WideWorldImporters database across to PostgreSQL.

I wrote scripts to do the main migration but then decided that I wanted to change the names of the columns.  While a large number of people use PascalCased names in SQL Server, it’s really, really common to use snake_cased names in PostgreSQL. Object names in PostgreSQL are also limited to 63 characters. I needed to alter these names while generating the scripts and I didn’t want to do that manually.

2019-05-08

Who moved my cheese (diagram tool)?

I was part of an interesting email chain today. It started with a guy complaining that in SQL Server Management Studio 18x, the database diagram tool had been removed.

Now I was disappointed to see the tool gone. Mind you, I didn’t ever think it was a great diagramming tool. I would have hoped they might have replaced it by a better one instead of just removing it.

2019-05-07

When you run a query against SQL Server, you might want the rows to be returned in a specific order. However, the first thing you should ask yourself is whether that’s needed at all.

Sorting can be a very expensive operation if there is no suitable index that’s already sorted the data for you. And the question is: should SQL Server be the one doing the sorting? In many cases, the client application that’s reading the data might be a better place to do the sorting. That’s not always true but it’s worth considering. You often have way more clients than your single database server, and sorting data unnecessarily in the database server could limit your ability to scale your system to higher volumes.

2019-05-06

DevOps has quickly become a core part of how many organizations deliver IT, and in particular, how they deliver applications. But just as quickly as it has become popular, a whole series of XXXOps names have appeared. One of the latest is AIOps. So is it just yet another almost meaningless acronym?

Well as Betteridges Law of Headlines suggests, the answer is no.

When I first saw the term, I was presuming this would be about how to deploy AI based systems, and I wondered why on earth that would need a special name. But that’s not what it is.

2019-05-03

This is part 3 in a series of posts:

• Part 1 covered being sure there is a locking and blocking issue
• Part 2 covered the impact of RCSI

Today, though, I want to look at the next potential issue, and that’s indexing.

Indexing impacts on locking

It might seem obvious that you need to fix indexing issues in your database applications but it mightn’t be so obvious that locking is one of the main things that is affected.

2019-05-02

While doing my consulting work, I often come across Foreign Keys and Check Constraints that are not trusted. What this means is that either:

• They were created without checking existing data
• They were disabled for a period and re-enabled without re-checking the existing data

I like to see this fixed whenever possible. To make that easy, in our free SDU Tools for developers and DBAs, we added two stored procedures to help: RetrustForeignKeys and RetrustCheckConstraints.

2019-05-01

Over the years, I’ve been really interested in what makes people happy in life. I’m always fascinated by people who think that wealth, products, properties, the latest handbag or car, new partner, etc. will make them happy. I’m sure the media has tried to tell them that, but it’s never been true.

I’ve had friends all colleagues all across the wealth spectrum, and I can say without any doubt in my mind, that some of the richest people I know are also some of the most unhappy. Worse, I’ve seen money destroy families so many times.

2019-04-30