Refactor two one-to-one and one-to-many relationships in a single table

The option I considered initially was a good start, just needed further improving. The schema of the solution is below.

The solution consists of two parts actually:

Add unique constraint on SubAccountId
Introduce table for SubAccount children hierarchy mapping, both columns have foreign key to Id of the SubAccount table.

Children nested hierarchy is restored at software layer, the adjacency list hierarchy was not really needed.


Performance issue with sp_executesql and VARCHAR parameter

What you're running into is the curse of local variables.

The short of it is that when you declare a variable and then use it in a query, SQL can't sniff the value.

It sometimes uses magic numbers depending on how the variable is used (there are different guesses for BETWEEN, >, >= , <, <=, <>.

For equality searches, the density vector is used, (though the column being defined as unique matters here) but the bottom line is that the cardinality estimate is usually way off. Lots more information here.

This is your plan for the fast query:

For parameterized dynamic SQL, the value can be sniffed, and you end up with a totally different plan with totally different estimates.

If you want a quick and dirty option, you can use OPTIMIZE FOR UNKNOWN

EXECUTE sp_executesql
    @stmt =  N'SELECT TOP 10 c.BDPID, seg.FINAL_SEGMENT
     FROM Customers c
     LEFT JOIN Segments seg
          ON   seg.DEPARTMENT  = @dd
          AND  seg.BDPID     = c.BDPID
          OPTION (OPTIMIZE FOR (@dd UNKNOWN));',
          @params N'@dd VARCHAR(10)', @dd = 'DEP345'

Which will get you the density vector estimate, but that's a hacky solution and I sort of hate it. This of course goes out the window when you remove the TOP 10 from your query, which you mentioned in a comment is just for this post.

What I'd much rather see you do is some index tuning. I'm willing to bet the Segment table is crying for a clustered index -- that many rows in a HEAP is usually a sign of trouble (forwarded fetches can ruin your day).

The nonclustered index that gets used here isn't covering, which is also dragging this query down badly.

You could get rid of the RID Lookup by adding FINAL_SEGMENT as an included column.


The link below is from oracledistilled.com and goes through Enterprise Manager >> Data Movement.

Clone a Database to Another Host using Enterprise Manager 11gR1 Grid Control

It is very well explained with good screen shots and walks through Cloning i.e. another function under 'Data Movement' >> 'Export Export to File'.

Under host credentials the normal user is the O/S Oracle user, Alex Poole mentioned in a comment:

  Host credentials are for an operating system account (e.g. Oracle). SYS is a database account and has to be connected AS SYSDBA (not as 'normal' in the drop-down role when you log in to EM).


Pgpool-II: sequences advanced by 1 after PITR recovery

Answer per the question:

Script pgpool_recvery_pitr:

psql -t -c 'SELECT datname FROM pg_database WHERE NOT datistemplate AND
datallowconn' template1|
while read i
do
if [ "$i" != "" ];then
psql -c "SELECT setval(oid, nextval(oid)) FROM pg_class WHERE relkind =
'S'" $i
fi
done

After recovery all sequences in
database advance 1.

There is no information in official docs about it: http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html#online-recovery.

They forgot about other enabled nodes in pgpool. This statement MUST BE RUN for every enabled node in pgpool to keep all sequence points on all nodes equal.


How to get data from multiple tables in one row

Build the SELECT statements
Add UNION ALL for all except the first row at the start

Easier than adding to the endexcept for the last row...

Use the XML PATH technique to concat rows into one string

Tested code:

DECLARE @sql nvarchar(max), @base nvarchar(max) = '';

SET @SQL =
    (
    SELECT
        CASE WHEN X.rn >= 2 THEN ' UNION ALL ' ELSE '' END + X.stmt
    FROM
        (
        SELECT
            @base + 'SELECT ''' + t.name + ''' AS table_name, ' + c.name + ' FROM ' + t.name AS stmt,
            ROW_NUMBER() OVER (ORDER BY t.object_id) AS rn
        FROM
            sys.tables t
            INNER JOIN
            sys.columns c ON t.object_id = c.object_id
        WHERE
            t.name LIKE 'T%' AND c.name = 'DESCRIPTION'
        ) X
    ORDER BY X.rn
    FOR XML PATH ('')
    );
PRINT @sql;
EXEC (@sql);

SQL Server 2012+ version:

DECLARE @sql nvarchar(max), @base nvarchar(max) = '';

SET @SQL =
    (
    SELECT
        CONCAT(CASE WHEN X.rn >= 2 THEN ' UNION ALL ' ELSE '' END, X.stmt)
    FROM
        (
        SELECT
            CONCAT(@base, 'SELECT ''', t.name , ''' AS table_name, ', c.name, ' FROM ', t.name) AS stmt,
            ROW_NUMBER() OVER (ORDER BY t.object_id) AS rn
        FROM
            sys.tables t
            INNER JOIN
            sys.columns c ON t.object_id = c.object_id
        WHERE
            t.name LIKE 'T%' AND c.name = 'DESCRIPTION'
        ) X
    ORDER BY X.rn
    FOR XML PATH ('')
    );
PRINT @sql;
EXEC (@sql);


Is clustering a table beneficial, given this update pattern?

CLUSTER helps read performance where actual table rows have to be read (from the main relation). Index-only scans don't care about the physical order of rows in the underlying table. If your table layout is that simple, you might cover almost all SELECT queries with index-only scans (Might require aggressive autovacuum settings for the table to support that.)
UPDATE performance may even suffer from CLUSTER. First of all, CLUSTER rewrites the table without dead tuples, thereby removing "wiggle-room" for H.O.T. updates. A lower FILLFACTOR might help, but not in your case. While updating indexed columns, H.O.T. updates are not possible anyway.

PostgreSQL Initial Database Size
PostgreSQL unexplained table bloat

While you update millions of rows in one command (or transaction), clustered together, new row versions will hardly ever find room on the same data page, which makes it a bit more expensive to begin with.
And since you have multiple indexes including the updated columns, your updates are particularly expensive, since all of these indexes need to be updated as well.
Dead rows have to be reclaimed eventually. So new rows are not always written "en bloc", which inevitably leads to (slow in your case) fragmentation over time.
More efficient approach?

for each value of i, there will be from 5 to 10 million rows.
...
All updates or deletes are "bulk", i.e. they involve all rows for a given value of i.

Seems like you should not be updating millions of rows to begin with. Instead, create a second table (1:n) with a single row for every distinct i in my_table:
CREATE TABLE current_i (
   org_i     integer PRIMARY KEY
 , current_i integer NOT NULL  -- UNIQUE?
);

Should be a very small table, given your specifications. Now you only update a single row in current_i instead of millions in my_table, saving a lot of bloat and vacuuming in my_table and its indexes - in addition to the much faster updates - which should make everything else faster, too.
To enforce referential integrity, you may want to add a FOREIGN KEY constraint to my_table.i:
REFERENCES current_i(i)

In queries, just join to current_i. You might provide a VIEW as drop-in replacement for the current table in SELECT queries:
CREATE VIEW my_view AS
SELECT i.current_i AS i, m.x, m.y, m.z
FROM   my_table  m
JOIN   current_i i ON i.org_i = m.i;

Should be much faster overall. All of this may come down to a simple case of normalization.


You can use current_date (without a time) and then add the 7 hours:

WHERE created_at BETWEEN (current_date - 1) + time '07:00:00'
                     AND current_date + time '07:00:00'

(current_date - 1) is "yesterday"

Alternatively you could use an interval:

WHERE created_at BETWEEN (current_date - 1) + interval '7' hour
                     AND current_date + interval '7' hour

I prefer adding a "time" as that is easier to write if you also want to include minutes, e.g. current_date + time '07:30:00'


PostgreSQL How to optimize a query with ORDER BY and LIMIT 1?

A btree index on (sender,arrival) could help.  That would allow it to jump directly to the first-arrived message for a given sender.

One on (arrival,sender) is less likely to help.  That allows you to jump to the first-sent message globally, but then you still have to walk along those messages until you hit one from the specified sender.  If that particular sender is new, or only sends messages to people who keep up on their inbox, then you might have to walk through most of the index  before you find a qualifying message.  It might help in that you only have to walk the index, not the index and table combined, but that would still be a much smaller win than the proper index of (sender,arrival).

Similarly, you would want another index on (box_id, arrival), not on (arrival, box_id).

Also, performance testing on a table with 800 rows is useless if the real table will have 100,000 rows.


column must appear in the GROUP BY clause or be used in an aggregate function

You just need to remove the parentheses around the columns in group by (col1, col2). This works in version 9.4 and previous as well:

delete from mytable
where (col1, col2) in  (
    select col1, col2 from mytable
    group by col1, col2                   -- <-- changed
    having  count(distinct col3) >1) ;

The reason that it fails (I think) is that while (col1, col2) is equivalent to row(col1, col2), there was some inconsistency in how it was handled in the various clauses which was fixed in 9.5. In previous versions, you could use a more complex construction in WHERE: WHERE (SELECT (col1, col2)) IN .... So this should work in 9.3 as well:

delete from mytable
where (select (col1, col2)) in  (
    select (col1, col2) from mytable
    group by (col1, col2) having  count(distinct col3) >1) ;


SELECT all the children of a row and concat

For those who encountered the same problem. I did not answer this but someone provided the answer, I'll leave the link here: https://stackoverflow.com/questions/52773824/recursive-query-in-oracle-to-find-children-and-sibling/52776124#52776124


Connection's MAC address randomly changes on Windows 10, then stops

One of the things you have if you use IPv6 is something called Privacy Extensions. This changes the IP address every hour (or whatever implementation specific time). This guarantees anonymity of the MAC.


Delete from table rows where any of the column field is null

I see two ways of doing that:

With plain standard SQL, simply list all columns and combine that with an OR:

delete from the_table
where date is null
   or persons is null
   or two_wheelers is null
   or cars is null
   or vans is null
   or buses is null
   or autos is null;

Another (Postgres specific) solution is the comparison of the whole row with NOT NULL

select *
from the_table
where the_table is not null;

will return only rows where all columns are not null. You want the opposite, so you need to negate that where not (the_table is not null) The condition where the_table is null is something different - that only matches rows where all columns are null.

delete from the_table
where not (the_table is not null);


Recreating Database from IBD files (no FRM)

As suggested by @AMtwo, .frm files contain the schema definition. Since these are WordPress tables, getting the schema was very easy as I had lots of installs to get this info from.
I then followed the guide at https://www.chriscalender.com/recovering-an-innodb-table-from-only-an-ibd-file/ and it worked just fine:

Create table based on the schema of original table.
Running ALTER TABLE product DISCARD TABLESPACE;
Copying the original IBD file
Running ALTER TABLE product IMPORT TABLESPACE;

The tutorial mentions further instructions but at this stage it simply worked for me. I did the same for all other tables, exported as SQL dump and imported successfully.
I would recommend using Docker + mySQL if you are going to be trying this out.
Thanks again @AMtwo, you pointed me in the right direction!


What are the performance considerations between using a broad PK vs a separate synthetic key and UQ?

There is no significant disadvantage using the natural key as the clustered index

there are no non-clustered indexes
no foreign keys referencing this table (it is a parent row)

The downside would be increased page splits as data inserts would be distributed throughout the data, instead of at the end.

Where you do have FKs or NC indexes, the using a narrow, numeric, increasing clustered index has advantages. You only repeat a few bytes of data per NC or FK entry, not the while business/natural key.

As to why, read the too 5 articles from Google

Note I avoided the use of "primary key".

You can have the clustered index on the surrogate key but keep the PK on the business rules but as non-clustered. Just make sure the clustered is unique becauuse SQL will add a "uniquifier" to make it so.

Finally, it may make sense to have a surrogate key but not blindly on every table: many-many tables do not need one, or where a compound key from the parent tables will suffice


Need a parallel database for reporting purpose

Data Guard Configurations

  Physical standby database

  Provides a physically identical copy of the primary database, with on
  disk database structures that are identical to the primary database on
  a block-for-block basis. The database schema, including indexes, are
  the same. A physical standby database is kept synchronized with the
  primary database, through Redo Apply, which recovers the redo data
  received from the primary database and applies the redo to the
  physical standby database.

  As of Oracle Database 11g release 1 (11.1), a physical standby
  database can receive and apply redo while it is open for read-only
  access. A physical standby database can therefore be used concurrently
  for data protection and reporting.

Having a physical standby in read-only open mode while applying redo requires the Active Data Guard option.

  Snapshot Standby Database

  A snapshot standby database is a fully updatable standby database.

  Like a physical or logical standby database, a snapshot standby
  database receives and archives redo data from a primary database.
  Unlike a physical or logical standby database, a snapshot standby
  database does not apply the redo data that it receives. The redo data
  received by a snapshot standby database is not applied until the
  snapshot standby is converted back into a physical standby database,
  after first discarding any local updates made to the snapshot standby
  database.

  A snapshot standby database is best used in scenarios that require a
  temporary, updatable snapshot of a physical standby database. Note
  that because redo data received by a snapshot standby database is not
  applied until it is converted back into a physical standby, the time
  needed to recover from a primary database failure is directly
  proportional to the amount of redo data that needs to be applied.

You can convert a physical standby to a snapshot standby during the period required for running reports. Then after you can convert it to physical standby to catch up to the primary database. This does not require additional licensed options on top of EE.


How can I determine the last synchronization time for a SQL Server replication subscription?

From the subscriber side, you should be able to run EXEC dbo.sp_helppullsubscription to get some level of detail.

As an example:

use [SomeDB]
EXEC dbo.sp_helppullsubscription
    @publisher = N'MYSERVER'
    , @publisher_db = N'SomeDB'
    , @publication = N'SomeDBPublication';

One of the many columns returned will be last updating time, which is in this format:  yyyymmdd hh:mm:ss.mmm.  That column indicates the time the subscription information was updated.

If you look at this query from the subscriber database:

SELECT *
FROM dbo.MSreplication_subscriptions s
WHERE s.publisher = 'MYSERVER'
    AND s.publisher_db = 'SomeDb'
    AND s.publication = 'SomePublication';

The time column contains the time of the last update by the Distribution Agent.

You can setup threshold warnings in Replication Monitor; Microsoft Docs shows how to do that.  Also, this Microsoft Docs page shows how to programmatically monitor various aspects of Replication.

@SqlWorldWide has a good article on monitoring replication on their site.


Really slow join, limit and order_by query

I see some issues:

category_pointers is missing a unique key on its natural primary key.  Try using (category, isbn) as the primary key. It may be helpful to have the reverse index ('isbn', 'category'). This query can run strictly on the index.
category_pointers has a surrogate key for its primary index.  I would drop it and use the natural key (category, isbn).   I see this pattern far too frequently.  A surrogate key on a join table is a code smell to me.
The books_popularity_index may work better as a compound index defined as (popularity, isbn).  In index on (isbn, popularity) may help some search strategies.
The books table has a valid natural key isbn and a surrogate key id.  I would drop the id column as the primary index and use isbn as the primary key.
Use of RIGHT JOIN rather than INNER JOIN overrides the choice of driving table.  However, you are missing indexes which may help the query run faster.

Items 1 and 3 may improve your query performance.  Items 2 and 4 may be related to possible fragmentation of index ordered database tables.

I see two likely search paths:

small category: category - isbn - popularity.  This requires a sort on populary after all books in the category are found.  A (isbn, popularity) index might help.
large category: popularity - isbn - category.  Finds first thirty matching records without requiring a sort.  A large unpopular category may perform slowly.

Running the query prefixed by EXPLAIN should tell you how the data is being searched for.  This may help understand why the query is slow.  In most cases, each of the tables should be accessed by an index.  Only the index on category_pointers should be required.  (This may require both indexes specified above.)


Are heaps considered an index structure or are they strictly a table structure without index?

An index implies there is data order to the rows. Given that a heap isn't created with any such order and doesn't maintain an order either, it's just a method of storing table data.

There are a few examples (say, dm_db_index_physical_stats) where to specify a heap, you have to enter an index id of 0. While this seems to contradict what I just said, I think this is just a magic value to simplify the API and its usage; nothing more.


Displaying query plans in Server Management Studio

A1: The plan_handle is a hash for a group of statements, or batch.

A2: No, the dm_exec_query_plan returns the query plan in XML format, so you need to click on that in order to see the graph.

A3. Try this:

SELECT query_plan
FROM sys.dm_exec_text_query_plan (0x06000100A27E7C1FA821B10600,0,-1);


Based from experience, is dreamfactory better paired with a RDBMS like MySQL or NoSQL like MongoDB

DreamFactory works just fine with either SQL or NoSQL, and the API will actually be the same to GET or PUT data. The fields you have above make sense for either SQL or NoSQL. The only difference is that you need to create schema for the SQL table you want to use. Here is a tutorial that is very similar to your use case:

http://thenewstack.io/tutorial-prototyping-a-sensor-node-and-iot-gateway-with-arduino-and-raspberry-pi-part-1/

Disclaimer: I work for DreamFactory Software.


Provided audit trail is turned on, then I prefer to use the following to help track down login failures (which is usually the cause of locked accounts):

select * from dba_audit_trail where returncode in (1017, 28000) order by timestamp desc;

returncode is the ORA- error that would be returned from the database: 1017 is "invalid usercode or password" and 28000 is "account is locked".

This view provides valuable information such as the time the login failures occurred, the computer where the error originated from, as well as the OS user.

If you do not have audit_trail turned on, which you can verify with:

show parameter audit_trail;

then obviously, the above query will not help with past login failures, but you may want to consider turning it on to help with future failures.  Also, keep in mind that this change requires a bounce of the database to go into effect.

You can refer to this document in order to set this environment variable to a value that is appropriate to your environment: http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams017.htm

You would set this parameter as follows:

alter system set audit_trail='DB' scope=spfile;

Keep in mind that if you do turn on audit_trail, consider periodically clean out older records out of the audit table or the table can grow to be pretty unwieldy.  There is an informative blog that talks about that here: http://www.oradba.ch/2011/05/database-audit-and-audit-trail-purging/

As for another possible cause:

If you are running in a distributed environment (multiple databases connected by database links), then proxy database links (null user/password) could be your problem since such database links attempt to login to the destination database with the credentials that are passed from the source database.

If the credentials are different between the source and the destination, ORA-1017 is returned which counts against the login attempts on the destination DB (provided the usercode was the same, and just the passwords are different).

In addition, if the source DB user is logged in via a proxy user, then proxy DB links will ALWAYS fail with ORA-1017 regardless of whether or not the passwords match.


Idempotent PostgreSQL schema dump that can be applied to an existing DB

I wonder if there's a simple way to produce DDL that is idempotent

There is not, and it doesn't make much sense for a schema with data in it. If one column goes away and another appears, how should such a tool know whether it's an ALTERed column that's had its type changed and been renamed, or a DROP + a ADD? It could be either. The tool would have no way to deduce what the expression used to move the data over, if any, was either.

What you should generally do is use a schema management tool that can create versioned "migration" files for you, like liquibase.


is it a good practice to ip block dev/test app servers from production database server?

Not only is it a best practice, it's mandatory to any major compliance requirements such as PCI V2 & V3, SOX, and almost any auditor will ask about it. Yes, having them on the same network is asking for major problem.

Page 5 of the PCI DSS V2:

For example, there must be a clear segmentation of functions and segregation
of networks with different security levels; segmentation should
prevent the sharing of production and test/development environments;

Page 32: 6.4.1 Separate development/test and production environments.


How to use column alias to calculate other column value

Additionally to previous answers.

1) You can try to use user-defined variables:

SELECT
    @earnings := (`house_rent`+`conveyance`+`medical`+`dearness`+`others_allowances`) AS earnings ,
    @deductions := (`income_tax`+`pro_tax`+`emp_state_insu`+`absence_fine`+`others_deductions`) AS deductions,
    @earnings - @deductions AS net_salary
FROM
    salary
    -- , (SELECT @earnings:=0, @deductions:=0) vars

Documentation do not guarantee the calculations order, but in practice all calculations are performed from left to right.

2) If your MySQL version is 8+, you can use CTE:

WITH cte AS (
SELECT
    (`house_rent`+`conveyance`+`medical`+`dearness`+`others_allowances`) AS earnings,
    (`income_tax`+`pro_tax`+`emp_state_insu`+`absence_fine`+`others_deductions`) AS deductions
FROM
    salary
)
SELECT earnings, deductions, (earnings - deductions) AS net_salary
FROM cte;


selecting where two columns are in a set

Is there a construct in SQL that would allow me to do something like the following:

Yes, there is, almost exactly as you wrote it. Just put col1, col2 inside parentheses:

-- works in PostgreSQL, Oracle, MySQL, DB2, HSQLDB
SELECT whatever
FROM t                               --- you missed the FROM
WHERE (col1, col2)                    --- parentheses here
       IN ((val1a, val2a), (val1b, val2b), ...) ;

If you try it however in a DBMS, you may find that it doesn't work. Because not all DBMS have implemented all the features of the (evolving) SQL standard. This works in latest versions of Oracle, MySQL, Postgres, DB2 and HSQLDB (it was not well optimized in MySQL and not use indexes, so it should be avoided there unless they fixed it in 5.7).

See MySQL documentation about IN operator and Postgres documentation about Row constructors. The two* (or more) values in parentheses is called a row-constructor.

Other ways that express the same idea:

-- works in PostgreSQL, DB2
SELECT whatever
FROM t
WHERE (col1, col2)
       IN ( VALUES (val1a, val2a), (val1b, val2b), ...) ;

SELECT t.whatever
FROM t
  JOIN
    ( VALUES (val1a, val2a), (val1b, val2b), ...) AS x (col1, col2)
      ON (x.col1, x.col2) = (t.col1, t.col2) ;

Both work in Postgres and DB2 (afaik). The last one can be modified to work in SQL Server, too:

-- works in PostgreSQL, DB2, SQL Server
SELECT t.whatever
FROM t
  JOIN
    ( VALUES (val1a, val2a), (val1b, val2b), ...) AS x (col1, col2)
      ON  x.col1 = t.col1
      AND x.col2 = t.col2 ;

It can also be modified to work everywhere, by placing the values into a (temporary or permanent) table first:

-- works everywhere
CREATE TABLE values_x
( col1  ...,
  col2  ...) ;

-- use appropriate for the DBMS syntax here
INSERT INTO values_x (col1, col2)
VALUES (val1a, val2a), (val1b, val2b), ... ;

SELECT t.whatever
FROM t
  JOIN values_x  x
      ON  x.col1 = t.col1
      AND x.col2 = t.col2 ;

DROP TABLE values_x ;

And there is always the long way or converting the IN to a long expression with OR that should work everywhere:

-- works in all SQL DBMS
SELECT whatever
FROM t
WHERE col1 = val1a AND col2 = val2a
   OR col1 = val1b AND col2 = val2b
   ---
   ;

*: It can actually be just one value, with ROW(v), see Postgres docs.


Pros and cons of DBCC SHRINKDATABASE operation

As you know DBCC SHRINKDATABASE attempts to shrink both data and log files.

Personally, I would probably never use SHRINKDATABASE. Instead I'd use DBCC SHRINKFILE, and even then would only use it on datafiles to resize them correctly, or on log files if I was trying to shrink them back in order to grow them out again to the correct number of virtual log files (VLFs). The other use case for datafiles (if you had multiple) would be if you were looking to remove them; you would first need to shrink with EMPTYFILE.

In a properly configured database, you should never need to use either SHRINKDATABASE or SHRINKFILE. You should have pre-allocated and sized your log and data files correctly, and set caps to avoid files growing out of control. Prevent the log file from needing to grow in the first place through correct sizing (to accommodate the longest possible running open transaction) and regular log backups.

For debate on VLF sizing please check out the SQLSkills article:

Transaction Log VLFs – too many or too few?


Can a complex primary key made up of 4 fields have only one of this fields set to AUTO INCREMENT?

You can do this:

CREATE TABLE mytable
(
    `ID` MEDIUMINT NOT NULL AUTO_INCREMENT,
    `C1` MEDIUMINT NOT NULL,
    `C2` MEDIUMINT NOT NULL,
    `Year` MEDIUMINT NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (C1, C2)
            REFERENCES othertable(pkcol1, pkcol2)
);

ALTER TABLE mytable ADD UNIQUE INDEX(`C1`, `C2`, `Year`, `ID`);

Adding the ID column to the unique index will guarantee that you can have the same values for C1, C2 and Year in your table. If you don't want that, remove ID from the ALTER TABLE statement.

The syntax of the FOREIGN KEY is for a multiple column key in another table called othertable. pkcol1 and pkcol2 would be the corresponding columns in that table.

ID would be your fourth column in the question example code you presented.


Will DIFF backup size be reduced if we shrink the data files?

A differential backup actually only reflects the changes that were made on your database since the last full backup; more specifically: the extents that were changed since your last full backup.

A reason for the differential backup still backing up the no longer used extents may be that you have deleted a bunch of data using TSQL DELETE and not dropped the tables involved.
From https://docs.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql?view=sql-server-2017:

  When rows are deleted from a heap the Database Engine may use row or
  page locking for the operation. As a result, the pages made empty by
  the delete operation remain allocated to the heap. When empty pages
  are not deallocated, the associated space cannot be reused by other
  objects in the database.

Since the pages remain allocated, they will also be included in the backup.

My guess is that a DBCC SHRINKFILE will deallocate the empty pages, thus decreasing the size of DIFFERENTIAL backups, however I was not able to confirm this.

Shanky's comment of course also is a valid question as backup compression surely will not store empty pages themselves but only a reference to them and therefore vastly make a difference in size for you (in case you have Enterprise Edition, though).


SSRS and PowerBI Report Server on same server

I now have this working on my Win-2012 server with PBIRS (August 2018) and SSRS 2016.

First they need to have thier own ReportServer Databases. I used:

SSRS: ReportServer
PBIRS:  PBIReportServer

The Webservice URL's use the same port number but different Virtual directories.

SSRS: http://ServerName:80/ReportServer
PBIRS:  http://ServerName:80/PBIReportServer

And the Webportal again uses the same port number, but different URLS.

SSRS: http://ServerName:80/Reports
PBIRS:  http://ServerName:80/PBIReports

And so far it seems to be behaving. I've restarted the server and both services started automatically and appear to be working.


Which database management system type to choose for price tracking?

I would suggest building a test system using whatever DBMS you're most familiar with.  As speed is a mission-critical requirement, I would put the data on an NVMe SSD, and load it up with as much fake data as you can create, then put that through a rigorous set of tests.

Back-of-the-envelope calculations suggest you'll need around 100 GB of space for a years-worth of data, so a 1 TB NVMe SSD should suffice.  Of course, if data redundancy is a concern, you may want two, with data being mirrored between them.

PostgreSQL should have no difficulty handling your data.


The backup size is simply used 8k pages. These 8k pages are part of the MDF. On restore, the MDF and LDF files may need recreated (if already existing and different in some way) or just created

So backup file size isn't an indicator of space used on disk by the restored database. And this ignores backup compression too

So, if the MDF is 100GB (with 2GB actually used), : unless you have Instant File Initialisation on, then the 100GB needs zeroed. Note the LDF must be zeroed.


Feasible to have thousands of users in Postgres?

Yes, it should be fine. You should use connection pooling though, as pg uses a fair amount of memory per connection (about 10MB AFAIK).

More than 500 simultaneous connections per box will be a problem though (like actively querying the database at the exact same time). More cpus/cores is better. Use SSDs with RAID 10.

Your SaaS application should connect as one user, then set role to the real user. This allows you to use connection pooling, as the connection string will be the same, but use different users. You should reset role when returning connection to the pool.

This is not really database authentication. It's proxy authentication (aka Impersonation).

You could also consider separate pools per company or per role.

To make admin easier, you can put users into groups and set permissions via groups. This is called RBAC.

Update: I was able to create 50,000 users in 2.4 seconds. PGAdmin is noticeably slower, due to the number of users. However connecting via JDBC is as fast as before. I was unable to drop 50,000 users at once, but could do about 10,000 at a time.


Is it normal to share CPUs on a VM of SQL Server?

Yeah, it's pretty common.  A lot of times VMs are used to consolidate lots of SQL Servers (especially ones that don't have extreme performance requirements) onto one host.  This can save on licensing costs, since SQL Server can be licensed at the host level.

Is it a good idea?  I mean, it depends a lot on how oversubscribed the VM is, and how CPU intensive the workloads are.

Looking at the screenshots of the two execution plans, they are basically the same except for the parallelism.  One problematic area in the parallel plan is the serial zone where the "Top" operator lives:

There is some overhead associated with bringing all the rows together onto one thread, and then redistributing them for the parallel insert.  I wouldn't expect that overhead to be 30 seconds though.

  So my theory is that because first query uses multiple CPUs, it must wait till all CPUs in question are idle, and therefore it just waits.

No, that's not how parallelism works in SQL Server.  The threads that are scanning the clustered index at the top-right of the plan can do very uneven levels of work depending on how busy the different CPUs are.

Now, if this instance of SQL Server is so busy that all of the available threads are being used for other queries, then the parallel query might be waiting on THREADPOOL.  Which brings me to my next point:

The parallel query is most likely waiting on some resource.  I would start by looking at the "WaitStats" portion of the execution plan in SSMS:

That'll be in the "Properties" window of the left-most operator in your plan.  As one example, a really high SOS_SCHEDULER_YIELD value in this case could be a sign of this SQL Server instance not getting a turn on the hosts CPU.  Jonathan Kehayias has a really good post on that topic here:

CPU Ready Impact on SOS_SCHEDULER_YIELD

You could also compare the ratio of elapsed time to CPU time in the two queries.  Those numbers are in the same properties window:

If the ratio is significantly different between the two queries, that's another sign that the parallel query is waiting on some resource.

If you have access to the host / virtualization, you could look into stats directly there to see if guests are waiting long periods to be scheduled on the CPU.  Jonathan has another post about that here, which is specific to VMWare: CPU Ready Time in VMware and How to Interpret its Real Meaning


How to determine if a column is the first part of an index?

Use INFORMATION_SCHEMA.KEY_COLUMN_USAGE to retrieve the position of columns in your key. The position of column in a key is the ORIDINAL_POSITION column of KEY_COLUMN_USAGE.

See more information from MySQL::The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table


Instead of
'2020-04-30' = date_add(op.dataDate, INTERVAL 14 DAY);

Use
op.dataDate = date_sub('2020-04-30', INTERVAL 14 DAY);

Your first statement will be interpreted as "add 14 days to all dataDate and return when that is 2020-04-30."  This will require a full scan of the table.
The second statement will evaluate to: "return records where the dataDate is 2020-04-16."  This allows the engine to perform a seek on your index that begins with dataDate.
Do whatever weird stuff you want to do to expDate since that won't factor much into how the query engine will optimize.


ORDER BY gives ERROR: function array_position(text[], character varying) does not exist

Sample data

You have a table like,

CREATE TABLE research(colors)
  AS VALUES ('Blue'), ('Orange'), ('Yellow');

ENUM type

You have an enumerated list of colors. So the easy thing here would be to use an ENUM type

CREATE TYPE colors AS ENUM ('Red','Orange','Yellow','Green','Blue');

Then

ALTER TABLE research
  ALTER COLUMN colors      -- myColorsColumn
  SET DATA TYPE colors
  USING (colors::colors);  -- myColorsColumn::NewType

Now it's faster, more efficient, and cleaner. MORE WIN. MORE JOY. Etc. ENUM are stored as 4-byte internally.

ORDER BY colors

Is all you'll need.

Array Sort

But, you've got a God-given right to treat this like any other database that doesn't support ENUM types,

ORDER BY array_position(ARRAY['Red','Orange','Yellow','Green','Blue'], color);

I think this is perfectly fine, but I believe there is a limitation here in the implementation,

ARRAY['foo', 'bar', 'baz']

Is essentially

ARRAY['foo', 'bar', 'baz']::text

This means you need to either (speed doesn't matter as both options perform the same)

Make the color column of the native text. This can be done in the call, or you can actually modify the table and should. In PostgreSQL, nothing should be varchar without a limit (since that's just a parallel type for text), and very few things should be varchar with a limit (since there is no advantage)

color::text            -- PostgreSQL sexy sexy cast
CAST(color AS TEXT))   -- ANSI SQL standardized vanilla and boring

Or, you can construct the array itself as type varchar[]

array_position(ARRAY['Red','Orange','Yellow','Green','Blue']::varchar[], color);

Further notes

In PostgreSQL, we wouldn't use varchar for colors. Even if you insist on not using an ENUM here (though I would), that should be text
The array_position is a shorthand, I expect it to be substantially slower than a similar operation though

CASE
  WHEN color='Red' THEN 1::smallint
  WHEN color='Orange' THEN 2::smallint
  WHEN color='Yellow' THEN 3::smallint
  ... etc
END;


MySQL does not follow Ubuntu System Time Zone

You say you run mysql inside docker. Docker needs timezone as an environment variable as follows:

Add the following to mysql startup command in docker:

-e TZ='America/New_York'

https://github.com/docker-library/mysql/issues/320


TempDB Contention with 24 physical tempdb files on 48 core AMD Opteron box

Trace flag 1118 forces uniform extent allocations instead of mixed page allocations.   The trace flag is commonly used to assist in TEMPDB scalability by avoiding SGAM and other allocation contention points. If you have SQL Server 2008 or SQL Server 2005 and the fix applied and are still encountering TEMPDB contention consider trace flag 1118 for further assistance in resolving the contention.


Get-MSSQLLinkPasswords Powershell script doesn't seem to work under SQL Server 2016

I've tested dbatools' Copy-SqlLinkedServer from SQL Server 2016 to SQL Server 2016 and SQL Server 2014 to SQL Server 2016.

All linked servers and their respective logins were successfully migrated.

I checked both the permissions and ensured the same login was being used. What versions are to you migrating from? What error do you get?

(I am the creator of dbatools).


howto choose the right character set in MariaDB

Choosing character set usually based on the environment, each set has pros and cons. for example if your system has mixed character I strongly recommend utf8 but even utf8 has many types. for example: utf8_general_ci is faster than utf8_unicode_ci but less accurate for sorting. and if you want to use specific language Czech for example then you need to make the character set utf8_czech_ci to get full language function.

I found the following URLs regarding encoding hope they will help:
http://dev.mysql.com/doc/refman/5.0/en/charset-charsets.html
http://dev.mysql.com/doc/refman/5.0/en/charset-unicode-sets.html
http://code.openark.org/blog/mysql/mysqls-character-sets-and-collations-demystified

the following URL contains some examples about disk usage for character set:

http://spin.atomicobject.com/2010/08/23/on-the-importance-of-character-sets-and-character-encodings-in-mysql/


Question on EDR: a plant may have one or more harvesting seasons

I would tend towards a one-to-many relationship between Plants and Harvests.
i.e. the Harvests table has a foreign key to Plants allowing as many harvests as desired.  The Harvests table would have all the details relating to a single harvest, such as Start Date, Minimum Duration of Harvest, etc.
I'd combine the two green tables.  If the start date for one harvest is earlier than the start date for the 2nd harvest, then that's just showing them correctly ordered in the presentation layer (i.e. by an ORDER BY clause on StartDate).
I've created a simple fiddle to show you how I'd implement the design here.
This is the DDL:
CREATE TABLE plant(
    plant_id INT AUTO_INCREMENT,
    taxon_name VARCHAR(25),
    common_name VARCHAR(25),
    PRIMARY KEY (plant_id)
);

CREATE TABLE harvesting_req(
    harvesting_req_id INT AUTO_INCREMENT,
    plant_id INT,
    number_of_harvests INT,
    harvest_start_date DATE,
    PRIMARY KEY(harvesting_req_id)
);

ALTER TABLE harvesting_req
ADD FOREIGN KEY(plant_id_fk)
REFERENCES plant(plant_id) ON DELETE SET NULL;

JOINing the tables looks like:
SELECT p.taxon_name
    , p.common_name
    , hr.*
FROM plant p
    INNER JOIN harvesting_req hr ON p.plant_id = hr.plant_id;

The output looks like:

taxon_name
common_name
harvesting_req_id
plant_id
number_of_harvests
descriptive_growing_season
min_length_of_growing_harvest
max_length_of_growing_harvest
harvest_start_date

Taraxacum
dandelion
1
1
2

2021-04-15

Taraxacum
dandelion
2
1
3

2021-07-01

I'm not convinced you need a number_of_harvest column in the harvesting_req table since you'd have a row per harvest.


This seems to have worked:

SELECT SLD.SLD_Number, SLD.Date_Received, SLD.Time_Received, [Payment
table].Date_Received, IIf(Payment_Status='NOT PAID',DateDiff("d",[Invoice
table].Date_Received,Date())) AS Expr1,
IIf(Payment_Status='PENDING',DateDiff("d",[Invoice
table].Date_Received,Date())) AS Expr2, DateDiff("d",[Invoice
table].Date_Received,[Payment table].Date_To_Payment) AS Expr3,
DateDiff("d",[Order table].Date_Quotes_Requested,SLD.Date_Received) AS
Expr4, DateDiff("d",[Order table].Date_Of_Order_Number,SLD.Date_Received) AS
Expr5, IIf(Payment_Status='PAID',DateDiff("d",[Invoice table].Date_Received,
[Payment table].Date_Authorised_Paid)) AS Expr6, IIf([Invoice
table].Date_Received=NULL,'True','No') AS Expr7,
Format(SLD.Date_Received,"mmm-yyyy") AS [Month Of SLD]
FROM (([Payment table] RIGHT OUTER JOIN SLD ON SLD.SLD_ID = [Payment
table].SLD_ID) LEFT OUTER JOIN [Order table] ON [Payment table].SLD_ID =
[Order table].SLD_ID) LEFT OUTER JOIN [Invoice table] ON [Invoice
table].SLD_ID = [Payment table].SLD_ID;


Select DISTINCT records with extra information

SELECT c.*
FROM
      Customers AS c
   INNER JOIN
      ( SELECT [Address 1], MIN([Order Date]) AS min_od
        FROM Customers
        GROUP BY [Address 1]
      ) AS grp
         ON  grp.[Address 1] = c.[Address 1]
         AND grp.min_od = c.[Order Date] ;

This may still give you two rows for an address, if you have two rows with the same minimum [Order Date] for an address.


Reclaiming space after delete statement in SQL Server

You can identify and resolve heap / index fragmentation following this guide: How to identify and resolve SQL Server Index Fragmentation
You likely only need to reorganize, and not worry so much about rebuilding as often. There's recommendations for when to use one over the other, but rebuilding is a heavier operation that likely doesn't result in much more benefit than reorganizing.
While 20 million rows is a nice little chunk of data, it isn't anything enormous by any means. I would still potentially recommend always using clustered indexes and you'll see the best benefits during the JOINs and calculations on those tables.
Using clustered indexes in a smart way will also reorganize your data for you in a sense. What I mean by that, is if every day you're loading 20 million records into an empty table, and then add a clustered index on that table after the data has been fully loaded, it'll efficiently organize the data sorted on that clustered index. It can basically take the place of manually rebuilding / reorganizing operations that are pretty heavy operations by themselves. Adding the clustered index to the table after the data has been fully loaded is usually the most efficient way for it to reorganize the data.


This should come close to meeting your need.

This assumes you want the rel_user_id from the most recently added row for a given user_id and parent_id, and that the row with the highest id should be the one most recently entered. If you want the rel_user_id with the highest value instead, .you can basically just use the agg subquery (changing MAX(ID) to MAX(rel_user_id).

Also, I didn't worrying about the join to the posts table - I'm guessing that parent_id is the foreign key column, and you've got that; you should be able to get there from here.

Here's the query:

SELECT ntf.user_id
      ,ntf.parent_id
      ,ntf.rel_user_id
      ,agg.notification_count
  FROM (SELECT user_id, parent_id, COUNT(*) as notification_count, MAX(id) as max_id
          FROM notifications
         WHERE user_id = 1
         GROUP BY user_id, parent_id
       ) agg
         INNER JOIN notifications ntf ON (    agg.user_id = ntf.user_id
                                          AND agg.parent_id = ntf.parent_id
                                          AND agg.max_id = ntf.id
                                         )
 ORDER BY user_id, parent_id
;

The agg subquery gets us one row for each user_id and parent_id; we'll put the WHERE clause specifying which user_id you want here, to limit how many records we're grouping. This gets us the count of rows for the user_id and parent_id combo, and gets use the "most recent" id.

We then tie back to notifications with an INNER JOIN to get the rel_user_id from the indicated id row.

Here's a db-fiddle.com link showing the query in action, with the sample data you provided.


Setting sql_slave_skip_counter is not working on Percona 5.6.24

From the release notes for 5.7.8:

"The maximum length of MySQL user names has been increased from 16 characters to 32 characters, which provides greater flexibility in choosing the user name part of MySQL account names. The change affects permitted user names in these contexts: ..."

followed by a long list of "Account Management Notes".  That was in addition to some "Incompatible Changes" in 5.7.6.

I guess that is one reason for advising against the Master running a newer version than the Slave.

It should be possible to rummage through the binlog using mysqldumpbinlog and find the pos of the next command.  Then do CHANGE MASTER ... to change just the position.

(Meanwhile, I am surprised that the SKIP... failed.)

Bottom line:  Previously, it was often safe to mix Master/Slave versions.  But 5.7 has so many significant changes that I suspect this is just one of many potential problems.


How should I build the architecture of my MongoDB database?

Should I use MongoDB for real-time changes (updates) in this project?

Yes, you can use MongoDB for real-time updates. There are two options available for real-time updates based on whether you are using MongoDB Atlas(Fully Managed Cloud MongoDB service) or Self managed MongoDB service.

Self Managed(On-Prem/Cloud) - From MongoDB 3.6 the new feature Change Streams got introduced. Change streams allow applications to access real-time data changes without the complexity and risk of tailing the oplog. Applications can use change streams to subscribe to all data changes on a single collection, a database, or an entire deployment, and immediately react to them.
MongoDB Atlas - This comes with a service called Stitch Triggers. This will enable you to execute application and database logic automatically, either in response to events or based on a pre-defined schedule. This is the backend uses the Change Streams functionality.

So MongoDB should be a perfect option for your requirement.


What causes Transactional SQL Server Replication to need to be rebuilt/re-initialised

To answer the question, you would need to test how many rows your change will affect (an update to a column without a where clause, or adding a column will affect all rows). A reindex will not cause replication to be "rebuilt". Can you clarify what do you mean by "rebuilt" do you mean all rows are re-replicated? or do you mean you are forced to snapshot?

To know what is actually being replicated, you can use http://msdn.microsoft.com/en-us/library/ms186983.aspx


Attempting to Pivot a column of grades and coming up with NULL values

Change your query to this:

SELECT
     pvt.ID
    ,[198] = isnull([198],0)
    ,[281] = isnull([281],0)
    ,[280] = isnull([280],0)
    ,[295] = isnull([295],0)
    ,[296] = isnull([296],0)
    ,[297] = isnull([297],0)
FROM ( SELECT [ID], [Catalog], [Grade] From data ) as c
PIVOT (
    SUM([Grade]) FOR [Catalog] IN ([198], [281], [280], [295], [296], [297])
)AS PVT

or perhaps this:

SELECT
     ID = cast(pvt.ID as char(12))
    ,[198] = isnull(str([198]),'        -')
    ,[281] = isnull(str([281]),'        -')
    ,[280] = isnull(str([280]),'        -')
    ,[295] = isnull(str([295]),'        -')
    ,[296] = isnull(str([296]),'        -')
    ,[297] = isnull(str([297]),'        -')
FROM ( SELECT [ID], [Catalog], [Grade] From data ) as c
PIVOT (
    SUM([Grade]) FOR [Catalog] IN ([198], [281], [280], [295], [296], [297])
)AS PVT

to yield:

ID           198        281        280        295        296        297
------------ ---------- ---------- ---------- ---------- ---------- ----------
1012642448           -          -          -           1          4         -
1012923963           -          -          -           1         -          -
5000289133           -          -           4         -          -          -

The SQL value null represents the absence of data, so when you pivot and a cell as no data a null will perforce be generated as the cell value. It is your responsibility as the programmer to coalesce the null to whatever domain value is appropriate for missing data, when that makes sense.


Where to put ReportServer and ReportServerTempDB databases?

SSRS Planning a Deployment would be a good article to read through.

You can read specifically on the SSRS databases here. As it states:

  A report server is a stateless server that uses the SQL Server
  Database Engine to store metadata and object definitions. A Reporting
  Services installation uses two databases to separate persistent data
  storage from temporary storage requirements. The databases are created
  together and bound by name. By default, the database names are
  reportserver and reportservertempdb, respectively.

You general, with large reporting requirements, will run SSRS databases on one server (shared SQL Server instance) and then the SSRS components on another. Keep in mind though how licensing works, you will have two licensed SQL Server servers with this type of setup. I normally see the report server databases reside on the same server as a data warehouse. I have not seen any issue with the SSRS databases being on a shared instance with the data warehouse. As it states in the article they are only storing information for SSRS (execution log, schedules, report definitions, etc.).

The "meat" of what SSRS does resides with the SSRS component services. SSRS processes the data the report is requesting and then also works out rendering it, all of this is resource intensive if there is a high load. It will depend on what your specific needs are if it justifies a standalone server. I generally will see SSRS installed on the web server with the application that uses the reports.

To determine if I need a standalone report server I would probably consider things like number of reports (many small or many large), the frequency (internal report server for monthly reporting or serving up user reports for external web application). You may also look at the report design or standard used by the developers, if the reports do a high amount of data processing on the report side, versus letting the database engine do it.


Multilingual Dictionary Database question

If it were my decision, it would look like this:

words (wordID, word, languageID, date_of_addition)
related_words (wordID, otherword0
definitions (wordID, definition, order)
languages (languageID, languageName, languageIdentifier)
language_name_translations (languageID, languageTranslatedID,
                            languageTranslatedName)
  So that this table can be joined back on the previous one to use the two ids
  to show for instance that 2 (spanish) is presented to users of 1 (english) as
  "Espanol" (really it's just because this is _my_ definition that it makes sense
  to me, you would work on your own definitions of course)
synonyms (wordID1, wordID2)
antonyms (wordID1, wordID2)
word_origins (originID, wordID1, wordID2, notes)

So I would suggest at this point that you go get a copy of OED and identify all the parts that they present in their listings. Find out things like related words and word origins and consider that every single entry is a list in some fashion or another, so every presented part should be an entity in a table.


Barman not able to connect: fe_sendauth: no password supplied

After checking barman's source code, I've realized that barman is actually overwriting the conninfo string:

    # Override 'dbname' parameter. This operation is required to mimic
    # the behaviour of pg_receivexlog and pg_basebackup
    self.conn_parameters['dbname'] = 'replication'

Which isn't very intuitive, thus the dbname in .pgpass should include replication db:

pg.example.com:5432:replication:barman:s3cr3tP2ssw0rd


Do I need to perform db.fsyncLock() when running a replica set without journal enabled

Yes, to get a consistent backup (when journaling is not enabled) you need to db.synclock your mongod. You use synclock only at that node from where you take the backup (and you only need to make the backup from one node; you can restore whole replica set from one backup).
db.synclock locks only that node where you give this command, other nodes work as they were, so lock (and make the backup) one of the SECONDARY nodes.

Addition, you DON'T need to make the backup of arbiter, because there is no data to be backuped. Arbiter is just "voter" or "observer" at replica set.


powershell to get sql server memory counters and show value

Aaron Bertrand wrote a good post on it that is pretty detailed...How I use PowerShell to collect Performance Counter data.

Then Laerte Junior has an excelent walk through on how he finds the counters he wants in a Simple-Talk article: Gathering Perfmon Data with Powershell. This might be where you want to start. It has some cmdlets that he uses to capture the counters for a particular instance I believe.

See if this is what you  need:

$listofmetrics = (Get-Counter -ListSet "*Databases" -ComputerName $hostname).Counter | Where {$_ -like "*\Transactions/sec"}
$listofmetrics | Get-Counter


Database design issue, with multiple variables and constraints

Here is one approach I use this model.  I suggest being careful with attributes,  do not turn everything into an attribute,  I have dealt with DB models that use such an approach several times.  It makes what should be simple query a mess, and means tracking types, and lots of casting to and from data types.

treat the below a psuedo code just to get the ball going on conversation

create table item (
    item_id serial primary key,
    item_descrip text,
    item_uom_id int, --UOM = unit of Measure another table not described
    item_list_price);

create table atts (
    atts_id serial primary key,
    atts_name char(55),
    att_value char(85),
    atts_notes text
);

create table cats (
    cats_id serial primary key,
    cats_name char(55),
    cats_notes text);

create table links_cat_item_atts (
    lcia_id serial primary key,
    lcia_type char(4),
    lcia_link_id  int,
    lcia_cat_id int default NULL,
    lcia_att_id int default NULL,
        CONSTRAINT link_type_con CHECK (lcia_link_id = 'ITEM'
        OR lcia_link_id = 'CATS' OR lcia_link_id = 'ATTS')) ;

--to use this most simple quiers to figure out the what is linked to what

Select * from item
    inner join links_cat_item_atts on item_id = lcia_link_id and lcia_type = 'ITEM'
    inner join cats on lcia_cat_id = cats_id
    inner join atts on lcia_att_id = atts_id

-- to pull cats with attributes this

Select * from cats
    inner join links_cat_item_atts on cat_id = lcia_link_id and lcia_type = 'CATS'
    inner join atts on lcia_att_id = atts_id

-- built a list of attributes that are for cats you can do this

Select * from atts where atts_id in (select lcia_att_id  from links_cat_item_atts
                                    where  lcia_link_id = LIMIT_ID
                                    and  lcia_type = 'CATS' )

--this holds true for items to build a list of attribiutes that are for specific item
Select * from atts where atts_id in (select lcia_att_id  from links_cat_item_atts
                                    where  lcia_link_id = LIMIT_ID
                                     and  lcia_type = 'ITEM' );

-- you can go back and add FK constraints and other things if you choose.

-- I use this model for  several things
-- do not treat price as a attribute this will just muddy the waters
-- if you do treat everything as attribute it means you have to track its data
--type in the database

Select * from item, atts, cats, links_cat_item_atts
    inner join links_cat_item_atts on item_id = lcia_link_id and lcia_type = 'ITEM'
    inner join cats on lcia_cat_id = cats_id
    inner join atts on lcia_att_id = atts_id
    where item_price between 10 and 50
    and cats_name = ' hi there cats '
    and atts_value = 'filter on atts' ;


Is there a way to get a history of transaction log used space in SQL Server?

Nothing is logged by default, generally speaking. So, unless you have something keeping track of the amount of log generated, you are out of luck.

Ordinarily, we would recommend you to check the size of your log backups, but as I understand it, you don't have those and the purpose here is to determine how much space you would need for those. Catch 22. :-)

Even with some active monitoring, you would have to check this pretty frequently, since you never know when checkpoint kicks in and "truncate the log". There's a perfmon counter you can use for this: "Log File(s) Used Size (KB)". And you can grab the value from sys.dm_os_performance_counters. I.e., pretty easy to create a job that uses a SELECT to check the used space in the tlog for selected databases, store it in a table and execute that job every minute, or whatever frequency you find suitable.


Is there any best number for rows count in a table to start using indexing?

An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.

I want to know if there is any specified point in a table's rows count that when we reach that point, then we could use an index.

The rowcount in a table does not dictate the use of Index. Its your queries (pattern) that should dictate the need for creating indexes, so that the data retrieval is as fast as possible.
The query optimizer relies on statistics to produce optimal query plan.
From SQL Server Index Design Guide :

The selection of the right indexes for a database and its workload is a complex balancing act between query speed and update cost.
Narrow indexes, or indexes with few columns in the index key, require less disk space and maintenance overhead.
Wide indexes, on the other hand, cover more queries. You may have to experiment with several different designs before finding the most efficient index. Indexes can be added, modified, and dropped without affecting the database schema or application design.

You should use SQL Server's DMV to tune your indexing strategy or even use sp_BlitzIndex to get more insights.
Refer to :

Query Tuning Fundamentals: Density, Predicates, Selectivity, and Cardinality
Ten Common Threats to Execution Plan Quality


script to spool all tables in a schema to xml format in Oracle 12c

I don't know SQLcl, but in sqlplus a pl/sql block must be terminated by a line

/

I hope the following will work

spool sqltables.sql
set echo off
set feed off;
set serveroutput on
prompt set termout off
prompt set sqlformat xml
begin
for table_list in (select table_name from dba_tables WHERE owner='MYUSER') loop

   dbms_output.put_line('spool ./'||table_list.table_name||'.xml');
   dbms_output.put_line('select /*+ parallel */* from '||table_list.table_name||';');
   dbms_output.put_line('spool off');

end loop;
end;
/
prompt exit
spool off;
@sqltables.sql

so I added SET SERVEROUTPUT ON. This is necessary in sqlplus, otherwise you will not see the dbms_output output.
Also I added the PROMPT statements that write commands to the sqltables.sql file.


Replicated timestamp column in different timezone

The current time zone setting does not affect values displayed by functions such as UTC_TIMESTAMP() or values in DATE, TIME, or DATETIME columns. Nor are values in those data types stored in UTC; the time zone applies for them only when converting from TIMESTAMP values.

http://dev.mysql.com/doc/refman/5.6/en/time-zone-support.html

Change your binlog format to ROW to eliminate the vagaries of statement-based replication and then change your DATETIME columns to TIMESTAMP (on the master, let the change replicate).

This solves the issues you're experiencing.

Although... it solves entirely the wrong problem, which is that it's typically better to store times properly in UTC and localize them as needed.  Of course, that's what TIMESTAMP columns essentially do -- they store datetimes as UTC after converting then from the current session time zone, and then they convert back when you read them.

  If you want locale-specific arithmetic for DATE, TIME, or DATETIME values, convert them to UTC, perform the arithmetic, and then convert back.


On which node first transaction will be executed in Always-on setup?

1) From Overview of AlwaysOn Availability Groups (SQL Server)

  The primary replica sends transaction log records of each primary database to every secondary database. Every secondary replica caches the transaction log records (hardens the log) and then applies them to its corresponding secondary database.

So, primary first, then secondary.

2) Are you asking how node 1 is brought back on line? That's what your operations people and DBAs are paid their huge salaries to do.

Alerts would be a good idea. Maybe even a very good idea.

If node 1 dies AlwaysOn will re-direct connects to node 2, transparently to the application. In-flight work may receive an error. New connections will be routed to node 2. This is the HA and DR in action. When node 1 comes back you can continue running on node 2 as primary and have node 1 as secondary, or organise a managed failback at a time of your choice. If node 2 dies while node 1 is still down, then you have a problem.

3) Active-passive has the secondary as "warm": it is ready to become the primary when required but cannot do any application work until then. Active-active has a "hot" secondary: it can process read-only transaction from the application, and be used for backups and such like. IIRC one license is needed for active-passive and two for active-active.


How to add data to sql server using MS access front end, when user will not always have sql connection

This is the typical scenario for replication. You could set up a second copy of the SQLServer database on his machine (remember that SQLExpress has limitations on the size of the database - 10 GB - and does not have SQLAgent) using merge replication.

The SQL Server instance you are using now could act as a publisher and the laptop could act as a subscriber. The Access frontend should point to the instance of SQL Server on the laptop instead of pointing to the publisher. When the user gest out of the wifi range, he would still be able to work using the local SQL Server instance. When he gets back to office, the subscriber could sync with the publisher.

Since SQLExpress does not have a task scheduler (no SQL Agent), you would have to use the Windows task scheduler for running the merge agent. In case you need very frequent sync, you could use the continuous switch in the merge agent.

Keep in mind that merge publishing a database is not totally painless: all published tables (articles) will need to have a uniqueidentifier UNIQUE column decorated with the rowguidcol flag. If you don't have such column, it will be added automatically, which will likely break all the insert statements that don't specify the column list. Moreover, all articles will get triggers for insert/update/delete operations and your workload might suffer a noticeable slowdown during write operations, depending on the shape of the workload itself.


How to find when the user last logged in Mysql

mysql hasn't such a thing.
You could force the users to change the password every 90 days and select the date, where the users didn't change their password.

By

SELECT User FROM mysql.user WHERE password_last_changed <  NOW() - INTERVAL  3 MONTH;

Everything else would require things like enable file logs see for more information


Adding time slots through trigger SQL Server

Your schema looks fine but you need to calculate the dates in a different way,
In T-SQL you can't just add and subtract.

@NumberOfSlots calculation should be like this:

    set @NumberOfSlots = convert (int, (DATEDIFF(MINUTE, @SlotStart, @SlotEnd)))/@Duration

adding to a datetime is done like this:

    DATEADD(MINUTE, @Duration, @SlotStart)

The order in the loop needs to be changed too:

    set @i = 0;
    While @i<@NumberOfSlots
        Begin
           Set @SlotEnd = DATEADD(MINUTE, @Duration, @SlotStart)
           insert into timeslots
           (AvailabilityId, SlotStart, SlotEnd, SlotDoctorId,IsAvailable)
           values
           (@AvailabilityId, @SlotStart, @SlotEnd, @SlotDoctorId,1)
           Set @SlotStart = @SlotEnd

           set @i=@i+1
        End

With these changes, I think the trigger will work and give correct values, but
that is a lot of work in a trigger.

I think you should consider solving this by building stored procedures doing the same, leaving the tables with data and no logic.

One stored procedure per table, that inserts a row to that table.

For 'Availability' the stored procedure could be createAvailability and the code would be very similar to what you have in the trigger, but in the loop it calls the stored procedure to create a row in Timeslots:

    CREATE PROCEDURE createAvailability
    (
        @AvailabilityId INT,
        @SlotStart DATETIME,
        @SlotEnd DATETIME,
        @SlotDoctorId INT
    ) AS
    BEGIN
        DECLARE @i INT
        DECLARE @NumberOfSlots INT
        DECLARE @Duration INT

        SET @NumberOfSlots = convert(INT, (DATEDIFF(MINUTE, @SlotStart,@SlotEnd))) / @Duration
        SET @i = 0;
        WHILE @i < @NumberOfSlots
        BEGIN
            SET @SlotEnd = DATEADD(MINUTE, @Duration, @SlotStart)
            EXEC createTimeslots(@AvailabilityId, @SlotStart, @SlotEnd, @SlotDoctorId, 1)
            SET @SlotStart = @SlotEnd
            SET @i = @i + 1
        END
    END


What are the main reasons to split a Data Warehouse into multiple databases?

You are definitely on the right track! 320GB is not huge for a database, particularly a DW.

  1) Current db is poorly optimized, little documentation, sub-optimal
  datatypes, sub-optimal indices.

  My rookie thoughts: Aren't these problems irrelevant to splitting the
  database? They are simply problems that need to be solved on their own
  either way.

This is bang on the money. Splitting one large(ish) poorly organised, optimised and documented database into 7 poorly organised, optimised and documented databases is a waste of time! You need to tackle the root of the problem!

  2) There are 372 objects in the current database which makes it slow.

  My thoughts: This hardly seems large in my opinion.

Again, you are correct! 372 is positively small in terms of number of objects - many large servers have 10's of thousands. From here

  The sum of the number of all objects in a database cannot exceed
  2,147,483,647.

Your 370 divided by ~ 2E9 ~= 1.7E-7 - so no worries on that score! :-)

  3) One database is harder to document and draw schema diagrams for than 7 databases (we will have views that will span multiple
  database).

  My thoughts: .... This seems completely ridiculous to me, but maybe
  I'm wrong. We've already organized our data warehouse by 13 'source
  system' schemas.

Again, you're correct. If there are 372 entities with inter-relationships between them, you'll need to document and diagram them. It's going to have an inherent degree of complexity. What you can do is try to split your overall system into subsystems and document them and then try and fit them into the bigger picture - great oaks from little acorns grow!

  4) One database will lead to more database deadlocks.

  -- Isn't this problem also completely irrelevant to having multiple databases? It's my understanding that deadlocks occur at the table
  level (actually usually even just the row level, but eh). Even then,
  all our data inserts happen at midnight, all our selects downstream to
  the BI happen at 2 am. Having two processes update the same table is
  irrelevant to multiple databases, is it not (deadlock would happen
  either way)? Also, I personally have seen no evidence of table
  deadlocks occurring during normal operations.

What you will lose in the multiple database scenario is ACID transactions within the same schema - OK, you can have 2-phase commit, but it's not as robust as transactions within the same schema (IMHO). I'm not sure of a valid reason to hive off tables if they're necessary for your requirements.

You appear to be talking about writes blocking reads? Well, you also appear to have a batch process at midnight followed by a querying process at 02:00? If you can make transactions/tables read only, this will take some load off the server engine as it is processing your data. Only you can tell if this can be applied to your scenario!

  5) Database technical ownership/ ownership.

  It's only the two of us that work on the database. It's possible he
  wants to really segregate our 'fiefdoms'. Really, hasn't been an
  issue, but can't user permissions be determined at the schema level
  anyway?

Certainly, ownership is at the table level and access can, depending on your server/version, be granted on a column and/or a row level - so the business of ownership is a complete red herring! If you are a server DBA performing a reorganisation (as opposed to simply scheduling backups and other mundane tasks), then you will need "access all areas"!

You should have a comment on every table and field in your system - you can put "ownership" (in the organisational as opposed to database sense of things) in there - commenting tables and fields is an excellent first step to documenting a system - it becomes self-documenting!

  What ARE valid reasons for splitting a Data Warehouse into multiple
  databases?

There can be many reasons - some are associated with multi-tenancy (both in terms of machine resources (CPU, RAM, HDD and Network) and client confidentiality or requirements. Have a look here and also google "database multi-tenancy" or similar.

Everyone says it, but it is a struggle - "documentation is very important"! As a first step, document your tables and fields in the comments. Produce ERD diagrams for all of your subsystems. Don't let anything new into the system without these steps being implemented. Best of luck in your new role!


Efficient way to move rows across the tables?

I think its faster (feel free to test) to keep the record in the same table while processing, but be sure to set an index on the column(s) that track which processor is using them.

Also use MyISAM tables if you don't require InnoDB's features. The ARCHIVE table can best be set to the archive type, so it does not make space for updates (if you don't need to write to it)


How can I access certain information from YQL?

From reading the YQL Documentation and viewing the data sources it appears that while the yahoo.finance.quote does provide data on all the stock data, running a query on a large data set make take some time and the data may be returned in multiple pages which means that your application will have to handle the pages. It also appears that the YQL data requires a where clause in order to run so you would need to know the symbols for all of the stocks.

There is a YQL Console which allows you to access the YQL data and run queries, view the output etc. I would suggest reading the YQL documentation and running some sample queries in the YQL Console to see if the data you require is available.

To access the YQL Console you can

Start at the YQL Console
In the top left hand corner under Datatables ensure you tick the Show Community Tables
In the search box type Finance. This should display all of the Finance tables (approx 31)
In the "Your YQL Statement" window type your YQL statment.
In order to get the average daily volume for Yahoo you could use the following YQL:

select symbol,AverageDailyVolume from yahoo.finance.quote where symbol in ("YHOO")


"Incorrect Syntax near S" in Stored Procedure

You should add 2 more parentheses -- ))) S. Try it!


How to estimate the size of a PostgreSQL DB to be dumped through pg_dump

A very rough estimate is to compute the size of all tables with:

select pg_size_pretty(sum(pg_table_size(tablename::regclass)))
from pg_tables
where schemaname not in ('pg_catalog','information_schema');


Sub-select takes "ages" - EXCEPT is much faster

in is better for list of constant values. Try using not exists instead.

Query:

explain analyze select distinct ticket_id from history h
       where not EXISTS (select id from ticket t where t.id = h.ticket_id);

And execution plan:

Unique  (cost=0.58..2294.04 rows=1 width=4) (actual time=23.140..23.140 rows=0 loops=1)
  ->  Merge Anti Join  (cost=0.58..2294.04 rows=1 width=4) (actual time=23.139..23.139 rows=0 loops=1)
        Merge Cond: (h.ticket_id = t.id)
        ->  Index Only Scan using history_ticket_id on history h  (cost=0.29..922.29 rows=30000 width=4) (actual time=0.037..6.848 rows=30000 loops=1)
              Heap Fetches: 30000
        ->  Index Only Scan using ticket_pkey on ticket t  (cost=0.29..1228.29 rows=40000 width=4) (actual time=0.026..6.970 rows=30000 loops=1)
              Heap Fetches: 30000
Total runtime: 23.189 ms

Reason for that I think is that for NOT IN Postgres will need to build distinct list of values from table ticket and then only filter history.
NOT EXISTS does not need to create a list. It can just check if value exists in tickets PK index.

Usually when you don't get "Anti Join" in such type of queries - something is written bad.


Why do/should DBMS cache at the application layer?

Databases typically implement a lot of the OS style functionality themselves. At a high level, this is done to improve performance and scalability.

To give you some examples:

Operating system file caches are generic, least frequently used, style caches. A database needs much finer control of what goes into the cache and what does not. For example, when you calculate hash tables for queries, you want to give them preference over file data while the query runs (it is cheaper to bring in some new file data than to page out hash tables). A database also need to be in very careful control of the memory allocator to avoid fragmenting the heap. Most operating systems are simply not up to the task of handling high the speed allocations that databases need and database typically implement their own memory manager, purpose build for the database engine.

Operating system caches also don't guarantee persistence unless you call flush in Linux or ask for unbuffered I/O in Windows. To guarantee consistency and ACID properties, a database needs much finer control over when data is on disk and when it is cached. Because of this, there is very little benefit to using synchronous, buffered I/O.

Furthermore, most OS caches are relatively poorly implemented. Windows file system caches only got really good NUMA style scale support around Windows 2008 and Linux still has very poor file system performance at high core count. FreeBSD doesn't even know what NUMA is. Since database need to run on very large servers (in some cases up to 32 CPU sockets) database vendors often implement their own caching data structures that are much more scalable than what the OS provides by default.

And last but not least: Database are very hungry for I/O and can drive the file system much harder than a typical file server. Relying on traditional, synchronous I/O is simply too slow for most database and they instead use aggressive, core affinitised, asynchronous I/O completion to gain scale. The I/O subsystem of database tends to be much more advanced than what a standard OS provides.

In general, you will find much more advanced control of machine resource in database source code than in operating system kernels. In fact, most high scale database implement their own user space kernel to avoid using the operating system kernel primitives. If you see a database do a lot of kernel transitions, start looking around for a better product.

Great Question!


PostgreSQL Operator Class for index support of custom operator

For trigram support, you can try the parray_gin extension

WHERE domains @@> ARRAY['www.foo%'];

If you just want to do prefix matching (more efficiently than that provided by trigram), I don't think there is any way you can do that without writing some C code to glue the pieces together.  I think you would then work on the array type directly, so wouldn't need the ANY, and so wouldn't benefit from the reverse_like operator at all.


You didn't specify which SQL version you are on but you could use a Server Side trace or an Extended events session to capture the traffic that is sent to the SQL server (if you can live with the performance impact it might have) and replay those captured traces on a test environment to see what the daily growth would be for the day where you ran your traces.

See here how to replay a profiler trace or distributed replay for info on how to run an XEvents replay.

That would allow you to rerun the captured workload on a test system and see for real what is happening with your log file.


Here is the proof.

(⇐) Suppose T1 ∩ T2 → T1 ∈ F+. Let r a valid instance of R⟨T, F⟩, and s = π T1(r ) ⨝ π T2(r ). If t ∈ s, then we need to show that t ∈ r.
By definition of s, we have two tuples u and v in r such that u [T1] = t [T1], v [T2] = t [T2] and u [T1 ∩ T2] = v [T1 ∩ T2] = t [T1 ∩ T2]. Since T1 ∩ T2 → T1 ∈ F+, then u [T1] = v [T1] and so t = v.

The case T1 ∩ T2 → T2 ∈ F+ can be proved in the same way.

(⇒) Suppose that, for each valid instance r of R⟨T,F⟩, r = π T1(r ) ⨝ π T2(r ); we need to show that T1 ∩ T2 → T1 ∈ F+ or T1 ∩ T2 → T2 ∈ F+.

Using reductio ad absurdum, we suppose that none of the two functional dependencies is implied by F.
Let W = (T1 ∩ T2)+, Y1 = T1 − W and Y2 = T2 − W. Y1 and Y2 are not empty by hypothesis, and W, Y1 and Y2 are a partition of T.
For each Ai ∈ T, 1 ≤ i ≤ k, we consider two values ai, ai′ ∈ dom(Ai), such that ai ≠ ai′. Let’s build a relation r with attributes WY1Y2 constituted by the two tuples:

e1[Ai] = ai, 1 ≤ i ≤ k

e2[Ai] =  ai if Ai ∈ W; ai′  if Ai ∈ Y1Y2

r satisfies each dependency V → Z ∈ F. In fact, if V ⊈ W, then e1[V] ≠ e2[V], and r obviously satisfies the dependency. If V ⊆ W, then (T1 ∩ T2) → V, so (T1 ∩ T2) → Z for transitivity, from which Z ⊆ W, and so e1[Z] = e2[Z], an this implies that r satifies the dependency. Moreover, since Y1 and Y2 are not empty, π T1 (r ) and π T2 (r ) contain two tuples and their natural join contains four tuples, more than those of r:

π T1 (r ) ⨝ π T2 (r )

contradicting the hypothesis.


OK, so I'm just going to go off general best practices here and give you some advice on what I would do and what I have seen implemented in the past. I am also going to assume that you have sufficient security protocols in place to protect your public-facing website.

Firstly, be willing to lose everything in the public database if you get hacked (obviously back it up regularly but don't be surprised if it gets taken down).

Secondly, the database that runs your website needs be isolated. Everything that you need to run it needs to be independent and generally should not be linked via a library directly to a private database. If there are components in the private database that the public website requires then you have two options:

Duplicate these components so that they exist in both databases. (Preferred)
Create a "shared" database that contains only these components.

Once you have your public database isolated you immediately reduce the amount risk that you need to mitigate. If you need information from the public database, or need to get information into your public database from your private database then you could set up replication or some other sort of schedule to perform this task.

If you cannot separate your databases then you will need to be absolutely militant about what permissions are granted to your public facing website/data library. Be prepared to document a lot!

If you create roles, then you need to ensure that each role has the minimum permission required to complete the task assigned to it. For instance, your class library that is shared between the two sites, give it the least permission possible that enables it to run.

Any user accounts created for the public website should have access to the public database and nothing more. If the public website has direct access to your private database then it should be considered public as well even if that was not your intention.

Again, your windows application, should not call directly into your public facing database. This is to reduce the surface area of attack and also to ensure that someone does not unintentionally do something that takes your public website down (like deleting all content).

So to summarize:

Public website should only connect to public database and nothing that is deemed internal or private.
Private website and applications should not connect to the public database.
Shared components/tables should be present in both databases and replicated between them.
Permissions should be the absolute minimum to complete the task they are performing, nothing more nothing less.
Don't use code components in your public website that can access and amend private data.
Be prepared to lose everything.
Never, ever give the public website SA or equivalent. Everything it needs to do should be explicitly granted to it.

If you isolate your public database then assigning permissions to your internal applications become trivial and easy to do.


How can I optimize this query and support multiple SKUs?

I have refactored the query to use left joins and IF statement checks for the joins so that you do not have to use nested sub-queries. The group by for sku and site will give the totals by site if needed, but this can be changed to suit your needs.

I am not sure of how the responses_authnets table joins to the orderlineitems for the siteid so this may need additional clarification

SELECT (pi.available - SUM(IF(ISNULL(pl.quantity), 0, pl.quantity)) /* Sums up only those quantities which are not null through the left join */
- SUM (IF(ISNULL(ol.quantity), 0, ol.quantity))) as free, //* SUM up only quantities in the order line items which are not null */
pi.available as total,
pi.sku,
pi.on_hold
FROM product_inventories pi
LEFT JOIN product_locks pl ON pl.sku = pi.sku /* Left join since not all products in the inventory may have locks */
LEFT JOIN orderlineitems ol ON ol.sku = pi.sku
LEFT JOIN responses_authnetsid ra ON ra.id = ol.response_id /* Not sure how the responses come up here, so this join is not clear*
GROUP BY pi.sku, pi.salesite_id


Securely deleting data from SQL Server logs

The SQL Server log contains all the data that was written to the database (in some form). So, you are right to be concerned about it.

The log is a ring buffer made up of virtual log files (VLF). Once a VLF is not needed anymore, it can be reused. However it will not be reused until the "write head" comes around to that VLF again.

The only difference between simple and full (with a few small exceptions) recovery model is how quickly a VLF is "not needed anymore". If you use full recovery mode, a VLF is "needed" at least until a log backup has been taken. (A full backup never clears the "needed" flag of a VLF.)

The only way to force a VLF to be overwritten is to first make sure that it is not needed anymore and then pump enough transactions into the database that the VLF gets reused. That however is probably not acceptable to you.

Another way to force the data out is to shrink the log file. If you can take downtime, follow this process:

Set the database to READ_ONLY
Run the CHECKPOINT command.
take a log backup (if in full recovery)
make sure other log-readers (replication, CDC, ...) have caught up
set the database to READ_WRITE
create a new log file
empty the original file migrating the data to the new (DBCC SHRINKFILE, there should not be any data left to migrate at this point, but you still need to execute this step.)
drop the old log file
take another log backup (if in full recovery)

You still need to test this, but this should get you to where you need to be.

If you can't take downtime, follow the same process without setting the database to READ_ONLY. However, you run the risk not to be able to have a complete clean cutover, but if you do this regularly you still should be OK.

Note, as some of the commenters have mentioned, a delete does not actually remove the data from the MDF files either. However, that can be dealt with in a similar fashion:

rebuild all indexes on the offending table(s) (or better all tables)
create a new file
empty the old file, migrating the data to the new file using DBCC SHRINKFILE
drop the old file

Step 1 is necessary as the file migration does not change page contents, so single deleted rows might survive the move. Rebuilding the indexes should use new pages.

Additional notes:

The log cleanup has to happen after the data file cleanup as there is a chance that the data file cleanup moves some of the data into the log file.

Index rebuild operations and also a lot of data access operations utilize tempdb, so you have to shred that too. Tempdb is recreated on server restart, so you can stop the SQL Server service, delete the tempdb files and then start the service again.

Finally, this all causes a lot of blocks (~ database pages) to be unlinked from existing database files. This unlinking (due to file shrinkage or the deletion of files) does not force those blocks to be overwritten. So it is recommended to create the new files in the above processes on a separate disk and afterwards securely wipe the original disk.


Sure (and let's assume that a while loop and a cursor are basically the same thing for the purposes of the discussion, throwing out any arguments that using a while loop avoids a cursor, which is simply not true except in the most pedantic way):

You need to execute a stored procedure (say, a vendor stored procedure that you aren't allowed to touch, and that you must use to stay in compliance/support). For every row in some set.
You need to send a distinct e-mail to each of a set of users (as opposed to just building a massive To/CC list - not only because that doesn't scale but also because you may need to personalize the e-mail for each user).

There are also some metadata operations where you might want to use a cursor, e.g. to disable and re-enable all foreign key constraints on every table. Personally, I've always preferred building a string in a query that I can execute at once, but you may not want to do this for various reasons. As an example, I didn't use a single cursor in this answer, though I could have and it would have been simpler to write. (Also note that solutions like that one - and many seemingly set-based queries - still technically use a cursor under the covers - you just don't actually write DECLARE CURSOR...)

It can also be quite complex to build a set-based query that does exactly what a cursor does, and often isn't worth the time investment or the impact to future maintainability due to query complexity, when the cursor is actually much more straightforward (even if it is less efficient). And for efficiency, if you're comparing a cursor solution to a set-based solution, make sure you use the most efficient cursor options - these can make a big difference.

For normal queries you're building from scratch and without any of these constraints, I don't know that there is a situation where you have to use a cursor, especially as SQL Server continues to add windowing functions and other features that assist in more advanced set-based programming that used to typically require cursors.

If you're writing a cursor, you should be asking yourself, "Is there a set-based way to do this?" Depending on the purpose of the code, how much it will be re-used, whether it is a one-off task, etc. the answer is almost always, unilaterally, "Yes!" But a perfectly valid answer to that question might also be, "Yes, but who cares?"

Would an example where a cursor is actually the best performing, supported, documented and guaranteed solution to a problem suffice? One such example is calculating running totals in SQL Server < 2012. I blogged about it here - and actually demonstrated that in my use case a cursor was faster than all the other common approaches, even the often recommended (but not guaranteed, documented or supported) "quirky update" method. (Only if the new 2012 LAG feature is not an option, in which case, it runs circles around everything else).


Best practices with large amount of data

There are 3 common contemporary database designs:

1) Most database design is based on normalization, so have a look at that.

2) When that has performance problems denormalization is a common route.

3) When vast amounts of data are projected database design has moved away from these techniques (1 and 2 are two sides of the same coin) and new technologies have been used, for example have a look at noSQL.

Unless you are dealing with seriously vast amounts of data you'll probably want to start with 1) and possibly look at 2) if and when you have performance issues.

"Everything in a single table" is generally a bad idea as that table becomes huge, with a great deal of contention on it - every part of the application using it constantly. This would lead to deadlocks, for example, making queries slow.


Postgres trigram match acting strange for specific characters

SELECT show_trgm('$ Hello');

            show_trgm
---------------------------------
 {"  h"," he",ell,hel,llo,"lo "}
(1 row)

SELECT show_trgm('- Hello');

            show_trgm
---------------------------------
 {"  h"," he",ell,hel,llo,"lo "}
(1 row)

All non-alphanumeric characters are stripped from a string before the trigrams are calculated.

That's why the strings have similarity 1.

You cannot use trigram indexes for searching for symbols.

The documentation has it in its first sentence (emphasis mine):

  The pg_trgm module provides functions and operators for determining the similarity of alphanumeric text based on trigram matching


Best way to divide CPU along all sql instances on 1 server

You can configure the processor affinity used by an instance of SQL Server by right clicking the instance and choosing Properties -> Processors:

Processor Affinity
Assigns processors to specific threads to eliminating processor reloads and reduce thread migration across processors. For more information, see affinity mask Server Configuration Option.

This will enable you to assign specific processors per instance. Getting this right though requires constant attention and tuning so generally I don't advise it unless you really know what you are doing.
If you do decide to proceed, then I highly recommend reading about the affinity mask Server Configuration Option before you attempt it and also you can read a quick tutorial on TechNet and some basic guidelines can be found on Microsoft Support.


Loading data in mysql using LOAD DATA INFILE, replication safe?

Another answer has already been accepted, but I disagree with that answer, because your question first requires an understanding of the meaning of the term "unsafe" and the phrase "considered unsafe" when it is used related to MySQL replication.

  When speaking of the “safeness” of a statement in MySQL Replication, we are referring to whether a statement and its effects can be replicated correctly using statement-based format.
  http://dev.mysql.com/doc/refman/5.5/en/replication-rbr-safe-unsafe.html (emphasis added)

There is no such thing as an "unsafe" statement when you are using MIXED or ROW-based replication, because the entire premise of "unsafe" is related to how statement-based logging and replication work.

Something "considered unsafe" doesn't mean it shouldn't be done -- it means the optimizer will consider it unsafe to log in statement-based mode and therefore that it will be automatically replicated using the row-based replication logging format if row-based logging is available -- that is, if you are not using binlog_format = STATEMENT -- which you should not be doing unless you have a specific reason to use it.  If you are not aware of a specific reason to use it, then you most likely do not have one, and you should used MIXED logging, so that the optimizer can choose how to replicate each statement.

http://dev.mysql.com/doc/refman/5.5/en/replication-formats.html


select rows where column contains same data in more than one record

HAVING is a great aggregate filter. (http://dev.mysql.com/doc/refman/5.6/en/group-by-extensions.html) For example, select the article_titles with more than on occurrence:

SELECT count(*), article_title
FROM articles
GROUP BY article_title
HAVING COUNT(*) > 1;

Adding columns to the SELECT and GROUP BY clauses allow you to locate duplicates based on a composite key of multiple columns.


MySQL large databases, unique index effect on efficiency?

For write operations (e.g. INSERT and DELETE) having a composite index will slow things down. However, MySQL can handle tables with MANY more rows than that. If you need to keep the row count to 1k to maintain performance then I would suggest something is very wrong somewhere. Regardless, I would strongly suggest not breaking the data up in this way, you're just giving yourself a maintenance headache further down the line...

What datatypes are the columns that make up the composite index?


Read massive flat file into relational database

Try using LOAD DATA INFILE into an ISAM table with few or no indexes. ISAM should reduce memory consumption and get all data in there the fastest possible way. I have never run out of memory when doing this with larger data sets than yours, on pretty old servers with alot less memory than is used today.

Once the data is imported, use ALTER TABLE to add required indexes, in essence everything needed to uniquely identify a business (name, address, state etc). This may take some time.

Create another ISAM table having an auto increment primary key as well as all columns from the first table which are required to uniquely identify a business (e.g the ones that were indexed, but nothing related to employees). Add a UNIQUE key which includes ALL all columns in the table except the primary key.

Use REPLACE INTO to fill the second table with data from the first, and you will in the process generate unique numeric keys for all the businesses. Add the key column to the first table and UPDATE it using data from the second, which then can be dropped afterwards.

From there you should have a table where all businesses have unique, numeric and indexed keys and should easily be able to split the data into the (InnoDb) tables you require.


Given that this is an internal system table that you can only access when connecting via the DAC, no, there is no documentation on this. What are you using this table for?

I found several instances here where rscolid was not identical to hbcolid. For example:

SELECT rsid, rscolid, hbcolid
  FROM sys.sysrscols
  -- WHERE rscolid <> hbcolid
  WHERE rsid = 72057594038714368;

rsid                 rscolid   hbcolid
-----------------    -------   -------
72057594038714368    4         2

Where does this row come from?

SELECT OBJECT_NAME(object_id)
  FROM sys.partitions
  WHERE partition_id = 72057594038714368;

-------------------------
queue_messages_1003150619

So what indexes might be involved?

SELECT index_id, name
  FROM sys.indexes
  WHERE object_id = OBJECT_ID('sys.queue_messages_1003150619');

index_id   name
--------   ---------------------
1          queue_clustered_index
2          queue_secondary_index

So going on a guess, I ran this query:

SELECT
  ic1.index_id,
  c.name,
  ic1.column_id, -- column id in the table
  ic1.key_ordinal -- column order in the index
FROM sys.all_columns AS c
INNER JOIN sys.index_columns AS ic1
ON c.[object_id] = ic1.object_id
AND c.column_id = ic1.column_id
AND ic1.column_id <> ic1.key_ordinal
WHERE c.object_id = OBJECT_ID('sys.queue_messages_1003150619');

One of the rows here:

index_id   name                   column_id   key_ordinal
--------   ---------------------  ---------   -----------
1          conversation_group_id  4           2

So, without doing much more investigation, my initial guess is that rscolid is the column id within the base table, and hbcolid is the ordinal position of that column in the index. This holds true for 5 of the columns in the clustered index of this queue table - they all have identical mismatches in sys.sysrscols, and also for one of the columns in the non-clustered index.

But again, whether I'm right or wrong, I'm not sure what you could possibly do with that information: what are you using this table for?