How to quickly cascade rare delete to a large table? I have a schema that looks like this:

create table items (
    id serial primary key
);

create table revisions (
    id serial primary key,
    item_id int not null references items(id),
    name text not null,
    property_a text,
    property_b text,
    property_c text
);

create table deltas (
  id serial primary key,
  item_id int not null references items(id) on delete cascade,
  old_revision_id int not null references revisions(id) on delete cascade,
  new_revision_id int not null references revisions(id) on delete cascade
);

This schema can't be changed.

We need to support the rare operation of deleting from items or revisions with the delete cascading to the deltas table. These deletes rarely happen compared to inserting/selecting data from the deltas table (but at least ~20-30 times a day).

A major problem is that the deltas table is very big so these cascades were very slow (taking > 30 seconds). So, we added separate indexes to item_id, old_revision_id, and new_revision_id which turned the cascading delete into a sub-millisecond operation. Side note: on our actual table, there are other columns and other indexes to support our application's normal query patterns.

This introduced a new problem: the combined size of these indexes for the deltas table is now ~7-8 times the size of the actual table and the rate of growth is pretty high since the deltas table has a lot of writes to it.

It seems silly to use many large indexes to support such a rare operation. We have a couple of options to solve this:

Leave it with multiple indexes and deal with the disk size as a separate problem.
Re-architect the application to avoid doing any deletes for these tables to prevent the cascades from happening in the first place.
Change our indexes.

For #3, I read in the Postgres docs that we might be able to use a multi-column GIN index on (item_id, old_revision_id, new_revision_id) which could support the cascading delete like delete from changeset_deltas where old_revision_id = <deleted_revision_id>:

  A multicolumn GIN index can be used with query conditions that involve any subset of the index's columns. Unlike B-tree or GiST, index search effectiveness is the same regardless of which index column(s) the query conditions use.

Am I correct in understanding this and will it make a difference rather than 3 separate indexes or should we take a different approach?

The GIN index certainly can be used for the cascading delete (you will need to create extension btree_gin before creating the index).

How much space it will save you is hard to predict, it would depend on how much duplication there is in the values of those columns.

Having 3 separate GIN indexes will also work, and is likely slightly smaller than the one combined index.  Each value in a combined index has to "remember" which column it came from, which takes up extra space.

If the btree indexes are bloated, whatever is causing that might cause the gin index to get bloated as well.


Extracting the maximum of several subsets and join all maximums in a new table I have a big table:

What I need is to obtain the maximum value for each testname, with different entry and get a new table such as:

And then I want to compute the maximum, the minimum and the average value for this new table.

Can you help me?

First step:

SELECT testname, entry, MAX(`value`) as max_value
FROM your_table
GROUP BY testname, entry

Second step:

SELECT MAX(max_value), MIN(max_value), AVG(max_value)
FROM ( /*here we insert the query from the first step*/
    SELECT testname, entry, MAX(`value`) as max_value /*rename the column so we can conveniently access it from the outer query*/
    FROM your_table
    GROUP BY testname, entry
) sq /*sq is an alias for the subquery, it's needed*/


Concurrent connections to the same table We are a small firm, with about 10 people using a database server.

I have this application that connects to a database and fills about 40 rows of data in an table. Then calls a stored procedure to process the data before inserting the data back into another table and then truncates the table.

Is it possible if two people run the application at the same time, the data can be nonsensical. If so, how can I prevent this?

One approach:

At the beginning of the operations use SELECT GET_LOCK('no-nonsense');
At the end, do SELECT RELEASE_LOCK('no-nonsense');


How to get a list of postgresql indexes that have not been used in any queries recently? Is there a way to get a list of the indexes that have not been used by any queries since some arbitrary cutoff time in the past? Or more simply, a list of all indexes along with a timestamp of when each one was last used in the execution of a query?

What I've tried so far

I've read the Wall Of Text at https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-ALL-INDEXES-VIEW, but a solution is not obvious (I'm mostly a MySQL user and I find PostgreSQL documentation very unclear, lost in the details).

The time of last use is not stored, but you can use the query from my blog to find all indexes that have never been scanned and server no other purpose either:

SELECT s.schemaname,
       s.relname AS tablename,
       s.indexrelname AS indexname,
       pg_relation_size(s.indexrelid) AS index_size
FROM pg_catalog.pg_stat_user_indexes s
   JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
WHERE s.idx_scan = 0      -- has never been scanned
  AND 0 <>ALL (i.indkey)  -- no index column is an expression
  AND NOT i.indisunique   -- is not a UNIQUE index
  AND NOT EXISTS          -- does not enforce a constraint
         (SELECT 1 FROM pg_catalog.pg_constraint c
          WHERE c.conindid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;

Indexes can serve other purposes than scanning:

implement constraints
make autoanalyze gather statistics on the indexed expression


Database placement on abstract storage structure Often times we all hear the same thing.  The recommendations on where to put OLAP/OLTP databases, where to put the tempdb, where to put transaction logs...etc.

But say you are in an environment where the physical definition of the storage is a mystery to you.  In other words, sure you can see logical drives, and you can give the system admin a call and ask for more space, but you never really know what is a different physical drive (say there's a SAN or a NAS behind the scenes).  If you don't know this for fact, how can you follow best practices by putting, say, the heavily hit tempdb database on a different physical drive?

What's the best practice on this one?

What I would do is to benchmark the performance before and after the changes. There should be a performance gain after tempdb is moved to another drive. Use DMVs like sys.dm_io_virtual_file_stats to see the read and write wait times for the DB files.

Use the perfmon physical disk counters: Avg. Disk sec/Read, Avg. Disk sec/Write, Disk Reads/sec, Disk Writes/sec. The change in these metrics will let you know if the disks are physically separate or are just the same disks which are logically separated.

Want a third party disk benchmarking tool for your disks, so that the SAN admins cannot accuse SQL server of its results? Use SQLIO. Although there is SQL in the name, this tool does not require SQL server to run and is not related to SQL server.

This is a good question, and I look forward to see the other answers.


Oracle SQL Loader - load file where record is composed of "n" delimiters How would you write a control file for Oracle SQL Loader that treats each record as being composed of 3 commas?

I have read the documentation, however I cannot find how to handle this scenario, without having to edit my data file by hand.

For example, my data file has:

fd,3232,gfd67gf,
peas,989767,jkdfnfgjhf,
dhdhjsk,267,ujfdsy,fuyds,637296,ldosi,fduy,
873,fuisouyd,try
save,2837,ipoi

I would like to load the data into the table looking like this:

col1       col2       col3

fd         3232       gfd67gf
peas       989767     jkdfnfgjhf
dhdhjsk    267        ujfdsy
fuyds      637296     ldosi
fduy       873        fuisouyd
try\nsave  2837       ipoi

Note: Where I have "\n" in the last row of col1, I mean an actual line feed not literally "\n".

So the linefeed before peas and dhdhjsk should be skipped but the linefeed before save is part of the data. Which linefeeds should be skipped and which are part of the data? I think such a format is very error prone and should be avoided. Perhaps you can do it the following way but I did not verify this

define "," as the physical record separator
join three physical records to on logical records
to skip a leading \n trim leading whitespace from fields  or use sql-functions

The following links to the manual can provide information:

Concatenating physical records to logical records

Input formats

Whitespace

Apply SQL functions

Specifying delimiters


Count of open sockets to the database I want to check open socket connection count to the database.

Is there any way to get the number of open sockets to the database?

Yes.  This query will give you the current number of client connections using TCP/IP.  Of course there are other things that use sockets on the server.

select count(*) connections
from sys.dm_exec_connections
where net_transport = 'TCP'

You can see all the sockets usage on the server using the netstat command.


Do NDF files move with Alter Database automatically I know you don't want to hear this but i am not a DBA, but I am responsible for a 2016 SQL server.  From time to time I need to do some DBA work.

This time I need to move the tempdb (mdf, ndf, and ldf files) to another volume.

When i run the alter database query do i need to specify the ndf files in the query?  Or do those move automatically because they are part of the file group?

Being each of the ndfs have logical names, I am thinking I need to, but just want to be certain.

I know I need to specify the LDF files.

These are all the files we have associated with the TEMPDB:

  tempdev   C:\Program Files\Microsoft SQL
  Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb.mdf

  templog   C:\Program Files\Microsoft SQL
  Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\templog.ldf

  temp2 C:\Program Files\Microsoft SQL
  Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_2.ndf

  temp3 C:\Program Files\Microsoft SQL
  Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_3.ndf

  temp4 C:\Program Files\Microsoft SQL
  Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_4.ndf

  temp5 C:\Program Files\Microsoft SQL
  Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_5.ndf

  temp6 C:\Program Files\Microsoft SQL
  Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_6.ndf

  temp7 C:\Program Files\Microsoft SQL
  Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_7.ndf

  temp8 C:\Program Files\Microsoft SQL
  Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_8.ndf

Thanks for the help!

-Evan

ALTER DATABASE can be used to do many things. In order to move the file you'll want to use MODIFY FILE to move your files to the specified location. This goes for mdf, ndf, ldf. Erik Darling blogged about this a while back and gave some examples.

ALTER DATABASE [Sample] MODIFY FILE ( NAME = Sample, FILENAME = 'C:\Whatever\Sample.mdf' );

From the docs:

  MODIFY FILE Specifies the file that should be modified. Only one
   property can be changed at a time. NAME must always be
  specified in the  to identify the file to be modified. If
  SIZE is specified, the new size must be larger than the current file
  size.

  To move a data file or log file to a new location, specify the current
  logical file name in the NAME clause and specify the new path and
  operating system file name in the FILENAME clause. For example:

  To move a data file or log file to a new location, specify the current
  logical file name in the NAME clause and specify the new path and
  operating system file name in the FILENAME clause. For example:


Can I artificially limit the Columnstore cache size in SQL Server? I am trying to evaluate the potential performance of a data science workload on a large-ish dataset (~200GB).  We have had excellent results using columnstore indexes in SQL Server 2017.

However, the business is interested in cost savings, and I have pointed out to them that columnstore indexes are now available in Standard Edition, albeit with a memory limit of 32GB.  It's possible to determine the current memory use by looking at DMV sys.dm_column_store_object_pool

I would like to know if it's possible to limit the columnstore-dedicated RAM allocation to evaluate how the workload performance changes.

I'm aware that it's possible to limit the total RAM usage in SQL Server, but that will not provide a real indication of the very specific columnstore limits.

I should clarify that since we are currently using Developer Edition, there are no resource limits at all.

If you have an MSDN license you can download and install SQL Server Standard Edition for Dev/Test.

Otherwise you can use an Azure Pay-As-You-Go instance for testing.  See eg

SQL Server 2017 Standard on Windows Server 2016


SQL Server 2012 stored procedure runs very slow sometimes I have a stored procedure with a single large query which takes an ID as a parameter and returns some counts. It has quite a few inner joins and left outer joins but they are all on indexes.

The stored procedure runs most of the time in around 3-4 seconds, but sometimes takes over 10 seconds (there is a mailer setup in the SQL Server for sending emails for queries that take over 10 seconds). It also ran for over 100 seconds a couple of times. How do I troubleshoot this and what could be the possible reasons for the varied execution times?

The query is something like this:

SET NOCOUNT ON;

DECLARE @date2 datetime;
SET @date2 = GETDATE();

WITH lstats
AS
(
    SELECT
        [T1].id1, [T3].flag1, [T3].num1, [T3].string1,
        [T3].date1, [T3].string2, [T3].id
    FROM
        [dbo].[table1] AS [T1]
    INNER JOIN
        [dbo].[table2] AS [T2] ON [T1].id1 = [t2].id1
    INNER JOIN
        [dbo].[tabl3] AS [T3] ON [T2].[id2] = [T3].[id2]
    WHERE
        ([T3].[flag1] = 1 AND [T3].id = @id
         AND (([T3].[num1] IS NULL) OR (-1 <> CAST( [T3].[num1] AS int))))
)
SELECT
    [l].[id1] AS [id1],
    [l].[id] AS [id],
    [lu].[id] AS [idx],
    CAST(CAST([date2] AS datetime2) AS datetime2) AS [date2],
    (SELECT COUNT(1) AS [A1]
     FROM lstats AS [ls]
     WHERE ([l].id1 = [ls].id1)) AS Count1,
    (CASE WHEN (2 = CAST( [Type] AS int))
            THEN
               (SELECT COUNT(1) AS [A1]
                FROM lstats AS [ls]
                WHERE ([ls].id1 = [l].id1)
                  AND (([ls].[date1] IS NULL) OR ([ls].[date1] <= @date2))
                  AND (([ls].[string2] IS NULL) OR (NOT ([ls].[string2] LIKE '%sometext%'))))
            ELSE
               (SELECT COUNT(1) AS [A1]
                FROM lstats AS [ls]
                WHERE ([ls].id1 = [l].id1)
                  AND (([ls].[string1] IS NULL) OR ('sometext2' <> [ls].[string1]))
                  AND (([ls].[date1] IS NULL) OR ([ls].[date1] <= @date2))
                  AND (([ls].[string2] IS NULL) OR ( NOT ([ls].[string2] LIKE '%sometext%'))))
         END) AS Count2
FROM
    [dbo].[table1] AS [l]
INNER JOIN
    [dbo].[table4] AS [lu] ON [l].[id1] = [lu].[id1]
LEFT OUTER JOIN
    [dbo].[table5] AS [les] ON [l].[id1] = [les].[id1]
LEFT OUTER JOIN
    [dbo].[table6] AS [u] ON [l].[id] = [u].[ID]
LEFT OUTER JOIN
    [dbo].[table7] AS [lt] ON [l].[id3] = [lt].[id3]
WHERE
    ([lu].[id] = @id OR (@someflag = 1 AND [u].ID = @id))

First thing to check is if there's blocking happening. If the procedure / statement is blocked by another process then it's quite clear why it takes longer.

If that's not the case, you can try to check from plan cache if there's something that explains it. Here's one short example query to get some of the figure from there:

select top 100
SUBSTRING(t.text, (s.statement_start_offset/2)+1,
((CASE s.statement_end_offset
WHEN -1 THEN DATALENGTH(t.text)
ELSE s.statement_end_offset
END - s.statement_start_offset)/2) + 1) as statement_text,
t.text,
s.total_logical_reads, s.total_logical_reads / s.execution_count as avg_logical_reads,
s.total_worker_time, s.total_worker_time / s.execution_count as avg_worker_time,
s.execution_count
from sys.dm_exec_query_stats s
cross apply sys.dm_exec_sql_text (sql_handle) t
where t.text like '%name_of_proc%'
order by s.total_worker_time desc

There's also a lot of other columns, like max_elapsed_time, max_worker_time, max_logical_reads etc. That might give some insight.


Why do uncommitted transactions rollback instead of redo after a crash in ARIES? Since all actions within a transaction are idempotent in crash recovery algorithms like ARIES that use write-ahead logging why do we rollback after a crash during an uncommitted transaction to get back to consistent state rather than just re-doing the entire transaction?

This way we would complete the transaction and we don't need to deal with any undo.

Community wiki answer - please edit to add your contribution:

Generally speaking, there is no guarantee that an uncommitted transaction had actually completed everything it needed to do.

A transaction that withdrew $100 from BankA, but has not deposited the money in BankB is an uncommitted transaction.  If a crash occurred between those two processes, there is no way to tell what to redo because we didn't even get to the deposit part of the transaction which could also involve other calculations and updates.

An external application begins a transaction in code and runs several updates, inserts and deletes within that transaction.  Somewhere in the middle of that transaction, the server crashes.  Now, even if the database could replay and harden all of the updates that had happened up until the crash, how would the application know where to restart in its logic?

In a complete undo, you simply start the external application back at the beginning. The absolute safest way to deal with uncommitted transactions during crash recovery is to roll it back as if it had never occurred.

Atomicity - Transactions are often composed of multiple statements. Atomicity guarantees that each transaction is treated as a single "unit", which either succeeds completely, or fails completely: if any of the statements constituting a transaction fails to complete, the entire transaction fails and the database is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors and crashes.

In a properly-implemented ARIES - Crash recovery will start from a checkpoint (stored in master record), then it will need to figure out which transactions committed since checkpoint and which failed , then redo all actions and then UNDO effects of failed Transactions. See the answer to What happens when a DBMS crash during the recovery phase of ARIES algorithms? by Remus Rusanu.

Also, remember that during crash "the contents stored in memory (RAM) are assumed to be corrupted or lost" so they have to be rolled back. Only the ones that are written to disk are valid / stable.


Explain SQL Server deadlock graph modes I got such a deadlock:

I see that /process/@lockMode is equal to /owner/@mode.
But why /keylock/@mode is not equal to /owner/@mode?
What does /keylock/@mode mean?

As I understand from the entire deadlock, processVictim should neither use U lock nor X lock.

Here is the entire deadlock:

<deadlock-list>
 <deadlock victim="processVictim">

  <process-list>
   <process id="processVictim" taskpriority="0" logused="0" waitresource="KEY: 10:72057602742812672 (3100aad8604d)" waittime="2982" ownerId="222541347" transactionname="user_transaction" lasttranstarted="2018-04-28T00:17:29.680" XDES="0x7d6dcf990" lockMode="S" schedulerid="13" kpid="14052" status="background" spid="25" sbid="0" ecid="0" priority="0" trancount="1">
    <executionStack>
     <frame procname="Db1.notify.pRuleActivated" line="71" stmtstart="3296" stmtend="3508" sqlhandle="0x03000e00d9368812ce87f700aaa800000100000000000000">
if exists(select 1 from DbRepl.dbo.vRule where RuleId = @nRuleId_ and CloseDate is not null)     </frame>
     <frame procname="Db1.notify.pRuleActivatedHandler" line="21" stmtstart="928" sqlhandle="0x03000e000f89bc79375d210165a100000100000000000000">
EXEC notify.pRuleActivated
    @xmlMsg = @xmlMsg     </frame>
     <frame procname="Db1.notify.pQppQueueProcNotificationProcessingQueue" line="105" stmtstart="5906" stmtend="6154" sqlhandle="0x03000e0020de2a3dd505d00096a100000100000000000000">
exec @proc_ @xmlMsg = @msg_, @uuidConversationGroup = @cgid_, @uuidConversationHandle = @dh_, @nServiceId = @service_id_;     </frame>
    </executionStack>
    <inputbuf>
    </inputbuf>
   </process>

   <process id="processSurvivor" taskpriority="0" logused="149676" waitresource="KEY: 10:72057602742681600 (460029689516)" waittime="2904" ownerId="222536438" transactionname="user_transaction" lasttranstarted="2018-04-28T00:16:28.673" XDES="0x17ea1c780" lockMode="X" schedulerid="12" kpid="9864" status="suspended" spid="63" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-04-28T00:17:34.753" lastbatchcompleted="2018-04-28T00:17:34.710" isolationlevel="read committed (2)" xactid="222536438" currentdb="10" lockTimeout="4294967295" clientoption1="671156320" clientoption2="128056">
    <executionStack>
     <frame procname="DbRepl.dbo._pReplUpd_dbo_tRule" line="34" stmtstart="1514" stmtend="6938" sqlhandle="0x03000a0067d8b8712176bf00c0a800000100000000000000">
update [dbo].[tRule] set
        [Id] = case substring(@bitmap,1,1) &amp; 2 when 2 then @c2 else [Id] end,
        [TypeId] = case substring(@bitmap,1,1) &amp; 4 when 4 then @c3 else [TypeId] end,
        [EventId] = case substring(@bitmap,1,1) &amp; 8 when 8 then @c4 else [EventId] end
where [RuleId] = @pkc1     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 10 Object Id = 1907939431]    </inputbuf>
   </process>
  </process-list>

  <resource-list>
   <keylock hobtid="72057602742812672" dbid="10" objectname="DbRepl.dbo.tEvent" indexname="PK_tEvent" id="lock261441400" mode="X" associatedObjectId="72057602742812672">
    <owner-list>
     <owner id="processSurvivor" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="processVictim" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>

   <keylock hobtid="72057602742681600" dbid="10" objectname="DbRepl.dbo.tRule" indexname="PK_tRule" id="lock476f3d680" mode="U" associatedObjectId="72057602742681600">
    <owner-list>
     <owner id="processVictim" mode="S"/>
    </owner-list>
    <waiter-list>
     <waiter id="processSurvivor" mode="X" requestType="convert"/>
    </waiter-list>
   </keylock>
  </resource-list>

 </deadlock>
</deadlock-list>

And it's visualization:

Both sessions have locks on the second key.  The reader has an S and the writer has a U and wants to convert to X.  Consider changing the database to use READ_COMMITTED_SNAPSHOT mode, or add an XLOCK hint to the UPDATE so it doesn't read with U locks and then convert to X.

  What are these modes.

I think that is the most restrictive lock locks currently held on the resource, as opposed to the locks requested.  This is often the lock that is incompatible with the blocked session's request, but not always.

  Why is this mode not S.

Because the most restrictive lock currently held on the resource is the U lock held by the writer session, and which it has requested to "convert" from U to X.


Aggregating data by group and time gaps I've got a postgresql+postgis database filled with GPS data:

CREATE TABLE "my_table" (
    "id" serial NOT NULL PRIMARY KEY,
    "gps_id" text NOT NULL,
    "lat" numeric NOT NULL,
    "lon" numeric NOT NULL,
    "speed" numeric NOT NULL,
    "geom" geometry,
    "timestamp" timestamp with time zone NOT NULL,
);

My data comes in regularly but there are some gaps. I'd like to find the average speed and build up a linestring from the data grouped by "gps_id" AND by gaps greater than X hours:

SELECT
   gps_id,
   avg(speed) as avg_speed,
   ST_MakeLine(my_table.geom ORDER BY timestamp) as line
FROM my_table
GROUP BY gps_id

It's pretty easy to do find the basic aggregate but grouping by time-gap has me stumped. I've tried to group by time buckets (using date_trunc) and then counting the number of gps messages within the time bucket and tossing if < X but that's not really the behaviour I want. I'd like to be able to get something like:

Data Output:

gps_id,       avg_speed,     line,        gap
ABC,          12.01          01002...     0
ABC,          20,20          01002...     1
DEF,           0,2           01002...     0

A bonus would be to find the start and end times of each group so that I could eyeball where/when the gaps are happening.

Any ideas?

As far as I understood the task you want to calculate average speed for every 4 hours as example. Means 0:00-3:59 o'clock, then 4:00-7:59 etc. So, probably it'll help you if you will use date part and floor functions as

floor(date_part('hour', timestamp) / 4)

then you'll get a rank of time gap.


SQL Server 2008 R2 Drops Connections when Anti-Virus Scan is running I have a SQL Server 2008 R2 Enterprise (SP2) cluster (2 nodes Windows Server 2008 R2 SP1, NetApp Shared storage).

There are 5 instances all running on this cluster in an active passive configuration and we have found that occasionally during the time that the weekly antivirus scan runs, one of the instances starts to drop connections and becomes inaccessible for about an hour (the scan runs for 2 hours), all the other instances are fine.

In the error log, we see the following messages when the server is inaccessible:

  TCP connection closed but a child process of SQL Server may be holding
  a duplicate of the connection's socket.  Consider enabling the
  TcpAbortiveClose SQL Server registry setting and restarting SQL
  Server. If the problem persists contact Technical Support.

  Error: 26073 Severity: 16 State: 1.

They are also accompanied by the following error (although not all the time):

  Process 0:0:0 (0x15540) Worker 0x0000000E721041A0 appears to be
  non-yielding on Scheduler 20. Thread creation time: 13025544955370.
  Approx Thread CPU Used: kernel 0 ms user 0 ms. Process Utilization
  0%. System Idle 91%. Interval: 119402 ms.

During this time there are also some warnings with IO operations taking longer than 15 seconds to complete.

What I'd like to know is:

We have excluded all the locations recommended by Microsoft from scanning, so why is this only happening during the scan, can these issues just be caused by the extra load of the scan, they're pretty powerful boxes so I wouldn't have thought that the extra load would cause a problem, and the time at which this runs, the server is not busy, also if it is caused by the scan why does the problem only stay for 1 hour rather than the full 2?

What is the TcpAbortiveClose registry setting and what are the implications of this setting, I know how to set it from the Microsoft KB article(http://support.microsoft.com/kb/2491214), but I can't find any documentation on this setting anywhere on what it does or whether it is a good idea.

I see that you are still running SQL Server 2008 R2 SP1, so I would suggest that you update your software.  If you update to SQL Server 2008 R2 SP2 CU 2 there is a fix for this problem.

You can read about this particular problem at: http://support.microsoft.com/kb/2491214

I would recommend not applying the "workaround" but just getting up-to-date with SP2 CU2 (at least).  You should know that SP2 CU8 is available.


Is Oracle Configuration Manager HIPAA compliant? Does Oracle's Configuration Manager which according to Oracle provides a "40% faster issue resolution", violate HIPAA or require specific configuration in order to comply?

This question concerns databases that contain Protected Healthcare Information (PHI).  The questions scope only covers OCM and not the broader scope of Oracle support accessing the database.

The following from Oracle describes what OCM collects:

  Oracle Configuration Manager can automatically gather the
  configuration information of Oracle product installs, and upload this
  information onto Oracle’s support systems. The configuration
  information being collected by Oracle Configuration Manager includes:
  • Installed patches
  • Deployment platforms, dates, versions, and type
  • Deployed components and applications
  • Content of configuration files
  • Information about network configurations

  Note that the information collected by Oracle Configuration Manager is
  limited to configuration information. The utility does not collect
  sensitive data such as actual customer data (that is any data other
  than configuration information, including actual applications or
  database transactions), password hash values, log on events, etc. My
  Oracle Support note 728985.1 provides a list of all the data collected
  by Oracle Configuration Manager.

According to hss.gov the following information is protected by HIPAA:

  The Privacy Rule protects all "individually identifiable health
  information" held or transmitted by a covered entity or its business
  associate, in any form or media, whether electronic, paper, or oral.
  The Privacy Rule calls this information "protected health information
  (PHI)."12

  “Individually identifiable health information” is information,
  including demographic data, that relates to:

  • the individual’s past, present or future physical or mental health or condition,
  • the provision of health care to the individual, or
  • the past, present, or future payment for the provision of health care to the individual,

  and that identifies the individual or for which there is a reasonable
  basis to believe it can be used to identify the individual.13
  Individually identifiable health information includes many common
  identifiers (e.g., name, address, birth date, Social Security Number).

Yes, as long as the data being transmitted is monitored in a "reasonable" way.
If the database hosts PHI and oracle is assisting in the management of the database you must have a written contract with the vendor.

Standard: Business associate contracts and other arrangements. A covered entity, in accordance with §164.306, may permit a business associate to create, receive, maintain, or transmit electronic protected health information on the covered entity's behalf only if the covered entity obtains satisfactory assurances, in accordance with §164.314(a) that the business associate will appropriately safeguard the information.
Document the satisfactory assurances required by paragraph (b)(1) of this section through a written contract or other arrangement with the business associate that meets the applicable requirements of §164.314(a).

You have to log the vendors access into the database and ensure they cannot access PHI.

(A) Authorization and/or supervision (Addressable). Implement procedures for the authorization and/or supervision of workforce members who work with electronic protected health information or in locations where it might be accessed.
(C) Log-in monitoring (Addressable). Procedures for monitoring log-in attempts and reporting discrepancies.
(B) Access authorization (Addressable). Implement policies and procedures for granting access to electronic protected health information, for example, through access to a workstation, transaction, program, process, or other mechanism.
(a)(1) Standard: Access control. Implement technical policies and procedures for electronic information systems that maintain electronic protected health information to allow access only to those persons or software programs that have been granted access rights as specified in §164.308(a)(4).
(b) Standard: Audit controls. Implement hardware, software, and/or procedural mechanisms that record and examine activity in information systems that contain or use electronic protected health information.

If they access PHI you will have to log the incident and report it.
Please see http://www.hhs.gov/ocr/privacy/hipaa/administrative/privacyrule/adminsimpregtext.pdf page 38 for more information on HIPAA IT policy's.
The thing about HIPPA is that most of the requirements are vague and it asks you to make "reasonable" steps to prevent a PHI information breach (It marks these items as addressable). I've been through a couple of HIPAA audits myself.


I've created a view for a report, it now takes 30 minutes to run - Any ideas? View the actual execution plan here

The plan is showing what happens when a stored procedure is called to get the payments that are associated with a client.

Unfortunately there is no other way to get this data than by inner joining a whole bunch of tables together.

Objec5.Index11 is on the Payments table (nothing sensitive, just a record from the gateway to confirm the transaction). This is 457mb of data according to SQL Sentry Plan Explorer. The rest of the clustered index seeks following on from the inner joins are all roughly the same amount of data equating to somewhere between 10% and 13% of the execution cost of this query.

My questions are:

What is the best approach to getting data from multiple tables? I've joined them together via inner joins and dropped them into a view
Should the view have a clustered index? Can this only be achieved with schemabinding? If so, will this prevent field level changes to all tables within the view?
Would having better estimated row values help SQL Server run this query more efficiently? Or is the approach so fubar that this just is not efficient by design?
Is there anything i can do in the short term to boost the performance of this query?

Any help would be appreciated.

I've provided responses to each of the questions below:

SET STATISTICS IO ON output here
All of the tables have a clustered index on them on the primary key
The query consistently performs poorly. However, modifying the query to join on the tables themselves as opposed to views speeds the time the stored proc takes to run quite significantly (from 20 mins to 6 mins) but it still is not fast enough for production
The query used to run within 1 - 2 minutes. Now the fastest is 6 minutes
Index11 is the payments table, there are 1,570,473 rows according to the execution plan actual rows. This is the same table where there were over 4 million logical reads and 1.4 million scans - This does not seem normal to me or conducive to performance? I could be wrong ...

Unable to get to the root cause of the performance issue I've backed out the stored procedure changes. These changes introduced mostly left joins on views. Not sure whether the 1.4m row table scan was having an impact on performance.

With the code changes reverted reports are now running every 3 - 4 minutes. This includes the time to write the file to disk. This code will likely not see the light of day again.


Calculate row size and max row size for a table Problem:

Is there any way of calculating number of bytes occupied by the table creation, i know that you can get some information from information_schema.tables but that information is not accurate enough.

What actually required is the number of bytes according to the definition of the table for innodb only and collation could also be considered as utf-8-general-ci

For example a table test is as following

  create table test
  (
  col1 varchar(25),
  col2 int,
  col3 varchar(3),
  col4 char(15),
  col5 datetime
  );

Now would require to know the total row size that can be accumulated in one row according to the types of columns in the table.

Found a some sort of similar solution in MSSQL but need its MySQL version

Script to estimate row sizes for any table

Any help is much appreciated.

After much of thinking and researching found one answer which really helped in achieving what  was required. It is a perl script and reference link is

http://dev.mysql.com/doc/refman/5.6/en/storage-requirements.html

#!/usr/bin/perl
use strict;
$| = 1;

my %DataType = (
"TINYINT"=>1, "SMALLINT"=>2, "MEDIUMINT"=>3, "INT"=>4, "INTEGER"=>4, "BIGINT"=>8,
"FLOAT"=>'$M<=24?4:8', "DOUBLE"=>8,
"DECIMAL"=>'int(($M-$D)/9)*4+int(((($M-$D)%9)+1)/2)+int($D/9)*4+int((($D%9)+1)/2)',
"NUMERIC"=>'int(($M-$D)/9)*4+int(((($M-$D)%9)+1)/2)+int($D/9)*4+int((($D%9)+1)/2)',
"BIT"=>'($M+7)>>3',
"DATE"=>3, "TIME"=>3, "DATETIME"=>8, "TIMESTAMP"=>4, "YEAR"=>1,
"BINARY"=>'$M',"CHAR"=>'$M*$CL',
"VARBINARY"=>'$M+($M>255?2:1)', "VARCHAR"=>'$M*$CL+($M>255?2:1)',
"ENUM"=>'$M>255?2:1', "SET"=>'($M+7)>>3',
"TINYBLOB"=>9, "TINYTEXT"=>9,
"BLOB"=>10, "TEXT"=>10,
"MEDIUMBLOB"=>11, "MEDIUMTEXT"=>11,
"LONGBLOB"=>12, "LONGTEXT"=>12
);

my %DataTypeMin = (
"VARBINARY"=>'($M>255?2:1)', "VARCHAR"=>'($M>255?2:1)'
);

my ($D, $M, $S, $C, $L, $dt, $dp ,$bc, $CL);
my $fieldCount = 0;
my $byteCount = 0;
my $byteCountMin = 0;
my @fields = ();
my $fieldName;
my $tableName;
my $defaultDbCL = 1;
my $defaultTableCL = 1;
my %charsetMaxLen;
my %collationMaxLen;

open (CHARSETS, "mysql -B --skip-column-names information_schema -e 'select CHARACTER_SET_NAME,MAXLEN from CHARACTER_SETS;' |");
%charsetMaxLen = map ( ( /^(\w+)/ => /(\d+)$/ ), <CHARSETS>);
close CHARSETS;

open (COLLATIONS, "mysql -B --skip-column-names information_schema -e 'select COLLATION_NAME,MAXLEN from CHARACTER_SETS INNER JOIN COLLATIONS USING(CHARACTER_SET_NAME);' |");
%collationMaxLen = map ( ( /^(\w+)/ => /(\d+)$/ ), <COLLATIONS>);
close COLLATIONS;

open (TABLEINFO, "mysqldump -d --compact ".join(" ",@ARGV)." |");

while (<TABLEINFO>) {
chomp;
if ( ($S,$C) = /create database.*?`([^`]+)`.*default\scharacter\sset\s+(\w+)/i ) {
$defaultDbCL = exists $charsetMaxLen{$C} ? $charsetMaxLen{$C} : 1;
print "Database: $S".($C?" DEFAULT":"").($C?" CHARSET $C":"")." (bytes per char: $defaultDbCL)\n\n";
next;
}
if ( /^create table\s+`([^`]+)`.*/i ) {
$tableName = $1;
@fields = ();
next;
}
if ( $tableName && (($C,$L) = /^\)(?:.*?default\scharset=(\w+))?(?:.*?collate=(\w+))?/i) ) {
$defaultTableCL = exists $charsetMaxLen{$C} ? $charsetMaxLen{$C} : (exists $collationMaxLen{$L} ? $collationMaxLen{$L} : $defaultDbCL);
print "Table: $tableName".($C||$L?" DEFAULT":"").($C?" CHARSET $C":"").($L?" COLLATION $L":"")." (bytes per char: $defaultTableCL)\n";
$tableName = "";
$fieldCount = 0;
$byteCount = 0;
$byteCountMin = 0;
while ($_ = shift @fields) {
if ( ($fieldName,$dt,$dp,$M,$D,$S,$C,$L) = /\s\s`([^`]+)`\s+([a-z]+)(\((\d+)(?:,(\d+))?\)|\((.*)\))?(?:.*?character\sset\s+(\w+))?(?:.*?collate\s+(\w+))?/i ) {
$dt = uc $dt;
if (exists $DataType{$dt}) {
if (length $S) {
$M = ($S =~ s/(\'.*?\'(?!\')(?=,|$))/$1/g);
$dp = "($M : $S)"
}
$D = 0 if !$D;
$CL = exists $charsetMaxLen{$C} ? $charsetMaxLen{$C} : (exists $collationMaxLen{$L} ? $collationMaxLen{$L} : $defaultTableCL);
$bc = eval($DataType{$dt});
$byteCount += $bc;
$byteCountMin += exists $DataTypeMin{$dt} ? $DataTypeMin{$dt} : $bc;
} else {
$bc = "??";
}
$fieldName.="\t" if length($fieldName) < 8;
print "bytes:\t".$bc."\t$fieldName\t$dt$dp".($C?" $C":"").($L?" COLL $L":"")."\n";
++$fieldCount;
}
}
print "total:\t$byteCount".($byteCountMin!=$byteCount?"\tleast: $byteCountMin":"\t\t")."\tcolumns: $fieldCount\n\n";
next;
}
push @fields, $_;
}
close TABLEINFO;

Thanks all for great help.


Performance impact of Extended Events filter [like_i_sql_unicode_string] I have created an extended events session in which I wanted to see all queries using nolock hint. I have decided to use it instead of sql trace in order to minimaze possible performance impact. Session was created like with following statement:

    CREATE EVENT SESSION [SCOMDB_debug] ON SERVER
    ADD EVENT sqlserver.deprecation_announcement(
        ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)),
    ADD EVENT sqlserver.rpc_completed(
        ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)
        WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%NOLOCK%'))),
    ADD EVENT sqlserver.rpc_starting(
        ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)
        WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%NOLOCK%'))),
    ADD EVENT sqlserver.sp_statement_completed(
        ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)
        WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%NOLOCK%'))),
    ADD EVENT sqlserver.sp_statement_starting(
        ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)
        WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%NOLOCK%'))),
    ADD EVENT sqlserver.sql_batch_completed(
        ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)
        WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%NOLOCK%'))),
    ADD EVENT sqlserver.sql_batch_starting(
        ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)
        WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%NOLOCK%'))),
    ADD EVENT sqlserver.sql_statement_completed(
        ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)
        WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%NOLOCK%'))),
    ADD EVENT sqlserver.sql_statement_starting(SET collect_statement=(1)
        ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)
        WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%NOLOCK%')))
    ADD TARGET package0.event_file(SET filename=N'M:\MSSQL11.SCOMDB\MSSQL\Log\SCOMDB_debug.xel',max_file_size=(500))
    WITH (MAX_MEMORY=40960 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
    GO

The result was that database instance (used for SCOM databases) became very slow and application was almost unusable.

Wait stats were full of SOS_SCHEDULER_YIELD and PREEMPTIVE_XE_DISPATCHER and I could see many long running queries in sys.dm_exec_requests waiting mostly on CPU as well.

Is it expecated bahaviour? I can imagine that [like_i_sql_unicode_string] operator can be quite performance consuming but still I would expect much better performance.

Is there any other filter in ex events which could be used to catch queries which contain 'sometext'?

I would say you don't need all the start events, just the completed. I don't see that you are capturing data just for the SCOM database either. Extended events can cause an overhead just near to what SQL Server traces will depending on what you are trying to capture.

You are basically capturing every query that comes across the server and telling it to wait so you can first check the text of the query and then grab information through the extended event session, then letting it complete.

As well the constant activity SCOM has, depending on environment configuration, it is likely to have an extreme performance hit either with trace or extended events. I would suggest looking at the plan cache for those types of queries initially. However you might try adding more filters to your session such only grabbing a sample of data from active sessions and then also only capturing data for the particular database. That would look something like this through the GUI:

Or code your WHERE clause would look something like:

WHERE ((([package0].[divides_by_uint64]([sqlserver].[session_id],(5)))
   AND ([package0].[greater_than_uint64]([sqlserver].[database_id],(4))))
   AND ([package0].[equal_boolean]([sqlserver].[is_system],(0)))))

This would capture I think 20% of the active sessions for that database at any given time. However if you want all events then you would have to suffer the overhead of that action.

With it being a Microsoft product I would not be to concerned with what queries it is producing. If it is experiencing performance issues you should follow Microsoft guidelines for the product or consult Microsoft support.


Is an in-memory table faster to read from than a table cached in memory? Is an in-memory (aka Hekaton) table faster to read from than a non-in-memory table that's already cached in the memory of a SQL server?

I ask this because I have a case where I'm considering converting one of my tables to an in-memory table, but right now it's pretty fast to load into the memory cache, but the performance to read / operate against it after it's loaded into the cache is where it's slow.

Can I still see potential performance improvements by converting this table to an in-memory table?

What is the wait type you experience when your on-disk table is slow to read from when it's already cached?

Memory-optimized tables are optimized for writing, not reading, so it's very unlikely you'd see any performance benefit from migration to Hekaton.


postgres how to drop all the table in a schema through command I use postgresql and I need to drop all the tables which are present in the schema. How can I delete from command prompt.

You want to use the CASCADE option of DROP SCHEMA. From the documentation:

  CASCADE  - Automatically drop objects (tables, functions, etc.) that are
  contained in the schema, and in turn all objects that depend on those
  objects

BE CAREFUL - emphasis above mine.

Obviously you'll need to recreate the schema afterwards.

To just drop all tables in the current schema, you can use this script:

DO $$ DECLARE
    tabname RECORD;
BEGIN
    FOR tabname IN (SELECT tablename
                    FROM pg_tables
                    WHERE schemaname = current_schema())
LOOP
    EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(tabname.tablename) || ' CASCADE';
END LOOP;
END $$;

Change WHERE schemaname = current_schema() if you want to drop all of the tables in a different schema.


select distinct -> join 3 tables Hi i need to mix (more than join) three tables, but the id could be present or not on each table (Please see the example below).

I have spend a few days trying join and union but all time some data is missing!!! can someone give me a fresh idea?

Thanks

If MySQL had implemented FULL joins, this would be fairly simple:

select
    id,
    a.price as price_1701,
    b.price as price_1702,
    c.price as price_1703
from
    t_1701 as a
    full join t_1702 as b using (id)
    full join t_1703 as c using (id)
order by
    id ;

Unfortunately it hasn't (and neither the sister implementation MariaDB), so you have to restore using a UNION and LEFT joins:

select
    id,
    a.price as price_1701,
    b.price as price_1702,
    c.price as price_1703
from
    ( select id from t_1701
      union
      select id from t_1702
      union
      select id from t_1703
    ) as t
    left join t_1701 as a using (id)
    left join t_1702 as b using (id)
    left join t_1703 as c using (id)
order by
    id ;


How do I add a constraint after I've truncated a table? I am writing a script. The purpose of this script is to

Get all foreign key constraints on a database.
Truncate a table.
Re-Add the foreign key constraints that were removed.

I'm having trouble adding the constraints again [step 3] and I believe this is to do with the fact that my constraint conflicts with what is available. The data initially is...

authors table

1   Kenneth Graham  k.graham@twitw.com

books table

1   The wind in the willows Classic childrens story about woodland animals  123456

Authorbooks table

1   1   1

I have the following constraints on the authorbooks tables:

ALTER TABLE [dbo].[AuthorBooks]  WITH CHECK ADD  CONSTRAINT [FK_Books_Author_xref_Books] FOREIGN KEY([BookId])
REFERENCES [dbo].[Books] ([ID])
GO
ALTER TABLE [dbo].[AuthorBooks]  WITH CHECK ADD  CONSTRAINT [FK_Books_Author_xref_Authors] FOREIGN KEY([AuthorId])
REFERENCES [dbo].[Authors] ([Id])
GO

So when I try and add the constraints I get the following error:

  The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
  "FK_Books_Author_xref_Authors". The conflict occurred in database
  "PeteDb", table "dbo.Authors", column 'Id'.

Is there any way around this and being able to add the constraint in regardless?

Use WITH NOCHECK instead of WITH CHECK to add a constraint that conflicts with existing data. https://stackoverflow.com/questions/529941/whats-the-difference-between-with-check-add-constraint-followed-by-check-constr

Perhaps you are getting the error because you "2. Trucate a table." and not all of them? Maybe you are removing the Authors or the Books and are leaving the records in AuthorBooks that references these other tables.


Copy values to another column - ORA-01481: invalid number format model I want to copy values of col1 NUMBER(13,4) column to another newly added col1_temp VARCHAR2(50). To perform this action I build the following update query:

update table1 set col1_temp = to_char(col1, '99.99%');

Here is the current view of both columns:

 col1  |  col1_temp
-------------------
 13.9  |  null
 65.7  |  null
 21.63 |  null
 50.5  |  null
 10.8  |  null
 30.1  |  null

But running of the query gives me back always this error:

SQL Error: ORA-01481: invalid number format model

What I'm doing wrong?

% is not a valid format specifier for numbers.

See the documentation below:

Number Format Models


MSSQL over the internet but we connect over RDP We connect throguh RDP to our ERP solution which uses MSSQL 2008, right now I can see on windows logs and SQL logs that we have a HUGE amount of connection attempts to sa user and results in slow connections to us, users.

Since we connect through RDP and both ERP and DB are on the same server, I think I can close SQL port to the internet, and that would stop direct attacks to the DB, but our IT consultant it's not sure about it. I think it's safe to say that the only ports needed to be open through internet are RDP ports, besides that all happens in a LAN enviroment.

Am I right or am I missing something?

I wrote a blog post about the perils of having your SQL Server exposed to the internet.

You should almost never allow internet access directly to SQL Server.  From your description, it sounds like you have no requirement for that, since you RDP into the machine for access.  I would immediately ensure the port used by the SQL Server is blocked at the firewall.


Counting duplicate instances within column in SQL when grouping by another column My DB looks something like the following:

id |  Name  | JOBID
----+--------+------
  1 | Sam    | a
  2 | Sam    | b
  3 | Ali    | b
  4 | Tim    | c
  5 | Tim    | d
  6 | Fin    | a
  7 | Mona   | c
  8 | Sam    | e (edited) e is not a duplicate job
  9 | Sam    | f (edited) f is not a duplicate job
  10| Ali    | x (edited) x is not a duplicate job
  11| Fin    | z (edited) z is not a duplicate job

(assume other jobs are all unique and not double-vote (duplicate)

Basically each person will do a job (marked by JobID) once, but the same job can go to two people (max). I want to group by name, but count the instances of duplicate jobIDs that each person got (in column JOBID). How can I do this in SQL?

*Edits - Sorry for editing in the comment section - this is my first time here. You're right, I had made an error in giving the logic. Jobs are either single vote (goes to only one operator) or double vote (go to two operators - hence show as duplicates in JobID column). Any person may have done hundreds of jobs, but I just want to know how many duplicates (double vote jobs) they did.  TO clarify now:

Sam has done 4 (a,b,e,f) jobs but I want to display 2 since he did 2 double vote jobs (a,b).
Ali has done 2 (b,x) jobs but I want to display 1 since he did 2 double vote jobs (b).
Tim has done 2 jobs (c,d) but I want to display 1 since he did 1 double vote jobs (c).

Basically count of double vote is done within the JobID column, but I want it to reflect when I group by name - which person got how may of these jobs. Hope I made this clearer now :)

This should do what you require (fiddle):

Create table j (tid serial, nom text, jid text);

Insert into j (nom, mid) values
('Sam', 'a'),
('Sam', 'b'),
('Ali', 'b'),
('Tim', 'c'),
('Tim', 'd'),
('Finn', 'a'),
('Mona', 'c'),
('Bill', 'z'),
('Sam',  'x'),
('Sam',  'y'),
('Finn', 'w');

Then:

With dup_jobs AS
(
  Select jid from j
  Group by jid
  Having count(1) > 1
)
Select j.nom, count(1)
From j
Join dup_jobs dj
  On j.jid = dj.jid
Group by j.jid
Order by j.jid

Result:

Ali 1
Finn 1
Mona 1
Sam 2
Tim 1

Note that Bill has no jobs in common with anyone else and therefore doesn't appear. Sam's jobs 'x' and 'y' are not shared with anyone else and similarly aren't counted.

Interesting question (+1). p.s. welcome to the forum! :-) Oh, and BTW, in future could you please provide sample table structures (DDL) and data (DML) in the form of a fiddle - it helps us to help you and if there's only one definitive source for the tables and data, it helps prevent errors and duplication of effort.


pgAgent : how to configure pgpass.conf for a local connection on Windows Server 2008 I try to configure pgAgent on Windows server 2008 to make automatic backup on the database.
pgAgent is installed on the server and the service is running normally.
I use PgAdmin3 and PostgreSql 9.3

I have already planned query using the connection string with the password in it, and it works perfectly.

Now, I try to make it by specifying the password in pgpass.conf (as said in the doc) but I always got the "Couldn't get a connection to the database !" error.

My pgpass.conf is something like this :

175.35.105.15:5432:*:admin:***
175.35.105.15:5432:*:postgres:***
localhost:5432:*:postgres:***
localhost:5432:*:admin:***
127.0.0.1:5432:*:admin:***

I know that some line are rhetorical, it's just that I've tried a lot of things.

I usually try to connect on localhost, but even in remote connection mode it doesn't work.

Here some examples of what I've tried with remote connection in step configuration :

user=admin host=175.35.105.15 port=5432 dbname=db
user=postgres host=175.35.105.15 port=5432 dbname=db
user=admin host=127.0.0.1 port=5432 dbname=db
user=postgres host=127.0.0.1 port=5432 dbname=db

EDIT : in Postgres log, pgagent is connected as adminpostgres. This username isn't in the login role...

pgpass.conf is in c:/user/Application Data/postgresql

pgAgent was installed with StackBuilder as Admin

I've tried to fill the "host agent" field with the content of "pga_jobagent.jagstation" but it doesn't work either

I've found it !

In Windows Service, i found that it was "adminpostgres" who was running the pgAgent service.

I just had to edit the pgpass.conf in c:/user/adminpostgres/AppData/postgresql with my old config to make it works !


Query to list all the databases that have a specific user I need to form a query that lists all the databases that include the words 'PRD' that have a single specific user name 'user_1'
So far I have this query:
select name as username,
create_date,
modify_date,
type_desc as type,
authentication_type_desc as authentication_type
from sys.database_principals
where type not in ('A', 'G', 'R', 'X')
and sid is not null
and name != 'guest'
and name = 'user_1'
order by username;

The above query will require me to do "use database name" for each database. How can I include all the databases that contain the word 'PRD' in it?
Any help is appreciated.
EDIT:
How can I combine the above query with the query below?:
SELECT name FROM master.sys.databases
where name like '%PRD%'

By using this inner join and the link is database_id = principal_id
select
dp.[name] as username,
dp.create_date,
dp.modify_date,
dp.[type_desc] as [type],
dp.authentication_type_desc as authentication_type,
dp.principal_id,
m.[name]
from sys.database_principals dp
INNER JOIN master.sys.databases m ON m.database_id = dp.principal_id
    AND m.name like '%PRD%'
where dp.[type] not in ('A', 'G', 'R', 'X')
and dp.[sid] is not null
and dp.[name] <> 'guest'
and dp.[name] = 'user_1'
order by username;

Also try to avoid != and replace it by <>.
Hope it helps,


Lock Pages in Memory keep SQL database engine stuck at allocating 60MB ram? I'm using Window Server 2003 Enterprise, Microsoft SQL Server 2005 Standard Edition.

The problem is the SQL Server memory gets stuck at allocating 1.7GB RAM max even though I have 11GB of RAM left.

Following the advice from a post in this thread, in which the thread starter had the same issue, I tried adding /PAE to my boot.ini, adding the network service account which is running sqlserv.exe to Lock Pages in Memory user right, and reconfiguring SQL to use AWE to allocate memory.

But I had no luck. After restarting the server, the SQL database engine cannot allocate more than 60MB RAM, which is a terrible failure as I expected.

So after that, I must restore the Lock Pages in Memory setting: remove the network service account from Lock Pages in Memory option, restart my server, and I'm back to the first problem. The SQL Server database engine is stuck at allocating 1.7GB RAM.

So the Lock Pages in Memory keeps the SQL database engine stuck at allocating 60MB RAM? And how can I resolve the first problem now?

As you might know, the reason it allocates 1.7 GB of working set is 32-bit memory limitations. On 32-bit editions of Windows, by default each process gets 2 GB of usable virtual address space, and by default 32-bit editions of SQL Server leave a few hundred MB outside of its largest consumer of memory, the buffer pool.

It is most likely just a misunderstanding that SQL Server allocated only 60 MB of memory when you enabled locked pages. Memory allocated through AWE doesn't show up on the processes tab in Task Manager (or other tools that report a figure based on the processes' working set size) because it isn't part of the process' working set. If SQL Server used a smaller amount of memory when you enabled Lock Pages in Memory, this suggests that AWE was working and the SQL Server process had most likely allocated more than 60 MB of memory.

There are several ways to view memory allocated by AWE, including but not limited to the following.

Query sys.dm_os_memory_clerks, for example as follows:

SELECT SUM(awe_allocated_kb) AS [Total KB AWE Allocated]
FROM sys.dm_os_memory_clerks;

View the Performance Monitor counter SQL Server: Memory Manager\Total Server Memory (KB)

A few notes on your question:

As you probably know, basically anyone who does this for a living will tell you that it's long past time to migrate off the 32-bit operating system and 32-bit SQL Server. You'll be able to use far more RAM without any hassles on a 64-bit platform.
It's highly unusual for SQL Server to be running as the Network Service account.
Processes allocate memory, not RAM (which may not be the same thing)
You shouldn't need to add /PAE to boot.ini. Windows has booted a PAE-aware kernel by default since Windows 2003 SP1 unless you also disable Data Execution Prevention with the switch '/noexecute=alwaysoff'. (You don't want to do that, by the way.)
Using locked pages can cause out-of-memory errors if you don't set a reasonable memory limit in SQL Server or if something other than the SQL Server buffer pool -- either another program or another part of SQL Server -- is competing for memory in Windows.

References:

How It Works: SQL Server 32 bit PAE/AWE on (SQL 2005, 2008, and 2008 R2) – Not Using As Much RAM As Expected!

Fun with Locked Pages, AWE, Task Manager, and the Working Set…

Great SQL Server Debates: Lock Pages in Memory


Should binary files be stored in the database? What is the best place for storing binary files that are related to data in your database? Should you:

Store in the database with a blob
Store on the filesystem with a link in the database
Store in the filesystem but rename to a hash of the contents and store the hash on the database
Something I've not thought of

The advantages of (1) are (among others) that atomicity of transactions is preserved. The cost is that you might dramatically increase storage (and associated streaming/backup) requirements

The goal of (3) is to preserve atomicity to some degree - if you can enforce that the filesystem you are writing to does not allow files to be changed or deleted, and always has the correct hash as filename. The idea would be to write the file to the filesystem before permitting the insert/update referencing the hash - if this transaction fails after the filesystem write but before the database DML, that is fine because the filesystem is 'faking' being the repository of all possible files and hashes - it doesn't matter if there are some files in there that are not being pointed to (and you could clean them up periodically if you are careful)

EDIT:

It looks like some RDBMSs have this covered in their individual ways - I'd be interested to know how others do it - and particularly in a solution for postgres

Store in the database with a blob

A disadvantage is that it makes your database files quite large and possibly too large to back up with your existing set up. An advantage is integrity and atomicity.
Store on the filesystem with a link in the database

I've come across such horrible disasters doing this, and it scares me that people keep suggesting it. Some of the disasters included:

One privileged user who would rearrange the files and frequently break the links between the paths in the DB and where they now are (but somehow this became my fault).
When moving from one server to another, the ownership of some of the files was lost as the SID for the old machine's administator account (what the old website was running on) was not part of the domain and so the copied files had ACLs that could not be resolved thus presenting users with the username/password/domain login prompt.
Some of the paths ended up being longer than 256 characters from the C:\ all the way to the .doc and not all versions of NT were able to deal with long paths.

Store in the filesystem but rename to a hash of the contents and store the hash on the database

The last place I worked at did this based on my explanation of the above scenarios did this. They thought it was a compromise between the organization's inability to obtain experience with large databases (anything larger than about 40G was ordained to be "too big"), the corporate inability to purchase large hard drives, and the inability to purchase a more modern back up solution, and the need to get away from risks #1 & #3 that I identified above.

My opinion is that storing in the DB as a blob is a better solution and more scalable in a multi-server scenario, especially with failover and availability concerns.


Linked Server with Windows Authentication Permissions I’m trying to figure out how to setup a linked server object from SQLServerA to SQLServerB using Windows Authentication.

I’ve setup many linked servers but have always used a SQL Server login which works fine.

In this scenario, a logon has been created at SQLServerB using Windows Authentication granting access to a specific database.

From SQLServerA, I’m creating a linked server object to SQLServerB. On the Security Page of the linked server configuration, I’m using "Be made using this security context" and enter Remote login:domain\user, With Password: password respectively.

When SQL performs the Test Connection final step, I receive an error indicating “Login failed for user ‘domain\user’ (Microsoft SQL Server, Error: 18456)”.

Is there something special I need to do to create the linked remote server using Windows Authentication as the authorization type instead of a SQL Server login?

If that’s not possible, is there another solution that I can use to query a remote servers other than interactively through SSMS?

My goal is to setup SQL job(s) to import some data from the remote SQL Server.

Thanks

The "Be made using this security context" doesnt support Windows Authentication.

  Be made using this security context Specify that a connection will be
  made using the login and password specified in the Remote login and
  With password boxes for logins not defined in the list. The remote
  login must be a SQL Server Authentication login on the remote server.

You need to use this:

  When connecting to the local server using Windows Authentication
  (recommended), select Be made using the login's current security
  context to connect to the remote server using the same Windows
  Authentication credentials.

Or you could use "Not be made" and specify the login with impersonate. Requires that the user exists on both servers aswell.

You can read more here: https://technet.microsoft.com/en-us/library/ms188477(v=sql.105).aspx


Lookup table for a one-to-many relationship I have a tickets table with an id that I need to associate to a lookup table where the counterpart of that data is another id that is controlled from an outside source.
tickets
- id
- sutff

lookup
- outside_data_id
- ticket_id

I just need to know if the association exists (hence the lookup table) and has a one to many relationship. I know nothing else about the outside_data_id except the id itself and the related ticket_id.
Also I don't want to alter the tickets table as this will only affect a small amount of ticket_id's.
Example
outside_data_id = 1234

Can be related to:
ticket_id = 321
ticket_id = 322
ticket_id = 900

Would the below lookup table structure work or is there a better way?
CREATE TABLE lookup
(
   outside_data_id integer,
   ticket_id integer
)

Your table will work fine for this purpose, but you probably want to add an index. If the primary reason for using this table is to take an outside_ticket_id and get the corresponding ticket_id's I would add the following clustered index:

CREATE CLUSTERED INDEX [CL_Lookup_OD_ID] on [lookup](outside_data_id)
GO

If the primary lookup will be the other way around (trying to find the outside_data_id  from a ticket_id) place the clustered index on the other column.

----  Oh, sorry, just noticed this is Postgres. The above syntax is SQL Server.  For Postgres, create an index on the column, then issue the cluster command, like so:

CREATE INDEX IX_outside_data_id on lookup(outside_data_id);
CLUSTER lookup using IX_outside_data_id;

You may also want to look at the "fillfactor" on the index depending on how heavy the insert load is here.  But that is a large topic worth exploring on it's own...


Why does sql server need to convert count(*) result into int before comparing it with an int variable? I have many queries in my application where in the having clause, I have comparison of count aggregate function with int variable.
In the query plans, I can see an implicit_convert before the comparison.
I want to know why this happens because as per sql server documentation, the return type of count function is int. So why should there be an implicit conversion for comparison of two int values?

Following is a part of one such query plan where @IdCount is defined as an int variable.

|--Filter(WHERE:([Expr1022]=[@IdCount]))
 |--Compute Scalar(DEFINE:([Expr1022]=CONVERT_IMPLICIT(int,[Expr1028],0)))
  |--Stream Aggregate(GROUP BY:([MOCK_DB].[dbo].[Scope].[ScopeID]) DEFINE:([Expr1028]=Count(*)))

The fact that you are comparing it against an integer variable is irrelevant.

The plan for COUNT always has an CONVERT_IMPLICIT(int,[ExprNNNN],0)) where ExprNNNN is the label for the expression representing the result of the COUNT.

My assumption has always been that the code for COUNT just ends up calling the same code as COUNT_BIG and the cast is necessary to convert the bigint result of that back down to int.

In fact COUNT_BIG(*) isn't even distinguished in the query plan from COUNT(*). Both show up as Scalar Operator(Count(*)).

COUNT_BIG(nullable_column) does get distinguished in the execution plan from COUNT(nullable_column)  but the latter still gets an implicit cast back down to int.

Some evidence that this is the case is below.

WITH
E1(N) AS
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)                                       -- 1*10^1 or 10 rows
, E2(N) AS (SELECT 1 FROM E1 a, E1 b)   -- 1*10^2 or 100 rows
, E4(N) AS (SELECT 1 FROM E2 a, E2 b)   -- 1*10^4 or 10,000 rows
, E8(N) AS (SELECT 1 FROM E4 a, E4 b)   -- 1*10^8 or 100,000,000 rows
, E16(N) AS (SELECT 1 FROM E8 a, E8 b)  -- 1*10^16 or 10,000,000,000,000,000 rows
, T(N) AS (SELECT TOP (2150000000)
                  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM E16)
SELECT COUNT(CASE WHEN N < 2150000000 THEN 1 END)
FROM T
OPTION (MAXDOP 1)

This takes about 7 minutes to run on my desktop and returns the following

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
Warning: Null value is eliminated by an aggregate or other SET operation.

Which indicates that the COUNT must have continued on after an int would have overflowed (at 2147483647) and the last row (2150000000) was processed by the COUNT operator leading to the message about NULL being returned.

By way of comparison replacing the COUNT expression with SUM(CASE WHEN N < 2150000000 THEN 1 END) returns

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

with no ANSI warning about NULL. From which I conclude the overflow happened in this case during the aggregation itself before row 2,150,000,000 was reached.


Restored database size I have a large (90 GB) database. About half of the space is taken up by two tables that are no longer used. I intend to truncate these two tables and as I understand things this won't free up the space (on the disk) assigned to this database until I shrink it. I don't want to shrink it at the moment.

There is an overnight backup process. Will it be possible for me restore this database to a server that only has 50GB space free or will the restore create a 90GB database that needs a shrink?

Are you using the compression function in SQL 2k8? If so then it should work.

I would personally restore this database onto a Dev/Test server and just carry out the scenario you have stated and see what size a full backup is.


How can I prevent/detect that a end user restored a backup? We need to prevent the SQLServer 2008 .mdf file from being replaced by the end user of our application.

we collect data in the MDF that is important to us.  If the user saves an old copy of the MDF, and then restore it at a later date, we lose that data.  We want to prevent the user from doing this.

Is it possible, and if so, how could it be done?

If you want to prevent the table from being deleted, see here: http://pratchev.blogspot.com/2007/12/prevent-table-drop.html.  If you want to prevent the data from being altered by the end user, simply do not give your users modify permissions to that table.

If you set up your user accounts and permissions properly, changing out the MDF won't work, because the user accounts and credentials won't line up.


Can you enforce uniqueness in MySQL based on column in foreign table? I am refactoring one of my database tables and I've come up with an issue.

Specifically, I have a table with four columns. We'll call the first three my.a, my.b and my.c.  I have a uniqueness constraint on the combination of these three columns.  Column my.d is a foreign key to an auto-incremented ID column in another table foreign.

I realized that this other table also has a column foreign.a and both column as are complete duplicates, i.e., if you join the tables together on d, my.a will always equal foreign.a.  Apparently this is part of the requirements.

I would like to remove column my.a from my table, but that will remove the uniqueness constraint.  Is there a way to somehow set up a uniqueness constraint on the combination of foreign.a, my.b, my.c?  Or am I better off just leaving the duplicate column in place?

You cannot directly create a unique constraint across tables. I say directly because you could come up with some indirect scheme; what comes to my mind would be using triggers to insert into a third table that has the unique constraint. But that's a lot of extra work compared to the obvious solution, which is to keep the column "a" in the "my" table.


Preventing duplicate data when usual normalization only makes sense for some of the records Background

I have two tables, one called issues and the other called status_checks, which both contain (among others) the field comment (VARCHAR).

An issue is raised by a user when something needs to be looked at by the support staff. A status_check on the other hand is something that is done every so often by support staff and can (but doesn't have to) result in an issue being raised.

So, for instance, a status_check might look like this:

+----+---------+---------+--------------+---------+------------+
| id | user_id | date    | satisfactory | comment |  issue_id  |
+----+---------+---------+--------------+---------+------------+
| 1  |    23   | 2014... |      1       | blah... |    NULL    |
+----+---------+---------+--------------+---------+------------+

or like this:

+----+---------+---------+--------------+-------------------+------------+
| id | user_id | date    | satisfactory |      comment      |  issue_id  |
+----+---------+---------+--------------+-------------------+------------+
| 2  |    24   | 2525... |      0       | It all started... |     25     |
+----+---------+---------+--------------+-------------------+------------+

(Typically, an issue will only be raised if it's unsatisfactory (0), but that's not required.)

Then, the issue for that second one would look something like this:

+----+---------+---------+-------------------+---------+--------------+
| id | user_id | date    |      comment      |  status | close_reason |
+----+---------+---------+-------------------+---------+--------------+
| 2  |    24   | 2525... | It all started... |  open   |     NULL     |
+----+---------+---------+-------------------+---------+--------------+

The Problem

So the question is... how can I avoid duplicating the comment field when an issue is raised by a status_check? I could leave that field NULL when an issue is raised, and simply put the comment contents in the issue table (or vice versa)... but then that makes the SQL retrieval logic more complex. Having to optionally join to another table based on a field being possibly null seems like overkill, but I can't think of a better solution.

Normally, one would normalize by putting the comment in an issue and always having an issue_id in the status_checks table, but obviously that doesn't make sense here, since most status_checks are not issues.

Possible solution?

The other way I thought of doing things seems like normalization gone wrong to me, but maybe it's actually better. That solution is to have a comments table:

+----+---------+
| id | comment |
+----+---------+
| .. | ....... |
+----+---------+

Then I would just include a comment_id in both tables, rather than the comment itself. It's still duplicating the data, but it's only duplicating an integer.

Can some other way of normalizing help me here? Or is one of the above the best I'm going to get?

EDIT: Looking at it now, I see that the user_id and date are also both duplicated, so maybe it's natural that the comment be duplicated too? I guess it's only the size of the comment that makes me queasy about duplicating it. The comments-only table looks ugly, since a comment is such a general concept and the comments by themselves would just be floating in there with no context.

You may want to normalize out the comments field to allow for a comments chain.  This may have additional fields such as userid and date.  This might result in a table tracking actions on issues.  If this is not part of your design, I wouldn't normalize the comments field out.

It appears you have an optional 1-1 relationship between the status checks and issues.  Either table can carry this relationship as a nullable foreign key.  If the relationship might not be 1-1 that may drive the table carrying the foreign key, or require a relationship table.


"Could not find server error" when using INSERT SELECT statement in SQL Server I'm trying to copy a record from a production database table into a staging database table.  They are both SQL Server databases on Amazon RDS.  My query looks something like this:

INSERT INTO [neonpig.staging1.us-east-1.rds.amazonaws.com].[neonpig_prod_db].[dbo].[AspNetUsers] SELECT * FROM [neonpig.prod1.us-east-1.rds.amazonaws.com].[neonpig_prod_db].[dbo].[AspNetUsers]

But this seems to produce the error below:

  Could not find server 'neonpig.prod1.us-east-1.rds.amazonaws.com' in
  sys.servers. Verify that the correct server name was specified. If
  necessary, execute the stored procedure sp_addlinkedserver to add the
  server to sys.servers.

I've ensured that the server address are correct.   Anyone know what I'm doing wrong?

Note that the database name is the same on both the prod server and the staging server.

To execute such statement you need to create LinkedServer first. In this case, you need to have LinkedServer from one RDS instance to another RDS instance.

https://aws.amazon.com/blogs/database/implement-linked-servers-with-amazon-rds-for-microsoft-sql-server/

  RDS SQL Server to RDS SQL Server

  For this scenario, both instances of RDS SQL Server must be private
  instances (not publicly accessible) within the same VPC. If the RDS
  SQL Server instances are publicly accessible, then you need to refer
  to the private IP of the RDS instances when creating the linked
  servers. Because you don’t have access to the private IP addresses of
  the servers, you need to use an EC2 instance in the same VPC to run
  NSLookup on the private instances of RDS. Do this for each RDS
  instance name.

  NSlookup privatesql.cb2xkfsffmy7.us-west-2.rds.amazonaws.com

  Address: 10.0.4.236

  From there, the code is very much like the RDS to EC2 code, except now
  you use the private IP address of the remote RDS instance.

  EXEC master.dbo.sp_addlinkedserver @server = N’RDSPrivate‘, @srvproduct=N”, @provider=N’SQLNCLI’, @datasrc=N’10.0.4.236′;

  EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’RDSPrivate’,@useself=N’False’,@locallogin=NULL,@rmtuser=N'<username>’,@rmtpassword=‘<password>’;

  GO

  Just as we did preceding, use a four-part name to reference the remote
  RDS instance:

  SELECT * from RDSPrivate.TestDB.dbo.t1;

  Note one limitation for all RDS instances: If the physical server
  supporting your RDS SQL Server instance changes (such as when
  upgrading to a new version of SQL Server or changing the instance
  type), the private IP address of the RDS instance of SQL Server might
  change. This change can happen with no alerts or notifications to your
  administrative team. Thus, if the linked server connection fails, your
  first troubleshooting steps should include verifying that the private
  IP address of the RDS instance has not changed. If this risk is
  unacceptable in your environment, then AWS doesn’t recommend using the
  linked server feature with RDS SQL Server.


Should we really turn on trace flag 1118 for every SQL instance? It has been recommended that every SQL instance have TF 1118 turned on

  What is Paul’s recommendation for using trace flag 1118? Everyone should turn it on, on all instances of SQL Server, from SQL Server 2000 onwards. There’s no down-side to having it turned on.
  Paul S. Randal

It is documented in KB 2154845  while it does impact all databases, it's biggest impact is on tempdb

  This means that each newly allocated object in every database on the instance gets its own private 64KB of data. Tempdb is usually the place where most objects are created, so it makes the most difference there.
  Kendra Little

I have looked around and have not found anyone recommending against its use.  So should we make it part of our process to turn it on when we create new instances, as well as adding it retroactively to existing SQL instances as opportunities present?

I am using SQL 2000 through 2014, have not gone live with any 2016 yet.  We follow the directions to use 1 tempdb per processor up to 8.

For SQL Server 2000 → 2014

I generally agree with Paul Randal, that this is something you could always turn on, but I do have a little bit of hesitation to say it should always be on and you should leave it on regardless of what you observe (with or without adequate testing).

People can have poorly set up tempdb configurations, for example not enough files, files too small, files not equally sized, improper growth settings, instant file initialization not enabled, and bad coding symptoms where temp tables are not cached and reused (see here and here). In this scenario, with the perfect storm workload, it's conceivable that the trace flag could actually lead to worse overall performance. Think about a case where those settings are not optimal and you have tons of users all trying to create their own, not-from-cache copies of temp tables, and they're all using their own dedicated extents instead of sharing them. There may be some level, even if I can't produce it at will, where the overhead of dedicated extents outweighs the allocation contention savings.

Just because Paul hasn't seen a scenario where the scales tip at some threshold doesn't mean it's impossible, just that it's unlikely / uncommon.

You can likely avoid that by following all of the other best practices, because I suspect it really would require a perfect storm, I just do not feel strong enough to say always and without exception.

For SQL Server 2016

In SQL Server 2016, uniform extent allocation is the default behavior - see Bob Dorr's "it just runs faster" post about it. Other than that, many of the same things above hold true.

You can't turn this off for tempdb; so, effectively, trace flag 1118 is always on in 2016, and is permanent. So my suggestion would be to make sure tempdb works effectively under TF 1118 on your current instances and under your current workload, to avoid any surprises when you upgrade (at which point you will no longer have the option to turn it off).

If you want to turn it off for user databases in 2016, you use ALTER DATABASE:

ALTER DATABASE MyDatabase SET MIXED_PAGE_ALLOCATION ON;


SQL Server Maximum and Minimum memory configuration What are the correct settings for min-memory and max-memory in this use case?

The server has 8GB ram, dual Intel Xeon processors, running Windows Server 2008 R2 / Sql Server 2008 Standard Edition. It's running several databases ranging from 30GB - 5GB in size.

Originally the memory usage was set to the default settings (min=0 max=2,147,483,647). On these settings most of the memory usage was taken up by sqlservr.exe and the server would eventually need to be restarted every day or two. It would run normally at first but within a day start to timeout on simple operations like looking up a record using the primary key.

I have changed min=4,096 and max=6,144. This results in only 1.4GB memory usage. However now all four cpus are running at 50-60% cpu usage constantly. Tasks are taking roughly 1/3rd longer to execute, although the server is much more stable.

Looking at your RAM availablity for this particular server and you are running several databases ranging from 30GB to 5 GB, you definitely need more RAM on this server.

You have not mentioned that this is a stand alone instance or this server is having more than one instance of sql server running.

Your MAX Memory settings seems OK for a server having 8GB RAM. See these suggested best practice settings from Glenn Berry.

I would highly recommend you to do a baseline of your environment using below PERFMON counters to get a good value of your memory configuration :

SQL Server:Buffer Manager\Page Life Expectancy
SQL Server:Buffer Manager\Page reads/sec
Physical Disk\Disk Reads/sec
Memory\Available Mbytes
SQL Server: Memory Manager - Total Server Memory
SQL Server: Memory Manager - Target Server Memory

Total Server Memory: Amount of memory currently allocated to Buffer Pool and not the total amount of memory to SQL Server

Target Server memory: Ideal Size of the buffer pool corresponding to max memory for the instance.

Note: If Total Server Memory > Target Server memory, then it suggests memory pressure.

Below script will help you find LOW or HIGH Memory notifications  from sys.dm_os_ring_buffers - system health session :

SELECT CONVERT (varchar(30), GETDATE(), 121) as [RunTime],
dateadd (ms, (rbf.[timestamp] - tme.ms_ticks), GETDATE()) as [Notification_Time],
cast(record as xml).value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') AS [Notification_type],
cast(record as xml).value('(//Record/MemoryRecord/MemoryUtilization)[1]', 'bigint') AS [MemoryUtilization %],
cast(record as xml).value('(//Record/MemoryNode/@id)[1]', 'bigint') AS [Node Id],
cast(record as xml).value('(//Record/ResourceMonitor/IndicatorsProcess)[1]', 'int') AS [Process_Indicator],
cast(record as xml).value('(//Record/ResourceMonitor/IndicatorsSystem)[1]', 'int') AS [System_Indicator],
cast(record as xml).value('(//Record/MemoryNode/ReservedMemory)[1]', 'bigint') AS [SQL_ReservedMemory_KB],
cast(record as xml).value('(//Record/MemoryNode/CommittedMemory)[1]', 'bigint') AS [SQL_CommittedMemory_KB],
cast(record as xml).value('(//Record/MemoryNode/AWEMemory)[1]', 'bigint') AS [SQL_AWEMemory],
cast(record as xml).value('(//Record/MemoryNode/SinglePagesMemory)[1]', 'bigint') AS [SinglePagesMemory],
cast(record as xml).value('(//Record/MemoryNode/MultiplePagesMemory)[1]', 'bigint') AS [MultiplePagesMemory],
cast(record as xml).value('(//Record/MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') AS [TotalPhysicalMemory_KB],
cast(record as xml).value('(//Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS [AvailablePhysicalMemory_KB],
cast(record as xml).value('(//Record/MemoryRecord/TotalPageFile)[1]', 'bigint') AS [TotalPageFile_KB],
cast(record as xml).value('(//Record/MemoryRecord/AvailablePageFile)[1]', 'bigint') AS [AvailablePageFile_KB],
cast(record as xml).value('(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS [TotalVirtualAddressSpace_KB],
cast(record as xml).value('(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS [AvailableVirtualAddressSpace_KB],
cast(record as xml).value('(//Record/@id)[1]', 'bigint') AS [Record Id],
cast(record as xml).value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
cast(record as xml).value('(//Record/@time)[1]', 'bigint') AS [Record Time],
tme.ms_ticks as [Current Time]
FROM sys.dm_os_ring_buffers rbf
cross join sys.dm_os_sys_info tme
where rbf.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
--and cast(record as xml).value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') = 'RESOURCE_MEMPHYSICAL_LOW'
ORDER BY rbf.timestamp ASC

Some good references :

How much memory does my SQL Server actually need? - by Jonathan Kehayias
Performance Tuning using the Waits and Queue
Great SQL Server Debates: Lock Pages in Memory


Restrict access to rows by usename In my database i store data of different users (e.g. addresses or invoices) in the corresponding tables. I want to make sure that a logged in user only has access to it's own data in the tables (so that the user cannot read e.g. an address of an other user).

Currently this is done in the application accessing the mysql server.

Because the application will be split into multiple independent parts, written in different languages, I'm looking for a solution that is closer to the database, otherwise i need to make sure that the access rules are equal in every application.

While I also have the alternative of a middleware on my list, I'm looking for a in database solution in the first place.

Currently I already  have a structure running in a test environment (It is a shorted version for illustrating). But I would like to know if there is a better solution to achieve this.

I already know that that a VIEW with the algorithm MERGE is limited in what can be in the query and which JOINs can be done in the VIEW to keep the VIEW in a state that INSERT and UPDATE queries are still available.

Tables

CREATE TABLE IF NOT EXISTS `User` (
  `_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `mysqluser` varchar(120) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `_Address` (
  `_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `owner` int(10) unsigned NOT NULL,
  PRIMARY KEY (`_id`),
  KEY `owner` (`owner`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

CREATE TABLE IF NOT EXISTS `_Invoice` (
  `_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `owner` int(10) unsigned NOT NULL,
  `address` int(10) unsigned NOT NULL,
  PRIMARY KEY (`_id`),
  KEY `owner` (`owner`),
  KEY `address` (`address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Views

CREATE ALGORITHM=MERGE DEFINER=`cm_root`@`localhost` SQL SECURITY DEFINER VIEW `Address` AS
SELECT `a`.`_id` AS `_id`
     FROM `_Address` `a` JOIN `User` `u` ON (`a`.`owner` = `u`.`_id`)
     WHERE (`u`.`mysqluser` = substring_index(user(),'@',1));

CREATE ALGORITHM=MERGE DEFINER=`cm_root`@`localhost` SQL SECURITY DEFINER VIEW `Invoice` AS
SELECT `a`.`_id` AS `_id`,`a`.`address` AS `address`
    FROM `_Invoice` `a` JOIN `User` `u` ON (`a`.`owner` = `u`.`_id`)
    WHERE (`u`.`mysqluser` = substring_index(user(),'@',1));

Constraints

ALTER TABLE `_Address`
  ADD CONSTRAINT `_owner_address_fk` FOREIGN KEY (`owner`) REFERENCES `User` (`_id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `_Invoice`
  ADD CONSTRAINT `_owner_invoice_fk` FOREIGN KEY (`owner`) REFERENCES `User` (`_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `_address_fk` FOREIGN KEY (`address`) REFERENCES `_Address` (`_id`) ON DELETE CASCADE ON UPDATE CASCADE;

MySQL views are a terrible solution to anything because they are VEERRYYY slow and also cause lots of issues during database backup and restores (due to @DEFINER keyword).

You store the id of the current user in the application session session and always use it as a filter for any queries that are executed since its a foreign key in each table.


Postgres function index doesn't work correctly with regular expressions I have this Postgres function. It forces text to be lowercase and all nulls to become empty strings, so that I more cleanly perform searches for things that don't match something, etc:

CREATE OR REPLACE FUNCTION magic_text(txt text) RETURNS text
IMMUTABLE PARALLEL SAFE
LANGUAGE SQL AS $$
  SELECT lower(coalesce(txt,''))
$$;

This function is heavily used in many different query types, so I created all of the varieties of text index for it:

CREATE INDEX index_magic_cards_on_oracle_text_magic
ON magic_cards
USING BTREE (magic_text(oracle_text)) WITH (fillfactor = 100);

CREATE INDEX index_magic_cards_on_oracle_text_magic_text_pattern
ON magic_cards
USING BTREE (magic_text(oracle_text) text_pattern_ops) WITH (fillfactor = 100);

CREATE INDEX index_magic_cards_on_oracle_text_magic_gist_trgm
ON magic_cards
USING GIST (magic_text(oracle_text) gist_trgm_ops);

CREATE INDEX index_magic_cards_on_oracle_text_magic_gin_trgm
ON magic_cards
USING GIN (magic_text(oracle_text) gin_trgm_ops)

These indexes are interfering somehow with some (but not all) kinds of complex regular expression searches. I haven't been able to determine if specific regex symbols or features cause the problem.

Here's an example (explain.depesz):

SELECT card_name
FROM magic_cards
WHERE magic_text(oracle_text) ~ '***:(?n)eldrazi\ (?!scion)';

This returns nothing, and according to the query planner, it performs a  bitmap index scan on index_magic_cards_on_oracle_text_magic_gin_trgm.

This expanded query also scans the same index, and fails to find anything (explain.depesz):

SELECT card_name
FROM magic_cards
WHERE lower(coalesce(oracle_text, '')) ~ '***:(?n)eldrazi\ (?!scion)';

However, if I force Postgres to not use my index, this query has results! (explain.depesz) The following has multiple rows of results and performs a sequential scan on the table. All I did below was change the coalesce fallback to ⌘, which shouldn't affect the results:

SELECT card_name
FROM magic_cards
WHERE lower(coalesce(oracle_text, '⌘')) ~ '***:(?n)eldrazi\ (?!scion)';

Why does using the index change the results for regular expression searches?

(Postgres 9.6.1, pg_trgm 1.3)

This looks like a live bug in pg_trgm to me.  I can strip away much of the stuff and still reproduce it with this simple test case:

create table foobar (x text);
insert into foobar values ('eldrazi scion'),('eldrazi scio');
create extension pg_trgm ;
create index  on foobar using gin (x gin_trgm_ops);
select * from foobar where x  ~ 'eldrazi (?!scion)';  -- returns 1 row
set enable_seqscan TO off;
select * from foobar where x  ~ 'eldrazi (?!scion)'; -- returns 0 rows

Note that a similar bug was fixed recently (in yet-to-be-released code, commit 16500d2278ab3dd), but that fix does not fix this bug.

I've reported this bug myself, as bug #14623.

And it has now been fixed.  When 9.6.3 is released, it should contain the bug-fix.