68
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 100,000 values isn't a parameter - it's a table. Rather than thinking about raising the limit, consider Swart's Ten Percent Rule: if you're approaching 10% of ...
53
You can find configuration settings for roles and databases in the catalog table pg_db_role_setting.
This query retrieves any settings for a given role or database:
SELECT r.rolname, d.datname, rs.setconfig
FROM pg_db_role_setting rs
LEFT JOIN pg_roles r ON r.oid = rs.setrole
LEFT JOIN pg_database d ON d.oid = rs.setdatabase
WHERE r.rolname = ...
35
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 collations, which are the _100_ series as they have newer / more complete sort weights and linguistic rules than the older series with no version number in the name ...
34
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 --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It gives a fast dump operation and produces a dump file that can be ...
32
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 indeed work at the DB level, even when connecting via .NET SqlClient. These have been tested and verified by others.
Server-wide
You can set the user options ...
28
Your understanding is correct. It does get a little confusing.
Kim Tripp (one of the programmers of SQL Server and a integral part of SQLSkills) goes through exactly what you stated in the MCM videos on Snapshot Isolation. Fast fwd to 41:45 in the video to get to the part where she answers your question.
If you use ALLOW_SNAPSHOT_ISOLATION make sure you ...
25
If the MySQL Debian-7 minimal cannot use local_infile, look around all the make files used for compiling to see if it is disabled by default or if local_infile is enabled for the Debian-7.
Before taking that kind of time, please run the following:
SHOW GLOBAL VARIABLES LIKE 'local_infile';
SET GLOBAL local_infile = 'ON';
SHOW GLOBAL VARIABLES LIKE '...
23
Under Windows, pgAdmin4 stores the server list in the users directory in a SQLLite 3 format. Here:
%your-user-home%\AppData\Roaming\pgAdmin\pgadmin4.db
19
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 like the error message hints.
You might go to the trouble of setting checkpoint_segments higher just before the restore and then lower it again. This is even ...
16
Under Windows this information is stored in the Registry under the key:
HKEY_CURRENT_USER\Software\pgAdmin III
In Linux systems I would expect some file in the user home directory.
answered Nov 20 '13 at 15:57
a_horse_with_no_name
63.8k12 gold badges122 silver badges161 bronze badges
16
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 when building PostgreSQL)
So, it's just default values as advertised:
select name, setting, setting::int * 16 || 'MB' AS setting_in_mb
from pg_settings
...
16
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 from the list.
Click on Startup in the left pane after scrolling down a ways.
Uncheck the box Play Toad wave file and then click ok. Click apply instead if you ...
15
OK, went back home and tested. Here is the observation.
CREATE DATABASE TEST;
GO
CREATE TABLE TABLE1
(
ID tinyint,
Details varchar(10)
);
GO
INSERT INTO TABLE1
VALUES (1, 'Original');
GO
SELECT
name,
snapshot_isolation_state_desc,
is_read_committed_snapshot_on
FROM sys.databases
WHERE name = 'TEST';
GO
First test with both settings ...
15
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 details: http://www.postgresql.org/docs/current/static/app-psql.html#AEN88713
answered Jul 15 '14 at 21:19
a_horse_with_no_name
63.8k12 gold badges122 silver badges161 bronze badges
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
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 status is closed as fixed, but only because it's been converted into an internal issue
Thanks for this feedback. I have converted this request to either a
...
15
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 situation be to artificially limit the car to 49MPH, or to find out what the fault is with the engine?
Why should you limit your car to 49MPH? The manufacturer stated ...
13
What do you need to take into account when migrating SQL Server to another domain?
The steps below presume:
IP address will also change
SQL Server is NOT clustered
Steps:
A. Backup:
BEFORE: backup the databases off-machine
B. Services:
BEFORE: depending on the nature of the change/move, you may want to set service start to Manual for all SQL Service
...
13
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. That is, my requirement is to use a maximum of 75% of the cores in a NUMA node, or an overall maximum of 8 cores.
SQL Server 2016 (13.x) SP2 and above, and ...
13
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 kind of "Show hidden files" option in your file manager.
13
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.dm_server_registry AS DSR
WHERE
DSR.registry_key LIKE N'%MSSQLServer\Parameters';
From: An easier way to get SQL Server startup parameters
13
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 set to the same value as innodb_buffer_pool_instances
Check innodb_buffer_pool_instances with:
mysql> SHOW GLOBAL VARIABLES LIKE '...
13
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
There are 86,400 seconds in day.
SELECT (86400 * 55) seconds_in_55_days
The answer there? 4,752,000
You have a total of 452,488 seconds of CXPACKET.
SELECT ...
12
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 recommended best practices is a good starting point. For additional components, you should refer to Jonathan's blog on how to configure max memory.
I have answered on ...
12
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 parallelism
cross db ownership chaining
index create memory
max degree of parallelism
max server memory
max text repl size
max worker threads
min memory per query
...
11
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 Connection can still be used.
Restart services using SQL Server Configuration Manager, try to login and if not successful, use the Dedicated Administrator ...
11
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 you can do:
alter database YourDatabase
set online;
go
This is a way to "restart" the database itself directly within SQL Server, without bouncing either the ...
11
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 DATABASE floob TO DISK = 'c:\wherever\floob.bak' WITH INIT, COPY_ONLY;
ALTER DATABASE floob SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE floob FROM ...
11
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 adding more fields to displayed messages. TERSE excludes the
logging of DETAIL, HINT, QUERY, and CONTEXT error information.
Bold emphasis mine.
11
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 two cores, but other things may use the other 30 SQL Server is using. It's a crapshoot, really.
If this is working for you, then stick with it - we're all busy, ...
Only top voted, non community-wiki answers of a minimum length are eligible
