There are many flaws in the output from EXPLAIN.  This is a new one to me.
Things like "covering" indexes, ranges, ORDER BY, etc are poorly indicated by key_length.  Also, the length of NULLable columns is a constant, not 1 or 2, depending on the schema.
"Using temporary, Using filesort" is usually on the first line, even if that is not when the sort occurs.
GROUP BY x ORDER BY y may require two sorts; there is no clue of such.
Use EXPLAIN FORMAT=JSON SELECT ... to get more information, sometimes close to all the info available.
Back to the specific example.
Well, a better index is this order: (value, customer_id).  Columns involved in col=constant should come first for both columns of the index to be efficiently used.
A guess is that there is a relatively new Optimization.  What version(s) of MySQL/MariaDB show this inconsistent key_len?
As Jørgen says in that blog:

The difference between <,> and BETWEEN sounds strange as these conditions should be treated the same way in the optimizer code. One theory is that the optimizer considers two ways of executing this query to be almost equally good and therefore sometimes picks a good plan and other times a suboptimal plan due to small differences in index statistics.
If you are able to reproduce this issue consistently, however, you might want to file a bug at bugs.mysql.com.

That is, please provide a full test case -- CREATE, INSERT, EXPLAIN, etc., that consistently demonstrates the issue in at least one version of MySQL (the newer, the better).
Found the answer in his latest blog:

But that leaves a question: Why is key_length=10 for the BETWEEN version of the query? The reason is that although the value keypart cannot be used to set up ranges, it is used to determine the starting point of the first range (there is only have one range in this query - try optimizer trace and see for yourself), and there is no use in reading index rows with value < 500 and customer_id=7.

Thanks for the Question; I learned something.
As for your Question 1:

By contrast, the condition key_part3 = 'abc' does not define a single interval and cannot be used by the range access method.

That, together with WHERE key_part3 = 'abc' (without any other clauses) together with INDEX(kp1, kp2, key_part3)...  It is saying that there may be several clumps (not a "single range") of places in the ordered list based on (kp1, kp2, key_part3) where abc is found.
It is better to go to one spot in the index, then scan until there are no more relevant rows.  But in that case, you need to look in several spots in the index.  The discussion is leading up to the 'new' ability to do so (somewhat).
Bottom line...  As discussed in my Index Cookbook, the best indexes start with column(s) that are tested against constant(s).  Sometimes the Optimizer can make some use of indexes where the columns are not optimally ordered.


How to break table into two without losing performance? According to https://stackoverflow.com/a/174047/14731, splitting away infrequently-needed columns frees up the cache allowing faster retrieval of the commonly-used columns.

I've got a table whose columns are always retrieved together, but I'd still like to split them for design reasons (reduce duplication across multiple tables, improve code reuse). For example, I've got different tables that use the same permission scheme. Instead of adding permission columns to each table, I'd like to use a foreign key to reference a separate permission-scheme table.

I've populated MySQL with 1 million rows, ran queries against both versions, and discovered that the version with a JOIN is ~3x slower (0.9 seconds vs 2.9 seconds).

Here are my tables:

original
(
    id BIGINT NOT NULL,
    first BIGINT NOT NULL,
    second BIGINT NOT NULL,
    third BIGINT NOT NULL
);
part1
(
    id BIGINT NOT NULL,
    first BIGINT NOT NULL,
    second BIGINT NOT NULL,
    PRIMARY KEY(id)
);
part2
(
    link BIGINT NOT NULL,
    third BIGINT NOT NULL,
    FOREIGN KEY (link) REFERENCES part1(id)
);

Here are my queries:

SELECT first, second, third FROM original;
SELECT part1.first, part1.second, part2.third FROM part1, part2 WHERE part2.link = part1.id;

Is there any way to reduce the performance overhead of the split design?

If you want to reproduce this test on your side, you can use the following Java application to generate the SQL script to populate the database:

import java.io.FileNotFoundException;
import java.io.PrintWriter;

public class Main
{
    public static void main(String[] args) throws FileNotFoundException
    {
        final int COUNT = 1_000_000;
        try (PrintWriter out = new PrintWriter("/import.sql"))
        {
            for (int i = 0; i < COUNT; ++i)
                out.println("INSERT INTO original VALUES (" + i + ", " + i + ", 0);");
            out.println("INSERT INTO original VALUES (" + (COUNT - 2) + ", " + (COUNT - 1) +
                ", 1);");
            out.println();
            for (int i = 0; i < COUNT; ++i)
            {
                out.println("INSERT INTO part1 (first, second) VALUES (" + i + ", " + i + ");");
                out.println("INSERT INTO part2 VALUES (LAST_INSERT_ID(), 0);");
            }
            out.println("INSERT INTO part1 (first, second) VALUES (" + (COUNT - 2) + ", " +
                (COUNT - 1) + ");");
            out.println("INSERT INTO part2 VALUES (LAST_INSERT_ID(), 1);");
            out.println();
        }
    }
}

OPTION #1 : Use INT UNSIGNED instead of BIGINT

If the fields will not exceed 4,294,967,295, change them to INT UNSIGNED

ALTER TABLE part1
    MODIFY COLUMN id     INT UNSIGNED NOT NULL AUTO_INCREMENT,
    MODIFY COLUMN first  INT UNSIGNED NOT NULL,
    MODIFY COLUMN second INT UNSIGNED NOT NULL;
ALTER TABLE part2
    MODIFY COLUMN link  INT UNSIGNED NOT NULL,
    MODIFY COLUMN third INT UNSIGNED NOT NULL;

Smaller datatypes, especially for JOIN keys, will make the same query go faster.

If the fields will not exceed 16,777,215, useMEDIUMINT UNSIGNED for even smaller columns.

OPTION #2 : Use a Bigger Join Buffer

Add this to my.cnf

[mysqld]
join_buffer_size = 16M

Then, login to MySQL and run

mysql> SET GLOBAL join_buffer_size = 1024 * 1024 * 16;

MySQL restart not needed.

See MySQL Documentation about join_buffer_size

OPTION #3 : Make sure link is indexed

Since you have a FOREIGN KEY reference, this is a rather moot point. If you do not have the FOREIGN KEY, make sure link is indexed:

ALTER TABLE part2 ADD UNIQUE KEY (link);

Give it a Try !!!

UPDATE 2014-08-22 17:13 EDT

I created my own version of the Sample Data using this:

DROP DATABASE IF EXISTS GILI; CREATE DATABASE GILI;
USE GILI
create table original
(
    id mediumint unsigned not null auto_increment,
    first mediumint unsigned not null,
    second mediumint unsigned not null,
    third mediumint unsigned not null,
    primary key (id)
);
create table part1 like original;
alter table part1 drop column third;
create table part2 select third from original;
alter table part2 add link mediumint unsigned not null first;
alter table part2 add primary key (link);
insert into original (first,second,third) values (1,2,3);
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into part1 (id,first,second) select id,first,second from original;
insert into part2 (link,third) select id,third from original;
select count(1) from (select * from original) A;
select count(1) from (select * from part1 inner join part2 on part1.id = part2.link) A;

Results are roughly the same as yours: About 3x's slower. The, I read your stackoverflow link. Then I got to thinking: Look at the sample we used. We basically pushed MySQL to its limit to join 1M rows with 1M rows. This is the worst-case equi-join scenario. All things considered, the performance is pretty good.

I remember my college days where I had to create a two-dimensional array using linked lists to construct a sparse array. If a node did not exist in a particular coordinate, the default value for the array was defined as zero in the app. Then, imagine this. Creating a 1000x1000 sparse array where all 1000000 (1 million) coordinates had a nonzero value represented. Now, you had at least 2.002 million pointers mapping all adjacent nodes. This is in additional to the 1 million 4-byte integers for the data. Fetching a single value out of it required more CPU for navigation than the retrieval of the actual data.

Doing a INNER JOIN of 1M rows from part1 to part2 where part2 has absolutely every key requires more resources for navigation (temp table creation, key comparison, value population). Denormalizing can sometimes be demorializing if the right side of a LEFT JOIN isn't very sparse or the left side of the LEFT JOIN is huge. In your case, separating the original into part1 and part2 buys you nothing if they have to be frequently referenced together and in bulk. In other words, separating columns that do not form repeating groups isn't true normalization.

The 3 options I gave would do a world of good for part1 and fetching part2 on a row by row basis.

Think about the following cases:

you only need some of the rows from part1

do you trust the MySQL Query Optimizer
do you refactor the query to get a cross section to part1 before joining to part2 ?
do you retrieve from part2 only one row at a time ?

Using a LEFT JOIN and learning there is no part2 for a given part1

set default value in the app for part2 ?
populate part2 for that part1 ?

Giving more consideration to how you plan to retrieve data, how much data you need in a single query, and how you structure the query will become the determining factor as your seek good performance.

EPILOGUE

You are not going to get better performance because of the cost to make the JOIN happen. It's like trying to turn lead into gold (Theoretically possible, practically and financially impossible).

Your best bet is to leave the table in its original state.


How to query if record exists for prior month in mysql I have a table with custId and orderDate fields, and I need a query to display whether or not the custId in that record has another record in the table with an order from the prior month. For example:

custId   orderDate    hasOrderInPriorMonth
aaa      2017-11-01   false
bbb      2017-12-15   false
aaa      2018-01-29   false
bbb      2018-01-15   true

Another way is to use variables to calculate the flag. The variables will store the values of custId and orderDate as well as the calculated flag value. All three will be used to calculate the subsequent row's flag.

You will need to sort the rows by custId, then by orderDate and implement the following logic to calculate the flag values:

if the current row's custId is different from the one stored in the corresponding variable (which at this point is carrying the previous custId), set the flag to false;
otherwise, if the year and month of the current row's orderDate match those of the date stored in the corresponding variable (which, similarly, is the date from the previous row), keep the previous flag value;
otherwise, set the flag to true if the current orderId minus one month is the same year and month as the date stored in the variable;
store the current custId and the year and month of the current orderId for use in the next iteration.

The MySQL-specific SQL query for that could be something like this:

SELECT
  custId,
  orderDate,
  flag AS hasOrderInPriorMonth
FROM
  (
    SELECT
      @flag :=
        CASE
          WHEN o.custId <> @custId THEN false
          WHEN EXTRACT(YEAR_MONTH FROM o.orderDate) = @orderMonth THEN @flag
          ELSE (EXTRACT(YEAR_MONTH FROM o.orderDate - INTERVAL 1 MONTH) = @orderMonth)
        END AS flag,
      @custId := o.custId as custId,
      o.orderDate,
      @orderMonth := EXTRACT(YEAR_MONTH FROM o.orderDate)
    FROM
      (SELECT @custId := '', @orderMonth := 0, @flag := false) as x,
      orders as o
    ORDER BY
      custId    ASC,
      orderDate ASC
  ) AS s
