Is left hash join always better than left outer join?

Semantically, both queries are the same.  The LOOP versus the HASH simply tells SQL Server which option to use to return results.  If you run the query without LOOP or HASH, SQL Server may pick either of those options, depending on which it thinks will perform best.  Both options return identical results by design.  To put it slightly differently1, the keywords HASH and LOOP in this case are what is known as join hints, one of the three kinds of hints in Transact-SQL.  To confuse you even more, you can also specify a join hint as a query hint, though the effect is not necessarily the same in both cases.  Be aware that providing these kinds of join hints implies a FORCE ORDER hint, which specifies that the join order indicated by the query syntax is preserved during query optimization (see Paul's answer below for further details).
SQL Server uses statistics on each table in the query to make an informed choice about what kind of physical operation to take with for each JOIN in the T-SQL query statement.
In this case, since [ExternalTable] is a view referenced through a linked server, SQL Server probably expects there to be 1 row in the table - i.e. it has no idea how many rows to expect.
You can either add a join hint to your query to force a merge join, or simply copy rows from [ExternalTable] into a local #temp table with a clustered index, then run the query against that.
The full syntax for the hash join would be:
LEFT OUTER HASH JOIN [ABC].[ExternalTable] s ON s.foot = t.foo .....

The version with the HASH in the join statement is allowing SQL Server to choose the join type, and in your particular case it's most likely choosing a LOOP join, which you could force with:
LEFT OUTER LOOP JOIN [ABC].[ExternalTable] s ON s.foot = t.foo .....

I typically would not recommend specifying the join type since most of the time SQL Server is very capable of choosing the most appropriate style of join operator.

1 - thanks Andriy for the wording here.


IO thread error : 1595 (Relay log write failure: could not queue event from master)

we need to keep on start io_thread whenever it stopped manually, hope its bug from percona

I have simply written shell and scheduled the same  for every 10mins to check if io_thread is not running , start slave io_thread for channel 'db12';.   It's working as of now


Timing issues for backups during recovery

You should first restore your PRIMARY with norecovery, then your SECONDARY with norecovery, and then all the log backups from 10:15 till tail of the log, the last one with recovery.


Can SQL Server Replication decrease publisher performance?

While it's theoretically possible for replication on the Publishing database to be affecting your application's write performance to that database (dependent on many factors) it's less likely that's your issue.
When you change servers, even if it's an exact replica server to the same specs (hardware and software) and same exact version of SQL Server, there's still going to be performance affecting differences such as statistics that will need to be calculated (and potentially change on recalculation) on the new server.
One way it's theoretically possible is if your Distributor lives on the Publisher's server, and is causing resource contention, but this is highly unlikely to be the case. Depending on which kind of replication you're using, there may be other applicable ways it can affect performance as well. For example, Snapshot Replication is more resource intensive than Transactional Replication when it's creates a snapshot (since it's recreating everything, not just the differentials, and locks the table the entire time it's snapshotting it), and therefore isn't meant to be used for as real-time of data synchronizations as Transactional Replication is meant for. If you were using Snapshot Replication and had it scheduled very frequently (e.g. every 5 minutes) then I could see that being more of a potential problem.
But most likely your issue is arising from somewhere else. I'd recommend comparing the old execution plan (if possible) to the new one, and comparing the Time and I/O statistics to try to trace your issue for your application's slow queries. You'll more likely find the root cause going that route.


Naming convention for automated table outputs (processed with Python)

I dont think there are any hard rules around this. It's really up to you to decide what convention suits you best.

The options available to you may be influenced by the database software you are using.

The 3 suggestions i have are:

Use a different schema i.e [Processed] and keep the table name the same.
Add a prefix so that all processed tables appear together.
Add a suffix to the table name. So that when you sort by name the original and processed appear together.

The 2nd and 3rd option make more sense if you spend more time working with tables as groups or with original and processed as a pair.


My question is, is it possible to just "rollback" the db to a few hours ago , using this huge LDF file

No, You are in simple recovery, once the transaction is committed it is not kept in the .ldf there are no point in time recovery options.

  and if not, what possible use does this LDF file serve if I can't restore from it?

You do not differentiate between the allocated and used space on .ldf file.  My best guess is that long ago it was in full recovery, and no one was doing t-log backups so it grew huge.  Then is was changed to simple recovery and it became mostly empty.  See related Why Does the Transaction Log Keep Growing or Run Out of Space?


How to prevent seek pagination from doing sequential scan over entire table

There may be more potential for improvement. But the obvious big issue is this:

SELECT
   r.resource_id,
   title,
   COALESCE(likes, 0) AS likes
FROM
   resources r
   LEFT JOIN
      resource_votes_aggregate a
      ON r.resource_id = a.resource_id
ORDER BY
   likes DESC,               -- refers to output column!
   resource_id DESC
LIMIT 5;

The manual:

  If an ORDER BY expression is a simple name that matches both an output
  column name and an input column name, ORDER BY will interpret it as
  the output column name. This is the opposite of the choice that GROUP BY
  will make in the same situation. This inconsistency is made to be
  compatible with the SQL standard.

So even if there is an applicable index including resource_votes_aggregate.likes, it cannot be used as the column is hidden behind the expression COALESCE(likes, 0) AS likes - reflected in the query plan as:

  Sort Key: (COALESCE(a.likes, '0'::bigint)) DESC

Fix with:

SELECT
   r.resource_id,
   r.title,
   COALESCE(a.likes, 0) AS likes
FROM
   resources r
   LEFT JOIN
      resource_votes_aggregate a
      ON r.resource_id = a.resource_id
ORDER BY
   a.likes DESC NULLS LAST,  -- refers to input column!
   r.resource_id DESC
LIMIT 5;

This refers to the input column now and qualifies for index usage. It's substantially cheaper to evaluate in any case, even without index, so you can't go wrong with this.

Since likes obviously can be NULL, be sure to add NULLS LAST. See:

Sort NULL values to the end of a table

Table-qualification for all input columns (like I added) is a good way to avoid confusion - not only for the problem at hand.

MATERIALIZED VIEW

The MV in your fiddle would be more efficient like this:

CREATE MATERIALIZED VIEW resource_votes_aggregate AS
SELECT resource_id
     ,(count(*) FILTER (WHERE vote))::int AS likes  -- faster
FROM   resource_votes
GROUP  BY resource_id;

The aggregate FILTER is typically faster.
count() returns bigint (8 bytes). Cast to integer (4 bytes).

This is the all-important index (missing in your fiddle):

CREATE INDEX ON resource_votes_aggregate (likes DESC, resource_id DESC);

With two integer columns, index tuples fit the minimum size of 8 bytes. (With bigint for likes, it would be 16 bytes (12 + 4 padding). See:

Is a composite index also good for queries on the first field?
Configuring PostgreSQL for read performance

Obviously, you also need an index on resources(resources_id). If that's the PK, then it's there.

If you use this query a lot, and preconditions for index-only scans are given, a multicolumn index on (resources_id) INCLUDE (title) may pay. See:

Can Postgres use an index-only scan for this query with joined tables?

Optimized query

If Postgres is not smart enough to give you the best query plan, this equivalent query will:

(
SELECT r.resource_id, r.title, a.likes -- COALESCE not needed
FROM   resource_votes_aggregate a
JOIN   resources r USING (resource_id)
ORDER  BY a.likes DESC                 -- NULLS LAST not needed
        , a.resource_id DESC           -- perfect for above index
LIMIT  5  -- logically redundant, but may help with best plan
)
UNION ALL
(
SELECT resource_id, title, 0 AS likes
FROM   resources r
WHERE  NOT EXISTS (  -- only rows without likes
   SELECT FROM resource_votes_aggregate a
   WHERE  a.resource_id = r.resource_id
   )
ORDER  BY r.resource_id DESC
LIMIT  5  -- logically redundant, but may help with best plan
)
LIMIT 5

The second SELECT will typically be never executed, if there are at least 5 resources with likes. See:

Way to try multiple SELECTs till a result is available?
Select first record if none match

Aside

You mentioned pagination. Be sure to avoid LIMIT / OFFSET for big tables. See:

What is the recommended way to join junction tables for efficient ordering/pagination?


How to Delete rows based on a combination of columns having a certain count


Mysql Cluster Exceeding MaxBufferedEpochs

You may have had a large transaction, a query retrieving many rows, pull back a lot of data and saturate the network connection for node 9.

On reconnect the API nodes are readonly so it has to be a query executed before this

NDB Binlog: ndb tables initially read only on reconnect


Most efficient way to execute DELETE and INSERT operations in the same Table

Using a merge statement is exactly what you need, but you can make it a little faster by using CTE's for the target and source tables.  Putting an index on dbo.Table1 (C11, C12) should make this pretty fast as well.

;WITH CTE_Target AS
    (
    SELECT T.C11, T.C12
    FROM dbo.Table1 AS T
    WHERE T.C11 = @Parameter
    )
, CTE_Source AS
    (
    SELECT T.C21
    FROM dbo.Table2 AS T
    )
MERGE INTO CTE_Target  AS T
USING CTE_Source  AS S ON S.C21 = T.C12 AND T.C11 = @Parameter
WHEN NOT MATCHED BY TARGET THEN
    INSERT (C11, C12)
    VALUES (@Parameter, S.C21)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;


Technically you can assign any existing/new filegroup to any new partition scheme (for other table).

However, you need to understand the existing partition function and scheme design, and more importantly the purpose of the existing partition. It could be partitioned for improved scalability and performance reason by having filegroup on different disks. Or it could be partitioned to archive older data on slower and cheaper disk. Or partitioned on a same/different disk for quicker subset access like during data loading process. So, the idea is to find out if the purpose of the new table partition inline with existing partition to determine if existing or new filegroup should be used.


SQL Server Debugger Permissions without Granting sysadmin to Devs

You could add a declare @idebug int variable to your stored procedures and then code around the important bits when you require relevant information.

Your stored procedure would then look a bit like this:

CREATE PROCEDURE [dbo].[uspDoSomething]
...
@iiDebug int = 0
...
AS
...
BEGIN
    /* debugging configuration */
    declare @debug int

    /* debug settings
    1 = turn on debug information
    2 = turn on all possible outputs
    4 = turn on transaction handling

    e.g.: Adding an @iDebug paramter of 6 will turn on transaction handling
    and turn on all possible output information

    e.g.: Adding an @iDebug value of 1 will turn on debugging information
    */

    set @debug = @iiDebug
    ....
    if @debug & 1 = 1  print 'Checking variables...'

                    /* If general output has been turned on print output*/
                    if @debug & 2 = 2
                        BEGIN
                            PRINT 'Debug comment here' + convert(varchar(100), @iRetVal) + 'Debug comment here' + convert(varchar(20),getdate())
                        end

                    close <cursor_name>
                    deallocate <cursor_name>

                    RETURN(@iRetVal)
    ...
    END
    ...
END

This is just an example of how it can be done.

You would then call the sproc with:

execute uspDoSomething @iiDebug = 3

...which would the provide basic (bitwise 1) and detailed (bitwise 2) information, depending on where you inserted the relevant code.

I had issues once while running a stored procedure that wasn't producing the right results and I had to debug the individual statements, so I just entered the various debugging levels in the stored procedure and when required ran the sproc with the relevant @iiDebug values depending on the level of information I required.

Examples of input values:

@iiDebug = 1 -- > Basic "where am I in the sproc" information
@iiDebug = 2 -- > Print of @nvSQL values
@iiDebug = 4 -- > Run individual execution of statements in BEGIN and COMMIT transactions

Examples as code (input variable @iiDebug is stored in @debug in the sproc code):

set @debug = @iiDebug
...
...
if @debug & 4 = 4
BEGIN
    begin tran mojo
END
if @debug & 2 = 2 then print @nvSQL

exec @iRetVal = sp_executesql @nvSQL
if @iRetVal <> 0
BEGIN
    /* If transactions have been turned on then rollback if failed */
    if @debug & 4 = 4
    BEGIN
        rollback tran mojo
    END

/* If transactions have been turned on then commit on success */
if @debug & 4 = 4
BEGIN
    commit tran mojo
END

These are just quick examples of how you can introduce debugging without having access to the SQL Server Debugger or the required privileges.

  Caution:
  It can be a bit of a performance hog and is better removed from production.


Slow SQL Server Database (approx 2TB) - high CPU due to index fragmentation?

Instead of looking at indexes, start looking at what queries are causing the high CPU use. Start with Michael J. Swart's DMV queries to find the top CPU-using queries. Look at what indexes the queries are using, whether they're doing things like implicit conversion, etc. But generally no, fragmentation by itself probably isn't doing this.


DELETE command not completing on 30,000,000 row table

Please look at the Architecture of InnoDB (picture from Percona CTO Vadim Tkachenko)

The rows you are deleting is being written into the undo logs. The file ibdata1 should be growing right now for the duration of the delete. According to mysqlperformanceblog.com's Reasons for run-away main Innodb Tablespace:

Lots of Transactional Changes
Very Long Transactions
Lagging Purge Thread

In your case, reason #1 would occupy one rollback segment along with some of the undo space since you are deleting rows. Those rows must sit in ibdata1 until the delete is finished. That space is logically discarded but the diskspace does not shrink back.

You need to kill that delete right now. Once you kill the delete query, it will rollback the deleted rows.

You do this instead:

CREATE TABLE tablename_new LIKE tablename;
INSERT INTO tablename_new SELECT * FROM tablename WHERE `columnname` NOT LIKE '-%';
RENAME TABLE
    tablename TO tablename_old,
    tablename_new TO tablename
;
DROP TABLE tablename_old;

You could have done this against the MyISAM version of the table first. Then, convert it to InnoDB.


SQL Server 2016 SP1 - Unable to execute R code

We ran into a number of problems when trying to configure R-Services, but we eventually got it working.  Please refer to the following link for the details.

Problem getting Sql Server 2016 R Services (In-Database) working


Calculating tsrange based on parent/child created dates


How does duplicity do the "incremental" backups?  By doing something like rsync?  Or using MySQL's binlogs?

One of the least invasive backups involves LVM.

About 1 minute of downtime, regardless of dataset size.
Only a few percent of disk space needed, regardless of dataset size.  (10% might be more than generous.)
And, yet, you always get a full backup.

How, you should ask?  Obviously some kind of sleight of hand.  Hint: a COW is involved.

But, alas, you have to plan ahead and set up a "Logical Volume" for this to work with.  And the OS has to provide such.  Here's how it works:

Set up the LV (one-time task)
when you want to take a dump, do the following steps:
stop mysqld
take a "snapshot" of the entire disk (perhaps 1 minute)
start mysqld
now you get the slow tedious task of copying the entire snapshot to somewhere else.

More info on the LV and the snapshot in the LV:

The snapshot is a "logical" copy of all the disk blocks.
It feels like a separate drive.
It takes very little extra space -- to start with
When either drive writes anything, the COW mooves into help.  COW = Copy On Write.  That is, the block that is being modified now needs to be come two blocks (and takes more real disk space).  One drive keeps the old copy; one gets the new.
During your copy to elsewhere, the backup LV drive still sees only "old" blocks.
During your copy, mysqld continues to modify blocks, oblivious to the COW grazing in the background.
Well, there will be a lot of I/O due to the copy.

Other notes...

Yes, it would be better to have exactly the same version.
You could install a newer version with the backup, but be sure to do mysql_upgrade.  You could even test an upgrade right there in the snapshot -- without copying to elsewhere.  (Note: the LV would need to be big enough for whatever changes in it.)
There is no concept of "incremental" (unless your copy is something like rsync).
Because I suggested actually stopping mysqld, there is no hard need for playing with %dirty%, etc.  After all, InnoDB can recover from a power failure; this is more graceful than that.


TempDB Best Practices For Shared Storage

There is no a definitive answer.
Anyway I suggest, if possible, to store tempdb files in different LUN. But it is worth only if tempdb is under pressure.

In my environment I use shared storage also for tempdb for performance reasons: my local disks are slower then shared storage via EMC.


How to drop all scheduler events in MySQL 5.6?

you can disable them via the following command:

set GLOBAL event_scheduler=0;

or dropping them one by one using the command:

DROP EVENT [IF EXISTS] event_name;


CLR Assemblies and SQL Server Availability Groups

When you create a mirror of the database the "trustworthy" flag is set to false by default. And you need to sync the SID of the dbo on each side (I don't understand how this works for the SA user. Need to study more.

To resolve the issue I failed the database over to the secondary node. Set Trustworthy to True. After that I attempted to run the Procedure and got a failure related to the SID not being registered (sorry forgot to capture the message). Since the database was owned by SA i decided to remove and readd the assemblies.

After removing and readding the assemblies i was able to then run the assembly on the node. At that point I failed everything back to the primary node where I was able to run the assembly. So I failed back and forth a few times and the assembly works on both sides. While this worked for us I'm still not entirely certain how SIDs for the SA user work on both sides.

To summarize:
- You have to failover to the secondary to set Trustworthy to True
- While the secondary was in read-write Drop and ReADD the assemblies on the second node (while acting as primary)
- That seemed to enable the assembly to run on both sides.

Thanks to Aaron and srutzky who guided me to a working solution. If anyone can better explain why what I did worked I'd be glad to know.


I need to update a Oracle procedure via a .sql script

Just save the ddl for the procedure. This can be done using sql-developer 'save to file'. Tell the client to start sql-developer, connect to the schema where the code should be written and have them run the script using 'start' or the equivalent for start '@' script_name
The sql script should be located in the directory defined in the preferences for Database>Worksheet>'select default path to look for scripts'

@your_script

or

start your_script

It works from the worksheet, very similar to sql*plus.


Flush/Clean MySql ib_logfile0 ib_logfile1

You could have InnoDB clean things up as follows:

signal mysqld to have InnoDB flush everything out of the logs on shutdown
shutdown mysqld
delete the logs
start mysqld

No crash recovery will happen because there will be nothing to recover.
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS} -h127.0.0.1 -P3306 --protocol=tcp"
SQL="SET GLOBAL innodb_fast_shutdown = 0"
mysql ${MYSQL_CONN} -ANe"${SQL}"
mysqladmin ${MYSQL_CONN} shutdown
cd /var/lib/mysql
rm -f ib_logfile*
service mysql start

If you want lingering data pages cleansed out of the Double Write Buffer, please see my old post Fill ibdata1 With Zeros/Nulls/etc Or Change A Table's Tablespace
GIVE IT A TRY !!!


Replacing the MS-Acccess frontend for accessing linked tables with SQL Server?

SQL Server Management Studio allows you to create multiple versions of queries where you can see the SQL text and results simultaneously.

You can use dotted-notation to refer to tables in separate databases on the same server like:

SELECT * FROM database.schema.table

If you need to query data from multiple servers, you can create linked servers in SQL Server that provide all the capabilities of an Access front-end.  This allows you to use 4-part dotted-notation like:

SELECT * FROM server.database.schema.table

For example, this code creates 2 Databases on the local SQL Server, with 2 tables that contain a single record each.  The SELECT at the end joins data from the 2 tables into a single output:

CREATE DATABASE Test1 ON PRIMARY
    (NAME = 'Test1_Data'
        , FILENAME = 'C:\Temp\Test1_data.mdf'
        , SIZE = 10MB
        , MAXSIZE = UNLIMITED
        , FILEGROWTH = 10MB
    )
LOG ON
    (NAME = 'Test1_Log'
        , FILENAME = 'C:\Temp\Test1_log.mdf'
        , SIZE = 10MB
        , MAXSIZE = UNLIMITED
        , FILEGROWTH = 10MB
);

CREATE DATABASE Test2 ON PRIMARY
    (NAME = 'Test2_Data'
        , FILENAME = 'C:\Temp\Test2_data.mdf'
        , SIZE = 10MB
        , MAXSIZE = UNLIMITED
        , FILEGROWTH = 10MB
    )
LOG ON
    (NAME = 'Test2_Log'
        , FILENAME = 'C:\Temp\Test2_log.mdf'
        , SIZE = 10MB
        , MAXSIZE = UNLIMITED
        , FILEGROWTH = 10MB
);
GO

USE Test1;
GO
CREATE TABLE TestTable1
(
    ID INT NOT NULL PRIMARY KEY CONSTRAINT PK_TestTable1 IDENTITY(1,1)
    , TestText nvarchar(255)
);

INSERT INTO TestTable1 (TestText) VALUES ('This is table 1 in database 1');

USE Test2;
GO
CREATE TABLE TestTable2
(
    ID INT NOT NULL PRIMARY KEY CONSTRAINT PK_TestTable2 IDENTITY(1,1)
    , TestText nvarchar(255)
);

INSERT INTO TestTable2 (TestText) VALUES ('This is table 2 in database 2');

SELECT * FROM Test1.dbo.TestTable1
UNION ALL
SELECT * FROM Test2.dbo.TestTable2;

The output is:

ID  TestText
1   This is table 1 in database 1
1   This is table 2 in database 2


How to make middle and suffix matching using full text search?

The "Full" in Full Text Search means match full words, not parts of words

You should use RegExp or LIKE or tri-gram matching instead

You can mimic suffix matching by reversing words in your index and also reversing your queries, but this takes more space:

select
reverse('brown fox')::tsvector @@ (reverse('rown') || ':*')::tsquery --true


Multi query with group by , join them in different columns

For MySQL 8+:

WITH RECURSIVE
cte1 AS ( SELECT customername, ROW_NUMBER() OVER () rn
          FROM tablegroup
          GROUP BY customername
        ),
cte2 AS ( SELECT amazonnumber, ROW_NUMBER() OVER () rn
          FROM tablegroup
          GROUP BY amazonnumber
        ),
cte3 AS ( SELECT 1 num
          UNION ALL
          SELECT num+1
          FROM cte3
          WHERE num < ( SELECT GREATEST(cnt1, cnt2)
                        FROM ( SELECT COUNT(*) cnt1
                               FROM cte1
                             ),
                             ( SELECT COUNT(*) cnt2
                               FROM cte2
                             )
                      )
        )
SELECT cte1.customername, cte2.amazonnumber
FROM cte3
LEFT JOIN cte1 ON cte3.num = cte1.rn
LEFT JOIN cte2 ON cte3.num = cte2.rn


Looks like the database is not in the pool.  Go to the Elastic Pool in the portal and click on Configure Pool, and Add Databases.


SQL Server Reporting Services 2017: Multiple Instances

From my research, this is currently not implemented in SSRS 2017. I was not able to find any information in an official Deprecated Features document.

Comment from user 'Matt'
Comment from user 'Sai_Kumar'
Twitter-Post from Christopher Finlan, Senior Program Manager, Power BI Report Server & SQL Server Reporting Services at Microsoft.

I found two votes to bring this functionality back to the product:

Support Power BI Report Server multiple instances on a single server
Restore multiple instance support for Reporting services in SQL 2017

Update (May 2019): The feature will not be reimplemented

  AdminChris Finlan (Program Manager, Microsoft Power BI) replied:

  Hi all, We aren’t planning to re-implement multi-instance support as
  we had prior to SSRS 2017. We recommend using virtualization as an
  alternative, and we’re also looking at possibly adding container
  support in the future.


Filtered index throwing error - UPDATE failed because the following SET options have incorrect settings: ANSI_WARNINGS

Possibly because (source):

  ANSI_WARNINGS is not honored when passing parameters in a stored procedure

In any case, you can work around it in two ways:

Stop setting ANSI_WARNINGS to OFF in proc1
Turn ANSI_WARNINGS ON before calling proc2


how does SQL Server know to lock view objects?

From Conor Cunningham, the ultimate source of anything engine- or optimizer-related:

  We track things during compile to check at runtime.  We do not parse
  things at execution for this purpose.

  Note: the internals of what we do from one release to another are not
  guaranteed.  This is beneath the officially supported surface area.

My belief is that the binary version of the execution plan (not the one that is readable and exposed to us via XML, which is only a subset of the binary version) must contain some pointer to the view(s) referenced in the original query text (and this was alluded to in the SO question). It obviously isn't parsing the query text every time. Conor implies as much above, but is careful to not reveal any details about where or how this is stored, since this could potentially change from release to release or even with a service pack or cumulative update. He probably also doesn't want to encourage any detective work. :-)


Audit Plugin on Ubuntu: CONTROL [main] Failed global initialization: FileNotOpen: Failed to open "/path" : exit status 1

You can give it a try using the following changes (without the trailing /):

path: /opt/mongouser/store/mongodb-audit

instead of:

path: /opt/mongouser/store/mongodb-audit/


comparison operator in count() is not working

I have a requirement where I want to see if at least 1 record exist return 1 or else 0

You can do that with following query in MS-SQL (db-fiddle)

SELECT CASE WHEN count (*) >= 1 THEN 1 ELSE 0 END
FROM new_table_name

Not sure how mySQL treats this SELECT 15 > 14 FROM Duel, but in MS-SQL it's not complete statement (even columns exists with the name 15 and 14), operators (=, <>, !=, >, <, LIKE, IN, NOT IN) usually used in WHERE and ON clauses. When it used in SELECT clause it must complete the Boolean logic (CASE, IIF).


At the risk of being way too general in my answer, I will say that you should run an index maintenance process regularly.  However, your index maintenance process should only rebuild/reorganize the indexes that specifically require it.

This presents the question:  when does an index require to be rebuilt or reorganized?  Rolando touched on this nicely.  Again, I risk being extremely broad.  An index requires maintenance when the fragmentation level adversely affects performance.  This level of fragmentation could vary based on the size and composition of the index.

Speaking for SQL Server, I tend to choose a index size and index fragmentation level at which point I begin performing index maintenance.  If an index contains less than 100 pages, I will perform no maintenance.

If an index is between 10% and 30% fragmented, I will REORGANIZE the index and UPDATE the statistics.  If an index is over 30% fragmented, I will REBUILD the index - with no UPDATE STATISTICS, as this is taken care of by the REBUILD. Remember though that a rebuild only updates the statistics object directly associated with the index. Other column statistics will need to be maintained separately.

This answer is really just a long way to say:  Yes, you should do routine index maintenance, but only on the indexes that need it.


Unless I completely destroy the question in my head this is what you meant by two rows out of each one:

create table #TableOne (
    TxnId int,
    FromUserId int,
    ToUserId int,
    Price decimal(5,2)) ;

