Category: SQL

Simple Microsoft SQL performance gains
article #1256, updated 1692 days ago

Open up your SQL server instance in the Studio. Go to the Properties of your instance.

Under Memory, you will see a minimum and a maximum. Set the minimum to 1024 (MB) or 2048. Reserve 4 gigabytes RAM minimum for the OS, add some more for anything else running on it, then use the rest as your maximum memory usage setting.

Also under Memory, you will see “Minimum memory per query”. Default is 1024. Set this to 2048.

Then under Processors, check “Boost SQL Server priority”.

And click OK.

Some info here:

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-2017#max_server_memory

Categories:      

==============

Calculate innodb_buffer_pool_size for MySQL
article #1280, updated 1810 days ago

Run this query:

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM 
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

and use the result it gives, i.e., 4 equals 4G. This and lots more great info here:

https://dba.stackexchange.com/questions/27328/how-large-should-be-mysql-innodb-buffer-pool-size

Categories:      

==============

Control memory usage on Microsoft SQL
article #1276, updated 1820 days ago

By default Microsoft SQL instances are all over the place, sometimes you’ll see them take 1.5G on a 32G server, sometimes they’re trying to eat everything alive and the servers slow to a crawl. To control them, try the following. Performance gains are likely.

  1. Make sure SQL Management Studio is installed of the correct version.
  2. Right-click the SQL Management Studio icon, and Run as Administrator. This is a great workaround for many SQL authentication issues. If you still can’t move forward after this, you’ll have to create a new SQL sysadmin user in SQL single user mode, which is a different set of steps!
  3. Connect to the database.
  4. Right-click on the server name. Choose Properties.
  5. Look at Memory, and start your tweaking. Also go to Processors if you like, and choose “Boost SQL Server priority”.
  6. You can also do this by SQL. Create a new query, containing the below if you want 10G max and 2G min, and execute the query. See results in Task Manager. Amazing!
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 10240;
GO
RECONFIGURE;
GO
sp_configure 'min server memory', 2048;
GO
RECONFIGURE;
GO

At least one reason this can do a lot of good, is the default is 2147483647 megabytes (2,147 terabytes) for “Maximum server memory”. Which means (a) SQL is going to try to take all of the RAM that there is if it imagines it might need it, and (b) even if it doesn’t, it’s going to calculate memory sometimes in terms far larger than necessary. Thus far, leaving 4 or 8 gigabytes for the OS and giving SQL the rest for a maximum has worked very well in many cases, though if you have other applications running on the server you’ll want to divvy up carefully.

Categories:      

==============

Automatic Microsoft SQL Tuning
article #1260, updated 1883 days ago

It exists as of SQL 2017:

https://docs.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning?view=sql-server-2017

Categories:      

==============

Tune MySQL and MariaDB
article #790, updated 3278 days ago

Here’s a good place to start:

http://mysqltuner.com/

Categories:      

==============

Effective MS SQL Database Maintenance
article #784, updated 3304 days ago

An interesting article:

https://technet.microsoft.com/en-us/magazine/2008.08.database.aspx

Categories:      

==============

Download SQL Studio 2008 R2
article #761, updated 3387 days ago

Go to this page:

http://www.microsoft.com/en-us/download/details.aspx?id=30438

which looks like it is for SQL as a whole, check the downloads list; the ones you want are near the bottom.

Categories:      

==============

Download SQL Studio 2008
article #760, updated 3387 days ago

Go to this page:

http://www.microsoft.com/en-us/download/details.aspx?id=30438

which looks like it is for SQL as a whole, check the downloads list; the ones you want are near the bottom.

Categories:      

==============

Automatic Microsoft SQL backup job settings
article #562, updated 3890 days ago

Open SQL Studio, under the server, Management, Maintenance Plans.

Categories:      

==============

SQL Server 2008 internal backup
article #272, updated 4760 days ago

In SQL Studio for SQL 2008, under a database object, go to Management, then Maintenance Plans. If you double-click on an item under there, you’ll see a Visio-like flowchart; double-clicking on any of the items will permit reconfiguration.

Categories: