shrink after archiving, is this that bad too?

What you are seeing on net is mostly a copied advise where people actually want to say that "please don't make shrinking data file or log file a daily routine operation". Had it been so bad Microsoft would have removed it but it is still there and even most experienced DBA's and developer use it but they are aware about the after affects so they know what to do after. Only shrinking can give you back free space

Yes of course you can shrink data and log file because you want to reclaim space and if reclaiming space is very much required. If you see Paul's article he has given a method like

The method I like to recommend is as follows:

Create a new filegroup
Move all affected tables and indexes into the new filegroup using the CREATE INDEX … WITH (DROP_EXISTING = ON) ON syntax, to move the tables and remove fragmentation from them at the same time
Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)

You can use it if it suits you. Since you are aware shrinking is bad activity and would do it once in a year may be twice this is actually not bad. Shrinking do causes logical fragmentation so dont forget to rebuild indexes after shrinking.

  Backup script does save a copy of the backup to a different location. Moving 20GB or 5Gb does make a difference

Backup only includes data and few amount of transaction log(if required) to bring back database in consistent state after restore. So again backup would be around 5 G not 20G but yes when you restore it back it would take 20 G space

PS: I am not supporter of shrinking the database or database files but in extreme scenario and knowing the repercussions you can do it.


Assuming that ID is the primary key of the table (or has a unique constraint), you can use this variant:

SELECT t.*
FROM   SameLogTable AS t
  JOIN ( SELECT id
         FROM   SameLogTable
         WHERE  <SameCondition>
       ) AS c
       ON t.id = c.id -1
       OR t.id = c.id
       OR t.id = c.id +1 ;

Note: if the id have gaps, which is very probable, the above will not work as expected and neither will your original query. ROW_NUMBER() can help you for that:

; WITH cte AS
( SELECT t.*,
         rn = ROW_NUMBER() OVER (ORDER BY t.id)
  FROM   SameLogTable AS t
)
SELECT t.*
FROM   cte AS t
  JOIN ( SELECT rn
         FROM   cte
         WHERE  <SameCondition>
       ) AS c
       ON t.rn = c.rn - 1
       OR t.rn = c.rn
       OR t.rn = c.rn + 1 ;


Your covered query example is hitting a special case optimisation: an equality query on the _id index.

Since the _id index is always both required and unique, equality queries on _id  choose this index and bypass some of the usual stages of query planning and execution. This is a fast path for common queries looking up documents by their primary key.

When this optimisation happens your explain output will include an IDHACK input stage and the  detailed executionStatswill  not show any rejectedPlans (since the query planner is bypassed for this path). The IDHACK stage always examines the document so won't be a covered query (as at MongoDB 4.2).

The IDHACK optimisation is specific to equality queries, so if you want a workaround for a covered query on _id you could use a different expression like $in:

db.family.find({ "_id" : { $in: [ObjectId("5c87890b3adcde7da566f0d7")]}},{_id:1}).
     explain("executionStats")

A $in query goes through the normal query planning path so other candidate plans will be considered and the explain results will include an IXSCAN stage instead of IDHACK. This query can also be covered (only index keys examined).


Generally a many-to-many relationship is done with a junction table, like so:

Student (id, name, date of birth, department)

Course (id, name)

Student_Course (student_id, course_id)

You can either create an artificial key on the Student_Course table or create a primary key or unique index with both fields.  student_id is a foreign key to student.id and course_id is a foreign key to course.id.

Note that the junction table need not be exactly two fields.  You can include fields such as date_added or date_dropped so you could see when students have added and dropped courses.


Why are graph databases bad at CRUD operations?

I think the software is too complicated to give simple yes/ no answers to this question. It also depends on how you define CRUD.

Let's take the "place order" function as an example. When seen from a business point-of-view this is a single atomic action. In a normalized relational database it will likely touch many tables, however. There will be an insert for the order; perhaps updates to customer or shopping cart tables; reads of many related tables to ensure referential correctness. All these statements will (should?) be wrapped in a database transaction. So a single business action becomes many database statements wrapped in a single database transaction.

Were a document database used instead of a relational one likely there would have been a single write. A graph DBMS would sit between these two, being more or less normalized depending on the particular application's architecture.

Looking just at on-disk structures the discussion is more nuanced, too. Inserting a single row to a table seems simple. If there are indexes then likely there will be write amplification. If there is an index split then more so. If the row holds BLOBs these may be stored in separate data structures, or different files. There could be cascading actions through triggers or materialized views. Compression and encryption may incur large CPU consumption.

There are many on-disk structures to support graph-like processing. In one nodes are the basic object and edges become properties of nodes. Edges are stored as pointer-list in the nodes at either end of the edge. So, to create a node would be one write. To create an edge will be two. To insert a row with a foreign key constraint (the nearest equivalent to an edge) will be a read and a write.

On the read side there are swings-and-roundabouts, too. Counting child rows by parent will be a two-table join in the relational world. If edge pointers are stored as a list within the node it's a single node-list scan. Doing is-that-connected-to-this queries (friends of people who know someone that lives in the same city that person can get to by rail within one hour) is easy in graph DBs; it's monstrous in the relational world. For the "R" bit of CRUD, for transitive queries, graph wins hands-down every time.

In conclusion, I question the premise of the title. If milliseconds are precious in your context then test, at scale, on production-equivalent hardware.


So the answer ended up being bad raid controller.  I had a Dell tech come out and replace the raid controller and drives.  Then there was a setting in the Dell configuration to allow the dual raid controllers to work independently.


Trying to design database for eSports Teams - Issue with dependencies

IMHO This is not good design.
You have teamname in league table and teams table.
What also is not good to have mixed conventions (plural and singular for table names)

I would note that you should also put unique index on this fields:
toplane, jungler, midlane, adc, support.

create unique index teamroster_unique_idx on (toplane, jungler, midlane, adc, support);

This is valid for PostgreSQL, check your RDBMS documentation.

And also CHECK constraint that every value is different, you don't want to have all positions with same players. ;)

Unique index is to have unique combinations (no duplicates),
and check constraint to have all different values.

PS. You created logical design well, but you need to translate it to physical design
(create that in your RDBMS)


According to the official Tokutek documentation, TokuDB is only available for 64-bit Linux.


How do I pass a variable into -job on Copy-DbaAgentJob?

No need to reinvent the wheel with the linked server query. dbatools also provides the Get-DbaAgentJob function to enumerate jobs on both agents. As well, the $Job parameter accepts an array as input, meaning you don't need the for-loop. Assuming the job name is the keying attribute as in your sample, you could try something like the following.

$sourceJobs = Get-DbaAgentJob -SqlInstance '[Source\Instance]'
$destJobs   = Get-DbaAgentJob -SqlInstance '[Dest\Instance]'

$missingJobs = $sourceJobs | Where {
    $_.Name -notin $destJobs.Name
}

Copy-DbaAgentJob `
    -Source '[Source\Instance]' `
    -Destination '[Dest\Instance]' `
    -Job $missingJobs

Although strictly speaking, Copy-DbaAgentJob already checks for duplicates, so you could slim it down even further by just omitting the -Job parameter entirely and just run something like...

Copy-DbaAgentJob `
    -Source '[Source\Instance]' `
    -Destination '[Dest\Instance]' `

...which will show you which jobs were skipped because they already exist.


How do I form an SQL query to show entire column of dates with modified values?

Asuming the column has a datetime type, simply:

SELECT PurchaseDate + INTERVAL 5 DAY FROM

If it was a string type, you would have to parse it first.


There is an Oracle doc that you might want to look at, Doc ID 1681266.1. You want to make sure that you are using 4K block sizes for storage. If you are moving to a new server and you are running at least Redhat 6.5 or Windows 2012, than that should not be a problem. Using a 4K physical block size will give you better performance.


How do I automatically enable or disable report jobs at AOAG failover?

As it turns out, AOAG event ID #35266 quite regularly occurs 2 or 3 times as a node is coming up or down. So long as what you do when a 35266 is detected isn't toggle-like in nature, you should be OK. It works fine in my case because what I do when I detect a 35266 is to (depending on the text in the event) either enable or disable specific jobs. So if I enable my job 2 or 3 on the primary node in rapid succession, no big deal

Here is what I ended up with.

Create SQl Server Agent job 'Enable Or Disable Rpt Jobs'

Accept defaults except as follows

Name: Enable Or Disable Rpt Jobs
Owner: account that has permissions to run T-SQL to enable/disable SQL Server Agent jobs
Enabled: Checked

Create job step:

Step name: enable_or_disable
Type: Transact-SQL script
Database: master

Command:

DECLARE @DB nvarchar(25)
DECLARE @MSG nvarchar(200)
SET @DB = '{DB name that provides report data}'

If sys.fn_hadr_is_primary_replica ( @DB ) = 1
BEGIN
    EXEC msdb.dbo.sp_update_job @job_name='{job name}',@enabled = 1
    .
    .
    .
    EXEC msdb.dbo.sp_update_job @job_name='{job name}',@enabled = 1
    SET @MSG = '"' + @DB + '" is PRIMARY. Enabled report jobs'
    PRINT @MSG
END
ELSE
BEGIN
    EXEC msdb.dbo.sp_update_job @job_name='{job name}',@enabled = 0
        .
        .
        .
    EXEC msdb.dbo.sp_update_job @job_name='{job name}',@enabled = 1
    SET @MSG = '"' + @DB + '" is not PRIMARY. Disabled report jobs'
    PRINT @MSG
END

Schedule:

Enabled: Yes
Schedule Type: Start automatically when SQL Server Agent Starts

Create SQl Server Agent Alert 'Alert Disable Rpts on 35266'

Accept defaults except as follows

Name: Alert Disable Rpts on 35266
Database name:
Error number: 35266
Raise alert when message contains: checked
Message Text: connection with primary database established for secondary database '{DB name that provides report data}'

  Example: connection with primary database established for secondary
  database 'DB_NAME'

Response:

Execute job: checked
Select the job: Enable Or Disable Rpt Jobs

Create SQl Server Agent Alert 'Alert Enable Rpts on 35266'

Accept defaults except as follows

Name: Alert Enable Rpts on 35266
Database name:
Error number: 35266
Raise alert when message contains: checked
Message Text: connection with secondary database established for primary database '{DB name that provides report data}'

  Example: connection with secondary database established for primary
  database 'DB_NAME'

Response:

Execute job: checked
Select the job: Enable Or Disable Rpt Jobs


It is an SSIS 2008 package running on a SQL Server 2008 instance. My package security is DontSaveSensitive.

You have to save the package with EncryptAllWithPassword using a password.

Then schedule it using SQL Agent Job as below :

"DriveLetter\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe" /FILE "SSIS_Package_Location\bin\Package.dtsx" /DECRYPT "PasswordStrong007" /CONFIGFILE "Location_to_Config_file_If_any\Config.dtsConfig" /CHECKPOINTING OFF /REPORTING E

Now the user account that you use should have permissions on the database server instance.

You can check the permissions using below T-SQL :

SELECT [Login Type] = CASE sp.type
        WHEN 'u'
            THEN 'WINDOWS Login'
        WHEN 's'
            THEN 'SQL Login'
        WHEN 'g'
            THEN 'GRP'
        END
    ,convert(CHAR(45), sp.NAME) AS srvLogin
    ,convert(CHAR(45), sp2.NAME) AS srvRole
    ,convert(CHAR(25), dbp.NAME) AS dbUser
    ,convert(CHAR(25), dbp2.NAME) AS dbRole
FROM sys.server_principals AS sp
JOIN sys.database_principals AS dbp ON sp.sid = dbp.sid
JOIN sys.database_role_members AS dbrm ON dbp.principal_Id = dbrm.member_principal_Id
JOIN sys.database_principals AS dbp2 ON dbrm.role_principal_id = dbp2.principal_id
LEFT JOIN sys.server_role_members AS srm ON sp.principal_id = srm.member_principal_id
LEFT JOIN sys.server_principals AS sp2 ON srm.role_principal_id = sp2.principal_id


I have used this script from David  Rueter, its pretty good have changelog script used and login name also.

https://github.com/DavidRueter/SQLVer/
http://www.sqlservercentral.com/articles/Version+Control+Systems+(VCS)/119029/


ERROR 1290 MYSQL secure-file-priv option

/tmp is, by design, cleared on reboot.  This cleans up certain trash that might be left behind by a crash.  And it is a security feature.
LOAD FILE INFILE is a security hole waiting for hackers to march in.  5.7 (finally) got serious about it, and partially plugged it by having --secure-file-priv.


sysadmin cannot drop login when ddl audit trigger enabled

After a considerable amount of testing, I finally discovered the reason behind this error.  The client connection explicitly set ANSI_WARNINGS and CONCAT_NULL_YIELDS_NULL OFF. XML data operations, such as @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), require both to be ON.  I had attempted to override these within the trigger, but I may have placed them wrong.  The final code below works, even with the explicit SET options in the connections from Great Plains:

CREATE TRIGGER [ddl_log]
ON ALL SERVER
FOR DDL_DATABASE_LEVEL_EVENTS, DDL_SERVER_LEVEL_EVENTS
AS
BEGIN
SET NOCOUNT ON;
SET ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL ON;

DECLARE @data XML
SET @data = EVENTDATA()

EXECUTE AS LOGIN='<dummy login>'
INSERT admin.dbo.ddl_audit (PostTime, DB_User, [Event], [TSQL], Host, DatabaseName)
VALUES (
   GETDATE(),
   CONVERT(NVARCHAR(100), ORIGINAL_LOGIN()),
   @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),
   CONVERT(NVARCHAR(100), HOST_NAME()),
   @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(100)')
   ) ;
REVERT
END

As an alternative, I also could have simply inserted EVENTDATA() as an XML LOB into a table, rather than parsing it out into columns.  Because I would not be manipulating the XML, the SET options do not matter.  Then I would just build an XML index for querying performance, and construct a view to use for audit log reporting that parses the XML in the view definition, in the same manner as I am doing above in my INSERT statement.

Thanks to Max for pointing me in a different research direction, and @AaronBertrand on #sqlhelp who helped me with correct SET options within the body of the trigger.


whats the use of an autoincrement primary column?

There are a few advantages to using an auto-increment column as a primary key.

Unlikely to need to change: If your primary key is business data that's meaningful to human beings, you may be asked to change that at some point. Examples I have seen would include usernames based on a combination of company branch number and person's initials (want to change them if the person moves to a different branch); and company region names (may need to change if company reorganizes regions). Updating a user's username ultimately touched about 20 columns across about a dozen tables, and almost always caused blocking for long enough periods that users noticed.
Less fragmentation on table: Assuming that your primary key is also the clustered index for your table, records will also be inserted at the end of the table. since you won't be inserting a new record in between existing records, you won't be splitting pages in the middle of your data, which leads to fragmentation.
Smaller indexes: In some DBs, at least, the clustered index value is used in any other indexes to locate a row in the table. If your primary key is a varchar value that's 8 characters long on average, that will require twice as much space to store as a 4-byte integer value (4 times as much, if nvarchar).

For me, personally, the first reason was the strongest one, and the one that made me start including an autoincrement primary key in basically every table. Human-meaningful values will always be at risk of being changed by order of the business; putting those into a position where making that sort of changes could take hours and cause millions of other records to get changed is not acceptable, in my opinion.

UPDATE: As joanolo points out there are certain codes that may be meaningful to human beings, but which are part of established standards, and should (at least in theory) not change because of that. Examples would include an ISO 3166 country code, an ISO 4217 currency code, an ISO 639 language code, or a UN ISIC activity code. He notes these are usually a reasonable size (char(4) or less), so they wouldn't cause overly large indexes, and they are generally static, and thus fragmentation wouldn't be a concern. So, it would make sense to use these codes as primary keys in tables based on this sort of information. It's an excellent point, and I agree.


Pre-sorting postgres table instead of running CLUSTER

Aside from the index you mentioned, and it being the default CLUSTER sort order now (Postgres remembers), both tables will turn out identical. Same order, no bloat.

Parallelism could potentially change this. But for now, writing queries are not parallelized. The manual:

  Even when it is in general possible for parallel query plans to be
  generated, the planner will not generate them for a given query if any
  of the following are true:

  The query writes any data or locks any database rows. If a query contains a data-modifying operation either at the top level or within
  a CTE, no parallel plans for that query will be generated. As an
  exception, the commands CREATE TABLE ... AS, SELECT INTO, and CREATE MATERIALIZED VIEW
  which create a new table and populate it can use a parallel plan.

So neither INSERT nor COPY use parallel queries and a single process writes rows as received, i.e. in the order of the CSV file - even if there are no formal guarantees. Tables are heaps in theory and Postgres (like most RDBMS) generally treats the physical order of rows as implementation detail. But in the current implementation order is preserved, and that won't change without good reason. Parallelism would be a good reason, but that's hard to implement for writing queries.

Related:

Does (or can) the PostgreSQL COPY TO command guarantee a particular row order?
Does Postgres preserve insertion order of records?


Does my entity-relationship diagram reflect the relevant business rules?

First - I agree with S4V1N completely on the Category-Business link - you need a table in between, as each business can have many categories, and each category could be tied to many businesses.

However, as the rules are currently stated, I don't think you need a table between City and Business. I think you've got City laid out wrong.

Currently, each business must be in one and only one city, and each city can be home to multiple businesses. For that, the City table should not have a B_ID column, as a city is tied to no one business. (I also see no reason for it to have a U_ID column, as no relationship between User and City has been established. I suspect a cut-and-paste issue.) Business, however, should have a CI_ID for the city it's in.

Also - the way I read the diagram, it looks like each user must have at least one Comment, Rating, and Checkin. The rules explicitly indicate "one or more" checkins per user, so that appears correct. However, it doesn't look to me like the user is required to have any comments or ratings - so shouldn't those two be linked with the "0 or more" connector?


Can Availability Groups replace transactional replication?

Swiped from my answer on StackOverflow, only to prevent others from spending effort on the same type of answer.

Personally I think this will work a lot better than transactional replication, though I haven't done any formal comparisons of the two in a true migration scenario. I know that with the amount of troubles folks have with transactional replication, compared to even standard mirroring (and this is an upgrade of that), you are certainly bound to have fewer problems.

The biggest boon is that the secondary can be marked as read only - so you can run all the reporting off of it that you want, and it won't affect the mirroring at all. You just need beefier tempdb (since it essentially uses rcsi to do this).

Of course you do need to be aware that both sides of the AG need to be fully licensed in order to use the replica for read-only operations. And both sides need to be running on specific versions of Windows (Enterprise or better on 2008 R2 and lower; Standard or better on 2012 and above) because they require failover clustering - the SQL instances in AGs don't need to be clustered, but they need to be sitting on top of that infrastructure in the OS.


Why does replication with SSL fail on my MySQL database in AWS RDS?

You may want to check apparmor/selinux configuration.

Another option is to move your certs to a location already allowed (e.g. /var/lib/mysql).


Sorry for the previous useless response - skipped over the fact that you are setting up a mirror without a domain. You will need to use certificates for this.

I found an article that might help you with that part. Check it out: http://mssqlnet.wordpress.com/2012/06/20/microsoft-sql-server-database-mirroring-in-a-workgroup-without-domain-with-certificate/


Rights needed to see database files in Properties dialog?

If I'm understanding correctly I believe you need to grant VIEW DEFINITION to your contractor's account. You can do this only on the DBs you want the contractor to access.


The comment from Barmar is correct.

You do not have enough memory in the server for the InnoDB Buffer Pool.

Since you got the message

140107 0:09:29 InnoDB: Initializing buffer pool, size = 64.0M

followed by

140107 0:09:29 InnoDB: Fatal error: cannot allocate memory for the buffer pool

you should lower innodb_buffer_pool_size below 64M in /etc/my.cnf and restart mysql.

SUGGESTION: If it is withn your budget, you should setup Apache and PHP on a separate server. Connection all webserver requests to the DB thereafter.


Recommended postgres parameters for index creation with 244gb RAM and 32 CPUs?

This is on AWS Aurora, using postgres-10.6, in case that makes a difference.

Yes, this matters quite a lot.  Native parallel btree index builds were introduced in v11, so your "max_parallel_workers" setting won't matter for index builds under v10.

Unless you upgrade, you will have to parallelize them yourself by opening multiple sessions in parallel and building one index in each one.  You will probably want to lower "maintenance_work_mem" as well if you have parallel processes (either of the manual or the v11 variety) as each one can claim that much memory.


As @Rick suggested in the question comments. We need to grant all the permissions, procName expects. In my case, procName Creates and Deletes temporary tables. So, granting CREATE TEMPORARY TABLES permissions on dbName.* did the trick


SQL Server Tuning a table for massive inserts

One solution I used was to thread out writes to multiple staging tables, then collect the data in batches. We put the staging tables on their own filegroups to try and segregate the I/O as much as possible, though we did end up with two tables on each available LUN, as opposed to each table getting its own dedicated I/O.

Since you said order of writes doesn't matter, this may be viable, but I don't know if 20 seconds will be sufficient to make this work and still not encounter a bunch of blocking.

So let's say we have a base table like this, which is where everyone reads from (and is currently writing to):

CREATE TABLE dbo.StockData
(
  StockID INT,
  ...other info...
);

Then we have staging tables like this, where we're going to write instead:

