7
votes
Accepted
STATISTICS IO for parallel index scan
If you add a TABLOCK or READUNCOMMITTED hint to the table, you will get an allocation-ordered scan, which will report the same number of logical reads as the number of pages in the table.
With an ...
- 90.3k
7
votes
Accepted
Number of full table scans
Well, that table will give you the answer you are looking for. Just join onto sys.indexes to determine the type of index. Tables are either Heaps or Clustered indexes, so a scan on that index type ...
- 9,244
4
votes
Accepted
Delete cascade produce scan intead of index seek
You may want to look in the table sys.foreign_keys for the foreign key joining the tables, and see if the index has the value is_not_trusted = 1.
This query, originally from BrentOzar.com, will show ...
- 2,058
4
votes
How does a column-oriented DBMS filter on multiple columns?
There are a few factors which reduce the horror of a full scan of the shop column.
The values of each column can be stored in the same order: the first value of timestamp corresponds to the first ...
- 25.1k
3
votes
PostgreSQL select from table (without ORDER BY) comes out in different order every time
Why it does it is probably due to synchronize_seqscans. Now go have fun shooting yourself in the feet with even more vigor.
- 41.3k
3
votes
How does a column-oriented DBMS filter on multiple columns?
Columns-organized storage engines are vastly different in implementation, so a general answer to your question is hardly possible. At a very basic level though a DBMS with a modestly advanced query ...
- 26.9k
2
votes
Accepted
Vulnerability in Server from SQL Server 2008 R2
The web components are/were used for the cube browser in SSMS (That changed in SQL Server 2012 IIRC), so you could probably remove it if you are not using that functionality and only browse the cubes ...
- 15.7k
2
votes
Accepted
Why Are Table Scans Bad?
Table scans are not evil per se, it depends on what the query is supposed to do. If a large portion of the table is either returned to the application or used in some aggregate (like sum), it is ...
- 23.6k
1
vote
When is a regular index scan faster than a bitmap index + heap scan in Postgres?
The advantage for a bitmap scan is:
every heap page is only fetched once, while an index scan may need to
read a heap page multiple times
If effective_cache_size is large compared to the number of ...
- 41.3k
1
vote
Accepted
How do I bulk export data from an RDBMS without killing performance?
The comments above indicate that several of those who read your question are skeptical that a single query could cause such a problem on a healthy database server, even if the query has a large result ...
- 16k
1
vote
PostgreSQL select from table (without ORDER BY) comes out in different order every time
No, the answer is a definite NO.
There is only one way to ensure consistent and repeated order. Using ORDER BY.
- 98.6k
1
vote
Accepted
How to connect to Oracle RAC from HOST
If you have a DNS then the idea is to configure three IP address in round robin system on the DNS server. If Oracle client wants to connect by using the scan DNS name he will get a list of the three ...
- 7,752
1
vote
How to connect to Oracle RAC from HOST
IMHO you will need DNS.
Oracle client first performs DNS lookup for SCAN address. It will get all 3 IPs in random order. It will pick 1st one. (PS: since some version Oracle client does not use OS ...
- 1,564
1
vote
Accepted
How does MySQL skip ranges (nodes) in a loose index scan?
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, ...
- 79.4k
1
vote
Accepted
MSSQL Join between big and empty tables
I was finally able to replicate your issue due to you noting that
LEFT JOIN Table_C c ON c.Id_B = b.Id AND c.Id_B IS NOT NULL
improved the query.
DDL & DML
CREATE TABLE Table_A (id int not ...
- 16.5k
1
vote
Very high (99%) insertion cost in estimated execution plan
The high relative cost for the insert is a result of the cost-based model used by the optimizer. In particular, it looks like SQL Server assigns a much higher I/O cost to inserts into a temp table ...
- 29.8k
Only top scored, non community-wiki answers of a minimum length are eligible