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 ...
answered Oct 16 at 18:26
David Browne - Microsoft
36.2k3 gold badges38 silver badges79 bronze badges
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