9

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 ...


8

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.


7

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 ...


5

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 ...


5

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 ...


5

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) ...


5

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 ...


5

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; and ALTER INDEX IX_Employee_ManagerID ...


5

Short answer to your questions: Forever See above (this doesn't happen, because of above) But there are exceptions: The client app can have defined a timeout less then indefinite, meaning it will send an "abort" (formally called "attention") on the TDS protocol after that many seconds. SQL Server receives the attention signal and ...


4

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 ...


4

I dislike the name "create_date" for a column that's not actually a date but a timestamptz. Using "created_at" instead. Since created_at can be NULL, this 3rd variant will be faster (even if not by much): CREATE INDEX index_c ON my_table (created_at DESC NULLS LAST); NULL values sort after the greatest value by default. DESCENDING sort ...


4

Your indexed view cannot be created as written because it is nondeterministic. Rows would fall out of the view as time passes. Your current query is likely producing an execution plan like: Leaving aside the question of separate tables and indexed views for a moment, give the following minor rewrite (using existing indexes) a shot: SELECT t.* FROM ...


4

First of all: Missing index requests aren't smart. At all. To put it mildly. I like to think of it as that it doesn't have a lot of CPU cycles to produce the recommendations. However, in this case there is a logic to the recommendations. Consider this predicate: E_DATE_OPENED < DATEADD(DAY, 7, E_DATE_SENT) An index cannot be SEEKed for it, since you ...


4

nope Even with perfect indexes, SQL Server won't be able to take advantage of them to avoid physically sorting data, the way your where clause is written. The issue is apparent in your query plan, because you're not sorting by columns, you're sorting by expressions. The only way to make expressions like this index-able is to: Make computed columns, and ...


3

Ideal for those queries would be in index on (court_id, id), and with the columns in that order. It should be extremely fast in either direction. And once you have it, you should be able to get rid of the plain index on court_id as it wouldn't be much good anymore.


3

Rewrite the condition on reserved_until to WHERE COALESCE(reserved_until, TIMESTAMP '-infinity') <= '2021-05-14 14:23:16' and create an index for it: CREATE INDEX ON table_name (COALESCE(reserved_until, TIMESTAMP '-infinity')) WHERE rejected_at IS NULL AND deleted_at IS NULL; Then you should gather statistics: ANALYZE table_name;


3

In addition to unique indexes, indexes supporting foreign keys should normally be retained even if they are infrequently used.


3

I would say that Ola scripts work well for SQL 2014 too. Here is what you should do: EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @...


3

As I understand it, you created two non-clustered indexes and in order to cover the query, you have all the columns from the table in these indexed (the key is the key and the other columns are included columns). If above is correct, you now have two more copies of the table table, sort of. I.e., three instances of the same data. You have the clustered index ...


3

No, there's no difference at all in Teradata. The PI is hash-based, both columns must be used in a Where- or Join-condition to allow index-based access.


3

It is obvious that PostgreSQL can only deliver the 100000000th row if it scans the first 100000000 result rows and discards the first 99999999. This can be done using only the index if it is an index-only scan; if not, the table row is fetched too. There is a potential optimization here in that an index-only scan could be used to fetch the first 99999999 ...


3

I would suggest this way of thinking instead of a rule of thumb: Are write IOs a problem in your system for that table? (ex: slowness or deadlock) if yes NEXT, if no GOTO 5 Is the index required for any report or application in your system? (ex: required for a quick report needed by the CEO) if yes GOTO 4, if no NEXT remove the index then GOTO 1 Improve the ...


3

SQL Server (and hence Azure SQL Database) has a cost based query optimiser (QO). This means it generates a number of logically equivalent but physically different query execution plans then chooses the cheapest one according to some internal, proprietary cost calculation model. So, trivially, it chose to do this two-index join because that was cheaper than ...


3

E_ID is not part of the WHERE clause while index IX_EMAIL_3 is sorted by E_ID so SQL can't seek to any of value from where and need to scan the index to find it. That is why it is taking IX_EMAIL_2. Try this: CREATE NONCLUSTERED INDEX IX_EMAIL_4 ON EMAIL (E_CUS_ID ASC, E_TYPE ASC, E_ID ASC) INCLUDE (E_PER_ID) WITH (FILLFACTOR = 95) I think it will be used.


2

If the index is not enforcing uniqueness, it is only there to (potentially) assist in read access. I'd just caution against dropping indexes that might be used for quarter end/year end reporting jobs that have been created to avoid locking tables for extended periods of time. You'll have to use some judgment and knowledge of the tables to determine that or ...


2

What a terrible query! Consider using different software. The only thing that could help are trigram indexes on both columns: CREATE EXTENSION pg_trgm; CREATE INDEX ON upload_file USING gin ((name::text) gin_trgm_ops); CREATE INDEX ON upload_file USING gin ((id::text) gin_trgm_ops); Then you might get two bitmap index scans and a "bitmap or". If ...


2

The planner does weigh between the options and chooses the one that it thinks will be cheaper, of course based on the estimates it has at hand at the time. When having the ANY in the "Index Cond", it needs to re-descend the index one time for each of the 19 members of your list. Each time it re-descends, it thinks it will land on a different index ...


2

I think the Clustered Columnstore is probably overkill for your situation and working against you. Given the amount of data and how you're trying to query, you should just do a regular clustered index (primary key (ComponentId, Timestamp) and enable page compression if you're trying to save space/reduce disk i/o. The thing to remember about columnstore is ...


2

With that design it is expected that the query requires a full scan of the columnstore. That should be very fast at that scale, but the IO stats show that you're reading from disk. If you look at the wait stats for the actual execution plan you should see that it's more IO waits than CPU utilization. You could scale up the database to get more cache memory,...


2

We have an application generated query using a view that has two tables joined on a LEFT OUTER join. When filtering by fields from just one table (either table) an index seek happens and it's reasonably fast. It is valid (= guaranteed to always produce correct results) to push a selection (aka filter, predicate) below an inner join when the selection ...


Only top voted, non community-wiki answers of a minimum length are eligible