MySQL uses more than 500% of the CPU Everything was going fine, but since a couple of hours ago MySQL is using from 400% to 550% of the CPU.

I'm getting a lot of these errors:

1003 14:03:17 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/var/mysql_tmpfs/#sql_10ff_8.MYI'; try to repair it
141003 14:03:17 [ERROR] Got an error from unknown thread, /build/buildd/mysql-5.5-5.5.38/storage/myisam/mi_write.c:226

I have no broken tables.

Also, using iotop, I discovered that MySQL was writing files inside the temp directory at a speed of around 150MB/s, that seems too high to me.

After googling a while I discovered that the problem could be a full /tmp directory, so I've created a 4GB ramdisk mounted on "/var/mysql_tmpfs".

After I setted this ramdisk as MySQL's default temp folder the server was able to slowly process some requests. but still not even an half of what it usually handles.

What can I do to solve the problem?

All the software are updated.
The OS is Ubuntu Server 14.04.1 64-bit.
The machine has 6 cores and 32gb of RAM.

EDIT 1:

I've tried to manually run this query:

SELECT * FROM my_table WHERE ID = 400000

And it takes one second or more to output a result, obviusly I have an index in that column and there are around 450000 rows in that table.

The profiler says that 100% of the time has been spend on "Waiting for Table Level Lock", isn't it strange?

Create folder mysql_tmp on the biggest partition you have.
Add the line tmpdir = /path/to/the/mysql_tmp to the section [mysqld] of the mysql.conf.
restart mysql
perform REPAIR for all the tables in your working base


Drag and Drop table object explorer into query window issue Sql Server Management Studio 18.1 I recently installed SSMS 18.1 and I am having an issue when trying to drag and drop table names from the object explorer to the query window. For example, I am writing a query and dont want type out the table name I would drag and drop the table into the query editor and the name will be there.

This is the error I get: "An exception has been encountered. This may be caused by an extension".

Any help would be appreciated.

Follow this feedback item for updates on the issue:
SSMS Crash when dragging table from Object explorer into new query window

Microsoft SQL Server (Product Manager, Microsoft Azure) responded  ·  Jun 14, 2019
Issue has been identified and a fix is being made. It will be rolled out with the next update of SSMS 18.

In the meantime, you could revert to SSMS 17.9.

This is clearly mentioned in the known issues section of the release notes for SSMS 18.1:

  Users may see an error when dragging a table object from the Object Explorer into the Query Editor. We are aware of the issue, and the fix is planned for the next release.

Its not a major issue since intellisense will offer auto-completion on your table names anyway but if you find it a major blocker you can always revert back to a previous version. SSMS 17.x can be installed side by side while you wait for SSMS 18.2 to be released.


Should I opt for a new database or adding more tables? this is a question around best practice that I'm currently dealing with.
I have a database (SQL Server 2008 R2) with a heap of tables (around 40)
with data, now the need has risen to have logging of which Changes are made
not from a technical standpoint but for instance if a row gets updated some
users needs to have a "tracking view" in the application in which they can see
which fields were changed and when.

So to my question, should I add a new table and store all this or is it better
to add a new database to handle all history stuff in? What are the pros and cons?

