30

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 ...


29

Edit: +1 works in this situation because it turns out that FILE_NUMBER is a zero-padded string version of an integer. A better solution here for strings is to append '' (the empty string), as appending a value can affect order, or for numbers to add something which is a constant but contains a non-deterministic function, such as sign(rand()+1). The idea of '...


28

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 ...


24

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 ...


24

This is far less often a disk issue, and far more often a networking issue. You know, the N in SAN? If you go to your SAN team and start talking about the disks being slow, they're gonna show you a fancy graph with 0 millisecond latency on it and then point a stapler at you. Instead, ask them about the network path to the SAN. Get speeds, if it's ...


21

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) can, ...


20

The XE event being used is leading you incorrectly to think the trigger is actually compiling every execution. There are two extended events query_pre_execution_showplan and query_post_compilation_showplan that have similar descriptions, but differ by one important word: query_pre_execution_showplan Occurs after a SQL statement is compiled. This event ...


17

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 ...


16

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 ...


16

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 default (...


15

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 ...


15

SQL Server builds different execution plans for TOP 100, using a different sort algorithm. Sometimes it's faster, sometimes it's slower. For simpler examples of it, read How Much Can One Row Change A Query Plan? Part 1 and Part 2. For in-depth technical details, plus an example of where the TOP 100 algorithm is actually slower, read Paul White's Sorting, ...


15

We have a similar setup and recently encountered these messages in the logs. We are using a DELL Compellent SAN. Here are some things to check when receiving these messages that helped us find a solution Review your windows performance counters for your disks that the warning messages are pointing to, specifically: Disk avg. read time Disk avg. write time ...


13

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. <-- This is no longer true in newer PgJDBC versions, which can now batch prepared statements to reduce round-...


13

CPU Time is the quantity of processor time taken by the process. This does not indicate duration. "Elapsed Time" represents the total duration of the task. If a given task uses a parallelism of 8 (i.e. 8 threads), and each thread is used at a rate of 100% over the entire duration of the task, CPU time could be 8000ms, while Elapsed Time would only be ...


13

No. Triggers are not always recompiled. Simple query statements, however, do not get their plans cached and would therefore always be recompiled. Triggers do get recompiled if the number of rows in inserted or deleted changes significantly. See: https://technet.microsoft.com/en-us/library/ms181055.aspx I don't know if they have the same in XEvents, but in ...


13

Transaction log writes, when they occur, are synchronous operations, that is, the activity that has caused a log write must wait until log I/O completes before continuing to do whatever it is doing. As a result log writes are very sensitive to the write throughput of the underlying storage. As you have mentioned, every write to a RAID-5 device has an ...


11

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 ...


11

The question is mainly about how to optimize the select statement: SELECT [TABLE], [FIELD], [AFTER], [DATE] FROM mytbl WITH (NOLOCK) WHERE [TABLE] = 'OTB' AND [FIELD] = 'STATUS' Removing the redundant projections and adding the presumed dbo schema: SELECT [AFTER], [DATE] FROM dbo.mytbl WITH (NOLOCK) WHERE [TABLE] = 'OTB' AND FIELD = 'STATUS'; Without an ...


11

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 key ...


11

Forrest is mostly right, but the finer details are: SQL Server can't parallelize modifications to table variables, which your function uses. Prior to SQL Server 2017's Interleaved Execution, row estimates from Multi-Statement Table Valued Functions were very low. One side effect of this is that plans were costed very poorly on the low end, and often ...


10

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 ...


10

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 ...


10

The whole point of using DATE as a type is so the database can efficiently query the data. It's the same reason you store a number as an INT and not a VARCHAR - so the engine can make intelligent decisions. If you use the LIKE operator on a date, you lose the benefits of having chosen the correct data type. Using MONTH(birthday) allows MySQL to grab the ...


10

The canonical unit for shared_buffers is pages of 8kB, so the actual memory allocated in bytes is: 524288 * 8192 = 4294967296 or 4096*1024*1024 as requested. You can also check the size of the segment of memory with ipcs -m


10

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 ...


10

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 ...


10

As you are specifically interested in locking rather than general waits the locks_lock_waits extended event sounds more suitable. With a filter on increment >= 200 CREATE EVENT SESSION [locks_lock_waits] ON SERVER ADD EVENT sqlserver.locks_lock_waits( ACTION(sqlserver.sql_text) WHERE ( [sqlserver].[is_system] = 0 ...


10

Stop using Activity Monitor Use sp_WhoIsActive to figure out what's actually going on It could be backups, in which case there's not a whole heck of a lot you can do that doesn't involve hardware upgrades (maybe that 1 Gb iSCSI wasn't such a great idea...) It could be client-side code consuming data RBAR (think foreach loops for every row coming in), or ...


10

First of all, it surprises me that the actual number of rows for both queries from SQL Sentry isn't more or less the same. Second. It's hard to tell how correct your estimates are in the plan with a cursor without an actual plan but some things stand out to me. (P.S.: refer to my answer here to get an actual plan). That being said, there are a couple of ...


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