23
If you have a small-enough number of (StationID, ParameterID) pairs, then try a query like this:
select StationID, ParameterID, m.DateTime LastDate
from StationParameter sp
cross apply
(
select top 1 DateTime
from MyTable
where StationID = sp.StationID
and ParameterID = sp.ParameterID
order by DateTime desc
) m
To enable ...
12
Nonclustered indexes contain a row locator back to the base table.
This is a clustered index key for rowstore tables with a clustered index or a physical RID (file/page/slot) for heaps.
So the parts in your question assuming that it uses the primary key (in the event that these are different) are moot.
The row locator is added to the key for non unique ...
9
Current State
In your "good" case the plan looks as below. The upper branch is sorted by SalesOrderID (and therefore also by SalesOrderID,ProductId as the grouping ensures there is only one row per SalesOrderID). The lower branch reads the index in SalesOrderID,ProductId order and they are merge joined together.
Your "bad" case uses a typical execution ...
7
Also "reducing fragmentation" is not per se a performance goal. On many (most?) modern storage platforms there is little difference between sequential and random IO, which is a major historical reason for defragmenting.
I've worked on systems where the difference in throughput between sequential and random IO was 10x or more. As SQL Server attempts to ...
5
sys.dm_db_index_physical_stats
is a system table valued function.
Internally this will perform an openrowset call to an internal INDEXANALYSIS system data source.
create function [sys].[dm_db_index_physical_stats]
(
@DatabaseId SMALLINT = 0,
@ObjectId INT = 0,
@IndexId INT = -1,
...
3
Trying to get the better performing execution plan structure by adding hints
ImplRestrRemap
The query rule ImplRestrRemap is used on the secondary query when the index is added as a result of it being estimated to be cheaper in query bucks than the merge join. This is more of a consequence of the problem. Why it has a lower subtree cost than using a merge ...
2
Since this topic seems to still be active, I thought it would be worthwhile to note that using the new resumable index operations in SQL Server 2019 and Azure SQL DB (in 150 compatibility mode) provides this functionality. The catalog view sys.index_resumable_operations has a percent_complete column that indicates progress.
In addition to being able to ...
2
It looks like the other suggestions so far all lead back to sys.dm_db_index_physical_stats
This is where you will have to look to get the index stats, but you could look to use the LIMITED option and restrict it to specific indexes at a time.
With the size of your table, I would want to know how quickly fragmentation occurs to help me confirm that the ...
2
Rule of Thumb: A disk hit on HDD takes about 10ms (100 hits/second). SSD access might be 10 times that fast.
"0.1-0.6" on HDD -- perhaps 10 to 60 blocks needed to be fetched. That might be only one for the index, then several for the data. Perhaps there are at least 60 rows in the resultset?
You say that the slow version is "remote". This might add a ...
2
There are two problems
PROBLEM #1 : MyISAM
MyISAM does not cache data (stored in the .MYD) which means data must be read for disk every time. Indexes may be cached (read once from .MYI), but not data.
See my old post in What are the main differences between InnoDB and MyISAM? under the MyISAM subheading
PROBLEM #2 : HDD vs SSD
An HDD must use spindles ...
2
Ola Hallengren has already done the work for you here, no need to roll your own code. You can even cherry pick specific indexes or tables.
https://ola.hallengren.com/
I'd also recommend going ONLINE with those rebuilds if you're on Enterprise Edition, that'll save you locking of the tables for writes.
1
While some RDBMSs might be tuned to interact better with fixed-length strings (I seem to recall that maybe DB2/MVS did better with fixed-length strings, but I learned this in 1996 and it was mainframe DB2, so not sure if that even applied to DB2 on Unix or Windows), I would be shocked if using CHAR(50) did anything but decrease performance as compared to ...
1
If you create one index on each column, you should get a very efficient combined index BitmapOr plan. Try it and see, using EXPLAIN or EXPLAIN ANALYZE or EXPLAIN (ANALYZE, BUFFERS).
If the number of rows returned is large, then you might want to have a large setting of work_mem so that the bitmap can fit in memory and you avoid needing to remove rows by ...
1
You could try splitting it into a union and indexing the two individual columns.
Your query would be
SELECT *
FROM Things
WHERE person_1_id = 123
UNION
SELECT *
FROM Things
WHERE person_2_id = 123
and index person_1_id AND person_2_id
Note we use a UNION rather than a UNION ALL so we don't get superflous records
Disclaimer:
I'm more ...
1
If your table is well-vacuumed, you can get an index only scan by creating the index:
CREATE INDEX ON temp_test(dt,val);
This might be quite a bit faster as it doesn't need to visit the table at all. (In my test, it was about 2.5 times faster than the sort.)
As to why it chooses the wrong plan with the index you already have, it could just be that you ...
1
This is documented behaviour:
MySQL requires indexes on foreign keys and referenced keys so that
foreign key checks can be fast and not require a table scan. In the
referencing table, there must be an index where the foreign key
columns are listed as the first columns in the same order. Such an
index is created on the referencing table ...
1
Yes and no.
An "array" in a programming language does some trivial address arithmetic to quickly locate the Nth entry in the array.
In MySQL, most indexes are built as B+Trees. (See Wikipedia) This structure is more complex than an array, but still the best available. WHERE id=2 requires drilling down a "tree" of nodes to locate the item with "2" in the ...
1
You don't need a separate index for the first column of your primary key (id_of_table_x in your example) because Postgres will happily use that index in every situation where it would use the single-column index on the first column.
If you very often delete rows from the referenced table, then adding another index on the second column helps validating the ...
1
The use of views backed by linked server queries puts you in a pretty tough situation. You get bad cardinality estimates coming out of the "remote query" operators (10k rows estimated, vs. the actuals of 521k and 30k respectively). This results in SQL Server choosing to sort and use a merge join, when it would probably have chosen a hash join had the ...
1
I think the Optimizer works something like this:
It can find the first row: MIN(name) WHERE foo=123
It can find the last row: MAX(name) WHERE foo=123
Those are done via a drill-down in the BTree, and assumed to be reasonably cheap. If there are a lot of rows for foo=123, then this is likely to skip over some blocks. Note: fetching blocks potentially ...
1
All the indexes you mention can in principle speed up the query (relative to no indexes), but not by equal amounts. And it would depend on how many rows meet each individual condition, and how many meet the combined condition.
With a btree index on (a,b), for example, it can jump to the part of the index where A>30, and then scan to the end of the index. ...
1
A multi-column index cannot be used for both conditions. For example, an index on (a, b) could be used for the condition a > 30, but not for b < 20.
If one of the conditions alone is selective enough and the other does not reduce the result set considerably, just create an index for that condition.
If you need index support for both conditions ...
Only top voted, non community-wiki answers of a minimum length are eligible
