How do I pull a backup of an oracle database so I can send it to a vendor

In the Two Day DBA Guide see  see the section called "Performing a Whole Database Backup".  It explains how to use Database Control to do a full backup.  If you have 11g see this version.  Assuming Database Control is working this would probably be the easiest for someone who knows nothing about Oracle.

On the other hand, the database may already have regular backups done on it in which case you can just send one of those.  If it doesn't or the information about them hasn't been retained, then it would seem that the data would not be important enough to convert.


Limit redo for materialized view complete refresh or manual equivalent

Good question. I "solved" this problem for my situation a while back by making the MV's and any indexes on them NOLOGGING. There was no point to it my situation - I was doing a full refresh of the view anyway, why would I need redo?


Transfer SQL Server 2017 Standard license to a new server

Look under the section “ Licensing SQL Server for application mobility” the first bullet details on what you are asking about.  There is no need to remove the license from the first machine prior to installation of the replacement machine.

  License Mobility is a use right that is available for all editions of SQL Server 2017 software licenses with active Software Assurance (SA) coverage. With this SA benefit, customers can reassign SQL Server licenses to different servers within a server farm as often as needed. Customers can also reassign licenses to third party shared servers. License Mobility is available for licenses under both the Per Core and Server+CAL license models.

  SQL Server licenses that are not covered with active SA can only be reassigned to a different server within a server farm once every 90 days, and they cannot be reassigned to a third-party web hoster or non-private cloud at any time. (In the event of permanent hardware failure, the 90-day reassignment limit is waived.)
  All SQL Server licenses with active SA can be reassigned to another server within the server farm as often as needed; however, they can only be reassigned to another server in another server farm, or to a non-private cloud, once every 90 days.

  A server farm may consist of up to two data centers located in time zones that are within four hours of one another and/or with the European Union (EU) and/or European Free Trade Association (EFTA).
  A given data center may only be part of one server farm.
  License Mobility use rights do not apply to SQL Server PDW software.
  License Mobility can benefit customers who license individual virtual machines (VMs) or containers and then want to reassign those licenses to different servers within a server farm—as workloads move dynamically—or to VMs in cloud environments.

2017 licensing doc


Why does MariaDB choose different index use for the same data?

This should temporarily fix the statistics that appear to be screwed up:  Do ANALYZE TABLE logs;; it will take a long time (for MyISAM, but not for InnoDB).
Run CHECK TABLE logs; -- the index may be corrupted.  (This is a MyISAM-only thing to do.)
Use InnoDB, not MyISAM.  InnoDB does not need ANALYZE TABLE nearly as often, and the ANALYZE runs much faster.


How to Limit Access in a Database System

Allowing even trusted users to login to what appears to be a production database is in my mind a big no-no. The solutions you mention above may provide you with a sense of security/relief but they their own have side effects.

I would be particularly worried about your statement

  Manual updating and inserting of records is very infrequent

If you have users who are updating/insert records then I am sure they are also deleting records most likely with no or a very limited audit trail.

If this was my database I would

Immediately prevent all users from accessing the database tables and running any SQL statements such as Select, Insert, Delete, Update etc
Sit down with the end users who are requesting data and determine exactly what data is required. Based on the end user requirements create a set of SPs that produce the required data.
A DB account should be created that would only have execute permissions on the SPs
If possible determine if the reports really have to be run in real time on a production database. If not I would suggest a snapshot be created on a regular basis on which the reports are run.
I would also ensure that the sa account has a strong password and is not used on a day to day basis and that the password is know to only 1-2 people.
Monitor the database over a period of time to ensure users have not gained additional privileges


How can I get the row causing a conversion failure in SQL Server?

This will help you figure out what value is causing your error. The example below shows a table with a NULL, a word, and a time stamp. You can see by running a case when statement that analyzes the value to essentially see if it's a date or could be a date and prints the value that is not a date. The CTE wrap up gives us a clean cut view into what specific values are not dates, so you can edit that value.

As Erik said, you can do a try_convert to get the update to take. The approach below will show you which value is not converting.

DECLARE @table TABLE (test VARCHAR(255))

INSERT  INTO @table
        (test)
VALUES
        ('cat'  -- test - varchar(255)
            )

INSERT  INTO @table
        (test)
VALUES
        (NULL)

INSERT  INTO @table
        (test)
VALUES
        ('2017-03-04');
WITH    results
            AS (
                SELECT
                    ISNULL((CASE    WHEN ISDATE(test) = 0 THEN test
                                    ELSE 'not error'
                            END) , 'not error') AS test
                FROM
                    @table
                )
    SELECT
        *
    FROM
        results
    WHERE
        test != 'not error'


InnoDB (the default Engine in MySQL) allocates separate "blocks" for each table.

A "block" is 16KB, regardless of what size the OS or disk controller does.

Yes, a table with one small row is "wasting" a lot of disk space.  But if you have a million rows, the percentage waste will be lower.

Similarly, each INDEX (except for leaf nodes of the PRIMARY KEY) will use disk in blocks of 16KB.

Use this to see the "Data_length" and "Index_length":

SHOW TABLE STATUS LIKE 'T1';

I created a table with 2 secondary indexes, but have not put any rows in it yet:

mysql> SHOW TABLE STATUS LIKE 'mod%'\G
*************************** 1. row ***************************
           Name: modification
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384        -- one 16KB block for data
Max_data_length: 0
   Index_length: 32768        -- two 16KB blocks, one per index
      Data_free: 0
  ...

Those 3 blocks belong to that one table; no sharing.


Loop inside stored procedure doesn't stop after query cancellation

You could have a look at running requests with either sp_whoisactive or with the below query and kill the session associated with the query / stored procedure.

select r.session_id
    , s.text
from sys.dm_exec_requests r
    cross apply sys.dm_exec_sql_text(sql_handle) AS s

Then you just execute the KILL command, like below, with the appropriate integer value from the above result, session which is associated with your stored procedure / query:

KILL <session_id>


Use maatkit heartbeat. You will need another perl process running is very helpful.

 You can also use mk-slave-delay to generate lag. Protection purposes.


What's the use of assigning foreign keys on MySQL

A foreign key is a type of constraint.  The purpose of constraints is to make sure that data in tables follows certain rules.

You use foreign key constraints to ensure that child data has parent data.  You can use it to either prevent orphaning child records or to facilitate cascade deletes or updates.


What are some databases that are schema-full but not relational

Neo4j is a schema-optional graph database.  You could choose to enforce a full schema and reap the benefits.

MongoDB v3.2 has  validation of structure and data type.


You probably don't want to hear this, but the best option to speed up SELECT DISTINCT is to avoid DISTINCT to begin with. In many cases (not all!) it can be avoided with better database-design or better queries.
Sometimes, GROUP BY is faster, because it takes a different code path.
In your particular case, it doesn't seem like you can get rid of DISTINCT (well, see blow). But you can support the query with a special index if you have many queries of that kind:
CREATE INDEX foo ON events (project_id, "time", user_id);

Adding user_id is only useful if you get index-only scans out of this. Follow the link for details. Would remove the expensive Bitmap Heap Scan from your query plan, which consumes 90% of the query time.
Your EXPLAIN shows 2,491 distinct users out of half a million qualifying rows. This won't become super-fast, no matter what you do, but it can be substantially faster. With around 200 rows per user, emulating an index skip scan on above index might pay. The range condition on time complicates matters, and 200 rows per user is still a moderate number. So not sure. See:

SELECT DISTINCT is slower than expected on my table in PostgreSQL
Select first row in each GROUP BY group?
Optimize GROUP BY query to retrieve latest row per user

Either way, if time intervals in your queries are always the same, a MATERIALIZED VIEW folding user_id per (project_id, <fixed time interval>) would go a long way. No chance there with varying time intervals, though. Maybe you could at least fold users per hour or some other minimum time unit, and that would buy enough performance to warrant the considerable overhead. Can be combined with either query style.
Nitpick:
Most probably, the predicates on "time" should really be:
AND "time" >= '2015-01-11 8:00:00'
AND "time" <  '2015-02-10 8:00:00';
Aside:
Don't use time as identifier. It's a reserved word in standard SQL and a basic type in Postgres.


See the mydata.csv file at bottom of the answer.

I created a table xy

CREATE TABLE `xy`
(
  `fred` int(11) DEFAULT NULL,
  `mary` int(11) DEFAULT NULL,
  `billy` varchar(50) DEFAULT NULL
);

From the documentation here, I tried this

LOAD DATA INFILE 'mydata.txt' INTO TABLE tbl_name
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'

But that failed (only one line imported). I changed \r\n to \n (Unix), that was better (all lines imported, but no \ in the VARCHAR field - they were simply deleted).

Further Googling led me here

So I tried this (from the answer)

LOAD DATA INFILE 'mydata.csv' INTO TABLE xy
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ''
LINES TERMINATED BY '\n';

(note ESCAPED BY '' - i.e. nothing - the \ is the default escape character).

And that worked (sample of four lines in table xy below)

|    2 |    3 | asf\dsadfs |
|    2 |    3 | asfdsadfs  |
|    2 |    3 | asfdsadfs  |
|    2 |    3 | asf\dsadfs |

My sample mydata.csv file

2,3,asfdsadfs
2,3,asfdsadfs
2,3,asfdsadfs
2,3,asfdsadfs
2,3,asfdsadfs
2,3,asfdsadfs
2,3,asfdsadfs
2,3,asf\dsadfs
2,3,asfdsadfs
2,3,asfdsadfs
2,3,asf\dsadfs
2,3,asfdsadfs
2,3,asfdsadfs
2,3,a\sfdsadfs
2,3,asfdsadfs
2,3,asfdsadfs


Oracle enterprise manager showing wrong port after change

Sometimes it helps to de-install the enterprise manager and then re-install it using the emca command. We'll leave that for the worst case.

Using EMCA After Changing the Database Listener Port

  If you change the listener port of the database after you have configured Database Control, the database status will appear as down. To reconfigure Database Control so it uses the new listener port, run the EMCA command using the -config dbcontrol db [-cluster] command-line arguments.

(emphasis mine)

Reference: Enterprise Manager Advanced Configuration (Oracle Docs 10g)

In your case you would run the following commands:

SET ORACLE_HOME= your_oracle_home
SET ORACLE_SID= your_oracle_sid

emca -config dbcontrol db

You might have to provide various pieces of information when re-configuring the Enterprise Manager:

port
SID
DBSNMP_PWD
SYSMAN_PWD
HOST_USER
HOST_USER_PWD
SYS_PWD

If this doesn't reconfigure the database control for your instance you might have to re-create the repository containing all the Entrprise Management Console configuration settings. You would achieve this be adding a parameter to the previous command:

SET ORACLE_HOME= your_oracle_home
SET ORACLE_SID= your_oracle_sid

emca -deconfig dbcontrol db
emca -config dbcontrol db -repos recreate

This will de-configure the EM Console settings for a database (not for a cluster installation) and then recreate the repository.

The additional information I listed above will be required for a re-configuration to work.

  DISCLAIMER
  Re-configuring the Enterprise Management Console is no easy task. I have failed many times (especially with broken timezone settings). If you are unsure or don't feel in the position to risk your installation, please consider contacting an Oracle Consultant.

  If you have a test environment, consider trying this out there first.


Why does NULL ordering impact a btree scan in PostgreSQL?

I asked the question in irc, it seems that they just never did it.

< johto> EvanCarroll; "because nobody implemented it" is usually the answer
< macdice> EvanCarroll: we absolutely could implement that and it's been discussed in this channel.  the codez have not been forthcoming
< macdice> an index scan that is smart enough to skip null (start after null) and then skip back to the nulls at the end
< macdice> probably the easiest of many potential skip scans tricks we could teach indexes to do

(pending other potential answers, I'll just accept this)


postgres and passwordless authentication

Do you have administrative access to the database?

If so you can edit pg_hba.conf to adjust access control configuration. See http://developer.postgresql.org/pgdocs/postgres/auth-pg-hba-conf.html

After you make changes issue pg_ctl reload or restart the database.


MySQL - MySQLDump problems with large table

Your problem stems from network. In this instance, it is not the buffer used for data transmission over the network. It is the length of time the network tries to keep alive in so doing.
Please note these options:

net_read_timeout (Default 30) : The number of seconds to wait for more data from a connection before aborting the read. When the server is reading from the client, net_read_timeout is the timeout value controlling when to abort. When the server is writing to the client, net_write_timeout is the timeout value controlling when to abort. See also slave_net_timeout.
net_write_timeout (Default 60) : The number of seconds to wait for a block to be written to a connection before aborting the write. See also net_read_timeout.

You have a busy server and, at some moment in time, the data you need to read for the mysqldump to continue times out.
What I have done in the past is run the following on both source and target servers:
mysql> SET GLOBAL net_read_timeout = 31536000;
mysql> SET GLOBAL net_write_timeout = 31536000;

Then, go run the mysqldump.
Setting these values very high forces the mysqldump DB Connection to wait a long time for data to be shipped over. In case you are wondering, The number 31536000 is the number of seconds in a year (365 * 24 * 60 * 60). The mysqldump DB connection will just have to stay around and wait, even for your busy server, until data comes (eventually). Please see my post from Jan 12, 2012 : Client times out, while MySQL query remains running?
GIVE IT A TRY !!!
UPDATE 2018-01-04 09:53 EST
I noticed your comment just now
Please note the error message you got from the mysqldump:

Error 2013 - Lost connection to MySQL server during query when dumping table responses at row 12891212

Please take note of the row number 12891212. If that row number comes back in the error message every single time your mysqldump fails, then maybe you can suspect data corruption. However, if your get a different row number with every mysqldump attempt, this suggests mysqldump connection is able to move along the large table and just timeout and some random point.
Please go set those values and try the mysqldump again. Then, come back back tell us what happened.


Get the total count of every child foreign keys

Yes, it's possible to do it using either a recursive CTE or a recursive function (at first I couldn't figure out how to solve it using CTE, so I coded the solution using the function and, since it might be useful to someone else, I'm keeping it on the answer as well). Here's how you can do it:

Recursive CTE

;WITH QUERY AS
(
    SELECT IDEquip, 1 AS Quantity
    FROM Conns

    UNION ALL

    SELECT E.IDParent AS IDEquip, 1 AS Quantity
    FROM Equipament E
        INNER JOIN QUERY Q ON E.ID = Q.IDEquip
)
SELECT E.ID, ISNULL(SUM(Q.Quantity), 0) AS Total
FROM Equipament E
    LEFT JOIN QUERY Q ON E.ID = Q.IDEquip
GROUP BY E.ID;

The trick to solve it using the recursive CTE was to query the values from back to front.

Recursive Function

CREATE FUNCTION dbo.GetAllDescendants(@id nvarchar(100))
RETURNS INT
AS
BEGIN
    RETURN(
        SELECT SUM(Total + ISNULL(dbo.GetAllDescendants(ID), 0))
        FROM (
            SELECT ID, IDParent, COUNT(IDEquip) AS Total
            FROM Equipament
                LEFT JOIN Conns ON ID = IDEquip
            GROUP BY ID, IDParent
        ) AS TotalConns
        WHERE IDParent = @id
    )
END;

With this query you can get the total of descendants, but not the connections for the ID itself:

SELECT ID, ISNULL(dbo.GetAllDescendants(ID), 0) AS [Total Conections Dependancy]
FROM Equipament
ORDER BY ID;

To get the exact result you described you should run it like this:

SELECT ID, IIF(COUNT(IDEquip) > 0, COUNT(IDEquip), ISNULL(dbo.GetAllDescendants(ID), 0)) AS [Total Conections Dependancy]
FROM Equipament
    LEFT JOIN Conns ON ID = IDEquip
GROUP BY ID, IDParent
ORDER BY ID;


UPDATE only if COUNT from another table is not zero

Derive a table of counts by id from table2, join the results to table1 and use that join in the UPDATE statement:

UPDATE
  table1 AS t1
  INNER JOIN
    (
      SELECT
        id,
        COUNT(*) AS cnt
      FROM
        table2
      GROUP BY
        id
    ) AS t2 ON t1.id = t2.id
SET
  t1.number = t1.number + t2.cnt
;

The above statement will affect only the table1 rows that have matches in table2.


How to find the latest SQL statements within the database?

Here is the SQL to do the job. Open for trial.

Step 1: Determine the installatin IDs & user IDs.

SELECT inst_id,sid FROM gv$session WHERE username='<ENTER-USERNAME>';

Step 2:

SELECT
      s.sid
     ,s.CLIENT_INFO
     ,s.MACHINE
     ,s.PROGRAM
     ,s.TYPE
     ,s.logon_time
     ,s.osuser
     ,sq.sorts
     ,sq.DISK_READS
     ,sq.BUFFER_GETS
     ,sq.ROWS_PROCESSED
     ,sq.SQLTYPE
     ,sq.SQL_TEXT
 FROM gv$session s
    , gv$sql sq
WHERE s.SQL_HASH_VALUE = sq.HASH_VALUE
  AND s.inst_id = :inst_id -- replace with instID from above
  AND s.sid = :sid -- replace with ID from above
  AND sq.inst_id = s.inst_id

There might be multiple Ids & instance Ids returned. So it's up to a users' choice on how to use this data in a web interface etc.


Why is server configuration 'min server memory' configured at 0, but running at 16 (sometimes 8)? The instances use the default value. 0 is the default and we don't change it, but apparently something changes it when SQL Server starts

When you leave 'Min Server Memory' to default value of 0 this does not means that Lowest possible value is 0. The lowest possible value would be 16 MB. This is documented in This Blog.msdn

For SQL Server 2008 R2

Sp_configureOption   DefaultSetting     LowesPpossibleValue     HighestPossiblevalue

Min server memory          0                 16                Less Than Max server memory

For SQL Server 2012

Sp_configureOption   DefaultSetting     LowesPpossibleValue     HighestPossiblevalue

Min server memory          0                 16                Less Than Max server memory


It may or may not be faster, depending on your data distribution and the queries you execute.  What it will be is better.

Normalisation isn't a goal in itself.  It's a series of processes one applies to remove potential errors while changing data.  The output is a schema which is resilient to changing data.  Without normalisation you leave yourself open to inconsistent data.  It really doesn't matter how fast your site runs; if it's wrong it's wrong and it's no less wrong by being wrong quickly.

Separated values could potentially be faster if your current queries retrieve too much information because it is all bundled up together in CSV columns.  It will definitely be faster if, down the line, you discover you have to join to another table on one value currently embedded in a CSV.  At that point your performance will fall off a cliff.


Why Azure SQL Server table used space is too high?

Using bigger data types
These columns in dbo.Table1 are varchar(50):

[ID]
[C_ID]

In dbo.Table2, they are nvarchar(32) and nvarchar(64).
The added "n" makes each character take up twice as much space as the varchar version.  It's possible the same thing is going on with the FK column and P_COUNT, but I don't see those defined on the original table, so I can't confirm.
There are a couple of datetime2(7) columns changed to datetime, but these have the same storage cost, so that shouldn't make a difference.
If you get your data types back to what they were in the original table, you should see a pretty big reduction in space used.  Note that you will need to REBUILD the clustered index (or just drop and reload the table) after making the data types changes.
Fragmentation?
It's possible that fragmentation is contributing to the additional size of dbo.Table2.  If the size is still significantly different after fixing the data types and recreating the table, you could try loading the table with ORDER BY [ID] OPTION (MAXDOP 1) to limit fragmentation in the target table.


Backup a database with a huge number of tables

You should really increase max_locks_per_transaction. As specified in http://www.postgresql.org/docs/9.0/static/runtime-config-locks.html , changing this parameter may require to also change the System V shared memory. You have to increase that value also. In OS X this can be done as explained in http://www.postgresql.org/docs/9.0/static/kernel-resources.html#SYSVIPC (look for OS X section). I think you should really change at least shmmax.


Installation of Oracle Instant Client on an Ubuntu 64 machine

Easily solved:

export ORACLE_HOME=/usr/lib/oracle/12.1/client64
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

Stick those lines in your ~/.bash_profile if you want them to persist for a given user, or in /etc/bashrc to persist for all users.


How to copy table content before deleting to flat file using BCP

As scsimon has pointed out, you can take advantage of OUTPUT clause instead of using a flat file.

CREATE TABLE MyTable(id int identity, foo int);
CREATE TABLE MyTableDeleted(id int, foo int);

INSERT INTO MyTable VALUES
(100),(200),(300),(400),(500);

DELETE FROM
    MyTable
OUTPUT
    DELETED.* INTO MyTableDeleted
WHERE
    foo <= 300;

SELECT * FROM MyTable;

id | foo
-: | --:
 4 | 400
 5 | 500

SELECT * FROM MyTableDeleted;

id | foo
-: | --:
 1 | 100
 2 | 200
 3 | 300


Combine 2 SQLite result sets to calculate percentage

A fellow redditor provided me with the assistance to answer my question.  The following is the end result that did what I needed:

select t.location, printf("%.3f", sum(st.456Clicks))/printf("%.3f", sum(st.123Clicks)) as percentage
from (
    select email, count(*) as 123Clicks, 0 as 456Clicks
    from table
    where test = "123"
    group by email

    union all

    select email, 0 as 123Clicks, count(*) as 456Clicks
    from table
    where test = "456" and "Clicked?" = "Yes"
    group by email
) st
join table t
on t.email = st.email
where t."Clicked?" = "Yes" and t.test = "123"
group by t.location
order by percentage DESC


First, we need to make a few minor adjustments to the original code:

It does not make sense to have a permission value of 0 since 0 means "no permissions".
The "BitNum" is not the direct value that you use in the POWER function. If you need a "bit" value of 1, that comes from raising 2 to the power of 0. So you need to subtract 1 from the "BitNum" for use in the POWER function.

With those two changes in mind, the following changes to the original query give you the correct initial result set:

DECLARE @T TABLE (id SMALLINT IDENTITY(1, 1), BitMask TINYINT);
INSERT INTO @T (BitMask) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

SELECT  t.id, t.BitMask, bm.BitNum, bm.Permission
FROM    @T t
OUTER APPLY (
  SELECT * FROM (VALUES
    (1, 'Can Y'),
    (2, 'Can Z')
  ) bm(BitNum, Permission)
  WHERE t.BitMask & POWER(2, bm.BitNum - 1) <> 0
) bm

And that query can be further reduced / simplified to be a simple LEFT JOIN as follows:

SELECT  t.id, t.BitMask, bm.BitNum, bm.Permission
FROM    @T t
LEFT JOIN (VALUES
    (1, 'Can Y'),
    (2, 'Can Z')
          ) bm(BitNum, Permission)
  ON t.BitMask & POWER(2, bm.BitNum - 1) <> 0

Results (12 rows):

id  BitMask BitNum  Permission
1   0       NULL    NULL
2   1       1       Can Y
3   2       2       Can Z
4   3       1       Can Y
4   3       2       Can Z
5   4       NULL    NULL
6   5       1       Can Y
7   6       2       Can Z
8   7       1       Can Y
8   7       2       Can Z
9   8       NULL    NULL
10  9       1       Can Y

Next, now that we have the proper base query, you cannot simply add an APPLY since your original query had each permission as a separate row but now you want them grouped into a single row per "BitMask". Hence you need to restructure the request to be the following (or something similar):

SELECT   t.id, t.BitMask, PermissionList =
(
  SELECT PermissionList = STUFF(
     (
       SELECT  '; ' + bm.Permission
       FROM   (VALUES
                    (1, 'Can Y'),
                    (2, 'Can Z')
              ) bm(BitNum, Permission)
        WHERE  t.BitMask & POWER(2, bm.BitNum - 1) <> 0
        ORDER BY bm.BitNum
        FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 2, ''
   )
)
FROM @T t
GROUP BY t.id, t.BitMask;

Results (10 rows):

id  BitMask     PermissionList
1   0           NULL
2   1           Can Y
3   2           Can Z
4   3           Can Y; Can Z
5   4           NULL
6   5           Can Y
7   6           Can Z
8   7           Can Y; Can Z
9   8           NULL
10  9           Can Y

You also have the option of using SQLCLR to create a User-Defined Aggregate (UDA) that can do this type of String.Join() operation. And an Aggregate function such as this already exists in the SQL# library (which I am the author of, but this function is available in the Free version), though it is hard-coded to use a comma (and no spaces) as the delimiter, and returns an empty string instead of NULL if there are no matches. But, it does make for a much more readable query:

SELECT  t.id, t.BitMask, SQL#.Agg_Join(bm.Permission) AS [PermissionList]
FROM    @T t
LEFT JOIN (VALUES
    (1, 'Can Y'),
    (2, 'Can Z')
          ) bm(BitNum, Permission)
  ON t.BitMask & POWER(2, bm.BitNum - 1) <> 0
GROUP BY t.id, t.BitMask;

This is not to say that using a SQLCLR UDA is necessarily the better choice, I am just pointing out that it is a choice, and depending on the specific requirements, might be better.

OR, starting in SQL Server 2017, there is a built-in aggregate function, STRING_AGG, that can handle this.

A slightly different way of testing the Bit value against the Bitmask is to compare the bit-wise AND operation of them to the Bit value itself instead of against <> 0:

DECLARE @T TABLE (id SMALLINT IDENTITY(1, 1), BitMask TINYINT);
INSERT INTO @T (BitMask) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

 -- based on "improved" query
SELECT   t.id, t.BitMask, [PermissionList] =
(
  SELECT [PermissionList] = STUFF(
     (
       SELECT  '; ' + bm.Permission
       FROM   (VALUES
                    (0, 'Default'),
                    (1, 'Can Y'),
                    (2, 'Can Z')
              ) bm(BitNum, Permission)
        WHERE  t.BitMask & POWER(2, bm.BitNum - 1) = POWER(2, bm.BitNum - 1)
        ORDER BY bm.BitNum
        FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 2, ''
   )
)
FROM @T t
GROUP BY t.id, t.BitMask;

This gets you a little closer to using 0 as a value, but then you have the problem of that value being implicitly in all records. The results of the above (notice that the ON condition was changed AND I added the 0 record back in):

id  BitMask     PermissionList
1   0           Default
2   1           Default; Can Y
3   2           Default; Can Z
4   3           Default; Can Y; Can Z
5   4           Default
6   5           Default; Can Y
7   6           Default; Can Z
8   7           Default; Can Y; Can Z
9   8           Default
10  9           Default; Can Y


Service Broker and procedure which contains Execute as

Answer is here:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/95f5ee43-d3b2-4a2d-bdd2-6fb0aed56458/service-broker-and-procedure-which-contains-execute-as?forum=sqlservicebroker&prof=required
In short: I perform in the context of the current database and treated in a nearby base. And it requires action from the links above.


What's the correct behavior for mongoDB when mongo hosts in two DC can't talk to each other

In this strange situation where somehow mongod processes are network partitioned (between DC's) but application nodes are not. DC2's mongod nodes stays on "SECONDARY" status.

So, YES, DC2 applications still keep reading from DC2 mongod nodes even if data is not updated from primary.

BUT, in this layout your PRIMARY (at DC1) won't stay as primary if there is network partition between DC1 and DC2, because you have four voting nodes and after loosing two votes (DC2) there is no majority.

You need arbiter and it should be located at DC3. There should never be 'even' vote count in replica set.


Multiple databases or one large database?

Do the data marts need to have any of different schemas or different backup and recovery strategies? These would justify the separate databases.

Having these data marts has disadvantages:

query performance degradation
logical inconsistency

difficulty to enforce certain constraints between data-marts (unique, FK)
duplication of lookup data in each data mart, followed by drift of values

(globally) inconsistent restores
operational overhead
HA/DR

Your post does not provide the reasoning why data marts were chosen, so I cannot judge what advantages were leveraged or expected.

For a more thorough discussion see Multi-Tenant Data Architecture.

If you want performant queries on aggregated data from all data-marts, why not do just that? Aggregate the data-marts into a single DW, powered by columnstores, and enjoy performance.


How to move a fairly large tsql table from one server to another server?

A couple of feasible options, in order of my preference:
Option 1

Create a new, empty database locally - maybe make the log file large enough, at least temporarily, to accommodate the entire set of data you are moving without growth
Use SELECT INTO, the Import/Export "Wizard", or Red Gate SQL Data Compare to copy this table to the new database (note that if you use SELECT INTO you'll have to create any indexes, constraints etc. manually)
Back up the new database, and send that .bak over (note that the log may be bigger than you expect due to the initial data movement)

Option 2

Back up your database locally
Restore as a copy
Drop everything but this one table
Back up the copy, and send that .bak over


Why not just use the SQLNCLI10 provider? Are you required to use MSDASQL?

Here is a sp_addlinkedserver script adding a linked server named ServerST that points to ServerAR.

EXEC master.dbo.sp_addlinkedserver @server = N'ServerST', @srvproduct=N'ServerST',
    @provider=N'SQLNCLI10', @datasrc=N'ServerAR'

Update 19/082014

Download SQL Query Stress Tool to compare performance of both SQL Servers, ServerST and ServerAR.

Scripting out your existing Linked Server

This will also include any specially defined parameters such as collation and time out configurations.


Fast Backup and Restore of Growing Databases

Concerning PostgreSQL, I can think of two options:

Low level solution:
Use a storage system or a file system that supports snapshots and take a snapshot before the upgrade.
After the test, either discard the snapshot or restore to it.

Database level solution:
Build up a streaming replication standby database and stop replication before you upgrade.
If the test is fine, discard the standby. If the test fails, discard the master database, promote the standby and continue using that.


MariaDB Galera Cluster without HAProxy, MYSQL Proxy, etc

You should use some kind of load-balancing solution, at least for these main reasons:

To equally balance the load between the nodes.
To check availability of the nodes and make it transparent as possible to your app.

If you use an HAproxy server you should have it replicated, so it's not a single point of failure, e.g. you can have two HAproxies in active/passive with a VIP* managed by keepalived, so if one dies, the other one starts taking the connections.

As you say, there are other options, as having local MySQL proxies in your application servers, or a pool of connections with health checks in your app backend.

If anyway you don't want to deploy any proxy, you can try to have a VIP (or some of them) managed by keepalived directly in your nodes, but with this solution it'd be more difficult to have fair balancing between the nodes and probably you wouldn't benefit so much of the cluster.

The best solution probably depends on the kind of load your cluster is going to have.

* VIP - Virtual IP. In keepalived terms it's an IP that is managed by keepalived. It's initially configured in the "active" host and if it dies, or some of its checks fail, or it's preempted by any other reason, then the keepalived daemons ensure that the IP is down in the failing host and is configured in the "passive" one.


This trigger runs in all cases even the the EXITS is not true!

The condition will always be true since this trigger fires after the record is inserted. FOR is synonymous with AFTER for TRIGGERS, thus the EXISTS would never not be true.

  FOR | AFTER

  Specifies that the DML trigger is fired only when
  all operations specified in the triggering SQL statement have executed
  successfully. All referential cascade actions and constraint checks
  also must succeed before this trigger fires.

  AFTER is the default when FOR is the only keyword specified.

When you say it runs do you mean the trigger fires, or that the error is always raised? I'd expect both, as explained above.

It seems like you are trying to do error handling for an INSERT in which I'd do it in the procedure or code block that you are doing the INSERT via a TRY / CATCH block, or just add a constraint on the table to prevent this type of duplicate insertion. Erland Sommarskog has an extensive blog on error handling that's very helpful. Here's a basic framework via one of this posts, but be sure to read it!

Here is a stored procedure that showcases how you should work with errors and transactions.

CREATE PROCEDURE insert_data @a int, @b int AS
   SET XACT_ABORT, NOCOUNT ON
   BEGIN TRY
      BEGIN TRANSACTION
      INSERT sometable(a, b) VALUES (@a, @b)
      INSERT sometable(a, b) VALUES (@b, @a)
      COMMIT TRANSACTION
   END TRY
   BEGIN CATCH
      IF @@trancount > 0 ROLLBACK TRANSACTION
      DECLARE @msg nvarchar(2048) = error_message()
      RAISERROR (@msg, 16, 1)
      RETURN 55555
   END CATCH


How to switch from MariaDB to MySQL in WAMP server?

To pick MySQL or MariaDB, right click on the wampmanager icon in the system tray and you should see this this menu

Please Select Components like I did please check the 2nd Screenshot


I have Fragmented Index on a empty table!

Fragmentation values are meaningless for small tables.
An empty (or emptied) table is small.

Small is determined by how many 8k data pages are allocated to it.

For example, see this question: Why is my database still fragmented after I rebuilt and reindexed everything?


Failed to attach SQL Express DB, Could not continue scan with NOLOCK

There have been other cases that have shown that this error can happen when trying to attach a corrupted database.

Instead of detaching the database and sending MDF/LDF files, I suggest asking for a proper backup and attempt to restore it. This is a much safer way to copy a database. Also, detaching a database that is suspect or corrupt is especially dangerous, and absolutely the last thing you ever want to do or advise a client to do. Hopefully the client has backups dating back to before the corruption occurred (and this is why we keep more than just the last backup, and why we test our backups frequently to ensure they can be restored).

To avoid all the upgrade steps, which may be what is causing this to fail, another option would be to set up an instance of SQL Server that matches the version of SQL Server that the client has (SQL Server 2008), because attaching it to a newer version makes real, irreversible changes to the database. This will avoid changing the database and skip the upgrade steps, but is unlikely to allow the database to be attached or restored if there is, in fact, corruption.

As an aside, in general, don't use the SSMS UI to attach a database... write a CREATE DATABASE ... FOR ATTACH; command in a query editor window. The UI leaves a lot to be desired.


SQL Server - Anyone use SUMA, trace flag 8048, or trace flag 8015?

This is an awesome post.

To answer your final question, I'd speculate that your answer is "yes".

That said, I probably would have pursued soft numa before resorting to the trace flags.  I think you are right about the numa node allocation and that's could be at the root of your problem.  Via soft numa, you could scale out the requests, depending on your count of numa nodes (4?) - to 4, if that's the correct number, and then assign, via ip address, each host to a specific numa node, in addition to that, I'd disable hyper threading.  Combined, the issue would likely decrease, however, it would do so at the cost of fewer schedulers.

On a seperate thought, I'd look at forced parameterization - the fact that your load is driving your CPU so high is very interesting and it may be worth looking into that.

Lastly, on multi-numa node systems, I typically have the output of the following queries dumping to a table every N seconds.  Makes for some interesting analysis when workload changes or trace flags are implemented:

SELECT getdate() as poll_time, node_id, node_state_desc, memory_node_id, online_scheduler_count, active_worker_count, avg_load_balance, idle_scheduler_count
FROM sys.dm_os_nodes WITH (NOLOCK)
WHERE node_state_desc <> N'ONLINE DAC'

and

SELECT top 10 getdate() as sample_poll, wait_type, count (*)
FROM sys.dm_os_waiting_tasks
WHERE [wait_type] NOT IN
('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK',
'SQLTRACE_BUFFER_FLUSH','WAITFOR', 'BROKER_TASK_STOP',
'BROKER_RECEIVE_WAITFOR', 'OLEDB','CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT' )
GROUP BY wait_type
ORDER BY COUNT (*) DESC


Creating Duplicate Table in SQL SERVER 2014

Explicitly named constraints must be uniquely named within a database.

If they are not unique, you'll see errors like this:

  Msg 2714, Level 16, State 5, Line 10
  There is already an object named 'PK_SomeTable' in the database.
  Msg 1750, Level 16, State 0, Line 10
  Could not create constraint. See previous errors.

This is especially pertinent for creating #temp tables.  Never specify the name of a constraint when creating a #temp table, since concurrent executions of the code will fail with the above error.

Take for example:

CREATE TABLE dbo.SomeTable
(
    ID int NOT NULL
        CONSTRAINT PK_SomeTable
        PRIMARY KEY CLUSTERED
);

CREATE TABLE dbo.SomeTable2
(
    ID int NOT NULL
        CONSTRAINT PK_SomeTable
        PRIMARY KEY CLUSTERED
);

The command fails because PK_SomeTable is defined twice, once in each table.  However, if we re-code the 2nd statement:

CREATE TABLE dbo.SomeTable2
(
    ID int NOT NULL
        CONSTRAINT PK_SomeTable2
        PRIMARY KEY CLUSTERED
);

Now, it works.

For #temp tables, you'd use the following construct:

CREATE TABLE #SomeTable
(
    ID int NOT NULL
        PRIMARY KEY CLUSTERED
);


Efficient way to move 170 GB table from production to another server

I would use:

1) backup (copy-only) & restore the database on the same server to make this db available locally -> copy records into the other database. No option if the database itself is too large.

2) use bcp out/in. Use QueryOut if  you want to specify the records to be exported yourself (partition on PK value)


Difference between joining one table twice times and operator OR

I've set up the below sample: (There is a link at the end of the answer)

create table orders (orderId int primary key, creatorId int, editorId int);
create table users  (userId int primary key, name varchar(10));
insert into users values (1, 'A'),(2, 'B'),(3, 'C'),(4,'D');
insert into orders values
(1, 1, 2), (2, 1, 3), (3, null, 4), (4, 2, null);
GO

First let me have a look at which rows are returned using a single JOIN for both conditions.

select     *
from       orders
inner join users
on         creatorId = userId;
GO

orderId | creatorId | editorId | userId | name
------: | --------: | -------: | -----: | :---
      1 |         1 |        2 |      1 | A
      2 |         1 |        3 |      1 | A
      4 |         2 |     null |      2 | B

select     *
from       orders
inner join users
on         editorId = userId;
GO

orderId | creatorId | editorId | userId | name
------: | --------: | -------: | -----: | :---
      1 |         1 |        2 |      2 | B
      2 |         1 |        3 |      3 | C
      3 |      null |        4 |      4 | D

As you can see orders 1,2,4 match firts condition: creatorId = userId and orders 1,2,3 match the second one: editorId = userId

This should give you one idea about what will happen using OR and joining twice table users with editorId and creatorId.

Ok, first we will try using OR:

select     *
from       orders
inner join users
on         editorId = userId
or         creatorId = userId;
GO

orderId | creatorId | editorId | userId | name
------: | --------: | -------: | -----: | :---
      1 |         1 |        2 |      1 | A
      2 |         1 |        3 |      1 | A
      1 |         1 |        2 |      2 | B
      4 |         2 |     null |      2 | B
      2 |         1 |        3 |      3 | C
      3 |      null |        4 |      4 | D

That is, it returns orders 1,2,3 plus orders 1,2,4, all the records returned by first single query plus all records returned by the second one.

But what happens when you join twice users table:

select     *
from       orders
inner join users u1
on         editorId = u1.userId
inner join users u2
on         creatorId = u2.userId;
GO

orderId | creatorId | editorId | userId | name | userId | name
------: | --------: | -------: | -----: | :--- | -----: | :---
      1 |         1 |        2 |      2 | B    |      1 | A
      2 |         1 |        3 |      3 | C    |      1 | A

Hey, only orders 1,2 are there. If you remember the single queries, which returned rows are equals in both results? 1 and 2, and that's what you asked for: Give me the records where creatorId match a user AND editorId match a user too.

Humm, ok, what happens if I use AND instead of OR:

select     *
from       orders
inner join users
on         editorId = userId
AND        creatorId = userId;
GO

orderId | creatorId | editorId | userId | name
------: | --------: | -------: | -----: | :---

No rows are returned, ok what have we asked here?

Give me the rows where one editorId = creatorId.

select     *
from       orders
inner join users
on         creatorId = userId
UNION
select     *
from       orders
inner join users
on         editorId = userId;
GO

  Ok, now with UNION:

orderId | creatorId | editorId | userId | name
------: | --------: | -------: | -----: | :---
      1 |         1 |        2 |      1 | A
      1 |         1 |        2 |      2 | B
      2 |         1 |        3 |      1 | A
      2 |         1 |        3 |      3 | C
      3 |      null |        4 |      4 | D
      4 |         2 |     null |      2 | B

What was the question: Give all records that match first condition PLUS all records that match second condition, that is: orders 1,2,3 + orders 1,2,4

Using EXCEPT condition:

select     orderId, creatorId, editorId, userId, name
from       orders
inner join users
on         editorId = userId
or         creatorId = userId
EXCEPT
select     orderId, creatorId, editorId, u1.userId, u1.name
from       orders
inner join users u1
on         editorId = u1.userId
inner join users u2
on         creatorId = u2.userId;
GO

orderId | creatorId | editorId | userId | name
------: | --------: | -------: | -----: | :---
      1 |         1 |        2 |      1 | A
      2 |         1 |        3 |      1 | A
      4 |         2 |     null |      2 | B
      3 |      null |        4 |      4 | D

Question: Give all records returned by the query using OR: Orders: 1,2,1,4,2,3 except those records returned joining twice users table, Orders: 1, 2

Well now the final point on your question:

  which query is right?

I don't know. It depends on what is the desired result.

Please, tell us what are you trying to accomplish with your query.

Ok, your last comment clarify your question:

  My target is getting all data that userId=creatorId or userId=editorId without duplicating any orders

The easiest way is to use DISTINCT:

select     distinct orderId, editorId, creatorId
from       orders
inner join users
on         editorId = userId
or         creatorId = userId;
GO

orderId | editorId | creatorId
------: | -------: | --------:
      1 |        2 |         1
      2 |        3 |         1
      3 |        4 |      null
      4 |     null |         2

Or you can use a LEFT JOIN if you want to add information of users table.

select     orderId, creatorId, u2.Name as creatorName, editorId, u1.Name as editorName
from       orders
left  join users u1
on         editorId = u1.userId
left  join users u2
on         creatorId = u2.userId;
GO

orderId | creatorId | creatorName | editorId | editorName
------: | --------: | :---------- | -------: | :---------
      1 |         1 | A           |        2 | B
      2 |         1 | A           |        3 | C
      3 |      null | null        |        4 | D
      4 |         2 | B           |     null | null

dbfiddle here


How do flush innodb table, copy files, then unlock tables from Windows bat file?

Your problem is to maintain two sessions

Session #1 : Lock the table
Session #2 : Copy .ibd file

Note the Following

Session #1 must remain alive in order to hold the lock
Session #1 must remain alive long enough for Session #2 to copy the .ibd file

Steps to Perform

Session #1 sleeps for 30 seconds after issuing the lock
Pause 5 seconds to give Session #1 time to lock the table
Session #2 copies the .ibd file
When Session #1  terminates, the lock is implicitly released (unlocked)

This gives Session #2 25 seconds to copy the .ibd file

Here is the Windows BAT file

@echo off
rem
rem Setup Path to MySQL Folders
rem
set BASE_FOLDER="C:\Program Files\MySQL\MySQL Server 5.6"
set DATADIR=%BASE_FOLDER%\data
rem
rem Setup Path to .ibd file and Destination Folder
rem
set COPY_FROM_DB=mydata
set TABLE_TO_COPY=mytable
set IBD_TO_COPY=%DATADIR%\%COPY_FROM_DB%\%TABLE_TO_COPY%.ibd
rem
rem Setup Destination Folder
rem
set TARGET_FOLDER=D:\BackupFolder
rem
rem Setup MySQL Username and Password
rem
set MYSQL_USER=root
set MYSQL_PASS=rootpassword
set MYSQL_CONN=-u%MYSQL_USER% -p%MYSQL_PASS%
rem
rem Setup Session to Lock Table and Hold Lock for 30 seconds in the Background
rem Pause 5 Seconds
rem Perform Physical Copy
rem
set TIME_TO_SLEEP=30
set TIME_TO_PAUSE=5
set SQL=FLUSH TABLES %TABLE_TO_COPY% FOR EXPORT ; DO SLEEP(%TIME_TO_SLEEP%)
start mysql %MYSQL_CONN% -D%COPY_FROM_DB% -ANe"%SQL%"
mysql %MYSQL_CONN% -D%COPY_FROM_DB% -ANe"DO SLEEP(%TIME_TO_PAUSE%)"
copy %IBD_TO_COPY% %TARGET_FOLDER%\.

GIVE IT A TRY !!!

Note : You can change TIME_TO_SLEEP and TIME_TO_PAUSE if it takes more  than 30 Seconds to copy


How much memory are used by perticular session during run time in MySQL 5.1 community version?

MySQL 5.7 (still in development as of this post, but soon to be released) is the first version to include memory profiling.

You have to enable memory profiling on the setup instruments (it is disabled by default) ans then you can check it with this sys view:

You can get results such as:

mysql> select * from sys.memory_by_thread_by_current_bytes limit 5;
+-----------+----------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| thread_id | user           | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+-----------+----------------+--------------------+-------------------+-------------------+-------------------+-----------------+
|         1 | sql/main       |              29333 | 166.02 MiB        | 5.80 KiB          | 131.13 MiB        | 196.00 MiB      |
|        55 | root@localhost |                175 | 1.04 MiB          | 6.09 KiB          | 350.86 KiB        | 67.37 MiB       |
|        58 | root@localhost |                236 | 368.13 KiB        | 1.56 KiB          | 312.05 KiB        | 130.34 MiB      |
|       904 | root@localhost |                 32 | 18.00 KiB         | 576 bytes         | 16.00 KiB         | 6.68 MiB        |
|       970 | root@localhost |                 12 | 16.80 KiB         | 1.40 KiB          | 16.00 KiB         | 1.20 MiB        |
+-----------+----------------+--------------------+-------------------+-------------------+-------------------+-----------------+

Before 5.6, you had to relay on external tools that you may not want to run in production. @rene's recomendation on profiling session status variables could be a better bet.


Postgresql: How do full_page_writes help prevent data loss?

Many (most?) redo records actually need the block to be in some reasonable state in order to replay into it.

Consider:

Not all WAL records are row-level.  Say you read and replay a WAL record that says "I defragmented this block according to the usual rule of defragmenting blocks".  This would be a deterministic operation given the correct starting state of the block, but given a mixture of blocks states it is not deterministic and so cannot be replayed safely.  It might be possible to enumerate which WAL record types are of this type, and only have those types lead to FPW, but that seems brittle.
If a block did suffer a torn write, I for one would not want to rely on all the bytes in that block being exactly either the old state or new state, in precisely two contiguous chunks.
If the WAL could not be read entirely to the end (and how would one ever know if it was?) then there may be some blocks that have "new" data which was in fact "future" data, for a future which no longer exists.  In theory this should never happen, as the WAL was synced to disk before the block data was released.  But in the nanoseconds when a crash is evolving but has not yet manifested, who knows what could happen?  Better to lose a few transactions that were reported to have been committed, then to corrupt the system entirely.


Does MAXDOP affect the number of tempdb data files I should create?

I wouldn't add single additional files, I would at least go up by 2 or 4 each time. Not many operations will work better at cores = 5 than cores = 4. I don't think you'll get much improvement when, faced with contention, you go from 4 files to 5. I would probably go up in groups of 4 (except when you have 6-core processors, for example, and want to test limiting SQL Server to exactly 6 cores), but by 16 if you still have contention I would have to wonder if this is really getting anywhere near solving the problem - especially because your best bang for the buck is going to be placing each of those files on its own set of spindles; highly unlikely you have this many completely independent disks.

As for the MAXDOP effect, I don't think you should think about MAXDOP that way. Unless you use affinity and prevent SQL Server from even seeing the other 6 cores, this doesn't mean that all queries will use the same 6 cores. So in theory, one query could use CPUs 1-6, and the other could use 7-12, and if contention could be relieved by letting each CPU access its own file (and it actually worked out perfectly), then certain concurrent ops could benefit from 12.

Another very easy way to solve tempdb contention problems is to throw a couple of SSD drives in the box (format one to 80%, fill it with a single pre-sized tempdb file, and use the other drive as standby - SSD lifespans can vary, but keeping a chunk unallocated can help extend that life quite a bit). This is even supported in a cluster as of SQL Server 2012. Takes a lot of thinking and effort out of trying to squeeze performance out of tempdb when slow storage is always going to be a bottleneck no matter how many files you try to configure.

Some background reading that might be useful, especially if you're stuck with old-fashioned spinny disks for tempdb:

http://www.sqlskills.com/blogs/paul/tempdb-configuration-survey-results-and-advice/
http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/
http://www.sqlskills.com/blogs/paul/misconceptions-around-tf-1118/
http://www.simple-talk.com/sql/database-administration/optimizing-tempdb-configuration-with-sql-server-2012-extended-events/
http://www.sqlservercentral.com/blogs/robert_davis/2010/03/05/Breaking-Down-TempDB-Contention/


For 768M RAM server is 617M allocation in my.cnf good or bad?

This depends on the amount of MySQL Data and what storage engines your are using
When it comes to MyISAM and InnoDB, they cache differently. I wrote a post about this back on April 14, 2011.
Since your machine only has 768M of RAM you have to do a nice balancing act depending on which storage engine you are using the most.
All MyISAM
Please run this query
Query #1
SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;

This query will tell you how big a MyISAM Key Buffer you data needs in MB. Of course, the amount installed RAM takes precedence, so use common sense. You should run this query:
Query #2
SELECT
    DataSize/POWER(1024,2) DataMB,
    IndexSize/POWER(1024,2) IndexMB,
    (DataSize/IndexSize) DataToIdexRatio
FROM
(
    SELECT SUM(data_length) DataSize,SUM(index_length) IndexSize
    FROM information_schema.tables
    WHERE engine='MyISAM' AND
    table_schema NOT IN ('information_schema','mysql')
) A;

If the DataToIndexRatio is less than 3, look over all the indexes. You may have indexes that are too big or just too many indexes. If you remove all unused indexes, this will raise the DataToIndexRatio and then you can run Query #1, and get a smaller recommendation. Again used common sense since you only have 768M. Personally, I would not exceed 256M. Therefore, you should choose either 256M or the result of Query #1, whichever is smaller.
If you have no InnoDB Data whatsoever, you added this to my.cnf (my.ini)
[mysqld]
skip-innodb

and restart mysql. That way, mysqld will startup faster because it not have to check for InnoDB crash recovery.
You mentioned your app is write intensive. A mix of INSERTs, UPDATEs, and DELETE will cause table and index fragmentation in a hurry.If this is the case, you need to convert the row format of the MYiSAM tables to Fixed. Why?
The book MySQL Database Design and Tuning recommends using ROW_FORMAT=FIXED on pages 72,73. This will internally convert all VARCHAR fields to CHAR. It will make the MyISAM table larger, but executed SELECTs against it will be much faster. I can personally attest to this. I once had a table that was 1.9GB. I changed the format with ALTER TABLE tblname ROW_FORMAT=FIXED. The table ended up 3.7GB. The speed of the SELECTs against it was 20-25% faster without improving or changing anything else.
All InnoDB
Please run this query
Query #3
SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 2 PowerOf1024) B;

This query will tell you how big an InnoDB Buffer Pool you data and indexes need in MB. Of course, the amount installed RAM takes precedence, so use common sense. You should run Query #2 and apply the same DataToIndexRatio rule even more strictly because secondary indexes in InnoDB tend to be more bloated than their MyISAM counterparts.
If you are running MySQL 5.5, please tune InnoDB for Multiple CPU engagement:

Possible to make MySQL use more than one core?
About single threaded versus multithreaded databases performance
Multi cores and MySQL Performance

Because you only have 768MB of RAM, I again recommend 256MB for the innodb_buffer_pool_size or the result of Query #3, whichever is smaller.
You should resize the InnoDB Log Buffers. For example, if the innodb_buffer_pool_size you choose is 256M, you need to resize the log files to 25% of that number, 64M like this:
STEP 01) Shutdown mysql

service mysql stop for Linux
net stop mysql for Windows

STEP 02) Delete ib_logfile0 and ib_logfile1
STEP 03) Add this to my.cnf (my.ini)
[mysqld]
innodb_log_file_size = 64M