CREATE TABLE staging.StockData0
(
  StockID INT,
  ...other info...
);

CREATE TABLE staging.StockData1
(
  StockID INT,
  ...other info...
);

... up to StockData9

*Don't bother mimicking all of the non-clustered indexes, constraints etc. on these staging tables,

Then assuming you have singleton inserts via a stored procedure, change the stored procedure to use dynamic SQL and insert into one of the 10 tables based on the StockID value:

DECLARE @sql NVARCHAR(MAX) = N'INSERT staging.StockData'
  + CONVERT(CHAR(1), @StockID % 10 + '(StockID, cols...)
    SELECT @StockID, @params...;';

EXEC sp_executesql @sql, N'@StockID INT, @params...', @StockID, @params...;

Then have a background job that performs a very quick, metadata operation moving these tables into a dummy schema - this minimizes the amount of time that is spent blocking users' ability to write while you move this data into the real table (which you can now control much better than sporadic writes from all over the place). So first create a second copy of the tables in a different schema:

CREATE SCHEMA shadow  AUTHORIZATION dbo;
CREATE SCHEMA holding AUTHORIZATION dbo;
GO

CREATE TABLE shadow.StockData0
(
  StockID INT,
  ...other info...
);

-- repeat for 1-9

Now the job would do this:

TRUNCATE TABLE shadow.StockData0;

BEGIN TRANSACTION;

  ALTER SCHEMA holding TRANSFER staging.StockData0;
  ALTER SCHEMA staging TRANSFER shadow.StockData0;

COMMIT TRANSACTION;

ALTER SCHEMA shadow TRANSFER holding.StockData0;

-- repeat for 1-9 (I actually used dynamic SQL in a loop
-- so I wouldn't have to repeat myself ten times...

I blogged about this part of it here and here.

The job would then go on to insert batches into the primary table, with a delay in between, to allow some reads to happen.

BEGIN TRANSACTION;

  INSERT dbo.StockData(StockID, ...cols...)
    SELECT StockID, ...cols...
    FROM holding.StockData0;

COMMIT TRANSACTION;

WAITFOR DELAY '00:00:01';

-- repeat for 1-9

This was several years ago, and something that didn't occur to me at the time (which I remember now, too, after seeing the other link posted by wBob) is that using TABLOCK inside those transactions may help speed up the inserts (though you'll want to test based on volume, isolation level, etc).

It might also be useful (even with all this work) to enable Read Committed Snapshot Isolation and/or if you are on Enterprise Edition considering partitioning (and aligning the staging tables to those partitions).


Index Strategies on Text or NVARCHAR(MAX) Fields

Why do you think anything but a scan should be used to pull back all the data? A full-text index won't really help - that helps you search those columns, but if you're just returning all the data (for any variety of WHERE clauses) then there's no shortcut to reading all of the data. Can I ask why a to_addr, which is presumably limited to ~320 characters by the SMTP standards (depending on which standard you believe), contains data > 4000 characters?

A lot of people think that a scan is bad. If you need to return a large amount of data, then often a clustered index scan will be used. Your where clause may lead to seeks being used to locate the rows to return, but a seek isn't going to work where the data in that column is that large. Are you just seeing a scan in the execution plan and assuming that must be the problem?


Why does the size of the datafile on disk not decrease after deleting records from a table?

Space in database files is allocated as needed.  When rows are removed from a table, that space is simply marked as unused inside the datafile.  Datafiles do not shrink automatically (unless auto-shrink is turned on) because that requires extra disk I/O that is typically counter productive for performance.  The assumption is that any unused space will at some point be re-used by new rows.

To summarize, space allocated to the file on disk, and the logical size of tables within the database are not tightly coupled.

In more detail (although still very simplified):

They are definitely related. Rows in a rowstore live on pages, which are 8k in size and arranged into extents. Extents are groupings of 8 contiguous pages on disk.

Rowstore rows lives on pages and when "deleted" are not physically removed from the file - just marked as "free" inside of SQL Server through the use of bitmap and bytemap allocation structures and SQL Server will re-use the space accordingly.


Is there any way I can speed up this large full-table query?

Is there anything obvious I can do to improve this query's performance by adding/tweaking indexes or tweaking the query itself?

Yes.  First make the predicate sargable.

SELECT * FROM
[Sales].[dbo].[Interaction]
WHERE Interaction_date >= '20190101'

And then consider partitioning, or a filtered index with included columns.  But even if you have an index that can support this query as a simple seek+scan, sending all the columns to the client takes time.


Handle 4 tables and to show 4 columns as result

First I'll explain the mistakes/errors.

1) UNION provides the vertical merging not horizontal.

+---+---+         +---+---+     +---+---+
| A | B |         | A | B |     | 1 | 2 |
+---+---+         +---+---+     | 3 | 4 |              +---+---+---+---+
| 1 | 2 |  UNION  | 6 | 7 |  =  | 6 | 7 |          not | 1 | 2 | 6 | 7 |
| 3 | 4 |         | 8 | 9 |     | 8 | 9 |              | 3 | 4 | 8 | 9 |
+---+---+         +---+---+     +---+---+              +---+---+---+---+

Also UNION requires both merged tables have columns of the same
names/types/order. That is why you get only two columns while the columns and rows from the second table are dissapeared.

2) That "some syntax error" was the requirement that each tablesource used by query should have the name. When you have used the subquery you should give it an alias:

SELECT SectorTrend.Trend
  FROM ( SELECT Sectors.SectorName
           FROM ( SELECT Companies.Symbol, CompanyData.strenght
                   --- some missed clause should be here
                   INNER JOIN CompanyData ON Companies.CompanyID = CompanyData.CompanyID
                   WHERE CompanyData.strenght = 'anyvalue'
                ) AS aaa --- that's what I mean
          INNER JOIN Sectors ON Sectors.SectorID = SectorTrend.SectorID
          WHERE Sectors.SectorID = 'anynumber'
      ) AS bbb --- that's what I mean
 INNER JOIN SectorTrend ON SectorTrend.SectorID = Sectors.SectorID

Anyway that query is too complicated and wrong to be used.

3) Multiple JOINs
Your mistake is that you have joined the table Companies twice (instead of Sectors) and engine can't tell one instance from another. The good practice is to assign an unique alias to each table in the query:

SELECT c.Symbol
     , s.SectorName
     , cd.strenght
     , st.Trend
  FROM Companies   AS c
  JOIN Sectors     AS s  ON s.SectorID   = c.SectorID
  JOIN CompanyData AS cd ON cd.CompanyID = c.CompanyID
  JOIN SectorTrend AS st ON st.SectorID  = s.SectorID

I've replaced the INNER JOINs by JOINs because (at least in the mysql dialect) they are exactly the same with ON clause. And this query is my choice for the task.


ora-12514 Error While Connecting local Oracle EXPRESS EDITION 11

ORA-12514 means there is no requested service registered in the listener.

ORA-12505 means there is no requested instance registered in the listener.

If the service name or instance name you provided was correct, then your database may be not started.

Oracle databases run as a service on Windows named OracleServiceXE (for Express Edition), you should check and start the service if it is not running.

On Linux, you can start the database as:

sqlplus / as sysdba
SQL> startup

After that you should see your instance and services in the output of lsnrctl status, and that is when you should try connecting again.


You can disable the trigger in the database function, but any actions on the table with the trigger that occur outside of that transaction (functions create implicit transactions) will be blocked until the function completes.

Simple test case:

-- In session 1
drop table if exists test;
create table test ( x int, y text );

create or replace function test_trg_f() returns trigger as $func$
begin
    perform null;
    return NEW;
end;
$func$ language plpgsql;

CREATE TRIGGER test_trg BEFORE INSERT ON public.test FOR EACH ROW EXECUTE PROCEDURE public.test_trg_f();

-- in session 2
DO $$
begin
    alter table test disable trigger test_trg;
    perform pg_sleep(10);
    alter table test enable trigger test_trg;
end;
$$;

-- In session 1, will be blocked until session 2 completes
INSERT INTO test (x,y) VALUES (1,1);


How to quote qualified table name with format() function?

To avoid ambiguity in the target table name, provide names of schema and table separately. With existing tables Postgres can use the current search_path to default to the first schema with an object of that name for unqualified table names. Since that's obviously not possible for (yet) non-existing tables, we have to be more explicit there.

If you typically want to place the target table in the same schema as the source, you can still omit the schema name and make the function default to the schema of the (existing!) source table for convenience. Like:

CREATE OR REPLACE FUNCTION copy_table(_source_tbl regclass
                                    , _target_tbl text
                                    , _target_schema text = NULL)
  RETURNS bool AS
$func$
DECLARE
   query_str text;
BEGIN
   IF _target_schema IS NULL THEN               -- if no target schema provided ...
      SELECT c.relnamespace::regnamespace::text -- ... default to schema of input table
      FROM   pg_class c
      WHERE  c.oid = _source_tbl
      INTO  _target_schema;
   END IF;

   query_str = format('DROP TABLE IF EXISTS %1$I.%2$I;
                       CREATE TABLE %1$I.%2$I AS (TABLE %3$s);'
    , _target_schema
    , _target_tbl
    , _source_tbl);

   EXECUTE query_str;
   RAISE NOTICE '%', query_str;
   RETURN true;
END
$func$  LANGUAGE plpgsql;

Call:

SELECT copy_table('ex.test', 'text1', 'ex');

Since the 3rd (last) parameter _target_schema has a default value, we can omit it, in which case the function defaults to the schema of the source table:

SELECT copy_table('ex.test', 'test1');

Even if we don't provide the source schema explicitly, relying on the current search_path:

SELECT copy_table('test', 'test1');

Related:

Table name as a PostgreSQL function parameter


Why doesn't time for a simple update scale linearly with the number of records?

If you post the execution plans there may be obvious differences that we can highlight. Other than plans it could be:

Different distribution of values in the dataset, leading to more frequent page splits
Data and or log file growth
A sort or hash operation spilling to tempdb
Contention with other workloads

Need more information (queries, sample data, schema, plans) for better answers.


Cannot restore Amazon RDS SQL Server database to given point-in-time using SQL Server Management Studio

I would try scripting out the operation instead of running whatever Management Studio is doing "for" you. You shouldn't need to use xp_fileexist (assuming you can validate that the files actually exist in the place the script says). Using 2012 SSMS, I am restoring to a point in time that does not coincide directly with a log backup:

The resulting script in my case was:

BACKUP LOG ... WITH NOFORMAT, NOINIT, NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY
RESTORE DATABASE ... WITH FILE = 1, NORECOVERY, NOUNLOAD
RESTORE LOG ... WITH FILE = 1, NORECOVERY, NOUNLOAD
...
RESTORE LOG ... FROM DISK = [...path from line 1...] WITH NOUNLOAD, STOPAT = <time>


Join relation on itself and pull distinct rows

SELECT t1.reviewer_id AS first,
       t2.reviewer_id AS second,
       t1.book_id
FROM table AS t1
LEFT JOIN table AS t2 ON t2.book_id = t1.book_id
                     AND t2.reviewer_id > t1.reviewer_id

or

SELECT MIN(reviewer_id) AS first,
       CASE WHEN COUNT(reviewer_id) > 1
            THEN MAX(reviewer_id)
            ELSE NULL
            END AS second,
       book_id
FROM table
GROUP BY book_id


No, I don't think defining an index for a common table expression is possible.

You can experiment with the materialize hint, but I don't think you'll get anything faster than the solution with (indexed) temporary tables.


Postgres-9.1 having more Idle connections

The answer is: "Yes, sure you can." But I doubt you wanted to ask that.

Obviously, you need to make sure you only kill processes that need killing.
If possible, it's best done with the same role as the targeted processes to avoid collateral damage - only superusers can terminate processes of other roles.

Related:

How do I detach all other users from a postgres database?


Set InnoDB as default engine and set a default collation

You can set default-storage-engine=InnoDB (Set the default storage engine for tables) and you can even force your customers to use InnoDB with enforce_storage_engine=InnoDB (Force the use of a particular storage engine for new tables). enforce_storage_engine  is a MariaDB only feature.

Default character set and collation you can set in my.cnf

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

The other options like default storage engine need also be in my.cnf to be permanent.

When you don't specify storage engine, collation and character set, MySQL chooses defaults set by you.


PSQL cte delete seem have have ambiguity when select same column from delete table

Your second query will fail, because d0 only has a column seller_id.
Your first query deletes all rows because the id in select id from d0 cannot refer to a column from d0, so it is taken as a column from the outer query, and the whole thing effectively becomes
WITH d0 AS (...)
DELETE FROM seller
WHERE seller.id IN (SELECT seller.id FROM d0)

and that condition will be true whenever d0 is not empty.


Optimal mySQL design for 500k+ products with up to 50 specs

A common request.  You are actually a step ahead of most questions tagged with [eav] -- you have thought about "common use patterns" and how to optimize once you gather that info.
You should be able to make an initial guess at what is most common, put them in columns for efficient searching, put flag-like attributes in a FULLTEXT-indexed column, then throw the rest into a JSON column for testing in the app.
More:  http://mysql.rjweb.org/doc.php/eav


Finding discrepancies between soft link parents and children

something like below query? adjust code to suit your needs. if you have one column name with multiple definitions, you need to concentrate on those. You didn't mention oracle version so I am going to assume you have access to LISTAGG function in your environment. if not, make suitable amends but this should get you closer. You may optionally want to add views for this comparison as well, but I have only chosen to select tables.

with st as (select c.OWNER ,c.TABLE_NAME, COLUMN_NAME, case when C.NULLABLE = 'N' then 'NOT NULL ' end ||
case when C.DATA_TYPE in ('CHAR','VARCHAR2') then C.DATA_TYPE || ' (' || C.DATA_LENGTH || case when c.char_used = 'C' then ' char' end || ')'
     when C.DATA_TYPE = 'DATE' then 'DATE' when C.DATA_TYPE = 'TIMESTAMP' then 'TIMESTAMP (' || c.DATA_SCALE || ')'
     when C.DATA_TYPE = 'NUMBER' and C.DATA_PRECISION is null then 'NUMBER'
     when c.DATA_TYPE = 'NUMBER' and c.DATA_PRECISION is not null then 'NUMBER (' || c.DATA_PRECISION ||',' || NVL(c.DATA_SCALE,'0') || ')'
end Col_def
from DBA_TAB_COLUMNS C join DBA_OBJECTS O on (O.OWNER = C.OWNER and O.OBJECT_NAME = C.TABLE_NAME)
where O.OWNER in (<list your schema owners here>)
and O.OBJECT_TYPE in ('TABLE') order by 2,3,1)
select column_name,col_def, listagg(table_name,',') within group (order by table_name) tlist
from st group by column_name, col_def order by 1,2;


Check whether MySQL Innodb Rows are compressed

Use the show command to see the table definition :

mysql:>show create table <table name >


How to prevent SQL Server database server from running out of disk space?

I have my transaction log shipping every 2 days, plus daily backup of the db. My hard disk is 500 GB, but the database size is already reaching 490 GB. I'm afraid it'll run out of diskspace soon.

Taking transaction log backups, as are required for log shipping, internally clears portions of the transaction log, allowing the physical space on disk to be reused.

Assuming it's the size of the log file that's the problem, the frequency of the log backups should be increased. This will mean less physical disk space is required for ongoing operations; the physical file could then be shrunk to reclaim the unused space, probably so it can be used by data file growth.

If you still only want to do log restores on the secondary once every 2 days, that's fine -- the frequency of the backup/copy/restore jobs can be set independently.

Note that this only applies to log files.

If it's the data portion of the database that is growing and the amount of log being generated isn't necessarily increasing (and is a small part of the total space used), you will need to either (a) provision more storage, or (b) implement some kind of archiving/data deletion process, as there is no built-in mechanism that will clear out data.


What am I doing wrong in mysql database?

If you are having a trigger on a table, You can not operate the same table which is used in the trigger.

As you mentioned you want to delete records from the table after inserting the records. Instead, why can't you skip inserting records to the table.

Can you mention your exact requirement in brief.


Monitor number of records returned in a long-running SELECT statement during SSAS processing

If you connect SQL Server Profiler to SSAS and watch the Progress Report Current events with an EventSubclass=ReadData the IntegerData column will show the cumulative rows read so far for that SSAS partition.

  Integerdata:   Contains the integer data associated with the reported
  event, such as the current count of the number of rows processed for a
  processing event.


MySQL/PHP - SELECT DISTINCT column based on another column with ORDER and LIMIT

Test:

SELECT CASE WHEN sender_id = 1 THEN receiver_id ELSE sender_id END user_id
FROM messages
WHERE 1 IN (sender_id, receiver_id)
GROUP BY user_id
ORDER BY MAX(id) DESC LIMIT 3


try..

SET @_c = 0;
SELECT kills,deaths,revives,damage,levels,rank,skill
FROM (  SELECT  *,  (@_c \:= @_c + 1) AS `rank`,
        ((kills + damage + levels)/(deaths+revives+1)) AS skill
    FROM leaderboard   ) AS newtable
WHERE soldier_id = 1(example)
ORDER BY skill DESC;

check for NOT NULL values in cols: kills, damage, levels, deaths, revives


How to convert list of dates to list of date ranges grouped by a condition

With help of this other question, you can try :

WITH
  t AS
    ( SELECT state, auditDate, x = CASE WHEN state = LAG(state) OVER (ORDER BY auditDate)
                           THEN NULL ELSE 1
                       END
      FROM #Records
    )
select min(auditDate), dateadd(dd, 1, max(auditDate)), state
from (
    SELECT state, auditDate, c = COUNT(x) OVER (ORDER BY auditDate)
    FROM t
    ) t
group by c, state
ORDER BY 1 ;

That assumes that records are every day. If not, let's do some small changes:

WITH
    t AS
    ( SELECT state, auditDate, x = CASE WHEN state = LAG(state) OVER (ORDER BY auditDate)
                           THEN NULL ELSE 1
                       END
      FROM #Records
    ),
    t2 as
    (
    SELECT state, auditDate, c = COUNT(x) OVER (ORDER BY auditDate)
    FROM t
    )

select a.state, min(a.auditDate) as startDate, isnull(min(b.auditDate), getdate()) as endDate
from t2 a
    left join t2 b on a.c = b.c - 1
group by a.state, a.c
order by 2


Can Postgres partition table by column values to enable partition pruning

In such a case having an index on country and city will probably perform much better than partitioning.
If you are really set on using partitioning, postgres always uses the CHECK constraints to prune partitions.
A way to approach this is to:

Create a function that based on the "country" & "city" input will create a table that inherits from salesdata (if the table already exists return it)
If the table is newly created add a check constraint on the values of country & city.
Create a trigger that executes before/after insert into salesdata that will run that function and insert into the country & city partition.

When following this procedure and you do a select on SalesData postgres will prune the tables not matching the check constraint.


Does a replica set with only two machines make sense?

If a primary member unexpectedly dies, the secondary one won't elect itself the new primary anyway, since it won't have the majority (which is two in a replica set of two machines).

This is correct. In order to elect (and maintain) a primary, a majority of voting members need to be available.

  It is impossible to read the data from the database if the primary member is unavailable, even with read_preference=ReadPreference.SECONDARY.

This is incorrect. Without a primary you cannot write to a replica set, but reads are still possible using non-primary read preferences such as secondary, primaryPreferred, secondaryPreferred, or nearest. The default read preference is primary, which provides strong consistency (versus eventual consistency when reading from a secondary). The primaryPreferred read preference reads from a primary if available, otherwise from a secondary.

  Recreating the primary member won't work anyway: it cannot be added to the existent replica set, since it lacks the primary member; it cannot rs.initiate(); either, since it will create a different replica set with a different ID.

If you lose one of the members in a two node replica set, you can force reconfigure the surviving member to be a single node replica set and then add new member(s). You only need to run rs.initiate() once in the lifetime of a replica set.

  Therefore, is there a real life situation where having only two members in a replica set would make any sense? Or such situation is only theoretical, and one would expect to see at least three members in every replica set in practice?

In general, most replica set deployments have sufficient members to allow for automatic failover (i.e. minimum of three members). If high availability is not a concern (or you prefer to have manual intervention), a two member replica set is not disallowed. However, a more typical option to allow failover would be to add a third voting-only member (aka an arbiter) to your replica set.

An arbiter is a lightweight voting-only member that will provide the additional vote needed for a majority in the event one of your two data bearing members is unavailable in a three-member configuration. The arbiter helps with availability but is a compromise over a third data-bearing member. When a three member replica set with an arbiter is in a degraded state (i.e. one of your data-bearing members is unavailable) you will still have a primary but no longer have data redundancy or replication. An arbiter will also prevent your application from being able to rely on the majority write concern to ensure that data is committed to a majority of replica set members.

My personal recommendation would be to deploy three data-bearing members as a minimum for a production replica set, however fewer data-bearing members could be considered for a development or non-critical deployment.


'BULK' rowset provider not supported in this version of SQL Server.

BULK INSERT is not supported on SQL Azure. You can use bcp or write something that wraps SqlBulkCopy to bulk insert into SQL Azure. There's other ways to bulk insert aside from these, too.

  On executing it,my SQL management studio 2008,gives the following error :

Don't confuse SQL Server Management Studio with SQL Server Database Engine.


