Standard process during a disaster In our environment we have a 2-node Failover cluster instance and log shipping for disaster recovery. Failover cluster is the primary site and log shipping is on DR. 2 node: same data center; log ship is on another data center.

What is the best practice when a disaster strikes, failover with data loss or try to bring the primary up or else failover?

There is no RPO/RTO defined. I am the only one who manages. With log shipping my maximum data loss is 5 minutes. It will take 30 minutes to failover.

"When disaster strikes" isn't a one-size-fits-all situation. The HADR worksheet from brentozar.com includes multiple categories, each with specific failure scenarios. Then, RPO & RTO are defined for each of those failure scenarios. I'd recommend you use this worksheet, or make one similar to it.

Next, you will want to develop a DR Plan for what you will do when those scenarios arise. DR Plans are organization-wide documents that involve not just data, but also detail failing over applications, infrastructure, etc. This is especially important for a failover to DR that involves data loss. You will want to pre-define who needs to be involved, what actions are needed for failover, and how long it takes to perform those actions. When data loss is involved, one of the most important actors in your DR Plan is identifying who will make the decision to accept data loss & fail over to your DR site. This is usually a management or business decision.

In the event one of these scenarios happens (ex, a Storage outage), that worksheet and DR Plan will guide you on how long you work to resolve the failure situation in your primary data center before beginning failover to DR.

Lets look at an example:

Let's say your RTO is 4 hours during a widespread outage. Your RPO is 20 minutes of data loss, and you are using 10-minute log shipping to your DR site, and this is working as designed. Your DR Plan estimates failover will take 90 minutes. The Director of IT and CTO are designated as being allowed to authorize failover to DR with data loss.

A complete power outage affects your primary data center, and all systems are completely offline. Because your log shipping is up to date, failing over would cause only 10 minutes of data loss--well within your 20-minute RPO goal. You can wait up to 2.5 hours for the power to come back online, and still meet your RTO goal.

The CTO or Director of IT would be charged with monitoring the situation and making the decision if and when to fail over to DR. If they choose to fail over at or before the 2.5 hour mark, you should meet your RTO target. If they wait longer, you would miss your RTO target--that's ultimately a business decision, not a technical one. As a DBA, your job is to provide enough information about the failure (and recovery/failover process) so the designated person can make that decision.


Large inserts breaking indexed queries Very similar to https://serverfault.com/questions/163442/large-insert-update-in-table-causes-timeout-while-sql-server-re-arrange-indexes only I am not using SSIS with only occassional large (predominently annual) inserts from another staging table in a stored procedure and the target table is partitioned with data being written to an existing partition. All indexes are aligned.

I've tried setting AUTO_UPDATE_STATISTICS_ASYNC ON but that doesn't seem to help. Queries are still blocked for about 10 minutes while the indexing catches up even when they should use partition elimination and not hit the inserted data.

I'd rather have a fast but stale result than a time-out.

Even after

ALTER DATABASE [MyDB] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [MyDB] SET AUTO_UPDATE_STATISTICS_ASYNC ON
GO

sp_helpdb MyDB.status

still reads

  Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
  Recovery=SIMPLE, Version=655, Collation=Latin1_General_CI_AS,
  SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics,
  IsFullTextEnabled

Shouldn't it have something to indicate that async is on?

Is there anything else I can do to make sure queries aren't slowed down?

I've found the problem. It's because one of the columns in the partitioned table has a varchar(max). Limiting it to varchar(120) resolves to problem. It seems ALTER DATABASE [MyDB] SET AUTO_UPDATE_STATISTICS_ASYNC ON requires the online index build.

I should have said I'm on 2008 R2

Thanks though.


Cpu% usage alert is not working for 'alert if counter=raises above' I am thinking to create an alert for Cpu usage when it crosses certain value. So i configured one alert for this and attached those screen shots here

But the above alert is not working mail is not triggering when it crosses 50%. Is any thing wrongly i configured?
And same thing i have tested with Alert if counter='falls below' then mail was triggered and it worked perfectly.
Please help me in this any thing i want to change.

I am only able to get SQL Server 2012 to fire an alert based upon the "Workload Group Stats -> CPU Usage %" if I enable a dummy Resource Governor classifier function that classifies all connections into the default Resource Governor group.

I used the following classifier function, with the associated code to enable that function:

USE master;
GO
CREATE FUNCTION dbo.fnDummyClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    RETURN N'default'; --every session is assigned to the "default" pool.
END
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = [dbo].[fnDummyClassifier]);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

I created an alert using this code, that alerts whenever CPU usage rises above 10%:

EXEC msdb.dbo.sp_add_alert @name=N'CPUUsage'
        , @enabled=1
        , @delay_between_responses=0
        , @include_event_description_in=1
        , @performance_condition=N'Workload Group Stats|CPU usage %|default|>|0.1'
        , @job_id=N'00000000-0000-0000-0000-000000000000';
GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'CPUUsage'
    , @operator_name=N'Max'
    , @notification_method = 1;
GO

I then used this query to force CPU usage in a session that is connected to SQL Server after the classifier function has been enabled, and the Resource Governor has been reconfigured:

SELECT CRYPT_GEN_RANDOM(10)
FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v1(num)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v2(num)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v3(num)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v4(num)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v5(num)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v6(num)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v7(num)

This query shows the status of the alert, as well as when it was last fired:

SELECT AlertName = sa.name
    , Enabled = sa.enabled
    , LastOccurrence = CASE WHEN sa.last_occurrence_date > 0
        THEN dbo.agent_datetime(sa.last_occurrence_date, sa.last_occurrence_time)
        ELSE NULL
        END
    , OccurrenceCount = sa.occurrence_count
    , PerformanceCondition = sa.performance_condition
FROM dbo.sysalerts sa
WHERE sa.name = 'CPUUsage';


Is there anyway to make sql server search the master database for SQLCLR stored procedures before the current one? For T-SQL stored procedures with the prefix sp_, SQL Server will search for and user a procedure in the master database before the current one. This does not appear to be the case with a SQLCLR stored procedure I created  with SQL Server 2010 using Visual Studios built in deploy. The database server is SQL Server 2008 R2 (SP1).

I execute this script:

SELECT name
from master.sys.procedures
WHERE type_desc='CLR_STORED_PROCEDURE' and schema_id = 1

USE tempdb
PRINT 'USING tempDb'
EXEC sp_RAISERROR_CaughtDemo;
GO
USE master
PRINT 'USING master'
EXEC sp_RAISERROR_CaughtDemo
GO
--SELECT * from sys.assembly_modules

And the stored procedure will only execute with an unqualified name from the master database:

name
----------------------------------
sp_RAISERROR_CaughtDemo
sp_RAISERROR_UncaughtDemo

(2 row(s) affected)

USING tempDb
Msg 2812, Level 16, State 62, Line 5
Could not find stored procedure 'sp_RAISERROR_CaughtDemo'.
USING master
RAISERROR() Caught Severity 0
RAISERROR() Caught Severity 1
Msg 50000, Level 1, State 1
RAISERROR() Caught Severity 2
Msg 50000, Level 2, State 1
RAISERROR() Caught Severity 3
Msg 50000, Level 3, State 1
RAISERROR() Caught Severity 4
Msg 50000, Level 4, State 1
RAISERROR() Caught Severity 5
Msg 50000, Level 5, State 1
RAISERROR() Caught Severity 6
Msg 50000, Level 6, State 1
RAISERROR() Caught Severity 7
Msg 50000, Level 7, State 1
RAISERROR() Caught Severity 8
Msg 50000, Level 8, State 1
RAISERROR() Caught Severity 9
Msg 50000, Level 9, State 1
RAISERROR() Caught Severity 10
Msg 50000, Level 11, State 1, Line 1
RAISERROR() Caught Severity 11

The code for those procedures is as follows:

[SqlProcedure(Name = "sp_RAISERROR_UncaughtDemo")]
public static void RaiserrorUncaught()
{
    short i = 0;
    using (var cn = new SqlConnection("context connection=true"))
    using (var cmd = cn.CreateCommand())
    {
        cn.Open();
        cmd.CommandText = "RAISERROR('RAISERROR() Uncaught Severity %d', @i, 1, @i)";
        cmd.Parameters.Add("@i", SqlDbType.SmallInt);
        while (true)
        {
            cmd.Parameters["@i"].Value = i++;
            SqlContext.Pipe.ExecuteAndSend(cmd);
        }
        cn.Close();
    }
}

[SqlProcedure(Name = "sp_RAISERROR_CaughtDemo")]
public static void RaiserrorCaught()
{
    try
    {
        short i = 0;
        using (var cn = new SqlConnection("context connection=true"))
        using (var cmd = cn.CreateCommand())
        {
            cn.Open();
            cmd.CommandText = "RAISERROR('RAISERROR() Caught Severity %d', @i, 1, @i)";
            cmd.Parameters.Add("@i", SqlDbType.SmallInt);
            while (true)
            {
                cmd.Parameters["@i"].Value = i++;
                SqlContext.Pipe.ExecuteAndSend(cmd);
            }
            cn.Close();
        }
    }
    catch(SqlException) {}
}

Is there anything I can do to make SQL Server search in the master database to find this stored procedure? I tried sys.sp_MS_marksystemobject to no avail.

I don't know a solution (and I don't know if the CLR scaffolding was ever designed to mimic the functionality you're talking about), but one workaround could be to create a T-SQL stored procedure in master that serves as a wrapper to relay the call to the CLR version. Marking it as a system object shouldn't be necessary, as long as a stored procedure with the same name doesn't exist in the user database.

EDIT - just proved that this works fine, as much for myself as anything. First I created your assembly and added the procedure necessary to repro (I'm not going to include all the bits here for brevity):

USE master;
GO
CREATE ASSEMBLY [Justin]
    AUTHORIZATION [dbo]
    FROM 0x4D5A900003000000040... lots of data here ...;
GO
ALTER ASSEMBLY [Justin]
    DROP FILE ALL
    ADD FILE FROM 0x4D6963726F... even more data here ...;
    AS N'Justin.pdb';
GO
CREATE PROCEDURE [dbo].[sp_RAISERROR_CaughtDemo]
AS EXTERNAL NAME [Justin].[StoredProcedures].[RaiserrorCaught]
GO

Then I created a wrapper stored procedure, making sure to fully qualify the CLR procedure:

CREATE PROCEDURE dbo.sp_RAISERROR_CaughtDemo_Wrapper
AS
BEGIN
    SET NOCOUNT ON;
    EXEC master.dbo.sp_RAISERROR_CaughtDemo;
END
GO

Then I adjusted your repro code to call the wrapper from tempdb instead:

USE tempdb;
PRINT 'USING tempDb';
EXEC sp_RAISERROR_CaughtDemo_wrapper;
GO

Results:

USING tempDb
RAISERROR() Caught Severity 0
RAISERROR() Caught Severity 1
Msg 50000, Level 1, State 1
RAISERROR() Caught Severity 2
Msg 50000, Level 2, State 1
RAISERROR() Caught Severity 3
Msg 50000, Level 3, State 1
RAISERROR() Caught Severity 4
Msg 50000, Level 4, State 1
RAISERROR() Caught Severity 5
Msg 50000, Level 5, State 1
RAISERROR() Caught Severity 6
Msg 50000, Level 6, State 1
RAISERROR() Caught Severity 7
Msg 50000, Level 7, State 1
RAISERROR() Caught Severity 8
Msg 50000, Level 8, State 1
RAISERROR() Caught Severity 9
Msg 50000, Level 9, State 1
RAISERROR() Caught Severity 10
Msg 50000, Level 11, State 1, Line 1
RAISERROR() Caught Severity 11

So this shows that you should be able to use a wrapper like this to be able to call CLR procedures, unreferenced, from other databases.

(However, I will suggest that in general this shouldn't be a goal - you should be properly defining references with three-part names where applicable.)

I'll confess I did make one change to your stored procedure though, to prevent Visual Studio from whining at me. I changed:

while (true)

To:

while (i <= 11)

But of course this change had nothing to do with the scoping issue.


Dropping unique constraint on PostgreSQL table taking 10+ hours We have a PostgreSQL database with a fairly large table (~50 million rows). The table has a unique constraint across several columns, each of which is indexed. For context, I recently deleted about half the rows in the table -- old data. Now I want to simply remove the multi-column unique constraint, so I executed this SQL:

alter table my_big_table drop constraint my_constraint_name

However, I started the command 10+ hours ago, and it's still running with no sign of abating. Why would dropping a constraint be taking so long? Is there anything I'm missing, or any way to perform actions like this with some kind of progress or status indication?

(I'm using PostgreSQL 9.0 on a Windows 64-bit system, and I generally connect to it using pgAdmin, though I can use the command-line client or connect programmatically too.)

Step one - go into task manager, have a look and see if anything is actually happening. If you can't see a lot of cpu and/or disk bandwidth then it's not doing anything.

Step two - check what PostgreSQL says is happening. There are two system views useful here: pg_stat_activity and pg_locks. See what's in both. The database/relation numbers in pg_locks are internal OIDs - try SELECT OID,* FROM pg_class to see which tables/indexes are being locked.

My guess is that the system is idle and it's just waiting for an exclusive lock on the table in question and something else is blocking it. The two system views should show you what is happening though.


designing database where users can have multiple positions I am trying to determine the best model for the following case:

There are multiple offices
Each office has multiple staff, each of which have a role (chief, deputy chief, etc)
Each office also has many projects
Each project is managed by one or more staff (project manager, assistant project manager, deputy project manager, etc)

so, for example, an office has an office head, deputy head, etc.  They also have project managers (which could be the same as office head) and sub managers that run projects.

I want to have a design where we can tag office employees to an office as well as tag employees that are project managers to a project.  Here is what I have:

I have an office table
I have a project table
I have a office_project table

I have an employee table
I have a role table

I have an office_employee table
I have an employee_role table

The question is, what is the best way to tie the project table to the employee table?  I assume I should have one employee table, or does it make sense to have a table to employees and a table for project managers, each with their own roles table?  It doesn't seem that way. And, the way it is now, I can tie an office to a project, and see all the project employees, but not all office project managers would be associated with a given project.

If you need an ERD design of what I have, please let me know.

thanks

I would suggest that instead of having an explicit office-project relationship that you set up an employee-project relationship and allow the existing office-employee relationship to take care of this problem naturally.

From your question, it seems as though many employees can be associated with a single project and a single employee can be associated with many projects. In this case, you'll want a linking table of the form:

    employee_project
    -------------------
    employee_id | project_id

In the event that the relationship is more complex- say, that an employee participates in a project via one of their roles, you may want to move the relationship to refer to that point, for example:

    project_employee_role
    -------------------
    employee_role_id | project_id

if there is a surrogate employee_role_id

or

   project_employee_role
   -------------------
   employee_id | role_id | project_id

If not.


Is a REINDEX required after CLUSTER? I'm considering using CLUSTER to reorder a table by an index. I understand that this recreation of the table data makes all the existing indexes either bloat or be useless. I've seen some indications that a REINDEX is required after a CLUSTER. I've found other references that indicate that CLUSTER does a REINDEX. The Official Documentation says nothing at all about REINDEX being part of CLUSTER or required (Although it does suggest running ANALYZE after the CLUSTER)

Can anyone definitively (i.e. with some sort of reference to official docs) say whether or not a REINDEX is required after a CLUSTER?

You do not need to reindex, because CLUSTER effectively does it for you.

More specifically, CLUSTER locks the source table then creates a new copy of it ordered according to the target index. It creates indexes on the new copy then replaces the old table and indexes with the new ones.

Note that this is also true of VACUUM FULL in 9.0+.

If you've been seeing discussion suggesting that CLUSTER bloats indexes it could be people who're assuming that CLUSTER works like pre-9.0 VACUUM FULL. You might also be seeing and misreading discussions that mention index bloat caused by the old VACUUM FULL implementation and suggesting CLUSTER as an alternative.

This is implied in the documentation:

  a temporary copy of the table is created that contains the table data
  in the index order. Temporary copies of each index on the table are
  created as well. Therefore, you need free space on disk at least equal
  to the sum of the table size and the index sizes

What it doesn't say, but should, is that those temporary copies then replace the original table. (Bold mine).


Auto Disable a SQL Server User I have a SQL Authentication User: JOHNDOE

I would like this account to auto-expire or auto-disable after X days or at a certain date/time. Any suggestions or best practices to do this?

You could setup a job that reads from a table containing a list of logins and their expiration dates. For every login that is expired in this table it could just drop it. There you could also automatically deal with users associated with and databases own by this login.


Compare table content from different PostgreSQL 9.3 DBs The problem

How do I efficiently and idiomatically compare the content of two tables, each in a different DB?

I have 3 DBs (the classic dev, stg and prod). Each DB has a few tables which should be exactly the same regardless of environment, like countries.

I would like to write a script that would compare these tables and alert me when there's a mismatch.

Characteristics

There are less than 10 tables to compare
The tables are not very large - at most ~100K of data
The script is executed from a Jenkins CI machine

Current solution

Currently, I am using pg_dump and comparing the files. I wonder if there is a more idiomatic approach.

For such a small amount of data getting the whole lot and performing a text comparison as you are is probably the way I would go too.

For larger data and/or data on remote sites that could be far too cumbersome so you might instead want to compare a checksum of the data in each table with something like:

SELECT ENCODE(
              DIGEST(
                     STRING_AGG(
                                COALESCE(<col1>,'NULL')
                               || COALESCE(<col2>,'NULL')
                               || COALESCE(<col3>,'NULL')
                               ,','
                     ),'sha512'
              ),'base64'
       ) AS table_checksum
FROM <table> ORDER BY <pk>

That way you are reading all the data but only transmitting the resulting checksum.

Also if your databases are on the same machine (unlikely in the case of dev/stg/prod, but...) you could use a cross-database query (see this question) to scan for differences.

In all the above cases you are going to be scanning the whole table(s) in both places.

CAVEAT: I'm a SQL Server person generally and I've translated something I've done there to postgres, the above syntax may need a little work!


Can I use pg_restore to restore data to subset of tables? I am trying to restore data in to select tables of a database (9.3.5).  This is a data refresh from production to test, while maintaining some table data in the test database (like application users table).  I first truncate the tables that I plan to refresh and then I can successfully restore 1 table using this command:
pg_restore -a -d ACMETEST -n public -v -U acme -t hardware /var/acme/backups/restore/ACMEDatabase.bak
, which gives the following response followed immediately by a command prompt: !pg_restore: connecting to database for restore
pg_restore: processing data for table "hardware"
pg_restore: setting owner and privileges for TABLE DATA hardware

Queries show that the data successfully loaded.

The man page doesn't indicate that you can restore data in to multiple tables at once by using an array as an input to -t but I assumed it was possible so tried this:
pg_restore -a -d ACMETEST -n public -v -U acme -t hardware,location /var/acme/backups/restore/ACMEDatabase.bak
The result of the second command is one line that says:   and then it dumps me right back to the command prompt as if the command is complete but none of the data is loaded.

I did look at the -L option to use a listfile but I'm trying to create a repeatable process that can be used by many people on databases across the country so I don't see the process of commenting out tons of lines from a listfile for each restore as having realistic chances of success. Do I have to execute the first command for every individual table I want to restore or is there some way I can feed an array of tables to this command?

From pg_restore's documentation you can see:

  -t table
  --table=table

Restore definition and/or data of named table only.
Multiple tables may be specified with multiple -t switches.
This can be combined with the -n option to specify a schema.

Highlighting "Multiple tables may be specified with multiple -t switches", which means that you can use -t switch multiple times, and your example would work as:

pg_restore -a -d ACMETEST -n public -v -U acme -t hardware -t location /var/acme/backups/restore/ACMEDatabase.bak

As seen, I just used -t hardware -t location instead of -t hardware,location.


Why does a log file grow even when transaction is not commited? Below is what I did:

Created database in simple recovery mode.
Created table.
Began tran and inserted so many rows to exceed size of the log file.
Committed the transaction.

Before step 4 I noticed that log file grew, which confused me. Why did that happen?

According to MSDN:

  For each logical write, a transaction log record is inserted in the log cache that records the modification.

And then:

  Log records are written to disk when the transactions are committed.

So how do you explain the fact that the log file is growing when the data is being inserted, even when a transaction is not committed?

As I understand, uncommitted log records should be stored in the log buffer, not on disk.

Transaction log records are flushed as log buffers become full, which is why you observed growth before the final COMMIT. The buffer is also flushed when a durable transaction is committed, which guarantees durability.


Database distribution in AlwaysOn Good morning all,
I have an on Always On SQL server configuration where transactional replication is configured
If I open SSMS I find that the distribution database is set to AlwaysOn on then I cannot find the synchronized word.

thanks

I would not rely on Object Explorer (or the AG dashboard) in SSMS to determine the status. Use the DMVs directly (or create your own dashboards against them). Something like this:
SELECT
  d.name,
  AGName = COALESCE(ag.name, 'N/A'),
  drs.synchronization_state_desc,
  ar.replica_server_name
FROM sys.databases AS d
LEFT OUTER JOIN sys.dm_hadr_database_replica_states AS drs
  ON d.database_id = drs.database_id
LEFT OUTER JOIN sys.availability_groups AS ag
  ON drs.group_id = ag.group_id
LEFT OUTER JOIN sys.availability_replicas AS ar
  ON drs.replica_id = ar.replica_id;

I suspect this specific limitation is just that they didn't add the display sync status logic into the code path that renders the System Databases node. The fact that is it missing is documented here, but without an explanation why:

SSMS does not show Distribution Database as synchronizing/synchronized, when distribution database is added to an availability group.


What's a pathological case where a bitmap filter would not allow the PROBE(Field, IN-ROW) semijoin reduction optimization? I recently learned a performance optimization in SQL Server I did not know about ("Bitmap Filter On (Clustered) Non-Nullable Column Hash Semi-join Reduction via Scan + Probe IN-ROW" - a real mouthful), and am looking for data sets that can defeat my optimization, as this same query will be used in a nearly identical database schema in a different database with different customer use cases.

Specify OPTION(Hash Join, MAXDOP = 8)

MAXDOP effectively tells the query optimizer to look for plans that can exploit parallelism
HASH JOIN is what tells the query optimizer to consider a Bitmap Filter

Non-nullable column filtered on EXISTS clause

When the column is non-nullable, the semi-join (EXISTS clause) guides the optimizer to choose a clustered index scan with a predicate that says something like PROBE(NonNullableColumn, 'IN-ROW')
Combined with the bitmap filter, the PROBE is highly efficient because it uses a bitmap filter.  This is, in effect, equivalent to a perfect Index Seek.  It's like a bookmark lookup on steroids.

However, in thinking through this cleverly constructed query I just made, I'm a little scared I don't know how to break it and when to expect it to stop working and the cost-based optimizer will choose a different plan.

Suppose I have 3000 unique values for NonNullableColumn, each with 30 million rows binned to those unique values.  In my performance tests so far, I passed in a set containing 15 as a unique test value.  This works great and I'm pretty confident this is the optimal plan.  What happens if I pass in a set containing random numbers - will SQL Server stop using the bitmap filter?  What's the "worst case" scenario here?

I've attached a picture of the query plan to re-inforce what I'm trying to test and how I can defeat (or anticipate) Murphy's Law.

In thinking through "what could go wrong", I found this in SQL Server Docs:

  The Repartition Streams operator (or exchange iterator) consumes multiple streams and produces multiple streams of records. The record contents and format are not changed. If the query optimizer uses a bitmap filter, the number of rows in the output stream is reduced.

and:

  SQL Server uses the Bitmap operator to implement bitmap filtering in parallel query plans. Bitmap filtering speeds up query execution by eliminating rows with key values that cannot produce any join records before passing rows through another operator such as the Parallelism operator. A bitmap filter uses a compact representation of a set of values from a table in one part of the operator tree to filter rows from a second table in another part of the tree. By removing unnecessary rows early in the query, subsequent operators have fewer rows to work with, and the overall performance of the query improves. The optimizer determines when a bitmap is selective enough to be useful and in which operators to apply the filter. Bitmap is a physical operator.

  Showplan Logical and Physical Operators Reference

According to Bitmap Filter (Star Join Query Optimization):

  Bitmap filter doesn’t work if:

  It’s not a hash join
  Out of date stats/missing indexes (cardinality/rows)
  Not enough memory
  Not enough threads (BF only works in parallel query – you need more than 1 CPU)

In my case, 1, 2 and 4 are not problems. 3 is evidently not a problem, at least in my example test. The memory requirement essentially means my StarJoinInfo table (Build table) must be smaller enough to fit in main memory; I wish I had a formula that gave me a upper bounds on how many unique values I could create a BitMap for.

I figured out the answer to my question - sort of.

When you specify MAXDOP(8), the optimized bitmap filter optimization will create 8 hash buckets.  The exact spread of integers across those hash buckets is probably Pearson hashing.  I am curious if anyone in the comments knows the exact hashing technique, though.

Quoting: https://sqlserverfast.com/blog/hugo/2018/05/plansplaining-part-5-bitmaps/

  Simple example: Let’s say that only three stores in our database have less than 30 employees. These stores have StoreKey 17, 38, and 82. The first row the Bitmap operator receives is for StoreKey 17; it hashes the number 17 and the result is 29, so bit 29 in the bitmap is set to one. It then reads the next row, for StoreKey 38; hashes the number 38 which results in 15; and sets bit 15 to one. The third row it reads is for StoreKey 82; due to a hash collision this number also hashes to 29 so it will once more set bit 29 to one (effectively not changing anything). There are no more rows so the end result will be a bitmap with bits 15 and 29 set to one and all other bits set to zero.


Compare Past Records to Most Recent Record Is it possible to structure a single query that would return a 0 or 1 (true/false) by comparing first record to previous records?

Sample records in table:

ProductID    SampleDate    RunFailure    LevelCode
101          20160322      0             2
101          20160321      0             3
101          20160320      1             2
101          20160318      1             2
102          20160317      0             3
102          20160316      0             2
102          20160315      0             1
103          20160314      1             1
103          20160313      0             2
103          20160312      1             3

I need to evaluate the most recent record by ProductID.  In the example above the last record (on 20160322) has a LevelCode of 2.  It needs to be compared to the record on 20160320 because it is the last record that failed with the same LevelCode (so it would ignore the record on 20160318 because it is older than the record on the 20th), therefore, in this case the query would return 1 (or true).

For ProductID 102 the query would return 0 (or false) because the LevelCode on 20160317 is 3 and there isnt an older record that failed with that same LevelCode.

ProductID 103 would return a 0 (false) because on 20160314 the LevelCode is 1 and the last record for that ProductID that failed was on 20160312 was for LevelCode3

Ideally the query would return something like this:

ProductID    IsMostCurrentRunSameLevelAsLastFailedRun
101          1
102          0
103          0

One way would be using CROSS APPLY.

If you identify the most recent record for each product in the first table and then pass the criteria into the cross apply.

SELECT
      ProductId
   ,  MAX(CASE WHEN t1.RunFailure = t2.RunFailure THEN 1 ELSE 0 END) AS IsMostCurrentRunSameLevelAsLastFailedRun
   FROM <SomeTable> t1
   CROSS APPLY (
      SELECT TOP 1
         RunFailure
         FROM <SomeTable> st
         WHERE st.ProductId = t1.ProductId
         AND   st.LevelCode = t1.LevelCode
         AND   st.SampleDate < t1.SampleDate
         ORDER BY st.SampleDate) AS t2
   GROUP BY t1.ProductId

Alternatively, look into the windowing functions. You can do a great deal with them that may be more efficient.


Mysql 8.0.22 master slave replication: why slave is trying to create user? I am following this repo (https://github.com/alexanderfefelov/docker-mysql-replication) to setup one master one slave replication.  I am not able to get it working.  The master seems to be OK, but the slave has the following error:

mysql_slave1.1.ydo3yuvbyibc@64g-480g-node2    | 2020-12-22T23:19:42.284061Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.22'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.
mysql_slave1.1.ydo3yuvbyibc@64g-480g-node2    | 2020-12-22T23:19:42.285009Z 5 [System] [MY-010562] [Repl] Slave I/O thread for channel '': connected to master 'replicator@mysql_master:3306',replication started in log 'log-bin.000023' at position 156
mysql_slave1.1.ydo3yuvbyibc@64g-480g-node2    | 2020-12-22T23:19:42.289356Z 6 [ERROR] [MY-010584] [Repl] Slave SQL for channel '': Error 'Operation CREATE USER failed for 'replicator'@'%'' on query. Default database: ''. Query: 'CREATE USER 'replicator'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19'', Error_code: MY-001396
mysql_slave1.1.ydo3yuvbyibc@64g-480g-node2    | 2020-12-22T23:19:42.289425Z 6 [Warning] [MY-010584] [Repl] Slave: Operation CREATE USER failed for 'replicator'@'%' Error_code: MY-001396
mysql_slave1.1.ydo3yuvbyibc@64g-480g-node2    | 2020-12-22T23:19:42.289442Z 6 [ERROR] [MY-010586] [Repl] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'log-bin.000016' position 3107328

The master already created user replicator during start up, why slave also tries to create this user?
Thanks!

Found the issue.  It was caused by the left over binlog.  I externalized the binlog folder, after delete binlog folder on the host and start again, it worked.


Select records not associated to a specific tag in a many-to-many relation as shown in this sql-fiddle I have a bridge table events_tags representing a many-to-many between events and tags.

I need the fastest way to find all events not associated to a given tag t.

The first query in the fiddle shows the incorrect solution where a an event assigned to t and another tag is included in the result set.

The second query makes a anti-join (learned this from an answer by ypercubeᵀᴹ) and is correct, but very slow in my real schema, where it would return 18K rows from the bridge table in ~130ms on the hardware on which our application runs.

I need this functionality in an endpoint of a RESTful API, whose total response time must stay <300ms.

Is there a faster approach I can use or am I limited to the fact that the "real" query has to deal with a derived table of 18K rows?

EDIT
I failed to mention that I will then combine this (as a subquery) with another query selecting only events matching some criteria.
I am not selecting 18K events :)

P.S.: let me know if any other detail on the problem is required

Thanks

SELECT DISTINCT events.id, events.title
  FROM events
  JOIN events_tags et1
         on events.id = et1.event_id
        and not exists ( select 1
                           from events_tags et2
                          where et2.event_id = et1.event_id
                            and et2.tag_id IN (1) );

And why a iden PK in events_tags?  Drop that.
Just have a composite PK of tag_ID, event_id in that order.

This might be faster but I doubt it

SELECT DISTINCT events.id, events.title
  FROM events
  JOIN events_tags et1
         on et1.event_id = events.id
        and et1.tag_id not IN (1)
        and not exists ( select 1
                           from events_tags et2
                          where et2.event_id = et1.event_id
                            and et2.tag_id IN (1)
                       );

I don't think MYSQL supports except

SELECT events.id, events.title
  FROM events
  JOIN (   select event_id
           from events_tags
           where tag_id not IN (1)
         except
           select event_id
           from events_tags
           where tag_id     IN (1)
       ) tt
    on tt.event_id = events.id


What is the best way to design one-to-many relationship with null value? What is the best way to design one-to-many relationship with nullable FK?

I have two tables:

TableA:
  Id PK
  Name

TableB:
  Id PK
  TableARef FK
  Name

TableB has reference to TableA but this reference can be null. What is the best way to design that? I want to avoid nulls in my database (or maybe I shouldn't). It is a good practise to add join table to one-to-many relationship? Like this:

TableA_TableB:
  Id PK
  TableARef FK
  TableBRef FK

I know that in many-to-many is a good solution but I couldn't find enough data about this solution in one-to-many relationship.

I would not model a one->many relationship as a many<->many one, with a junction table as you describe, because you are allowing more in your data model than the system it is modelling. You will need to add extra constraints, either in the DB if possible or other application layers, to enforce the more limited relationship and/or add code to detect and deal with breaches of this constraint that happen due to bugs.

Is there a particular reason you want to disallow NULLs for a value that seems to naturally call for them?

If so then you could create "partial junction table" or "extension table" (I'm making that terminology up, I don't know off the top of my head if this is a pattern that has a commonly accepted name):

TableA_TableB:
TableARef FK, NOT NULL
TableBRef FK, PK

this way the FK ensures that you have a one->many relationship as each ID from TableB can only appear once while rows from table A can be referred to by many entities in TableB. The empty condition then becomes that the row does not exist. This avoids the NULL needing to be "stored" but you now have an extra table to join in every time you need to use this field.

If you want to have a surrogate key as your PK, as in the example in the question, then the definition becomes:

TableA_TableB:
Id        PK
TableARef FK, NOT NULL
TableBRef FK, UNIQUE

though that would be wasteful IMO unless you think that you might in later revisions change the relationship to be many<->many where it is valid for the same two entities can relate to each other more than once.


concatenating columns from multiple unrelated 1-row resultsets Assume I have these two queries in SQL Server 2014, both of which return one row from unrelated tables:

SELECT SUM(A) A, SUM(B) B FROM X
SELECT SUM(C) C, SUM(D) D FROM Y

I'd like to combine these queries into a single resultset that contains A, B, C, D.

What's a good way to do this within a single query, as opposed to multi-query solutions like selecting the results into and out of scalar variables?

Two subqueries?

SELECT Q1.A, Q1.B, Q2.C, Q2.D
FROM (SELECT SUM(A) A, SUM(B) B FROM @X) Q1(A, B)
CROSS APPLY (SELECT SUM(C) C, SUM(D) D FROM @Y) Q2(C, D);


Connect two remote oracle databases as a schemas on each other This Question is about Oracle Database.
I have two remote databases DB_1 and DB_2, and I have two schemas on each database:
Schema DB_1 and Schema DB_2 on database DB_1, and Schema DB_1 and Schema DB_2 on database DB_2.
I want to reach database DB_2 Schema DB_2 from database DB_1 Schema DB_1.
For example: when I try this query  in DB_1 from  schema DB_1:
SELECT * FROM DB_2.example_table
I got an error because there is not table "example_table" in DB_2 Schema on the DB_1 database.
But, when I try the same query on DB_2 Schema DB_2, I got the correct result (some data).
So, I need to tune my databases in some way so I can SELECT * FROM DB_2.example_TABLE from DB_1 Database.
But, there is another problem, I don't have permission to CREATE LINK.
However, when I query SELECT * FROM ALL_LINKS; on my production database DB_1 schema DB_1 - there are no links at all. So, I need to figure out which way my production Databases was linked (unfortunately my colleagues don't know either). On my production database DB_1 schema DB_1 I can fetch data with the query:
SELECT * FROM DB_2.example_table
And the data I got is the same if I try to query the same on DB_2 production database DB_2 schema.
But there are no public\private Links on DB1/DB2 production databases at all. Is there another way to create a "connect" between my databases? (I queried SELECT * FROM ALL_LINKS from DB_1 schema DB_1, is this possible so I can fetch some other data if I try the query on DB_1 schema DB_2?)
Thanks in anticipation!

Several issues, in no particular order.
Your SELECT syntax is wrong. To select a table from another database the syntax is
select some_columns from remote_table@remote_db;

Note the "@remote_db".  Here 'remote_db' refers to an entry in the local database's tnsnames.ora file, which in turn resolves to the remote db in question.   CORRECTION:  Here 'remote_db' is the name of the database link.
Second, you say you have no permission to create a db link. Well, someone is going to have to create one.  That is the mechanism by which one database queries another. Period. Full stop.
Third, you say you find no db links when you query ALL_LINKS.  The name of the view is ALL_DB_LINKS.  And that only shows the links that the current user has permissions on.  If you want to see all links in the database, you query DBA_DB_LINKS, as a user that has permissions to query that view.  Plus, I see nothing in your question description that would cause me to suspect that there indeed are any db links.


Question regarding 2NF partial key dependency If the functional depencies are
   AB-->CD
   BC-->D
   IS the relation still in 2NF?,I mean since AB is the key and the 2nd BC,out of which B is part of the key,is the relation still in 2NF??

Assuming your relation is ABCD and assuming you meant the following functional dependencies:

AB-->C
BC-->D

Then you need to decompose this relation to get it to third normal form (3NF).  This is because D is not determined by the key of ABCD.  To bring this to 3NF you need to have two relations: ABC and BCD.  Now each non-key attribute is fully dependent on the key of its relation (and nothing else).


schema of messaging system in a web application where people talk to each other , there will be  a lot of messages , suppose for simplicity that each row in a table is a message ,

do you think if a have 5 million messages that one table is enough for them all ?
or should i have 5 tables of one million each ?

the server used will be a simple vps or small dedicated server with 8Gb of ram ,two cores processor 2.5Ghz frequency , ssd ,

Yes, one table is probably enough. You don't usually gain anything by splitting the messages into multiple tables, i.e. horizontal partitioning. On the contrary, it will usually make your queries more complex and slower.

However, horizontal partitioning does make each index smaller, thereby potentially resulting in faster queries provided your data is segregated into e.g. different countries or regions, and you have an obvious, robust, implicit way to identify in which table a particular row will be found without first needing to search the index.

But in most cases, a single table is what you want.


Which is quicker: Select of existing row vs Update where no row exists? Which would run the fastest?

Select from a table using the primary key (integer, clustered index, 1,000,000+ rows)
Attempt an update on a different table, where the row does not exist? (where clause on integer primary key, clustered index, 200,000+ rows)

Background

We currently have some procedures that need to maintain a filtered copy of their table.

Tables involved:

[MasterTable] that contains the filter criteria
[ChildTable] to be filtered
[ChildFilterTable] to hold the filtered records

This is currently being done by:

Select filter criteria
If filter criteria match then:

Attempt an update
If no records updated, insert a new record

Example SQL:

DECLARE @FilterValue INT

/* Get FilterValue to check */
SELECT @FilterValue = FilterValue FROM [MasterTable] WHERE ID = @IDFromChildTable

IF @FilterValue = 123
BEGIN
    /* Attempt update */
    UPDATE [ChildFilterTable] SET
        ...
    WHERE ChildID = @IDFromChildTable

    IF @@ROWCOUNT = 0
    BEGIN
        /* Row not there yet, insert it! */
        INSERT INTO [ChildFilterTable] (ChildID, ....) VALUES (@IDFromChildTable, ....)
    END
END

Proposed change

Change to:

Attempt an update
If no records updated, then:

Select filter criteria
If filter criteria match then: insert a new record

So:

DECLARE @FilterValue INT

/* Attempt update */
UPDATE [ChildFilterTable] SET
    ...
WHERE ChildID = @IDFromChildTable

IF @@ROWCOUNT = 0
BEGIN
    /* Get FilterValue to check */
    SELECT @FilterValue = FilterValue FROM [MasterTable] WHERE ID = @IDFromChildTable

    IF @FilterValue = 123
    BEGIN
        /* Row not there yet, insert it! */
        INSERT INTO [ChildFilterTable] (ChildID, ....) VALUES (@IDFromChildTable, ....)
    END
END

Note: Business rules confirm that the filter value will never change once setup on the [MasterTable] so we don't need to worry about updating a value which does not match the filter record (ie: if it's in the ChildFilterTable, we want to update it.

Can you not use the MERGE statement added with SQL Server 2008 to "UPSERT" in one atomic operation?

DECLARE @FilterValue INT

;MERGE
INTO ChildFilterTable AS CFT
USING  (your filter, source thing here)
                  ON (CFT.ChildID = ...)
WHEN MATCHED
    THEN update stuff
WHEN NOT MATCHED BY TARGET
    THEN insert stuff;


Variable Substitution I am working in TOAD for Oracle and am trying to use variable substitution in my coding.

I have been able to get variable substitution to work in the code body pressing F5:

Define Value = '1.5'
Create table XX_DELETEMEEE as Select
percent * To_Number('&&Value') Test
from Source_Table;

I have been unable to make the variable work in the same way for text as table names or column names:

Define PROJECT = 'P747'
Create table XX_DELETEMEEE as Select
percent (To_CHAR('&&Project'))
from Source_Table;

The error for this code has 'percent' as an invalid identifier.

Define PROJECT = 'P747';
Create table &&Project||_MU as Select
percent
from Source_Table;

The error for this code has table name as an invalid option.

Any ideas on how to make this work while not straying to far in methodology?

define/& is a simple text substitution mechanism that does not know anything about sql. And sql and the Oracle Database server do not know anything about the text processing mechanism. So the text that results from the 'define/&'substitutuin must be valid sql statement. Otherwise you get an error.

 CREATE TABLE P747||_MU as Select percent from Source_Table

is not a valid sql statement.

 CREATE TABLE P747_MU as Select percent from Source_Table

is  the valid statement, an so

Define PROJECT = 'P747';
Create table &&Project._MU as Select
percent
from Source_Table;

Avariable name like &&Project must be followed by a whitespace or and end of file or by a dot (.). The dot is removed after variable substitution.

Define PROJECT = 'P747';
Create table &&Project_MU as Select
percent
from Source_Table;

will not work, because the variable is &&Project_MU and not &&Project.

you mix up simple text substituion in the Toad editor ( 'define/&)  with sql string functions (to_char, ||). That are different things.


sybase errors - meaning and resolution We have a server that connects to 40+ different remote computers which are running Sybase 11.0.1.2596. Our server is hosted on an aws ec2 AMI Linux instance, and the connections are set up using FreeTDS. The server has several different command-line PHP scripts that use PDO with dblib to connect and select data. 99+% of the time everything works, but sometimes I get these errors:

SQLSTATE[HY000]: General error: PDO_DBLIB: dbresults() returned FAIL

SQLSTATE[HY000]: General error: 20047 TDS: unexpected token 56 [20047] (severity 1) [(null)]

SQLSTATE[HY000]: General error: 20047 DBPROCESS is dead or not enabled [20047]

They seem to happen randomly, and I can't figure out what is causing them or how to fix it. If I just wait, it will start working again. It also seems if I use tsql to connect it will start responding:

tsql -S <odbc name> -U <username>

Any advice is greatly appreciated.

TDS: unexpected token

TDS (transaction Data Stream) is the application layer protocol of Sybase & MS SQL for communication between OpenClient & OpenServer
The sample messages in the post point to TDS exchange between ASE & DB-LIB client not being in sync according to the TDS protocol
The network exchange machine state in the affected TDS connection at the ASE OpenServer end is not expecting a request from the DB-LIB client for any resultset
The above situation may happen if an existing DB-LIB client is picking up a new TDS connection instead of the original one to continue with getting the resultset from a query submitted earlier
A typical scenario would involve a pool of connections from a Sybase client to the ASE, should an existing client incorrectly switches to another connection in the pool after submitting a query, the OpenServer of the new, incorrect connection would not understand the client's TDS token requesting resultset
Another possible root-cause could be network issue because 56 is not a valid TDS token (0x38 is not a valid token nor 0x56)
BTW, DB-LIB is an arcane, unsupported Sybase client (newer, supported Sybase client is CT-LIB)


SQL Server 2008 - Run the Check Database Integrity Task with the PHYSICAL_ONLY option? As part of our nightly maintenance, we include the Check Database Integrity Task which is an encapsulation of the DBCC CHECKDB command. It's beginning to take a considerable amount of time due to the size of our database, and after reading up on the command at MSDN, we decided to take their advice:

  Therefore, using the PHYSICAL_ONLY option may cause a much shorter run-time for DBCC CHECKDB on large databases and is recommended for frequent use on production systems. We still recommend that a full run of DBCC CHECKDB be performed periodically. The frequency of these runs depends on factors specific to individual businesses and production environments.

So we want to change our plan to run DBCC CHECKDB with the PHYSICAL_ONLY option every night, and then without it once a week. Is there any way to get SQL Server to include the PHYSICAL_ONLY option in the integrity task? Can I somehow modify the T-SQL produced by the task? I can't figure out how to include the option as part of the maintenance plan.

No, the Maintenance Plan tasks are very limited.

I recommend using this maintenance script, which has an option for PHYSICAL_ONLY.

Also, consider reducing the frequency of running the checks. Is it really necessary to run them every night? This doesn't just use SQL Server resources -- it's also extra wear & tear on your SAN. Just something to think about.


How to restore a "contained" database? I recently tried to restore to my local development SQL Server a backup from a network instance.  To my surprised I received the following error message:

  Msg 12824, Level 16, State 1, Line 3 The sp_configure value 'contained
  database authentication' must be set to 1 in order to restore a
  contained database.  You may need to use RECONFIGURE to set the
  value_in_use. Msg 3013, Level 16, State 1, Line 3 RESTORE DATABASE is
  terminating abnormally.

What steps must I follow to successfully restore the database?

In order to restore a contained database into a different instance of sql server, in this case my local server, the "Enable Contained Databases" property must be set to True.

You can do this from management studio:

Right-Click on the server instance, select Properties
Select Advanced page, set  under Containment the property value to True
Proceed to restore the database backup.
ALTER AUTHORIZATION ON DATABASE::ReplaceThisWithYourDatabaseName TO ReplaceThisWithLeastPrivilegeUser;

Here are the script lines I actually used for enabling/disabling containment:

-- Enable "contained database authentication"
EXEC sp_configure 'contained', 1;
RECONFIGURE;

-- Disable "contained database authentication"
EXEC sp_configure 'contained', 0;
-- Force disabling of "contained database authentication"
RECONFIGURE WITH OVERRIDE;

For additional information please refer to:
http://www.sqlsoldier.com/wp/sqlserver/protectionfromrestoringabackupofacontaineddatabase


Install Oracle 11gR2 driver for Node.js 0.10.20 I want to connect Oracle 11gR2 and Node.js 0.10.20. I use this package but I don't understand this part of installation process. Can you explain it to me?

# Replace /opt/instantclient_11_2/ with wherever you extracted the Basic Lite files to
echo '/opt/instantclient_11_2/' | sudo tee -a /etc/ld.so.conf.d/oracle_instant_client.conf
sudo ldconfig

This means that you have to include Oracle instant client libraries into the database used by Linux dynamic linker when it has to resolve run time bindings.

Instructions tells you to create new .conf file in /etc/ld.so.conf.d/ directory with the Oracle instant client libraries directory. And then you have to run ldconfig with root permissions so that it will create new database with the Oracle instant client libraries included.

Example from one of our servers:

# cat /etc/ld.so.conf.d/oracle.conf
/usr/lib/oracle/11.2/client64/lib/

.conf file name does not matter.


Can I hide my password for data at rest encryption key? For MariaDB encryption at-rest, my setting is
[mysqld]
# at-rest encryption
plugin_load_add              = file_key_management
file_key_management_filename = /etc/mysql/rest/keyfile.enc
file_key_management_filekey  = FILE:/etc/mysql/rest/keyfile.passwd
file_key_management_encryption_algorithm = AES_CBC

However, if hackers steals the database, they has access to both keyfile.enc and keyfile.passwd and the point of encryption at-rest is useless.
Can I tell the key manager not to read the super password from the file. Instead, read it from the user at the startup?

Usually database is started as a service in background, so entering keys at startup will not be possible.
To separate key(s) and data you have the following options:

store keys on a separate device which can be unplugged after start of database server.
use an external key management system, e.g Eperi which also supports key rotation.


Schema Changes Are Not Populating to The Subscribers One of my publications was dropped mistakenly. I restored it from the latest backup but now schema changes are not replicated to the subscribers.

What can I do to re-initialize/fix the publication?

Did you mean afterwards you re-created the publication that you dropped? Afterwards, did you started back the snapshot agent?


how to build statistical database? According to the book I am reading, a statistical database is a database that permits queries that derive aggregated information but not queries that derive individual information.
At current time, is it possible to build a statistical database? And how?
Can it be created using SQL?

What is a statistical database?

At a high level, it's just a type of database that only stores statistical data. An example is a census database. Typically, access control for a pure SDB is straightforward: Certain users are authorized to access the entire database.

It's about the data not the engine

Keep in mind, this is about the data and not the type of relational database management system you chose such as MySQL, SQL Server, Oracle, etc. Any database can be a statistical database if you store statistical data in said database such as census information and so forth.

Typical structures for statistical databases

That being said, most statistical databases are Online Analytical Processing (OLAP) which is characterized by relatively low volume of transactions. Queries are often very complex and involve aggregations (e.g.: derived aggregated information and not derived individual information).

OLAP vs OLTP

Can statistical databases be created using SQL

Yep. Any database engine can be used to create a statistical database if the data is one that provides data of a statistical nature, such as counts and averages and the users accessing the database are querying aggregate, or statistical, data from said database as opposed to individual records on the users the statistics are ultimately based on.

Being most of the popular database engines use a form of SQL to get data to and from the underlying system, then the answer to the question is, "yes".

Why is individual information restricted?

This greatly depends on the business or organization using the statistical database for their needs. In general, individual records, what the statistics are based on are restricted for privacy reasons. For example, medical statistics based on medical records from the local  hospital.

Therefore, security is a big concern for statistical databases in order to help prohibit users from unearthing individual information from the use of aggregated statistical information.

Examples of how individual records can be compromised with statistics

  A statistical user of an underlying database of individual records is
  restricted to obtaining only aggregate, or statistical, data from the
  database and is prohibited access to individual records. The inference
  problem in this context is that a user may infer confidential
  information about individual entities represented in the SDB. Such an
  inference is called a compromise. The compromise is positive if the
  user deduces the value of an attribute associated with an individual
  entity and is negative if the user deduces that a particular value of
  an attribute is not associated with an individual entity. For example,
  the statistic sum(EE· Female, GP) = 2.5 compromises the database if
  the user knows that Baker is the only female EE student.

Hope that helps!


Trigger to UPDATE after UPDATE? I want to make a trigger to record the time of any update as:

CREATE TRIGGER col_update
AFTER UPDATE ON col
FOR EACH ROW BEGIN
UPDATE col SET updated=NOW() WHERE id=NEW.id; // or OLD.id
END

The problem is that when this trigger tries to update the updated column, it is also another update event, which runs the trigger. This will create an infinite loop, which does not work.

How can I store the update time in the corresponding column?

I wish to use a trigger because there are many columns in the table. If I try to set the update time manually, I would need to modify many queries.

Use a BEFORE trigger instead, and set the updated column assigning a value to NEW.updated (this would be a simple assignment, not an UPDATE).  This way you won't trigger additional UPDATEs.

Your trigger body will simply look like

SET NEW.updated = NOW()

I usually use AFTER triggers only for modifying other tables, BEFORE for modifying the new (or updated) row, or suppress DELETE.


Code creating clustered columnstore index while maintaining row order I want to convert a rowstore table to a columnstore table by creating a clustered columnstore index. There are three columns in the table: id,time, and value.

The table is ordered by id and time before creating columnstore index; however, after creating columnstore index, the row order is messed up. I thought it might be due to the parallelism and added the maxdop = 1 option, but that didn't fix the problem. Can anyone help me with this?

Here is the code creating tables and indexs:

-- creating rowstore table
drop table if exists tab1_rstore
select id, time, value
into tab1_rstore
from tab0
order by id_loan, period
option(maxdop 1)

-- creating clustered index on rowstore table
create clustered index idx on tab1_rstore (id,time)

-- creating columnstore table
select *
into tab1_cstore
from tab1_rstore
    option(maxdop 1)

-- comparing the first two rows from these two tables
    select top 2 *
from tab1_rstore

    select top 2 *
from tab1_cstore

The screenshot of the query results:

-- creating clustered columnstore index
create clustered columnstore index idx on tab1_cstore
with (maxdop = 1)

-- comparing the top two rows again
select top 2 *
from tab1_rstore

select top 2 *
from tab1_cstore

The screenshot of the query results with columnstore index:

My understanding is that the order of rows is determined by the compression algorithm and there is nothing we can do about it, see the limitation and restriction in the document here with the following quote:

  Cannot include the ASC or DESC keywords for sorting the index.
  Columnstore indexes are ordered according to the compression
  algorithms. Sorting would eliminate many of the performance benefits.

I'm using SQL Server 2016 Developer Edition on Windows 10 64bit.

A clustered columnstore index is fundamentally different from a clustered rowstore index. You may have noticed there is no key column specification for a clustered columnstore index. That's right: a clustered columnstore index is an index with no keys - all columns are 'included'.

The most intuitive description I have heard for a clustered columnstore index is to think of it as a column-oriented heap table (where the 'RID' is rowgroup_id, row_number).

If you need indexes to support direct ordering and/or point/small range selections, you can create updateable rowstore b-tree indexes on top of clustered columnstore in SQL Server 2016.

In many cases this is simply not necessary, since columnstore access and batch mode sorting is so fast. Many of the things people 'know' about rowstore performance need to be relearned for columnstore. Scans and hashes are good :)

That said, of course columnstore has a structure to its row groups (and metadata about min/max values in each segment), which can be useful in queries that can benefit from row group/segment elimination.

One important technique in this area is to first create a clustered rowstore index with the desired ordering, then create the clustered columnstore index using the WITH (DROP_EXISTING = ON, MAXDOP = 1) option. In your example:

CREATE [UNIQUE] CLUSTERED INDEX idx
ON dbo.tab1_cstore (id, time)
WITH (MAXDOP = 1);

CREATE CLUSTERED COLUMNSTORE INDEX idx
ON dbo.tab1_cstore
WITH (DROP_EXISTING = ON, MAXDOP = 1);

Care is needed to maintain the benefits of row group/segment elimination over time. Also, while columnstore is already implicitly partitioned by row group, but you can explicitly partition it as well.

I'm not 100% sure what you're looking to test, but it is true that the 'order' of values within a segment is determined by the compression algorithm. My point about creating the columnstore index with DROP_EXISTING is about the ordering of data flowing into the segment creation process, so that segments overall will be ordered in a particular way. Within the segment, all bets are off.


The reading with secondaryPreferred when chunk migration occurred often fail We have some problems regarding balancer configuration.
Our application which is set up with secondaryPreferred often fails to read documents from the secondary when balancer( chunk migration ) is running.
So I changed the Balancer setting to the appropriate setting as below, but I could observe the unexpected behavior.

use config
db.settings.update(
   { "_id" : "balancer" },
   { $set : { "_secondaryThrottle" : true ,
              "writeConcern": { "w": 6} } },
   { upsert : true }
)

->  { "_id" : "balancer",   "stopped" : false,   "_secondaryThrottle" : true,   "writeConcern" : { "w" : 6 } }

1. Environment

Our cluster is as below.

  ( 1 primary, 5 secondary, one arbiter ) * 6 masters on Sharded cluster
  on MongoDB 3.2.4 on Google Cloud Platform Mongod instance type  is
  "n1-standard-16 (16 vCPUs,  60 GB memory) with 500GB persistent disk"

2. What did I expect?

I expect that the application with reading as secondaryPreferred can read from the Secondary-mongod without failing. I think that the Balancer can wait for all completion of chunk-migration because I set up "writeConcern:6". So my expectations are as described above.

3. Test conditions and results

I configured as above settings and restarted balancer and execute the application.
The application( secondaryPreferred ) failed with high frequency to read from the secondary-mongod.

Please let me ask a question.

Q1. What is the meaning of writeConcern in balancer setting? It seems
that writeConcern does not work as shown in the above test.
Q2. How can I read without failing when chunk migration occurred?
Q3. What is the purpose of _secondaryThrottle?  I have read the
official document of v3.2. And official document described that
_secondaryThrottle is true as the default value and has the same meaning as writeConcern: 2.  But we can configure settings as below.
If I set up _secondaryThrottle:false, does it makes the writeConcern
invalid right? Is "_secondaryThrottle" the flag for enabling
writeConcern?

  e.g. { "_id" : "balancer",  "stopped" : false,  "_secondaryThrottle" :
  false,  "writeConcern" : { "w" : 6 } }

sincerely

Nowadays, balancer doesn't honor _secondaryThrottle, nor w: when w:<=1. But, this case that w:6 means that when data is removed (range deleter at end of chunk move), we are removing documents one by one AND every document delete must have 6 acknowledge. If moved chunk had 100.000 documents, then there is 100.000 individual deletes with acknowledges. (slooooow)

If your query at secondary hits documents in the range what is under range delete, there will be a collision.


Why my transaction has not been replicated? What was the transaction sequence number? I have been troubleshooting a replication problem, according to the picture below:

with reference to the picture above,

the bit that says
Sessions of the distribution agent
I can get using the following query:

--================================================================================
-- finally the contents
-- sessions of the distribution agent:
--================================================================================
use [master]
exec [distribution]..sp_MSenum_distribution_s
@name = N'MYSERVER-Product-Product-MYSUBSCRIPTION-184',
@hours = -1,
@session_type = 1

Actions in the selected session

--========================================================
-- get the actions in the selected session:
-- for each row on the above data, we can get the details:
--========================================================
-- example of the last session - without errors
use [master]
exec [distribution]..sp_MSenum_distribution_sd
@name = N'MYSERVER-Product-Product-MYSUBSCRIPTION-184',
@time = N'20170411 14:16:03.920'

-- example of the second session - with errors
use [master]
exec [distribution]..sp_MSenum_distribution_sd
@name = N'MYSERVER-Product-Product-MYSUBSCRIPTION-184',
@time = N'20170410 15:50:27.650'

Now this bit of code below gives me the errors I am looking for:

Error details or message of the solected session:

And here goes the Question too:

How can I get the number 5468694 from the result above and use it in the query below?

--========================================================================
-- Error details or message of the solected session:
--=========================================================================
    use [master]
    exec [distribution]..sp_MSenum_distribution_sd
    @name = N'SQLAPPLON1-Product-Product-REPLON1-184',
    @time = N'20170410 15:50:27.650'

    use [master]
    exec [distribution]..sp_MSget_repl_error
    @id = 5468694

This isn't a verified query as I'm not running replication, but after scripting out definitions for both sp_MSenum_distribution_sd and sp_MSget_repl_error via sp_helptext and smashing together the underlying logic, I came up with the following that may give you what you're looking for.

DECLARE @name NVARCHAR(100), @time DATETIME

SET @name = N'SQLAPPLON1-Product-Product-REPLON1-184',
    @time = N'20170410 15:50:27.650'

SELECT  msre.source_type_id,
        msre.source_name,
        msre.error_code,
        msre.error_text,
        sys.fn_replformatdatetime(msre.time) AS 'time',
        error_type_id,
        CASE WHEN msre.xact_seqno IS NULL OR msre.xact_seqno = 0x0
            THEN CONVERT(BIT, 0) ELSE CONVERT(BIT, 1) END AS has_xact_seqno,
        msre.xact_seqno,
        msre.command_id,
        rh.error_id
FROM    [distribution].dbo.MSrepl_errors msre
        INNER JOIN [distribution].dbo.MSlogreader_history rh WITH (READPAST)
            ON msre.id = rh.error_id
WHERE   rh.agent_id = (SELECT TOP 1 ID
                       FROM MSlogreader_agents
                       WHERE name = @name)
        AND rh.start_time = @time
        AND rh.comments not like N'<stats state%'
        AND rh.error_id <> 0
        -- rows with error_type_id are placeholders
        AND msre.error_type_id IS NOT NULL
ORDER BY 5 ASC


Expected Performance of SQL Server We have a client who has been having performance issues. One of the developers is saying that a select * from a table is taking ~50 seconds to perform the query (no joins just straight select with no fields). The table has 166 columns (I know that is probably over the top, but this is from an ERP software that the developer integrates with) and around 400,000 rows.

Now considering the crazy number of columns and 400k records, 50 seconds seems about right for me.

The SQL Server is running on a Hyper-V VM and has decent hard drive read/write speeds, the hard-drive specs are: 16 x 600gb 15RPM 6.0Gbps SAS drives in Raid 10.

The VM performs well and has plenty of RAM, just interested to see if any experts can chime in whether they think that query is performing within reason?

It's quite a few rows, and the table is quite wide - so I'd be interested to how much data is being pulled back. You can get a good idea by capturing it in SQL Server Profiler, or from identifying the SPID, then watching the logical_reads column in sys.dm_exec_requests just before the query completes on modern versions of SQL Server. This measures 8KB pages, so divide by 128 and 1024 to get the approx size in GB. You'll probably find it's quite a chunky read like 100GB! And 100GB will always take some time to come off disc!

Following a more business approach would be having SLAs. Hopefully, it might be documented that this business action (eg "add customer") should take a maximum of 2 seconds for the system, and that business action (eg "find address") would also take 2 seconds. As long as the app is meeting or exceeding these requirements, a select * from the entire table won't matter.


does automatic checkpoint flushes dirty pages which are related to un-committed transactions to MDF File in SQL Server when an automatic checpoint runs in background in sql server, i know it flushes all the dirty pages to data file.

does it also flushes dirty pages which are related to un-committed transactions into data file or it only flushes dirty pages related to committed transactions?

I am still not able to get a single source where I can get the entire information about database recovery in SQL Server. More so, I am interested to how SQL Server does recovery in case of system failure and how it depends on CHECKPOINT and are there any other factors on which DB recovery depends?

Does recovery process depends on Recovery model as well?

Any inputs / pointers would be greatly appreciated. :)

does it also flushes dirty pages which are related to un-committed
  transactions into data file or it only flushes dirty pages related to
  committed transactions?

Yes of course, it flushes all the dirty pages:

  When a checkpoint operation occurs, no matter how it’s triggered (for
  instance through a manual CHECKPOINT, from a database or differential
  backup, or automatically) the same set of operations occurs:

  All dirty data file pages for the database are written to disk (all    pages that have changed in memory since they were read from disk or
  since the last checkpoint), regardless of the state of the
  transaction that made the change.
  Before a page is written to disk, all log records up to and including    the most recent log record describing a change to that
  page are    written to disk (yes, log records can be cached in memory
  too). This    guarantees recovery can work and is called write-ahead
  logging. Log    records are written to the log sequentially, and log
  records from    multiple transactions will be interspersed in the log.
  The log cannot    be selectively written to disk, so writing a dirty
  page to disk that    only has a single log record affecting it may
  mean writing many more    previous log records to disk as well.
  Log records describing the checkpoint are generated.
  The LSN of the checkpoint is recorded in the database boot page in    the dbi_checkptLSN field (see Search Engine Q&A #20: Boot pages, and
  boot page corruption).
  If in the SIMPLE recovery model, the VLFs in the log are checked to    see whether they can be marked inactive (called clearing or
  truncating the log – both of which are terrible misnomers, as nothing
  is either physically cleared or truncated).

How do checkpoints work and what gets logged by P. Randal

  I am interested to how SQL Server does recovery in case of system
  failure and how it depends on CHECKPOINT and are there any other
  factors on which DB recovery depends?

  During crash recovery, the mechanism is more complicated. The fact
  that database pages are not written to disk when a transaction commits
  means that there is no guarantee that the set of database pages on
  disk accurately reflects the set of changes described in the
  transaction log—either for committed or uncommitted transactions.
  However, there is one final piece of the puzzle that I haven't
  mentioned yet—all database pages have a field in their page header (a
  96-byte portion of the 8192-byte page that contains metadata about the
  page) that contains the LSN of the last log record that affected the
  page. This allows the recovery system to decide what to do about a
  particular log record that it must recover:

For a log record from a committed transaction where the database page

  has an LSN equal to or greater than the LSN of the log record, nothing
  needs to be done. The effect of the log record has already been
  persisted on the page on disk.

For a log record from a committed

  transaction where the database page has an LSN less than the LSN of
  the log record, the log record must be redone to ensure the
  transaction effects are persisted.

For a log record from an

  uncommitted transaction where the database page has an LSN equal to or
  greater than the LSN of the log record, the log record must be undone
  to ensure the transaction effects are not persisted.

For a log record

  from an uncommitted transaction where the database page has an LSN
  less than the LSN of the log record, nothing needs to be done.

  The
  effect of the log record was not persisted on the page on disk and as
  such does not need to be undone. Crash recovery reads through the
  transaction log and ensures that all effects of all committed
  transactions are persisted in the database, and all effects of all
  uncommitted transactions are not persisted in the database—the REDO
  and UNDO phases, respectively. Once crash recovery completes, the
  database is transactionally consistent and available for use.

Understanding Logging and Recovery in SQL Server by P.Randal

  Does recovery process depends on Recovery model as well?

No, it does not.
Database should be consistent in any recovery model after crash recovery.


Disk block per table? At T1, insert comes for table_1, goes to disk block_1.
At T2, insert comes for table_2, can it go to same disk block_1(provided block_1 has space) or new block will be created per table ?
At T3, again insert comes for table_1, can it go either block_1 or block_2 based on available space ?

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

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

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

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

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

SHOW TABLE STATUS LIKE 'T1';

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

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

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


Enforcing type correctness for foreign keys into a generic typed table We currently have a single table which represents a bunch of equipment (of different types). Since the fields are pretty similar, they're stored in the same table.

select * from equipment;

id | name | type

----+--------+----------

1 | item 1 | CONVEYOR

2 | item 2 | PICKER

3 | item 3 | CONVEYOR

I want to create another table, which represents combinations of exactly 1 x PICKER and 1 x CONVEYOR. Something like:

CREATE TABLE "picker_conveyor_combo" (
  "id" serial,
  "picker_id" integer not null,
  "conveyor_id" integer not null
);

picker_id and conveyor_id would be foreign key references into the main equipment table.

However, I'd like to enforce that picker_id always references a row where the type is "PICKER" and similarly with conveyor.

I know that triggers are commonly used for this, but triggers don't typically validate historical data. So the fact the trigger exists won't guarantee that the data is valid.

I'm thinking of an alternative approach with composite foreign keys:

create table "picker_conveyor_combo" (
  id serial,
  "picker_id" integer not null,
  "picker_type" text not null,
  "conveyor_id" integer not null,
  "conveyor_type" integer not null
);

alter table "picker_conveyor_combo"
  add constraint "check_types"
  check ("picker_type" = 'PICKER' and "conveyor_type" = 'CONVEYOR');
alter table "picker_conveyor_combo"
  add constraint "picker_foreign"
  foreign key ("picker_id", "picker_type")
  references "equipment" ("id", "type");
alter table "picker_conveyor_combo"
  add constraint "conveyor_foreign"
  foreign key ("conveyor_id", "conveyor_type")
  references "equipment" ("id", "type");

In this method, there are some extra columns. However, all data will be validated based on constraints - so that adds some extra confidence.

Is this a valid approach, or is there something fundamentally wrong with the schema design?

Your approach is the one I would use.

You forgot a unique constraint:

ALTER TABLE equipment ADD UNIQUE(id, type);

That will be the target of the foreign key constraints.

I would not store type as a string. Rather, I would use a lookup table for the types and store the numeric identifier. This will prevent typos, and it will reduce the required storage space.


How subscriber table updated during snapshot replication? I am testing a snapshot replication process (SQLServer 2008 (yes, unfortunately...)).

Everything is fine but I can't find in MS documentation how are tables updated.
I saw that the publication process drops tables, then recreates them, and then populates it with data.

OK, nice.
But is there a kind of security (locks, transactions...) that prevents a query to be made during this synchronization (in particular just between the drop table and the create)?

Let's imagine a database A (publisher) having a table T replicated on another database B (subscriber).
When the distribution agent is running and table T is updated on server B (drop table T + create table T + bcp-in data ?), during a few milliseconds the table won't be there. If an application tries to read table T, it will get an error.

When I try to launch a trace on server B when distribution agent is running, I can't see any transaction made for this step. And indeed I can make a select on a replicated table T on server B (and get an error).

Is it really how it is supposed to work ?

Are there settings somewhere to avoid such behaviour ?

Is it really how it is supposed to work ?

Yes this how it is supposed to work. You only run snapshot at the beginning of  setting up transactional replication. Unless you are out of sync due to any error you do not need to run snapshot again. If you are running snapshot replication ONLY you can control when you want to resync all your articles and nothing should connect to subscriber while snapshot is being applied.

From books online:

  Snapshot replication is most appropriate when data changes are
  substantial but infrequent. For example, if a sales organization
  maintains a product price list and the prices are all updated at the
  same time once or twice each year, replicating the entire snapshot of
  data after it has changed is recommended. Given certain types of data,
  more frequent snapshots may also be appropriate. For example, if a
  relatively small table is updated at the Publisher during the day, but
  some latency is acceptable, changes can be delivered nightly as a
  snapshot.

  Snapshot replication has a lower continuous overhead on the Publisher
  than transactional replication, because incremental changes are not
  tracked. However, if the dataset set being replicated is very large,
  it will require substantial resources to generate and apply the
  snapshot. Consider the size of the entire data set and the frequency
  of changes to the data when evaluating whether to utilize snapshot
  replication.

When is a subscription available; when can the subscription database be used?

  A subscription is available after the snapshot has been applied to the
  subscription database. Even though the subscription database is
  accessible prior to this, the database should not be used until after
  the snapshot has been applied. Use Replication Monitor to check the
  status of snapshot generation and application.

Are there settings somewhere to avoid such behaviour ?

If you are sure that your schema and data is already in sync or you only want future changes to get replicated you can use following option to avoid snapshot. I used this many time during upgrade with downtime.

@sync_type=replication support only

  Provides automatic generation at the Subscriber of article custom
  stored procedures and triggers that support updating subscriptions, if
  appropriate. Assumes that the Subscriber already has the schema and
  initial data for published tables. When configuring a peer-to-peer
  transactional replication topology, ensure that the data at all nodes
  in the topology is identical. For more information, see Peer-to-Peer
  Transactional Replication.

  Not supported for subscriptions to non-SQL Server publications.


Best way to handle dates prior to 1000 A.D. in MySQL? I am creating a database for records that extend prior to 1000 AD, but MySQL Date  and DateTime fields only support dates starting at 1000.

Is there a way that would be more convenient than either using a bigint type to count seconds before/after 1/1/1970 using a Unix timestamp, or switching to a database software that supports larger date ranges?

No native RDBMS date data type is going to do for applications that require very old (and for some, even distant future) dates.

If I were you, I'd use a string type for the native storage and stick with a place-significant format like: +YYYY-MM-DD to accomodate BC/AD and any foreseeable historical or reasonable future date.

If it might help, you could build a library class that converts your internal storage format into a more presentable one for the UI layer.  You might even include library functions that convert to a native date type, if your language of choice supports the dates that you will have in your database.


Data History Table vs Using a Current Record Flag There are two strategies when dealing with data records which contain multiple versions. One is to have current records in one table, and their past versions in a history table.

The other is to have all versions in the same table with a flag on the current version.

I've seen arguments for each, and I've seen community agree with both strategies in different posts.

If there is no right answer, how does one decide which strategy to take? I can see the advantage of keeping all the records in the same table for query simplicity (all the records in one table) and less tables in general.

The flip side is you have these huge tables with a ton of records depending on how many versions and complicated relationships between tables.

Pro History Table Database Design for Revisions? Database history changes, same or different table?

Pro Current Record flag https://softwareengineering.stackexchange.com/questions/94489/handling-deleted-users-separate-or-same-table

Other Articles https://softwareengineering.stackexchange.com/questions/94489/handling-deleted-users-separate-or-same-table

If we're explicitly discussing Slowly Changing Dimensions in a Data Warehouse scenario and you're following the Kimball methodology, there's actually more than 2 different ways of doing this.

The Kimball methodology has 7 different ways of recording SCDs (scroll down to Slowly Changing Dimension Techniques in the link). Types 1, 2 and 3 are apparently the most common, I don't really know of anyone using 5, 6 or 7.

The history table being Type 4, is the one I currently use. It allows us to keep the 'live' table as compact as it can be. We rarely query the history tables, so they're really just there for when we need to audit the history of a dimension. There is no relationship between this table and any others and it has a surrogate ID as the Primary Key.

I've worked in places where we've used Type 2, which is the Flag method and it can lead to unnecessarily large table growth on a key table. However, that's just my experience!


Single Core SQL Server on VM Recently our school district has upgraded several servers from version 2005 to 2016 and from physical servers to VM servers.

Our infrastructure team presented the VM servers for SQL Server installation with a single core.

I am attempting to explain to the head of that department that SQL Server 'hates' being on a single core and he wants to see 'proof'.  None of the data that I have presented has satisfied his need for proof and so he has asked for some articles on why a VM SQL Server won't operate efficiently on a single core.

What articles to you use for this?

Any help for me making my case would be appreciated!

Any non-SQL Server-process that consumes CPU may cause the entire SQL Server to be completely unusable.

As an example, Windows Update will bring SQL Server to a stop while it is unpacking .Net assemblies.

Any Anti-Virus process, even if it is configured to exclude SQL Server data files and folders may cause SQL Server to become unresponsive while it scans files and while it unpacks updated virus definition files.

If you have any management software installed, such as IBM Tivoli Server Monitor, and it decides to use 100% of one core, you probably won't be able to do anything other than cold shutdown the machine.  Don't ask me why I'm bringing that up.

I could go on, but suffice it to say, any server with a single core is simply asking for trouble with performance and manageability.   Check that, any computer with a single core should be considered unusable today.  Heck, my phone has 8 cores.

While you're at it, convince the virtualization guy to give each SQL Server a reasonable memory reservation that will ensure SQL Server's memory doesn't get paged out of the hosts main memory to disk.

For serious VMware-hosted SQL Servers, you should configure VMware according to the Architecting SQL Server on VMware Best Practices guide.

It really boils down to a single-core VM can only ever run a single thread at any given time.  Virtualization is not a silver bullet for that one-core-per-thread situation.

The above points take into account that Windows NT (the 1993 codebase which all modern versions of Windows are built on) utilizes preemptive multi-tasking.  Preemptive multi-tasking allows the O/S to enforce multi-tasking by forcing context-switching at regular schedules.  Even with preemptive multi-tasking, a server with a single core will still suffer from very poor performance if a single task decides to use as much CPU as possible, since the O/S will give that task as much CPU time as can be spared while still allowing higher-priority tasks to run, forcing CPU to 100%.  If SQL Server is running at the time CPU goes to 100%, SQL Server's quantum of CPU will be reduced so much as to become very noticeable.  In the above scenarios I outlined, I did not say, the machine (or even SQL Server) will completely "stop" or "die"; that would simply be untrue.  However, the machine may become so busy that for all intents-and-purposes the machine becomes unusable.  Adding a second core, while certainly not a panacea, reduces the likelihood that the machine will become completely unresponsive.  Each additional core means another thread can run to 100% CPU without causing the machine to become unusable.


Performance hickups after Postgres 9.4 upgrade After upgrading our database from 9.3.5 to 9.4.1 last night, the server suffers from high CPU spikes. The upgrade was done with pg_dump.  So the database was converted to SQL and then imported into 9.4.

During the CPU spikes, there are a lot of these messages in the logs:

process X still waiting for ExclusiveLock on extension of relation Y of database Z
after 1036.234 ms

And:

process X acquired ExclusiveLock on extension of relation Y of database Z
after 2788.050 ms

What looks suspicious is that there are sometimes several "acquired" messages for the exact same relation number in the exact same millisecond.

Why would Postgres grow a table twice in the same millisecond? Could it be an index with a high fill factor?

Any suggestions on how to approach this issue are welcome.

P.S. I've also asked this question on the Postgres mailing list, if that's not okay let me know.

The problem had to do with a kernel feature called Transparent Huge Pages (THP.)  You can diagnose this with perf top:

 59.73%       postmaster  [kernel.kallsyms]      [k] compaction_alloc
  1.31%       postmaster  [kernel.kallsyms]      [k] _spin_lock
  0.94%       postmaster  [kernel.kallsyms]      [k] __reset_isolation_suitable
  0.78%       postmaster  [kernel.kallsyms]      [k] compact_zone
  0.67%       postmaster  [kernel.kallsyms]      [k] get_pageblock_flags_group
  0.64%       postmaster  [kernel.kallsyms]      [k] copy_page_c
  0.48%           :13410  [kernel.kallsyms]      [k] compaction_alloc
  0.45%           :13465  [kernel.kallsyms]      [k] compaction_alloc
  0.45%       postmaster  [kernel.kallsyms]      [k] clear_page_c
  0.44%       postmaster  postgres               [.] hash_search_with_hash_value
  0.41%           :13324  [kernel.kallsyms]      [k] compaction_alloc
  0.40%           :13561  [kernel.kallsyms]      [k] compaction_alloc

The compaction_alloc function points at a problem.  You can turn off THP with:

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

Postgres versions before 9.4 do not specifically ask for huge pages, but it can be forced on them with always.

Here's a link to RedHat discouraging THP for database workloads.


How big can innodb_doublewrite_file grow? When I use the enable innodb_doublewrite_file ...

How big can this file grow ?
How can I calculate the optimal disk size, if it would be used only for this file?

Please look at the following view of the InnoDB/XtraDB Architecture

Please notice the Percona Server Documentation on This

  Performance Impact of the Doublewrite Buffer

  In usual workloads the performance impact is low-5% or so. As a
  consequence, you should always enable the doublewrite buffer because
  the strong guarantee against data corruption is worth the small
  performance drop.

  But if you experience a heavy workload, especially if your data does
  not fit in the buffer pool, the writes in the doublewrite buffer will
  compete against the random reads to access the disk. In this case, you
  can see a sharp performance drop compared to the same workload without
  the doublewrite buffer-a 30% performance degradation is not uncommon.

  Another case when you can see a big performance impact is when the
  doublewrite buffer is full. Then new writes must wait until entries in
  the doublewrite buffer are freed.

  How to Choose a Good Location for the Doublewrite Buffer

  Basically if you want to improve the I/O activity, you will put the
  doublewrite buffer on a different disk. But is it better on an SSD or
  a more traditional HDD? First you should note that pages are written
  in a circular fashion in the doublewrite buffer and only read on
  recovery. So the doublewrite buffer performs mostly sequential writes
  and a few sequential reads. Second HDDs are very good at sequential
  write if a write cache is enabled, which is not the case of SSDs.
  Therefore you should choose a fast HDD if you want to see performance
  benefits from this option. For instance, you could place the redo logs
  (also written in a sequential way) and the doublewrite buffer on the
  same disk.

What can we learn from this ?

The doublewrite buffer is a circular buffer. It is subject to pending writes when it is full.
The fact that the doublewrite buffer can get full proves that growth is never an issue.

CAVEAT

Looking at the InnoDB/XtraDB Architecture, it says that the Undo Logs can grow unllimitedly. That is the source of uncontrolled growth in the system tablespace.

Your Actual Question

  How big can grow innodb_doublewrite_file? How to calculate optimal disk size, if it would be used only for this file?

Nothing in the Documentation states a way to set a size or know its limitations. It only recommends placing the doublewrite file on a fast HDD.

CONJECTURE (OK I am guessing at this point)

If there is no limit on the doublewrite buffer size, then the number of blocks that make up all the data and index pages would be the maximum size. You would run this query:

SELECT
    ibbytes InnoDBSpace,
    ibbytes/POWER(1024,1) InnoDBSpaceKB,
    ibbytes/POWER(1024,2) InnoDBSpaceMB,
    ibbytes/POWER(1024,3) InnoDBSpaceGB,
    ibbytes/POWER(2,14)   InnoDBBlocks
FROM (SELECT SUM(data_length+index_length) ibbytes
FROM information_schema.tables WHERE engine = 'InnoDB') A;

These numbers assume that every block of data and index for InnoDB has a place in the doublewrite buffer to go to.

A more realistic number may be this:

SELECT
    ibbytes InnoDBSpace,
    ibbytes/POWER(1024,1) InnoDBSpaceKB,
    ibbytes/POWER(1024,2) InnoDBSpaceMB,
    ibbytes/POWER(1024,3) InnoDBSpaceGB,
    ibbytes/POWER(2,14)   InnoDBBlocks
FROM (SELECT (variable_value * POWER(2,14)) ibbytes
FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data');

These numbers are going by the actual working set in the InnoDB Buffer Pool

I hope this little explanation helped you !!!


MySQL service stuck at starting after increasing 'innodb_log_file_size' Cross posting this question here, since I haven't gotten an answer there: https://superuser.com/questions/1476105/mysql-service-stuck-at-starting-after-increasing-innodb-log-file-size

Good day everyone, We are trying to install the 'ESET Enterprise Inspector' on a server, which requires mysql. we installed MySQL Server 8.0 in the requirement page it says we have to use these parameters:

But when we try to start the mysql service, the progress bar goes to end (for two minutes) and it says "the service did not respond to the start or control request in a timely fashion" and it is stuck in starting status. the eset installer doesn't continue to install if we haven't used those parameters. The server has 32G of ram and we use innodb_buffer_pool_size=26G and innodb_log_file_size=13G

Does anyone know why the service doesn't start? Here is the my.ini file. Thanks in advance.

I recommend you change back that setting.

The problem is that the old versions of MySQL did not have a way to change that setting without some extra manual effort, namely removing the log files.

Unless the setting was terribly small, the failure to change it won't impact performance much.

Hmmm...  Are you upgrading from some old version to 8.0?  If so, there are other steps.

Meanwhile,...

  Increase the value of open_files_limit to at least 30000.

That seems very high, unless you expect to have thousands of tables, which is another problem to discuss.

  Change the value of innodb_flush_log_at_trx_commit to 0.

There are 3 options for that variable; I wonder why they picked the worst.


Will deleting data in master automatically delete data in slave? I have data replication setup on MySQL.  If I delete data on the master, will replication know data was deleted and automatically delete the same data on slave?

Yes, it will automatic apply the delete on the slave, with some restrictions:

If you have replicate-do-db=db_name enabled, it will automatically filter out the statements to the given database names
If you have replicate-ignore-db=db_name enabled, it will automatically filter out statements to the given table names