How can I move data from one filegroup to another? I have 10 million rows in a table, however the table is created on the primary filegroup.

All the indexes and primary keys were created in the primary filegroup.

Is there any way to move the table, indexes and primary keys to a user-defined filegroup without loosing the data in the table?

The following code can be used on an ad-hoc basis to move data from one filegroup to another:

CREATE PROCEDURE dbo.MoveDataToCustomFilegroups
(
    @IndexType int
    , @OldFileGroupName sysname
    , @NewFileGroupName sysname
    , @DebugOnly bit = 0
)
AS
BEGIN
    /*
        Description:    MOVE ALL INDEXES of specified @IndexType from @OldFileGroupName
                        to @NewFileGroupName
        By:             Max Vernon
        NOTE:           Should not be used on partitioned tables.
    */
    SET NOCOUNT ON;
    DECLARE @OnlineOption bit;
    DECLARE @msg nvarchar(255);
    DECLARE @cmd nvarchar(max);
    /*
    @IndexType can be
        0 = Heap
        1 = Clustered
        2 = Nonclustered
        3 = XML
        4 = Spatial
    */
    IF NOT EXISTS (SELECT 1 FROM (VALUES (0), (1), (2), (3), (4))v(n) WHERE v.n = @IndexType )
    BEGIN
        SET @msg = N'Invalid @IndexType specified.  @IndexType can be
        0 = Heap
        1 = Clustered
        2 = Nonclustered
        3 = XML
        4 = Spatial
';
        THROW 50000, @msg, 1;
    END
    IF @DebugOnly = 1
    BEGIN
        SET @msg = N'Running in DEBUG mode.  No statements will be executed.';
        RAISERROR (@msg, 10, 1);
    END
    SET @cmd = N'';
    SET @msg = N'';
    SET @OnlineOption = 0;
    IF EXISTS (SELECT name FROM sys.data_spaces WHERE name = @OldFileGroupName)
    BEGIN
        IF EXISTS (SELECT name FROM sys.data_spaces WHERE name = @NewFileGroupName)
        BEGIN
            IF @IndexType = 0
            BEGIN
                /*
                    Heaps require building a clustered index on the target filegroup,
                    then dropping the clustered index.
                */
                SELECT @cmd = @cmd + CASE WHEN @cmd = N'' THEN N'' ELSE CHAR(13) + CHAR(10) END +
                N'CREATE CLUSTERED INDEX [CX_' + o.name + N'_' + (SELECT TOP(1) col_c.name FROM sys.columns col_c WHERE col_c.object_id = o.object_id ORDER BY col_c.column_id) + N'] ON ' + QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) + N' (' + QUOTENAME((SELECT TOP(1) col_c.name FROM sys.columns col_c WHERE col_c.object_id = o.object_id ORDER BY col_c.column_id)) + N')
    WITH (FILLFACTOR=100, DATA_COMPRESSION=PAGE) ON ' + QUOTENAME(@NewFileGroupName) + N';
    DROP INDEX [CX_' + o.name + N'_' + (SELECT TOP(1) col_c.name FROM sys.columns col_c WHERE col_c.object_id = o.object_id ORDER BY col_c.column_id) + N'] ON ' + QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) + N';
    '
                FROM sys.indexes i
                    INNER JOIN sys.objects o on i.object_id = o.object_id
                    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
                    INNER JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
                    INNER JOIN sys.partitions p ON o.object_id = p.object_id AND i.index_id = p.index_id
                WHERE i.type = 0 /* HEAP */
                    AND o.type = 'U' /* USER_TABLE */
                    AND o.is_ms_shipped = 0
                    AND ds.name = @OldFileGroupName;
            END
            ELSE
            BEGIN
                SELECT @cmd = @cmd + CASE WHEN @cmd = N'' THEN N'' ELSE CHAR(13) + CHAR(10) END +

                    CASE WHEN i.is_primary_key = 1 AND i.type = 2
                    THEN N'
    BEGIN TRANSACTION
    BEGIN TRY
        ALTER TABLE ' + QUOTENAME(s.name) + N'.'+ QUOTENAME(o.name) + N'
        DROP CONSTRAINT ' + QUOTENAME(i.name) + N';
        ALTER TABLE ' + QUOTENAME(s.name) + N'.'+ QUOTENAME(o.name) + N'
        ADD CONSTRAINT ' + QUOTENAME(i.name) + N' PRIMARY KEY NONCLUSTERED ('
                    ELSE
                        N'CREATE ' + CASE WHEN i.is_unique = 1 THEN N'UNIQUE ' ELSE N'' END + i.type_desc + N' INDEX ' + QUOTENAME(i.name) + N' ON ' + QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) +
                        N'('
                    END +
                        (
                        SELECT STUFF((
                            SELECT N', ' + QUOTENAME(col_c.name) + CASE WHEN col_ic.is_descending_key = 1 THEN N' DESC' ELSE N'' END
                            FROM sys.indexes col_i
                                INNER JOIN sys.index_columns col_ic ON col_i.object_id = col_ic.object_id AND col_i.index_id = col_ic.index_id
                                INNER JOIN sys.columns col_c ON col_ic.object_id = col_c.object_id AND col_ic.column_id = col_c.column_id
                            WHERE col_ic.is_included_column = 0
                                AND col_i.object_id = i.object_id
                                AND col_i.index_id = i.index_id
                            ORDER BY col_ic.key_ordinal
                            FOR XML PATH('')
                            ),1,2,'')
                        )
                    + N')' + CHAR(13) + CHAR(10) +
                    CASE WHEN (
                            SELECT COUNT(1)
                            FROM sys.indexes col_i
                                INNER JOIN sys.index_columns col_ic ON col_i.object_id = col_ic.object_id AND col_i.index_id = col_ic.index_id
                                INNER JOIN sys.columns col_c ON col_ic.object_id = col_c.object_id AND col_ic.column_id = col_c.column_id
                            WHERE col_ic.is_included_column = 1
                                AND col_i.object_id = i.object_id
                                AND col_i.index_id = i.index_id
                            ) > 0
                    THEN
                        N' INCLUDE (' +
                        (SELECT STUFF((
                            SELECT N', ' + QUOTENAME(col_c.name)
                            FROM sys.indexes col_i
                                INNER JOIN sys.index_columns col_ic ON col_i.object_id = col_ic.object_id AND col_i.index_id = col_ic.index_id
                                INNER JOIN sys.columns col_c ON col_ic.object_id = col_c.object_id AND col_ic.column_id = col_c.column_id
                            WHERE col_ic.is_included_column = 1
                                AND col_i.object_id = i.object_id
                                AND col_i.index_id = i.index_id
                            ORDER BY col_ic.key_ordinal
                            FOR XML PATH(N'')
                            ),1,2,N'')
                        ) + N')'
                    ELSE N''
                    END +
                    CASE WHEN i.has_filter = 1 THEN N' WHERE ' + i.filter_definition ELSE N'' END +
                    N'    WITH (' +
                    CASE WHEN NOT(i.is_primary_key = 1 AND i.type = 2) THEN N'DROP_EXISTING = ON, ' ELSE N'' END +
                    CASE WHEN i.fill_factor > 0 AND i.fill_factor < 100 THEN N'FILLFACTOR = ' + CONVERT(NVARCHAR(3), i.fill_factor) + N', ' ELSE N'' END +
                    N'PAD_INDEX = ' + CASE WHEN i.is_padded = 1 THEN N'ON' ELSE N'OFF' END + N', ' +
                    N'IGNORE_DUP_KEY = ' + CASE WHEN i.ignore_dup_key = 1 THEN N'ON' ELSE N'OFF' END + N', ' +
                    CASE WHEN NOT(i.is_primary_key = 1 AND i.type = 2) THEN N'ONLINE = ' + CASE WHEN @OnlineOption = 1 THEN N'ON' ELSE N'OFF' END + N', ' ELSE N'' END +
                    N'ALLOW_ROW_LOCKS = ' + CASE WHEN i.allow_row_locks = 1 THEN N'ON' ELSE N'OFF' END + N', ' +
                    N'ALLOW_PAGE_LOCKS = ' + CASE WHEN i.allow_page_locks = 1 THEN N'ON' ELSE N'OFF' END + N', ' +
                    N'DATA_COMPRESSION = ' + p.data_compression_desc +
                    N') ON ' +
                    N'[' + @NewFileGroupName + N'];'

                    + CASE WHEN i.is_primary_key = 1 AND i.type = 2
                    THEN N'
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
    END CATCH'
                ELSE
                    N'
    '
                    END
                FROM sys.indexes i
                    INNER JOIN sys.objects o ON i.object_id = o.object_id
                    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
                    INNER JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
                    INNER JOIN sys.partitions p ON o.object_id = p.object_id AND i.index_id = p.index_id
                WHERE o.type = 'U' /* USER_TABLE */
                    AND i.type = @IndexType
                    AND o.is_ms_shipped = 0
                    AND ds.name = @OldFileGroupName
                    --AND i.name = 'AcisC5ModuleLaunc3'
                ORDER BY o.name, i.name;
            END
            IF @cmd = N''
            BEGIN
                SET @msg = N'Warning: no indexes found on ' + @OldFileGroupName + N'!';
                THROW 50000, @msg, 3;
            END
            ELSE
            BEGIN
                RAISERROR (@cmd, 0, 1) WITH NOWAIT;
                IF @DebugOnly = 0
                BEGIN
                    EXEC sp_executesql @cmd;
                END
            END
        END
        ELSE
        BEGIN
            SET @msg = N'ERROR: ' + @NewFileGroupName + N' does not exist!';
            THROW 50000, @msg, 2;
        END
    END
    ELSE
    BEGIN
        SET @msg = 'ERROR: ' + @OldFileGroupName + N' does not exist!';
        THROW 50000, @msg, 2;
    END
END
GO

I wrote a blog post featuring this code at SQLServerScience.com.

The script doesn't automatically move data for new tables.  I wouldn't recommend it, but you could automate that by running the script via a SQL Server Agent job on a schedule.  However, you'd be much better off to use the ON [filegroup] clause in the CREATE TABLE statement to avoid needlessly moving data around.  Check the Microsoft Docs for CREATE TABLE for the details on the ON <filegroup> clause.

An example of a correctly formed CREATE TABLE statement with an ON clause is:

CREATE TABLE [schema].[table]
(
    [column_name] int NOT NULL
) ON [filegroup];

If you define a clustered primary key inline, you could use this syntax:

CREATE TABLE [schema].[table]
(
    [primary_key_column_name] int NOT NULL
         CONSTRAINT [table_pk]
         PRIMARY KEY CLUSTERED
         ON [filegroup]
);


MySQL can't import a custom library for udf I am working on Linux Mint 17 Qiana with MySQL Ver 14.14 Distrib 5.5.53, for debian-linux-gnu (x86_64).

Following the instructions found here:

http://dev.mysql.com/doc/refman/5.7/en/adding-udf.html

and subsequent pages, I have created a library file for my own user-defined function. But MySQL gives an error when I create the MySQL function from the library file.

Here are the steps I made.

I have created the following C file:

#include <math.h>
#include <stdint.h>
#include <stdlib.h>
#include <string.h>

#include <mysql/mysql.h>

//*** Functions for compressing a protein sequence into a binary
my_bool compress_protein_sequence_init(UDF_INIT* initid, UDF_ARGS* args, char* message) {
    if(args->arg_count != 1) {
        strcpy(message, "Error: compress_protein_sequence needs a string");
        return 1;
    }

    if( args->arg_type[0] != STRING_RESULT ) {
        args->arg_type[0] = STRING_RESULT;
    }

    return 0;
}

char* compress_protein_sequence(UDF_INIT* initid, UDF_ARGS* args, char* result, unsigned long* ret_length, char* is_null, char* error) {
    unsigned long length = args->lengths[0];

//*** Checking on the arguments

    *ret_length = (unsigned long) length;

    if(!(result = malloc(sizeof(char) * ((*ret_length) + 1)))) {
        *is_null = 1;
        *error=1;
        *ret_length = 0;
        return 0;
    }
    if(initid->ptr) {
        free(initid->ptr);
    }
    initid->ptr = result;

    memset( result, '\0', *ret_length + 1 );

//*** Some logic

    result[*ret_length] = '\0';
    return result;
}

void compress_protein_sequence_deinit(UDF_INIT* initid) {
    if( initid->ptr )
        free( initid->ptr );
}

I compile the C code:

gcc -Wall -I/usr/local/include -shared -o libcompress_sequence.so -c compress_sequence.c

MySQL plugin directory is given by:

SHOW VARIABLES LIKE '%plugin%';

the answer is:

+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| plugin_dir    | /usr/lib/mysql/plugin/ |
+---------------+------------------------+

So I put the libcompress_sequence.so in the plugin folder of MySQL:

sudo cp libcompress_sequence.so /usr/lib/mysql/plugin/

I start MySQL in command line as root:

mysql -u root -p

I chose a database then I execute this MySQL command:

CREATE FUNCTION COMPRESS_PROTEIN_SEQUENCE RETURNS STRING SONAME 'libcompress_sequence.so';

and MySQL shows this error:

ERROR 1126 (HY000): Can't open shared library 'libcompress_sequence.so' (errno: 0 /usr/lib/mysql/plugin/libcompress_sequence.so: only ET_DYN and ET_EXEC can be loaded)

What is ET_DYN? What is ET_EXEC?
What is happening?
How to solve the problem?

The issue has been solved by:

removing the -c option from the gcc command line and adding the -fPIC option at the end, turning the command line into:

gcc -Wall -I/usr/local/include -shared -o libcompress_sequence.so compress_sequence.c -fPIC

The -c option removed the linking, which is not wished here since the library is compiled in one step. So removing the -c option tells to gcc to make the linking with the libraries included in the C file. But then gcc didn't compile anymore, and complained about the option -fPIC missing. I added it and gcc compiled.

Then MySQL complained about not being able to detect the function COMPRESS_PROTEIN_SEQUENCE in my library. So I put it in small letters: compress_protein_sequence, as the function is named into my C code, then MySQL imported the library. The actual MySQL command is the following:

CREATE FUNCTION compress_protein_sequence RETURNS STRING SONAME 'libcompress_sequence.so';

I am now able to use the MySQL function COMPRESS_PROTEIN_SEQUENCE.


SQL Server 2008 Maintenance Plan Failure Instead of using my old school index rebuild script I had decided to use 2008's built-in Maintenance Plan to do a simple Rebuild Index task.

The details are:

Connection = Local server connection
Databases = Specific databases (just one)
Object = Tables and Views
Free space options = I have tried both default amount and change percentage to 70%
Advanced options = Keep index online while reindexing is the only one checked.  This is the primary reason I wanted to use this was to see if we could keep an index online while still doing the rebuild.

When I run the T-SQL from this manually everything works ok, but I assume it is not keeping the index online.  Whenever I run the job though I get this very strange error that has not led me anywhere:

  Executing the query "ALTER INDEX [Agreement_PK] ON [dbo].[Agreement]
   RE..." failed with the following error: "String or binary data would
   be truncated. The statement has been terminated.". Possible failure
   reasons: Problems with the query, "ResultSet" property not set
   correctly, parameters not set correctly, or connection not established
   correctly.

USE [<dbase>]
GO
ALTER INDEX [Agreement_PK] ON [dbo].[Agreement] REBUILD PARTITION = ALL WITH ( FILLFACTOR = 30, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = ON, SORT_IN_TEMPDB = OFF )
GO
USE [<dbase>]
GO
ALTER INDEX [IX_Agreement_1] ON [dbo].[Agreement] REBUILD PARTITION = ALL WITH ( FILLFACTOR = 30, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = ON, SORT_IN_TEMPDB = OFF )
GO

The T-SQL is very generic and gives no indication as to what isn't set correctly or why such a simple local connection would be a problem.  EDIT: Here is that code from my comment.

It is set to use Windows NT Integrated security, but I am not sure why that would be a problem again on a local system connection.

You have "SET ANSI_WARNINGS  OFF" when running the code as T_SQL

When running the job this setting must be different.


TImestamp format in Oracle DB Query:

select date,TO_CHAR(date,'dd/MM/yyyy HH24:mm:ss')  from user.table  where rownum<10;

02-08-18 05:21:02.301000000 PM  02/08/2018 17:08:02

Problem is minutes in above time are different 'mm' loses the precision of minutes and probably providing some garbage value. How do I format dates without losing precision?

As documented in the manual you have to use mi for minutes.  mm  is for the month number

select date,
       TO_CHAR(date,'dd/MM/yyyy HH24:mi:ss')
from user.table
where rownum<10;


Connecting to a Remote SQL Server 2008 Instance I work with SQL Server 2008 at my current job and the way I connect to the (backup) server where The SQL Server 2008 instance is installed is by doing a remote desktop from my local work machine into a windows server 2003 computer where the SQL Server 2008 database program and database files are stored (also on our local network)...

This works fine but the only problem is that the SQL Server installation does not have SSRS, SSIS or BI tools installed. I know it is fairly easy to install these features BUT I was wondering If I would be able to connect to the database using a different SQL Server 2008 Installation on a totally different computer through an internet connection (not LAN)?

For Example, If I have a fresh Sql Server 2008 installation on my home computer with all the needed features (SSIS,SSRS,BI Tools), would I be able to Open up Management Studio and connect to the SQL Server 2008 database server/engine at my workplace?

The way you do this really depends on your network setup, corporate security policies, and what kind of infrastructure you have available to use.

What's worked really well for me is creating a totally separate management server on which SSMS, BIDS, etc., is installed, and all management of servers is done from there.

There are many advantages to doing this:

Management tools are installed and configured centrally, which itself does a couple of important things:

Alleviates the installation of management tools on the actual production servers (SQL Server 2012 can be installed on Server Core, which makes external management a necessity). This saves your production CPU cycles, memory, and (probably) expensive storage.
If you retire a server, the management tools don't go down with the ship, too.

If you need to manage multiple servers, it's the perfect place to install a CMS, and do things like centralized monitoring and logging.
It gives you an area you can test with and not affect production. (If you blow something up, you haven't blown up production!)
Eliminates the need to directly connect to the SQL servers from the outside (simply RDP to the management box).
You can land the O/S on less expensive or performant storage than the production boxes, thus saving $$.

This kind of thing is super easy to set up if you have a virtualized infrastructure available to use.


SELECT for values in a table I have this table structure:

id1   id2
 1     2
 1     3
 2     1
 2     4

id1 and id2 are not unique as you can see in the values. I need to retrieve those values that exists in the two columns meaning id1=1,2 id2=2,1. Any help?

Cheers and thanks in advance

SELECT x.id1, x.id2
FROM   tbl x
WHERE  EXISTS (
    SELECT *
    FROM   tbl y
    WHERE  (y.id1, y.id2) = (x.id2, x.id1)
    );

Assuming that the combination of (id1, id2) is unique, so no DISTINCT. This would still allow (1,2) and (2,1) to coexist (and both will be returned), but no two rows with (1,2).

Also assuming id1 != id2 for every row (deriving from your test data and question). A unique entry (1,1) without doppelganger would be returned by this query. If my first assumption is true, you can exclude id1 = id2 right away. Else, you'd need something like row_number() to identify duplicates ...

A multicolumn index would greatly speed up such a query:

CREATE UNIQUE INDEX tbl_id1_id2_idx ON tbl (id1, id2);

Use it without UNIQUE keyword, should (id1, id2) not be unique after all.

Or, you can use those other techniques discussed under your prequel question, accordingly modified.


Vertical Partition MySQL table I am reviewing a case, where I have 200+ columns mostly varchar(100). The columns are from several external data sources like CRM / Demographics data etc. I cannot hold them in one MySQL table with ever growing number of columns.

The general query situation may contain columns from one or more vertical partitions.

Is it a good idea to vertically split them? and join them on query?
What should be the size of each partition (number of columns)? To improve performance.
What should be the best JOIN matching condition?

MySQL version: 5.7
Storage Engine: InnoDB

I don't think that someone can give you magic numbers like the acceptable number of columns, or information too strictly related to your workload, like if it is a good idea to split the table. There are too many variables: number/types of existing columns and indexes, number of queries, how many columns you read per query, and so on. Proper tests will give you a good answer. All we can say is that, yes, common sense says that such a table should be split if possible.

But then, every query will need to read from multiple partitions? Every new row will have matches in all partitions? This could slow down your application. Ideally, most of the queries should be able to read from only one partition. So the first suggestion would be to check with developers if some queries can be rewritten so that they will read less columns - possibly by rewriting some SELECT *.

You also ask about the best possible match condition. Here the answer is easy: join by primary key. All matching rows should have the same id. It should be an AUTO_INCREMENT columns on only one table. You first insert new rows into the table, then to the others, in this way:

INSERT INTO t1 (...) VALUES (...);
INSERT INTO t2 (id, ...) VALUES (LAST_INSERT_ID(), ...);
INSERT INTO t3 (id, ...) VALUES (LAST_INSERT_ID(), ...);

Also don't add foreign keys, because they will slow down your writes.


Restoring database backup makes identity column start at 1 again I created a backup of our database in SSMS 2012, using Tasks -> Backup as a Full Backup, including all data.

When I restore this backup on another server (2012 as well), all identity columns are reset to start at 1, which in turn messes up the ORM library (NHiberate) the application uses.

An example is the accounts table. After I restored it and created a new account, that account's ID started with 1, although on the production system that I backed up, the IDs were up to 2000 or something.

CREATE TABLE [accounts](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](255) NULL,
    [password] [nvarchar](255) NULL,
    [passwordSalt] [nvarchar](255) NULL,
PRIMARY KEY CLUSTERED
(
    [id] ASC
) WITH (PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON,
    FILLFACTOR = 90) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
    [name] ASC
) WITH (PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON,
    FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

Why is this happening and how can I transfer the database, including all data so the identity columns are properly restored?

The backup file only contains two sets, the Data and Log entries.

Running dbcc checkident('accounts', noreseed) on the source server yields Checking identity information: current identity value '2816', current column value '2816'. On the target server, it returns 1 as the current column value.

Some more digging with the server administrators revealed that the problem was Schemas. On the source server, all tables were in a special schema, let's call it Custom. The Login I used to manipulate data and create the backup with was assigned to this schema and used it as default.

Transferring the database to the target server brought the user along, but not the login (that is stored on server level). So the administrator went along and created a new login that matched the user. That didn't work because the user already existed and SSMS tries to create the user in the database when creating a new login.

So he deleted the user, created it along with the new login, but didn't set the default schema to Custom.

That meant that any query and connection that used that login for some reason saw the table definitions, but not the data inside it - and apparently that included the identity columns as well.

So we switched the new user to use the Custom schema and now everything works as expected.


Incorrect statistics are used on index seek I have a table-valued function and ordinary table and need to perform INNER JOIN on them. Because, the engine thinks that the function is returning always one row, I try to store the results in temporary table and then to perform the join operation - in both cases, the Estimate number of rows count that are read from the table/index is wrong.

When the temporary table is used, the correct Estimate number of rows count value is used for the rows returned by the function. All involved indexes are rebuilt.

Why I am worried about this? This join is part of more complex query which execution is very slow. I believe this is due to the fact that the statistics that are used by the engine are wrong and not the optimal execution plan is used. Even when the query use parallelism again nested loop is used to join the tables.

If I use a HASH or MERGE hints on join the correct statistics are used and better execution plan is built but I prefer to find a way to provide the correct statistics instead of forcing the engine to do something.

Could anyone tell why always one row is expected? Maybe because table-valued function is used, the engine thinks one row is going to be read from the table/index?

The estimate for the inner (lower) input to a nested loops join is per iteration in SSMS. The 'actual' number of rows shown in SSMS is a total over all iterations. This often causes confusion, and is the result of a questionable design decision.

The one row estimate therefore needs to be multiplied by the estimated number of executions. Many people prefer to view plans in SQL Sentry Plan Explorer, which does this for you, so the numbers are directly comparable (both estimate and actual are shown over all iterations).

Using a temporary table to materialize a relatively small result to improve cardinality estimation is a very valid tuning technique. If the code executes as part of a function or stored procedure, you may need to manually update statistics on the temporary table and request a recompile to get the best plan in some circumstances, see my article Temporary tables in Stored Procedures for details.

There are still times when the optimizer will choose the 'wrong' join type even with good cardinality and distribution information, but these are relatively rare. You certainly maximize your chances of getting a good plan by ensuring the optimizer has accurate information to base its decisions on.

I realize this answer is a bit generic, but there's only so much to be said on the basis of a couple of partial execution plan images. If you add showplan XML for both queries to the question (link to an off-site host e.g. gist), I might may be able say more.


SQLServer Query Store not saving failed queries run by SP_ExecuteSQL We are on SQL Server 2016
Query Store is activated on the database in question.
We have had some timeout problems reported on a nightly batchjob, and we have located the query in question.
The query is from a .Net application, generated by some framework
I have also found the query in Query Store.
But if i use 'tracked queries', all executions are reported as good, no timeouts (and no errors)
And if i query the QueryStore for that particular queryID, all executions has result 'Regular'
If i setup an extended events session to catch timeouts, the timeouts of the query is indeed collected and i can see that the actual call of the query is done with
EXEC SP_EXECUTESQL N'querystring'
does this somehow cheat the querystore into thinking it is ok, no matter what the result ?
regards
Peter

does this somehow cheat the querystore into thinking it is ok, no matter what the result ?

Queries executed with sys.sp_executesql can definitely be logged in the Query Store as "Aborted" or "Exception."  Here's a small repro.  First I created a database with impractical Query Store settings:
USE [master];
GO
DROP DATABASE IF EXISTS [278067];
GO
CREATE DATABASE [278067];
ALTER DATABASE [278067] SET QUERY_STORE = ON;
ALTER DATABASE [278067]
SET QUERY_STORE
(
    OPERATION_MODE = READ_WRITE,
    DATA_FLUSH_INTERVAL_SECONDS = 30,
    INTERVAL_LENGTH_MINUTES = 1
);
GO
USE [278067];
GO

Then I ran some .NET code that exercised the following scenarios:

Run a normal successful query
Run a query that errors out (in this case, it was a divide by zero)
Run a query that returns millions of rows, and then cancel client-side before all the results have been consumed
Run a query that returns millions of rows, and then close the connection before all the results have been consumed
Run a query that gets blocked, and experiences a server side lock timeout

Here's how the came through in the Extended Events "rpc_completed" event:

I embedded a comment of /* Track Me */ in each of the queries so I could find them in the query store:
SELECT
    qsrs.execution_type,
    qsrs.execution_type_desc,
    qsrsi.start_time,
    qsrsi.end_time,
    qsqt.query_sql_text,
    qsp.query_plan
FROM sys.query_store_plan qsp
INNER JOIN sys.query_store_query qsq
    ON qsp.query_id = qsq.query_id
INNER JOIN sys.query_store_query_text qsqt
    ON qsqt.query_text_id = qsq.query_text_id
INNER JOIN sys.query_store_runtime_stats qsrs
    ON qsrs.plan_id = qsp.plan_id
INNER JOIN sys.query_store_runtime_stats_interval qsrsi
    ON qsrsi.runtime_stats_interval_id = qsrs.runtime_stats_interval_id
WHERE
    qsqt.query_sql_text LIKE '%Track Me%'
    AND qsqt.query_sql_text NOT LIKE '%sys.query_store_plan%';

As expected, we see Regular, Exception, and then three Aborted queries.
All that is to say that I don't think the discrepancy can be explained by the application's use of sys.sp_executesql.
If you could elaborate more on:

your query store settings (perhaps the queries experiencing timeouts aren't being logged)
what specific kind of timeout the query experienced (client side connection or command timeout, lock timeout, something else)
the extended event you used to identify the timeout

Then you might be able to get some more help tracking this down.


MySql ORDER BY slow with join, fast as 2 queries Why is the following query slow but fast when I provide the values inline?
select u.* from user u
join user_group g on u.group_id = g.id
where g.account_id = 1
order by u.id limit 10;
-- takes ~30ms

select id from user_group where account_id = 1;
-- which is (99,198,297,396,495,594,693,792,891,990)

select * from user
where group_id in (99,198,297,396,495,594,693,792,891,990)
order by id limit 10;
-- takes ~1ms

Sub query is also slow. The plan is the same as the join.
select u.* from user u
where u.group_id in (select id from user_group where account_id = 1)
order by u.id limit 10;
-- ~30ms

All queries produce the same result.
98  0   99
197 0   198
296 0   297
395 0   396
494 0   495
593 0   594
692 0   693
791 0   792
890 0   891
989 0   990

Schema
I've got the following simple table structure, with 100 accounts 1k user_groups and 10 million users.
create table account(
  id int primary key auto_increment
);

create table user_group(
  id int primary key auto_increment,
  account_id int not null,
  foreign key (account_id) references account(id)
);

create table user(
  id int primary key auto_increment,
  deleted tinyint default 0,
  group_id int not null,
  foreign key (group_id) references user_group(id)
);

-- I've been trying with this index, but it doesnt seem to help.
create index user_1 on user(group_id, id, deleted);

Plans

The plan with the join using indexes and doing temporary filesort.

I don't understand why MySql seems to think it needs actually do the complete join to filter the data down. We clearly don't read anything from user_group.
For comparison I've tried the same thing in PostgreSQL and both queries run fast.
Why is it slow and is there a way to write the query (a single query!) that does it quickly? Sub select doesnt work.
This dbfiddle shows the problem.
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8ed68310d8ca72e9daef389dc0469a6f
Using MySql 8.0.23
Thanks
Edit
Here are the complete Session Status Handler debug details. It looks like the slow query is reading every value unlike the fast one.
-- FLUSH STATUS;
-- select u.* from user u join user_group g on u.group_id = g.id where g.account_id = 1 order by u.id limit 10;
-- SHOW SESSION STATUS LIKE 'Handler%';

Handler_commit  1
Handler_delete  0
Handler_discover    0
Handler_external_lock   4
Handler_mrr_init    0
Handler_prepare 0
Handler_read_first  0
Handler_read_key    11
Handler_read_last   0
Handler_read_next   100110
Handler_read_prev   0
Handler_read_rnd    0
Handler_read_rnd_next   0
Handler_rollback    0
Handler_savepoint   0
Handler_savepoint_rollback  0
Handler_update  0
Handler_write   0

-- FLUSH STATUS;
-- set @uGroups := (select group_concat(id) from user_group where account_id = 1 group by account_id);
-- select * from user where group_id in (select @uGroups) order by id limit 10;
-- SHOW SESSION STATUS LIKE 'Handler%';

Handler_commit  2
Handler_delete  0
Handler_discover    0
Handler_external_lock   4
Handler_mrr_init    0
Handler_prepare 0
Handler_read_first  0
Handler_read_key    2
Handler_read_last   0
Handler_read_next   19
Handler_read_prev   0
Handler_read_rnd    0
Handler_read_rnd_next   0
Handler_rollback    0
Handler_savepoint   0
Handler_savepoint_rollback  0
Handler_update  0
Handler_write   0

Investigate the plan. MySQL alters the order of the tables scanning.
Add STRAIGHT_JOIN. https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=592b1fb77c5c383abb8d93f9d9e13559


NOLOCK hint changes order of records returned There is a clustered index on a table Client field LastName.

When I simply dump all records from the table, they appear in the alphabetical order unless (nolock) hint is used as in the query in question. That hint changes the order of records. Should it?.  I am positive that no other session has an open transaction with the changes to that table (at least sp_who2 is not showing me any).

How can the difference in the order be explained?

Additional information pulled from comments:

There is no order by. Should non clustered index enforce the order?
The queries still return different order even when using an index hint specifying a clustered index. Should they? I am wondering why nolock changes the order of returned records without a visible change of the plans.
I did a WinDiff on them - same except for [the] (nolock) [query hint].

The appearance of an ordered result set, without an ORDER BY clause, often results from a scan retrieving rows in index order. One reason why an index-order scan is generally chosen under the default READ COMMITTED isolation level is that it reduces the chances of unwanted concurrency anomalies such as encountering the same row multiple times, or entirely skipping some rows. This is detailed in several places, including in this series of articles about isolation levels.

With a NOLOCK table hint, this behavior is relaxed, and access to the table is performed under the more tolerant READ UNCOMMITTED isolation level, which may scan data in allocation order instead of index order. As described in that link, the decision about whether to use an allocation-order or index-order scan is left up to the storage engine. This choice may change between executions without a change in query plan.

This may sound very abstract, but can be more easily demonstrated with some queries using undocumented functions against the AdventureWorks2012 database.

USE AdventureWorks2012;
GO
-- Appears to be ordered by BusinessEntityID
-- File:Page:Slot goes up and down several times
-- Show physical locations with sys.fn_PhysLocFormatter (undocumented)
SELECT
    P.BusinessEntityID,
    [(File:Page:Slot)] =
        sys.fn_PhysLocFormatter(%%physloc%%)
FROM Person.Person AS P;

-- Same query with TABLOCK or NOLOCK
-- Allocation-order (IAM) scan
-- Now appears to be ordered by File:Page:Slot instead of BusinessEntityID
SELECT P.BusinessEntityID,
    [(File:Page:Slot)] =
        sys.fn_PhysLocFormatter(%%physloc%%)
FROM Person.Person AS P WITH (NOLOCK);

The queries are borrowed with slight modification from Paul White.

Finally, just to be clear, this answer is about the appearance of an ordered result set. There is no guaranteed presentation order without a top-level ORDER BY.

An allocation-order scan can occur in a variety of other circumstances, such as when a table-level lock is acquired, or the database is in read-only mode. Parallelism can also influence the order the data is returned in. The key point is that without ORDER BY, the order data is returned in can vary over time by design.


Postgres consuming a lot of cpu I have a Java system with Postgres 8.3 and Glassfish application server, from time to time, the application is very slow at certain times - when it has more hits; by the top command, we can see the increase of cpu consumption by the user postgres, in relation to memory the consumption is normal, I've done tunning, I'm logging the queries that take longer to be executed and optimizing, but nothing solved, so I am here asking for your help, who knows and can give me any tips, I thank you in advance.

The fact that 4 out of the 5 queries are in the BIND state make me think you have a problem with probing the endpoints of an index.  This can often be problem if the planner is deciding if it wants to use a merge join.  Note that even if it decides against the merge join, it has still called get_actual_variable_range and if that is slow then it still suffers the slowness. However, I don't think that this mechanism even exists in 8.3.  Is that really what you are running?  If so, you might be better off spending time on getting an upgrade done, rather than trying to tune obsolete software.

One thing to try is to make sure you don't have long running transactions open, and to then vacuum the database.


How to convert the JSON date with to_date function How to convert the specific string Aug 6, 2018 3:17:11 PM with the function to_date?

That string is a result of a Java parsing mechanism for JSON. I would like to be able to recover it directly from oracle.

  My real problem is much more comprehensive that looks to be. It evolves the APEX_JSON usage, but it is not the question.


Handling sql column with mixed varchar and float data types We have some Excel spreadsheets with parcel numbers for properties that should be in a standard varchar format similar to:

[Parcel # 1]          [Building Address]
038-60-171-16-000     1750 W 4th St
038-60-238-09-000     2003-2027 W 4th St
01-15393              201 5th St NE
01-13504              101 5th St SE
25-20-00-03-008.501   1800 E 5th St
36-102-0700-000       2932 S 5th St
280615                2600-B 6th St SW
31-0101.000           1202 16th St
054-259620-00.000     843 N 21st St

Unfortunately we have some parcel numbers that were converted to scientific notation. This means they are in the wrong format for Parcel Numbers and should be converted back to regular decimal or numeric format. I am encountering difficulty converting just those values that have the scientific notation. The column is of nvarchar(255) data type but these values need to be manipulated back into numeric or decimal data type.

Parcel Number 1(Min)    Building Address
3.4007000221e+015   247 S Burnett Rd
3.1844401004e+013   433 N Cleveland Ave
3.4007000222e+016   2415 Lexington Ave
E020-0010-006-0211  7271 N Main St
3.6360705006e+013   825 W Market St
3.4007000353e+015   148 W North St
1.40109139e+011     4453-4461 SR-159
16-B-029-E-00-004   7314-7352 Industrial Park Blvd

The above table was retrieved with the following SELECT statement

SELECT [Parcel Number 1]
,[Building Address]
FROM XLSimport
WHERE [Parcel Number 1] like '%e%'

My understanding is that I should be able to cast the value to a decimal when it's in scientific notation. However, other parcel numbers that have a legitimate 'E' need to remain the same. How can I account for the different 'e' values and still convert the parcel numbers in scientific notation back to their standard parcel number format?

I frequently run into the error converting nvarchar to numeric error messages and I'm wondering if it's because of the parcel numbers that have an E but are not in scientific format.

For instance I run this query:

SELECT
CASE
    WHEN [Parcel Number 1] like '%E+%' THEN CAST(CAST([Parcel Number 1] AS FLOAT) AS DECIMAL)
    ELSE [Parcel Number 1]
END
FROM XLSimport

I then get the following error:

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.

The output from the CASE can only be of one data type. The first branch is decimal and the second is nvarchar(255).

According to the Data Type Precedence (Transact-SQL) rules in SQL Server the resulting data type for the CASE is decimal.

Add one more cast to nvarchar(255) in the first branch of the CASE.

SELECT
CASE
    WHEN [Parcel Number 1] like '%E+%'
    THEN CAST(CAST(CAST([Parcel Number 1] AS FLOAT) AS DECIMAL) AS NVARCHAR(255))
    ELSE [Parcel Number 1]
END
FROM XLSimport


Can we restore backup for standby from external hard drive with differnet drive labels I am using windows server 2003 and Oracle database 10g R2 and i have taken the backup using RMAN of my primary database in Drive label E. The size of backup is 1.5TB. In my standby database i don't have much space to copy the backup of primary database in standby machine. My standby machine have only space for restore the backup. i have copied the backup files in my external hard drive label G. Now i want to restore my backup using external hard drive label G.
can i be able to restore it?
please share your experience and documentations.

Yes, you can. The backup entries in the controlfile point to 'E:...', so after restoring the controlfile, you will have to catalog the backup pieces in RMAN by:

catalog start with 'G:\';

After this you can restore the database.


Add a JOIN to GROUP_CONCAT() I have a table of mostly-static objectives and another to track users' completions of those objectives. They can associate the completion with one or more records entered in another table and/or a text note. I'd like to format all of this together into a single entry for displaying in a table (i.e. one row per objective).

Here's an example of what Completion might look like:

ID    userID    objectiveID    recordID    text
1     4         8              500         NULL
2     4         8              NULL        "Lorem ipsum..."
3     4         8              750         NULL

I've gotten this far:

SELECT objectiveID,
   GROUP_CONCAT(recordID SEPARATOR ',') AS records,
   GROUP_CONCAT(text SEPARATOR ',') AS text
FROM Completion AS c
GROUP BY objectiveID;

Which returns:

objectiveID    records    text
8              "500,750"  "Lorem ipsum..."

What I'd actually like to display, however, is an attribute of the code being referenced by recordID... Suppose that this is the Record table:

ID    userID    codeID
500   4         1111
750   4         2222

And that this is the Code table:

ID    description
1111  dolor
2222  sit amet

My desired output would be:

objectiveID    records            text
8              "dolor, sit amet"  "Lorem ipsum..."

What's the best approach to get the other values merged in?

You join Completion (columns userID,recordID) to Record (columns userID,ID)

You join Record (column codeID) to Code (column ID)

Here is the proposed query

SELECT c.objectiveID,
   GROUP_CONCAT(d.description SEPARATOR ',') AS records,
   GROUP_CONCAT(c.text SEPARATOR ',') AS text
FROM Completion AS c
INNER JOIN Record AS r ON c.userID=r.userID AND c.recordID=r.ID
INNER JOIN Code   AS d ON r.codeID=d.ID
GROUP BY c.objectiveID;

Since GROUP_CONCAT's default separator is a comma, you could rewrite as

SELECT c.objectiveID,
   GROUP_CONCAT(d.description) AS records,
   GROUP_CONCAT(c.text) AS text
FROM Completion AS c
INNER JOIN Record AS r ON c.userID=r.userID AND c.recordID=r.ID
INNER JOIN Code   AS d ON r.codeID=d.ID
GROUP BY c.objectiveID;


You will need to look into how your connection pool is configured in your PHP web app. Even a fully functional connection pool will often keep many connections to a database open even when they are not in use.  Opening connections to the database can be very expensive for a web app and it makes sense to have some unused ones sitting around.

Most connection pools have settings for things like:

Minimum number of connections to keep open
Maximum number of connections to keep open
Number of connections to open when new connections are needed

There is also the possibility that your web app is not releasing connections back to the connection pool properly.  When you are done with your connections, you need to close them to let the pool know that they are available.   Not doing so is a a kind of resource leak and you will eventually run out of connections.


How to search for multiple synonyms? I have a database with 2 tables : products, and synonyms.
When a user type one or several words to search in the products.description field, I would like to search for all the synonyms/substitutions as well.
The synonyms table is basic, and have to columns : word1 and word2.
Example of its content :

chiptune <-> 8bit
lofi <-> lo-fi
lo-fi <-> 8bit

In SQL, How can I present to the user results feturing the word "lofi" when he type "chiptune"?
What I am trying to achieve is a "loop between synonyms", in both ways.
Thank you if you have some thoughts.

To avoid problems with cycles in your graph, I would suggest that you re-design your structure to:
CREATE TABLE products
( product_id ... NOT NULL PRIMARY KEY
, ...
);

CREATE TABLE synonym_groups
( synonym_group_id ... NOT NULL PRIMARY KEY
, ...
);

CREATE TABLE synonym_group_products
( synonym_group_id ... NOT NULL
, product_id NOT NULL PRIMARY KEY  -- A product belongs to exactly one group
,     CONSTRAINT ...
      FOREIGN KEY (synonym_group_id)
          REFERENCES synonym_groups (synonym_group_id)
,     CONSTRAINT ...
      FOREIGN KEY (product_id)
          REFERENCES products (product_id)
);

I created a Fiddle, I just used integer for keys since I don't know what identifies your entities. To get the synonyms of chiptune:
select p2.*
FROM products p1
JOIN synonym_group_products sgp1
    ON p1.product_id = sgp1.product_id
JOIN synonym_group_products sgp2
    ON sgp1.synonym_group_id = sgp2.synonym_group_id
JOIN products p2
    ON sgp2.product_id = p2.product_id
WHERE p1.product_name = 'chiptune'
  AND p1.product_id <> p2.product_id;


Oracle 11g R2 installation on Ubuntu 14.04 LTS I have installed Oracle 11g R2 on ubuntu 14.04 LTS.
On terminal I wrote following to start oracle:

sudo service oracle-xe start

on pressing enter, got following output :

" Starting Oracle Net Listener.
  touch: cannot touch '/var/lock/subsys/listener' : No such file or directory
  Starting Oracle Database 11g Express Edition instance.
  Failed to start Oracle Net Listener using /u01/app/oracle/product/11.2.0/xe/bin/tnslsnr
  and Oracle Express Database using /u01/app/oracle/product/11.2.0/xe/bin/sqlplus. "

What should I do now.

try these commands on terminal :

sudo mkdir -p /var/lock/subsys

sudo chmod -R 777 /var/lock/subsys


MAX function and group by usage - postgresql I am building a ranking algorithm using IMDB's formula:

https://stackoverflow.com/questions/1411199/what-is-a-better-way-to-sort-by-a-5-star-rating/1411268#1411268

The formula for calculating the Top Rated 250 Titles gives a true Bayesian estimate:

weighted rating (WR) = (v ÷ (v+m)) × R + (m ÷ (v+m)) × C

where:

* R = average for the movie (mean) = (Rating)
* v = number of votes for the movie = (votes)
* m = minimum votes required to be listed in the Top 250 (currently 1300)
* C = the mean vote across the whole report (currently 6.8)
for the Top 250, only votes from regular voters are considered.

The problem I am having is that I am not sure how to get the AVG and MAX from the whole result set. SQLFiddle here

I am guessing it has to do with the GROUP BY clause.

Also, how can I set "business 4" ranking to 0 instead of null?

This might get you started:

select name,
       (reviews_count / reviews_count + max_reviews_count) * rating + (max_reviews_count / (reviews_count+max_reviews_count) * avg_rating) AS ranking
from (
  SELECT name,
         reviews_count,
         rating,
         MAX(reviews_count) over () as max_reviews_count,
         AVG(rating) over() as avg_rating
  FROM businesses
) t;

Note that I removed the nullif to make it easier to read.

I'm not sure the use of the window functions is totally correct. I guess you need to do some grouping in there. If that is the case, then add this to the over() clause, e.g. over(partition by name)

The partition by in a window function is essentially a group by without the need to group the whole result.

For more details on window functions, please see the manual: http://www.postgresql.org/docs/current/static/tutorial-window.html


Storing mirror database failover details to the respective server or on repository I would need some practical suggestions on how to achieve below:

We have quite a few servers involved in database mirroring. As part of various activities and other fixes, we have database failover happening now and then.

What i am generally doing is browsing the sql error log to find the failover details for most of them

Is there a way , we can store or track the failover details for them either locally on server, say saving all that details in a text file or on repository server?

Thanks

The concept is to create an extended event session listening to the DATABASE_MIRRORING_STATE_CHANGE event. This post covers the mechanics of querying extended event info using TSQL on live server, though haven't used this approach.

Otherwise, you can create the extended events session via SSMS, and configure that session to write the output to a file (xel) which can either be opened by SSMS or by using a system function that can convert the data to xml. The XEL ("event file") itself is binary. easiest way IMO is just to configure some kind of script to copy those files to a central instance, then open and process there.

Fields that DATABASE_MIRRORING_STATE_CHANGE event captures:  https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/database-mirroring-state-change-event-class.

See https://msdn.microsoft.com/en-us/library/hh213147.aspx  for step by step to set up the an extended events session that dumps results to an event file. (needs to be done per instance, though can be scripted). Fairly quick setup to see if it's giving you all the details you need.

You can include the DATABASE_MIRRORING_STATE_CHANGE event class as part of a trace as well.


import csv data stored in a blob column Here's what i need to do:

Users will upload CSV files into SQL Server 2012 blob column
Each night, i would like to take each file and import the data into table
I will have 2 web sites using the same database. One is internal and one is in the DMZ
If i want my 2 sites to see the files, the easy way would be to store the files in the database. It will be also more easy to take backup of everything
If i store the files on a shared folder, i will have to open a port in the DMZ to see this shared folder inside our network. I don't know if its a good idea. Also, the backup plan will be harder to make (backup the database and backup the files separatly and try to synchronize them)

Is it possible to use a stored procedure to read the file inside the blob column, loop on each line and insert the data in a table?

Should i export the file to a temporary system file
Read the file and do the importation

or can i

Read the file directly from the blob colum and do the importation

other proposition?

Is it possible to use a stored procedure to read the file inside the blob column, loop on each line and insert the data in a table?

Definitely.

However, I would consider rethinking this plan.

A SQL Server database generally isn't a great place to be storing BLOBs, particularly given that you're just going to turn around and process them into row data later. It's a lot of extra disk activity and (presumably more expensive) storage that you just don't need to use. Also, the kind of processing being proposed will almost certainly perform worse than the many direct-processing alternatives. Generally speaking, the less processing you need to do (and the simpler the process itself), the better performance you'll get out of the system. And it will probably be more reliable as well.

Why not turn the files into row data immediately? Are you concerned about blocking or latency of the client application? If that's the only concern, consider setting up an asynchronous queuing system, possibly by using Service Broker. You can use BULK INSERT to turn the CSV files into row data directly from the file system without first loading the files as BLOBs. If this is going to blast the CPU during load when you need to run other things on the same server, consider using Resource Governor if you're on Enterprise Edition.

If you have to process the files in batch at night due to other constraints, it may be better to simply direct the raw files into a named (YYYYMMDD) folder on a network share during the day, and then once/day use an SSIS package with a Foreach File Enumerator container to process the files. I suppose this could also work in a job-based scenario where you just fire it up every 15 minutes or so to process and remove files that landed in the folder in the last period. SSIS may also be a good solution if you need some kind of transformation process to happen between the raw files and the row data.

There are lots of different possibilities here depending on your exact requirements, but I think I've given enough of the more common elements that you can piece together a solution that will work best for your situation.


What happens with WITH if later the query fails I am by no means a database expert so I might be asking something dumb.

So I am using PostgreSQL and I want to do the insert to one table, keep the ID (ID is incrementing automatically) and insert a tuple to another table (tuple has that ID in it).

Obviously I want to keep things atomic.

Query:

WITH inserted AS (
  INSERT INTO
    "public"."user" (email, first_name, last_name, username)
  VALUES
    (?, ?, ?, ?)
  RETURNING id
)
INSERT INTO
  "public"."security" (user_id, security)
VALUES((
  SELECT id FROM inserted
), ?);

Question is, can this even work? What will happen if the second insert fails? Will the first remain persisted?

The obvious answer is that I might put this into transaction and commit/rollback but will this be sufficient?

A single statement is always run atomically.

Quote from the manual

  PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it. A group of statements surrounded by BEGIN and COMMIT is sometimes called a transaction block

A set of common table expressions defined under the same WITH clause, however many of them (including writeable CTEs), is still part of a single statement.

So the statement either fails completely or succeeds completely.

You only need (explicit) transactions if you want to run atomically more than one statement.

The handling of generated sequence values is no different than in any other situation: a nextval operation is never rolled back


Is there a way to construct a PIVOT's IN clause using a SELECT? I want to build a PIVOT table, but I may have to use dynamic SQL unless the IN clause can be constructed in regular SQL.  I want to do something like this:

SELECT *
FROM
(
    SELECT x, y, z...
) srcTable
PIVOT
(
    MIN(srcTable.TimesDownloaded) FOR OrganizationName IN (SELECT CONCAT('Download_', OrganizationName) FROM AdamUser GROUP BY OrganizationName)
) pivotTable

So when I SELECT unique OrganizationNames from AdamUser and prefix Download_, that gives me a table whose row values equal the names of the columns I want.  This is invalid syntax though.  Do I always have to hard-code the PIVOT column names?

The T-SQL documentation of the FROM clause has this for the  specification:

<pivoted_table> ::=
    table_source PIVOT <pivot_clause> [ [ AS ] table_alias ]

<pivot_clause> ::=
        ( aggregate_function ( value_column [ [ , ]...n ])
        FOR pivot_column
        IN ( <column_list> )
    )

<column_list> ::=
    column_name [ ,...n ]

So no, it doesn't allow you to select the column names with a SELECT statement, and yes, you have to "hard-code" the PIVOT column names.


db2pd: Database mydb not activated on partition 0 As a user with DBADM privileges I enter

db2pd -db mydb -locks showdetail

from the DB2 CLP. The command then fails with message

Database mydb not activated on database partition 0.

Option -locks requires -db <database> or -alldbs option and active database.

I verified that the database is active:

C:\Windows\system32>db2 list active databases

                           Active Databases

Database name                              = MYDB
Applications connected currently           = 0
Database path                              = C:\DB2\NODE0000\SQL00002\

I also googled the error message and found an IBM support page for TSM which suggests my environment variable %db2instance% might not be set appropriately. However, everything appears to be in order:

C:\Windows\system32>set %db2instance%
DB2CLP=DB20FADE
DB2INSTANCE=DB2
DB2PATH=C:\Program Files\IBM\SQLLIB

What other reasons could there be, that db2pd is giving me the above error message?

I'm running DB2 v9.7.4 on Windows 7 Embedded.

db2pd requires Administrator authority on Windows, in the command shell you are executing it.  Setting your ID as a member of the Administrators group on the local machine is not sufficient.

IBM adds "DB2 Command Window - Administrator" in your Start Menu to give you a DB2 Command Window that has administrator privileges (which is the recommended method).  Alternatively if you are using a normal command window (cmd.exe), you would have to start it by right clicking "Command Prompt" and choosing "Run as Administrator".

It might be possible to use runas to execute db2pd, but it's probably easier to just start the command window directly with administrator privileges.


Is it possible to join these both tables? SELECT u.*,h.*,d.*
FROM User u
LEFT JOIN HealthDec h ON u.ID = h.EmpId
LEFT JOIN Document d On u.ID = d.UserID;

and:
SELECT count(id) FROM OHS
WHERE Category = 'POLICIES'
  AND ID NOT IN
  (
    SELECT OHSID
    FROM User_OHS
    WHERE UserID='1' AND Type='POLICIES'
  );

Both queries are working fine separately as required. But I want to combine both queries to get result for each user.

you can combine them like this, as long as your subse3lect only has onbe row and 1 coumn
SELECT u.*,h.*,d.* ,
(SELECT count(id) FROM OHS
WHERE Category = 'POLICIES'
  AND ID NOT IN
  (
    SELECT OHSID
    FROM User_OHS
    WHERE UserID=u.ID AND Type='POLICIES'
  )) as countr
FROM User u
LEFT JOIN HealthDec h ON u.ID = h.EmpId
LEFT JOIN Document d On u.ID = d.UserID;


Why does MariaDB 10.1.2 blow away MySQL 5.6.22 in sysbench? Problem

Maria DB is doing roughly twice the number of transactions that mysql is using standard oltp benchmark.

The VM and db's are both fresh installs from vendor apt repos with default configs.

VM [Virtualbox 4.3.10]

1 cpu

ubuntu 14.04 [fresh install guest machine on virtualbox 4.3.10]

sysbench 0.4.12

Methodology

sysbench --test=oltp --oltp-table-size=50000 --mysql-db=test --mysql-table-engine=innodb --mysql-user=root prepare

sysbench --test=oltp --oltp-table-size=50000 --oltp-test-mode=complex --oltp-read-only=on --num-threads=1 --max-time=60 --max-requests=0 --mysql-db=test --mysql-user=root run

MariaDB 10.1.2 Result

OLTP test statistics:
    queries performed:
        read:                            1149876
        write:                           0
        other:                           164268
        total:                           1314144
    transactions:                        82134  (1368.89 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1149876 (19164.41 per sec.)
    other operations:                    164268 (2737.77 per sec.)

Test execution summary:
    total time:                          60.0006s
    total number of events:              82134
    total time taken by event execution: 59.7102
    per-request statistics:
         min:                                  0.22ms
         avg:                                  0.73ms
         max:                                170.77ms
         approx.  95 percentile:               1.24ms

Threads fairness:
    events (avg/stddev):           82134.0000/0.00
    execution time (avg/stddev):   59.7102/0.00

MySQL 5.6.22 Result

OLTP test statistics:
    queries performed:
        read:                            506296
        write:                           0
        other:                           72328
        total:                           578624
    transactions:                        36164  (602.73 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 506296 (8438.17 per sec.)
    other operations:                    72328  (1205.45 per sec.)

Test execution summary:
    total time:                          60.0007s
    total number of events:              36164
    total time taken by event execution: 59.7937
    per-request statistics:
         min:                                  1.04ms
         avg:                                  1.65ms
         max:                                 97.15ms
         approx.  95 percentile:               2.10ms

Threads fairness:
    events (avg/stddev):           36164.0000/0.00
    execution time (avg/stddev):   59.7937/0.00

The MariaDB has a lot of optimizer features not found in mySQL (especially 5.5, but still compared to 5.6)

See this link for a comparison of optimiser features.

There is also a benchmark results page which shows MariaDB faster in a lot of tests.

So in the end MariaDB has more options when choosing an execution plan.


MySQL: Recover Master from Slave after failover I have replication running between two MySQL instances on two separate servers. Let's say M is the master and S is the slave.  It's setup as one way replication M->S.  I currently have S as read only and writing binary logs with the --log-bin option.  Suppose I have to failover to S because M goes down. What are the steps to recover M such that it is in sync with S again? And so S can become the SLAVE again?

I have seen the following thread but they depend on circular replication:
https://serverfault.com/questions/350756/recover-a-crashed-mysql-master-server-from-the-slave
https://serverfault.com/questions/350756/recover-a-crashed-mysql-master-server-from-the-slave

Steps I have so far to promote S to master:

STOP SLAVE IO_THREAD;
SHOW PROCESSLIST; check 'has read all relay log' status
STOP SLAVE;
SET GLOBAL read_only=0;
UNLOCK TABLES;
RESET MASTER;

UPDATED LIST OF COMMANDS:

On Master:
CHANGE MASTER TO MASTER_HOST=slave.com, MASTER_LOG_FILE=MASTER_LOG_FILE, MASTER_LOG_POS=MASTER_LOG_POS, ....
START SLAVE;
# Wait till caught up;
STOP SLAVE;
RESET MASTER;

On Slave:
CHANGE MASTER TO <original slave settings>;
START SLAVE;
USE mydb;
FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only=1;

Before you set it to read_only=0 and point connections to the Slave (new master) run a

show master status;

and take a note with those numbers.

When you recovered your master server you can run

CHANGE MASTER TO MASTER_HOST=myslavehost.example.com, MASTER_LOG_FILE=[take it from your notes],MASTER_LOG_POS=[take it from your notes], MASTER_USER='replication_user', MASTER_PASSWORD='replication_password';
START SLAVE;

Then the old master should start to replicate from the new master and catch up.

A couple of things to keep in mind:

Make sure your expire_logs_days settings is set to more days than your recovery takes otherwise you would lose the binlogs and you need to set up the old master from scratch
Make sure you know the replication user and password. If you don't you can recover it from:

the repository table (in case of master_info_repository = TABLE)

select User_name, User_password from mysql.slave_master_info;
the master.info file (5th and 6th line)

If your master has crashed chances are your slave is a bit behind so you will have some autoincrement conflict after you set the master back to replicate from the slave. pt-table-sync can you remedy that situation.


Run stored procedures in parallel I'm looking to try and run the same stored procedure multiple times with different parameters but at the same time.

I'm using SQL 2014

The reason for this is that the procedure takes around 7 hours to complete. It actually does the same process many times. So for instance it might build a new database and tables for each branch.

What I want to do is break down the stored procedure so I can run in per branch but then run each query in parallel. I've run tested this by running it in separate query windows and it runs almost 80% quicker.

Can anyone give me a dummies guide into running queries in parallel?

At one point I answered this question over at StackOverflow, but it seems like it would be useful to have that information at DBA.SE as well, revised and updated.

Just to be totally explicit: TSQL does not (by itself) have the ability to launch other TSQL operations asynchronously.

That doesn't mean you don't still have a lot of options (some of them mentioned in other answers):

SQL Agent jobs: Create multiple SQL jobs, and either schedule them to run at the time desired, or start them asynchronously from a "master control" stored proc using sp_start_job. If you need to monitor their progress programatically, just make sure the jobs each update a custom JOB_PROGRESS table (or you can check to see if they have finished yet using the undocumented function xp_sqlagent_enum_jobs as described in this excellent article by Gregory A. Larsen).  You have to create as many separate jobs as you want parallel processes running, even if they are running the same stored proc with different parameters.
SSIS Package: Create an SSIS package with a simple branching task flow. SSIS will launch those tasks in individual spids, which SQL will execute in parallel.
Custom application: Write a simple custom app in the language of your choice (C#, Powershell, etc), using the asynchronous methods provided by that language. Call a SQL stored proc on each application thread.
OLE Automation: In SQL, use sp_oacreate and sp_oamethod to launch a new process calling each other stored proc as described in this article, also by Gregory A. Larsen.
Service Broker: Look into using Service Broker, a good example of asynchronous execution in this article.
CLR Parallel Execution: Use the CLR commands Parallel_AddSql and Parallel_Execute as described in this article by Alan Kaplan (SQL2005+ only).
Scheduled Windows Tasks: Listed for completeness, but I'm not a fan of this option.

If it were me, I'd probably use multiple SQL Agent Jobs in simpler scenarios, and an SSIS package in more complex scenarios.

In your case, unless you're trying to launch 200 separate threads, multiple scheduled Agent jobs sounds like a simple and manageable choice.

One final comment: SQL already attempts to parallelize individual operations whenever it can*. This means that running 2 tasks at the same time instead of after each other is no guarantee that it will finish sooner. Test carefully to see whether it actually improves anything or not.

We had a developer that created a DTS package to run 8 tasks at the same time. Unfortunately, it was only a 4-CPU server :)

*Assuming default settings. This can be modified by altering the server's Maximum Degree of Parallelism or Affinity Mask, or by using the MAXDOP query hint.


How to obtain SIDs of Sql Server installed NT Service Accounts I am using WMI to obtain members of server local groups, as well as the SIDs of the members. The WQL queries work for members that are local users, local groups, AD domain users and AD domain groups. However when the script is looking at members that happen to be SQL Server related, it cannot find the object in the class it thinks it should look at, and therefore I don't get the SID of the members. For example, here are some queries constructed dynamically for group members that happen to be SQL Server related:

select SID from Win32_SystemAccount where Domain='NT SERVICE' and Name='SQLBrowser'
select SID from Win32_SystemAccount where Domain='NT SERVICE' and Name='MsDtsServer100'
select SID from Win32_SystemAccount where Domain='NT SERVICE' and Name='SQLAgent$SQLEXPRESS'
select SID from Win32_SystemAccount where Domain='NT SERVICE' and Name='MSSQL$EPROVISIONAPP'
select SID from Win32_SystemAccount where Domain='NT SERVICE' and Name='MSOLAP$EPROVISIONAPP'
select SID from Win32_SystemAccount where Domain='NT SERVICE' and Name='ReportServer$EPROVISIONAPP'
select SID from Win32_SystemAccount where Domain='NT SERVICE' and Name='SQLAgent$EPROVISIONAPP'

The queries above do not return any result, even though the PartComponent of such members tells me that the member is from the Win32_SystemAccount class, and the Domain is 'NT Service'.

On the server, if I look at Local Users and Groups -> Groups, and look at the properties of a group, say "SQLServerSqlAgentUser$myservername$SQLEXPRESS", it has one member "NT SERVICE\SQLAgent$SQLEXPRESS (S-1-5-80-592940576-165...". I cannot see the entire value of the SID.

I cannot figure out how Microsoft is pulling the SID of such members, where it is getting them from.

Any ideas?

From the WMI documentation, I couldn't find any way to directly obtain a service SID, probably because as far as I've seen, service SIDs aren't physical directory objects, and thus couldn't be queried except where they appear as part of ACLs. This would also explain why the values aren't persisted in the registry.

Having said that, the service SIDs definitely do not appear in Win32_SystemAccount. Using PowerShell on a sample machine, you can verify this by running Get-WmiObject -Query "SELECT * FROM Win32_SystemAccount".

Luckily, however, there are alternatives:

Per this TechNet blog post, the non-well-known portion of a service SID is generated by taking a SHA1 hash of the uppercase service name, and then splitting apart the results into five unsigned 4-byte numbers. The following code performs this computation and returns the SID as displayed in Windows:

DECLARE @serviceName nvarchar(128) = 'MSSQL$SQL2008R2DEV';

SELECT
    'S-1-5-80' +
        '-' + CONVERT(varchar(20), CONVERT(bigint, CONVERT(varbinary(4), sys.fn_varbintohexsubstring(0, a.BinSid, 17, 4), 2))) +
        '-' + CONVERT(varchar(20), CONVERT(bigint, CONVERT(varbinary(4), sys.fn_varbintohexsubstring(0, a.BinSid, 13, 4), 2))) +
        '-' + CONVERT(varchar(20), CONVERT(bigint, CONVERT(varbinary(4), sys.fn_varbintohexsubstring(0, a.BinSid, 9, 4), 2))) +
        '-' + CONVERT(varchar(20), CONVERT(bigint, CONVERT(varbinary(4), sys.fn_varbintohexsubstring(0, a.BinSid, 5, 4), 2))) +
        '-' + CONVERT(varchar(20), CONVERT(bigint, CONVERT(varbinary(4), sys.fn_varbintohexsubstring(0, a.BinSid, 1, 4), 2)))
    FROM
    (
        SELECT
            CONVERT(varbinary(20), REVERSE(HASHBYTES('SHA1', UPPER(@serviceName)))) AS BinSid
    ) a;

The binary SID can also be obtained by using the SUSER_SID function (with some extra processing to handle the fixed portion) in lieu of HASHBYTES. (Note that the raw binary SID must have its endianness reversed before conversion and output. Also, the @serviceName variable being nvarchar is important.)

If you have .NET available, doing the hash calculation is trivial. Here's the C# code to do it:

using System.Security.Cryptography;

...

string serviceName = "MSSQL$SQL2008R2DEV";

HashAlgorithm ha = HashAlgorithm.Create("SHA1");
byte[] hash = ha.ComputeHash(serviceName.ToUpper().SelectMany(c => BitConverter.GetBytes(c)).ToArray());

string sid = "S-1-5-80";

for (int i = 0; i < 5; i++)
    sid += "-" + BitConverter.ToUInt32(hash, i * 4);

I suspect the SIDs could be found using classes in the .NET System.DirectoryServices namespace, either by inspecting group membership (fragile), or by some other method. Unfortunately, the methods I tested to directly translate a principal name into a SID failed, so I'm not sure how SQL Server does it (PInvoke?). In any event, computing the hash isn't a big deal if .NET is available (in fairness, the code will be longer without the use of LINQ).
Capture the relevant part of the output of the sc utility as used below, or run this manually if you don't care about being able to automate the solution:

sc showsid MSSQL$SQL2008R2DEV


Best Practices to reconfigure SQL Server for VMware What I want to know is why would someone configure a server this way.

3 instances of SQL Server on the machine, 64GB RAM, no affinity mask settings and MAXDOP 0 with CostThreshold 5.

I want to give better recommendations for the SQL Server and I'm still analyzing the cause of the performance issues and have collections enabled, as well as using DPA (formerly Ignite) I'll have more info tomorrow.

In my previous infrastructure architecture work, I would have never gone with a 2x10 config on VMware ever, but wondering if things have changed in best practices since vSphere5 days.

What I want to know is why would someone configure a server this way.

Is there a technical reason? I highly doubt it. Is there a different reason? Likely.

They didn't care what they were doing.
They didn't know what they were doing.
They weren't allowed to do what they were meant to do.
The system is so low-use that it doesn't matter what they do.

Either way it's a little irrelevant. There are a few schools of thought on what to do next when it gets reported to you as broken.

Always standardise to minimal best practices before troubleshooting further (max mem, max dop, cost threshold, trace flags, affinity, backups, index maintenance).
As above but only after taking before/after baselines in a way that will let you compare whether it's working better or worse.
Only change what's necessary to get it working again.

It's up to you and they're all justifiable (except maybe in banking and health care).


MYSQL Update data based on top 10 records of each user I have a table users and other table user_scores. In user_scores, I save daily score of every user based on a algorithm. Now I want to calculate AVG user_score by only considering latest 10 records of user_score in user_scores table. I have tried following two queries but not successful with these.

1.

UPDATE      user
SET         user.user_score = (
    SELECT AVG(score) as avg_score FROM (
        SELECT score FROM user_scores WHERE user_id = user.id ORDER BY id DESC LIMIT 0,10)as x)

I am facing error Unknown column 'user.id' in 'where clause' cuz I am not able to pass variable to subquery of the query

Other thing I have tried by JOIN but in JOIN I am not able to calculate AVG(score) of each user based on latest 10 records.

Please suggest.

I may have a solution, but first please see if this computes the avg_scores correctly:

SELECT
        user_id, AVG(score) AS avg_score
    FROM
      ( SELECT  @prev := 0, @n := 0 ) init
    JOIN
      ( SELECT  @n := if(user_id != @prev, 1, @n + 1) AS n,
                @prev := user_id,
                user_id, score, id
            FROM  user_scores
            ORDER BY
                user_id  DESC,
                id  DESC
      ) x
    WHERE  n <= 10
    GROUP BY user_id;

Recommend INDEX(user_id, id, score) on user_scores .

If that works, then a multi-table UPDATE may finish the task:

UPDATE   user
    JOIN ( the above ) AS y  USING(user_id)
    SET  user.user_score = y.avg_score;


SELECTing min, max (maybe length) of continuous id ranges in a table I have a table foo in Postgres (10.3)

# select * from foo;
 id | cust  |  load_dt
----+-------+------------
  1 | cust1 |
  2 | cust1 |
  3 | cust1 |
  4 | cust2 | 2018-05-15
  5 | cust2 | 2018-05-15
  6 | cust1 |
  7 | cust1 |
  8 | cust3 | 2018-05-17
(8 rows)

from which I've been trying to get the start and end ids for each continuous range of ids:

 min | max | cust  |  load_dt
-----+-----+-------+------------
   1 |   3 | cust1 |
   4 |   5 | cust2 | 2018-05-15
   6 |   7 | cust1 |
   8 |   8 | cust3 | 2018-05-17

but all my tries have ended basically to this:

   1 |   7 | cust1 |
...

Length of each range would be a nice touch.

Test stuff:

create table foo (
  id serial,
  cust text,
  load_dt date
);

insert into foo(cust,load_dt)
values ('cust1',NULL),('cust1',NULL),('cust1',NULL),
       ('cust2','2018-05-15'::date),('cust2','2018-05-15'::date),
       ('cust1',NULL),('cust1',NULL),
       ('cust3','2018-05-17'::date);

You can apply a GROUPING AND WINDOW solution.

select min(id) as min,
       max(id) as max,
       cust,
       load_dt,
       grp
from (
      select id, cust, load_dt,
             sum(rst) over (order by id) as grp
      from
      (
          select id, cust, load_dt,
                 case when coalesce(lag(cust) over (order by id), '') <> cust then 1 end as rst
          from   foo
      ) t
     ) t
group by grp, cust, load_dt
order by 1;

This is the result:

min | max | cust  | load_dt    | grp
--: | --: | :---- | :--------- | --:
  1 |   3 | cust1 | null       |   1
  4 |   5 | cust2 | 2018-05-15 |   2
  6 |   7 | cust1 | null       |   3
  8 |   8 | cust3 | 2018-05-17 |   4

db<>fiddle here


Table 1 and Table 2 have matching columns. How do I join the 9th column from table 2 to table 1 based on the matches? My Issue:

I want to know if an account has an active error.

Background Information:

I have 2 tables:
    (1) Work - 9 Columns;
    (2) Errors - 9 Columns

The Work table contains Account Numbers and 8 variables which are associated to an error from the Errors table.

The Errors table contains each possible error. Each error has 8 variables and is either set as on (y) or off (n) with an Active Flag column.

    In table 1 (Work) I have 9 columns:
    (i) acct_number
    (ii) system
    (iii) source
    (iv) object
    (v) group
    (vi) code
    (vii) detail
    (viii) routing
    (ix) complexity

    In Table 2 (Errors) I have 9 Columns:
    (i) active_flag
    (ii) system
    (iii) source
    (iv) object
    (v) group
    (vi) code
    (vii) detail
    (viii) routing
    (ix) complexity

Therefore both tables have the same columns except that the Error table does not have the acct_number column and the Work table does not have the active_flag column.

I am attempting to match (join) the active_flag column from the Error table to the Work table based on a match from the other 8 columns.

The result I am looking would look like this: acct_number, active_flag (where each account number is showing whether or not the error associated is active or inactive).

SELECT *
FROM tbl1
JOIN tbl2
  USING (
    acct_number,
    system,
    source,
    object,
    group,
    code,
    detail,
    routing,
    complexity
  );

But this is insanity. You should be reducing this to a sane primary key. Even if you're primary key is a natural key, this many columns is highly indicative of bad practice.


Why MySQL query is too much slow with sending data status? I don't know why MySQL query is too slow with status "Sending data". I read many questions and found that this status comes when you have slow disk I/O. But I have a good server hosting with 500GB Hard disk and 8GB ram on my server. I am also using indexes on the required columns on each table.

My query with left join:

Select  tbl_songs.id, tbl_songs.song_title, tbl_songs.picture,
        tbl_songs.description, tbl_songs.song_status, tbl_songs.latest,
        tbl_songs.popularity, tbl_songs.posted_date, tbl_songs.ranking_order,
        tbl_artists.id as artist_id
    from  tbl_songs
    left join  tbl_songs_artist  ON tbl_songs_artist.song_id=tbl_songs.id
    left join  tbl_artists  ON tbl_songs_artist.artist_id=tbl_artists.id
    where  MATCH (tbl_artists.artist_name)
           AGAINST ('Vectro Electro' IN BOOLEAN MODE)
    ORDER BY  tbl_songs.ids ASC
    limit  15

Here are few stats below on my server:

free -mh
             total       used       free     shared    buffers     cached
Mem:          7.8G       7.7G       123M        38M        88M       7.0G
-/+ buffers/cache:       617M       7.2G
Swap:         4.0G        33M       4.0G

top - 13:58:57 up 32 days,  9:43,  2 users,  load average: 2.07, 2.23, 1.76
Tasks: 133 total,   1 running, 132 sleeping,   0 stopped,   0 zombie
%Cpu(s): 10.6 us,  5.8 sy,  0.0 ni, 75.2 id,  8.4 wa,  0.0 hi,  0.1 si,  0.0 st
KiB Mem:   8176780 total,  8038168 used,   138612 free,     1200 buffers
KiB Swap:  4194300 total,    40028 used,  4154272 free.  7424124 cached Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
 7683 mysql     20   0 1051368 186976   2984 S  65.8  2.3 161:18.73 mysqld
 4946 root      20   0  104564   3972   2884 S   0.7  0.0   0:00.08 sshd
   52 root      20   0       0      0      0 S   0.3  0.0  26:08.48 kswapd0

MySQL global variables are:

#tmpdir         = /tmp
tmpdir          = /var/mysqltmp

    max_heap_table_size = 512M
    tmp_table_size = 512M
    #sort_buffer_size = 218M
    #join_buffer_size = 5G
    #read_buffer_size = 5G
    #myisam_sort_buffer_size = 218M
    #innodb_buffer_pool_size=2G

    #
    #key_buffer             = 218M
    #max_allowed_packet     = 218M
    thread_stack            = 192K

# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 0
query_cache_type = 0

[mysqldump]
quick
quote-names
#max_allowed_packet     = 218M

[isamchk]
#key_buffer             = 218M

Change LEFT JOIN to JOIN.

Change

AGAINST ('Vectro Electro' IN BOOLEAN MODE)

to

AGAINST ('+Vectro +Electro' IN BOOLEAN MODE)

Have this index on tbl_artists:  FULLTEXT(artist_name).

I assume that tbl_songs has PRIMARY KEY(id).

Assuming that tbl_songs_artist is a many-to-many mapping table, follow the tips in http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

Is this a typo?  tbl_songs.ids

Use ENGINE=InnoDB.

Don't comment out the setting for innodb_buffer_pool_size.

Not more than 1% of RAM for these:

max_heap_table_size
tmp_table_size

("Sending status" is useless info.)


How does one change the default socket used by mysqladmin? How does one change the default socket used by mysqladmin?

I don't see a section for [mysqladmin] in /etc/my.cnf.

It tries by default to connect to /tmp/mysql.sock, however, this is not the socket that my MySQL server uses, so I get the error:

error: 'Can't connect to local MySQL server through
    socket '/tmp/mysql.sock'

I know you can specify the socket at the command line, such as:

mysqladmin --socket='/some/other/path/mysql.sock'

How do I change the default socket used by mysqladmin so I don't have to specify my non-standard path each time?

Add a [mysqladmin] section in your /etc/my.cnf or $USER/.my.cnf and set the socket.  This will override the compiled-in socket and the socket defined in the [mysqld] section.

You should be able to figure it out from what's shown below:

VM52-CentOS58[root@ivdb41 ~]# mysqladmin ping

mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql2.sock' (2)' Check that mysqld is running and that the socket: '/var/lib/mysql/mysql2.sock' exists!

VM52-CentOS58[root@ivdb41 ~]# grep -A 1 mysqladmin /etc/my.cnf

[mysqladmin]
socket=/var/lib/mysql/mysql2.sock

VM52-CentOS58[root@ivdb41 ~]# mysqladmin ping mysqld is alive

VM52-CentOS58[root@ivdb41 ~]# grep -A 1 mysqladmin /etc/my.cnf

[mysqladmin]
# socket=/var/lib/mysql/mysql2.sock


Why I can tnsping Server P from Server A but cannot tnsping Server P from Server B? Here is my situation:
I can ping P from both server A and Server B. But I just can tnsping Server P from A but not from B.

I checked the tnsnames.ora of A and B. They are identical. And I can tnsping other servers from B.

Any thought about it? Should I configure something on Server P?
Thanks a lot and have a great one!

Ping uses ICMP, tnsping checks the connectivity of the host and port defined in the TNS entry.

  I checked the tnsnames.ora of A and B. They are identical.

Yes, and name resolution may be still different. Or more likely the firewall may block connection attempts from host B on the listener port.

Just use telnet, nc or whatever tool you have installed to check the host + port combination:

telnet serverP_ip_address listener_port
nc -z serverP_ip_address listener_port
echo 1 > /dev/tcp/serverP_ip_address/listener_port


Named point in the transaction log? Does it exist? Is it possible to create a named point in the transaction history of the database, such that a subsequent connection could retrieve data at that named point in the log?

For example, lets say there is a loader process that is writing to multiple tables fairly often. At some point, a reader process creates a named point in the log, lets call it point A. The loader does some more updates and now we are at point B. Then another reader process comes along, and can select data as point of A.

Is such a thing possible in SQL server?

Unfortunately, the tables / loader doesn't have a bi-temporal structure, so the reader can't supply an as of datetime as a predicate on the select statements.

You can use a Log Mark to enable a restore to specific logical point-in-time of a database, but you can't use that in queries to the main database.

You can take a Database Snapshot to present a point-in-time consistent copy-on-write snapshot of an existing database.


Performance Metrics be on Own Server or Own Server Instance We would like to collect SQL Server performance metrics (memory, Cpu, page writes/sec, batches/sec,active sessions), etc. I heard its a rule not to collect Sql server metrics on the live Financial Server itself. Place it somewhere else. Should the monitor metrics be on a Different server instance or be on a totally different server? Should I consider Sql server standard, or anything lower?

The issue is mostly because of resource competition. Any tool or method you use to monitor your server will introduce observer overhead. This goes for traces, Extended Events, home grown scripts, or third party monitoring tools.

The goal of putting them on a separate server is to reduce further overhead.

Monitoring queries write a lot to disk at regular intervals, and often get hit with rollup/aggregation queries to report on metrics, smooth data out, etc.

Those queries are usually big and ugly because those tables don't have a lot of indexes. Lots of indexes to help reporting queries would just slow those big/regular writes down.

Production resources should be solely for production data. It's not just a rule for monitoring tools, but for dev, QA, UAT, or demo databases sitting in prod. They're a potential performance liability that's easy to avoid.


SQL Server AlwaysOn AG Secondary Replica is not joined I have created an Availablity Group on SQL Server 2019. I selected 2 SQL server for  Replicas but the Secondary replica has an error:

  This secondary database is not joined to the availability group. The configuration of this secondary database is incomplete. For information about how to join a secondary database to an availability group, see SQL Server Books Online.

When I want to add manually I give an error:

  An availability replica already exists...

What the problem?

I found the problem. It is my authentication method problem. I changed my account and it is resolved.


Why does my query return inconsistent amount of rows? I have what seems like a basic query:

select
item
,prevailingmarketcost as cost
,SuggestedRetail as price
,SuggestedRetail-prevailingmarketcost as profit
from sprucedotnet.dbo.inventorystore
where primaryvendorcode = 'MILWAU184'

It consistently returns 484 rows. However adding a calculated column to the query causes me to get a varying number of runs with each query. How can this be?

select
item
,prevailingmarketcost as cost
,SuggestedRetail as price
,SuggestedRetail-prevailingmarketcost as profit
,(SuggestedRetail-prevailingmarketcost)/SuggestedRetail as margin
from sprucedotnet.dbo.inventorystore
where primaryvendorcode = 'MILWAU184'

The table has ~85180 records and 151 fields, So I won't add the entire table schema unless it is requested. The fields being calculated are DECIMAL(18,4).

Looks like I had a divide by zero error. My database tool was not reporting the error. I was able to reproduce on fiddle. http://www.sqlfiddle.com/#!18/e2ff2/1

Update:

The bug is now fixed.
https://valentina-db.com/bt/view.php?id=8516


I can see a X lock on a RID although the query uses a non-clustered index My initial issue was two update queries, ran in the same transaction, trying to acquire a X lock on a RID on the same table. The second query wants to update a different record in the table, but it would never complete because the first query blocks the table.

In order to fix that issue, I created a non-clustered index.

My question is: After creating that new non-clustered index, I checked the execution plan and I can see that the query uses the new non-clustered index. But, when I look at my locks, I see that the first query still acquires a X lock on a resource_type RID. Why ? I expected to see a X lock on a KEY resource_type.

Non-CLustered Index

    CREATE NONCLUSTERED INDEX [NonClusteredIndex-20170302-092518] ON [dbo].[USERS]
(
    [PARTITION] ASC,
    [DATAAREAID] ASC,
    [WORKBENCHID] ASC,
    [VALID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
GO

Query (acquires a X lock on the RID resource_type, updates 54 rows)

BEGIN TRANSACTION
UPDATE WORKBENCHOPENINVOICES SET VALID=1,RECVERSION=894734738
WHERE (((PARTITION=5637144576) AND (DATAAREAID=N'1010')) AND ((WORKBENCHID='WB10000887') AND (VALID=1)))
    WAITFOR DELAY '00:30:00' -- 5 minutes
ROLLBACK TRANSACTION

By the way, the second query will complete now because I have that non-clustered Index, but I still don't understand why the first query would acquire a lock on a RID instead of KEY when using the non-clustered index
Thoughts ?

Thank you

The update still has to lock the rows being updated regardless of any nonclustered index, so RID locks are required. The nonclustered index helps limit the number of RID locks obtained (and/or avoids table escalation) so the second query is no longer blocked.


How to add newly discovered cell node as member of exadata storage server grid in oem12c? I have successfully discovered a new storage node in oem using emcli

./emcli add_target -type=oracle_exadata -name="da13cel04.myhost.com" -host="da13db01.myhost.com" -properties="CellName:da13cel04.myhost.com;MgmtIPAddr:10.92.43.23"
Target "da13cel04.myhost.com:oracle_exadata" added successfully

But now i want to add this cell node under existing exadata storage grid target in em and i tried below,but it didn't helped

./emcli modify_target -type=’oracle_exadata_grid’ -name=’Exadata Grid da13.myhost.com’ -properties=’Members:da13cel01.nyhost.com,da13cel02.myhost.com,da13cel03.myhost.com,da13cel04.myhost.com’

Need help

Thanks in advance

Got answer for my question.Here is the command to add newly discovered cell nodes under existing exadata server grid

emcli modify_system -name='Exadata Grid da13.myhost.com' -type='oracle_exadata_grid' -add_members='da13cel04.myhost.com:oracle_exadata'

output:
System "Exadata Grid da13.myhost.com:oracle_exadata_grid" modified successfully


Is there any way to filter or ignore messages written to log? My database is behind a load balancer and even though the health check interval is set to 10secs, im getting multiple checks per second which result in thousands of "incomplete startup packet" messages written to the logfile.

This makes it hard to find errors when I have to scroll forever in the log file to find actual errors.

Is there any way to ignore just the message "incomplete startup packet" from being written to the log?

In postgresql.conf the parameter log_checkpoints (boolean) causes checkpoints and restartpoints to be logged in the server log. Some statistics are included in the log messages, including the number of buffers written and the time spent writing them. This parameter can only be set in the postgresql.conf file or on the server command line. The default is off.

Not sure if this is the same thing, is yours commented or not?

#log_checkpoints = off

And anyway if you viewed your log using something like Glogg you could set an exclude pattern like:

^(?!.*incomplete startup packet).*$

As simple as that.


Error when using DBCA to create database Oracle XE 18c I am trying to create a new database using dbca.
Windows 10.
Please help me to find solution.

Here is the log

Sorry it took me a while to get back to this. I see at least two major issues in the trace.log:
First, it appears your environment is misconfigured:

[main] [ 2021-02-03 15:09:37.030 GMT+03:00 ] [StepContext.:429]
Error initializing Network Configuration: Environmental error
detected: Oracle Home is set to "E:\oracle\client_19_3" in the
environment is different from Oracle Net Configuration Assistant
installed Oracle Home.

and second, it appears that you do not have enough physical memory on your host to run a database:

Status     : VERIFICATION_FAILED
Name       : dexp
Type       : Node
Has Results: Yes
Exp. value : 3,1543GB (3307520.0KB)
Act. value : 1,1372GB (1192476.0KB)
Info type  : StorageSize
Info type  : StorageSize
Errors  :
PRVF-7562 : Sufficient available memory is not available on node "dexp" [Required available memory = 3230.0 MB]


What is meant by "minimal" key? I'm going though some past papers for my database exam and it asks:

  Specify all minimal keys for R

R(A,B,C,D,E)
A → B
CD → E
E → A
B → D

I am unsure what is meant by minimal key, I've tried googling it but it only comes up with minimal superkeys. Does it just mean shortest candidate keys: CD, CA, CE, CB?

I'm getting confused as in each question he uses a different name for it and as far as I understand a minimal superkey is a candidate key?

I am in the same exam as you, I have asked Paolo what is meant by this:
Minimal key and candidate key determine all other attributes in a relationship.

a primary key is a candidate key chosen as the smallest number of unique attributes.

He states that a minimal key is the fewest number of attributes so he means a primary key when he says minimal