Considerable size difference between mysql versions We usually employ different MySQL versions for different scenarios. And I realized that similar databases, whith the same amount of tables and data, just because of different mysql versions differs in size from 43M to 223M. (~5x more)

My goal is understand why of that 5x more disk space required..

Some version 5.5.46 have this size:

du -hs /var/lib/mysql/
43M

And some 5.7.11 habe this size:

du -hs /var/lib/mysql/
223M

Inside of it, we use to have those default datafiles like: ib_logfile0, ib_logfile1, ibdata1, mysql..

Within 5.5.46 thay usually have this sizes respectively:

5.1M   ib_logfile0
5.1M   ib_logfile1
26M   ibdata1
6.5M   mysql

..but within 5.7.11 are waaay bigger:

48M   ib_logfile0
48M   ib_logfile1
76M   ibdata1
12M   have also this ibtmp1
25M   mysql

In order to understand all that difference, I took a look to release notes where Oracle writes down what they're changing: https://dev.mysql.com/doc/relnotes/mysql/5.5/en/

But could not find something conclusive.
I appreciate your help on that.

I suggest looking at the 5.6/5.7 release notes to see what changed from 5.5.

ib_logfile file size are because the innodb_log_file_size changed from the outrageously small value of 5M to something that actually performs when people update any modest amount of data.

The ibdata1 increase in size might be a result of utf8_mb4 being the default.

System tables support more features and have some bigger fields.

Overall, its not that much. Are you really running on an embedded processor?


Error updating temporal tables I have implemented the System Versioned Temporal Tables on the table "Orders". The application is using different access patterns to modify the data in this table. There are direct statements coming from the application or the application is running long batches in explicit transactions where it makes several changes to multiple tables. The update on the "Orders" table is not the first statement in those long batches! So, sometimes we are facing the following error.

  Data modification failed on system-versioned table "Orders" because
  transaction time was earlier than period start time for affected
  records.

Apparently this is a standard behavior of System Versioned Temporal Tables. https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-2017#how-does-temporal-work

Is this something that will always need to be handled in an exception routine? Or is Microsoft considering to change that behavior?

--Script to simulate the error message:

CREATE TABLE dbo.Orders
    (
      [OrderId] INT NOT NULL PRIMARY KEY CLUSTERED
      , [OrderValue] DECIMAL(19,4)
      , [ValidFrom] DATETIME2 (2) GENERATED ALWAYS AS ROW START
      , [ValidTo] DATETIME2 (2) GENERATED ALWAYS AS ROW END
      , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
     )
     WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.OrdersHistory));
     GO

     INSERT dbo.Orders ([OrderId], [OrderValue])
     VALUES (1, 9.99), (2, 9.99);
     GO

     SELECT * FROM dbo.Orders;
     GO

       --Run first query
   BEGIN TRAN
      WAITFOR DELAY '00:00:15';
      UPDATE dbo.Orders
      SET [OrderValue] = [OrderValue] + 1;
    COMMIT TRAN

       --Run Query 2 in another session sql server
    BEGIN TRAN
       UPDATE dbo.Orders
       SET [OrderValue] = [OrderValue] + 1;
    COMMIT TRAN

Is this something that will always need to be handled in an exception routine?

The system generated start and end times are keyed to the server's system time at the moment the BEGIN TRANSACTION is executed.  If you have long-running transactions, you should:

Build in exception handling that can retry the transaction.
Rework the offending code so it executes more quickly.  This may sound like a lot of work, but it will be necessary to do some level of rework to handle temporal-based exceptions correctly.  If you're going to do rework, you may as well determine why the transaction is taking so long, and mitigate that behavior.

  Or is Microsoft considering to change that behavior?

There is no way to know what Microsoft will do in future versions of their products unless they have made a public announcement that is not covered by a nondisclosure agreement.  They might modify the temporal table functionality at some point in the future such that the start and end dates are reflective of the current system time at the point of insert/update, however that is not currently how it works.


How to UPDATE a column to SET previous row value to NULL cells? I have a table as

id  value
1   music
2
3   movie
4
5
6   book
7
8

Some values are missing, and I want to assign the value of previous row to produce

id  value
1   music
2   music
3   movie
4   movie
5   movie
6   book
7   book
8   book

Sample schema and data

create table tbl (
  id int,
  value varchar(10)
  );
insert into tbl select
  1,   'music' union all select
  2,   ''  union all select
  3,   'movie' union all select
  4,   '' union all select
  5,   '' union all select
  6,   'book' union all select
  7,   '' union all select
  8,   '';

Query (SQL Fiddle)

update tbl join (
    select curr.id, (
        select prior.value
          from tbl prior
         where prior.value > ''
           and prior.id < curr.id
      order by prior.id desc
       limit 1) value
      from tbl curr
    where ifnull(curr.value,'') = ''
  ) g on g.id = tbl.id
set tbl.value = g.value;

Results

select * from tbl

| ID | VALUE |
--------------
|  1 | music |
|  2 | music |
|  3 | movie |
|  4 | movie |
|  5 | movie |
|  6 |  book |
|  7 |  book |
|  8 |  book |


MySQL database not loading table data My centOS 7 server had an unexpected shutdown. For some reason one of the mysql database shows that all tables are empty, but if I browse the files in /var/lib/mysq/database I can see the files(.frm and .ibd and the other files) opening them with a text editor I can read some of the data in it.

What's wrong here? why is one of my database not reading the files or loading the data?

Edit: I still have all my files related to the database such as ibdata1, all other databases/schemas are working fine except for one. That one database I can browse the tables but loading the data shows empty tables.

First attend to the error for which the server had unexpected shutdown. Without attending this you can not move ahead.

And then for the database / tables missing data...

1. Log files are to start with.

Find the date/time range in which those particular .frm and .ibd files and the ibdata1 are last updated (not last accessed) and look for all entries of all relevant log files. Hope you can check all relevant...

Once the lines of log files with issues are identified, you yourself can correct them or else just post them here

2. Look at
https://dev.mysql.com/doc/refman/5.7/en/repair-table.html


Is it acceptable to have a surrogate/primary key of one table be the primary/foreign key of another table? For my purposes I'm trying to apply this to Microsoft SQL, but would like to know if there are restrictions for other DBMS regarding this.

I don't know how to represent UML as ascii characters so if someone has a tool suggestion to do so please add in comments.

Example of the scenario below.  Note that I'm using composite keys.

building
------------------------------------
+ buildingFullName:String(30) <<PK>>
+ buildingId:Int <<SK>> <<PK>>

buildingRoom
------------------------------------
+ buildingId:Int <<PK>>  <<FK>>
+ roomNumber:String(10) <<PK>>
+ roomId:Int <<PK>> <<SK>>

buildingRoomAsset
-------------------------------------
+ roomId:Int <<PK>> <<FK>>
+ roomAsset:String(9) <<PK>> <<SK>>

If you are using composite primary keys, your foreign key references would also need to be composite keys.  If the primary key of building is (buildingId, buildingFullName) then buildingRoom would need to have both a buildingId and a buildingFullName that would be the foreign key to building.  Similarly, if (buildingId, buildingRoom, roomId) is the primary key of buildingRoom, you would need all three of those columns in buildingRoomAsset in order to create the foreign key.  Defining the foreign keys this way doesn't make sense.

If you are adding a surrogate key to the table, you would almost always want to define that column and that column alone as the primary key of the table.  You can add additional unique constraints to ensure that things like the buildingFullName are unique within the table.  Adding the buildingFullName to the primary key not only forces the buildingFullName to be pulled through to all the child tables, making it much more difficult to update the data, it means that you can have many buildings with the same name which seems unlikely to be what you're after.

Additionally, I would strongly suspect that you want an asset table that stores information about assets and then a roomAsset table that maps a roomId to one or more assetId values.  That will make life much easier when an asset gets moved from one room to another among other use cases.

If it were me

building
------------------
+ buildingId <<SK>> <<PK>>
+ buildingFullName

room
------------------
+ roomId <<SK>> <<PK>>
+ buildingId <<FK>>
+ roomNumber

asset
------------------
+ assetId <<SK>> <<PK>>
+ assetName

roomAsset
------------------
+ roomAssetId <<SK>> <<PK>>
+ roomId <<FK>>
+ assetId <<FK>>


How to run update queries saved in a permanent table? Permanent Table Name: PermaTable1
Values:

Question is how to execute all update commands inside the table PermaTable1?

This is my query but it does not execute the update command.

DECLARE @Query1 Nvarchar(MAX)

SET @Query1= 'SELECT Query FROM dbo.PermaTable1'

EXEC(@Query1)

Hope you can help me. Thank you.

DECLARE @Query1 nvarchar(max) = N'';

SELECT @Query1 += Query + N';' FROM dbo.PermaTable1;

PRINT @Query1; -- to see the first 8,192 characters

-- EXEC sys.sp_executesql @Query1; -- uncomment this when you trust it

But I have questions. Shouldn't those update queries have where clauses? Otherwise each one is going to overwrite all rows and basically undo all the updates that came before it. Also are these update commands really stored in the same table they affect?


What is the purpose of %ORACLE_HOME%/dbs directory in Windows Environment? I see some files in there like an init.ora and opctrn11.h, but what is the purpose of this directory, if we use %ORACLE_HOME%/database to store the p/spfile instead?

$ORACLE_HOME/dbs is used to store the spfiles, init files and password files on Linux and Unix systems. But as you pointed out the dbs directory not used on windows servers. So on a Windows servers it is probably not needed.


Instead of insert trigger VS For insert Triggers in SQL server Edited: My question is since there is an insert statement in the instead off insert trigger, then it should call insert trigger from inside the instead off insert trigger and which should avoid inserting row in the testtable. What my observation is that for insert trigger is firing but after instead off trigger which already has inserted the new row in the testable. For insert trigger is raising error but instead off trigger has already inserted a row in testtable. Why for insert trigger is not called from inside the instead off insert trigger?

I have two triggers on the table called TestTable. One is Instead of insert trigger and second is After insert trigger. First instead of insert trigger will fire and then for insert trigger.What it does that it inserts new row in TestTable and CopyTable, but not in AuditTable.

My question is why insert trigger is not fired from instead of trigger? It should because it is also using insert query in it. Why TestTable get the new inserted row?

My table script

CREATE TABLE [dbo].[TestTable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL,
    [Age] [int] NULL,
    [DOB] [date] NULL,
    [Address] [nvarchar](50) NULL,
 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED

My SQL Query:

Insert into dbo.TestTable values ('Waleed',24,'10-2-2015','Lahore');

My instead of insert trigger is:

ALTER TRIGGER [dbo].[utInsteadoff]
   ON  [dbo].[TestTable]
   Instead of Insert
AS
    Declare @Name as nvarchar(50)
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    Insert into dbo.TestTable (Name,Age,DOB,Address) Select Name,Age,DOB,Address From inserted;
    Insert into dbo.CopyTable (Id,Name,Age,DOB,Address) Select Id,Name,Age,DOB,Address From inserted;

END

After insert Trigger is:

ALTER TRIGGER [dbo].[insertTrigger]
   ON  [dbo].[TestTable]
   AFTER Insert
AS
 Declare @Name as nvarchar(50)
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
     Set @Name = (Select Name from inserted)

 If (@Name = 'Waleed')
    Raiserror('You Cannot Enter Waleed Name',10,1)
  Else
 Begin

    Insert into dbo.AuditTable (ID,Name,Age,DOB,Location)
    Select ID,Name,Age,DOB,Address from inserted
    -- Insert statements for trigger here
end
END

My question is why insert trigger is not fired from instead of trigger? It should because it is also using insert query in it. Why TestTable get the new inserted row?

The INSTEAD OF trigger replaces the original insert. So the code:

Insert into dbo.TestTable (Name,Age,DOB,Address)
Select Name,Age,DOB,Address From inserted;

...is executed instead of the original insert:

Insert into dbo.TestTable values ('Waleed',24,'10-2-2015','Lahore');

The actions in the instead of trigger are completed before constraints are checked, and before any AFTER triggers fire.

When the instead of trigger completes, a row has been added to the TestTable and CopyTable tables. Note though that the transaction is still active at this point, so the changes are not yet permanent.

Next, the after trigger fires. It sees the name 'Waleed' in the inserted table, prints a message, but does not rollback the changes made so far in the transaction (including the rows added by the instead of trigger). No row is added to the AuditTable.

If the condition that checked for the name 'Waleed' also performed a rollback, everything would be rolled back, and no rows would be added to any table.


Converting table to a 2NF relation I'm currently going through the process of learning about database normalization & having a crack at creating my first relational database. It was going well until I hit the following problem.

In a non-normalised form my justifications table would like like this:

| justification_id | user_id | question | response | action | category | category_id | submitted_at |
-----------------------------------------------------------------------------------------------------

My research & thought process led me to the following:

justification_category

| justification_id | category | category_id |

justification_response

| justification_id | question | response |

justification_action

| justification_id | user_id | action | submitted_at |

How does a justification work?

When a user performs an action on a category ( the current categories being tickets, orders, accounts ) a justification is required by the user to state why they are performing that action. I.e. account deletion

An action can have multiple questions attached resulting in multiple responses. For example: Deleting an account would ask the questions: Have you followed our internal steps? Why do you need to perform this action? etc. This is why I proposed the justification_response table because it will not store any NULL fields.

The justification_action table will log the action performed, the user_id ( this is the submitter of the action ) & the time of the action.

Questions

I currently have 3 potential categories where a justification is required on an action, they're tickets, orders & accounts. With this in mind would it be a better approach to have 3 separate tables to describe each category? Would this approach meet 2NF?

I feel I am far away from a 2NF relation. This is because I'm constantly re-using the justification_id as either a FK or a PK. How far away am I?

Kudos for trying to get it right, and understand the principles.  I've been doing this for years and still find it difficult to label existing structures with precisely accurate relational theory terminology at times.

Normal forms, it's important to note, only describe individual relations (that is, "tables," in SQL), and not the relationships between them... so the frequent appearance of justification_id does not in any sense move your tables further from 2NF.  Indeed, these are all tables of attributes about justifications, so it's only sensible that the justification would appear.

The justification_id seems to be a candidate key, or at least a prime attribute,  in each table... isn't it?  Candidate keys, pretty much by definition, can't have a denormalizing effect.  It's the other attributes that must be analyzed.

I'm confused about category and category_id.  Would "category" be (e.g.) "ticket" and "category_id" be (e.g.) "27"...?  If so, your naming is a little counter-inituitive, and you're going off in a direction that normalization doesn't really address, but that may cause you difficulty later -- you're mixing data domains in a single attribute (column).

By that, I mean this: the value of "category_id" might be an id from the "ticket" table or an id from the "order" table, etc., depending on which type of category.  If I am right about what these columns are being used for, then you don't want to do this, because (among other reasons) it is impossible for the database to correctly impose a foreign key constraint on this column.  As a rule of thumb, a value in a column should mean the exact same thing regardless of what's contained in any other column.  It might be more correct if there were junction tables, "ticket_has_justification" (with a ticket_id and justification_id), "order_has_justification" (order_id, justification_id), etc.  You can still outer join all justifications to the various types of things they justify, but this is a cleaner approach, at least in my opinion.

The "questions" column also might be more appropriate in a "question" table, with justification_response having a question_id column referencing it, but here again, this isn't really addressed by normalization -- whether it's stored in this table as the words of the question, or as a surrogate for the words (question_id), the "question" column is still a prime attribute, with (justification_id, question) being the candidate key and response being dependent on both, so this one is at least 3NF, despite having what looks to me like room for improvement.

Fundamentally, though, I don't see what isn't 2NF about any of these... depending on exactly what "category" and "category_id" mean.


Why is there no data loss with mysql innodb_flush_log_at_trx_commit set to 0? Here is the MySQL info:

mysql> show variables like 'innodb%flush%log%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_timeout    | 2700  |
| innodb_flush_log_at_trx_commit | 0     |
+--------------------------------+-------+
2 rows in set (0.00 sec)

mysql> select version()
    -> ;
+-----------+
| version() |
+-----------+
| 5.6.14    |
+-----------+
1 row in set (0.00 sec)

Then I insert something:

mysql> insert into x select now();
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from x;
+---------------------+
| now()               |
+---------------------+
| 2014-08-20 18:59:09 |
+---------------------+
1 row in set (0.00 sec)

mysql>

I immediately kill mysqld to make it crash:

:/data/mysql_dbf # killall -9 mysqld
:/data/mysql_dbf # /usr/local/bin/mysqld_safe: line 166: 28859 Killed                  nohup /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql_dbf --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql_dbf/.err --pid-file=/data/mysql_dbf/.pid < /dev/null >> /data/mysql_dbf/.err 2>&1 >> /data/mysql_dbf/.err 2>&1 >> /data/mysql_dbf/.err 2>&1 >> /data/mysql_dbf/.err 2>&1 >> /data/mysql_dbf/.err 2>&1 >> /data/mysql_dbf/.err 2>&1 >> /data/mysql_dbf/.err 2>&1
140820 18:59:22 mysqld_safe Number of processes running now: 0
140820 18:59:22 mysqld_safe mysqld restarted

Then I check it again:

mysql> select * from x;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> select * from x;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test

+---------------------+
| now()               |
+---------------------+
| 2014-08-20 18:59:09 |
+---------------------+
1 row in set (0.02 sec)

No data was lost. Can someone explain why?

Edit 1

With the help of @jynus, we are going to test for sufficient data.

i=0;
while true;
do ./bin/mysql --defaults-file=my.cnf -umysql -pmysql test -e "insert into x select now()";
    if [ $i -eq 100 ];
    then killall -9 mysqld;
        sleep 3;
    fi;
    ./bin/mysql --defaults-file=my.cnf -umysql -pmysql test -e "select count(*) from x" >> trace.log;
    if [ $i -gt 110 ];
    then break;
    fi;
    ((++i));
done

And the output shows we are facing data loss

......
count(*)
99
count(*)
100
count(*)
101
count(*)
102
count(*)
103
count(*)
3
count(*)
4
count(*)
5
count(*)
6
......

With innodb_flush_log_at_trx_commit = 0 there is not a guaranteed data loss (such an option would be nonsense), but a non-guarantee that data will not be lost in the last innodb_flush_log_at_timeout seconds (between fsyncs).

To test how much data you are losing, you need to write several times per second and then kill mysqld just after some of those writes have been successfully committed in an automatic way. You will suffer between 0 and innodb_flush_log_at_timeout(maybe a bit more than that; innodb_flush_log_at_timeout is by default 1 second, 2700 in your example) seconds of lost transactions due to changing the durability options.

By the way, some of us prefer innodb_flush_log_at_trx_commit = 2 as it provides the same performance and durability promises, but it does the buffering at OS level, maybe minimising data loss in case of a MySQL-only crash.

Independently of the durability, you will never get corruption or end up in an inconsistent state thanks to the REDO log and the double write buffer, unless there is a hardware fault.

Edit: It seems that the why is not yet clear. Let me elaborate:

Every innodb_flush_log_at_timeout seconds mysql calls the OS function fsync(), which -except on those operating systems/virtual machines where it is implemented as a null operation (another funny story for other time)- it should makes sure that a particular file descriptor (the one for the transaction log) has everything which has been written to not on memory (the filesystem cache) but really on disk (or on hardware cache).

That doesn't take from MySQL or the operating system from writing really from writing to disk from the log buffer or the operating system cache from time to time (actually, they will want to do that to avoid I/O queuing). So fsync is only a limitation in terms of "everything should be written now", the log is actually written when IO is available (even if the transaction has not been commited!) to make everything more performant. If you want your data not to be durable, either do not commit your transactions (not recommended) or use a non-durable, memory-only engine, like HEAP.


Maximum number of TEXT/BLOB columns per table/performance of table with many of them? I've created a system that generically imports CSVs into a database table with metadata columns for keeping track of when a CSV record has been changed, and it allows filtering on CSV columns instead of having to read the entire file each time. Right now, the structure of the table is essentially:

id, csv_id, hash, date_modified, column_0, column_1, ..., column_63, column_64

The columns are all VARCHAR(255) because I wanted the maximum number of columns with the maximum amount of size. My database is UTF-8, so each of the column sizes has to be multiplied by three and that value has to be below 65,535. This is the size of my current schema:

255 * 65 * 3 = 49,725

255 (*3) bytes is too small because URL columns might be stored in the database that can be up to 512 bytes or more. My question is if I made the current schema of:

column_0 (TEXT), column_1 (TEXT), ..., column_63 (TEXT), column_64 (TEXT)

Is there a maximum number of TEXT columns that can be on a database table? I'm using MySQL 5.6 right now but I'll be migrating the database to Amazon Aurora. My gut says the performance of this would be horrendous, and I do need to filter against the content of the columns.

If the performance hit isn't too large for a smaller subset of columns, I'm considering a hybrid solution:

id, csv_id, hash, date_modified, data LONGTEXT, column_0 VARCHAR(512), ..., column_19 VARCHAR(512)

Where data is a JSON array and the 20 columns are indices mapped elsewhere in the system, but if changing them all to TEXT (since the columns can be as longer, theoretically), how would a design of:

id, csv_id, hash, date_modified, data LONGTEXT, column_0 TEXT, ..., column_19 TEXT

Differ?

If you lump all the column_## columns into a JSON field, you can have a limit of 4GB.

The question of how much gets complicated.  InnoDB has 4 ROW_FORMATs:  REDUNDANT, COMPACT, DYNAMIC, COMPRESSED.  I think you could have 400 columns of VARCHAR(255) or TEXT (or ...) in ROW_FORMAT=DYNAMIC.  That format uses a 20-byte pointer in the record to where the actual text is stored.

If you changed the block size from the default 16KB to 32KB, you could get about 800 columns.  Going to 64KB would hit the limit of 1017 columns (in 5.6.9).  Changing the block size is non trivial, so I don't recommend it.

More Limits.  (Hmmm... maybe I should add your question there.)

These two statements do go together:  "performance of this would be horrendous" and "I do need to filter against the content of the columns".  This is because DYNAMIC has an extra disk fetch for each column you need to 'filter' on.  On the other hand, in some situations (short text, not too many columns, etc), the entire text of some of the columns may be stored inline.

COMPACT might actually be better.  This format tries to keep the text in the record.  But if the record would become bigger than about 8K, it peels off the longer cells for off-block storage.  It will put the first 768 bytes of record inline.  From that, you are limited to about 10 columns.  (Try 12 columns, it will spit at you.)

I believe you will need innodb_file_per_table=1 for DYNAMIC.

How will you be 'filtering'?


Allocation strategy where tables regularly grow and shrink by large amounts I have a SQL Server 2014 database with 100+ tables that have a 5% growth rate over the course of a year. Lots of reading, very little writing.

This database is going to expand by another set of 20+ tables that regularly (quarterly) grow overnight by almost as much as the entire rest of the database combined.

Those tables are completely emptied at the end of processing in a few hours.  The data in those tables is fairly expendable, but need to be in the same database as the rest for referential integrity.

For some sample numbers, let's say the first DB is 20GB and the subset of tables grow to 15GB and then are emptied.  So the actual size of the data fluctuates between 20 and 35 GB throughout the course of a few days.

What can I do in planning this system to help account for this fluctuation?
Is this where filegroups would come into play?
What advantages / problems would I get moving this set of tables to their own filegroup?

Putting those staging tables on their own filegroup allows you to do piece-meal restores.  This helps get the database up-and-running more quickly in the event of a disaster recovery effort.

Having those tables in their own filegroup will allow you to place them on their own set of disks which may increase performance.


SQL Server 2008 R2 - How to export/migrate only tables and their PKs is there a way how I can only migrate/export table names with the PKs but no other relationships like FKs? So there is no dependency between the tables. And then import them to another database?

I would like to perform this between SQL Server 2008 R2 and Azure SQL.

You can try any of these methods given in How to copy tables from one database to another in SQL Server by Ahmad Yaseen:

  1) Using SELECT INTO Query
  the SELECT INTO SQL statement. This statement will create the tables in the destination database first, then it will copy the data to these tables. If you manage to copy the database objects such as the indexes and constraints, you need to generate script for it individually, after that you need to apply the scripts to the destination database.

  2) Using SQL Server Export / Import wizard

  Another method that can be used to copy tables from the source database to the destination one is the SQL Server Export and Import wizard, which is available in SQL Server Management Studio. You have the choice to export from the source database or import from the destination one in order to transfer the data.

  3) Using Generate Scripts

  SQL Server provides another way to generate script for the SQL Server databases with its objects and data. This script can be used to copy the tables’ schema and data from the source database to the destination one in our case.

  4)Using the ApexSQL Diff and ApexSQL Data Diff combination

  ApexSQL Diff is a useful SQL tool that can be used to find the differences between the databases from schema side and generate synchronization script to create these tables in the destination database in the correct order.

  ApexSQL Data Diff also is another SQL tool that can be used to find the differences between the databases from data side and generate synchronization script to insert the data in the destination database tables, taking into consideration the IDENTITY columns.


MySQL intermittent slow queries After recently migrating MySQL 5.1 to a new, faster server, I'm seeing occasional and seemingly random periods where some queries are running slowly. Despite a lot of investigation, I can't find the source of the problem.

I've listed a few concrete questions at the end of this writeup, but otherwise I'm not sure where to go at this point. If you can suggest any possible solutions or tools I can use, I would be very grateful.

Symptoms and Background

Every day or two, a small group of queries will appear at random in my slow query log, normally within a period of two or three minutes. These queries, which typically execute within 30-60 milliseconds, are taking vastly longer -- in the range of 6 to 20 seconds. The slow query log doesn't indicate any lock time for these queries.

The problems became obvious after migrating to a new database server. Both servers run MySQL 5.1 under CentOS 6, and both use SSDs for database storage. The new server, unlike the old one, runs two SSDs using Linux software RAID-1. Although the hardware has changed (different datacenter, different network), the software hasn't. The CentOS and application versions were the same at the time of the migration. The application code has also remained the same.

My Analysis/Troubleshooting

I have no reason to believe the queries themselves are problematic or that the database is experiencing performance problems; these aren't complex or unoptimized queries. They normally execute in <100ms, and the database server has a typical load average less than 1.0.
I don't believe that all queries are affected during these periods of slowness. The server sees, on average, about 8000 queries per hour. But my slow query log is showing only a handful (maybe 5-10) during the slow periods.
The queries appear to have nothing in common. They are mostly select statements against several different databases involving tables that use both InnoDB and MyISAM engines. Sometimes update queries will also appear.
I tried reducing the slow query time to 2 seconds (from 5 seconds) to find out if there were more periods of slowness leading up to the very long-running queries. I don't see that happening. The slow periods seem to start and stop suddenly and inexplicably.
Suspecting that IO was a problem, I setup the sar utility to capture system stats every minute. I can find no correlation between the periods of slowness and the system or IO load reported by sar. The server appears to be nearly idle when the slow queries are logged.
I examined all cronjobs as well as all logfiles in /var/log, but can't find anything interesting happening at the same time as the slowness.
Just to be safe, I disabled r1soft (backup software) and disabled other backup-related tasks. I also reduced the IO priority of Sphinx, a search engine that runs on the same server. Neither had any effect.
I tried to disable the query cache, as I've read that it can cause strange lock situations. No effect.
I upgraded MySQL to version 5.5, just to see what would happen. This also had no effect on the slow query issue.

Questions

Is there any way I can find out if some strange, intermittent hardware issue with the new server is in play here? As I wrote above, I haven't noticed anything while carefully checking the system logs.
Is there any chance that network latency or packet loss has any role in this? I assume not, as the query time recorded in the slow query log should exclude any network delay, right?
I see that MySQL 5.5 offers a Performance Schema feature that is new to me. But it's not obvious to me how this can be used. Is there some way I can use this to troubleshoot the problem?

Thanks very much for any help with this frustrating problem!

Short solution: The problem was IO-related. It was solved by moving to a new, more modern system with faster SSDs.

Longer version:

I invested a lot of time over several months trying to get to the bottom of the performance issue described above. As a stopgap measure, I did everything possible to prevent concurrent queries that caused a lot of IO (such as backups and search engine indexing). However, this never really solved the problem or answered the questions I posted -- it only covered up the underlying issue with one IO-intensive process blocking database activity.

My "answer," if you can call it that, was finally realized by moving to more modern hardware. Unlike the old system, which used consumer-grade Micron SSDs, the new server uses IBM datacenter SSDs and a faster processor. Otherwise, the software configuration is identical as in the system tested above (same OS, same use case for MySQL, same mdraid RAID-1 config, etc.).

I can only assume that the new system's better SSDs and/or more modern motherboard and chipset are better at handling concurrent operations. I no longer experience slow queries of any sort, no matter what's going on with the database server. Database operations do not seem to be affected by disk activity nearly like before.

So, if you're experiencing strange slowdowns with database queries and can't find any application-level reasons for this, don't forget to look at the underlying hardware as a possible source. Try renting a new dedicated server or VPS for a month from a different hosting provider, replicate your software configuration, and give it a try. Worked for me!


SQL Server 2012 SSIS : retrieve line number in Flat File Connection Manager I'm developing a ETL, that uses a FFCM to parse data from a csv and loads it in a SQL Server table.

Some float data aren't parsing properly, so I need to see the result in the table and get back to the flat file to see how it is there.

I'm reading the file in Notepad++, it would be much easier to find data if I had its line number.

Is it possible for FFCM/Flat File Source to add the line number to the list of columns, so I can save it in the table?

I found it!

http://weblogs.sqlteam.com/jamesn/archive/2008/02/13/60509.aspx

It's not a CCFM or a Flat File Source, but it works pretty fine and it's elegant.


Create View giving SQL7029 If relevant, I'm using IBM i V7R1M0.

I have some code in C#:

var generateSqlSql = $@"
CALL QSYS2.GENERATE_SQL(
    '{obj.Name}',
    '{obj.Library}',
    'VIEW',
    CREATE_OR_REPLACE_OPTION => '1',
    HEADER_OPTION => '0',
    COMMENT_OPTION => '1',
    PRIVILEGES_OPTION => '0',
    LABEL_OPTION => '0'
)";
...
//get results
//split on semicolon
//change library
//write back to database

The sql that it's then trying to write is:

CREATE OR REPLACE VIEW AMMLIBC.BBI_USER_MY_VIEW FOR SYSTEM NAME         BBIU_00001 (
/* view columns */ )
AS
/*view definition */
RCDFMT BBIU_00001

When I try to run it, it gives the following error:

iDB2SQLErrorException: SQL7029 New name BBIU_00001 is not valid.

...Why? What's causing this, and how do I make it work?

Found the problem.

The view already existed in both the original library and the target library, but with different system names.

Adding this line fixed the problem:

SYSTEM_NAME_OPTION => '0',


Enable MySQL to use more than 1 core (multicore) I am running a very intensive MySQL query and noticed that only 1 core is being used to 100% while the other cores are idle. The system is running Ubuntu 12.04 and MySQL 5.5.28

Is there a way to force MySQL to use all cores?

MySQL's implementation won't allow you to use more than one core per Server Thread.

I recommend divide-and-conquer strategy: break up your query in multiple (hopefully smaller, independent) jobs and start again.


How to go about testing your network for an AlwaysOn implementation So i'm taking my first steps into deploying an AlwaysOn solution at my company. I've set it up in a test environment so know the setup/config steps required, however what we don't know is how our systems are going to perform once AlwaysOn has been switched on in production, as we haven't done any performance testing with production-like load.

We have about 46 databases that need to be synchronized across to DR - totalling around 1 TB in size. This is a data warehousing system and is used for reporting during the day (all reads) and batch loads at night (all writes).

We are not using AGs to provide HA - this is for DR across two data centers using asynch mode, but with added bonus of up to date read-only data on DR for the analytics team to do their ad hoc analysis without interfering with regular business reporting. In a DR scenario failover would be manual.

Reading posts about AG performance, I know there are things we could tweak such as max worker threads and using a separate NIC for mirror traffic to improve performance - so we have options should we run into issues. I haven't really ever looked into network latency/bandwidth performance metrics before so specifics would be helpful.

How should we run an Availability Groups network test, and what network latency or bandwidth numbers should we watch during the test?

The easiest way to test AG network throughput is to rebuild one of your largest indexes.

While the index rebuild is running (and afterwards), measure the latency for each replica using any of these scripts:

Measuring Availability Group synchronization lag - Derik Hammer
Monitoring Availability Group Replica Synchronization - Jonathan Kehayias

This will give you a rough idea of how far behind each replica gets. If performance doesn't meet your goals, then you can drill into more granular measurements.

Keep in mind that network latency & bandwidth isn't the only issue that can affect replica latency. There's also log file write latency and redo thread throughput on the replicas. Starting at the high level of just checking synchronization lag will tell you whether or not there's a problem at all first, and then you might be able to just avoid drilling down into details altogether - especially since you wrote that this is a DR-only setup, not for HA.


Most Efficient Strategy to Insert Many Records Thanks for reading this.  I'm trying to work out the best strategy to keep my customer table up to date.

I have a customer table plus 6 daily transaction tables that get new data every day.  If there is a new customer, they might come in the transaction table so I need to find the customers in the transaction table that aren't in the customer table and add them.  This seems right to me but I wanted to get input from others because it seems really inefficient with "select the customer that is not already in the primary table" when the customer table is so big.   The data is loaded every day.

Here is my command:

insert into customer (customer, reptdate)
(select distinct customer, getdate() as reptdate
 from T_TX_CUST
 where not customer in (select customer from customer ))

The thing is, as part of the transaction table processing, I go through the tables row wise in C#.  I could do a select there to see if the customer existed and then add them if they don't.  The programmer in me really wants to do it this way but using SQL would be much simpler but I'm afraid of the cost.   Is using a set of SQL commands in a stored procedure faster than doing a select and then an insert on a record by record basis?

I would really appreciate any thoughts or advice.

As a programmer, your time is valuable too. For a daily load process I would start with something as simple as possible and only make it more complicated if you know that you need to improve performance. I don't know your exact requirements, but for a load that happens once per day you probably don't need your code to finish in seconds.

You have six transaction tables, so you can do a small proof of concept for just one table. I'm going to use T_TX_CUST as an example.

Load new data from your customer data source into the customer table
Load new data for T_TX_CUST into a temporary staging table.
Use the staging table to find and insert any missing customers into the customer table.
Load new data from staging into T_TX_CUST.

The advantage of this process is that it's simple and it doesn't matter how much data you already have in the T_TX_CUST table. All that matters is how much data you load per day. Try it out, and if it meets your needs then implement it for all of your tables. One trick to speed things up is to run multiple queries concurrently. For example, you may be able to load multiple staging tables at once or to load data into multiple transaction tables at once.


T-SQL query to merge all rows into one row I have a T-SQL query, which pulls details from several locations (Something like below):

IF OBJECT_ID('tempdb..#mytable') IS NOT NULL DROP TABLE #mytable;

CREATE table #mytable
    ([Locno] varchar(6), [Stories] varchar(6), [Type] varchar(13))
;

INSERT INTO #mytable
    ([Locno], [Stories], [Type])
VALUES
    ('54', '2', 'TH'),
    ('65', '5', 'OB')
;

SELECT * FROM #mytable

Locno | Stories | Type
54    | 2       | TH
65    | 5       | OB

I want to bring all the rows into a single row. It's not a PIVOT or CONCATENATE, but something like below (I might have n number of locations):

Locno_1 | Stories_1 | Type_1 | Locno_2 | Stories_2 | Type_2
    54  | 2         | TH     |  65     | 5         | OB

Can someone guide me how to do the same?
TIA

For an arbitrary number of columns into a single row, you would have to build the dynamic SQL yourself.  There may be cleaner ways than this, but the below is a start point.

Note that, unless the query optimiser can do something nice for you (which I doubt), it will scan the original data a huge number of times (at least 2 + colcount * rowcount):

DECLARE @query AS NVARCHAR(MAX);

SELECT @query = N'
    WITH r AS (SELECT _r = ROW_NUMBER() OVER (ORDER BY (SELECT 1)), * FROM #mytable)
    SELECT
';

;WITH
 r AS (
    SELECT r = CONVERT(SYSNAME, ROW_NUMBER() OVER (ORDER BY (SELECT 1)))
    FROM #mytable
 )
,c AS (
    SELECT c = REPLACE(name, ']', ']]')
    FROM sys.dm_exec_describe_first_result_set('SELECT * FROM #mytable', '', 0)
 )
SELECT @query = @query + STUFF((SELECT N',[' + c + N'_' + r + N'] = (SELECT [' + c + N'] FROM r WHERE _r = ' + r + N')' + NCHAR(10)
                                FROM r, c
                                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
                                1, 1, N'')

PRINT(@query);
EXECUTE sp_executesql @query;

This kind of thing is much-better handled in a presentation layer, rather than at the database layer.  C#/.NET could munch through an IDataReader and represent to a one-row HTML table far more reliably than SQL:

using (var conn = new SqlConnection("Data Source=.; Integrated Security=SSPI"))
using (var cmd = new SqlCommand("SELECT * FROM myRealTable;", conn))
using (var reader = cmd.ExecuteReader())
{
    var htmlTableHeader = new StringBuilder();
    var htmlTableRow = new StringBuilder();
    var rownum = 1L;

    while (reader.Read())
    {
        for (var f = 0; f < reader.FieldCount; f++)
        {
            htmlTableHeader.AppendFormat("{0}_{1}	",
                                         WebUtility.HtmlEncode(reader.GetName(f)),
                                         rownum)
                           .AppendLine();

            htmlTableRow.AppendFormat("{0}	",
                                      reader.IsDBNull(f)
                                        ? "-"
                                        : WebUtility.HtmlEncode(reader.GetValue(f).ToString()))
                        .AppendLine();
        }
        ++rownum;
    }

    Console.WriteLine("

");
    Console.WriteLine(htmlTableHeader.ToString());
    Console.WriteLine("

");
    Console.WriteLine("

");
    Console.WriteLine(htmlTableRow.ToString());
    Console.WriteLine("

");
}


Visual Studio 2010 deployment script creates objects in wrong order - trigger after stored procedure I created a view that selects from multiple tables in a complex query.

Due to the complexity, I created insert/update/delete INSTEAD OF triggers to handle these operations on the view.
I also created stored procedures that update the view.

When I build the VS2010 database project, the SQL script contains the CREATE commands in this order: (1) the view, (2) the stored procedures, and then (3) the triggers.

Now, when I run the deployment script, I get an error:

*** SQL01268    D:\scripts\vs2010\Database.sql (28494,0).Net SqlClient Data Provider:
Msg 4405, Level 16, State 1, Procedure MyViewSave, Line 18 View or function 'MyView' is
not updatable because the modification affects multiple base tables

If the triggers would be created before the stored procedure, there would be no problem.

How can I make sure the triggers are created before the stored procedures?

Thanks!

You may have run into a known issue with Visual Studio 2010:

Connect request
SO Question
MSDN forum thread

That said, Microsoft recommends you help Visual Studio detect the object dependencies it seems to be ignoring in its deployment script by:

  schema-qualify[ing] the names of objects that are involved in dependent relationships.

So if everything you are developing sits in the dbo schema, make sure to prepend the proc and view names giving you trouble with their schema name.  For example: dbo.MyView instead of just MyView. Visual Studio has a "Fully Qualify Name" feature under the Refactor menu when you right-click on an object that'll help you do that very quickly.

If that doesn't help, give Google a spin and take a look at some of the workarounds suggested on the pages linked above. Let us know what ended up working for you.


How to subtract records from a table based on their existence in another I have an Access 2010 table Assignment

key Empl    Number
key Year    Number
key Week    Number
key Proj    Number

It assigns employees to zero or more projects for given work weeks,
thus, all four columns make up the composite key.
I have another table, Import, having identical structure, containing data to be used to update Assignment according to different options.

Option 1
Keep all existing assignments, add completely new imports, and ignore any imports that already exist.

I can manage that using a simple append query:

INSERT INTO Assignment
SELECT *
FROM Import

So far, so good.

Option 2
As above, except where there's an employee-year-week match between assignment and import, replace the existing assignment with the import.

One approach to achieve that is to first delete all records with an employee-year-week match, then append the imports (as above).
The result should be to replace exact matches as-is, replace partial (employee-year-week) matches with new projects, and add completely new assignments.

I can isolate the matches via this query, called Existing:

SELECT a.*
FROM Import AS i INNER JOIN Assignment AS a
ON a.Empl = i.Empl AND a.Year = i.Year AND a.Week = i.Week

How can I delete records from Assignment that are also in Existing?

I've tried variations upon this

DELETE
FROM Assignment
WHERE IN (Existing)

all resulting in error messages.

By the way, it seems that, if there were such an animal, I could use a MINUS (or DIFFERENCE) query:

Assignment
MINUS
Existing

A minor variation on the approach would be to first delete the records with both an employee-year-week match and a project mismatch, then append the imports, as in Option 1. (Fewer deletions and additions, but same result.)

I can isolate those matches via this query, called Existing_exp:

SELECT a.*
FROM Import AS i INNER JOIN Assignment AS a
ON a.Empl = i.Empl AND a.Year = i.Year AND a.Week = i.Week AND a.Proj <> i.Proj

but I'm still left with the task of how to delete records from Assignment that are in Existing_exp!

It seems the answer was hiding in plain sight:

DELETE a.*
FROM Import AS i INNER JOIN Assignment AS a
ON a.Empl = i.Empl AND a.Year = i.Year AND a.Week = i.Week

or

DELETE a.*
FROM Import AS i INNER JOIN Assignment AS a
ON a.Empl = i.Empl AND a.Year = i.Year AND a.Week = i.Week AND a.Proj <> i.Proj

depending on which is most appropriate.

I don't know how I failed to see it first time around!


Mysql replication - Error No. 1146&1062 short version
In master master replication, How to have one master write data with odd IDs and the other one with even IDs? Is it the way to avoid conflict?
detailed version
I wanted to setup a MySQL master-master replication on two virtual machine I have on my system, to just play around with replication and get familiar with the concept.
I edited [mysqld] section of /etc/my.cnf of Master-1 and it looks like:
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
datadir         = /var/lib/mysql

log-bin         = mysql-bin
binlog-ignore_db= mysql
binlog_format   = mixed

server-id       = 1

my.cnf of the Master-2 is almost identical to Master-2 except server-id which I set to 2. Then I restart the mysql service, and create slave users in each VMs:
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'<the-other-VM-ip>'
IDENTIFIED BY 'somePass'

Followed to that I set the Master config in each VM:
CHANGE MASTER TO MASTER_HOST='<the-other-VM-ip>',
MASTER_USER='<replicator>',
MASTER_PASSWORD='somePass';

And finally start slave on both machines. I created a database in Master-1 and added a table with some value. When I commit and check in Master-2 I can see all the data. But When I do the same in Master-2, the changes are not committed to the Master-1. SHOW SLAVE STATUS \G in Master-2 show me the following error:
Last_Errno: 1146
Last_Error: Error 'Table 'mydb.taggregate_temp_1212047760' doesn't exist'
            on query. Default database: 'mydb'.
Query: 'UPDATE thread AS thread,taggregate_temp_1212047760 AS aggregate
       SET thread.views = thread.views + aggregate.views WHERE thread.threadid = aggregate.threadid'

I stopped the salve, wrote SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; in mysql and then start the slave and ask for the status, which I get a new error:
Last_SQL_Errno: 1062
Last_SQL_Error: Could not execute Write_rows event on table mysql.user;
                Duplicate entry 'localhost-root' for key 'PRIMARY',
                Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY;
                the event's master log mysql-bin.000001, end_log_pos 26231
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1

I believe this could be solve using auto-increment-offset = 2 in one config file, although I'm not sure about the procedure... beside not quite sure what SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; is and what it does, when Googled my first problem found it here.
I would be thankful for a description of the errors and how to overcome them.

I did as described here and it solved my problem.
So basically I added the following to [mysqld] section in my.cnf files:

Master-1:

server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1

Master-2:

server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2


Nothing to do, no schemas or tables selected on export I'm using MySql Workbench 6.3.6, on Windows 10, which has all updates.

I have previously been able to get a data dump from this database. Now I can't get any from any of the schemas I have open in workbench.

I've been able to run queries and make changes to the databases.

Then when I try to use the export function I get this message:

  Nothing to do, no schemas or tables selected.

I have found some bug reports, but am not convinced they apply to version 6.3.6:

mysqlbugs

ubuntu mysql workbench

File bug report http://bugs.mysql.com/bug.php?id=79866&thanks=4

Uninstall workbench from system and install it back.
Problem mainly occurs with the some where file distribution might be corrupted.


Creating Non-Clustered Index on Non-Persisted Computed Column SQL Server I am struggling to find any documentation on how SQL Server actually stores a non-persisted computed column.

Take the following example:

--SCHEMA
CREATE TABLE dbo.Invoice
(
    InvoiceID INT IDENTITY(1, 1) PRIMARY KEY,
    CustomerID INT FOREIGN KEY REFERENCES dbo.Customer(CustomerID),
    InvoiceStatus NVARCHAR(50) NOT NULL,
    InvoiceStatusID AS CASE InvoiceStatus
                         WHEN 'Sent' THEN 1
                         WHEN 'Complete' THEN 2
                         WHEN 'Received' THEN 3
                       END
)
GO

--INDEX
CREATE NONCLUSTERED INDEX IX_Invoice ON Invoice
(
    CustomerID ASC
)
INCLUDE
(
    InvoiceStatusID
)
GO

I get that it is stored at the leaf level, but if the value is not persisted how is anything stored at all? How does the index help SQL Server find these rows in this situation?

Any help greatly appreciated,

Many Thanks,

EDIT:

Thanks to Brent & Aaron for answering this, here's the PasteThePlan clearly showing what they explained.

When SQL Server creates the index on the computed field, the computed field is written to disk at that time - but only on the 8K pages of that index. SQL Server can compute the InvoiceStatusID as it reads through the clustered index - there's no need to write that data to the clustered index.

As you delete/update/insert rows in dbo.Invoice, the data in the indexes is kept up to date. (When InvoiceStatus changes, SQL Server knows to also update IX_Invoice.)

The best way you can see this for yourself is to actually do it: create these objects, and execute updates that touch the InvoiceStatusID field. Post the execution plan (PasteThePlan.com is helpful for this) if you want help seeing where the index updates are happening.


check history along with dates for queries in Postgres I am running PostgreSQL on my Linux terminal and If we do select pg_backend_pid(); it gives pid for specific session.
Below gives us only the last query executed
select pid,
       usename as username,
       datname as database_name,
       query,
       application_name,
       backend_start,
       state,
       state_change
from pg_stat_activity
where pid = 'your-pid';

but looks like this \s gives us all the history of query but not with dates. can we get dates along with them
and if we do save output to a filename where does it gets stored on Linux server?
\s filename

Kindly suggest if there are more specific way, actually I am looking for the queries executed on particular date.

PostgreSQL doesn't remember the times when queries are run.
If you need that information, set log_statement = 'all' and include %m in log_line_prefix. Then the queries and their time are written to the log file.


Drop primary key without dropping an index Is it possible to drop primary key constraint on a table without dropping the index? I have a situation when I want to remove the PRIMARY KEY to get rid of the NOT NULL constraints and set it to another column pair. I want to avoid dropping and recreating the index. Can just the PRIMARY KEY 'attribute' on the index be dropped? Dropping the constraint removes the index.

I am using PostgreSQL version 9.1.

I don't think there is a supported way to do this. Consider the comments so far.

If you are concerned about effects on concurrent transactions, there is a code example in the manual:

  To recreate a primary key constraint, without blocking updates while
  the index is rebuilt:

CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id);
ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
    ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;

For your case, you could create the same index the PK uses a second time and  the index used by your new PK. Then drop the PK like in the example, add a different (unlike the example) PK based on the new index and a new UNIQUE constraint based on the duplicated former PK index. All in a single statement. That would only lock the table for a millisecond.
Three separate commands (not in one transaction):

CREATE UNIQUE INDEX CONCURRENTLY dupe_of_old_pk_idx ON tbl (old_pk_id);
CREATE UNIQUE INDEX CONCURRENTLY new_pk_idx ON tbl (new_pk_id1, new_pk_id2);

ALTER TABLE tbl
   DROP CONSTRAINT tbl_pkey
 , ADD  CONSTRAINT tbl_uni  UNIQUE USING INDEX dupe_of_old_pk_idx
 , ADD  CONSTRAINT tbl_pkey PRIMARY KEY USING INDEX new_pk_temp_idx;


Postgres Sorting Optimisation I am looking for a way to order table entries during insertion rather than fetching.
If this is not possible, is there a way to have an auto-increment respective to the primary key? (i.e. if id is primary, ai will increment starting from 0 for every different id inserted)

example:

id | ai
---+---
 0 | 0
 0 | 1
 0 | 2
 1 | 0
 1 | 1
 1 | 2
 1 | 3

Short answer: No, and yes, respectively.

SQL databases do store data in some sort of order; whether by allocation, or (when there is a clustered index on a table) by the clustering key.

However, the process of executing a query does not guarantee that the data will be returned in the order in which is was stored. To guarantee the order of your data, you must use the ORDER BY clause in your SELECT statement.

Yes, you can create an auto-incrementing ID value in PostgreSQL. While this would normally be used to create a surrogate key to be used as the primary key, it appears it can be used to merely create a unique column, if you like.

The accepted answer to this question should give you what you need. You either use a sequence, or the SERIAL pseudo-datatype (which creates a sequence for you behind the scenes). Check the docs for your version for more details on sequences.

(I believe SERIAL will most likely start from 1, not 0 - you would probably have to set up the sequence yourself to get a specific starting point)

Note: This answer is for the tagged version of PostgreSQL, 9.4. As of version 10, PostgreSQL now has an identity column mechanism similar to that in Oracle or DB2, per the SQL:2003 & SQL:2008 standard (feature # T174). See the doc for GENERATED ... FOR IDENTITY here.


Is there any way to delete from this postgres 9.6 view? Here is the view:

    CREATE OR REPLACE RECURSIVE VIEW employees_recursive(id, name, child_category_id,parent_category_id, level,topic_id, type_name, inserted_at, updated_at) AS
(
    SELECT
        ee.id,
        ee.name,
        child_category_id,
        parent_category_id,
        1 as level,
        ct.id,
        ct.name,
        ee.inserted_at,
        ee.updated_at
    FROM category_hierarchy cth
    LEFT JOIN categories ee ON cth.child_category_id = ee.id
    INNER JOIN category_types ct ON ee.topic_id = ct.id
    WHERE
        parent_category_id IS NULL
    UNION ALL
    SELECT
        ee.id,
        ee.name,
        c.child_category_id,
        c.parent_category_id,
        ct.id,
        ct.name,
        ee.inserted_at,
        ee.updated_at
    FROM employees_recursive p
    INNER JOIN category_hierarchy c ON p.child_category_id = c.parent_category_id
    LEFT JOIN categories ee ON c.child_category_id = ee.id
    INNER JOIN category_types ct ON ee.topic_id = ct.id
)

I've been trying to write a function to delete rows from the view by id using functions and triggers following this post.  The Postgres docs say that views with "WITH" or "UNION" keywords at the top level are not updateable.

does that mean I can't delete rows from this view?

If that's the case, is there a way to delete rows from the actual table and somehow refresh the view?

Firstly, afaik, any view can be "updatable" but only views that are “simple enough” can be automatically updatable. You can make any VIEW updatable with

INSTEAD OF trigger trigger
PostgreSQL RULE SYSTEM I prefer to never use the rule system.

And of course you can make a function that works on the underlying table and updates it.


Best practice to avoid a composite key? Suppose my students fill every year a survey, containing some questions (called fields) which answer is real-valued (called value). Every student may answer the survey, at most once a year.

A simple example for the survey:

in 2012: Quality of the lecture (0-100)
in 2013: Quality of the lecture (0-100) and Difficulty level (0-100)

I am faced with a dilemma : use composite keys or check constraint. Which solution is a best practice ?

No composite key :
I create the following table structure but I need a constraint to make sure that field.year is the same as survey.year

CREATE TABLE survey (
   surveyID int primary key,
   student_name nvarchar(20),
   year int,
)

CREATE TABLE fields (
   fieldID int primary key,
   field_name nvarchar(20),
   year int,
)

CREATE TABLE data(
   surveyID int,
   fieldID int,
   value float,
   primary key (surveyID, fieldID),
   foreign key(studentID),
   foreign key(fieldID)
)

With composite key :
With the following table structure, I have the drawback to use the tuple (surveyID, year) for each join I make.

CREATE TABLE survey (
   surveyID int primary key,
   student_name nvarchar(20),
   year int,
   primary key (surveyID, year)
)

CREATE TABLE fields (
   fieldID int primary key,
   field_name nvarchar(20),
   year int,
   primary key(fieldID)
)

CREATE TABLE data(
   surveyID int,
   fieldID int,
   year int,
   value float,
   primary key (surveyID, year, fieldID),
   foreign key(studentID, year),
   foreign key(fieldID, year)
)

To start with the correct primary key (single column vs multiple or artificial vs natural) is to use the primary key that is correct for the task.

Some people will tell you to always use an Artificial or Surrogate Key.  This is a key that is a generated key and has nothing to do with the information it represents.  For example an identity column or a GUID.  Others will tell you that an artificial key is the wrong way to go and you want to use a Natural Key.  This is a key that is constructed from one or more columns of the data itself.  FYI a key with multiple columns is sometimes called a composite key.  LastName, FirstName for example.  Or in your case it looks like you are using a combination of the two.  You have an integer ID (presumably artificial) + a year (presumably natural).

I'm not really going to go into the arguments here.  You can Google them yourself.  In your particular case I would leave your primary keys alone.  In my opinion t makes sense to have SurveyId and FieldId columns that are your primary key.  These are unique values (I assume) and the tables are basically lookup tables in this particular case.  I would however move the Year column out of those tables and into the data table.  If you don’t want to do this then take advantage of the fact that foreign keys can join to a ‘unique key’ as well as a primary key. A unique key is an index that enforces uniqueness but is necessarily the primary key.  As an added bonus columns in a unique key can be nullable.  In this case your structure would look like this:

CREATE TABLE survey (
   surveyID int primary key,
   student_name nvarchar(20),
   year smallint
)
GO

CREATE UNIQUE INDEX ix_survey ON survey(surveyID, year)
GO

CREATE TABLE fields (
   fieldID int primary key,
   field_name nvarchar(20),
   year smallint
)
GO

CREATE UNIQUE INDEX ix_fields ON fields(fieldID, year)
GO

CREATE TABLE data(
   surveyID int,
   fieldID int,
   year smallint,
   value float,
   primary key (surveyID, year, fieldID),
   foreign key(surveyID, year) REFERENCES survey(surveyID, year),
   foreign key(fieldID, year) REFERENCES fields(fieldID, year)
)
GO

I did take the liberty of changing the year column to a smallint which is more than large enough to hold a 4 digit year and only takes 2 bytes instead of 4.

Using the unique key you can enforce the fact that you want the year columns to be the same while not adding an unnecessary column (the IDs are already unique) to your primary key.


Error when using views on a linked server I have this query:
SELECT
    -
FROM -.dbo.tb1
WHERE
    -IN
    (
        SELECT
            CASE
                WHEN EXISTS
                (
                    SELECT *
                    FROM -.dbo.tb2U1 WITH (NOLOCK)
                    WHERE col1 = '8614'
                )
                THEN
                (
                    SELECT TOP (1)
                        CASE WHEN ISNULL(col2,0) IN (1,2,3,6,8)
                            THEN '2'
                            ELSE '1'
                        END AS col3
                    FROM -.dbo.tb1U1 WITH (NOLOCK)
                    LEFT join -.dbo.tb3U2 WITH (NOLOCK)
                        ON U1.-= U2.-
                    WHERE
                        -= '8614'
                    ORDER by
                        -DESC
                )
                ELSE '3'
            END
    );

When I run it on -, it's ok. It returns me what I want:
col2
-------------------------------------------------- Value

But if I run it on - (we have the same database -, but with no tables, only views redirecting to - with the same names of the tables) it shows me this error:
Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared.
Msg 4145, Level 15, State 1, Line 1 An expression of non-boolean type specified in a
context where a condition is expected, near 'THEN'.

The views to the linked server are of the form:
SELECT *
FROM -.-.dbo.tb2

If I write the query without the SELECT here:
...Where -in ( ***NO SELECT HERE*** Case When Exists
(Select * from -.dbo...

...it runs perfectly.
I just want to know why this happens. How could this be possible? It's the same query.

This is a bug, though one that only arises in very specific circumstances.

SQL Server uses a component called Distributed Query (DQ) to build T-SQL commands to run against the linked server. The optimizer transforms a CASE expression including a sub-select to a form that works perfectly well on local objects, but which is not translated correctly to remote query form (in this particular circumstance).

The bug results in a remote node being generated that requires the result of local computations. An internal query tree is created that expects these computed values as parameters, but no parameters are generated. This bug only occurs if the sub-selects are not correlated to the outer query (as is the case here), because a simplification that is only safe for a local query is applied.

DQ generates the remote query with parameters (using standard ? syntax), but no parameters are passed, causing the "statement cannot be prepared" error (a prepared statement is the technical name for a parameterized statement).

If subquery contained an outer reference (correlation) instead of literal values, the local-only simplification would not be applied, and the query tree would be correct.

Reproduction

Create a linked server SQL2008 to another instance of SQL Server 2008, which contains the AdventureWorks sample database. Create the following views on the local instance:

CREATE VIEW dbo.Product AS
SELECT *
FROM SQL2008.AdventureWorks.Production.Product AS P;
GO
CREATE VIEW dbo.TransactionHistory AS
SELECT *
FROM SQL2008.AdventureWorks.Production.TransactionHistory AS TH;

Generate an estimated execution plan for the following query (running it will just produce the error message, of course):

SELECT
    P.ProductID
FROM dbo.Product AS P
WHERE P.ProductID IN
(
    SELECT -- Comment this
        CASE
            WHEN EXISTS
                (
                    SELECT *
                    FROM dbo.TransactionHistory AS TH
                    WHERE TH.ProductID = 1 -- Not correlated
                )
            THEN
                (
                    SELECT TOP (1)
                        TH.TransactionID
                    FROM dbo.TransactionHistory AS TH
                    WHERE
                         -- Not correlated
                        TH.ProductID = 1
                    ORDER BY
                        TH.TransactionID DESC
                )
            ELSE 0
        END
);

The estimated query plan below highlights the problem operator:

The Remote Query property of that operator is:

SELECT "Tbl1001"."ProductID" "Col1019"
FROM "AdventureWorks"."Production"."Product" "Tbl1001"
WHERE CASE WHEN ? THEN ? ELSE (0) END = "Tbl1001"."ProductID"

Notice the ? parameter markers. The unresolved parameters generate the second error message when the remote server tries to compile WHEN ? THEN ?.

If you comment out the marked SELECT, the transformation in question is not applied, and the erroneous remote query is not generated.

Equally, replacing either of the TH.ProductID = 1 instances with a correlation, for example TH.ProductID = P.ProductID also generates correct remote queries.

This bug reproduces in all current versions of SQL Server, up to and including SQL Server 2014 RTM CU4.


Update only some records I search an update-command, which update me only some records (more than one row) in a table. The update Information comes from an other table.
Unfortunately it updates all 3500 records instead of only 603 records.
Here is the update/select. What is wrong:

update Z_VERTRAGSDATEN_HIST HIST
  set
    (HIST.VERHI_BISDAT) =
    (select
  UPDA.VERHI_BISDAT
     from
        Z_VERTRAGSDATEN_HIST HIST
        , Z_VERTRAGSDATEN_UPDATE UPDA
    where
        HIST.VERHI_VER_ID = UPDA.VERHI_VER_ID
    and
        HIST.VERHI_BISDAT != UPDA.VERHI_BISDAT
    )
;
commit;

I friend told me the solution:

update Z_VERTRAGSDATEN_HIST HIST
  set
    ( HIST.VERHI_BISDAT
    , HIST.VERHI_ENDE
    , HIST.VERHI_ZEITSTEMPEL
    )  =
    ( select
      UPDA.VERHI_BISDAT
      , UPDA.VERHI_ENDE
      , sysdate
     from
       Z_VERTRAGSDATEN_UPDATE UPDA
     where
       HIST.VERHI_VER_ID = UPDA.VERHI_VER_ID
     )
where
  HIST.VERHI_VER_ID in
  ( select
      verhi_ver_id
    from
      Z_VERTRAGSDATEN_UPDATE
  )
;
commit;


Why doesn't running a full sample *always* improve query performance Conventional wisdom would suggest that running a full sample of statistics would provide SQL with the optimum information to make the best decisions when forming a query plan for execution and therefore the best performance.

However, I have a number of queries (produced by Business Objects) which prefer a sample below 100%. The exact percent required for sample varies for success. What I want to know (but am struggling to find online) is why is why a 100% sample doesn't produce the best performance. Index maintenance is regular, every night following the 'Ola Hallengren' method.

The nature of the query makes it difficult to post (contains sensitive information) but those who are familiar with business objects queries will know they can be unwieldy. . .rather unusual in their construction, I often think its their setup which pokes the performance.

Thanks!

A perfect query optimizer should make the best plan if provided with a full sample of statistics. But even a perfect optimizer does not need the information provided by a full sample of statistics to make the best plan. A simple example:

select count(*)
from tab1
where col1=3
and col2=4

Assume that for this table tab1 there exists a nonclustered index on col1. The optimizer has to possible plans:

1) scan the whole table and check for each row if col1=3 and col2=4
2) consult the index to find the position of all rows in the table with col1=3. Lookup each of these row in the table and check if col2=4

now assume the table has a size of 123,456 GB and 1 123 456 789 rows and 567 890 123 of them (about half of them) have col1=3. What should the optimizer propose? It schould propose a full table scan. The other possibility, looking up 567 890 123 rows one by one is too expensive.

Now assume the optimizer only has a rough estimate of the table data: the table size is about 100GB, there are about 1,000,000,000 rows and about 600,000,000 of them have col1=3. Based on this information the optimizer will choose the same good plan as in the first case.

The decision is based on the size of the ratio 100*(# of col1=3-rows)/#(all rows) and if this value is larger  10% (I don't know which value is really used) the full table is scanned. The decision is not based of the size of the 5th place after the decimal point of the ration.

In a special case where the exact ratio is 10,00001% and the ratio based on the rough estimate is 9,99999% the optimizer will choose different plans but maybe for these different plans the execution time may be the same for both plans or may be better for the plan based on the rough estimate

Administrative jobs like calculationg statitistics should be done only with least resource consumption that is possible. Therefore only the precision that is necessary should be tried to be reached.


Compare value from previous record How can I compare value from previous records?

Table:

ID    SAMPLEDATE    LEVEL    PASSED
1     1/1/2016      0        1
2     1/2/2016      1        1
3     1/3/2016      2        0
4     1/4/2016      1        1
5     1/5/2016      2        0

Is the last record (ordered by SAMPLEDATE) the same LEVEL as the last record that failed?  If yes, return TRUE otherwise return FALSE.

So in my example data the last record that failed with LEVEL 2. Then the last record (before the 1/5/2016 record) that failed on 1/3/2016 was the same LEVEL, so TRUE would be returned.  If it was a different LEVEL FALSE would be returned.

I assumed that "failed" means passed = 0.

There are some detail missing from the question, i.e. do we need to compare the very last row (when ordered by sampledate) with the previously last "failed" row? If yes, then the following query will do:

with last_row as
( select top (1) sampledate, level
  from dbo.tablex
  -- where failed = 0
  order by sampledate desc
)
select top (1) result =
    case when t.level = r.level
        then 1 else 0
    end
from dbo.tablex as t
  join last_row as r
  on t.sampledate < r.sampledate
where t.passed = 0
order by t.sampledate desc ;

If, on the other side, we want to compare the last "failed" row with the previous "failed" row, then we could uncomment the where failed = 0 line above or use the less complicated, just find the last 2 failed rows and compare their levels with a simple group by:

with last_2_failed_rows as
( select top (2) sampledate, level
  from dbo.tablex
  where failed = 0
  order by sampledate desc
)
select result =
    case when min(level) = max(level)
        then 1 else 0
    end
from last_2_failed_rows ;


Correlated sub-query on the same table with only one alias I have query which look like this

SELECT *
FROM my_table
WHERE ... AND id IN (SELECT t2.id
                     FROM my_table t2
                     WHERE id = t2.id AND ... );

I know in this example I could combine the two WHERE clauses and don't use a sub-query but this is not the point.

This query works perfectly on the DBMS I'm using (SQL Anywhere 16) but I'm wondering if the fact that id in the sub-query refers to my_table.id from the main query is a standard behavior or if I'm just lucky.

Link or reference to an RFC or any official document appreciated :)

No, this is wrong. The standard behaviour is that in WHERE id = ... in the subquery the id refers to the closest table in scope. And that is t2. It would refer to my_table in the external query only if t2 had no id column.

You are lucky though because the WHERE id = t2.id - which is translated as WHERE t2.id = t2.id is not actually needed because you used WHERE id IN (subquery). If you had an EXISTS  version, it wouldn't work as you expected.

So, the proper ways to write the query would be either prefix all column references:

SELECT t1.*
FROM my_table AS t1
WHERE ... AND t1.id IN
              (SELECT t2.id
               FROM my_table t2
               WHERE --- t1.id = t2.id AND  -- remove, we don't need this
               ... );

Or be very careful and prefix only when needed:

SELECT *
FROM my_table AS t1
WHERE ... AND id IN
              (SELECT id
               FROM my_table t2
               WHERE --- id = t2.id AND  -- remove, we still don't need this
                 ... );

I would still prefer the EXISTS version for such queries:

SELECT t1.*
FROM my_table AS t1
WHERE ... AND EXISTS
              (SELECT *
               FROM my_table t2
               WHERE t1.id = t2.id AND  -- we do need "t1". here
               ... );

or (if you are always very careful):

SELECT *
FROM my_table AS t1
WHERE ... AND EXISTS
              (SELECT *
               FROM my_table t2
               WHERE t1.id = id AND    -- we need only the "t1". here
                                       -- the second ref, = id is translated
                                       -- to:   = t2.id
               ... );


Copying Db permission between same instance I have to copy the entire database permissions from database A to database B which has  same objects in database B in same instance.

What are the things that I need to copy from database A to database B to get the same permission as in database A.

Environment: SQL Server 2012 , in same server with same logins.

I use powershell based Sync-DbaSqlLoginPermission

This command syncs SQL login permissions from one server to another including login permissions, roles, database permissions and more. By default, all logins are synced. The -Logins and -Excludes parameters is autopopulated for command-line completion and can be used to sync only specific logins.
If a matching login or securable does not exist on the destination, the permission sync will be skipped.
Credential removal and application role syncs not currently supported.

Or You can use TSQL based Script DB Level Permissions v3


SQL Server Execution Plans and Index Drops When a SQL Server index is dropped (say it's a duplicate) what are the steps undertaken by the engine to clear and then rebuild the execution plans that reference it?  In particular I'm wondering whether it's an all-at-once process, an as-new-queries-occur process or something in between.  Thanks.

Welcome to DBA.StackExchange, and interesting question!...one I've never thought of before. I had to double check myself on what I thought the process was, so I think this StackOverflow Answer should be what you're looking for. Specifically the first statement of the answer:

Dropping/Rebuilding an Index will result in invalidation of any cached
execution plans using this table/Indexed view. And sql server will
generate a new execution plan on next execution.

Essentially, the plans in the plan cache will automatically be marked as invalid of any existing execution plans that reference the dropped index. Then the next time a query runs that involves the entity of which the index was dropped from, a new execution plan is automatically generated and cached in the plan cache because the old one is invalidated. (Eventually the invalidated plans are flushed from the plan cache to make room for new plans being cached.)
Here's an additional resource that concurs this is how it occurs: Dropping unnecessary indexes - effect on query plans in SQL Server 2005


Visual Studio Database Project I work in a company that is deeply ingrained in .Net programming.  I am responsible for managing a couple SQL Servers, with about a dozen databases in total.  Our programming manager recently demonstrated the Visual Studio Database Projects for my team and I am looking to know if anyone is using it and if so the pros/cons.  Right now I have to manually promote changes from test to production via SSMS which VS would eliminate.  I also have a large collection of SQL scripts on our SAN that are fairly unorganized and it would be nice to include them in a common solution.

My apologies beforehand I know this is probably not the best place for this question since I am looking for opinions rather than a technical answer...

I would suggest using SQL Server Data Tools (SSDT), which is essentially the latest iteration of the Visual Studio Database Projects. I use this at places that do not already have a way to source control their databases since it easily integrates with TFS, which most Visual Studio shops use for source control.

A couple pros I have for using it are:

Source controlling database schema
Able to test changes locally before publishing to server
Easier workflow when changing project and generating change script to server
Peer reviews are simpler as the other person just needs to open the project
Table design has most items (indexes, constraints, foreign keys, primary keys, etc.) right in designer instead of opening multiple areas
Latest version has everything in one install (database schema, SSIS, SSRS, and SSAS).

A few cons for using it would be:

Change script may be as simple as you would like (sometimes a change creates a new table and copies the data into this table, which could take a long time depending on row count)
Generates change script using SQLCMD syntax so be aware of that
Default settings detect changes that may be irrelevant so sometimes best to not check for those (permissions, role memberships, and users are examples)
Referencing external sources may not be intuitive (i.e. table in another database)
This only supports SQL Server (the older Visual Studio Database Projects supported more than SQL Server).
No schema design tool

Having said all that, I still use it and recommend it. I generally try to let developers use just that tool instead of SQL Server Management Studio (SSMS) because I look at SSMS as a DBA tool and SSDT as a developer tool.


How to write and read files from client machine SQL Server 2019 I need to prepare a stored procedure which export as many files as many tables I have in schema, but files need to be written on local machine on specific path e.g. C:\Temp\ not on SQL Server destination.
When file exists it should be overwritten, if not simply created.
I need also read from local files: with parameters and template, which will be used in procedure.
I know how to do everything except the method of read/write files.
I found some ideas for export to files:

xp_cmdshell executing bcp command I could only write files on Sql Server 2019 machine.
I found also sp_configure 'Ole Automation Procedures', 1;

    DECLARE @OLE INT
    DECLARE @FileID INT
    EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT
    EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, 'D:\data\sqltotext.text', 8, 1
    EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, 'Today is wonderful day'
    EXECUTE sp_OADestroy @FileID
    EXECUTE sp_OADestroy @OLE
    GO

Do you know easier way to achieve that?

A SQL Server instance has no concept of another machine other than it's own. When you connect to it with SSMS (or other means) you're specifically initiating the connection to that server, not the other way around. So out of the box I don't believe there is a way with pure SQL to accomplish this.
These are a couple ideas of what you can do:

xp_cmdshell essentially is a way to execute commands on the server that the SQL Server instance is running on. If you can setup a file share to the local machine from the server that the SQL Server instance is running on first, then you can probably leverage xp_cmdshell to write the files to that share.

CLR might be another solution you can use to create a procedure that pipes the data from your server to a web service that has access to the local machine. Then that web service can easily write files to the local machine using C# code (or really any code of your choice that supports web services).

Last but certainly not least (probably the most standard way to go about this) is SSIS. You can create an SSIS package that exports your data and then writes actual files to your local machine (though you probably need to add your local machine as a connection in the package).


Backup detects corruption, but CHECKDB does not I have a database where when I run the backup command

BACKUP DATABASE [MyDatabase] TO
DISK =  'G:\Backup\MyDatabase_01_01_2018.bak'
WITH    NOFORMAT, NOSKIP, COMPRESSION, INIT, BUFFERCOUNT = 100

I get the error message

  Msg 3043, Level 16, State 1, Line 8
  BACKUP 'MyDatabase' detected an error on page (1:745345) in file 'F:\Data\MyDatabase_1.ndf'.
  Msg 3013, Level 16, State 1, Line 8
  BACKUP DATABASE is terminating abnormally.

I ran a full CHECKDB but it comes back clean.  I did notice that the Page Verify option had been set to NONE (not my doing) so I changed it to CHECKSUM and rebuilt all the indexes in the DB to get it to write to all pages and generate checksums. After this the backup still fails and the checkdb still shows clean (so no change).

DBCC CHECKDB('MyDatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS,
DATA_PURITY, EXTENDED_LOGICAL_CHECKS;

from the SQL log:

  DBCC CHECKDB (MyDatabase) WITH all_errormsgs, no_infomsgs, data_purity executed by xxx found 0 errors and repaired 0 errors. Elapsed time: 0 hours 21 minutes 46 seconds.  Internal database snapshot has split point LSN = 000ab776:0000112f:0001 and first LSN = 000ab776:0000112d:0001.

I ran DBCC PAGE but it errors (doesn't even seem to be returning the right page in the first place).  I CAN run it with print option 2 and it returns but honestly I don't know what I'm looking for there.

DBCC PAGE ('MyDatabase',1,745345,3)

PAGE: (3:513793)

BUFFER:

BUF @0x00000003811F8280

bpage = 0x00000000F2D70000          bhash = 0x0000000000000000          bpageno = (1:745345)
bdbid = 5                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 44283                       bstat = 0x809
blog = 0x5adb215a                   bnext = 0x0000000000000000

PAGE HEADER:

Page @0x00000000F2D70000

m_pageId = (3:513793)               m_headerVersion = 1                 m_type = 2
m_typeFlagBits = 0x4                m_level = 0                         m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 1075937538                                 m_indexId (AllocUnitId.idInd) = 2
Metadata: AllocUnitId = 633462595911680                                  Metadata: PartitionId = 0
Metadata: IndexId = -1              Metadata: ObjectId = 0              m_prevPage = (3:513795)
m_nextPage = (3:513820)             pminlen = 17                        m_slotCnt = 426
m_freeCnt = 2                       m_freeData = 7338                   m_reservedCnt = 0
m_lsn = (608841:643611:411)         m_xactReserved = 0                  m_xdesId = (0:0)
m_ghostRecCnt = 0                   m_tornBits = 0                      DB Frag ID = 1

Allocation Status

GAM (1:511232) = ALLOCATED          SGAM (1:511233) = NOT ALLOCATED
PFS (1:744096) = 0x40 ALLOCATED   0_PCT_FULL                             DIFF (1:511238) = NOT CHANGED
ML (1:511239) = NOT MIN_LOGGED

  Msg 2514, Level 16, State 8, Line 20
  A DBCC PAGE error has occurred: Invalid page metadata - dump style 3 not possible.

Any ideas what I could try next?  Server version is

select @@version

Microsoft SQL Server 2014 (SP2-CU11) (KB4077063) - 12.0.5579.0 (X64)
    Feb 21 2018 12:19:47
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

Compatibility level of the DB is 100 (SQL 2008).

This answer is taken from an issue of the SQLskills.com newsletter written by Paul Randal, about "a database which would fail a backup with page checksum errors, but passed a DBCC CHECKDB".

  The only time this can happen is when an extent is a mixed extent
  (where the 8 pages in the extent can be allocated to potentially 8
  different allocations units – see here) and some pages are
  erroneously marked as allocated by the relevant PFS page.

  When that happens, DBCC CHECKDB will not attempt to read those pages,
  as it derives what pages to read from an allocation unit's IAM pages
  (the first of which lists the pages allocated from a mixed extent).
  This case is a gap in DBCC CHECKDB's corruption-detection logic.

  [Because] DBCC CHECKDB couldn't detect the corruption, it wasn't
  possible for it to make the repairs needed to fix them.
  So using DBCC WRITEPAGE, I worked out the changes needed in the allocation status
  for the erroneously-allocated pages, directly in the PFS page, and it
  worked!

  This was an extremely rare case – it's much more common that a DBCC CHECKDB
  fails but a backup would succeed.

In my opinion, Paul's resolution is way above and beyond exporting and importing the data like you did, so I think you did the right thing.


Difference ReadOnly connections through sqlcmd vs SSMS I configured my HA SQL Server to have a readonly.  When I entered the following command

sqlcmd -S servername -E -d db_name -K ReadOnly

It points correctly to the secondary server.

Then in SSMS I have my applicationintent=ReadOnly. However, when I run a trace all the queries point to the listener instead of the secondary HA server.

Is this a normal behaviour or am I missing something in my readonly configuration?

Without knowing the exact parameters you've used, it's hard to be sure.  But based on the example code you provided for sqlcmd:

sqlcmd -S servername -E -d db_name -K ReadOnly

I would bet you used the instance name of the current secondary replica - which would explain why you saw all the traffic there in your trace.

When you connected via SSMS, I imagine you used the listener name - which would go through the listener to connect to the primary, and then connect to the appropriate secondary replica (based on read only routing rules, load balancing, if there are any secondary replicas actually available, etc).

PS: to clarify one thing regarding this statement:

  However, when I run a trace all the queries point to the listener instead of the secondary HA server

The listener is neither the primary nor secondary, so the "instead of" relationship you mention here is a little confusing.  The listener is a virtual network name that facilitates connecting to the different replicas in the AG.


What can cause Reads to slow down on SQL Server 2008R2? Our primary DB is 15GB in size. The server has 16GB of RAM. We are using the simple recovery model.

Lately we've noticed that our custom apps lookups - just basic reads with adhoc SQL statements - have slowed down noticeably. A couple seconds longer in most cases.

I'm not a SQL Server Admin and am not sure where to look next.

We have monitored the CPU which never seems to peak. Same with the RAM. We have tons of indexes, but I thought that was only an issue with write speed...

Any suggestions on what I could look at next?

There could be many reasons for this. A few questions:

Do you do index maintenance?
Do you import data? Or is it all entered via a front-end application by users?

Option 1 - We could look at your wait stats and see if that helps pin-point a direction to look.

Option 2 - We could look at the execution plan of a slow-running query.

For option 1:
Run this via Management Studio (it will run for 30 seconds), after you hit execute, run one of your slow-running queries in a second tab, then switch back to the previous tab and copy and paste the results here:

    DECLARE @FinishSampleTime   DATETIME

    IF OBJECT_ID('tempdb..#WaitStats') IS NOT NULL
        DROP TABLE #WaitStats;

    CREATE TABLE #WaitStats
    (
        Pass TINYINT NOT NULL,
        wait_type NVARCHAR(60),
        wait_time_ms BIGINT,
        signal_wait_time_ms BIGINT,
        waiting_tasks_count BIGINT,
        SampleTime DATETIME
    );

    -- Populate #WaitStats with DMV data. In a second, we'll compare these.
    INSERT #WaitStats(Pass, SampleTime, wait_type, wait_time_ms, signal_wait_time_ms, waiting_tasks_count)
    SELECT
        1 AS Pass,
        GETDATE() AS SampleTime,
        os.wait_type,
        SUM(os.wait_time_ms) OVER (PARTITION BY os.wait_type) as sum_wait_time_ms,
        SUM(os.signal_wait_time_ms) OVER (PARTITION BY os.wait_type ) as sum_signal_wait_time_ms,
        SUM(os.waiting_tasks_count) OVER (PARTITION BY os.wait_type) AS sum_waiting_tasks
    FROM sys.dm_os_wait_stats os
    WHERE
        os.wait_type not in (
            'REQUEST_FOR_DEADLOCK_SEARCH',
            'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
            'SQLTRACE_BUFFER_FLUSH',
            'LAZYWRITER_SLEEP',
            'XE_TIMER_EVENT',
            'XE_DISPATCHER_WAIT',
            'FT_IFTS_SCHEDULER_IDLE_WAIT',
            'LOGMGR_QUEUE',
            'CHECKPOINT_QUEUE',
            'BROKER_TO_FLUSH',
            'BROKER_TASK_STOP',
            'BROKER_EVENTHANDLER',
            'SLEEP_TASK',
            'WAITFOR',
            'DBMIRROR_DBM_MUTEX',
            'DBMIRROR_EVENTS_QUEUE',
            'DBMIRRORING_CMD',
            'DISPATCHER_QUEUE_SEMAPHORE',
            'BROKER_RECEIVE_WAITFOR',
            'CLR_AUTO_EVENT',
            'DIRTY_PAGE_POLL',
            'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
            'ONDEMAND_TASK_QUEUE',
            'FT_IFTSHC_MUTEX',
            'CLR_MANUAL_EVENT',
            'SP_SERVER_DIAGNOSTICS_SLEEP',
            'HADR_CLUSAPI_CALL',
            'HADR_LOGCAPTURE_WAIT',
            'HADR_TIMER_TASK',
            'HADR_WORK_QUEUE'
        )
    ORDER BY sum_wait_time_ms DESC;

    -- Wait for the specified amount of time
    SET @FinishSampleTime = DATEADD(second,30,GETDATE());
    WAITFOR TIME @FinishSampleTime;

    -- Populate #WaitStats with DMV data. In a second, we'll compare these.
    INSERT #WaitStats(Pass, SampleTime, wait_type, wait_time_ms, signal_wait_time_ms, waiting_tasks_count)
    SELECT
        2 AS Pass,
        GETDATE() AS SampleTime,
        os.wait_type,
        SUM(os.wait_time_ms) OVER (PARTITION BY os.wait_type) as sum_wait_time_ms,
        SUM(os.signal_wait_time_ms) OVER (PARTITION BY os.wait_type ) as sum_signal_wait_time_ms,
        SUM(os.waiting_tasks_count) OVER (PARTITION BY os.wait_type) AS sum_waiting_tasks
    FROM sys.dm_os_wait_stats os
    WHERE
        os.wait_type not in (
            'REQUEST_FOR_DEADLOCK_SEARCH',
            'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
            'SQLTRACE_BUFFER_FLUSH',
            'LAZYWRITER_SLEEP',
            'XE_TIMER_EVENT',
            'XE_DISPATCHER_WAIT',
            'FT_IFTS_SCHEDULER_IDLE_WAIT',
            'LOGMGR_QUEUE',
            'CHECKPOINT_QUEUE',
            'BROKER_TO_FLUSH',
            'BROKER_TASK_STOP',
            'BROKER_EVENTHANDLER',
            'SLEEP_TASK',
            'WAITFOR',
            'DBMIRROR_DBM_MUTEX',
            'DBMIRROR_EVENTS_QUEUE',
            'DBMIRRORING_CMD',
            'DISPATCHER_QUEUE_SEMAPHORE',
            'BROKER_RECEIVE_WAITFOR',
            'CLR_AUTO_EVENT',
            'DIRTY_PAGE_POLL',
            'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
            'ONDEMAND_TASK_QUEUE',
            'FT_IFTSHC_MUTEX',
            'CLR_MANUAL_EVENT',
            'SP_SERVER_DIAGNOSTICS_SLEEP',
            'HADR_CLUSAPI_CALL',
            'HADR_LOGCAPTURE_WAIT',
            'HADR_TIMER_TASK',
            'HADR_WORK_QUEUE'
        )
    ORDER BY sum_wait_time_ms DESC;

    ----------------------------
    -- What happened: #WaitStats
    ----------------------------
    ;with max_batch as (
        select max(SampleTime) as SampleTime
        from #WaitStats
    )
    SELECT
        'WAIT STATS' as Pattern,
        b.SampleTime as [Sample Ended],
        datediff(ss,wd1.SampleTime, wd2.SampleTime) as [Seconds Sample],
        wd1.wait_type,
        c.[Wait Time (Seconds)],
        c.[Signal Wait Time (Seconds)],
        (wd2.waiting_tasks_count - wd1.waiting_tasks_count) AS [Number of Waits]
    FROM  max_batch b
        JOIN #WaitStats wd2 on
            wd2.SampleTime =b.SampleTime
        JOIN #WaitStats wd1 ON
            wd1.wait_type=wd2.wait_type AND
            wd2.SampleTime > wd1.SampleTime
        CROSS APPLY (SELECT
            cast((wd2.wait_time_ms-wd1.wait_time_ms)/1000. as numeric(10,1)) as [Wait Time (Seconds)],
            cast((wd2.signal_wait_time_ms - wd1.signal_wait_time_ms)/1000. as numeric(10,1)) as [Signal Wait Time (Seconds)]) AS c
    WHERE (wd2.waiting_tasks_count - wd1.waiting_tasks_count) > 0
        and wd2.wait_time_ms-wd1.wait_time_ms > 0
    ORDER BY [Wait Time (Seconds)] DESC;

--  ===========================================================================
--  ********** Begin Clean-Up **********
--  ===========================================================================
    DROP TABLE #WaitStats

Like I said, this code takes 30 seconds to execute. So you hit 'Execute' then run your slow-running query in a second tab, then come back to the first tab and wait for the results. Please tell me if this is unclear.

For option 2:
Queries that suddenly slow down make me think indexing troubles. If your indexes are badly fragmented it could effect execution plans, which could cause what you are experiencing.

Assuming you are using SQL Management Studio to run your queries, hit 'CTRL+M' before executing the queries. This will generate a graphical execution plan. If you could post a screen shot it might give us something to work from. This is assuming you are ok with users of StackExchange seeing your table/index names.

Depending on how comfortable you are with everything, you could also get a free copy of SQL Sentry Plan Explorer, it will allow you to upload anonymized execution plans so others can look at them in-depth. Get that and the SSMS add-on. When you execute a query after pressing 'CTRL+M' you can right-click on the plan to open it in Plan Explorer.