Queue Depth and vSCSI controllers We have a purestorage san, a couple of VMhosts each serving up several SQL Server VM's. Each VMhost is given a slice of the SAN and each SQL Server VM is presented a single data VMDK and a single log VMDK.

Some SQL Servers have like 50+ databases. Each SQL Server VM is presented 1 single vSCSI controller.

Can adding a second vSCSI and creating another drive improve performance by increasing Queue Depth?

Ensure you read the VMware / SQL Server best practices guidelines.

This is taken from that document:

  Utilize the VMware Paravirtualized SCSI (PVSCSI) Controller as the virtual SCSI
  Controller for data and log VMDKs. The PVSCSI Controller is the optimal SCSI
  controller for an I/O-intensive application on vSphere allowing not only a higher I/O
  rate but also lowering CPU consumption compared with the LSI Logic SAS. In addition,
  the PVSCSI adapters provide higher queue depth, increasing I/O bandwidth for the
  virtualized workload.

  Use multiple PVSCSI adapters. VMware supports up to four (4) adapters per a VM and
  as many as necessary, up to this limit, should be leveraged. Placing OS, data, and
  transaction logs onto a separate vSCSI adapter optimizes I/O by distributing load
  across multiple target devices and allowing for more queues on the OS level. Consider
  distributing disks between controllers. vSphere 6.7 supports up to 64 disks per
  controller

VMDK disk files have their own queue, with 64 entries, by default.  PVSCSI adapters have a default queue length of 256 per PVSCSI adapter, however those settings can be modified.


Why are Stored Procedure schema changes not being replicated when using ”initialize with backup”? In SQL Server I have set up transactional replication using the "initialize with backup" option. Data is being replicated fine, as are schema changes to tables. However, schema changes to stored procedures are not appearing as a replicated command and therefore are not being made on the subscriber.
I found this post which seems to describe my problem, but I have been unable to implement the workarounds.
In my testing I'm positive I had this working and I think it is because I first created the replication using the GUI, which turns on Snapshot replication even though I wasn't using it. This leads me to believe that I should be able to turn on parts of the Snapshot Replication process and get this to work (I think this is what the first workaround in the blog post is alluding to).
Can anyone provide further information on the issue or suggest how I  can get this working?

Probably not the issue, but double check this:

On the publication, go to properties
Go to Subscription Options
make sure Replicate schema changes is set to true.


Database Design for Product Promotions Assume I have a products table with the following columns:

id (PK)
category_id (FK)
color_id (FK)
material_id (FK)
seller_id (FK)
size_id (FK)
name
price

Further assume that there could be 0 or more promotions that apply to a product. A promotion will have a start and end date / time, a discount amount (e.g. $100 off) or percentage (e.g. 10% off), etc.

The promotion will apply only to products that match a certain criteria, such as:

all products that are "red" color and "large" size
all products that are under the "shirts" category
all products sold by seller_id = 1 and price > $50

How would I represent this in a database? I feel that I need to associate one or more "query string" to the promotions table (e.g. ?color_ids[]=1&sizes[]=3) and test each product against all query strings to see if that product is part of that promotion. I expect this to be very inefficient, though.

Another option is to have a table products_promotions that gets set whenever a product or promotion is inserted / updated in the system, then it's just a matter of checking that table to see if the product has any promotions associated with it.

Thoughts?

One way is to create a table promotions as:

id (PK)
start_date
end_date
discount_amount
discount_percentage
condition

To determine the promotions the product qualifies for, you would need to dynamically execute all the queries, and select sum(discount_amount) and sum(discount_percentage). Here is a snippet that can help you get started:

SET @SQL = CONCAT('SELECT id FROM products WHERE ', @condition, ' INTO @product_id');
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

You can put this snippet into a function that takes condition and returns product_id.

Running all those queries every time you SELECT a product will probably end up very time consuming. So you might want to create a table product_discounts, with two foreign keys to both products and discounts, to facilitate the many-to-many relationship between these two tables.

Then you insert and delete rows from product_discounts using either events (daily recalculation of entire relationship) or triggers on the two tables, or a combination of events and triggers.

I hope this helps.


Which programming languages will have problems pulling dash named column names? I sometimes work in clojure, a language promoting the use of dashes in variable names (much more readable than Java camelCasing I can say). Taking this a step further, I'd favor having column name parity in one database implementation, so that column names in my code are the same as in the database. My database of choice supports this. I wonder how this would affect developers using other languages, who may have to access the same tables:

Will developers in other common languages have to go through special effort to accommodate reading dash inclusive column names? e.g. I once heard rants from PHP developers around hyphens in names of resources. Which programming languages will require extra handling for dash inclusive column names? what extent of effort will those languages require for it?

I have no experience in clojure, but it looks very similar to LISP. Not sure what languages that will get in to trouble, but every major DBMS will. I tested with Mariadb:

[test]> create table tx (a-b int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MariaDB server version for the right
syntax to use near '-b int)' at line 1

and with DB2:

~]$ db2 "create table tx (a-b int)"
DB21034E  The command was processed as an SQL statement because it was
not a valid Command Line Processor command.  During SQL processing it
returned: SQL0104N  An unexpected token "-b int)" was found following
"create table tx (a".  Expected tokens may include:  "<space>".
SQLSTATE=42601

I suspect that all other DBMS:s will have problem as well. You can quote your way around that:

]$ db2 "create table tx (\"a-b\" int)"
DB20000I  The SQL command completed successfully.

but I would strongly advise against that.

I would create a mapper in Clojure that translates "-" to "_" and vice versa for all DBMS objects.


Finding differences between two tables takes too long I'm trying to find the diff of two tables using a left join.

My query is as follows:

CREATE TABLE my_diff (INDEX my_index (name, type))
SELECT AA.name, AA.type
FROM AA
LEFT JOIN BB
USING (name, type)
WHERE BB.type IS NULL;

Table AA is sized: ~400K records and has PRIMARY KEY on all columns as well as an INDEX on each column
Table BB is sized: ~8K records and has a composite INDEX on both columns: my_index (name, type)

EXPLAIN EXTENDED:

id  select_type     table   type    possible_keys   key         key_len     ref     rows    filtered    Extra
1   SIMPLE          AA      index   NULL            PRIMARY     383         NULL    396178  100.00      Using index
1   SIMPLE          BB      index   NULL            my_index    104         NULL    8359    100.00      Using where; Not exists; Using index; Using join buffer (Block Nested Loop)

The query takes a mind-boggling 11 min to finish.

I have tried various options of composite and single column indexes / primary keys, tried forcing the keys/indexes on the select and the join, but to no avail.

What can I do to improve this?

Create the result table, my_diff, without index, and when it is
populated, add the desired index.
Make sure you have an index on (name, type) in both tables. i.e. a composite index
It is not a good idea to have primary key on all fields. This is especially true if you are using InnoDB engine. Instead, alter the table to add an integer auto increment field as primary key, and if needed, add a composite unique index on the fields you want to be unique.

From your explain result, it looks like you don't have the index on (name, type), so for each row in AA, it will do a full  scan to BB. This is one enough reason to make the query slow.


How to make subtraction without repeating the subqueries? I need to efficiently obtain:

  players who play more than one world cup and who have never been champion.

primary-key | foreign-key | pk-and-fk

PLAYER<player_id, player_name, country_player, ...>

WORLD<year, winner, ...>

PLAYED_WORLD<player, world_year, ...>

In the first operand of the subtraction I obtain the players with more than 1 world cup no matter if they are champions.
In the second operand, I get the players with more than 1 world cup that have already won.

The following query works for me but my idea is to optimize it to be more efficient, since the difference between both operands is nothing more than: WHERE country_player = winner

SELECT *
FROM        (SELECT player_name
        FROM    (SELECT player_id, world_year, player_name, country_player
                FROM    (SELECT player, world_year
                        FROM    (SELECT player
                                FROM played_world
                                GROUP BY player
                                HAVING count(player) > 1) AS playerMoreThan1
                        NATURAL JOIN played_world) AS worldPlayers
                JOIN player ON player= player_id) AS infoWorldPlayers
        JOIN world ON world_year = year) noChampions
EXCEPT      (SELECT player_name
        FROM    (SELECT player_id, world_year, player_name, country_player
                FROM    (SELECT player, world_year
                        FROM    (SELECT player
                                FROM played_world
                                GROUP BY player
                                HAVING count(player) > 1) AS playerMoreThan1
                        NATURAL JOIN played_world) AS worldPlayers
                JOIN player ON player = player_id) AS infoWorldPlayers
        JOIN world ON world_year = year
        WHERE country_player = winner)

Is there a strategy to reuse the queries of the first operand?

If you need the design and the initial load of the db the attached in the publication.

You can use CTEs when you have repeating subqueries. Although it's not necessarily better for performance, in Postgres CTEs are an optimization fence, in other words they are evaluated once (or not at all).

Your query can probably be written more simply though:

SELECT player
FROM played_world AS pw
WHERE NOT EXISTS
      ( SELECT 1
        FROM world AS w
             JOIN player AS p
             ON w.winner = p.country_player
        WHERE w.year = pw.world_year
          AND p.player_id = pw.player
      )
GROUP BY player
HAVING count(*) > 1 ;

or with EXCEPT:

SELECT player
FROM played_world
GROUP BY player
HAVING count(*) > 1

EXCEPT

SELECT p.player_id
FROM world AS w
     JOIN player AS p
     ON w.winner = p.country_player ;


When is it worth it to use Triggers and stored procedures/functions? As an example, lets say I have a forum application and for every topic that is created, I have a trigger that fires a database function creating a post.(lets assume we have a topics table and a post table)

I know these things can be quite circumstantial, but generally, do simple tasks like this belong in triggers/functions, or should they just be done in the application code?

As for updating views and replies I was thinking of offloading them to a queue, since the systems functionality doesn't really depend on them.

Triggers are problematic because they tend to get forgotten about.  I only ever use triggers for auditing purposes such as updating a "last updated datetime" column.  Stored procedures and functions can be used at any time since they are called directly by processes wishing to modify data -- as opposed to triggers that are fired as the result of data being changed.

If you are building queues to do some of the work, I suggest you also use queues to create your posts as well.


innodb DEPENDENT SUBQUERY very slow Here is some innodb table. It insert,update,query very frequently.

tn is product id

col is product sub id

tindex is product description.(store by each word)

date is product add date stored by strototime('now')

CREATE TABLE IF NOT EXISTS `mytable` (
  `tn` varchar(15) NOT NULL,
  `col` smallint(2) NOT NULL,
  `tindex` varchar(30) NOT NULL,
  `date` int(10) NOT NULL,
  KEY `date` (`date`),
  KEY `tn` (`tn`),
  KEY `tindex` (`tindex`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

How I want to query some products which sub id contains words 'anticorrosive' and  'waterproof'

SELECT *
FROM mytable a
WHERE tindex =  'anticorrosive'
AND EXISTS (
SELECT 1
FROM mytable
WHERE tn = a.tn
AND col = a.col
AND tindex =  'waterproof'
)
ORDER BY DATE
LIMIT 10

full table is neally 12,700,000 lines, 1.1GB ,it cost 4.8356s, explian the query get

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY a   ref tn,tindex   tindex  92  const   1173    Using where; Using filesort
2   DEPENDENT SUBQUERY  mytable ref tn,tindex   tn  47  production.a.tn 161 Using where

And here is my.ini,

[mysqld]
character_set_server=utf8
skip-external-locking
skip-networking
key_buffer = 256M
tmp_table_size = 128M
max_connections = 1024
wait_timeout=10
back_log = 2048
key_buffer_size = 256M
max_allowed_packet = 2M
table_cache = 2048
table_open_cache = 1024
sort_buffer_size = 8M
read_buffer_size = 4M
net_buffer_length = 92K
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 256M
thread_cache = 512
query_cache_size= 256M
bulk_insert_buffer_size = 192M
ft_min_word_len=2
innodb_buffer_pool_size = 512M
innodb_flush_log_at_trx_commit = 2

How to speed up the query? re-do the index? query code? or change some value in my.ini

UPDATE 1
I have tried change index kye, and my table still grow up, now it has neally 20mln lines 2.2GB

ALTER TABLE mytable ADD UNIQUE INDEX (tn,col,tindex);
ALTER TABLE mytable ADD INDEX (date);

query 1

SELECT A.* FROM
(SELECT * FROM mytable WHERE tindex='words1') A
INNER JOIN
(SELECT tn,col FROM mytable WHERE tindex='words2') B
USING (tn,col) order by date limit 10

using 29.6278s

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    15674   Using temporary; Using filesort
1   PRIMARY <derived3>  ALL NULL    NULL    NULL    NULL    1716    Using where; Using join buffer
3   DERIVED mytable index   NULL    date    5   NULL    13918039    Using where; Using index
2   DERIVED mytable index   NULL    date    5   NULL    13918039    Using where; Using index

query 2

SELECT * FROM mytable
WHERE tindex IN ('words1','words2')
GROUP BY tn,col HAVING COUNT(1)=2 order by date limit 10;

using 23.8711s

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  mytable index   NULL    date    5   NULL    19359399    Using where; Using index; Using temporary

Some notes:

Why is there no PRIMARY KEY in the table?
Why date is of type int and not date or datetime or timestamp?
Why is it called date when it stores date and time?

Regarding the actual question, the efficiency of the query, an index on (tindex, tn, col, date) would help in my opinion much more than the other suggestions. And since you have no primary key and the (tindex, tn, col) is unique (you have added another unique index), my suggestion is:

(optionally) drop that unique index)
define the primary key as (tindex, tn, col):

ALTER TABLE mytable
  DROP INDEX __the_name_of_the_unique_index,  -- this is optional
  ADD PRIMARY KEY (tindex, tn, col) ;

This will take some time (that's why I suggest you do both operations in one pass.)

Then you can measure your query and all the suggested rewritings of the query (and check if the primary index is used).

Why is this a better index?

Because the tindex column is the first column in the index, all the rows with tindex='anticorrosive' will be in consecutive pages in the index (and all the rows with tindex='corrosive' will be in another part of index but still in consecutive pages.) So, reading (scanning) these 2 parts of the index will be much faster than scanning the whole table or scanning the whole index twice (which is what mysql does essentially with the (tn, col, tindex) index.)

Another benefit of making this index the primary key is that you get rid of the (hidden) 6-byte column that InnoDB added and has been using as the clustered index of the table (since you did not provide any primary or unique constraint/index) so the table is now less wide. The (tindex, tn, col) will be the clustered index of the table from now on. That also means that the date values will be available for the query - after the 2 parts of the index have been scanned.


How to read multiple record from xml to insert in table in SQL? DECLARE @tbl_XMLResult table
(
    Status varchar(50),
    Address varchar(100),
    ListPrice varchar(25),
    SoldPrice varchar(25),
    YearBuilt varchar(25),
    SF varchar(25),
    Acres varchar(25),
    OnMarkDate varchar(25),
    PendDate varchar(25),
    SaleDate varchar(25),
    DOM varchar(25)
)

DECLARE @XMLRESULT xml

exec usp_ReportResults @query = "759,905,1048,170,725,80129", @ReportName = "GenReport", @XMLResult = @XMLRESULT OUTPUT

INSERT INTO @tbl_XMLResult(Status, Address, ListPrice, SoldPrice,
        YearBuilt, SF, Acres, OnMarkDate, PendDate, SaleDate, DOM)
    SELECT
        Listing.value('(/ListingStatus/text())[1]', 'varchar(50)') AS Status,
        ReportData.value('(/Listing_StreetAddress/text())[1]', 'varchar(50)') AS Address,
        Listing.value('(/ListPrice/text())[1]', 'varchar(50)') AS ListPrice,
        Listing.value('(/ClosePrice/text())[1]', 'varchar(50)')AS SoldPrice,
        Listing.value('(/YearBuilt/text())[1]', 'varchar(50)') AS YearBuilt,
        Listing.value('(/TotalLvSpace/text())[1]', 'varchar(50)') AS SF,
        Listing.value('(/AcresApx/text())[1]', 'varchar(50)') AS Acres,
        Listing.value('(/ListDate/text())[1]', 'varchar(50)') OnMarkDate,
        Listing.value('(/PendingDate/text())[1]', 'varchar(50)') AS PendDate,
        Listing.value('(/CloseDate/text())[1]', 'varchar(50)') AS SaleDate,
        Listing.value('(/DaysOnMarket/text())[1]', 'varchar(50)') DOM
    FROM
        @XMLRESULT.nodes('/Results/Report/Listings/Listing') AS ListingTable(Listing),
        @XMLRESULT.nodes('/Results/Report/Listings/Listing/PMCReportData') AS TempTable(ReportData)

SELECT *
FROM @tbl_XMLResult

I am trying to get result from stored procedure as in xml format and by reading that result store it into the temporary table created above. But I'm getting result as null and current stored procedure returning only six records in xml but it showing 36 rows in temporary table with null values in it. How to fix that?

This is the xml format:

You need to remove the / at the start of the value() function. i.e. this one Listing.value('(/.
You also need to remove the CROSS JOIN that occurs because you are using old style JOIN syntax between ListingTable(Listing) and TempTable(ReportData). You can do that by referencing the PCMReportData slightly differently.

Based on your XML schema, you should then get values e.g.

DECLARE @XMLRESULT XML;

SET @XMLRESULT = '<Results ReplyCode="0" ReplyText="Operation Successfull">
                    <Report PropertyType="ResidentialProperty">
                    <Listings>
                    <Listing>
                      <ListingID>759</ListingID>
                      <PCMReportData>
                        <Listing_StreetAddress>My Street Address</Listing_StreetAddress>
                      </PCMReportData>
                    </Listing>
                    <Listing>
                      <ListingID>852</ListingID>
                    </Listing>
                    </Listings>
                    </Report>
                    </Results>';

SELECT
  Listing.value('(ListingID/text())[1]', 'varchar(50)') AS ListingID,
  Listing.value('(PCMReportData/Listing_StreetAddress/text())[1]', 'varchar(50)') AS Address
FROM @XMLRESULT.nodes('/Results/Report/Listings/Listing') AS ListingTable(Listing);

You're doing a CROSS JOIN between ListingTable(Listing) and TempTable(ReportData). Therefore the 6 results, multiplied by the 6 inner results = 36.


"can't return a result set in the given context" error when calling stored procedure - differences between mysql 5.0 and 5.5? I have a stored procedure to regenerate leafs (nleft, nright) in simple (id, id_parent) tree. (sample table)

When I'am calling

CALL EdgeListToNestedSet( 'category', 'id_category', 'id_parent');

it works great with version 5.5.29 of mysql but on 5.0.26  I've got an error:

#1312 - PROCEDURE test.EdgeListToNestedSet can't return a result set in the given context

Any ideas what can be wrong and how to make it working?

Older versions of phpMyAdmin have issues with MySQL. This has been asked 8 times in StackOverflow with the tag mysql-error-1312.

This bug was seen and addressed in MySQL 5.0.09 beta about 6 weeks after MySQL 5.0.26 was released Oct 09, 2006. Still, you got this error.

According to http://qasimbadami.wordpress.com/tag/mysql-error-1312/, you should make sure you are using mysqli / PDO.


Sum of different values on the same row in a table I have a table with numbers from 0-5, under different field names (Score1, Score2 etc. all the way to Score50) that need to be summed at the last column of that same row. Each row represents a different client with its own info.

Is it possible and if it is, some help would be nice.

select *, Score1 + Score2 + ... + Score50 as TotalScore
from table

You can fill in the rest of the scores :-)

By the way, you really should normalise this table. It will make your life much easier.


How to collapse into a single row from multiple rows Please refer to the attached screenshot for the requirement. I have to collapse rows into a single row with latest test_date in one column and second_latest_date in an other column and latest order_number.

Please share the tips to create a SQL Server query that can (a) transform data from the Source table to (b) data as shown in the Target table.

Data sample for the Source table:

Data sample for the Target table:


When do we usually map all partitions to the same filegroup? I was studying "Partition Function" and "Partition Schema" in SQL-Server in MSDN. I found this syntax that maps all partitions to the same filegroup.
For example:

CREATE PARTITION FUNCTION myRangePF3 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
GO
CREATE PARTITION SCHEME myRangePS3
AS PARTITION myRangePF3
ALL TO ( test1fg );

My question is that what is the key point in doing this?
Is this even called partitioning???
When do we usually map all partitions to the same filegroup?

Partitioning a table with all of the partitions on the same filegroup gives you the following benefits:

Partition elimination in queries
Ability to partition switch within the filegroup
Rowgroups cannot be split across partitions for CCIs which can lead to better rowgroup elimination in queries
Incremental statistics as of SQL Server 2014
Other maintenance operations can be done at the partition level such as rebuilds
Different behavior for parallel scans which can be helpful in some cases.

These benefits are also present when putting partitions on different filegroups, except when noted otherwise.

One reason to put partitions on different filegroups is when you want to put older data on a filegroup that's mapped to a slower tier of storage.


Can't remove active node from SQL Server 2008 R2 Clustered Instance I am trying to remove the active and only node from a SQL Server 2008 R2 failover cluster.

Everything checks out and then I get this error

  Final result:                  Failed: see details below
  Exit code (Decimal):           -568706566
  Exit facility code:            1562
  Exit error code:               14842
  Exit message:                  Cannot create a file when that file already exists. (Exception from HRESULT: 0x800700B7)
  Start time:                    2015-05-30 16:09:38
  End time:                      2015-05-30 16:10:28
  Requested action:              RemoveNode

2015-05-30 18:47:12 Slp: Sco: Attempting to write hklm registry key SOFTWARE\Wow6432Node\Microsoft\MSSQLServer to file C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20150530_184438\Registry_SOFTWARE_Wow6432Node_Microsoft_MSSQLServer.reg_
2015-05-30 18:47:13 Slp: Cannot create a file when that file already exists. (Exception from HRESULT: 0x800700B7)
2015-05-30 18:47:13 Slp: Watson bucket for exception based failure has been created

2015-05-30 16:10:24 Slp: Error: Action "Microsoft.SqlServer.Configuration.SetupExtension.ValidateFeatureSettingsAction" threw an exception during execution.
2015-05-30 16:10:24 Slp: Microsoft.SqlServer.Setup.Chainer.Workflow.ActionExecutionException: Cannot create a file when that file already exists. (Exception from HRESULT: 0x800700B7) ---> System.Runtime.InteropServices.COMException (0x800700B7): Cannot create a file when that file already exists. (Exception from HRESULT: 0x800700B7)
2

Any thoughts on what is causing this error?

I needed to remove an instance that was only installed on one node on a two node cluster. This unwanted instance had never been installed on the second node.

The error in the summary and detail log files stored in Setup Bootstrap sent me in the wrong direction.

I finally resolved the issue by following the following steps.

Adding a node to the unwanted instance
Move the unwanted instance to the second node
Removed the node from the first node
Removed the node from the second node

Unwanted instance removed from the cluster.


Access Db `Long Text` Unicode Byte Corruption I have a column that is Long Text when I attempt to edit a cell it randomly inserts characters and does other weird things. The column has French HTML text in it. What could be causing this and what could fix it? I assume there might be a byte offset problem but I never dealt with that before. I've googled around and haven't been able to find anything on it.

E.g.,

Se présente en nappes.

When I type a space, , right after the first word Se it inserts an e, e.g.,

Se e présente en nappes.

I don't work with Access very much so this is quite strange to me.

Thanks in advance.

It turned out to be a \n character that was imported into the record. Did a query on the data and removed it and updated the record. Not having issues anymore.


Sorting multiple columns in a full outer join, possibly containing NULLs I am trying to compare a plan against a history to see if there are steps omitted or if unplanned steps have been included. The regular, planned history items as well as the anomalies are intended for an elaborate report. The problem is, I have a column in each joined table that I need for sorting, but due to the full outer join I cannot guarantee them to be not null, which breaks sorting. I'll continue with a simplified example, where the plan corresponds to a recipe:

Table: RecipeSteps

 Id | RecipeId | Position | Message
----+----------+----------+----------------------------
 50 |      123 |        1 | Wash vegetables
 51 |      123 |        2 | Peel vegetables
 52 |      123 |        3 | Cut meat
 53 |      123 |        4 | Turn on stove
 54 |      123 |        5 | Cook and stir it
 55 |      123 |        6 | Turn off stove
----+----------+----------+----------------------------

... and the history to the actual steps while cooking:

Table: History

 Id | TimeStamp | Session | StepId | Message
----+-----------+---------+--------+-----------------------------
 90 |  00:01:00 |     321 |   NULL | Chef entered kitchen
 91 |  00:02:00 |     321 |     51 | Chef peeled vegetables
 92 |  00:03:00 |     321 |     52 | Chef cut meat
 93 |  00:04:00 |     321 |   NULL | Chef picked his nose
 94 |  00:05:00 |     321 |     53 | Chef turned on stove
 95 |  00:06:00 |     321 |     54 | Chef started cooking
 96 |  00:10:00 |     321 |   NULL | Chef left kitchen
 97 |  01:00:00 |     321 |   NULL | FIRE!
----+-----------+---------+--------+-----------------------------

Now, I want to compare these tables in a result table with a full outer join similar to this:

Table: Result

 Sorting | TimeStamp | Position | Message                | Comment
 --------+-----------+----------+------------------------+---------------
       1 |  00:01:00 |     NULL | Chef entered kitchen   | unplanned
       2 |      NULL |        1 | Wash vegetables        | omitted
       3 |  00:02:00 |        2 | Chef peeled vegetables | planned
       4 |  00:03:00 |        3 | Chef cut meat          | planned
       5 |  00:04:00 |     NULL | Chef picked his nose   | unplanned
       6 |  00:05:00 |        4 | Chef turned on stove   | planned
       7 |  00:06:00 |        5 | Chef started cooking   | planned
       8 |      NULL |        6 | Turn off stove         | omitted
       9 |  00:10:00 |     NULL | Chef left kitchen      | unplanned
      10 |  01:00:00 |     NULL | FIRE!                  | unplanned
 --------+-----------+----------+------------------------+---------------

Then the reader of the report sees:

Entering/leaving the kitchen is not part of the recipe, but it has been recorded anyway.
The chef forgot to wash the vegetables (Ugh!) or the step has not been recorded for some reason (Phew!).
History continues as planned, with the exception of the chef picking his nose in between (double-Ugh!)
The chef forgot to turn off the stove, which caused the fire, in the end.

I came up with the following SELECT statement, selecting from this query with ORDER BY Sorting ASC, but I still have a problem to fill the Sorting criterion:

SELECT
  0 Sorting, -- this has to be replaced
  h.TimeStamp,
  r.Position,
  '...' Message, -- case-when-construct, not important for the question
  '...' Comment -- case-when-construct, not important for the question
FROM RecipeSteps r
FULL OUTER JOIN History h
ON h.StepId = r.Id
WHERE (r.RecipeId = 123 OR r.RecipeId IS NULL)
AND (h.Session = 321 OR h.Session IS NULL)

There is a natural order in the history given by the timestamps and one in the recipe given by the Position column. As they are never both null simultaneously, I could create the Sorting column based on these, but I couldn't figure out, so far, how to achieve this.

I know I can sort both separately, enumerate and compare both sequences etc. programmatically, but I'm curious if this is also possible with non-procedural SQL.

Please note: The query below doesn't take into account partitioning by Session to keep it simple.

The main idea is to split the data set into two groups and process them separately. History goes first. I decided to make History more important than Steps. If some steps are swapped, they will be presented in the history order.

So, the first group is all rows from History plus possible details from the corresponding Step. This is a simple LEFT JOIN.

The second group is omitted Steps. Another LEFT JOIN with a filter.

If you run the first CTE_History ordering by timestamp you'll see that unplanned events have NULL Position, but they will be in correct position in the result set, because of their timestamp. All we need to do is fill in these blanks.

+-------------+---------------------+----------+------------------------+-----------+
| NewPosition |         ts          | Position |       NewMessage       |  Comment  |
+-------------+---------------------+----------+------------------------+-----------+
|           0 | 2017-01-01 00:01:00 | NULL     | Chef entered kitchen   | unplanned |
|           2 | 2017-01-01 00:02:00 | 2        | Chef peeled vegetables | planned   |
|           3 | 2017-01-01 00:03:00 | 3        | Chef cut meat          | planned   |
|           3 | 2017-01-01 00:04:00 | NULL     | Chef picked his nose   | unplanned |
|           4 | 2017-01-01 00:05:00 | 4        | Chef turned on stove   | planned   |
|           5 | 2017-01-01 00:06:00 | 5        | Chef started cooking   | planned   |
|           5 | 2017-01-01 00:10:00 | NULL     | Chef left kitchen      | unplanned |
|           5 | 2017-01-01 01:00:00 | NULL     | FIRE!                  | unplanned |
+-------------+---------------------+----------+------------------------+-----------+

One way to do it is to take the last non-null value from the previous rows. Unfortunately, SQL Server doesn't implement IGNORE NULLS clause in the LAST_VALUE function, so we have to use a workaround with MAX and rely on the fact that Position grows as ts grows. In a more general case it will be a bit more complicated, as Itzik Ben-Gan showed in his The Last non NULL Puzzle.

The very first NULL is changed to 0 (or some number that is less than any possible Position).

Once we have values for all Positions, it is trivial to union this result with all omitted steps and order them.

In this approach all omitted steps go after unplanned steps in case of ambiguity, so Turn off stove will appear last. If you choose to calculate MIN of following rows instead of MAX of previous, this rule will be swapped.

Sample data

DECLARE @RecipeSteps TABLE (Id int, RecipeId int, Position int, Message nvarchar(255));
INSERT INTO @RecipeSteps (Id, RecipeId, Position, Message) VALUES
(50, 123, 1, 'Wash vegetables '),
(51, 123, 2, 'Peel vegetables '), --
(52, 123, 3, 'Cut meat        '), --
(53, 123, 4, 'Turn on stove   '), --
(54, 123, 5, 'Cook and stir it'), --
(55, 123, 6, 'Turn off stove  ');

DECLARE @History TABLE (Id int, ts datetime2(0), Session int, StepId int, Message nvarchar(255));
INSERT INTO @History (Id, ts, Session, StepId, Message) VALUES
(90, '2017-01-01 00:01:00', 321, NULL, 'Chef entered kitchen  '),
(91, '2017-01-01 00:02:00', 321,   51, 'Chef peeled vegetables'),
(92, '2017-01-01 00:03:00', 321,   52, 'Chef cut meat         '),
(93, '2017-01-01 00:04:00', 321, NULL, 'Chef picked his nose  '),
(94, '2017-01-01 00:05:00', 321,   53, 'Chef turned on stove  '),
(95, '2017-01-01 00:06:00', 321,   54, 'Chef started cooking  '),
(96, '2017-01-01 00:10:00', 321, NULL, 'Chef left kitchen     '),
(97, '2017-01-01 01:00:00', 321, NULL, 'FIRE!                 ');

Query

WITH
CTE_History
AS
(
    SELECT
        ISNULL(
            MAX(Position)
            OVER (ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
            , 0) AS NewPosition
        ,H.ts
        ,S.Position
        ,H.Message AS NewMessage
        ,CASE WHEN S.Id IS NULL THEN 'unplanned' ELSE 'planned' END AS Comment
    FROM
        @History AS H
        LEFT JOIN @RecipeSteps AS S ON S.Id = H.StepId
)
,CTE_OmittedSteps
AS
(
    SELECT
        Position AS NewPosition
        ,H.ts
        ,S.Position
        ,S.Message AS NewMessage
        ,'omitted' AS Comment
    FROM
        @RecipeSteps AS S
        LEFT JOIN @History AS H ON S.Id = H.StepId
    WHERE
        H.Id IS NULL
)
SELECT * FROM CTE_History
UNION ALL
SELECT * FROM CTE_OmittedSteps
ORDER BY NewPosition, ts;

Result

+-------------+---------------------+----------+------------------------+-----------+
| NewPosition |         ts          | Position |       NewMessage       |  Comment  |
+-------------+---------------------+----------+------------------------+-----------+
|           0 | 2017-01-01 00:01:00 | NULL     | Chef entered kitchen   | unplanned |
|           1 | NULL                | 1        | Wash vegetables        | omitted   |
|           2 | 2017-01-01 00:02:00 | 2        | Chef peeled vegetables | planned   |
|           3 | 2017-01-01 00:03:00 | 3        | Chef cut meat          | planned   |
|           3 | 2017-01-01 00:04:00 | NULL     | Chef picked his nose   | unplanned |
|           4 | 2017-01-01 00:05:00 | 4        | Chef turned on stove   | planned   |
|           5 | 2017-01-01 00:06:00 | 5        | Chef started cooking   | planned   |
|           5 | 2017-01-01 00:10:00 | NULL     | Chef left kitchen      | unplanned |
|           5 | 2017-01-01 01:00:00 | NULL     | FIRE!                  | unplanned |
|           6 | NULL                | 6        | Turn off stove         | omitted   |
+-------------+---------------------+----------+------------------------+-----------+

Query for SQL Server 2008

Here I replaced the window function MAX with a subquery that I put into OUTER APPLY. It produces the same result as above.

WITH
CTE_HistoryRaw
AS
(
    SELECT
        H.ts
        ,S.Position
        ,H.Message AS NewMessage
        ,CASE WHEN S.Id IS NULL THEN 'unplanned' ELSE 'planned' END AS Comment
    FROM
        @History AS H
        LEFT JOIN @RecipeSteps AS S ON S.Id = H.StepId
)
,CTE_History
AS
(
    SELECT
        ISNULL(A.NewPosition, 0) AS NewPosition
        ,ts
        ,Position
        ,NewMessage
        ,Comment
    FROM
        CTE_HistoryRaw AS Curr
        OUTER APPLY
        (
            SELECT TOP(1)
                Prev.Position AS NewPosition
            FROM CTE_HistoryRaw AS Prev
            WHERE
                Prev.ts <= Curr.ts
                AND Prev.Position IS NOT NULL
            ORDER BY Prev.ts DESC
        ) AS A
)
,CTE_OmittedSteps
AS
(
    SELECT
        Position AS NewPosition
        ,H.ts
        ,S.Position
        ,S.Message AS NewMessage
        ,'omitted' AS Comment
    FROM
        @RecipeSteps AS S
        LEFT JOIN @History AS H ON S.Id = H.StepId
    WHERE
        H.Id IS NULL
)
SELECT * FROM CTE_History
UNION ALL
SELECT * FROM CTE_OmittedSteps
ORDER BY NewPosition, ts;


Is it ok to use 4 application server against one database server? Initially I have two database server (Master Master Replication config with Haproxy load balancing) A and B. And two application server say P and Q.
Both P and Q is pointed towards A will move to B in case of failure. OLTP operations are huge in numbers per minute.

Now we are adding two more app servers R and S.

Just want to know experts opinion. Is it ok to point both R and S towards A?
Or should I use B this time.

Please suggest.

Having 4 application servers pointed at the same DB is not a problem. You may want to monitor the various metrics such as connection counts on each server. In theory, there may be a few more connections than previously but that is ok. You may also want to start planning on sharding. A cheap and easy way to do this is to have auto increment offsets being different on each machine. This would allow you to use both machines in a distributed fashion rather than a failover (failover would still work).

I would just be careful on the other side of the fence. With 2 additional application servers people might suspect that they're able to double the traffic.


Minimizing the number of parent rows for a distinct column in a child table I have three tables with the following format

A        B       C
-----    -----   -----
aKey     bKey    fk_c_b
         fk_b_a  c_enum
                 c_value

aKey and bKey are the primary key for each table, C does not have a primary key. fk_b_a and C.fk_c_b are the foreign keys that points to the parent table. c_enum is a enum of 87 different values. C.c_value is float that can be any value

The relationships between the tables are:

(A.aKey) 0..1 --- * (B.fk_b_a)
(B.bKey) 0..1 --- * (C.fk_c_b)

There are:

57,668 rows in A
650,768 rows in B
34,734 rows in C

I need to display a distinct list of all c_enum values and their parents, however If two rows in C share the same fk_c_b and c_value values but different c_enum values I can use not those two rows in the distinct list.

select aKey, bKey, c_enum, c_value, newid() as id
into #myResultSet
from A
inner join B on fk_b_a = aKey
inner join C as temp1 on fk_c_b = bKey
where c_value not in (select c_value from C as temp2
                         where temp1.c_enum <> temp2.c_enum
                            and temp1.fk_c_b = temp2.fk_c_b
                            and temp1.c_value = temp2.c_value)

--This is the step that I do not know how to optimize
delete #myResultSet where id <> (select top 1 id from #myResultSet as t1 where #myResultSet.c_enum = t1.c_enum)

Also in that process I need to minimize the the distinct count of aKey and bKey. So If I did

select count(distinct aKey), count(distinct bKey), count(*) from #myResultSet

the result would have the minimum values possible. The optimal result is 1, 1, 87 and the worst case would be 87, 87, 87. My current method does not attempt to get minimal values (my results are 14, 17, 87 which is not bad, but I think that is just luck with this dataset, running on a different dataset may return worse results) and I am having a lot of trouble figuring out how to do it the right way.

What would be the correct way to solve this problem? My current method is not optimal all and I have been having a lot of trouble figuring out what I need to do to build #myResultSet with minimal values for count(distinct aKey) and count(distinct bKey).

I think i found the solution. On the delete step I put the these two order by clauses in

delete #myResultSet where id <>
    (
    select top 1 id
    from #myResultSet t1
    where #myResultSet.c_enum = t1.c_enum
    order by count(aKey) over(partition by aKey),
             count(bKey) over(partition by aKey, bKey)
    )

This gives me 10,13,87 which I think is the optimal solution I can get with my data set.

If I am wrong or there is a better way to do this I am welcome to more answers.


AWS Aurora running out of freeable memory and crashing We have an issue with our AWS Aurora MySQL instance running out of freeable memory and crashing as a result. AWS's response was to upgrade to a bigger instance, but I don't feel that will necessarily solve the problem.

We are on a db.r5.large instance which is 15GB of RAM.

After a reboot it drops to 5GB of RAM which is pretty much expected, but then gradually declines over the course of a week to 0GB RAM and then reboots - and sometimes fails to do so and requires a manual reboot of the instance.

CPU usage generally hovers around 15% which spikes when we do heavy late-night processing.

From How large should be mysql innodb_buffer_pool_size?

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

gives 10GB as required to hold all data and indexes in memory.

SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM
(SELECT variable_value PagesData
FROM information_schema.global_status
WHERE variable_name='Innodb_buffer_pool_pages_data') A,
(SELECT variable_value PageSize
FROM information_schema.global_status
WHERE variable_name='Innodb_page_size') B;

gives 4.6GB in the InnoDB Buffer Pool

SHOW FULL PROCESSLIST

shows only a couple of processes and no hung threads.

My question is, is there a way of ensuring the freeable memory never goes past a certain point and is released back for usage e.g it should never fall below 2GB so if something is memory heavy, it won't exhaust memory.

I understand there might be a performance hit for this, but before I scale up to a bigger instance (at double the cost) and experience the same slow decline to 0, I'd like to see how it performs as it's better than a production machine crashing at random times.

Additonial information:

I've added the various MySQL outputs to https://pastebin.com/CkRxqL04

Not really sure how to run the Unix commands on the RDS instance as there's no access to the filesystem.

I checked other logs and it seems the CPU is steadily climbing too when it should burst and drop back to a normal level.

This is not a high traffic site, though it does do some heavy lifting behind the scenes.

While doing further digging, I noticed there's an attempt at synching to a slave still active from when the initial migration was done. Even as root I can't remove it, but could this be eating memory / CPU?

2019-08-29 00:30:04 7112 [Note] Error reading relay log event: slave SQL thread was killed
2019-08-29 00:30:04 7112 [Note] Slave I/O thread killed while connecting to master
2019-08-29 00:30:04 7112 [Note] Slave I/O thread exiting, read up to log 'mysql-bin-changelog.000016', position 904410367

UPDATE 19/09/09

The pattern still seems to be the same - CPU is going up and up - albeit gradually.

Slow log is empty (I know it's turned on as there was previously entries in it).

I also installed an APM tool and there is nothing suspicious coming up there - all script execute in the time they should as do the Db queries. We have some long-running CRON jobs (3 minutes), but they shouldn't affect the DB as they're on the webserver box.

Rate Per Second=RPS - Suggestions to consider for your AWS Aurora Parameters Group

log_output=TABLE,FILE  # from TABLE so you can review your Error Log FILE after crash
log_warnings=2  # from 1 to include aborted_connects and other info in your Error Log
query_cache_min_res_unit=512  # from 4096 to conserve QC RAM used
query_cache_size=64M  # from ~ 443M to reduce CPU cycles used for QC management
innodb_lru_scan_depth=100  # from 1024 to conserve 90% CPU cycles used for function
thread_cache_size=24  # from 11 to reduce threads_created count
read_rnd_buffer_size=256K  # from 512K to reduce Handler_read_rnd_next RPS of 2,558
read_buffer_size=512K  # from 256K to reduce Handler_read_next RPS of 29,608
tmp_table_size=64M  # from 16M to expand capacity
max_heap_table_size=64M  # from 16M to reduce created_tmp_disk_tables RPHr of 1,134

if ALLOWED,

innodb_io_capacity=1900  # from 200 to enable higher IOPS to SSD devices

OBSERVATIONS,
innodb_buffer_pool_size adjustment to be considered when innodb_buffer_pool_reads > 50 RPS (you are less than 1 RPS today)
innodb_change_buffering=none    to be reconsidered, review refman details

These Suggestions may help stabilize your instance.

Disclaimer: I am the content author of website mentioned in my profile, Network profile where we offer FREE downloadable Utility Scripts to assist with improving performance, more suggestions, contact info.


ASM and IPv6 Compatibility In the whitepaper mentioned below, Under 'Limitations' it says ASM and fan based notifications are not supported by IPv6 in Oracle. I'm just curious what ASM has to do with ipv4 or ipv6 or ipv4? ASM is just a file system.
http://www.oracle.com/technetwork/database/enterprise-edition/oracledatabaseipv6sod-2141330.pdf

ASM isn't just a file system.  It's also a volume manager.

An ASM instance is a cut-down Oracle instance that database instances talk to in order to find out how to access the blocks they need.  The ASM instance doesn't write data to or read data from the disks itself, it only provides the information that the database requires in order to do this.

This communication is over the network, so involves IP.  Hence the note about IPv4/IPv6.


Is that a good idea to use one database for 50.000+ shops? I know Shopify use only one database for all shops.
But how can they handle their database with such a big data?
Is that a good idea to use single database for 50.000+ shops?

Please note: I'm answering from a SQL Server perspective, so I mention some concepts specific to SQL Server, but I believe all of these concepts have equivalents in other major RDBMS platforms, with similar benefits and limitations.I will also probably continue editing this answer as I think of other potential pros/cons.

Well, it really depends on schema, volume, etc. What exactly is a shop storing? How is it different from storing data about 50,000 cats or 50,000 products or 50,000 wingnuts?

There are several reasons (other than just the size aspect on its own) why you might not want to store data for 50,000 different customers in a single database, if indeed the data can be completely segregated by customer (not including lookup tables like zipcodes or application-specific tables, which could go into a single, central database):

if one customer outgrows the application, there is no easy way to extract just their data and move it to another instance, server, etc. to scale out, unless you plan ahead and partition on something like CustomerID and have 50,000 filegroups (you're limited to 15,000 partitions anyway, or 1,000 if you're on an older version of SQL Server, and having too many filegroups can be disastrous). Also note that partitioning requires Enterprise Edition.
if it turns out that all of your customers are simply too big for this instance, scaling out means getting new hardware and moving the entire database there (and potentially doing that again down the road).
deleting a customer can be equally painful, as you will have to delete some % of rows from very big tables, and that will not be cheap.
you will likely have wide distribution of customer data (one customer with a billion rows, another customer with 5,000). This can lead to things like parameter sniffing and detrimental performance involving cardinality and plan quality (since you will likely be re-using the same plans for the same queries against very different data sets).
all of your customers are subject to the exact same SLAs and HA/DR plans. You either have the entire database in full recovery mode with n-minute log backups, or you are in simple and rely on full+diff backups. If you have to revert because of a customer error, or need to recover the database to a point in time, that affects every single customer.
there is potential for errors in data retrieval - bugs in where clauses, for example, could lead to one customer seeing another customer's data, or all of the other customers' data.
there may be legal implications (some companies will have strict requirements in place that you do not place their data in the same database as any other company, and particularly that of their competitors).
if security of any one customer's data is important, then achieving that is much easier using database separation than separation within a table.

Some advantages to having each customer in a separate database (or at least having multiple databases, each for a group of customers):

in terms of size, it will take about the same size on disk.
scaling out is easier, since you can just move a database (or many) to a different server.
deleting a customer and all of its data roughly equates to DROP DATABASE.
you're using more memory for plans (or you have fewer plans in cache per customer), but at least those plans are relevant to the data in their respective databases and are less prone to statistics / parameter sniffing issues.
you can easily have different SLAs and DR plans, placing some databases in full and others in simple. Also reverting or restoring to a point in time only affects that customer.
you can easily place different databases (say, your high priority customers) on faster I/O. You could do this in a single database with filegroups, but that is much trickier to manage (at least IMHO).

Some drawbacks:

size aside, you probably won't want to have 50,000 databases on a single instance of SQL Server, so this will probably mean scaling out to multiple servers.
startup time goes up because there is some inherent overhead in starting up each database.
the app has to be a bit smarter - instead of just having CustomerID on the where clause, it has to dynamically connect to CustomerID's database. This isn't hard with a proper middle tier but it is a change.
yes, you have many copies of the same tables and procedures, but code and schema are identical across databases, just the data is different. So deploying code/schema changes is now just a loop instead of a single execution.
maintenance is a little bit different when you're managing 50,000 databases - again the overall size is roughly the same but the process has to change - you can't just defrag / reindex / back up all 50,000 databases at once. Having said that, at my previous job I managed instances with 500-1,000 identical databases, and the difference between managing 3 identical databases and 750 identical databases is simply the time it takes.


Different collation between System and User databases in SQL Server Recently, I have came across a situation where I found there is different SQL Collation is set for System databases and user databases on a sql instance in production.

Like: Danish_Norwegian_CI_AS for master, tempdb,model and msdb.
    Danish_Norwegian_CI_AI for USER_DB's.

Would there be any problem with this type of collation settings?

This question is definitely a duplicate of SQL Server default collation vs database with different collation - potential problems?

However, as that question was purely generic and this question specifies the two collations, I can at least clarify one of the issues raised in that other question:

  do you see any potential data loss?

In this case there is no potential data loss due to the only difference being a sensitivity (accent sensitivity), which affects sorting and comparisons, but not which character set is being used to store VARCHAR data.

Though, depending on how one defines "data loss", it should be pointed out (at least for the sake of thoroughness) that there is a possibility that a temp table is being used that contains a string column that is not being given an explicit collation when the temp table is created (either Danish_Norwegian_CI_AI or DATABASE_DEFAULT, there is no appreciable difference between them here), and is thus using Danish_Norwegian_CI_AS. That could lead to a query classifying two values as being different if they only differ in accents (well, and yes, also case, width, and kana-type), whereas using Danish_Norwegian_CI_AI would classify them as being equal. This can return unexpected results, or cause an error. But, neither case is a character conversion, which is what I was initially addressing in terms of "data loss".


Cannot connect to DB2 database after restoring an offline backup This is for DB2 9.7 Enterprise Server Edition on AIX.

I had a full, offline backup taken of several databases within our instance in a performance environment.

I wanted to restore these databases to our integration environment.

I ran the command

db2 restore database database_name from
/bkp/instance_name/OfflineBackups/RestorePoint/my_restore_point
taken at 20120321103846 into database_name without prompting;

for each of my databases (modifying the database name and timestamp appropriately of course).

Each database restored successfully according to DB2. However, now when I wish to check if the database did indeed restore what I thought, I try to connect to a database and I get the error

    SQL1117N  A connection to or activation of database "database_name" cannot be made
because of ROLL-FORWARD PENDING.  SQLSTATE=57019

I didn't have any in process transactions in the previous database because all connections were forced off and the database was down for an offline backup. What do I do?

Found the answer here.

I needed to run the command

db2 rollforward db database_name to end of backup and complete;

This commits everything to the logs and places the database in a no longer pending state, thus allowing connection to it. I needed to do this for each database restored.

  EDIT: Found this nifty nugget while attending a DB2NightShow episode. With 9.7 FP2, they introduced a registry variable called
  DB2_RESTORE_GRANT_ADMIN_AUTHORITY. This is handy if you are restoring
  a backup from one instance to another, as it automatically grants
  instance owner SECADM, DBADM, DATAACCESS, and ACCESSCTRL on the
  databases restored. With 9.7 FP5, the setting is dynamic and does not
  require bouncing of the instance. I realize my original question had
  nothing to do with security, but it can be encountered and thought
  that this would be a handy tidbit placed here.

  EDIT #2: I recently found out that if the backup is an offline backup based on an archival logged database, I could have included the
  clause WITHOUT ROLLING FORWARD in my RESTORE command. This would
  have taken the database out of roll forward pending state immediately
  upon successful restore, which would mean that I would not have had to
  then issue the ROLLFORWARD DB command.


Performance of an In-Memory Table is worse than a disk-based table I have a table in SQL Server 2014 that looks like the following:
CREATE TABLE dbo.MyTable
(
[id1] [bigint] NOT NULL,
[id2] [bigint] NOT NULL,
[col1] [int] NOT NULL default(0),
[col2] [int] NOT NULL default(0)
)

with (id1,id2) being the PK. Basically, id1 is an identifier to group a set of results (id2, col1, col2), whose pk is id2.
I'm trying to use an in-memory table to get rid of an existing disk-based table which is my bottleneck.

The data in the table are written -> read -> deleted once.
Each id1 value has several (tens/hundreds of) thousands of id2.
Data are stored in the table for a very short amount of time, e.g. 20 seconds.

The queries performed on this table are the following:
-- INSERT (can vary from 10s to 10,000s of records):
INSERT INTO MyTable
  SELECT @fixedValue, id2, col1, col2 FROM AnotherTable

-- READ:
SELECT id2, col1
FROM MyTable INNER JOIN OtherTbl ON MyTable.id2 = OtherTbl.pk
WHERE id1 = @value
ORDER BY col1

-- DELETE:
DELETE FROM MyTable WHERE id1 = @value

Here's the current definition that I used for the table:
CREATE TABLE dbo.SearchItems
(
  [id1] [bigint] NOT NULL,
  [id2] [bigint] NOT NULL,
  [col1] [int] NOT NULL default(0),
  [col2] [int] NOT NULL default(0)

  CONSTRAINT PK_Mem PRIMARY KEY NONCLUSTERED (id1,id2),
  INDEX idx_Mem HASH (id1,id2) WITH (BUCKET_COUNT = 131072)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)

Unfortunately, this definition results in a degradation of performance with respect to the previous situation with a disk-based table. The order of magnitude is more or less 10% higher (that in some cases reach 100%, so double time).
Most of all, I was expecting to gain a super-advantage in high-concurrency scenarios, given the lock-free architecture advertised by Microsoft. Instead, the worst performances are exactly when there are several concurrent users running several queries on the table.
Questions:

what is the correct BUCKET_COUNT to set?
what kind of index should I use?
why the performance are worse than with the disk-based table?

A query of sys.dm_db_xtp_hash_index_stats returns:
total_bucket_count=131072
empty_bucket_count=0
avg_chain_len=873
max_chain_length=1009
I changed the bucket count so the output from sys.dm_db_xtp_hash_index_stats is:
total_bucket_count=134217728
empty_bucket_count=131664087
avg_chain_len=1
max_chain_length=3
Still, the results are almost the same, if not worse.

While this post won't be a complete answer due to lacking information, it should be able to point you in the proper direction or otherwise gain insight which you can later share with the community.

  Unfortunately, this definition results in a degradation of performance with respect to the previous situation with a disk-based table. The order of magnitude is more or less 10% higher (that in some cases reach 100%, so double time).

  Most of all, I was expecting to gain a super-advantage in high-concurrency scenarios, given the lock-free architecture advertised by Microsoft. Instead, the worst performances are exactly when there are several concurrent users running several queries on the table.

This is troubling as it should definitely not be the case. Certain workloads are not for in memory tables (SQL 2014) and some workloads lend themselves to it. In most situations there can be a minimal bump in performance just by migrating and choosing the proper indexes.

Originally I was thinking very narrow about your questions regarding this:

  Questions:

  what is the correct BUCKET_COUNT to set?
  what kind of index should I use?
  why the performance are worse than with the disk-based table?

Initially I believed there to be an issue with the actual in memory table and indexes not being optimal. While there are some issues with the memory optimized hash index definition I believe the real issue to be with the queries used.

-- INSERT (can vary from 10s to 10,000s of records):
INSERT INTO MyTable
  SELECT @fixedValue, id2, col1, col2 FROM AnotherTable

This insert should be extremely fast if it were only involving the in memory table. It, however, also involves a disk based table and is subject to all of the locking and blocking associated with that. Thus, the real time waste here is on the disk based table.

When I did a quick test against 100,000 row insert from the disk based table after loading the data into memory - it was sub-second response times. However, most of your data is only kept for a very short amount of time, less than 20 seconds. This doesn't give it much time to really live in cache. Additionally I'm unsure how large AnotherTable really is and don't know if the values are being read off of disk or not. We have to rely on you for these answers.

With the Select query:

SELECT id2, col1
FROM MyTable INNER JOIN OtherTbl ON MyTable.id2 = OtherTbl.pk
WHERE id1 = @value
ORDER BY col1

Again, we're at the mercy of the interop + disk based table performance. Additionally, sorts are not cheap on HASH indexes and a nonclustered index should be used. This is called out in the Index guide I linked in the comments.

To give some actual research based facts, I loaded the SearchItems in memory table with 10 million rows and AnotherTable with 100,000 as I did not know the actual size or statistics of it. I then used the select query above to execute. Additionally I created an extended events session on wait_completed and put it into a ring buffer. It was cleaned after each run. I also ran DBCC DROPCLEANBUFFERS to simulate an environment where all of the data may not be memory resident.

The results weren't anything spectacular when looking at them in a vacuum. Since the laptop I'm testing this on is using a higher grade SSD, I artificially turned the disk based performance down for the VM I am using.

The results came in with no wait info after 5 runs of the query on just the in-memory based table (removing the join and no sub-queries). This is pretty much as expected.

When using the original query, however, I did have waits. In this case it was PAGEIOLATCH_SH which makes sense as the data is being read off disk. Since I am the only user in this system and didn't spend time to create a massive test environment for inserts, updates, deletes against the joined table I didn't expect any locking or blocking to come into effect.

In this case, once again, the significant portion of time was spent on the disk based table.

Finally the delete query. Finding the rows based off just ID1 is not extremely efficient with a has index. While it is true that equality predicates are what hash indexes are proper for, the bucket that the data falls into is based off the entire hashed columns. Thus id1, id2 where id1 = 1, id2 = 2, and id1 = 1, id2 = 3 will hash into different buckets as the hash will be across (1,2) and (1,3). This won't be a simple B-Tree range scan as hash indexes are not structured the same way. I would then expect this to not be the ideal index for this operation, however I wouldn't expect it to take orders of magnitude longer as experienced. I would be interested in seeing the wait_info on this.

  Most of all, I was expecting to gain a super-advantage in high-concurrency scenarios, given the lock-free architecture advertised by Microsoft. Instead, the worst performances are exactly when there are several concurrent users running several queries on the table.

While it's true that locks are used for logical consistency, the operations must still be atomic. This is done through a special CPU based compare operator (which is why In-Memory only works with certain [albeit almost all cpus made in the last 4 years] processors). Thus we don't get everything for free, there will still be some time to complete these operations.

Another point to bring up is the fact that in almost all of the queries, the interface used is T-SQL (and not natively compiled SPROCs) which all touch at least one disk based table. This is why I believe, in the end, we aren't actually having any increased performance as we're still constrained to the performance of the disk based tables.

Follow-Up:

Create an extended event session for wait_completed and specify a SPID known to you. Run the query and give us the output or consume it internally.
Give us an update on the output from #1.
There is no magic number for determining bucket count for hash indexes. Basically as long as the buckets don't get completely full and the row chains stay below 3 or 4, the performance should stay acceptable. This is kind of like asking, "What should I set my log file to?" - it's going to depend per process, per database, per usage type.


MYSQL join on subquery optimization This is a distilled version of the query that I am trying to run:

SELECT * FROM table1 t1
INNER JOIN (SELECT * FROM table2 WHERE foo = 'bar') t2
ON t1.id = t2.id;

The above query is incredibly slow because MYSQL fails to notice that the subquery in the join i.e. SELECT * FROM table2 WHERE foo = 'bar' has id as a primary key which it can use in the join clause.

Ofcourse, I should have written this query as:

SELECT * FROM table1 t1
INNER JOIN table2 t2
ON t1.id = t2.id
WHERE t2.foo = 'bar';

But, the problem is that the subquery(SELECT * FROM table2 WHERE foo = 'bar') is generated by a library that I do not control. What is the best way for me to optimize this given that I cannot do much about the subquery since I programmatically get it from somewhere else.

What's the MySQL version?

MariaDB 10 can do the "merge" - automatically transforming the first query to the second one internally.

MySQL 5.6 cannot do that afaik, but it can materialize the derived table into a temporary one and add automatic indexes - manual.

And you could emulate that even in older versions. If you always get some code for a  then you can do

create temporary table t_indexed as <subquery>;
alter tably t_indexed add key(id);

SELECT * FROM table1 t1
INNER JOIN t_indexed t2
ON t1.id = t2.id;

If you always know the structure of the  results, you can create the temp table with this structure and proper indexes in place and use insert ... select instead.


How to add/update a column with an incremented value and reset said value based on another column in SQL I am stumped, I have searched and researched until my browser tabs no longer show the close button ("X") until you hover over them.
I tried rewriting the search term over and over but I guess I do not know exactly what to ask so I apologise if this has been answered already.

I am trying to figure out how I can add an INT column to a table which starts at and increments by 1 (let's call this field version) based on another columns value (which we shall call case_id) IF that value is the same as the previously iterated row - in the case it is NOT the same, then the version value should reset to 1.

I have tried to figure it out by either updating a temp table I create, or adding the value during the SELECT INTO statement.

The purpose of this table is to store a record of the historic updates to a case, identified by the case_id column, with this version value in the same order as the modify timestamp.

Consider this table structure as an example of the source data:

---------------------------------
| case_id | modify_time         |
---------------------------------
| 1       | 2015-05-01 11:12:13 |
| 1       | 2015-05-02 12:13:14 |
| 1       | 2015-05-03 13:14:15 |
| 2       | 2015-05-01 11:12:13 |
| 2       | 2015-05-01 12:13:14 |
| 3       | 2015-05-02 11:12:13 |
---------------------------------

Consider this as an example of exactly what I want to achieve as the end result:

-------------------------------------------
| case_id | modify_time         | version |
-------------------------------------------
| 1       | 2015-05-01 11:12:13 | 1       |
| 1       | 2015-05-02 12:13:14 | 2       |
| 1       | 2015-05-03 13:14:15 | 3       |
| 2       | 2015-05-01 11:12:13 | 1       |
| 2       | 2015-05-01 12:13:14 | 2       |
| 3       | 2015-05-02 11:12:13 | 1       |
-------------------------------------------

As you can see, I need to add the version (INT) column and insert an incremented value based on the modify_time (ascending order) which will reset when the case_id value changes.

I have tried a few things I have stumbled on during my searches, but nothing I have come across has been able to help me figure this out.

I guess this is just a little too advanced for me.. :-(
This is on SQL Server 2012 and I am not afraid to try anything no matter how advanced as I can only learn from this exercise.

Thank you for taking the time to view my question and thank you in advance if you can help me out here.
Cheers.

You can maintain this column easily:

;WITH x AS
(
  SELECT case_id, modify_time, version,
    rn = ROW_NUMBER() OVER (PARTITION BY case_id ORDER BY modify_date)
  FROM dbo.source_table
)
UPDATE x SET version = rn WHERE version <> rn;

But since you could also generate that version column on the fly every time you need to query (maybe it could be in a view), I don't see any benefit of storing the output or even having a version column in the table, since you are potentially going to be updating it more often than you read it (you will have to update entire ranges or even the entire table every time any rows are changed or added).

If you are moving data from one system to another, or one table to another, you still don't need this column to exist in the source table, and you don't need a temporary table to store this column either:

INSERT dbo.target_table(case_id, modify_time, version)
SELECT case_id, modify_time, version = ROW_NUMBER() OVER
  (PARTITION BY case_id ORDER BY modify_date)
FROM dbo.source_table;


Join is causing other column data to change? I'll lead off with saying that I am relatively new to databases and SQL and I am trying to make an addition to a View in SQL Server that a previous co-worker created.

I am trying to join the Customer_Snacks table so that I can get the Snack Quantity for each customer's order. The issue I'm having is when I do a LEFT OUTER JOIN which, as far as I can tell, should work, some of the other columns data is drastically changed.

I managed to get it to work the way I would like it to by using a subquery but it is DREADFULLY slow. The query takes over a minute to run and in return, the application that is making the call for that view times out.

Here is what I currently have that works (I know, it's messy):

SELECT
    cl.CustomerID,
    cl.FirstName + ' ' + c.LastName as CustomerDisplay,
    cl.InvoiceID,
    cl.MealPlanID,
    mp.DeliveryDate1 as DeliveryDate,
    SUM(CASE WHEN cll.Name = 'Meal' THEN cll.Quantity ELSE 0 END) as RegularQuantity,
    SUM(CASE WHEN cll.Name = 'Meal' THEN cll.Quantity * Amount ELSE 0 END) as RegularTotal,
    SUM(CASE WHEN cll.Name = 'EXTRAPROTEINMEAL' THEN cll.Quantity ELSE 0 END) as ProteinQuantity,
    SUM(CASE WHEN cll.Name = 'EXTRAPROTEINMEAL' THEN cll.Quantity * Amount ELSE 0 END) as ProteinTotal,
    SUM(CASE WHEN cll.Name = 'TAX' THEN Amount ELSE 0 END) as TotalTax,
    SUM(CASE WHEN cll.Name = 'SNACKS' THEN Amount ELSE 0 END) AS SnackTotal,
    -- Here is my subquery that I would like to integrate into this view with a join so that it's much faster
    (SELECT SUM(cs.Quantity) FROM Fustomer_Snacks as cs WHERE cs.CustomerID = cl.CustomerID and cs.DeliveryDate = mp.DeliveryDate1) as SnackQuantity,
    ------------------------------------------------------------------
    CAST(SUM((CASE WHEN cll.Name = 'home' or cll.Name = 'work' THEN 1 ELSE 0 END)) as bit) as IsDelivery,
    SUM(CASE WHEN (cll.Name = 'home' or cll.Name = 'work' or cll.Name = 'pickup') and cll.Quantity > 0 THEN cll.Quantity * Amount else 0 END) as DeliveryCharge,
    cl.Balance as PrePromoTotal,
    ISNULL(cl.Balance * (pc.Discount * .01), 0) * -1 as PromoDiscount,
    cl.FinalBalance - ISNULL((cl.FinalBalance * (pc.Discount * .01)),0) Total
FROM
    Customer_Ledger as cl
INNER JOIN
    Customer_Ledger_LineItems as cll
        on cl.ID = cll.LedgerID
INNER JOIN
    MealPlan as mp
        on cl.MealPlanID = mp.ID
INNER JOIN
    Customer as c
        on cl.CustomerId = c.Id
LEFT OUTER JOIN
    PromoCode as pc
        on cl.BalancePromoId = pc.Id
GROUP BY
    cl.CustomerID,
    c.FirstName,
    c.LastName,
    MealPlanID,
    InvoiceID,
    mp.DeliveryDate1,
    cl.Balance,
    cl.FinalBalance,
    pc.Discount

Like I said, the way I currently have it, it works but it's way too slow. As soon as I try a LEFT OUTER JOIN it's a lot faster but other columns like RegularQuantity become inflated with other numbers (and I'm not sure how that's even happening).

If anyone can guide me in the right direction, that would be great!

The odds are that your problem is that, when you add in the snacks, you have more than one table that returns more than one row for the columns in your GROUP BY statement - probably Customer_Snacks and Customer_Ledger_LineItems.

When the DB engine performs a join, it matches however many rows in the first table with value ABC with however many rows in the second table have the same value, ABC (outer joins working a little differently, but let's ignore that for the moment).

I'll guess that for a given Customer_Ledger row, you tie to one Customer row, one MealPlan row, one (or zero) PromoCode row, and multipleCustomer_Ledger_LineItems` rows.

I'm also guessing for one Customer_Ledger row and its associated MealPlan row, you can bring back multiple Customer_Snacks rows.

So, when we put all the rows together, let's say we have:

1 Customer row
1 Customer_Ledger row
1 MealPlan row
1 PromoCode row
3 Customer_Ledger_LineItems rows

Match all those together, you get 1 * 1 * 1 * 1 * 3 = 3 rows.

Now, add in 2 Customer_Snacks rows. those are matched against all the rows we already have, so we get 1 * 1 * 1 * 1 * 3 * 2 = 6 rows.

When we go to take our sums, we'll get double the amount for sums from Customer_Ledger_LineItems rows, because each row now shows up twice. And, we'll get triple the amount for the sum from Customer_Snacks, because those rows each now show up three times. Basically, you're getting a CROSS JOIN between the relevant Customer_Ledger_LineItems rows, and the Customer_Snacks rows.

There are two ways to avoid this. You already employing the first option - use a subquery to get the sums from one of the tables returning multiple rows to our grouped values.

The other option is very similar: build a temporary table (or a CTE) with all relevant values that you need, so that you'll just have one row with the sums you need when you go to join the data in. In your case, this would look like:

;WITH SnackSums AS
      (SELECT cs.CustomerID
             ,cs.DeliveryDate
             ,SUM(cs.Quantity) as SnackQuantity
         FROM Customer_Snacks cs
        GROUP BY cs.CustomerID, cs.DeliveryDate
      )

and would go right before the SELECT for your query. Then, you just put ss.SnackQuantity where the subquery was, and add:

LEFT OUTER JOIN
  SnackSums ss
    ON (cl.CustomerId = ss.CustomerId AND ss.DeliveryDate = mp.DeliveryDate1)

right before your GROUP BY. Finally, you'll also need to add ss.SnackQuantity to the GROUP BY, since in the context of your main query this is a stand-alone, non-aggregated value.

Now, I make no promises that this will be faster than the subquery. However, it should be accurate - and it doesn't matter is something is blazing fast, if it isn't accurate.


mysqldump between servers with different timezones (tz-utc) If I am moving databases between servers datetime/timestamp fields with different timezones; I have been doing:
mysqldump --tz-utc=false database_name;

then reloading it on the server with different timezone. This appears to work and the dates are correct.
It seems that according to sql manual I shouldn't have to do this:
From manual:

--tz-utc
This option enables TIMESTAMP columns to be dumped and reloaded between servers in different time zones. mysqldump sets its connection time zone to UTC and adds SET TIME_ZONE='+00:00' to the dump file. Without this option, TIMESTAMP columns are dumped and reloaded in the time zones local to the source and destination servers, which can cause the values to change if the servers are in different time zones. --tz-utc also protects against changes due to daylight saving time. --tz-utc is enabled by default. To disable it, use --skip-tz-utc.

I guess I don't understand exactly how this works.
What is the proper way to move data between servers that have different timezones?
EDIT to show that --tz-utc=false is a real statement
cmuench@cmuench:~$ mysqldump --tz-utc=false pos > 1.sql;
cmuench@cmuench:~$ mysqldump --tz-utc=true pos > 2.sql;
cmuench@cmuench:~$ mysqldump --tz-utc pos > 3.sql;
cmuench@cmuench:~$ diff 1.sql 2.sql

10a11,12
> /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
> /*!40103 SET TIME_ZONE='+00:00' */;
2903a2906
> /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2913c2916
< -- Dump completed on 2017-07-12  0:29:54
---
> -- Dump completed on 2017-07-12  0:29:57

cmuench@cmuench:~$ diff 2.sql 3.sql
2916c2916
< -- Dump completed on 2017-07-12  0:29:57
---
> -- Dump completed on 2017-07-12  0:30:01

That sounds like the "proper" way.

A TIMESTAMP column contains the time in UTC, but converts when storing and fetching, so that you can only see the value converted to your local time.

A DATETIME column, on the other hand, is like taking a picture of a clock.  There is no TZ conversion during store/fetch.

Since mysqldump is essentially a bunch of SELECTs, and the reload is a bunch of INSERTs, the data in the file looks like a picture of a clock.  By using UTC during the fetch, the file will contain UTC time.  By having that SET, the INSERT will, again, do no conversion to mess up the value.


Configuration file missing after successful SSRS migration Getting the backup of all SSRS databases including compressing and encrypting can be straight forward.

However, when planning a complete solution for SQL Server Reporting services backup, following the link below can be more demanding.

Backup and Restore Operations for Reporting Services

Among other things, including the encryption keys, master keys, Backing Up the Configuration Files

Reporting Services uses configuration files to store application settings.
You should back up the files when you first configure the server and after you deploy any custom extensions.

Files to back up include:

Rsreportserver.config
Rssrvpolicy.config
Rsmgrpolicy.config
Reportingservicesservice.exe.config
Web.config for the Report Server ASP.NET application
Machine.config for ASP.NET

QUESTION:

I have just installed SQL Server Reporting Services, migrated all databases and reports, including all folder permissions,  from a previous server (sql server 2008),and all is working fine.

However, on the backup strategy, I cannot find some of the .config files that are required to backup.
The file that is missing is Rsmgrpolicy.config

Why is this file missing?

cls
# PowerShell script to list the DLL files under the system32 folder
$Dir = get-childitem "C:\Program Files\" -recurse
# $Dir |get-member
$List = $Dir | where {$_.extension -eq ".config" -and $_.name -like "*rs*"}
$List | format-table Directory, name

Everything is working fine, including the encryption and all the security,
so as the file is not there, I will not need to include it on my backups.

this link below shows where all the configuration files should be:

Reporting Services Configuration Files

  Install Paths   C:\Program Files\Microsoft SQL
  Server\MSRSxx.MSSQLSERVER  (where xx is the MS SQL version number)
  or   C:\Program Files\Microsoft SQL Server Reporting Services\SSRS
  depending on the SSRS version

There it also explains the role of the RSMgrPolicy.config file.

  Stores the code access security policies for the web portal. For more
  information about this file,
  see
  Using Reporting Services Security Policy Files.

Looking at this second link, that explains the Security policy, I understood that the file RSMgrPolicy.config was not needed in my setup. It was not clear in the first documentation but all is good now.


What is the y-axis of this Oracle ASH Viewer? I am investigating different monitoring software for my database, and I stumbled upon this open source ASH Viewer. I noticed in the main graph on the y-axis for resource usage, it's labeled as "Active Sessions" with an axis of varying integers

Link: https://github.com/akardapolov/ASH-Viewer#how-it-works

I don't understand what "Active Sessions" is supposed to mean. Is this the CPU % utilization of the database?

Since the link you included says the data is taken from V$ACTIVE_SESSION_HISTORY, presumably these are the wait event classes. In other words, the diagram shows how many sessions are waiting on each event class or actively executing ("CPU used") at any given time.


How to convert timestamp / rowversion to a date and time in SQL Server? How to convert timestamp / rowversion to a date and time in SQL Server?

I have several tables where I would like to track the last update, with column names like ModifiedAt, UpdatedAt, UpdatedDate.

Would this be possible if these were with a Rowversion data type or Timestamp data type?

as in

CREATE TABLE dbo.Customer (
    CustomerID BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    CustomerName NVARCHAR(200) NOT NULL ,
    CreatedAt DATETIME2 NOT NULL CONSTRAINT df_Customer_CreatedAt DEFAULT GETUTCDATE(),
    UpdatedAt ROWVERSION NOT NULL /* does this work ? */
)

It is not possible to convert the TimeStamp colunm into a Date & Time format. Microsoft has renamed the TimeStamp data type to RowVersion to avoid the confusion.

The timestamp/rowversion data type is really just a Database Transaction ID, and it can also be stored in a column of the Binary(8) data type.

But here is a work-around to implement the ModifiedAt, UpdatedDate column type.
This will translate (or "convert") a RowVersion column into a SmallDateTime datatype, that can be formatted according to your needs.

But you need to do a bit of work;

1. Create a table "UpdateTimeStamp" with three columns

(CreatedDate Smalldatetime, NewRowVersion, OldRowVersion) like this:

Create Table dbo.UpdateTimeStamp(
    OldRowVersion binary(8) not null,
    CreatedDate SmallDateTime not null constraint DF_UpdateTimeStamp_CreatedDate Default getdate(),
    NewRowVersion ROWVERSION not null CONSTRAINT PKUpdateTimeStamp PRIMARY KEY CLUSTERED
)

Insert the first row manually

Insert into dbo.UpdateTimeStamp(OldRowVersion, CreatedDate)
VALUES (0x0000000000000000, '2000-01-01')

2. Create a SQL Agent job

Create a standard SQL Agent job which inserts one row in the table every one minute.

Make step 1 run this code:

Insert into dbo.UpdateTimeStamp(OldRowVersion)
SELECT TOP (1) NewRowVersion
FROM dbo.UpdateTimeStamp
ORDER BY NewRowVersion DESC

Set the schedule to run every 1 minute.

3. Query the data

You need to write your query with a join to the UpdateTimeStamp table to your table with a between join clause like this:

SELECT top 10000 mt.*, uts.CreatedDate AS ModifiedDate
FROM dim.MyTable MT
LEFT JOIN dbo.UpdateTimeStamp uts
    ON MT.DT1RowVersion > OldRowVersion AND MT.DT1RowVersion <= NewRowVersion
ORDER BY uts.CreatedDate

Caveat, buts...

1)
Microsoft SQL Server is not very good at "date range join" clauses, so if you do not need minute resolution, but 10 minute accuracy is acceptable, then you can improve the query speed a bit with lowering the frequency of the SQL Agent job to 10 minute intervals.

2)
This will only work for time periods where the SQL Agent job has been running, as it is not easy to create the dbo.UpdateTimeStamp table retrospectively.
Unless you have an old table with a RowVersion/TimeStamp datatype, and this table also never sees updates, and the table has a CreatedDate column. And the table has to be in the same database on the same server.

Bonus features

The RowVersion column was already added to many tables already in our Data Warehouse, because I use it as a watermark for loading data into our Data Marts.
So now I have the this feature as a debug option, when I need to investigate when things went wrong in our facts and dimensions.
Furthermore I do not need a trigger, which may have performance issues and/or stability issues, if not coded correctly.


Use a SP to Archive/Revision related records in several related tables? I have a database for an application that has a complicated relational table structure. Is there a good way to create revisions of the data, or archive the data before a user makes changes?

Ideally I would want a stored procedure that would save the state of all the related data when ran, insomuch that when a field is added to any of the related tables, the stored procedure would not miss it.

Creating such a stored procedure is probably going to be complex and error prone. That's why there are temporal databases or temporal features in a database. If you really wanted to do this with a stored procedure, you can have a duplicate schema with a version number column added to all tables.

That way, on each write operation, you can first call the stored procedure which does an INSERT INTO.... SELECT using the same WHERE clause as your write operation. The stored procedure is best initiated by the app which runs the stored procedure before the actual write (UPDATE/DELETE) statement.

If you use triggers, it can be very tricky just figuring out which table(s) to host the trigger and making sure you don't get a trigger storm.

Far easier path is to implement something like Temporal tables in SQL Server 2016: https://msdn.microsoft.com/en-us/library/dn935015.aspx. Takes a bit of work to setup but it's only somewhat tedious work, not very complex work. If you want to keep this history around for a long time, you can even stretch the temporal history table so you don't have to deal with administration and storage challenges with a massive history table.


You have actually asked two questions:

"If a query is written in MySQL SP/Triggers/Functions/Events is slow, it will not be logged into slow log file"

Answer: it will not be logged in standard MySQL server. Do take a look at the Percona Server extension for the slow log: in particular look at the log_slow_sp_statements configuration variable. It does what you want: the slow queries within routines are logged. Note that this does not apply to triggers.
"Do we need to optimize each SP/Triggers/Functions/Events individually?"

Answer: well, of course. Each and every query needs to be as optimized as you can, depending on your requirements. A routine is not a "magic solution" that causes inner queries to run faster...


Is there some kind of table-level replication in PostgreSQL? I work with two databases in PostgreSQL 9.1, say A and B, one per application. Database B needs to read (and not write into) one table in database A (say users), but at the same time some tables from database B need to reference some of their fields as foreign keys on table users.

So far I've tried:

Finding some kind of native table-level replication, so any change on users is automatically replicated to the clone table in database B. Didn't find anything.
Creating a TRIGGER on table users so after any change a function is called, but that doesn't solve anything since I can't insert values from database A into database B's tables.
Using FOREIGN TABLEs. Neither solves it, since no foreign keys may be applied to foreign tables.
Use a SCHEMA instead of DATABASE for A, but application enforces it to be a database.
I've even thought about a CRON each minute which would dump and import the table from A to B, but that would break any foreign key relationship with other tables.

I can't combine the two databases in one (although technically both have uniquely named tables), because when upgrading one of them it checks the database and should anything weird be in it, it would fail.

Do I have any other chances here? Any hint is very appreciated.

I finally managed to find a solution that works so far. I installed plpython procedural language module along with the psycopg2 module, wrote a FUNCTION and created two TRIGGERs, one for INSERT and one for DELETE (I won't have any UPDATE events).

The function code would be something like this:

CREATE OR REPLACE FUNCTION synchronize()
RETURNS trigger AS
$$
    import psycopg2

    conn = psycopg2.connect("host=... dbname=... user=... password=...")
    cur = conn.cursor()

    # If inserting...
    if TD['event'] == 'INSERT':
      cur.execute(...)

    # If deleting...
    if TD['event'] == 'DELETE':
      cur.execute(...)

    conn.commit()
    cur.close()
    conn.close()
$$
LANGUAGE 'plpython2u' VOLATILE;

Then, I defined both triggers:

CREATE TRIGGER replica_insert
AFTER INSERT ON users
FOR EACH ROW
EXECUTE PROCEDURE synchronize();

CREATE TRIGGER replica_delete
BEFORE DELETE ON users
FOR EACH ROW
EXECUTE PROCEDURE synchronize();

Maybe over-complicated, or patchy, but right now this is the easiest way I found how to do it. If someone improves it or has an alternative (preferably native) way to do it, I'll be glad to change the accepted answer.


separating tlog and db file when using fusionio We have a FusionIO card that we use in our production database. We have both the transaction log as well as the database files on that same drive. I know the recommendation is to place the tlog files on a separate physical drive, but given that the I/O on the FusionIO card is significantly better will moving the tlogs to a disk drive create a bottleneck?

I understand that  the tlogs are written to sequentially (the reason it is recommended moving them). But if IO difference is significant between the two drives as it is between traditional drives and a FusionIO card, will I still see a benefit from moving the tlogs to a different drive? Or will performance be worse?

The reason I'm asking is because we're running out of space to have both on the same drive and it would be nice if we could use a cheaper drive array for the tlogs instead of having to buy a separate FusionIO card for the tlogs.

BACKGROUND:

The database is a typical web-based workload with much more reading going on than writing - with the exception that when writes happen there is a lot of writing going on. For example, users can upload multiple files with 10,000 or more records in each of them.

Let's go through some of the points.  You mentioned:

  I understand that the tlogs are written to sequentially (the reason it
  is recommended moving them).

It's also for Recoverability

Moving transaction files to a different array isn't just for performance, it's also for recover-ability.  Assume you take a transaction log file backup at 3:45pm and your Fudion IO disk goes bad at 3:55pm before your next transaction log backup.  If you have your data and log files on the same drive you are not able to go in after the crash, take a final transaction log backup thus ensuring almost no data loss (in flight transactions would still be lost of course).

Performance

  I understand that the tlogs are written to sequentially (the reason it
  is recommended moving them). But if IO difference is significant
  between the two drives as it is between traditional drives and a
  FusionIO card, will I still see a benefit from moving the tlogs to a
  different drive? Or will performance be worse?

Without proper benchmarking, you could very well lose performance by isolating your log files from a lightning fast PCI-E FusionIO card and moving them to a SAS6 RAID array with magnetic disks.  Typically moving log files to a different array also increased performance, but in your case it might decrease it.

You should measure the following perfmon counters after moving the log files over:

DISK SEC/WRITE

DISK SEC/READ

If DISK SEC/WRITE on the log file goes over 5MS it's over what MS recommends.  However, if your app doesn't have performance issues with writes, just keep an eye on that counter so you're not caught off guard.

Also,
Transaction Logs are sequential in nature but the second you RAID them or put more than 1 accessed file on the drive the physical IO is no longer sequential thus you lose performance on traditional disks.  All the FusionIO drives I benchmarked did perform better for sequential reads/writes but random IO was sufficient.

Next you mention:

  The database is a typical web-based workload with much more reading
  going on than writing

Typically most apps are much more read intensive than write intensive, which is why common SQL Server benchmarking tools use 8K IOs @ roughly 70% read / 30% write so that part of your setup is fairly common.

  For example, users can upload multiple files with 10,000 or more
  records in each of them.
  If your writes aren't slowed down by moving to the new disk then great, but if you're having scaling issues you should look into BULK LOAD.  This will minimize the log activity.

Real world benchmarking in a test system is the only way I'd go at it personally.  I'd reload a test system with a production workload and see how the drives handle by recording the metrics in SQL Server wait stats and perfmon, then make a decision.  Good luck.


SQL Server Query Store - Force Plan does not work Here is my situation: I have a simple SP (containing giant joins and a few temp tables), which essentially returns a resultset, works in 15 seconds in 2008 compatibility level, but takes 3 hours when compatibility level changes to 2014. When I revert back to 2008, everything is dandy, then slow when I switch to 2014.

I want to know how I can force the plan (or how to in general force the SP) to run in the same way it did in 2008 CL (whether that's accomplished using Execution Plan forcing or not, doesn't matter to me).

Here is what I tried: I tried to FORCE execution plan using Query Store but that does not work. Because the SP generates a new QUERY ID every single time.. i don't understand how this tool is supposed to work, because no matter how much you force it, it'll be a new QUERY ID and therefore plan the next time. Here is what I did:

I can also confirm that this does NOT apply to me: Query Store Force Plan feature doesn't work

Query store uses the query hash to identify and group queries together. If you're seeing a new plan ID every time this proc executes, it could be because you have a different SQL hash each time maybe because of dynamic SQL. Without seeing the code it is difficult to determine why.

You should check your cache for recent executions of this procedure and verify if they have different query hash values as this will be impacting Query Store's ability to track that query's executions.

To force the old Cardinality Estimator, you can add OPTION(QUERYTRACEON 9481) to your queries to force the use of the old pre-2014 cardinality estimator. In SQL 2016 SP1 and higher, you can also use OPTION(USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION')) which does the same thing but doesn't require permissions to turn on trace flags.

This should force the use of the old CE and return your query to more predictable performance levels, however, you should also look at tuning and refactoring your stored procedure to identify the cause of the poor performance with the 2014+ CE and resolve it if possible.


Generate script to automate renaming of default constraints Background: Some of our default column constraints were generated without explicit names, so we get fun names that vary from server to server like: DF__User__TimeZoneIn__5C4D869D

I would prefer to have them all manageable with a consistent naming like DF_Users_TimeZoneInfo so that we can ensure that the appropriate constraints exist on future target tables (like in RedGate compare, or even just visually)

I've got a script that mostly works for what I want:

select 'sp_rename N''[' + s.name + '].[' + d.name + ']'',
   N''[DF_' + t.name + '_' + c.name + ']'', ''OBJECT'';'
from sys.tables t
    join
    sys.default_constraints d
        on d.parent_object_id = t.object_id
    join
    sys.columns c
        on c.object_id = t.object_id
        and c.column_id = d.parent_column_id
    join sys.schemas s
        on t.schema_id = s.schema_id
WHERE d.NAME like 'DF[_][_]%'

But this just gives me a resultset, and not something I can actually pass into an exec or whatever.

How can I make this so I can just execute those sp_rename scripts without having to resort to copying out all the returned elements and pasting them into a new query window and running them again? Trying to save as many keystrokes as possible so I can correct this in many environments.

Ok, couple of things.

always use EXEC when executing stored procedures; the shorthand without EXEC only works when it is the first statement in the batch (and that will not be the case here).
always use semi-colon terminators - in this case they are useful in lieu of pretty carriage returns and indentation, but they are always wise to have.
always use QUOTENAME() instead of manually applying square brackets yourself. In this case you're probably safe, but there are cases where the manual approach will break.
you can test the PRINT output but it won't necessarily be complete if your total command is > 8k (see this tip for some alternative approaches).

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

SELECT @sql += N'EXEC sys.sp_rename N'''
    + QUOTENAME(s.name) + '.' + QUOTENAME(d.name)
    + ''', N''DF_' + t.name + '_' + c.name + ''', ''OBJECT'';'
  FROM sys.tables AS t
  INNER JOIN sys.default_constraints AS d
     ON d.parent_object_id = t.object_id
  INNER JOIN sys.columns AS c
     ON c.object_id = t.object_id
    AND c.column_id = d.parent_column_id
  INNER JOIN sys.schemas AS s
     ON t.schema_id = s.schema_id
  WHERE d.NAME LIKE N'DF[_][_]%';

PRINT @sql;
-- EXEC sys.sp_executesql @sql;


ibdata1 corrupted, recover from ib_logfile? A failing hard drive have left me with a corrupted ibdata1 file. I know where the file starts but not where it ends. The hard drive recovery software claims the file is larger than the hard drive itself, leaving me to the conclusion that I can't trust the integrity of the file at all.

The .frm and ib_logfile0 and ib_logfile1 files are safe and sound, though.

Is it possible to recreate the table data from the ib_logfile[0,1] files, or should I cut my losses?

ib_logfile-s store only recent changes, so you won't recover all data from them.

It's possible however to fetch records directly from disk image, you don't need to restore ibdata1. Actually you even don't need a filesystem.

Check out data recovery toolkit from http://www.percona.com/software/mysql-innodb-data-recovery-tools .

You need something like

./page_parser -f disk.img

And then you can fetch records from InnoDB indexes.

UPDATE: Data recovery toolkit moved to GitHub