Hot answers tagged

7

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 allocation-ordered scan, SQL Server uses allocation structures to drive the distribution of pages among threads. IAM page accesses are not counted in STATISTICS IO....


7

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 is a scan on the table. See the example below, add any additional columns you are looking for. SELECT S.name AS SchemaName , O.name AS ObjectName , I....


4

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 you which foreign keys in your system are not trusted: SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS keyname from sys.foreign_keys i INNER ...


2

Listener is a process running in the Oracle Server machine. The responsibility of the listener is to listen for the incoming database connection request and spawning server process to handle the request(in Dedicated Server mode) or handing over to dispatcher process(in Shared-Server mode). In RAC architecture we have SCAN listener to redirect connection ...


2

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 from other clients, but that's an untested configuration so who knows for sure. It will definitely be unsupported. As you state: Can we remove that ...


2

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 probably most efficient to do a table scan. If on the other hand, a small percentage of the table is to be used, looking up the rows via an index is much more ...


2

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 value in shop and to the first in product; the second to the second to the second, and so on. So a fast lookup that gives the offsets for the start and end of the ...


1

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 optimizer would implement something like this: Values of each column are stored together (as you have mentioned). Each value has some sort of a pointer (think ...


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

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 null, constraint ClusteredIndex_A primary key (id)) INSERT INTO dbo.Table_A(id) SELECT top(50000) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) ...


1

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 compared to reading rows from a heap or clustered index. The percentages you see are purely based on these cost estimates. As a simplified example, check out ...


1

You can avoid the bitmap scan by set enable_bitmapscan to off. Then you can see what it chooses instead, and how that choice performs. This is generally an exploratory tool, it is usually not a good idea to set it this way globally in a production system. But you can set just in one session, so there is no problem with doing the test on a production ...


1

Looks like the JDBC 11g NAT+RAC issue. JDBC Connections Using SCAN Fail With ORA-12516/ORA-12520 (Doc ID 1555793.1) JDBC thin 11g connections using Single Client Access Name (SCAN) fail with errors: ORA-12520: TNS:listener could not find available handler for requested type of server ORA-12516: TNS : listener could not find instance with ...


1

I found PDF about Oracle Single Client Access Name (SCAN) Using SCAN with Multiple Ports on the Same Subnet For the purpose of allowing applications to maintain a distinguishable connect string on host / port level using one SCAN as the host or to enable port-based firewall configurations, SCAN can be set up to support more than one port for the same ...


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