Database design for attendance feature My app has a requirement wherin we need to capture attendance details of a class. A class typically has 50-60 students and there will be records with attendance/absense status of each student for a day.

My idea is having a table like this:

| student_id | class | section | date       | status      |
------------------------------------------------------------
| 2          | 7     |     A   | 2013-10-24 | 1 or X      |

X - Absent , 1 - Present

This would have records for all months for all students across all classes. So 1000 students, 30 record per student per month, this table must be ending with 1000*30*12 = 360,000 records. We'll be pulling attendance report student-wise and class-wise.

My friend has a suggestion to create a table for each year which would look like this:

| student id  | class   | section | month  | date1 | date 2 | date 3 |......| date30 |
--------------------------------------------------------------------------------------
| 1           | 2       |     C   | Jan    | X or 1|  X or 1| X or 1 |      |X or 1  |

This would mean that I'll have the attendance details of each student for a month in a single record. There would be 1000 (student) * 12 (months) = 12000 records only for an year

Please suggest which would be more suitable here, and if there's any other better way of doing it.

Thanks.

I would suggest something different than you have here- a separate table to record the student_class relation:

| student_id | class | date       |
-----------------------------------
| 2          | 7     | 2013-10-24 |

This would have one record for a student where a class was attended. For each class and date for which there is no record, it can be seen that the student did not attend.

If you want to further normalize the design, I would suggest instead three new tables:

Student_Class: (to determine enrolled students)

| class | student_id     |
--------------------------
| 1     | 2              |

Class_Date: (class_date_id surrogate key for pk(class, date)) (to determine dates classes  were held)

| class_date_id | class | date       |
-----------------------------------
| 1             | 7     | 2013-10-24 |

Student_Class_Date: (to determine which students attended which class period)

| class_date_id | student_id     |
----------------------------------
| 1             | 2              |

This would allow you to easily define which classes were held on which date, easy updating etc.

Edit: Added additional detail with student_class implied relation from question context


SQL Server - Update with Output not working in Parallel environment I know that the output parameter does not guarantees parallelism logic.
And the logic I want to implement is failing in a multi thread environment.

To test this, just open 2 windows and execute them side by side as fast as you can. Then compare the Ids, some will be duplicated.

declare @id int
select @id = 1
while @id >=1 and @id <= 100
begin
    UPDATE sys_QueueJob
    SET StartedOn = GETDATE()
    OUTPUT DELETED.Id as Result
    WHERE Id = (select top 1 qj.Id
                    from sys_QueueJob qj
                    where qj.ProcessedOn is null AND qj.StartedOn is null
                    order by CreatedOn asc)
    print 'Count: ' + convert(nvarchar(max), @id)
    select @id = @id + 1
end

I just want to get the oldest entry from the qeueJob table that has not yet being started and at the same time tell that now it has. And the same code needs to provide me 100% certainty that the next execution will give me the next correct Id.

How can I do this?

While I agree that "the sp_getapplock/sp_releaseapplock approach [is] the most straight forward and least dirty way to manage explicit semaphore logic."

There is a reason that's not working and a way to fix it directly, and enable multiple, concurrent transactions to simultaneously fetch disjoint rows:

This should fix it:

UPDATE sys_QueueJob
    SET StartedOn = GETDATE()
    OUTPUT DELETED.Id as Result
    WHERE Id = (select top 1 qj.Id
                    from sys_QueueJob qj with (rowlock, updlock, readpast)
                    where qj.ProcessedOn is null AND qj.StartedOn is null
                    order by CreatedOn asc)

See Using Tables as Queues


IF statement not skipping TempDB when Looping through Databases with sp_MSForEachDB [SQL Server 2012 SP2 EE]

Why does the following script gives me an error relating to tempdb?

    exec sp_MSForEachDB '
    IF ( (select database_id from sys.databases where name = ''?'') > 4)
    BEGIN
    ALTER AUTHORIZATION ON DATABASE::? TO [sa];
    ALTER DATABASE [?] SET RECOVERY SIMPLE;
    END'

Here is the error I get:

  Msg 5058, Level 16, State 1, Line 5
  Option 'RECOVERY' cannot be set in database 'tempdb'.

It does the job it's supposed to do. But I am unable to think of a reason for the error. I know that the tempdb's databaseID is 2, then at least it should not try to set the option for tempdb.

NOTE TO READERS: Please read the entire question, including the example code (i.e. not just the title). This question is not about how to best cycle through databases, nor is it about why does [tempdb] receive this error. The O.P. is already trying to avoid executing the ALTER statements on all system databases (well, the 4 visible ones) and is asking why the IF statement that should be skipping [tempdb] does not appear to be skipping it.

  Why does the following script gives me an error relating to tempdb?

The reason is that the IF statement only affects what happens when the code is actually running, but SQL Server still has to parse and compile the batch prior to executing it. Parse errors are ones related to syntax, such as making sure that SQL statements are properly formed and that variables have been properly declared:

-- parse the following by hitting Control-F5 or clicking the check mark in SSMS
SELECT @Bob;

gets the following error:

Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@Bob".

And the following:

-- parse the following by hitting Control-F5 or clicking the check mark in SSMS
CREATE TABLE b

gets the following error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'b'.

If the batch parses successfully, then it is compiled, at which time things like permissions are checked and some other checks are performed.

-- parse the following by hitting Control-F5 or clicking the check mark in SSMS
IF (1 = 0)
BEGIN
  ALTER AUTHORIZATION ON DATABASE::[tempdb] TO [sa];
  ALTER DATABASE [tempdb] SET RECOVERY SIMPLE;
END;

The above SQL is properly formed so the batch parses successfully. Now try to execute the above SQL by hitting F5 or Control-E or the ! Execute button, etc.

This time you get the following error:

Msg 5058, Level 16, State 1, Line 4
Option 'RECOVERY' cannot be set in database 'tempdb'.

even though the IF (1 = 0) ensures that the code will never run. This means that you are running into a compilation error. You can get around these types of errors by moving the offending code into a subprocess via an EXEC call.

Execute the following and it will complete successfully since what is inside of the EXEC() is not parsed or compiled until that statement is executed at runtime.

