Hot answers tagged

13

It's a matter of giving results using the minimum IO operations. The index is much smaller than the whole table and based on statistics SQL Server knows the average cardinality of a single name ('xyz'). So, counting number of pages to read that index plus the number of pages of the lookups (number of occurrance * index depth) to retrive others fields of the ...


8

No, querying things does not write data to the transaction log. The transaction log is the log of changes to your database. When you update an indexed column, the index is updated. The changes this makes to the index are logged in the transaction log the same way that the changes made to the table are logged. This is because if your database was to crash, ...


8

The filtered index isolates the set of rows where L2 = ''. Your query is asking to take that entire subset of rows (the matching rows are subset of the table but also all the rows in the index) and aggregate them with the GROUP BY clause. You would do so by looking at the entire filtered index, or in other words scanning it, especially so since it's covering ...


7

if I have a clustered index on [column_a], [column_b], and [column_c] and run the same query from above, will the data ALWAYS come back sorted based on that order since that's the order that the clustered index was created on? No. SQL Server does not guarantee that it will return data in any order unless you specify the order. It is easy to prove things can ...


5

Columns would be listed in the inequality_columns if the query prompting the missing index request has a range predicate, such as > or NOT. Microsoft Docs says this about the inequality_columns column in sys.dm_db_missing_index_details: Comma-separated list of columns that contribute to inequality predicates, for example, predicates of the form: table....


4

FYI, here is Brent's support page for sp_blitzIndex. Scripts like Brent's are based on SQL's built-in "dynamic management views" (DMVs), such as sys.dm_db_index_usage_stats, so let's look at that documentation page: The counters are initialized to empty whenever the database engine is started. Use the sqlserver_start_time column in sys....


4

Goal is to put together both columns with a length of MAX and an index That is ambiguous. If you mean that the table structure changes such that there are only two colums (ID, textvalue+longtextvalue) - then the index could only be keyed on ID and include textvalue+longtextvalue. If the table structure isn't changing then same concept but index could be ...


3

predicates Equality columns: = IS NULL INTERSECT EXCEPT Inequality columns: >= <= > < != and <> IS NOT NULL BETWEEN LIKE


3

Now when there is an insert of value 9, then 9 added either to page2 (page split) or to a new page? One or the other. It doesn't really matter. Usually the splitting value isn't right in the middle and it must go on the old page or must go on the new page. Page2: 6, 7, 8, Page4: 9, 10, 11 Page3: 12, 13, 14 How does this result in fragmentation? Is it ...


3

In addition to MBuschi's answer, if it isn't also clear, but a Scan operation literally iterates through every item in the data structure (e.g. if it's an Index Scan then every item in the underlying B-Tree is scanned), so ordering in the index (for the most part) doesn't matter anymore. Scanning the entire B-Tree of the index is essentially the same (or ...


2

For wide character columns your best bet is to calculate a checksum and add an index. Then calculate the checksum of what you are searching for and add that to your query, it looks like this: -- add a persisted computed column, we will calculate a checksum over the body column which is of type nvarchar(max) alter table dbo.posts add chksum_body as checksum(...


2

One way: generate all sub-domains, top-level down, and take the first match: WITH cte AS ( SELECT ord, string_agg(label, '.') OVER (ORDER BY ord ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS subdomain FROM unnest(string_to_array('bla.bla.dd.cc.360.cn', '.')) WITH ORDINALITY AS x(label, ord) ORDER BY ord DESC OFFSET 1 -- skip tld ) ...


2

SELECT * is generally bad even if you really need all or most of the columns, it is even worse if you only need some small number that could be covered by the index Depending on the optimization the subquery itself might be evaluated many times - once per main table row. But it might be optimized by joining or by materializing too. Execution plan can show ...


2

If I get this right, You start with a set of contract id's to collect the set of group id's that correspond to them. Then you want to collect all the rows that have a group id that is in that set. By definition, this means the DBMS must do a table scan. Your best chances are to create a second index in which group id is the first attribute (e.g. group id, ...


2

Covering; yes, "A". Useful; it depends. The effective secondary index will be (c,a,b), in that order. That is "covering" for a SELECT that contains only a,b,c in any combination and any arrangement. But that index may not otherwise useful. WHERE c=32 -- the secondary index will probably be used, even if not covering WHERE a=12 -- ...


2

Contrary to wide-spread belief, it does not matter if you put columns with many different values first in the index or not. What matters is that you put those columns first that are used with the = operator in the WHERE clause. If all columns are compared with = (and there are no ORs in the WHERE clause), the order does not matter for the performance of that ...


1

Plan A - Chunking UPDATEing 250K rows takes a long time, regardless of indexing, etc. This is because the Engine hands on to the old values of each of the 250K rows in case you do ROLLBACK or the server crashes. This allows the UPDATE to be atomic. You want it "faster"? Why? Is it because you want it to finish sooner; if so, why? Or, more ...


1

If the condition col1 = 'bar' matches 250k rows from 1M, that's 25% of your table, that means the update will modify almost all pages of the table, because almost every page will contain at least one row matching the condition (unless there is some direct connection between id and col1 but the optimizer would not know anyway). And when it has to rewrite ...


1

NULL values are stored in an index just like all other values. If you want an index to speed up queries on the columns where the value is not NULL, you can use a partial index that will only index the rows that satisfy a condition: CREATE INDEX ON tab (...) WHERE somecol IS NOT NULL; The advantages are: this will work with queries that have somecol IS NOT ...


1

It depends on if and how you defragment this index. (Which might not be something you want to do in the first place, but that is a different discussion.) The FF value doesn't affect the modifications in any way, the pages will fill up as you do inserts and this will not be limited by the specified FF value (that would make FF useless). ALTER INDEX REORAGINZE ...


1

This "composite" index should help: replies: INDEX(repliable_type, repliable_id, created_at) (And DROP INDEX repliable_type_repliable_id since it is now in the way. This may be why you are not seeing an improvement.) Since that one seems to be giving you trouble, DROP any indexes starting with reliable_id, then ADD INDEX(reliable_id, ...


1

If there is something else in the WHERE clause, such as WHERE state = 2 AND foo = "blah" Then add a composite index INDEX(state, foo) Then the "cardinality" question applies to the combination of those two columns; the index is more likely to be used. At the same time, drop the existing INDEX(state) as being useless; even 'in the way'.


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