2022 Developer Survey is open! Take survey.
69 votes

SQL Server Error 8632 due to over 100,000 entries in WHERE clause

To search for more than 100,000 values, put them in a temp table instead, one row per value that you're searching for. Then, join your query to that temp table for filtering. Something with more than ...
user avatar
  • 41.3k
36 votes

Make SqlClient default to ARITHABORT ON

Seemingly preferred approach I was under the impression that the following had been tested already by others, especially based on some of the comments. But my testing shows that these two methods do ...
user avatar
35 votes
Accepted

What are the optimal mysqldump settings?

You are going to find this shocking, but you only need one major option : --opt What is --opt ? This option, enabled by default, is shorthand for the combination of --add-drop-table --add-locks --...
user avatar
35 votes

Which collation should I use for biblical Hebrew?

First: There is no distinction, collation-wise, between biblical Hebrew and modern Hebrew. We are just dealing with Hebrew. Second: Regardless of anything else, you want to use the newest set of ...
user avatar
26 votes

save/export PGAdmin server list configuration settings

Under Windows, pgAdmin4 stores the server list in the users directory in a SQLLite 3 format. Here: %UserProfile%\AppData\Roaming\pgAdmin\pgadmin4.db
user avatar
  • 361
21 votes
Accepted

How do you turn off the croak sound for Toad?

Issue: You are experiencing a noise / croak sound when TOAD starts up. Solution: http://www.odipundits.com/2017/01/how-to-get-rid-of-croak-sound-at-toad.html View in the top toolbar -> Toad Options ...
user avatar
  • 3,012
20 votes
Accepted

Query for all the Postgres configuration parameters‘ current values?

SHOW ALL The SHOW ALL command displays the current setting of run-time parameters in 3 columns. SHOW ALL ; pg_settings The pg_settings view shows the same items as SHOW ALL but with additional ...
user avatar
19 votes
Accepted

Checkpoints are occurring too frequently during pg_restore

Not uncommon during a whole-DB restore because that's an exceptionally huge operation. If you see this during normal operation, consider raising your setting for checkpoint_segments permanently, just ...
user avatar
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
Accepted

Understanding "max_wal_size" and "min_wal_size" parameters default values from postgresql.conf file

These are 16 MB WAL segments by default. The manual: The system physically divides this sequence into WAL segment files, which are normally 16MB apiece (although the segment size can be altered ...
user avatar
16 votes
Accepted

Postgresql: set default psql client encoding

Is there a way to permanently configure this setting, either in the .pgpass file or anywhere else Yes there is: it's ~/.psqlrc (or %APPDATA%\postgresql\psqlrc.conf in Windows) See the manual for ...
user avatar
16 votes
Accepted

Use Dark Theme on SSMS Results Grid, Object Explorer and other windows

These options are ignored as of SQL Server 2005 SP2 and still not fixed. See the relevant connect item: SP2: Results Grid Background color property ignored by Management Studio. archive link The ...
user avatar
  • 15.4k
15 votes
Accepted

Listing SQL Server instance startup parameters

In SQL Server 2008 R2 SP1 or later, this is made considerably easier via the sys.dm_server_registry DMV: SELECT DSR.registry_key, DSR.value_name, DSR.value_data FROM sys....
user avatar
  • 69.4k
15 votes

Limit max CPU usage SQL SERVER with WSRM

Is there any reason NOT to use the approach you've defined? Absolutely. Imagine you had bought a car - a car that when you hit 50MPH the engine starts to overheat. Would your reaction to this ...
user avatar
14 votes

MAXDOP setting algorithm for SQL Server

Looking at a post from the MSDN team, I've come up with a way to reliably get the physical core count from a machine, and use that to determine a good MAXDOP setting. By "good", I mean conservative. ...
user avatar
  • 67.1k
13 votes

save/export PGAdmin server list configuration settings

In Ubuntu 13.10 pgAdmin stores configuration in user's home directory: /home/<<username>>/.pgadmin3 or shorter ~/.pgadmin3 Note that this file is hidden, so you may need to enable some ...
user avatar
13 votes
Accepted

MySQL InnoDB page_cleaner settings might not be optimal

The innodb_page_cleaners default value was changed from 1 to 4 in MySQL 5.7.8. If the number of page cleaner threads exceeds the number of buffer pool instances, innodb_page_cleaners is automatically ...
user avatar
  • 146
13 votes
Accepted

MAXDOP Settings for SQL Server 2014

Bogus Here's why that wait stats report stinks: It doesn't tell you how long the server has been up. I can see it in your screenshot of CPU time: 55 days! Alright, so let's do some math. Math ...
user avatar
  • 33.4k
12 votes

Setting a SQL Server instance back to multi user mode

First thing, never set Max Number of Concurrent Connections on the server properties page to 1. This option can even prevent administrators logging in, but in any case the Dedicated Administrator ...
user avatar
12 votes

SQL Server max memory, page file, max degree parallelism

Your 3 questions touch max memory, page file and max dop setting. MAX MEMORY If the server is dedicated SQL Server only without Analysis services (since SSAS is another beast), then Glenn's ...
user avatar
  • 61.1k
12 votes
Accepted

Will Changing the Max Memory Setting Invalidate Query Plans?

Per Microsoft documentation changing the max server memory will clear the plan cache, as will changing: access check cache bucket count access check cache quota clr enabled cost threshold for ...
user avatar
  • 3,943
12 votes

Limit max CPU usage SQL SERVER with WSRM

Erik Darling mentioned the biggest practical reason for not using WSRM in a comment on your question: ...there's no reciprocal limiting of CPU use in other processes. SQL Server may not use those ...
user avatar
  • 27.1k
11 votes

Restarting database server or just the database

You can cycle the database by doing: alter database YourDatabase set offline with rollback immediate; -- this line will rollback ongoing transactions go And then to bring the database back online ...
user avatar
11 votes
Accepted

Moving DB Log destination?

Please don't detach and attach your database. If you detach it and something happens to it, you now have zero copies of your database. Much safer approaches: Backup/Restore USE master; GO BACKUP ...
user avatar
11 votes
Accepted

How to disable the logging of parameter values in PostgreSQL

You can set log_error_verbosity = TERSE The manual: Controls the amount of detail written in the server log for each message that is logged. Valid values are TERSE, DEFAULT, and VERBOSE, each ...
user avatar
11 votes
Accepted

How to define custom path to MySQL socket file?

Your error message says it's connecting to the wrong socket: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) You can either specify that in ...
user avatar
  • 2,890
11 votes
Accepted

Postgres seems to ignore listen_addresses setting

Look at the postgresql.auto.conf file in your $PGDATA. If someone uses the ALTER SYSTEM command to change the settings, you will find the value there. postgresql.auto.conf is read after postgresql....
user avatar
  • 1,325
11 votes

SQL Server Error 8632 due to over 100,000 entries in WHERE clause

If you're going to change the app anyway, consider either (a) using a TVP for the entire set of values - you would create a DataTable in C# and pass it into a stored procedure using StructuredType, ...
user avatar
11 votes
Accepted

How can I disable parallel queries in PostgreSQL?

From the docs, max_parallel_workers_per_gather (integer) Sets the maximum number of workers that can be started by a single Gather or Gather Merge node. Parallel workers are taken from the pool of ...
user avatar
  • 55.8k
11 votes
Accepted

What are the ramifications of setting ARITHABORT ON for all connections in SQL Server?

There are some defaults that exist merely because nobody really knows what the effect of changing them would be. For example, the default instance-level collation when installing on a system that uses ...
user avatar

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