A Fine Slice Of SQL Server
David Fitzjarrell is looking for a speedup:
There may be times when the DBA wants, or needs, to know:
- What indexes are created against a given table
- How those indexes are built
Such information is critical to performance tuning or discovering why, when an index exists, it isn’t used. Let’s dig into this and see what we find.
Click through for some sample queries.
Leave a CommentKendra Little does a bit of index management:
Dear Postgres, Why won’t you use my covering index?
Lately I’ve been learning to tune queries running against PostgreSQL, and it’s pretty delightful. One fun question that I worked through struck me something that most Postgres users probably encounter one way or another: sometimes you may create the perfect index that covers a given query, but the query planner will choose to ignore it and scan the base table.
Why in the world would it do that?
Read on to learn why.
Leave a CommentJon Russell shares some thoughts:
Most database administrators are comfortable with the daily tasks of backup, CHECKDB, index maintenance, and statistics updates. The available guidance for those topics is extensive. Much less has been written about caring for estates that contain hundreds of databases—or a single database large enough to strain conventional maintenance windows. This post will focus on options that help when individual tables or complete databases grow beyond the point where “standard” maintenance jobs finish in a reasonable time.
Click through for Jon’s advice. My big thing is to stop worrying and learn to love the bomb fragmentation. Index fragmentation was very important back in the days of spinning disk, where accessing pages took much longer if you needed to move the disk head. With NVMe storage, all-flash arrays, and even more prosaic solid state drives, random access is typically at least as fast as sequential access, so there’s no real benefit to having pages of data be contiguous.
Andy Brownsword reviews the list:
When running queries, reviewing execution plans, or diving into Query Store it’s not uncommon to see missing index recommendations, particularly for more complex queries. But are they any good?
The answer is yes, but also no. Read on to learn how it is useful, but only if you take into account the foibles of the missing indexes dynamic management view.
Comments closedStephen Planck explains the value (and limitations) of filtered indexes:
When a table holds millions of rows yet most queries touch only a small, well-defined subset, a traditional non-clustered index feels like using a searchlight to find something sitting under a desk lamp: the index still stores an entry for every row even though the workload rarely needs most of them. SQL Server’s answer is the filtered index—introduced in SQL Server 2008 and still under-used today—allowing you to index just the rows that match a predicate you supply in a
WHEREclause.
I always thought I would get more value out of filtered indexes. Instead, they’re very useful for specific circumstances, but not the all-purpose performance dynamos you’d want them to be, particularly because of the limitations.
Comments closedDaniel Hutmacher gives it a try:
Starting today, the public preview of SQL Server 2025 is available to download!
One really interesting new feature that got my attention was the addition of JSON indexes. I’m a big fan of everything that makes working with JSON easier, since JSON blobs are so much easier to work with than table variables when you’re moving data from point A to point B. This is especially true when you’re working with complex, relational data.
Daniel lays out some of the limitations of JSON index creation and also some of the performance gains you might see from it. This will be most helpful in data engineering scenarios, shredding JSON from various services, but the normalization purist in me says that if you’re shredding JSON enough to need indexes, it’s probably time to normalize that data.
1 CommentKevin Hill shares some thoughts:
Here’s the truth: if you’re doing this daily on indexes smaller than 10,000 pages, you might be chewing up CPU, bloating your logs, and annoying your users for zero gain.
Let’s fix that.
I disagree with Kevin on index reorganization, in that I would never perform index reorgs—there’s no there there. Even so, I firmly agree with the thrust of Kevin’s argument and believe that, in the majority of cases, companies with DBAs (or people who have stumbled through maintenance plans before) are maintaining indexes too much rather than not enough.
Comments closedLukas Vileikis explains how hash indexes work in MySQL:
In MySQL, hash indexes are indexes that are used in queries that use the equality operators like
=or<=>(which is the MySQLNULLsafe equality operator, equivalent to the SQL StandardIS NOT DISTINCTFROM). Hash indexes are not used in other situations, so they can be useful for things like random or generated PRIMARY KEY values where you are looking up single rows and not needing to order rows or searching for a range of values.
Just like in SQL Server hash indexes only work for in-memory tables, and some of the mechanisms look very similar.
Comments closedErik Darling has some doozies today:
Many of our Tables have 15 plus guid s from other tables, but not declared as Foreign Key. Plus Foreigne Keys. When I add a Index on for every Foreign Key plus Indexes for declared foreign keys we end up with tables with 30 plus indexes for fks only. We don’t have write performance issues. would you index every fk for every possible join?
Click through for the answer to this and four other questions.
Comments closedDavid Fitzjarrell wants to see which indexes are in use:
Database performance is one of the biggest areas for DBAs to address, measured primarily in time. Users usually complain when queries “take too long” and immediately run to the DBA team to register their disappointment. One avenue used to address such concerns is the creation and maintenance of indexes, and as any DBA with any experience can attest, simply throwing indexes at a table isn’t necessarily the smartest play; a major concern is if the index is actually being utilized. Oracle, in its wisdom, has provided tools to monitor these objects in the form of system views. Let’s look at what is available and how to get the most benefit out of them.
Click through for some scripts and information on how to tell whether an index is in use or not.
Comments closed