;

A demo of this query is available at SQL Fiddle. (It borrows the test setup created by Jehad Keriaki for his own answer.)


Retrieve Latest (max) date Using SQL Server 2008R2, I am trying to pull the latest assessment date for a patient. I have it pulling the correct data I need, but it is pulling all dates. For instance if a patient has an assessment on 1/1, 1/5, 1/8, and 1/10 I am currently getting all records when I really only want the last one (in this example, 1/10).

SELECT     TOP (100) PERCENT dbo.PT_BASIC.PATIENT_CODE,
           dbo.PT_BASIC.NAME_FULL,
           dbo.VW_ASSESSMENT_DATA.ASSESSMENT_DATE,
           dbo.VW_ASSESSMENT_DATA.QUESTION_TEXT,
           dbo.VW_ASSESSMENT_DATA.ANSWER_TEXT
FROM       dbo.VW_ASSESSMENT_DATA
INNER JOIN dbo.PT_BASIC
ON         dbo.VW_ASSESSMENT_DATA.PATIENT_ID = dbo.PT_BASIC.PATIENT_ID
WHERE      (dbo.VW_ASSESSMENT_DATA.ANSWER_TEXT LIKE '%Level %')
AND        (dbo.VW_ASSESSMENT_DATA.ASSESSMENT_DATE > CONVERT(DATETIME, '2017-12-01 00:00:00', 102))
AND        (dbo.VW_ASSESSMENT_DATA.QUESTION_TEXT LIKE '%Plan/Class%')
ORDER BY   dbo.PT_BASIC.NAME_FULL

Try by using a CROSS/OUTER APPLY solution:

It should return one record ordered by ASSESMENT_DATE DESCfor each record of main table dbo.PB_BASIC

SELECT      TOP (100) PERCENT
            B.PATIENT_CODE,
            B.NAME_FULL,
            A.ASSESSMENT_DATE,
            A.QUESTION_TEXT,
            A.ANSWER_TEXT
FROM        dbo.PT_BASIC B
CROSS APPLY (SELECT   TOP 1
                      PATIENT_ID,
                      ASSESSMENT_DATE,
                      QUESTION_TEXT,
                      ANSWER_TEXT
             FROM     dbo.VW_ASSESSMENT_DATA
             WHERE    PATIENT_ID = B.PATIENT_ID
             AND      (ANSWER_TEXT LIKE '%Level %')
             AND      (ASSESSMENT_DATE > CONVERT(DATETIME, '2017-12-01 00:00:00', 102))
             AND      (QUESTION_TEXT LIKE '%Plan/Class%')
             ORDER BY ASSESSMENT_DATE DESC) A
ORDER BY   B.NAME_FULL;


SQL Server 2012 Visualization of Table relationships Does SQL Server 2012 /2014 have a way of creating a visualization of the table relationships of a database ?

In SQL Server Management Studio, you can create Database Diagrams with the relationships on tables.
This is per database, and not on the Instance level.

Microsoft Reference


Using Sync Orchestrator to Syncronize two SQL CE 3.5 Databases I am trying to set up synchronization between two SQL Compact 3.5 databases using SqlCeSyncProvider.  I am able to create a scope for the two databases, which creates a meta table.  When I try to run SyncOrchestrator.Synchronize() though, I get the following error and am not able to get the two databases to synchronize when I insert data:

"An error occurred when initializing adapters for scope 'Person'. Ensure that the scope name specified on the provider exists in the scope metadata table, and that the correct base tables are specified for that scope."

When I run the following code, three tables are created for each database: Sync_Person_tracking, Sync_scope_config, and Sync_scope_info. Both databases contain the same scope name of Person in the Sync_scope_info table.

    SqlCeSyncScopeProvisioning ceConfig = new SqlCeSyncScopeProvisioning();
    SqlCeConnection ceConn = (SqlCeConnection)syncProvider.Connection;
    string scopeName = syncProvider.ScopeName;
    if (!ceConfig.ScopeExists(scopeName, ceConn)) {
        var description = new DbSyncScopeDescription();
        description.ScopeName = scopeName;
        var table = new DbSyncTableDescription("Person");
        var pkCol = new DbSyncColumnDescription("ID", "int");
        pkCol.IsPrimaryKey = true;
        table.Columns.Add(pkCol);
        table.Columns.Add(new DbSyncColumnDescription("Name", "nvarchar 50"));
        table.Columns.Add(new DbSyncColumnDescription("Height", "int"));
        table.Columns.Add(new DbSyncColumnDescription("Weight", "int"));
        description.Tables.Add(table);
        ceConfig.PopulateFromScopeDescription(description);
        ceConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
        ceConfig.ObjectPrefix = "Sync";
        ceConfig.Apply(ceConn);
    }

In the above code, syncProvider is a SqlCESyncProvider object with a connection string attached to the specified database.

Here is the code I use to set up synchronization between the databases using SyncOrchestrator:

    _syncOrchestrator.LocalProvider = localSyncProvider;
    _syncOrchestrator.RemoteProvider = remoteSyncProvider;
    _syncOrchestrator.Direction = SyncDirectionOrder.DownloadAndUpload;

    try {
        var sync = _syncOrchestrator.Synchronize();
        Console.WriteLine(string.Format("Sync Downloads: {0}\nSync Uploads: {1}\nSync Start Time: {2}\nSync End Time: {3}",
            sync.DownloadChangesTotal.ToString(), sync.UploadChangesTotal.ToString(),
            sync.SyncStartTime, sync.SyncEndTime));
    } catch (Exception ex) {
        Console.WriteLine(string.Format("Sync Orchestrator threw exception: {0}", ex.Message));
    }

Thanks in advance for any help!

I was able to solve the error by changing the provisioning.  Instead of calling the above code for each of the two databases, I created a single function as shown below:

    public void SetupScope() {
        if (ProvidersCollection["Local"] != null && ProvidersCollection["Remote"] != null) {
            var localConnection = (SqlCeConnection)((SqlCeSyncProvider)ProvidersCollection["Local"]).Connection;
            var remoteConnection = (SqlCeConnection)((SqlCeSyncProvider)ProvidersCollection["Remote"]).Connection;

            var localScopeDescription = new DbSyncScopeDescription("LocalPerson");
            var localPersonTableDescription = SqlCeSyncDescriptionBuilder.GetDescriptionForTable("Person", remoteConnection);
            localScopeDescription.Tables.Add(localPersonTableDescription);

            var localDatabaseConfiguration = new SqlCeSyncScopeProvisioning(localConnection);
            try {
                localDatabaseConfiguration.PopulateFromScopeDescription(localScopeDescription);
                localDatabaseConfiguration.ObjectPrefix = "Sync";
                localDatabaseConfiguration.SetCreateTableDefault(DbSyncCreationOption.CreateOrUseExisting);
                localDatabaseConfiguration.Apply();
            } catch (Exception ex) {
                Console.WriteLine(string.Format("{0}", ex.Message));
            }

            var remoteScopeDescription = new DbSyncScopeDescription("RemotePerson");
            var remotePersonTableDescription = SqlCeSyncDescriptionBuilder.GetDescriptionForTable("Person", localConnection);
            remoteScopeDescription.Tables.Add(remotePersonTableDescription);

            var remoteDatabaseConfiguration = new SqlCeSyncScopeProvisioning(remoteConnection);
            try {
                remoteDatabaseConfiguration.PopulateFromScopeDescription(remoteScopeDescription);
                remoteDatabaseConfiguration.ObjectPrefix = "Sync";
                remoteDatabaseConfiguration.SetCreateTableDefault(DbSyncCreationOption.CreateOrUseExisting);
                remoteDatabaseConfiguration.Apply();
            } catch (Exception ex) {
                Console.WriteLine(string.Format("{0}", ex.Message));
            }
        }
    }

The difference here is that for each of the two databases, I now reference the connection of the other database when setting up my table descriptions.


MySQL Table level Replication I have 3 servers, say A,B &C. All have the equivalent data(same data). Now what I want is to make A as the master and B,C the slaves.

But the scenario is that I only want to replicate some of the tables from a single database of the master (server A).

All the servers are running with the same data. So no need to take the dump from the master and restore on the Slaves.

Just to set the replication for some tables from a single database.

Is there any harm in doing this? And what step(s) should I follow to achieve this?

Setup a master<>slave configuration, then use the config parameter:

replicate-do-table=db_name.table_name

... to specify the tables that you wish to replicate. Either wildcard the table_name (if possible) or use multiple replicate-do-table= lines.


Logon procedure instead of logon triggers I want to restrict a sql server authentication user's logging in from Management Studio by using a logon procedure instead of a logon trigger. How can I do that?

There is no reliable way to do this, unfortunately. The things you want to go by is set in the connection string and, hence, can be spoofed. Here's an example what I did in SSMS:

I execute:

SELECT session_id, program_name
FROM sys.dm_exec_sessions
WHERE is_user_process = 1

With the result:

session_id  program_name
51  MyCRM
52  MyCRM
53  MyCRM
54  MyCRM
55  MyCRM
56  SQLServerCEIP


How to convert data type from varchar to number in SQL Server? I want to convert a field currently in varchar data type to number data type in the Views of SQL Server say (dbo.CUSTOMER.STORE_NBR) from Varchar to number.
How to do that ?
Will CONVERT function will do the necessity?
If yes, can you help us with syntax?

In Sql Server 2012 and up: each of these will return null when the conversion fails instead of an error.

try_convert(datatype,val)
try_cast(val as datatype)
try_parse(val as datatype [using culture])

So if you want to change the way a view is returning the value you would use something like:

alter view dbo.myView as
  select
    ....
    , store_nbr = try_cast(dbo.customer.store_nbr as int)
    ....


MySQL: mysqld_safe options Is there a way to specify these options in configuration file?

I've tried to add them into [mysqld_safe] section but it doesn't work:

[mysqld_safe]
numa-interleave = 1
flush-caches = 1

-

# ps -ww -lfC mysqld_safe
F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
4 S root     17180     1  0  80   0 - 26549 wait   15:06 pts/1    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/mysql.pid

Please note that I'm using Percona server 5.5.30-30.2

EDIT: 28 Aug 2013 - 08:34:

Unfortunately I can't find this line in /etc/init.d/mysql:

# grep -n "interleave" < /etc/init.d/mysql
# echo $?
1

I'm able to manually invoke these options:

mysqld_safe --datadir=/mnt/data/mysql --pid-file=/mnt/data/mysql/db3.improve.pid --numa-interleave --flush-caches

but with option file there is still default policy

# cat /proc/$(pidof mysqld)/numa_maps | cut -d ' ' -f 2 | uniq
default

There must be some way other than hard coding these options into init script or manually staring MySQL daemon...

EDIT: 29 Aug 2013 - 08:38:

I've just tested it with Percona Server 5.5.28-29.1.

I still don't see numa related entries in /etc/init.d/mysql however there are in mysqld_safe script:

# grep -n "interleave" /usr/bin/mysqld_safe
21:numa_interleave=0
67:  --numa-interleave          Run mysqld with its memory interleaved
215:      --numa-interleave) numa_interleave=1 ;;
777:# Set mysqld's memory interleave policy.
780:if true && test $numa_interleave -eq 1
785:    log_error "numactl command not found, required for --numa-interleave"
788:  elif ! numactl --interleave=all true
794:  cmd="$cmd numactl --interleave=all"
795:elif test $numa_interleave -eq 1
797:  log_error "--numa-interleave is not supported on this platform"

I've tested other options like nice, open_files_limit and they work fine so it looks like the only option is to hard-code it into init script

I believe Percona already has that option built-in.
Inside /etc/init.d/mysql, somewhere I saw something like
# numactl --interleave=all

When I discovered this, every time I installed Oracle's MySQL, I added that command to /etc/init.d/mysql.
If you want to be sure, just run this:
grep -n "interleave" < /etc/init.d/mysql

and you will see which line that appears.
I have discussed this before

Jul 22, 2013 : MySQL 5.5 High CPU Usage
Mar 06, 2013 : mysqld_safe version different than mysqld?
Dec 30, 2012 : Mysql slowly uses memory until it starts to use swap

Based on the link you posted, you should be able to activate those features starting from Percona Server 5.5.28. My posts mentioning numactl were evidently from earlier versions of Percona Server.
In light of all this, the same thing applies to flush-caches.
UPDATE 2013-08-28 09:54 EDT
If you have Percona Server 5.5.30 and there are no options, go install Percona Server 5.5.28 or 5.5.29 on a Dev Server and test it out there. Check /etc/init.d/mysql as follows:
# grep -n "numa" /etc/init.d/mysql

If you find it, then Percona must have missed putting that option on 5.5.30 (which would be very surprising). As the quick fix, take the mysqld_safe line from 5.5.28/29 and hardcode it into the mysqld_safe invocation in 5.5.30.
At the very least, take the line you said works:
mysqld_safe --datadir=/mnt/data/mysql --pid-file=/mnt/data/mysql/db3.improve.pid --numa-interleave --flush-caches

and imbed that into /etc/init.d/mysql for 5.5.30 where mysqld_safe is called.
I hope that human error (Percona missing this one (highly unlikely)) is the reason from this problem and not something much deeper.


select a value from different tables depending on a condition I am working on an inventory system. We have stored transactions in a transactions table which stores (challanid, challandate, toid, fromid, toenittyid). Toentity can be a branch, distributor, dealer or stockist which are stored in different tables. I need to build a query for transactions over a period of time which shows all transactions. For this I need to show entityname which needs to be withdrawn by toid from respecitve dealer/distributor or stockist tables depending on toenitytid.

For example, if toentityid is 2, we need to get branchname from branch table, if toentityid is 3 we need to get stockistname from stockist table by toid and so on. I tried switch case, but that does not work to change the table name. I need help to complete this query. Thanks a lot for your help.

You could use something approximately like this; I think you will see how.

CREATE VIEW TransactionEntity
AS
SELECT 2 AS EntityType, Branch.* FROM Branch
UNION ALL
SELECT 3 AS EntityType, Stockist.* FROM Stockist
UNION ALL
SELECT 4 AS EntityType, ...

The tables may have different column names from each other, in which case you shouldn't use "*".  The column names of the view are the column names assigned in the first SELECT statement, but those can be changed from actual column names.

A different method, more complicated, more efficient (I think), is this:

SELECT
      tr.*
    , COALESCE(br.branchname, st.stockistname, - other columns - )
      AS entityname
FROM
transactions     tr
LEFT OUTER JOIN
branch           br
ON
( tr.toentity = 2 AND tr.toid = br.branchid )
LEFT OUTER JOIN
stockist         st
ON
( tr.toentity = 3 AND tr.toid = st.stockistid ) -- corrected mistake of ':' should be '.'
LEFT OUTER JOIN
- other tables that are needed -

The result of COALESCE is the first of its terms that is not NULL.  So, if the transaction is to a branch, then branchname produces the value.  If the transaction is to a stockist, then branchname is NULL but stockistname produces the value.  And so on, for the other tables that may hold the data that you need.

(Updated - typing mistake in the code example, corrected, see the comment)


Orphaned Availability Group Name So I was creating a distributed availability group and noticed I wanted to change the name of the AG so I tried to run DROP AVAILABILITY GROUP.  But it was already seeding and was just stuck in "Cancelling" status. So I restarted the SQL Service to get it to quit trying to seed.  But now the AG is gone on the primary but is still in the sys.availability_groups table as a record, so when I try to recreate it, it says that it already exists.

Is anyone aware of how to fix that without having to create a ticket with Microsoft since Adhoc updates aren't allowed on system tables anymore.

Interestingly, I ran the DROP AVAILABILITY GROUP on the local AG name and it cleaned up the orphaned distributed availability group row in the sys.availability_groups table.


mysqldiff all tables in database I'm running mysql diff on a reference db (created from scratch) and a dump of the current DB.

However, the compare dbs option only compares the names of the databases, which in my case are different. Because I'm running on the same server.

mysqldiff --server1=jaap@localhost --difftype=sql current:reference
# server1 on localhost: ... connected.
# Comparing `current` to `reference`                                   [FAIL]

# WARNING: Cannot generate SQL statements for these objects.
# Check the difference output for other discrepencies.
--- `current`
+++ `reference`
@@ -1 +1 @@
-CREATE DATABASE `current` /*!40100 DEFAULT CHARACTER SET latin1 */
+CREATE DATABASE `reference` /*!40100 DEFAULT CHARACTER SET latin1 */
Compare failed. One or more differences found.

I there a way to compare all tables in 1 statement?

As @jkavalik mentioned in the comment, the mysqldiff command stops after the first error it finds.
The --force option continues to look for more differences!


Bug with sp_execute_external_script I think I encountered a bug with sp_execute_external_script, so here is the replay.

I will get an error if I run the following script:

-- error occurs
exec sp_execute_external_script @language=N'R'
, @script = N' result <- as.data.frame(c(i, j));'
, @params = N'@i varchar(100), @j varchar(100)'
, @i = 'hello'
, @j = 'world'
, @output_data_1_name = N'result' -- the position of this param caused problem
with result sets ((test varchar(100)));

I will get an error

  Msg 8144, Level 16, State 2, Line 1
  Procedure or function  has too many arguments specified.

However, if I just move up the @output_data_1_name like the following:

-- NO error occurs
exec sp_execute_external_script @language=N'R'
, @script = N' result <- as.data.frame(c(i, j));'
, @output_data_1_name = N'result' -- this position in front of @parms caused NO problem
, @params = N'@i varchar(100), @j varchar(100)'
, @i = 'hello'
, @j = 'world'
with result sets ((test varchar(100)));

I will get a normal output:

Does anyone know whether I am doing something wrong or it indeed could be a bug in SQL Server 2016?

My environment is (by select @@version):

Microsoft SQL Server 2016 (SP1-CU1) (KB3208177) - 13.0.4411.0 (X64)
    Jan  6 2017 14:24:37
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 14393: )

Update:
I tested a little bit more and indeed the position of parameters will cause errors, such as following:

exec sp_execute_external_script
 @script = N' result <- as.data.frame(c(i, j));'
, @output_data_1_name = N'result' -- the position of this param caused NO problem
, @params = N'@i varchar(100), @j varchar(100)'
, @i = N'hello'
, @j = N'world'
, @language=N'R' -- if I put @language here, it will cause error
with result sets ((test varchar(100)));

or this:

exec sp_execute_external_script  @language=N'R'
, @script = N' result <- as.data.frame(c(i, j));'
, @output_data_1_name = N'result' -- the position of this param caused NO problem
, @i = N'hello'
, @j = N'world'
, @params = N'@i varchar(100), @j varchar(100)'
with result sets ((test varchar(100)));

The error message will be:

  Msg 214, Level 16, State 175, Procedure sp_execute_external_script, Line 1 [Batch Start Line 19]
  Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar'.

But I actually give each parameter of type nvarchar already.

I will consider open a bug report to MS if I do not see anyone pointing out my "errors".

The parameter list in @params can have names matching the names of the parameters used by the sp_execute_external_script procedure itself, like @script or @language, for instance. There would need to be a way, then, to determine whether a parameter pertains to the system SP or to the user script.

Making it a rule that @params be always specified last in the list of the parameters pertaining to sp_execute_external_script and all the parameters specified after @params be considered pertaining to the user script is one easy way to implement the distinction. So, if a parameter like @language is specified after @params, it will be interpreted as a script parameter and not the SP's parameter.

And I believe that is what is happening in the cases where you are specifying @output_data_1_name or @language after @params. Those parameters simply must go before @params to avoid potential ambiguity.

Granted, there is not a word about this in the manual, which, however, is a problem with the manual more than a problem with sp_execute_external_script.


External Memory Pressure I have a few "gaps" in my understanding of external memory pressure.  I've found quite a bit of helpful information online, including this info from SQLSkills.com.

  SQL Server has a built-in component of the SQLOS, the Resource
  Monitor, that monitors the QueryMemoryResourceNotification Windows
  Server API to get status about the OS level memory availability on the
  server.  If the Windows OS is under memory pressure, it will set a low
  memory notification that the Resource Monitor thread will detect and
  force the external clock hands on the caches internally to begin
  sweeps of the caches to cleanup and reduce memory usage allowing the
  process to free memory back to the OS.

I have inferred that the Windows OS does not actively "push" the OS
level memory availability status to SQL Server.  Rather, the SQLOS
Resource Monitor must actively ask for it via the Win API
function.
This would seem to be an asynchronous operation.  Am I interpreting
this correctly?
If my reasoning for #1 is sound, what causes the Resource Monitor to get the OS level memory availability status?  Is is performed on a schedule?  Event driven?  How much time might pass from the moment there is low memory at the OS level to the moment the SQL OS Resource Monitor becomes aware of it?
When SQL Server reduces memory usage to free memory back to the OS, just how bad is that?  Is this an "expensive" operation that should be avoided at all costs?

Yes you are correct, from SQL Server 2005 onwards with arrival of SQLOS( actually it was called UMS in SQl Server 2000) Wiindows OS never directly asks SQL Server to trim down its memory consuption when OS is seeing memory pressure. Instead it raises flags(MEMPHYSICAL_HIGH and MEMPHYSICAL_LOW). These flags are monitored by SQLOS dedicated threads ,that in turn sets a internal High Memory resource flag that tells the various caches they can allocate additional memory, or Low Memory resource flag that tells the various caches to try and reduce their memory allocations.

If memory pressure is severe apart from raising low memory notificaion OS  can page out SQL Server as well. There are certain scenarios where things would work differently regarding paging. No paging would happen if SQL Server service account has LPIM. In case of LPIM VirtualAlloc() function do not do major chunk of memory allocations instaed AWEAPI() does the major memory allocation and this is non pageable memory

The monitoring is continuous this is one of the tasks that SQLOS dedicated threads do. If I say dedicated they are bound to keep looking for any notification. I am not aware about schedule if any but whatever may be the schedule it would be definitely in seconds.The reaction time is quick and moment SQL server sees low memory notification it would immediately start trimming its memory consumption. But sometimes if external memory pressure is grave SQL Server might not get enough time to trim its memory consumption and free its caches and before it can complete it, OS might page out SQL server process.
Yes its expensive in terms that SQL Server has to clear its caches, trim down its memory consumption basically devoiding its processes from memory Or partially catering to there memory requests. The process of consuming and releasing memory involves cost so once SQL server consumes memory it does not gives it back(unless OS flags low memory notification) because it has to go all the way to process of asking memory from OS and the VirtualAlloc() function of OS would find a VAS region and map it to physical memory to actually provide memory to the process.

Edit:

  I'm wondering about the ramifications of not setting Max Server Memory.

Microsoft suggests that if you have DEDICATED SQL Server box solely running SQL Server database engine services leave max server memory to default. SQL Server can manage in better way. If you take my opinion this is not true if you have Windows server 2008 and 2005 having SQL Server 2005-2008 R2 installed on it because of various Bugs in Windows server release trimming SQL Server memory consumption. This holds true if you have latest Windows server 2012 having SQL Server 2012. But again this is as per my experience and scenario is limited.

What I suggest is its better to fix max server memory if you correctly set optimum value. Whatever version you are in please see answer given(By me and others) on This SE Thread and This Thread there is lot of evidence to say you should fix max server memory.

  If SQL Server "knows" the OS is low on memory immediately, and can return memory to the OS in a timely fashion, why not let it perform that task

That is good question but like I wrote before sometimes if memory crunch faced by OS is too severe SQL Server will respond to memory pressure but in that case before SQL Server gets enough time to clear cache it would be paged out by OS(because OS is in desperate need of memory) eventually slowing SQL Server process very much. Cases where OS can face severe memory crunch

Bug in OS
Third party driver installed leaking memory
Max server memory not set correctly and point 1 and 2 are true.
Someone start big file copy and any of the above 3 points are true.

In all above case problem was with OS and applications installed on it and SQL Server became victim.


Resolving DUPLICATE for a column with UNIQUE I have a few column with UNIQUE INDEX. In some queries, duplicates should be acceptable, in which a postfix should be added to the value. For example, if test title exits, we change it to test title-2.

Is there a way to make a unique value upon DUPLICATE?

INSERT INTO table1
  (title, abr, name)
  VALUES
  ('title', 'abr', 'name')

In this typical example, all three columns are UNIQUE. When the INSERT fails, I do not know which col caused the error to change its corresponding value.

How can I make a query to change value to value-identifier upon duplicate error?

I would look at your model first.  Placing a unique key constraint on a non-unique column gets you into this kind of problem.  What happens when you get a legitimate value like test title-2 but you've already used that value to resolve a collision on test title.

If I had to resolve your problem, I would build a query for each unique key of the form:

SELECT  title
FROM    table1
WHERE   title STARTS WITH ?

The results will need to be parsed to see what the suffix should be.  It might be possible to do this in a stored procedure.  However, I would strongly argue that the data model needed to be fixed, not the code.


Copy Table from one MySQL server to another Is there a way to copy or export a large table from MySQL server to postgres without affecting servers performance.

I mean the table is very large and using mysqldump or any select query has bad consequences.

Overall my question is: if there is a way to get the copy of the production database or table without performance problems on production.

If you can ignore changes that might happen while the copy is taking place...

Use the PRIMARY KEY to walk through the source table, fetch chunks of 100-1000 rows, copy them.  More on chunking, but adapt for just fetching, not deleting or updating.


SQL Server Agent Job - Unable to open Step output file. The step succeeded I have seen many posts with "Unable to open Step output file.", but can't find any references where the step succeeded. I have my output file location set to:

$(ESCAPE_SQUOTE(SQLLOGDIR))\$(ESCAPE_SQUOTE(JOBNAME))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt

Many other jobs on the same server are using the exact same output location (verified exact character match). This job is owned and being executed as the same user as the jobs which are successfully creating output files.

The job is performing a database restore of a user database with the job config pointed at the master database. This is the only job pointing at master, but I would be surprised if that made the difference.

Any thoughts?

It was recommended that I review the job name for any characters that would resolve to a bad windows file path since I am using a variablized path. This ended up being due to a '/' in the SQL Server Agent Job name.


Flexibility vs Performance question I'm in the middle of designing a database, and have a question for the veterans in regards to scalability.

Currently, I have table A, which represents some object, and table B, which contains key-value pairs that describe objects in table A.

If an object can have fields such as:

type
color
taste
isState
etc

but doesn't necessarily have to have every field, is this an efficient model for scalability?  a year from now, properties of objects in table A may change -- I may need to add more or remove some, and this seems more manageable than schema updates.  However, it has the potential to create issues since all of my data is amalgamated into a single table.  If table A has 20, 000 records, and each of those records links to 10 different things in table B, I have 200,000 records stored in table B.

Will this cause indexing issues as the number of records in table A grows?  How does this compare in terms of a more traditional FKEY relationship design for things like a type and color table?

Any insight is most appreciated!

You can create a table for every property. That way you define the property with the data type that best fits each property. A similar question and answer that describes a simple example, complete with SqlFiddle, is here:

What's the best way to store and search through many product specifications

This might be a little iffy on the scalability side. Each new property means creating a new table, although that would have no effect on existing objects or the code that handles them. So it is expandable but I don't know the absolute limits. Queries are a bit on the complicated side, with at least one COALESCE and possibly a CASE statement or two, but not, I think, any more complicated than the EAV model.


Excel / MS SQL Server: Exception of type Microsoft.Office.PowerPivot.BackEnd.StorageException We have an Excel table that gather data from our datawarehouse db on a SQL Server 2016. It queries a relative simple view to find the top customers of the last few days (including some aggregated columns) from a very large (some billion rows) ColumnStore table. The query usually needs about 1 minute to return about 200-300 rows.

A few days ago it started (randomly) to throw a very unspecific Exception of type Microsoft.Office.PowerPivot.BackEnd.StorageException error message after opening and / or manually refreshing the data in the Excel sheet (after about 30 seconds).

While Excel is refreshing the data, I can see the query when running sp_WhoIsActive / sp_BlitzWho.

Executing the query manual in SSMS works usually fine, there was no real workload at the server and after some tries (in SSMS) Excel started to work again.

Using the profiler I found, that Excel sends an EXEC sys.sp_prepare to return the meta data and compiles the execution plan.

When I executed the prepare statement in SSMS it hangs too, the same when I tried to display the estimated execution plan for the SELECT on the view itself. I let it run for a while and after about 7 minutes it was done compiling the query and showing me the estimated plan (sp_WhoIsActive showed me only about 30 sek CMEMTHREAD plus some minor wait stats for my query).

After this "initial" compiling both - SSMS and Excel - were able to load the data / show execution plans / preparing the statment without any delay.

Reason:

When I executed

SELECT STATS_DATE(s.object_id, s.stats_id) AS last_updated, s.*
  FROM sys.stats AS s
 WHERE s.object_id = OBJECT_ID('dwh.big_table')
 ORDER BY last_updated DESC

I found, that there are 4 auto generated statistics that were updated today, the last one exactly at the moment when the execution plan was finally generated. All updated statistics were on columns that were used in the query / view.

Obviously we had a problem with manually updating the statics (e.g. using Ola Hallengreens Maintenance Solution) after loading the latest data to the DWH and the Auto Update Statistics feature kicked in, when the query was first executed today. And while the statistics were updating, it could not compile an execution plan (e.g. since it does not know how many rows we have for the last x days and how much memory it needs to reserve).

Excel itself seems to have an internal timeout (found no way to configure it) of about 30 seconds regarding the sp_prepare statement and shows the very vague error message (Exception of type Microsoft.Office.PowerPivot.BackEnd.StorageException) when it does not get back the metadata for the query in this time.

Solution:

I moved the statistics update to a new (later) time frame, to ensure that the data was loaded first (before updating the statistics), so that there is no need for the Auto Update Statistics feature to kick in and block the table for several minutes...


Schema for a reservation system I'm working on a reservation system schema and have some problems defining availability of resources. I searched for similar question here and on Google but still don't have clear understanding

My app is for booking taxis. Where each taxi driver is either working today or taking a day off. So far I have come up with the following structure

table: Drivers
id INT PRIMARY KEY
name INT

table: Users
id INT PRIMARY KEY
name TEXT

table: Reservations
id INT PRIMARY KEY
start: DATETIME
end: DATETIME
userID: INT (FOREIGN KEY(Users.id))
driverID: INT (FOREIGN KEY(Drivers.id))

I was thinking of treating each driver as available if there is no reservation entry, and then  coming up with different types of reservations:

Unavailable => driver is taking a day off
Booked => driver is booked by a user between start/end times
Cancelled => cancelled booking

However, maintaining Unavailable/Booked status sounds a bit more complicated than I'd like to.

So, are there any suggestions how to improve the database schema?

What I would suggest to you before putting a lot of work into designing a system, is to take a leaf from the book of the great Issac Newton - "If I have seen further it is by standing on the shoulders of giants".

By this I mean take a long hard look at what's been done already - there are systems that could be of interest to you in this domain - take a look at this for example. Of course, in one sense, a taxi is just a resource to be scheduled - so you could look further afield at open source software for scheduling generally for ideas and inspiration.

That's not to say that you can't have your own ideas, but just take a look at some of the contenders in the open source world out there - borrow what you want and need from them and disregard the rest.

With respect to your particular question, I would go to the trouble of maintaining the details of day_off, booked, ill, unavailable... that way you can reward drivers who are never ill, rarely unavailable (i.e. when not actually booked) &c. The more information you can put into the database, the more you can do with it later. Just IMHO.


Setting innodb_buffer_pool_size in for Mysql We have a dedicated DB box with with 8 GB of RAM and running Redhat linux 64 bit OS. Our MySQL Data + Index size almost 7.8 GB. About setting innodb_buffer_pool_size, I have read 2 suggestions

