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 ...
64
As I answered here, you can add a section for each user/host/db you connect to using the syntax in your ~/.my.cnf:
[clienthost1] # Note: client + host1
user=myuser
password=mypass
database=dbname
host=server.location.com
Once this is in your user's .my.cnf, you can utilize it by doing this on a command line:
$ mysql --defaults-group-suffix=host1
49
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 (...
33
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 = ...
32
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 ...
28
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
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 ...
25
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 ...
18
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 '...
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
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 ...
15
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
47.4k9 gold badges95 silver badges126 bronze badges
14
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 ...
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
You really just have to get creative. As we all know, there are many places that settings are stored, depending on what exactly you're looking to compare. For instance, to compare instance-wide configuration settings, you can simple do an EXCEPT query (you may have to create a linked server, or export/import the data depending on how you want to approach ...
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
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
...
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
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
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
My advice:
Leave 'auto update stats' on (until you run into a very good reason not to) - you don't want a big delete in middle of a day to throw off query plans until the next time you run maintenance.
However, schedule index maintenance/update statistics at a quiet time. sp_updatestats will update all stats for all tables in a database for you, but that ...
11
In a read-heavy, low-write environment on a RAID5, I would just leave that to one's budget, tolerance, and blood pressure.
In a write-heavy, low-read or write-heavy, read-heavy environment, RAID5 is simply out of the question. This is especially true for InnoDB. Think of an InnoDB's table interaction.
InnoDB
If you do not use innodb_file_per_table, OMG ...
11
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 ...
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 ...
11
As a general rule, use higher DOP for an OLAP system, and lower (or no) DOP for an OLTP system. Many systems are somewhere in between, so find a happy medium that allows the occasional large workload to get enough CPU to complete quickly, without strangling your OLTP workloads.
Also, be careful about using the cpu_count column to get a core count. If ...
Only top voted, non community-wiki answers of a minimum length are eligible
