Index Seeks for better performance? Deep Dive on “Covering vs Non Covering Indexes”

Hi there,

A few days ago a client brought me a question, he had two tables with the same data, one in each database (same SQL version and compatibility level in different environments with similar infrastructure and configurations). Both tables had an extremely selective column and both had indexes in that column. The thing is that when running a particular query on one Database, let’s call it DB1 here, it was really fast whereas running the same query on the other database (DB2) it was a lot slower. The subset of rows that matched the WHERE clause was the same and it was really small in both cases (since the tables had the same data).

“Ok, let’s have a look at that”, I said, and when doing so, the query running on DB1 was doing a Clustered Index Seek whereas the same query in DB2 was doing a Table Scan. Didn’t I say that both tables had indexes in that column?

Query Store Hints, how can it help in performance issues?

Hi there!

Last December I got lucky since after upgrading from SQL Server 2012 to 2022, one of my clients had a process running in a particular database that went from around 10 minutes to around 10 hours!

You might be thinking: “It got 60 times worse, where’s the luck in that?”

Well, it presented me with a great opportunity to try a new feature in SQL Server 2022 called “Query Store Hints” (in a real production environment) and confirm the fabulous results that can be obtained by using it, with no effort at all. I thought it could be worth to share this experience and that’s what I’ll present in this post 🙂

Why you shouldn’t use the securityadmin role anymore

Hi there!

I usually avoid using the two “radical” words: “never” and “always” but regarding the membership need for the “securityadmin” server role since SQL 2005/2008 onward, I find it hard to come up with a good reason why it should ever be used, especially considering the security risks involved. A few weeks ago, while checking permissions for some logins in a (critical) SQL Server 2019 instance, I came across some really worrying situations, among which I found this one… again! Although it wasn’t the worst I found there (the worst was to bad to even mention here) I felt impelled to write about this one, maybe because the risks may not be so obvious or are somewhat concealed… Well, let’s bring them to light! 🙂

The “hidden” benefits of Indexes in Foreign Key columns

Hi!

Let me start this post with a question, “Do you think that it can be beneficial to have a single column index for the foreign key column in the child table?“

Well, I believe I can ear three types of answers to this question:

• Always!
• Never!
• It Depends…

Hmm, I guess we’ll have to find out which of these answers is the most appropriate and more importantly: why!

The “computed column”, the “scalar function” and the “non parallel plan”… Oops!

Hi there!

Just like in the movie “The Good, the Bad and the Ugly” there are also 3 main actors here.
In this post, I’m going to invite you to meet a scenario that can result in a significant performance issue, learn how to look for it, verify the consequences of its use and ways to solve it!

If you like performance tuning, just keep on reading 🙂