STEP 04) Startup mysql (log files are recreated to the new size)

service mysql start for Linux
net start mysql for Windows

InnoDB and MyISAM Mixed
I would apply the above rules and split up 128MB for key_buffer_size and 128MB for innodb_buffer_pool size.
SUMMARY
I choose 256M because regardless of the Storage Engine Mix you have, you have a write intensive app. You will need to do good caching for InnoDB as well as MyISAM indexes.
On the flip side, I only set aside 256MB of one or both storage engines because the OS will need as much RAM as possible for the following aspects:

Operating System
Query Cache
DB Connections (Buffers Per Connections,Max Connection Count)
File Handles (open_files_limit, innodb_open_files (if using innodb_file_per_table))

Have fun configure all these aspects !!!


Why can't you use the MAX() function?

I did the following

mysql> CREATE TABLE fred (myd date);
Query OK, 0 rows affected (0.31 sec)

mysql> INSERT INTO fred VALUES('2015-10-05');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO fred VALUES('2014-10-05');
Query OK, 1 row affected (0.07 sec)

mysql> SELECT MAX(myd) FROM fred;
+------------+
| MAX(myd)   |
+------------+
| 2015-10-05 |
+------------+
1 row in set (0.00 sec)

MAX(myd) works perfectly. Could you show where "I can't get MAX() to function with this." is failing, along with the error message?


Is there any security risk in having many foreign keys referencing a single table?

This stretches the bounds of credibility.  The only thing I can think of is that by deliberately causing referential integrity errors the attacker is able to harvest the error messages and derive information the underlying schema.

However, if the attacker is able to do that you have bigger worries.


MySQL query to fetch data by lang_id (if not fetch by default lang_id = 0)

What you have is fine.

Another way to write this (assuming that for every page_id, there is always a row with the default lang_id = 0), is to use a LEFT JOIN. There should be minimal if any difference in performance but this is easily extendable for multiple requests:

select d.page_id,
       coalesce(p.lang_id, d.lang_id)       as lang_id,     -- ours or the default
       coalesce(p.page_title, d.page_title) as page_title   -- ours or the default
from lang_pages as d               -- the default
  left join lang_pages as p        -- our language
    on  p.lang_id = <lang_id>
    and p.page_id = d.page_id
where d.lang_id = 0
  and d.page_id = <page_id>             -- one page or
--  and d.page_id in (1, 3, 7, ...)     -- possible other criteria
 ;


Regular Data Space Specification vs Text/Image Filegroup

Regular data is in-row data and it is located on the filegroup listed in Regular Data Space Specification, LOB data is located on a Text/Image Filegroup that can be different from a filegroup where in-row data is located.