Set it to 80% of RAM available on a dedicated DB server.
Set at-least 10% above the size of Data + Indexes.

We cannot satisfy the second condition in my case. So will it have negative effect on Database performance?

Will having innodb_buffer_pool_size of like 6GB affect the performance?

I think 80% is a little bit too much, I suggest 60%. Otherwise you'll risk swapping.

If you're data and indexes do not fit into ram (innodb_buffer_pool_size) you'll experience more disk reads. Which of course is slower than reading from RAM, especially when you have slow disks.

Monitoring disks (IOPS) can be done using iostat or pt-diskstats.


How to select records where NOW is between two dates in fields? I've searched all over for this, and I seem only to find how to get records where a single date is between two "outside" dates.

I want to find out how to select records where the current date is between the value in the startDate field and the value in the endDate field.

What I have so far (PHP):

$now = new DateTime();
$sql = "
    SELECT content, image, imageHeight, imageWidth, link, linkDescription
    FROM news
    WHERE appearanceDate < $now
      AND $now < termDate
    ORDER BY appearanceDate DESC
";

But it doesn't work.

You can use the NOW() function in mysql. Note, this assumes that termDate is the later date:

Select content, image, imageHeight, imageWidth, link, linkDescription
  From news
  Where appearanceDate < NOW()
    And termDate > NOW()
  Order By appearanceDate Desc;


Exporting table records to an INSERT script I'm setting up a test environment for development and need to export some static data from tables in Production. Ideally the exported product would take the form of a script with all the required INSERT statements. In doing so, I can reset the development environment quickly after each test.

Can anyone tell how to accomplish this using SQL Server 2008, SQL Management Studio, and/or Visual Studio 2010?

Note: None of the tables have any foreign keys, I have full permissions in the source database, and the number of rows per table is no more than a few dozen.

As well as using SSMS, you can use the commercial SSMS Tools Pack

I prefer it: more options, batching, cleverer all round.

  Insert statements for the whole database are generated by the order of PK-FK relationships. Top tables with no FK's are scripted first. Binary data is by default fully scripted. If you wish you can also set the scripting data limit between 0 and 10 Mb. Larger values than the limit are then scripted as NULL. Insert statements for the data in result grids are scripted into a new temporary table for each grid. For example from 5 result grids insert statements for 5 temporary tables get created.


Oracle Standard Edition Monitoring Tools We have 10g and 11g Oracle databases and all of them is Standard Edition.

We need monitoring tools like cloud control for standard editions. Do you have any suggestions ?

There you go;

Oratify -> I think, This is the best.
Solarwinds -> It's fine but I don't know about Standard Edition Support
MyOra -> We were using about 5 years ago but if you have free time you can try it :)


Locking SYS and SYSTEM Accounts In the interest of keeping the database as secure as possible, I'd like to lock the SYS and SYSTEM accounts so that no one can login with them.

Assuming that:

There are no OS scripts/cron jobs logging in as SYS or SYSTEM
There aren't any applications or outside utilities using either of these accounts
I can always login "/ as sysdba" with the proper OS account

Will locking these two accounts have any adverse effects? Has anyone done this before who can comment on whether or not it's a good idea?

Lock them both (SYS and SYSTEM). You shouldn't need to use them day-to-day, nothing will break.

On a day to day basis you should be using named user accounts that have SYSDBA or SYSOPER.

Tom Kyte recommends doing it, so you can always blame him if something does go wrong :-)


Can a Wallet be used with SQL Developer? How? I have a Wallet configured and working fine for SQLPlus. Is there a way to make it work with SQL Developer?

One way to do this is to specify an Advanced Connection Type and enter a custom JDBC URL as follows:

jdbc:oracle:oci:/@myDatabase