IF (1 = 0)
BEGIN
  EXEC('
    ALTER AUTHORIZATION ON DATABASE::[tempdb] TO [sa];
    ALTER DATABASE [tempdb] SET RECOVERY SIMPLE;
  ');
END;

To summarize:
First, consider that sp_MSForEachDB cycles through the databases and executes your passed-in SQL after replacing the ? with the current database name. So when the cursor inside of sp_MSForEachDB gets to [tempdb], it effectively does the following:

IF ( (select database_id from sys.databases where name = ''tempdb'') > 4)
BEGIN
  ALTER AUTHORIZATION ON DATABASE::tempdb TO [sa];
  ALTER DATABASE [tempdb] SET RECOVERY SIMPLE;
END

Second, there are several steps that SQL Server takes when you execute a query batch:

Parse
Compile
Actual Execution

It is important to understand that these are separate steps and can generate errors before proceeding to the next step (and hence cancel further processing before going on to that next step). In the case here, the error is happening in Step 2 -- Compile -- as proven in the 2nd to last example above (the first one to start with IF (1 = 0)). The IF (1 = 0) prevents the code inside of the BEGIN...END block from ever running, yet the error still occurs. Hence the error is not happening due to an actual attempt to run the two ALTER statements.

The reason that wrapping the ALTER statements inside of the EXEC() function works is because SQL Server will not parse and compile what is inside of the EXEC() until the EXEC() is actually running. At that point, the IF ( (select database_id from sys.databases where name = ''?'') > 4) statement will be allowed to run since the batch won't fail during Compilation and will make it to Execution, and the IF statement will skip over [tempdb] and the "Option 'RECOVERY' cannot be set in database 'tempdb'" error will not occur.


Dynamic script to Drop Indices of a specific column I´m currently writing an SQL script to update our database and for that I need a script to drop indices that rely on a specific column (because I also want to drop the column).

In my script I have used the normal approach by using the following SQL for known indices (before dropping the column via ALTER TABLE).

IF EXISTS(SELECT * FROM sys.indexes WHERE name = '{IndexName}' AND object_id = OBJECT_ID('{TableName}'))
BEGIN
    DROP INDEX {Table}.{Index};
END

But on some systems of our software I cannot use this approach, because there are some indices that are added manually and I need to identify these indices to drop them also.

I have found a good SQL statement on Stack Overflow to list all indices, columns and tables, but I´m stuck how I can select the names of the indices so that I can pass them to DROP INDEX.

Pseudo-code (something like that):

 declare indiciesToDrop = Select indexName from sql-statement where t.Name = {TableName} and col.Name = {ColumnName};

 DROP INDEX indiciesToDrop;

Since you are planning to DROP a column and the indices that support it, I'll assume that doing so is a product of well-thought plan.  Also, I'll assume that you want to drop ANY indice that use the to-be-deleted column, regardless of how it is used.   If so, you can build your script based upon something like below.  You can put this in a stored procedure and pass the parameters, TableName, ColumnName, and SchemaName in this example.

Now, before you run this or any modified version of it, please be clear that once this script starts, all associated indices plus the column will be dropped, and such decision shouldn't be taken lightly.

I'm assuming you have already done your homework, but please allow me.  One situation that might become a problem is when the column you want to drop (C) is a part of a multi-column index that supports a popular query.  For example, let's consider the following structure.

CREATE NONCLUSTERED INDEX nci_tableA_ABC ON dbo.A(A,B,C) INCLUDE (D,E);

SELECT D,E FROM dbo.A WHERE A=@? AND B=@? AND C=@?

If this query is very frequently used hence the index is frequently used, dropping this index will severely impact the performance, and the index needs to be manually recreated albeit without the column C.  I'm sure there are other risks to consider.

Also please note that this script is completely void of error trapping or constraint checking.  If the column you are interested has constraint(s), that need to be dropped before the column can be dropped. You can use sys.objects table to construct dynamic SQL.  (and this might help https://stackoverflow.com/questions/13818837/how-to-check-if-default-value-constraint-exist-not-exist)

SET NOCOUNT ON;

IF OBJECT_ID(N'Tempdb..#IndexList', 'U') IS NOT NULL
    DROP TABLE #IndexList;
GO
DECLARE
    @SchemaName SYSNAME,
    @DropThisIndex SYSNAME,
    @DropThisColumn SYSNAME,
    @CheckThisTable SYSNAME,
    @LoopCounter INT,
    @IndexCount INT,
    @DropColumnSQL NVARCHAR(1000);

SET @SchemaName = N'SchemaName'
SET @CheckThisTable = @SchemaName + '.' + N'TableName';
SET @DropThisColumn = N'ColumnName';
SET @LoopCounter = 1;
SET @DropColumnSQL = 'ALTER TABLE ' + @CheckThisTable + ' DROP COLUMN ' + @DropThisColumn

--First check if the column even exists--

IF EXISTS(SELECT * FROM sys.columns
            WHERE Name = @DropThisColumn AND Object_ID = Object_ID(@CheckThisTable))

    BEGIN --the column exists in the specified table Construct a temporary table that stores name of index to be dropped--
        SELECT
            I.name AS IndexName
        INTO #IndexList
        FROM
            sys.index_columns IC
            INNER JOIN
            sys.objects O
        ON  IC.[object_id]= O.[Object_ID]
            INNER JOIN
            sys.indexes I
        ON  IC.[object_id] = I.[object_id] AND
            IC.index_id = I.index_id
            INNER JOIN
            sys.columns C
        ON  IC.[object_id] = C.[object_id] AND
            IC.column_id = C.column_id
        WHERE O.[type] NOT IN ('S','IT') AND --we don't want anything to do with System or Internal Table
            IC.[INDEX_ID] <> 1 AND --neither Clustered Index
            IC.[object_id] = OBJECT_id(@CheckThisTable,'U') AND
            C.name = @DropThisColumn;

        --Check how many times to loop through--
        SET @IndexCount = @@ROWCOUNT;
        PRINT CAST(@IndexCount AS NVARCHAR) + N' index(es) on the table [' + @CheckThisTable + N'] for the column [' +  @DropThisColumn + '] found'

        IF @IndexCount > 0 --there is at least one index(es) that are associated with the table and the column in question.  Begin loop--
            BEGIN
                WHILE @LoopCounter < @IndexCount+1
                    BEGIN
                        SELECT TOP(@LoopCounter)
                            @DropThisIndex = IndexName
                        FROM #IndexList;
                        --Execute Drop Index statement--
                        PRINT N'Loop ' + CONVERT(NVARCHAR,@LoopCounter) + N': Dropping [' + @DropThisIndex + ']'
                        EXEC('DROP INDEX ' + @DropthisIndex + ' ON ' + @CheckThisTable);
                        SET @LoopCounter +=1;
                    END;
                --Dropping Index is done. Let's drop the column now
                PRINT N'Dropping the column [' + @DropThiscolumn + ']'
                EXEC(@DropColumnSQL);
            END;
        ELSE --There was no index.  let's just drop the column.
            BEGIN
                PRINT N'Dropping the column [' + @DropThiscolumn + ']'
                EXEC(@DropColumnSQL);
            END
    END
ELSE --no column found in the specified table.  Are the input corrects?
    PRINT 'The column [' + @DropThisColumn + '] in the table [' + @CheckThisTable + '] wasn''t found'
;

Now, if your goal is to drop ONLY the indices that has the to-be-dropped column as the key (left most key that is), then you can modify #IndexList  table like below and filter unwanted row, like Clustered Index, before your get the loop count.

CREATE TABLE #IndexList
    (IndexName SYSNAME,
    IndexType NVARCHAR(255),
    Index_Key SYSNAME)

INSERT INTO #indexlist
EXEC sys.sp_helpindex N'TableName';


MySQL replication: 'Duplicated entry for PRIMARY key' Could you please help me to understand why I'm receiving 'Duplicated entry for PRIMARY key' on a slave server after a full re-sync.

Basically 'mysqldump' was running almost whole night and then the restore process took a couple of hours so when I've started the slave it was ~63874 seconds behind master.

The slave server is read only (read_only) and there was no any writes during re-sync process so I don't understand why there are duplicated keys.

Binary log format is set to MIXED on master.

Command used to backup DB:

mysqldump --opt --single-transaction -Q --master-data=2 db | bzip2 -cz > db.sql.bz2

The slave is replicating only one database from master (db -> db_backup) with the following options:

replicate-wild-do-table = db_backup.%
replicate-rewrite-db = db->db_backup

ASPECT #1 : Replication
I don't think that
replicate-wild-do-table = db_backup.%
replicate-rewrite-db = db->db_backup

belong together.
Other people have wondered about this as well

Jun 14, 2012 : Configured MySQL replication but its not working
Jun 13, 2012 : What is the difference between 'replicate-rewrite-db' and 'replicate-do-db ' during mysql replication ?
May 31, 2012 : Mysql replicate-rewrite-db not working

The problem stems from the order replication rules are processed. According to the MySQL Documentation on Replication Rules:

If any --replicate-rewrite-db options were specified, they are applied before the --replicate-* filtering rules are tested.

Even the MySQL Documentation on replicate-rewrite-db says:

The database name translation is done before the --replicate-* rules are tested.

The replicate-wild-do-table is enforced after the rewrite. It would not be surprising if this ordering somehow imposed an INSERT into a table that has data already.
You are probably asking how did the data get there ?
ASPECT #2 : mysqldump
Doing mysqldump --single-transaction would seem to be the greatest way to point-in-time dumps of data. Unfortunately, mysqldump --single-transaction has an Achilles' Heel : ALTER TABLE. If a table is subject to any ALTER TABLE commands, such as a DROP TABLE and CREATE TABLE, that can break the integrity of the transaction the mysqldump was trying to do the dump in. Truncating a table (which is DDL in the MySQL Universe) and dropping and adding indexes can also be as disruptive.
You can find more information on that from MySQL Performance Blog's Best kept MySQLDump Secret. I actually addressed this point in a past question describing 12 commands that can break the integrity of a mysqldump's transaction : MySQL backup InnoDB
CAVEAT

Personally, I would switch binary-format to STATEMENT
Like I said in MySQL backup InnoDB, you should have another slave (without any replication filters) dedicated to doing mysqldumps.
63000+ seconds behind? You should look into doing parallel table dumps : How can I optimize a mysqldump of a large database?

EPILOGUE
One or both of the aspects may have contributed to letting a row slip in during the mysqldump that should not have existed due to either the rewrite rules or the isolation of the mysqldump being overridden.
SUGGESTIONS
I would do a mysqlbinlog dump of all the relay logs since the start of the mysqldump to see all INSERTs that the Slave will process and see if those rows already exist on the Slave. If they do, you could probably do two things:
1 : Skip all the Duplicate Key errors
Simply add this to my.cnf on the Slave
[mysqld]
slave-skip-errors=1062
skip-slave-start

and restart mysql. Then, run START SLAVE;
all the duplicate-key errors will get bypassed. When Seconds_Behind_Master gets to 0, remove those lines and restart mysql.
2 : Download percona tools
The tools you need are

pt-table-checksum
pt-table-sync

Use these to find the differences in the Slave, and then correct them


CentOS and Ubuntu differences with information_schema I have a Vagrant VM running CentOS 6.5.  My localhost is Ubuntu 12.04, both running MySQL.  I have the exact same database on both (structured exactly the same, different testing seed data in both).  My issue is that running the following query gives different results between them:

SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'videos' OR
    TABLE_NAME = 'videos'

The output of describe videos; is exactly the same:

mysql> describe videos;
+-----------------+--------------+------+-----+-------------------+-------+
| Field           | Type         | Null | Key | Default           | Extra |
+-----------------+--------------+------+-----+-------------------+-------+
| id              | varchar(36)  | NO   | PRI | NULL              |       |
| created_at      | timestamp    | NO   |     | CURRENT_TIMESTAMP |       |
| updated_at      | timestamp    | YES  |     | NULL              |       |
| title           | varchar(255) | NO   |     | NULL              |       |
| url             | varchar(127) | NO   |     | NULL              |       |
| provider_id     | varchar(36)  | NO   | MUL | NULL              |       |
| deleted_at      | timestamp    | YES  |     | NULL              |       |
+-----------------+--------------+------+-----+-------------------+-------+

My question is should there be any difference between the contents of INFORMATION_SCHEMA.KEY_COLUMN_USAGE for the foreign keys of a table between CentOS and Ubuntu?  I have no idea why that query would be returning different results.

Results on CentOS:

+------------+-------------+-----------------+-----------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+------------+-------------+-----------------+-----------------------+------------------------+
| videos     | id          | PRIMARY         | NULL                  | NULL                   |
+------------+-------------+-----------------+-----------------------+------------------------+

Results on Ubuntu:

+-----------------+-------------+----------------------------------+-----------------------+------------------------+
| TABLE_NAME      | COLUMN_NAME | CONSTRAINT_NAME                  | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+-----------------+-------------+----------------------------------+-----------------------+------------------------+
| presenter_video | video_id    | presenter_video_video_id_foreign | videos                | id                     |
| videos          | id          | PRIMARY                          | NULL                  | NULL                   |
| videos          | provider_id | videos_provider_id_foreign       | providers             | id                     |
+-----------------+-------------+----------------------------------+-----------------------+------------------------+

A little background in case it helps: The Vagrant VM and Ubuntu host share a Laravel repository folder, so the codebase is exactly the same.  The databases are built using Laravel's artisan migrate command (executing exactly the same code to construct the DB's).

I see two possible reasons:

Your installation script is not correct and failed to create the tables correctly
The CentOS installation does not have InnoDB installed (or activated). MySQL will not tell you about it if you request a non-existing storage engine (or at least not loud and clear)


Stored Procedure started running slowly I have a stored procedure in production which started running slower all of a sudden. I did some investigation and found out that it is using a massive table which has 75 million rows and is growing. Looking at the plan it doesn't show any major missing indexes in the warning. This procedure used to take few minutes earlier however its taking 12 mins now.

Below is the query plan:

https://www.brentozar.com/pastetheplan/?id=ryTS3fIiH

Any help is appreciated.

First issue that I have noticed in the provided plan is --> There are so many implicit conversions as listed below:

<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)"/>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)"/>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)"/>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)"/>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)"/>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)=CONVERT_IMPLICIT(nchar(11),[f].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)=N'0002'"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)=CONVERT_IMPLICIT(nchar(11),[f].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)=N'0008'"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)=CONVERT_IMPLICIT(nchar(11),[f].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)=N'0017'"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)=CONVERT_IMPLICIT(nchar(11),[f].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)=N'0019'"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)=CONVERT_IMPLICIT(nchar(11),[f].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)=N'0004'"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)=CONVERT_IMPLICIT(nchar(11),[f].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)=N'0020'"/>

