31

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


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

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


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


26

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


23

The most common two types of operators that consume memory are: Sorts Hashes If a plan is parallel, memory requirements will go up some amount to compensate for exchanges for threads to pass rows through. Parallel plans do not require the entire serial memory grant * DOP (though there may be a relationship between serial required memory and DOP). The full ...


22

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


20

OPTION(RECOMPILE) is used in real word production scenarios. I've employed it to address parameter sniffing and optimize kitchen sink queries. It might be the answer for your issue but the symptoms suggest OPTIMIZE FOR UNKNOWN (same as local variables) may address the problem as well. I certainly would not avoid an option just because a bug once existed, and ...


19

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


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


17

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


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

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


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


14

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


14

The subtree cost represents the estimated cost of a plan. It can be useful when investigating why the query optimizer chose one plan over another. For example, you might see a plan with a hash join and think that a loop join would have been a more efficient choice. Adding a query hint to force a loop join and comparing subtree costs can be helpful in ...


14

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


13

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


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


12

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


12

Yes. OPTION RECOMPILE is appropriate for high-cost/low-frequency queries where the query cost varies significantly by parameter values. As an alternative consider using the Query Store, where you can force a good plan.


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

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


11

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


11

Using a simple BRIN index TIAS. Here is a table exactly as you described, worst case 100 million rows with 1 million rows per SEGMENT_ID explain analyze CREATE TABLE foo AS SELECT (x::int%100)::int AS SEGMENT_ID FROM generate_series(1,100e6) AS gs(x); QUERY PLAN ...


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


11

I can't think of a fully transparent way to achieve what you want with views without disabling row goals in general, which probably won't suit your purposes. As the purpose of the view is to provide some business analytics to data analysts, while they are developing reports they use to check a sample of the view by doing a select (top N) query. Perhaps ...


10

You can use the below query to pull currently executing requests and the corresponding session/connection information: select r.session_id, s.login_name, c.client_net_address, s.host_name, s.program_name, st.text from sys.dm_exec_requests r inner join sys.dm_exec_sessions s on r.session_id = s.session_id left join sys....


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