SQL Server 2005 Express supports a single socket with up to four cores.

Each core is mapped to a SQLOS scheduler. There is one VISIBLE ONLINE scheduler per core in the sys.dm_os_schedulers dynamic management view. The following screenshot was taken from an instance of SQL Server 2005 Express running on a machine with a single socket CPU containing four cores:

The general SQLOS model is:

Statements in a client batch are mapped to one or more tasks.
Each task is bound to a worker thread.
A worker thread is bound to a scheduler.

In a serial execution plan, there is just one task, and only one scheduler is used. SQL Server Express Edition only supports serial execution. Therefore, each statement can only use a single core.

Other editions of SQL Server are capable of generating parallel execution plans, where a single statement may generate multiple tasks, which can then run on multiple schedulers concurrently. This is generally limited to operations that read data (not write it).

Before SQL Server 2014, parallel data modifications were limited to index (re)build plans. General INSERT, UPDATE, DELETE, and MERGE statements could only use parallelism on the read side of the execution plan; the data modifications were always serial (using a single task/worker/scheduler).

SQL Server 2014 Enterprise Edition (and equivalent Developer and Evaluation Editions) added the ability to do parallel writes for SELECT INTO statements only. SQL Server 2016 Enterprise Edition further adds parallel writes for some INSERT...SELECT statements.

All this means that SQL Server Express Edition can only use one scheduler (= one core) per client statement batch. Having four cores simply means that four separate statements (from different clients/connections) can be executing concurrently.

If the affinity mask bit is not set for a particular scheduler, Windows may choose which physical execution unit to run the scheduler on, and this may vary over timeslices. If the affinity mask bit is set, the scheduler is bound to a particular execution unit, and Windows cannot move it around.

It is still possible (in the right circumstances) for SQL Server Express to use multiple logical processors to concurrently bulk load a single unindexed heap table. This requires four separate statements (on four separate connections) targeting the same table, and specific conditions must be met. The details are contained in The Data Loading Performance Guide. There is an element of chance as well, in that the separate statements must be bound to different schedulers at connection time by SQL Server. That is a complex topic of its own.

I am not familiar with "MSSync", and the question doesn't state exactly what the task is, but in many cases achieving minimally-logged inserts is more than enough to meet performance objectives. Avoiding sorts on the server side is often an important factor as well. High CPU utilization is often an indication of server-side sorting when loading data. It is often avoidable.

A properly configured minimally-logged bulk load (without sorting) should saturate the server's I/O abilities before it runs out of CPU. Perhaps a follow up question could be asked with details of the exact task being performed. There may be other improvement options as well.


Eclipse data source explorer not showing Inserted table records (Oracle)

Modifications performed by a session are not visible to others until they are commited. Issue a commit after performing these changes.


Should "Teachers" and "Students" be kept in a common "Person" table?

Yes, but the primary key should also include PersonType.
Then in each sub-table (Teachers, Students etc) you can create the same primary key which is also a foreign key on Person. This represents a 1 -> 0..1 relationship. The PersonType field in this table should be constrained to a single value


Maximizing Availability on a Table When Changing Data Type

You didn't say whether this column was part of any of the FKEY or indexes. Assuming it is not, the simplest way to achieve this is to add a new bigint column and update the data there, and then prepare a script to apply the changes to other affected objects. Potentially, the changes could be done in one transaction and have near-zero downtime.


How to quantify rollback events in SQL Server between 2 dates

As originally supplied by Mark Sinkinson in a question comment, the answer was found in:

Identifying rolled back transactions by SQLscope

From that link:

  In SQL 2008 R2 and before there are very few ways to identify transactions that have been rolled back. A server side trace capturing the SQLTransaction events is one way. You’d filter on EventSubClass = 2 to capture just the rollbacks. Or you could try reading the transaction log using fn_dblog() filtering on Operation = LOP_ABORT_XACT. Yet another way, which provides very limited data, is to periodically query the syscacheobjects sql column for any text which is like ‘%rollback%’. Each of these methods has their own issues and overheads. Ideally we’d like a light-weight solution.

  Fortunately, in SQL Server 2012 extended events comes to the rescue. You can either use extended events to map to a trace definition for SQLTransaction events or you could roll your own event session. Here’s one possible roll your own solution:

-- Create the event session to catch rollbacks

CREATE EVENT SESSION evtsRollback
ON SERVER
ADD EVENT sqlserver.sql_transaction (
  ACTION (sqlserver.session_id,sqlserver.database_id,sqlserver.sql_text)    -- Could add more actions
  WHERE transaction_state = 2                        -- Rollbacks only
  AND sqlserver.database_id = 8                        -- Just this one database
)
ADD TARGET package0.ring_buffer;
GO

-- Start the sesssion
ALTER EVENT SESSION evtsRollback ON SERVER STATE=START;

-- Begin a transaction, make a change, then rollback the transaction

-- Drop the event
ALTER EVENT SESSION evtsRollback ON SERVER DROP EVENT sqlserver.sql_transaction;

-- Query the results
SELECT
  event.value('(event/@name)[1]','varchar(50)') AS event,
  DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),CURRENT_TIMESTAMP),event.value('(event/@timestamp)[1]','datetime2')) AS [timestamp],
  event.value('(event/action[@name="session_id"])[1]','int') AS session_id,
  event.value('(event/action[@name="database_id"])[1]','int') AS database_id,
  event.value('(event/data[@name="duration"])[1]','bigint') AS duration_microseconds,
  event.value('(event/data[@name="transaction_id"])[1]','bigint') AS transaction_id,
  event.value('(event/data[@name="transaction_state"]/text)[1]','nvarchar(max)') AS transaction_state,
  event.value('(event/data[@name="transaction_type"]/text)[1]','nvarchar(max)') AS transaction_type,
  event.value('(event/action[@name="sql_text"])[1]','nvarchar(max)') AS sql_text
FROM
(
  SELECT n.query('.') AS event
  FROM
  (
    SELECT CAST(target_data as XML) AS target_data
    FROM sys.dm_xe_sessions AS s
    JOIN sys.dm_xe_session_targets AS t
    ON s.address = t.event_session_address
    WHERE s.name = 'evtsRollback'
    AND t.target_name = 'ring_buffer'
  ) AS s
CROSS APPLY target_data.nodes('RingBufferTarget/event') AS q(n)
) AS t;

-- Tidy up
DROP EVENT SESSION evtsRollback ON SERVER;


Columnstore Index maintenance and finding fragmentation

My production experience with columnstore indexes is on SQL Server 2016 and later versions but I believe everything in this answer also applies to SQL Server 2014. The simplest answer is that you can look at the ratio of row count and space used by each partition if you prefer to not use the sys.column_store_row_groups dmv. The more complicated answer is that there are many different types of possible fragmentation for a columnstore index. The most important ones to you depend on your data and workload.

Compressed deleted rows - this is the one that you mentioned in your question. The deleted rows take up space and serve no useful purpose.

Rows in delta rowgroups - these are rows that haven't been compressed (yet) - if you end up with too many you can see an impact to query performance.

Compressed rowgroups not of the maximum size (1048576 rows) - for a variety of reasons you can end up with compressed rowgroups with a row count less than 1048576. Microsoft claims that tables typically get the best compression if the rowgroup row count is maximized. In my experience this varies depending on the data. I see no measurable difference with our data model.

Misaligned segments - you may be loading your data in an order to get highly selective rowgroup elimination on key columns. If data ends up out of the preferred order, such as after a maintenance operation, then it can be considered to be fragmented.


Oracle: How to gather stats in a logical standby database?

You cannot run any DML in a logical standby database unless you deactivate, temporary, the guard feature. To overcome you isse you can do:

SQL> ALTER DATABASE GUARD NONE;
SQL> SELECT GUARD_STATUS FROM V$DATABASE;
GUARD_STATUS
--------------------
NONE

And then run your dbms_stats.


SELECT using JOIN where result counts per join are less than n?

you can use a subquery to fin all columB that have a lower countg tha 4

CREATE TABLE TableA
    ("columnA" varchar(5), "columnB" varchar(11))
;

INSERT INTO TableA
    ("columnA", "columnB")
VALUES
    ('item1', 'focaccia'),
    ('item2', 'spinach'),
    ('item3', 'goat cheese'),
    ('item4', 'beets')
;

CREATE TABLE TableB
    ("columnA" varchar(6), "columnB" varchar(11))
;

INSERT INTO TableB
    ("columnA", "columnB")
VALUES
    ('blah1', 'focaccia'),
    ('blah2', 'focaccia'),
    ('blah3', 'focaccia'),
    ('blah4', 'spinach'),
    ('blah5', 'goat cheese'),
    ('blah6', 'goat cheese'),
    ('blah7', 'goat cheese'),
    ('blah8', 'goat cheese'),
    ('blah9', 'goat cheese'),
    ('blah10', 'goat cheese'),
    ('blah11', 'beets'),
    ('blah12', 'beets'),
    ('blah13', 'beets'),
    ('blah14', 'beets'),
    ('blah15', 'beets'),
    ('blah16', 'beets'),
    ('blah17', 'beets')

SELECT a.* FROM TableA AS a INNER JOIN (SELECT "columnB" FROM Tableb GROUP BY "columnB" HAVING COUNT(*) < 4) AS b
ON a."columnB" = b."columnB"

columnA | columnB
:------ | :-------
item1   | focaccia
item2   | spinach

db<>fiddle here


Unable to remove permission for mysql.user

There is a big reason for this. There are four levels of grants in MySQL

Global (stored in mysql.user)
Database (stored in mysql.db)
Table (stored in mysql.tables_priv)
Column (stored in mysql.columns_priv)

When you ran

show grants for demo@'localhost';

mysqld read the grants and saw demo@'localhost' in mysql.user

When you ran

revoke all privileges on test.* from 'demo'@'localhost';

mysqld looked for the grants, not in mysql.user, but mysql.db.

You cannot yank grants on individual database from a user with global grants.


Is Oracle DB immune to the InnoDB deadlocks found in MySQL?

No database can possibly work around deadlock errors in general-- in Oracle, a deadlock indicates a bug in the application, not in the database.  The Oracle database will detect the deadlock condition (i.e. session A has a lock that session B is waiting on and session B has a lock that session A is waiting on) and terminate one of the blocked statements to resolve the problem.  Your application ought to avoid creating a deadlock in the first place-- one option is to always generate locks in the same order in every session.


SQL Server Database Mirroring is going to die. How can I guarantee high availability now?

As noted, mirroring is not dead.  (At least not yet.)  The general population doubts that Microsoft would totally abandon HA for Standard Edition.

However, there are other methods.  In the SQL Server 2012 documentation when referring to mirroring it says: "If your edition of SQL Server does not support AlwaysOn Availability Groups, use log shipping."

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

There is plenty of instruction on using log shipping.

EDIT: You might read Kin's links at: SQL Server Log Shipping: File copy options?


Permissions required for row counts to show up on Object Explorer Details

Through a little experimentation, I discovered that rowcount requires "VIEW DATABASE STATE".

Methodology: Captured queries that SSMS runs and executed as a user with datareader permissions only. One in particular throws an error. After granting the permission, verified that rowcounts were now visible.


You are correct, it is a signal to the engine that you wish to put a lock on an object. To over-simplify it, imagine you want to obtain a book from the library, if somebody has the book checked out you cannot obtain it, so you put your name on a list of people who intend to check that book out. When everybody else is done with it, and it is your turn, then you are allowed to check out the book.

If you haven't done so already I highly recommend reading Transaction Locking and Row Versioning Guide on MSDN which provides some great detail on how it all works.

The below is a snippet from that page:

  Intent Locks

  The SQL Server Database Engine uses intent locks to protect placing a shared (S) lock or exclusive (X) lock on a resource lower in the lock hierarchy. Intent locks are named intent locks because they are acquired before a lock at the lower level, and therefore signal intent to place locks at a lower level.
  Intent locks serve two purposes:

  To prevent other transactions from modifying the higher-level resource in a way that would invalidate the lock at the lower level.
  To improve the efficiency of the SQL Server Database Engine in detecting lock conflicts at the higher level of granularity.

  For example, a shared intent lock is requested at the table level before shared (S) locks are requested on pages or rows within that table. Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive (X) lock on the table containing that page. Intent locks improve performance because the SQL Server Database Engine examines intent locks only at the table level to determine if a transaction can safely acquire a lock on that table. This removes the requirement to examine every row or page lock on the table to determine if a transaction can lock the entire table.


Oracle auditing on specific user activity

Thanks to Andrew Brennan, I created a trigger that enables tracing when the user logs on:

CREATE OR REPLACE TRIGGER USER_TRACE_TRG
AFTER LOGON ON DATABASE
BEGIN
    IF USER = 'SCOTT'
  THEN
    execute immediate 'alter session set events ''10046 trace name context forever, level 1''';
  END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/


What makes up "Queries per second avg" in MySQL

Tracing it through it ends up being a COM_STATISTICS in the protocol, resulting in this bit of server code.

So based this 'Queries per second avg' is the, the thread_id as the number of queries, divided by the total uptime of the server.


Update statements with set transaction isolation level read uncommitted

If I have a stored procedure with set transaction isolation level read
  uncommitted, will it affect update statements?

Read uncommitted allows dirty reads. An X lock will be taken on the row or higher level (in the data page or index) before it is made dirty. Rows accessed directly by the query when locating a row to update will take a U lock and be blocked.

However it is still possible for an Update to be affected by the isolation level.

Connection 1

CREATE TABLE T1
(
X INT NULL,
Y INT NULL
);

INSERT INTO T1 DEFAULT VALUES;

BEGIN TRAN
UPDATE T1 SET X = 100;

WAITFOR DELAY '00:00:10'

ROLLBACK;

Connection 2 (run this within 10 seconds of firing off connection 1)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

UPDATE T1
SET    Y = (SELECT SUM(X)
            FROM   T1);

SELECT *
FROM   T1;

Result

The read operation read the uncommitted value and the final result was used in the UPDATE even though the 100 that was read was eventually rolled back.


How to do a fast simple query on a large table of postcodes

Adding a postcode, population combined index might help with the ORDER BY part of the SQL.

ALTER TABLE postcode ADD KEY postcode_population (postcode, population)

The main problem is that running a function over a column means its unable to use and index. So here should be an equivalent query that has no function over postcode.

SELECT * FROM `postcodes` WHERE Postcode LIKE 'TERM%' order by population desc limit 0,10

You can see which index is used with EXPLAIN {query}. MySQL only used one index per table in the expression.


How can you stop MySQL slave from replicating changes to the 'mysql' database?

Alright, after a few more hours of investigation, I think I figured it out. Adding my answer in case this is of use to others.

According to the docs on replicate-ignore-db:

  Statement-based replication.  Tells the slave SQL thread not to replicate any statement where the default database (that is, the one selected by USE) is db_name.

Of course, statement-based replication is the default and what I was using. So I made the attempt to change the format by restarting the master with binlog_format=row to see what would happen. No dice. GRANTS and REVOKES still were replicated.

Further investigation into the docs on replication changes on the mysql table revealed

  Statements that change the mysql database indirectly are logged as statements regardless of the value of binlog_format. This pertains to statements such as GRANT, REVOKE, SET PASSWORD, RENAME USER, CREATE (all forms except CREATE TABLE ... SELECT), ALTER (all forms), and DROP (all forms).

Gah! Ok, so I checked the binlog using mysqlbinlog and my GRANT statement was not issueing a USE mysql database call (why should it?). So replicate-ignore-db could not in good conscience ignore the statement.

My solution was to cut the changes to the mysql table out of the binary log completely by adding binlog-ignore-db=mysql to my.cnf and restart the server. Worked like a charm.


How to INSERT a row INTO a table that has an 'order' column without cursor

I suppose you want something like this:

BEGIN TRANSACTION ;

    DECLARE @pos INT = 1 ;

    UPDATE tbl
    SET position = position + 1
    WHERE position >= @pos
      AND EXISTS
          (SELECT * FROM tbl WHERE position = @pos) ;

    INSERT INTO tbl
      (<column names>, position)
    VALUES
      (<values ...>, @pos) ;

COMMIT TRANSACTION ;

Note that this design will behave highly inefficiently, if you have often inserts. It will essentially update almost the entire table for every new insert!


How to accurately measure query time in PostgreSQL (because EXPLAIN ANALYZE does not seem to do it)?

From a_horse_with_no_name in the comments,

  The time reported by explain (analyze) indeed does not include the time it takes to send the data to the SQL client. It is the time the server needed to completely prepare the result so that it could be sent to the driver. As any delay after that is completely up to the combination of the SQL client and the network between client & server it's impossible for explain to show that

That's pretty much all there is too it. One of them is server side, the other network side.


Previous 5 Years Data based on a Specific Year


Rule of thumb on what changes require a database reboot

No reboot of the database is required vis a vis the listener.  The only communication from the db to the listener is for the database to register itself with the listener, which it does every several seconds, on its own. Changes to db init parms only require a reboot if the change was 'scope=pfile', and that is only necessary if the description of the parm (in the Database Reference) says 'modifiable - NO'.

The db and the listener are not as tightly linked as you seem to think (based on this and other questions you've asked).  They are separate and distinct processes, and the listener's only role is to receive connection requests, and set them up by spawning a dedicated server process or connecting the client to a dispatcher process.  Once that is done the listener is out of the picture and you can even stop it without impacting existing connections.


Try granting to 'myuser'@'127.0.0.1', @'::1', or even @'%'.  In your logs, you will see 'Access denied for user' and what IP address you were trying to connect from.


These two functions appear to be internal / built-in functions given that:

they are referenced without a schema-name
SSMS color-codes them the same as all other built-in functions

sys.server_permissions, on Line #7, has:

        p.type, permission_name(p.class, p.type) AS permission_name,

sys.server_principals, on Line # 18, has:

    WHERE has_access('LG', p.id) = 1

When the instance is in single-user mode, I can execute the following:

USE [mssqlsystemresource];
SELECT PERMISSION_NAME(100, 'COSQ');
-- CONNECT SQL

The same statement does not work outside of mssqlsystemresource, nor does it work in that DB when prefixed with sys. or dbo..

Ergo, no, there is no definition that you will be able to get.


List all databases of a certain user ( SQL Server 2008 R2 )

You can impersonate that login and use the HAS_DBACCESS built-in function.

USE master;
GO
EXECUTE AS LOGIN = N'your_login_name';
GO
SELECT name, HAS_DBACCESS(name)
  FROM sys.databases;
GO
REVERT;

If you need to find the user mapping in each database (e.g. the user name in the database is not always the same as the login name), you can use dynamic SQL, e.g.

CREATE TABLE #db(name SYSNAME);
GO
use master;
GO
EXECUTE AS LOGIN = 'floob';
GO
INSERT #db(name)
  SELECT name FROM sys.databases
  WHERE HAS_DBACCESS(name) = 1
  AND database_id > 4; -- don't really care about system DBs, right?
GO
REVERT;
GO

DECLARE @sql NVARCHAR(MAX) = N'', @login SYSNAME = N'floob';

SELECT @sql += N'
  UNION ALL
  SELECT login = @login, db = ''' + QUOTENAME(name) + N''',
    username = p.name
    FROM ' + QUOTENAME(name) + N'.sys.database_principals AS p
    WHERE sid = SUSER_SID(@login)'
  FROM #db;

SET @sql = STUFF(@sql, 1, 11, N'');
EXEC sys.sp_executesql @sql, N'@login SYSNAME', @login;
GO
DROP TABLE #db;
GO


MariaDB: Why is this still using temporary and filesort - is it because I'm using a self-join? How to fix?

Try to convert your query to
SELECT e2.personId, COUNT(*) - SUM(e2.personId = e1.personId)
FROM entries e1
JOIN entries e2 USING (categoryId)
WHERE e1.personId  =  1
GROUP BY e2.personId

Update
It seems that MariaDB uses implicit STRAIGHT_JOIN... or plan builder cannot optimize the query correctly.
For query optimize it's enough to swap table copies. See fiddle for details.


Assuming (video_id, user_id) is unique in user_video, a plain LEFT JOIN would do the job:

SELECT v.*, uv.video_id IS NOT NULL AS has_bought
FROM   video v
LEFT   JOIN user_video uv ON uv.video_id = v.video_id
                         AND uv.user_id = $current_user_id;

$current_user_id being the ID of the current user.

If (video_id, user_id) is not unique, you could add GROUP BY

SELECT v.*, count(uv.video_id) > 0 AS has_bought
FROM   video v
LEFT   JOIN user_video uv ON uv.video_id = v.video_id
                         AND uv.user_id = $current_user_id
GROUP  BY v.video_id;

count() doesn't count NULL values.
Or use an EXISTS semi-join to avoid duplicates:

SELECT v.*, EXISTS (SELECT 1 FROM user_video uv
                    WHERE  uv.video_id = v.video_id
                    AND    uv.user_id = $current_user_id) AS has_bought
FROM   video v;

Or use the PostgreSQL-specific DISTINCT ON, that's particularly handy if you need the result in a certain sort order anyway.

SELECT DISTINCT ON (v.video_id)
       v.*, uv.video_id IS NOT NULL AS has_bought
FROM   video v
LEFT   JOIN user_video uv ON uv.video_id = v.video_id
                         AND uv.user_id = $current_user_id
ORDER  BY v.video_id, uv.video_id;  -- NULL sorts last


Both works with b-tree index, but MyISAM can work with FULLTEXT index also.

MySQL 5.6 is supposed to support FULLTEXT indexing as well. Look for it when it goes GA.

InnoDB is more strict in data integrity, while MyISAM is loose
InnoDB has transactions while MyISAM does not.
InnoDB has foreign keys and relationship contraints while MyISAM does not.

This is true in light of the fact that InnoDB supports transaction isolation via MVCC. That, in part, makes InnoDB fully ACID compliant.

MyISAM are faster to read but slower to write (I'm not sure about this one)

This depends entirely on the row format of the MyISAM table (Fixed or Dynamic). Slow is relative. After all, each DML statement against a MyISAM performs a full table lock each and every time.
Given a large enough cache, InnoDB can be faster for both reads and writes because COMMITs can be delayed. This leaves data already cached in the InnoDB Buffer Pool available for reads right out of memory.
InnoDB caches both Data and Indexes while MyISAM only caches Indexes. Thus, disk reads for data from MyISAM is always required.


What are the cons of implementing faceted search with WHEREs and JOINs?

You should have indexes on more of the fields; it gets tedious scanning all 7K rows.

What is the datatype of order; can it be the PRIMARY KEY and get rid if id?

Recommend you use standard 2-letter country_code instead of an extra table.  And declare it CHAR(2) CHARACTER SET ascii.

If you expect to have a million rows, I would say that you will have lots of performance problems.  But...  1K/minute is exciting; 1K/second is challenging; 1K/year is nothing. -- So, performance is not an issue.


Best way to increase no of partition in SQL Server Table

It sounds like what you want to do is to create a new boundary for your existing partitioning implementation, and this can be one through ALTER PARTITION FUNCTION ... SPLIT RANGE ....

You will also need to run prior to this an ALTER PARTITION SCHEME ... NEXT USED ... to indicate the next filegroup that will hold the new partition (there are a few caveats to this, as quoted below).  Here is a quote from BOL regarding this operation:

  A filegroup must exist online and be marked by the partition scheme that uses the partition function as NEXT USED to hold the new partition. Filegroups are allocated to partitions in a CREATE PARTITION SCHEME statement. If a CREATE PARTITION SCHEME statement allocates more filegroups than necessary (fewer partitions are created in the CREATE PARTITION FUNCTION statement than filegroups to hold them), then there are unassigned filegroups, and one of them is marked NEXT USED by the partition scheme. This filegroup will hold the new partition. If there are no filegroups marked NEXT USED by the partition scheme, you must use ALTER PARTITION SCHEME to either add a filegroup, or designate an existing one, to hold the new partition.


The operating system returned error 665(The requested operation could not be completed due to a file system limitation)

DBCC uses snapshots internally. Snapshots are then implemented as sparse files in Windows.

So this is actually a problem with Windows' handling of sparse files, which causes the snapshots used by DBCC to occasionally break on large and very active databases. It's reasonably well documented with a few recommended fixes in http://support.microsoft.com/kb/2002606

However, if you can DBCC on the dev server I'd recommend that instead. (I've personally only seen this problem with databases that were still serving traffic while being DBCC'd/snapshotted. Backup/restore to an idle server, DBCC there => no more 665 errors.)


You can calculate a running total in SQL 2008 R2 by using the ROW_NUMBER() window function along with a correlated sub-query to calculate the running total column.

Here is an example using some data I have available. You would need to amend the query accordingly to suit your tables/data and would likely need to use a PARTITION BY within the ROW_NUMBER function so you get a running total for the different projects/record types.

If you are able to post some sample data I can provide you with an example using your data structures.

WITH ContactMonthOrdered AS
(
    SELECT
            ContactMonthStartDate
        ,   COUNT(*) AS Total
        ,   ROW_NUMBER() OVER(ORDER BY ContactMonthStartDate) AS MonthOrder

    FROM
        Clinical.ContactDetail

    WHERE
        ContactFiscalYearLongName = '2014/2015'

    GROUP BY
        ContactMonthStartDate
)

SELECT
        ContactMonthStartDate
    ,   Total
    ,   MonthOrder
    ,   (
            SELECT  SUM(Total)
            FROM    ContactMonthOrdered
            WHERE   MonthOrder <= c.MonthOrder
        ) AS RunningTotal

FROM
    ContactMonthOrdered AS c

ORDER BY
    c.MonthOrder

UPDATED
The query below is derived from the example query you posted in your question. Provided I understand your schema correctly, this will give you the total for each period for a project and also a running total across the periods for a project. The assumption is that the sequence of PeriodId is meaningful.

;WITH CommitmentPeriodCost AS
(
    SELECT
        Commitments.ProjectId
        , CommitmentDetails.PeriodId
        , SUM(CommitmentDetails.TotalCost) AS TotalCost

    FROM
        CostManagement_Commitments AS Commitments
        LEFT JOIN CostManagement_CommitmentDetails AS CommitmentDetails
            ON  Commitments.Id = CommitmentDetails.CommitmentId

    WHERE
        Commitments.ProjectId = 1

    GROUP BY
        Commitments.ProjectId
        , CommitmentDetails.PeriodId
)

SELECT
    ProjectId
    , PeriodId
    , TotalCost
    , (
            SELECT  SUM(TotalCost)
            FROM    CommitmentPeriodCost AS RunningCost
            WHERE   ProjectId = CommitmentPeriodCost.ProjectId
                    AND PeriodId <= CommitmentPeriodCost.PeriodId
    ) AS RunningTotal

FROM
    CommitmentPeriodCost

ORDER BY
    ProjectId
    , PeriodId


You could use a graph database, or a plain old relational one:

/* set parent_id for child tasks */

create table task (
  id serial primary key,
  parent_id integer null references task(id),
  subject text not null,
  body text null,
  due_at date null
);

create table task_role_type(
  id smallint primary key,
  name text not null
);

insert into task_role_type values
(1, 'Created By'),
(2, 'Assigned To'),
(3, 'Completed By');

create table task_role (
  task_id integer not null references task(id),
  party_id integer not null references party(id),
  role_id smallint not null references task_role_type(id),
  from_date timestamp not null default current_timestamp,
  to_date timestamp null,
  primary key (task_id, party_id, role_id, from_date)
);

Create a task:

insert into task (subject, due_at) values ('do stuff', '2013-02-18');

insert into task_role (task_id, party_id, role_id) values (1,1,1);

Assign a task:

insert into task_role (task_id, party_id, role_id) values (1, 2, 2);

Split up a task:

insert into task (parent_id, subject) values (1, 'do stuff B');
insert into task_role (task_id, party_id, role_id) values (2, 1, 1);

Complete a task:

insert into task_role (task_id, party_id, role_id) values (1, 2, 3);

I would recommend PostgreSQL, as it can handle hierarchical data better than MySQL


Convert a lookup value to foreign key when a query is run

There are various ways, depending on the use case. For your example:

INSERT INTO public.people(name, states_id)
VALUES ('John', (SELECT states_id from public.states WHERE name = 'CA'));

Or:

INSERT INTO public.people(name, states_id)
SELECT 'John', states_id
FROM   public.states
WHERE  name = 'CA';

Assuming public.states.name to be unique - there should be a constraint in the table definition.
And a column public.people.states_id, obviously.


SQLCMD :connect directive - how can I specify connection protocol?

sqlcmd.exe -S tcp:myserver or sqlcmd.exe -S tcp:myserver\instance should do the trick.  If you must specify the port, do so like this:  sqlcmd.exe -S tcp:myserver,1433

By the way, documentation for SQLCMD is available directly from Microsoft, where it states the -S parameter syntax as:

  -S [protocol:]server[instance_name][,port]

SQLCMD Mode in SQL Server Management Studio accepts precisely the same syntax.

So:

:CONNECT [protocol:]servername[instance_name][,port]


Exclusion constraint generates: ERROR: operator &&(anyrange,anyrange) is not a member of operator family "range_ops"

An EXCLUSION constraint with && (Overlaps) needs to be GIST or SP-GIST because the default index is btree and its operator class does not support && (Overlaps). From the docs on Interfacing Extensions To Indexes

  GiST indexes are more flexible: they do not have a fixed set of strategies at all. Instead, the “consistency” support routine of each particular GiST operator class interprets the strategy numbers however it likes. As an example, several of the built-in GiST index operator classes index two-dimensional geometric objects, providing the “R-tree” strategies shown in Table 37.4. Four of these are true two-dimensional tests (overlaps, same, contains, contained by); four of them consider only the X direction; and the other four provide the same tests in the Y direction.

You can use the GIST index on an exclusion constraint with,

CREATE TABLE foo (
  a tsrange,
  EXCLUDE USING GIST (a WITH &&)
);

The operator class

btree supports only <, <=, =, >, >=
gist and sp-gist support far more. And, gin may also one day support being used in an EXCLUSION constraint.

So long as the default GIST operator class is properly defaulted for the type you're using it should work: it's not for inet and cidr

See also,

Why does a CIDR or INET index require me to specify the index's Operator Class?


I discovered that the wait_event_type: Extension was referring to it getting locked up in a query passed to the foreign data wrapper.  However, the problem does not seem to be the Postgres_FDW, but rather using Language SQL to write my function and not using LANGUAGE plpgsql.  I recalled a post either here, on StackOverflow, or another forum which mentioned a bug in the SQL procedural language in Postgres that would unexpectedly lock up queries intermittently. I rewrote the function in plpgsql, which was very simple, and it has been working flawlessly for over 3 months now.  I wish I could find the thread that mentioned the bug, but they had said it had been reported to Postgres, and the likelihood of them fixing it was slim.  Just use LANGUAGE plpgsql for your functions and stay away from the LANGUAGE SQL.


Passing column names dynamically to UNPIVOT

If you are going to have an unknown number of columns that you will need to unpivot, then you will have to look at implementing dynamic SQL.

You can use sys.columns to get the names of all of the columns in your cal table. If you use the following query you will get the list of all of the columns in your table:

select C.name
from sys.columns c
where c.object_id = OBJECT_ID('dbo.cal')

Now you can use this query along with FOR XML PATH to create a comma-separated list of the names to be concatenated to a string to be executed:

select @colsUnpivot
  = stuff((select ','+quotename(C.name)
           FROM sys.columns c
           WHERE c.object_id = OBJECT_ID('dbo.cal')
           for xml path('')), 1, 1, '')

Finally, you will take this list and place it into your query string to be executed so the full query will look like:

DECLARE @colsUnpivot AS NVARCHAR(MAX),
   @query  AS NVARCHAR(MAX)

select @colsUnpivot
  = stuff((select ','+quotename(C.name)
           FROM sys.columns c
           WHERE c.object_id = OBJECT_ID('dbo.cal')
           for xml path('')), 1, 1, '')

set @query
  = 'select data, datename(dw, data) dayname
     from cal
     unpivot
     (
        data
        for d in ('+ @colsunpivot +')
     ) u'

exec sp_executesql @query;

See SQL Fiddle with Demo


Connection to a MySQL server fails after server restart

Local Server Firewall settings.

in my case the local server was using McAfee, so enabling the port 3306 access to all pc's fixed the issue.


Deleting all the content from the database

Use DBCC CLONEDATABASE to generate a new copy without data, drop the original database an rename the generated clone.


Log shipping monitor primary not seeing recent backups, warnings triggered in error

You are correct the last backup time is showing 2017-02-09 12:15:02.010 while last copied and restored are much greater 2017-02-09 16:32:02.397 and 2017-02-09 16:34:11.477. So I believe somehow the catalog is not getting updated. There can be two reasons:

This can be a bug make sure  SQL Server 2008 R2 is patched to SP3
There was old logshipping which was configured and was not removed correctly and completely.

I believe the solution here would be to drop and recreate log shipping.


placing conditions in a join query join on vs where

When using inner joins there is no semantic difference between the two, and it would usually be make sense to put join related conditions in the JOIN and filtering ones in the WHERE clause.

However, when using outer joins the semantics are not the same.
for example: in a left join - rows from the left table will be returned even if there the JOIN ON includes some limitation on the fields from right table that are not met. but if the same limitation would be moved to the WHERE clause - the (already Left Joined) row from the left table that has no matching row on the right table will be discarded.

So for INNER JOIN - it does not matter - for OUTER JOIN - you should think what exactly do you want to get.

Note that in some cases this is actually desirable.
e.g. - In Queries such as:

SELECT *
FROM T1 LEFT JOIN T2 ON T2.id = T1.t2_id
WHERE T2.id IS NULL ;


I'm no huge fan of Microsoft Access, but I'd be curious to know why you were unsuccessful at it.  It should easily be able to hold that information and is probably easier to learn than other RDBMS systems.
Having said that, I think either MySql or Sql Server Express would work great.  You would use a text import process (Sql Server has a wizard that makes it easy), bring all the data into one table and then dedup and organize anyway you see fit.

1 million rows with 10 columns wont be a very large table, my guess is it will be somewhere near 500Mb.


Can I enforce one field being dependent on another?

You can use a CHECK constraint with a CASE expression. If check2 is enabled, make sure check1 is enabled too, otherwise you can default to a no-op ("checking" that check1 = check1). For simplicity I'm assuming these columns are not NULLable.

CREATE TABLE dbo.foo
(
  check1 BIT NOT NULL DEFAULT 0,
  check2 BIT NOT NULL DEFAULT 0,
  CONSTRAINT ck_both CHECK (check1 = CASE WHEN check2 = 1 THEN 1 ELSE check1 END)
);

INSERT dbo.foo DEFAULT VALUES;             -- 0,0 succeeds
INSERT dbo.foo(check1,check2) VALUES(1,0); -- succeeds
INSERT dbo.foo(check1,check2) VALUES(1,1); -- succeeds
GO
INSERT dbo.foo(check1,check2) VALUES(0,1); -- fails

To add this constraint to your existing table:

ALTER TABLE dbo.tablename
  ADD CONSTRAINT ck_BothBits
  CHECK (check1 = CASE WHEN check2 = 1 THEN 1 ELSE check1 END);

If the columns are NULLable (hopefully they aren't), you can say something like this (untested):

ALTER TABLE dbo.tablename
  ADD CONSTRAINT ck_BothBits
  CHECK (COALESCE(check1,0) = CASE
    WHEN check2 = 1 THEN 1 ELSE COALESCE(check1,0) END);

With that constraint in place:

INSERT dbo.foo(check1,check2) VALUES(NULL,NULL) -- succeeds
INSERT dbo.foo(check1,check2) VALUES(1,0);      -- succeeds
INSERT dbo.foo(check1,check2) VALUES(1,1);      -- succeeds
INSERT dbo.foo(check1,check2) VALUES(1,NULL);   -- succeeds
INSERT dbo.foo(check1,check2) VALUES(0,NULL);   -- succeeds
INSERT dbo.foo(check1,check2) VALUES(NULL,0);   -- succeeds
GO
INSERT dbo.foo(check1,check2) VALUES(0,1);      -- fails
GO
INSERT dbo.foo(check1,check2) VALUES(NULL,1);   -- fails


Designing database containing code metadata

"Millions of columns"? Or millions of rows? For single user applications and just a few megabytes of data, sqlite can be perfectly sufficient, so it will really depend on how many lines of code/rows of data you'll be dealing with, and the complexity of the queries that you'll require. You may want to consider postgresql or mysql from the get go, to avoid getting bogged down or finding sqlite is too limited when you've invested a lot of code and time on it.


Postgresql: Permission denied to schema to user created based on master with rights

Grant usage on schema mysch  to newuname;

Grants on a database object doesn't GRANT rights to the schemas.

GRANT


Remove a SQL Server Patch only visible in View Installed Update List

It sounds like an orphaned registry entry. The steps in this article outline how to back up the uninstall key and delete unwanted entries:

Click Start, then click on Run and type regedit in the Open field. Then press enter on your keyboard.
Navigate to the Registry key: HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Uninstall. You do this by clicking on the plus sign next to each key in the path to expand the path below it. For example you will click on the plus next to HKEY_LOCAL_MACHINE, Software, Microsoft, Windows, CurrentVersion, and finally Uninstall.
Right click on the the Uninstall key and select the Export option. (If you are using Win98/95/ME, you should clik on the Registry menu and then select Export). This will open up a dialog box where you can select a directory and name for the file you will export to. Make the Save in: field set to your Desktop and in the File name: field enter uninstall_programs and press the Save button. This will create a backup of your Uninstall registry key on your desktop in case of a problem.
Now that we have a backup created, we will proceed with deleting the entries for the programs that we would no longer like to appear in your Add/Remove Programs list. As long as the Uninstall key is expanded you will see below it a listing of programs. Search for the program you would no longer like to appear in the Add/Remove Programs list and right click on the name of the program and then click on the Delete key. When it asks for confirmation, click on the Yes key.
Once the key is deleted, exit regedit, go back into your Add/Remove Programs option in the Control Panel and see if the program is now gone from the listing. If it is not listed, then you have deleted the entry from the Registry correctly. If on the other hand, the listing looks incorrect you can restore the backup you created by double-clicking on the uninstall_programs.reg icon found in your desktop. When it prompts you if you would like to merge the contents, you would reply Yes. Then you should repeat steps 1 through 5 to remove the desired program.


Why does Mongo query hang for a long time when creating index?

As at MongoDB 3.2, this is expected behaviour for foreground index builds:

  By default, creating an index blocks all other operations on a database. When building an index on a collection, the database that holds the collection is unavailable for read or write operations until the index build completes. Any operation that requires a read or write lock on all databases (e.g. listDatabases) will wait for the foreground index build to complete.

To avoid this behaviour when building new indexes on collections with significant existing data, you should build the index in the background instead:

mongos> db.lc_data.ensureIndex({"name": 1}, {background:true})

MongoDB 2.4+ can build multiple indexes in the background. For more information, see: Background Index Builds.

You may also wish to upvote/watch SERVER-20960: Default index build option support in config/runtime in the MongoDB issue tracker, which is a feature request to make the default index build type configurable (eg. default to background index builds).


As I hinted in my first comment, you have to move the join in the subquery, so you still have only one row of output per item. Example can be tested at http://sqlfiddle.com/#!9/11b37/3

SELECT items.*,
  (SELECT GROUP_CONCAT(tags.tag)
   FROM tags
   JOIN tags_items ON tags_items.tag_id = tags.id
   WHERE items.id = tags_items.item_id) AS tags
FROM items
WHERE items.user_id = 0;

Using 3 tables is the right design. With only two you would end up with multiple rows for the same tag name.

Your original tables have redundant user_id column in tags table (and that same would apply for tags_items) - as one tag/tags_items row can only be linked with one item the user_id is defined by it.


Error during pg_restore: text search dictionary "unaccent" does not exist

It came out that there was a stupid hack, unaccent function was marked immutable and there was an index that used this unaccent function. Dropping the index and recreating it after backup did the trick.

Do not use hacks, folks :)


Consider using the MEMORY storage engine, so the changes will happen in memory. In case of a crash, you can rely on the binlog to recover the latest value. And if you have a slave, you can use InnoDB on the slave.

If you want to stay on InnoDB, consider the HandlerSocket plugin. It provides an SQL-less interface to InnoDB, and it should be much faster in your scenario.


How to automatically increment several Integer columns based on alphabetically ordered text columns?

Assumption:
These ID values will only be used for output/display and will not be used as Unique Keys or in other tables (as Foreign Keys), otherwise you'll need a lot of cascade updates and loss of performance as a single row insert would result in updating a huge part of the table and many other tables as well.
Solution:
You don't have to store these values. You can use window functions, e.g. DENSE_RANK() to calculate these number when needed.
SELECT
    province, district, town,
    DENSE_RANK() OVER (ORDER BY province)
        AS province_id,
    DENSE_RANK() OVER (PARTITION BY province
                       ORDER BY district)
        AS district_id,
    DENSE_RANK() OVER (PARTITION BY province, district
                       ORDER BY town)
        AS town_id
FROM
    tableX AS t
 ;


Why is the schema prefix (dbo) mandatory when we call a function?

Then why we can call function without prefix (schema), created under dbo?

From Books Online Document on UDF's

  Scalar-valued functions can be invoked where scalar expressions are used. This includes computed columns and CHECK constraint definitions. Scalar-valued functions can also be executed by using the EXECUTE statement. Scalar-valued functions must be invoked by using at least the two-part name of the function.

So this is basically a restriction set by SQL Server development team and I consider it quite correct. Even if it is somehow allowed (just for sake of conversation) I would still use Schema prefix.

I always support using schema name even if it would work without adding it. This is best practice and all "Good" developers use it no matter how much redundant it is.

The other reason what I see is, Database engine needs something to differentiate between system function like getdate () and user defined functions. If you are allowed to call function without schema name how would database engine differentiate between User created function named Getdate or system GETDATE() function.


Oracle 12c Database swapping after set up huge pages

The steps I did to setup this hugepage configuration on ReDHat 7.3 64GBRAM and the mistake. (Link from oracle base posted above provides a better Manual for reuse)
Check total RAM of OS:

grep MemTotal /proc/meminfo
MemTotal:       65809456 kB

Check Oracle Database – AMM needs to be off:
MEMORY_TARGET und MEMORY_MAX_TARGETneed to be set to zero.

SQL> select value from v$parameter where name = 'memory_target';
VALUE
---------------------------
0

Calculate SGA size, for example 32GB:

select value/1024 from v$parameter where name = 'sga_target';

VALUE/1024
----------
  32768000

Calculate Huge Pages,
for a  x86_64 Red Hat Enterprise Linux Server we can use a default 2MB hugepagesize:

SGA / Hugepagesize=Hugepage +1
  32768000/2048=16001

This need to be set in /etc/sysctl.conf as root:

vm.nr_hugepages =16001

Also we need to set the /etc/security/limits.conf. This value hard and soft memlock should be greater than the the SGA and smaller than the total RAM.

Hugepages * Hugepagesize = minimum Memlock or something like 90% of total RAM.

Setting memlock to a lower value then SGA leads to unpredictable behavior of the Oracle Database. As commented above Oracle Database will allocate memory without using the huge pages. In my case this leaded to fatal errors when using impdp. Datapump did not provide errors which made sense.

grep oracle /etc/security/limits.conf
oracle  soft    nproc   2047
oracle  hard    nproc   16384
oracle  soft    nofile  1024
oracle  hard    nofile  65536
oracle  soft    stack   10240
#oracle  soft    memlock 19857408
#oracle  hard    memlock 19857408
oracle  soft    memlock 57671680
oracle  hard    memlock 57671680

For RedHat we also need to turn off Transparent Hugepages

cat /sys/kernel/mm/redhat_transparent_hugepage/enabled
[always] madvise never

echo never > /sys/kernel/mm/redhat_transparent_hugepage/enabled
echo never > /sys/kernel/mm/redhat_transparent_hugepage/defrag

cat /sys/kernel/mm/redhat_transparent_hugepage/enabled
always madvise [never]

Reboot System and start Oracle Database check:

grep Huge /proc/meminfo
AnonHugePages:     40960 kB
HugePages_Total:   16001
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB


Another way:

SELECT a.id,
       GROUP_CONCAT(c.body,' ') AS contents
FROM a
  LEFT JOIN b
  ON b.id_a = a.id
  LEFT JOIN c
  ON  b.id_c = c.id
  AND c.id <= COALESCE(
    (  SELECT ci.id
       FROM c AS ci
         JOIN b AS bi
         ON  bi.id_c = ci.id
       WHERE b.id_a = bi.id_a
       ORDER BY ci.id
       LIMIT 1 OFFSET 4
    ), 10000000000)
GROUP BY a.id ;

a variation:

-- variation 2
SELECT a.id,
       GROUP_CONCAT(c.body,' ') AS contents
FROM a
  LEFT JOIN b
    JOIN c
    ON  b.id_c = c.id
    AND c.id <= COALESCE(
      (  SELECT ci.id
         FROM c AS ci
           JOIN b AS bi
           ON  bi.id_c = ci.id
         WHERE b.id_a = bi.id_a
         ORDER BY ci.id
         LIMIT 1 OFFSET 4
      ), 10000000000)
  ON b.id_a = a.id
GROUP BY a.id ;

and two more, all using the same basic pattern:

-- variation 3
SELECT a.id,
       GROUP_CONCAT(c.body,' ') AS contents
FROM a
  LEFT JOIN b
    ON  b.id_a = a.id
    AND b.id_c <= COALESCE(
      (  SELECT bi.id_c
         FROM b AS bi
         WHERE b.id_a = bi.id_a
         ORDER BY bi.id_c
         LIMIT 1 OFFSET 4
      ), 10000000000)
  LEFT JOIN c
  ON b.id_c = c.id
GROUP BY a.id ;

-- variation 4
SELECT a.id,
       ( SELECT GROUP_CONCAT(c.body,' ')
         FROM b
         LEFT JOIN c
         ON b.id_c = c.id
         WHERE b.id_a = a.id
           AND b.id_c <= COALESCE(
             ( SELECT bi.id_c
               FROM b AS bi
               WHERE b.id_a = bi.id_a
               ORDER BY bi.id_c
               LIMIT 1 OFFSET 4
             ), 10000000000)
       ) AS contents
FROM a ;

Tested in sqlfiddle.


RDS Postgres - pg_stat_statements is missing queryid column

Update:  Lukas from pganalyze helped me.  How nice!
The answer was as follows:

DROP FUNCTION pganalyze.get_stat_statements();
ALTER EXTENSION pg_stat_statements UPDATE;
CREATE OR REPLACE FUNCTION pganalyze.get_stat_statements() RETURNS SETOF pg_stat_statements AS
$$
/* pganalyze-collector */ SELECT * FROM public.pg_stat_statements;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;


Is it good practice to only delete through a trigger to enforce 'deleted by' auditing?

I am not sure that using an IsDeleted column would create unnecessary headache.   Locking down the table, and allowing non-administrative action via a view which filters on that column, while not displaying it, isn't very difficult.  Add an Instead Of Delete trigger to the view, and you're all set.

Given that it's essentially boiler plate once you have the initial pattern, you could very easily write a procedure locks down the table and (re)generates an associated view, adding the filtering and trigger when it finds it has IsDeleted.  It could add user logging requirements if there was a deleted_by_user_id field as well.  I'd recommend parameter that allow rebuilding the world, or a pattern that is matched against table names (and/or schema).

In addition, versus forcing a deleted_by_user_id to be directly populated, I would look at leveraging SESSION_CONTEXT.  The trigger would check against an agreed upon session context variable (e.g. 'AppUser'), throw if not set, then use that for populating deleted_by_user_id from that.

On the application side, you should be able to create sub-class SqlConnection and override Open.  After calling the base Open, you should be able to run a EXEC sp_set_session_context N'AppUser', '{username}';, there by setting the App user for the session.

The advantage of using IsDeleted is that you don't have to write a whole row of data when doing a delete.  If you do bulk deletes, or a lot of deletes in general, this could be advantageous.  Though I would couple with Partial Index, cover just undeleted records. The advantage removing and writing to another table is that after reorganization you should have much more contiguous data, which in many cases generally makes things faster.


Use the \o command as documented in the manual:

http://www.postgresql.org/docs/current/static/app-psql.html

specifically:

> \o <path_to_the_file_where_you_want_the_output>
> [your command]
> \o

the last statement switches the output of psql back to stdout.


It could also be that you applied the update to SQL Server but didn't apply it to other services (SSAS, SSRS, etc.) or to other components (like Management Tools). Run SP2 again (or upgrade to something even newer!) and see which components it finds eligible for an update - I bet there is at least one.


Unable to restore database in Oracle 10g

Looks like there is already a job on the imported database with that job number.  Just recreate the job on the import database.

The job looks to be a queue so you could, depending on your business procedures, probably run the new job to test that everything is working.


Why am I seeing poor performance on SQL server 2019 and asynchronous AlwaysOn

You have the AG set to async mode, which means the difference isn't due to HADR_SYNC_COMMIT waits.
Just the overhead of compressing and sending the log records to the secondary replica(s) could explain the small differences you've noticed though.  Especially if the workload is so sensitive that a 2ms difference per query results in noticeable changes.
Per the really useful Thread Usage By Availability Groups docs section, your primary server will use

1 Log Capture thread for each primary database. In addition, it uses 1 Log Send thread for each secondary database

In the background during your normal user query workload, these threads are reading log records as they are committed, compressing them, and then sending them to each of the secondary replicas configured.
These threads will use CPU time. If the workload is write-heavy (lots of inserts and updates), it could be a noticeable amount of CPU usage.  If there are multiple AGs, multiple databases in the AG, or multiple secondary replicas that need to be updated, even more CPU usage will be needed.
IF the increase in query runtimes is due to AG thread CPU usage*, one possible solution would be to enable trace flag 1462, described in Tune compression for availability group:

Disables log stream compression for Availability Groups with asynchronous replicas. This feature is enabled by default on asynchronous replicas to optimize network bandwidth.

You'll end up using more network bandwidth, but the primary server won't spend CPU cycles compressing log blocks before sending them to the secondary.
*You could try confirm this is the case, potentially by checking to see if queries are experiencing signal waits at a higher rate than on your previous server.

Note that this answer focuses on the availability group aspect, since that's a major difference between the systems per your question description.
The difference in performance could be numerous things, including even minor config differences that can make a big difference.  Considering using the free, open-srouce sp_Blitz procedure to check for anomalous settings, offline schedulers, etc.


Can subqueries produce different execution plans than CTE's?

As is, I'd argue that the question isn't answerable. It's impossible to prove a negative and you won't find a guarantee in the product documentation. If you'd like an example of a technical difference between the two approaches, watch a few minutes of Paul White's Query Optimizer Deep Dive session. It is not clear how someone could translate that into a performance best practice.

I suggest approaching the issue as a matter of coding style instead of trying to find a performance best practice. Switching out a CTE for a derived table or a derived table for a CTE is not a meaningful way to rewrite a query.


MSSQL peer to peer replication what is the best practice to drop and create multiple table

Yeah, this is the problem with Transactional Replication: every time you change something you need to recreate the publisher and the subscription.
I think your three steps are right.


Are the cost percentages in this SQL Server plan over 100% for a valid reason?

The visual cost estimator is crap. This sort of stuff happens all the time.  Just go with the highest ones are the most expensive and attack those ones first.


How does using separate schemas affect SQL Server 2008 performance?

There may be an impact on the performance of the query optimizer, albeit a small one, depending on your coding style. If you reference a table without a schema, the optimiser has to try to identify the table first by checking tables within the users default schema (if there is one), then using dbo., and then everything else. If you explicitly reference the tables as schema.table, which is probably good practice anyway, then even this minor overhead will be avoided.


SQL Server - How many type of timeouts can happen, and how?

Well from an application point of view there are:

connection timeout (how long the app is willing to wait to establish a connection to SQL Server)
command timeout (how long the app is willing to wait for a command to complete, including pulling the results down from SQL Server)

Back in my classic ASP days, the defaults for these were 15 and 30 seconds respectively, I have no idea what they are by default in .NET today.

SQL Server has its own set of timeouts, for example:

Remote query timeout. Default is 600 seconds (10 minutes).
Remote login timeout. Default is 10 seconds.
Query wait. Default is -1 (25 x query cost).
Full-text protocol handler timeout. Default is 60 seconds.

You can see these values for your system here:

SELECT * FROM sys.configurations
WHERE configuration_id IN (1519,1520,1541,1557);

There is also @@LOCK_TIMEOUT (which defaults to -1 (infinity)). This is how long SQL Server will wait on a blocked resource. You can override this for a particular session using SET LOCK_TIMEOUT. More details here.

Deadlocks I suppose could also fall into this category as well. The system scans for deadlock situations every 5 seconds, and there is no magic formula to determine when the deadlock will occur in relation to when any of the involved requests started. This is because SQL Server doesn't let the oldest transaction win; it chooses the victim based on DEADLOCK_PRIORITY and the estimated amount of resources required to roll the victim back. More details here.

There is also a memory grant timeout (which may be customized using Resource Governor). Depending on concurrency, a query won't necessarily fail if it reaches the timeout before obtaining all of the memory requested, it will just run with the allocated amount (and therefore might be less efficient). If it fails you will likely see Msg 8645.

You can get an idea for other potential timeout scenarios that may occur within SQL Server by reviewing these error messages:

SELECT message_id, [text]
  FROM sys.messages
  WHERE language_id = 1033
  AND ([text] LIKE '%timeout%' OR [text] LIKE '%time out%')

However I don't think it is practical, feasible or productive for anyone to try to provide you with a complete and exhaustive list of every single timeout situation possible. Solve the problems you're having, rather than prematurely solving a whole bunch of problems you probably never will...


SQL Server 2005 encryption - What Grants should I give

The specific permissions required are:

GRANT CONTROL ON CERTIFICATE :: [cert] TO [user]
GRANT VIEW DEFINITION ON SYMMETRIC KEY :: [key] TO [user]

However, rather than grant permissions directly on the Certificate and Key, I like to have two stored procedures - one for encryption and one for decryption. We generally have different users that perform the encryption and decryption. Generally, the application is encrypting records as they are inserted into the database. Then it's a different user that needs to decrypt them for reporting purposes. Both procedures are created WITH EXECUTE AS OWNER and the users are granted EXECUTE on the procedures.

This way no users have direct permissions on the Certificate and Key and no user can both encrypt and decrypt.


Oracle 11 rman db restore file corruption

Your only solution for this problem is to take a backup of the datafiles that are hit by a direct load operation, or prevent force_logging operations totally.
Force_logging operations may seem like a good idea, performance wise but if you care about your data ..... don't do it.

alter database noforce logging;


Based on the statistics the oprimizer has estimated this as the cheapest way to get the data. A INDEX FAST FULL SCAN reads the entire index as it is stored on disk using multiblock read. This kind of operation is prefered to other index operation   because  a high number/fraction of rows with ID1=1110 and ID2=1112 exists in the index and the data is not needed sorted. It is prefered to a full table scan because  all the data needed (ID1, ID2, MID, PARENTID) is contained in the index.


how to set mogodb in upstart service with authentication in mongodb way

You need to use sudo service to start MongoDB as a service, check the documentation for further details:

sudo service mongod --auth --port 27017 --dbpath /var/lib/mongodb


Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries

First of all, keep in mind that work_mem is per operation and so it can get excessive pretty quickly.  In general if you are not having trouble with sorts being slow I would leave work_mem alone until you need it.

Looking at your query plans, one thing that strikes me is that the buffer hits are very different looking at the two plans, and that even the sequential scans are slower.  I suspect that the issue has to do with read-ahead caching and having less space for that.  What this means is you are biasing memory for re-use of indexes and against reading tables on disk.

My understanding is that PostgreSQL will look to the cache for a page before reading it from disk because it doesn't know really whether the OS cache will contain that page.  Because the pages are then staying in the cache and because that cache is slower than the OS cache, this changes the sorts of queries which are fast vs the sorts that are slow.  In fact reading the plans, aside from work_mem issues, it looks like all of your query info comes from the cache but it is a question of which cache.

work_mem: how much memory we can allocate for a sort or related join operation.  This is per operation, not per statement or per back-end, so a single complex query can use many times this amount of memory.  It isn't clear you are hitting this limit but it is worth noting and being aware of.  if you increase this too far, you lose memory that might be available for the read cache and the shared buffers.

shared_buffers: how much memory to allocate to the actual PostgreSQL page queue.  Now, ideally the interesting set of your database will stay in memory cached here and in the read buffers.  However, what this does is ensure that the most frequently used information across all backends gets cached and not flushed to disk.  On Linux this cache is significantly slower than the OS disk cache, but it offers guarantees that the OS disk cache dos not and is transparent to PostgreSQL.  This is pretty clearly where your problem is.

So what happens is that when we have a request, we check the shared buffers first since PostgreSQL has deep knowledge of this cache, and look for the pages.  If they are not there we ask the OS to open them from the file, and if the OS has cached the result it returns the cached copy (this is faster than the shared buffers, but Pg can't tell whether it is cached or on disk, and disk is much slower so PostgreSQL typically will not take that chance).  Keep in mind this affects random vs sequential page access as well.  So you may get better performance with lower shared_buffers settings.

My gut sense is that you probably get better, or at least more consistent, performance in high concurrency environments with larger shared_buffer settings.  Also keep in mind that PostgreSQL grabs this memory and holds it so if you have other things running on the system, the read buffers will hold files read by other processes.  It's a very large and complex topic.   Larger shared buffer settings provide better guarantees of performance but may deliver less performance in some cases.


SQL Server: Covering indexes including all columns?

I've done this on specific indexes before now, to aid oft-run heavy queries. Effectively what they have done is create multiple clustered indexes: when any of those indexes is used to find rows no extra work is needed looking up the rest of the data in the real clustered index (or the heap if there is no real clustered index).

  is this a sensible strategy?

For some indexes where needed to support certain query patterns, certainly yes.

But to do this with all indexes, I would just as certainly say no.

It is going to be wasteful of space to do where not actually needed, and will slow inserts/updates significantly. It may slow down as many read queries as it helps too, because each index page holds less records so any query needing to reference a chunk of the index for filtering but not using all other columns will have to access more pages. This will make your database more memory-hungry: those pages will need to be loaded into the buffer pool, potentially ejecting other useful pages if memory is low. If compression is used on those indexes to try mitigate the effect on storage and memory requirements, then it will be pushing extra load to the CPUs instead.

  as access is via an ORM that by default (but not always) retrieves all columns

This is a common pattern with poorly optimised use of an ORM (or just naive ORMs) and in these cases I've seen SQL Server's index adviser (and similar 3rd party tools) suggest indexes with many INCLUDEd columns, so I would agree with your suggestion that this is why the indexes have been created this way.

But while it may make all such queries slightly faster and some of them significantly faster, I suspect that in many cases any benefit is so small as to not be worth the extra memory footprint required by your common working set, the space on disk, and the IO between disk and memory.

Also remember that the ORM might not be selecting out all columns of all tables tha a query touches, so that benefit might only hold for the main target of the current request, and the larger indexes may penalise the query when other objects are used for filtering but not returning data  (SELECT * FROM table1 WHERE id IN (SELECT someID FROM table2 WHERE someColumn='DesiredValue') perhaps).

Another consideration for the excess space used, particularly if the data is large, is that it will have an impact on your backup strategy: storage and transfer costs for those backups, potential restore times, and so forth.

  should we be prepared for any differences between the two [on-prem & AzureSQL]

Generally I think the considerations here are going to be the same in each case, though any excess memory/IO cost imposed by the large indexes may be more directly visible in Azure where you can tweak the service tier and therefore infrastructure cost more easily rather than having a relatively fixed set of hardware resource. If using standard/premium tiers instead of vcore based pricing then you'll be affected more by the IO cost in standard as premium includes significantly more IO per DTU. If you are using multi-region backups or redundancy or other non-local features in Azure than there might be a bandwidth cost associated with the extra space taken by uneccesarily wide indexes.


logic/design for storing achievments and progress for users

I think it would be a good idea to create an 'Achievement' table with required condition to be unlock (ex:Win 8 rounds in a row) and  an association table 'UserAchevement' to indicate users.the same To store every user progress (the number of Wining round for exemple) a 'Progress' table is needed.


Run an exe file that exists on a different server from a SQL Server Job

You could still use xp_cmdshell; however, you'll need to make sure that your SQL Server Service account (the AD account used to run SQL Server) has access to the directory containing the exe file and any other directories that that program may require.

xp_cmdshell runs on your SQL Server itself; you can use it just like any other command window.

If, on the other hand, you need the exe to run on the machine that hosts it, then you'll need to look at other options.


Help with preventing double bookings with variable lengths

Lets say that we have two venues A and B with both a start date/time and a end date/time. We need to check whether their time spans overlap. They overlap if:

A.end > B.start AND A.start < B.end


Database Design Advice for Expert Systems - Rule Based Systems

In university, I took courses where we worked with, and then built our own (very simple) expert systems in Prolog. I can't think of an RDBMS implementation that would be easy, or elegant. Most of them would probably end up as an EAV schema (which would work - I've seen and used such systems, but they're not pretty). One of the first hits on google for "prolog expert system" is this: http://www.amzi.com/ExpertSystemsInProlog/ It looks long, but might be a good resource.


Change IDENTITY_INSERT to ON in SQL server

Explicit identity insert require IDENTITY_INSERT property set to ON.

SET IDENTITY_INSERT MyTable ON  -- Statement Allows explicit values to be inserted into
                                -- the identity column of a table.
GO

INSERT INTO MyTable(ID, Name, Description)
VALUES (0, 'Special title', 'special item');
GO

SET IDENTITY_INSERT MyTable OFF  -- Statement revert granted permission
GO


Why does a query on a local instance with local database files cause NETWORK_IO waits?

Usually this surfaces as ASYNC_NETWORK_IO outside of Extended Events, but yes, it is the time SQL Server spent waiting for the caller (in this case SSMS) to consume the results. If the caller is slow (like SSMS) then SQL Server has to sit there and wait before feeding more rows. If you have a wide result set or large values, you should compare results to grid and results to text. But really, you shouldn't be timing anything based on response times in SSMS, since it does unrealistic things with the data and how it renders that your application won't be doing (hopefully).

I wrote about this wait type (and 9 other common ones) here:

What to do (or not do) about top wait stats


How does SQL Server reuse reserved pages after deleting a table

Quite simply a DROP or TRUNCATE statement marks any allocated pages that were associated with the table (regardless if it was a heap or a clustered table) as no longer being allocated and the space is reused by the next operation that requires additional space (e.g. new records, a page split, etc.).

From sys.allocation_units (Transact-SQL):
ⓘ Note
When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sys.allocation_units immediately after dropping or truncating a large object may not reflect the actual disk space available.

A TRUNCATE TABLE operation is effectively the same thing as a DROP TABLE statement, but it comes with the bonus of recreating the table immediately after it has been dropped.  This is why a TRUNCATE TABLE statement is a Data Definition Language (DDL) statement and not a Data Manipulation Language statement such as a DELETE.
If you want to look at how the engine deallocates and reuses space, you can dig into the DBCC PAGE undocumented function.  Paul Randal has a few articles (e.g. ref1, ref2, ref3, ref4) about its use that you can read through if you're so inclined.
If you've got a sandbox environment, you can also run the following series of scripts which will show the pages being allocated and deallocated in the same manner, regardless if a TRUNCATE TABLE or DROP TABLE operation is performed:
-- Create Test Database
USE [master]
GO

EXECUTE AS LOGIN = 'sa'

CREATE DATABASE [TestDB]

REVERT
GO

USE [TestDB]
GO

-- Look at GAM
DBCC PAGE(0, 1, 2, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
--Only 1016 pages are reserved.  This is because that's the initial size needed by data populating a "blank" database

ParentObject   Object                   Field                     VALUE
-------------- ------------------------ ------------------------- --------------
GAM: Header    GAM: Extent Alloc Status (1:0)        - (1:312)        ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:320)      - (1:1016)   NOT ALLOCATED
*/

-- Look at SGAM
DBCC PAGE(0, 1, 3, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
The SGAM also shows only 1016 pages are reserved, this view will remain relatively unchanged throughout the demo

ParentObject  Object                     Field                    VALUE
------------- -------------------------- ------------------------ --------------
SGAM: Header  SGAM: Extent Alloc Status  (1:0)        - (1:304)   NOT ALLOCATED
SGAM: Header  SGAM: Extent Alloc Status  (1:312)      -               ALLOCATED
SGAM: Header  SGAM: Extent Alloc Status  (1:320)      - (1:1016)  NOT ALLOCATED
*/

-- Create our Test Table
CREATE TABLE T1
(
    ID  INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    Value   CHAR(8000)
)
GO

-- Populate it with exactly 1GB of data
INSERT INTO T1 (Value)
SELECT TOP 131072 ''
FROM sys.configurations t1
    CROSS JOIN sys.configurations t2
    CROSS JOIN sys.configurations t3
    CROSS JOIN sys.configurations t4

-- Table is 1GB in Size, as expected
EXEC sp_spaceused 'T1'

/*
name  rows    reserved    data        index_size  unused
----- ------- ----------- ----------- ----------- -------
T1    131072  1052616 KB  1048576 KB  3904 KB     136 KB
*/

-- Recheck GAM and SGAM
-- Look at GAM
DBCC PAGE(0, 1, 2, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
This allocates 131072 8kb data-pages for the data and 488 8kb data-pages for the index
This view shows the location of the allocated and unallocated pages within the datafile

ParentObject   Object                   Field                     VALUE
-------------- ------------------------ ------------------------- --------------
GAM: Header    GAM: Extent Alloc Status (1:0)        - (1:8080)       ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:8088)     -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:8096)     - (1:16168)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:16176)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:16184)    - (1:24256)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:24264)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:24272)    - (1:32344)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:32352)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:32360)    - (1:40432)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:40440)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:40448)    - (1:48520)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:48528)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:48536)    - (1:56608)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:56616)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:56624)    - (1:64696)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:64704)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:64712)    - (1:72784)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:72792)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:72800)    - (1:80872)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:80880)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:80888)    - (1:88960)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:88968)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:88976)    - (1:97048)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:97056)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:97064)    - (1:105136)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:105144)   -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:105152)   - (1:113224)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:113232)   -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:113240)   - (1:121312)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:121320)   -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:121328)   - (1:129400)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:129408)   -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:129416)   - (1:130480)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:130488)   -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:130496)   - (1:132024)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:132032)   - (1:132088) NOT ALLOCATED
*/

-- Look at SGAM
DBCC PAGE(0, 1, 3, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
This view only changed to show no additional Shared Extents are allocated, though you can see
the number of 8kb data-pages reserved in the db grew to 132,088

ParentObject  Object                     Field                      VALUE
------------- -------------------------- -------------------------- --------------
SGAM: Header  SGAM: Extent Alloc Status  (1:0)        - (1:304)     NOT ALLOCATED
SGAM: Header  SGAM: Extent Alloc Status  (1:312)      -                 ALLOCATED
SGAM: Header  SGAM: Extent Alloc Status  (1:320)      - (1:132088)  NOT ALLOCATED
*/

TRUNCATE TABLE T1

-- Let the background process mark the extents as NOT ALLOCATED, so wait 10 seconds before running this
-- Recheck GAM and SGAM
-- Look at GAM
DBCC PAGE(0, 1, 2, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
This shows that all pages that were previous used by T1 have been deallocated

ParentObject   Object                   Field                     VALUE
-------------- ------------------------ ------------------------- --------------
GAM: Header    GAM: Extent Alloc Status (1:0)        - (1:312)        ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:320)      - (1:132088) NOT ALLOCATED
*/

-- Look at SGAM
DBCC PAGE(0, 1, 3, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
No changes here, as the db didn't grow and we haven't done anything regarding mixed extents

ParentObject  Object                     Field                      VALUE
------------- -------------------------- -------------------------- --------------
SGAM: Header  SGAM: Extent Alloc Status  (1:0)        - (1:304)     NOT ALLOCATED
SGAM: Header  SGAM: Extent Alloc Status  (1:312)      -                 ALLOCATED
SGAM: Header  SGAM: Extent Alloc Status  (1:320)      - (1:132088)  NOT ALLOCATED
*/

-- Repopulate T1
INSERT INTO T1 (Value)
SELECT TOP 131072 ''
FROM sys.configurations t1
    CROSS JOIN sys.configurations t2
    CROSS JOIN sys.configurations t3
    CROSS JOIN sys.configurations t4

-- Table is 1GB in Size
EXEC sp_spaceused 'T1'

/*
name  rows    reserved    data        index_size  unused
----- ------- ----------- ----------- ----------- -------
T1    131072  1052616 KB  1048576 KB  3904 KB     136 KB
*/

-- Recheck GAM and SGAM
-- Look at GAM
DBCC PAGE(0, 1, 2, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
Again, this allocates 131072 8kb data-pages for the data and 488 8kb data-pages for the index
This view shows the location of the allocated and unallocated pages within the datafile and they've
likely changed slightly from when they were first allocated, though the counts remain consistent

ParentObject   Object                   Field                     VALUE
-------------- ------------------------ ------------------------- --------------
GAM: Header    GAM: Extent Alloc Status (1:0)        - (1:8080)       ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:8088)     -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:8096)     - (1:16168)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:16176)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:16184)    - (1:24256)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:24264)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:24272)    - (1:32344)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:32352)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:32360)    - (1:40432)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:40440)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:40448)    - (1:48520)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:48528)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:48536)    - (1:56608)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:56616)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:56624)    - (1:64696)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:64704)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:64712)    - (1:72784)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:72792)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:72800)    - (1:80872)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:80880)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:80888)    - (1:88960)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:88968)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:88976)    - (1:97048)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:97056)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:97064)    - (1:105136)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:105144)   -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:105152)   - (1:113224)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:113232)   -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:113240)   - (1:121312)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:121320)   -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:121328)   - (1:129400)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:129408)   -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:129416)   - (1:130416)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:130424)   -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:130432)   - (1:131960)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:131968)   - (1:132024) NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:132032)   - (1:132088)     ALLOCATED
*/

-- Look at SGAM
DBCC PAGE(0, 1, 3, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
Still no changes here, as the db didn't grow and we haven't done anything regarding mixed extents

ParentObject  Object                     Field                      VALUE
------------- -------------------------- -------------------------- --------------
SGAM: Header  SGAM: Extent Alloc Status  (1:0)        - (1:304)     NOT ALLOCATED
SGAM: Header  SGAM: Extent Alloc Status  (1:312)      -                 ALLOCATED
SGAM: Header  SGAM: Extent Alloc Status  (1:320)      - (1:132088)  NOT ALLOCATED
*/

DROP TABLE T1

-- Let the background process mark the extents as NOT ALLOCATED, so wait 10 seconds before running this
-- Recheck GAM and SGAM
-- Look at GAM
DBCC PAGE(0, 1, 2, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
Again, this shows that all pages that were previous used by T1 have been deallocated.
This is the same behavior we saw with a TRUNCATE, even though we just executed a DROP TABLE command.

ParentObject   Object                   Field                     VALUE
-------------- ------------------------ ------------------------- --------------
GAM: Header    GAM: Extent Alloc Status (1:0)        - (1:312)        ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:320)      - (1:132088) NOT ALLOCATED
*/

-- Look at SGAM
DBCC PAGE(0, 1, 3, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
Still no changes here, as the db didn't grow and we haven't done anything regarding mixed extents

ParentObject  Object                     Field                      VALUE
------------- -------------------------- -------------------------- --------------
SGAM: Header  SGAM: Extent Alloc Status  (1:0)        - (1:304)     NOT ALLOCATED
SGAM: Header  SGAM: Extent Alloc Status  (1:312)      -                 ALLOCATED
SGAM: Header  SGAM: Extent Alloc Status  (1:320)      - (1:132088)  NOT ALLOCATED
*/

-- Cleanup
USE [master]
GO

DROP DATABASE [TestDB]
GO

For more information on the GAM, SGAM, extents, and the basics to the storage engine, start with the Pages and Extents Architecture Guide by Microsoft.  Hopefully this answers your question, though I feel you'll have more questions to follow-up with.


Any different guidance for tempdb with Availability Groups?

You pretty much always want more than a single TempDB file.  I'd certainly not go above 8 files in most situations; and probably more like 4.  Having said that, the only way to know the best configuration is to test it.

This applies equally to SQL Server instances of all kinds, including high availability setups, not just AlwaysOn Availability Groups.


How can I use 'system pwd' command with MySql CONCAT() function?

system is not a recognized MySQL server command. This will only work on the MySQL command line interface "mysql". With a command like this:

mysql> system pwd
/tmp

you will get the client's current directory, nothing to do with the server. You cannot use that information on server side, for executing queries. If you want to concat the pwd with something else, use a script at client side (bash, php). For example:

$ echo $(pwd)test
/tmptest
$ mysql  -e "SELECT concat('$(pwd)', 'test')" # there is not reason to do this at all
+------------------------+
| concat('/tmp', 'test') |
+------------------------+
| /tmptest               |
+------------------------+

While there is a custom non-standard UDF library to make MySQL server interact with the operating system, I do not see a valid use in your case.

If you want to know the directory where MySQL is installed, where the datafiles are, etc., you can execute:

mysql> SHOW GLOBAL VARIABLES like '%dir';
+---------------------------+--------------------------+
| Variable_name             | Value                    |
+---------------------------+--------------------------+
| basedir                   | /usr/local/mysql         |
| datadir                   | /usr/local/mysql/data/   |
...


Special thanks to @WilsonHauck. After 4 weeks of monitoring, Manually upgrading Aurora to the latest version solves the issue.

There have been several bugfixes addressing unexpected restarts on 2.01.1. https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/AuroraMySQL.Updates.20Updates.html

To manually upgrade your Aurora:

Go to RDS - AWS Console
Navigate to Clusters
Select your cluster
Click Actions >> Upgrade now


error recovering pg_dump compressed file

Just use pg_restore:

  pg_restore is a utility for restoring a PostgreSQL database from an
  archive created by pg_dump in one of the non-plain-text formats

Using -Z5 -Fc on pg_dump does not transform the backup into a gzip format. It adjusts the compression level, but the structure of the file is still the specific custom archive format that non-database commands like gzip or 7zip don't know.


How wrong is my web app modeling schema?

Seriously look at third normal form.  I would use surrogate keys with the natural keys implemented as unique keys. You will likely find that author belongs in its own authors table.  You may find you have a few tables which are quite similar such as user_content_faves, user_author_faves, user_author_shares.  This is normal.

Having a single content table with a content_type column may be appropriate.  The content column would need to be capable of storing all the content types.

EDIT: For relationship tables I usually name the join table by concatenating the names of the joined tables, abbreviating as necessary. If there multiple relationships between the two tables, I use one of two options:

Append the purpose of the relationship (as I did above); or
Add a type/reason code to the relationship (in which case the type is not needed in the relationship name).

I made the assumption that you would want to track who favorited or shared things.  It appears that both the producer (author or user) as well as the content items.  Therefore you have users favoriting a producer (user_author_faves now user_user_faves), or a product (user_content_faves).   Depending on how you do sharing,

it could be an attribute on the content, or
a relationship like user_content_shares, where content is share with a particular user.  Re-sharing could be problematic, if you track who shared things, and multiple users share the same content to the same user.  Un-sharing re-shared items is problematic if you don't track who did the sharing.

You may want to consider (and set policies for):

tracking/audit information like when something was done (added, favorited, shared, etc).
whether to do physical or logical deletes.
if you do logical deletes how to handle refavoriing or sharing something after a logical delete.

When indexing relationship tables I generally have the primary key consisting of the primary keys of the two tables being joined.  A second index with the primary keys reversed, or just the primary key which is the second column in the primary key is usually required.  If the relationship between two rows can occur more than once, the column(s) used to differentiate the reason/type and/or timing (since date) of the relationship needs to added to the primary key.


Execute permission denied on object sp_start_job

I don't like the TRUSTWORTHY option because it significantly increases your exposure to a variety of things. As Remus explains in this answer, it essentially elevates any db_owner to sysadmin. Some other things worth reading are a series on TRUSTWORTHY by Sebastian Meine, the BOL topic, and a KB article (even though the assembly portions may not be relevant to you in this scenario):

The TRUSTWORTHY Database Property Explained – Part 1
The TRUSTWORTHY Database Property Explained – Part 2
The TRUSTWORTHY Database Property Explained – Part 3
TRUSTWORTHY Database Property
KB #2183687 : Guidelines for using the TRUSTWORTHY database setting in SQL Server

(And there are tons of other posts out there cautioning against the blind use of this property - just because it works and it is easy doesn't mean it's the right thing to do - in fact that should make you question it even more.)
So I would suggest a different approach (and there are still others, such as signing with a certificate, but this has always worked for me):

Create the procedure in msdb.
Create a user for this user's login in msdb:

USE msdb;
GO
CREATE USER floobarama FROM LOGIN floobarama;

Grant the user execute privileges on the stored procedure:

GRANT EXECUTE ON [dbo].[RunJob] TO floobarama;

Test it - either by calling the procedure from another database:

USE tempdb;
GO
EXECUTE AS LOGIN = N'floobarama';
GO
EXEC msdb.dbo.RunJob @job_name = N'whatever';
GO
REVERT;

Or an easier test, in case you don't want to run any job now and don't want to wait until this user executes it to find out whether they have sufficient access to msdb:

USE msdb;
GO
CREATE PROCEDURE dbo.whatever
WITH EXECUTE AS N'sysadminaccount'
AS
BEGIN
  SET NOCOUNT ON;
  SELECT [I am really...] = SUSER_SNAME();
END
GO
GRANT EXECUTE ON dbo.whatever TO floobarama;

USE tempdb;
GO
EXECUTE AS LOGIN = N'floobarama';
GO
EXEC msdb.dbo.whatever;
GO
REVERT;

Result should be:

I am really...
---------------
sysadminaccount

Validate that this doesn't expose anything else in msdb to this user:

USE tempdb;
GO
EXECUTE AS LOGIN = N'floobarama';
GO
SELECT job_id FROM msdb.dbo.sysjobs;
GO
REVERT;

Result should be...

  Msg 229, Level 14, State 5, Line 21
  The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'.

...since creating a user in a database doesn't give them automatic rights to anything in that database; you need to explicitly do so either for that user, or for a role or group they are in (including public).


Creating new database and insert values in Oracle 10g xe hr account?

You can create a new user/schema using create user clause and then start creating database tables and other objects into this schema. I would not recommend you to exploit hr schema, I would keep that schema intact and use it for testing purposes.


When REBUILDing indexes on SQL Server, what bearing does tempdb & LOG disk speed have?

Indexes are treated the same as any other data modification i.e. they are written to the log.  The performance of your log will directly impact the performance of all writes to your database.  If your LOG drive is 50 times slower that your data drive, you have serious performance limitations and should consider rectifying that as soon as possible.


Like @Shanky mentioned in the comment, it could be due to Express Edition accidently being installed instead of Standard or Enterprise. Due to the Database size size limit, I don't think you could even restore a large database, so you would notice almost immediately.

As an experiement, have you tried caching a lot of data in memory by doing several large SELECTs to see if it changes? SQL Server is fairly memory-hungry, but by default it doesn't use memory until it needs it, so if nothing has happened since the last restart, there's nothing in memory yet.


Why is SQL Server memory showing in task manager

The number reported by Task Manager is not inaccurate exactly; you just have to understand what the number is telling you.

If you expand the Task Manager column so you can see the whole title, you'll see it says: Memory (Private Working Set). The working set does not include any memory that is being accessed via Address Windowing Extensions (AWE).

AWE is a technology API that allows a 32-bit process to access more memory than its 4GB virtual address space, by mapping and remapping a small 'window' of virtual address space onto a larger physical memory space.

AWE is how 32-bit SQL Server is able to access more than 4GB of memory, and the same mechanism is used in 64-bit SQL Server to implement the 'locked pages' feature.

So, Task Manager will show only a relatively small amount of memory in use for:

32-bit SQL Server using AWE to access memory; or
64-bit SQL Server using locked pages

In your case, with Task Manager reporting over 4GB in use, it seems likely the SQL Server you were looking at was a 64-bit installation that is not using locked pages. Instead of using Task Manager, use the Performance Monitor SQL Server instance counter Total Server Memory, or query the sys.dm_os_process_memory DMV in SQL Server 2008 and later.

Reference and further reading: Fun with Locked Pages, AWE, Task Manager, and the Working Set by Microsoft Customer Service and Support (CSS) SQL Support.


Do you think is necessary to create an auxiliary translate table for table that has only two elements (id and name)

Definitely the first one with both Allergen and AllergenTranslation tables.

The reason is that Allergen is likely to be referenced in other places. Say you have a table of medicines each of which is effective against a subset of allergens. What would be the foreign key in this Efficacy table? You don't want that FK to have LanguageId in it at all - the medicine's response is not conditional on the patient's speech. Nor do you want to repeat rows in Efficacy for each language as this is redundancy leading to duplication and errors. Think of the changes required to introduce another language.