is there a GUI client that can execute multiple queries and show its multiple results? I'm currently using pgadmin and it appears that it is not possible to send more than one query and retrieve multiple data sets back.

For example, if I try to do this:

select travelerId, name from travelers;

select dateCreated from carDealerships;

I would expect to see TWO sets in my data output window.  As you can see, these two queries have nothing to do with each other so I'm not trying to do anything fancy by using UNION or anything like that.

I really do want to see more than one result set in the data output.  In pgadmin, in the message pane, it shows that the first query result with XX rows from table "travlers" are discarded.  Then it says that the next set of XX rows from table "carDealerships" are retrieved.

Why does pgadmin discard the first query set?

More importantly, can anyone recommend a GUI client that can output multiple data sets based on the number of queries executed?  Doesn't matter if it's open source or proprietary.

Looks like Aqua Data Studio could be what I'm after.  To get multiple query results you have to separate your queries with a slash.  It's not automatic but it's a step in the right direction for me.

e.g.

select travelerId, name from travelers;
/
select dateCreated from carDealerships;
/


Aggregating Attributes I have a product review table in an oracle 12.1 EE database that has different attributes rated on a scale of 1 through 5. How do I query this table to count up all of the reviews and group by the product and the 1-5 rating.

Example Data

WITH DATA_SET
AS
    (   SELECT  1   AS REVIEW_ID,
                'a' AS PRODUCT_ID,
                5   AS OVERALL,
                4   AS COMFORT,
                5   AS FIT,
                4   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  2   AS REVIEW_ID,
                'a' AS PRODUCT_ID,
                4   AS OVERALL,
                4   AS COMFORT,
                4   AS FIT,
                4   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  3   AS REVIEW_ID,
                'b' AS PRODUCT_ID,
                4   AS OVERALL,
                5   AS COMFORT,
                4   AS FIT,
                5   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  4   AS REVIEW_ID,
                'c' AS PRODUCT_ID,
                3   AS OVERALL,
                2   AS COMFORT,
                2   AS FIT,
                4   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  5   AS REVIEW_ID,
                'c' AS PRODUCT_ID,
                2   AS OVERALL,
                1   AS COMFORT,
                2   AS FIT,
                1   AS APPEARANCE
        FROM    DUAL
    )
SELECT  *
FROM    DATA_SET;

review_id | product_id | overall | comfort | fit   | appearance
----------------------------------------------------------------
1            a            5         4         5       4
2            a            4         4         4       4
3            b            4         5         4       5
4            c            3         2         2       4
5            c            2         1         2       1

I've looked at doing pivots and various analytic queries. But I can't quite get the desired output below. I'm sure I'm making this too hard. But for the life of me I can't put this together. Any help/direction would be appreciated!

Example Output

product_id | rating | overall | comfort | fit   | appearance
--------------------------------------------------------------
a            1         0        0         0         0
a            2         0        0         0         0
a            3         0        0         0         0
a            4         1        2         1         2
a            5         1        0         1         0
b            1         0        0         0         0
b            2         0        0         0         0
b            3         0        0         0         0
b            4         1        0         1         1
b            5         0        1         0         0
c            1         0        1         0         1
c            2         1        1         2         0
c            3         1        0         0         0
c            4         0        0         0         1
c            5         0        0         0         0

HOWTO

Your query needs to do the following:

UNPIVOT the data
PIVOT the data to get the COUNT()
add missing rows

sub-query of all row LEFT OUTER JOIN calculated data
use of then not-well-known MODEL clause

and, of course, the ORDER BY clause

EXAMPLE 1 (sub-query)

WITH DATA_SET
AS
    (   SELECT  1   AS REVIEW_ID,
                'a' AS PRODUCT_ID,
                5   AS OVERALL,
                4   AS COMFORT,
                5   AS FIT,
                4   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  2   AS REVIEW_ID,
                'a' AS PRODUCT_ID,
                4   AS OVERALL,
                4   AS COMFORT,
                4   AS FIT,
                4   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  3   AS REVIEW_ID,
                'b' AS PRODUCT_ID,
                4   AS OVERALL,
                5   AS COMFORT,
                4   AS FIT,
                5   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  4   AS REVIEW_ID,
                'c' AS PRODUCT_ID,
                3   AS OVERALL,
                2   AS COMFORT,
                2   AS FIT,
                4   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  5   AS REVIEW_ID,
                'c' AS PRODUCT_ID,
                2   AS OVERALL,
                1   AS COMFORT,
                2   AS FIT,
                1   AS APPEARANCE
        FROM    DUAL
    )
,unpivot_data as (
  SELECT  product_id, score, catagory
  FROM    DATA_SET
    UNPIVOT ( score
              for catagory in (
                overall, comfort, fit, appearance
              )
            )
  )
,count_values as (
  select product_id, score, overall, comfort, fit, appearance
  from unpivot_data
  pivot (
    count(catagory)
    for catagory in ( 'OVERALL' AS overall, 'COMFORT' as comfort, 'FIT' as fit, 'APPEARANCE' as appearance)
  )
)
,all_rows as (
  select *
  from (select distinct product_id from data_set),
       (select level as score from dual connect by level <= 5)
)
select a.product_id, a.product_id
  ,nvl( b.overall, 0) overall
  ,nvl( b.comfort, 0) comfort
  ,nvl( b.fit, 0) fit
  ,nvl( b.appearance, 0) appearance
from all_rows a
  left outer join count_values b
    on (a.product_id=b.product_id and a.score=b.score)
order by a.product_id, a.score

EXAMPLE 2 (model)

WITH DATA_SET
AS
    (   SELECT  1   AS REVIEW_ID,
                'a' AS PRODUCT_ID,
                5   AS OVERALL,
                4   AS COMFORT,
                5   AS FIT,
                4   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  2   AS REVIEW_ID,
                'a' AS PRODUCT_ID,
                4   AS OVERALL,
                4   AS COMFORT,
                4   AS FIT,
                4   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  3   AS REVIEW_ID,
                'b' AS PRODUCT_ID,
                4   AS OVERALL,
                5   AS COMFORT,
                4   AS FIT,
                5   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  4   AS REVIEW_ID,
                'c' AS PRODUCT_ID,
                3   AS OVERALL,
                2   AS COMFORT,
                2   AS FIT,
                4   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  5   AS REVIEW_ID,
                'c' AS PRODUCT_ID,
                2   AS OVERALL,
                1   AS COMFORT,
                2   AS FIT,
                1   AS APPEARANCE
        FROM    DUAL
    )
,unpivot_data as (
  SELECT  product_id, score, catagory
  FROM    DATA_SET
    UNPIVOT ( score
              for catagory in (
                overall, comfort, fit, appearance
              )
            )
  )
,count_values as (
  select product_id, score, overall, comfort, fit, appearance
  from unpivot_data
  pivot (
    count(catagory)
    for catagory in ( 'OVERALL' AS overall, 'COMFORT' as comfort, 'FIT' as fit, 'APPEARANCE' as appearance)
  )
)
select *
from count_values
model return all rows
  DIMENSION by (product_id,score)
  measures ( overall, comfort, fit, appearance )
  rules (
    overall[ for product_id in ( select distinct product_id from data_set), for score in (1,2,3,4,5)] =
      nvl( overall[ cv(product_id), cv(score) ], 0),
    comfort[ for product_id in (select distinct product_id from data_set), for score in (1,2,3,4,5)] =
      nvl( comfort[ cv(product_id), cv(score) ], 0),
    fit[ for product_id in (select distinct product_id from data_set), for score in (1,2,3,4,5)] =
      nvl( fit[ cv(product_id), cv(score) ], 0),
    appearance[ for product_id in (select distinct product_id from data_set), for score in (1,2,3,4,5)] =
      nvl( appearance[ cv(product_id), cv(score) ], 0)
    )
order by product_id, score


pg_dump: Write failed: Connection reset by peer we are trying to move all database data to other location.
we chose to perform pg_dump and we encountered few problems:

first was out of memory problem:
during pg_dump initialization we get an error:

pg_dump: saving search_path =
pg_dump: saving database definition
out of memory

then we gave to VM +16GB of RAM, so 24GB in total, the pg_dump process passed it and now we have another problem:

pg_dump: dumping contents of table lob_messages
Write failed: Connection reset by peer
gumul@IT-dep:~$

Error occurs so far only on this "lob_messages" table, this table is huge - pgAdmin shows 256GB size.
I have tried pg_dump all the database, after that error i have tried only pg_dump on that exact table. Error still occurs.

We are using Postgesql 9,4. Our sys admins dunno the problem.
Here is htop view:

Here is df -h view:

i have tried to make pg_dump file on /mnt mounted NAS storage and also locally in /var/lib/postgresql/9.4/main

here is postgresql.conf changed settings:

error remains. Can you share some wisdom what i'm doing wrong?
During that error (connection reset by peer) i got disconect from the server, but only on that session where pg_dump is run.
On other connection (monitoring htop) remains connected during that error.

This looks like the error is coming from your ssh session, not pg_dump.  Use 'screen' or 'tmux' so you can resume the session after ssh failure.

Perhaps pg_dump is still running, or completed normally, even after it's controlling session went away.


Transaction Log Files Larger Than Data Files I'm an accidental DBA and need help determining a course of action regarding transaction log files on several databases. I ran Brent Ozar's sp_blitz to see what areas of concern there might be on an instance of SQL Server 2012 and one of the things I saw was transaction log files that are bigger than the data file.  On one database the data file is at 3.33 GB and the log is 5.19 GB. The others have a ratio that's about the same.

The instructions for this scenario indicates that the log file might need to be shrunk, but only if the database is in Full recovery mode. This database is set to Simple recovery.

Should I shrink the log files or just leave them alone?

Thanks for your time.

Jeff, Brent here. We put a lot of documentation in that URL - make sure you copy/paste the URL into your web browser to read the full documentation.

If you're in simple recovery model, and you've got log files larger than the database, there are lots of possible causes:

Someone started a transaction, locked their workstation, and went home for the weekend
Someone ran a legitimately large transaction (like a huge update across the entire database, multiple times)
The log is growing due to something other than a backup (to find out what it is, check SELECT * FROM sys.databases, and look at the log_reuse_wait_desc column)

My bigger concern would be the fact that your databases are in simple recovery model. That means you can't do a point-in-time recovery. If something goes wrong, you're restoring all the way back to your last good full backup. Businesses are often uncomfortable with that much data loss.


Adding where clause in LEFT OUTER JOIN giving unexpected result SELECT abc_discussions.id as id,
       abc_discussions.subject as subject,
       abc_todo_lists.title as todo_list_title,
       abc_todos.title as todo_title,
       abc_files.title as file_title,
       count(abc_comments.comment) as no_of_comments,
       MAX(abc_comments.posted) as last_comment_posted
FROM abc_discussions
LEFT OUTER JOIN abc_todo_lists
ON abc_discussions.commenttable_type = 'todo_list' AND abc_discussions.commenttable_id = abc_todo_lists.id
LEFT OUTER JOIN abc_todos
ON abc_discussions.commenttable_type = 'todo_item' AND abc_discussions.commenttable_id = abc_todos.id
LEFT OUTER JOIN abc_files
ON abc_discussions.commenttable_type = 'file' AND abc_discussions.commenttable_id = abc_files.id
LEFT OUTER JOIN abc_comments
ON abc_discussions.id = abc_comments.discussion_id
GROUP BY abc_discussions.id

gives everything correctly, but if I will add  WHERE abc_discussions.project_id = 2 it gives todo_list_titleandfile_titleas null, but if I will addWHERE abc_discussions.project_id = 1` it gives again correct result  what may be the cause ?

abc_discussions table

CREATE TABLE $abc_discussions_table(
            id bigint(20) NOT NULL AUTO_INCREMENT,
            project_id bigint(20) NOT NULL,
            user_id bigint(20) NOT NULL,
            subject VARCHAR (2000) NOT NULL,
            description VARCHAR (20000) NOT NULL,
            posted datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
            commenttable_type VARCHAR (200),
            commenttable_id bigint(20),
            UNIQUE  KEY id(id)

That's because you violated the rule, that every column in the select clause has to be either in the group by clause or have an aggregate function applied to it.

When you don't follow this rule, a random for every group is chosen to display. The group by "collapses" the rows for each group and you have to specify which row to display with min(), max() or whatever aggregate function works for you.

MySQL is to my knowledge the only RDBMS that allows violating this rule, but you can turn this off by setting the sql mode only_full_group_by.

read more about it here


install FirstResponderKit in Azure SQL DB? I've just downloaded the newest version (20190128) of FirstResponderKit from BrentOzar.com, and tried to install it on Azure SQL DB.
I'm getting these errors:

Msg 40515, Level 15, State 1, Procedure sp_AllNightLog, Line 16 [Batch Start Line 13]
Reference to database and/or server name in 'master.sys.procedures' is not supported in this version of SQL Server.
Msg 40515, Level 15, State 1, Procedure sp_AllNightLog_Setup, Line 16 [Batch Start Line 1492]
Reference to database and/or server name in 'master.sys.procedures' is not supported in this version of SQL Server.
Msg 40515, Level 15, State 1, Procedure sp_Blitz, Line 16 [Batch Start Line 2801]
Reference to database and/or server name in 'master.sys.all_objects' is not supported in this version of SQL Server.
Msg 40515, Level 15, State 1, Procedure sp_BlitzLock, Line 16 [Batch Start Line 28063]
Reference to database and/or server name in 'msdb.dbo.sysjobs' is not supported in this version of SQL Server.
Msg 40515, Level 15, State 1, Procedure sp_DatabaseRestore, Line 16 [Batch Start Line 35912]
Reference to database and/or server name in 'master.sys.xp_dirtree' is not supported in this version of SQL Server.

It installs nicely on my SQL Server 2008.

Do FirstResponderKit work on Azure?

Edit:
Most of it runs, and I've started to change sp_blitz, so that it will run as well. please see my github if you want to test it.

Edit2:
I've run it against a new Azure SQL database that I've moved from on-prem. It found two issues for me (caused by Microsoft Data Migration Assistant) about statistics not updated, and many indexes that were disabled. I think that other people will find similar issues, if we could get this version into the main release.
Testers Needed!

From the documentation on GitHub - (highlighting mine)

The First Responder Kit runs on:

SQL Server 2008, 2008R2, 2012, 2014, 2016, 2017 on Windows - yes,
fully supported
SQL Server 2017 on Linux - yes, fully supported except sp_AllNightLog
and sp_DatabaseRestore, which require xp_cmdshell, which Microsoft
doesn't provide on Linux
SQL Server 2000, 2005 - not supported by Microsoft anymore, so we
don't either
Amazon RDS SQL Server - fully supported
Azure SQL DB - It's a dice roll. Microsoft changes DMV contents in here without warning, so no guarantees


Avoid unique violation in atomic transaction Is possible to create atomic transaction in PostgreSQL?

Consider I have table category with these rows:

id|name
--|---------
1 |'tablets'
2 |'phones'

And column name has unique constraint.

If I try:

BEGIN;
update "category" set name = 'phones' where id = 1;
update "category" set name = 'tablets' where id = 2;
COMMIT;

I'm getting:

ERROR:  duplicate key value violates unique constraint "category_name_key"
DETAIL:  Key (name)=(tablets) already exists.

In addition to what @Craig provided (and correcting some of it):

Effective Postgres 9.4, UNIQUE, PRIMARY KEY and EXCLUDE constraints are checked immediately after each row when defined NOT DEFERRABLE. This is different from other kinds of NOT DEFERRABLE constraints (currently only REFERENCES (foreign key)) which are checked after each statement. We worked all of this out under this related question on SO:

Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

It is not enough for a UNIQUE (or PRIMARY KEY or EXCLUDE) constraint to be DEFERRABLE to make your presented code with multiple statements work.

And you can not use ALTER TABLE ... ALTER CONSTRAINT for this purpose. Per documentation:

  ALTER CONSTRAINT

  This form alters the attributes of a constraint that was previously
  created. Currently only foreign key constraints may be altered.

Bold emphasis mine. Use instead:

ALTER TABLE t
   DROP CONSTRAINT category_name_key
 , ADD  CONSTRAINT category_name_key UNIQUE(name) DEFERRABLE;

Drop and add the constraint back in a single statement so there is no time window for anybody to sneak in offending rows. For big tables it would be tempting to conserve the underlying unique index somehow, because it is costly to delete and recreate it. Alas, that does not seem to be possible with standard tools (if you have a solution for that, please let us know!):

Drop primary key without dropping an index

For a single statement making the constraint deferrable is enough:

UPDATE category c
SET    name = c_old.name
FROM   category c_old
WHERE  c.id     IN (1,2)
AND    c_old.id IN (1,2)
AND    c.id <> c_old.id;

A query with CTEs also is a single statement:

WITH x AS (
    UPDATE category SET name = 'phones' WHERE id = 1
    )
UPDATE category SET name = 'tablets' WHERE id = 2;

However, for your code with multiple statements you (additionally) need to actually defer the constraint - or define it as INITIALLY DEFERRED Either is typically more expensive than the above. But it may not be easily feasible to pack everything into one statement.

BEGIN;
SET CONSTRAINTS category_name_key DEFERRED;
UPDATE category SET name = 'phones'  WHERE id = 1;
UPDATE category SET name = 'tablets' WHERE id = 2;
COMMIT;

Be aware of a limitation in connection with FOREIGN KEY constraints, though. Per documentation:

  The referenced columns must be the columns of a non-deferrable unique
  or primary key constraint in the referenced table.

So you cannot have both at the same time.


SQL Azure Performance of new servers I have a fairly small database ~200MB which when running in the web edition of SQL azure is lightning fast. I've switched it to the basic edition and all of a sudden a group of queries which took less than 1 second to execute is now taking ~25 Seconds (running in basic mode)
Switched it to a D1 and the halved the speed to 12.5 seconds.

Running on premise with an average server and the query took ~1 second.

In both tests I was the only one using the database.
I'm shocked that it's running so slowly. I know the new databases work slightly differently but was really shocked at the performance difference.

Does anyone have any good suggestions on how to optimise your database design to improve  performance when running in SQL Azure.

If anyone has any suggestions for me to check I'd love to hear it. I'm figuring that it's the amount of queries which it's executing in one hit.

I'm about to create a view to do all the querying in one hit. Something that I've already run on the database and on the basic edition it's instant.

This is only a little test app so it's easy for me to alter but i'm concerned about our productions apps which may suffer from the same issue.

I am the author of the blog post mentioned above by Andrew.

The new service tiers scale (ie. limit) I/O, CPU and memory by tier.  Higher levels/tiers provide more resources.  There are no documented limits by resource type (only vague DTUs), though my tests provide some indicative I/O numbers.

UPDATE:  I have now tested the memory limits by Service Tier, see:
http://cbailiss.wordpress.com/2014/11/11/azure-sql-database-memory-limits-by-service-tier/

In terms of optimising design, I would first see which resource type I am hitting the limit on.  The sys.resource_stats DMV in the master database has columns that show the %age resource usage (e.g. % CPU) you are using over time in that service tier/level.  If you are able to run a test for say at least half an hour, then check that table and see if one of those columns is max'd at 100% then you have an idea of where you are bound.  Of course, resolving that may or may not be easy/possible, but you have a starting point.  The sys.dm_db_resource_stats is a newer DMV which has data for the last hour at 15 second intervals so would be useful for shorter tests.

If the I/O is the limiting factor, then for a small database I would expect performance to increase dramatically once the data is in the buffer pool.  That was a very clear pattern in the tests I undertook and blogged, e.g. see the dramatic performance increase in this sequential read test on Basic Edition:
http://cbailiss.files.wordpress.com/2014/07/2014-07-04-seqsel-metrics1.png
taken from this post:
http://cbailiss.wordpress.com/2014/07/06/microsoft-azure-sql-database-performance-tests-sequential-selects/

Of course, if/when the buffer pool is cleared, then performance will drop off again.  That should in theory happen less with the new tiers - since the guaranteed performance levels in the new tiers are supposed to prevent noisy neighbours / load balancing database failovers that happen in Web/Business.
However, as far as I know, there are no documented numbers / guarantees about the buffer pool size that is allocated to each database in the new service tiers.

Hope that helps

Chris


How to enable symbolic link in mysql? How can I enable symbolic link support in my MySQL Server. When I run show variables like 'have_symlink'; I got result as disabled.

If you want to enable symbolic link then add following line in mysql server configuration file in the section of [mysqld].
    symbolic-links=TRUE
    After adding restart mysql server then you will get show variables like 'have_symlink'; value as YES.


MongoDB replica set and db.stats() We just added a node to our replica set. After the node's state switched to SECONDARY, I do
use obs
db.stats()

on both the PRIMARY node and the new SECONDARY.
               PRIMARY    SECONDARY
"collections" :  19          18
"views" :        0            0
"objects" :    70926        70409

Why are numbers of objects and collections different?

OK we found why there is a discrepancy.
We were able to list the collections on the secondary node with
db.getMongo().setSlaveOk()
show collections

From there, I realized the extra collection on the primary was system.profile (I'd forgotten I'd turned on the profiler) and it's 517 documents accounted for the different object counts from db.stats()


Does PostgreSQL work well as a workstation-based DBMS? I've been considering PostgreSQL for an application I am writing in .NET WinForms, and the app is intended to be standalone with occasional sync-ing with a web service.  I plan to use Sql Server for the web server (since I already have it available), but would like to use a DBMS with a smaller footprint on the client WinForms app -- thus PostgreSQL.

So is a good option for my intentions?

(this is just too long for a comment)

Postgres works very well on a workstation - it also works very well on the server.

You should be aware that Postgres, being a full blown server DBMS does require some kind of installation - and it requires to start the server process in order to use it. You don't really need a Windows service and you can start and stop the server from within your application (by calling pg_ctl).

For our development team I have prepared a little batch file that gets a Postgres server up and running from the ZIP distribution without any installation hassles. Something like that should be suitable for your needs as well). If your are interested, I can add it to this answer.

Although I really like Postgres (and would chose it over SQL Server any time), I agree with dezso that an embedded engine like SQLite (or even SQL Server CE) might be more suitable, due to smaller installation requirements (and thus less possible problems). But they are less powerful as well.


SQL Server restore from transaction log In SQL Server 2005, if you have a database with no backup - but it is running in full recovery mode - is it possible to "roll back" some deletes?

If yes - how?

No. The recover process always starts from a full database backup and then it applies the log backups.

If your database has no backups then it is not in full recovery mode. Untill you actually take a backup it will always run in simple, no matter the intented recovery mode (aka 'pseudo simple', see New script: is that database REALLY in the FULL recovery mode?)

If your database really is in FULL recovery mode, then a backup was tacken and the recovery can start with that.

There are third party tools that can analyze the log and recover stuff, but they work only if the log was not recycled, so is back to the discussion whether the recovery model is really full or simple.


Monitoring deadlocks in SQL Server 2014 We are experiencing deadlocks on SQL Server 2014 and I am trying to capture as much information about them as I can.

There is already Extended Events Session that captures xml_deadlock_report events and I can see deadlock graph in SSMS.

Currently there are no trace flags enabled on this SQL Server. Will enabling flags 1204 and 1222 help to capture any additional information? It seems these flags don't apply to Extended Events but I am not 100% sure.

If you have the Deadlock Graph - you have quite a lot of information. Reading the deadlock graph takes a bit more skill. But not too much. The graph is showing you what a deadlock is. It's sort of like when two cars are at a point where they each need to cross a road to enter a parking lot across the street from them at the exact same spot. Say they each want to make a left turn. Car A can't take a left because Car B is in the way. Car B can't take a left because Car A is in the way.

SQL Server just blows one of the cars up for you instead of moving one of the cars a bit or driving around the other.

But I digress. The deadlock graph information and the rest of the info in the deadlock report will show you which object each session have locked and what type of lock - and also what lock the other wants.

You may see some query info, may not depending on a few things. But if you can recreate the situation you can capture some of the query info from an extended event session or a third party tool that watches queries coming in - tools like Sentry One, Idera DM or Solar Winds DPA should all show you queries as they happen - and most have a free trial period. Sentry even has their own deadlock graph, I trust the others do also but not sure.


Should database files be on different disk(s) than OS? My very short overview

SQL Server databases are installed as virtual machines on VMWare, there are plugged a few disk areas with the same (or very similair) parameters
versions are SQL Server 2005/2008 R2 Standard installed on Win2003/2008 Datacenter
Databases files are saved on another disk as is WinOS installed

Is this logic about good practicies, or are there other important, significant arguments for or against, why we should do this (or not do this)?

It's typically good practice to seperate your OS installation from your database installation to isolate OS disk issues from database disk issues.

The primary reason for this is to reduce the change that a problem with the OS could prevent recovery of the database, or vice versa.  If your database fills up the free disk space, it can crash both the database and the OS.

This also allows you to have different recovery options available for the different pieces.  We typically run RAID 1 (Mirroring) on our OS disks, and RAID 5 (or some other type of parity + striping) on our Database disks.  Since the OS disk has fewer writes, we can take the performance hit of mirroring, to reduce recovery time in a crash situation.  But on the Database side we want to maximize the performance of reads/writes at the expense of recovery time in a crash situation.


How many users are supported on SQL Server 2012 Express? I would like to say that i have gone through some of the sql blog and MSDN BOL, but still there is some confusion in mind like.

The limitation of SQL Server 2012 Express is 10 GB. It is the limitation of instance based or per database based?
No. of connections for SQL Server 2012 Express: the number of connections is limited or unlimited.

If I shall use Server version of OS then what can be the max concurrent user connections.
The limitation comes from DB level or OS level.
If I shall use Windows Server 2012 R2 with SQL Server 2012 Express SP1 then what will be the max concurrent connections?
How many databases can we keep under the hood of single SQL Server instance.

Any help or suggestion would be appreciated

The limitation is for database not for instance. Plus 10G limitation is ONLY for data file, the log file can be 10.5 G or 20G for example.
As per this BOL document it says that

  The actual number of user connections allowed also depends on the
  version of SQL Server that you are using, and also the limits of your
  application or applications and hardware. SQL Server allows a maximum
  of 32,767 user connections. Because user connections is a dynamic
  (self-configuring) option, SQL Server adjusts the maximum number of
  user connections automatically as needed, up to the maximum value
  allowable. For example, if only 10 users are logged in, 10 user
  connection objects are allocated. In most cases, you do not have to
  change the value for this option. The default is 0, which means that
  unlimited user connections are allowed

There is no LIMIT set as such for express edition. However when connecting from application connection pooling comes into picture, if enabled. In connection pooling, after a connection is created, it is placed in the pool and it is used again so that a new connection does not have to be established.

Yes number of connections are limited by the OS you are using. Please refer to this thread which says only 20 connections are allowed on windows 7. I am not sure about other client OS but if you use server OS there is no such restriction.
Unlimited for practical purpose. There is no restriction as such if your OS is Windows server 2012.
32767 databases is what maximum capacity specification states. But you wont be able to go far than 10 due to express limitation on memory and CPU


how to call lo_unlink(lo) in batches In Postgres 9.5 I am tying to reproduce the postgres tool vacuumlo to get rid of unreferenced large object to free disk space.
References are stored in text type columns by hibernate so using the tool as is is not an option.
Looking at vacuumlo sources ( vacuumlo.c) I have created a vacuum_l table like so:
CREATE TABLE vacuum_l AS SELECT oid AS lo, false as deleted FROM pg_largeobject_metadata;

and proceded to delete all rows from vacuum_l table wich oids are referenced by user tables in my database.
Now is safe to call unlink(lo) for all items remaining in the vacuum_l table.
Unfortunately the table is very big and, as reported in the vacuumlo.c source code:

We don't want to run each delete as an individual transaction, because the commit overhead would be high.  However, since 9.0 the backend will acquire a lock per deleted LO, so deleting too many LOs per transaction risks running out of room in the shared-memory lock table. Accordingly, we delete up to transaction_limit LOs per transaction.

So far I tried to unlink all oids with this sql function:
CREATE OR REPLACE FUNCTION unlinkOrphanedLobs() returns void AS
$BODY$
DECLARE
count_variable int;
begin
    loop
        SELECT COUNT(*) INTO count_variable FROM vacuum_l WHERE deleted=false;
        EXIT WHEN count_variable <= 0;
        UPDATE vacuum_l SET deleted = true WHERE lo IN (SELECT lo FROM vacuum_l where deleted=false limit 500);
        PERFORM lo_unlink(lo) FROM vacuum_l where deleted=true;
        delete from vacuum_l where deleted=true;
    end loop;
END
$BODY$
LANGUAGE plpgsql;

however this end up with the error:

ERROR: out of shared memory
HINT: You might need to increase max_locks_per_transaction.

Why, is clearly explained by the code comments reported above.
This function wont close the transaction at the end of the loop.
How would it be the correct way to do it?
Thanks for any help.

Only solution so far is via bash script:
#!/bin/bash
user=myuser
host=127.0.0.1
database=mydb
batch_size=10000

elements=$( psql -X -A -U $user -h $host -d $database -t -c "SELECT COUNT (*) FROM vacuum_l where deleted = false" )
echo "los to unlink:"
echo $elements
echo
num_batches=$((elements / batch_size))
num_batches=$((num_batches + 1))
echo "Batches:"
echo $num_batches
echo
for ((i = 1; i <= $num_batches; i++)); do
    echo "Batch "$i
    psql -U $user -h $host -d $database -c "UPDATE vacuum_l SET deleted = true, lo = lo_unlink(lo) WHERE lo IN (SELECT lo FROM vacuum_l where deleted=false limit $batch_size )"
done


Optimize mongo inserts I have a very write intensive application that I want to optimize. Additionally I don't care about losing data in a crash, as long as there is no corruption in the existing ones.

Is it safe to run with journal disabled in this case? What other options do I have to increase insert performance?

Option 1: Set up a sharded cluster to distribute writes. However a sharded cluster brings complexity in terms of additional components needed (mongos, config servers, multiple replica sets, etc), along with technological overhead you'll need to deal with (balancer, chunk migration, no point-in-time backup/restore, etc).

Option 2: Set default writeConcern to 0, a.k.a. 'fire and forget' mode. Read more here. Basically, your application writes to mongod process (mongod in Primary, if you use a replica set), and receive no acknowledgement back. Two ways to use writeConcern:

Set as default across replica set
Use writeConcern per query

Read more about it here.


MongoDB Manual References vs DBREF I've been researching MongoDB and trying to see if it would be a fit for my company's product catalog.

I came across database references: http://docs.mongodb.org/manual/reference/database-references/ and had some questions about how exactly to link documents together.

Assume we have two db.categories and db.products collections... I have documents in db.products that need to link to their categories in db.categories.

I have a "category" field in db.products that I will store the objectID of the category document in. However, I do not see a way to specify that this objectID references the db.categories collection.
Do I need to use DBREFs?

Below is a brief example of my data...

Document in db.categories:

 {
        "_id" : ObjectId("546b72a47ea1d03593000158"),
        "name" : "Category1",
        "type" : "type1 of category1",
        "spec_columns" : [
            {
                "field" : "capacity",
                "display" : "Capacity (tons)"
            },
            {
                "field" : "weight",
                "display" : "Weight (lbs)"
            }
        ]
    }

Document in db.products:

{
    "_id" : ObjectId("546a575f7ea1d03593000154"),
    "model" : "NER003H",
    "category" : ObjectId("546b72a47ea1d03593000158"),
    "capacity" : "1/2",
    "weight" : 10
}

Any help, suggestions and advice is welcome and greatly appreciated.

Darius

Short version: use a manual reference, not a DBRef.

Explanation:

There is no particular benefit to the use of a DBRef beyond giving you the collection and database that the referenced document resides in.  If you know the reference is only between categories and products, then that is not particularly useful.  A DBRef simply contains the _id of the referenced document, the collection name and optionally the database name.  You will usually have all that information in your application (i.e. you have the _id and you know that db/collection it resides in already).

If that were not the case, let's say you had references to documents in a slew of databases and collections and it was difficult or cumbersome application side to keep track of which one each document resided in, then it may be a benefit to use DBRefs.  After all, the easiest way to do a manual reference in that case would be to store the database and collection that you are referencing in the document too, and that is what DBRefs do for you in any case.

That (referencing multiple collections from a single source collection) is not a terribly common pattern though and not what you have outlined above, and so unless you have such a compelling reason to use them, manual references are recommended instead.

Some of the drivers have helper methods which may save a few lines of code versus a manual reference, but not all drivers do, and it may make the code harder to understand (without some nice comments/documentation to explain it).  As well as that, the DBREF approach is more fixed than a manual implementation - what if you want to reference a field other than _id for example.

Finally, in terms of product catalog design, I would highly recommend reading these two excellent and detailed blog posts from Antoine Girbal:

Part 1 - Schema Design
Part 2 - Product Search

There were also three presentations given at MongoDB World 2014 on this very topic, so I would recommend reviewing those also as you progress:

Presentation 1: Flexible, Searchable, Low Latency Product
Catalog
Presentation 2: Real-time, Geo-Distributed Inventory
Presentation 3: Scalable Insight Component (User History,
Recommendations, Personalization)


How to plan for change to log backup schedule? I've looked everywhere but can't find an answer for this. I know it's an easy question, but I want to be sure I don't screw anything up.

I want to change our log backup from every 30 minutes to every 15 minutes. Our logs are growing too fast.

If I change to every 15 minutes, do I have to set a new start time? Currently is showing to start at 12:00 and end at 11:59.

If I change at 10:00 am, will the existing jobs still run until 12:00? or will nothing run until the new start time is hit?

So for example, if I set a new job at 10:00am, would I also need to change the start time to 10:00am?

If I change to every 15 minutes, do I have to set a new start time?

Nope! The start and end time is when the schedule is active. So, assuming you still want to take t-log backups every 15 minutes all day, then you just need to adjust that value and the when the next hour/half hour occurs it will take the backup.

  So for example, if I set a new job at 10:00am, would I also need to change the start time to 10:00am?

No - because 10AM is still between 12:00AM and 11:59PM, so the schedule will still be active.


How to select rows after a certain row using auto increment temporary columns I have a table that has ID varchar(255) as PK. I have managed to create an auto increment temporary column rowNumber to use with a SELECT, as follows:
SELECT
   (@cnt := @cnt + 1) AS rowNumber, ID
  from table
  CROSS JOIN (SELECT @cnt := 0) AS n
where
  (
    some conditions
    )
  )
  and date > {ts '2020-08-06 08:51:23.08'}
  ORDER BY ID
  LIMIT 10
;

Executing the above query gives me the following result:
rowNumber     ID
1           0000fb46-7eb1-48b4-8b47-6d99f636c92a
2           000198a5-0453-4339-b022-3050cad4e2b8
3           000ef1cc-1dcb-4162-806d-a040a29986d1
4           001de8e9-2809-4747-b12b-278b76a8bb5d
5           002080fb-21a8-437c-b468-243eb172cd1f
6           0024e99c-f576-404a-b14e-de8b4d6c6d73
7           004f2923-96ae-46ae-8d24-fd67cef40d16
8           006c8439-1ec0-486a-a372-7091d4a85eea
9           0078ce32-5858-4885-b207-c528561c2d40
10          0086618f-ab05-4c06-9c0a-7ec9fed1b82f

How can I modify the query above so that I can select the rows, after a certain rowNumber value, 4 for example. Could really use some help with this.

Use HAVING for checking rownames
SELECT
   (@cnt := @cnt + 1) AS `rowNumber`, ID
  from table1
  CROSS JOIN (SELECT @cnt := 0) AS n
where
  (
    (1 = 1
    )
  )
  and date > '2020-08-06 08:51:23.08'
  HAVING `rowNumber` > 4
  ORDER BY ID
  LIMIT 10
;


Question about joining tables I am working on a project that needs a time clock solution.  The point of sale software they use has a clock in/clock out function built in.  But if an employee misses a punch or something needs to be modified the software does not have that built in. It also has a function of logging what type of break an employee takes.
My issue is this: The punch in and out data is in one table, the break punch in and out data is in another table.
Here is the data that I have in the database now.  I am just learning this and playing with fake data:

The startshift is the punch in time, the end shift is the punch out time.  Those are in 1 table.  The start time and the endtime are a break.  The name is the employee.
There should only be one break on one day but the line show up on every row pulled from the one table.
Here is my SQL. I apologize that this is probably a really newbie thing, but I am trying to help out a friend.
SELECT startshift, endshift, starttime, endtime, `NAME`
from shifts
JOIN people on shifts.PPLID=people.id
JOIN shift_breaks on shifts.PPLID=BREAKID
where PPLID="0"
ORDER BY STARTSHIFT

Here are my table columns:
People

Shifts

shift_breaks

The query returned the results I have above.  If you notice each line has a different startshift, and endshift, but the starttime and end time which are the actual break taken shows up in each row returned.  It should actually show up only in one row as only one break was taken in those 3 days.  When a person punches in the time gets logged into shifts as startshift, when they punch out it gets logged into shits as endshift. If they punch out for a break it gets logged into shift_breaks as starttime. when they punch back in from a break it gets logged into shift_breaks as endtime.

I think I understand what you are getting at. Could you try the following query:

SELECT startshift, endshift, starttime, endtime, `NAME`
from shifts
JOIN people on shifts.PPLID=people.id
INNER JOIN shift_breaks on shifts.PPLID=BREAKID
    and (date(startshift) = date(starttime))
    and (date(startshift) = date(endtime))
where PPLID="0"
ORDER BY STARTSHIFT

This ensures that your join between shifts and shift_breaks are on Person ID AND the corresponding date.

I tested this in a database locally and it seemed to give the desired result.

This approach would have an issue if the start of the shift and end of the shift were on DIFFERENT dates (i.e.: overnight swing shift.)

Overnight shift wont work because we are comparing the DATE of the break begin/end to the DATE of the shift start. To handle spanning dates, you may need a SHIFT table with SHIFT ID, SHIFTSTART (datetime), SHIFTEND (datetime)... then Breaks would have a FK relationship to a SHIFT and EMPLOYEE. Shifts would have an FK relationship with EMPLOYEE. Would then join on IDs.

There are a few ways to restrict the records to a date range within the where clause. You can add where PPLID="0" and date(startdate) = '2017-03-30' for a single day or BETWEEN for a range (between '2012-03-11 00:00:00' and '2012-05-11 23:59:00').


Database for efficient range aggregate queries? As a simplified example, suppose I have a table like this:

seq | value
----+------
102 | 11954
211 | 43292
278 | 19222
499 |  3843

The table may contain hundreds of millions of records, and I need to frequently do queries like this:

SELECT sum(value) WHERE seq > $a and seq < $b

Even if seq is indexed, a typical database implementation will loop through each row to compute the sum in best case O(n), where n is the size of the range.

Is there any database that can do this efficiently, as in O(log(n)) per query?

I have come across a data structure called a Segment Tree as described here. Also sometimes referred to as a range tree or interval tree, although all these names are often described as a slightly different variation of the data structure.

However, I have not come across any database that implements such a data structure. Implementing it from scratch is easy for an in-memory structure, but becomes tricky if it has to be persisted or is too big to fit into memory. If there is an efficient pattern for implementing this on top of an existing database, that could also help.

Side note: This is not an append-only table, so a solution such as keeping a cumulative sum will not work in this case.

Using SQL Server ColumnStore indexes

Well, okay, just one -- a clustered CS index.

If you want to read about the hardware I did this on, head over here. Full disclosure, I wrote that blog post on the website of the company I work for.

On to the test!

Here's some generic code to build a pretty big table. Same warning as Evan, this can take a while to build and index.

USE tempdb

CREATE TABLE t1 (Id INT NOT NULL, Amount INT NOT NULL)

;WITH T (N)
AS ( SELECT X.N
     FROM (
      VALUES (NULL), (NULL), (NULL),
             (NULL), (NULL), (NULL),
             (NULL), (NULL), (NULL),
             (NULL) ) AS X (N)
           ), NUMS (N) AS (
            SELECT TOP ( 710000000 )
                    ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL )) AS N
            FROM   T AS T1, T AS T2, T AS T3,
                   T AS T4, T AS T5, T AS T6,
                   T AS T7, T AS T8, T AS T9,
                   T AS T10 )
INSERT dbo.t1 WITH ( TABLOCK ) (
    Id, Amount )
SELECT NUMS.N % 999 AS Id, NUMS.N % 9999 AS Amount
FROM   NUMS;

--(705032704 row(s) affected) --Aw, close enough

Well, Evan wins for simplicity, but I've talked about that before.

Here's the index definition. La and dee and dah.

CREATE CLUSTERED COLUMNSTORE INDEX CX_WOAHMAMA ON dbo.t1

Looking at a count, every Id has a pretty even distribution:

SELECT t.Id, COUNT(*) AS [Records]
FROM dbo.t1 AS t
GROUP BY t.Id
ORDER BY t.Id

Results:

Id  Records
0   5005005
1   5005006
2   5005006
3   5005006
4   5005006
5   5005006

...

994 5005005
995 5005005
996 5005005
997 5005005
998 5005005

With every Id having ~5,005,005 rows, we can look at a pretty small range of IDs to get you a 10 million row sum.

SELECT COUNT(*) AS [Records], SUM(t.Amount) AS [Total]
FROM   dbo.t1 AS t
WHERE  t.Id > 0
       AND t.Id < 3;

Result:

Records     Total
10010012    50015062308

Query profile:

Table 't1'. Scan count 6, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 2560758, lob physical reads 0, lob read-ahead reads 0.
Table 't1'. Segment reads 4773, segment skipped 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 564 ms,  elapsed time = 106 ms.

For fun, a larger aggregation:

SELECT COUNT(*) AS [Records], SUM(CONVERT(BIGINT, t.Amount)) AS [Total]
FROM   dbo.t1 AS t
WHERE  t.Id > 0
       AND t.Id < 101;

Results:

Records     Total
500500505   2501989114575

Query profile:

Table 't1'. Scan count 6, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 2560758, lob physical reads 0, lob read-ahead reads 0.
Table 't1'. Segment reads 4773, segment skipped 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1859 ms,  elapsed time = 321 ms.

Hope this helps!


Optimizing queries against a set of tables that contain partitioned data I have a third party program that creates and populates a series of tables to hold some historical data:

data_33 -- contains data for 12/01/11 - 12/05/11
data_34 -- contains data for 12/05/11 - 12/14/11
data_35 -- contains data for 12/14/11 - 12/20/11
data_36 -- contains data for 12/20/11 - 12/22/11
data_37 -- contains data for 12/22/11 - 12/23/11
data_38 -- contains data for 12/24/11 - 12/28/11
data_39 -- contains data for 12/28/11 - 01/02/12
data_(you get the idea)

Unfortunately, while these tables are indexed, they are not created with a partitioning scheme or partition function so I'm left to manually iterate through the tables. Now I want to allow the user generation of reports, but I'm unsure of how to best write a stored procedure to query against these tables in an optimized manner.

My first idea was to just UNION ALL all the tables into one gigantic temporary table, but I'm guessing this is going to be costly as a single table has a large number of records and even doing a SELECT * FROM data_33 is taking about 10 minutes to resolve. On top of that, I'll have to maintain this (materialized) view as the software generates new tables.

My second idea was to programmatically UNION ALL everything by grabbing all the tables in the database matching the results of SELECT name FROM sys.tables WHERE name LIKE 'data_%' but without any other limit I'm afraid this would still be extremely slow.

What would be the best way to optimize presenting this mass of data in a somewhat optimized manner?

Before table partitioning was introduced the way of doing the very thing you asked was called Partitioned Views:

  A partitioned view joins horizontally partitioned data from a set of
  member tables across one or more servers, making the data appear as if
  from one table.

Partitioned Views are not only a UNION ALL over each table. By using appropriate check constraints on the underlying tables partitioning column the optimizer can do some fancy stuff, including partition elimination.

PS. Obviously you can generate the view pragmatically. You can even use Event Notifications to generate it automatically every time your 3rd party tool creates a new table, assuming you're running on SQL Server 2005 or later.


Can I select data inserted in the same uncommited transaction? Maybe this is a dumb beginner question, but I cannot find an answer anywhere. Everywhere I read about the Transaction Isolation which solves the visibility of data within the concurrent transactions. My concern is the behavior within a single transaction.

If I start a transaction, insert some data, am I going to be able to select them right after - still within the same, yet uncommitted, transaction?
If yes, can this behavior be changed in a similar way like the mentioned Transaction Isolation in the case of concurrent transactions?

To be specific, I'm targetting PostgreSQL 9.4.

Yes.
Everything you did inside the same transaction is visible to later commands inside the same transaction. Just not to other transactions until committed. This is true for all isolation levels except Read uncommitted where "dirty reads" are possible (but that does not affect your question per se).

It's implemented with the MVCC model (Multiversion Concurrency Control) based on TransactionIds determining relative age and visibility for every table row. Every new row version written in the same transaction gets the same xmin and is considered to have happened "at the same time".

There is a corner case for multiple CTEs (Common Table Expression) in the same command. One might think those are executed sequentially, but unless one CTE references the other their sequence is arbitrary. And all of them see the same snapshot from the start of the query. That's why it's disallowed to UPDATE the same row more than once in multiple CTEs of the same query: would be ambiguous.

Example:

Return pre-UPDATE Column Values Using SQL Only - PostgreSQL Version

Advanced example:

Are SELECT type queries the only type that can be nested?


Can I create an AlwaysOn replication of a DB where some tables don't have a primary key? Using SQL Server 2016 and I have a 3rd party application that uses a large database on my SQL Server for data storage, processing, everything.

I have a BI team that needs access to this data to run reporting and batch-processing tasks, some of which include heavy queries that bring the server to its knees, so they can't be doing that on the live DB. To that end, I have an existing log-shipping solution which maintains a replication of this DB for them to use, but the amount of data and number of users who depend on data from that replication has grown to the point where taking the DB offline several times during the day to perform a log-shipping update is no longer acceptable.

So, I want to use an AlwaysOn availability group replication instead of log-shipping, so that the data remains online during business hours and is automatically updated.

However, my problem is that the application whose database I'm replicating is a little old and out of date, and whoever designed it clearly didn't know or care much about database optimization, since a lot of the larger tables don't have a primary key.

I've added indexes to some of the tables, which has worked OK, but if I modify the structure of the tables at all (such as adding a primary key), the application refuses to recognize them as valid and stops working. So these tables are going to have to remain without primary keys until I can convince the vendor to add them, which is likely to take years.

In the meantime, Is there anything I can do? I've tried using transactional replication and it's replicated all the tables withprimary keys just fine, but the tables without keys simply aren't there in the replicated DB.

I've heard that merge replication can be done on tables without primary keys, and it's possible to use merge replication for availability groups. Is that true? If it is, can you point me in the right direction to create such a replication?

The replication that is used by availability groups is akin to database mirroring, not table replication (SQL Server Replication).  AG replication has no requirements regarding primary keys on tables.  The limitations and restrictions are clearly documented at Prerequisites, Restrictions, and Recommendations for Always On availability groups -> Availability Database Prerequisites and Restrictions.

For the situation you are describing, you would want to Configure read-only routing for an Always On availability group and have the BI team to include ApplicationIntent=ReadOnly in the connection string so that their queries run on the secondary.


AlwaysOn database not synchronizing/recovery pending due to cluster loss quorum I have this error in cluster event

  'Cluster node 'MMPDB1NEW' was removed from the active failover cluster membership. The Cluster service on this node may have stopped. This could also be due to the node having lost communication with other active nodes in the failover cluster'

But now even though my cluster nodes are online and quorum is healthy, still my node 2 database is showing not synchronizing/Recovery pending and in AlwaysOn health events I can see:

  'A connection for availability group 'MTCBAG' from availability replica 'node1' with id.. to 'node 2' with id.. has been successfully established.

Should I need to wait until it synchronizes back or anything need to be done to bring back my secondary node?

This issue solved after click on resume data movement in node 2 . Hope it helps others.


What is your workflow for planning a data migration? So many times I've been brought in at the end of a software development effort and been told something like "okay, we've got all this new code and it requires tables to change and data to be migrated".

It seems like every time it's a one-off, shoot-from-the-hip, best-guess scenario. I feel like this is my weakest skill set as a DBA.

I'd like to get into some patterns for approaching, managing and testing data migrations.

Please clue me in to some best practices and/or where I can get learning material to help me get better in this area.

Every time I've done it, we've gone for two passes ...

take a snapshot, and working on a different server, use that to determine what has to be done for the migration, and script it.
once they have the script in hand, the snapshop is restored on the test system, and it's timed to see if it'll run within the required time, or it's tuned and modified until it can.
have the stakeholders sign off that nothing looks wrong with the data on the test system.

Then, over a weekend, you have a scheduled outage :

Friday night, the systems that use the database are brought down, a full cold backup is made, and the scripts are run to migrate/modify/whatever to the data
Systems are brought back up under some private address or somehow set up so it's not open to anyone but the stakeholders for acceptance testing
If the stakeholders approve, the system's put online and made public; if not, the database is restored from the backup made on Friday night, and you start the process over again.

With our schedule, the database folks generally had from 6pm on Friday to 10am on Saturday to run the backup & migration scripts, so our goal was that they'd run in under 8 hrs (~6 of that was backups), so we'd have some time for our testing and corrections before it got released to the stakeholders.

Stakeholders were given their time windows in advance, so they knew to leave their weekend open for testing at the beginning of the window.  They'd also be told the end of their window, typically Sunday afternoon, where if everyone hadn't signed off, we'd have to start rolling back.

Oh, and of course ... if someone had a change during either of the acceptance tests, and we made a change, it meant that all of the stakeholder's sign-offs were voided, and they had to re-test ... so we'd try to give them all a while to look for issues and run any corrections as a batch, rather than apply them one at a time.

Luckily, the only times I've had one of those situations where we couldn't have significant downtime, the sytems I was migrating were fed from scripts, not user input, so I could just have two parallel systems going, and swap them out when things got signed off.  (only once was there a problem, when my boss insisted that we take a full backup, not understanding that the whole thing was going to still be online at a different IP ... so what should've been a 5 min outage on a bad day became a 5 hr outage.)


Increase SQL Server Performance I have a fairly simple SQL Server 2014 Standard database with one main table. Here's the workload:

I insert a row, do a few updates to it (up to 5 updates), and move on. I always update recent rows. After a few seconds, I never update the row again. I do occasional selects, but my main problem is with the inserts and updates, even when there are no selects happening.

The queries look like:

insert into Table (Id, Field1, Field2, ...) values (@Id, @Field1, @Field2, ...)

update Table set Field3 = @Field3, Field4 = @Field4 where Id = @Id

Id is the only column in the clustered index. There are no other indices on this table. Id is a uniqueidentifier. I changed it from bigint identity after seeing a huge amount of pagelatch_ex waits.

It's doing about 45k requests/sec with the CPU at 90% usage and IO at 50%. This is in AWS where I'm on c3.2xlarge and 2000 IOPS. I don't have much room to grow CPU wise, but I do have another 10x IOPS. What can I do to decrease CPU usage?

It's doing about 45k requests/sec

Do not do 45k requests per second.

On the insert side you can batch them and use batches of inserts.

Possibly use a stored procedure - that should be a little less intense on the CPU (less parsing).

But mostly consider whether you can cut down the number of operations. BulkCopy is quite nice and you can basically do two operations per second with all inserts and updates in one run (bulkcopy into temp table, merge into target).

This would likey liminate nearly all your CPU.

Yes, this may require some serious reprogramming. But if you need to get down the CPU usage, this is the way to do it.


Find timestamp for highest number of concurrent users by date I have a table including these columns:
Sample data:
concurrent_users  concurrent_timestamp       concurrent_date
3                 2020-06-24 07:20:00 UTC    2020-06-24
7                 2020-06-24 08:20:00 UTC    2020-06-24
8                 2020-07-05 06:20:00 UTC    2020-07-05
2                 2020-07-05 03:20:00 UTC    2020-07-05

I want to find/list the date and timestamp where the concurrent_users is highest for each date.
Expected output:
concurrent_users  concurrent_timestamp       concurrent_date
7                 2020-06-24 08:20:00 UTC    2020-06-24
8                 2020-07-05 06:20:00 UTC    2020-07-05

I tried following approach/query:
Error 1
select concurrent_timestamp, max(concurrent_users)
from sample_data
group by concurrent_date;

But this gives an error as the concurrent_timestamp column is not included in the group by clause.
When I put concurrent_timestamp in group by, I get ...
Error 2
select concurrent_timestamp, max(concurrent_users)
from sample_data
group by concurrent_timestamp ;

It does not give the desired output instead just lists more than a single record for each date.
How to solve this?

In PostgreSQL you can simply use DISTINCT ON:
SELECT DISTINCT ON (concurrent_date) *
FROM   sample_data
ORDER  BY concurrent_date, concurrent_users DESC;

We don't need the redundant column concurrent_date for this at all. Casting concurrent_timestamp on the fly is very cheap - overall cheaper than storing the functionally dependent value redundantly.
SELECT DISTINCT ON (concurrent_timestamp::date) *
FROM   sample_data
ORDER  BY concurrent_timestamp::date, concurrent_users DESC;

db<>fiddle here
See:

Select first row in each GROUP BY group?

DISTINCT ON is typically fastest for few rows per group. Depending on table definition and data distribution, there may be (much) faster solutions. See:

Optimize GROUP BY query to retrieve latest row per user


Windows Authentication In SQL DTS packages We have a SQL server 2008 R2 and have legacy DTS packages running on the server. As of now, the subtasks inside DTS packages are using dynamic properties where SQL Login ID details have been given, which are used by the subtasks to login to another server in same domain and get the results from databases for processing.

Our need is to get rid of SQL logins and start using Windows Authentication for all the processes inside the DTS packages, which includes the subtasks to connect another SQL instance. However, I couldn't find any know way or option in the dynamic properties file to make use of Windows Authentication mode.

You can do this by giving the specified Windows account the necessary permissions to the servers/databases that the SSIS job connects to, and then running the DTS package under that Windows account.

For example, to run a SQL Agent job step with the Windows account, you have to create a SQL Server credential, then a SQL Server Agent proxy for that credential, and then assign the job step that runs the package to run under the proxy.  I don't recommend it, but for completeness will mention that if you are using a domain account for the SQL Server Agent account, you can simply give permissions to that account, and have the job step run with it.  The problem with this is that you have one account with permissions to numerous objects and it becomes a security concern.

If you are using a Windows Scheduled Task, you need to configure that task to run with the specified account.

You'll also need to change the connection strings to remove the SQL login and password, and specify ;Integrated Security=SSPI so that it will use Windows authentication.

This thread also mentions that a linked server can be used, but doesn't provide any further details.


How are the following situations handled in MySQL Q1. What happens if two rows are inserted simultaneously in a table where a column is marked unique and both rows have same value for that column.

Q2. What happens if a row is being updated and at the same time another query tries to delete it.

UPDATEs and DELETEs will at very least apply a row level lock that prevents other transactions from affecting that row until the locking transaction is complete. If the LOCK takes 'too' long to resolve the transaction it can result in a TIMEOUT or a DEADLOCK scenario. Transactions are applied in a synchronous manner based upon when the command reaches the database.


Migrate MySQL to MSSQL with views I have one MySQL database and I want to migrate it with data to MSSQL. I have tried a few tools including MS SQL server Import and Export wizard, DataLoader, ESF Database migration toolkit, mysqldump with --compatible=mssql.

All of these tools are working, but it is migrating views as tables.

Do you have any experience with the tool that can migrate views as well?

Rather unfortunately ... https://www.sqlservercentral.com/articles/20-tips-on-converting-mysql-views-to-ms-sql

  Although there are many free tools and solutions to migrate MySQL data to MS SQL server, none of them is able to convert views. That's why everybody who wants to migrate complete database have to convert views manually ...

After that they give 20 tips on how to convert the view-declaration so that mssql understands it. Unfortunately this means you have to work view by view .. by hand


How to finds users that can access a particular table? How do I find all the users that can access one particular table?

For example table 'Table01'

Thanks

If you are sys user then you can use dba_tab_privs view as shown below.

SQL>Conn / as sysdba
SQL>select grantee, table_name, privilege from dba_tab_privs where table_name='Table01';

Update:

As Justin Cave and mustaccio said, its a bit complex to get the accurate result from general dba_ or role_ views.

Though you can query role_tab_privs to get all the roles and its object privileges.

SQL> select grantee,  privilege from dba_tab_privs where table_name='EMPLOYEES' and owner='HR';

GRANTEE                        PRIVILEGE
------------------------------ ----------------------------------------
SCOTT                          SELECT
OE                             SELECT
OE                             REFERENCES
JAY                            UPDATE
R1                             SELECT

In the above result Grantee R1 is a role which has SELECT object privilege on EMPLOYEES table.

Here is a useful post similar to this. oracle - list users with access to certain tables


Differences between estimated and actual execution plans I'm new to SQL Server and I want to tune my first query.

In SQL Server 20xx, what is the difference between "display estimated execution plan"  and "include actual execution plan" ?

The estimated execution plan is generated based solely on the statistics that SQL Server has - without actually executing the query. The query optimizer is just asked what it would most likely do with this query, based on all the information it has on the query and the data distributions etc.

This works OK, the query doesn't need to run (which could take a long time), but if the statistics are out of date, the plan might be severely skewed.

The actual execution plan is just that - the actual execution plan that was used when actually running the query. This will show you things that might hint at "out-of-date" statistics etc. But to get this, you must run the query - which can take a long time.


Can SSIS packages call other SSIS packages in the connection string? I am running an SSIS package that transfers data from one (SQL2008) server to another (SQL2000). However after P2V conversion the SQL2008 server cannot execute an SSIS package due to a user authentication error.
Lets say the package is called "Transfer-Go". In the connection string of that package can it call another SSIS package? In the SSIS library there is another package with the same that appears in the string (called Transfer-Now) name The string is below:
Data Source=<IP>;User ID=<user>;Initial Catalog=<db_name>;Provider=SQLNCLI10.1;Persist Security Info=True;OLE DB Services=-13;Auto Translate=False;Application Name=SSIS-<Transfer-Now-name of other SSIS package>-{8ABA18EE-637E-424F-A3F7-F7E4EA50DD9D}<IP.db_name.user>;

So is this SSIS package connection string calling that package?
And if the credentials are wrong in that package could that be why I am unable to authenticate?
Thanks for any input, not a DB/SQL guy at all so I apologize if I sound green here.

The connection string is not calling another SSIS Package. The property Application Name is optional filler text so you can differentiate one connection from another.
Application Name=SSIS-<Transfer-Now-name of other SSIS package>-{8ABA18EE-637E-424F-A3F7-F7E4EA50DD9D}<IP.db_name.user>

If Transfer-Go has the a reference to Transfer-Now in the connection string, the probable cause is that the developer created Transfer-Now and then copy/pasted either the package in whole or at least the Connection Manager between the packages.
The mechanisms for one package to run another package are

Execute Package Task {either as an in-process (synchronous) or out of process (asynchronous)}
Execute SQL Task
Execute Process Task

The later two options will have you either running a predefined Agent Job or building out the appropriate commands to dtexec


Splitting a year into pieces I want to split a given year into given number of date range pieces using SQL. Following points need to be considered while splitting it:

Given year will always start from 01-01 and end at 12-31 and it should be split into given number of pieces based on months. For example:

year  SplitCount   output
2019  2            2019-01-01
                   2019-07-01

2019  3            2019-01-01
                   2019-05-01
                   2019-09-01

If user asks to split given year into 12 pieces, it should split the whole year into 12 months.
If user asks to split given year more then 12 pieces, it should split it into week wise

Please suggest.

Try this:. It uses DateFromParts(year, month, 1) to find the correct month for any value 1..12 or it uses DateAdd(week, date) to figure for any value 13..52. This way, we let the database engine worry about pesky things like leap year for us rather than doing that kind of math on our own.

Create or alter function udf_SplitYear(
    @TargetYear numeric(4,0),
    @SplitCount tinyint )
returns @ReturnVals table (
    [Year] numeric(4,0),
    SplitCount numeric(3,0),
    SplitNum numeric(3,0),
    StartDate datetime,
    WeekNum numeric(2,0)
)
as
 -- Split this into evenly divisible blocks based on months or weeks.
begin
    -- Valid dates for SQL Server are 1/1/1753 to 12/31/9999  https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-2017
    If @TargetYear < 1753
        Return;
    if @splitcount < 2      --cannot split something into an unsplit thing...
        Return;
    if @SplitCount > 52     --not going to divide this year into days or worse, hours...
        Return;

    declare @rowNumber int = 1;
    declare @PreviousDate datetime = DateFromPartS(@TargetYear, 1, 1);
    declare @ThisDate datetime = DATEFROMPARTS(@TargetYear, 1, 1);
    declare @dateGap int = 0;
    declare @Divisor int;
    declare @ThisSplit int = 1;

    -- 01 Jan <year>
    insert @ReturnVals ([Year], SplitCount, SplitNum, StartDate, WeekNum) values (@TargetYear, @SplitCount, 1, @ThisDate, DatePart(week, @ThisDate));

    if @splitCount < 13
      Begin
        --split across month boundaries
        set @Divisor = Round(12 / @SplitCount, 0)
        set @ThisSplit =  @Divisor + 1;
        While @rowNumber < @SplitCount
        Begin
            set @ThisDate = DATEFROMPARTS(@TargetYear, @ThisSplit, 1);
            set @dateGap = DateDiff(day, @PreviousDate, @thisDate);
            insert @ReturnVals ([Year], SplitCount, SplitNum, StartDate, WeekNum) values (@TargetYear, @SplitCount, @ThisSplit, @ThisDate, DatePart(week, @ThisDate));
            set @PreviousDate = @thisDate;
            set @ThisSplit = @ThisSplit + @Divisor;
            set @rowNumber = @rowNumber + 1;
        End;
      end
    else
      begin
        --split across weeks.
        set @Divisor  = Round(52 / @SplitCount, 0);
        set @ThisSplit = @Divisor ;
        While @rowNumber < @SplitCount
        Begin
            set @ThisDate = DateAdd(wk, @ThisSplit, DateFromParts(@TargetYear, 1, 1));
            set @dateGap = DateDiff(day, @PreviousDate, @thisDate);
            insert @ReturnVals ([Year], SplitCount, SplitNum, StartDate, WeekNum) values (@TargetYear, @SplitCount, @ThisSplit, @ThisDate, DatePart(week, @ThisDate));
            set @PreviousDate = @thisDate;
            set @ThisSplit = @ThisSplit + @Divisor;
            set @rowNumber = @rowNumber + 1;
        End;
      end;

    Return
end

This

Select * from udf_SplitYear(2019, 3)

Gives:

Year   |   SplitCount   |   SplitNum   |   StartDate   |   Days
2019   |   3   |   1   |   2019-01-01 00:00:00.000   |   0
2019   |   3   |   5   |   2019-05-01 00:00:00.000   |   120
2019   |   3   |   9   |   2019-09-01 00:00:00.000   |   123

This

Select * from udf_SplitYear(2019, 6)

Gives:

Year   |   SplitCount   |   SplitNum   |   StartDate   |   Days
2019   |   6   |   1   |   2019-01-01 00:00:00.000   |   0
2019   |   6   |   3   |   2019-03-01 00:00:00.000   |   59
2019   |   6   |   5   |   2019-05-01 00:00:00.000   |   61
2019   |   6   |   7   |   2019-07-01 00:00:00.000   |   61
2019   |   6   |   9   |   2019-09-01 00:00:00.000   |   62
2019   |   6   |   11   |   2019-11-01 00:00:00.000   |   61

This

Select * from udf_SplitYear(2019, 26)

Gives:

Year   |   SplitCount   |   SplitNum   |   StartDate   |   WeekNum
2019   |   26   |   1   |   2019-01-01 00:00:00.000   |   1
2019   |   26   |   2   |   2019-01-15 00:00:00.000   |   3
2019   |   26   |   4   |   2019-01-29 00:00:00.000   |   5
2019   |   26   |   6   |   2019-02-12 00:00:00.000   |   7
2019   |   26   |   8   |   2019-02-26 00:00:00.000   |   9
2019   |   26   |   10   |   2019-03-12 00:00:00.000   |   11
2019   |   26   |   12   |   2019-03-26 00:00:00.000   |   13
2019   |   26   |   14   |   2019-04-09 00:00:00.000   |   15
2019   |   26   |   16   |   2019-04-23 00:00:00.000   |   17
2019   |   26   |   18   |   2019-05-07 00:00:00.000   |   19
2019   |   26   |   20   |   2019-05-21 00:00:00.000   |   21
2019   |   26   |   22   |   2019-06-04 00:00:00.000   |   23
2019   |   26   |   24   |   2019-06-18 00:00:00.000   |   25
2019   |   26   |   26   |   2019-07-02 00:00:00.000   |   27
2019   |   26   |   28   |   2019-07-16 00:00:00.000   |   29
2019   |   26   |   30   |   2019-07-30 00:00:00.000   |   31
2019   |   26   |   32   |   2019-08-13 00:00:00.000   |   33
2019   |   26   |   34   |   2019-08-27 00:00:00.000   |   35
2019   |   26   |   36   |   2019-09-10 00:00:00.000   |   37
2019   |   26   |   38   |   2019-09-24 00:00:00.000   |   39
2019   |   26   |   40   |   2019-10-08 00:00:00.000   |   41
2019   |   26   |   42   |   2019-10-22 00:00:00.000   |   43
2019   |   26   |   44   |   2019-11-05 00:00:00.000   |   45
2019   |   26   |   46   |   2019-11-19 00:00:00.000   |   47
2019   |   26   |   48   |   2019-12-03 00:00:00.000   |   49
2019   |   26   |   50   |   2019-12-17 00:00:00.000   |   51


Updating a view on multiple joined tables Since MSDN does not say much, what happens exactly if I execute the following query?

update claims set status='Awaiting Auth.'
where status = 'Approved'

Can I use the ClaimStatusName column of the linked table dimClaimStatus to update the main table that is referenced via foreign-key?

The view itself queries multiple tables, the main table is tabData, which I also want to update with above query. I want to change fiClaimStatus in tabData from the FK that means Approved in the referenced table dimClaimStatus to Awaiting Auth.. Does it work this way?

Only one view row is possible for each row in tabData.

Here's the view:

CREATE VIEW [dbo].[Claims]
AS
  SELECT mu.MarketUnitName AS MarketUnit,
         c.CountryName     AS Country,
         gsp.GSPName       AS GSP,
         gsp.WCMSKeyNumber AS GspNumber,
         sl.SLName         AS SL,
         sl.WCMSKeyNumber  AS SlNumber,
         m.ModelName       AS Model,
         m.SalesName       AS [Model-Salesname],
         s.ClaimStatusName AS [Status],
         d.Work_Order      AS [Work Order],
         d.SSN_Number      AS IMEI,
         .... more columns ....
         idData,         -- PK of main table tabData
         fiSL,
         fiModel,
         fiClaimStatus  -- FK to dimClaimStatus
  FROM   tabData AS d
         INNER JOIN locSL AS sl
                 ON d.fiSL = sl.idSL
         INNER JOIN locGSP AS gsp
                 ON sl.fiGSP = gsp.idGSP
         INNER JOIN locCountry AS c
                 ON gsp.fiCountry = c.idCountry
         INNER JOIN locMarketUnit AS mu
                 ON c.fiMarketUnit = mu.idMarketUnit
         INNER JOIN modModel AS m
                 ON d.fiModel = m.idModel
         INNER JOIN dimClaimStatus AS s
                 ON d.fiClaimStatus = s.idClaimStatus
         INNER JOIN tdefProductType
                 ON d.fiProductType = tdefProductType.idProductType
         LEFT OUTER JOIN tdefServiceLevel
                      ON d.fimaxServiceLevel = tdefServiceLevel.idServiceLevel
         LEFT OUTER JOIN tdefActionCode AS ac
                      ON d.fimaxActionCode = ac.idActionCode

Update

Since the table contains 20 million customer records, I wanted to know first what will happen before I execute it. After the comments and answers, I have executed it now. Immediate result was:

(1 row(s) affected)

Which is surprising, because there were several thousands of records in this status which seem to be updated now.

Update 2

Actually it didn't work as expected and the suspicious (1 row(s) affected) was right. Only the referenced table was updated. So now the status Approved changed to Awaiting Auth..

Conclusion:

It seems to be the best way to just avoid using a view to do updates. This works in my case:

UPDATE tabData
SET fiClaimStatus = (SELECT idClaimStatus
                     FROM dimClaimStatus
                     WHERE ClaimStatusName = 'Awaiting auth.')
WHERE fiClaimStatus=(SELECT idClaimStatus
                     FROM dimClaimStatus
                     WHERE ClaimStatusName = 'Approved')

General view updatability
The key part of the CREATE VIEW (Transact-SQL) documentation is:

Generally, the Database Engine must be able to unambiguously trace modifications from the view definition to one base table.

Note that even if the view is technically updatable, it may not be actually updatable in practice, due to limitations of the query processor's reasoning. That is the subtlety behind the phrase, "...the Database Engine must be able to..."
The easiest way to be sure that a view is actually updatable is to request a pre-execution ("estimated") plan for the update query. If you get an error, either the view is not logically updatable, or the query processor can't tell that it is.
Requesting a "estimated" plan does not involve executing the query, naturally. The plan shown will also show you how much of the view definition the query optimizer was able to remove (because it is redundant). Typically, it does a good job with this, so the update view plan may look very similar to a plan for a simple update to the single affected base table.
Specific example

Can I use the ClaimStatusName column of the linked table dimClaimStatus to update the main table that is referenced via foreign-key? [...] I want to change fiClaimStatus in tabData.

Not using the query you posted:
update claims
set status='Awaiting Auth.'
where status = 'Approved'

This changes the base table column associated with the view's exposed column name status. From the view definition, that is the alias for column  ClaimStatusName in table dimClaimStatus.
The execution plan shows that dimClaimStatus is the table updated through the view:

If you want to update fiClaimStatus, that is the column you need to specify in the update statement. If that involves a lookup, chances are you can't use the view directly, as you originally wanted, but you could write something like:
update claims
set fiClaimStatus =
(
    select CS.idClaimStatus
    from dbo.dimClaimStatus AS CS
    where CS.ClaimStatusName = 'Awaiting auth.'
)
where status = 'Approved';`


