Hot answers tagged

26

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


26

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 ...


14

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 ...


11

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 ...


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

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 ...


10

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 ...


10

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 ...


10

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.


10

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 ...


9

I want to start by saying everything I do is SQL Server so those are the examples I give. In general however this applies to any form of code regardless of system. Let's start by breaking this down a bit. Upgrades You have a system and are about to upgrade some or all of it. For example upgrading an instance from SQL Server 2012 to 2014. At this point ...


9

If your queries have a common shape, you might be able to add the required maxrecursion hint using one or more plan guides. There can be a knack to getting them right. If you add specific query details to your question, we might be able to work that out for you. Typically, you would trace the SQL actually hitting the server, or obtain a parameterized form ...


8

If you have absolutely have to use a function (a limitation of your ETL tool as you imply), you can specify OPTION as part of a multi-statement table-valued function, eg something like this: CREATE FUNCTION dbo.udf_MyFunction ( @StartID INT ) RETURNS @tv TABLE ( id INT ) AS BEGIN WITH Episodes( xlevel, PersonID, EventID, EpisodeID, StartDT, EndDT ) AS ...


8

If you are asking whether it is possible to enforce the database defaults for every connection that ever happens on the server, then no. Those defaults only take effect if something contradictory isn't set at the connection level. In any individual batch in SSMS you can use statements like SET ANSI_NULLS ON, SET ANSI_NULLS OFF, SET QUOTED_IDENTIFIER ON, and ...


8

With spinning-platter disks you want to have the logs and data on separate drives as random access data disrupts the sequential log write operations, making the logs a performance bottleneck. SSDs do not have this issue as they lack the performance constraints imposed by the mechanical action of conventional hard disks. If you're getting SSDs for a DB ...


8

Speaking from experience which is humble but I think worth sharing, the major bottleneck with SQL databases (Sybase and SQL server here) is storage. But I think it's only fair that someone first benchmarks their setup before making any wrong assumptions. In my case, CPU usage has never risen high enough to justify upgrading the CPU any time soon. Instead, I ...


8

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.


8

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 ...


8

Do: netstat -an|grep 3306 | grep LISTEN If something similar to the following line is returned: tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN .. it means that it's listening on all interfaces. If something similar to the following line is returned, and no other lines: tcp 0 0 127.0.0.1:3306 ...


8

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 ...


8

So SET ARITHABORT ON basically says "if a divide by zero error happens or an arithmetic overflow happens abort the query" This is usually desirable behavior and is the default instance wide setting. If this causes issues with your vendor's queries, I would say that they may have been suffering from some coding issues to begin with. I would ask them for more ...


8

This is not a technical problem, it's a contract management problem. Don't frig with the turnkey application. It gives the vendor a get-out-of-jail-free card that allows them to ignore their service level agreement. Either, Throw hardware at the problem (SSDs, bigger server, more RAM etc.), or Talk to whomever manages the relationship with your vendor. ...


8

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 ...


8

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 ...


8

MyISAM Key Cache You said you have key_buffer_size at 8GB. Question: Do you really have 8GB of MyISAM indexes? Please run this query SELECT KBS/power(1024,0) KBS_BB, KBS/power(1024,1) KBS_KB, KBS/power(1024,2) KBS_MB, KBS/power(1024,3) KBS_GB FROM ( SELECT SUM(index_length) KBS FROM information_schema.tables WHERE ...


8

PostgreSQL isn't using lots of RAM, the kernel is using it for disk cache. This is exactly what you want to happen. There is nothing to fix here. "Free" RAM is wasted RAM. The best thing for a database server is for as much RAM as possible to be used as disk cache.


8

The not-so-good news is that I have done a lot of searching on this topic, only to find that over the years a lot of others have done a lot of searching on this topic, and there is no way to configure the behavior of SqlClient. Some MSDN documentation implies that it can be done via a ConnectionString, but there are no Keywords that would allow for altering ...


8

Things I see wrong right away Way to many instances Not enough vCPUs vNUMA probably doesn't match NUMA I'm guessing there's a ton of missing indexes MAXDOP of 1 or 2 Cost threshold of 50 Optomize for adhoc workloads = on (it's probably off) Questions What do the waits look like? What are all the memory configs setup for? Fixing this is going to take ...


8

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 ...


8

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 = ...



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