insert into #TableOne values
    (1, 1, 2, 100.01),
    (2, 1, 2, 100.01),
    (3, 2, 1, 100.01),
    (4, 2, 1, 100.01),
    (5, 3, 4, 100.01),
    (6, 4, 3, 100.01),
    (7, 4, 3, 100.01) ;

select
    FromUserId AS FocalUserId,
    TxnId,
    FromUserId,
    ToUserId,
    Price
from #TableOne
union all
select
    ToUserId AS FocalUserId,
    TxnId,
    FromUserId,
    ToUserId,
    Price
from #TableOne ;

Alternatively, using APPLY (lateral join):

SELECT
    CA.FocalUserId,
    TON.TxnId,
    TON.FromUserId,
    TON.ToUserId,
    TON.Price
FROM #TableOne AS TON
CROSS APPLY
(
    VALUES
        (TON.FromUserId),
        (TON.ToUserId)
) AS CA (FocalUserId);


Realizing that this is a complete redesign of your intent but why not script out the change for each table?  Ie create a new table, copy the data over, re-create all indexes, security etc.

The easiest way to do this would be to use the designer, make your changes, don't hit save, but instead right click on the background of the designer and request "generate change script".  Copy that out and you can then make changes to it as needed.  That way you only have 15 scripts to run.

I did this recently for a particularly large table (~700gb), using the script to create the new table and using an SSIS package to copy the data.  Then I ran the remainder of the script to generate the indexes etc.  This can be surprisingly fast depending on your needs.

Now if you don't want to do it that way you do have a couple of other options.

You could put GO statements and use PRINT to print a status between each ALTER.  Then when you run your script you can tell where it failed.  It will only roll back the last statement you ran and you can run from that point forward.

Last option I can think of is to remote to the server itself (not considered a best practice) and run the script there.  That way even if you lose your connection to the server the script will continue to run regardless.


How can I restore ##MS_SSISServerCleanupJobLogin##?

I decided to experiment with creating the user with a random password, and everything worked.

CREATE LOGIN [##MS_SSISServerCleanupJobLogin##] WITH PASSWORD=N'blahblahblah', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE SSISDB
GO
ALTER USER [##MS_SSISServerCleanupJobUser##] with LOGIN = [##MS_SSISServerCleanupJobLogin##]


Fetch first 9 records and make one more record which will be as "Others" in MySQL

Suggestion 1

How about (untested)

SELECT designation, count(*)
  FROM designations
 GROUP BY designation ORDER BY COUNT(*) DESC LIMIT 9

UNION SELECT "Others" designation, COUNT(*)
        FROM designations
       WHERE designation NOT IN (
               SELECT designation
               FROM designations
               GROUP BY designation
               ORDER BY COUNT(*) DESC
               LIMIT 9 )

Suggestion 2

This one is tested. It uses MySQL-specific variables. It's efficient.

SET @n=0;

SELECT IF(row_number<10, designation,'Others') name,
       sum(c) AS occurances
FROM
  (SELECT designation, c, @n:=@n+1 row_number
   FROM
     (SELECT designation,
             count(*) c
      FROM designations
      GROUP BY designation
      ORDER BY count(*) DESC) t1) t2
GROUP BY IF(row_number<10,row_number,10);


what's difference between sql_log_bin and log_bin in MySQL？

log_bin is a global variable that is set at runtime. It determines if binary logging should take place, and (if set) the basename of the binary logs. It cannot be changed without restarting the database.

http://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#option_mysqld_log-bin

sql_log_bin is a global OR session variable that can be set after the server is running. It's main use is as a SESSION variable to prevent statements from the current session being written to the binary log. Use as a GLOBAL variable is not recomended.

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_log_bin


Think I have the right indexes and query, but MySQL Query is still slow

How about FORCE INDEX (import_date) to FORCE INDEX (multi_column)?


How I can check whether 2 databases despite being in different postgresql servers of versions are the same?

You cannot do that in a fast way.
The best you can do is to dump both databases using pg_dump from v11 and compare the files with diff.
If you want to do that just to ensure that the migration was successful, don't. If you don't trust PostgreSQL's upgrade process, why should you trust pg_dump?


Why do you wish to avoid outer joins? Using these does have a specific meaning which is "return me all the values from the driving table regardless of whether there is a matching row in the outer joined table." This gives a clear statement to future developers who maintain your code which would be lost if using a dummy value.

There are some things to bear in mind however:

(Note - discussion is based on Oracle; other major RDBMSes are likely to be similar but may have different/better optimizations which affect how outer joins are executed).

From a performance perspective, outer joining restricts the options available to the query optimizer. When constructing the query plan, the optimizer determines which is the smaller rowset and uses that as the driving table. When you specify an outer join, then the table you're outer joining from will become the driving rowsource.

Using the classic emp and dept tables supplied with Oracle, we can see this in action:

explain plan for
  select *
  from   scott.emp e, scott.dept d
  where  e.deptno = d.deptno;

select * from table(dbms_xplan.display(null, null, 'BASIC +ROWS'));

--------------------------------------------------------
| Id  | Operation                    | Name    | Rows  |
--------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |
|   1 |  MERGE JOIN                  |         |    14 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |
|   4 |   SORT JOIN                  |         |    14 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |
--------------------------------------------------------

explain plan for
  select *
  from   scott.emp e, scott.dept d
  where  e.deptno = d.deptno (+);

select * from table(dbms_xplan.display(null, null, 'BASIC +ROWS'));

-------------------------------------------
| Id  | Operation          | Name | Rows  |
-------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |
|   1 |  HASH JOIN OUTER   |      |    14 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |
|   3 |   TABLE ACCESS FULL| DEPT |     4 |
-------------------------------------------

When we fully join the two tables, Oracle chooses the smaller of the two (dept) to be the driving table. However, when we outer join to it, the order is switched. This is because it knows we need to return all the rows from emp no matter what.

As query plans are generally most efficient when starting with the smallest rowset, working up to the biggest, this could cause issues with multi-table joins outer joining from a "large" table to a "small" one. In some cases this can cause queries to be very slow. In an OLTP application most queries should only be returning a handful of rows though, so all rowsets should be small (even if coming from a large table), meaning you shouldn't encounter this problem very often in a well designed (OLTP) system.

To convert your queries to become full joins, you'll need to decide what value to use instead of null. This is a tricky issue as you're converting something which means I don't know into whatever value you assign "null" to be. This can cause confusion for future developers down the line and introduce performance issues of it's own (e.g. single column indexes don't include the null rows). See this for some discussion around null.

Personally I wouldn't attempt to create dummy values to represent null. If you find that you have performance issues related to outer joins then I would start by looking at the affected queries and tuning those individually. If this issue is affecting a lot of queries I may start looking at using dummy values, but only as a last resort.

Of course, you should always ask whether you genuinely mean to use an outer join or if you're using it "just in case"!


Share single index for multiple linearly correlated columns

No, you cannot use an index on one column for searches on another column. I second Jasens comment that you should consider doing away with the generated integer and using the timestamp instead.


MySql replication failed with error 1236 data position > file size with relay-bin file not increasing

To avoid the problem in the future, turn on sync_binlog.

What is happening is that the replication stream had not yet gotten to the Slave when the Master shutdown.  The offset into the binlog had been bumped up, but the data was not on the Slave.

After coming up, move to the next binlog and set the position to 0 (or 4, either one works).


I suggest an SQL function:

CREATE OR REPLACE FUNCTION foo(_date date)
  RETURNS TABLE (
   name       text  -- types have to match your actual types!
 , keyword_id int
 , project_id int
 , the_date   date
 , today      int
 , yesterday  int
 , week       int
 , month      int) AS
$func$

SELECT k.name, f.keyword_id, f.project_id, _date -- AS the_date -- col alias irrelevant
     , f.t AS today, f.y As yesterday, f.w AS week, f.m AS month
FROM   crosstab(

    -- crosstab function from previous question here
    -- http://dba.stackexchange.com/a/71266/3684

   ) f (rn int, keyword_id int, project_id int
       , t int, y int, w int, m int)
JOIN   keyword k USING (keyword_id);

$func$  LANGUAGE sql;

Call:

SELECT * FROM foo('2014-07-07');

Replace all occurrences of now()::date with your input parameter named _date.
 In Postgres 9.1 or older use the positional parameter $1 in SQL functions (which can be used in any version).

More code examples:

Return rows from a PL/pgSQL function
Pass In “WHERE” parameters to PostgreSQL View?

Tricky detail

The crosstab() function takes query strings as parameters. Function parameters are not visible inside crosstab(). So you need to pass in the date values as string literals!
I suggest the function format() for convenience. For example, the second parameter becomes:

,format('VALUES(%L::date), (%L), (%L), (%L)'
        , $1, $1 - 1, $1 - 7, ($1 - interval '1 month')::date
       )

instead of:

,$$
 VALUES
       (now()::date)
     , (now()::date - 1)
     , (now()::date - 7)
     , ((now() - interval '1 month')::date)
 $$

Complete code

SQL function

CREATE OR REPLACE FUNCTION foo_sql(_date date)
  RETURNS TABLE (
   name       text
 , keyword_id int
 , project_id int
 , the_date   date
 , today      int
 , yesterday  int
 , week       int
 , month      int) AS
$func$
BEGIN

SELECT k.name, f.keyword_id, f.project_id, _date
     , f.t, f.y, f.w, f.m
FROM   crosstab (
   $$
   SELECT rn
        , pk.keyword_id
        , pk.project_id
        , d.created_at
        , COALESCE(pr.pos, 0)
   FROM  (SELECT *, row_number() OVER () AS rn FROM project_keyword) pk
   CROSS  JOIN (
       $$
    || format('VALUES(%L::date), (%L), (%L), (%L)'
              , $1, $1 - 1, $1 - 7, ($1 - interval '1 month')::date)
   || $$
     ) d(created_at)
   LEFT JOIN (
      SELECT keyword_id
           , project_id
           , created_at::date AS created_at
           , min(position) AS pos
      FROM   project_report
      GROUP  BY keyword_id, project_id, created_at::date
      ) pr USING (keyword_id, project_id, created_at)
   ORDER  BY pk.rn, d.created_at
   $$

  ,format('VALUES(%L::date), (%L), (%L), (%L)'
              , $1, $1 - 1, $1 - 7, ($1 - interval '1 month')::date)
   ) f (rn int, keyword_id int, project_id int
       , t int, y int, w int, m int)
JOIN   keyword k USING (keyword_id);

END
$func$  LANGUAGE sql;

PL/pgSQL function

Shorter, reusing the VALUES expression. Probably a bit faster, too.

CREATE OR REPLACE FUNCTION foo_plpgsql(_date date)
  RETURNS TABLE (name text, keyword_id int, project_id int, the_date date
               , today int, yesterday int, week int, month int) AS
$func$
DECLARE
   _dates text := format('VALUES(%L::date), (%L), (%L), (%L)'
                        , $1, $1 - 1, $1 - 7, ($1 - interval '1 month')::date);
BEGIN

SELECT k.name, f.keyword_id, f.project_id, _date, f.t, f.y, f.w, f.m
FROM   crosstab (
  'SELECT rn
        , pk.keyword_id
        , pk.project_id
        , d.created_at
        , COALESCE(pr.pos, 0)
   FROM  (SELECT *, row_number() OVER () AS rn FROM project_keyword) pk
   CROSS  JOIN (
       ' || _dates || '
     ) d(created_at)
   LEFT JOIN (
      SELECT keyword_id
           , project_id
           , created_at::date AS created_at
           , min(position) AS pos
      FROM   project_report
      GROUP  BY keyword_id, project_id, created_at::date
      ) pr USING (keyword_id, project_id, created_at)
   ORDER  BY pk.rn, d.created_at'

  ,_dates
   ) f (rn int, keyword_id int, project_id int, t int, y int, w int, m int)
JOIN   keyword k USING (keyword_id);

END
$func$  LANGUAGE plpgsql;

CTE

For completeness, the "same" without persisting a function, with CTEs:

WITH d(day)   AS (SELECT '2014-07-07'::date)  -- provide your date here
,    v(dates) AS (
   SELECT format('VALUES(%L::date), (%L), (%L), (%L)'
                 , day, day - 1, day - 7
                 ,(day - interval '1 month')::date)
   FROM d
   )
SELECT k.name, f.keyword_id, f.project_id, d.day AS the_date
     , f.t AS today, f.y As yesterday, f.w AS week, f.m AS month
FROM   crosstab (
  'SELECT rn
        , pk.keyword_id
        , pk.project_id
        , d.created_at
        , COALESCE(pr.pos, 0) AS pos
   FROM  (SELECT *, row_number() OVER () AS rn FROM project_keyword) pk
   CROSS  JOIN (
       ' || (SELECT dates FROM v) || '
     ) d(created_at)
   LEFT JOIN (
      SELECT keyword_id
           , project_id
           , created_at::date AS created_at
           , min(position) AS pos
      FROM   project_report
      GROUP  BY keyword_id, project_id, created_at::date
      ) pr USING (keyword_id, project_id, created_at)
   ORDER  BY pk.rn, d.created_at'

  ,(SELECT dates FROM v)
   ) f (rn int, keyword_id int, project_id int
       , t int, y int, w int, m int)
JOIN   keyword k USING (keyword_id);


Is SELECT FOR ... UPDATE *necessary* to DELETE or UPDATE multiple rows without deadlock?

Your assessment is spot on, and there is no better way of doing it.

As an alternative, you could do without the sort, but delete in smaller batches, each in its own transaction. That is still susceptible to deadlock, but the likelihood is smaller, and re-running the transaction doesn't hurt quite as much.


Peter's quite right about the problem, of course. However, this entire function is unnecessarily slow and complex - that repeated string concatenation will be horrible for performance, and PL/PgSQL loops are best avoided when you can anyway.

You can do the same job with an ordinary SQL function using generate_subscripts:

CREATE OR REPLACE FUNCTION intarr_to_hex_string(integer[]) RETURNS text AS $$
SELECT
  string_agg(
    lpad(to_hex($1[x]),2,'0'),
    ''
    order by x
  )
FROM generate_subscripts($1,1) x;
$$ LANGUAGE sql;

The above version expects an integer array, but you can accept text arrays with an overload:

CREATE OR REPLACE FUNCTION intarr_to_hex_string(text[]) RETURNS text AS $$
SELECT intarr_to_hex_string($1::integer[])
$$ LANGUAGE sql;

Note that no sanity check is done to make sure the hex string is two digits or less. You should really add a check where you use a CASE statement to execute a PL/PgSQL function that raises an error if there's bad input. Here's a complete example:

CREATE OR REPLACE FUNCTION raise_exception(text)
  RETURNS void LANGUAGE plpgsql AS
$BODY$
BEGIN
   RAISE EXCEPTION '%', $1;
END;
$BODY$;

CREATE OR REPLACE FUNCTION intarr_to_hex_string(integer[]) RETURNS text AS $$
SELECT
  string_agg(lpad(
    CASE WHEN $1[x] < 256 THEN
      to_hex($1[x])
    ELSE
      raise_exception('Argument to intarr_to_hex_string contained value '||coalesce($1[x]::text,'NULL')||', expected all values in i[] to be 0 <= i < 256. Full argument was '||quote_literal($1)||'.')::text
    END,
    2, '0'),
  ''
  order by x)
FROM generate_subscripts($1,1) x;
$$ LANGUAGE sql;


Question on Oracle OEM version and plugin version upgrade

Plugins are deployed on the OMS and the agents as well. First you should upgrade the plugin on the OMS, then on the agents.
You don't need to upgrade between versions sequentially, you can upgrade to the highest available.
Yes, there are. Currently 12.1.0.5 is the latest version, and there are quarterly PSUs as well. Check the below MOS note:

Oracle Recommended Patches (PSU) for Enterprise Manager Base Platform (All Releases) (Doc ID 822485.1)

There is no PSU released for 12.1.0.5 yet.


Several Questions about SGAM & GAM & IAM & PFS in SQL Server

How SQL Server knows that extents have free space ?

SQL Server tracks free space on the page, in heap tables as well as in LOB pages using PSF page internally.
Also, you can use sys.dm_db_file_space_usage DMV to find out total_page_count, allocated_extent_page_count as well as unallocated_extent_page_count within a database.

Page Free Space (PFS) pages record the allocation status of each page, whether an individual page has been allocated, and the amount of free space on each page. The PFS has one byte for each page, recording whether the page is allocated, and if so, whether it is empty, 1 to 50 percent full, 51 to 80 percent full, 81 to 95 percent full, or 96 to 100 percent full.
A PFS page is the first page after the file header page in a data file (page number 1). This is followed by a GAM page (page number 2), and then an SGAM page (page 3). There is a PFS page approximately 8,000 pages in size after the first PFS page. There is another GAM page 64,000 extents after the first GAM page on page 2, and another SGAM page 64,000 extents after the first SGAM page on page 3. The following illustration shows the sequence of pages used by the Database Engine to allocate and manage extents.

Click here to enlarge - output using DBCC PAGE

Refer to : Inside The Storage Engine: GAM, SGAM, PFS and other allocation maps

File header & SGAM & GAM & IAM pages are among the first pages within a file. What data-structure actually points to them ?

I do not get this question. The smallest unit of storage is a page. Eight 8K Pages form an extent. Below is what comprises of a data page:

I would highly recommend to read SQLSkills.com blog wherein Paul and his team blogs a lot about SQL Server internals.


if database is not shrinked does it affect backup size?

The backup process does not back up empty pages, only pages with data, so typically, the backup file will be a lot smaller.

One important thing to note: when using compression, the initial size needed on the destination drive will be the uncompressed size; the backup will only be fully compressed at the end of the process.


What is the limit on the number of INSERT statements you can paste into SSMS and run?

bcp is not configured on the environment

BCP is an exe that comes with SQL Server. The default path is C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe

You can refer to my BCP and BULK INSERT script here.

  I am going to export could have up to 2,000,000 insert statements.

SSMS wont be able to support this. You have to use SQLCMD.

Highly suggest you to use BCP or SSIS to do the task.


Subscriber database to be in sync using Distributor

Why don't you setup your environment to replicate from DR1 to DR2 instead of from D2 to DR2. That way you don't have to re-initialize or -synch anything on failover. You just need to make sure that you fail over both D1 to DR1 and D2 to DR2 together.

If that is not an option, you could also look into clustering. if D1 and DR1 build one cluster C1 and D2 & DR2 make up cluster C2 you can setup replication from C1 to C2. In that setup a failover of either site won't affect replication.


Try this...

select owner,table_name,grantable from dba_tab_privs where grantee = 'SCOTT' and privilege = 'SELECT';

Change SCOTT to the user you are interested in.


Well, with the help of Rick James' remark I managed to find the answer.
I installed MariaDB 10.2.5 (10.2.2 or higher required) and imported all MySQL data files by just copying the files in the Data dir to the MariaDB Data dir, and running mysql_update on the MariaDB database.

WITH RECURSIVE tree (parent_id) AS (
    SELECT parent_id FROM link_parent_child WHERE child_id = $ChildStartID$

    UNION ALL

    SELECT c.parent_id FROM link_parent_child lpc
    JOIN tree t ON t.parent_id = lpc.child_id
)
SELECT parent_id FROM tree

It really is that simpel :|

What are CTEs


As per BOL rather than KB, use ALTER TABLE

ALTER TABLE Mytable DROP <defaultname>;

The name of the default could be user supplied or system generated. To find it and generate the SQL needed:

SELECT
    OBJECT_NAME(c.object_id), dc.name,
    'ALTER TABLE ' + QUOTENAME(OBJECT_NAME(c.object_id)) + ' DROP ' + QUOTENAME(dc.name);
FROM
    sys.columns c
    JOIN
    sys.default_constrainst dc ON c.columnid = dc.parent_column_id AND c.object_id = dc.parent_object_id
WHERE
    c.name = 'UW_ZeroDefault';


From the 5.5 manual:

  Setting this option causes the log_bin system variable to be set to ON (or 1), and not to the base name. This is a known issue; see Bug #19614 for more information.

Newer versions have made changes in this area, but there continues to be a difference between how the VARIABLE log_bin (in my.cnf) and the commandline --log-bin (when invoking mysqld).

From https://dev.mysql.com/doc/refman/5.5/en/replication-features-triggers.html :

  With statement-based replication, triggers executed on the master also execute on the slave. With row-based replication, triggers executed on the master do not execute on the slave. Instead, the row changes on the master resulting from trigger execution are replicated and applied on the slave.


How can I find the difference between queries that return multiple rows?

Try to use the following:

select * from website a where not exists (select null from page where site_id=a.id and deleted=0)

Probably this is what you're asking for


Redshift postgresql time series data index DISTKEY and SORTKEY

The choice of distribution key and sort key are driven by the queries you intend to issue on the table, for the choice made defines what queries can be issued in a timely manner on the table, and you need those queries to be the business queries you need to run.  You cannot chose either of these keys simply by taking a table on its own, without any consideration for how the table will be queried.
As an aside, I would strongly advise you, if your example value column here is representative, to convert your value column to an integer, by multiplying its values by 1000, and converting it back to a float when you need to use it.
This is because the sorting value for floats is the integer part of the number, and so all those example numbers have a sorting value of 1; which is to say, if most of your values are like that, min-max culling (the zone map) will have basically no effect, and that will make your queries behave much as they did on MS SQL - the entire column will be scanned - only the and expensive brute power of the hardware in the cluster will provide an improvement in performance.
Ah, and, finally, if you insert your data out of order, and you have six billion columns, you'd going to have a multi-day VACUUM after you've finished inserting your data.
Redshift is a knowledge-intensive database, which can handle Big Data in a timely manner when and only when it is operated correctly, and although I may be wrong, I think the people having you perform this work do not understand what they are getting into.


SQL Server 2008 - Two FULL backups in one day

Would be there a problem with POINT IN TIME recovery? Does his backup broke our chain?

No, Full Backup has no affect on transaction log backup chain. You would still be able to restore full backup and all log backups in sequence. So let us say you have following

Full backup 1 taken by you
Log backup 1
log backup 2
Vendor Full backup 1
Log backup 3
Log backup 4
Vendor Full backup 2
Log backup 5
In case of disaster you would be able to restore Full backup 1 and then log backups 1,2,3,4,5 in sequence.

Having said that you can ask vendor to take copy_only full backups just to be on safer side and simplify things

Hope this is what you are asking.


I think best of it is still to came , but i feel comfortable with **full join **.

SELECT
a1.ram,
a1.product_id,
a1.date_ad as fromDate,
a2.date_ad as toDate
FROM
 (
   select
    ram,date_ad, cvd.product_id
from challan_from_vendor cv left join challan_from_vendor_detail cvd on cv.id = cvd.challan_from_id
group by 1,date_ad, cvd.product_id
) as a1
FULL JOIN
(
 select
    ram, date_ad, cvd.product_id
from challan_to_vendor cv left join challan_to_vendor_detail cvd on cv.id = cvd.challan_to_id
group by 1, date_ad,cvd.product_id
) as a2 ON (a1.product_id=a2.product_id)
ORDER BY 1;


Database table data with different set of values

I think you're on the right track.

I presume questions.type (NB type might be a reserved word and cause problems) will have the check constraint check type in ('multiple_choice','free_text') or something similar.

Then all you would need to do is rename your proposed UserAnswers to UserMultipleChoiceAnswers and drop the column answer,
and create another table, UserFreeTextAnswers with columns id, question_id, answer, user_id.


asynchronous mirroring
It means, a transaction is committed on the Principal server before being copied over to the Mirror Server.

Whereas in Synchronous Mode Principal Server send the logs to the Mirror server and waits for the signal from the Mirror Server, once the transactions has been applied to the Mirror Server only then it commits the transaction on the Principal Server. Also the transaction Safety needs to be set to full and you to have a Witness Server.

Asynchronous mirroring does not effect the performance of Principal Server whereas other operating modes in Database Mirroring will have effect on the performance of the principal server as in Synchronous Mode both the principal and Mirror server are in the same state and you can have a long Redo Queue (Pending Transactions to be rolled-Forward on Mirror Server).

Note: Asynchronous mirroring is only available in the Enterprise Edition.

Reporting with snapshot script
Have you considered SNAP SHOT Replication for the reporting purposes ???


Creating Clustered Index uses all my tempdb space. Why?

Although you had SORT_IN_TEMPDB = OFF, that is not the only thing that uses tempdb.  You also had set ONLINE = ON.

Since the job ran 50 minutes before you had trouble, it may be that you had enough activity in that single transaction to fill tempdb with row versioning data.  This is described here:

http://technet.microsoft.com/en-us/library/ms179542.aspx

In part it says: "Online index operations use row versioning to isolate the index operation from the effects of modifications made by other transactions. ... Concurrent user update and delete operations during online index operations require space for version records in tempdb."

EDIT: Actually a 200 MB tempdb seems quite small for a database with a 44 GB table.


One table to save all records vs create one table each day

Avoiding separate tables by date would be best. Besides the benefits you mentioned, performance will likely be better too.

If each table has a row for the same ID and Timestamp (e.g. a device that reports all 5 readings at the same time), you could have a single table with ID, Timestamp, pressure, flow, volume, temperature, and distance.

You might also consider using columnstore and partitioning by date for maximum performance and manageability (respectively).


How to SELECT "closest" rows from another table?

This would achieve it:

SELECT m1.id1, m2.*
FROM   map1 m1
CROSS  JOIN LATERAL (
   SELECT ST_HausdorffDistance(m1.g1, m2.g2) AS h_dist, m2.id2, m2.g2
   FROM   map2 m2
   WHERE  ST_HausdorffDistance(m1.g1, m2.g2) < 2
   ORDER  BY 1, 2
   LIMIT  2
   ) m2;

Returns 1 or 2 rows for every row in map1, extended with the top 2 corresponding row(s) in map2 (as defined by minimum Hausdorff distance) and the said Hausdorff distance between them. If there is no row with Hausdorff distance < 2 in map2, no row is returned.

Key element is the LATERAL subquery. There are variants of this query, depending on exact (missing) requirements. Related:

OFFSET and LIMIT on complex query
How to make DISTINCT ON faster in PostgreSQL?
How to speed up querying last values in a time series?
Optimise a LATERAL JOIN query on a big table

I wouldn't know of any way to use an index here. So this is going to be an expensive query.


To use a cursor or something else in my while loop?

Ideally, you should change the other stored procedure to work on a set rather than take individual values (or create a new stored procedure that works on the set). If you show the actual logic in the procedure and the schema of the affected table(s), rather than just describe the high level process as a word problem, you can get some ideas about that. One way to avoid calling the procedure multiple times would be to pass the values for the different rows as a single set using a table-valued parameter (but again this requires re-writing the procedure or creating a new one).

In the meantime, if you absolutely must use a loop, you must also be willing to accept the performance consequences. One way to offset those - in cases where you really are using a cursor as a read-only, single pass through a set - is to use the following keywords when declaring your cursor:

DECLARE c CURSOR LOCAL FAST_FORWARD
FOR SELECT ...

More info here:

http://www.sqlperformance.com/2012/09/t-sql-queries/cursor-options


How can I automatically have a node become primary in an Availability Group?

You need to set that in the AG ROLE properties as below :

Windows server 2012


Yes, 64KB is still the current recommendation as it has to do with partition alignment.  To quote Jimmy May from the the SQL CAT team:

  Partition alignment remains a best practice for all versions of
  Windows Server as well as SQL Server, including SQL Server 2012 & SQL
  Server 2014.  No exceptions.  Period.

As part of the Best Practice guide referenced in that article, the following line is quite explicit:

  The file allocation unit size (cluster size) recommended for SQL
  Server is 64 KB

As for a SSD vendor's perspective, I point you to Argenis Fernandez's blog post about this very topic.  Pure even recommends 64KB for NTFS block size, so I think it's safe to assume this is a pretty universal truth.


Does clustering provide disaster recovery in MS SQL Server?

By itself, no: clustering relies on shared storage. A failover cluster with shared storage looks like this:

All 4 of the components - the 2 servers, the switch, and the shared storage - should be located in the same data center.

Disaster recovery is about maintaining availability even when you lose a data center. That means you need the same infrastructure pictured here, but in yet another data center, with some kind of mechanism keeping the data in sync between those environments.

Options include:

Storage replication
Always On Availability Groups
Log shipping
Database mirroring

But all of these are above and beyond plain ol' failover clustering.


Please never mind, I found a solution on this blog.

Still the mysql data directory points to /var/lib/mysql

https://schworak.com/blog/e53/move-mysql-binlog-to-another-disk-or-folder/

Then did chmod 700 binlog.


Is it possible to show DML that is locking some table?

To see each session's currently running SQL (which supposedly holds the lock) you can join v$session with v$sqltext. I don't have anywhere to test this but something along these lines (using the answer you linked to as a starting point):

select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine,
   s.sql_text
from
   v$locked_object a ,
   v$session b,
   dba_objects c,
   v$sqltext s
where
   b.sid = a.session_id
and
   a.object_id = c.object_id
and s.address = b.sql_address and s.hash_value = b.sql_hash_value
and s.piece = 1
;

This will retrieve the first "piece" of the SQL statement. If you need the complete statement, see for example this link on "Ask Tom".


No, do not switch to SIMPLE recovery model since that will result in a break in your backup chain.  If you are doing in FULL recovery model, do the following:

CHECKPOINT
BACKUP the LOG
DBCC SHRINKFILE (logfile, targetsize)

Repeat until you shrink the log file as small as possible. (My experience is that two or three times is usually needed.)

Then set the preferred default log size and the increment of growth for the log. Do not use percentage log growth, but have a defined increment.

If you follow this, then the backup chain is preserved.

If there are reasons that you need to switch to SIMPLE recovery model, then once you return to FULL recovery model you should BACKUP DATABASE to reestablish the start point for your following log backups.


Can I place the attribute in the Entity, when the foreign key is also already present?

If I saw that, I might expect that there are situations where the automatic attribute and the separate but seemingly identical attribute might actually be different, and this would be confusing. If the foreign key that is used to automatically get the attribute is already present on the entity, I am not sure it's necessary to add that same attribute on the entity - it seems like it would be redundant and could lead to confusion.


You appear to have corruption in your transaction log file. This needs to e resolved before you continue with you mirroring configuration.

  Where is the error log?

Using SSMS the error log can be found in the management folder.

I would check the log for more information and run a full DBCC CHECKDB to ensure the database is OK.

Paul Randall is an expert in data corruption in SQL Server and he mentions a fix to your problem in this article. I would read this through to get a good understanding of the situation you are in. In brief he says that the situation may be solved by switching the database to SIMPLE recovery mode, back to FULL recovery mode and then performing a FULL backup. The full backup restarts the log chain.

I would also inspect the hard drives that the transaction log file is running on as they may have caused the corruption if they are faulty.


I do not like the method provided in the linked question, due to the way UPDATE is processed in MySQL.

Especially in your case, where you want to allow (or restrict) the update based on values from several rows of the table (and not just the row to be updated), I suggest you use 2 statements, one to get the max and sum values from the tickettypes, events and ticketsorders tables and then update accordingly or use JOIN if you want to do this in one statement. The CROSS JOIN are not a problem here as the three first subqueries produce one row each:

UPDATE
    ( SELECT maxtickets
      FROM tickettypes
      WHERE id = $tickettype->id
    ) AS tt
  CROSS JOIN
    ( SELECT maxtickets
      FROM events
      WHERE id = $eventid
    ) AS ev
  CROSS JOIN
    ( SELECT COALESCE(SUM(amount), 0) AS sum_amount
      FROM ticketsorders
      WHERE tickettype_id = $tickettype->id
    ) AS ts
  JOIN
    ticketsorders AS t
      ON  ts.sum_amount +$val <= tt.maxtickets
      AND ts.sum_amount +$val <= ev.maxtickets
SET
    t.amount = $val
WHERE
    t.id = $ticketsorder->id ;


Converting PostgreSQL table to TimescaleDB hypertable

A couple questions as well as answers here:

it's the unique constraint (con1) that's the problem not the foreign key constraint but that was probably a typo.
Yes, it's a problem because the time partitioning column isn't included, the first question I have is, the entry_id seems like it might be a proxy for time from the other system, can you just use pair_id, time as your unique constraint? Will there be multiple records with different entry_ids same pair_ids and same times? You could probably run a query with a count(*) grouped by pair_id, time to figure this out... Note that a three column unique constraint could work, it's a question of how the time is assigned, is it assigned by you at write time or by the upstream system? If it's the upstream system then I wouldn't worry too much about duplicates.
the array thing is somewhat similar to compression, but the compression does different, more efficient algorithms than simple array packing.
figuring out a unique constraint that works, either on pair_id, time or pair_id, entry_id, time will probably be much more efficient than trying to reduce your data to the unique set at query time. And a three column unique constraint isn't going to have a huge impact on performance. The ordering of those columns should be chosen based on query patterns, what sorts of where clauses do you usually have? ie on pair id or something like that as well as time? or just by time?
finally, given the size of your table, I'd highly recommend creating the hypertable without migrating data, so create a separate table, make it a hypertable, then make a small job to migrate the data. The migrate_data = true is really meant for much smaller tables, having an outside job do it means you could also introduce some parallelism which will make it go much faster.


Discovering databases with specific table on a PostgreSQL server

Not in a single query. The information_schema.tables view and the pg_catalog.pg_class table are per-database. You can't query tables across multiple databases in PostgreSQL without hacks like DBLink.

A PostgreSQL database is a bit more like an independent MySQL server, and a PostgreSQL schema is more like a MySQL database. If you want to be able to query across multiple namespaces in PostgreSQL, create them all as schemas within one database, instead of in multiple databases.

To list tables in all databases must connect to each database of interest. You can list them from select * from pg_catalog.pg_database where datistemplate = 'f';. The pg_catalog.pg_database table is global across all databases, so the same results are obtained by connecting to any database.


If you have common attributes across non-exclusive tables, you should consider normalizing your tables.

It appears that Client, Employee, Manager, and Developer are all sub-types of the Person entity.  As such any attributes common to all types should be in the person entity, and attributes for the other entities should depend only on that sub-type.

I would expect Managers and Developers are often or always Employees.

Programmers while need to join the tables need to access the data required for their use case.  It may be appropriate to select from only from the subtype tables, or only from the Person table.


Please explain how this query generates a calendar table in MySQL

I don't know what you want in the explanation, but the last part of the query does a cross join (Cartesian join) to generate an ordered sequence of numbers from 0 to 11322 using the digits table named ints. The query then adds these number to date '2010-01-01' as day interval, thus generating

'2010-01-01'
'2010-01-02'
'2010-01-03'
.....
.....
'2040-12-31'

etc. till you add 11322 days, that is '2040-12-31'. These dates are then inserted into the dt field of the calendar_table.

Well this is a common way of creating a date dimension table. Once the dates are inserted you can update rest of the fields. You generally want to create your organization's holiday table and a payday logic or payday table to update the holiday and payday fields. Rest of the fields can be updated with a query with date functions as below.

update calendar_table
set y = year(dt),
q = quarter(dt),
m = month(dt),
d = day(dt),
dw = dayofweek(dt),
monthName = monthname(dt),
dayName = dayname(dt),
w = week(dt),
isWeekday=
case
   when dayofweek(dt) = 1 or dayofweek(dt) = 7 then 0
   else 1
end;

Cheers.


Can't start mysqld service after changing datadir

This is likely to be an issue with SELinux. If SELinux is running in enforcing mode, you need to to persuade it to allow mysqld to use the non-default datadir. Or alternatively, put SELinux into permissive mode, or disable it entirely. Run sestatus to see which mode it's currently running in. Run grep mysql /var/log/audit/audit.log to see if SELinux has blocked any mysql file access attempts or similar. (But note that this is fine if running in permissive mode, because then it doesn't actually block anything, it just logs what it would have blocked if you were running in enforcing mode.)

Run setenforce permissive to put SELinux into permissive mode, though note you need to edit /etc/sysconfig/selinux (or /etc/selinux/config) to make the change permanent.

If you go the route of trying to persuade SELinux to allow the new datadir, you need to make sure the SELinux context is kept for the files. I suspect this may be lost when using rsync, but not sure.

Here's a page explaining in detail how to deal with SELinux when moving the MySQL datadir: How to move MySQL data directory on CentOS and Ubuntu


You can use information_schema to check if the table exists but in MySQL there is a simpler method:

DROP TABLE IF EXISTS employees ;


MySQL Update a table based on the last data from another table while grouping with conditions

The correct query is:

UPDATE
    lp_plates_backup AS t
    INNER JOIN  (
        SELECT
            plate_uid, brand, model, date_validated
        FROM
            lp_pictures_backup as parent
        WHERE
            brand <> '' AND
            date_validated = (
                 SELECT MAX(date_validated)
                 FROM lp_pictures_backup as t2
                 WHERE t2.plate_uid = parent.plate_uid
                 GROUP BY
                     plate_uid)
) AS m ON
    m.plate_uid = t.uid
SET
    t.brand = m.brand,
    t.model = m.model
WHERE
    t.brand <> m.brand
    OR
    t.model <> m.model;

Just some little explanations. You need INNER JOIN because you must update row of lp_plates_backup only if plate_uid exists into lp_plates_backup. ORDER BY is useless because you are selecting all rows, order is not important.

You need the max date_validated of rows grouped by plate_uid, select MAX(date_validate) must individuate only a row for plate_uid through the date_validated field. So you need to add group by to select max(..).

The query select plate_uid returns a row for plate_uid so you don't need to aggregate here. The condition of single row is already builded into select max subquery.

I hope you did not get confused by me :-)

Updated

The previous works well if date_validate is the pair (plate_uid, date_validated) is unique.
If you have this kind of data:

| plate_uid | brand    | model    | date_validated      |
|         1 | Fiat     | Panda    | 2014-10-11 10:03:20 |
|         1 | BMW      | 7-Series | 2014-10-11 10:03:20 |   <- changed data
|         1 | BMW      | 7-Series | 2014-07-28 19:14:02 |
|         1 | Mercedes | S-Class  | 2014-06-12 08:54:57 |
|         a | Tesla    | Model S  | 2014-12-17 11:00:00 |
|         a | BMW      | 3-Series | 2014-11-07 14:34:11 |

The following query returns the first two rows for plate_uid 1.

SELECT plate_uid, brand, model, date_validated
FROM lp_pictures_backup as parent
WHERE
      brand <> '' AND
      date_validated = (
                 SELECT MAX(date_validated)
                 FROM lp_pictures_backup as t2
                 WHERE t2.plate_uid = parent.plate_uid
                 GROUP BY plate_uid)

The update will choice the values from the first or the second row. I think of the following alternatives:

using some other lp_pictures_backup fields to choise between rows with the same date_validated.
enforcing a unique constraint on table. I.e. alter table lp_pictures_backup add unique index (plate_id, date_validated). Rejecting invalid data.
detecting valid lp_pictures_backup pair of (plate_uid, date_validated). Updating lp_plates_backup only with valid pairs, review invalid pairs and correct them.

Maybe there are more alternatives. I prefer enforcing contraint on data so to have better data. I expand 3th alternative. Just create a view to define what a valid pair (plate_uid, date_validated) is:

CREATE VIEW lp_pictures_backup_valid as
SELECT plate_uid, date_validated
FROM lp_pictures_backup as parent
WHERE date_validated = (
        select max(date_validated)
        from lp_pictures_backup t2
        where t2.plate_uid =  parent.plate_uid GROUP BY plate_uid
      )
group by plate_uid, date_validated
having count(*) = 1;                    <-- you can change this to make an invalid row

A valid pair (plate_uid, date_validated) is a pair with the max date_validated only if there are a unique date_validated value.

I rewrite the update statements to consider only valid pair:

UPDATE
    lp_plates_backup AS t
    INNER JOIN  (
        -- modification start
        SELECT p.plate_uid, p.brand, p.model, p.date_validated
        FROM lp_pictures_backup as p
             INNER JOIN lp_pictures_backup_valid valid
                        ON p.plate_uid = valid.plate_uid and
                           p.date_validated = valid.date_validated
        WHERE p.brand <> '')
        -- modification end
    )
    AS m ON m.plate_uid = t.uid
SET
    t.brand = m.brand,
    t.model = m.model
WHERE
    t.brand <> m.brand
    OR
    t.model <> m.model;

Hope this make sense.

Update: 2014-03-20

In the first case:

  Blockquote
   1. using some other lp_pictures_backup fields to choise between rows with the same date_validated.

I have assumed your date is like this:

|id| plate_uid | brand    | model    | date_validated      |
|4 |         1 | Fiat     | Panda    | 2014-10-11 10:03:20 |
|3 |         1 | BMW      | 7-Series | 2014-10-11 10:03:20 |
|2 |         1 | BMW      | 7-Series | 2014-07-28 19:14:02 |
|1 |         1 | Mercedes | S-Class  | 2014-06-12 08:54:57 |
|2 |         a | Tesla    | Model S  | 2014-12-17 11:00:00 |
|1 |         a | BMW      | 3-Series | 2014-11-07 14:34:11 |

You can try this:

UPDATE
    lp_plates_backup AS t
    INNER JOIN  (
        SELECT t1.plate_uid, t1.brand, t1.model, t1.date_validated
        FROM lp_pictures_backup as t1,
          (SELECT t2.plate_uid, MAX(id) as id,  MAX(date_validated) as dv
           FROM lp_pictures_backup as t2
           GROUP BY t2.plate_uid) as t3
        WHERE t1.brand <> '' AND
              t1.plate_uid = t3.plate_uid AND
              t1.date_validated = t3.dv AND
              t1.id = t3.id
) AS m ON
    m.plate_uid = t.uid
SET
    t.brand = m.brand,
    t.model = m.model
WHERE
    t.brand <> m.brand
    OR
    t.model <> m.model;

The fields used to choise the row to be updated are extracted by this part:

 ...
 (SELECT t2.plate_uid, MAX(id) as id,  MAX(date_validated) as dv
  FROM lp_pictures_backup as t2
  GROUP BY t2.plate_uid) as t3
 ...

So it assumes a correlation between date_validated and id: at increasing dates corresponds to increasing id.

Hope it help.


Database Design for Preserving Values and Additional columns

Which fields will you need to do any of the following on?

sorting
filtering
counting
grouping
type checking

If there are some fields that you simply want to store for viewing or what-not, then you don't need to put them in their own columns. You could store them as JSON or XML or some other serialized form.

But if you need to do the above on your fields, then you almost certainly want real database columns for them. And by real database columns I mean not EAV or LOBs.

If it sounds like a lot of work, then consider using Hibernate (with inheritance) to generate your database schema for you. And use Hibernate Envers to handle change capturing.


Right way to get references to an object in Oracle?

After talking with some people with more Experience in Oracle than me, they told me I was right in my understanding.

This query returns the references to an object (except the constraints)

SELECT object_id, owner, object_name, object_type
       FROM sys.DBA_OBJECTS
       WHERE object_id IN (select object_id
                           from public_dependency
                           where REFERENCED_OBJECT_ID = :id_object)
       ORDER BY object_name


