Denis Gobo violates Betteridge’s Law of Headlines:
This question came up the other day from a co-worker, he said he couldn’t change a query but was there a way of making the same query produce a better plan by doing something else perhaps (magic?)
He said his query had a WHERE clause that looked like the followingWHERE RIGHT(SomeColumn,3) = '333'
I then asked if he could change the table, his answer was that he couldn’t mess around with the current columns but he could add a column
Click through to see how Denis was able to solve this problem.
Tables were very narrow with just a few columns and my expectations for data growth were very modest. However, after just a little while I was very surprised when my database showed huge unexpected growth and size of the data became multiple times higher than I’ve expected.
After very little research I’ve found and fixed the problem. In this post I’ll describe how I’ve done it.
Read on to learn how Slava figured this out and how a clustered index fixed the problem.
Ed Elliott wades into the clustered index debate:
I have seen this debated in forums spread over the internet for decades, and the advice that we gave ten years ago isn’t as valid today as it was then. Ten years ago, memory was considerably less, and disks were spinning rust. The advent of SSD’s and the ability to get servers with more memory than data, even on large systems have changed how we should think about designing and maintaining databases.
I generally subscribe to the NUSE philosophy: Narrow, Unique, Static, Ever-Increasing. That generally leads me to selecting identity integers or longs. For junction tables (whose entire purpose is to join two tables together and which never get referenced outside of that), I use the primary key as the clustered index.
In extreme insert scenarios, I can see wanting to maximize fragmentation in order to insert into more pages in the B-tree and avoid hot spot pages.
Tibor Karaszi revises and extends some remarks on index fragmentation:
In my last blog post, I wanted to focus on the sequential vs random I/O aspect and how that part should be more or less irrelevant with modern hardware. So I did a test that did a full scan (following the linked list of an index) and see if I could notice any performance difference on my SSD. I couldn’t.
That isn’t the end of the story, it turns out. Another aspect is how the data is brought into memory. You might know that SQL server can do “read ahead”, meaning it does larger reads per I/O instead if single-page I/O. I.e., fewer but larger I/O operations. This sounds fine, but what happens when we have fragmentation?
Read on for a situation in which fragmentation does matter.
Monica Rathbun fills us in on filtered indexes:
What is a filtered index?
Simply it’s an index with a where clause. It is an optimized non clustered index that can be narrowed down in scope to better fit a subset of data. Example being date ranges, years, non NULLs or specific product types.
I wish filtered indexes were better than they are because they can solve some interesting problems but get stuck on parameterized queries.
Marek Masko walks through an attendee question:
Last week I was speaking at SQLDay 2019 conference in Wroclaw, Poland. My session was about things you should focus on during work with Legacy Databases. One of the topics I discussed was concerning the database usage statistics collection and aggregation (mainly indexes and stored procedures).
After the session, one of the attendees came to me and ask me if I know that actions causing check of Foreign Keys and that use indexes underneath, don’t update index usage stats DMV. I was very surprised because, in my opinion, such behavior would be a huge SQL Server defect. So I decided to check it out…
Read on for Marek’s explanation and demo.
Paul White explains how to perform minimal logging when using the INSERT..SELECT pattern to insert into an empty table with a clustered index:
The summary top row suggests that all inserts to an empty clustered index will be minimally logged as long as
TABLOCKandORDERhints are specified. TheTABLOCKhint is required to enable theRowSetBulkfacility as used for heap table bulk loads. AnORDERhint is required to ensure rows arrive at the Clustered Index Insert plan operator in target index key order. Without this guarantee, SQL Server might add index rows that are not sorted correctly, which would not be good.Unlike other bulk loading methods, it is not possible to specify the required
ORDERhint on anINSERT...SELECTstatement. This hint is not the same as using anORDER BYclause on theINSERT...SELECTstatement. AnORDER BYclause on anINSERTonly guarantees the way any identity values are assigned, not row insert order.
Read on to see what you can do.
We compress to gain space, correct? So why sacrifice it to our indexes?
Here is a script (complete with demo databases and tables) that will find the culprits and fix them for you. Put it (minus the demo databases and tables) in a job, and quit worrying about this. Your tables (and indexes) will be sparkling clean. You’re welcome.
Click through for the script. It’s a good reminder that compressing a clustered index does not automatically compress non-clustered indexes—nor should it, as these won’t necessarily compress at the same ratio because data type makeup can differ.
Erik Darling shows how READPAST is no panacea:
Locking hints can be really handy in these situations, especially the READPAST hint. The documentation for it says that it allows you to skip over row level locks (that means you can’t skip over page or object level locks).
What it leaves out is that your READPAST query may also need to try to take row level shared locks.
Read on for an example as well as an alternative which ends up being better in this case.
Tibor Karaszi argues that index fragmentation is a less serious issue than most DBAs think:
You know the story. Every week or so, we defragment the indexes. Many of us uses Ola Hallengren’s great script for this, some uses Maintenance Plans, and there are of course other alternatives as well. But are we just wasting time and effort? Quite probably we are. I’m going to start with some basics, and then do some reasoning, and finally give you some numbers of a very simple test that I ran. The T-SQL code is available. If you give it a try, please let us know your finding for your environment by adding a comment. I will do some generalizations and simplifications, to avid this post being 10 times longer.
Jeff Moden has a couple of great talks on the topic which really pushed me in this direction. Grab his slides from the SQL Saturday site for a much deeper look at this topic.
Kevin Feasel
2019-06-13
Indexing, Query Tuning
No Comment