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 ...
- 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 ...
- 65.6k
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 --...
- 173k
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 ...
- 65.6k
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
- 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 ...
- 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 ...
- 8,996
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 ...
- 151k
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 ...
- 27.1k
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 ...
- 151k
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 ...
- 70.7k
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 ...
- 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....
- 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 ...
- 5,408
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. ...
- 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 ...
- 343
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 ...
- 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
...
- 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 ...
- 2,749
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 ...
- 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 ...
- 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 ...
- 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 ...
- 41.3k
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 ...
- 176k
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 ...
- 151k
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 ...
- 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....
- 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, ...
- 176k
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 ...
- 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 ...
- 65.6k
Only top scored, non community-wiki answers of a minimum length are eligible