Can a Selective XML index help when using query()?

From Selective XML Indexes (SXI), Supported Features, Prerequisites, and Limitations

  Selective XML indexes support the XQuery supported by SQL Server
  inside the exist(), value() and nodes() methods.

query() is not supported so it will not work for you.

On the other hand it says that

  For the query() and modify() methods, selective XML indexes may be
  used for node filtering only.

I have not been able to produce a query that use XML indexes using the query() method so I can't really tell you in what cases it may work.

If you want to tackle a performance issue with your query I would recommend you to shred the XML using nodes() and value() and then reconstructing the XML using for xml path. It is usually faster than building the XML with query() and you could also probably make use of selective XML indexes if you need it.


Does Postgres automatically rotate WAL files out (pg_xlog) if archive_mode is off?

If archive_mode = off, PostgreSQL will delete old WAL files as soon as they are older than the latest checkpoint. These checkpoints occur by default at least every 5 minutes, so there should never be many old WAL files around.

If you set archive_mode = on, a WAL file are only deleted once archive_command has returned success for that file. An empty archive_command should always do that immediately. It is a tradition to set archive_command = '/bin/true' to indicate that you temporarily disabled archiving.


Well here is a small checklist. Due to the fact that I'm not on your machine I cannot take a look inside and give you some suggestions. But I'm pretty sure, that you'll find the root cause with the provided statements.