I would definitely err on the side of keeping the tables in the same database. It makes it easier to maintain referential integrity (unless you hard delete records in which case foreign keys won't work for you anyway) and create consistent backups (simply backup one database and you know it is in a consistent state, instead of taking two+ backups that my be slightly out of sync).

If you concern is wanting to spread the data between different storage pools (for instance keeping history and audit data on something big & cheap but potentially slow, while keeping active data on something faster but smaller & more expensive), or just keeping the different data on different spindle-sets to reduce potential IO contention, then you can achieve this within a single database by distributing your tables between files/filegroups instead of just having one filegroup containing everything. See http://technet.microsoft.com/en-us/library/ms189126(v=sql.105).aspx (amongst other reference locations in MS's sites) for more details on this.

I would do this in the same database because the data belongs together. To create a trigger for this use, it is not practical to insert records to another database.

Refer to the following answer on how to design the auditing table. https://stackoverflow.com/questions/7975317/audit-tables-each-field-for-table-or-one-table


sp_executesql - What is wrong with my Parameter-string? I've stared myself blind at this. It started as a more complex procedure, but I've stripped it down to the bare bones, trying to make it run. This is the current code:

DECLARE @sql NVarchar(MAX) = '
CREATE PROCEDURE dbo.Test
AS
BEGIN
    SELECT 1, @num;
END'

EXEC [sp_executesql] @sql, N'@num int', @num = 1;

It simply refuses. It's worth mentioning that if I run the code without the parameter-part (@sql being the sole parameter to the sp_executesql function) it runs fine. I've tried other syntax alternatives, such as

EXEC [sp_executesql] @sql, N'@num int', 1;

and

DECLARE @params NVarchar(1000) = '@num int';
EXEC [sp_executesql] @sql, @params, 1;

What is wrong with my parameter-specification?

Edit 1: Included error-message

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'PROCEDURE'.

You seem to be expecting that the parameters will end up being treated as string interpolation arguments.

They won't.

One way of achieving something similar to what you want would be simply to parameterise the stored procedure

CREATE PROCEDURE dbo.Test
@num int
AS
BEGIN
    SELECT 1, @num;
END

Then when you execute it you supply the desired value.

If you definitely want the procedure to end up with @num replaced with a hardcoded 1 you can use.

DECLARE @num int = 1

DECLARE @sql NVarchar(MAX) = '
CREATE PROCEDURE dbo.Test
AS
BEGIN
    SELECT 1, @num;
END'

SET @sql = REPLACE(@sql, '@num', @num)

EXEC sys.sp_executesql @sql -- or just EXEC(@sql)

Take care of SQL injection if you go this route.

Also you would need to be careful of parameter/place holder names and order of replacements, so if you had place holders @num and @number the @number one would need to be replaced first.

You should avoid making the entire call dynamic because of the sql injection vulnerability.

Your procedure needs to accept the parameter you are specifying on your dynamic
sql call as well

    @sql='
    CREATE PROCEDURE dbo.Test
    @num int
    AS
    BEGIN
        SELECT 1, @num;
    END'
    exec sp_executesql @sql

The proper way to parameterize your dynamic sql is:

    DECLARE @params NVarchar(1000) = '@num int';
    EXEC [sp_executesql] @sql, @params, @num=1;

Parameterizing dynamic sql also allows the plan in the procedure cache to be reused and is much more efficient for the server. If you ever come across dynamic sql that is not parameterized and you can't modify because it is in a third party software, you can also use plan guides.


Moving data into an archive table and reducing the size of the original table On a SQL Server I inherited, there is a 55GB database on a 70GB drive. There are many small tables, and one big table that is 50GB in size (approx 36GB data, 15GB index). The log file is on a different drive. After shrinkfile the log file's drive has about 12GB free. Database is in simple recovery mode.

The table is badly designed, for example there are many columns defined as char(10) that have nothing bigger than 3 characters in them.

There isn't enough free space on the drive to alter these columns and then rebuild the clustered index (when altering a column the database creates a new column then copies the data from the old).

I'm wondering if I can instead do the following:

Create an archive table with a much better space saving design (appropriately sized columns for the data they contain)
Incrementally move old data from the huge table to the archive table.

Will the source table reduce in size when data is deleted from it (after being moved to the archive table)? Or would I have to do a clustered index rebuild for that to happen?  (In which case I cannot do it because there isn't enough space.)

It now occurs to me that column alteration uses space in the log file, but whenever I do a column alteration on a smaller table, the main data file grows as well.

If allocating space or another drive is impossible, then there might be ways you can do this with minimal growth, assuming you can put the database into RESTRICTED_USER mode or otherwise prevent users/applications from trying to modify the data while you fix it.

First off, don't shrink your data or log files at this point. You'll free up space on the disk temporarily, but the files will just grow again - so as with most shrink operations this is pointless.

Next, take one of the char(10) columns. Ensure that you know the largest string contained:

SELECT MAX(DATALENGTH(CONVERT(varchar(10), char10_column)))
FROM dbo.table;

Add a new, nullable, varchar(that length) column. (I haven't supported 2005 in some time, so I forget if this is fully or partially online - in theory, this shouldn't be a size-of-data operation, but I don't know if 2005 was smart enough to not treat it as one.)

ALTER TABLE dbo.table ADD newvarchar10_column varchar(10);

In small chunks, as I describe here for deletes, update that new column to have the value of the old column, and set the old column to NULL. This will add to the table size gradually, but will have minimal impact on the log.

UPDATE TOP (?top?) dbo.table -- not sure what your ?top? sweet spot will be
  SET newvarchar10_column = oldchar10_column,
      oldchar10_column = NULL
  WHERE oldchar10_column IS NOT NULL;

-- CHECKPOINT / BACKUP LOG

Once the data is all located in the new column, dropping the old column should be minimal work for the database, since there isn't any actual data to log. If this column is involved in any indexes or constraints, or schema-bound views/functions, you'll have to deal with those first.

ALTER TABLE dbo.table DROP COLUMN oldchar10_column;

Then rename the new column so that it look like the old column.

EXEC sys.sp_rename N'dbo.table.newvarchar10_column',
       N'oldchar10_column', N'COLUMN';

Don't forget to update any procedures or code that still think this column is a char(10).

This is just a theory. You might want to test it on a non-critical system first to test the effects on data/log files, figure out the sweet spot for ?top?, etc.

Aaron Bertrand gave a great answer, to answer your original question "no" sql server does not deallocate those pages when a delete happens, truncate yes, but delete no. The source table will have the same amount of reserved space.