Hot answers tagged

12

A <> X predicate is sargable. SQL Server can convert it to two range seeks (on < X or > X). Unless the index is covering for the query however you may well not see this. The tipping point for a query using a non covering index and look ups is typically very low. (at most selectivity of a single digit percent). So unless, say, 99% of the table ...


12

You need to use the NOEXPAND query hint (I'm assuming you're on Standard Edition of SQL Server, as Enterprise won't expand by default) in order for your query to use the Indexed View (instead of expanding it to the underlying table). This will solve your first issue. This is how you can use it in your query: SELECT TOP (100) * FROM Losses_CombinedPortfolio ...


11

The optimization that allows you to use the filtered index with a RECOMPILE hint is called the "parameter embedding optimization." That's a process where the query parser replaces the variable reference with the literal value inside the variable. See this post from Paul White for the reason why it doesn't work in your second case: Parameter ...


10

If you want SQL Server to use that index for that specific query, you need to include the session_id column. Otherwise, for each row it finds it will have to go do a key lookup into the base table. It will choose to do this for small result sets, but once you get above a certain number of rows ("the tipping point"), SQL Server thinks it's more ...


8

I think we can remove the @SPID variable with a reference to sys.dm_exec_requests: ;WITH agg AS ( SELECT SUM(qp.[row_count]) AS [RowsProcessed], SUM(qp.[estimate_row_count]) AS [TotalRows], MAX(qp.last_active_time) - MIN(qp.first_active_time) AS [ElapsedMS], MAX(IIF(qp.[close_time] = 0 AND qp.[first_row_time] > 0, ...


8

Another answer from another postgresql contributor. PostgreSQL will not even try to use any indexes during execution of "alter table set not null". It is just not implemented. Proper implementation of index scan is difficult part. We cannot just do something like this query select exists(select from foos where bar1 is null) from alter table command for ...


8

What you ask for is what we call a "clustered index" in the SQL Server world. The clustered index is the data. I.e., the b-tree for the index is you actual table data. A clustered index don't have the concept of included columns since all columns are in the clustered index - the clustered index being the data. So you have the key columns (what you ...


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

No, it's not necessary. Statistics on the main table are not affected by indexes at all. One thing to observe: expression indexes introduce new "derived" columns, and Postgres gathers separate statistics for those. Removing such indexes also removes the special statistics - which may lead to different query plans, beyond the fact that the index is ...


7

Your questions: Doesn't an Index with included columns have the exact same problem? Yes. Is a table with included columns not just the same as a "shadow table" with the same fragmentation problems? Yes 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 ...


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


7

Let's break down what's going on, step-by-step: The compiler needs to get the value of session_id for each row. session_id is not in the index, so it needs to be looked up from the base table Your table has no clustered index, it is a heap table, and every row is located using its RID. A clustered index would need a key lookup instead So the compiler needs ...


6

Adding the nonclustered columnstore index allows for a batch mode sort in the second execution plan. This causes all of the processing to be done on one thread - so even though the query has a parallel plan, it's essentially running serially. You can see that by looking at the details of the different operators. I reproduced your problem locally, here's ...


6

Why the dropped index is still there? In fact that index is no longer there, the column is the one still there and what was once a Clustered Index is now a Heap. Did I got the dependency error above because of this still-existing index? No, because the index no longer exists. If not, is there something else I should check? I'd like to avoid dropping the ...


6

To answer the specific question: No, an extra index on (entry_date) or two would not hurt performance of updating a different, title column. Additionally: No, the version of MySQL, 5.6, is not too ancient, even if some modern features (like window functions) are missing. You should have decent performance with decent hardware. We can only speculate on ...


6

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


6

You're pretty much right but this isn't the best way to conceptualize it, rather you should draw out a Tree since that is the logical data structure typically used to hold the data within an index. Using your notation, your first example and your third example are a little hard to distinguish between what is meant to be the key / row ID for referencing the ...


5

We usually think of an index as a way of rapidly finding all references to a given piece of information. In a textbook given a topic we can find all pages that mention that topic. In a database given a column value we can find all rows that have that value. Calling a columnstore an "index" in this sense is a bit of a misnomer. It is not intended to provide ...


5

You could use the additional module pg_prewarm. Has to be installed once per database. See: PostgreSQL: Force data into memory It can "prewarm" tables as well as indexes. To do it for your index: SELECT pg_prewarm('test.test_table_idx'); Unless you get index-only scans (which you do not with the index at hand), you might want to prewarm the table as well:...


5

The "B" in B-tree doesn't necessarily stand for "balanced". It depends on who you ask. See for instance this. As often is the case, terminology degrades (over time). All SQL Server "row indexes" (as opposed to other types of indexes in SQL Server like hash, columnstore, full-text, XML (to some extent) and geospatial) are B-tree indexes. They are always "...


5

Some of this information like index depth can be found in DMF dm_db_index_physical_stats(). Some outer interesting information that can be found in it is number of used data pages and the fragmentation level. Personally I have used the following query to get the state of the indexes for current database SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, ind....


5

Access to a row using a non-clustered index, which idx_name is, requires extra random I/O: b-tree lookup finds the clustered (primary key) index value, then you need to go and fetch the actual row from the clustered index. The alternative is a sequential scan of the clustered index itself, which does not incur that extra I/O cost and is also simply more ...


5

Looking at the MySQL Documentation, the glossary indicates this about Covering Indexes: An index that includes all the columns retrieved by a query. Instead of using the index values as pointers to find the full table rows, the query returns values from the index structure, saving disk I/O. InnoDB can apply this optimization technique to more indexes than ...


5

Based on those facts, 190M rows, 450GB, un-indexed heap, long-running queries, you should create a Clustered Columnstore index and store the data in compressed columnar format. However, that might not be the optimal solution, as you still have a table with no key and no indexes, which is strange.


5

Welcome to DBA.StackExchange, and interesting question!...one I've never thought of before. I had to double check myself on what I thought the process was, so I think this StackOverflow Answer should be what you're looking for. Specifically the first statement of the answer: Dropping/Rebuilding an Index will result in invalidation of any cached execution ...


5

To go a bit more into details, the plan isn't flushed when the index is dropped. See repro below. The plan stay in cache, but it is now invalid. Next time that plan is up for usage, SQL Server check whether it is valid. If for instance a schema change was made (as in dropping an index), it isn't valid. So a new plan will be generated. Dropping the plan when ...


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 No. Long answer No, and adding the index could be detrimental to performance. Something that factors heavily into whether or not a secondary/non-clustered index is even used is how selective it is (and the searches you are trying to perform are). Y/N has two values - it's ability to be selective is going to depend on the proportion of Y to N. ...


4

I looked at the source code (function ATRewriteTable in src/backend/commands/tablecmds.c), and PostgreSQL always uses a sequential scan of the table to verify NOT NULL constraints. So creating indexes won't speed up the execution.


4

Is the above approach the best way to do this? Essentially this is the only way to approach this - indexes can only be created by one command. You could execute T-SQL for the CREATE INDEX manually, but the same command is going to be executed on the server regardless (barring any different options you choose, such as online). As with any production ...


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