You specify these filegroups when create a table that contains LOB data:

CREATE TABLE ...
[ ON { partition_scheme_name ( partition_column_name )
           | filegroup1
           | "default" } ]
    [ TEXTIMAGE_ON { filegroup2 | "default" } ]

Here filegroup1 is for regular data and filegroup2 is for LOB data.

  TEXTIMAGE_ON { filegroup| "default" }

  Indicates that the text, ntext, image, xml, varchar(max),
  nvarchar(max), varbinary(max), and CLR user-defined type columns
  (including geometry and geography) are stored on the specified
  filegroup.

  TEXTIMAGE_ON is not allowed if there are no large value columns in the
  table. TEXTIMAGE_ON cannot be specified if partition_scheme is
  specified. If "default" is specified, or if TEXTIMAGE_ON is not
  specified at all, the large value columns are stored in the default
  filegroup.
  The storage of any large value column data specified in
  CREATE TABLE cannot be subsequently altered.

CREATE TABLE (Transact-SQL)

Note the last cited phrase in bold.
If you have LOB data and think that you can just rebuild your table on another filegroup, your LOB data will not be moved at all.


Mysql update adding subquery field value to field

Please move the SET a.field=(a.field+b.subfield) after ON a.id = b.subid and before WHERE

So, the query should look like this:

UPDATE table a
INNER JOIN (SELECT subid, subfield FROM subtable WHERE ..... ) AS b
ON a.id = b.subid
SET a.field=(a.field+b.subfield)
WHERE ...

Give it a Try !!!


How to trace SQL queries which are crashing SQL Server

Follow the steps outlines in How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server. The remedy action will depend on your findings. You can also read How to Identify Microsoft SQL Server Memory Bottlenecks which is more accessible.

One word of caution though: is unlikely you will find individual queries to blame. Tracking downs memory problems is more subtle than that. Keep in mind that when you're running out of resources and a query throws an out-of-memory error it may well be that the query that throws the error is just the victim, not the culprit.


Prevent users deleting more than a certain number of rows in MySQL

With this kind of granularity, you need to have a rather elaborate setup.
I was thinking of suggesting several things
SUGGESTION #1 : Use Stored Procedure for Specialized DML
Having to micromanage the number of DELETEs per user to mitigate DML should not become a server-centric function for DELETEs against a specific table. That should remain an application-centric function. Therefore, create a Stored Procedure that can manage the number of DELETEs.
SUGGESTION #2 : Use the BLACKHOLE storage engine
Any audit trail of DELETEs using a real table can create a localized disk bottleneck that would be a little clumsy to throttle. If the audit trail was recorded in a BLACKHOLE table, that would significantly reduce any disk I/O issues that would otherwise rear its ugly. If you write to a BLACKHOLE table, where would the audit trail data be written? That's leads up to...
SUGGESTION #3 : Use MySQL Replication
How would MySQL Replication help? Suppose the table is called mydb.audittrail. If the mydb.audittrail uses BLACKHOLE on the Prod Server, it could replicate to a Slave that that has mydb.audittrail as MyISAM. That would be the location of the actual audit data. The only drawback would be added disk I/O for writing audit trail info to the binary logs. You can further reduce that by having the binary logs stored on SSD or a RAM disk.
Please see my answer to Is it normal to use many Triggers?  for further info on this concept of audit trail recording with the BLACKHOLE Storage Engine and MySQL Replication.


How do I monitor PostgreSQL WAL shipping if I'm not using streaming replication?

Here are a few ways:

Compare pg_current_xlog_location() on the primary with pg_last_xlog_replay_location() on the standby.  That will give you the lag in bytes, which might not be very useful for alerting, but it can be useful to chart it.
Monitor pg_last_xact_replay_timestamp() on the standby against the current time.
Have a cron job on the primary periodically modify a value and then check how long it takes to get to the standby.  That's essentially how it used to be done before pg_last_xact_replay_timestamp() became available.


Is SQL_CALC_FOUND_ROWS Safe and Accurate for a Single DB User Environment?

Well, it might not be a complete in depth analysis but, from a developer perspective, I can say it depends on how your server handle the requests and execute the application. To be more clear: synchronously or asynchronously.

If your server handle requests synchronously, than there's nothing to worry. In this case, given two hypothetic users (User A and User B) making requests at the same time, your server will attend only one request first, All the processing for the given hypothetic requests are going to happen in a different moment so there will be no information crossover madness.

But if your application is running in many server instances or in a server that handles requests asynchronously, I wouldn't suggest you to rely the presented method to count rows.

  Even in cases when my assumption is not 100% accurate, for a sake of
  sanity, in your application, I would suggest you to use the good old
  common bulletproof double query solution:

SELECT [fields] FROM [tables] WHERE [filter]

and later our count query...

SELECT count(*) as total FROM [tables] WHERE [filter]

There's no way to go wrong and, as @DTest added, this 2 query approach can be faster in some environments (see the link in his comment).

Oh, and I took a fast read at MySQL documentation related to Information Functions, there it was on a tiny little silly notice:

  Important:
  FOUND_ROWS() is not replicated reliably, and should not be used with
  databases that are to be replicated.

from: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_row-count


How do I determine if a database restore file should be restored to the database based on backupset?

There is an msdb table that contains the restore history:

select  *
from    msdb.dbo.restorehistory rh

For a database, you can look up the date of the last restore.  If you can determine the date of the incoming restore (typically the restore date is part of the file name) you could compare the two.

While this safeguards against overwriting newer restores with older ones, it's not ideal.  There can be a difference between the backup date and the restore date, and newer restores that fall in between will be ignored.

You could improve that if you knew the filename that the last restore came from, but I can't find that information in the msdb tables.


SQL Server asks for 100GB for a dump with 1GB

Yes, it is conceivable. You probably need to shrink the source database files before making the backup. Run sp_helpfile on the source database to see the files and sizes; when you restore from a backup, it will create those files at exactly the same size, though you can move them to different drives/directories.

If the log file or any of the data files are unnecessarily large, you can shrink them, do a new backup, and the restore will use less space. Just beware of performance implications if you get in a continual shrink/grow/shrink process.


Does the concept of candidate key exist only in theory?

As far as I know, no SQL database management system (DBMS) supplies the CANDIDATE KEY keyword as such, but (as I consider that you are suggesting in the question) that does not mean that the notion (or the functionality) of candidate key cannot be configured in a SQL table.
How to represent a candidate key
For example, if

there is no primary declared for the table under consideration, and
a whole set of columns (i.e., one or more) that is configured with a UNIQUE constraint is also fixed with the corresponding NOT NULL constraint(s),

then the designer is, precisely, representing a candidate key.
For example, the following table shows three distinct candidate keys:
CREATE TABLE Foo (
    FooId  INT      NOT NULL,
    Bar    CHAR(30) NOT NULL,
    Baz    DATETIME NOT NULL,
    Qux    INT      NOT NULL,
    Corge  CHAR(25) NOT NULL,
    Grault INT      NOT NULL,
    Garply BIT      NOT NULL,
    Plugh  TEXT     NOT NULL,
    CONSTRAINT Foo_CK1 UNIQUE (FooId),          -- Single-column CANDIDATE KEY
    CONSTRAINT Foo_CK2 UNIQUE (Bar),            -- Single-column CANDIDATE KEY
    CONSTRAINT Foo_CK3 UNIQUE (Baz, Qux, Corge) -- Multi-column  CANDIDATE KEY
);

A candidate key set up in this manner is, as you know, susceptible of being the reference of one or more foreign key constraints.
It is worth to stress the fact that, since SQL and its dialects include the idea of NULL marks, a UNIQUE constraint alone is not sufficient to stand for a candidate key (as expounded in the DDL sample above). This point is particularly significant because the column(s) constrained as a candidate key cannot retain NULL marks, otherwise it could not be deemed a true candidate key (besides, there are reasons to argue that a table enclosing NULL marks in any of its columns cannot be considered a relational table but, yes, that is a different subject).
How does this differ from the CANDIDATE KEY keyword approach?
In this way, if the vendors/developers of a certain SQL DBMS wants to provide the CANDIDATE KEY keyword, then this kind of constraint, apart from the evident uniqueness enforcement, must also ensure the rejection of any attempt to insert NULL marks in relevant column(s), factor that would make it different from the approach combining the UNIQUE and NOT NULL constraint(s).
How to portray an alternate key
If, on the contrary,

a certain set of columns was chosen and defined as the PRIMARY KEY of a given table, and
other set of columns is constrained as UNIQUE, and each of the members of such set is also fixed with a NOT NULL constraint,

then the designer is representing an alternate key (if, a certain table has one or more candidate keys, and one of these is granted the status of primary, then the remaining ones become alternate keys).
For instance, the following table presents one PRIMARY KEY and three ALTERNATE KEYs:
CREATE TABLE Foo (
    FooId  INT,
    Bar    CHAR(30) NOT NULL,
    Baz    DATETIME NOT NULL,
    Qux    INT      NOT NULL,
    Corge  CHAR(25) NOT NULL,
    Grault INT      NOT NULL,
    Garply BIT      NOT NULL,
    Plugh  TEXT     NOT NULL,
    CONSTRAINT Foo_PK  PRIMARY KEY (FooId),           -- Single-column PRIMARY KEY
    CONSTRAINT Foo_AK1 UNIQUE      (Bar),             -- Single-column ALTERNATE KEY
    CONSTRAINT Foo_AK2 UNIQUE      (Baz, Qux, Corge), -- Multi-column  ALTERNATE KEY
    CONSTRAINT Foo_AK3 UNIQUE      (Grault)           -- Single-column ALTERNATE KEY
);

An alternate key put up as demonstrated above can be, evidently, referenced from one or more foreign key constraints.
Using the CANDIDATE KEY keyword when there is already a PRIMARY KEY?
Assuming that there is a DBMS that does provide the CANDIDATE KEY keyword, if a table has a primary key declared, then the creation of a candidate key should be rejected, and said DBMS should as well provide the ALTERNATE KEY keyword to represent one or more alternate keys when applicable.
Illustration of the same table (i) with one candidate key and (ii) with primary key
Yes, at the logical level of abstraction of a database, a candidate key that is established by means of a table-level UNIQUE constraint in conjunction with the applicable column-level NOT NULL counterpart(s), as exemplified as follows:
CREATE TABLE Foo (
    FooId  INT      NOT NULL,
    Bar    CHAR(30) NOT NULL,
    Baz    DATETIME NOT NULL,
    CONSTRAINT Foo_CK UNIQUE (FooId)
);

…would be equivalent to a primary key set up as shown below:
CREATE TABLE Foo (
    FooId  INT,
    Bar    CHAR(30) NOT NULL,
    Baz    DATETIME NOT NULL,
    CONSTRAINT Foo_PK PRIMARY KEY (FooId) -- Single-column PRIMARY KEY, constraint that implies the rejection of NULL marks.
);