mysql - ALTER IGNORE ADD INDEX removed in mysql 5.7.4 - What to use instead? I am trying to add an index to a table, but only if one doesn't exist. I don't know what the index name is. In the mysql manual I found the alter ignore command:

ALTER [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...]
[partition_options]

alter_specification:
table_options
...
| ADD {INDEX|KEY} [index_name]
    [index_type] (index_col_name,...) [index_option] ...

So, I tried to do the following:

ALTER IGNORE TABLE payments ADD INDEX (id_project);

However, this fails, with the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE payments ADD INDEX (id_project)'

Turns out, this has been removed as of mysql 5.7.4.

What's a good way to do this in newer versions of mysql?

This may help:

Rename the existing table and create a new table.
Use insert ignore to copy data from old table to new table.


Transaction log backup impact on CPU I had a serious CPU bottleneck 24/24 (it reaches 100% very often) for some weeks. The application has a pure aggressive OLTP (a thousand of query that retrieves often 1 row based on several joins).

This week I decided to begin a backup of the transaction log file, and hopefully the CPU bottleneck disappears.

Is there a direct relation between the transaction log file (size,backup or whatever) a the CPU bottleneck?

Backups of the transaction log will not help your CPU bottleneck.

How many deadlocks per hour or day do you have?
What is you CXPACKET wait value?
How much blocking do you have?

I am not saying to stop your transaction log backups, I am saying that this won't help the CPU bottleneck.


MySQL Query on bad designed table with multiple Selfjoins i have to create a report out of a bad designed database (I cannot change)

It saves different Formulars into a form_values table

---+-------+-----------+-------
id | value | form_item | entry
---+-------+-----------+-------

value = input value (DB type = text but can also hold numeric inputs dates and so on)
form_item = input name (example: "name:", "birthday:")
entry = id to formular

My Problem on big Formulars I have to self join multiple times to get all form values and make conditions out of it

Example query:

    SELECT fv.entry as 'entry' FROM form_values fv
                INNER JOIN form_entry_links fel ON fel.child = fv.entry
                LEFT JOIN form_values fv2 ON fv2.entry = fel.child AND fv2.form_item = 117
                LEFT JOIN form_values fv3 ON fv3.entry = fel.child AND fv3.form_item = 118
                LEFT JOIN form_values fv4 ON fv4.entry = fel.child AND fv4.form_item = 119
                LEFT JOIN form_values fv5 ON fv5.entry = fel.child AND fv5.form_item = 120
                LEFT JOIN form_values fv6 ON fv6.entry = fel.child AND fv6.form_item = 121
                LEFT JOIN form_values fv7 ON fv7.entry = fel.child AND fv7.form_item = 122
                LEFT JOIN form_values fv8 ON fv8.entry = fel.child AND fv8.form_item = 123
                LEFT JOIN form_values fv9 ON fv9.entry = fel.child AND fv9.form_item = 124
                LEFT JOIN form_values fv10 ON fv10.entry = fel.child AND fv10.form_item = 125
                LEFT JOIN form_values fv11 ON fv11.entry = fel.child AND fv11.form_item = 126
                LEFT JOIN form_values fv12 ON fv12.entry = fel.child AND fv12.form_item = 127
                LEFT JOIN form_values fv13 ON fv13.entry = fel.child AND fv13.form_item = 128
                LEFT JOIN form_values fv14 ON fv14.entry = fel.child AND fv14.form_item = 129
                LEFT JOIN form_values fv15 ON fv15.entry = fel.child AND fv15.form_item = 132
                WHERE
                fv.entry IN(4,5,6,10,11,12,16,17,18,22,23,24,28,29,30,34,35,38,41,43,45,48,51,52,54,55,57,58,60,61,63,64,65,67,68,70,72,75,78,79,82,83)
                AND fv.entry IN (43,48,51,52,54,55,57,58,60,61,63,64,65,67,68,70,75,82,4,5,6,10,11,12,16,17,18,22,23,24,28,29,30,34,35,38,78,79)
                AND fv.entry IN (4,5,6,10,11,12,16,17,18,22,23,24,28,29,30,34,35,38,78,79)
                AND fv2.value = 0
                AND fv3.value = 0
                AND fv4.value = 0
                AND fv5.value = 0
                AND fv6.value = 0
                AND (
                    ( fv2.value > 0)
                    OR( fv7.value > 0)
                    OR( fv8.value > 0)
                    OR( fv9.value > 0)
                    OR( fv10.value > 0)
                    OR( fv11.value > 0)
                    OR( fv12.value > 0)
                    OR( fv13.value > 0)
                    OR( fv14.value > 0)
                    OR( fv15.value > 0)
                )
                 GROUP BY fv.entry

This is really slow and so the report takes over 1 hour on a small database with 15000 entries

Maybe someone can help me to make a performant query out of this mess,
thank you so much

Looking at your Query, I have a few suggestions:

Get rid of the "OR"s.  you can do this by checking if the
addition of all the fields > 0... OR's are a killer.
as Jkavalik mentioned, use a distinct  instead of a group by... better perf.
The "AND"s of Values 2 to 6 can also be an addition = 0
Do you absolutely need the Left joins? Can they be replaced with inner joins?

As for the logic, I'm not sure I understand the whole concept.


What events cause each run_status code to appear in sysjobhistory? We have been asked to report on the health of our SQL jobs. I am having trouble understanding the run_status column in dbo.sysjobhistory. The MSDB page for sysjobhistory gives this description:

  Status of the job execution:

  0 = Failed
  1 = Succeeded
  2 = Retry
  3 = Canceled

What events cause each status code?

Here is what I have done to attempt to produce each status code. I was able to reproduce...

0 (Failed) by writing SQL that throws an error.

For example, step 1 is SELECT SELECT

1 (Succeeded) by writing SQL that succeeds.

For example, step 1 is SELECT 1

3 (Cancelled) by stopping a job while it was running

4 (In progress) by querying sysjobshistory while a job was running

I know a way produce 0's, 1's, 3's, and 4's, but I don't know all of the ways to produce them, and I have no idea how to cause a 2.

The health of our jobs is binary. We are either healthy or not healthy, because the end user does not want to see anything more granular than that on their dashboard. That is why I am asking this question - I have to interpret the status codes and run durations and draw a single, binary conclusion about the health of the SQL jobs. The problem is that MSDN doesn't explain what the status codes mean, or more importantly, what events might cause them.

For example, if a user cancels a job (resulting in a run_status of 3), we might decide that we don't care and just report that we are healthy. However, if a job gets cancelled because of some other event, we might care about that.

Create a job with a single step. On the advanced tab for that step, change retry attempts from 0 to something greater than 0. In the job step, have it be your SELECT SELECT. Run the job. You will see an entry .

I don't think anyone could provide a full list of all scenarios that could lead to the various possible status values. Did you try anything like this while a job was running (and it's easy to test this with a simple job that has a loop with SELECT TOP (1) name FROM somedb.sys.all_objects;WAITFOR DELAY '00:00:01';); things like:

unplugging the computer
failing over (cluster or AG)
setting somedb to single_user or offline
killing sqlservr.exe process or shutting down the agent service
having the step raising batch- and connection-aborting errors
having the step raise an exception that causes "severe error" or assertion check failures (assuming there are some that exist and you can repro on your version)

run_status is useful, but you can get a better picture from the combination of run_status and message (and, in severe cases, the SQL Server error log). The trouble with message is that the contents can get truncated, so if you have a step that performs 400 successful backups and then one fails, the content will be full of successful messages, and the failure will be missing. For important jobs you may want to log to a table or to a file; you can see a variety of options on the Advanced tab of any job step.


Best practices for history/temporal tables? Suppose I have an object, with certain fields which I want to track history, and certain fields which I do not want to track history. From a normalization perspective, is the following schema alright:

CREATE TABLE MyObject AS (
    MyObjectId INT IDENTITY NOT NULL PRIMARY KEY,
    MyObjectField1 VARCHAR(100) NOT NULL,
    MyObjectField2 VARCHAR(100) NOT NULL,
    MyObjectField3 VARCHAR(100) NOT NULL,
    MyObjectTrackedField1 VARCHAR(100) NOT NULL,
    MyObjectTrackedField2 VARCHAR(100) NOT NULL,
    MyObjectTrackedField3 VARCHAR(100) NOT NULL,
)
CREATE TABLE MyObjectHistory AS (
    MyObjectHistoryId INT IDENTITY NOT NULL PRIMARY KEY,
    MyObjectId INT NOT NULL FOREIGN KEY REFERENCES MyObject(MyObjectId),
    MyObjectTrackedField1 VARCHAR(100) NOT NULL,
    MyObjectTrackedField2 VARCHAR(100) NOT NULL,
    MyObjectTrackedField3 VARCHAR(100) NOT NULL,
)

where MyObjectHistory contains the tracked fields for all except for the latest revision. Or, should all tracked fields be in one table, and all revisions including the latest be in that table, as in:

CREATE TABLE MyObject AS (
    MyObjectId INT IDENTITY NOT NULL PRIMARY KEY,
    MyObjectField1 VARCHAR(100) NOT NULL,
    MyObjectField2 VARCHAR(100) NOT NULL,
    MyObjectField3 VARCHAR(100) NOT NULL,
)
CREATE TABLE MyObjectHistory AS (
    MyObjectHistoryId INT IDENTITY NOT NULL PRIMARY KEY,
    MyObjectId INT NOT NULL FOREIGN KEY REFERENCES MyObject(MyObjectId),
    MyObjectTrackedField1 VARCHAR(100) NOT NULL,
    MyObjectTrackedField2 VARCHAR(100) NOT NULL,
    MyObjectTrackedField3 VARCHAR(100) NOT NULL,
)

For practical data access reasons, you should use the structure from your first option, but instead keep all versions of your tracked column values including the current version in your history table.

The reason for this is that in general, when you want to look at history, you want to include the present and all past versions.  When you don't want to look at history, you want it out of the way.  In many cases this means going so far as to segregate the history into a separate schema or database altogether.  Even if you keep your history in the same schema as your current data, any queries that look at historical data (including the current values) will be much more complex since they have to essentially union two sources.


How to record prices for the product sales? I'm building a database for daily sales report. For example:
29/01/2021 -> 50 apples were sold
30/01/2021 -> 46 apples were sold
I have these tables:
products

id INT AUTO_INCREMENT PRIMARY KEY
name VARCHAR(100) NOT NULL
price FLOAT NOT NULL
deleted_at DATETIME NULL,
Other stuff

sales

saledate DATE,
product_id INT,
quantity INT,
PRIMARY KEY (saledate, product_id),
FOREIGN KEY (product_id) REFERENCES products(id),

It is working. But now I have to record the price of the product when it was sold. The problem is that the product's price can be changed during the day for example:
29/01/2021 -> 43 apples were sold (Apple price: 1$)
29/01/2021 -> 7 apples were sold (Apple price: 0.90$)
30/01/2021 -> 46 apples were sold (Apple price: 1.10$)
as you can see, on 29/01/2021 we sold 43 apples to 1$ and 7 apples to 0.90$ because the price of  apples has changed. I thought to add a column to sales table: price FLOAT NOT NULL but then how can I to have multiple prices associated to a specific saledate?
In essence, how to change the sales table to get output like in the example above? I thought to make price column primary key: PRIMARY KEY (saledate, product_id, price) but honestly I don't know if it is a clean solution and if it has a bad impact on performance. Currently the query to insert a new sale is:
INSERT INTO sales (saledate, product_id, quantity) VALUES(date_value, product_id_value, quantity_value)
ON DUPLICATE KEY UPDATE quantity = quantity + quantity_value

Thank you in advance.
PS: Sorry for my bad english

What you really should do (and is how a lot of similar type systems architect their schema) is have two normalized tables that store sales information.
One table is the SalesHeader (or sometimes just called SalesOrder) and the other table is the SalesLine. SalesHeader has a one-to-many foreign key relationship to SalesLine on the OrderId.
The SalesHeader stores basic information like OrderId (as the primary key) and SaleDate, where the SalesLine table has a new record for each ProductId with Quantity and Price columns, and its own primary key column called LineId.
This allows you the flexibility to have the same product sold on the same date at different prices, or even multiple products on the same date at different prices, and even batch different orders up (if you wanted). You can adjust the SalesHeader and SalesLine tables as what's more relevant to your use case, but that's the general solution. (Also please don't mind that I use PascalCasing when naming my obejcts. :)