Wrong memory configuration

As you mentioned, you upgraded your SQL Server and run different instances on the same physical machine. One of the things which may be harmful is a restart or anything like that (e.g. Failover). Maybe your instance has reset their memory limits. This may explain your drastically slowdown. You can check if they still up with your documented max memories. You can get them running the following the command below:

EXEC sp_configure N'max server memory (MB)'

If you just want to see this as a quickshot. You can try to query the dm_os_sys_memory which has a nice column system_memory_state_desc which isn't very detailed but it says you the needed things too. It will display if your memory is low, which means there is a constant memory pressure on the system.

SELECT system_memory_state_desc
FROM sys.dm_os_sys_memory

Statistics are out of date

You might want to update your statistics for your database tables, as they may be too old.

EXEC sp_updatestats

In a situation like yours, it is wise to throw away your cache plan, as it might be useless due to the old statistics. If sp_updatestats shows many tables and many statistics which are updated, it may be a good idea to throw it away and let the SQL Server recreate it.

You can achieve this using this DBCC command:

DBCC FREEPROCCACHE

Fragmented indices

It might be, that your indices are too fragmented which cause a huge load on your I/O subsystem. Before you blindly execute the statement, you should check if your machine has a higher load on your I/O subsystem. This may be easily done by using perfmon for example. The READ UNCOMMITTED should be used as some indices may be written at the moment. You can try to run the following statement.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- Get Index Fragmentation
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName
    , ind.name AS IndexName, indexstats.index_type_desc AS IndexType
    , indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
        ON ind.object_id = indexstats.object_id
        AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 25
ORDER BY indexstats.avg_fragmentation_in_percent DESC
GO

  Beware, it might run very long and on your system which is already under pressure it might kill it. Only run it, if your system may to be affected by it.

Each row which will be returned is an index which is fragmented and may need an REBUILD or REORGANIZE. This may improve your overall system performance too, but during the rebuild itself, it will slow down!

ARITHABORT problem

It may be an problem with ARITHABORT which tends to make problems on older databases (for some users). Maybe you try to enable/disable it.

Many other causes...

As mentioned in the first sentences, it's pretty hard to see where your problem is. It may be another error - maybe even a hardware fault. I've had a error on a switch to my SAN which killed my performance too for some years (before it was redundant).

But if nothing of the above tends to help, you can still provide the output of your dm_os_wait_stats which may help me to get the right view on your system.

SELECT TOP 30 *
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC

This will return the biggest waitcounter and may help to find a bottleneck or error. You can do this using the following query:

-- Show all active processes with detailed informations
SELECT pr.spid, pr.loginame, pr.status, pr.blocked, pr.waittime, pr.lastwaittype,pr.cpu, pr.physical_io, pr.memusage, pr.last_batch, db.name AS databaseName, obj.name AS objectName, txt.text AS currentQuery, pr.request_id
FROM sys.sysprocesses as pr
INNER JOIN sys.databases as db
        ON pr.dbid = db.database_id
OUTER APPLY sys.dm_exec_sql_text(pr.sql_handle) as txt
LEFT JOIN sys.all_objects as obj
        ON txt.objectid = obj.object_id
WHERE pr.status <> 'sleeping'
    and pr.spid >= 50

Additionally it can be a process which produces locks or consume more than it should. But I think this isn't the best possibility. But anyway, you can take a look in your running processes. Maybe you see something which isn't correct.


Clustered index on a column with low selectivity

Low Selectivity

Here is a good quote from SqlServerCentral:

  In general, a nonclustered index should be selective. That is, the
  values in the column should be fairly unique and queries that filter
  on it should return small portions of the table.

  The reason for this is that key/RID lookups are expensive operations
  and if a nonclustered index is to be used to evaluate a query it needs
  to be covering or sufficiently selective that the costs of the lookups
  aren’t deemed to be too high.

  If SQL considers the index (or the subset of the index keys that the
  query would be seeking on) insufficiently selective then it is very
  likely that the index will be ignored and the query executed as a
  clustered index (table) scan.

  It is important to note that this does not just apply to the leading
  column. There are scenarios where a very unselective column can be
  used as the leading column, with the other columns in the index making
  it selective enough to be used.

Couple of things to consider when indexing:

When To Avoid Indexing

Indexes should not be used on small tables.
Tables that have frequent, large batch update or insert operations.
Indexes should not be used on columns that contain a high number of
NULL values.
Columns that are frequently manipulated should not be indexed.
When you already have a lot of indexes (do not over index a table)
Avoid Nonclustered, Unfiltered Indexes on Columns that have few Distinct Values (Low Cardinality)

Effectiveness of Indexing

I'm going to quote another stack exchange post:

  There are a few concepts and terms that are important to understand
  when dealing with indexes. Seeks, scans, and lookups are some of the
  ways that indexes will be utilized through select statements.
  Selectivity of key columns is integral to determining how effective an
  index can be.

  A clustered index's key columns are
  called a clustering key. This is how records are made unique in the
  context of a clustered index. All nonclustered indexes will include
  the clustered key by default, in order to perform lookups when
  necessary. All indexes will be inserted to, updated to, or deleted
  from for every respective DML statement. That having been said, it is
  best to balance performance gains in select statements against
  performance hits in insert, delete, and update statements.

  In order to determine how effective an index is, you must determine
  the selectivity of your index keys. Selectivity can be defined as a
  percentage of distinct records to total records. If I have a [person]
  table with 100 total records and the [first_name] column contains 90
  distinct values, we can say that the [first_name] column is 90%
  selective. The higher the selectivity, the more efficient the index
  key. Keeping selectivity in mind, it is best to put your most
  selective columns first in your index key. Using my previous [person]
  example, what if we had a [last_name] column that was 95% selective?
  We would want to create an index with [last_name], [first_name] as the
  index key.

Personally

I index on how the table is being used. This includes the holistic view of all queries on the tables as well what queries are not taking advantage of indexes. As Jeremiah Peshka said in the same thread, if the percentage of missing indexes is high, then an index on how it's being used is likely needed.

Key Takeaway

The SQL query engine loves highly selective key columns. Index on how a table is being used. Keep It Simple Stupid (KISS).


Using Resource Governor with SQL Agent Jobs

Resource governor classifier functions only run during the  Login process.  Impersonation via EXECUTE AS does not trigger the classifier function.  EXECUTE AS is how SQL Server Agent runs jobs in the context of another login or user.

Specifying the Run As ... user or modifying the Job Owner does not change how SQL Server Agent logs into SQL Server.  The only way I can get SQL Server agent jobs to run in a specific resource group with a classifier function that looks only at the user name is to put the SQL Server Agent Service Account into it's own resource group.

Since testing is a great way to understand what is really happening, I've created a small test-bed.  Don't run this on a production system since it will modify the resource group configuration.

This creates the classifier function, and several resource groups attached to a test resource pool:

USE master;
SET NOCOUNT ON;
GO
CREATE FUNCTION dbo.fnDummyClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @GroupName sysname = NULL;
    IF SUSER_SNAME() = 'DOMAIN\USER'
        SET @GroupName = N'AgentServiceAccountGroup';

    IF SUSER_SNAME() = 'ResourceGovernorTestLogin'
        SET @GroupName = N'ResourceGovernorTestLoginGroup';

    IF SUSER_SNAME() = 'ResourceGovernorTestUser'
        SET @GroupName = N'ResourceGovernorTestUserGroup';

    IF @GroupName IS NULL
        SET @GroupName =  N'default';

    RETURN @GroupName;
END
GO
CREATE RESOURCE POOL TestPool
WITH (MAX_CPU_PERCENT = 10);
CREATE WORKLOAD GROUP AgentServiceAccountGroup
WITH (
      group_max_requests=0
    , importance=Medium
    , request_max_cpu_time_sec=0
    , request_max_memory_grant_percent=25
    , request_memory_grant_timeout_sec=0
    , max_dop=0
    )
USING TestPool;
CREATE WORKLOAD GROUP ResourceGovernorTestLoginGroup
WITH (
      group_max_requests=0
    , importance=Medium
    , request_max_cpu_time_sec=0
    , request_max_memory_grant_percent=25
    , request_memory_grant_timeout_sec=0
    , max_dop=0
    )
USING TestPool;
CREATE WORKLOAD GROUP ResourceGovernorTestUserGroup
WITH (
      group_max_requests=0
    , importance=Medium
    , request_max_cpu_time_sec=0
    , request_max_memory_grant_percent=25
    , request_memory_grant_timeout_sec=0
    , max_dop=0
    )
USING TestPool;
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = [dbo].[fnDummyClassifier]);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Here we'll create a login, and allow it to VIEW SERVER STATE so we can tell which resource group the session has been assigned to.

CREATE LOGIN ResourceGovernorTestLogin
WITH PASSWORD = '!NnrtiHummusPlenumPoodle2'
    , DEFAULT_LANGUAGE = us_english
    , CHECK_EXPIRATION = OFF
    , CHECK_POLICY = OFF;
GRANT VIEW SERVER STATE TO ResourceGovernorTestLogin;
GO

This creates a SQL Server Agent job where the "owner" is set to the login we just created.  Since the login is not a member of sysadmin, SQL Server agent will run this job in the context of that login.

DECLARE @JobID uniqueidentifier;
EXEC msdb.dbo.sp_add_job @job_name = 'TestResourceGovernorJob'
    , @enabled = 1
    , @description = 'Tests resource governor classification'
    , @start_step_id = 1
    , @owner_login_name = 'ResourceGovernorTestLogin'
    , @job_id = @JobID OUTPUT;
EXEC msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = 'Step1'
    , @subsystem = 'TSQL'
    , @command = 'SELECT '''';
SELECT UserName = CONVERT(nvarchar(30), SUSER_SNAME())
    , [SYSTEM_USER] = CONVERT(nvarchar(30), SYSTEM_USER)
    , [SESSION_USER] = CONVERT(nvarchar(30), SESSION_USER)
    , [ORIGINAL_LOGIN] = CONVERT(nvarchar(30), ORIGINAL_LOGIN())
    , WorkloadGroup = CONVERT(nvarchar(30), wg.name)
FROM sys.dm_exec_requests der
    INNER JOIN sys.dm_resource_governor_workload_groups wg ON der.group_id = wg.group_id
WHERE der.session_id = @@SPID;'
    , @flags = 4 --write step output into msdb.dbo.sysjobstephistory
    , @on_success_action = 1;
EXEC msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(LOCAL)';
/*
    Call SQL Server Agent via msdb.dbo.sp_notify_job
    which in turn calls msdb.dbo.xp_notify_job.
    The SQL Server Agent executable logs into the
    SQL Server instance using the service account,
    then performs an "EXECUTE AS ..." to run
    job step(s).
*/
EXEC msdb.dbo.sp_start_job @job_id = @JobID;
GO

WAITFOR DELAY N'00:00:01';
DECLARE @msg nvarchar(max);
SELECT @msg = sjh.message
FROM msdb.dbo.sysjobhistory sjh
    INNER JOIN msdb.dbo.sysjobs sj ON sjh.job_id = sj.job_id
WHERE sj.name = 'TestResourceGovernorJob'
    AND sjh.step_id = 1;
PRINT (N'');
PRINT (@msg);
PRINT (N'');
EXEC msdb.dbo.sp_delete_job @job_name = 'TestResourceGovernorJob';
GO

The results from the msdb history table for the above job:

Job 'TestResourceGovernorJob' started successfully.

Executed as user: ResourceGovernorTestLogin.
-

(1 rows(s) affected)
UserName                       SYSTEM_USER                    SESSION_USER                   ORIGINAL_LOGIN                 WorkloadGroup
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
ResourceGovernorTestLogin      ResourceGovernorTestLogin      guest                          DOMAIN\USER                    AgentServiceAccountGroup
(1 rows(s) affected).  The step succeeded.

Next, we'll create a user for the login, and run a new version of the job using the Run As option in the advanced properties of the SQL Server Job Step.

CREATE USER ResourceGovernorTestUser
FOR LOGIN ResourceGovernorTestLogin;

DECLARE @JobID uniqueidentifier;
EXEC msdb.dbo.sp_add_job @job_name = 'TestResourceGovernorJob'
    , @enabled = 1
    , @description = 'Tests resource governor classification'
    , @start_step_id = 1
    , @owner_login_name = 'ResourceGovernorTestLogin'
    , @job_id = @JobID OUTPUT;
EXEC msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = 'Step1'
    , @subsystem = 'TSQL'
    , @database_name = 'master'
    , @database_user_name = 'ResourceGovernorTestUser'
    , @command = 'SELECT '''';
SELECT UserName = CONVERT(nvarchar(30), SUSER_SNAME())
    , [SYSTEM_USER] = CONVERT(nvarchar(30), SYSTEM_USER)
    , [SESSION_USER] = CONVERT(nvarchar(30), SESSION_USER)
    , [ORIGINAL_LOGIN] = CONVERT(nvarchar(30), ORIGINAL_LOGIN())
    , WorkloadGroup = CONVERT(nvarchar(30), wg.name)
FROM sys.dm_exec_requests der
    INNER JOIN sys.dm_resource_governor_workload_groups wg ON der.group_id = wg.group_id
WHERE der.session_id = @@SPID;'
    , @flags = 4 --write step output into msdb.dbo.sysjobstephistory
    , @on_success_action = 1;
EXEC msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(LOCAL)';
/*
    Call SQL Server Agent via msdb.dbo.sp_notify_job
    which in turn calls msdb.dbo.xp_notify_job.
    The SQL Server Agent executable logs into the
    SQL Server instance using the service account,
    then performs an "EXECUTE AS ..." to run
    job step(s).
*/
EXEC msdb.dbo.sp_start_job @job_id = @JobID;
GO

WAITFOR DELAY N'00:00:01';
DECLARE @msg nvarchar(max);
SELECT @msg = sjh.message
FROM msdb.dbo.sysjobhistory sjh
    INNER JOIN msdb.dbo.sysjobs sj ON sjh.job_id = sj.job_id
WHERE sj.name = 'TestResourceGovernorJob'
    AND sjh.step_id = 1;
PRINT (N'');
PRINT (@msg);
PRINT (N'');
EXEC msdb.dbo.sp_delete_job @job_name = 'TestResourceGovernorJob';

DROP USER ResourceGovernorTestUser;
GO

The output from that run of the job:

Job 'TestResourceGovernorJob' started successfully.

Executed as user: ResourceGovernorTestLogin.
-

(1 rows(s) affected)
UserName                       SYSTEM_USER                    SESSION_USER                   ORIGINAL_LOGIN                 WorkloadGroup
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
ResourceGovernorTestLogin      ResourceGovernorTestLogin      ResourceGovernorTestUser       DOMAIN\USER                    AgentServiceAccountGroup
(1 rows(s) affected).  The step succeeded.

This cleans up the resource governor configuration, and drops the classifier function and the login.

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
DROP WORKLOAD GROUP AgentServiceAccountGroup;
DROP WORKLOAD GROUP ResourceGovernorTestUserGroup;
DROP WORKLOAD GROUP ResourceGovernorTestLoginGroup;
DROP RESOURCE POOL TestPool;
ALTER RESOURCE GOVERNOR RECONFIGURE;
DROP FUNCTION dbo.fnDummyClassifier
DROP LOGIN ResourceGovernorTestLogin;
GO

In both result sets above, you can see the Resource Governor classifier function put each job into the AgentServiceAccountGroup group; this is because the SQL Server Agent initially logs into SQL Server using the Service Account used when SQL Server Agent starts up.


Store last elements in a dedicated table

It is likely that you would partition the table by time or region, etc.  Also this is what indexes or caching already do.

So your idea could be appropriate but you need to think about whether you are reinventing the wheel or if your use case can really benefit.


The --apply-slave-statements parameter doesn't make sense here, as it is used when you dump from a slave, not from a master.

And this one most likely causes your problems: --databases my-db

My guess is, that you expect, that MySQL/MariaDB only replicates the database that you've dumped and restored on the slave. But without filtering MySQL/MariaDB replicates everything from the master to the slave. To filter you can use the following manual page: https://mariadb.com/kb/en/library/replication-filters/

If that is not your problem, then the first thing I would check is, if someone has disabled foreign key checking on your master with select @@global.foreign_key_checks; If this returns 0 it's bad. Then you can use my stored procedure from this answer to check for data inconsistencies.


Cannot attach the AdventureWorks database

SQL Server service account don't have permission in the drive you are using to create database.Don't put you database in the root of drive. It would be problematic if you have UAC enabled, Create a folder in drive. Like D:\Data

Check this http://msdn.microsoft.com/en-us/library/windows/desktop/ms681382(v=vs.85).aspx

ERROR_ACCESS_DENIED
5 (0x5)
Access is denied.

Make sure that the SQL Server Service Account has read/Write permission to the folder where database file is kept or use the instance default folder of Data/Log where it will already have such permissions.


Need to change alphanumeric characters to next character

Use a numbers table to split your string and a case statement to figure out what the values your characters should have and for xml path to rebuild the string.

Put the logic in a function so it can be reused for multiple columns.

create function dbo.SS(@S varchar(max))
returns table as return
(
  select
  (
    select case
            when S.C = '9' then '0'
            when S.C = 'Z' then 'A'
            when S.C like '[A-Z,0-9]' then char(ascii(S.C) + 1)
            else S.C -- Keep not A-Z,0-9 as is
          end
    from Number as N
      cross apply (select substring(@S, N.N, 1)) as S(C )
    where N.N between 1 and len(@S)
    for xml path(''), type
  ).value('text()[1]', 'varchar(max)') as C
)

This would be your update statement.

update YourTable
set Name = (select C from dbo.CC(Name)),
    [Passport No.] = (select C from dbo.CC([Passport No.]))


10.4.10-MariaDB Column 'authentication_string' is not updatable

You are suppose to run mysql_upgrade command for Mariadb instance. Command is as following :

mysql_upgrade [--force] [--user=# --password
  --host=hostname --port=# --socket=#
  --protocol=tcp|socket|pipe|memory
  --verbose]