Can SQL Server's BCP output to multiple gzip compressed files?

Not natively with BCP, but if you define SQL Server Integration Services as native (after all, it's free in the box with SQL Server), then use SSIS instead. You can even output to compressed files with third party add-ons for SSIS.


SQL update multiple rows in destination table with same id but different values from source table

Couldn't find a SQL Server 2008 fiddle engine so I had to opt for a SQL Server 2014 ... so not sure if the following will work in SQL Server 2008, but fwiw ...

Setup some sample data:

create table Table1(id int, Date datetime null);
create table Table2(id int, Date datetime);

insert Table1 values (1,null)
insert Table1 values (1,null)
insert Table1 values (2,null)
insert Table1 values (2,null)
insert Table1 values (2,null);

insert Table2 values (1,'2013-01-29 08:50:00.000')
insert Table2 values (1,'2013-01-29 15:28:00.000')
insert Table2 values (2,'2013-01-31 11:56:00.000')
insert Table2 values (2,'2013-03-11 16:08:00.000')
insert Table2 values (2,'2013-01-31 14:04:00.000');

Keeping in mind that we haven't been provided (yet) with any means to determine which rows to match between Table1 and Table2 for a given id value, I'll just let row_number() generate a 'matching' rowid.

And then we'll make use of SQL Server's ability to update Table1 via a derived table definition:

update T1
set    T1.Date=T2.Date

from   (select row_number() over(partition by id order by Date) as rowid,
               id,
               Date
        from   Table1
        where  Date is NULL) T1

join   (select row_number() over(partition by id order by Date) as rowid,
               id,
               Date
        from   Table2) T2

on      T1.id    = T2.id
and     T1.rowid = T2.rowid;

And the results:

select * from Table1;

id  Date
--- --------------------
1   2013-01-29T08:50:00Z
1   2013-01-29T15:28:00Z
2   2013-01-31T11:56:00Z
2   2013-01-31T14:04:00Z
2   2013-03-11T16:08:00Z

And here's a SQL Fiddle for the above.


SQL Service Using Group Managed Service Account does not start after reboot

The information in Using a gMSA with SQL Server by Wayne Sheffield worked for me with the service issue.

  The pitfalls of using a gMSA with SQL Server

  As with almost all things, there is inevitably something that doesn’t work correctly. One thing that I found is that when the server is rebooted, the SQL Server services are not restarted. And I’m not the only person that has had this problem. After digging through the Windows event logs, we were able to figure out the issues. The service was not restarting because it was trying to do things before those services had been started. We were able to overcome this issue by creating service dependencies. A service dependency prevents the service (SQL Server) from starting up before other specified services are running.

See the linked article for full details.


Assign Failover Cluster and High Availability Always On from Old to New Domain

An important note is to ensure you have databases fully backed up before beginning any migration path, particularly when moving between domains. File permissions, and other problems could result in data loss & require reverting to those backups.
If you are using Windows Server 2019
With Windows Server 2019, migrating a cluster to a new domain is supported via a straightforward process documented here.
Given most folks are still on 2016 and older
With Windows Server 2016 & older, the migration process involves destroying the cluster, and rebuilding it on the new domain. Given the need to destroy & re-create the cluster, I recommend uninstalling before & reinstalling after the SQL Server installation. You will want to have good backups before the migration as well.
The instructions to move a Windows Server Failover Cluster to a new domain are included in the docs, and quoted below.
Build a fresh cluster
Building a fresh cluster & migrating to it is going to be your best option. (And moving to an Availability Group at the time would be my preferred path, too!)
If you don't have new or extra hardware to build new, you can do something like this to reuse existing servers:

Evict a node from your existing FCI.
Uninstall SQL Server from that node.
Remove the server from the domain & add it to the new domain.
Build the new single-node Windows cluster on the new domain.
Install the FCI on the single-node cluster.
Unmount storage from cluster in the old domain.
Mount storage to the cluster in the new domain.

Note: Be prepared to encounter NTFS permissions issues on your disks. Permissions will be assigned to users/groups on the old domain.
Alternatively, you could assign fresh storage on the new domain & move databases via backup/restore or log shipping.

Attach your databases.
Repeat steps 1-3 for remaining nodes.
Add remaining nodes to the FCI on the new node.

Documented Cluster Migration steps:

The steps in this article enable the Cluster service to start in the new domain. However, you may be unable to bring the resources online in the new domain, and the resources that can be brought online may not work correctly.
To move the cluster:

Create a user account for the Cluster service in the new domain. You must make sure that no Group Policy objects (GPOs) or security template requirements remove any of these rights. The user account must have the following rights:

Lock pages in memory.
Log on as a service.
Act as part of the operating system. (Windows 2000 and Windows Server 2003)
Back up files and directories.
Increase quotas.
Increase scheduling priority.
Load and unload device drivers.
Restore files and directories.
Adjust memory quotas for a process (Windows Server 2003).
For more information about the Cluster service account, click the following article number to view the article in the Microsoft Knowledge Base:

269229 How to manually re-create the Cluster service account
In addition, the Cluster service account must have administrative permissions on all nodes in the cluster.

Set the Startup value for the Cluster service to Manual on all nodes in the cluster:
a. Click Start, point to Settings, click Control Panel, and then double-click Services.
b. Click Cluster Service, and then click Startup.
c. Change the Startup Type from Automatic to Manual.
d. Click OK.

Stop the Cluster service on all cluster nodes:

Click Start, point to Settings, click Control Panel, and then double-click Services.

Click Cluster Service, and then click Stop.

Turn off all nodes except one.

Move the node into the new domain by using procedures that are appropriate to your operating system. Complete the process, and then restart the node.

On the node, change the service account that is used by the Cluster service to log on to the domain to the user account that you created.

Start the Cluster service on that node.

10.Use Cluster Administrator to verify that there are no issues. Try to bring all resources online. Test the functionality of all resources from client computers, and then check the Event Viewer System log for error messages.

⚠ Note
At this point, you can still cancel the move by moving this node back into the old domain and starting the nodes that are not moved.

If the first node move is successful, continue to migrate the other nodes in the cluster to the new domain starting with step 5 for each node.


I need a process that will be constantly running deleting documents
  older than some expiry data.

This you can do it by creating a Time to live(TTL) on the expiration date. TTL indexes are special single-field indexes that MongoDB can use to automatically remove documents from a collection after a certain amount of time or at a specific clock time.

  Reading the MongoDB documentation it looks like when I do a write I'll
  be locking out the reads until the writes have completed.

MongoDB wired tiger storage engine gives document level locking. So, in this case, only the documents that are being updated/inserted will be locked. Other documents are available to read.


Others have already pointed out the culprit: SQL Server accumulates updates in memory (in the buffer pool) and only flushes them out periodically (at checkpoints). The two options suggested (-k and checkpoint interval) are complementary:

-k will make the cause the checkpoint to produce less aggressive IO requests and last longer
lowering recovery interval will cause the checkpoint to start more often

But I did not respond only to regurgitate the fine comments you received do far :)

What you're seeing is, unfortunately, a very typical behavior of queued processing. Whether you use Service Broker queues or opt for using tables as queues approach, the system is very prone to this kind of behavior. This is because queuing based processing is write heavy, even more write heavy than OLTP processing. Both enqueue and dequeue primitives are write operations and there are almost no read operations. Simply put, queue processing will generated the most writes (= most dirty pages, and most log) compared to any other workload, even OLTP (ie. TPC-C like workload).

Very importantly, the writes of a queue workload follow an the insert/delete pattern: every row inserted is very quickly deleted. This is important to distinguish from an append-only pattern of a insert heavy (ETL) workload. You are basically feeding the ghost cleanup task a full meal, and you can easily outrun it. Think about what that means:

enqueue is an insert, it will create a dirty page
dequeue is a delete, it will dirty the same page again (it may be lucky and catch the page before checkpoint, so it will avoid double-flush, but only if is lucky)
ghost cleanup will cleanup the page, making it dirty again

Yes, it really means that you may end up writing a page three times to disk, in three different IO requests, for each message you process (worst case). And it also means that the random IO of checkpoints will be really random as the write point of the page will be visited by those moving heads again between two checkpoints (compare with many OLTP workloads tend to group the writes on some 'hot spots', not queues...).

So you have these three write points, racing to mark the same page dirty again and again. And that is before we consider any page splits, which queue processing may be prone too because of the insert key order. By comparison 'typical' OLTP workloads have a much more balanced read/write ratio and the OLTP writes distribute across inserts/updates/deletes, often with updates ('status' changes) and inserts taking the lion's share. Queue processing writes are exclusively insert/delete with, by definition, 50/50 split.

Some consequences follow:

Checkpoint becomes a very hot issue (no longer a surprise for you)
You'll see heavy fragmentation (the fragmentation per-se won't matter much as you are not going to do range scans, but your IO efficiency suffers and ghost cleanup has more to work, slowing it down even more)
Your MDF storage random IO throughput is going to be your bottleneck

My recommendation comes in 3 letters: S, S and D. Move your MDF to a storage that can handle fast random IO. SSD. Fusion-IO if you have the moneys. Unfortunately this is one of those symptoms that cannot be resolved with more cheap RAM...

Edit:

As Mark points out you have two logical disks backed by one physical disk. Perhaps you tried to follow best practices and split log on D: and data on C: but alas is to no avail, C and D are the same disk. Between checkpoints you achieve sequential throughput but as soon as checkpoint starts the disk heads start to move and your log throughput collapses, taking down the entire app throughput. Make sure you separate the DB log so that is not affected by data IO (separate disk).


Create endpoints on all servers:

CREATE ENDPOINT EndPointName
STATE=STARTED AS TCP(LISTENER_PORT = PortNumber, LISTENER_IP = ALL)
FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE,
                   ENCRYPTION = REQUIRED ALGORITHM RC4)

For a witness server, change ROLE = PARTNER to ROLE = WITNESS in the witness's endpoint.

Run the first SET PARTNER command on the mirrored server:

ALTER DATABASE DatabaseName SET PARTNER = N'TCP://PrincipalServer:PortNumber'

Notice that it is pointing to the principal server in the command, but you are running it on the mirror.

Then run the next two commands on the principal server, pointing the SET PARTNER at the mirror:

ALTER DATABASE DatabaseName SET PARTNER = N'TCP://MirrorServer:PortNumber'
ALTER DATABASE DatabaseName SET SAFETY OFF --OFF=async, FULL=sync

If you are setting up a witness for automatic failovers, use SET SAFETY FULL above instead of SET SAFETY OFF and then run this command on the principal:

ALTER DATABASE DatabaseName SET WITNESS = N'TCP://WitnessServer:PortNumber'


I think I might have a legit answer .. and its a bug.

(similar was found when running checkdb in 2014 & fixed in SP1 + CU1)

When I run the query as is, SQL Server crashes

  2016-06-16 17:27:32.00 spid37s      Failed allocate pages: FAIL_PAGE_ALLOCATION 1

And in the error log I see :

MEMORYCLERK_SQLQUERYEXEC (node 0)                KB
---------------------------------------- ----------
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                            10678248  ==> 10GB !!
2016-06-16 17:27:32.00 spid37s

With OPTION (MAXDOP 1) => serial execution

MEMORYCLERK_SQLQUERYEXEC (node 0)        KB
---------------------------------------- -----------
VM Reserved                              0
VM Committed                             0
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
Pages Allocated                          144

Please test it in your environment and let me know.


Following is one way to find the 10 most frequent values across all 10 columns - but only counting frequency for each value within its own column (explained further, later):

select AggregatedCounts.ICD_Value, AggregatedCounts.Source_Column, AggregatedCounts.frequency from
(
    select ICD9_DGNS_CD_1 as ICD_Value, 'CD_1' as Source_Column, count(1) as Frequency from seis735_db.ip group by ICD9_DGNS_CD_1
    union
    select ICD9_DGNS_CD_2, 'CD_2' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_2
    union
    select ICD9_DGNS_CD_3, 'CD_3' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_3
    union
    select ICD9_DGNS_CD_4, 'CD_4' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_4
    union
    select ICD9_DGNS_CD_5, 'CD_5' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_5
    union
    select ICD9_DGNS_CD_6, 'CD_6' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_6
    union
    select ICD9_DGNS_CD_7, 'CD_7' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_7
    union
    select ICD9_DGNS_CD_8, 'CD_8' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_8
    union
    select ICD9_DGNS_CD_9, 'CD_9' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_9
    union
    select ICD9_DGNS_CD_10, 'CD_10' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_10
) as AggregatedCounts
order by frequency desc limit 10;

How this works:

The main part of this solution is that we have 10 separate SELECT statements.

The first selects all values from column 1 (ICD9_DGNS_CD_1), groups by these values and counts the frequency.

The next statement does the same for the second column, and so on.

By using the UNION operator, we merge together the results from all of these 10 SELECT statements - producing one big set of results. This counts the frequency of each unique value in each of the 10 columns and puts them all into a single long list.

The next part of the solution is that we SELECT from this result set. So the result of those 10 SELECT statements (with UNIONs) is named 'AggregatedCounts' - and we now select from these aggregated counts.

Note, in each of the 10 SELECT statements we are actually selecting 3 values: first the value in the column we are looking at, then a hard-coded string which tells you the name of the column we are looking at (like 'CD_1'), then finally the count. In the first SELECT statement we name these 3 columns (ie. we give each an alias): ICD_Value, Source_Column and Frequency.

Finally, in our outer select, we select these 3 column names. That outer select is ordered by Frequency, descending, and we limit the results to the top 10 rows - that is, the 10 rows with the highest value in the Frequency column - and we report what the value is, and from which column it originally came.

But note (!) If you have, say, the value 'aaa' in column 1, and 'aaa' in column 2 - they will not be counted together using this solution.

If we want to count values across all columns, then we use the following solution instead:

select AggregatedCounts.ICD_Value, count(1) as Frequency from
(
    select ICD9_DGNS_CD_1 as ICD_Value from seis735_db.ip
    union all
    select ICD9_DGNS_CD_2 from seis735_db.ip
    union all
    select ICD9_DGNS_CD_3 from seis735_db.ip
    union all
    select ICD9_DGNS_CD_4 from seis735_db.ip
    union all
    select ICD9_DGNS_CD_5 from seis735_db.ip
    union all
    select ICD9_DGNS_CD_6 from seis735_db.ip
    union all
    select ICD9_DGNS_CD_7 from seis735_db.ip
    union all
    select ICD9_DGNS_CD_8 frequency from seis735_db.ip
    union all
    select ICD9_DGNS_CD_9 frequency from seis735_db.ip
    union all
    select ICD9_DGNS_CD_10 frequency from seis735_db.ip
) as AggregatedCounts
group by AggregatedCounts.ICD_Value
order by Frequency desc limit 10;

What have we changed here?

Firstly, the 10 selects are now just selecting all values - no group by, and no other values (like source column or counting).

Secondly, the UNION statements have been changed to UNION ALL - this is because by default, UNION will include only unique values in the result set. This time we want to do our counting in the outer SELECT, so we need all instances of all values in our AggregatedCounts list.

Finally, as just suggested, now our GROUP BY has moved to the outer SELECT statement - and we are doing our counting here - across all values from all columns.

You can view the first solution working on db-fiddle

And you can view the second solution working on db-fiddle - using exactly the same source data.

Note on these 2 fiddle solutions, in the first solution, the value '500' ranks above the value '401' (with frequencies 30 and 28) but in the second, '401' ranks above '500' (with frequencies 31 and 30). Examine the last 3 lines of inserting data - I have put the value '401' into the 5th column here - so '401' appears 3 times in column 5, and 28 times in column 4 = 31 times in total. This explains the difference in results, and the difference between the two SELECT statements.

Lastly, using UNION like this is making your database do a lot of work. If your table is large, this solution may take a long time to run and slow down your database. There may be other ways to solve this using procedures, but this is an easy to understand approach using SELECT statements.


Resources for understanding SQL Server locking and concurrency?

Best book on the subject, and the most complete resource, is Kalen Delaney's SQL Server 2008 Internals. You really can't do any better. Another good book on the subject is Chris Bolton's SQL Server 2008 Internals and Troubleshooting. I don't think it's as complete as Kalen's book, but it does cover things pretty well, especially around locking and concurrency.


jdbc equivalent to libpq "hostaddr" connection string parameter

If your proxy is just a TCP forwarder, like iptables DNAT, your postgresql certificate has to include a SubjectAlternativeNames with the IP address you connect to (the proxy address).
Otherwise, if your proxy decode SSL and creates a different connection to postgresql, then the same change should be made to the proxy server.


Length of LOB data (%1) to be replicated exceeds configured maximum %2

The max text repl size options exists to limit how much LOB data can be added to a replicated column.  If you turn off the LOB size limit and a user inserts a large amount of LOB data, latency will increase and replication will come to a crawl.  The max text repl size option can be used to prevent this.


Difference between Session Timeout and Heartbeat in always on

Heartbeat is what will determine if a node is down and initiate a failover.  Session timeout could be from the client, server to server, or other network related issues.  it seems like you are having several network issues.

You don't have to have a dedicated heartbeat network but if you don't, it does expose you to broadcast storms and other issues that might hit your main network.

AlwaysOn runs on and uses the WSFC APIs but if you have a bad laggy network, it's best to resolve that or at least put your servers on separate network hardware/VLAN/subnet/etc than the clients if that is what is needed to resolve this.  Regardless, you will want to ensure a healthy heartbeat at a minimum.

WSFC Inter-node Health Detection and Quorum Voting

  Each node in a WSFC cluster participates in periodic heartbeat
  communication to share the node's health status with the other nodes.
  Unresponsive nodes are considered to be in a failed state. A quorum
  node set is a majority of the voting nodes and witnesses in the WSFC
  cluster. The overall health and status of a WSFC cluster is determined
  by a periodic quorum vote. The presence of a quorum means that the
  cluster is healthy and able to provide node-level fault tolerance. +

If you need a quicker bandaid or perhaps you just have to deal with the latency/network issues you can change the value of the timeout to something that works better for your environment.

  Run command prompt as administrator. Execute the following to change
  the same-site heart beat to 2 seconds (replace clustername with the
  actual WFC name).

cluster /cluster:clustername /prop SameSubnetDelay=2000:DWORD

  Execute the following to change the
  cross-site heart beat to 4 seconds (replace clustername with the
  actual WFC name).

cluster /cluster:clustername /prop CrossSubnetDelay=4000:DWORD

  Execute the following to change the
  same-site ping loss threshold to 10 lost pings (replace clustername
  with the actual WFC name).

cluster /cluster:clustername /prop SameSubnetThreshold=10:DWORD

  Execute the following to change the
  cross-site ping loss threshold to 10 lost pings (replace clustername
  with the actual WFC name).

cluster /cluster:clustername /prop CrossSubnetThreshold=10:DWORD

With Powershell:

  Run PowerShell as administrator. Import the FailoverClusters module.

Import-Module FailoverClusters

  Execute the following to change the
  same-site heart beat to 2 seconds (replace clustername with the actual
  WFC name). (get-cluster).SameSubnetDelay = 2000 Execute the following
  to change the cross-site heart beat to 4 seconds (replace clustername
  with the actual WFC name).

(get-cluster).CrossSubnetDelay = 4000

  Execute the following to change the same-site ping loss threshold to
  10 lost pings (replace clustername with the actual WFC name).

(get-cluster).SameSubnetThreshold = 10

  Execute the following to change
  the cross-site ping loss threshold to 10 lost pings (replace
  clustername with the actual WFC name).
  (get-cluster).CrossSubnetThreshold = 10

Note in no way is this a recommendation to run this on your production systems without first testing it and getting the wider group such as network and server admins involved


Is there a reason/benefit I am not aware of for this kind setup?

I don't know about mysql but in SQL server, you would use this type of thing when you can't afford to have gaps in the sequence from rollbacks. Often this is a requirement for invoice numbering for accounting auditing.


Creating a database link from Oracle Cloud server to remote access database

In theory yes, you could set up such a link, but I'm not sure I'd advise it. You would need an Oracle Gateway server on your local network to bridge the connection. You will also need direct network connectivity from the cloud to your Gateway server, which means opening your local firewall to allow the inbound connection from the internet. It would imply that you have a pretty robust network/secure infrastructure to support something like that.

See here: https://blogs.oracle.com/datawarehousing/making-database-links-from-adw-to-other-databases

or here: https://conferences.heanet.ie/assets/files/Thursday_B_11.00_Dick_Doyle_UCD.pdf


ETL: extraction strategy for 200 source databases

If you have 200 identical sources then you can parameterise a SSIS package with the data source and kick off nultiple threads.  These can be controlled within the package by a foreach loop or from an external source that kicks off the extractors with a parameter.

You could consider a full load for relatively small dimensional sources and an incremental load for transactional data.  This would require you to have persistent dimensions, but this is fairly straightforward to do with MERGE operations, or a pre-load area and dimension handler if you need slowly-changing dimensions.

You may wish to consider giving each source its own staging area (maybe a schema for each source in the staging database).  This eliminates locking issues on the staging tables.
Build a set of views over the staging tables (essentially just set of unions that correspond to each of the source tables) that includes data source information.  These can be generated fairly easily, so you don't have to manually cut and paste 200 different queries into the union.  Once you've staged the data then ETL process can read the whole lot from the view.

This allows the ETL to run in one hit, although you will have to come up with a strategy to deal with extract failures from individual systems.  For this, you might want to look into an architecture that deals with late arriving data gracefully, so you can catch up individual feeds that had transient issues.

BCP

For 200 simple extracts, BCP is probably a good way to go.  The sources are all identical, so the BCP files will be the same across sources.  You can build a load controller with SSIS.  Getting multiple threads to read the top off a common list would require you to implement synchronised access to the list.  The SSIS process has a bunch of loops running
in parallel in a sequence container that pop the next item, execute it and update the corresponding status.

Implementing the 'next' function uses a sproc running in a serializable transaction that pops the 'next' eligible source off the list and marks it as 'in progress' within the transaction.  This is a 'table as queue' problem, but you don't have to implement synchronised inserts - a whole batch can be pushed into the table at the start of the run.

Structure the individual extract process so that it tries once or twice again if the first attempt fails.  This will mitigate a lot of failures caused by transient errors.  Fail the task if it fails twice, and structure the ETL so it is resilient to individual extraction failures.

Incremental loads

An incremental loader is probably not worth bothering for dimension tables unless you have a really big dimension that shows real performance issues.  For the fact table data sources it probably is worth it.  If you can add a row version to the application table with a timestamp column or some such, you can pick up stuff that's new.  However, you will need to track this locally to record the last timestamp.  If there is an insert or update date on the data you may be able to use that instead.

Full Loads

What could possibly go wrong?

200 processes kicking off to do a full load places a load spike on the network and possibly the staging database.  This could lead to all sorts of transient issues like timeouts.  For small dimension tables it's probably not such a big issue.  However for 100GB there are quite a wide variety of issues - WAN saturation, locking (although the right staging architecture will mitigate that), availability of sources.  The longer the extract process has to run the bigger influence environmental factors have on the reliability of the process.

There are quite a lot of imponderables here, so YMMV.  I'd suggest an incremental load for
the larger tables if possible.


IBM DB2 9.7 and latest 10 version compatibility

Not as much a compatibility thing, but definitely something to watch out for.....table spaces that are still DMS/SMS. You can upgrade a V 9.7 database with them to a V 10.x database, and it will accept them. You can't create DMS/SMS anymore, but it appears to be backwards compatible for migration purposes. You will probably want to convert to automatic storage as soon as possible.

Now...here's what really to watch out for....SMS/DMS tablespaces from a database originally created prior to version 9.7 and then migrated to version 9.7. Reason being, there is something called the reclaimable storage attribute that was not introduced until version 9.7 and unfortunately it is only set on table space creation. It is not added if you migrate to 9.7 or higher (even if you migrate the table space to automatic storage!!!!). This becomes a problem only in the sense that if a REORG is issued after mass deletes, DB2 may not release the freed up disk back to the OS. You may need to lower the high water mark to do so. However you cannot lower the high water mark on DMS or converted automatic storage table spaces if the table space was from a database created prior to 9.7. From what I have researched, the only way to resolve that issue (ie, getting tablespaces with the reclaimable storage attribute) is to create new table spaces with copies of your tables and unload and load all your data into these new tables and then drop the old ones.


List of all missing order numbers that are not in the table

First create an auxiliary numbers table larger than the maximum range you will ever be interested in.

CREATE TABLE dbo.Numbers
(
N INT primary key
);

WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
    E02(N) AS (SELECT 1 FROM E00 a, E00 b),
    E04(N) AS (SELECT 1 FROM E02 a, E02 b),
    E08(N) AS (SELECT 1 FROM E04 a, E04 b),
    E16(N) AS (SELECT 1 FROM E08 a, E08 b),
    E32(N) AS (SELECT 1 FROM E16 a, E16 b),
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
INSERT
INTO  dbo.Numbers
SELECT TOP(100000) N
FROM cteTally
OPTION (MAXDOP 1);

Then the query is simple

DECLARE @Start INT = 9001
        , @End INT = 9009

SELECT @Start + N - 1 AS ORDERNUMBER
FROM   dbo.Numbers
WHERE  N <= ( 1 + @End - @Start )
EXCEPT
SELECT [ORDERNUMBER]
FROM   [dwdata].[dbo].[ORDERS]
ORDER BY [ORDERNUMBER] DESC


How to restore MySQL database from Files

install the new MySQL
create the db you want, let's say DB_NAME_RESTORE
go to /var/lib/DB_NAME_RESTORE and copy the data files there
chmod on the files if you have to


I think I'm understanding basically what you're after.

Are you wanting to record each individual vote or maintain a single value in a +1/-1 fashion? This could be a start anyway:

UserLists Table

Userid
ListId
VotingAllowed
CreationDate

ListItems Table

ListId
ListDescription
InitialSequence
Votes


Multiple parents and multiple children in product categories

You should consider Postgres, as it does hierarchical querying better than Mysql. If using Mysql, consider using a closure table to aid querying.

I think you are actually confusing Brand and sub-sub-category. You should have a brand table too.

create table category (
  id serial primary key,
  name text not null,
  parent_id int null references category(id)
);

create table brand (
  id serial primary key,
  name text not null unique
);

create table brand_category (
  brand_id int not null references brand(id),
  category_id int not null references category(id),
  primary key (brand_id, category_id)
);

/* the product specification, not the actual product asset instance: */
create table product (
  id serial primary key,
  name text not null,
  brand_id int not null references brand(id)
);

create table product_category (
  product_id int not null references product(id),
  category_id int not null references category(id),
  primary key (product_id, category_id)
);

example:

/* null parent_id means top-level category */
insert into category values

(1, 'Electronics', null),
(2, 'Cameras', null),

(3, 'Smart Phones', 1),
(4, 'Tablets', 1),
(5, 'Laptops', 1),
(6, 'Headphones', 1);

insert into brand values
(1, 'Samsung'),
(2, 'Nokia'),
(3, 'Apple');

/* samsung brand in both smartphones and cameras: */
insert into brand_category values
(1, 3),
(1, 2),
(2, 3),
(3, 3);

insert into product values
(1, 'Samsung Model X', 1);

/* add samsung model x as a smart phone: */
insert into product_category values
(1, 3);


Create stored procedure with dynamic number of columns + column names

When passing a table name as a parameter, you can create a clone of this table in a plpgsql function or procedure with simple code like this:

CREATE FUNCTION example(tblname text) RETURNS void AS
$$
 begin
   execute format('CREATE TABLE %I (LIKE %I)', tblname || '_adj', tblname);
 end
$$ LANGUAGE plpgsql;

See Executing Dynamic Commands in plpgsql documentation for more.

To adjust the values in columns like GDP or Dividends, you may automate this with a dynamic UPDATE similarly to the CREATE TABLE, if the columns that need the updates exist under identical names in all these tables (but that seems unlikely except if the schema was designed up-front with this constraint in mind).


Splitting Existing Partitions in SQL 2014

Below is an example script to create the new yearly partitions. You may need to tweak the script for your actual partition function name and new filegroup names. This method assumes the operation is performed in a maintenance window when there are no inserts into the table and data for 2016 and later isn't needed for queries.

The summary of the steps performed by the script are:

create new partition function and scheme like the originals
create new aligned staging table like the original
switch the partition containing data > 2016-01-01 into the staging table
add 3 new partitions to the original partition function/scheme
rebuild the staging table on the original partition with DROP_EXISTING=ON
switch the 3 new partitions from the staging table back into the original table

There are a couple of issues with your existing table partitioning you should be aware of. Because of the RANGE LEFT function, datetime values that are exactly midnight on the first of the year will be placed in the wrong partition. For example, a row with Time value '2012-01-01T00:00:00' will be in partition 1 along with year 2011 data instead of partition 2 with the rest of year 2012 data as desired. This might not be a concern unless you purge data by partition.

Be mindful that the last partition if a RANGE LEFT function can never be removed. The implication is that the filegroup of the last partition is permanently part of the partition schemes that use the function. In your case, filegroup Year6FileGroup will always be the last filegroup of the psYearlyPartition_Log scheme, although it can also be used for another partition, such as for year 2016 here.

The partitions after the script is run will be as below and it is expected the last partition will be empty (assuming you have no future year data). You should create the partition for next year before the start of the year to avoid splitting a non-empty partition or avoid jumping through these hoops again.

+------------------+----------------+----------------+----------------+
| Partition Number | Lower Boundary | Upper Boundary |   Filegroup    |
+------------------+----------------+----------------+----------------+
|                1 | NULL           | <= 2012-01-01  | Year1FileGroup |
|                2 | > 2012-01-01   | <= 2013-01-01  | Year2FileGroup |
|                3 | > 2013-01-01   | <= 2014-01-01  | Year3FileGroup |
|                4 | > 2014-01-01   | <= 2015-01-01  | Year4FileGroup |
|                5 | > 2015-01-01   | <= 2016-01-01  | Year5FileGroup |
|                6 | > 2016-01-01   | <= 2017-01-01  | Year6FileGroup |
|                7 | > 2017-01-01   | <= 2018-01-01  | Year7FileGroup |
|                8 | > 2018-01-01   | <= 2019-01-01  | Year8FileGroup |
|                9 | > 2019-01-01   | NULL           | Year6FileGroup |
+------------------+----------------+----------------+----------------+

I generally recommend a RANGE RIGHT function when partitioning on incremental values like datetime because the behavior is more intuitive. See Table Partitioning Best Practices for this and other considerations.

--create identical partition function and scheme
CREATE PARTITION FUNCTION pfYearlyPartition_Log_Staging(datetime)
    AS RANGE LEFT FOR VALUES(
      '20120101'
    , '20130101'
    , '20140101'
    , '20150101'
    , '20160101'
);
CREATE PARTITION SCHEME psYearlyPartition_Log_Staging
    AS PARTITION pfYearlyPartition_Log_Staging TO (
      Year1FileGroup
    , Year2FileGroup
    , Year3FileGroup
    , Year4FileGroup
    , Year5FileGroup
    , Year6FileGroup
);

--create identical aligned staging table
CREATE TABLE [dbo].[Log_Staging](
      [RefNo] [nchar](7) NOT NULL,
      [DevID] [nvarchar](7) NOT NULL,
      [Time] [datetime] NOT NULL,
      [summary] [float] NULL,
      [staging] [float] NULL,
      [position] [float] NULL,
CONSTRAINT [PK_Log_Staging] PRIMARY KEY CLUSTERED
(
      [RefNo] ASC,
      [DevID] ASC,
      [Time] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 96)
) ON psYearlyPartition_Log_Staging(Time);

--switch last partition into staging table
ALTER TABLE dbo.Log
    SWITCH PARTITION $PARTITION.pfYearlyPartition_Log('20170101')
    TO dbo.Log_Staging PARTITION $PARTITION.pfYearlyPartition_Log_Staging('20170101');

--create new partitions for main table (assuming filegroups already exist for years 7 and 8)
ALTER PARTITION SCHEME psYearlyPartition_Log
    NEXT USED Year6FileGroup;
ALTER PARTITION FUNCTION pfYearlyPartition_Log()
    SPLIT RANGE('20170101');
ALTER PARTITION SCHEME psYearlyPartition_Log
    NEXT USED Year7FileGroup;
ALTER PARTITION FUNCTION pfYearlyPartition_Log()
    SPLIT RANGE('20180101');
ALTER PARTITION SCHEME psYearlyPartition_Log
    NEXT USED Year8FileGroup;
ALTER PARTITION FUNCTION pfYearlyPartition_Log()
    SPLIT RANGE('20190101');

--we must specify IGNORE_DUP_KEY=OFF in order to use DROP_EXISTING_ON here
--but, since the original table PK index definition is unchanged, that's a non-issue
CREATE UNIQUE CLUSTERED INDEX [PK_Log_Staging] ON dbo.Log_Staging
(
      [RefNo] ASC,
      [DevID] ASC,
      [Time] ASC
)
WITH (DROP_EXISTING=ON, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 96)
ON psYearlyPartition_Log(Time);

ALTER TABLE dbo.Log_Staging
    SWITCH PARTITION $PARTITION.pfYearlyPartition_Log('20170101')
    TO dbo.Log PARTITION $PARTITION.pfYearlyPartition_Log('20170101');
ALTER TABLE dbo.Log_Staging
    SWITCH PARTITION $PARTITION.pfYearlyPartition_Log('20180101')
    TO dbo.Log PARTITION $PARTITION.pfYearlyPartition_Log('20180101');
ALTER TABLE dbo.Log_Staging
    SWITCH PARTITION $PARTITION.pfYearlyPartition_Log('20190101')
    TO dbo.Log PARTITION $PARTITION.pfYearlyPartition_Log('20190101');


I might be way off because the question is extremely vague, but you could try something like this:

DO $$
DECLARE
x character varying[] := SELECT name FROM abc;
z record;
BEGIN
FOR z IN x
LOOP
    SELECT decode(z, 'base64');
EXCEPTION
    WHEN SQLSTATE '22023' THEN encode(z, 'base64');
END LOOP;
END; $$


SQL Server cannot import what it cannot see. The reason that you can import it via SSMS is because it opens a Windows File Dialog box, which uses your permissions and not SQL Servers to find the file. If you must import the file then the only way you can do this is to make the file accessible to SQL Server.

How you decide to do this is up to you, whether it be through File Shares, the NET USE command or simply by copying the file over to the server that SQL sits on. You may not be able to do any of these, but somebody in your organization can - if this is a regular thing you should also talk to them about arranging either a SQL Job or a scheduled task whichever is easier to do.


Both of these guys authored several books, but I'm going to link to their blogs in case you want something more immediate.

Louis Davidson: http://drsql.org
Paul Nielsen: http://www.sqlserverbible.com/


The whole code of your trigger is weird. Why checking if SELECT COUNT(*) FROM INSERTED?, I mean, if the trigger is running is because you inserted some row on the table. Then, you are trying to assign posibly multiple values on a variable, and joining with a CTE when it really doesn't seem necessary.

The code seems that it can be replaced with just:

CREATE TRIGGER [dbo].[SENDTXT_MSG] ON [dbo].[db_log] after
INSERT AS
BEGIN

INSERT INTO test(test)
SELECT object_name
FROM INSERTED
WHERE msg_type = 'error';

END


Backup errors after TDE certificate expired and new cert was created with AlwaysOn

So after upgrading to SP1 CU7 the backups started working again with TDE.


Does MySQL's MyISAM use cache before insert?

MyISAM does not cache data at all. It caches only indexes ( See my post What are the main differences between InnoDB and MyISAM? )
You could just set key_buffer_size to something absurbly small, like 8 (the minimum allowed). You may as well disable the query cache while you're at it (Setting query_cache_size to 0).
Just add these lines to /etc/my.cnf
[mysqld]
key_buffer_size = 8
query_cache_size = 0

and run service mysql restart
This will effectively disallow caching anything for MyISAM.
Give it a Try !!!
UPDATE 2013-02-19 13:37 EDT
You just commented:

Also if I understand correctly , you want to say InnoDB use Both INDEX AND Data in memory , so it should have faster speed than MyISAM , but I saw MyISAM insert speed is 10 time faster than InnoDB, why ?

Believe it or not, it is possible to tune MyISAM to outperform InnoDB

Jul 06, 2012 : MySQL table with 100,000 records queried often
May 03, 2012 : Which is faster, InnoDB or MyISAM?
Sep 20, 2011 : Best of MyISAM and InnoDB


Concerning the sort performance in the query, there is this:

Sort Method: external merge Disk: 58480kB

Meaning, there is not enough work_mem to sort in RAM. The operation spills to disk, which is typically a lot more expensive. If you have more RAM to spare, increase the setting for the current session (only!).

See:

Improving sort performance in GROUP BY clause

Check with SHOW work_mem;, then add 100 MB to the number you get and use it below instead of '???MB'. If you still see mention of "disk" in the EXPLAIN output, add more.

But there is more: You create the new table with:

CREATE TABLE temp_table (like main_table including all);

INCLUDING ALL, as the wording suggests, also includes constraints and indexes. Obviously, at least a PK constraint as indicated by cals_pkey in the EXPLAIN output. I assume a column:

id bigserial PRIMARY KEY

It's a lot cheaper to create an index on a filled table than to update it incrementally. The index is also typically better optimized. So you should drop the PK (and all other indexes and constraints) before you write to the table, and recreate them afterwards.

Also, dropping and re-creating the id column on a huge table is needlessly expensive. Create new values for id on the fly with row_number() (do you really need to renumber?). Then update the associated SEQUENCE accordingly.

Related:

Copy table with separate sequences for serial columns
How to copy structure and contents of a table, but with separate sequence?

Assuming ...

you need to renumber all rows
no concurrent load on the table
parsing_timestamp is a typo in the question and should really be the same column timestamp. Else, adapt accordingly.

... the operation could look something like this:

BEGIN;

SET LOCAL work_mem = '???MB';  -- see above

CREATE TABLE temp_table (LIKE main_table INCLUDING ALL);

ALTER TABLE temp_table DROP CONSTRAINT temp_table_pkey;  -- before!
-- drop more indexes or constraints?

INSERT INTO temp_table
      (id                  , listing_id,date,available,price,timestamp)
SELECT row_number() OVER (), listing_id,date,available,price,timestamp
FROM  (
   SELECT listing_id,date,available,price,timestamp  -- No parentheses around SELECT
   FROM   main_table c
   WHERE  NOT EXISTS (SELECT FROM update WHERE id = c.id);

   UNION ALL
   SELECT listing_id,date,available,price,timestamp  -- parsing_timestamp ???
   FROM   update
   ) sub

DROP  TABLE main_table CASCADE;
DROP  TABLE update CASCADE;

ALTER TABLE temp_table RENAME TO main_table;
ALTER TABLE main_table ADD CONSTRAINT main_table_pkey PRIMARY KEY (id);  -- after!
-- recreate more indexes or constraints?

SELECT setval(pg_get_serial_sequence('main_table', 'id'), max(id)) FROM main_table;  -- reset sequence

COMMIT;

Also, some more of the advice for "Populating a Database" in the manual may well apply to your case. Maybe more maintenance_work_mem, maybe ANALYZE or VACUUM ANALYZE afterwards, ... Have a look.


Combine individual sensor values into an overall value with correction factors

You could do the following, provided that your sensor 6511 takes measurements reliably every 30 minutes (use instead the one that is most reliable). This will calculate measurements based on the recordings of that primary sensor used (in the main FROM, in this case 6511), show the exact date and time of such recording, also show an adjusted time in 30 min slots, and use any available measurements for all other sensors, whichever latest available up to 10 minutes after the primary measurement:

SELECT "date",
       DATE("date") + INTERVAL round((UNIX_TIMESTAMP("date") - UNIX_TIMESTAMP(DATE("date")))/1800) * 30 MINUTE AS adj_date,
       (measured_value * -0.106830613) +
       (SELECT measured_value * 0.065201457
        FROM Measurements
        WHERE stream_id = '6512' AND "date" < (s1."date" + INTERVAL 10 MINUTE)
        ORDER BY "date" DESC LIMIT 1) +
       (SELECT measured_value * 0.335456161
        FROM Measurements
        WHERE stream_id = '6513' AND "date" < (s1."date" + INTERVAL 10 MINUTE)
        ORDER BY "date" DESC LIMIT 1) +
       (SELECT measured_value * -0.248569888
        FROM Measurements
        WHERE stream_id = '6514' AND "date" < (s1."date" + INTERVAL 10 MINUTE)
        ORDER BY "date" DESC LIMIT 1) +
       (SELECT measured_value * 1.423460009
        FROM Measurements
        WHERE stream_id = '6515' AND "date" < (s1."date" + INTERVAL 10 MINUTE)
        ORDER BY "date" DESC LIMIT 1) + -2.113591555 AS "Corrected value"
FROM "Measurements" AS s1
WHERE stream_id = '6511';

Ensure you have an index by stream_id, "date"!


How to sum of column by depending other column in my sql?

SELECT
t1.code,
COUNT(*) AS total_rows,
SUM(t1.data->'$.chart."21"') AS total_a
FROM yourTable AS t1
JOIN (SELECT code, MAX(updateid) AS updateid
      FROM yourTable
      GROUP BY code) AS t2 ON t1.code = t2.code
                          AND t1.updateid = t2.updateid
GROUP BY t1.code
ORDER BY t1.code;


How can I return top 10 rows from each state

In case of postgresql:

You didn't write which version you have from postgresql. You can use LATERAL JOINs (available from 9.3) or window functions.

Example for window function:

select *, row_number() over (partition by category_id order by [whatevercolumn]) as rownum
 from records r
 join category c on c.id = r.category_id
 where c.id IN (1, 2, 3, 4) -- If you want to filter
) a
where rownum <= 10
order by category_id;

Example for LATERAL JOIN:

select r.* from category
join lateral (
    select * from records
    where category_id = category.id
    order by [whatevercolumn]
    limit 10
) r on true
where category.id IN (1, 2, 3, 4) -- If you want to filter
order by category.id;

The performance difference can be significant. For more info you can take a look at this benchmark: Group by limit per group in PostgreSQL

In SQL Server:

I'm not a user of SQL server but as far as I know it supports both. The syntax might be a bit different but the same idea applies.