31 votes

SQL Server has encountered occurences of I/O requests taking longer than 15 seconds

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 ...
user avatar
  • 34.2k
30 votes

Why does adding a TOP 1 dramatically worsen performance?

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, ...
user avatar
30 votes
Accepted

Warning about memory "Excessive Grant" in the query plan - how to find out what is causing it?

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 ...
user avatar
  • 34.2k
29 votes
Accepted

Why does adding a TOP 1 dramatically worsen performance?

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 ...
user avatar
  • 4,975
29 votes

Why does adding a TOP 1 dramatically worsen performance?

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 ...
user avatar
26 votes
Accepted

Latin1_General_BIN performance impact when changing the database default collation

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 ...
user avatar
  • 72.4k
22 votes
Accepted

Why do SQL Server row estimates change when I add a join hint?

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 ...
user avatar
  • 72.4k
20 votes
Accepted

Do triggers compile each time?

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 ...
user avatar
20 votes
Accepted

Is OPTION (RECOMPILE) used in production environments?

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 ...
user avatar
  • 24.5k
19 votes
Accepted

SQL Server has encountered occurences of I/O requests taking longer than 15 seconds

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 ...
user avatar
  • 2,204
18 votes

Setting shared_buffers in postgresql.conf does not seem to take effect

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 ...
user avatar
17 votes

Statistics: CPU Time vs Elapsed Time. What is more important?

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 (...
user avatar
  • 67.5k
16 votes

Should the index on an identity column be nonclustered?

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 ...
user avatar
15 votes
Accepted

'SELECT TOP' performance question

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 ...
user avatar
  • 41.4k
14 votes
Accepted

Cardinality Estimation for >= and > for intra step statistics value

The only difficulty is in deciding how to handle the histogram step(s) partially covered by the query predicate interval. Whole histogram steps covered by the predicate range are trivial as noted in ...
user avatar
  • 72.4k
14 votes
Accepted

Huge "Network I/O" type resource waits

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 ...
user avatar
  • 34.2k
14 votes
Accepted

SQL Relationship between Subtree Cost and Performance Time

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 ...
user avatar
  • 30.7k
14 votes
Accepted

Why is it not recommended to use RAID 5 for a log file?

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. ...
user avatar
  • 22k
13 votes

Do triggers compile each time?

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 ...
user avatar
12 votes

Optimizing queries for 25+ million rows

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 ...
user avatar
  • 72.4k
12 votes

Is OPTION (RECOMPILE) used in production environments?

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 ...
user avatar
11 votes

Should the index on an identity column be nonclustered?

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 ...
user avatar
  • 72.4k
11 votes
Accepted

PostgreSQL doesn't use all the RAM it could

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 ...
user avatar
  • 52.5k
11 votes
Accepted

How to tune the query on all_constraints in Oracle 12c?

Often the RULE-hint helps when querying dictionary views. select /*+ RULE */ constraint_name,table_name from all_constraints where r_constraint_name in ...
user avatar
  • 7,471
11 votes

Effectively handle 10-100 millions row table of unrelated data

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)::...
user avatar
  • 56.7k
11 votes
Accepted

What is the algorithm behind the EXCEPT operator?

What is the internal algorithm of how the Except operator works under the covers in SQL Server? I wouldn't say that there's a special internal algorithm for EXCEPT. For A EXCEPT B, the engine takes ...
user avatar
  • 30.7k
11 votes

Converted a Scalar function to a TVF function for parallel execution-Still running in Serial mode

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 ...
user avatar
  • 34.2k
11 votes
Accepted

Disable top (n) Sort Optimization for a specific view

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 ...
user avatar
  • 72.4k
10 votes

Cannot tune database any further; what next?

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 ...
user avatar
  • 10.2k

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