Category: SQL

Calculate innodb_buffer_pool_size for MySQL
article #1280, updated 110 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 119 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 183 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:      

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

Control Microsoft SQL memory usage
article #1256, updated 239 days ago

Great 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:      

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

Create New Microsoft SQL Administrative Login
article #1160, updated 497 days ago

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.
  1. 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.
  1. Start the database.
  1. 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>
  1. 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
  1. Reverse the change in SQL Server Configuration Manager.
  1. Restart the SQL server.

Categories:      

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

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

Here’s a good place to start:

http://mysqltuner.com/

Categories:      

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

Effective MS SQL Database Maintenance
article #784, updated 1604 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 1687 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 1687 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 2190 days ago

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

Categories: