67
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
The best way to do is -- use coreinfo (utility by sysinternals) as this will give you
a. Logical to Physical Processor Map
b. Logical Processor to Socket Map
c. Logical Processor to NUMA Node Map as below :
Logical to Physical Processor Map:
**---------------------- Physical Processor 0 (Hyperthreaded)
--**-------------------- Physical Processor 1 (...
47
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 = ...
34
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 ...
31
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 ...
29
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 ...
27
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 ...
20
There isn't a way to prevent this. Microsoft feels that it is better to prevent you from shooting yourself in the foot, probably since the number of people out there who create databases, objects or columns with bad names (e.g. my table or date) or reserved keywords (e.g. log or timestamp) outnumber those who never do so.
There was a Connect item asking for ...
20
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 '...
18
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 ...
17
The key words here are:
"heavily updated"
"in the table for 2-3 hours".
Point 1. is indication for a lower fill factor, while 2. is the opposite. It helps performance if multiple row versions are stored on the same data page. H.O.T. updates would achieve that. Read here or here. They need some wiggle room on the data page - like dead tuples or space ...
17
When setting MAXDOP you typically want to limit it to the number of cores in a NUMA node. That way schedules aren't trying to access memory across numa nodes.
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
57.3k11 gold badges110 silver badges143 bronze badges
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
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
15
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
...
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
...
14
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
57.3k11 gold badges110 silver badges143 bronze badges
14
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 ...
14
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
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
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 datases 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 ...
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
In general, no. A tnsnames.ora change shouldn't require a reboot but some applications will read and parse the tnsnames.ora at startup to be able to present a drop-down list of servers to the user, for example, and will cache whatever was read when the application started up rather than re-reading the file. Depending on the situation, it might be easiest ...
12
While I agree with Rolando's recommendation to change innodb_flush_method, I wasn't 100% clear what you meant by:
it did not make the actual MySQL setting changed
I want to point out the caveat that making a change to the GLOBAL variable affects any new connections, but does not modify the current session (emphasis mine):
The global variable change ...
12
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
12
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
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
The problem, though, is that you are supplying a numeric value, unquoted, so MySQL tries to cast the other side of the comparison as a DOUBLE, to compare it with the numeric argument you've supplied... which leaves you with a 0 that's equivalent to 0.
mysql> select 'foo' = 0;
+-----------+
| 'foo' = 0 |
+-----------+
| 1 |
+-----------+
1 row in ...
Only top voted, non community-wiki answers of a minimum length are eligible
