XML index in SQL Server is implemented as an internal table that is a persisted version of the node table that is much the same as the XML shredding functions produce.
One of the columns in the internal table is called hid and that column contains the value used in the seek for a path expression. The value is an ordpath value. When you create a path xml ...
If there was a clever person who decided to use index hints in their application's queries, dropping said index will cause the query to fail outright if/when it runs.
Something like a quarter or year-end report might not be showing any index usage due to its infrequency of execution depending on how often the system is restarted.
Your query is a tricky one for the optimizer, for two reasons.
The optimizer doesn't currently have logic to transform a disjunction (OR) across tables into a union (related Q & A).
The optimizer doesn't reason about deferring lookups (dealing with keys only until the last moment). This relates to the SELECT * component of the given query.
Doesn't an Index with included columns have the exact same problem?
Is a table with included columns not just the same as a "shadow table" with the same fragmentation problems?
Should I migrate to use UserId, TipIndex as a ClusteredIndex instead of Id?
I would, yes.
How to prevent fragmentation?
There are a couple of ...
So if your primary access path is by question, then the unique clustered index that makes the most sense will be (QuestionId, EventId).
Adding a second index to EventId may not be useful as the index might not be selective enough and the query engine will decide it's just faster to read the entire table instead of doing a lot of work to read a large portion ...
do the nonclustered indexes even matter?
Yes. You have multiple copies of this table (or subsets of it), and updates have to be coordinated across all the copies. Query performance is probably not enhanced by the non-clustered indexes, but they are probably the cause of your deadlocks.
But for SELECT query performance, the non-clustered indexes shouldn't ...
It seems you are too much concerned about fragmentation, As long as you keep updating statistics regularly, fragmentation shouldn't bother you much for performance. You may read more details about this on a video shared by Mr. Brent Ozar and also another page here. Let me try answering your question one by one:
Doesn't an Index with included columns have ...
How could I remove the Filter operator and somehow force the Index Scan operator to read only the few thousand rows?
The Filter operator is applying the bitmap built on the join columns at the hash join.
Of the three join predicates, only order_date has a data type that is supported for bitmap pushdown to the column store scan. If you look at the Predicate ...
This is a common issue with functional transformations of any type not only dates. To avoid this issue just move the transformation to the constant side of comparison. Instead of
WHERE CONVERT_TZ(TBL.completed_date, timezone1, timezone2)
use the next syntax: (notice the reverse transformation of the TZs)
If you are unsure if you have a periodic report or job running that might use an index you would be well advised to disable the index rather than dropping it as then you have the definition in situ should you discover that it was after all required.
ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee DISABLE;
ALTER INDEX IX_Employee_ManagerID ...
It looks to me like it’s doing a Scan because it may well need rows from T553 if the condition in T1011 holds. On the other hand, if any of the conditions on T553 hold, it’ll need rows from T1011.
So indexes would have to be able to handle finding rows in T553 and then pulling in the relevant rows from T1011, and also finding rows in T1011 and pulling in the ...
I think your approach with nested loops is suboptimal.
Why can't you do something like:
select * from sensors where deviceid =? AND pcode = ? AND rectime between ? and ?
This would return the whole dataset and you could process it locally.
Selecting 500 or even more rows in one correct select is better then 500 single row selects.
In this case I would ...
an index can only be used when we have predicates for the leading (or all) columns.
In Postgres, this rule of thumb is only somewhat applicable to (default) B-tree indexes. See:
Working of indexes in PostgreSQL
But mostly wrong for GiST indexes. The manual:
A multicolumn GiST index can be used with query conditions that
involve any subset of the index's ...
Let's go through this step by step. We'll create a table and all relevant indexes and then populate our table with some data.
Create Table Q275204
/****** Object: Table [dbo].[Q275204] Script Date: 10.09.2020 07:54:00 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[Q275204](
Your query must be using an index on "id" to scan the index in the implied order, and then filtering out everything where "user_id" does not equal 123, stopping after it finds 31 rows which survive the filter. Going in one direction it quickly finds 31 such rows, going in the other direction needs to filter out a large number of rows ...
You're trying to write one magical query that will handle all possible combinations of search criteria efficiently. I call this the kitchen sink but it's simply not achievable to do this for 10 parameters with one query and one index. It's just not possible.
The bad news: You're not going to be able to create an index (or even 100 indexes - see here to get ...
RID Lookups occur on a heap data structure in SQL Server (as opposed to a B-Tree). This occurs when a non-covering nonclustered index is used to fetch the data and it needs to lookup the remaining fields it's missing. Your table data is stored in a heap when there is no clustered index on that table (as the clustered index defines the ordering the records ...
Updates is the one you IMO you should weigh the positive aspects of the index (seek and scan) against. With few updates, then the the overhead is marginal. Unless you consider diskspace, but I assume you are after "what makes things go slower" as opposed to "what uses storage".
Note that if an index hasn't been touched since startup, you ...
Consider storing your xml in a more usual format if you can. This might require a change at an earlier stage of the process, or some pre-processing when you import the data, but it could well be worth it.
The key observation is that encoding information in element names is quite unusual. Using xml with a predictable structure (ideally conforming to a schema) ...
Yes, because that composite index in your example is also known as a covering index. Specifically mentioned in the previously linked documentation here:
...people sometimes made covering indexes by writing the payload columns as ordinary index columns, that is writing
CREATE INDEX tab_x_y ON tab(x, y);
even though they had no intention of ever using y as ...
As explained in the documentation (and evident from the procedure source code), TimeLimit is used to
Set the time, in seconds, after which no commands are executed.
(emphasis above is mine).
A command that started before the threshold will keep running until it completes.
Pretty much every implementation decision is a trade off between competing factors. Building a columnstore index is CPU intensive but afterwards queries touching many rows are fast, and updates are slow. Which is most important for your workload, on average? Is there a time window in which that amount of CPU can be consumed without breaking other parts of ...
The single best way to answer your question would be to run the query, measure the performance, and look at the execution plan (two steps, measure performance in one, get the execution plan in a second, capturing execution plans affects performance). See which index is used and how it's used. Then, disable that index that was used, run the query again, and ...
Without any index hint, the Optimizer uses statistics to decide which index to use. And it updates the statistics in a reasonably (but not always) intelligent manner.
With a hint, such as FORCE INDEX, it ignores the statistics and uses the given index if at all possible.
Probably the FORCE was beneficial at first. But later the data in the table shifted ...
It depends on if you use an INSTEAD OF trigger or an AFTER trigger.
It sounds like you want an INSTEAD OF trigger where you can prevent the default values from being INSERTed or UPDATEd and replace them with NULLs instead.
(An AFTER trigger would effectively cause the index to update twice.)
You can read more on how to implement an INSTEAD OF trigger here.
As @Johnakahot2use mentioned, indexes on fields with really low uniqueness (low cardinality) is not a bad thing. It's just fields with high uniqueness are generally more efficient when indexed and stored in a B-Tree.
That being said, you don't normally write queries around the cardinality of the fields in your table (usually that's not even a luxury), you ...
Assuming INDEX(city_id, item_id, vote_date, ip_address_str, month, year)
Important: An index is used from left to right.
WHERE city_id = 123 AND ... -- At least the first column of the index is used
WHERE city_id < 123 AND ... -- ONLY the first column of the index is used
WHERE /* city_id not used */ -- The index is useless.
WHERE ... AND city_id = ...
I'm wondering what the best way would be to keep a nonclustered index for looking by accountId without all the fragmentation
You've got the partition key in the nonclustered index, so it's on the partition scheme (by default), and only the head partition is going to get inserts and additional fragmentation.
So you can rebuild the older partitions rarely and ...
What I would do is
SET STATISTICS IO ON
and then I would test the SELECT statement that produces the seek and the UPDATE statement.
Finally, I would compare the number of pages read by the SELECT with the number of pages written by the UPDATE.
But in general this ratio between seek and updates seems to suggest that your nonclustered index is not valuable. ...
I would say that Ola scripts work well for SQL 2014 too.
Here is what you should do:
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,