Due to this issue you have issue in cardinality estimate and also in Seek Plan.

Please read more on this from below links:

https://www.sqlskills.com/blogs/jonathan/implicit-conversions-that-cause-index-scans/

https://hackernoon.com/are-implicit-conversions-killing-your-sql-query-performance-70961e547f11

https://www.sqlservercentral.com/forums/topic/type-conversion-may-affect-cardinalityestimate

https://www.sqlshack.com/implicit-conversion-in-sql-server/

Whenever you see yellow mark on the Select box in the query plan, please hover that and see what is the warning. Most of the time, it would give you very meaningful feedback.

Another issue I see is with the table CDB_MEMBER, its going for clustered index scan, meaning full table scan and if you would have noticed there is minor difference in estimates and actuals suggesting stats are a bit out of sync. Important aspect here is missing NC index which should have columns CDB_MBR_COV_REC_ID and SBSCR_ID

I think above two should help you a bit in fine-tuning the execution and reduce duration.

Please let us know if this helped.


How does Yelp efficiently calculate distance in the database? For example, say I have a table:

Business(BusinessID, Lattitude, Longitude)

All are indexed of course. Also there are 1 million records

Say I want to find businesses closest to 106,5, for example, how would I do so?

If I do

SELECT *
FROM Business
WHERE (Some formula to compute distance here) < 2000

for example, or if I do

SELECT *
FROM Business
TOP 20

In theory the computer will have to compute distance for all biz while in practice only those with lattitude and longitude within a certain range that should be computed.

So how can I do what I want in PhP, or SQL, for example?

I am grateful with the answer so far. I am using mysql and they do not have anything more efficient than the obvious solution. MySQL spatial do not have compute distance function either.

