A Fine Slice Of SQL Server
In this video, I show you various techniques you can use to make window functions faster, including proper indexing and usage of batch mode. I also demonstrate the performance difference between RANGE and ROWS.
This wraps up my series on window functions, and although I pack a lot of content into the video, I highly recommend checking out the links for deeper dives into performance.
Leave a CommentErik Darling talks about indexed views. The set of limitations is huge and painful, just as Erik describes. For every instance in which I was successfully able to build an indexed view, there were probably a couple of dozen instances in which it was a great idea until I hit one or more of the limitations that Erik describes.
Leave a CommentAaron Bertrand extends a prior article:
In my previous tip, Managing Unused Indexes in SQL Server Availability Groups – Part 1, I showed how I use dynamic SQL to gather index usage statistics for a given table from all replicas in an availability group. Knowing the usage from all workloads is definitely better than focusing on only the primary or a single secondary. But what if I want to make more informed decisions, incorporating row counts, size, and index columns into this output?
Read on to see how you can incorporate this additional information.
Leave a CommentAaron Bertrand wants a cross-replica accounting system:
As a part of optimizing performance, I evaluate index usage across many instances and databases. I often find that some indexes aren’t used much or, at least on first glance, appear to go unused. Since we use availability groups (AG), different workloads run against replicas in different roles. All writes happen on the primary, obviously. However, some queries only happen on read-only secondaries (either because read-only routing is in use, or because some processes are manually directed at specific secondaries, or both). Unfortunately, index usage is not rolled up anywhere across all replicas. This means that looking at the primary alone gives an incomplete picture. How do I make sure I account for index activity everywhere, not just on the primary?
This is part one of a series but already has enough to get us started.
Leave a CommentDavid Fitzjarrell looks at bitmap indexes:
Bitmap indexes can be very useful, especially when NULL columns are present, as a bitmap index will include such values when btree indexes may not, such as entirely null index keys. Unfortunately bitmap indexes do not behave well with concurrent transactions, where deadlocks may arise because of the bitmap index.
Oracle will trap, report and “resolve” deadlocks by assessing the situation, determining which session created the deadlock and killing the ‘offending’ session, with no manual intervention required. The trace file generated reports this as an issue with application coding and/or logic and in many cases this is the likely cause. Enter the bitmap index and a concurrent transaction and, mysteriously, a deadlock may appear, confounding the developer and the DBA.
Read on to learn more about how bitmap indexes can provide a (potentially) strange source of deadlocks.
Comments closedBrent Ozar shares a surprise if you’re coming from the SQL Server world:
Postgres starts with a very different assumption: it uses multi-version concurrency control (MVCC, or what SQL Server folks call RCSI) by default, right from the get-go. When we update rows, Postgres automatically makes new versions of the rows, storing them inside the same table. There’s no need to enable it – it’s on for all databases, by default.
So because Postgres is constantly creating new versions of rows, it doesn’t really make sense to store the table itself in some kind of order.
Every table is a heap.
This fundamental storage difference has some ramifications for query tuning and indexing strategy that makes the two platforms quite different.
Comments closedAaron Bertrand only needs recent data:
I recently resolved an issue where a query pulling data from the last 30 days would time out due to the table’s size and the lack of a supporting index. Creating a supporting index is possible, but not ideal; it will be very large and may not be useful for most queries and use cases. I wonder how I could implement a filtered index that follows time and is always limited to the last
ndays.
Read on to see how Aaron did this. It’s a clever solution and Aaron does make clear the tricky part of filtered indexes: explicit conditions.
Comments closedLaurenz Albe shares some advice:
People often ask “How can I automatically rebuild by indexes regularly?” or “When should I rebuild my indexes in PostgreSQL?”. That always gives me the feeling that they want to solve a problem that isn’t there. But the
REINDEXstatement is certainly there for a reason, and sometimes it is perfectly reasonable to rebuild an index. In this article, I’ll explain when it makes sense to rebuild an index and how you can get the relevant data to make that decision.
Read on to learn more.
Comments closedAndrei Lepikhov responds to feedback:
In the previous post, I explored some nuances of Postgres related to indexes and parallel workers. This text sparked a lively discussion on LinkedIn, during which one commentator (thanks to Ants Aasma) proposed an index that was significantly more efficient than those discussed in the article. However, an automated comparison of EXPLAINs did not clarify the reasons for its superiority, necessitating further investigation.
Click through for the index and what Andrei learned along the way.
Comments closedBrent Ozar flips everything around:
99.9% of you are never gonna need this.
But let’s say you need to run queries with leading (not trailing) wildcards, like this search for all the different national versions of the Encabulator, each of which has different prefixes depending on which government it’s being sold to:
This is indeed a pretty uncommon scenario. I’m pretty sure I’ve only ever needed to do this once. Well, twice, but in one case I couldn’t actually use the REVERSE() function because the column was itself an awful non-deterministic function and this solution wouldn’t work.