Category: SQL

Simple Microsoft SQL performance gains
article #1256, updated 1337 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 1456 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 1465 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 1529 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:      

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

Create New Microsoft SQL Administrative Login
article #1160, updated 1843 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 2924 days ago

Here’s a good place to start:

http://mysqltuner.com/

Categories:      

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

Effective MS SQL Database Maintenance
article #784, updated 2950 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 3033 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 3033 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 3535 days ago

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

Categories: