I don't think there's a general best option in this matter. It depends on many things;

For instance, if you are dealing with high activity on the database, or even have memory issues (not you, but the database jk) you might want to grab the data and leave asap, that means, move most of the computations on SSRS;

Leave the simple aggregations (like totals, avgs etc) to the SSRS, because it is already optimised to do such tasks on Tablix. If you have more complex aggregations (eg. partitions) let the SQL server do them. If the database and SSRS are on different machines, watch the activity (load), and based on that do the judgement.

These are all performance issues, but there is also flexibility to be taken into account. Try to be proactive, and find the elements prone to modifications, and see where it would be easier to apply them (on the SQL, or the report).

The idea is that it really depends on each individual report that you are trying to develop. My advise would be to do the computations on SQL, because it is easier.

Lastly, as a rule of thumb, do not try to optimise something that is not having any performance issues. Most of the times the improvement rate it just not worth the time invested. When your server starts to look tired, check the most expensive queries, and if this one is in top, then you might consider moving some of the computations on SSRS.

Hope my advice is useful to you.
Cheers!


Caching derived dataset automatically in SQL Server

I could create a cache table and manually load it any time the underlying table if modified, but this feels like a hacky workaround. I think I'd also have to create a SQL Agent Job for this

That's the fallback.  And it's totally normal to run an ETL job after loading staging tables.

You might be able to do something with 2 indexed views instead of one.  And, of course, you can play with indexes and columnstore tables to improve query performance.


How to design a schema for a site that accepts logins from multiple sites (e.g. facebook connect, google, flickr, etc..)?

There is a similar question in stackoverflow

And the solution is

  You need 2 additional fields in your
  table, one is the login source
  (twitter, openID, facebook) and the
  other is the source user id (the id
  provided from twitter, openID,
  facebook) I recommend making this a
  string as some log in sources have
  letters in their user IDs and not just
  numbers. more

I'm not sure how smart it is to use this approach.

  This approach is good, actually sites
  opting it. see SO login page
  https://stackoverflow.com/users/login

I don't know how to implement the third scenario in such an approach.

  I think  you can implement this, only
  if the thrid party sites provide
  access. This question is off-topic
  here, you can ask this in
  stackoverflow


Should I defragment InnoDB table with fixed-length fields?

I would say yes for four(4) major reasons

Page Boundaries / Index Pages
Index Layout
Index Statistics
Bulk DML

PAGE BOUNDARIES / INDEX PAGES
According to the MySQL Documentation on Limits on InnoDB tables

The maximum row length, except for variable-length columns (VARBINARY, VARCHAR, BLOB and TEXT), is slightly less than half of a page for 4KB, 8KB, 16KB, and 32KB page sizes. For example, the maximum row length for the default innodb_page_size of 16KB is about 8000 bytes. For an InnoDB page size of 64KB, the maximum row length is about 16000 bytes. LONGBLOB and LONGTEXT columns must be less than 4GB, and the total row length, including BLOB and TEXT columns, must be less than 4GB.

Your table uses INT UNSIGNED (4 bytes), DATETIME (8 bytes), 2 SMALLINTs (4 bytes) per row. That's a total of 16 bytes per row. This means you can fit 1000 rows into a single InnoDB page, but there is a very small piece of fragmentation. Using the default innodb_page_size (16K or 16384), there will be 384 bytes of fragmentation with a page. It is most likely used for mapping the data within the one page.
Doing mass INSERTs, DELETEs, and UPDATEs will generate many pages with empty space for whole rows that are marked as unused. This is in addition to the 384 bytes region of the data page.
INDEX LAYOUT
You will generate many index pages

You have a 6-byte PRIMARY KEY
Each secondary index carries a copy of the PRIMARY KEY
KEY external_id (external_id), will be 8 bytes (SMALLINT + PRIMARY KEY)
KEY counter (counter), will be 8 bytes (SMALLINT + PRIMARY KEY)
KEY date_idx (date) will be 14 bytes (DATE + PRIMARY KEY)
36 bytes for indexing a single row
Indexes are represented as BTREEs, so you are looking at O(n log n) space utilization. If you account for non-leaf page splits, many index pages will be 50% empty (without question).

INDEX STATISTICS
Running INSERTs, DELETEs, and UPDATEs will skew index stats since index cardinalities will frequently change. You need to run ANALYZE TABLE tbl_log;. Running OPTIMIZE TABLE tbl_log; will defrag the tables and run ANALYZE TABLE tbl_log; as the last step (See my post from Feb 27, 2013 : What is the difference between optimize table and analyze table in mysql)
BULK DML
The DELETE query you will be doing by the hour is a bulk operation. Many rows will need a ton of housecleaning to keep up with marking rows deleted.


No, shrinking a file or a database never deletes data, at least not data that hasn't been marked to be deleted via the DELETE FROM or the TRUNCATE TABLE ... T-SQL statements.

The "Reorganize pages before releasing unused space" provides the facility to move data around within the file to allow shrinking the file as much as physically possible.  If the minimum size reported by the "shrink file" dialog is 350,000 MB, then you won't be able to shrink the file to anything less than that number.

You may want to look at this question and the associated answers for more details about the pros-and-cons of shrinking databases/files.


Why does it take a long time to drop a function based index in Oracle?

A function-based index adds a virtual column to the table (This column is then indexed). Dropping the index removes the virtual column, which leads to a cleanup that takes time (same amount of work as the removal of a non-virtual column).


Why does adding a simple CHECK constraint take so long on Postgres

when creating the constraint, check pg_locks for granted  is false or pg_stat_activity for wait_event_type and wait_event where pid = <pid that adds the check> (note that these two columns are new in 9.6 - in earlier versions you see only the fact that it is waiting).

Most probably your ALTER TABLE was waiting for other process to release a lock before it could acquire exclusive one.


Use the solution that already exists for this problem.  Use a UUID.  This would require no communication or management of installations overall.  Do not use plain random values.  The birthday paradox means that the possibility of a collision increases exponentially as the number of records increases linearly.  UUIDs are specifically designed to address this issue.  I am not sure how good MySQL's UUID generator is.

Another option would be to use a compound primary key with an installation ID or customer ID that is determined by you for each instance of the application, combined with an auto_increment field to make sure each record is unique within each instance.


Select FK constraints with ON UPDATE NO ACTION

For the "update action" as in:

FOREIGN KEY (foo_id) REFERENCES foo(foo_id) ON UPDATE CASCADE ON DELETE CASCADE
FOREIGN KEY (foo_id) REFERENCES foo(foo_id) ON UPDATE DELETE ON DELETE CASCADE
FOREIGN KEY (foo_id) REFERENCES foo(foo_id) ON UPDATE NO ACTION ON DELETE CASCADE

etc.

.. look in the system catalog table pg_constraint. It contains the column confupdtype which holds, according to the manual:

  Foreign key update action code: a = no action, r = restrict, c = cascade, n = set null, d = set default

Queries

Find all FK constraints with ON UPDATE NO ACTION pointing to a given table (your question in the title):

SELECT conrelid::regclass        AS table_from
     , conname                   AS constraint_name
     , pg_get_constraintdef(oid) AS constraint_def
FROM   pg_constraint
WHERE  contype = 'f'
AND    confupdtype = 'n'
AND    confrelid = 'public.target_table'::regclass  -- target table here
ORDER  BY 1;

Inspect all FK constraints in the DB:

SELECT conrelid::regclass        AS table_from
     , conname                   AS constraint_name
     , pg_get_constraintdef(oid) AS constraint_def
     , CASE confupdtype
        WHEN 'a' THEN 'NO ACTION'
        WHEN 'r' THEN 'RESTRICT'
        WHEN 'c' THEN 'CASCADE'
        WHEN 'n' THEN 'SET NULL'
        WHEN 'd' THEN 'SET DEFAULT'
        ELSE 'WARNING: unexpected value!'
       END                       AS fk_update_action
FROM   pg_constraint
WHERE  contype = 'f'
ORDER  BY 1;

Related:

Retrieving all PK and FK


Enable TDE for existing AlwaysOn databases

In the absence of any other input, here's what I was able to deduce. Short story: this appears to work fine.

As far as I can see, enabling TDE for a database is a logged operation, but the actual data encryption is not logged (otherwise the transaction log would grow considerably, which it doesn't). I assume there's something like the differential change map, or a flag in the page headers to indicate if a page is encrypted or not, and the storage engine just chews through everything until it's fully encrypted.

When I enable TDE at the primary database, the secondary database ends up encrypted too (I have verified this with a hex editor). While encrypting is in progress, the view sys.dm_database_encryption_keys indicates percent_complete for all the secondary servers is 0, and this value never increases. The primary server updates this column normally. All the replicas tend to finish encrypting at their own pace, at which point encryption_state changes to 3. Seems like everything works normally once it's finished.


DB2: Is it good to create the LONG table space to use 32 KB data page

Check out the general answer I gave to you on your other question.

If you are storing your XML as a LOB, then I guess I'd see what is the common average size per table. If the average size is 4MB or less (which it sounds like), then you'd only need a 4K tablespace (see Space requirements for table, table 1).

If you are storing the XML as an XML data type in DB2, then you actually get some performance gains over LOB, especially if you inline the XML (and aren't doing updates to it all that often). This may be the best since you mention sizes less than 32K. You may wish to check out XML storage page for in-lining XML within the table vs outside for XML storage.


MySQL Event Help - Update column value for each row every 15 minutes

Each hour:

CREATE EVENT reset ON SCHEDULE EVERY 1 HOUR DO UPDATE admin set aLoginAttempts = 0


Take a look at the -zr switch on the server, which turns on "request level logging". Start the server with -zr all -zo reqlog.txt and then look at reqlog.txt after some queries have been executed. It's fairly verbose but should list the SQL that's being executed.

Disclaimer: I work for Sybase in SQL Anywhere engineering.


Recursive queries cannot be done with pure SQL when it comes to MySQL.

I have written posts on how to use Stored Procedures to accomplish this

Oct 24, 2011 : Find highest level of a hierarchical field: with vs without CTEs
Dec 10, 2012 : MySQL: Tree-Hierarchical query
Jul 15, 2013 : Get top most parent by nth child id?

Give them a Try !!!

NOTE : This post, originally May 24, 2014, predates MySQL 8.0, which now has CTEs.


You don't specify what "first" means (or provide any table structure or example data), but let's assume it's the earliest completion timestamp:

  SELECT entity, min(completion_ts)
    FROM your_table
GROUP BY entity

If you are just getting results for a single entity, you can simplify even more:

  SELECT min(completion_ts)
    FROM your_table
   WHERE entity = 'XXX'


SQL Server MCSA Database Administration Certification retires on June 30, 2020 - is there going to be a replacement?

If you are focused on DB administration I would rather recommend certification "Microsoft Certified: Azure Database Administrator Associate"

https://docs.microsoft.com/en-us/learn/certifications/azure-database-administrator-associate

This one requires DP-300 exam and it is focused on Azure and on-perm administration.


select      log_id
           ,array_agg (sequence)

from       (select      log_id
                       ,sequence
                       ,count (is_restart) over
                        (
                            partition by    log_id
                            order by        made_at
                        ) as restart_id

            from        (select      made_at
                                    ,log_id
                                    ,sequence
                                    ,case
                                         when sequence <
                                              lag (sequence) over
                                              (
                                                  partition by    log_id
                                                  order by        made_at
                                              )
                                         then 1
                                     end            is_restart

                         from        logs
                         ) l
            ) l

group by    log_id
           ,restart_id

order by    log_id
           ,restart_id
;

+--------+-----------+
| log_id | array_agg |
+--------+-----------+
| 206480 | {1,1,2,3} |
+--------+-----------+
| 206480 | {1,2,4,5} |
+--------+-----------+
| 206480 | {1,2}     |
+--------+-----------+
| 206481 | {1,2,3,4} |
+--------+-----------+

Walkthrough

Identify restarts by comparing current sequence to previous sequence (LAG).

select      made_at
           ,log_id
           ,sequence

           ,case
                when sequence <
                     lag (sequence) over
                     (
                         partition by    log_id
                         order by        made_at
                     )
                then 1
            end            is_restart

from        logs

+---------+--------+----------+------------+
| made_at | log_id | sequence | is_restart |
+---------+--------+----------+------------+
| 1       | 206480 | 1        |            |
+---------+--------+----------+------------+
| 2       | 206480 | 1        |            |
+---------+--------+----------+------------+
| 3       | 206480 | 2        |            |
+---------+--------+----------+------------+
| 4       | 206480 | 3        |            |
+---------+--------+----------+------------+
| 5       | 206480 | 1        | 1          |
+---------+--------+----------+------------+
| 6       | 206480 | 2        |            |
+---------+--------+----------+------------+
| 7       | 206480 | 4        |            |
+---------+--------+----------+------------+
| 8       | 206480 | 5        |            |
+---------+--------+----------+------------+
| 9       | 206480 | 1        | 1          |
+---------+--------+----------+------------+
| 10      | 206480 | 2        |            |
+---------+--------+----------+------------+
| 11      | 206481 | 1        |            |
+---------+--------+----------+------------+
| 12      | 206481 | 2        |            |
+---------+--------+----------+------------+
| 13      | 206481 | 3        |            |
+---------+--------+----------+------------+
| 14      | 206481 | 4        |            |
+---------+--------+----------+------------+

Do "running counts" (similar to "Running totals") of restarts (is_restart).
Rows that belongs to the same group will have the same count (AKA restart_id).
The "Order by" in COUNT implies range between unbounded preceding and current row

select      log_id
           ,sequence
           ,count (is_restart) over
            (
                partition by    log_id
                order by        made_at
            ) as group_id

from        (...) l

+--------+----------+----------+
| log_id | sequence | group_id |
+--------+----------+----------+
| 206480 | 1        | 0        |
+--------+----------+----------+
| 206480 | 1        | 0        |
+--------+----------+----------+
| 206480 | 2        | 0        |
+--------+----------+----------+
| 206480 | 3        | 0        |
+--------+----------+----------+
| 206480 | 1        | 1        |
+--------+----------+----------+
| 206480 | 2        | 1        |
+--------+----------+----------+
| 206480 | 4        | 1        |
+--------+----------+----------+
| 206480 | 5        | 1        |
+--------+----------+----------+
| 206480 | 1        | 2        |
+--------+----------+----------+
| 206480 | 2        | 2        |
+--------+----------+----------+
| 206481 | 1        | 0        |
+--------+----------+----------+
| 206481 | 2        | 0        |
+--------+----------+----------+
| 206481 | 3        | 0        |
+--------+----------+----------+
| 206481 | 4        | 0        |
+--------+----------+----------+

Group by log_id and restart_id and aggregate sequence

select      log_id
           ,array_agg (sequence)

from       (...) l

group by    log_id
           ,restart_id

order by    log_id
           ,restart_id
;

+--------+-----------+
| log_id | array_agg |
+--------+-----------+
| 206480 | {1,1,2,3} |
+--------+-----------+
| 206480 | {1,2,4,5} |
+--------+-----------+
| 206480 | {1,2}     |
+--------+-----------+
| 206481 | {1,2,3,4} |
+--------+-----------+


MsSql Bulk Insert With commit size 1 vs Simple Insert

Singleton inserts via a bulk insert technique are significantly slower than normal singleton inserts.  To prepare for bulk inserts, the client API must first send column meta data describing the tabular data stream that follows, a task not needed with normal inserts.  This overhead is offset by improved performance when streaming multiple rows of data but not when single rows are passed.  See https://msdn.microsoft.com/en-us/library/dd304523.aspx for details of the TDS protocol specification.

I ran tests a few years ago for a presentation on insert performance and found single-row bulk insert performance using SqlBulkCopy averaged about 500 rows per second compared to 1,800 per second for inserts.  With 100 row batches, bulk inserts averaged over 10K/sec compared to 3.5K with single-threaded singleton inserts. I would expect a similar difference with the IRowsetFastLoad OLE DB interface commonly used in SSIS.


PostgreSQL How to get friend requests in table by seeing if there is a duplicate of the reverse of a column?

You can use EXCEPT to remove the complementing rows.

SELECT friend1,friend2 FROM friends
    WHERE friend1=2 or friend2=2
EXCEPT
SELECT friend2,friend1 FROM friends
    WHERE friend1=2 or friend2=2;

But maybe using a CTE will perform better.

WITH buddies
AS ( SELECT friend1,friend2 FROM friends
        WHERE friend1=2 or friend2=2 )
SELECT friend1,friend2 FROM buddies
EXCEPT
SELECT friend2,friend1 FROM buddies

Unfolding the or into a union may help performance too.

WITH buddies
AS ( SELECT friend1,friend2 FROM friends
        WHERE friend1=2
     UNION ALL
     SELECT friend1,friend2 FROM friends
        WHERE friend2=2
   )
SELECT friend1,friend2 FROM buddies
EXCEPT
SELECT friend2,friend1 FROM buddies


Huge Analysis Services log file: msmdsrv.log

You can't recycle the SSAS log in a similar manner to SQL Server. You need to do this manually by building a script and then scheduling it to run either inside of SQL Agent or Windows Task Scheduler.

Here is a sample Powershell script you can use:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

#Get a list of installed SSAS instances on this server that are running
#We need a service to be started in order to rename the log file
$SSASServices = Get-WmiObject -query "select * from win32_service where DisplayName LIKE 'SQL Server Analysis%' and State = 'Running'"

#for each installed instance, update the logfile name

ForEach ($sname in $SSASServices)

{

# Connect to the SSAS server
$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServer.Connect($sname.SystemName)

#gets current filename
$SSASErrorFileName = ($SSASServer.Serverproperties | where-Object {$_.Name -eq "Log\File"}).Value

#split out the .log extension
$SSASErrorName=$SSASErrorFileName.Split(".")[0]

#rename the errorlog, appending the current date
$ErrorLogName = "msmdsrv_{0}.log" -f (Get-Date -Format 'MM_dd_yyy_hh_mm_ss')

$SSASServer.ServerProperties['Log\File'].Value = $ErrorLogName
$SSASServer.ServerProperties['Log\File'].PendingValue = $ErrorLogName
$SSASServer.Update()

Restart-Service $SSASServices.Name

}

I blogged about it at http://thomaslarock.com/2015/01/how-to-recycle-the-sql-server-analysis-services-msmdsrv-log-file/ as well.

HTH


Nothing is written to log in log_directory() for a PostgreSQL cluster

Ok, so I did not get the Log files to work. But instead I got the Windows event log to work.

In postgresql.conf I set these line uncommented with those options:

log_destination = 'eventlog'        # Valid values are combinations of
                    # stderr, csvlog, syslog, and eventlog,
                    # depending on platform.  csvlog
                    # requires logging_collector to be on.

# This is only relevant when logging to eventlog (win32):
# (change requires restart)
event_source = 'PostgreSQL'

log_statement = 'all'    # none, ddl, mod, all

To register The Event log to Windows, I followed these instructions
I entered this command: REGSVR32 "C:\Program Files\PostgreSQL\11\lib\pgevent.dll" and a pop-up dialog confirmed that the pgevent.dll was registerd to REGSVR32
Then I opened Windows Event Viewer
On the right hand side, I chose Create custom view and got this pop-up dialog. I just filtered by the event source.

Then i get a neat list of events.


What is the best way to check for matching sets of rows?

Generally, inner join on a key

  Probability of of the shuffling being the exact same?

A big number as described in a few places

1 in 806,581,751,709,438,785,716,606,368,564,037,669,752,895,054,408,832,778,240,000,000,000

  What would be the fastest way to shuffle a deck of cards the same way
  twice?

One way would be to just use a single table. I use a tally table for the initial insert to speed that up, and also use two CTE's for the shuffle. Is it the fastest? Not sure, but it's pretty quick!

drop table deck;
create table deck ([card] int primary key, pos int);

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
cteTally(N) AS
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E2
    )

insert into deck (card,pos)
select   card = N
        ,pos = rank() over (order by newid())
from cteTally where N < 53;

;with shuffle1 as(
select card
       ,pos =rank() over (order by newid())
from deck),

shuffle2 as(
select card
       ,pos =rank() over (order by newid())
from deck)

select s1.card, s1.pos as s1, s2.pos as s2
from shuffle1 s1
inner join shuffle2 s2 on s1.card = s2.card
where s1.pos = s2.pos

Takes about 4 milliseconds on my machine for the initial and follow on shuffles, on average.

STATISTICS TIME FOR ENTIRE SCRIPT

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 2 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 4 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(52 row(s) affected)
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 4 ms.

(1 row(s) affected)

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


Risks when running a MySQL Galera Cluster in production without binlogs?

As per as best practices for setting up MySQL Galera cluster , it is compulsory to enable binary logs and binlog format as ROW.

Not sure why you are interested in disabling binlogs , few are the disadvantages:

unable to debug queries
unable to setup a replica server if required.


Finding the total sum of salary for person who worked at least two years

Try this:

SELECT name AS [Name of Employee],
    SUM(salary) AS [Salary Earned]
FROM Employee
GROUP BY name
HAVING MAX(year) > 1;

This will exclude those records whose maximum "year" having is 1, basically those who have not been employed for more than 1 year.


Do SQL Server stored procedures cache data results?

Query results are not cached

However, the source table and index data and metadata will be cached after the 1st use (subject to continued use, load and memory pressure though)

That is, the results of a query will be evaluated every execution but the tables(s) (and any indexes etc) used by the query will most likely be in memory already.

The compiled execution plan will be cached which is where the confusion comes from I suspect


Efficient way to write query for multiple table which depends on column value of a table in mysql?

First of all, append a computed column, say A4Key, set to SUBSTRING(A4, 4, 3).

This column shall be kept up-to-date either by your application code or by triggers, such as:

CREATE TRIGGER ... BEFORE UPDATE ...
...
    SET NEW.A4Key = SUBSTRING(NEW.A4, 4, 3)
...

CREATE TRIGGER ... BEFORE INSERT ...
...
    SET NEW.A4Key = SUBSTRING(NEW.A4, 4, 3)
...

first solution

Then, rewrite your query to:

SELECT * FROM A
WHERE A4Key IN('023', '009', '011', '013', '015', '017', '019', '021')
  AND A3 NOT IN(SELECT Z FROM B)
-- -------
UNION ALL
-- -------
SELECT * FROM A
WHERE A4Key IN('006', '024', '028', '031')
  AND A3 NOT IN(SELECT Z FROM C)
-- -------
UNION ALL
-- -------
SELECT * FROM A
WHERE A4Key IN('004', '025')
  AND A3 NOT IN(SELECT Z FROM D)

Now, evaluate your query plans to devise the best indexes.

second solution

Create a table such as:

CREATE TABLE A4Exceptions(
  A4Key VARCHAR(3),
  Z     `whichever_type_of_Z`
)

With initial data set by:

INSERT INTO A4Exceptions SELECT '023', Z FROM B
INSERT INTO A4Exceptions SELECT '009', Z FROM B
...
INSERT INTO A4Exceptions SELECT '006', Z FROM C
...

And keep it up-to-date with the changing (?) data from B, C and D.

Done that, now you may, with blazing performance (after creating some indexes):

SELECT *
FROM A
WHERE NOT EXISTS(
  SELECT *
  FROM A4Exceptions exc
  WHERE exc.A4Key = A.A4Key
    AND ext.Z     = A.A3
)


How can I improve performance on a 3 table query?

I see joins to v1 on start_dttm and end_dttm but dont see it in the view or the table listed.

Looking at your definition of your view, are you sure this is an issue with the join, or does the view itself have issues?

There are a few problems, and I will list them in order my brain saw them:

, DATEDIFF(d,GETDATE()+(14-(select DayofPayPeriod from Table_Calendar where date = CONVERT(date,GETDATE()))),date) day_offset
, CASE
    when DATEDIFF(d,GETDATE()+(14-(select DayofPayPeriod from Table_Calendar where date = CONVERT(date,GETDATE()))),date) < 0
    then '-'
    else '+'
    end + right('0000'+ REPLACE(DATEDIFF(d,GETDATE()+(14-(select DayofPayPeriod from Table_Calendar where date = CONVERT(date,GETDATE()))),date),'-',''),4)
    day_offset_label

These sections of the query indicate you might have RBAR behavior, eg the queries in the parenthesis may be running for each row that you need to return, as you add more data this will take longer and longer.

Of the columns you use that I can see:

FLOOR(((CONVERT(float,(c1.[Date]-GETDATE()))+(14-c1.[DayofPayPeriod]))/14))

Uses a calculation which is not SARGable in anyway, and so that -1 comparison would need to be calculated for each row as well.

This may be a case where the execution plan's estimates vs actual rows would be off, you may want to download a tool such as SQL Sentry Plan Explorer (dont need to buy the pro version to easily compare things like this http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view) which will highlight problem areas.

From what I can see of the plan (not much) it is giving me the impression that the planner cannot produce a useful plan and causing you to have a bad time.

My recommendations:

Compare like types so that you can use an index, or worst case make some sort of materialized view if you cant. See if you can unwrap some of those calculations and use less human readable/nice numbers to evaluate date offsets.

You may need to add an index or two to make this fly.

Consider using a numbers table to calculate offsets or values needed for far in the future, Why are numbers tables "invaluable"? so that you can use the power of the index to query this instead of churn your CPU each time you run this query.
Try to query some of this information in a SARGable fashion and then do the gross formatting work on a temp table or something that is your "final set", if this is still to slow you may want to consider doing this in some other programming paradigm.


Database structure for hierarchical quiz?

In relational database, you should create a table called Questions which should contain following fields:

ID
QuestionString
QuestionType (Master, Individual, sub-question)
TrueCase (here you should store the ID of the next question. Since
we'll store all questions in this table, therefore each questions
shall have unique ID)
FalseCase (ID of the question to ask if answer is not correct. If ID
is null, then you should exit)

Hope it helps.


Combining multiple-row data into a single row

Simple Group by will work for your expected output

select
columnid,
Number,
max(Value1) as value1, max(User1)as user1 , max(Value2) as value2,  max(User2) as user2,max(Value3) as value3,max(User3) as user3
from
table
group by
columnid,
number


You cannot get a single MAX value for end_date while you're grouping on lines_0_description because each billing record will, presumably, have a different description producing a different distinct group and different max end_date values.

You can use a CTE to extract the most recent billing record per customer then join that to your main SELECT to produce the result set you want. Something like below should work (disclaimer: I've guessed at some column names, e.g. _id in the billing table, as I don't know the schema)

;WITH BillingRecords AS (
  SELECT MAX(_id) AS billing_id,
    customer_$id
  FROM billing
  WHERE (billing.lines_0_description like '%package1%'
       or
       billing.lines_0_description like '%package2%'
       OR
       billing.lines_0_description like'%package3%')
  GROUP BY customer_$id
)

  select customer.addresses_0_firstname AS AdminFName,
       customer.addresses_0_lastname AS AdminLName,
       customer.addresses_0_organisation AS CompanyName,
       customer.addresses_0_address1 AS Address1,
       customer.addresses_0_address2 AS Address2,
       customer.addresses_0_city AS City,
       customer.addresses_0_postcode AS Zip,
       customer.addresses_0_country AS CountryID,
       customer.addresses_0_email AS Email,
       (CASE WHEN billing.lines_0_description like '%Package0%'
             THEN 'False'
             ELSE 'true' END) AS Reseller,
       billing.end_date
from customer
INNER JOIN BillingRecords br ON br.customer_$id = customer._id
INNER JOIN billing on billing._id = br.billing_id
where customer.name='example'
  and (billing.lines_0_description like '%package1%'
       or
       billing.lines_0_description like '%package2%'
       OR
       billing.lines_0_description like'%package3%')
group by customer.addresses_0_firstname,
         customer.addresses_0_lastname,
         customer.addresses_0_organisation,
         customer.addresses_0_address1,
         customer.addresses_0_address2,
         customer.addresses_0_city,
         customer.addresses_0_postcode,
         customer.addresses_0_country,
         customer.addresses_0_email


SELECT with equal chance between same IDs - MySQL

To select a random id with equal chances:

SELECT DISTINCT id
FROM tableX
ORDER BY RAND()
LIMIT 1 ;

You can combine the above in a subquery (derived table) to find a single row:

SELECT t.*
FROM tableX AS t
  JOIN
  ( SELECT DISTINCT id
    FROM tableX
    ORDER BY RAND()
    LIMIT 1
  ) AS i
    ON i.id = t.id
ORDER BY RAND()
LIMIT 1 ;


Updating a set of rows in a table with values from a different set of rows. Both sets are from the same table

Assuming that all rows in 2018 have a matching one in 2017 (with same values in Customer and Invoice Line) or you want to update only the matching rows, then you only need an UPDATE, not MERGE:

UPDATE upd
SET Amount  = old.Amount,
    Product = old.Product
FROM dbo.Invoices AS upd
    JOIN dbo.Invoices AS old
    ON  upd.Customer       = old.Customer
    AND upd.[Invoice Line] = old.[Invoice Line]
WHERE upd.Year = 2018
  AND old.Year = 2017 ;

You may want to add AND upd.Amount = 0 as a safeguard so not to update good data but only rows with 0.


IIS takes longer to run queries than SMSS

Usually this means that SQL is using cashed execution plan that just does not work any more. From SMSS it is recompiled and uses fresh execution plan. Try to force some index in queries, or rebuild indexes ? Definitely check execution plan for both queries.


Prometheus is a time-series database. The question is, do you need more data types and a stronger query language? What type of data is saved for location? Geo coordinates?

It also depends on your availability requirements. Both InfluxDB and Prometheus are single node. InfluxDB supports clustering only if you purchase the enterprise version.

For Prometheus, you need multiple instances to ensure high availability and they will be scraping the same data in duplicate. If your data requirements are high, you may have to look into long term remote storage. https://prometheus.io/docs/prometheus/latest/storage/#remote-storage-integrations.

We have developed a new serverless time-series database as a service called Hyprcubd. Full disclosure, I’m the founder.  It was architected with IoT in mind. It supports multiple data types, long term storage, and high availability. https://www.hyprcubd.com


0) You have FRA configured, but it is not your archivelog destination.

Setting log_archive_dest_N to the FRA directory is not the same as actually setting the FRA as an archivelog destination.

The configuration you most likely have:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /oradata/flash_recovery_area
db_recovery_file_dest_size           big integer 20G
log_archive_dest_1                   string      location=/oradata/flash_recovery_area/qrec/archivelog

The proper configuration would be:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /oradata/flash_recovery_area
db_recovery_file_dest_size           big integer 20G
log_archive_dest_1                   string      location=use_db_recovery_file_dest

The naming of archivelog files also confirms this, as archivelogs in the FRA are use OMF format, and they are actually placed in seperate directories (named based on the date) each day.

So instead of this:

/oradata/flash_recovery_area/qrec/archivelog/log_1_1056_965658004.arc

The name of your archivelog file would be:

/oradata/flash_recovery_area/qrec/archivelog/2018_02_22/o1_mf_....arc

1) This is typically caused by inappropriate configuration of the control_file_record_keep_time parameter combined with a lacking backup method. The default value 7 means that records (of archivelogs or backups) are kept for 7 days, after that, they can be reused. If you do not backup/delete your archivelogs for 7 days, the database may reuse the controlfile records pointing to them, basically the database "forgets" these entries and leaves them in the filesystem.

You should regulary backup and delete your archivelogs and set control_file_record_keep_time appropriately based on your backup strategy.

If you use user-managed backups instead of RMAN, you need to take care of deleting these logs manually. RMAN can backup and delete these files in a single command.

2) Depends on your backup requirements.

If you use user-managed backups, make sure you have backups of these files, then delete them.

If you use RMAN, you can use catalog start with '/oradata/flash_recovery_area/qrec/archivelog/';, then use RMAN to backup and delete them.

If you do not need any backup of these files, simply delete them.


Postgres Can't Add Foreign Key Constraint

about how long should it take

It depends on the amount of data, your indexes, and the speed of your IO sub-system (the latter particularly if supporting indexes aren't in place).

As you already have 220 million rows in the child table, it must check each of them against the parent table to make sure they are all valid values. Without a useful index on events.visitor this will mean scanning the whole table.

Note that creating a foreign key does not automatically create a supporting index, because depending on your access patterns such an index may not be needed so would be a waste of space.

  What's the fastest way to do this,

You can tell the DB not to validate the foreign key constraint for existing rows by adding NOT VALID (see the documentation at https://www.postgresql.org/docs/current/sql-altertable.html for the details and caveats associated with this). That is the fastest way but can lead to you having rows that are invalid that you do not know about until they cause a problem.
(NOT VALID is postgres specific syntax, other DBs that offer the option may name it differently, for instance with MS SQL Server the equivalent is WITH NOCHECK)

The second fastest method is to make sure there is an index on that column before defining the foreign key, though creating that index will itself take a notable amount of time on a table of that many rows.

  I tried again and it ended up completing in around half an hour. Not sure what was going on before.

It may be that at the point of the later attempt much of the table was in memory after being read during the earlier aborted create, so scanning to validate existing rows was faster second time around due to fewer disk reads being needed.


TOP 1 without ORDER BY could not be guaranteed to return the same results even if the output of STRING_SPLIT() were guaranteed (which it most definitely is not). While in real-world usage you may find it hard to come up with a counter-example where the data is not returned in order, this is a terrible kind of thing to do. Trusting that something always works because you've never seen it break is like assuming that if you put a deer sign on the highway that is the only place you'll ever see a deer.
However, let's look at another way we could solve this. Why don't we locate the position of each individual string in the overall list:
DECLARE
  @tags nvarchar(400) = N'clothing,road,touring,bike',
  @c nchar(1) = N',';

SELECT value, CHARINDEX(@c + value + @c, @c + @tags + @c)
  -- we surround the value and the string with leading/trailing ,
  -- so that cloth isn't a false positive for clothing
  FROM STRING_SPLIT(@tags, ',') AS t;

Output here is:

Do you think we could use this to determine the first element in the list? Of course! Let's try again:
DECLARE
  @tags nvarchar(400) = N'clothing,road,touring,bike',
  @first nvarchar(100),
  @c nchar(1) = N',';

;WITH t AS
(
  SELECT value, idx = CHARINDEX(@c + value + @c, @c + @tags + @c)
  FROM STRING_SPLIT(@tags, ',')
)
SELECT TOP (1) @first = value FROM t ORDER BY idx;

PRINT @first;

Output:
clothing

You could use the same logic to find the last element, just change the ORDER BY idx to ORDER BY idx DESC. In fact, you could use this logic to return the nth string in the list:
DECLARE
  @tags nvarchar(400) = N'clothing,road,touring,bike',
  @c nchar(1) = N',',
  @nth tinyint;

SET @nth = 3;

;WITH t AS
(
  SELECT value, idx = ROW_NUMBER() OVER
    (ORDER BY CHARINDEX(@c + value + @c, @c + @tags + @c))
  FROM STRING_SPLIT(@tags, ',')
)
SELECT * FROM t WHERE idx = @nth;

Result:
touring

As a disclaimer, if you have duplicates, that's going to mess things up, because the index value will always represent the first appearance of that value in the string. You can switch from ROW_NUMBER() to DENSE_RANK() but that won't solve for all cases. You can de-dupe the string first (I talk about some edge cases here).


How to get random samples from a table? I don't want repeated records between samples

TABLESAMPLE can't provide the mutually exclusive nature between samples that you need even with its REPEATABLE seed.  So I would consider rolling your own, using something like NTILE.  eg assuming your table has a primary key and you want 10 samples, create a table which contains the sample Id and the primary key of the table.  Use it as a 'master' to create your samples from, then you're guaranteed they will not overlap.  Here's a simple example:

-- Create your sample master table; I'm using a temp table here
-- but you could use a permanent one
SELECT NTILE(10) OVER( ORDER BY NEWID() ) sampleId, yourPrimaryKey
INTO #tmp
FROM dbo.yourTable

-- Create the sample, eg sample 3 of 10
SELECT m.*
FROM dbo.yourTable m
WHERE EXISTS
    (
    SELECT *
    FROM #tmp t
    WHERE t.sampleId = 3
      AND m.yourPrimaryKey = t.yourPrimaryKey
    )


How to reduce LOG file after switching from full recovery mode to simple mode?

You need to perform a shrink operation on the log file.

You can follow the instructions there.

PS: Don't forget to switch back to FULL Mode.


How to view the full, flattened query underlying a Postgresql view?

Not in PostgreSQL at this time. PostgreSQL doesn't actually build a single giant SQL statement; instead, it uses the plan tree of the view(s) and inserts that into the top level query's plan tree. To turn that back into SQL would require a deparser, something PostgreSQL doesn't have a the moment.

You can kind-of do-it-yourself by replacing references to the view with the text of the view query, but you need to understand that the effect isn't exactly the same, particularly where there are multiple references to the view.

You're best off using explain and/or explain analyze to observe what the query does.


max_fsm_pages was eliminated in version 8.4.  Free space tracking has gotten a lot smarter and more automatic.

Performance of deletes is going to depend on a lot of things.

If there are any foreign keys which reference this table, you should check whether there is an index on the referencing column(s).  Without that, each DELETE will cause a scan of the referencing table, to check that you're not causing any rows to be orphaned.
Like other database modifications, it is generally more efficient to group actions into larger transactions where reasonable, since there is overhead to each database transaction.
If you do have many transactions, you might want to consider setting synchronous_commit to off on the connection you're using for that work.
If you are deleting a significant fraction of the table, and you can do this during some maintenance window, you might consider dropping indexes not needed to find the rows to be deleted, and create them again after the mass delete.


To your data set, I added a row number (ROW_NUMBER), and this field is divided by 5 (you wanted 5 columns in the output) and hold the rest and the hole number of this division in fields inRows , inColumns. Base on this, will do the pivot.

There is a special case when you divide by 5 and have rest = 0 ; for this I use case...

,RN / 5 + CASE WHEN RN % 5  = 0 THEN -1   ELSE 0  END AS inRows

,CASE WHEN RN % 5  = 0 THEN 5 ELSE RN % 5 END AS inCols

;WITH myCTE AS
(
SELECT
  Title,StartOfShowing
  ,ROW_NUMBER() OVER(PARTITION BY Title ORDER BY StartOfShowing ASC) AS RN
FROM
  tblResults
)
, myPivotSource AS
(
SELECT Title,StartOfShowing
   --,RN
   --,(RN - 1 )/ 5  AS inRows
   ,RN / 5 + CASE WHEN RN % 5  = 0 THEN -1   ELSE 0  END AS inRows
   ,CASE WHEN RN % 5  = 0 THEN 5 ELSE RN % 5 END AS inCols
FROM
   myCTE
)

SELECT Title
    , CONVERT(varchar(10),[1],101) As dateofShowing
    , CONVERT(varchar(5),[1],108) AS [1]
    , CONVERT(varchar(5),[2],108) AS [2]
    , CONVERT(varchar(5),[3],108) AS [3]
    , CONVERT(varchar(5),[4],108) AS [4]
    , CONVERT(varchar(5),[5],108) AS [5]
FROM
   myPivotSource AS PS
   PIVOT
   (
      MIN(StartOfShowing) FOR inCols IN ([1], [2], [3], [4], [5])
   )PV
ORDER BY Title, inRows

output of it:

Title                               dateofShowing   1       2       3       4       5
A Quiet Place                       04/30/2018      12:40   15:05   17:30   19:55   22:20
Avengers : Infinity War             04/30/2018      11:10   11:30   12:00   13:00   13:20
Avengers : Infinity War             04/30/2018      14:00   14:20   14:40   15:10   16:10
Avengers : Infinity War             04/30/2018      16:30   17:10   17:30   17:50   18:20
Avengers : Infinity War             04/30/2018      19:20   19:40   20:20   20:40   21:00
Avengers : Infinity War             04/30/2018      21:30   22:50   null    null    null
Avengers : Infinity War  ATMOS      04/30/2018      12:40   15:50   19:00   22:10   null
Avengers : Infinity War  PLATINUM   04/30/2018      14:40   17:50   21:00   23:30   null
Love Simon                          04/30/2018      14:25   19:15   null    null    null
Rampage                             04/30/2018      16:55   21:45   null    null    null

dbfiddle


Weird tables with numbers and underscores in PostgreSQL execution plans

Those are not the table names, but aliases. You can use them to make a query more readable:
SELECT some.col1, another.col2
FROM some_really_long_table_name AS some
   JOIN another_terrible_table_name AS another
      USING (col3)
WHERE some.col4 = 42;

But of course you can also abuse aliases to obfuscate and inflate a query, as some object-relational mappers do.


No, there is no functionality like that in sp_BlitzIndex.


Passing comma separated values to parameters in stored procedure

There are several ways of doing it. Changing the data model may also be a better option if you can do it.
1. Comma separated parameters
If you want to stick with the comma separated string, you can find many string split functions online using CLR, CTE, XML, ... I am not going to put them all here and I won't benchmark them. But you must know that they all have their own issues. For more information you can look at this post from Aaron Bertrand: Split strings the right way – or the next best way
Sample query (using XML conversion):
DECLARE @c1 nvarchar(100) = N'10,15,13,14';
DECLARE @delimiter nvarchar(1) = N',';
SELECT v1 = LTRIM(RTRIM(vals.node.value('(./text())[1]', 'nvarchar(4000)')))
FROM (
    SELECT x = CAST('<root><data>' + REPLACE(@c1, @delimiter, '</data><data>') + '</data></root>' AS XML).query('.')
) v
CROSS APPLY x.nodes('/root/data') vals(node);

Output:
v1
10
15
13
14

Main query:
The idea is to convert it to a table. It can then be used in a correlated sub query:
DECLARE @c1 nvarchar(100) = N'10,15,13,14';
DECLARE @c2 nvarchar(100) = N'C21, B21';
DECLARE @delimiter nvarchar(1) = N',';

SELECT *
FROM FinalStatus f
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT CAST('<root><data>' + REPLACE(@c1, @delimiter, '</data><data>') + '</data></root>' AS XML) AS x
    )t
    CROSS APPLY x.nodes('/root/data') vals(node)
    WHERE  LTRIM(RTRIM(vals.node.value('.[1]', 'nvarchar(4000)'))) = f.Col1
)
OR EXISTS (
    SELECT 1
    FROM (
        SELECT CAST('<root><data>' + REPLACE(@c2, @delimiter, '</data><data>') + '</data></root>' AS XML) AS x
    )t
    CROSS APPLY x.nodes('/root/data') vals(node)
    WHERE  LTRIM(RTRIM(vals.node.value('.[1]', 'nvarchar(4000)'))) = f.Col2
);

Only the WHERE clause has to be updated in your procedure. Parameters remains of varchar type with comma separated values.
Output:
ID  Col1    Col2    Col3        LastUpdatedDate
1   10      ABC21   Msg1        2016-07-20 09:06:19.380 => match c1
2   10      ABC21   Msg2        2016-07-20 09:06:19.390 => match c1
3   11      C21     Some Msg1   2016-07-20 09:06:19.390 => match c2

2. XML parameter(s)
If you can change the parameter' types, the XML data type can be used. It can be easily deserialized by the procedure and the query.
This query is pretty similar to the previous one. However it gives a better control over invalid values and special characters since there is no conversion or search and replace.
DECLARE @c xml = N'
    <root>
        <c1>10</c1><c1>15</c1><c1>13</c1><c1>14</c1>
        <c2>C21</c2><c2>B21</c2>
    </root>';
DECLARE @delimiter nvarchar(1) = N',';

SELECT *
FROM FinalStatus f
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT x = @c.query('.')
    ) v
    CROSS APPLY x.nodes('/root/c1') val1(node)
    WHERE  LTRIM(RTRIM(val1.node.value('.[1]', 'nvarchar(4000)'))) = f.Col1
)
OR EXISTS (
    SELECT 1
    FROM (
        SELECT x = @c.query('.')
    ) v
    CROSS APPLY x.nodes('/root/c2') val1(node)
    WHERE  LTRIM(RTRIM(val1.node.value('.[1]', 'nvarchar(4000)'))) = f.Col2
);

The parameter' types have to be changed to xml. I used only one variable with c1 and c2 nodes but 1 variable for each node (c1, c2, ...) and different node names can also be used:
DECLARE @c1 xml = N'<root><data>10</data><data>15</data><data>13</data><data>14</data></root>';
DECLARE @c2 xml = N'<root><data>C21</data><data>B21</data></root>';
DECLARE @c3 xml = N'...';

The correct path and variables must be updated in the nodes part of the CROSS APPLY.
Using .Net, Powershell or other languages, an array or other types can easily be converted to xml and used as a parameter of the procedure.
...
3. User-Defined Table Types
Another option would be to create a Table Value Type that can be used by the Stored Procedure parameters.
Table Type
CREATE TYPE [dbo].[TableTypeCols] AS TABLE
(
    [col] varchar(15)
);

Stored Procedure with Table Type parameter(s)
You then update the Stored Procedure using this newly created type:
CREATE OR ALTER PROCEDURE [dbo].[FindResult]
    @c1 [dbo].[TableTypeCols] READONLY
    , @c2 [dbo].[TableTypeCols] READONLY
AS
    SELECT * -- fs.[...], fs.[...], ...
    FROM [dbo].[FinalStatus] fs
    WHERE
        EXISTS (
            SELECT 1 FROM @c1 c1 WHERE c1.col = fs.Col1
        )
        OR EXISTS (
            SELECT 1 FROM @c2 c2 WHERE c2.col = fs.Col2
        );
GO;

READONLY is mandatory in the parameter declaration.
Stored Procedure call with Table Type parameter(s)
With SQL, you can call it the same way using a table variable:
DECLARE @tc1 [dbo].[TableTypeCols];
DECLARE @tc2 [dbo].[TableTypeCols];

INSERT INTO @tc1(col) VALUES('10'), ('15'), ('13'), ('14');
INSERT INTO @tc2(col) VALUES('C21'), ('B21');

EXEC dbo.FindResult @c1 = @tc1, @c2 = @tc2;

Output:
ID  Col1    Col2    Col3        LastUpdatedDate
1   10      ABC21   Msg1        2016-07-20 09:06:19.380 => match c1
2   10      ABC21   Msg2        2016-07-20 09:06:19.390 => match c1
3   11      C21     Some Msg1   2016-07-20 09:06:19.390 => match c2

Please note that if you are planning on using more than a couple rows for each parameters, you should run some performance test with real data and make sure it works properly on your system.
.Net C# Call
The procedure can the be call from you .Net code
// Create & Fill Parameter 1
DataTable dt1 = new DataTable();
dt1.Columns.Add("col", typeof (string));
DataRow row = dt1.NewRow();
row["col"] = ("10");
dt1.Rows.Add(row);
/*
    ... add more row ...
*/

// Create & Fill Parameter 2
DataTable dt2 = new DataTable();
dt2.Columns.Add("col", typeof (string));
DataRow row = dt2.NewRow();
row["col"] = ("C21");
dt2.Rows.Add(row);
/*
    ... add more row ...
*/

// Output dataset
DataSet ds = new DataSet("SQLDatabase");

using (SqlConnection conn = new SqlConnection(...))
{
    // Stored Procedure with table parameters
    SqlCommand sqlComm = new SqlCommand("dbo.FindResult", conn);
    sqlComm.CommandType = CommandType.StoredProcedure;

    // Parameter 1
    SqlParameter param = new SqlParameter("@c1", SqlDbType.Structured)
    {
        TypeName = "dbo.TableTypeCols",
        Value = dt1
    };
    sqlComm.Parameters.Add(param);

    // Parameter 2
    SqlParameter param = new SqlParameter("@c2", SqlDbType.Structured)
    {
        TypeName = "dbo.TableTypeCols",
        Value = dt2
    };
    sqlComm.Parameters.Add(param);

    // Call Stored Procedure
    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = sqlComm;
    da.Fill(ds);
}

SqlDbType.Structured is mandatory.
Please note that I am not a .Net expert and there are other or better ways to write and use this code.
4. Json Type Parameters (Since SQL Server 2016)
With minimal changes, you can declare a NVARCHAR(MAX) parameters with json string.
json parameters
DECLARE @C1 NVARCHAR(MAX);
SET @jsonC1 =
N'[
    { "col" : "10"}, { "col" : "15"}, { "col" : "13"}, { "col" : "14"}
]';
DECLARE @C2 NVARCHAR(MAX);
SET @jsonC2 =
N'[
    { "col" : "C21"}, { "col" : "B21"}
]';

Query
SELECT *
FROM dbo.FinalStatus f
WHERE
EXISTS (
    SELECT 1 FROM OPENJSON(@C1) WITH(col nvarchar(15)) AS c1 WHERE c1.col = f.Col1
)
OR
EXISTS (
    SELECT 1 FROM OPENJSON(@C2) WITH(col nvarchar(15)) AS c2 WHERE c2.col = f.Col2
);

Please note that OPENJSON requires SQL Server 2016.


Does sp_addtabletocontents Use Precomputed Partitions

Intriguing question... as long as you have an actual scenario to apply it to it wouldn't be too difficult to figure out.  Perform your BCP process, run sp_addtabletocontents and then before the subscriber syncs verify the existence of the records in the MSmerge_current_partition_mappings and MSmerge_past_partition_mappings tables.

According to this technet article that is where the partitioning data for each row that has been changed is stored post change/pre sync.  Look into the article for deeper information and the manner in which the table structures are organized within the process.  The link provided is for SQL 2008 but if the table structure is the same its a safe bet that the pre-compute logic is close enough to answer your question.

I am not an expert on precomputed partitions but it seems to me that it would have to take advantage of the precompute process (if enabled) otherwise it wouldn't sync these records at all.


What are the differences between these two database structures?

ER diagrams document business rules. They are a visualization of many subject-verb-object sentences. Take the classic shopping cart example. Some rules may be

A customer places one or more orders.
An order consists of one or more items.
Each item is a purchase of exactly one product.

The nouns become entities (boxes in the ERD) and the verbs relationships (ERD lines and arrows or crows feet).

Your two diagrams represent different sets of business rules. For example, on the left there is no link between roster and case. The diagram is stating that, in the particular situation you are modelling, your users consider rosters and cases to be completely independent; neither places any constraint whatsoever on the other. The right-hand diagram, however, has a relationship between them. This states a case is somehow linked to a roster. The relationship name will document what that link is.

Because they are linked the right-hand system will be able to answer a whole class of queries the left-hand one cannot e.g. "which cases were processed during this roster?"

The general steps to develop an ERD are to identify the individual pieces of information required (a case number, a person's name) and the entity types these represent. Document the rules that link them, but only the rules that are meaningful in the context of the problem you're trying to solve. Determine the questions you want the system to answer and ensure it can do so. Reduce the entities to their normal forms. These steps often happen in parallel and cyclically.

In summary, the two diagrams represent two very different sets of business rules and sets of questions that can be answered. Only you can know which better solves your current problem.


Why is there no data loss with mysql innodb_flush_log_at_trx_commit set to 0?

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

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

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

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

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

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

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


Can I have two `psql` sessions use the same database connection?

Is it possible to open two psql sessions that use the same database
  connection?

In theory it's possible through connection sharing. Instead of connecting directly to PostgreSQL, psql would connect to a middleware such as pgBouncer, which has the ability to reuse the same backend connection across its multiple clients.

  My goal is to show that a second user on the same connection can see
  uncommitted INSERTs,

TL;DR: forget that, run the SELECT in the same psql session than the INSERT.

From the point of view of PostgreSQL, there is no second user, it's the same user on the same connection, which is seeing the effects of its own not-yet-committed INSERTs.
Otherwise it would be a dirty read, which PostgreSQL documentation states as "Not possible" in any of the isolation levels.

A middleware routing queries from different connections from its clients to the same connection to the backend could produce the effect of a dirty read to its client.
In practice, your goal is out of reach with a normal pooler, because these tools actively forbird situations where a client would see uncommitted changes of another client. It's part of the effort to be transparent to users, to whom the results should be the same whether their connection is shared or not.

For example PgBouncer when using statement pooling, says:

  Statement pooling
      Most aggressive method. This is transaction pooling with a twist - multi-statement transactions are disallowed. This is meant to enforce
  "autocommit" mode on client, mostly targeted for PL/Proxy.

The mentioned demo would need statement pooling in a multi-statement transaction.
So in order to make it work, you'd need to hack pgBouncer to bypass that check. That's technically possible, but in the end it would demonstrate a setup that never exists in practice. In this sense, it has more potential to confuse people rather than to help them understanding how isolation works.


How can I filter my query on a left joined table, if I don't know the exact value I'm searching?

I don't see any way in the immediate query to assist performance. You have indices on the requested columns and it has to do a full scan to figure out all of the data.

There are two solutions I see, both take architecture changes.

Full Text Search
Processing and mapping tables with foreign key implementation.

If you are able to create a mapping table and a key column on the tblAssetIdentifierVersion and tblAssetIdentifierSnapshot table, you could have a process that runs that helps map to the table. This causes processing time that would typically be done in the select to be done in the update as it does not resolve the like operator in the where clause. You'll have to weigh whether this is a good option for your database or Full Text Search is a better idea or if it's best to be left alone.

Example of the mapping table:

DECLARE @m_people TABLE
    (
        M_Job_ID INT IDENTITY(1 , 1)
        ,M_JOB VARCHAR(255)
    )

DECLARE @people TABLE
    (
        Name VARCHAR(255)
        ,JOB VARCHAR(255)
        ,Job_ID INT
    )

INSERT  INTO @people
        (Name , JOB , Job_ID)
VALUES
        ('Timmy' , 'Data Slave' , NULL)

INSERT  INTO @people
        (Name , JOB , Job_ID)
VALUES
        ('Joann' , 'Manager' , NULL)

INSERT  INTO @m_people
        (M_JOB)
VALUES
        ('Data Slave')

INSERT  INTO @m_people
        (M_JOB)
VALUES
        ('Manager')

UPDATE  @people
SET Job_ID = M_job_id
FROM @m_people
WHERE JOB LIKE '%' + m_job + '%'

SELECT * FROM @people
SELECT * FROM @m_people

Note: Due to variable tables, the objects due not exist in the sys.objects table so there is no obvious foreign key constraint declares. The FK would be Job_ID and M_Job_ID if that is not obvious.


I suggest a couple of important improvements for dealing with a million rows:

SELECT br.duration
FROM  (
   VALUES
      ('[2016-09-06 03:45:00+00, 2016-09-06 14:45:00+00)'::tstzrange)
    , ('[2016-09-06 14:45:00+00, 2016-09-06 15:45:00+00)')
      -- more items
   ) br(duration)
WHERE EXISTS (
   SELECT FROM booking
   WHERE  tstzrange(ts_start, ts_end) && br.duration
   );

While providing your list of values with the needlessly verbose and expensive form SELECT ... UNION ..., make that UNION ALL, or Postgres will waste time trying to fold duplicates. And you would only need to declare column name(s) and data type(s) for the first SELECT of the UNION query.
But a VALUES expression is simpler and faster. Or provide an array tstzrange[] and use unnest():

Optimizing a Postgres query with a large IN

The query you have would return one row for every overlapping row in booking, while you probably want each overlapping value from the list once, most likely. You could add DISTINCT or GROUP BY to get unique rows, but that would still be a waste of time. An EXISTS semi-join is one of the much simpler and cheaper alternatives for your case: Each row from duration is returned exactly once if an overlapping entry is found and Postgres can stop looking further for this row.
The query would still be slow without index support. Create a functional GiST or SP-GiST index. The latter probably performing best:

CREATE INDEX booking_ts_range_idx on booking USING spgist (tstzrange(ts_start, ts_end));

Related:

Optimizing queries on a range of timestamps (two columns)
Native way of performing this hours of operation query in PostgreSQL
Help with adding a time filter to Postgres query
Perform this hours of operation query in PostgreSQL (incl. SP-GiST index example and benchmark comparing alternatives)


Get the value of the first item of JSON object

OK so the final query is :

WITH status_per_table AS (
SELECT
  changes,
  stmt_date :: DATE
FROM pgmemento.table_event_log el
  JOIN pgmemento.row_log r ON (r.event_id = el.id)
  JOIN pgmemento.transaction_log t ON (el.transaction_id = t.txid)
WHERE r.changes ?| (SELECT array((SELECT DISTINCT key
                                  FROM pgmemento.row_log, LATERAL jsonb_each_text(changes)
                                  WHERE key LIKE '%_stat'
                                  GROUP BY key)))
) SELECT
    value,
    stmt_date,
    count(value)
  FROM status_per_table, LATERAL jsonb_each_text(changes)
  GROUP BY value, stmt_date
  ORDER BY stmt_date DESC

Don't hesitate to post a  suggestion from a new post name...


Finding all users with access to a table in SQL server

I think similar question has already been asked and you may refer the answers.

In the below query, you may pass your table name and this would work for you:

/*
Security Audit Report
1) List all access provisioned to a sql user or windows user/group directly
2) List all access provisioned to a sql user or windows user/group through a database or application role
3) List all access provisioned to the public role

Columns Returned:
UserName        : SQL or Windows/Active Directory user cccount.  This could also be an Active Directory group.
UserType        : Value will be either 'SQL User' or 'Windows User'.  This reflects the type of user defined for the
                  SQL Server user account.
DatabaseUserName: Name of the associated user as defined in the database user account.  The database user may not be the
                  same as the server user.
Role            : The role name.  This will be null if the associated permissions to the object are defined at directly
                  on the user account, otherwise this will be the name of the role that the user is a member of.
PermissionType  : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT
                  DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ObjectType      : Type of object the user/role is assigned permissions on.  Examples could include USER_TABLE,
                  SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ObjectName      : Name of the object that the user/role is assigned permissions on.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ColumnName      : Name of the column of the object that the user/role is assigned permissions on. This value
                  is only populated if the object is a table, view or a table value function.
*/

--List all access provisioned to a sql user or windows user/group directly
SELECT
    [UserName] = CASE princ.[type]
                    WHEN 'S' THEN princ.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE princ.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,
    [DatabaseUserName] = princ.[name],
    [Role] = null,
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    --database user
    sys.database_principals princ
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col ON col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE
    princ.[type] in ('S','U')
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT
    [UserName] = CASE memberprinc.[type]
                    WHEN 'S' THEN memberprinc.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE memberprinc.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,
    [DatabaseUserName] = memberprinc.[name],
    [Role] = roleprinc.[name],
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    --Role/member associations
    sys.database_role_members members
JOIN
    --Roles
    sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
    --Role members (database users)
    sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT
    [UserName] = '{All Users}',
    [UserType] = '{All Users}',
    [DatabaseUserName] = '{All Users}',
    [Role] = roleprinc.[name],
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    --Roles
    sys.database_principals roleprinc
LEFT JOIN
    --Role permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
JOIN
    --All objects
    sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
    --Only roles
    roleprinc.[type] = 'R' AND
    --Only public role
    roleprinc.[name] = 'public' AND
    --Only objects of ours, not the MS objects
    obj.is_ms_shipped = 0
ORDER BY
    princ.[Name],
    OBJECT_NAME(perm.major_id),
    col.[name],
    perm.[permission_name],
    perm.[state_desc],
    obj.type_desc--perm.[class_desc]

You may refer above from this question.

Alternatively you may use system defined views for the same:

select  princ.name
,       princ.type_desc
,       perm.permission_name
,       perm.state_desc
,       perm.class_desc
,       object_name(perm.major_id)
from    sys.database_principals princ
left join
        sys.database_permissions perm
on      perm.grantee_principal_id = princ.principal_id

This one is more similar to your requirement.

Hope above helps.


You can't drop the msdb database as stated in the docs (emphasis mine):

  Restrictions

  The following operations cannot be performed on the msdb database:

  Changing collation. The default collation is the server collation.
  Dropping the database.
  Dropping the guest user from the database.
  Enabling change data capture.
  Participating in database mirroring.
  Removing the primary filegroup, primary data file, or log file.
  Renaming the database or primary filegroup.
  Setting the database to OFFLINE.
  Setting the primary filegroup to READ_ONLY.

Tampering with system databases isn't a good idea usually. You should check where your space problem is and consider expanding your drives.

To check where your size problem is:

Check table and index sizes inside msdb database using this query:

USE msdb
GO

SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
    sys.tables t
INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    TotalSpaceMB DESC

If sysjobhistory pops up in space then review your current job history rentention policy and make sure that your jobs' schedules are on check and not triggering more often than they need.
Check database data and log file sizes with this query:

;with fs
as
(
    select database_id, type, size * 8.0 / 1024 size
    from sys.master_files
)
select
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeInMB,
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeInMB
from
    sys.databases db
where
    db.name = 'msdb'

If the log file size is high you need to find out which operation made it increase it's size and troubleshoot it. Shrinking the file will free some space but won't solve the underlying problem.


PostgreSQL: Cast any boolean field to integer in SELECT * query

PostgreSQL doesn't have a display type for output, so the question is just somewhat kind of silly. Using libpq you have two options

Binary
Text

You don't get to customize how it delivers text back. And, it wouldn't make much sense either way. The library exists for people to build interfaces with, not for end users. Using C, if you're taking 1-byte for a boolean value, it's better to have t and f because 0 (the integer) and '0' the string are fundamentally different but share the same gylph "0" and description "zero". Now for psql I think you may have a valid feature request for that. Perhaps in the future you'll see something like

\pset numericbinary

And you're welcome to open a feature request on that.

You can see this hard coded in the source here


How do I conditionally select elements of an array?

I figured out your question by brute force. Please give more details next time. I still have no idea what count() is for. You want the min(), max() and mode() from an array. We can do that by splitting out the text value with string_to_array. Then we can unnest the array to get something we can run our aggregate functions on.

Note, mode is a ordered set aggregate. That's why it looks different.

From there the job is simple.

CREATE TABLE foo(v) AS
VALUES ('112,19.3,142,142,19.3,172,172,20.3,202,202,20.3,232,232,19.3,262,262,19.3,292');

SELECT
  min(x),
  mode() WITHIN GROUP (ORDER BY x),
  max(x)
FROM foo
CROSS JOIN LATERAL unnest(string_to_array(v, ',')) AS x;

 min | mode | max
-----+------+-----
 112 | 19.3 | 292
(1 row)

Or, if you want that in an array..

SELECT ARRAY[
  min(x),
  mode() WITHIN GROUP (ORDER BY x),
  max(x)
]
FROM foo
CROSS JOIN LATERAL unnest(string_to_array(v, ',')) AS x;

     array
----------------
 {112,19.3,292}
(1 row)


Insert to particular location in Oracle DB table?

The code you have inherited is broken - and it always has been broken. That re-factoring you'd like to avoid needs to be done. There is no alternative to an explicit order by to guarantee the sort order of a result set and there never has been.

Who knows if the code you inherited always returned rows in the order the original developer 'expected' or not.


Problems updating with inner join with multiple on statements

First, you have conditions duplicated in the ON and the WHERE clause. That is not needed, the condition are only needed once, preferably in the ON clause as they are used for the join of the two tables. But that is not what's causing the slow execution (and the lost connections).

The reasons for being slow are three:

you are joining a 63K with a 363K table without any index to help. That is the major issue.
the columns used in the join do not have same type, length, character set and collation. This is another major issue, worse than the first. Even if you add indexes, but keep the same types, the indexes will not be of much help. More seriously, it's a question if you can / are allowed to change the types or the applications depend on the existing types.
you are updating potentially the whole table. This is rather minor as the table is small, only 63K rows. We'll see if that is still a problem, after we fix the major issues.

The first thing that needs addressing is the types (+ lengths + charsets + collations) of the two columns. Assuming that you are allowed to change the types of the table order, you should change the types (both varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL) to match the types of the geo table (one varchar(50), the other char(2), both CHARSET=latin1, unknown/default collation.)

This will be tricky and perhaps error prone, so I suggest you first take a backup of the whole database, or just the table orders, before you attempt to change anything.

If I were you, I'd then first (before changing anything), check the data in these two columns and whether their maximum length is less than 50 and 2 respectively, to be sure that the truncation of the length will not change any valuable data.

I'd also check the collation of the cities_extended.city and cities_extended.state_code columns, it's probably the default latin1_swedish_ci.

(Of course these two steps above can be skipped - but only if you don't really care about the orders table and you can recreate it from scratch if needed.)

After everything is double checked, we can proceed with the actual changing of the types:

ALTER TABLE ProcurementPortal.orders
    MODIFY COLUMN oCity
        varchar(50)
        CHARSET 'latin1'
        -- COLLATE 'latin1_swedish_ci'
        DEFAULT NULL,
    MODIFY COLUMN oState
        char(2)
        CHARSET 'latin1'
        -- COLLATE 'latin1_swedish_ci'
        DEFAULT NULL ;

(the COLLATE 'latin1_swedish_ci' are probably not needed, the defaults will be used, so they are commented out.)

If that goes well and there are no errors, we can proceed with the final step, which is to add the indexes:

Add two indexes, one on each table:

ALTER TABLE ProcurementPortal.orders
   ADD INDEX state_city_ix
       (oState, oCity) ;

ALTER TABLE ProcurementPortal.cities_extended
   ADD INDEX state_city_ix
       (state_code, city) ;

After that (it will take a while but you only need to do this once), you can proceed with the update statement.

If it's still taking too long, I'd consider making the update in batches (of say 1K-5K rows per time). But with so small tables, I don't think you need that.


SQL session that is 'never end', always stay in a running status but does not return a result set (Until I kill it)

I would check the following

is the session you normally kill, waiting on something
is the session you normally kill, being blocked
is the session you normally kill, in a transaction that someone forgot to end?

you can use the below query to check the first 2. You may be able to check the third if the query gets picked up in the last column.

SELECT [spid]
--      ,[kpid]
      ,[blocked]
      ,[open_tran]
--      ,[waittype]
      ,[waittime]
      ,[lastwaittype]
      ,[waitresource]
      ,[name]
      ,CAST([cpu] AS NUMERIC) cpu
      ,[physical_io]
      ,[loginame]
      ,[hostname]
      ,[program_name]
--      ,[hostprocess]
      ,[cmd]
--      ,[nt_domain]
--      ,[nt_username]
--      ,[net_address]
      ,[net_library]
--      ,[uid]
      ,[memusage]
      ,[login_time]
      ,[last_batch]
--      ,[ecid]
      ,[status]
--      ,[sid]
--      ,[context_info]
      ,[sql_handle]
--      ,[stmt_start]
--      ,[stmt_end]
--      ,[request_id]
        ,[text]
FROM [master].[sys].[sysprocesses] p
JOIN SYS.databases d
    ON p.dbid = d.database_id
CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
--WHERE status = 'runnable'
--WHERE name = 'runnable'
--ORDER BY blocked DESC
--ORDER BY cpu DESC
ORDER BY waittime DESC


Modeling a scenario in which each Music Artist is either a Group or a Solo Performer

The part of the scenario that you are confused with can be modeled with a classic construct called supertype-subtype1 structure.
I will (1) introduce some pertinent preliminary ideas, (2) detail how I would delineate —at the conceptual level— the business context under consideration, and (3) provide additional related material —e.g., the corresponding logical-level representation via SQL-DDL declarations— as follows.
Introduction
A structure of this nature takes place when, in a given business environment, there is a cluster of entity types within which the supertype has one or more properties (or attributes) that are shared by the rest of the entity types in the cluster, i.e., the subtypes. Every subtype has, in turn, a particular set of properties that are applicable to itself only.
Supertype-subtype clusters can be of two kinds:

Exclusive. Comes about when an instance of the superentity type must always have one and only one subtype counterpart; therefore, the potential subtype occurrences in question are mutually exclusive. This is the kind that concerns to your scenario.
A typical case in which an exclusive supertype-subtype comes about is a business domain where both an Organization and a Person are considered Legal Parties, like in the situation deliberated in this series of posts.

Nonexclusive. Presents itself when a supertype instance may be complemented by multiple subtype occurrences, each of which is compelled to be of a different category.
An example of this kind of supertype-subtype is dealt with in these posts.

Notes: It is worth mentioning that supertype-subtype structures —being elements of a conceptual character— do not belong to a specific data management theoretical framework, be it relational, network or hierarchical —each of which offers particular structures to represent conceptual elements—.
It is also opportune to point out that although supertype-subtype clusters bear a certain resemblance to object-oriented application programming (OOP) inheritance and polymorphism, they are in fact distinct devices because they serve different purposes. In a database conceptual model —that must represent real world aspects— one deals with structural features in order to describe informational requirements, whereas in OOP polymorphism and inheritance, among other things, one (a) sketches and (b) implements computational and behavioural characteristics, aspects that decidedly belong to appplication program design and programming.
Apart from that, an individual OOP class —being an application program component—, does not necessarily have to “mirror” the structure of an individual entity type that belongs to the conceptual level of the database at hand. In this respect, an application programmer may typically create, e.g., one single class that “combines” all the properties of two (or more) different conceptual-level entity types, and such a class may as well include computed properties.
Using entity-relationship constructs to represent a conceptual model with supertype-subtype structures
You asked for an entity-relationship diagram (ERD for brevity) but, although being an extraordinary modeling platform, the original method —as introduced by Dr. Peter Pin-Shan Chen2— did not supply enough constructs to represent scenarios of the sort being discussed with the precision that a proper database conceptual model requires.
Consequently, it was necessary to make some extensions to said method, situation that yielded results in the development of an approach that assists in the creation of enhanced entity-relationship diagrams (EERDs) that, naturally, enriched the initial diagramming technique with new expressive characteristics. One of those characteristics is, precisely, the possibility of depicting supertype-subtype structures.
Modeling your context of interest
The illustration shown in Figure 1 is an EERD (using symbols similar to the ones proposed by Ramez A. Elmasri and Shamkant B. Navathe3, who refer to such structures as superclass/subclass) where I modeled the business domain you describe considering all the specifications. It is also available as a PDF that can be downloaded from Dropbox.
As you can see in the aforementioned diagram, both Group and SoloPerformer are displayed as exclusive subtypes of the Artist superentity type:

Diagram description
In order to start the description of the EERD, it is important to point out that your sentence

“an Artist must be either a Group or a SoloPerformer (but not both)”

is related to the disjointness and the completeness aspects of the supertype-subtype cluster at hand.
Disjointness
The disjointness feature is particularly important because it is right here where the “or part” that you mentioned comes into play, due to the fact that an Artist has to be either one subtype instance or the other, which I specified in the EERD through the small circle containing the letter “d”, a construct that receives the name of disjoint rule.
When a supertype may be supplemented by one or more of its possible subtypes, this point must be expressed by a small circle holding a label with the letter “o”, a symbol called overlap rule.
Discriminator property
Also within the scope of the disjointness factor of this supertype-subtype association, it is worth paying close attention to the Artist.Type property, since it carries out a very relevant task in this arrangement: it functions as the subtype discriminator. It is named in this way as it is the property that points out the exclusive kind of subtype with which a specific instance of an Artist relates to.
In the cases of nonexclusive clusters, the use of a discriminator property is needless, for a certain supertype can have multiple subtypes as complements (as brought up above).
Total specialization rule and completness
The requirement that stipulates that every Artist must always have a supplementary subtype instance has to do with the completness characteristic of this cluster. This is delineated by means of a total specialization rule, demonstrated via the double-line symbol connecting (a) the Artist supertype with (b) the disjoint rule construct.
Relating Groups with Solo Performers
Evaluating the sentences

“a Group is made up of one or more SoloPerformers”

and

“a SoloPerformer may be a member of many Groups or of no Group”,

one can recognize that both subtypes are involved in a many-to-many (M:N) association (or relationship), which I represented with the diamond-shaped box labeled as Group-SoloPerformer.
If implemented in a relational database as a base table, this component would be very useful to derive (i.e., to carry out the calculation of) the total Number of SoloPerformers that make up a concrete Group (one of the requirements that you specified).
The association between Solo Performers and Instruments
The stipulation

“a SoloPerformer […] may play one or more Instruments”

permits us inferring that, at the same time,

“an Instrument is played by zero, one or more SoloPerformers”.

Thus, this is another example of a M:N association, and I used the diamond-shaped figure designated SoloPerformer-Instrument to expose it.
Additional material
In order to expound the scope of supertype-subtype structures I am going to include two more resources, i.e.,

an IDEF1X4 diagram presented in Figure 2 (and you can download it from Dropbox as a PDF, as well) that illustrates the expressive capabilities of this sort of diagrams regarding the business domain at issue; and

the respective expository DDL logical structure that exemplifies how to manage the full scenario under discussion by virtue of a SQL database management system.

1. IDEF1X representation
The IDEF1X information modeling technique certainly offers the capability of portraying supertype-subtype structures, though with a limitation: it does not lend a visual mechanism to indicate whether a precise cluster is of an exclusive or noneclusive kind (its “native” symbols can only communicate the complete or incomplete identification of all the possible subentity types of significance). Fortunately, one can employ Information Engineering (IE) notation to show this paramount aspect more accurately while taking advantage of the descriptive power of the IDEF1X standard.
In this technique, the main feature of your question is denominated “categorization relationship”, where a supertype is referred to as “generic entity” and a subtype receives the name of “category entity”. However, I will continue applying the term supertype-subtype in this post because (1) it was used by Dr. Edgar Frank Codd, the originator of the relational model, (2) it is more widely known and (3) IE notation is used instead of the “native” one.

Foreign keys and supertype-subtype clusters
As demonstrated, IDEF1X provides a further advantage: the means to exhibit FOREIGN KEY (FK) definitions, elements of prime importance if a practitioner is going to represent a supertype-subtype association in a relational database.
In order to portray such a sort of association, the PRIMARY KEY (PK) property of the supertype, i.e. Artist.ArtistNumber, has to migrate to Group and SoloPerformer, although it has been assigned two different role names5, 6, GroupNumber and SoloPerformerNumber respectively, for the purpose of emphasizing the meaning conveyed by the property in the context of each subentity type.
Apart from being characterized as PKs, the Group.GroupNumber and SoloPerformer.SoloPerformerNumber properties are, at the same time, depicted as FOREIGN KEYs (FKs) that make a reference to Artist.ArtistNumber, the supertype PK property.
So, since every SoloPerformer and Group occurrence is existence-dependent on an exact Artist instance, these entity types are involved in an identifying association that takes effect by way of the PK property migration process delineated in the preceding paragraphs.
Foreign keys and associative entity types
The IDEF1X diagram serves as well to illustrate the FKs that compose the PKs of the two associative entity types of relevance, i.e., GroupMember and SoloPerformerInstrument; the first one connects the two subtypes, and the second one links a subtype with an independent entity type, i.e., Instrument.
2. Expository SQL-DDL logical declarations
As explained before, a supertype-subtype structure is a means to express certain kinds of business-domain-specific conceptualizations regarding informational requirements, which can in turn be represented in a database by means of distinct constructs that must correspond to those offered by the particular theoretical paradigm (be it relational, network or hierarchical) followed by the database management system being utilized by the designer.
One of the multiple advantages of the relational paradigm is that it permits representing the information in its natural structure, and the most popular approximations to the systems proposed in the relational theory are the various SQL database management systems.
So, finally, here are some sample DDL statements —including (a) base tables schemas along with (b) some of the pertinent constraints— that represent, at the logical level of abstraction, the conceptual modeling exercise treated above:
--
--
CREATE TABLE Artist ( -- Stands for the supertype.
    ArtistNumber    INT      NOT NULL,
    Name            CHAR(30) NOT NULL,
    Type            CHAR(1)  NOT NULL, -- Holds the discriminator values.
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Artist_PK      PRIMARY KEY (ArtistNumber),
    CONSTRAINT Artist_AK      UNIQUE      (Name), -- ALTERNATE KEY.
    CONSTRAINT Artist_Type_CK CHECK       (Type IN ('G', 'S')) -- Enforces retaining either ‘G’, for ‘Group’, or ‘S’, for ‘SoloPerformer’, only.
);

CREATE TABLE MyGroup ( -- Represents one subtype.
    GroupNumber   INT  NOT NULL, -- To be constrained as PK and FK simultaneously.
    FormationDate DATE NOT NULL,
    --
    CONSTRAINT MyGroup_PK         PRIMARY KEY (GroupNumber),
    CONSTRAINT MyGroupToArtist_FK FOREIGN KEY (GroupNumber)
        REFERENCES Artist (ArtistNumber)
);

CREATE TABLE SoloPerformer ( -- Denotes the other subtype.
    SoloPerformerNumber INT  NOT NULL, -- To be constrained as PK and FK simultaneously.
    BirthDate           DATE NOT NULL,
    --
    CONSTRAINT SoloPerformer_PK               PRIMARY KEY (SoloPerformerNumber),
    CONSTRAINT SoloPerformerNumberToArtist_FK FOREIGN KEY (SoloPerformerNumber)
        REFERENCES Artist (ArtistNumber)
);

CREATE TABLE GroupMember ( -- Stands for a M:N association involving the two subtypes.
    MemberNumber INT  NOT NULL,
    GroupNumber  INT  NOT NULL,
    JoinedDate   DATE NOT NULL,
    --
    CONSTRAINT GroupMember_PK                PRIMARY KEY (MemberNumber, GroupNumber), -- Composite PK.
    CONSTRAINT GroupMemberToSoloPerformer_FK FOREIGN KEY (MemberNumber)
        REFERENCES SoloPerformer (SoloPerformerNumber),
    CONSTRAINT GroupMemberToMyGroup_FK       FOREIGN KEY (GroupNumber)
        REFERENCES MyGroup       (GroupNumber)
);

CREATE TABLE Instrument ( -- Represents an independent entity type.
    InstrumentNumber INT      NOT NULL,
    Name             CHAR(30) NOT NULL,
    --
    CONSTRAINT Instrument_PK PRIMARY KEY (InstrumentNumber),
    CONSTRAINT Instrument_AK UNIQUE      (Name) -- ALTERNATE KEY.
);

CREATE TABLE SoloPerformerInstrument ( -- Denotes another M:N association, in this case between a subtype and an independent entity type.
    SoloPerformerNumber INT  NOT NULL,
    InstrumentNumber    INT  NOT NULL,
    CreatedDate         DATE NOT NULL,
    --
    CONSTRAINT SoloPerformerInstrument_PK                PRIMARY KEY (SoloPerformerNumber, InstrumentNumber), -- Composite PK.
    CONSTRAINT SoloPerformerInstrumentToSoloPerformer_FK FOREIGN KEY (SoloPerformerNumber)
        REFERENCES SoloPerformer (SoloPerformerNumber),
    CONSTRAINT SoloPerformerInstrumentToInstrument_FK    FOREIGN KEY (InstrumentNumber)
        REFERENCES Instrument    (InstrumentNumber)
);
--
--

Data integrity and consistency considerations
In agreement with all that has been previously explained, the designer must guarantee that each “supertype” row is at all times complemented by its accompanying “subtype” counterpart and, in turn, make sure that said “subtype” row is compatible with the value contained in the supertype “discriminator” column.
It would be very practical and elegant to enforce said circumstances declaratively (as the relational framework proposes) but, alas, none of the major SQL platforms has provided the suitable mechanisms to do so (as far as I know). Therefore, it is highly convenient to employ ACID TRANSACTIONS so that these conditions are always met in a database (other option would be to make use of TRIGGERS, but they tend to make things untidy).
Data derivation considerations
One of the main aspects of the relational model is that it considers data derivation as a paramount factor in data management. In accordance, it facilitates creating (a) base relations —or base tables in SQL, as shown in the DDL statements above— and (b) derived relations —derived tables in SQL, i.e., those declared by dint of SELECT operations that may be fixed as views for further exploitation—.
So, one can declare a view that gathers the “full” Group data points:
CREATE VIEW FullGroup AS
    SELECT G.GroupNumber,
           A.Name,
           A.CreatedDateTime,
           G.FormationDate
         FROM Artist A
         JOIN MyGroup G
           ON G.GroupNumber = A.ArtistNumber;

And other view that combines the “full” SoloPerformer pieces of information:
CREATE VIEW FullSoloPerformer AS
    SELECT SP.SoloPerformerNumber,
            A.Name,
            A.CreatedDateTime,
           SP.BirthDate
         FROM Artist A
         JOIN SoloPerformer SP
           ON SP.SoloPerformerNumber = A.ArtistNumber;

In this manner, it is very easy to manipulate —declaratively— all the significant data via the very same logical-level device, i.e., the relation or table (be it base or derived). Evidently, the usage of views is more effective when the conceptual entity types represented in a relational database possess more properties of interest, but it is a possibility worth illustrating with the present scenario.

References
1 Codd, E. F. (Dec. 1979). Extending the Database Relational Model to Capture More Meaning, ACM Transactions on Database Systems, Volume 4 Issue 4 (pp. 397-434). New York, NY, USA.
2 Chen, P. P. (March 1976). The Entity-Relationship Model—Toward a Unified View of Data, ACM Transactions on Database Systems - Special issue: Papers from the International Conference on Very Large Data Bases: September 22–24, 1975, Framingham, MA, Volume 1 Issue 1 (pp. 9-36). New York, NY, USA.
3 Elmasri, R & Navathe, S. B. (2003). Fundamentals of Database Systems, Fourth Edition. Addison-Wesley Longman Publishing Co., Inc. Boston, MA, USA.
4 National Institute of Standards and Technology (U.S.) [NIST] (Dec. 1993). Integration Definition for Information Modeling (IDEF1X), Federal Information Processing Standards Publication, Volume 184. USA.
5 Codd, E. F. (June 1970). A Relational Model of Data for Large Shared Data Banks, Communications of the ACM, Volume 13 Issue 6 (pp. 377-387). New York, NY, USA.
6 See reference 4


Could not find prepared statement with handle -1 SQL Server Profiler

Well I figured it out.

Log4Net's AdoAdapter was keeping a prepared statement saved through the duration of its connection, and my plan cache was flushing them out sometimes.


I figured out what the problem is. When I created the storage account I choose the kind to be "Blob Storage". It must be "General Purpose" to work. The backup steps was correct, the problem was with the kind of storage account.


Structure database(s) dealing with different time points

Sounds like a single table in a single database.  It sounds like you need three columns: account, date, score.  The PRIMARY KEY might be (account, date).

10K rows is "small" for a table.