This is so because, if a given table has only one candidate key (which, as illustrated before, can be composite, i.e., made up of two or more columns), then it can be considered, automatically, the primary key.
Physical-level support
And, yes, in order to support a UNIQUE constraint, some DBMSs may employ an index whose type is different from the one of the index utilized to sustain a PRIMARY KEY counterpart (e.g., non-clustered vs clustered), but this is a factor that is part of the physical (or internal) level of abstraction (which, by the way, might or might not apply depending of the DBMS of use), therefore it is entirely outside of the scope of the logical level constraints configured for a table (as long as the DBMS guarantees the uniqueness of the values contained in the column[s] involved).


SQL statement to combine main table and child table as columns?

There are two ways that I see you can do this - one would be with a static query the other with dynamic SQL.

Static query (even though you stated you have an unknown number of columns - this is how you would do this).:

select m.id,
  min(case when [key] = 'X' then value else null end) as 'x',
  min(case when [key] = 'y' then value else null end) as 'y',
  min(case when [key] = 'z' then value else null end) as 'z'
from main m
left join child c
  on m.id = c.mainid
group by m.id

see SQL Fiddle with Demo

Dynamic SQL version, this will create the query:

DECLARE @query  AS NVARCHAR(MAX) = ' '
DECLARE @rowcount int
DECLARE @rowNumber int = 0
DECLARE @case as nvarchar(max) = ' '
DECLARE @keyValue as varchar(1)

select distinct [key]
into #temp
from child

-- get the number of unique keys
set @rowcount = (select count(*) from #temp)

while @rowNumber < @rowcount
  begin
    set @keyValue = (select top 1 [key] from #temp)

    set @case
      = @case + 'min(case when [key] = ''' + @keyValue
          + ''' then value else null end) as ''' + @keyValue + ''', '

    if @rowNumber < @rowcount
      begin
        set @rowNumber = @rowNumber + 1

        delete from #temp where [key] = @keyValue
      end
  end

set @query
  = 'select m.id, '+ substring(@case, 1, len(@case) -1) + '
     from main m
     left join child c
       on m.id = c.mainid
     group by m.id'

exec(@query)

see SQL Fiddle with Demo

The static version is portable but will not work with your dynamic requirement.


Defining constraints in `CREATE TABLE` statements

I personally think it's a matter of taste.

The scripts where the constraints are defined through an ALTER statement are a bit more flexible, as you don't need to care about the order of creation (first create all tables, then all PKs, then all FKs).

The scripts with embedded constraints are more "self-contained" however. You don't need to look for other places in the script to find the constraint definitions.

Using camel-case doesn't make any difference as everything will be converted to uppercase anyway (because you did not use " to quote your names - which is a good thing).

Regarding indention and whitespace: that again is completely personal taste. Do it the way you think you can still understand this in 6 months. The most important thing is to be consistent (and maybe document your style somewhere in your project documentation, so that new members can understand and use the same formatting)


Is it possible to speed up this PARTITION BY query?

I think your query uses only the rows where the date is the maximum so it is equivalent to this:

SELECT TOP (1) data.Id, data.Date, data.Statut, data.Value
FROM [DATA].[TimeSerie] AS data
  JOIN @MyStatuts AS s ON data.Statut = s.StatutValue
WHERE data.Id = 'timeserieid'
  AND data.Date = ( SELECT MAX(m.Date)
                    FROM [DATA].[TimeSerie] AS m
                    WHERE m.Id = 'timeserieid'
                      AND m.Date <= '20140424'
                  )
ORDER BY s.StatutOrder ASC ;


Any correlation between tempdb and sysmail?

Yes there is correlation between tempdb and db_mail.
Ref:
https://technet.microsoft.com/en-us/library/ms345368(v=sql.105).aspx
Database mail user service broker which in turn use tempdb.

Service Broker helps developers build asynchronous, loosely coupled
applications in which independent components work together to
accomplish a task. These application components exchange messages that
contain the information that is required to complete the task. Service
Broker explicitly uses tempdb for preserving existing dialog context
that cannot stay in memory. The size is approximately 1 KB per dialog.
Also, Service Broker implicitly uses tempdb by the caching of objects
in the context of query execution, such as work tables that are used
for timer events and background delivered conversations.
Database
Mail, Event Notifications, and Query Notifications implicitly use
Service Broker.


Why MySQL SELECT performance depends on table size?

Eventually I realized that it was some king of caching and probably on Linux level.

After applying # sync && echo 3 | tee /proc/sys/vm/drop_caches followed by mysql> RESET QUERY CACHE; the query time became linear.

Note though, that before this the effect was stable on different queries.


Selecting distinct values by column value

Pretty straight forward:

SELECT * FROM table
WHERE data_type = 'var'
AND keyword NOT IN (SELECT keyword FROM table WHERE data_type <> 'var');


Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction

Resolving deadlocks can be tricky because you need to be knowledgable about all of the different types of objects and locks and how they can prevent each other's sessions from making any progress. Your stack trace can be used to indicate what resources your process was using, but not the survivor process. Some deadlocks have more than two sessions involved. You have several options to obtain the facts so that an investigation can be conducted.

Run DBCC TRACEON (1204, 1222) to enable the trace flags indicated so that the information is saved into the SQL Server Error Log
Use Profiler and select the Deadlock graph event under the Locks section, then recreate the deadlock
Use Extended Events to capture the deadlock if you have SQL Server 2008 R2 or above

There is also third party monitoring software like Spotlight or Idera that can be configured to capture the deadlock graph and send it to you in an email.

Once you have the information about which objects or resources are involved, you then have the challenge of resolving the problem. Since everything in the deadlock graphs and xml are object IDs and hobts, you will need to look them up using object_name(obj_id), selecting from sys.objects or, in the case of a hobt (heap or b-tree) you would select from sys.partitions.

That should help you get started.


Grant PG permissions to not existing tables

Without claiming to address your 'by pattern' requirement, granting privileges on not-yet-extant objects is addressed by Alter Default Privileges

Create schema foo;

Grant Usage On Schema foo to foouser;

Alter Default Privileges In Schema foo
  GRANT SELECT, INSERT, UPDATE, DELETE, Truncate ON TABLES To foouser;

Alter Default Privileges In Schema foo
  Grant EXECUTE on FUNCTIONS To foouser;

Or simply

Alter Default Privileges In Schema foo GRANT All Privileges To foouser;

As @a_horse_with_no_name observes, the schema is the usual way to group objects for privileges. Using schemas for security is much less error prone than using naming patterns, so I think we should recommend you go this way anyway!

foouser only needs to use a dot in their statements:

create table foo.mine ( id int)
select * from foo.mine

PS Why are name-patterns an error-prone way of doing security?

Because you never know whether a year later you're going to

create table food_for_top_secret_party

which matches pattern foo*, and so can be not only read, but also accidentally dropped, by foouser.


SQL server tools to identify query optimisations and missing indexes

how can I utilise SQL Server tools to identify query optimisations, missing indexes etc?

No tool is perfect and you should not rely implementing the suggestions without proper testing your entire business life cycle.

Native tools like DTA are OK, but I have seen many problems with using it. I would not rely on it.

That being said, SQL Server exposes a wealth of information using DMVs and Extended Events.

I would suggest you start with : SQL Server Diagnostic Information Queries - Glenn Berry - choose as per your version of SQL Server.

Also, highly recommend this FREE book from RedGate - Performance Tuning with SQL Server Dynamic Management Views

You have to baseline your servers and then you can compare what is good and bad for your environment.

Remember performance tuning is an art, not science. It will take you some time to digest the resources I have cited and this is how I have learned.

You should be cautious when creating Missing indexes exposed by DMVs

Other blogs that contain a wealth of information are (in no particular order):

SQLSkills.com
BrentOzar.com
SQLPerformance.com
Querrprocessor.com
CSS SQL Server Engineers
SQLServerCentral - Stairway series


Using Same CASE WHEN Conditions For Multiple Query Columns

If all these columns are from the same table, you can use something like this:

    SELECT
        'Authorized' AS StatusCol,
        authTime     AS lastEventTimeCol,
        authUser     AS lastEventUserCol
    FROM test
    WHERE testStatus = 'A'

  UNION ALL

    SELECT
        'Completed',
        cmplTime,
        cmplUser
    FROM test
    WHERE testStatus = 'C'

  UNION ALL

    SELECT
        'In Progress',
        strtTime,
        strtUser
    FROM test
    WHERE testStatus = 'P'

  UNION ALL

    SELECT
        'Cancelled',
        cancTime,
        cancUser
    FROM test
    WHERE testStatus = 'X' ;


What index to add in case of join by two optional fields

Any time you apply a function to a column in a query, you prevent SQL Server from being able to use an index on that column. Also, it may often not be possible for SQL Server to use one of its more powerful join operations: merge and hash joins, which require equijoins - a simple columnA = columnB. For efficient queries, as many terms in the WHERE clause, and ON clauses, as possible should be sargable.

I think what you actually wanted is

SELECT B.X, A.Y
FROM tableA A INNER JOIN tableB B
ON (B.level5 = A.f05 OR B.level5 IS NULL) AND
   (B.level6 = A.f06 OR B.level6 IS NULL)

Then I would expect that a non-clustered index on level5, level6 would do the job:

CREATE NONCLUSTERED INDEX Index_On_B ON tableB (level5, level6)

All your current index is doing is storing just the data from the columns in this query, which will cause less I/O than scanning tableB itself, assuming that there are other columns in tableB.


Can I use transaction logs to rebuild a database?

No. You need a previous image of the a database (ie. a reliable backup) to start from.


Performance of checking for data in related tables

Example 4 has the fewest scans and reads:

Example 1

  SQL Server parse and compile time:
      CPU time = 4 ms, elapsed time = 4 ms.

  SQL Server Execution Times:
      CPU time = 0 ms,  elapsed time = 0 ms.

example1    Id    FirstName
-----------------------------
1           2     Aaron
1           3     John
1           8     Aaron
1           9     John
1           14    Aaron
1           15    John
1           20    Aaron
1           21    John

  (8 row(s) affected)

Table 'Person'.
Scan count 9, logical reads 27, physical reads 0,

Rows                 Executes             StmtText
-------------------- -------------------- ------------------------
8                    1                    select 1 example1

, parent.Id
, parent.FirstName
, parent.LastName
, case when max(child.Id) is null then 0 else 1 end HasChildren
from Person parent
left outer join Person child
on child.ParentId = parent.Id
where (@LastName is null or parent.Las 1           1           0
0       0     |--Compute Scalar(DEFINE:([Expr1005]=(1), [Expr1
8       1          |--Nested Loops(Left Outer Join, OUTER REFE
8       1               |--Clustered Index Scan(OBJECT:([sub].
3       8               |--Stream Aggregate(DEFINE:([Expr1004]
7       8                    |--Clustered Index Scan(OBJECT:([

  (6 row(s) affected)

  SQL Server Execution Times:
      CPU time = 0 ms,  elapsed time = 0 ms.

Example 2

  SQL Server parse and compile time:
      CPU time = 0 ms, elapsed time = 4 ms.

  SQL Server Execution Times:
      CPU time = 0 ms,  elapsed time = 0 ms.

example2  Id   FirstName
----------------------------
2         2    Aaron
2         3    John
2         8    Aaron
2         9    John
2         14   Aaron
2         15   John
2         20   Aaron
2         21   John

  (8 row(s) affected)

Table 'Person'.
Scan count 9, logical reads 27, physical reads 0,

Rows   Executes   StmtText
------ ---------- -----------------------
8      1          select 2 example2

, parent.Id
, parent.FirstName
, parent.LastName
, coalesce(child.hasChildren, 0) HasChildren
from Person parent
left outer join
(
    select distinct parentId, 1 hasChildren
    from Person
    where parentId is not null
) child

 1           1           0           NULL
0     0       |--Compute Scalar(DEFINE:([Expr1007]=(2), [Expr1
8     1            |--Nested Loops(Left Outer Join, OUTER REFE
8     1                 |--Clustered Index Scan(OBJECT:([sub].
3     8                 |--Stream Aggregate(DEFINE:([Expr1006]
0     0                      |--Compute Scalar(DEFINE:([Expr10
7     8                           |--Clustered Index Scan(OBJE

  (7 row(s) affected)

  SQL Server Execution Times:
      CPU time = 0 ms,  elapsed time = 0 ms.

Example 3

SQL Server parse and compile time:
   CPU time = 7 ms, elapsed time = 7 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

example3 Id   FirstName
---- ------ ------------------------
3    2      Aaron
3    3      John
3    8      Aaron
3    9      John
3    14     Aaron
3    15     John
3    20     Aaron
3    21     John

(8 row(s) affected)

Table 'Person'. Scan count 9, logical reads 41, physical reads 0, r

Rows     Executes   StmtText
------   ---------  ------------------
8        1          with parentCTE as (
    select Id, FirstName, LastName
    from person
    where (@LastName is null or LastName like @LastName)
    and (@FirstName is null or FirstName like @FirstName)
)
select 3 example3
, parentCTE.Id
, parentCTE.FirstName
, parentCTE.La 1           1           0           NULL
0     0     |--Compute Scalar(DEFINE:([Expr1011]=(3), [Expr1
8     1          |--Nested Loops(Left Outer Join, OUTER REFE
8     1               |--Clustered Index Scan(OBJECT:([sub].
3     8               |--Stream Aggregate(DEFINE:([Expr1010]
0     0                    |--Compute Scalar(DEFINE:([Expr10
7     8                         |--Nested Loops(Inner Join,
7     8                              |--Clustered Index Scan
7     7                              |--Clustered Index Seek

(9 row(s) affected)

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Example 4

SQL Server parse and compile time:
   CPU time = 3 ms, elapsed time = 3 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

example4 Id  FirstName
---- ----- -------------
4    2     Aaron
4    3     John
4    8     Aaron
4    9     John
4    14    Aaron
4    15    John
4    20    Aaron
4    21    John

(8 row(s) affected)

Table 'Person'. Scan count 3, logical reads 26, physical reads 0,

Rows     Executes   StmtText
-------- ---------- ---------------------
8        1          select 4 example4
, parent.Id
, parent.FirstName
, parent.LastName
, coalesce((select top 1 1 from Person child where child.parentId =
from Person parent
where (@LastName is null or LastName like @LastName)
and (@FirstName  1           1           0           NULL
0       0     |--Compute Scalar(DEFINE:([Expr1002]=(4), [Expr1
8       1          |--Nested Loops(Left Outer Join, PASSTHRU:(
8       1               |--Nested Loops(Left Outer Join, OUTER
8       1               |    |--Clustered Index Scan(OBJECT:([
0       0               |    |--Compute Scalar(DEFINE:([Expr10
3       8               |         |--Top(TOP EXPRESSION:((1)))
3       8               |              |--Clustered Index Scan
0       0               |--Compute Scalar(DEFINE:([Expr1008]=(
3       3                    |--Top(TOP EXPRESSION:((1)))
3       3                         |--Clustered Index Scan(OBJE

(11 row(s) affected)

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

You want the fewest scans possible.  The more times we have to scan the table, the longer it will take.


Using “nolock” hint for the SQL View (MSSQL)

In case you don't know, NOLOCK is equivalent to the transaction isolation level named "READ UNCOMMITTED."  I'd recommend reading this blog post from Paul White to understand some of the data integrity implications of using the NOLOCK hint: The Read Uncommitted Isolation Level

To your question though, you mention this justification for potentially adding the hint:

  Thus, all the reports regarding the dbo.rptdepitemdetailsResearch have a potential to block other transactions (insert/update/delete) while it executes.

I would say not to worry about this unless your users / applications are actually experiencing blocking due to this reporting view.

If there is blocking, you could use the READ UNCOMMITTED isolation level, but note (from Paul's blog post) that it's possible to see totally invalid data in the report.

Another option would be to enable SNAPSHOT ISOLATION in the database:

ALTER DATABASE YourDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON;

And then have any code that queries your view use the SNAPSHOT isolation level:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

This will prevent your reporting query from blocking insert / update queries, and will also make the reporting query accurate to the point in time the query started.

There is overhead to using this option, including increases to the database size and increased usage of TempDB.  So make sure you test accordingly.


This seems to be related to your earlier question about High Disk IO. Have you looked at other items to see what is going on as suggested in my answer there? Wait Stats, Query Information, Etc? I think you should look at everything. And in this case it still looks like your disk queue length on that drive at that one time isn't bad. You need to really look at your IO Latency as I mentioned in that answer. It looks to me like you are still hunting around for an IO issue, but should look at the full set of information as I mention in the answer there to really help you out.

But the question here: This screen is in the Resource Monitor, and the blue line refers to the active time of the drive. So in this case you had a quick spike of higher activity on the K drive and mostly the drive wasn't very active.


The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

The condition that is responsible for the error:
CAST(CONVERT(VARCHAR(10), createdate, 101) AS DATETIME) >=
CAST(CONVERT(VARCHAR(10), STARTDATE, 101) AS DATETIME)
AND
CAST(CONVERT(VARCHAR(10), createdate, 101) AS DATETIME) <=
CAST(CONVERT(VARCHAR(10), ENDDATE, 101) AS DATETIME)

is trying to do this comparison:
CAST(createdate AS DATE) >= CAST(STARTDATE AS DATE)
AND
CAST(createdate AS DATE) <= CAST(ENDDATE AS DATE)

but is using the 101 style code (US, mm/dd/yyyy) to first convert the datetime to varchar(10), effectively chopping off the time part and then converting back to datetime - but without explicitly specifying* a style code for the second cast.
Frankly, I'm surprised that it worked at all. It's quite possible that the various queries and procedures that use such conversions are producing false results, depending on server or client settings. (i.e. converting '2015-12-08 16:00:00' (2015-Dec-08) to '12/08/2015' and then to '2015-08-12 00:00:00' (2015-Aug-12)).
What you can do is to change these conversions to not depend on the client settings. The easiest would be to remove the second cast. But that would also require that  you change the 1st cast to use a sane format (and the responding style code). "Sane" meaning a format that preserves the order of dates, i.e. style codes 102, 112, 120, 126. (Do not use styles 101 or 103 that do not preserve order. That would make the issue much worse.) Examples of codes to use:
-- style code 120 (ODBC canonical):   yyyy-mm-dd hh:mi:ss(24h)
CONVERT(VARCHAR(10), createdate, 120) >=
CONVERT(VARCHAR(10), STARTDATE,  120)
AND
CONVERT(VARCHAR(10), createdate, 120) <=
CONVERT(VARCHAR(10), ENDDATE,    120)

-- style code 126 (ISO8601):   yyyy-mm-ddThh:mi:ss.mmm (no spaces)
CONVERT(VARCHAR(10), createdate, 126) >=
CONVERT(VARCHAR(10), STARTDATE,  126)
AND
CONVERT(VARCHAR(10), createdate, 126) <=
CONVERT(VARCHAR(10), ENDDATE,    126)

-- style code 112 (ISO):   yyyymmdd
CONVERT(VARCHAR(8), createdate, 112) >=
CONVERT(VARCHAR(8), STARTDATE,  112)
AND
CONVERT(VARCHAR(8), createdate, 112) <=
CONVERT(VARCHAR(8), ENDDATE,    112)

But the best in my opinion is not to convert to varchar at all and remove the time part using another way. If you are on 2005 and no DATE type is available, you can use this method to strip the time from a DATETIME:
DATEADD(day, DATEDIFF(day, 0, createdate), 0)
  >= DATEADD(day, DATEDIFF(day, 0, STARTDATE), 0)
AND
DATEADD(day, DATEDIFF(day, 0, createdate), 0)
  <= DATEADD(day, DATEDIFF(day, 0, ENDDATE), 0)

or simplified to (possibly use indexes):
createdate >= DATEADD(day, DATEDIFF(day, 0, STARTDATE), 0)
AND
DATEADD(day, DATEDIFF(day, 0, createdate), 0) <= ENDDATE

And finally the best  (and assuming all these columns are DATETIME) and that your version of SQL Server is 2008 or later (and so has the DATE type) is to change these conversions to cast to DATE so they are self-explanatory of the intention, to either:
CAST(createdate AS DATE) >= CAST(STARTDATE AS DATE)
AND
CAST(createdate AS DATE) <= CAST(ENDDATE AS DATE)

or even better, simplify to (possibly use indexes):
createdate >= CAST(STARTDATE AS DATE)
AND
CAST(createdate AS DATE) <= ENDDATE

What happens when a char or varchar value is implicitly converted to a date/time type, is nicely explained by Aaron Bertrand in the blog post Bad habits to kick : mis-handling date / range queries:

.., the only truly safe formats for date/time literals in SQL Server, at least for DATETIME and SMALLDATETIME, are:

YYYYMMDD
YYYY-MM-DDThh:mm:ss[.nnn]

About the question, why a user gets these errors while another user doesn't, this (thank you Paul White) technet page: Using Date and Time Data, explains:

Using Date and Time Formats
String literal formats affect the presentation of data in applications to users but not the underlying integer storage format in SQL Server. However, SQL Server might interpret a date value in a string literal format, input by an application or user for storage or to a date function, as different dates. The interpretation depends on the combination of string literal format, data type, and runtime SET DATEFORMAT, SET LANGUAGE and default language option settings.


How to add a summary count to this query

You can just replace attendees with a subquery that pre-selects "paid" rows and add the count without aggregating - with a window function:

SELECT DISTINCT a.event_id, a.user_id, u.tags, a.total_event_paid
FROM   users u
JOIN  (
   SELECT *, count(*) OVER (PARTITION BY event_id) AS total_event_paid
   FROM   attendees
   WHERE  status = 'paid'
   ) a ON u.id = a.user_id
WHERE  'crystal:promo' = ANY(u.tags)
ORDER  BY a.event_id DESC;

DISTINCT (or GROUP BY) in the outer SELECT may or may not be needed.

Related:

Best way to get result count before LIMIT was applied