In addition the "OS Authentication" check box needs to be checked. This would seem to cause it to pass the OS username and password, but apparently (perhaps because I don't have NTS set as an authentication service in my sqlnet.ora) it does not and instead uses the wallet.


MySQL time_zone = SYSTEM not getting correct date on CURRENT_TIME Via SSH I print the current date of my server, which is correct based on my location:

When I run SHOW GLOBAL VARIABLES on MySQL, I have time_zone = SYSTEM - which makes me think MySQL will be using the system date and time.

However, when I run SELECT CURRENT_TIME it returns to me a result that looks like it's in another time zone (ignore the diff in minutes due to the time I took between both snapshots):

Is there any other necessary configuration to make CURRENT_TIME in MySQL reflect exactly the same system date time?

Solved, not through MySQL, but in my WHM > Server Configuration > Server Time, then clicked on Sync Time with Time Server.


Is it possible to create a MySQL slave from a MariaDB master? I need to create a MySQL slave instance which replicates the data from a MariaDB master. (I guess it won't work.) Any advice on how to create this replica architecture using MariaDB and MySQL?

I am also investigating and considering using tungsten. Does anybody have experience with it?

I think it can work, see e.g. this question (with answers). Note that:

You won't be able to use GTID replication as this is implemented differently in MariaDB and MySQL
You need to use versions of MySQL and MariaDB which are somewhat compatible.
Avoid using any MariaDB-specific features.


In Oracle 12c, is there an alternative to CREATE TYPE when I only have read-only privileges? I have read-only access to a Oracle 12c system where there is zero chance I can get additional privileges or get the DBAs to change anything on my behalf (such as do CREATE TYPEs for me).

I'm using Oracle 12c feature WITH FUNCTION myfunc() .. SELECT .. to create functions as part of my SQL queries, which is working great.

Except, I would like to do something similar as shown in this, which is:
WITH FUNCTION myfunc(p_numbers t_numbers) .. SELECT ..
where t_numbers would normally be declared:
CREATE TYPE t_numbers AS TABLE OF NUMBER;
which I can't do.

Is there any alternative way to accomplish this kind of "inline" function without CREATE TYPE privileges?  I want to be able to pass in an arbitrary quantity of numbers to an inline function as discussed in this.

There is built-in type called SYS.ODCINUMBERLIST which is a VARRAY(32767) of NUMBER, and EXECUTE privilege is granted on it to everyone (PUBLIC). You can use that for up to 32767 numbers, example:

with function f1 (p_numbers SYS.ODCINUMBERLIST) return number
as
begin
 return p_numbers.count;
end;
select f1(SYS.ODCINUMBERLIST(1,2,3,4,5,6,7)) from dual;
/

F1(SYS.ODCINUMBERLIST(1,2,3,4,5,6,7))
-------------------------------------
                                    7

In 19c, there is another built-in type called SYS.FI_NUMERICALS which is a TABLE of NUMBER, also usable by anyone in a similar way.

with function f1 (p_numbers SYS.FI_NUMERICALS) return number
as
begin
 return p_numbers.count;
end;
select f1(SYS.FI_NUMERICALS(1,2,3,4,5,6,7)) from dual;
/

F1(SYS.FI_NUMERICALS(1,2,3,4,5,6,7))
------------------------------------
                                   7

I have not found any documentation or reference for the latter.


Why does an index rebuild requires a Sch-M lock? I don't see why an index rebuild would require a Sch-M lock on the given index.

The documentation for How Online Index Operations Work says:

SCH-M (Schema Modification) if any source structure (index or table) is dropped.*
* The index operation waits for any uncommitted update transactions to complete before acquiring the S lock or SCH-M lock on the table.

That is specifically for online operations. If you're asking about offline operations, the logic is the same: nothing can be accessing a structure that is about to be dropped, not even under Sch-S only, or for internal use inside a system transaction. Also note the "(index or table)" there, so this applies to all indexes (including clustered) and heap tables.
Some time ago, the Sch-M restriction was applied when creating an indexed view. That was pointed out to be unnecessary (Connect link no longer available) because no structure was being dropped, only created, so the behaviour was changed (to only take Sch-S and Tab-S).
Also related from the documentation:

ALTER TABLE acquires a schema modify (SCH-M) lock on the table to make sure that no other connections reference even the metadata for the table during the change...

From the same link, SQL Server 2012 introduced the ability to add a NOT NULL column without taking Sch-M so long as the default value is a runtime constant. I mention this because it shows how SQL Server has evolved to reduce unnecessarily restrictive locking where possible. If you find a particular case where you feel Sch-M is genuinely unnecessary, you could make the suggestion on the Microsoft Feedback site.
In addition, in Unicorns, rainbows, and online index operations, Paul Randal says:

When then index operation has completed, the new and old indexes are in lock-step as far as updates are concerned. A schema-modification lock (SCH_M lock mode) is required to complete the operation. You can think of this as a super-table-X lock – it's required to bump the major version number of the table – no operations can be running on the table, and no plans can be compiling while the lock is held.

Related links:

The Sch-M lock is Evil by Michael J Swart


Is Tablespace something like Logical Volume? I'm confused about tablespaces in PostgreSQL. Is it something like LVM? I mean when disk is getting full, can we add another disk, format and then CREATE TABLESPACE tblspace LOCATION /media/disk2/data? Is it enough? Or we should manually ALTER databases, tables or indexes to take benefit of it?

Historically, PostgreSQL stored tables and indexes in individual files.  Tablespaces are a means of placing multiple tables/indexes into a single file or group of related files in the same directory.  Other database management systems use similar techniques, although tney can store multiple objects within a file.  On PostgreSQL a tablespace is implemented as a directory that individual files are placed in, but the files are still one-per-database-object.

A table must reside in a single tablespace unless it is partitioned, in which case each partition must reside on a given tablespace.  On some DBMS platforms you could add files to a tablespace as you suggest, although in the case case of PostgreSQL a tablespace is essentially just a directory for grouping files together.  On systems where this is possible it could result in suboptimal I/O performance as individual disks could contain I/O hot spots.  Probably, you would be better off setting multiple disks up in a RAID volume and placing the tablespace on that.

Depending on your workload you may wish to set up individual disk volumes with tablespaces on each volume and distribute parts of your database over the different volumes.  The most trivial case is separating database logs onto a separate volume, but you could distribute data over multiple volumes for various reasons.

EDIT: Here is a link on PostgreSQL tablespaces.


Optimize MySQL InnoDB query for SUM and Order By I have an MySQL InnoDB table with 1.3M rows and 670MB size:

+----------------+--------------+------+-----+---------+-------+
|     Field      |     Type     | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| item_name      | varchar(255) | NO   |     | NULL    |       |
| item_link      | varchar(50)  | NO   | PRI | NULL    |       |
| item_price     | varchar(10)  | NO   |     | NULL    |       |
| shop_name      | varchar(40)  | NO   | MUL | NULL    |       |
| sales          | int(11)      | NO   |     | NULL    |       |
| sale_date      | varchar(10)  | NO   | PRI | NULL    |       |
+----------------+--------------+------+-----+---------+-------+

CREATE TABLE `sales`
  (
     `item_name`      VARCHAR(255) NOT NULL,
     `item_link`      VARCHAR(50) NOT NULL,
     `item_price`     VARCHAR(10) NOT NULL,
     `shop_name`      VARCHAR(40) NOT NULL,
     `sales`          INT(11) NOT NULL,
     `sale_date` VARCHAR(10) NOT NULL,
     PRIMARY KEY (`item_link`, `sale_date`),
     KEY `sale_date` (`sale_date`),
     KEY `shop_name` (`shop_name`)
  )
engine=innodb
DEFAULT charset=latin1

Here are my my.ini settings for my 3GB RAM server:

key_buffer = 16M
max_allowed_packet = 16M
sort_buffer_size = 8M
net_buffer_length = 8K
read_buffer_size = 2M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 8M
log_error = "mysql_error.log"
innodb_autoinc_lock_mode=0
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
thread_stack = 192K
tmp_table_size = 64M

innodb_buffer_pool_size = 1.5G
innodb_additional_mem_pool_size = 16M
innodb_log_file_size = 350M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 120
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90

When I run next query it takes over 10-15 seconds to return the results:

SELECT item_name,
       item_link,
       item_price,
       shop_name,
       sales
FROM   `sales`
WHERE  sale_date = '2019-04-18'

and even longer for:

SELECT item_name,
       item_link,
       item_price,
       shop_name,
       Sum(sales) AS sales
FROM   `sales`
WHERE  sale_date BETWEEN '2019-04-17' AND '2019-04-18'
GROUP  BY item_link

I have a composite primary key for item_link and sale_date, I've also added indexes for shop_name and sale_date.

I don't know what else to do and I think this kind of query should perform way faster for such a relatively small table, please help!

You have indexes on item_link and sale_date but you are not using item_link.  When The first column of index are not in use in the where clause, the index are tends to be skipped while query execution.

Plan you index as per you where clause which in this case sale_date would be a good

Thanks,


How to generate missing values for each employee for each day CREATE TABLE `EmpWorkCount` (
      `emp_Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `day` int(10) unsigned NOT NUL,
      `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `updated_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `daycount` int(10) NOT NULL DEFAULT '0',
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Current Values in the table :

    emp_Id    |       day            | daycount  |
       1      |        0             |  10       |
       1      |        1             |  11       |
       2      |        1             |  13       |
       2      |        0             |  12       |

.

CREATE TABLE `Emp` (
      `emp_Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `updated_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Emp Table looks like :

emp_Id    |       created_At            | updated_At            |
   1      |        2018-12-04 20:00:00  |  2018-12-04 20:00:00  |
   1      |        2018-12-04 20:00:00  |  2018-12-04 20:00:00  |
   3      |        2018-12-04 20:00:00  |  2018-12-04 20:00:00  |

I need to run a query where I need a count of EmpWorkCount with different day values (0, 1, 2, 3) for each employee

    Select emp_Id, day_list.day,
    IF(daycount = NULL, 0, daycount)  as daycount from (
        SELECT 0 day
           UNION ALL
           SELECT 1
           UNION ALL
           SELECT 2
           UNION ALL
           SELECT 3   )day_list
      LEFT JOIN
      select emp_Id, day, daycount from EmpWorkCount
   )

(my query is incorrect, but I don't know how I multiply these 4 values from day for each employee

this gives output for the emp_Id, day values present in the table e.g.

emp_Id    |       day            | daycount    |
   1      |        0             |  10         |
   1      |        1             |  11         |
   2      |        1             |  13         |
   2      |        0             |  12         |
   NULL   |        2             |  0          |
   NULL   |        3             |  0          |

but if the database does not contain let's say emp_Id : 1 with day values = 2 or 3 these records don't appear with a count 0.

Output that I look for :

    emp_Id    |       day            | daycount    |
       1      |        0             |  10         |
       1      |        1             |  11         |
       1      |        2             |  0          |
       1      |        3             |  0          |
       2      |        1             |  13         |
       2      |        0             |  12         |
       2      |        2             |  0          |
       2      |        3             |  0          |
       3      |        0             |  0          |
       3      |        1             |  0          |
       3      |        2             |  0          |
       3      |        3             |  0          |

How do I go about this one?

In my query I have not joined with Employee Table --> but the output should even contain Employees which do not have a entry in EmpWorkCount Table.

You must generate all possible (employee, day) pairs, so you must obtain employees list additionally, not only days list.

The query will be something like

SELECT emp_list.emp_Id, day_list.day, COALESCE(EmpWorkCount.daycount, 0) daycount
FROM ( SELECT 0 day
       UNION ALL
       SELECT 1
       UNION ALL
       SELECT 2
       UNION ALL
       SELECT 3 ) day_list
JOIN ( SELECT emp_Id FROM EmpWorkCount
       UNION
       SELECT emp_Id FROM Emp ) emp_list
LEFT JOIN EmpWorkCount ON day_list.day = EmpWorkCount.day
                      AND emp_list.emp_Id = EmpWorkCount.emp_Id
/* ORDER BY 1, 2 */


Run time fetch the value from a table and iterate these values and sent to another procedure I am new to write store procedures in sql I want to use a store procedure into another store procedure
Test is a store procedure which works fine but in usage store procedure I want stock is a table and stocks is a variable having the result value of this table with three column i.e : siteId,productId,id so i need to send all these rows of this table to another store procedure

CREATE PROCEDURE USAGE AS BEGIN
   DECLARE stocks table(siteId int, stockId int, productId int)
   DECLARE @i int
   set stocks  = 'insert into @stocks (siteId , stockId , productId)' +
   '(select  siteId, id, productId from "Stock")'
SET @i = 1
 WHILE (@i<stocks.length)
   EXEC Test stocks[@i].siteId, stocks[@i].stockId, stocks[@i].productId
  END
  select stocks
 END

unable to get stocks in array/table format for accessing it
getting the error
Msg 156, Level 15, State 1, Procedure USAGE, Line 2 Incorrect syntax near the keyword 'table'

If your goal is to execute the Test stored procedure for all stock rows, I'd recommend using a CURSOR to read each row from the stock table and pass the data into the Test stored procedure.  Here is an example.

CREATE PROCEDURE USAGE
AS
BEGIN
    --Declare variables
    DECLARE @SiteId INT;
    DECLARE @StockId INT;
    DECLARE @ProductId INT;

    --Declare cursor to select rows from stock table
    DECLARE _CURSOR CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
    FOR
    SELECT SiteId
        ,StockId
        ,ProductId
    FROM stock;

    --open the cursor
    OPEN _CURSOR;

    --fetch stock row into variables
    FETCH NEXT
    FROM _CURSOR
    INTO @SiteId
        ,@StockId
        ,@Productid;

    --for each stock row in the cursor, execute the Test stored procedure
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC Test @SiteId
            ,@StockId
            ,@ProductId;

        --fetch the next stock row in the cursor
        FETCH NEXT
        FROM _CURSOR
        INTO @SiteId
            ,@StockId
            ,@Productid;
    END --End While

    --close the cursor after processing all stock rows
    CLOSE _CURSOR

    --deallocate the cursor
    DEALLOCATE _CURSOR
END


SQL Server 2005 Index Rebuild/Reorganize I have checked my huge db and run some shrink tasks for 5 or 7 times. I got about 11 GB freespace. This DB is used for offline reporting operations. Before, when we create a report it was growing about 2 or 3 GB ( this is also strange but there about 6 millons lines of CDR logs imported and processed ) but last time we tried. Even if the free space is about 11 GB. Reports can not be generated. Disk spaces being empty unexpectedly.

So make some search and see some advices to check the indexes. According to the indexes fragmentation there may be some disk spaces issues.

Firstly; does index fragmentation causes disk spaces unefficient use ?
Second; what would happen if I make index reorganize or rebuild ? Of course there is a risk of DB crash always but would it be harmfull for the DB ?

There are two different types of fragmentation to worry about:  physical and logical.  Physical fragmentation means the file that stores your indexes is fragmented at a file system level.  Logical fragmentation means that page splits have occurred while inserted or updating data in an index.  Incidentally, shrinking your database files will eventually cause physical fragmentation if (when) your database files are forced to grow to accommodate more data and the space needed for the grow is not contiguous with the existing data file.  Also, reorganizing and rebuilding your indexes, unless sorting is performed in tempdb, will, more than likely, cause grows on the file containing your indexes.  This could further compound the physical fragmentation issue.  This is why shrinking your datafiles is almost never a good idea; they grew for a reason, they will simply grow again, and when they do they will cause physical fragmentation.

Unfortunately, I cannot speak to your first question.  I assume that logical index fragmentation will not cause problems at an IO level.  However, physical fragmentation will manifest itself as an increased number of random reads, due to the file system fragmentation.

As for your second question, index maintenance should not cause any issues.  As a matter of fact, you should already be performing index maintenance as a part of your routine maintenance.  An important thing to note is that index rebuilds implicitly update your index statistics with a full scan.  However, index reorganizations do not.  As a part of my index maintenance, I explicitly perform a full scan statistics update against indexes I reorganize, instead of rebuild.

Finally, I doubt your issue is caused by indexes.  It sounds like you are loading a lot of data into either a staging table or a temporary table, prior to running this report.  If you are loading into a staging table, perhaps you could move the staging table to its own filegroup, on another storage volume.  If you are loading into a temp table, you are probably running into tempdb running out of space.  Either way, it appears you either need additional storage or a new filegroup on a different storage medium for your staging table.

Hope this (admittedly too long) answer helps!

Matt


PostgreSQL 9.1 Hot Backup Error: the database system is starting up I have been working on a hot backup for Postgres 9.1 for awhile and have run into a consistent issue.  After restarting Postgres on the slave server the pgstartup log file and the daily log file under the pg_log directory reads with no errors. However, when I try to enter into the database using the psql command, I get the error:

FATAL: the database system is starting up.

The recovery.conf file also does not turn to recovery.done.  I have extensively researched this error and consistently find the same response: the database has not been cleanly shut down before I tried to restart Postgres. The only ways I have restarted Postgres is through the service postgresql-9.1 restart or /etc/init.d/postgresql-9.1 restart commands.  After I receive this error, I kill all processes and again try to restart the database and still receive the same error.  I am at a loss of where to go from here and how to fix this issue.  Below is the exact process that I have done to complete the hot backup.

Master Server Configurations:

pg_hba.conf, added the line:

host replication postgres IPAddressOfSlaveServer trust

postgresql.conf:

wal_level = hot_standby
max_wal_senders = 5
listen_address = '*'
port = 5432
max_wal_senders = 5
wal_keep_segments = 32

Slave Server Configurations:

postgresql.conf:

hot_standby = on

recovery.conf:

standby_mode = on
primary_conninfo = host=IPAddressOfMasterServer
port = 5432
user = postgres
restore_command = 'cp /var/lib/pgsql/9.1/data/pg_xlog/%f "%p"'

After configuring both servers

I change to the postgres user on the master server and run the commands:

psql -c "Select pg_start_backup('label',true);";
rsync -a -v -e ssh /var/lib/pgsql/9.1/data slave:/var/lib/pgsql/9.1/data \
        --exclude postmaster.pid
pgsql -c "select pg_stop_backup();";

After syncing database with slave server

I restart the slave server and the start up does not fail. The pgstartup.log reads:

Success. You can now start the database server using:

    /usr/pgsql-9.1/bin/postgres -D /var/lib/pgsql/9.1/data
or
    /usr/pgsql/9.1/bin/pg_ctl -D /var/lib/pgsql/9.1/data -l logfile start

the current day log file, postgresql-Thu.log, reads:

Log: shutting down
Log: Database system is shut down
Log: database system was shut down in recovery at 2012-4-10
Log: entering standby mode
Log: restored log file "logFileName" from archive
Log: consistent recovery state reached at 0/BF0000B0
Log: redo starts at 0/BF000020
Log: restored log file "logFileName" from archive
Log: unexpected pageaddr 0/85000000 in log file 0, segment 192, offset 0
Log: unexpected pageaddr 0/85000000 in log file 0, segment 192, offset 0
Log: streaming replication successfully connected to primary

I researched unexpected pageaddr and from the postgres archives, it is my understanding that it is quite normal and one of the expected ways to detect end-of-WAL.

Any advice would be greatly appreciated.

The message "The database system is starting up." does not indicate an error.  The reason it is at the FATAL level is so that it will always make it to the log, regardless of the setting of log_min_messages:

http://www.postgresql.org/docs/9.1/interactive/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN

After the rsync, did you really run what you show?:

pgsql -c "select pg_stop_backup();";

Since there is, so far as I know, no pgsql executable, that would leave the backup uncompleted, and the slave would never come out of recovery mode.  On the other hand, maybe you really did run psql, because otherwise I don't see how the slave would have logged such success messages as:

Log: consistent recovery state reached at 0/BF0000B0

and:

Log: streaming replication successfully connected to primary

Did you try connecting to the slave at this point?  What happened?

The "Success. You can now start..." message you mention is generated by initdb, which shouldn't be run as part of setting up a slave; so I think you may be confused about something there.  I'm also concerned about these apparently conflicting statements:

  The only ways I have restarted Postgres is through the service
  postgresql-9.1 restart or /etc/init.d/postgresql-9.1 restart commands.
  After I receive this error, I kill all processes and again try to
  restart the database...

Did you try to stop the service through the service script?  What happened?  It might help in understanding the logs if you prefixed lines with more information.  We use:

log_line_prefix = '[%m] %p %q<%u %d %r> '

The recovery.conf script looks odd.  Are you copying from the master's pg_xlog directory, the slave's active pg_xlog directory, or an archive directory?


How to estimate REDO phase time during RESTORE I am restoring database now and get it hang on 100% but still going (doing redo phase). I am thinking how to estimate time of completion for this phase. But because SQL have no build-in way to do it, I suppose is it possible to count all modified by commited transactions pages from transaction log, multiple it by 8 to get exact space needed to be modified during the redo phase in data file, and estimate completion time by dividing it on your disk subsystem throughtput?

I am thinking how to estimate time of completion for this phase. But because SQL have no build-in way to do it,

If you have not enabled any trace flag you have no option but to look at DMV sys.dm_exec_requests and look for column percent_complete to gauge how much your restore operation has completed. Sample script from here

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

  I am restoring database now and get it hang on 100% but still going (doing redo phase).

Are you sure this is the redo phase, I think this is the UNDO phase and depending on how much VLF's are there in database log file the UNDO process can take time. Of course the whole process of restore can further be delayed if Instant file initialization is not there. You can check using This script whether database is having IFI . If not provide SQL Server service account perform volume maintenance tasks rights and this will require a SQL Server service restart

As already pointed by Gameiswar you can enable trace flags to get more information. You can also read Blog by Bob Dorr for more information about internal restore process.

Please note these are undocumented commands so don't enable it on prod servers.. These should be used for testing purpose only

DBCC TRACEON(3004);

DBCC TRACEON(3014);

DBCC TRACEON(3604);

DBCC TRACEON(3605);

GO

Below is what I got when I restored a database with trace flags enabled

RestoreDatabase: Database logicalreads

Opening backup set

Restore: Configuration section loaded

Restore: Backup set is open

Restore: Planning begins

Halting FullText crawls on database logicalreads

Dismounting FullText catalogs

X-locking database: logicalreads

Restore: Planning complete

Restore: BeginRestore (offline) on logicalreads

Restore: PreparingContainers

Restore: Containers are ready

Restore: Restoring backup set

Restore: Transferring data to logicalreads

FileHandleCache: 0 files opened. CacheSize: 12

Restore: Data transfer complete on logicalreads

Processed 168 pages for database 'logicalreads', file 'Logicalreads' on file 1.
Processed 1 pages for database 'logicalreads', file 'Logicalreads_log' on file 1.
Restore: Backup set restored

Restore-Redo begins on database logicalreads

Rollforward complete on database logicalreads

Restore: Done with fixups

Restore: Transitioning database to ONLINE

Restore: Restarting database for ONLINE

Restore: Database is restarted

Resuming any halted fulltext crawls

Restore: Writing history records

Writing backup history records

Restore: Done with MSDB maintenance

RESTORE DATABASE successfully processed 169 pages in 0.159 seconds (8.303 MB/sec).
RestoreDatabase: Finished


How to load balance mongodb replica cluster I have created a mongod replica cluster of 2 nodes , mongo1 and mongo2. to load balance I have added following rule in my haproxy

listen mongo_replica_cluster
        bind *:27017
        mode tcp
        balance roundrobin
        server mongo1 10.2.0.12:27017 check inter 10s fall 3 rise 99999999
        server mongo2 10.2.0.11:27017 check backup

but if the mongo1 goes down mongo2 becomes the primary but even if mongo1 comes up mongo2 remains primary but ha redirect all traffic to mongo1 which is slave now. I want to redirect all my traffic to only the primary node.

You don't use ha-proxy.

Every node what connects to this replica set should have mongodb://mongo1,mongo2/?replicaSet="yourRS" -connection string, what tells which nodes are on that replicaset.. String don't need always list all nodes, most of them is enough, because Application can "ask" where current primary is located.


Query timeouts when duration is under a second We are getting the following exception in an application:

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

So, I looked at the query and sure enough it was taking a really long time.  So I tuned it up to take less than a second.
But I am still getting timeouts.
So I set up a Extended Events session for the Attention event to catch the timeouts and see what what going on (using this page as a guide).
The results were confusing.  I see my queries in the list, but the duration logged in the trace is under a second!
Here are some example values I am getting in duration column for the timeout:

timeout

539

539

474

423

505

904

I am confused.  Why am I getting an SQL Server timeout error, when the duration shows an average of a half a second?
NOTE: The output from the extended event shows some rows that are 30+ seconds.  These are legit timeouts to my view and I am working on those.  (I just point this out to show that the Attention extended event seems to be working for those queries.)
I thought it might be blocking, so I setup a Blocked Process Report extended event to see (following this guide). While there are blocks happening on the server, they are not on the databases that my query is running against.
The application is using a API called Dapper (a fairly popular Micro ORM that I hear is used to run Stack Exchange).  I imagine it has decent connection management in it.  But even if they misused its connections, as I understand it, that leads to resource leaks, not SQL timeouts.
The timeout of the query in the application is set to 120 seconds. That should be plenty of time to get most of the queries that I am seeing reported as timeouts.

I have realized that what ever duration is, it is not the time that the timedout query ran for.  I ran waitfor delay '00:00:30' in a query window set to timeout in 10 seconds.  The results should have been 10 seconds for all of the time outs.  But I got these values for my 6 tests: 70707, 311, 269, 264, 3442, 272.  My guess is that duration is the amount if time it took to handle the Attention event (i.e. Stop the query).

Just to be sure that the duration wouldn't work correctly with an actual running query, I ran a long running one, again set to timeout in 10 seconds.  The durations where: 64246, 58002, 52828, 68061, 48153, 49084.  They took longer, but none of those times is close to 10 seconds.  Again, confirms that duration is not what it seems to be.

The documentation says duration is in Microseconds (not milliseconds).  That also could work if it is the time to handle the Attention event, but it would never match up to the time it took for the timed out query to run.

I ran the long running query without a time out and had to stop it after 120 seconds.  It took a lot longer to stop, and the duration for the Attention event was 6400055.  6.4 seconds is about how long it took to stop.  Again confirming that duration is the amount of time it takes to handle the "Attention" event.


The best way to rename the tables after development completed What is the easiest and most reliable approach to rename the database tables and columns  in SQL Server 2008 r2?
We have finished development and for some reasons, we are required to rename some tables and afew columns.

Is using synonyms the good way of doing that? What are the pitfalls we should be prepared for? Does it help in renaming the columns gracefully as well?

We have a lot of scripts related to these tables and also they are referenced in the application by .net developers.

I think the approach depends on whether the application(s) are live or if you are still testing.

For tables, the safest approach is to create a synonym using the new name. This way you can change the app(s) one at a time (or even one reference at a time), without having to change all of them at once. You don't have to drop the synonym and rename the table until you are confident you have all the changes in place.

CREATE SYNONYM dbo.NewName FOR dbo.OldName;
-- change app to point to dbo.NewName;

-- once all of your changes have been tested:
BEGIN TRANSACTION;
  DROP SYNONYM dbo.NewName;
  EXEC sp_rename N'dbo.OldName', N'NewName', N'OBJECT';
COMMIT TRANSACTION;

For columns, it is a little trickier. You can create synonyms that point to a view instead, but not all views will necessarily be updatable depending on the base table. As a simple example:

CREATE VIEW dbo.vNewName
AS
  SELECT Column1, NewColumnName = OldColumnName
    FROM dbo.OldName;

CREATE SYNONYM dbo.NewName FOR dbo.vNewName;

Then like above, when you have changed all references to columns and the new table name, simply:

BEGIN TRANSACTION;
  DROP SYNONYM dbo.NewName;
  DROP VIEW dbo.vNewName;
  EXEC sp_rename N'dbo.OldName', N'NewName', N'OBJECT';
  EXEC sp_rename N'dbo.NewName.OldColumnName', N'NewColumnName', N'COLUMN';
COMMIT TRANSACTION;

If the application is not live and is still going through testing, just rename the columns and fix what breaks after a global search and replace (or smart refactor using SSDT, RedGate, etc) through app code / procedures etc.

If the application is live, you will need to step a little more gingerly.


Does a hidden member count toward and operate as part of a write_concern majority in MongoDB 2.6? Does a hidden member count toward and operate as part of a write_concern majority in MongoDB 2.6?

It states here that for the w option, prior to Mongo 3.0,

  "majority" refers to the majority of the replica set’s members.

It seems then that if I have four Mongo 2.6 nodes and one is hidden, then "majority" calculates as w=3, since the hidden node is still a replica-set member. Meanwhile, it's unclear to me whether writing to the hidden member counts toward fulfilling the write concern -- after all, only three nodes are visible for reads.

The concern I have is that with the above example, if one non-hidden node goes down, it becomes impossible to fulfill a write with w="majority".

Indeed hidden members take part in voting. If your write concern is equals to visible nodes and there is a hidden member who can vote, it shouldn't be an issue. You have to plan write concern carefully.

Please see https://docs.mongodb.org/manual/core/replica-set-hidden-member/


SQL Server Agent Jobs and Availability Groups I'm looking for best practice in dealing with scheduled SQL Server Agent jobs in SQL Server 2012 availability groups. Maybe I missed something, however at the current state I feel that SQL Server Agent is not really integrated with this great SQL2012 feature.

How can I make a scheduled SQL agent job aware of a node switch?  For example I have a job running on the primary node which loads data each hour. Now if the primary goes down, how can I activate the job on the secondary which now becomes primary?

If I schedule the job always on the secondary it fails because then the secondary is read-only.

Within your SQL Server Agent job, have some conditional logic to test for if the current instance is serving the particular role you are looking for on you availability group:

if (select
        ars.role_desc
    from sys.dm_hadr_availability_replica_states ars
    inner join sys.availability_groups ag
    on ars.group_id = ag.group_id
    where ag.name = 'YourAvailabilityGroupName'
    and ars.is_local = 1) = 'PRIMARY'
begin
    -- this server is the primary replica, do something here
end
else
begin
    -- this server is not the primary replica, (optional) do something here
end

All this does is pull the current role of the local replica, and if it's in the PRIMARY role, you can do whatever it is that your job needs to do if it is the primary replica.  The ELSE block is optional, but it's to handle possible logic if your local replica isn't primary.

Of course, change 'YourAvailabilityGroupName' in the above query to your actual availability group name.

Don't confuse availability groups with failover cluster instances.  Whether the instance is the primary or secondary replica for a given availability group doesn't affect server-level objects, like SQL Server Agent jobs and so on.


MYSQL Query Index Check I have a table with the following structure;

CREATE TABLE `operations_copy` (
  `id` bigint(22) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `zoneId` int(11) unsigned DEFAULT NULL,
  `machineId` int(11) unsigned DEFAULT NULL,
  `shiftId` int(11) unsigned DEFAULT NULL,
  `statusId` int(11) unsigned DEFAULT NULL,
  `actualCycle` float(10,4) NOT NULL,
  `targetCycle` float(10,4) NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=185538661 DEFAULT CHARSET=latin1;

I have nearly 200m records in this table.

I am frequently performing the following queries on this table;

1 - Operations By Zone

SELECT zoneId, statusId, COUNT(*) as intervals
FROM `operations`
WHERE (`date` > 'XXXX-XX-XX' AND `date` <= 'XXXX-XX-XX')
GROUP BY `zoneId`,`statusId`
ORDER BY NULL;

2 - Operations By Machine

SELECT machineId, statusId, COUNT(*) as intervals
FROM `operations`
WHERE (`date` > 'XXXX-XX-XX' AND `date` <= 'XXXX-XX-XX') GROUP BY `machineId`,`statusId`
ORDER BY NULL;

3 - Operations By Shift

SELECT shiftId, statusId, COUNT(*) as intervals
FROM `operations`
WHERE (`date` > 'XXXX-XX-XX' AND `date` <= 'XXXX-XX-XX')
GROUP BY `shiftId`,`statusId`
ORDER BY NULL;

I am currently looking at 30s for query #1.

I have enabled caching, which reduces the query time dramatically after the initial query, but I still want to optimise the initial query.

Studying has shown me that I need to make better use of indexes (ref: https://www.slideshare.net/billkarwin/how-to-design-indexes-really, MYSQL docs and more). So I want to check the following indexes will be the best to optimise each query;

#1

KEY `operations_by_zone_query` (`date`,`zoneId`,`statusId`,`id`)

#2

KEY `operations_by_machine_query` (`date`,`machineId`,`statusId`,`id`)

#3

KEY `operations_by_shift_query` (`date`,`shiftId`,`statusId`,`id`)

Any help will be much appreciated. Thanks.

I have an interesting idea for you.

Create the following indexes

ALTER TABLE operations
    ADD INDEX `date_zoneId_statusId_ndx`    (`date`,`zoneId`,`statusId`)
   ,ADD INDEX `date_machineId_statusId_ndx` (`date`,`machineId`,`statusId`)
   ,ADD INDEX `date_shiftId_statusId_ndx`   (`date`,`shiftId`,`statusId`)
   ,ADD INDEX `zoneId_statusId_date_ndx`    (`zoneId`,`statusId`,`date`)
   ,ADD INDEX `machineId_statusId_date_ndx` (`machineId`,`statusId`,`date`)
   ,ADD INDEX `shiftId_statusId_date_ndx`   (`shiftId`,`statusId`,`date`)
;

You could then evaluate your queries by running:

EXPLAIN SELECT zoneId, statusId, COUNT(*) as intervals
FROM `operations`
WHERE (`date` > 'XXXX-XX-XX' AND `date` <= 'XXXX-XX-XX')
GROUP BY `zoneId`,`statusId`
ORDER BY NULL\G

EXPLAIN SELECT machineId, statusId, COUNT(*) as intervals
FROM `operations`
WHERE (`date` > 'XXXX-XX-XX' AND `date` <= 'XXXX-XX-XX') GROUP BY `machineId`,`statusId`
ORDER BY NULL\G

EXPLAIN SELECT shiftId, statusId, COUNT(*) as intervals
FROM `operations`
WHERE (`date` > 'XXXX-XX-XX' AND `date` <= 'XXXX-XX-XX')
GROUP BY `shiftId`,`statusId`
ORDER BY NULL\G

The output from each EXPLAIN will plainly tell you which index.

It will also show the least number of steps the Query Optimizer will take.

My guess is that the indexes with the date column last will be selected. An exception may be a small date range which may favor the indexes with the date column first. In that instance, you would want to keep all 6 indexes.

The indexes with the date column last would be used by the GROUP BY so that no temp table sorting would be necessary.

WARNING ON HAVE MANY INDEXES : Bulk INSERT performance would suffer due to managing multiple covering indexes, but SELECT performance would improve greatly. Dropping unnecessary indexes becomes a necessity.


Difference between database backup and data-tier application export I don't know if this is a bad question, but I don't understand what the difference is between normal DB backups and data-tier applications in SQL Server. I know backups can't be imported from SQL Server to an older version of SQL Server, but data-tier applications do work for this, and I've been using this to move some of our databases between servers. What are the differences from a backup perspective?

A backup is a full copy of all the database pages, or a dump of the database if you like, starting at page 0 and written sequentially to the backup device.

The Data-Tier application is a set of scripts that will create the database objects along with insert statements that will insert the data into the table objects afterwards.

From a backup perspective the Data-Tier application is a snapshot of the database in a certain state, you can install the application in that state but you can not restore additional data but have to copy the data from another source. The backup is a consistent state of the database and you can make log or differential backups that you can use to restore the database to a point in time.


sp_BlitzFirst - File Stats Interpretation I've had sp_BlizFirst running for a few days every 12 minutes to capture the various statistics.

What I've come to realise though is that it doesn't seem to gather a lot of File Related Performance Stats apart from I/O stalls.

Is there enough info to make a decision on whether I/O is a problem or not based off Wait Statistics and I/O Stalls? I can't find in the Blitz tables any mention of Disk avg. read time, Disk avg. write time, Avg. disk queue length that I thought should be considered too?

Can anyone give some feedback on the below? My Parallelism stats definitely need to be properly looked at (CXPACKET), but I'm curious about my disk i/o performance also. OLDEDB Waits I believe are being caused by Linked Servers and Idera SQL Diagnostic Manager monitoring the instance every 1 minute.

From https://www.sqlskills.com/blogs/paul/are-io-latencies-killing-your-performance/ I read the following:
Everyone has their idea of what constitutes good or bad I/O latency, and here’s my take:
Excellent: < 1ms
Very good: < 5ms
Good: 5 – 10ms
Poor: 10 – 20ms
Bad: 20 – 100ms
Shockingly bad: 100 – 500ms
WOW!: > 500ms

If I go by that, I'm in the Bad category.

Here are my Wait Statistics:

Server 1

The Average Read I/O Stall is 23.91

The MAX Read I/O Stall was 1162

The Average Write I/O Stall is 0.71

The MAX Write I/O Stall was 139

and here's the full extract.

Server 2

The Average Read I/O Stall is 26.28

The MAX Read I/O Stall was 428

The Average Write I/O Stall is 2.60

The MAX Write I/O Stall was 667

and here's the full extract.

Disclaimer: I'm one of the authors of sp_BlitzFirst and the First Responder Kit.

The wait stats section is on top for a reason: you need to focus on your top wait types. It doesn't really matter how fast or slow your drives are if SQL Server isn't waiting on them. Great example: say you've got a read-only database hosted on a server with enough memory to cache the entire database, and none of your queries happen to hit TempDB. Once the data's up in cache, does it matter how slow your drives are? Kinda - for maintenance tasks like backups and index rebuilds - but not for end user queries.

Now, look at your server's top 3 waits:

CXPACKET 1,530 hours - related to parallelism
OLEDB 502 hours (typically harmless, but I leave it in there because sometimes it isn't harmless in large quantities)
LATCH_EX 250 hours - usually goes hand in hand with CXPACKET
PAGEIOLATCH_SH 191 hours - reading data pages from a data file.

Yo, man - your top wait is coming up on TEN TIMES LARGER than your storage wait.

Forget the storage: you're barking up the wrong tree. Focus on your top wait type. To tune parallelism, go to https://BrentOzar.com/go/cxpacket. The short story:

Set Cost Threshold for Parallelism to 50 (some folks will say 40 or 75, and that's fine too)
Set MAXDOP to the number of cores in each processor
Find the queries reading large amounts of data, and tune them or the indexes they need. My favorite way is sp_BlitzCache @SortOrder = 'reads'
Ask SQL Server which indexes it wishes it would have had during query execution. My favorite way is sp_BlitzIndex @GetAllDatabases = 1.