Some Notes on the Machines

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

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

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.

It exists as of SQL 2017:

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

Often one will find a Microsoft SQL server with no working or available administrative login. To set one up:

1. Stop the database. You’ll need its name. Go to services.msc , find entry “SQL Server (XYZ)”; XYZ is the name.

2. Set up the database to run in single-user mode. In the Sql Server Configuration Manager, in SQL Server Services, in the properties of the desired SQL Server, in the Advanced tab, and for Startup Parameters, add “-m;” (no spaces, no quotes) to the beginning.

3. Start the database.

4. From administrative command prompt, run:

SQLCMD -E -S 127.0.0.1\XYZ

If you have only one SQL server on this machine, omit the -S and everything after.

This may take multiple attempts. The attempts may error saying “Reason: Server is in single user mode. Only one administrator can connect at this time.” Eventually it will connect and give you the prompt:

1>

5. Enter the following, press Enter at every line, and choose something long and horrible for PasswordText:

CREATE LOGIN [sa2] WITH PASSWORD = 'PasswordText'
GO
GRANT CONTROL SERVER TO [sa2]
GO
EXIT

6. Reverse the change in SQL Server Configuration Manager.

7. Restart the SQL server.

Here’s a good place to start:

http://mysqltuner.com/

An interesting article:

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

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.

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.

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