Hot answers tagged

27

Since you get the correct plan with the ORDER BY, maybe you could just roll your own TOP operator? SELECT DOCUMENT_ID, COPIES, REQUESTOR, D_ID, FILE_NUMBER FROM ( SELECT dc.DOCUMENT_ID, dc.COPIES, dc.REQUESTOR, dc.D_ID, cj.FILE_NUMBER, ROW_NUMBER() OVER (ORDER BY cj.FILE_NUMBER) AS _rownum FROM ...


26

Try forcing a hash join* SELECT TOP 1 dc.DOCUMENT_ID, dc.COPIES, dc.REQUESTOR, dc.D_ID, cj.FILE_NUMBER FROM DOCUMENT_QUEUE dc INNER HASH JOIN CORRESPONDENCE_JOURNAL cj ON dc.DOCUMENT_ID = cj.DOCUMENT_ID AND dc.QUEUE_DATE <= GETDATE() AND dc.PRINT_LOCATION = 2 ORDER BY cj.FILE_NUMBER The ...


25

+1 No, I don't mean I'm agreeing with anything, I mean that as a solution. If you change your query to ORDER BY cj.FILE_NUMBER + 1 then the TOP 1 will behave differently. You see, with the small row goal in place for an ordered query, the system will try to consume the data in order, to avoid having a Sort operator. It will also avoid building a hash ...


20

Data alignment and storage size Actually, the overhead per tuple is 24 byte for the tuple header plus 4 byte for the item pointer. More details in the calculation in this related answer: Use GIN to index bit strings Basics of data alignment and padding in this related answer on SO: Calculating and saving space in PostgreSQL We have three columns for ...


18

From reading different articles and books, I assumed that the cardinality estimations are performed before the plan is built. Not exactly. An initial cardinality estimate is derived (after simplifications and other work), which influences the initial join order chosen by the optimizer. However, subsequent explorations (during cost-based optimization) ...


16

There is dynamic SQL, so no cache plans, meaning plans generated every time Not necessarily true. Dynamic SQL can (and does) use cached plans just as well as static SQL. For dynamic search conditions resolving to dynamic SQL is oft the right answer. See Dynamic Search Conditions in T-SQL for more details. There is an INSERT SELECT pattern, so table ...


11

In addition to Craig's advice I would like to advise you to examine the storage parameters of the affected tables. I am currently in a similar situation to yours. The largest table in my system contains ~200 million records and the performance was really bad. Tune the storage parameters of your tables and indexes Besides adding several indexes to the ...


11

To answer your main question directly, the sorts are there to present rows to update operators (performing deletions in this case) in index key order. The principle at work here is that sorting on the keys will promote sequential access to the index. This can be a good optimization, though the details depend on your hardware, how likely the affected pages ...


11

By default the PK is clustered and in most cases, this is fine. However, which question should be asked: should my PK be clustered? which column(s) will be the best key for my clustered index? PK and Clustered index are 2 differences things: PK is a constraint. PK is used to uniquely identify rows, but there is no notion of storage. However by ...


9

Fulltext isn't going to help without refactoring to use the full text functions ( CONTAINS, FREETEXT or their table equivalents ). It also doesn't really work with leading wildcard. Hacks are available, but basically you're going to struggle to write a semantically equivalent query for fulltext. For the future consider redesigning for fulltext which has ...


8

The reason for this behaviour is that rows where LD is NULL cannot be found in the index. Therefore Oracle has to scan the full table. If the table is created with LD as a NOT NULL column then the optimizer uses this information and does an INDEX FAST FULL SCAN. If you add a "CHECK(LD is not null)" constraint to the table that has not NOT NULL defined for ...


8

Collations in SQL Server determine the rules for matching and sorting character data. Normally, you would choose a collation first based on the comparison semantics and sorting order the consumers of the data require. Humans generally do not find that binary collations produce the sorting and comparison behaviours they expect. So, although these offer the ...


8

Given that this is an existing database that already has tables defined in it, there are some very serious implications to the action of changing the database collation, beyond the potential performance impact to DML opertions (which actually was already there). There is very real impact to performance and functionality, and this change not only did not ...


8

For a table with a primary key (PK) on an identity column, it will be clustered by default. Could it be better as nonclustered? If you're asking if the default for a primary key on an identity column (in particular) ought to be nonclustered, I would say no. Most tables benefit from having a clustered index, so making clustered the default for a primary ...


7

Have you seen this? https://community.oracle.com/thread/889338?start=0&tstart=0 It states you can only exchange partitions from a partitioned table to a non-partitioned table, or vice-versa. You'd need to temporarily exchange the partition into an interim, non-partitioned, table, then into the target partitioned table. There are some interesting ...


7

Actually, you do not need a Clustered Index nor a Primary Key to be created, since Unique Indexes and Non-Unique Indexes can handle the work. SQL Server has supported a Clustered Index since at least version 1.1, but the Primary Key was just a "concept" that programmers enforced by defining a unique index. But it seems that both Primary Keys and Clustered ...


7

I don't know of a fully accurate and reliable way to track this. One way to get at least something potentially useful is to keep snapshots of sys.dm_db_index_usage_stats, specifically the user_lookups column for index_id 0 (RID Lookup) and 1 (Key Lookup). This DMV can be reset for many reasons, including database or instance restarts. Rebuilding (but not ...


7

Your query is no faster with the index because SQL Server has determined that it would be more efficient to do a Clustered Index Scan, than use the IX_ActCost_ScenarioID that you have defined and perform a Key Lookup to retrieve the extra data needed. As you've only defined the index on ScenarioID, with no INCLUDE columns, each extra column you wish to ...


7

For insert performance, see speeding up insert performance in PostgreSQL and bulk insert in PostgreSQL. You're wasting your time with JDBC batching for insert. PgJDBC doesn't do anything useful with insert batches, it just runs each statement. Use COPY instead; see PgJDBC batch copy and the CopyManager. As for number of concurrent loaders: Aim for a couple ...


7

CXPACKET is never a cause; it gets all the blame, but it's always a symptom of something else. You need to catch these queries in the act and figure out what "something else" is. It might be different from query to query, and turning off parallelism altogether is - as you've suggested - unnecessary overkill in most cases. But it is often the least amount of ...


7

There is rarely any need, point or benefit trying to micro optimise star schema queries with non-clustered indexes laden with included columns. Fact tables are built to be scanned. The indexes you've created in your examples are subset copies of the parent table, which are being scanned (no seeks). The minor performance improvements come from scanning ...


6

Deferred indexing would be nice, but isn't currently supported. Adding indexes has a cost - write performance. They're a trade-off. COPY won't help much if index maintenance is the main issue. The simplest solution is to drop the indexes, and re-create them when you're done importing. Since you can live with losing all your data if the DB crashes, you ...


6

Often the RULE-hint helps when querying dictionary views. select /*+ RULE */ constraint_name,table_name from all_constraints where r_constraint_name in (select constraint_name from all_constraints where table_name='SUPPLIER'); But ...


6

Just call pg_database_size(dbname) to know the size of the database. VACUUM (without the FULL clause) does not free any space, it only marks it as reusable, and thus will not change the database's size (except in a rare boundary case, see Routine Vacuuming). ANALYZE does statistical sampling and would be useful if you needed the row counts, but for the ...


6

The indexes that were created and dropped by DTA were most likely hypothetical indexes - ones that DTA creates while running to perform its analysis and should (but doesn't always) delete once done. These hypothetical indexes can be created even if you weren't looking at tuning that specific table! I'd recommend you query some of the system DMVs directly ...


6

While I'm not convinced this is a problem with the query itself (did you check for blocking when it runs slow? did you check the wait type(s) occurring while it was running), IN and OR can be a problematic pattern to optimize for. Have you considered breaking this into multiple statements? UPDATE dbo.VillageSemaphoreset SET [TimeStamp] = GETDATE() -- ...


6

PostgreSQL relies on the operating system's disk cache for most caching. This cache is usually reported as "free" RAM by most tools, because modern operating systems use all but a little bit of the currently-free RAM for disk cache. This is normal. To confirm, use a better tool that shows buffers/cache separately from truly free memory. On Linux, free -m ...


5

You can use sys.dm_exec_sessions. This will only give you the count based on the current activity. You would need to store this historically if you wanted to trend it. Keep in mind that session ids of 50 or less are system sessions. --By Database select db_name(database_id) DatabaseName ,count(session_id) as Session_count from sys.dm_exec_sessions ...


5

To use the DAC in Express, you need to enable startup trace flag 7806. This is documented here and in more detail here. Open Configuration Manager, in your SQL Server Services pane right-click the Express instance and select Properties. On the Startup Parameters tab add -t7806, hit Add, then OK, and restart the service. You can check the error log or ...


5

If you look closely at SpBlitz memory recommendations it says it has two possible meanings Memory Dangerously Low or Max Memory Too High In your case looking at perfmon counters output you have shared their does not seems to be a memory pressure. So we are left with the fact that max server memory is configured incorrectly. And this seems true, on a ...



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