If I understand the question correctly (and I'm not sure I do), you are worried about computing "(Some formula to compute distance here)" for every row in the table each time you do a query?

This can be mitigated to a degree by using the indexes on latitude and longitude so we only have to compute the distance for a 'box' of points containing the circle we actually want:

select * from business
where (latitude>96 and latitude<116) and
      (longitude>-5 and longitude<15) and
      (Some formula to compute distance here) < 2000

Where 96, 116 etc are chosen to match the unit of the value '2000' and the point on the globe you are calculating distances from.

How precisely this uses indexes will depend on your RDBMS and the choices its planner makes.

In general terms, this is a primitive way of optimising a kind of nearest neighbour search. If your RDBMS supports GiST indexes, like postgres then you should consider using them instead.


Will table partitioning speed up my queries? My database is set up with what we call our staging tables and our live tables. Currently for we have about 2 million rows for a table called products. Our customers we call manufacturers load their data into the staging tables and when they are done updating, adding, deleting data we then push that data into our live tables.

These live tables feed a web service for a mobile app and websites. The pushing of data from staging to live consists of deleting all the data in the live tables and inserting all of the data from the staging tables. All of this is segregated by a column called ManufacturerID that exists in every table.

Some manufacturers have 500 products, some have 75,000. Depending on this we sometimes have REALLY REALLY slow web service responses because of paging through all of the 2 million records. Deleting the data from the live tables also seems to be getting ridiculously slow.

Would partitioning my products table by ManufacturerID help this situation? From what I have read this would basically mean when I am querying my products I would only be querying a small subset of the database on that ManufacturerID and therefore see a huge improvement in overall response time.

No. Partitioning does not speed things up. Your table that has 2 million records before partitioning, it will continue to have exactly the same 2 million records after partitioning. If you want to look only at a small subset of the records use an index. It does seem like your data is a multi-tenant schema with the tenant key be the ManufacturerID. In such cases the most likely design is to have the ManufacturerID be a leading key in the clustered indexes. BTW, I recommend reading Multi-Tenant Data Architecture.

Partitioning would help in scenarios involving fast data switch-in and switch-out, or in scenarios requiring distribution of data do different physical paths (filegroups). A good read is How To Decide if You Should Use Table Partitioning.

Before you say 'but what about partition elimination?' I'll say this: there's almost nothing partition elimination can do that an index cannot do better. Not to mention that partition elimination is a lottery while range scan on leading key in clustered index is pretty much a sitting duck. Besides, how many tenants will you have per partition? And how will you distribute them evenly, consider how unbalanced they seem to be? Both these problems are, again, addressed much better by a leading key in the clustered index. And, not least, consider the huge design changes partitioning brings (e.g. kiss goodbye the unique/primary key constraints or introduce unaligned indexes), see Special Guidelines for Partitioned Indexes, pay special attention to the 'Memory Limitations' section.

Partitioning is an awesome feature, but is not a performance feature. An example of what partitioning could help is to do a very fast switch-in of the staged data (which is a problem you raised), but that would require one partition per manufacturer, and it will not scale, assuming that each 'manufacturer' is a client of your business (a tenant).


Create table from stored procedure result I am new to this field if you can please tell me if it is possible to do this it will be very help full. I have SQL Server 2008 R2.

What I want to do is from my table I want to take first row and try to use it as a column name to create another table in a stored procedure. The reason why I want to use those particular column name is which will come in as data in first row is, it will keep changing every week since those are dates.

So my plan is if it is possible to extract that first line from data which is ([Employee Name], [10/18/2013], [10/19/2013]..) and create another table which will use that extracted data as "column name" and create another SP will fill data as required. But I have no clue if SQL will create table and use "column name" which extracted from another data.

Further information in response to the answer:

What I am trying to do is. I have data coming in as time sheet every week. my plan is once I will get time sheet in every week as excel I will some how import into SQL as Import/export. the only problem is that Time Sheet have columns start from [Last Name], [First Name], [10/21/2013],[10/22/2013],[10/23/2013],...,[10/24/2013]. and I need those date columns in my data so I can use those in my later store procedure. in order for it do, I have included those columns in my data and not in headers when I import, so I can use those data. after doing that i have wrote one simple store procedures, which only read first row which will be first name, last name, 10/21/2013, 10/22/2013 and so on to extract header from the data. and from their i want to execute a store procedure to create new table every week(since date will be change every week) and will have first name, last name, 10/21/2013, hours of work, Taxonomy( will have 3 characters from fist name, last name, date and proj name) in different table. once the time push for that week at the end i will delete that table.

I apologize if that sounds confusing. Do you think I am going on right direction. if you know any links that explain this type of case, it will be so helpful since i am new to this field.

If you're really committed to this type of activity then there is a lot of dynamic sql in your future.  Everything you describe is possible.  But tread very lightly as dynamic sql opens some doors that you may wish later on had stayed closed.

One of the most classic articles in the history of dynamic sql can be found here.  It's called the blessing and curse of dynamic sql.  You give that a thorough review and you should understand your options better.

http://www.sommarskog.se/dynamic_sql.html

This specific section is very pertinent to your question.

http://www.sommarskog.se/dynamic_sql.html#Cre_tbl

Note that the author identifies a key point.  If you're doing this your violating some fundamentals of relational database design.  Now, I'm all for breaking the rules, as long as you understand the tradeoff and the purpose of the rules being broken.  But, the question is valid.  What are you really trying to accomplish?


Foreign Key constraint on fixed value field Note: I am a developer...

I have an Asset table that has many codes that are foreign keys into a second table OutlineFiles. The OutlineFiles table has Type and Code as the primary key. The foreign key constraint need to look like this...

ALTER TABLE "dbo"."Assets"
ADD CONSTRAINT "Locations" FOREIGN KEY ( "LN", "LocationCode" ASC )
REFERENCES "dbo"."OutlineFiles" ( "Type", "Code" );

ALTER TABLE "dbo"."Assets"
ADD CONSTRAINT "CostCentre" FOREIGN KEY ( "CC", "CostCentreCode" ASC )
REFERENCES "dbo"."OutlineFiles" ( "Type", "Code" );

So there will be a relationship to the outlines table via a fixed value of "LN" and "CC". Or will it be easier to change the database to have two table and remove the Type column on the OutlineFiles table? I am attempting this in Sybase BUT we support many databases.

It sounds like you have a "One True Lookup Table" (OTLT) anti-pattern and you are mixing entities in this table. You've found why it isn't a good idea:

can't have filtered foriegn keys
can't FK to constants
can't have multiple parents

Your sample code above is confusing (you have multiple parents for the same Code column) so I'll give you what I understand

CREATE TABLE OutlineFilesCostCentre (
    CostCentreCode ...NOT NULL --PK
    ...
    )

CREATE TABLE OutlineFilesLocations (
    LocationCode ... NOT NULL --PK
    ...
    )

CREATE TABLE Assets (
    ...
    CostCentreCode ... NOT NULL,
    LocationCode ... NOT NULL,
    ...

    CONSTRAINT FK_Assets_CostCentre" FOREIGN KEY ("CostCentreCode")
                REFERENCES "dbo"."OutlineFilesCostCentre" ("CostCentreCode"),
    CONSTRAINT FK_Assets_Locations" FOREIGN KEY ("LocationCode")
                REFERENCES "dbo"."OutlineFilesLocations" ("LocationCode")
   ...)

If you have 30 codes to lookup you will have 30 lookup tables: this is correct.

If you insist on OTLT you'll have to add extra columns to store type in Assets and FK them to your OTLT. I wouldn't do this.

    ...
    CostCentreType char(2) NOT NULL,
    CostCentreCode ... NOT NULL,
    LocationType  char(2) NOT NULL,
    LocationCode ... NOT NULL,
    ...

Or use triggers to maintain the correct codes. I wouldn't do this either.


Should i expect to execute of 160billion iteration from oracle 11g? I have a table that contains approximately 390 000 records and up to now I found 3 different algorithm(solution) for my problem. Each algorithm require cartesian product of table with itself.
I am wondering only that; should i expect from oracle 11g to execute a query requires 160 Billion iteration or what? Because for one week I try a lot of things but whatever I do it is not end even after 8 hour execution.

If this is normal for this much process I maybe start thinking of a new algorithm because I must ended up in approximately 30 minutes.

My last and better among three algorithm is;

declare
loopCount number(10);
remainingRows number(10);
dup number(10);
crosses number(10);
res varchar2(5);--TRUE or FALSE
BEGIN
loopCount :=0;
remainingRows :=0;
crosses :=0;
dup := 0;
res :='FALSE';
SELECT COUNT(*) INTO remainingRows FROM ORAHAN;--376497 records
WHILE(remainingRows ^= 0) LOOP
 FOR aRow IN (SELECT mip,startmi,mi_prinx,geoloc,rownum FROM ORAHAN where rownum=1)  LOOP
  FOR bRow IN (SELECT mip,startmi,mi_prinx,geoloc FROM ORAHAN WHERE Mi_Prinx ^= aRow.Mi_Prinx) LOOP
    BEGIN
      --loopCount := loopCount+1;
      select SDO_GEOM.RELATE(aRow.geoloc,'anyinteract', bRow.Geoloc,0.02) into res from dual;
      IF (res='TRUE') THEN
        Insert INTO ORAHANCROSSES values (aRow.Mip,aRow.Startmi,bRow.Mip,bRow.Startmi);
        DELETE FROM ORAHAN WHERE MI_PRINX=bRow.MI_PRINX;
        COMMIT;
        crosses := crosses+1;
        END IF;
      EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
          dup := dup+1;
    END;
    remainingRows := remainingRows-1;
  END LOOP;
DELETE FROM ORAHAN WHERE MI_PRINX=aRow.MI_PRINX;
END LOOP;
SELECT COUNT(*) INTO remainingRows FROM ORAHAN;
END LOOP;
dbms_output.put_line('crosses: ' || crosses);
dbms_output.put_line('duplicate: ' || dup);
END;

I execute this and in 3 hour it is delete 604 record from source table and insert 99 row to target table. There is 376342 remaining rows.

We use SDO_RELATE function which is very fast against SDO_GEOM_RELATE. But SDO_REALTE has no parameter as buffer. We use SDO_GEOM.SDO_BUFFER for giving 2cm buffer to the target object. Now it takes one hour for intersection all records with each other.
Here is working code;

BEGIN
for curs in (select * from ORAHAN t) loop
 for curs2 in (select *
           from ORAHAN t2
          where SDO_RELATE(t2.geoloc,SDO_GEOM.SDO_BUFFER(curs.geoloc,0.2,0.02,'UNIT=M') ,
          'mask=ANYINTERACT') = 'TRUE'
            and t2.mi_prinx <> curs.mi_prinx) loop
  Insert INTO ORAHANCROSSES
   values
   (curs.Mip, curs.Startmi, curs2.Mip, curs2.Startmi);
  commit;
 end loop;
end loop;
END;


Failed to start TSQL debugger I want to debug in TSQL.When I start the debug the following message box popup. In SSMS v17.2

Then I see this message:

In Microsoft Visual Studio 2015 Server Explorer same Error:

Then I see this message:

Thank You!

Debugging is a highly privileged operation. Therefore, only members of the sysadmin fixed server role are allowed to debug in SQL Server.

See How to: Enable Transact-SQL Debugging for details on the requirements.

There also several Connect items discussing this and listing some possible scenarios:

Debugging without sysadmin permissions

  While I understand the frustration behind lack of debugging support for non-sysadmin members, there are lot of challenges in enabling such support in the engine. We looked at in the past & didn't find a clean way to isolate the debugging experience in the server without affecting other processes.
  Below are some key issues:

  Debugging in general requires higher privileges, this is true of SQL Server or OS for that matter
  Debugging SQLCLR code in SQL Server for example can freeze other SQLCLR tasks in the server severely affecting performance
  Debugging endpoints are DCOM based today and it has its own restrictions
  Lastly, debugging T-SQL code in a production server is not something we recommend. The scenario is primarily for development purposes and there are alternatives which I will describe below

  You can use Visual Studio or SQL Server Management Studio or SQL Server Data Tools (SSDT)to debug against a local copy of SQL Server. In SQL Server 2012, we also have the SQL Server Express LocalDB SKU which is used by SSDT. Today, the surface area / features supported in LocalDB is a subset of SQL Server but our long-term goal is to enable all features that are supported in SQL Server including SQL Azure.
  So our recommendation is to use the local dev environment to debug the T-SQL logic. Additionally, you can obtain clone database (which includes statistics without data) to repro query plan related issues too. This will work for most cases other than environment specific problems or missing features in LocalDB. Hope this helps.
  Umachandar, SQL Programmability Team

T-SQL debugging only with sa privileges!?
Debugging SQL Query fails with an error

  The minimum security requirement is sysadmin. This is by design from SQL engine security.


How to erase in sqlplus with backspace? Using SQL*PLUS, I cannot use the backspace button to erase content during a line command.

When I hit backspace, instead of erasing it writes ^H

Is there a way to configure backspace so it works as intended ?

If you're using Linux/Unix, there's a way of making SQL*Plus loads friendlier on the command line - use rlwrap. It adds readline support to SQLPlus, allowing you to use the cursor keys to navigate to previous queries (it retains a history), and edit them when you've made a mistake.

Launch SQL*Plus with rlwrap -i sqlplus or add  alias sqlplus="rlwrap -i sqlplus" to your shell dot file (.bashrc/.cshrc/etc).

To install rlwrap on RHEL 5.0 use the following commands:

wget http://download.fedoraproject.org/pub/epel/5/i386/epel-release-5-4.noarch.rpm
rpm -Uvh epel-release-5-4.noarch.rpm
yum install rlwrap
rlwrap –i sqlplus


How to achieve using TRIM command on SELECT * From TABLE I am building DWH where in source data dump is made through SELECT * query into DB. I am taking dump from the source DB, to save time (else had to write 115 select stmt using TRIM where average number of filed per table is 150) I used SELECT * FROM TABLE query to get the dump post which I am executing SED command to remove spaces from the fields.

This is taking a huge time & space as first SELECT query dumps the table and sed trims the Spaces. I have written a BJ to download the files & conversion.

There is space constraint in my server and I cannot mount extra space.

I am looking forward for inputs/tools/package/procedure on how can still use SELECT * but there are no spaces in the fields and I can avoid using sed command in UNIX.

If I understand you correctly, this PL/SQL will do the job for you:

DECLARE
   CURSOR c1 IS SELECT table_name FROM USER_TABLES;
   QUERY varchar(4000);
BEGIN
  FOR rec IN c1 LOOP
    SELECT 'SELECT '||RTRIM(EXTRACT(COLXML,'//COLUMN_NAME/text()'),',')||' FROM '||rec.table_name||';' COL_STRING INTO QUERY
    FROM (SELECT E.table_name,
      xmlelement("USER_TAB_COLUMNS",
      XMLAGG(xmlelement("COLUMN_NAME", case when data_type like '%CHAR%' then 'TRIM(' end ||
      column_name||
      case when data_type like '%CHAR%' then '),' else ',' end)
    ORDER BY COLUMN_NAME)) COLXML
    FROM USER_TAB_COLUMNS E WHERE TABLE_NAME=rec.table_name
    GROUP BY E.table_name );
  DBMS_OUTPUT.PUT_LINE(QUERY);
  END LOOP;
END;
/

Make sure you set serveroutput on to get the output from DBMS_OUTPUT.

Change the query in the CURSOR to alter the table list it generates queries for.

Note: Doesn't do anything for 'CLOB'/'BLOB' columns.

Example output:

SELECT A,TRIM(B),TRIM(C) FROM BLAH;
SELECT A,B FROM EDITIONTEST;
SELECT C,D FROM EDITIONTEST2;

PL/SQL procedure successfully completed.

Another thing to mention; You're going to be limited to the 4000 character limit, but will be OK unless you have tables with hundreds of columns.


Getting average event duration I am writing a query for getting average value of event duration for given period of time.
We might consider that we do have table with 2 columns:

start               | end
-----------------------------------------
2019-10-14 18:16:08 | null
2019-10-14 19:39:47 | 2019-10-14 23:28:17
2019-10-16 03:29:33 | 2019-10-16 03:31:08
2019-10-16 21:43:34 | 2019-10-17 02:07:31

The wanted behaviour would be to take avg. time for each day. The first row should be ignored, cuz the task is not finished - it might be still going / infinite. For second row we would get avg. time of

(2019-10-16 03:31:0 - 2019-10-16 03:29:33) + (2019-10-16 21:43:34 - 2019-10-16 23:59:59) / 2 -- 2 events only

And the for 17th day we would get 2:07:31.
So far I've got this query:

SELECT
    DATE_FORMAT(start, '%Y-%m-%d') AS time_label,
    AVG(TIME_TO_SEC(TIMEDIFF(end, start))) AS value
FROM
    time
GROUP BY start

which is not working as expected. I might add WHERE clause but it won't work. I've tested it on much bigger table.

You have it almost right, by grouping by start it would only group the same date and time, but when you group by time_label, it takes the individual days with out the time

SELECT
    DATE_FORMAT(start, '%Y-%m-%d') AS time_label,
    AVG(TIME_TO_SEC(TIMEDIFF(end, start))) AS value
FROM
    timetable
GROUP BY time_label

Result

| time_label | value |
| ---------- | ----- |
| 2019-10-14 | 13710 |
| 2019-10-16 | 7966  |

View on DB Fiddle


Huge size on mongodb's gridfs. Should I compact? I’m running MongoDB and grid.fs to store small files (less than 20mbs). These are part of a replica set. I currently have more than 350000 files stored.

I’ve noticed that the chunks collection takes around 700GB of preallocated space where the actual chunks consist of ~40GB. Even though there are 700GB of data preallocated, this keeps expanding over time.

Keep in mind that every 15 minutes or so I delete files older than 5 days. So in theory my fs.chunks and fs.files size should remain around the same over time.

Here's my fs.chunks stats

rs0:PRIMARY> db.fs.chunks.stats()
{
    "ns" : "collection.fs.chunks",
    "count" : 470388,
    "size" : 43295062144,
    "avgObjSize" : 92041.17057407927,
    "storageSize" : 757794040352,
    "numExtents" : 373,
    "nindexes" : 2,
    "lastExtentSize" : 2146426864,
    "paddingFactor" : 1,
    "systemFlags" : 1,
    "userFlags" : 0,
    "totalIndexSize" : 40356736,
    "indexSizes" : {
        "_id_" : 17431232,
        "files_id_1_n_1" : 22925504
    },
    "ok" : 1
}

Is this behaviour normal? Can I compact (defrag?) the chunks collection or even claim that preallocated space ? If I cannot reclaim that space (which I’m 99.9% sure I can’t) is there a way to ensure that the preallocated space will be used eventually rather than keeps expanding?

You have few options here.

Run DB command 'Compact' on the collection - This will not reclaim disk space, but will perform defragmentation over the specific collection.
Perform a repairDatabase - This reclaims disk space back to the OS. the repairDatabase runs over the entire DB (or shard) and not just one collection (I would read the documentation, as repaireDatabase both locks the DB it's repairing and requires at least x2 of disk space than your DB size)

Please keep in mind that MongoDB always seeks to allocate more data files.
If indeed as you mentioned, you're deleting a lot of data, Mongo will reuse this space but it might contain 'junk' blocks which makes mongo use less than it actually has allocated. this is why Mongo is allocating more and more data files.

You can read more about it here: Managing disk space in MongoDB

Hope this helps a bit.


What's difference between these 2 statements? Which is correct? or both? Why?

1st:

INSERT INTO table (num)
VALUES (SELECT COUNT(*) + 1 FROM table);

2nd:

INSERT INTO table (num)
VALUES (SELECT COUNT(*) FROM table + 1);

I was answering the question it its first form, where there were parentheses and the queries were syntactically correct (before the edits that removed parentheses):

  Which is correct? or both? Why?

  1st:

INSERT INTO table (num) VALUES
((SELECT COUNT(*) + 1 FROM table));

  2nd:

INSERT INTO table (num) VALUES
((SELECT COUNT(*) FROM table) + 1);

In this instance, they are identical except for perhaps the execution plan which honestly probably wouldn't be very different anyways.
I prefer the first one in terms of readability.  Generally, unless there is a windowed function or sub-query, there's no reason to have the +1 after the full select/from statement.  There's no really a good reason to nest the statement with all the extra parenthesis.  In fact, I could see how the second option would cause some confusion in terms of readability.

One more point, when you're using a SELECT for the values on an insert statement, you don't actually need the keyword values.  To simplify try this

INSERT INTO table (num)
SELECT COUNT(*) + 1
FROM table


How to list all the indexes along with their type (BTREE, BRIN, HASH etc...) I'm querying to the system catalog in Postgresql 9.6.4

Getting a result set of tables and their indexes is straight forward,what I'm missing is the index type (BTREE, BRIN, etc..) I can't seem to find the type of index anywhere in the system catalogs.

How can I query the catalogs to get a list of indexes along with their type?

The types of indexes in Postgresql are stored in the pg_am catalog table. So to get a list of all tables and their indexes with the index type (or access method ("am") as PostgreSQL calls it) you can run the following

SELECT tab.relname, cls.relname, am.amname
FROM pg_index idx
JOIN pg_class cls ON cls.oid=idx.indexrelid
JOIN pg_class tab ON tab.oid=idx.indrelid
JOIN pg_am am ON am.oid=cls.relam;


Easy path to REST on SQL Server? At work, one of our primary databases is running on MS SQL Server. I think it's 2008.

I'm currently hitting it via UnixODBC and FreeTDS from a Linux host. Unfortunately, the UnixODBC/FreeTDS are not supported by our admins, so it falls on me. For a long-term strategy I want to move off ODBC and switch to HTTP, using either SOAP or REST calls. The installation is a lot easier with HTTP and most languages understand it, at least Perl, Python and Ruby, which I develop in.

We've got a decent amount of SOAP calls set up on the database for some Java processes, so we can probably switch to that pretty easily. We were discussing REST also, which I like because it's lighter-weight. Our DBAs don't have any REST interfaces set up, so I was wondering if there is a plugin or tool for SQL Server that can do simple REST mappings of the tables to get us jump-started. I've considered writing something in Ruby's Sinatra but that'd be one more thing on my plate.

I searched for "SQL Server + rest" but it's pretty obvious that the Googles will do the wrong thing with the word "REST" and, as a consequence, didn't find anything worth while. So, if you have experience with something providing REST and felt it was a good tool then chime in.

If you're on SQL Server 2008 R2, I'd recommend taking a look at the Open Data Protocol (also known as OData). Microsoft has some FAQs on it as well, and there is a Java client library available.

Also take a look at sqlREST, which exposes relational databases using web services and works with most databases that have a JDBC driver.


How to handle listener failure in AG? Availability group has concept of listener that directs the requests to appropriate replica.
So is listener a single point of failure? How to handle this?

On Windows, the listener is a WSFC cluster resource with a dependency on the availability group. It is hosted by the current primary AG node and will move to other nodes of the cluster when the primary role switches, providing high availability.


Mysql database replication (failover solution) Is there anyone know how to do "mysql database replication" with appfog ? In simple word, just want to use 2 databases sync (2 locations) and set one as master database, and other one as slave. if master server down, instantly slave server should act as master database. Actually databases are hosted on 2 shared servers. because lack of the space provided by appfog. :)

Facebook's DBA YOSHINORI MATSUNOBU has developed MHA :

  A primary objective of MHA is automating master failover and slave promotion within short (usually 10-30 seconds) downtime, without suffering from replication consistency problems, without spending money for lots of new servers, without performance penalty, without complexity (easy-to-install), and without changing existing deployments.

The project link : http://code.google.com/p/mysql-master-ha/

Max.


Querying an array of strings with a string that represents search terms I have an array of values like:

array ('hello world', 'foo', 'bar') -- terms here have only letters and one space at most

and some text like: 'foo hello-world hello1world'

and I would like to get back from the original array the values foo and hello world filtering out bar.

Is there any way to get that with Postgres?

I would do something like this,

create a helper function to generate your query terms from the text-string input. You could inline this into the function, but it's ugly and silly and if you need it here then you probably need it elsewhere.
Write a query with NOT word LIKE ANY()

Here is a demo of the helper function,

CREATE OR REPLACE FUNCTION my_stupid_query(q text)
RETURNS text[] AS $$

  SELECT ARRAY(
    SELECT regexp_replace(t, '[^[:alpha:]]', '_', 'g')
    FROM unnest(string_to_array(q, ' ')) AS q(t)
  );

$$ LANGUAGE sql
IMMUTABLE;

Then you can use NOT word LIKE ANY(). This shows what it would look like,

SELECT word
FROM (VALUES (ARRAY['hello world', 'foo', 'bar'])) AS t1(x)
CROSS JOIN LATERAL unnest(x) AS t2(word)
WHERE NOT word LIKE ANY(my_stupid_query('foo hello-world helloXworld'));

 word
------
 bar
(1 row)


Unique time range or database schema for a scheduling system I'm having to design a table which contains scheduled times for media to be displayed. These scheduled times should not overlap.

In my SheduledSlot table I have:

id    integer PRIMARY KEY
begin datetime(or integer for SQLite)
end   datetime(or integer for SQLite unixtime)
media_item integer (foreign key)

How do I enforce a constraint which ensures a unique time range for each scheduled item?

You should implement this on the database side in the method that can be called by your create schedule time and modify schedule time methods.


Best practice for storing large files (+1GB each) I am looking to build a big data platform for weather forecasting. I will be using a numerical model known as WRF which takes in GFS files and produces files which can be over 1GB each.

I will be using either AWS or Azure as my cloud provider.

These 1GB files will be processed and a lot of the  variables will be removed, reducing file size, but exactly how much - I do not know.

Now, the 1GB files may be needed later (it's expensive to run the model), so I do not want to throw them away.

Do I store them as files on a disk somewhere  (ie the cloud) or do I put them in a DB? Of course, the files will be compressed, but they are still going to be massive...

As for the type of data, the files are binary and the data is like a big spreadsheet but not every row will have the same data... what I mean is that row 1 might have 6 columns whereas row 2 might have 5 columns. To make it worse, the column names are not always identical and some may have a value where others may not. I can however write a script which will ensure the records all have the same column names. I realise some of this information may not be relevant, so I apologise if it is not.

If I understand correctly that you only want to Archive the files produced in case you need the occasional one for referring to later, then cloud file storage would be cheaper and simpler than storing them in a DB.


Is it possible to make a reference to the result of an aggregate function in a SELECT clause from the same SELECT clause? I'm not a Database Administrator--just a Software Engineer. I would like to know if it is possible to reduce the following T-SQL query:

SELECT
  SUM(Price * Quantity) as 'Total',
  SUM(Price * Quantity) * 0.95,
FROM
  SomeTables;

To something like this:

SELECT
  SUM(Price * Quantity) as 'Total',
  'Total' * 0.95,
FROM
  SomeTables;

Note: This is just a sample to clarify/support my question. My current SQL query is larger and more complex than this.

Nope. Only your ORDER BY clause can reference assigned aliases in the same query.

I suggest declaring a CTE that computes the first value, and then computing the second value in a query against that CTE.

For example:

WITH totals AS (
   SELECT SUM(Price * Quantity) AS Total
   FROM   SomeTable
)
SELECT
     Total
   , (Total * 0.95) AS DiscountedTotal
FROM totals;

Think of a CTE as an inline, disposable view. It is valid only for the query that immediately follows it. In that regard, it doesn't give you any performance benefit over doing the same thing with a derived table or with an actual view, or over computing the total twice like in your original query.

Of course, using a CTE does have an advantage over calculating the totals twice in two different queries, and it does look cleaner than all the other approaches.


Mysql replication and ignore tables When setting up master/slave replication in Mysql, do you then need to ignore any tables in /etc/mysql/my.cnf or should replicate_wild_ignore_table be leaved out under normal circumstances?
This is what I'm doing...
my.cnf
replicate_wild_ignore_table = mysql.%,information_schema.%,phpmyadmin.%

Please change the directive to one of the following:

replicate_wild_ignore_table=phpmyadmin.%

or

replicate_ignore_db=phpmyadmin.%

mysql database

No need to do mysql. Why? Doing GRANT and REVOKE commands will bypass replicate_wild_ignore_table=mysql.% because the SQL does not explicitly mention mysql schema tables.

This will get by replicate_wild_ignore_table=mysql.%:

GRANT ALL PRIVILEGES ON *.* to rolando@locahost;

This will get caught by replicate_wild_ignore_table=mysql.%:

INSERT INTO mysql.user VALUES (...);

If you want to keep replicate_wild_ignore_table=mysql.%, I suggest the following:

SET sql_log_bin = 0; INSERT INTO mysql.user VALUES (...);

This will prevent the SQL from being recorded in the master's binary logs. Consequently, all SQL executed in the DB Session after SET sql_log_bin = 0; will not replicate.

information_schema database

As far as the information_schema database, mysqld uses it to monitor database metadata. Each is unique to the MySQL instance. They never replicate intrinsically because you have the option to keep different table on Master and Slave. If the information_schema was replicated, then creating replication schemes such as

using the BLACKHOLE storage engine for Star Topologies
using an all-InnoDB Master and an all-MyISAM slave

would be impossible for Slaves to handle.

SUMMARY

Doing

replicate_wild_ignore_table=phpmyadmin.%

or

replicate_ignore_db=phpmyadmin.%

should be all you need. Notwithstanding, make sure any SQL that prepends phpmyadmin to all it table names may still slide by if the default database is not phpmyadmin.


Negative amount of remaining transactions before wraparound Running a PostgreSQL 11 instance with a table of more than two billion rows.

I get the following output when I query for the remaining transactions before a transaction identifier wraparound happens:

Query source:

SELECT
   oid::regclass::text AS table,
   age(relfrozenxid) AS xid_age,
   mxid_age(relminmxid) AS mxid_age,
   least(
(SELECT setting::int
        FROM    pg_settings
        WHERE   name = 'autovacuum_freeze_max_age') - age(relfrozenxid),
(SELECT setting::int
        FROM    pg_settings
        WHERE   name = 'autovacuum_multixact_freeze_max_age') - mxid_age(relminmxid)
) AS tx_before_wraparound_vacuum,
pg_size_pretty(pg_total_relation_size(oid)) AS size,
pg_stat_get_last_autovacuum_time(oid) AS last_autovacuum
FROM    pg_class
WHERE   relfrozenxid != 0
AND oid > 16384
ORDER BY tx_before_wraparound_vacuum;

Output:

-[ RECORD 1 ]---------------+------------------------------
table                       | tablename
xid_age                     | 2146483650
mxid_age                    | 0
tx_before_wraparound_vacuum | -1946483650
size                        | 1583 GB
last_autovacuum             | 2019-08-18 05:03:56.64232+00

Why does it return a negative number for tx_before_wraparound_vacuum?

Does this mean that there have been 1946483650 transactions more than allowed since the last wraparound protection was run?

That just means that your query is bogus.

age(xid) compares the age to the current transaction ID, so it is useless to call it with a multixact ID.

Moreover, running that query will return nonsense for anything except for regular tables (relkind = 'r').

You could try something like

SELECT cc.next_multixact_id::text::bigint - t.relminmxid::text::bigint
FROM pg_class AS t
   CROSS JOIN pg_control_checkpoint() AS cc
WHERE t.relkind = 'r';

to see how old the oldest multixact is.


How to verify that at least one point fall into a polygon with Postgis? I have two tables where table A has a geometry (4326) column that contains surfaces (~20M). A second table B has ~ 500K points (4326). What I want to do is "flagging" (adding a boolean column) every single surface in A that contains at least one of the points in B.

I did this:

CREATE TABLE foo_data AS
    SELECT
        A.*,
        (SELECT EXISTS (SELECT ST_Contains(A.geom, B.geom) FROM B)) as flagged
    FROM A;

But I got all "flagged" values as true (which is impossible).

With joins/lateral I didn't go far too.

Any idea?

You're close. I wouldn't use EXISTS, which is really only useful for reducing a set. I would go with CROSS JOIN LATERAL and then coalesce the result. See how this performs.

SELECT a.id,
  coalesce(result, false) AS flag
FROM a
CROSS JOIN LATERAL (
  SELECT true
  FROM b
  WHERE ST_Contains(a.geom, b.geom)
  LIMIT 1
) AS r(result);

This should work on an index if you have a spatial index on a.geom and b.geom (and you should have indexes for this workload). This also looks like you're caching the results for mere performance improvements. I would suggest under normal circumstances a MATERIALIZED VIEW.

Alternatively you should be able to go,

SELECT
  a.id,
  bool_or(b.geom IS NOT NULL) AS flag
FROM a
LEFT OUTER JOIN b
  ON ST_Contains(a.geom, b.geom)
GROUP BY a.id;


Select unique value pairs based on two participants that occur more than once inside a table We are using MySQL where we have deals table that contains references to two users stored in two columns User_id1 & User_id2. There is no guaranty in which order those are stored. For example the records below represent a deal between two unique pair of users

id ... User_id1 User_id2
-- --- -------- --------
 1 ...       11       20
 2 ...       20       11
 3 ...      116     3459
 4 ...       20       11

We need an SQL query to migrate this data into two new tables below

Participants

chat_id user_id
------- -------
      1      11
      1      20
      2     116
      2    3459

Chat_rooms

id
--
 1
 2

For each unique pair of users in deals table, we need to create a unique conversation record in Chat_rooms table and add those two users as participants to the Participants table with the reference to the created Chat_room ID

I'm hopping to do this with SQL, potentially using some temporary tables.

CREATE VIEW uniqueness (id, User_id1, User_id2)
AS
SELECT MIN(id),
       LEAST(User_id1, User_id2) User_id1,
       GREATEST(User_id1, User_id2) User_id2
GROUP BY LEAST(User_id1, User_id2),
         GREATEST(User_id1, User_id2);

and then

INSERT INTO Chat_rooms (id) SELECT id FROM uniqueness;

and

INSERT INTO Participants (chat_id, user_id)
SELECT id, User_id1 FROM uniqueness
UNION ALL
SELECT id, User_id2 FROM uniqueness;

You may create temporary table instead of view

-- or --

You may insert view's source text into INSERT queries


One table with a flag or two tables I have a problem with the design of a database: In my Android application i need to store the upload (the status, so if the file is in upload it will report the % of the upload) of a file and a list of all files uploaded.

First, i was design the database to something like this:

Files
----------------
_id INT PK,
status INT,
progress INT,
imageupload BLOB

So status will be the status of the upload which will be

0. Unknown
1. In upload
2. Error
3. Success

While imageupload is just a class serializable which contains: imageTitle, imagesUri (A List), album, privateImage.

(i said that in case exists another better way to design it which i didn't know and avoid using a blob)

With this design i could see if a file is in upload by reading the status WHERE status != 3 AND status != 2

Another design which could be used is to use two tables where in one table are stored the in upload files and in another the already uploaded.

With the two tables i avoid using status but it will let me execute two queries inside to select all files and order them by IN-UPLOAD/UPLOADED.

What is the best practice? Is one table prefered over two tables? I would use one table since it's more easy and can be edited without problems. With the second version i should move the record from in_upload table to uploaded table (which could be problematic).

If it's important i'm using SQLite.

Maybe you don't expect this answer, but do both. Let's see.

In order to accommodate for the life-cycle of the data ( something many database designers don't contemplate ), you should have a "historic" table with the downloads that finished. That table will not only hold files that downloaded successfully to a 100%, but also downloads that were canceled or aborted. You could have a policy of deleting records from this table after some given number of days.
The "live" downloads are in other table. Keep in mind that for the sake of simplicity, the migration of records from the "live" table to the "historic" one should be asynchronous (maybe a cron job or timer), meaning completed download will live there for a few seconds, or minutes after they finished. There's nothing wrong with it. It's even desirable so files don't disappear from view the very millisecond they finish downloading.
Add two timestamped columns to each table: TimeAdded and LastModified.
Why do I say this is both ? As I see it, it's a combination of the two approaches. Table one behaves as if you'd chosen the single table approach. Only an asynchronous process migrates completed or aborted files to a historic table after some time, it doesn't have to be immediate.


oracle active sessions waiting for scheduler Load on my 11g RAC looks like this:

and I don't understand how to get insight into what this "scheduler" wait class is.  It seems like "scheduler" that it refers to is the process that runs scheduled maintenance jobs?  But can I see which jobs they are?  All I see is normal queries waiting for the "scheduler".

The wait even class is "resmgr: cpu quantum"

They are Oracle Resource Manager waits. Resource Manager is a set of Oracle functionality that lets you prioritise work.

See the list of possible waits in the documentation.


Use Distinct With Sum? I want to sum a few values using case statements.  My problem is compiler complains if I do not add a group by to the end of the query, which then that negates my Distinct, hah.  For example, in my sample below this returns 2 entries for red12 even though 1 entry does not fit any of the criteria above, but the Group By messes me up!  How can I run this query and only return applicable results, i.e. what the case statements show?

Create Table #Bobsled
(
  [event] varchar(100)
  ,[time] decimal(18,4)
  ,employeeID varchar(25)
  ,name varchar(500)
)

Insert Into #Bobsled Values
('Walk', '16.32','red12', 'red arrow')
,('Eat', '5.12', 'red12', 'red arrow')
,('Run', '32.13','pink01', 'pink pig')
,('Walk', '2.12', 'bl81', 'blue fire')
,('Sleep', '8.12', 'gr99', 'green pony')

Select Distinct
employeeid
,case when [event] = 'Walk' then SUM([time]) else 0 end as walktime
,case when [event] = 'RUN' then SUM([time]) else 0 end as runtime
,case when [event] = 'Sleep' then SUM([time]) else 0 end as sleeptime
FROM #Bobsled
--only executes sucesfully with this
GROUP BY employeeID, [event]

I think what you're looking for is an aggregate on employeeID where the CASE goes inside the SUM() aggregate function.

SELECT employeeid,
       SUM((CASE WHEN [event] = 'Walk' THEN [time] ELSE 0 END)) AS walktime,
       SUM((CASE WHEN [event] = 'RUN' THEN [time] ELSE 0 END)) AS runtime,
       SUM((CASE WHEN [event] = 'Sleep' THEN [time] ELSE 0 END)) AS sleeptime
FROM #Bobsled
GROUP BY employeeID;

This way, you get one result row per employeeID, basically pivoted on event.


How can I enable distributed transactions for a linked server? I have a SQL Server 2012 instance with a linked server pointing to a db2 database on an IBM Iseries system using the System i Access ODBC Driver.

I'm trying to enable distributed transactions. I believe this provider does support them as per IBM documentation (unless I'm misunderstanding that link).

I'm running the following query:

BEGIN TRANSACTION
    UPDATE LINKEDSERVER.LINKEDDATABASE.SCHEMANAME.TABLENAME SET COLUMN = VALUE

    SELECT CAST('qwerty' as integer) -- we want an error here so the transaction fails
COMMIT TRANSACTION

I get the following error:

  OLE DB provider "MSDASQL" for linked server "LINKEDSERVER" returned message "[IBM][System i Access ODBC Driver]Enlist with DTC phase failed. 2".
  Msg 7391, Level 16, State 2, Line 2
  The operation could not be performed because OLE DB provider "MSDASQL" for linked server "LINKEDSERVER" was unable to begin a distributed transaction.

All the research I've done on this error has met with people wanting to disable distributed transactions, not get them working. I've already set "Enable Promotion of Distributed Transactions" to true on the linked server properties.

Any ideas how I can get this working?

EDIT: More information/update. I was about ready to give up on this for now and wait for help, when I noticed this in the windows logs:

  A caller has attempted to register an XA resource while XA transactions are disabled.

I found this link which told me how to modify the settings for the MSDTC.
After playing with the settings and restarting my server things started to work (kind of):
(specifically, I enabled network DTC access, allow inbound/outbound/enable XA transactions).

At present, the query runs and commits/rolls back as expected, but seems to work some of the time, but error other times. The error is:

  OLE DB provider "MSDASQL" for linked server "LINKEDSERVER" returned message "[IBM][System i Access ODBC Driver]Internal driver error."

EDIT2: Now it's not working at all again. Error message:

  OLE DB provider "MSDASQL" for linked server "LINKEDSERVER" returned message "ODBC driver could not start new transaction.".
  OLE DB provider "MSDASQL" for linked server "LINKEDSERVER" returned message "[Microsoft][ODBC Driver Manager] Illegal operation while in a transaction server component transaction".

In addition to setting Local DTC Properties, you need to open the firewall on both machines to allow program Distributed Transaction Coordinator to go thru.

When testing T-SQL in Query Editor Window, make sure to turn on XACT_ABORT as suggested by Microsoft.

  XACT_ABORT must be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. The only case where this option is not required is if the provider supports nested transactions.

In my case, for the Local DTC Properties, I only need these:


split a row into multiple rows in sql server according to a specific column I have table a having fields id,val and identical table b having fields id,val.

when i am writing an inner join for different data in rows

  SELECT *  FROM a left join b on a.id = b.id   WHERE ( a.id != b.id or
  a.val != b.val)

i am getting like below

i want to split this table row into two rows. Help me to write a query to split this data and getting an output table like below

Help!

You can generate the extra row(s) by doing an unpivot using a cross apply and the table value constructor.

select c.id,
       c.val
from dbo.a
  inner join dbo.b
    on a.id = b.id
  cross apply (values(a.id, a.val),
                     (b.id, b.val)) as c(id, val)
where a.val <> b.val;


I got a 2 core Mysql Server with 25 GB of RAM, 1 TB of Hard drive running on windows server. What is the proper configuration? I am relatively new in running enterprise db server and as a developer, I am used to running my DB in the default setting on more focused on the code. Now, I saw that the DB is not properly configured in an enterprise level with at least 2,000 user and generating > 1000 tickets per day.

How should I configure my DB server? I read this article https://medium.com/@richb_/tuning-mysql-3-simple-tweaks-6356768f9b90 and somehow I got lost on the process of getting the innodb_buffer_pool_instances and more.

Below is my current configuration:

https://pastebin.com/1TaKpRaB -> my.cnf

https://pastebin.com/pWF6SV6W -> show variables;

https://pastebin.com/CnwiWmx6 -> show global status;

Set innodb_buffer_pool_size = 14G; don't bother with any other tuning until you encounter issues.

If, by 1000 tickets/day, you mean a few thousand queries per day, well, that is 'trivial'.  Even a few thousand queries per minute is not a lot.

Further analysis...

Observations:

Version: 5.6.32-log
25 GB of RAM
Uptime = 22:16:52; some GLOBAL STATUS values may not be meaningful yet.
You are running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

1700 qps, not queries-per-day!  So the VARIABLES and STATUS are worth studying.

innodb_buffer_pool_size = 14G since is currently considerably bigger than the dataset.)  Also
innodb_buffer_pool_instances = 14

Com_rollback is quite high (3.7/sec); are you deliberately running things that need to be undone?  Could they check sooner, thereby avoiding a potentially costly rollback?  Com_rollback and Com_commit are surprisingly close in value.  Sounds fishy.

The next upgrade may spit at you about innodb_additional_mem_pool_size, which is unused and deprecated.  Remove it from my.cnf.

myisam_sort_buffer_size = 500M  (The current 3G threatens to cause you to run out of RAM.)

tmp_table_size and max_heap_table_size work together.  The former is dangerously high; the latter is possibly too small.  Set both to 200M.

(Normally I advise against using the Query Cache.  However, your application seems to make good use of it.)

Created_tmp_disk_tables is quite high.  That, and some other clues, indicate inefficient queries.  See this for details on finding slow queries and presenting them for critique.

Since you are running on Windows, I think thread_cache_size should be set to 0.

Details and other observations:

( Key_blocks_used * 1024 / key_buffer_size ) = 1,192 * 1024 / 8M = 14.6% -- Percent of key_buffer used. High-water-mark.
-- Lower key_buffer_size to avoid unnecessary memory usage.

( innodb_buffer_pool_size / _ram ) = 20480M / 25600M = 80.0% -- % of RAM used for InnoDB buffer_pool

( innodb_buffer_pool_instances ) = 20 -- For large RAM, consider using 1-16 buffer pool instances, not allowing less than 1GB each

( Innodb_buffer_pool_pages_free * 16384 / innodb_buffer_pool_size ) = 1,107,476 * 16384 / 20480M = 84.5% -- buffer pool free
-- buffer_pool_size is bigger than working set; could decrease it

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 1,107,476 / 1310720 = 84.5% -- Pct of buffer_pool currently not in use
-- innodb_buffer_pool_size is bigger than necessary?

( Innodb_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 3,195,551,744 / 20480M = 14.9% -- Percent of buffer pool taken up by data
-- A small percent may indicate that the buffer_pool is unnecessarily big.

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 1,081,532,416 / (80212 / 3600) / 2 / 2048M = 0.0113 -- Ratio
-- (see minutes)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 80,212 / 60 * 2048M / 1081532416 = 2,654 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size. (Cannot change in AWS.)

( Com_rollback ) = 295,694 / 80212 = 3.7 /sec -- ROLLBACKs in InnoDB.
-- An excessive frequency of rollbacks may indicate inefficient app logic.

( innodb_additional_mem_pool_size ) = 32M -- (deprecated in 5.6.3, removed in 5.7.4.)
-- A high value is unnecessary.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( join_buffer_size / _ram ) = 262,144 / 25600M = 0.00% -- 0-N per thread. May speed up JOINs (better to fix queries/indexes) (all engines) Used for index scan, range index scan, full table scan, each full JOIN, etc.
-- If large, decrease join_buffer_size to avoid memory pressure. Suggest less than 1% of RAM. If small, increase to 0.01% of RAM to improve some queries.

( myisam_sort_buffer_size / _ram ) = 3072M / 25600M = 12.0% -- Used for ALTER, CREATE INDEX, OPTIMIZE, LOAD DATA; set when you need it. Also for MyISAM's REPAIR TABLE.
-- Decrease myisam_sort_buffer_size to keep from blowing out RAM.

( query_prealloc_size / _ram ) = 8,192 / 25600M = 0.00% -- For parsing. Pct of RAM

( query_alloc_block_size / _ram ) = 8,192 / 25600M = 0.00% -- For parsing. Pct of RAM

( net_buffer_length / max_allowed_packet ) = 16,384 / 256M = 0.01%

( local_infile ) = local_infile = ON
-- local_infile = ON is a potential security issue

( Qcache_inserts - Qcache_queries_in_cache ) = (9257553 - 3004) / 80212 = 115 /sec -- Invalidations/sec.

( Created_tmp_disk_tables ) = 597,469 / 80212 = 7.4 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs
-- increase tmp_table_size and max_heap_table_size.
Check the rules for temp tables on when MEMORY is used instead of MyISAM. Perhaps minor schema or query changes can avoid MyISAM.
Better indexes and reformulation of queries are more likely to help.

( Created_tmp_disk_tables / Created_tmp_tables ) = 597,469 / 614226 = 97.3% -- Percent of temp tables that spilled to disk
-- Maybe increase tmp_table_size and max_heap_table_size; improve indexes; avoid blobs, etc.

( tmp_table_size ) = 853M -- Limit on size of MEMORY temp tables used to support a SELECT
-- Decrease tmp_table_size to avoid running out of RAM. Perhaps no more than 64M.

( Com_rollback / Com_commit ) = 295,694 / 298826 = 99.0% -- Rollback : Commit ratio
-- Rollbacks are costly; change app logic

( Select_scan ) = 98,730 / 80212 = 1.2 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( innodb_autoinc_lock_mode ) = 1 -- Galera: desires 2 -- 2 = "interleaved"; 1 = "consecutive" is typical; 0 = "traditional".

( long_query_time ) = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2

( Threads_created / Connections ) = 322 / 1160 = 27.8% -- Rapidity of process creation
-- Increase thread_cache_size (non-Windows)

Abnormally large:

Com_show_keys = 0.063 /sec
Com_show_plugins = 0.27 /HR
Innodb_buffer_pool_pages_free = 1.11e+6
Qcache_hits = 1529 /sec
Select_range = 35 /sec
Select_range / Com_select = 23.2%
Sort_range = 36 /sec
myisam_sort_buffer_size = 3072MB


CNAME alias for AlwaysOn Listener We are currently experiencing an issue with SQL Server migration. There is a legacy product and we don't want to change connection strings due to various reasons.
Thus, we would like to keep existing Listener name.

My question is, Is it possible to create one or more CNAME aliases for AG Listener Name? Is there any danger in doing this?

Yes you can create CNAME pointing to listener name. I have been using the same for over 100 Availability group. SQL Server version 2012, 2014, 2016 and 2017. I have been using this for close to 3 years without any issue.

Two suggestions for better manageability if you go to that route enterprise wide.

Write a collection of all CNAME pointing to listeners, save it in
your DBA database and update periodically. That way during any
migration you are not in doubt what CNAME's are pointing to a
particular listener name.
I prefer for DBA team to have privilege to update CNAME. That way during a migration you are not dependent on someone else to make the cut over.


Multi-database object-oriented proxy Do you know if there is a system/product/technique which would allow to "wrap" one or more relational databases into one object-oriented virtual database (or could we call it a "multi-database object-oriented proxy")?

Let me illustrate the principle:

So, basically, the pink thing in the middle (hereafter "object proxy") would contain definitions of business entities and their mappings to data in multiple relational databases. Applications would request or insert/update/delete data from the object proxy, which would magically synchronize with underlying relational databases.

Is that possible? Does such a system exist?

You may be referring to a Federated Database System.


Who should own application objects? So I am just started doing stuff resembling full-blown Oracle DBA work recently, so am still learning much of the basics of the administration part of my job. I have been tasked with bringing multiple application databases up to meet a certain set of professional standards (I'm being intentionally vague). One of these standards is that owners of objects supporting the application by and large should not also be regularly utilizing them. The major exception being DBA, but then only for certain classes of objects that we would regularly be maintaining such as indexes. Other than when they are doing installation and maintenance tasks, the application object owners should be disabled.

This finally brings me to my question: who should be owning these application objects. Should we just have a user who creates the objects and then is largely disabled for the rest of its life and the requisite usage privileges be given to this or that role? Is it fine for the DBA to own all of the application objects, or should they just own those objects that are being maintained on a daily/weekly basis? Obviously the exact answer will depend the organization's needs and what specifically this standard is asking, but what is the best practice here for application object ownership?

This question may be too broad, so let me know if I need to edit it to make it more specific. I just don't really know where to start with this or even what the right questions to ask are. Thanks for any help you can provide.

The Principle of Least Privilege demands two things: the runtime user (or the human user logins) should not own the objects, because you cannot stop them from modifying or dropping them (on Oracle).

The same Principle also suggests you should not need to have extensive permissions for the user who wants to update application objects.

Both combined suggests: have a system user owning the application objects and another system user with access to those objects for runtime login. Use the DBA only to set up those users.

It is a bit different on MSSQL but basically you want to avoid having a runtime user with dbo role.


Always On: replication over cross server SQL instances I do agree, the question is not very clear. Let's explain that.

Context: we are wondering if it's a good idea (regarding our business and technical architecture) to use AlwaysOn features (SQLServer 2014).

A consultant told us to have (case of two servers to replicate): two servers (let's call them S1 and S2) in which we have two SQLServer instances (S1\DB1 plus S1\DB2 and S2\DB1 plus S2\DB2).
S1\DB1 and S2\DB2 are masters and S1\DB2 and S2\DB1 are replicas.

With a schema:

Instead of basic architecture (4 servers, one SQL instance per server):

Consultant's point of view: less servers so simpler management and less costs.

Question:is that a good way to implement SQLServer AlwaysOn?

Depending on your requirement, both solution are OK.
In first schema, imagine to have a fault on a server. Or you simply need to do updates. Can a single server handle your work load? If yes, you can save money.

You will need to handle correctly multiple IP for server, or different port for the istances, but it is not a problem if your department is skilled.

I mixed mutiple istances in multiple servers, also mixed failover cluster istances with Always ON. My only suggestion is to implement on SQL 2016 or 2017 if you can. It introduces DAG and is a bit more mature.


Number of databases on a server. Should I try to consolidate these objects together? Background

Hi, as a BI developer I create various scripts for a middle-size company (200-300 employees). These scripts are used to manipulate / aggregate / clean data from different sources and usually the result of these operations is stored into different tables (to perform extractions, analysis, whatever).

Problem

My boss is a great guy but he likes to create a new database and SQL Server Agent job (on the same server) each time a new business case pops in. For instance we're managing 10+ databases but a lot of them contain only a few tables. Same issue with the SQL Server Agent jobs, most of them perform only one step / action.

Personally I prefer a clean environment and to group tables / jobs together as much as possible (i.e.: storing all our derived tables into one DB rather 2-3 different DBs).

However before I engage any action / audit I would like to hear about your feedback. I mean, from your point of view, what's the best practice? Is it ok to have 10+ DBs / 50+ jobs when we could divide these numbers by 2 or 3?

Thanks,

Edit

I notice the question has been put on hold. Sorry for the inconvenience, I'll try to rephrase my question:
I run a SQL Server containing 10+ databases (same company). Some DBs, for no particular reason (legal or whatever), contains only a couple of tables. I feel like I could consolidate these databases.
Is it the right way to proceed? Or it's ok to create a new DB whenever you want?

PS: maybe the answer to this question is truly "opinion based". I just can't figure out what is the best way to proceed. In a way that's an answer too.

Best practice is what works best for your organization.

If you're going to discuss this with your boss, who has a certain way of doing things, then you need to plan out the discussion in terms of a business case.

So, first:
Rank the following twice each, in terms of What's important to you, and what's important to your boss.  If these rankings are the same, you need to re-evaluate; that's pretty unlikely

Maintainability

via the GUI?
via handwritten SQL?
via automated SQL?

File count on disk
Cross-database queries
Cross-database permissions

Remember, EXECUTE on DatabaseA allows the object executed to access other objects in schemas with the same schema owner; however, going cross-database changes the rules

Quickness of the initial, first draft of a solution
Quickness of adding dozens of followon, similar solutions
Quickness of adding dozens of followon, identical solutions
Quickness of making changes to dozens of already existing similar solutions
Quickness of making changes to dozens of already existing identical solutions
Ease of investigating why solution A6 is not quite acting like solution A23, when they "should be" the same
Ease of restoring the entire set of databases somewhere else
Separation of data so customer A's data is not mixed with customer B (often a contractual requirement)
Ease of moving the entire set of Agent jobs somewhere else
Overall performance of ALL the solutions at once - profiler, extended events, indexing, monitoring
Foreign keys

which CANNOT go cross-database

Essentially, most of this boils down to: If you want one-off results as quickly as possible for each one-off unique thing, a separate database makes sense, but they should be destroyed when they are no longer required.  However, long term, you're going to have hundreds of databases; is that desired?  Or required, perhaps, due to contracts?

I would say that the arguments to consolidation are firmly around foreign keys (data integrity), identicalness of solutions (data integrity), and doing stuff by hand.  The latter should be replaced with automated techniques which can just as easily handle 500 databases as 2, so I would ignore that.

Again, know what's important to your boss; if consolidating is a solution that is superior in terms of what's important to them, you've got a good potential for a successful argument.  If not, you don't.


If MySQL isn't caching, then what is? I ran a few commands to see whether MySQL is caching queries/result sets. It looks like this is not the case.

mysql> show variables like 'query_cache_size';
--------------
show variables like 'query_cache_size'
--------------

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_size | 0     |
+------------------+-------+
1 row in set (0.01 sec)

mysql> show status like 'Qcache%';
--------------
show status like 'Qcache%'
--------------

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_free_blocks      | 0     |
| Qcache_free_memory      | 0     |
| Qcache_hits             | 0     |
| Qcache_inserts          | 0     |
| Qcache_lowmem_prunes    | 0     |
| Qcache_not_cached       | 0     |
| Qcache_queries_in_cache | 0     |
| Qcache_total_blocks     | 0     |
+-------------------------+-------+
8 rows in set (0.00 sec)

At the same time, I can run the same query at one given time, and it will take 42 seconds, and at another given time, and it will take 1.10 seconds.

If the MySQL server is not doing the caching, what is? Is it the mysql client? Is it my operating system (OS X)? Is it some service on the server that's not MySQL? Are all of these possibilities, or only one?

You have two caches, one for each Storage Engine.
MyISAM
Only caches index pages in the key buffer (sized of key_buffer_size). All data pages are read from the .MYD of the MyISAM table.
InnoDB
Three Components

This would be the InnoDB Buffer Pool (sized by innodb_buffer_pool_size). It is used to collect all data and index pages you have read to accomplish all your queries.
You also have the InnoDB's Transaction Logs catching transactional changes on disk (ib_logfile0, ib_logfile1)
The changes are written redundantly in ibdata1 in the event of a crash so InnoDB Crash Recovery can bring the data back to a consistent state.

See the Pictorial Representation of InnoDB's Architecture to see the Buffer Pool and Transaction  Logs
Every time you read from or write to an InnoDB table, the .frm is checked (disk I/O), the data dictionary in ibdata1 is checked (disk I/O), and MVCC for transaction isolation is written in ibdata1 and transaction logs if there are incoming writes to the same table (disk I/O). Even a read-heavy InnoDB environment incurs disk I/O.
EPILOGUE
You could easily expect some pages to be rotated out. This is especially the case when the caches are configured too small. In the case of MyISAM, since data pages are read from disk each time, index pages can rotate in and out. As for InnoDB, it's data and index pages.
The result sets may have to be recreated each time. Sometime, this contribute to all caches activity.