How to pass an array of arguments to User Defined Aggregate in SQL Server I have a custom aggregation (UDA) and I want to pass an array of arguments like so:

select dbo.MyAggregation(
    t.type,
    N'My aggregation property',
    t.param1, t.param2, t.param3
)
from someTable t
group by t.type

The above is just an example, and I want to pass arbitrary number of params (i.e 4-...).

I've tried to use SqlString as an "array" with some delimiter, and then split the string by this delimiter, but this doesn't seem like a good solution.

The SQLCLR API does not allow for either overloading of methods nor the params keyword.

To pass in a variable number of parameters, you have a few options:

Concatenate them into a delimited string that can be split within the .NET code (this is your current solution)
Create a User-Defined Type (UDT) that is effectively just a wrapper to a simple array / generic list / dictionary (whichever is appropriate for your use- case). The UDT can have an "internal" method / property that is not exposed to the SQLCLR API but returns the native .NET collection such that you can get the values out of it directly. You would specify the name of the .NET struct / class of the UDT in the UDA signature instead of a Sql* type. This option allows for a variable number of elements (i.e. "parameters) to be passed in, but they will not be strongly typed (well, unless they are naturally all of the same type on the T-SQL side to begin with).
If you have a relatively small and finite number of parameters to pass in, and they are of different types, you can create a UDT that has SQLCLR-exposed properties which are each one of the desired "parameters". Like suggestion #2 directly above, you would use this UDT as the parameter type of the UDA and then access its properties directly in the UDA code to get the various "parameter" values. For example (partial example code!, not full solution):

internal string _Param1;
internal int _Param2;

public SqlString Param1
{
    get
    {
        if (IsNull)
        {
            return SqlString.Null;
        }
        else
        {
            return new SqlString(_Param1);
        }
    }

    set
    {
        if (!value.IsNull)
        {
            _Param1 = value.Value;
        }
    }
}

public SqlInt32 Param2
{
    get
    {
        if (IsNull)
        {
            return SqlInt32.Null;
        }
        else
        {
            return new SqlInt32(_Param2);
        }
    }

    set
    {
        if (!value.IsNull)
        {
            _Param2 = value.Value;
        }
    }
}

Usage:

DECLARE @Params dbo.TypeParams;
SET @Params.Param1 = N'some string';
SET @Params.Param2 = 35;

SELECT dbo.MyAggregation(
           t.type,
           N'My aggregation property',
           @Params
       )
FROM  someTable t
GROUP BY t.type;

In your .NET method for MyAggregation:

[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined)]
public class MyAggregation : IBinarySerialize
{
    private someType _SomeVariable;

    public void Init()
    {
        _SomeVariable = someValue;
    }

    public void Accumulate(SqlInt32 TheType, SqlString TheProperty,
                           typeNameOfUDT Params)
    {
        if (TheType.IsNull)
        {
            return;
        }

        SomeMethod(TheType.Value, Params._Param1, Params._Param2);
    }

    ...
}


Meaning of JOINs in sql I was trying to interpret the relationship between JOINs like LEFT-OUTER JOIN, RIGHT-OUTER JOIN, etc with relationships like Many-To-Many, One-To-Many etc in Hibernate or database language.

Relationships are used for describing how the tables are well, related and for enforcing this such as satisfying a foreign key from one table requires a matching entry in another. A join is used to realize those relationships. In a join you are telling the database what fields to match and how when it is executed. In a relationship you are telling the database to enforce this for future queries. Also joins are used in gathering data, whereas relationships are meant to maintain data integrity from update anomalies as well as used for conceptual reasons for developers and DBAs to know how the tables are meant to be used.


Query against sys.schemas and sys.synonyms runs very slow for one user Scenario: SQL Server 2014 (v12.0.4100.1)

.NET Service runs this query:

SELECT name, base_object_name
FROM sys.synonyms
WHERE schema_id IN (SELECT schema_id
                    FROM sys.schemas
                    WHERE name = N'XXXX')
ORDER BY name

...which returns about 6500 rows but it often times out after 3+ minutes. The XXXX above is not 'dbo'.

If I run this query in SSMS as UserA, the query returns in less than a second.

When run as UserB (which is how the .NET service connects), the query takes 3-6 minutes, and has the CPU% at 25% (of a 4 cores) the entire time.

UserA is a Domain Login in the sysadmin role.

UserB is a SQL Login with:

EXEC sp_addrolemember N'db_datareader', N'UserB'
EXEC sp_addrolemember N'db_datawriter', N'UserB'
EXEC sp_addrolemember N'db_ddladmin', N'UserB'
GRANT EXECUTE TO [UserB]
GRANT CREATE SCHEMA TO [UserB]
GRANT VIEW DEFINITION TO [UserB]

I can duplicate this in SSMS by wrapping the above SQL in an Execute as...Revert block, so the .NET code is out of the picture.

The execution plan looks the same. I diff'ed the XML and there are only minor differences (CompileTime, CompileCPU, CompileMemory).

IO Stats all show no physical reads:

Table 'sysobjvalues'. Scan count 0, logical reads 19970, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 9122, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysclsobjs'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

XEvent waits status (for a ~3min query) are:

+---------------------+------------+----------------------+------------------------------+-----------------------------+
|      Wait Type      | Wait Count | Total Wait Time (ms) | Total Resource Wait Time (ms) | Total Signal Wait Time (ms) |
+---------------------+------------+----------------------+-------------------------------+-----------------------------+
| SOS_SCHEDULER_YIELD |      37300 |                  427 |                            20 |                         407 |
| NETWORK_IO          |          5 |                   26 |                            26 |                           0 |
| IO_COMPLETION       |          3 |                    1 |                             1 |                           0 |
+---------------------+------------+----------------------+-------------------------------+-----------------------------+

If I rewrite the query (in SSMS, I don't have access to the App Code) to

declare @id int
SELECT @id=schema_id FROM sys.schemas WHERE name = N'XXXX'
SELECT a.name, base_object_name FROM sys.synonyms a
WHERE schema_id = @id
ORDER BY name

then UserB runs at the same (fast) speed as UserA.

If I add db_owner to UserB, then, again, the query runs < 1 sec.

Schema created via this template:

DECLARE @TranName VARCHAR(20)
SELECT @TranName = 'MyTransaction'

BEGIN TRANSACTION @TranName
GO

IF NOT EXISTS (SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
        WHERE SCHEMA_NAME = '{1}')
BEGIN
    EXEC('CREATE SCHEMA [{1}]')
    EXEC sp_addextendedproperty @name='User', @value='{0}', @level0type=N'Schema', @level0name=N'{1}'
END
GO

{2}

COMMIT TRANSACTION MyTransaction;
GO

And {2} is, I believe, a list of Synonyms created in that schema.

Query Profile at two points into the query:

I've opened a ticket with Microsoft.

Also, we tried adding UserB to db_owner, and then DENYing all of the privileges we know that are associated with db_owner.  The result is a fast query.  Either we missed something (entirely possible), or there is a special check for the db_owner role.

You may want to re-write your query as follows (I'm using dbo rather than XXXX so that I do find some synonyms on my testing database). This is similar to the re-write you found to be more efficient, but avoids the need to declare a variable and use two queries.

SELECT name, base_object_name
FROM sys.synonyms
WHERE schema_id = SCHEMA_ID(N'dbo')
ORDER BY name

This yields a plan like the following:

One very interesting thing about the Filter operator in this plan is that it has a predicate that performs an internal has_access() check. This filter removes any objects that the current account does not have sufficient permissions to see. However, this check is short-circuited (i.e., completes much more quickly) if you are a member of the db_owner role, which may explain the performance differences you are seeing.

Here is the query plan for your original query. Notice that all synonyms on the database (1,126 in my case, but likely many more in your case) pass through the very expensive has_access() filter, even though only 2 synonyms match the schema. By using the simplified query above, we can ensure that has_access() is only invoked for the synonyms that match your query rather than for all synonyms in the database.

Using sys.dm_exec_query_profiles to explore further

As Martin suggests, we can confirm that the has_access() check is a significant bottleneck by using sys.dm_exec_query_profiles on SQL Server 2014+. If I run the following query using a db_owner account on a database with ~700K objects, the query takes ~500ms:

SELECT COUNT(*)
FROM sys.objects

When run with an account that is not a db_owner, this same query takes about eight minutes! Running with actual plan on and using a p_queryProgress procedure that I wrote to help parse sys.dm_exec_query_profiles output more easily, we can see that almost all of the processing time is spent on the Filter operator that is performing the has_access() check:


Reporting services 2008 reports connection to azure sql database Due to the end of support of sql server 2008 R2 we are planning to move the database to azure as a paas service (azure sql database). Right now the application server has reporting services 2008 R2, for now it won't be moved to the cloud or upgrade his version. The users create the reports, then they upload them to reporting services and generate the desired information, My question is this: since azure sql database uses the latest version of sql server do the users or myself have to redo the reports in a newer version of report builder or visual studio data tools, or the reports will work the same way and the only thing I have to modify is the data sources.

I assume you are talking about migrating ReportServer and ReportServerTempDB.  As per this document, Azure SQL Database is not supported.  Below are the support databases

Azure SQL Managed Instance
SQL Server 2019
SQL Server 2017
SQL Server 2016 (13.x)
SQL Server 2014 (12.x)
SQL Server 2012 (11.x)
SQL Server 2008 R2

I have done one Reporting Server migration and since we will be backup and restoring the reporting databases, the reports are migrated with it.


Azure tempdb filling up while trying to convert a large table to columnstore... how to mitigate? I have a large reporting table (approx 6B rows) taking up roughly 400GB of storage on an azure sql db.  I've been trying to convert it to a columnstore table using the following commands:
insert Logs(Message) values ('Creating SRR table...');
select top 0 * into temp.SRR from dbo.SRR (nolock);
create clustered columnstore index ix_SRR on temp.SRR with (online = off);
create index ix_SRR_JobId on temp.SRR (JobId);
insert Logs(Message) values('Populating SRR table...');
insert into temp.SRR with (tablock) select * from dbo.SRR (nolock);
insert Logs(Message) values ('Switching out SRR table...');
alter schema old transfer dbo.SRR;
alter schema dbo transfer temp.SRR;
insert Logs(Message) values ('SRR table converted.');
if (select count_big(*) from old.SRR (nolock)) = (select count_big(*) from dbo.SRR (nolock)) begin
    drop table old.SRR;
    insert Logs(Message) values('Deleted old SRR table.');
end else begin
    insert Logs(Message) values('Row counts between old.SRR and dbo.SRR do not match; retaining old.SRR.');
end

This has worked for all of our other large reporting tables, but this one (after a good 30 hours of DTU time) consistently fails with the message:
Msg 40544, Level 17, State 2, Line 195
The database 'tempdb' has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions.

What can I do to make this work?

Bulk inserting into a CLUSTERED COLUMNSTORE index by itself shouldn't be blowing up tempdb.  Rows are read and put into compressed rowgroups in chunks of 1 million, there is no reason for long term tempdb usage.
I believe the problem is the non-clustered index ix_SRR_JobId on temp.SRR (JobId);
First try moving the index creation to after the bulk insert:
insert Logs(Message) values('Populating SRR table...');
insert into temp.SRR with (tablock) select * from dbo.SRR (nolock);
create index ix_SRR_JobId on temp.SRR (JobId) WITH (SORT_IN_TEMPDB = OFF);

This will substantially reduce tempdb pressure.
If tempdb is still blowing up, then try loading the data in smaller chunks. How you partition the data doesn't really matter, but, it should be based on the leading column(s) of an existing clustered index (unique or not).  The chunks should be small enough to load but large enough to avoid excessive "delta rowgroups"  (Columnstore Indexes: Data Loading Guidance).
As a starting point, aim for 10-20 chunks of equal size.  If the base table is 2 TB, then these would be 100-200 GB.  Adjust up or down until you get it to work.
For example, if column1 is a datetime, then try loading by year or quarter or month.


Cannot drop primary key or add primary key This db2 query:

alter table mylib.mytable add primary key(field1, field2, field3)

returns

  [SQL0624] Table MYTABLE in MYLIB already has a primary key'.

However

alter table mylib.mytable drop primary key

returns:

  [SQL0539] Table MYTABLEin MYLIB does not have a primary or unique key'.

How can I remove the existing primary key and create the new one?

The file was created in green screen, not by executing a SQL CREATE TABLE. The database version is V7R2.

You can find all FKEY/PKEY/CHECK Constraints in QSYS2.TABLE_CONSTRAINTS to see and/or remove any keys built on the table.


Set replica set member host names on Secondary I have a replica set rset1 that consists of a primary, secondary, and a backup server.

I recently just changed their DNS names in Route53--the only name that didn't change was the Primary's, however since it can't connect to the secondaries, it stepped-down from being Primary.

I've been trying write a query to use in an update of local.system.replset, where local is the database and system.replset the collection; I can't seem to get anywhere with it, though.

Here's what system.replset looks like:

{ "_id" : "rset1",
  "version" : 7,
  "members" : [
    { "_id" : 0,
      "host" : "mongo-rs1-p.foo-bar.com:27017",
      "priority" : 3
    },
    { "_id" : 1,
      "host" : "mongo-rs1-s1.foo-bar.com:27017",
      "priority" : 2
    },
    { "_id" : 2,
      "host" : "mongo-rs1-s0.foo-bar.com:27017"
    }
  ]
}

I need to change members to this:

 [
   { "_id": 0,
     "host": "mongo-rs-p.foo-bar.com:27017",
     "priority": 3
   },
   { "_id": 1,
     "host": "mongo-rs1-s.foo-bar.com:27017",
     "priority": 2
   },
   { "_id": 2,
     "host": "mongo-rs1-b.foo-bar.com:27017"
   }
]

I've tried using:

cfg = rs.conf()
cfg.members[1].host = "mongo-rs1-s.foo-bar.com:27017"
cfg.members[2].host = "mongo-rs1-b.foo-bar.com:27017"
rs.reconfig(cfg)

But since the primary is no longer primary, I can't get this to run successfully, and running db.getMongo().setSlaveOk() didn't help with using rs.reconfig()

As a last resort (I've had this problem before) I can run rm -rf /data; mkdir -p /data to remove mongo's data directory (after killing the mongod process) and then I can start the mongod server again and rebuild the replica set from scratch, but I'd rather not do that.

So how can I update the members of the replica set without deleting the database on a primary that  stepped down?

There is a guide for how to do this (reconfigure a replica set with unavailable members) here:

http://docs.mongodb.org/manual/tutorial/reconfigure-replica-set-with-unavailable-members/

You essentially have to do a forced reconfig of the set because there is no primary available.  If you want to keep a set operational while doing this type of move, you can:

Use your hosts file to temporarily point the old names to the new (or similar) and "fool" the primary into thinking they are up (then make the permanent changes one at a time)
Temporarily add arbiters to prevent the primary from stepping down (2 more for example, so the primary is kept with 3/5 votes)
Reconfigure the set to a single node before doing the moves
Do the moves one server at a time, ensuring there are 2/3 nodes up to form a majority at all times (and hence keep a primary up)


CSSCAN for the character set change, database version 11.2.0.3.0 According to Document 745809.1, Installing Csscan.

While running the "Csminst.sql" we are getting below error

SQL> @$ORACLE_HOME/rdbms/admin/csminst.sql

create user csmig identified by csmig password expire account lock

*

ERROR at line 1:
ORA-28003: password verification for the specified password failed

You can just edit the file $ORACLE_HOME/rdbms/admin/csminst.sql and change identified by csmig from csmig to a more complex password; you may have to enclose the password with double quotes if you have characters other than alphanumeric, underscore (_), dollar sign ($) or pound/hash sign (#).

Example:

create user csmig identified by "W#$!?X8Y" password expire account lock;


group by excluding a column I have this code:

insert into [dbo].[NGC_Agent_Intervals]
SELECT
    AgentID,
    date,
    [Hour] = DATEPART(HOUR, RSRange),
    hour_quarter = CONCAT(DATEPART(MINUTE, RSRange) ,'-', DATEPART(MINUTE, RERange)),
    DepartmentID,
    [DepartmentName],
    [AgentState],
    [AgentStateReason],
    ( select AgentStateReasonDescription from RealRanges) as AgentStateReasonDescription,
    [ExtensionID],
    [WorkstationID],

    Duration = sum(CASE
      WHEN RSRange <= aStart and aend >= RERange THEN DATEDIFF(SECOND, aStart, DATEADD(MINUTE, 15, RSRange))
      when  RSRange <= aStart and aend < RERange THEN DATEDIFF(SECOND, aStart, aEnd)
      WHEN RERange >= aEnd THEN DATEDIFF(SECOND, RSRange, aEnd)
      ELSE DATEDIFF(SECOND, RSRange, RERange)
    END)
 FROM RealRanges

 WHERE DATEDIFF(SECOND, RSRange, aEnd) > 0
   AND AgentState NOT IN ('logout', 'LOGIN')

 GROUP BY duration,RealRanges.AgentID, RealRanges.date, RealRanges.hour,
  DepartmentID,[DepartmentName], [AgentState], [AgentStateReason],
  [ExtensionID],[WorkstationID], RSRange, RealRanges.RERange

I run this code after using a cte to split the original rows from a table to a few rows by hour quarters.
What I want to do is to sum the duration value, if a few columns are matching in a few rows.

That's the original table values and I want to sum the duration:

if i dont sum the duration it looks like this:

It should look like this after the sum:

The only problem is that I want to group by only for the following columns:

AgentID  Date  hour  Hour_Quarter  AgentState  AgentStatereason

and exclude the other columns so even if the extensionid is different (for example) I still get the sum from 2 columns.
I tried doing that with a sub-query (in the code above) but I get an error:

So what am I missing?

To avoid grouping by a specific column that returns multiple values, you can either remove it from the query, or you can explicitly tell it which value you want. You can do this using aggregate or analytic functions, like:

MAX(ExtensionID)
MIN(ExtensionID)
FIRST_VALUE(ExtensionID) OVER (...)
LAST_VALUE(ExtensionID) OVER (...)

For numeric values you can also present new values, such as:

SUM(Duration)
AVG(Duration)

But basically, if you want to show a single row collapsed from two different ExtensionID values, you need to define how to do that. In this case, is ExtensionID important? If it is, do you want to show the most recent one? The first one? The one that occurs most frequently? What about ties? There are solutions to all of these, but you have to know what you want your query to return.

You can't just leave things out of the GROUP BY like you can with MySQL, where you get - let's say - weird results.


Create one row for every user in a Postgres table I have a bunch of rows of users in a users table, for example:

| id |  name |
|----|-------|
|  1 | Chris |
|  2 | Max   |
|  3 | Steve |

For each one of these users, I'd like to create a row in another table called user_flags:

| user_id |  flag  |
|---------|--------|
|       1 | myflag |
|       2 | myflag |
|       3 | myflag |

Currently what I'm doing is downloading users in batches of ~100 at a time, and then doing a bulk INSERT query for each batch. However I think that there should be a quicker way.

The goals:

Insert the flag rows as quickly as possible
Long running queries are fine, as long as there's no table locking anywhere
These are real-time queries, so indexes cannot be dropped
Should be able to comfortably run over millions of user rows

Is this possible? I feel like with some smart functions it could/should be.

This would seem rather simple:

INSERT INTO user_flags (user_id, flag)
SELECT u.id, 'myflag'   -- or whatever to write to this field
FROM   users u
LEFT   JOIN user_flags uf ON uf.user_id = u.id
WHERE  uf.user_id IS NULL;  -- to exclude already existing

How to ..

Select rows which are not present in other table

While this is susceptible to race conditions with concurrent transactions, that should not be a problem since you are not going to execute this INSERT in more than one transaction at a time.

You might limit the operations to n rows per call, say 100000, simply append LIMIT, possibly combined with ORDER BY:

...
ORDER  BY u.id
LIMIT  100000;

Repeat the query until you find < 100000 rows, in separate transactions to release locks. Normally, you would do it all in one command, though, even with millions of rows.

I suggest a FOREIGN KEY constraint to enforce referential integrity - if you don't have one, yet.

ALTER TABLE user_flags
  ADD CONSTRAINT user_flags_user_id_fkey FOREIGN KEY (user_id)
  REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE;

And a UNIQUE or PRIMARY KEY constraint on user_flags.user_id to make the 1:1 relationship complete.


MySQL - Slow InnoDB Write Speeds on Windows 10 I have Windows 10 bootcamped. I've installed the latest version of MySQL community server. I've also installed WordPress onto IIS. The MacBook has 16Gb of memory. Here is my config file

[client]
no-beep

# pipe
# socket=0.0
port=3306

[mysql]

default-character-set=utf8

[mysqld]
port=3306
datadir=C:/ProgramData/MySQL/MySQL Server 5.6/Data
character-set-server=utf8
]default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log-output=FILE
general-log=0
general_log_file="DESKTOP-F1OO1LC.log"
slow-query-log=1
slow_query_log_file="DESKTOP-F1OO1LC-slow.log"
long_query_time=10
log-error="DESKTOP-F1OO1LC.err"
server-id=1
max_connections=151
query_cache_size=0
table_open_cache=2000
tmp_table_size=90M
thread_cache_size=10
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=171M
key_buffer_size=8M
read_buffer_size=64K
read_rnd_buffer_size=256K
innodb_additional_mem_pool_size=13M
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size=7M
innodb_buffer_pool_size = 4G
innodb_log_file_size=48M
innodb_thread_concurrency=17
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=80
flush_time=0
join_buffer_size=256K
max_allowed_packet=4M
max_connect_errors=100
open_files_limit=4161
query_cache_type=0
sort_buffer_size=256K
table_definition_cache=1400
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000

Is there anything obvious I'm doing wrong or do you need more information? I've tried following various guides but getting lost with all the options avilable.

Currently looking at a maximum of 20 writes per second!

Thanks

RECOMMENDATION #1
You have innodb_thread_concurrency at 17. It really needs to be zero(0). Why ?
Rather than rehash stuff I wrote in the past, please read the 7 posts I wrote about innodb_thread_concurrency being the best choice at 0.
RECOMMENDATION #2
Using a small log buffer can be a bottleneck when writing to the redo logs.
Please read my 3-yr-old post MySQL transaction size - how big is too big? why it should be bigger
I recommend innodb_log_buffer_size being set to 128M
RECOMMENDATION #3
The redo log needs to larger as well. You have
You have innodb_log_file_size at the default size for MySQL 5.6 : 48M
I recommend setting innodb_log_file_size = 1G.
RECOMMENDATION #4
You have flush_time set to 0. IMHO For any Windows OS, that's a big NO-NO.
The default is 1800 for Windows. I don't trust MySQL flushing to disk every 30 min in Windows.
I recommend flush_time to 300 (every five(5) minutes)
RECOMMENDATION #5
You should increase the IO threads
The defaults are
innodb_read_io_threads = 4
innodb_write_io_threads = 4

Percona Server's defaults are
innodb_read_io_threads = 8
innodb_write_io_threads = 8

You should start with 8 for both innodb_read_io_threads and innodb_write_io_threads.
GIVE IT A TRY !!!
I have so much more to recommend in terms of per-connection buffers : How costly is opening and closing of a DB connection?


Is it safe to use "NOT BLOCKING" option on HSQL backup? The HSQL documentation has the following to say about the "NOT BLOCKING" option on backups:

  Hot backup is performed if NOT BLOCKING is specified. In this mode, the database can be used during backup. This mode should only be used with very large databases. A hot backup set is less compact and takes longer to restore and use than a normal backup set produced with the BLOCKING option.

Our databases can be quite large, so we're definitely interested in not blocking the application during backup. Based on this description, it sounds like it should be "safe" to use NOT BLOCKING. My main concern is that it would backup "partial" transactions if changes were being made during backup.

Since there is no mention of data integrity issues, can I assume it's safe to use NOT BLOCKING if I'm not concerned about backup size or restore time?

Yes, you can assume it is safe to use NOT BLOCKING as a valid means of backing up your HSQLDB.

I don't believe the backup would contain "partial" transactions, more likely all versions encountered during the backup operations, hence the larger backup size and more complicated restore process.


SQL Server 2008 T-SQL select hanging, but not dead locked This is a pretty strange issue so bear with me. I have a stored proc that does some heavy duty processing. When it runs well it usually takes a few minutes depending on server load, but occasionally it appears to get stuck.

I have looked at the results of sp_who2 and what I see is that the process is not blocked, it's still listed as 'RUNNABLE', but the value in disk IO stays the same. Once the process gets in this state it never finishes. If I kill the process and re-execute the stored proc, I usually get the same result.

Sometimes re-executing the stored proc works, but increasingly I end up having to restart SQL Server. Not ideal... After restart the stored proc executes as expected. Has anyone even hit a problem like this? Any advice would be greatly appreciated.

Some more context. The stored proc I am executing looks something like this:

delete
from table1

insert into table1
select columns
from
(
    select columns
    from giant_table_1 WITH (NOLOCK)
    where condition
    group by columns

    UNION

    select columns
    from giant_table_2 WITH (NOLOCK)
    where condition
    group by columns
)
where condition

more crud on table1
.
.
.

If I insert some logging statements into the stored proc I can see that when it hangs it hangs on the insert/select. Wondering if this could be a weird consequence of the NOLOCK statement. We (and by we I mean my boss :)) added the nolock because giant_table_1 and giant_table_2 are constantly being changed. Without the nolock our sproc was getting blocked a lot by other long running processes and vice versa.

Thanks in advance!

From the looks of your query I believe that the process is waiting on a spill to tempdb.  You will need to look at the output of

SELECT * FROM sys.dm_os_waiting_tasks WHERE session_id = <SPID for the SP>

to get a better idea of whats occuring in the background, Adam Machanic's sp_whoisactive is a great tool for simplifing the collection of details about a queries execution.

Also make sure that autogrowth is set correctly for your database and tempdb with sensible auto growth amounts for the data and log and that the number of VLFs in the log files is under a 100.  Look at his post for details 8 Steps to better Transaction Log throughput


How to back up a small database in SQL Server 2008 R2 Express Edition I will shortly be migrating a database from SQL Server 2000 (part of SBS 2003) to SQL Server 2008 R2 Express Edition

The database is small, and there are only a few hundred short transactions per day - I'd like to keep everything as simple as possible from a recovery perspective whilst minimising the amount of lost data in the event of a failure

Can I just run a full backup every hour using Windows Scheduler? I already have a solution for long term archival of database backups which these could just plug in to

What can I do to back up the 'logins' - I understand they are not saved as part of a full database backup. I want to have everything necessary to be able to perform bare metal recovery  if we have to

Anything else I need to think about?

To backup up logins, you'd backup the master database. For any jobs etc, it's be msdb

I assume you want to avoid some complexity by not having 96 log backups per day every 15 minutes.

Your approach to of hourly full backups will work, but I'd consider doing hourly or 30 minute differentials with a daily full backups. The combination of full/differential:

+consumes less space
+will give quicker backups
+can have more frequent backups (less average data loss)
-slightly increased restore time
-slightly increased complexity


Can I use OUTPUT from an UPDATE in an INSERT? I want to add a record to my app's "SystemSettings" table and set the PK value using the value from an UPDATE.  The PK value comes from the "TS_LASTIDS" table which contains the maximum PK value for each table in this application (MicroFocus SBM).  I need to increment the "TS_LASTID" column in that table and use the new value as the PK when INSERTING a new record into the "SystemSettings" table.

Insert Into
   ts_SystemSettings ( ts_Id, ts_Name, ts_LongValue)

Values (
   ( -- ******************** This subquery updates the PK in TS_LASTIDS and outputs the new value
      Update
         ts_LastIds
      Set
         ts_LastId=ts_LastId+1
      Output
         INSERTED.ts_LastId
      Where
         ts_Name = 'SystemSettings'
   ) ,  -- ********************
   'NSLastChangeId' ,
   1 ,
) ;

I can't figure out the syntax.  This is MS SQL server 2012.

Here is an example of using OUTPUT...INTO

--demo setup
drop table if exists InsertTest;
Create Table InsertTest (id int);

drop table if exists UpdateTest;
Create Table UpdateTest (id int);
insert into UpdateTest(id) values(1),(2);

--solution
UPDATE UpdateTest
SET id = id + 1
OUTPUT INSERTED.id
INTO InsertTest;

--verify results of insert
select * from InsertTest

ID
2
3


Ranking each row in Postgres from 1-100 when comparing in groups For each row in my table, I want to calculate its "expensiveness" when grouped together with other rows with same year/month and size.

Example data:

year,month,size,price
2017,1,300,1000
2017,1,300,1100

I tried a query like this:

select
*,
ntile(100) over (partition by year, month, size order by price) as percentile
from mytable;

The problem I have is that rows with the same price can get different percentiles, especially if a certain price is very common. Is there an alternate way to rank things from 1-100 that would give results that seem more consistent?

I don't mind if I need to create multiple views/queries to achieve this, e.g. perhaps I should calculate every percentile first and then my main query finds the "nearest" percentile, etc.

Also to note: sometimes the data may be sparse per group, e.g. less than 100 rows per group. Also, prices often group together so there will be even less discrete values.

I think I may have found the answer. The following query seems to produce results that are as I expect:

select
*,
(100*cume_dist() over (partition by year, month, size order by price))::int as percentile
from mytable;


MS Access / MySQL: unlock locked Records I have a Microsoft Access database that is connected to a MySQL server via ODBC and is accessed by multiple users.

Over time many records are locked and editing is not possible anymore.

Is there a way to bulk unlock all records?

The Error Message:

  This record has been changed by another user since you started editing
  it. If you save the record, you will overwrite the changes the other
  user made. Copying the changed to the clipboard will let you look at
  the values the other user entered, and then paste your changes back in
  if you decide to make changes.

I finally could fix my problem by adding a field named TIMESTAMP of type timestamp and default value CURRENT_TIMESTAMP and attribute = on update CURRENT_TIMESTAMP.
Then i updated all records and set TIMESTAMP=NOW().

For now i did not get any error again.


SQL Server 2012: stored procedures permissions Is it possible to control permissions on a single stored procedure level in SQL Server 2012? It seems that SQL Server Manager doesn't expose such functionality via GUI. Can somebody advice me how to view/set permission on a single stored procedure level?  In particular I'm interested in removing "Connect" right on one procedure for specified account while leaving Connect rights for this account on all the others procedures. Is it possible?

You just need to deny EXECUTE permissions on any object that you don't want the app login to see.  Denying VIEW DEFINITION will only prevent the code of the object from being seen by that user. That is the point of VIEW DEFINITION: to allow users to run code that they can't see the code for. Denying EXECUTE on the object will removing it completely as far as that login is concerned.

DENY EXECUTE ON SchemaName.ProcName TO [AppLogin];

Once the DENY has been run, the following won't return anything for that login:

SELECT OBJECT_ID('SchemaName.ProcName');

SELECT *
FROM   sys.objects so
WHERE  so.name = 'ProcName';


As a MongoDB DBA, what do I need to know about the meltdown/spectre vulnerabilities? If you didn't hear, a set of related vulnerabilities were recently discovered that impact virtually all processors sold over the last decade. You can find more technical detail about the meltdown/spectre vulnerabilities on InfoSec.SE.

As a MongoDB DBA, what do I need to understand about this?

What are the potential performance impacts? What are the proper patching guide lines? What are cloud providers doing in regards to this vulnerability?

Related Questions:

As a MS SQL DBA, what do I need to know about the meltdown/spectre vulnerabilities?

An official response from Mongo on 1/6/2018 (emphasis mine):

  Recently disclosed research regarding security vulnerabilities in
  almost all modern processors such as Intel and AMD (CVE-2017-5715,
  CVE-2017-5753, and CVE-2017-5754) has prompted public and private
  institutions, including cloud providers, to patch OS and hypervisor
  infrastructures. These patches disable performance optimizing features
  of CPUs, and it is expected that, regardless of OS or cloud provider,
  all workloads will see a performance impact. We are investigating the
  performance implications of these patches to MongoDB on both OS
  kernels as well as cloud hypervisors.

  Hypervisor Patch Impact

  As a result of the patches applied by AWS
  between January 3rd and January 5th, 2017, we have observed the
  following:

  For high-load benchmarks (e.g. YCSB) we measured a 10-15% impact on
  throughput with some exceptional cases that are still being studied.
  We believe these results are consistent with other industry research.
  We will release additional results (including additional cloud
  providers) in the coming days.

  Kernel Patch Impact

  This is the impact that customers can expect to
  see when they apply the recommended patches to their cloud VMs or
  on-premises hardware:

  At this time we have not seen a substantial signal (either better or
  worse) on major distributions such as Amazon Linux, Red Hat Enterprise
  Linux, and Ubuntu. Based on the industry research we expect that there
  will be some measurable performance impact (although yet to be
  quantified). We will have additional results made available in the
  coming days. If you have any questions or concerns, please file a
  ticket in the Support Portal.

  You can read Intel’s white paper to learn more about these
  vulnerabilities.

Related Posts:

Glenn Berry: Checking your Meltdown and Spectre Mitigation Status in Windows


Postgres: Disable "similarity" column output on "SELECT SIMILARITY"? I've tried disabling similarity column output on SELECT SIMILARITY but still cannot.

Below query will generate a column similarity:

SELECT SIMILARITY(title, 'Nation'), title
FROM gallery
WHERE title % 'Nation'
ORDER BY similarity DESC LIMIT 5;

Below query does not output similarity column but I am unable to sort:

SELECT title FROM gallery WHERE title % 'Nation';

Generally, you can always wrap a query as subquery if you don't want to output all rows:

SELECT title FROM (SELECT ...) sub;

But you can also use expressions in ORDER BY, not just input or output columns. So there is no need for this (like @ypercube already commented).

For the case at hand, it must be mentioned that you are using the additional module pg_trgm. What you are trying to do can be simplified to:

SELECT title
FROM   gallery
WHERE  title % 'Nation'
ORDER  BY title <-> 'Nation'
LIMIT  5;

I replaced SIMILARITY(title, 'Nation') DESC with the simpler, equivalent title <-> 'Nation', since the <-> operator (per documentation):

  Returns the "distance" between the arguments, that is one minus the similarity() value.

Either version works, but the latter is shorter and index support is bound to the operator, not the function. And you should have a GiST index on title.

Details:

PostgreSQL FTS and Trigram-similarity Query Optimization

Also relevant:

Finding similar strings with PostgreSQL quickly


Column with selective aliases I want to select an alias based on specific column criteria and output the required XML.

Say we have a table named ACCOUNTS:

Name       Age
---------  ---
John Doe   30
Mary Lane  40
Alice Bob  50

Query:

;WITH XMLNAMESPACES
(
  'http://www.w3.org/2001/XMLSchema-instance' AS abc, 'uri' AS def
)
SELECT
     CASE WHEN AGE <= 30 THEN 'BUYER' AS [def:GIRL]
          WHEN AGE >=40 THEN 'SELLER' END AS [def:BOY]
FROM ACCOUNTS
FOR XML PATH('abc:OilCompany'), TYPE

The output should be:

 <abc:OilCompany>
   <def:GIRL>BUYER</def:GIRL>
   <def:BOY>SELLER</def:BOY>
   <def:BOY>SELLER</def:BOY>
 </abc:OilCompany>

By default the SQL Server does not create a node for NULL values so if you change your query to use two CASE statements and return NULL when there is no match you will get the XML you are looking for.

WITH XMLNAMESPACES
(
  'http://www.w3.org/2001/XMLSchema-instance' AS abc,
  'uri' AS def
)
SELECT CASE WHEN AGE <= 30 THEN 'BUYER' END AS [def:GIRL],
       CASE WHEN AGE >= 40 THEN 'SELLER' END AS [def:BOY]
FROM ACCOUNTS
FOR XML PATH(''), ROOT('abc:OilCompany'), TYPE;

dbfiddle


First off, your question as well as your column name "key" are misleading. The column key does not contain any JSON keys, only values. Else we could use the function jsonb_object_keys(jsonb) to extract keys, but that's not so.

Assuming all your JSON arrays are either empty or hold integer numbers as demonstrated. And the scalar values (non-arrays) are also integer.

Your basic sort order would work with Postgres integer (or numeric) arrays. I use this little helper function to convert jsonb arrays to Postgres int[]:

CREATE OR REPLACE FUNCTION jsonb_arr2int_arr(_js jsonb)
   RETURNS int[] LANGUAGE sql IMMUTABLE AS
'SELECT ARRAY(SELECT j::int FROM jsonb_array_elements_text(_js) j)';

Explanation:

How to turn JSON array into Postgres array?

Then add jsonb_typeof(jsonb) to arrive at:

SELECT key
FROM   t2
ORDER  BY key <> '[]'             -- special case for empty array
        , jsonb_typeof(key) DESC  -- 'number' before 'array'
        , CASE jsonb_typeof(key)  -- sort arrays as converted int[]
            WHEN 'array'  THEN jsonb_arr2int_arr(key)
            WHEN 'number' THEN ARRAY[key::text::int]
          END;

Produces the desired result exactly.

Why?

The manual for jsonb explains:

  The btree ordering for jsonb datums is seldom of great interest, but
  for completeness it is:

  Object > Array > Boolean > Number > String > Null
  Object with n pairs > object with n - 1 pairs
   Array with n elements > array with n - 1 elements

  Objects with equal numbers of pairs are compared in the order:

key-1, value-1, key-2 ...

  Note that object keys are compared in their storage order; in
  particular, since shorter keys are stored before longer keys, this can
  lead to results that might be unintuitive, such as:

{ "aa": 1, "c": 1} > {"b": 1, "d": 1}

  Similarly, arrays with equal numbers of elements are compared in the
  order:

element-1, element-2 ...

Bold emphasis mine.
That's why jsonb '[2]' < jsonb '[1, 2]'.
But Postgres arrays just sort element-by-element: '{2}'::int[] > '{1, 2}' - exactly what you were looking for.


Update with rolling sum grouped by another column I'm trying to build a query to update a MySQL 8.0 table that looks like this

-----------------------
user_id | total | delta
-----------------------
      1 |     0 |    30
      1 |     0 |   -10
      2 |     0 |    -5
      2 |     0 |    10
      2 |     0 |   -10
      3 |     0 |    30

Into this:

-----------------------
user_id | total | delta
-----------------------
      1 |    30 |    30
      1 |    20 |   -10
      2 |    -5 |    -5
      2 |     5 |    10
      2 |    -5 |   -10
      3 |    30 |    30

Basically it should compute a rolling sum for each user and update the table with the value at each row. You can think of each row as being a transaction that takes place at a certain timepoint, but for simplicity's sake I've just removed the date info. For this example just presume that they're already ordered by 'date'.

It's easy to do something like this to do a rolling sum over the entire column:

SET @sum := 0;
UPDATE tablename
SET total = (@sum := @sum + delta)

But I'm not sure how to get it to perform separate rolling sums. I also have hundreds of millions of entries, so it needs to be performant.

You will need some defined ordering of your rows, otherwise, you will end up with a different CUMULATIVE SUM every time you run your update. I added an auto-increment column, but you likely have a timestamp or similar you can use:

create table T
( seqid int not null auto_increment primary key
, user_id int not null
, total int not null
, delta int not null
);

insert into T (user_id, total, delta)
values (1, 0, 30)
     , (1, 0, -10)
     , (2, 0, -5)
     , (2, 0, 10)
     , (2, 0, -10)
     , (3, 0, 30);

Window functions were introduced in SQL99 some 20 years ago, but only recently made it into MySQL. They are very handy for this kind of task:

with TT as (
    select seqid, user_id, delta, total
         , sum(delta) over (partition by user_id order by seqid) as new_total
    from T
)
update T, TT
    set T.total = TT.new_total
where T.seqid = TT.seqid;

For some reason I could not get the update to work with an ansi join, it is left as an exercise for the reader;-)


Cannot add an account [non domain account ] to SQL Server 2012 Background:

I have a application pool [ABCDEMO] in IIS. The identity of the appool is "NetworkService". I am trying to add IIS APPPOOL\ABCDEMO to my SQL SERVER 2012 on a Windows SERVER 2012.

When I try to add to the Logins I get the following error.

===================================

Create failed for Login 'IIS APPPOOL\ABCDEMO'.  (Microsoft.SqlServer.Smo)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Login&LinkId=20476

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
   at Microsoft.SqlServer.Management.SqlManagerUI.CreateLoginData.LoginPrototype.ApplyGeneralChanges(Server server)
   at Microsoft.SqlServer.Management.SqlManagerUI.CreateLogin.DoPreProcessExecution(RunType runType, ExecutionMode& executionResult)
   at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.DoPreProcessExecutionAndRunViews(RunType runType)
   at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.ExecuteForSql(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
   at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.Microsoft.SqlServer.Management.SqlMgmt.IExecutionAwareSqlControlCollection.PreProcessExecution(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
   at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.RunNow(RunType runType, Object sender)

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries, Boolean includeDbContext)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImplFinish(StringCollection createQuery, ScriptingPreferences sp)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()

===================================

Windows NT user or group 'IIS APPPOOL\ABCDEMO' not found. Check the name again. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=15401&LinkId=20476

------------------------------
Server Name: USALVWCOQA10
Error Number: 15401
Severity: 16
State: 1
Line Number: 1

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

It's probably because that's not the way that the SQL Server instance is seeing the application.  Attempt to use the application.  This should log an error in the SQL Server's ERRORLOG file when the application isn't able to connect.  This will tell you how the SQL Server is seeing the login.  Just copy the login from the ERRORLOG file and create a login for it.


In SQL Server 2008, in DMV sys.dm_db_index_phsicial_stats, what does compressed_page_count mean in comparision with page_count In SQL Server 2008, DMV sys.dm_db_index_physical_stats, what does compressed_page_count mean in comparison with page_count. I have a table with the following info:

Object_id     Index_type_desc      avg_frag_in_percent    page_count     compressed_page_count        avg_page_space_used_in_percent       avg_record_size_in_bytes
5464654        CLUSTERED INDEX         0                    140992          1410                                        99.24                        111.2
4465464        NONCLUSTERED INDEX      0                    52532           526                                         99.69                         32.2

Does it mean, that the table has 140992 pages, but after compression it is brought down to 1410 pages or something else?

  [BOL says:] MSDN
  compressed_page_count: The number of compressed pages.

The clustered index has 140992 pages, from which only 1410 are page compressed. Page compression is attempted only when it fills up, see When Page Compression Occurs:

  When a new table is created that has page compression, no compression
  occurs. However, the metadata for the table indicates that page
  compression should be used. As data is added to the first data page,
  data is row-compressed. Because the page is not full, no benefit is
  gained from page compression. When the page is full, the next row to
  be added initiates the page compression operation. The whole page is
  reviewed; each column is evaluated for prefix compression, and then
  all columns are evaluated for dictionary compression. If page
  compression has created enough room on the page for an additional row,
  the row is added, and the data is both row- and page-compressed. If
  the space gained by page compression minus the space that is required
  for the CI structure is not significant, page compression is not used
  for that page. Future rows either fit onto the new page or, if they do
  not fit, a new page is added to the table. Similar to the first page,
  the new page is not at first page-compressed.

It seems that in your case very few pages got compressed. Note that you still benefit from row compression.


Should domain "Users" group be removed from the SQL logins for database security? I am coming from a security standpoint. I see quite a lot of SQL DBs with the entire domain "Users" group ("XXX Domain\Users") added as a login account in SQL.

Does this mean that all users in the XXX domain can now access the SQL server? Is this a security risk? Will removing it impact my applications?

I have DBAs telling me that this is not a security risk.

Don't remove any SQL Login's until you are sure they are no longer required.

Now you might be mixing two definitions.

SQL Server databases contain database users
SQL Server instances contain SQL Logins

SQL Server Logins

A SQL Server login created on a SQL Server instance can either be a Windows Authenticated User or Group, or it could be a simple SQL Server account/login.
A SQL Server login will have its own password and rules.
A Windows Authenticated user or group created as a SQL Server login will be linked to the corresponding Windows Account and does not contain a password. It validates either against the Domain or against the Server the user/group belongs to.
A SQL Server login has permissions at SQL Server instance level.

Database Users

A database user is limited to permissions inside the database. (There are exceptions).
You can assign a database user DML or DDL permissions and grant EXECUTE permission on stored procedures.
You can assign a database user to database roles
You can assign a database user to a schema (default: dbo)

Linking database users and SQL Server logins

There can be a link between database users and SQL logins, but there doesn't have to be.

Let's give you a few examples

You could have the SQL Server login DOMAIN\Users with VIEW SERVER STATE permissions for the instance, but otherwise not linked to a database via database user. All domain users can then query the state of the SQL Server.
You could have the SQL Server login DOMAIN\Users linked to a database user TelephoneBook_Reader in the database TelephoneBook. The database user TelephoneBook_Reader might have SELECT permissions on the table Employees in the database. If you remove the DOMAIN\Users from the SQL Server instance, then nobody will be able to query the telephone book.
You could have the SQL Server login DOMAIN\Users linked to a database user  DOMAIN\Users in the database PsuedoDB. The Database user DOMAIN\Users might have permissions to select, insert, update and delete for the schema Inventory.
....

Answering your Question

No, just because a Domain group is assigned to a database via a SQL Server login linked to a Database user, does not have to be a security risk. It might be the easiest way to grant a lot of users access to a database.

Yes, if a Domain group has been granted permissions to access a database (via SQL Login and Database user) and to query information it should not be allowed to, then this can be a security risk.

As with so many things in SQL Server: It depends.

Check the database permissions assigned to the SQL Server login DOMAIN\Users and verify that these permissions are required.

From a hacker's standpoint: Every permission granted can be pose a security risk.


Oracle RAC on Virtual machine I have Oracle RAC on the very powerful servers and it can be said that these servers have extra memory,cpu and storage :) ...so my servers are much more powerful than it is needed.

My manager and other administrators(who do not have enough resources) need my RAC to be moved to Virtual Machine,to utilize RAC servers for their needs.

I need your advise... What to do? Do I have to move my RAC on virtual machines?  if not what arguments should I say, to prove that this idea is stupid?

Thank you very much, waiting your suggestions and clever arguments...

Virtualizing an Oracle RAC isn't necessarily a stupid idea, but it should only be done with a full understanding of the benefits and risks.  You are asking questions, so that is a good step in the right direction.

Your question doesn't say which virtualization technology, the Oracle version, or the edition.  For now I will assume this is for production on VMWare with the enterprise edition of Oracle 11.2.0.2+.

Virtualization provides some of the same benefits of RAC, though in a dramatically different way.  Both can reduce or eliminate downtime due to some hardware failures, and both can allow servers to be restarted without affecting availability.  RAC increases capacity and allows for rolling upgrades, which are things virtualization can't do.  VMware on the other hand can allow servers to be restarted, hardware to be changed, and storage to be relocated all without bringing any instances down.

Licensing

With VMWare the entire server has to be licensed even if it will be running multiple virtual machines only one of which is for Oracle.  This can either be a good thing or a bad thing.  If you are virtualizing Oracle onto an existing cluster with other VMs, then all the machines in the cluster will need Oracle licenses.  This could significantly increase your licensing costs likely to the point of making the purchase of additional hardware miniscule in comparison.

On the other hand, a VMWare cluster dedicated to Oracle can allow virtualized instances to float to less busy nodes and under-utilized hardware can handle multiple instances, potentially decreasing the overall hardware costs and licensing costs.

Support

Oracle's official stance for running databases on VMWare can be found in ID 249212.1, which says certification has not been done for it's products on VMWare. Since virtualization can be viewed as a hardware layer, this would be in keeping with the general lack of hardware certification.

The following note was added with 11.2.0.2:

  NOTE:  Oracle has not certified any of its products on VMWare.  For
  Oracle RAC, Oracle will only  accept Service Requests as described in
  this note on Oracle RAC 11.2.0.2 and later  releases.

Read the entire note for the full context, but it seems that Oracle is beginning to make allowances for even RAC on VMWare.

Application Support

If your applications are all RAC aware and can gracefully handle the loss of an instance, then some of the benefits that VMWare add would not be as worthwhile.  On the other hand, if the applications are not RAC aware and will need to re-connect anyway, then the transparency of VMWare VMotion would be more desirable.

Complexity/Flexibility

The combination of RAC and virtualization allows for a lot of flexibility, but also increases complexity considerably.  To restart a server you could VMotion all the instances off of that node to other nodes using VMWare, or you could bring the instances on that box down using RAC. Each option has pros and cons that you would have to consider.

Thoughts

Since virtualization provides some of the benefits of RAC, you could abandon RAC and potentially save a lot of licensing cost and reduce the overall complexity.
Moving multiple nodes of the RAC to the same virtual machine host would be a very bad idea.  You would loose some of the benefits of RAC while increasing the complexity and decreasing the availability.
It will likely all come down to licensing.  If you have to license even one additional CPU due to the move to VMWare, it would likely cost less to just buy more hardware.  Of course there are many factors that could affect this.

There is much more information that could be discussed on this subject, so please don't use this information here as your sole source in making your decision.


Oracle encryption wallet 12c I exported a database to a transportable tablespace and send the data to a customer.  They received the error: ORA-28365 Wallet is not open.
Do I need to close the wallet before creating the export?  Is there something on the customer's end that they can do to allow them to import this data?

Haven't had this problem with any other customer since switching to 12c in the last 6 months so this is strange to me that this is the only customer having this problem?

Thanks for any insight!

For TDE:

Do transportable tablespaces work with TDE tablespace encryption?

  Yes, but it requires that the wallet containing the master key is
  copied to the secondary database. If the tablespace is moved and the
  master key is not available, the secondary database will return an
  error when the data in the tablespace is accessed.

How to use TTS With TDE (Doc ID 1674166.1)

  ... Only difference when transporting tablespace using TDE is the
  wallet needs to copied to the target database. ...


Unable to copy a DB from SQL Server 2000 to SQL Server 2012 using copy wizard I am trying to copy a database from SQL Server 2000 to SQL Server 2012 using copy wizard in my local machine where SQL Server 2012 is installed. But, the wizard is not detecting destination server. Source DB is on a network and Destination is my local machine itself.

When I select the destination server as my local machine, I am getting the below error:

  TITLE: Copy Database Wizard

  The destination server cannot be a SQL Server 2005 or later Express instance.

Does Copy Database wizard not support if 'source' is earlier than SQL Server 2005 ? But the error message says only about 'Destination'. Here my destination is SQL Server 2012 and Source is SQL Server 2000.

The supported upgrade path does not go directly from 2000 to 2012.  You will have to have an intermediate step where you upgrade or restore those DBs to a 2005 or 2008 R2 instance.  Once you've done that, you can upgrade to 2012 or take a backup of the DBs in 2005, then restore them on to 2012.

We are migrating to 2014 as my company is on 2005 SP3 right now.


Replacement for Nested SELECT statements for every row in SELECT clause I've created main SELECT statement like below. For every row in Base table (base on IDKey I've added columns with values from result of INNER JOIN of few tables.

SELECT
  IDKey,
  Name,
  (select Amount from Table1 INNER JOIN Table2 ... where ID=IDKey) AS AmountTypeX,
  (select Amount from Table1 INNER JOIN Table2 ... where ID=IDKey) AS AmountTypeY
FROM BaseTable

BaseTable:

IDKey Name
----------
1     Ian
2     John
3     Ann
...

INNER JOIN of few tables Table1, Table2... :

ID Amount Type
--------------
1  100    X
1  200    Y
2  300    X
2  400    Y
3  500    X
3  600    Y
...

Result looks like:

IDKey  Name  AmountTypeX  AmountTypeY
-------------------------------------
1      Ian   100          200
2      John  300          400
3      Ann   500          600
...

Do you have any suggestion how to avoid this nested SELECT statements in the main query at the begining and made this query simpler and more effective ?

I wast thinking about PIVOTING of the INNER JOIN result to look like below (all data for particular ID in one row) and then JOIN with main query but Have no idea how to do this and is it possible.

ID  X   Y
-----------
1   100 200
2   300 400
3   500 600
...

It's the classic EAV pattern, which is a strong indicator of a design issue. I hope you understand that this design is against the core rules of relational databases. I have a blog post on this subject if you want to dig deeper.

However, if the design can't be changed, you can use a PIVOT query to extract your data:

DECLARE @BaseTable TABLE (
    IDKey int PRIMARY KEY,
    Name varchar(50)
)

INSERT INTO @BaseTable
VALUES
(1, 'Ian'),
(2, 'John'),
(3, 'Ann');

DECLARE @Table1 TABLE (
    ID int,
    Amount int,
    Type char(1),
    PRIMARY KEY (ID, Type)
)

INSERT INTO @Table1
VALUES
(1, 100, 'X'),
(1, 200, 'Y'),
(2, 300, 'X'),
(2, 400, 'Y'),
(3, 500, 'X'),
(3, 600, 'Y');

SELECT *
FROM @BaseTable AS B
INNER JOIN (
    SELECT ID, X AS AmountX, Y AS AmountY
    FROM @Table1 AS T
    PIVOT (MIN(Amount) FOR Type IN ([X],[Y])) AS P
) AS T1
    ON B.IDKey = T1.ID;


Microsoft Synchronizer Framework I'm planning to synchronize a pc that has SQL Server 2008 running on it, and the server has a SQL Server 2008 R2 running on it. Are there going to be any problems with data loss or compatibility?

We use Microsoft Sync Framework at enterprise to sync in one way or two ways several
database tables. It works amazing!. Sync framework is totally compatible with SQL 2008 R2 and you wont have any data loss if you configure it well.

The only thing you need to do is provisionate the source and destination tables, and you
can do it with .NET to have automatic task.

Here you have a microsoft example to sync SQL DATABASES with .sql setup files and project
example:

http://code.msdn.microsoft.com/Database-Sync-SQL-Server-7e88adab/view/SourceCode#content

Here you have a full example to sync data between sql servers:

http://rvramos.wordpress.com/2010/10/19/learning-microsoft-sync-framework-2-0-the-easier-way/

Provisioning server example:

//server connection
SqlConnection serverConn = new SqlConnection(@”Data Source=.\sqlexpress;Initial Catalog=Test;Integrated Security=True”);
//scope name
string scope = “sync_customer“;
// Create a scope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(scope);
//GetDescriptionForTable gets the schema of the table, so that tracking tables and triggers
//can be created for that table
DbSyncTableDescription tableDetailsCustomer = SqlSyncDescriptionBuilder.GetDescriptionForTable(“Customer”, serverConn);
//add the table description to scope
scopeDesc.Tables.Add(tableDetailsCustomer);
//Create the scope provisioner
SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(scopeDesc);
//We have to skip the creation of the table Customer because it is already created
serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
//Now we can provision the
serverserverConfig.Apply(serverConn);

Sync Example:

//server connection
SqlConnection serverConn = new SqlConnection(@”Data Source=Server1\sqlexpress;Initial Catalog=Test;Integrated Security=True”);
//client connection
SqlConnection clientConn = new SqlConnection(@”Data Source=Client1\sqlexpress;Initial Catalog=Test;Integrated Security=True”);
//scope name
//should be equal to the one we provision in the server and client
string scope = “sync_customer“;
//create an orchestrator
SyncOrchestrator sync = new SyncOrchestrator();
//specify that we will upload to the server first then download from the server
sync.Direction = SyncDirectionOrder.UploadAndDownload;
//specify the client
sync.LocalProvider = new SqlSyncProvider(scope, clientConn);
//specify the server
sync.RemoteProvider = new SqlSyncProvider(scope, serverConn);
//synchronize it
sync.Synchronize();

EDIT:

It is recommended to use between databases with the SAME version (Sql server 2008 standard, enterprise, express). but with same version to avoid problems. Never tried with 2008 to 2005 but I read is not convenient.


Other than restarting SQL Server, is there any way to force the SQLCLR AppDomain to be reset? I want to force the AppDomain being used by SQLCLR to be reset. How can I do that besides restarting the SQL Server instance?

There is a more elegant solution that won't affect all other assemblies: just change the PERMISSION_SET of one of the assemblies in the app domain (app domains are per user).

ALTER ASSEMBLY [AssemblyName] WITH PERMISSION_SET = {1 of the 2 levels that
                                                      this assembly is not current at}

Just remember that you will need to set the PERMISSION_SET back to what it was. Also, you need to access a method in the assembly before changing the PERMISSION_SET will unload it; changing an assembly that is not currently loaded into an app domain that is active, but with another assembly, has no effect on the app domain (App Domains are per-DB, per-User, not per-Assembly).

UPDATE
The method described above is the most fine-grained approach where it will only unload that one App Domain. But, it does require that the assembly can be set to one of the other two levels. For assemblies marked as SAFE it will only be possible if either

the database is set to TRUSTWORTHY ON, or
the assembly is signed and a Login, based on an asymmetric key that is itself based on the same signature as the assembly, exists and has been granted either the EXTERNAL ACCESS ASSEMBLY or the UNSAFE ASSEMBLY permission

In this case you can simply turn the TRUSTWORTHY setting ON and then immediately back OFF again and that will unload all App Domains in that particular database:

ALTER DATABASE CURRENT SET TRUSTWORTHY ON;
ALTER DATABASE CURRENT SET TRUSTWORTHY OFF;

If you only have one App Domain in the database anyway (and I suspect this is the case 95%, or more, of the time), then both of the methods described here have the same net effect. And in that situation, the ALTER DATABASE method seems simpler as it doesn't require specifying a particular object name nor does it require knowing what the original PERMISSION_SET was.

ALSO, if you only have a single App Domain then the ALTER DATABASE method is simpler even in the case where the database is either already set to TRUSTWORTHY ON or you have set up the key-base login with the appropriate permission. If you are using a key-based login then you can set TRUSTWORTHY to ON and then OFF again as mentioned above. But if you already have TRUSTWORTHY set to ON, then just reverse it and set it to OFF and then immediately back to ON:

ALTER DATABASE CURRENT SET TRUSTWORTHY OFF;
ALTER DATABASE CURRENT SET TRUSTWORTHY ON;


Grouping and counting rows by existence of matching criteria in any of several columns Disclaimer: I've inherited a database which is probably not quite as "normal" as I would like, and I'm limited to Jet/MS Access 2010 for my db backend and interface, as well my choice of reporting software (currently Style Intelligence, which has its own data interpretation interface (supports standard SQL, etc.). I'm open to some data restructuring if necessary.

I have several tables of assessment data from which I need to count rows that "pass" or "fail" the assessments (one row = one assessment). Each row has a series of columns which contain the answers YES, NO, NA or null, and these answers comprise the assessment. A NO value in any of the answer columns constitutes a fail. I need a count of rows that contain NO in any of these columns, and then a count of the remainder (or, no "No" values in any of the answer columns. I don't need to count the number of NOs on one row, just group and count by the existence of at least 1 NO answer in any of the given columns. There are other tables which store assessment data in a similar structure, but instead use 1, 2 and 3 as the "yes, no, NA" answers, because they're storing the value from an MS Access form's option group.

Here are some examples of rows that I have, and how they would be grouped for counting:

AsmtID  Q1      Q2      Q3      Q4      Q5      Q6      Q7      Desired grouping

3       NO      YES     YES     YES             NA              FAIL
4       NO      YES     YES     YES     YES     NO      NA      FAIL
5       YES     YES     YES     YES     YES     YES     YES     PASS
6       YES     YES     YES     YES     YES     YES     NA      PASS

It's worth noting that I'm working with a very small amount of data, certainly no more than 200 rows from any one table at a time, so computational efficiency isn't a big concern for me. My best guess would be some routine which steps through each column on a row and then returns Pass or Fail based on the first NO value found. I would really appreciate any insight on how to interpret this data. Thank you in advance!

SELECT COUNT(a.*) as Total, "Fail" as Status
FROM MyTable as a
WHERE a.Q1 = "NO" or a.Q2 = "NO" or a.Q3 = "NO" or
    a.Q4 = "NO" or a.Q5 = "NO" or a.Q6 = "NO" or a.Q7 = "NO"
GROUP BY "Fail"
UNION ALL
SELECT COUNT(b.*) as Total, "Pass" as Status
FROM MyTable as b
WHERE b.Q1 <> "NO" and b.Q2 <> "NO" and b.Q3 <> "NO" and
    b.Q4 <> "NO" and b.Q5 <> "NO" and b.Q6 <> "NO" and b.Q7 <> "NO";
GROUP BY "Pass"

The syntax may have some problems, but I think this will do it for you. If you have lots more columns to add, then it isn't too difficult to write them out once and then copy, paste, and replace in notepad.

You may need to wrap all the column comparisons in a NZ() to protect against null values. Example: WHERE Nz(b.Q1,"") <> "NO"


Passing Variable in json_value in sql server 2016 declare @id int
set @id = 1754
select
(JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap."1754".value')) CorrespondanceNumber,
(JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap.***"'+cast(@id as varchar)+'"***.value')) Agency,
(JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap."1754".value')) Protocal
from actions_new a
left join action_data ad on ad.id_ref = a.id
where baid = 32 and displayrequestid < 100;

Its working with sql server 2017 but error showing in sql server 2016.

  Error : Msg 13610, Level 16, State 1, Line 3 The argument 2 of the
  "JSON_VALUE or JSON_QUERY" must be a string literal. Problem in
  converting INT to vachar "'+cast(@id as varchar)+'"

As answered for JSON_QUERY in this question the same is true for JSON_VALUE as well.

Docs on JSON_VALUE

  In SQL Server 2017 (14.x) and in Azure SQL Database, you can provide a
  variable as the value of path.

Before SQL Server 2017 you would have to build the query dynamically.

Examples

Printing the query on sql server 2016

declare @id int;
set @id = 1754;

DECLARE @SQL VARCHAR(MAX);

SET @SQL ='
select
(JSON_VALUE(cast(ad.data_map as varchar(max)),''$.dataMap."1754".value'')) CorrespondanceNumber,
(JSON_VALUE(cast(ad.data_map as varchar(max)),''$.dataMap.***"'+cast(@id as varchar(4))+'"***.value'')) Agency,
(JSON_VALUE(cast(ad.data_map as varchar(max)),''$.dataMap."1754".value'')) Protocal
from actions_new a
left join action_data ad on ad.id_ref = a.id
where baid = 32 and displayrequestid < 100;';
PRINT (@SQL);

Executing the query on sql server 2016

declare @id int;
set @id = 1754;

DECLARE @SQL NVARCHAR(MAX);
DECLARE @Parameterdefinition nvarchar(20) ='@id int';
SET @SQL ='
select
(JSON_VALUE(cast(ad.data_map as varchar(max)),''$.dataMap."1754".value'')) CorrespondanceNumber,
(JSON_VALUE(cast(ad.data_map as varchar(max)),''$.dataMap.***"+cast(@id as varchar(4))"***.value'')) Agency,
(JSON_VALUE(cast(ad.data_map as varchar(max)),''$.dataMap."1754".value'')) Protocal
from actions_new a
left join action_data ad on ad.id_ref = a.id
where baid = 32 and displayrequestid < 100;';

EXEC SP_EXECUTESQL
@SQL,@Parameterdefinition,@id;

DB<>Fiddle


Changing SQL Server 2000 usernames We're running a SQL Server 2000 instance with users tied to Windows accounts. Many of the usernames have spaces in them and that is causing us some problems now. We would like to be able to rename them to get the spaces out, but I'm unable to figure out how this is done. I would love some help on this.

Some notes

When you create a login, then the name must match AD exactly
EXEC sp_grantlogin 'DOMAIN\Andy Soell
When you map the login to a database user, it can be renamed
EXEC sp_grantdbaccess 'DOMAIN\Andy Soell', 'Dilbert'

The 2nd bit will fix your issue with implied schema. This happens if don't qualify schema (eg dbo) because SQL Server 2000 has pretty much no user-schema separation like later versions

Note: you can't rename users in SQL Server 2000 IIRC: need to run sp_revokedbaccess then sp_grantdbaccess. If you own objects because of your implied schema then you'll need sp_changeobjectowner. I think SQL EM will prompt you.

That is, you should always qualify schema in SQL Server.

CREATE TABLE dbo.viewname...

SELECT * FROM dbo.myTable

EXEC dbo.MyProc

Then no need to change the users


dbcc checkdb runs for interminably long time, 50% of the time I've got a nightly maintenance job (largely taken from the ola hallengren maintenance package) that runs database backups and runs an an integrity check on every database.

The integrity check part is running online, against production dbs, and usually completes in about ~2.5 hours - but sometimes it runs for much, much longer (12+ hours in some instances).

There aren't any other scheduled jobs or other activity that is running on the days where it hangs vs. the days where it doesn't, and the db it's usually hanging on is only about 170gb.

I took a look at my wait stats:

And my write stalls:

But I'm not a DBA by training and am not sure what to actually do about this information, or if it's even where I should be looking for this particular issue.

Was there an error from CHECKDB for those occasions when it took so long? Anything useful in the errorlog file for those occasions?

If CHECKDB find an error, it might do a "deep dive" to further investigate (traverse the linked list for the index instead of just checking a bitmap, mentioned here: https://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-complete-description-of-all-checkdb-stages/).


How to create an additional boolean column in a view whose value depends on a subquery? Following is a view that I have created

CREATE VIEW [ForMasterTable]
WITH SCHEMABINDING AS
SELECT
[MasterID],
[MasterColumnData],
[DetailID],
[DetailColumnData],
[SomeOtherID],
[SomeOtherColumnData]

FROM [MasterTable]
LEFT OUTER JOIN [DetailTable] ON [MasterTable].[DetailID]= [DetailTable].[DetailID]
LEFT OUTER JOIN [SomeOtherTable] ON [MasterTable].[SomeOtherID]= [SomeOtherTable].[SomeOtherID]

I want to add an additional boolean column to this view the value of which will depend on the result of a condition on a subquery

CASE
WHEN EXISTS (
     SELECT ErrorID
     FROM [ErrorTable]
          WHERE DetailID IN
                (SELECT [DetailID]
                 FROM [DetailTable]
                      WHERE MasterID = [MasterTable].[MasterID])
            )
THEN
     1
ELSE
     0
END

How do I something like this in my view? Also I am assuming that a nested query would exponentially degrade the performance as the number of rows go up. Any help with this would be greatly appreciated.

The most important thing is that you don't need to assume that performance will get worse. SQL server can rewrite some subqueries to avoid the kind of row by row operation that I think you're assuming will happen. It's certainly possible that your subquery will slow down the view quite a bit but you can test this yourself. In fact, you can test this much more effectively than us because we have no idea about your table structures, indexes, statistics, size of the tables, etc.

Run queries that use the view with and without the bit column. Does the query with the new column get exponentially slower? Get an actual execution plan and analyze that. Run your queries after SET STATISTICS IO, TIME ON and see how IO, CPU time, and elapsed time change with different queries.

I'll give you one way to rewrite that query without a subquery. I'm assuming that [MasterID] is unique for [MasterTable].

Let's work through the logic of your subquery. One way to express it is you want to start with [MasterTable].[MasterID] and get all of the associated [DetailID] values from [DetailTable]. If there is at least one row in [ErrorTable] with a matching DetailID then the bit column has a value of 1. Otherwise it has a value of 0.

You can rewrite that to be a derived table. If the derived table has all of the distinct [MasterID] that have a corresponding entry in [ErrorTable] then you can do a LEFT OUTER JOIN to it. If the join column is not NULL then there was a match so your bit column has a value of 1. Otherwise it has a value of 0.

Here is one implementation:

SELECT
[MasterID],
[MasterColumnData],
[DetailID],
[DetailColumnData],
[SomeOtherID],
[SomeOtherColumnData],
CASE WHEN bool.[MasterID] IS NOT NULL THEN 1 ELSE 0 END
FROM [MasterTable]
LEFT OUTER JOIN [DetailTable] ON [MasterTable].[DetailID]= [DetailTable].[DetailID]
LEFT OUTER JOIN [SomeOtherTable] ON [MasterTable].[SomeOtherID]= [SomeOtherTable].[SomeOtherID]
LEFT OUTER JOIN
(
    SELECT DISTINCT dt.[MasterID]
    FROM [DetailTable] dt
    WHERE EXISTS
    (
        SELECT 1
        FROM [ErrorTable] et
        WHERE dt.DetailID = et.DetailID
    )
) bool ON [MasterTable].[MasterID] = bool.[MasterID];


SQL Index order and performance based on cardinality and data I've been working quite a bit on a query that's not operating very efficiently on a DB2 database. The thing will return up to 30k rows or so and take up to 10 seconds. I've been working on getting this thing to run faster and I think I've finally tracked it down to an improper index. A new and more appropriate index should fix the problem, but I'm not entirely sure how this should be ordered as there appears to be different ways for different data.

The query itself isn't terribly complicated. Here's a brief rundown:

SELECT
...
B.TYPE_CODE,
B.FIELD,
...
FROM A
INNER JOIN B
ON A.ID1 = B.A_ID1
AND A.ID2 = B.A_ID2
--A few inner joins and some left joins--
WHERE
B.TYPE_CODE IN (:parameter) --No more than four values within the IN clause

A few details about these tables:

A has about 900k rows and B has about 2.7 million.
B.TYPE_CODE has 15 possible values. Stats are accurate. The four
parameters together are contained in about 400k rows (about 15% of
all entries in the table). The A_ID1/2 columns are much more unique,
occurring 15 times at most, of course.
B already has an index on (TYPE_CODE, A_ID1, A_ID2). This is being
used, but it's not optimal at all
There are 6 rows being selected by the DB2-optimized query. It grabs
A_ID1 and 2 along with TYPE_CODE and FIELD, of course, but it's also
grabbing B.ID1 and B.ID2, which together form the primary key for B.
These fields are not used in the original query, but the optimizer is
including them, although I don't think they're used in any joins or
filters. They're just there.
B will have far more reads than write operations on it

The join between A and B is taking a third of the query cost. The explain plan shows a FETCH operation that uses a particular index four times. I was able to get a VERY detailed explain plan report and found that this operation is returning all 400k rows, then performing the join, which only needs about 26k of them.

I found a short-circuit method that added this to the predicate:

AND COALESCE(B.TYPE_CODE, B.TYPE_CODE) = B.TYPE_CODE

Logically this doesn't affect the result set, but it does tweak the optimizer. Adding this cut the query time in half. Checking the detailed explain showed that it added an additional filter factor that reduced the number of estimated rows in that FETCH operation to 26k or so, which is about right. This in turn reduced the estimate for the number of rows from A that would be returned overall and enabled index scans on A that hadn't been used prior due to the optimizer thinking it could be grabbing nearly half of A. The result was a much faster query. However, the COALESCE is a hacky bit and not suited for production, but it was handy for finding an issue.

So now it falls to creating a new index on B to fix this problem. The DB2 query analyzer suggested an index on all six fields, starting with B.TYPE_CODE and following with the rest in no particular logical order. This would cover all selected fields and the table itself would not need to be touched, which certainly has its advantages. It has also been suggested that an index on (B.A_ID1, B.A_ID2, B.TYPE_CODE) could be used as the more selective columns are first, which would narrow the results faster. I've seen different suggestions based on the cardinality of the data, so I'm wondering if anyone has some advice on how to construct the index here. I've been doing a lot of reading on indexes lately, but it can be difficult to find a good guide on certain aspects.

UPDATE: Well, DB2 refuses to use the new index and keeps using the existing index of (TYPE_CODE, B.ID1, B.ID2), and those latter two are the primary key for B, not to be confused with B.A_ID1, B.A_ID2. I think it has to to do with the optimized query grabbing B.ID1 and B.ID2 even though the original query doesn't touch those fields and using them as a SORTKEY. It may be that any index will need to include those fields.

If ID1 is more selective than ID2, then construct your index over B with (ID1, ID2, Type_Code) , and make sure there is an index over A by (ID1, ID2).

If ID2 is more selective than ID1, then construct your index over B with (ID2, ID1, Type_Code) , and make sure there is an index over A by (ID2, ID1).

If you are using DB2 for i, then you might also try an Encoded Vector Index [EVI] over B on (Type_Code), in combination with an index by (ID1, ID2) [or the reverse order, as above].  However, the EVI seems unlikely to help in this case.


MySQL XtraDB/Galera Cluster High Availability RamDisk *SuperFast* Ok, So this is going to be a speculative question, mostly design oriented and a rather long one. I'd grab a cuppa' covfefe, if I were you.

Preface: So I have been researching into databases and wanted a really fast (Like really-really) database(engine) with the following must haves,

ACID Compliance
In-Memory-ish for blazing fast IO.
Persistant (well...duh)
Scalable as in cluster/master-slave/etc
High-Availability(HA)
MySQL Drop-In replacement
Open Source
Should run on commodity server (IYKWIM)

So judging by my over optimistic list of requirements you'd already jump to ....ummm

Alright, alright jokes aside, I know if innodb_buffer_pool_size is tweaked properly it'll run off of the memory most of the time, but I say

It ain't in-memory yo!

But you'd say Hey, its 2k18 people might have already created some 100% in memory DBs, Right? Umm... actually they have but each have their own tradeoffs.

VoltDB Community Edition
Everything seems fine until you realise it isn't a drop in replacement. It needs some stored procedures-ish commands in java which requires you rewriting whole of your application or atleast the db layer/driver/etc of your php app. So? DEALBREAKER!!!
MemSQL, Well this seems a pretty strong contender for our "Best OpenSource In-Mem Scalable SQL Acid DB ever" contest. Only for, the memSQL Boss be like...

Needless to say, memSQL needs atleast 4 cores and 8Gigs of RAM at minimum, and recommeded is pretty insane at 4 cores and 32 gigs per core!!!!
Also the community version of memSQL(which btw, isn't fully OpenSource!, its just free) doesn't support high availability, as its a paid feature. Also its NoSQL. So? DEALBREAKER!!!

All other NoSQLish dbs like membase, Redis, Memcached, etc are prelly much ruled out.

So now for my genius idea!!!

I was wondering if we could run an XtraDB/Galera Cluster with all the instances running off of a RAMDisc with regular snapshots?

It gets all the checkboxes ticked.

Just hear me out, First addressing the elephant in the room, We know that running full mysql Dbs off of RAMDiscs is pretty umm...bold, put in the most polite of ways. So what happens if the server crashes/shuts off/etc we loose a node. While all our DB Cluster as a whole is still alive and kicking a**. All we have to implement is booting the db up from last snapshot and syncing back with the cluster which btw the clusters are pretty good at, inherently!

OK, peeps, Don't go all salty on me, if you see a flaw in my implementation, guide me.

OK, 3 Galera nodes, with:

Each having all disk stuff sitting in RAM disks.
Enough RAM to make that possible.
The nodes separated geographically so that a volcano, flood, etc, cannot take out all of them at once.
High speed network.

Notes:

That will allow for full and automatic recovery from one server loss.
The only delay during a failure is switching clients to one of the other two nodes.
But, you may find that the network is the slow point.  (Entanglement has not been perfected yet.)
No snapshots needed.  (You always have 2 other nodes with full copies of the data.)


Full database backup I'm using third party tool for making schedule database backups. I need to make one additional full backup. Should it be affected on the scheduled backups? If yes,  is there any solution?

The short answer is yes.

As usual, after full backup follows differential backups, that completely depend on the last full backup. It means that if the database failure occurs there will be no chance to apply scheduled differential backups because they will be with another LSN.

The solution is to use COPY_ONLY option.


Best way to choose which columns to index to improve performance I use a MSSQL Database for my web application and now the user audit(USER_AUDIT) records in the application have crossed beyond 5 million records/rows. Every day the performance of the application is degrading and the queries for the audit table are pretty straight forward from the UI.
The default sorting on the data is on the TimeStamp i.e. the time when the record was created so I was wondering if adding an INDEX on the TimeStamp column would improve the performance of the application or because the the ID column(Incremental IDs) already has the INDEX on it, its sufficient and would not improve the performance any further.
Also sometimes the records need to be filtered using the username which is also one of the columns in the same table but because the user names are not unique I was wondering if it would help at all to add INDEX to that column as well.
The clustered index is on the ID column on the table - Would it be helpful if I change the Clustered INDEX to be on a different column
Or
Based on the above information would it be of any help to add Non-clustered Indexes to either TimeStamp or Username or both columns
I come from a programming background with very limited knowledge of the Database management so please pardon any missing information and please feel free to let me know if I need to add more information to the Question.
I would really appreciate it even if you could point me in the right direction.

Determining indexes for tables is not easy. I can offer some general guidelines that will help, but the specifics are going to be up to you to determine through testing on your systems with your queries.
The first, and most important, index you set is your clustered index. You only get one, so you want to make it right. Further, the key on your clustered index is also going to be the key for looking up all your non-clustered indexes. So, generally, you want to try satisfy three general criteria for your clustered index. First, the cluster should go on the column, or columns, that define the most commonly used path to the data. You don't say how the data is most commonly filtered above, so I can't make a recommendation based on that. Second, because the clustered index key is used for lookups in other indexes, as much as you can, you want it to be as small a data type as possible. You'll frequently see an ID column like you have because it satisfies this. However, don't get hung up on this. If a 'Name' column is the most frequently used path to the data, even though it's a wider key than an int, it can still be the clustered index. Further, the clustered index has to be unique. That's not to say the column must be unique. SQL Server can make the cluster unique internally if it has to. Yes, this adds overhead, so unique data is preferred, but again, driven by reality, not arbitrary rules.
Nonclustered indexes. Use as few as you can. However, create them as you need them. From the sounds of things, sorting on timestamp frequently, you might want an index there. However, this is where things get fun, that doesn't mean it will get used to sort the data. It depends on the other filtering criteria and how the data is being retrieved, which columns, the exact WHERE clause, etc. Same thing goes for creating a nonclustered index on Name. It could be a good choice. However, what other columns are being retrieved?
As was already mentioned, if you add a nonclustered index, but it only satisfies the filter criteria for the query, you'll still have to go to the clustered index to look up the rest of the columns. This is fixed by either adding additional columns to the nonclustered key (although, only do that if they're also used for filtering), or by using the INCLUDE key word.
All of this has to be tested by looking at the execution plans for your query. That will show you which indexes are being used and how they are used. So plan on pulling that information out.
Yeah, this is an enormous topic and I've only scratched the surface here. I have a 900 page book on query tuning and a 600 page book on execution plans. There's that much to talk about.


PostgreSQL UPSERT issue with NOT NULL columns I want o know if it's possible to do an upsert when one of the columns (column1 or column2) is null, I just need a partial update, and of course, I'm trying to avoid a SELECT to verify if the record already exists.
This is an example of what I'm trying to achieve.
CREATE TABLE test(
    column1 timestamp NOT NULL,
    column2 uuid,
    status integer NOT NULL,
    id uuid NOT NULL,
    CONSTRAINT upsert_conflict UNIQUE (id, status)
);

And I have something like this:
INSERT INTO test(column1, status, id)
VALUES(now(), 0, <any uuid>)
ON CONFLICT ON CONSTRAINT upsert_conflict
DO UPDATE SET column1= now()

This one works as expected, but I also have something like this:
INSERT INTO test(column2, status, id)
VALUES(gen_random_uuid (), 0, <existing uuid>)
ON CONFLICT ON CONSTRAINT upsert_conflict
DO UPDATE SET column2= gen_random_uuid ()

I know that the INSERT is wrong because we cannot set a NULL value to column1, but assuming that I already have a record with that id and status, I would like to do the UPDATE, instead of that, I'm getting an error: ...violates not-null constraint. because of the missing column.
I'm creating these queries dynamically according to what users pass me. Sometimes I will have something like the first example, and sometimes, something like the second example, and also I don't know which column must not be null.
I hope you can help me.

Your second example never makes sense, while column1 is defined NOT NULL. You must provide a value for the required column, or the command will fail before it can even check for unique violations.
Query 1
This query only executes the UPDATE part only if either column1 or column2 are NULL and at least one actually changes (anything actually changes), but it does not allow to revert either back to NULL:
INSERT INTO test AS t
       (column1, column2          , status, id)
VALUES (now()  , gen_random_uuid(), 0     , '5beb004a-63ec-4f01-8604-37296a208e5f')
ON     CONFLICT ON CONSTRAINT upsert_conflict
DO     UPDATE
SET    column1 = COALESCE(t.column1, EXCLUDED.column1)
     , column2 = COALESCE(t.column2, EXCLUDED.column2)
WHERE (t.column1 IS NULL
    OR t.column2 IS NULL)
AND   (t.column1 IS DISTINCT FROM COALESCE(t.column1, EXCLUDED.column1)
    OR t.column2 IS DISTINCT FROM COALESCE(t.column2, EXCLUDED.column2));

But COALESCE() never kicks in for this particular setup. column1, being defined NOT NULL, cannot be NULL, neither in the table nor in the input VALUES. So column2 has to be NULL or nothing happens. Hence we can never reset column2 to NULL anyway.
Query 2
This query only executes the UPDATE part only if either column1 or column2 are NULL and at least one is actually being changed -even if back to NULL:
INSERT INTO test AS t
       (column1, column2, status, id)
VALUES (now()  , NULL   , 0     , '5beb004a-63ec-4f01-8604-37296a208e5f')
ON     CONFLICT ON CONSTRAINT upsert_conflict
DO     UPDATE
SET    column1 = EXCLUDED.column1
     , column2 = EXCLUDED.column2
WHERE (t.column1 IS NULL
    OR t.column2 IS NULL)
AND   (t.column1 IS DISTINCT FROM EXCLUDED.column1
    OR t.column2 IS DISTINCT FROM EXCLUDED.column2);

This time, actual input values are used, even if NULL. No COALESCE() that defaults to the existing value if the new one is NULL
db<>fiddle here
Either way, you can be sure that a row with (status, id) = (0, '5beb004a-63ec-4f01-8604-37296a208e5f') exists after executing the command successfully - even if that row might be missing from the set returned by a RETURNING clause. See:
Related:

How to use RETURNING with ON CONFLICT in PostgreSQL?


How (not) to manage SSRS report-execution history retention SQL Server 2008 R2 Reporting Services keeps a log of who runs what report, stored in the ExecutionLogStorage table of the ReportServer database.  The default retention period for that history data is 60 days.

A few months back, I wanted to increase that retention period on several of my reporting servers, so I searched for how to do it and found a couple of different blog posts which said that you update the ConfigurationInfo table in the ReportServer database.  Specifically, they said you set the Value column to the number of retention days for the row where Name = 'ExecutionLogDaysKept', and that if you set the value to 0, execution history will be kept indefinitely.  So that's what I did, and promptly forgot about it.

Fast-forward to this morning, when I ran a who-launched-what-report-when query against the ExecutionLog view, and promptly got back no rows.  While investigating, I learned about an SSMS GUI with which you can also manage report-execution history retention; the GUI evidently updates the same ConfigurationInfo row, but its labels make it fairly apparent that setting the 'ExecutionLogDaysKept' value to 0 actually translates to, "delete execution-log entries older than 0 days," or basically, "don't keep any history prior to today" -- the diametric opposite of "keep history indefinitely."

This is borne out in an examination of the stored procedure ExpireExecutionLogEntries, which references the 'ExecutionLogDaysKept' value, and in which the comments and the code itself clearly show that the correct value to indicate indefinite history retention is -1, not 0.

The authors of those blogs shall remain nameless, since they were SQL folks with whom I was not especially familiar and whose advice I therefore shouldn't have followed so blithely. I suppose this post is mostly a caveat not to make the same mistake I did; however, I do have a couple of questions:

Has anyone else experienced the same thing?
Does report-execution history retention management work the same way in
subsequent versions of SSRS?

Edit: Apologies to those unnamed blog authors, as it appears that the erroneous retention-setting info actually originates in at least one location in Books Online for 2008R2. It's still present in BOL for 2016 -- not sure whether there's still a disconnect with the underlying procedure code. Submitted a Microsoft Connect entry requesting that the documentation be corrected.

Interesting find.  I was unaware of this setting until I built a page that used the SSRS execution history table as the sole source of a report's download count.  To my surprise, the counts disappeared one day.  At that point, I discovered the default value of 60 days retention.  I recall increasing the retention setting in the GUI, but I did not set to 0.

I re-engineered my download count to be stored in a custom table and updated daily via a SQL Agent job using the SSRS history, in order to eliminate any other surprises.  There is definitely conflicting documentation on what "0" accomplishes.  However, if you set the retention days to a very large number (several hundred years), then that is essentially an infinite retention period and should accomplish your goal.


Using SQL*Plus to connect to remote DB from local server, how do I export a backup to my local server? I am trying to have my entire remote DB backup made to my local server.
Running the following command from my local server: expdp \"user/user@ip as SYSDBA\" full=Y directory=/backup/backup/ dumpfile=backup.dmp logfile=backup.log
I am met with the following error:

ORA-39002 invalid operation
ORA-39070 Unable to open log file
ORA-39087 directory name /backup/backup/ is invalid

I know the directory path on my local server does exist.

Datapump uses directory objects in the database, not direct file system paths. It can only export/import from directories visible on the database server itself. Your only possible option would be to share a directory from your local server to the database server and have Oracle write to the shared folder, but that doesn't always work either depending on the share type (plus it would be a pretty big security risk). Using datapump there is no way to export to a client system the way you are trying to do.