Why is my composite index not used for this table? I am working with a fairly complicated schema, but I've simplified it for the purposes of this question.

                Table "public.scheduler_appointment"
       Column        |           Type           |              Modifiers
---------------------+--------------------------+---------------------------------
 id                  | integer                  | not null default nextval(...)
 provider_id         | integer                  |
 title               | character varying(200)   | not null
 startTime           | timestamp with time zone | not null
 endTime             | timestamp with time zone | not null
 ... more columns ...
Indexes:
    "scheduler_appointment_pkey" PRIMARY KEY, btree (id)
    "scheduler_appointment_provider_id" btree ("provider_id")
    "scheduler_appointment_provider_id_68f646d601089728" btree ("provider_id", "startTime", "endTime")
    ... more indexes ...
Foreign-key constraints:
    "provider_id_refs_id_a536838c" FOREIGN KEY ("provider_id") REFERENCES scheduler_provider(id) DEFERRABLE INITIALLY DEFERRED
    ... more fks ...

I'm doing a complex query that includes a few joins, and slightly more complicated logic, but an EXPLAIN on that query revealed that 80% of the time was spend doing a Seq Scan on this table.

I read this article which suggested indexing on the column that will be equality tested first, then on columns that will be range tested. After adding a composite index on ("provider_id", "startTime", "endTime"), I noticed that the query plan did not change at all, and it was still doing a seq scan.

So I wrote the following smaller query to try and isolate the part of the query that should use an index.

EXPLAIN ANALYZE
SELECT * FROM scheduler_appointment
WHERE (
    "provider_id" = 14
    AND "startTime" >= '2014-07-19 14:30:00+00:00'
    AND "endTime" <= '2014-07-26 14:30:00+00:00'
)
ORDER BY "startTime" ASC;

However, this still does not use the index like I would expect.

 Sort  (cost=36.02..36.52 rows=201 width=143) (actual time=0.477..0.483 rows=57 loops=1)
   Sort Key: "startTime"
   Sort Method: quicksort  Memory: 33kB
   ->  Seq Scan on scheduler_appointment  (cost=0.00..28.33 rows=201 width=143) (actual time=0.050..0.428 rows=57 loops=1)
         Filter: (("startTime" >= '2014-07-20 00:00:00+09:30'::timestamp with time zone) AND ("endTime" <= '2014-07-27 00:00:00+09:30'::timestamp with time zone) AND ("provider_id" = 14))
 Total runtime: 0.538 ms

The first thing I noticed was that it's rewritten my filter to put the provider_id at the end. Is this the reason that it refuses to use the index, or is it simply that I don't yet have enough data in the table to make an index scan worthwhile? How would I go about testing this, or forcing it to use an index if available?

As @dezso suggested, the table with test data was not big enough for the query planner bother going to the index. After I imported a larger set of data, the query uses the index as expected.


SSRS bulk permission update 2008R2 to 2014 I'm moving SSRS reports from a 2008R2 server to a 2014 server.  I've moved all the RDLs with a 3rd party tool only to find none of the permissions exist on the target.  I'm attempting to copy the permissions over with Powershell but the script errors when attempting to set the policy with .SetPolicies.  The object members appear to be incompatible between 2008 R2 and 2014.  Is it possible to move the perms from 2008R2 to 2014 via Powershell?

$ReportServerUriSource =
'http://my2008Source/ReportServer/ReportService2010.asmx?wsdl'
$ReportServerUriTarget =
'http://my2014target/ReportServer/ReportService2010.asmx?wsdl'
$InheritParent = $true

 $rsProxy =       New-WebServiceProxy -Uri $ReportServerUriSource -
 UseDefaultCredential
$rsProxyTarget = New-WebServiceProxy -Uri $ReportServerUriTarget -
UseDefaultCredential

$SourceItems = $rsProxy.ListChildren("/MyReports", $true) | `
     SELECT TypeName, Path, ID, Name

$targetFolder = $rsProxyTarget.GetPolicies($SourceItem.Path,
  [ref]$InheritParent)

foreach($SourceItem in $SourceItems)
{
$Policies = $rsProxy.GetPolicies($SourceItem.Path, [ref]$InheritParent)

  if($InheritParent -eq $false)
 {
        $Policy = $rsProxy.GetPolicies($SourceItem.Path,
[ref]$InheritParent)
        $rsProxyTargetPath = $rsProxyTarget.GetPolicies($SourceItem.Path,
[ref]$InheritParent)
        if($rsProxyTargetPath)
        {
          $rsProxyTarget.SetPolicies($SourceItem.Path, $Policy)

           }

     }

}

Error:

Cannot convert argument "Policies", with value: "Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy1er_ReportService2010_asmx_wsdl.Policy[]",
for "SetPolicies" to type "Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy3er_ReportService2010_asmx_wsdl.Policy[]": "Cannot convert the
"Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy1er_ReportService2010_asmx_wsdl.Policy" value of type
"Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy1er_ReportService2010_asmx_wsdl.Policy" to type
"Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy3er_ReportService2010_asmx_wsdl.Policy"."
At line:21 char:15
+               $rsProxyTarget.SetPolicies($SourceItem.Path, $Policy)
+               ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : NotSpecified: (:) [], MethodException
+ FullyQualifiedErrorId : MethodArgumentConversionInvalidCastArgument

I was able to hack together a solution from a few different blogs-

function Add-SSRSItemSecurity
(
    [Parameter(Position=0,Mandatory=$true)]
    [Alias("url")]
    [string]$webServiceUrl,

    [Parameter(Position=1,Mandatory=$true)]
    [Alias("path")]
    [string]$itemPath,

    [Parameter(Position=2,Mandatory=$true)]
    [Alias("u")]
    [string]$groupUserName,

    [Parameter(Position=3,Mandatory=$true)]
    [Alias("r")]
    [string]$role,

    [Parameter(Position=2)]
    [bool]$inherit=$true
)

{

    #Fix item path if not starting with /
    if(!$itemPath.StartsWith("/")) { $itemPath = "/" + $itemPath}

    #Create Proxy
    Write-Host "[Add-SSRSItemSecurity()] Creating Proxy, connecting to : $webServiceUrl"
    $ssrsProxy = New-WebServiceProxy -Uri $webServiceUrl -UseDefaultCredential

    $type = $ssrsProxy.GetType().Namespace;
    $policyType = "{0}.Policy" -f $type;
    $roleType = "{0}.Role" -f $type;

    Write-Host "[Add-SSRSItemSecurity()] Retrieving all existing policies."
    $policies = $ssrsProxy.GetPolicies($itemPath, [ref]$inherit);

    $a = 1;
    foreach($policy in $policies)
    {

        foreach($r in $policy.Roles)
        {
            $msg = "[Add-SSRSItemSecurity()]  Existing Policy # {0} Group Name: {1}, Role: {2}" -f $a, $policy.GroupUserName, $r.Name
            Write-Host $msg
        }
        $a+=1;
    }

    $msg = "[Add-SSRSItemSecurity()] Total Existing Policies: " + $policies.Length;
    Write-Host $msg

    $Policy = $policies |
    Where-Object { $_.GroupUserName -eq $groupUserName } |
    Select-Object -First 1

    if (-not $Policy) {
        $Policy = New-Object ($policyType)
        $Policy.GroupUserName = $GroupUserName
        $Policy.Roles = @()
        $Policies += $Policy
        $msg = "[Add-SSRSItemSecurity()] Adding new policy: '{0}'" -f $GroupUserName
        Write-Host $msg
    }

    $r = $Policy.Roles |
        Where-Object { $_.Name -eq $role } |
        Select-Object -First 1
    if (-not $r) {
        $r = New-Object ($roleType)
        $r.Name = $role
        $Policy.Roles += $r
        $msg = "[Add-SSRSItemSecurity()] Adding new role: '{0}'" -f $role
        Write-Host $msg
    }

    #Set the policies
    $ssrsProxy.SetPolicies($itemPath,$policies);

}

$ReportServerUriSource = 'http://server1/ReportServer/ReportService2010.asmx?wsdl'
$ReportServerUriTarget = 'http://server2/ReportServer/ReportService2010.asmx?wsdl'
$rsProxy =       New-WebServiceProxy -Uri $ReportServerUriSource -UseDefaultCredential
$SourceItems = $rsProxy.ListChildren("/", $true) | `
         SELECT TypeName, Path, ID, Name

 $targetFolder = $rsProxyTarget.GetPolicies($SourceItem.Path,[ref]$InheritParent)
 foreach($SourceItem in $SourceItems)
 {
  $Policies = $rsProxy.GetPolicies($SourceItem.Path, [ref]$InheritParent)
      if($InheritParent -eq $false)
     {
 $x = $Policies.Count
 $y = 0
 while($y -lt $x)
 {
     $name = ($Policies[$y]).Roles[0].Name

     $z = ($Policies[$y]).Roles.Count
     $q = 0

 while($q -lt $z)
     {
  Add-SSRSItemSecurity -webServiceUrl $ReportServerUriTarget `
                     -itemPath $SourceItem.Path`
                     -groupUserName ($Policies[$y]).GroupUserName`
                     -role ($Policies[$y]).Roles[$q].Name
         $q++
  }

     $y++
 }
}
}


Does having Default Database Engine MyISAM and keeping InnoDB tags in my.ini create any problem? Recently I am getting table corruption issue very often and every time when I check the logs it says "Binlog has bad magic number; It's not a binary log file that can be used by this version of MySQL"

Every single time I've to move binary logs from it's existed location to somewhere else and then only I could start the mysql services. I need to know why it happens.

My database engine type is MyISAM, all the database files are in .MYD format. I also have InnoDB parameters in my.ini

(commented)Innodb parameters

innodb_flush_log_at_trx_commit=0
innodb_log_files_in_group=2
innodb_log_file_size=256M
default_table_type = MYISAM
transaction_isolation = REPEATABLE-READ
read_rnd_buffer_size = 8M
(commented)innodb_data_home_dir=D:/eClinicalWorks/mysql/data
innodb_data_file_path = mobiledoc_InnoDb_data1:300M:autoextend
innodb_file_per_table
innodb_additional_mem_pool_size=8M

(commented) configure innodb_buffer_pool_size according to available memory
innodb_buffer_pool_size=256M

innodb_log_buffer_size=8M

(commented)set-variable=key_buffer=16M
innodb_data_home_dir=D:\eClinicalWorks\\mysql\\data

Questions

Will keeping InnoDB tags enabled in my.ini create problem in?
Do you think these tags are creating problem?

Need help ASAP.

Regards
Suhaas

Binary Logs
As for your binary logs, the bad magic number is making reference to an encoding done at the beginning of the a binary log. It is a base64 constant that looks something like : 0xfe 0x62 0x69 0x6e. This is referred to the binlog magic number. If this encoding is not in this format, it would be reported as corrupt.
You should erase all binary logs and start fresh.
In order to clear them, run this in the mysql client
mysql> RESET MASTER;
mysql> SHOW MASTER STATUS;

This will erase all binary logs and show you the first binary log.
I have written posts on this issue before

Oct 28, 2012 : Binlog has bad magic number
Dec 26, 2011 : mysql emits BINLOG rows even though binlog_format=STATEMENT

InnoDB Options
It is OK for the InnoDB options to be around. Keep in mind that every time mysql is restarted, InnoDB's infrastructure is validated

mobiledoc_InnoDb_data1 exist in the innodb_data_file_path (D:/eClinicalWorks/mysql/data)
mobiledoc_InnoDb_data1 must be at least 300M
innodb_data_home_dir has ib_logfile0/ib_logfile1
innodb_log_file_size=256M (checks if ib_logfile0/ib_logfile1 are 256M)
innodb_buffer_pool_size=256M (allocates the 256M in RAM)
innodb_log_buffer_size=8M (allocates the 8M in RAM)

If none of your tables are InnoDB but you want to keep the present InnoDB settings and corresponding files, I have a great suggestion:
Add this under the [mysqld] section of my.ini
[mysqld]
skip-innodb

then run
C:\> net stop mysql
C:\> net start mysql

This will make mysqld startup faster because all the steps for InnoDB initialization/validation are bypassed. The memory InnoDB is configured to use (264M [256 + 8]) will not be allocated.
Give it a Try !!!


Why does PostgreSQL require its own user/group for installation? When installing PostgreSQL via MacPorts (and I assume via other means too), a postgres:postgres user/group is created. Are there any reasons why this user/group is needed?

The reason is that it is wise to run the database processes under a separate account.  At least one advantage of this is that the OS processes as safer in the case of the database server being hacked.  On the other side the database related files are safer from the 'normal' user accounts too - the PostgreSQL cluster (or at least its data) is relatively easy to destroy with a well-aimed single-file deletion.

If you check your passwd file you may find lots more similar users for other services, I imagine on similar grounds.


sql query using analytic functions The table is laid out like

   name, id, attr1, attr2, data_date
    a,    1,  sals, ksdjf, 05-25-2018
    b,    1,  laskd, lskd, 05-20-2017
    c,    2,  klss,  kjfe, 03-20-2018
    d,    2,  aow , lww ,  02-10-2018
 .....

select name, id, attr1, attr2, data_date from table1
where (id,data_date) in
(select id, max(data_date) from table1 group by id)

The above query works. Essentially for each ID, I want to select the row with the max( data_date ). I am sure there is a better way with analytic functions.

Results of my query should be

name, id, attr1, attr2, data_date
a, 1, sals, ksdjf,05-25-2018
c, 2, klss, kjfe, 03-20-2018

Appreciate any help to rewrite it better. Thank you.

I believe this is what you are looking for, using a window function:

select name,
    id,
    attr1,
    attr2,
    max(data_date) over (partition by id) as maxdata_date
from table1

Results:

name    id  attr1   attr2   maxdata_date
a       1   sals    ksdjf   2018-05-25
b       1   laskd   lskd    2018-05-25
c       2   klss    kjfe    2018-03-20
d       2   aow     lww     2018-03-20

http://sqlfiddle.com/#!18/109b5/1


How to change Postgres primary key column values? My postgres database has a column called "id" that runs from 40,000,000 to about 50,000,000. The "id" column is the primary key. I need to change the "id" column values such that they span different numbers in order to merge this database with another.

How can I go about generating code to change the values from 40,000,000 - 50,000,000 to, say, 0 - 10,000,000?

The table definition is

CREATE TABLE public.keyvaluehistory (
   id bigint NOT NULL
      DEFAULT nextval('keyvaluehistory_id_seq'::regclass),
   segkey text NOT NULL,
   dvalue double precision,
   bvalue bytea,
   tstamp timestamp with time zone,
   CONSTRAINT keyvaluehistory_pkey PRIMARY KEY (id)
);

There are no foreign keys on the table.

I can afford downtime on the order of minutes/hours.

I would add another column temporarily.

The first part can run while the database is active:

ALTER TABLE keyvaluehistory ADD new_id bigint;

CREATE SEQUENCE keyvaluehistory_new_id_seq OWNED BY keyvaluehistory.new_id;

/* update in batches to avoid table bloat */

UPDATE keyvaluehistory SET new_id = id - 39999999
   WHERE id BETWEEN 40000000 AND 40999999;

VACUUM keyvaluehistory;

UPDATE keyvaluehistory SET new_id = id - 39999999
   WHERE id BETWEEN 41000000 AND 41999999;

VACUUM keyvaluehistory;

...

SET maintenance_work_mem = '1GB';

CREATE UNIQUE INDEX CONCURRENTLY keyvaluehistory_new_pkey (new_id);

The following part locks the table and requires down time.

The most time consuming part is adding the primary key, because that requires scanning the table.

/* downtime starts here */

BEGIN;

LOCK TABLE keyvaluehistory IN ACCESS EXCLUSIVE MODE;

/* catch up */

UPDATE keyvaluehistory SET new_id = id - 39999999
   WHERE new_id IS NULL;

ALTER TABLE keyvaluehistory
   DROP CONSTRAINT keyvaluehistory_pkey;

ALTER TABLE keyvaluehistory
   DROP COLUMN id;

ALTER TABLE keyvaluehistory
   ADD CONSTRAINT keyvaluehistory_pkey USING keyvaluehistory_new_pkey;

ALTER INDEX keyvaluehistory_new_pkey RENAME TO keyvaluehistory_pkey;

ALTER TABLE keyvaluehistory RENAME new_id TO id;

ALTER SEQUENCE keyvaluehistory_new_id_seq RENAME TO keyvaluehistory_id_seq;

SELECT setval('keyvaluehistory_new_id_seq', 10000001);

COMMIT;

Please test befor running it in production; I may have forgotten something.


COUNT(), GROUP BY and GROUP_CONCAT() together on MySQL My question is quite simple. I just want to count events from each client and present them in a comma separated single row.

I have this table:

+----+----------------+
| id | event          |
+----+----------------+
| 22 | a              |
| 23 | bb             |
| 24 | bb             |
| 25 | ccc            |
| 26 | ccc            |
| 27 | ccc            |
+----+----------------+
6 rows in set (0.01 sec)

So far I have this following query:

SELECT COUNT(event) AS total FROM test_table GROUP BY event;

Which gives me the following result:

+--------------+
| count(event) |
+--------------+
|            1 |
|            2 |
|            3 |
+--------------+

I'm trying to use GROUP_CONCAT() in order to show them in a single line, like this, but I tried all different approaches and I didn't get the desired result:

+--------------+
| result       |
+--------------+
| 1, 2, 3      |
+--------------+

Do you have a clue on how to do that?

Thanks in advance!

To create the result you wanted, I used a subquery and used GROUP_CONCAT() from there.

create table test
(
  id int,
  event varchar(3)
  );
  insert into test
  values
  (22,'a'),(23,'bb'),(24,'bb'),(25,'ccc'),(26,'ccc'),(27,'ccc')

select group_concat(total)
from
(
SELECT COUNT(event) AS total FROM test GROUP BY event
  )a

+---------------------+
|group_concat(total)  |
+---------------------+
|1,2,3                |
+---------------------+

DB Fiddle


sorting output by incrementing IP in mysql I want an incrementing list of IP #'s in a subnet to display on a php web page in a table. Right now, 10.1.2.3 would display after 10.1.2.10 in the sequence.

My current query is this:

SELECT * FROM `ip` WHERE `subnet` LIKE '10.1.2.0/24' ORDER BY ip;

What query do I need?

My IP field is 16 character VARCHAR.


moving to MySQL 6.0 is a good move? what is most important issues and cautious we have to do before upgrade to MySQL 6.0?

How do you do the upgrade? setting up a new slave and do yum upgrade? what is the best way to upgrade with minimal downtime?

Moving to MySQL 6.0 is highly regarded as a bad move by most people, as it only briefly existed on the control version system of MySQL A.B. plus some very unstable alpha releases. MySQL 6 promised too many features to be able to be delivered, although some of its features were later ported to 5.5 and 5.6. Others, like SQL-layer foreign keys or the Falcon engine never saw the light. The order is more or less:
5.0, 5.1, 6.0 (not released), 5.4 (not released), 5.5, 5.6, 5.7 (not yet released)

You can watch the story by yourself, recounted in first person by a former employee on this video.

Regarding the upgrade (assuming you really meant 5.6), having a minimal HA setup is a must. I wrote a more detailed guide on a previous question, but every setup is different, and all potential problems are too many to summary in an answer, even between 2 specific versions of MySQL.


Best practice Azure Datawarehouse for less data-movements I have a Table with a HASH on a Employee_Key, it also Hold a Date_From and Date_To.

To build the facts I need to change it on a by_date column.

But what is the best practice in Azure DataWarehousing with the less DataMoving as possible.

I've tried with a INNER JOIN to a Dates table that was in ROUND_ROBIN, or tried to put all the dates in a View (with a trick that only sys.objects is called), .. All working but ... not sure what is the better way on performance due to the Data-Movements between the 60 nodes ...

Any suggestions ?

Here some parts of the tables ...

CREATE TABLE [edw_hr_temp].[DIM_EMPLOYEE]
(
    [EMP_UID] [BIGINT] NULL,
    [EMP_BK] [CHAR](8) NULL,
    [EMP_DATE_FROM] [DATE] NULL,
    [EMP_DATE_TO] [DATE] NULL,
)
WITH
(
    DISTRIBUTION = HASH ( [EMP_BK] ),
    CLUSTERED COLUMNSTORE INDEX
)

1st Option a Real Table for Dates

CREATE TABLE [bi_msft].[ALL_DATES]
(
    [ADATE] [DATE] NULL
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN,
    CLUSTERED COLUMNSTORE INDEX
)

2nd option is in the Stored Procedure user this as a subquery of store it in a #tempTable

  SELECT d
    FROM (SELECT d = DATEADD(DAY, rn - 1, @FROM_DATE)
            FROM (SELECT TOP (DATEDIFF(DAY, @FROM_DATE, @TO_DATE))
                         rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
                    FROM sys.all_objects AS s1
                         CROSS JOIN sys.all_objects AS s2
                   ORDER BY s1.[object_id]
                 ) AS x
         ) AS y;

  So basically my question is, when using the 2nd variant, would this
  query run on each node and so no data movement needed of will it run
  on one node and will all be moved....

For Azure SQL Data Warehouse, unless you have have millions of records, then it's unlikely you will really benefit from clustered columnstore indexes.  In fact this could even be detrimental to performance.  For smaller volume tables, you would probably be better off with either heap (for fast load) or clustered index.  ROUND_ROBIN will at least ensure even distribution.  If you do plan to hash distribute your table, ensure your chosen hash column will give even distribution, ie has a large number of distinct values.  What is EMP_BK for example?

I would generally recommend having a table to store dates, rather than some trick construct.  The table is likely to scale better.  As the datatype is date, it again seems unlikely there will be enough rows in this table to justify clustered columnstore.  Try this with clustered index instead.

Have a read through the 'Hash distribute large tables' section in this article:

https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-best-practices

Just as an observation, all columns in your tables are nullable, which would be bad for a dimensional model (you cannot build cubes on NULLs) and generally does not represent good data modelling.

If you have a genuine performance problem, consider posting an EXPLAIN plan and give an idea of the number of rows in your table and the type of data.  As mentioned, for your larger tables, to avoid data movement, hash distribute your tables on the same column.


Difference between Sharding And Replication on MongoDB I am just confused about how Sharding and Replication work.
According to the definitions I found in the documentation:

Replication: A replica set in MongoDB is a group of mongod processes that maintain the same data set.
Sharding: Sharding is a method for storing data across multiple machines.

As per my understanding if I have 75 GB of data then by using replication (3 servers), it will store 75GB data on each server means 75GB on Server-1, 75GB on server-2 and 75GB on server-3. (correct me if I am wrong).
And by using sharding, it will be stored as 25GB data on server-1, 25Gb data on server-2 and 25GB data on server-3. (Right?).
But then I encountered this line in the tutorial:

Shards store the data. To provide high availability and data
consistency, in a production sharded cluster, each shard is a replica
set

As a replica set is 75GB in size, but shard is 25GB in size, then how can they be equivalent?
This makes me quite confused. I think I am missing something obvious. Please help me with this.

A Replica-Set means that you have multiple instances of MongoDB which each mirror all the data of each other. A replica-set consists of one Master (also called "Primary") and one or more Slaves (aka Secondary). Read-operations can be served by any slave, so you can increase read-performance by adding more slaves to the replica-set (provided that your client application is capable to actually use different set-members). But write-operations always take place on the master of the replica-set and are then propagated to the slaves, so writes won't get faster when you add more slaves.

Replica-sets also offer fault-tolerance. When one of the members of the replica-set goes down, the others take over. When the master goes down, the slaves will elect a new master. For that reason it is suggested for productive deployment to always use MongoDB as a replica-set of at least three servers, two of them holding data (the third one is a data-less "arbiter" which is required for determining a new master when one of the slaves goes down).

A Sharded Cluster means that each shard of the cluster (which can also be a replica-set) takes care of a part of the data. Each request, both reads and writes, is served by the cluster where the data resides. This means that both read- and write performance can be increased by adding more shards to a cluster. Which document resides on which shard is determined by the shard key of each collection. It should be chosen in a way that the data can be evenly distributed on all clusters and so that it is clear for the most common queries where the shard-key resides (example: when you frequently query by user_name, your shard-key should include the field user_name so each query can be delegated to only the one shard which has that document).

The drawback is that the fault-tolerance suffers. When one shard of the cluster goes down, any data on it is inaccessible. For that reason each member of the cluster should also be a replica-set. This is not required. When you don't care about high-availability, a shard can also be a single mongod instance without replication. But for production-use you should always use replication.

So what does that mean for your example?

                            Sharded Cluster
             /                    |                    \
      Shard A                  Shard B                  Shard C
        / \                      / \                      / \
+-------+ +---------+    +-------+ +---------+    +-------+ +---------+
|Primary| |Secondary|    |Primary| |Secondary|    |Primary| |Secondary|
|  25GB |=| 25GB    |    | 25 GB |=| 25 GB   |    | 25GB  |=| 25GB    |
+-------+ +---------+    +-------+ +---------+    +-------+ +---------+

When you want to split your data of 75GB into 3 shards of 25GB each, you need at least 6 database servers organized in three replica-sets. Each replica-set consists of two servers who have the same 25GB of data.

You also need servers for the arbiters of the three replica-sets as well as the mongos router and the config server for the cluster. The arbiters are very lightweight and are only needed when a replica-set member goes down, so they can usually share the same hardware with something else. But Mongos router and config-server should be redundant and on their own servers.


How do I ensure only one instance of a job is running? SQL Server 2014 Standard Edition.

We have a job that runs from 20-40 min. We only want one instance of this job running, but we don't want huge gaps between runs.

So, scheduling it every 60 min is not ideal, because there could be 30-40 dead min. Ideally, we would schedule it every 30 min, because that would give us the ideal of either running every 10 min, or running back to back.

On a Windows OS scheduled task, there are settings to control how to handle job instantiation when the job is still running.

How do I get some control on SQL Server?
How do I get to my ideal (or close to it)?

The way SQL Server Agent works is exactly how you want already - if you schedule the job for every 10 minutes, and at 11:00 it runs for 12 minutes, it won't run again until 11:20 (the 11:10 instance won't start, because an instance of the job was already running).


a query that shows the load request received through GPRS by salesman I have mobile sales system, in which the salesman sends their stock request in handheld device and through GPRS the data comes to MS SQL database. I need o show a report that shows the which request is received at what time to warehouse administrator and by clicking on request number they can see the detail of that request (each item how much quantity) on daily basis.

  WarehouseTransaction

WarehouseID  TransactionID      TransactionDate             RequestedBy      TransactionTypeID
27             36101398         2012-04-18 14:49:34.000     3                 1

  Warehousedetail

 WarehouseID PackID  Quantity
    27       4          25.000000000
    27       26         175.000000000
    27       30         25.000000000

Whenever I run this query the data should be specific to today date only.

Unless I am missing something in your explanation, it sounds like you just want to do something like the following.

If your sample data is:

CREATE TABLE WarehouseTransaction([WarehouseID] int, [TransactionID] int, [TransactionDate] datetime, [RequestedBy] int, [TransactionTypeID] int);

INSERT INTO WarehouseTransaction ([WarehouseID], [TransactionID], [TransactionDate], [RequestedBy], [TransactionTypeID])
VALUES (27, 36101398, '2012-04-18 14:49:34', 3, 1),
    (29, 1234578, getdate(), 3, 1);

CREATE TABLE Warehousedetail ([WarehouseID] int, [PackID] int, [Quantity] numeric(20, 5)) ;

INSERT INTO Warehousedetail ([WarehouseID], [PackID], [Quantity])
VALUES
    (27, 4, 25.000000000),
    (27, 26, 175.000000000),
    (27, 30, 25.000000000),
    (29, 4, 25.000000000),
    (29, 26, 175.000000000),
    (29, 30, 25.000000000) ;

Then the query will be similar to this:

select t.WarehouseID,
  t.TransactionID,
  t.TransactionDate,
  t.RequestedBy,
  t.TransactionTypeID,
  d.PackId,
  d.Quantity
from WarehouseTransaction t
inner join Warehousedetail d
  on t.WarehouseID = d.WarehouseID
where t.TransactionDate >= Cast(getdate() as date)
  and t.TransactionDate <= DateAdd(d, 1, Cast(getdate() as date))

See SQL Fiddle with Demo

Which generates the result:

| WAREHOUSEID | TRANSACTIONID |                 TRANSACTIONDATE | REQUESTEDBY | TRANSACTIONTYPEID | PACKID | QUANTITY |
-----------------------------------------------------------------------------------------------------------------------
|          29 |       1234578 | December, 24 2012 13:29:00+0000 |           3 |                 1 |      4 |       25 |
|          29 |       1234578 | December, 24 2012 13:29:00+0000 |           3 |                 1 |     26 |      175 |
|          29 |       1234578 | December, 24 2012 13:29:00+0000 |           3 |                 1 |     30 |       25 |

You did not specify what version of SQL Server but the Cast(getdate() as date) will work in SQL Server 2008+, if you are not in SQL Server 2008, then you can use the following:

select t.WarehouseID,
  t.TransactionID,
  t.TransactionDate,
  t.RequestedBy,
  t.TransactionTypeID,
  d.PackId,
  d.Quantity
from WarehouseTransaction t
inner join Warehousedetail d
  on t.WarehouseID = d.WarehouseID
where t.TransactionDate >= DateAdd(Day, Datediff(Day,0, GetDate()), 0)
  and t.TransactionDate <= DateAdd(d, 1, Datediff(Day,0, GetDate()))

Edit, if you want your transactions to contain yesterday and today in SQL Server 2005 you can use:

select t.WarehouseID,
  t.TransactionID,
  t.TransactionDate,
  t.RequestedBy,
  t.TransactionTypeID,
  d.PackId,
  d.Quantity
from WarehouseTransaction t
inner join Warehousedetail d
  on t.WarehouseID = d.WarehouseID
where t.TransactionDate >= DateAdd(Day, Datediff(Day,0, GetDate()), -1)
  and t.TransactionDate <= DateAdd(d, 1, Datediff(Day,0, GetDate()))

See SQL Fiddle with Demo


Why can't I access IIS Manager on my server with Reporting Services installed? I have a Windows Server 2008 Server running SQL Server 2012 (including SSRS). I've noticed that the site is intermittently slow on first run, and found this blog post about the cause and how to fix it.

However, when I open the server manager for either of my servers (dev and production) they don't seem to have IIS installed.

Production:

On my dev server, I installed the IIS role, but I can't see anything to do with Reporting Services there:

Why can't I see anything to do with SSRS here? The web sites for both servers work fine.

As of SQL Server 2008, IIS is no longer used by SQL Server Reporting Services. It utilizes the HTTP.sys API now. So you will not see SSRS show up as a site or application pool.

The tip you linked to deals with SQL Server 2005, which required IIS in order to use SSRS. This tip speaks of the slowness you might be seeing and how to address it in SQL Server 2008/2008 R2. I would test to see if the same results are seen in 2012 version.


Does the fact a table is big can impact the overall performance of a PostgreSQL server? If I have a table that is getting bigger and bigger (i.e. it is taking more and more storage space - currently 65GB), can this affect the overall performance of the PostgreSQL server, e.g. impact the speed of queries on other tables?
This is for a PostgreSQL 9.6 database (we plan to upgrade to 10 -> 11 -> 12 later this year), hosted on Google Cloud (Cloud SQL for PostgreSQL).

No, this shouldn't affect the speed of queries for other tables.
One way it could indirectly affect the speed is, if you regularly run queries against that big table and retrieve many rows from there (either directly or indirectly e.g. because of a Seq Scan). This could then cause data from other (smaller) tables to be evicted from the cache.
Then a query using one of the other tables needs to retrieve the data again from the hard disk which of course is slower than from cache.


Insert with different key values to avoid contraint violation I have a set of Inserts traced of a database and I want to issue them into another similar one.
The problem is that I'm getting contraint violation errors due the similar database already has the keys i'm trying to insert.

INSERT INTO "COMPONENTS" ( "ID_COMPONENT", "CODE", "DESCRIPTION", "MODEL", "RESP" ) VALUES
(39822, "101087632", "COMPONENT TEST", "TEST", "ADMIN");
INSERT INTO "COMPONENTS" ( "ID_COMPONENT", "CODE", "DESCRIPTION", "MODEL", "RESP" ) VALUES
(39823, "101087632", "COMPONENT TEST", "TEST", "ADMIN");
INSERT INTO "COMPONENTS" ( "ID_COMPONENT", "CODE", "DESCRIPTION", "MODEL", "RESP" ) VALUES
(39824, "101087632", "COMPONENT TEST", "TEST", "ADMIN");
INSERT INTO "COMPONENTS" ( "ID_COMPONENT", "CODE", "DESCRIPTION", "MODEL", "RESP" ) VALUES
(39825, "101087632", "COMPONENT TEST", "TEST", "ADMIN");

Error:

  *Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
             For Trusted Oracle configured in DBMS MAC mode, you may see
             this message if a duplicate entry exists at a different level.
  *Action:   Either remove the unique restriction or do not insert the key.

My question:
Is there a way to avoid this problem and tell the database to insert another key value if it already exists? It's important also to keep the values of other columns..

I have searched already for some possible solutions and the best approach would be to use the PL/SQL Exception to handle that. I just can't see how to do that until now.

DECLARE
BEGIN
INSERT INTO "COMPONENTS" ( "ID_COMPONENT", "CODE", "DESCRIPTION", "MODEL", "RESP" ) VALUES
(39822, "101087632", "COMPONENT TEST", "TEST", "ADMIN");
INSERT INTO "COMPONENTS" ( "ID_COMPONENT", "CODE", "DESCRIPTION", "MODEL", "RESP" ) VALUES
(39823, "101087632", "COMPONENT TEST", "TEST", "ADMIN");
INSERT INTO "COMPONENTS" ( "ID_COMPONENT", "CODE", "DESCRIPTION", "MODEL", "RESP" ) VALUES
(39824, "101087632", "COMPONENT TEST", "TEST", "ADMIN");
INSERT INTO "COMPONENTS" ( "ID_COMPONENT", "CODE", "DESCRIPTION", "MODEL", "RESP" ) VALUES
(39825, "101087632", "COMPONENT TEST", "TEST", "ADMIN");
exception
   when DUP_VAL_ON_INDEX then
   --do the insert. but how?
END;

Any suggestions?
Thanks in advance.

I'd think it's probably not a best practice to use EXCEPTION to do logic/handling, such as duplicate keys.  Here's an idea: write a stored proc.  Also create a sequence that has your alternate values that will be used in cases of duplicate keys.  You might want to start this sequence with a fairly high value like 1,000,000 (or much higher, just depends on your actual data).  Then do your inserts via this stored proc whenever you want the dupe-sub functionality.

CREATE TABLE components (
    id_component INTEGER PRIMARY KEY
  , code         VARCHAR2(255)
  , description  VARCHAR2(255)
  , model        VARCHAR2(255)
  , resp         VARCHAR2(255)
);

CREATE SEQUENCE components_alt_id_seq
  START WITH 1000000;

CREATE OR REPLACE PROCEDURE component_ins
  (  p_id_component IN components.id_component%TYPE
   , p_code         IN components.code%TYPE
   , p_description  IN components.description%TYPE
   , p_model        IN components.model%TYPE
   , p_resp         IN components.resp%TYPE )
AS
  v_is_duplicate INTEGER;
BEGIN
  SELECT count(*) INTO v_is_duplicate FROM components WHERE id_component = p_id_component;
  IF v_duplicate = 0 THEN
    INSERT INTO components (id_component, code, description, model, resp)
      VALUES (p_id_component, p_code, p_description, p_model, p_resp);
  ELSE
    INSERT INTO components (id_component, code, description, model, resp)
      VALUES (components_alt_id_seq.NEXTVAL, p_code, p_description, p_model, p_resp);
  END IF;
END;
/

EXEC component_ins (39822, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (39823, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (39824, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (39822, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (39822, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (40015, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (40016, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')

And then checked the result. Notice that the duplicate input values were automatically subbed with a value taken from the sequence.

SELECT id_component FROM components;

ID_COMPONENT
------------
       39822
       39823
       39824
       40015
       40016
     1000005
     1000006

7 rows selected.


Best fill factor for GUID clustering key We have a database that uses GUIDs for most of their columns in a data (non sequential). Does anyone have any best recommendations for fill factors on a GUID database in an hgih insert/update database?

Regarding maintenance windows, we have downtime every night, and are usually closed on weekends.

There really is no "ideal" Fill Factor since there are too many other factors to consider.

What is the actual page size of the table?
How many variable length columns are in the table? If not "none, then: How likely / often are the variable length fields updated to a length greater than their initial value?

If you have a table with a row size of 3000 bytes, you would only ever be able to fit 2 of those rows on a data page in the first place. Setting the Fill Factor to 50 will initially build the pages out to have only 1 row per data page, but that only leaves room for one row to be added. In a high-insert environment, how long does that really last prior to a page-split occurring on that page? Probably not long, even with "random" placements due to using a GUID.

The only "ideal" here is to rebuild the tables using an INT or BIGINT key fields using an IDENTITY or SEQUENCE. If you need the GUID values for referencing externally because they are known by the app and/or customers, then there should be a single instance of them in one table, and that field should have a Non-Clustered index on it (this is known as an Alternate Key).

It might be a bunch of work, not only in terms of scripting, but possibly also of convincing the "business", and possibly some app code changes, but given that the Clustered Index key are copied into all Non-Clustered Indexes, PLUS PKs are used to Foreign Key to other tables, so now there are FK columns in tables with GUID, the entire system is slower due to needing to manage 16 byte values to find things instead of 4 or 8 byte values (INT or BIGINT respectively). Meaning, even if it is difficult politically, the end result will be a faster system, and people rarely complain about that.

But if you have no choice but to keep the GUID PKs, then you have no choice but to suffer the consequences of said situation (unfortunately).

Oh, and regarding using NEWSEQUENTIALID() those help because they are not as fragmented, but not fully sequential either since the initial value gets reset whenever the SQL Server NT service is restarted, so the new value could be "below" the existing set.

AND, even if you rebuilt all indexes every night, you would still be left with overly large Key values (again, copied into the NonClustered indexes) that take up 2x - 4x more space than the INT / BIGINT options, PER EACH TABLE, PER EACH ROW, PER EACH INDEX. The net-effect is that it takes longer to read everything from disk, longer to write things to disk, takes up more memory for all operations (since everything goes through the Buffer Pool), backups take longer, restores take longer, you can keep fewer backups (per the same storage), etc, etc. Meaning: this is not merely an issue of idealism like so many debates about which method or thingy is better; these are very real consequences if you have data volumes of anything but rather small databases.


High volume data aggregation I need to aggregate incoming data and save the resulting aggregates to a table. A sample of the data is below.

id,time
1967,2014-04-12 00:42:55+00
1967,2014-04-12 00:42:51+00
1972,2014-04-12 00:42:51+00
1972,2014-04-12 00:42:49+00
1972,2014-04-12 00:42:40+00

I need to do a simple aggregation:

SELECT id, MIN(time),MAX(time),COUNT(id) FROM xx GROUP BY id

I've tried a few things:

Directly updating a table each time a piece of data comes in. Was too slow of course.

Saving the incoming data to a temporary table and periodically running an aggregation query similar to the one above and then saving the result to a table. This approach was better but proved too much for my disks as volume increased.

My current approach is a daemon program that reads incoming data, aggregates it in memory and then saves the results to a table based on some time criteria. This approach works much better but still cannot scale as much as I'd like.

I am using Postgres 9.1 on a 4-disk RAID 10 system with 15k SCSI drives and 32gb of RAM. There is no disk partition, WAL and everything is on one disk. I realize this is a large performance hit. Hardware changes are problematic at this point.
This system cannot keep up with what I need to do. At the moment I need to write thousands of these aggregates per second and the solution I'll choose has to scale to much more.

I have been exploring distributed database options and I like the ideas behind Cassandra. However I am not up to speed on all the options as I'm primarily a software developer.
Moving to a distributed database in the cloud seems like a good option for a next step. Being able to sort by the min,max and count columns would be ideal but I can do that in the application layer if I have to.

Another important note is that the result of these data aggregations are actually rarely needed and are only for human consumption. A tiny fraction of these will actually be viewed. I tried generating the aggregations realtime (by querying a table that has the raw data) but they ended up with sequential scans on large tables (100m+ rows). If aggregating this much data and having most of it not seen is nonsensical I'd be happy to hear of a way to generate them upon request. My SQL skills are basic it is possible I am missing techniques that will help.

Here's some relevant parts of my postgres config:

shared_buffers = 22000MB
temp_buffers = 256MB
work_mem = 896MB
maintenance_work_mem = 16MB
fsync = off
wal_buffers = 64MB
checkpoint_segments = 128
checkpoint_timeout = 60min
checkpoint_completion_target = 0.9
random_page_cost = 2.0
effective_cache_size = 8192MB
default_statistics_target = 200
autovacuum_max_workers = 6
autovacuum_naptime = 15s
autovacuum_vacuum_threshold = 25
autovacuum_analyze_threshold = 10
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05
autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = 1000

So my questions:

How is my postgres config for these tasks?
What kind of limits for this task am I looking at with my current hardware?
Are there distributed databases well suited to my needs?

I would write the raw data to a very basic table without indexes or constraints, not even a primary key.

If you can, insert many rows at once, that's faster than single-row inserts.

If you can afford loosing some data in a catastrophic event, make that an unlogged table, that's faster.

If you can afford loosing some data in a catastrophic event and all inserts can run in a single session, make that a temporary table, that's even faster. Probably not possible, though.

Start a new partition every time unit of your choice (daily?) and aggregate the old partition into permanent storage at some opportune moment.


Connection denied from Java application I try to connect from my little Java class to Oracle11g database and connection was refused with a SQL error.

From client (SQL Developer) connection works.

This is the ERROR when I try to run java class:

java.sql.SQLException:

ORA-00604: errore riscontrato in SQL ricorsivo livello 1
ORA-20001: Denied!  You are not allowed to logon the database
ORA-06512: a line 19

    at oracle.jdbc.driver.T4CTTIo`enter code here`er.processError(T4CTTIoer.java:447)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:389)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:382)
    at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:675)
    at oracle.jdbc.driver.T4CTTIoauthenticate.processError(T4CTTIoauthenticate.java:448)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
    at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:383)
    at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:776)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:432)
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:553)
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:254)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:528)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:247)
    at DBConnection.setDBConnection(DBConnection.java:48)
    at Main.main(Main.java:31)

This is the code of my connection class:

String host = "xxx";
String service = "xxx";
String username = "xxx";
String password = "xxx";

        if(host != null && username != null && password != null) {
            // Establishing DB Connection
            try {
                connection = DriverManager.getConnection(
                        "jdbc:oracle:thin:@" + host + ":1521/" + service , username, password);
                ...

Seems like the DBA created a logon trigger that verifies connection on logins. ORA-20001-20999 are custom exceptions defined by the code that throws them, this is an environmental issue, ask the DBA.

If you have the necessary privilegs, you can check the trigger yourself, you can start with:

select * from dba_triggers where triggering_event like 'LOGON%';


Service Broker User Does Not Have Permission to Send to the Service I am using .NET SqlDependency with a named queue (not dynamically created).

My Service Broker Configuration Is

CREATE QUEUE dbo.SqlDependencyQueue;
GO
CREATE SERVICE SqlDependencyService AUTHORIZATION [dbo]
ON QUEUE SqlDependencyQueue
([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);
GO
CREATE ROUTE SqlDependencyRoute WITH SERVICE_NAME = 'SqlDependencyService', ADDRESS = 'LOCAL';
GO
GRANT SELECT to [ApplicationRole]
GO
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [ApplicationRole]
GO
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [ApplicationRole]
GO
GRANT REFERENCES on
CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
  to [ApplicationRole]
GO

GRANT RECEIVE ON SqlDependencyQueue TO [ApplicationRole]
GO

When I update a table related to the SqlDependency SqlCommand, I see in Sql Server Profiler the events

Broker:Conversation
Broker:Message Classify
Broker:Message Undeliverable
Broker:Remote Message Ackknowledgement

The error message in the Message Undeliverable event is:

This message could not be delivered because the user with ID 6 in database ID 54 does not have permission to send to the service. Service name: 'SqlDependencyService'.

User Id number 6 is ##MS_AgentSigningCertificate##

I have not seen any documentation that mentions this user id in the context of SqlDependency or Service Broker so I am not quite sure why the error is occurring.

I have tried granting this user id access to the service but the id is not recognized.

GRANT SEND ON SERVICE::SqlDependencyService TO ##MS_AgentSigningCertificate##

Msg 15151, Level 16, State 1, Line 1
Cannot find the user '##MS_AgentSigningCertificate##', because it does not exist or you do not have permission.

I thought maybe I needed to create a login for that user but the user is not browseable when creating a login.

I wound up having to grant send on the service to the role that the application was in.  I'm not sure why that is necessary given that the application is only supposed to be receiving notification of changes.   The complete SQL is

CREATE QUEUE dbo.SqlDependencyQueue;
GO
CREATE SERVICE SqlDependencyService AUTHORIZATION [dbo]
ON QUEUE SqlDependencyQueue
([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);
GO
CREATE ROUTE SqlDependencyRoute WITH SERVICE_NAME = 'SqlDependencyService', ADDRESS = 'LOCAL';
GO
GRANT SELECT to [ApplicationRole]
GO
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [ApplicationRole]
GO
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [ApplicationRole]
GO
GRANT REFERENCES on
CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
  to [ApplicationRole]
GO

GRANT RECEIVE ON SqlDependencyQueue TO [ApplicationRole]
GO
GRANT SEND ON SERVICE::SqlDependencyService TO [ApplicationRole]
GO


SQL Server 2008 Reindexing/Rebuilding Should I reindex/rebuild while my DB is up and running or should I take it offline? Also after doing some digging around I came across this article that recommends Rebuilding after 40% fragmentation and Reindexing between 10% to 40%. Can anyone verify this? My DB is a quite large and is slowing down more and more.

I'd highly recommend using Ola Hallengren's index maintenance scripts, they're very widely used.

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

I personally set the script to Reorganise at 50% and Rebuild at 80% (and only check indexes over 1000 pages). It's recommended to have this as an overnight run if you have the maintenance window.

Beware that the first time you run this it may have to do quite a lot of work.

As a side note, do you know that fragmentation is actually causing your performance slow down? It could be any number of things unless you've already narrowed this down. I'd recommend using Brent Ozar's scripts for a health check if you haven't already. Start with sp_Blitz and go from there.

https://www.brentozar.com/blitz/


Cannot change service name for Oracle I'm trying to change the service name of an Oracle 11.2.0.3 installation on a Windows 2003 server.

During installation the service name was defined with the default domain, but we'd like to get rid of that.

What I have done so far (and what has worked before) to change the service name mydb.foo.bar to mydb only:

alter system set service_names = 'mydb' scope = both;
alter database rename global_name to mydb;

Which seems to have worked:

SQL> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      mydb
db_unique_name                       string      mydb
global_names                         boolean     FALSE
instance_name                        string      mydb
service_names                        string      mydb
SQL>

(I removed some properties from the above output which are not relevant)

Then use alter system register to re-register with the listener.

This showed no effect so I restarted the database and the listener, still no luck.

The current situation is as follows:

select name from v$active_services returns:

SERVICE_ID | NAME            | NETWORK_NAME
-----------+-----------------+--------------------
1          | SYS$BACKGROUND  |
2          | SYS$USERS       |
3          | mydb            | mydb
5          | mydbXDB         | mydbXDB
6          | mydb.foo.bar    | mydb.foo.bar

So for some reason the old service name is still there and running.

When trying to stop the service using

SQL> exec dbms_service.stop_service('mydb.foo.bar');
PL/SQL procedure successfully completed.

no error is reported, but when trying to delete the service, Oracle won't let me:

SQL> exec dbms_service.delete_service('mydb.foo.bar');
BEGIN dbms_service.delete_service('mydb.foo.bar'); END;

*
ERROR at line 1:
ORA-44305: service mydb.foo.bar is running
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_SERVICE", line 454
ORA-06512: at "SYS.DBMS_SERVICE", line 343
ORA-06512: at line 1

I verified that I have no open connections using that service:

select count(*)
FROM v$session
where service_name = 'mydb.foo.bar';

returned 0 (zero)

I also re-recreated the Windows service using oradim, but to no avail.

At one point I ran

alter system set service_names = 'mydb,mydb.foo.bar' scope = both;

could that be the reason I have both now?

But after changing service_names to only a single name, the second one should have gone away, shouldn't it?

If I can't make the second service go away, it would be enough to make the instance register with the listener using the short service name, rather than the long one.

I'm sure I'm missing something pretty obvious, but I can't figure out what it is.

I'm sure I'm missing something pretty obvious, but I can't figure out what it is.

Yes it was something very obvious...

The parameter DB_DOMAIN still contained foo.bar and therefore the instance registered itself using mydb.foo.bar.

After doing a

alter system set db_domain='' scope=spfile;

and bouncing the database everything is working as expected now.


Computing values on the fly vs decrementing bucket contents Imagine you have an employee database and a table for each employee which contains their start date.  There are other tables which describe their vacation allowances based on role, seniority and month of hire.  There is also a table which contains the vacation used for each employee.

The question:

From a general, balanced perspective of performance and ease of code and maintenance, is it better to compute an employee's vacation balance by evaluating all the table data or to store the available vacation in buckets which are then adjusted as vacation is taken.

It seems to me the overhead and complexity of calculating the available vacation, then computing the vacation which has been taken is unnecessary and a more efficient and graceful solution would be to create a table which holds the available vacation, populate it at the beginning of the year and decrement it as vacation is taken.

Computers don't get tired.

Nobody has so many employees that the summation over a well-indexed set of rows of vacation actually taken would grind their servers to a halt.

What is a problem is precalculated data getting out of whack with respect to your raw transactional data.  If you're tracking vacation you're going to need to keep a list of days taken no matter what, for audit purposes, if nothing else.  What happens if an employee challenges your calculation of their vacation taken?  The only way you'll be able to justify your number is to go back and show the details.

Since you'll already have the transactional details, there's not much point in keeping a precalculated sum of these details.

From a code complexity/maintenance standpoint you're not really saving anything.  You write the code that calculates the remaining vacation entitlement once.  It's a single select statement that has three or four more keywords in it than the single select statement that would read a precalculated row.  That's not much of a savings.

What you're thinking about is often called pre-optimization and it's something to be avoided, because it actually makes things worse more often than it makes things better.


How to pass a table-valued parameter to sp_execute_external_script? I have a stored procedure that calls an external script via a SQL Server language extension. I would like for my stored procedure to use a caller supplied table valued parameter (TVP) to then submit input data to the external script:

ALTER procedure [dbo].[testTvp]
(
   @inputTvp dbo.myTvp READONLY
)
AS
BEGIN

   DECLARE @tSqlString nvarchar(100)=N'select * from @inputTvp'

   EXEC sp_execute_external_script
        @language = N'Java',
        @script = N'com.example.TestTvp',
        @input_data_1 = @tSqlString -- this doesn't work

END

When I execute this procedure:

DECLARE @myInput dbo.myTvp

INSERT INTO @myInput values ('a');
INSERT INTO @myInput values ('b');

EXECUTE [dbo].[testTvp] @myInput

I get the following error:

  Must declare the table variable "@inputTvp".

I suppose that means that @inputTvp is not visible (out of scope) to sp_execute_external_script.

Is there a way to pass in @inputTvp to sp_execute_external_script?

I know that I could store the @inputTvp data into a temp table and pass in a t-sql string to query from that temp table. But I'm trying to avoid using a temp table.

Focusing on Table-Valued Parameters (TVPs) is a bit misleading since the question isn't really concerned with them. The example code, in fact, isn't even using the TVP as a TVP, and would be the same question even if presented as asking about table variables. Meaning, the following adaptation of the OP's example code is really the same question since sp_execute_external_script is being passed a string containing a query and not the @inputTvp variable:

DECLARE @inputTableVariable TABLE
(
  [ID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  [SomeValue] NVARCHAR(50) NOT NULL
);

DECLARE @tSqlString NVARCHAR(100) = N'SELECT * FROM @inputTableVariable;';

EXEC sp_execute_external_script
    @language = N'Java',
    @script = N'com.example.TestTvp',
    @input_data_1 = @tSqlString; -- this doesn't work

Keep in mind that TVPs are meant to send structured data into SQL Server, not out of it. It's also a variable, hence it would only be available to the local context (i.e. not Dynamic SQL or any other sub-process). Still, just to be sure, I tried the following (in SQL Server 2017 so I couldn't specify "Java" as the language):

EXEC sp_execute_external_script
    @language = N'Java',
    @script = N'com.example.TestTvp',
    @params = N'@inputTableVariable dbo.[testTVP] READONLY';

and received:

  Msg 39018, Level 16, State 2, Line XXXXX
  Parameter '@inputTableVariable' uses a data type that is not supported by the runtime for 'R' script. Unsupported types are timestamp, datetime2, datetimeoffset, time, text, ntext, image, hierarchyid, xml, sql_variant and user-defined type.

That being said, you can pass in structured data (avoiding the use of temporary tables), you just need to serialize it as NVARCHAR(MAX) and pass it as a regular parameter (i.e. not an @input_data_1 query). For serialization, you can use either XML or JSON:

Via XML

DECLARE @XmlString NVARCHAR(MAX);

SET @XmlString = (SELECT *
                  FROM   @inputTvp
                  FOR XML AUTO, ROOT('data'));

PRINT @XmlString; -- DEBUG (else comment out)

EXEC sp_execute_external_script
    @language = N'Java',
    @script = N'com.example.TestTvp',
    @params = N'@inputData NVARCHAR(MAX)',
    @inputData = @XmlString;

Via JSON

DECLARE @JsonString NVARCHAR(MAX);

SET @JsonString = (SELECT *
                   FROM   @inputTvp
                   FOR JSON AUTO, ROOT('data'));

PRINT @JsonString; -- DEBUG (else comment out)

EXEC sp_execute_external_script
    @language = N'Java',
    @script = N'com.example.TestTvp',
    @params = N'@inputData NVARCHAR(MAX)',
    @inputData = @JsonString;


Estimate result size of Natural Join I have the following tables

R1(A, B, C) with 1.000 rows

R2(C, D, E) with 1.500 rows

R3(E, F) with 750 rows

where a bold letter denotes a primary key.

I need to estimate the number of rows of the natural join R1 |x| R2 |x| R3.

My textbook has proposed the following solution

The Natural join of R1, R2 and R3 will be the same no matter which way we join them (join is both associative and commutative). Size can be estimated using the strategy of first joining R1 and R2, and then joining the result with R3.
Joining R1 with R2 will yield a table of at most 1.000 rows, since C is a key for R2. Likewise, joining that result with R3 will yield a table of at most 1.000 rows because E is a key for R3. Therefore the final relation will have at most 1.000 rows!

I would have thought that the final relation will have at most 750 rows, since R3 only has 750 rows.

Is the textbook solution incorrect, or am I missing something?

You have at most 1000 rows from the natural join of R1 and R2 because there are two possible cases: either 1) all the values of  R1.C are present in R2.C (i.e. R1.C is a “foreign key” for R2), or, 2) there are values in R1.C not present in R2.C.

In the first case you have exactly 1000 rows in the join, since the for each row of R1 there is exactly one row in R2 with the same value of C, since C is a key of R2, and for a certain value of C there is only one row in it, so you can join a row in R1 with only one row in R2, and the result has exactly 1000 rows.

In the second case you have instead n in rows in the result, with 0 ≤ n ≤ 1000, where n is the number of row in R1 whose value of C is present in R2: in fact, when a row of R1 has a value of C present in R2, you obtain again a single row in the result.

Combining these two possibilities, we can say that the number of rows in R1 ⨝ R2 is a value  n, with 0 ≤ n ≤ 1000.

Now consider the second join. Since you have at most 1000 rows in R1 ⨝ R2, in each of those 1000 rows you can have a value of E which is present or not in table R3. But since E is a primary key in R3, when you join each of those n rows with R3 you can find at maximum one corresponding row in R3. So your result will have a number of rows which will be equal to m, such that m ≤ n.

Putting together these results, we know that for m, the number of rows of  R1 ⨝ R2 ⨝ R2, holds the following: 0 ≤ m ≤ 1000.

Added

(Based on the comment of @ypercubeᵀᴹ).

Note that the result can be easily generalized. If you have a natural join or an inner equijoin between k tables Ri, i = 1, ..., k, and if you can reorder the join as: R1 ⨝ R2  ⨝ ... ⨝ Rk, such that Ri ⨝ Ri+1 is on the primary key (or unique attribute) of Ri+1, then the cardinality of the result will be always less than or equal to the cardinality of R1.


DB2 Dynamic Load from cursor We have to load a number of huge tables using Load From cursor. The query formed for the same is a dynamic query as the column names are to be specified. we do the same using the below code in a procedure

For Mcnt As Mcur Cursor For
  Select Di_tablename From file_mapping  Order By Id
  Do

   For Mcnt1 As Mcur1 Cursor For Select Column_name  From Sysibm.Columns  Where Table_name=di_tablename
   Do
       Set v_colquery = v_colquery || Column_name || ',' ;
   End For;

   Set v_colquery = Substr(v_colquery, 1, Length(v_colquery)-1);

 set v_sql ='LOAD FROM (SELECT '|| v_colquery || ' FROM ' || Di_tablename  || ' ) OF CURSOR INSERT INTO ' || Di_tablename || '_LOG  ( ' || v_colquery || ') NONRECOVERABLE';
call admin_cmd(v_sql);
commit;

 END for;

The first round in loop executes just fine. But when it comes to the second it  gives the below error.

The cursor specified in a FETCH statement or CLOSE statement is not open or a cursor variable in a cursor scalar function reference is not open.. SQLCODE=-501, SQLSTATE=24501, DRIVER=4.18.60

All the cursors are implicit cursors(do correct me if  i am wrong). Unclear as to which cursor is being referenced and what exactly the issue is.

It was because of Commit while using  Cursor.

Adding WITH HOLD to the cursor definition resolved the Issue.


Common practices to determine what database technology to use Supposing I've started a new project that needs a database for persistence purposes.

What are common practices to determine which database technology (MSSQL, MySQL, MongoDB, etc...) would best fit the requirements of the project?

What are important considerations to keep in mind when looking for a proper database technology?

Adding a no-answer as I don't know of a formal method for choosing a database architecture (remember you buy a architecture with special backup requirement, HA feature, hardware requirements).
Basically it boils down to

You need the optimal database for the enterprise not only your project (If a  "optimal" database for your project would be DB2 on AIX is of no use to your project if all your operating team does only Windows and SQL Server)
Do you need HA? which one? Switchover? active-active?
What type of disaster recovery do you need?
Formal (e.g. customer requirements, supported OS) and legal requirements
What type of infrastructure is on the customer site (SAN? virtualization? cloud?)
Monitoring? Backup? (If your backup software does not support a RAC database or the modules for backing it up cost more than the rest of your project, it's pointless to use that database. Same is valid for monitoring
Skill of your operating teams
type of data (Geodata?) Is it business critical? (your HR has slightly different impact than a simple database the tags on your website
amount of data

There is a whole lot more to consider but in my experience these are good starting points.


Oracle - Count groups in group by I have the following query:

SELECT substr("Timestamp", 1, 16) "Timestamp", "Action", count(*) "Count"
FROM "MyTable"
WHERE substr("Timestamp", 1, 13) <= substr(CURRENT_DATE, 1, 13)
GROUP BY substr("Timestamp", 1, 16), "Action"
ORDER BY substr("Timestamp", 1, 16) DESC, "Action"

I want to do paging on the group, so that I e.g. get with page 1 and page-size 2 the first 2 groups with the corresponding elements.

I set up a fiddle for a better understanding. Please see: http://rextester.com/GLY48637

How can I get this paging working?

I found a solution for my problem. I can build a "group-index" and skip/take only the elements in the index. See the following script:

SELECT *
FROM
(
   SELECT x.*, ceil((rank() over (order by x."Timestamp") +
       count(1) over (partition by x."Timestamp"))/6) as pageindex
   FROM
   (
      SELECT substr("Timestamp", 1, 16) "Timestamp", "Action", count(*)  "Count"
      FROM "MyTable"
      WHERE substr("Timestamp", 1, 13) <= substr(CURRENT_DATE, 1, 13)
      GROUP BY substr("Timestamp", 1, 16), "Action"
      ORDER BY substr("Timestamp", 1, 16) DESC, "Action"
   ) x
)
WHERE pageindex > (:page - 1) * :pageSize and pageIndex <= :page * :pageSize


Row by row subtraction with single input number I want to subrtact dynamically from previous row subtraction result and as input i want to give a single number.

I have table a

CREATE TABLE a (id int, code text, qty numeric);

And data in it

INSERT INTO a (id,code,qty)
(1,'test',5),
(2,'test',3),
(3,'test',10);

1.sample - When input is 16 - Desired result would be:

id | qty
--------------
1  | 0
2  | 0
3  | 2

Calculation would be something like this

case when 16 > 5 then 16-5 else 5-16 end /* result 11, but displayed 0 */
case when 11 > 3 then 11-3 else 3-11 end /* result 8, but displayed 0 */
case when 8 > 10 then 8-10 else 10-8 end /* result 2 and displayed 2 */

2.sample - When input is 6 - Desired result would be:

id | qty
--------------
1  | 0
2  | 2
3  | 10 /* is optional to display */

Calculation would be something like this

case when 6 > 5 then 6-5 else 5-6 end /* result 1, but displayed 0 */
case when 1 > 3 then 1-3 else 3-1 end /* result 2 and displayed 2 */
case when ... end /* result 10, since input is fulfilled */

Fundamentally, this is a running total problem: for each row you are getting the running total of qty and subtracting a fixed value from it. More specifically, this is about how you want to use the running total. In this case you want to display a new qty value based on the result of the previous calculation. And there are essentially three cases here:

The result of the subtraction is negative.
The result of the subtraction exceeds the current row's qty.
The result is a non-negative value not exceeding qty.

The first case means that the supplied fixed value is more than necessary to exhaust the current running total – in other words, it exhausts all qty values from the beginning till the current row (including the current row). Therefore, we substitute a 0 for qty.

In the second case we just keep the qty, because the running total now exceeds the fixed value so much that the difference is greater than the current qty – so the latter stays intact.

The last case is when the fixed value either exactly covers all the qty values so far or is slightly less than the total but not much less: up to the amount of the current qty. In this case we show the difference between the running total and the fixed value.

The SQL for the above is this:

SELECT
  id,
  CASE
    WHEN balance < 0   THEN 0
    WHEN balance > qty THEN qty
    ELSE balance
  END AS qty
FROM
  (
    SELECT
      id,
      qty,
      SUM(qty) OVER (ORDER BY id ASC) - @fixedSum AS balance
    FROM
      a
  ) AS derived
;

where @fixedSum is the fixed value you want to subtract from the running total.

The central part of the solution is SUM(qty) OVER (ORDER BY id ASC) – the window aggregate function to get the running total of qty.

I am calculating the running totals here in a nested SELECT because the result of the subtraction needs to be referenced several times in a CASE expression, and you cannot reference a calculated column on the same level, you need to use nesting for that. The nesting can be in the form of either a derived table or a common table expression (CTE). The above solution uses a derived table.

There is a way to avoid nesting, though, and do all the calculations in a single SELECT using the functions LEAST and GREATEST:

SELECT
  id,
  qty,
  LEAST(
    qty,
    GREATEST(
      SUM(qty) OVER (ORDER BY id ASC) - @fixedSum,
      0
    )
  ) AS balance
FROM
  a

This method is much less straightforward than the one using CASE, though. Performance-wise, it would likely work the same.

The method works like this. First, you choose the greater value between the subtraction result and 0 using the GREATEST function. So, in terms of the cases listed above, if we have Case 1, GREATEST will yield 0. A non-zero result would mean we have one of the other cases.

The obtained value is then compared to the current qty and this time the lesser one of the two is chosen using the LEAST function. If we obtained 0 previously, LEAST will keep it. If we had the subtraction result and it is now found to be greater than qty, the LEAST function will give us qty (meaning this is Case 3). And if the difference happens to be less than qty, that is what LEAST will yield (Case 2).


Use trigger to synchronize columns with fields in json column on insert or update I'm a bit of a database/postgres beginner,so bear with me.
If I have a table, something like this.

CREATE TABLE testy (
    id INTEGER REFERENCES other_table,
    name varchar(128) PRIMARY KEY,
    json JSONB NOT NULL
);

I'm looking to create a trigger before insert or update that will set the columns id and name to the values of fields with the same names in json.

So for example if testy contained the below and UPDATE testy SET json = '{"id":2,"name":"jim"}' WHERE id = 1 was called.

id | name | json
---+------+-----
 1 | "jim"| {"id":1,"name":"jim"}

The desired result would be

id | name | json
---+------+-----
 2 | "jim"| {"id":2,"name":"jim"}

I wish to make this fairly generic so the column names do not need to be hard coded. Setting the column to NULL if the corresponding json field does not exist is fine.
So far I have

CREATE TABLE testy_index (
    id INTEGER PRIMARY KEY
);

INSERT INTO testy_index VALUES (1);
INSERT INTO testy_index VALUES (2);
INSERT INTO testy_index VALUES (3);

CREATE TABLE testy (
    id INTEGER REFERENCES testy_index,
    json JSONB NOT NULL
);

CREATE UNIQUE INDEX testy_id ON testy((json->>'id'));

CREATE OR REPLACE FUNCTION json_fn() RETURNS TRIGGER AS $testy$
    DECLARE
        roow RECORD;
    BEGIN
        FOR roow IN
            SELECT column_name FROM information_schema.columns WHERE table_name = 'testy'
        LOOP
            NEW.roow.column_name = (NEW.json->>roow.column_name);
        END LOOP;
    END;
$testy$ LANGUAGE plpgsql;

CREATE TRIGGER json_trigger
BEFORE INSERT OR UPDATE ON testy FOR EACH ROW
EXECUTE PROCEDURE json_fn();

Which doesn't work as you can't use roow.column_name that flexibly. I've tried playing around with EXECUTE with no success, although it's possible I'm just not doing it right.

Any help would be greatly appreciated!!

EDIT: The motivation for this is so that foreign key constraints can be placed on something that behaves as a json field.

EDIT: plv8 is great. Used a modified version of @Daniel Vérité 's answer so that columns not represented as fields in json will be nulled

CREATE OR REPLACE FUNCTION json_fn() RETURNS trigger AS
$$
  var obj = JSON.parse(NEW.json);
  for(var col in NEW){
      if(col == 'json'){
        continue;
      }
      if(col in obj){
        NEW[col]=obj[col];
      }else{
        NEW[col]=null;
      }
  }
  return NEW;
$$
LANGUAGE plv8;

CREATE TRIGGER json_trigger
BEFORE INSERT OR UPDATE ON testy FOR EACH ROW
EXECUTE PROCEDURE json_fn();

Dynamic fields are notoriously difficult in plpgsql. In particular there's no way we can write new.variable := something where variable stands for a column name.

See How to set value of composite variable field using dynamic SQL for ways that involve querying the catalog at runtime.

Personally, I'd suggest a simpler solution with the plv8 language.

CREATE FUNCTION json_fn() RETURNS trigger AS
$$
  var obj = JSON.parse(NEW.json);
  for (var key in obj) {
    NEW[key]=obj[key];
  }
  return NEW;
$$
LANGUAGE plv8;

CREATE TRIGGER json_trigger
BEFORE INSERT OR UPDATE ON testy FOR EACH ROW
EXECUTE PROCEDURE json_fn();

Otherwise, you may consider if your implementation is not simplified by moving the json field out of this table and into a separate table, assuming it's still compatible with your general intention.


Occasional error: Could not obtain information about Windows NT group/user 'domain\user', error code 0x54b I have a query that will impersonate as a specific domain user to send email with attachment. This impersonation is required as it needs to read a file at some other server to be able to attach the file to the email.

The query is as follows

EXECUTE AS login='domain\user' -- To impersonate
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'profile_name',
    @recipients = @recipient,
    @copy_recipients = @cc,
    @blind_copy_recipients = @bcc,
    @subject = @subj,
    @body = @mail_body,
    @body_format = 'HTML',
    @from_address = 'email@address.com',
    @file_attachments = @files;
REVERT

Sometimes I get error when running the query with error message as:

Could not obtain information about Windows NT group/user 'domain\user', error code 0x54b.

The weird thing is that, if I execute this query multiple times, it will work and it will send out the email.

What could possibly be wrong?

SQL Server version: 11.0.2100

I would strongly advise against using sysadmin privileges to simply send an email... have you ever hear of the principle of least privilege!? This wouldn't solve your problem, just provide a work around! Similarly, you would be enabling the login to drop databases, change passwords, and create all sorts of chaos!

Have you checked the SQL error logs, or the event logs. It may also be a good idea to check the logs on your DC, to see if there is any information there.

Are you having any problems with the account that the agent job is being execute as? Presumably this has the appropriate permissions for what you are asking? when you are executing the script you are executing it under your context, not the context of the agent. This is likely why it works for you all the time.

Without any additional information, preferably from your logs, it's difficult to help any better than this


PostgreSQL 9.1 - Asynchroneous commits performance boost During peak hours our users experience a lot of timeouts due to an overloaded database server. We are currently trying to make optimizations in order to cope with the high traffic. We read in the documentation of PostgreSQL that Asynchroneous commits might be an option for boosting performance when small data loss in case of a server crash is acceptable. We run the backend for an iPhone multiplayer game with quite simple, but a lot of, requests per second. Since it's a game and not a financial system we are running, the risk of data loss seems acceptable.

Does anyone have experience in difference in performance comparing synchroneous/asynchroneous commits?
Is there any risks with using this option, apart from data loss in case of a server crash?
Is there any risk that the queue of uncommitted transactions will keep growing since we will serve more requests?

Our setup
PostgreSQL, Nginx, Ruby on Rails, Unicorn

Your risk is data loss, not data corruption. An ACID platform guarantees transactions are

atomic,
consistent,
isolated, and
durable.

With asynchronous commits, you give up "durable". For your application, that seems a defensible decision.

If you have some transactions that need to be durable (new user registrations?) you can have that, too. PREPARE TRANSACTION is always synchronous; so is SET synchronous_commit = on. But it will interfere a little with all those asynchronous commits.

  Is there any risk that the queue of uncommitted transactions will keep
  growing

No, not if my understanding is correct. The WAL writer periodically writes all unwritten WAL records to disk. It doesn't see 1000 unwritten WAL records and decide, "Well, I can write 17 of them this time." If there are 1000 unwritten WAL records when the timer fires, it writes all 1000 to disk.

If your database server is constantly and heavily overloaded, I guess users might experience some "jerks" in performance if the WAL writer pushes a huge backlog to disk.

In every useful database application, you eventually have to think about upgrading hardware. Sometimes, the only defensible solution is a faster box.

PostgreSQL 9.1's wal_writer_delay setting.


SQL Server full backup location I created a full backup with SQL Server 2014 Management Studio. After that I removed it and created Differential backup, everything worked with no errors.
How the server knows against what differential backup needs to be done if original full backup was removed?

SQL Server creates a differential backup bitmap in all databases.
This bitmap contains a bit for each extent (8 pages) in the database.
Upon completion of a full backup, SQL Server clears the contents of each bit in the bitmap.
When pages are written to (ie rows are updated, deleted, inserted, etc, etc), the differential bitmap is updated to reflect the modified pages.
When SQL Server takes a differential backup, it uses the differential backup bitmap to determine what pages need to be backed up.
If you delete the full backup upon which the differential backup is based, the differential backup becomes useless.
To prove this, you can use the following test-bed:
USE master;
GO

CREATE DATABASE [Test];
GO

Backup the database:
BACKUP DATABASE [Test]
TO DISK = 'D:\SQLServer\Backups\Test_Full_Backup.bak'
WITH FORMAT
    , INIT
    , SKIP
    , STATS = 1;
GO

Add something to the database:
USE Test;
GO

CREATE TABLE dbo.SomeDDL
(
    ID INT NOT NULL
);
GO

Take a differential backup:
USE master;
GO

BACKUP DATABASE [Test]
TO DISK = 'D:\SQLServer\Backups\Test_Diff_Backup.bak'
WITH DIFFERENTIAL
    , STATS = 1;

Delete the full backup (this requires xp_cmdshell to be enabled):
EXEC xp_cmdshell 'DEL D:\SQLServer\Backups\Test_Full_Backup.bak';

Attempt to restore the differential backup:
RESTORE DATABASE [test]
FROM DISK = 'D:\SQLServer\Backups\Test_Diff_Backup.bak'
WITH RECOVERY;

Several errors occur:

Msg 3117, Level 16, State 1, Line 9
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 9
RESTORE DATABASE is terminating abnormally.

For details about how differential backups work, see this MSDN page.


Turn off listener logging in oracle 12c release 2 I am trying to turn off listener logging in oracle 12c R2 (SAP on oracle), but I am getting a TNS-12508 error. I have verified that the listener.ora is owned by the oracle user and I am performing the task as same oracle user, but I am still getting an error.

File Permissions

-rw-r--r--    1 oracle   oinstall       1151 Oct 23 13:48 listener.ora

Listener Error

LSNRCTL> set current_listener LISTENER_AAA.
Current Listener is LISTENER_AAA

LSNRCTL> set LOG_STATUS OFF
Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=AAA.WORLD))
TNS-12508: TNS:listener could not resolve the COMMAND given

LSNRCTL> set log_status off
Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=AAA.WORLD))
TNS-12508: TNS:listener could not resolve the COMMAND given

Am I missing something here?

This can happen if you've got ADMIN_RESTRICTIONS_LISTENER set to ON in your listener.ora file.

Turning it off should resolve the problem.


How do Databases Ensure Uniqueness of Primary Keys, Particularly UUID Keys? First off, I am assuming that it is possible for a database to generate but not insert a duplicate UUID value for a column that has a unique constraint.

Is there any intelligent way that databases typically create UUID values for unique columns or does it simply go through the loop of:

Generate a random UUID value for the column.
Check to see if the value is unique in the context of that column.
Insert the value into the column if it is unique, go back to step 1 if it isn't.

until it generates a unique value. I'm aware that the possibility of the database having to do a second iteration in a situation like this is infinitesimally small, but it's still possible. I'm just wondering how database engines deal with this possibility and if my description of the loop is similar to what actually happens.

Generating UUIDs (or any values, for that matter -- e.g. new sequence values) and inserting whatever into tables are two distinct and unrelated actions.

The database engine cannot know for what purpose the ID is generated -- it simply returns that ID value to the application, which uses it as it sees fit.

When the application inserts something, not necessarily a UUID it may have obtained earlier, into a column on which a unique constraint is defined, the database engine will verify the value uniqueness according to the constraint rules and return an error to the application if validation fails. It's the application's job to deal with the error as it sees appropriate. It can request a new UUID from the database, manipulate the value it already has, or simply fail.


How to Load Balance Availability Group I have 2 Servers under  SQL Server Cluster (Server 1, Server 2)

2 Databases configured with Availability Group (Database 1, Database 2)

Server 1:
Database 1 PRIMARY REPLICA
Database 2 PRIMARY REPLICA

Server 2:
Database 1: SECONDARY REPLICA
Database 2: SECONDARY REPLICA

If Database 1 is starting to consume a lot of resources and I want to add a 3rd server (Server 3) to lower the stress on Server 1 is this given example possible?

Server 1:
Database 1 SECONDARY REPLICA
Database 2 PRIMARY REPLICA

Server 2:
Database 1 SECONDARY REPLICA
Database 2 SECONCARY REPLICA

Server 3:
Database 1 PRIMARY REPLICA
Database 2 SECONDARY REPLICA

Is this possible to configure with minimum impact on users? I just want to know the possibilities of scaling horizontal with Availability groups as we have not the possibility on scaling vertically by adding resources to the server, the backups can be taken on a secondary replica right?

The million dollar question is - is it worth adding an extra server to the windows failover cluster in your current scenario ?

IMHO, NO - since you are on sql server 2012.

Why ?

SQL server 2012 and 2014 will allow read-only routing directed traffic to the FIRST available replica defined in the routing list - provided the first read-only replica is  accessible else it will pick up the next available replica.

So in sql server 2012 and 2014, even if you have multiple secondaries available for honoring read requests, its not possible to spread the read load across the available secondaries.

SQL server 2016 allows you to configure load-balancing across a set of read-only replicas.

  Is this possible to configure with minimum impact on users?

It is possible with minimum impact, but as I said above it would not be worth doing it.

How to do it ?

Add server 3 to windows failover cluster.
Take full backup from server 1 (since it is primary) and restore it with no recovery on server 3.
Take a log backup from server 1 and restore it with norecovery on server 3.
Join the database to the availability group.
Change the sync mode to Synchronous and then Failover the server1.db1 to server3.db1 and now server3.db1 will be primary.
Resume the data movement on all the secondary replicas. In my testing, the data movement gets suspended and you have to resume it.

Below is how it will look (the drawing is not pretty, but it explains what I am telling :-) ):

  the backups can be taken on a secondary replica right?

Yes, you can take copy_only full & regular log backups on secondary replica. A full backup must be performed on primary database.


PostgreSQL: Find rows for which any index of any value contained is over a given size I have a database originally built with an instance of PostgreSQL with a custom page size much larger than the default. I'm trying to migrate its contents to a standard, 8k-page database.

Because data indexed by a standard index in PostgreSQL must be no more than 1/3rd of a page size, some data contained in the old database cannot be represented in the new one without modifying index definitions. Making schema changes is not an immediately available option; dropping rows (from the tables I expect to be involved) is.

Finding overall size of an index in PostgreSQL is easy and well-documented. Finding individual rows for which associated index sizes are over a threshold is something I'm having some trouble.

How can I find rows (across all tables in a schema) containing indexed values for which the indexes of said values are too large to represent in a standard PostgreSQL build (which is to say, longer than 2712 bytes)?

Let me begin this reply with a caveat:  I've never encountered this exact problem, so I don't fully understand the nature of it.  However, I'm going to give my advice so that perhaps it gives you the insight you need to complete your task.

pageinspect extension

Recent versions of PostgreSQL come with an extension which you can install that allows you to inspect the individual pages of elements stored in your database.

First things first, all you need to do to use it is to run the command

 CREATE EXTENSION pageinspect;

and it's ready to go.

Now, once again, I don't fully understand the issue, but it appears you are concerned with the size of the indexed entry, and not necessarily the actual row entry in the heap.

For that case, you should use a function call from the pageinspect extension to query the pages of your BTree index, as

SELECT * FROM bt_page_items('idx_123', 1);

and you'll get back some results like

itemoffset  ctid   itemlen  nulls  vars  data
-----------------------------------------------------------------
1           (1,182)  16     f      f     "00 3e 9b ac b2 02 01 00"
2           (0,1)    16     f      f     "00 20 d6 fa 65 02 01 00"
3           (0,2)    16     f      f     "00 09 7b 30 66 02 01 00"
4           (0,3)    16     f      f     "00 f2 1f 66 66 02 01 00"
5           (0,4)    16     f      f     "00 db c4 9b 66 02 01 00"
6           (0,5)    16     f      f     "00 c4 69 d1 66 02 01 00"
...

Presumably, though you should check against your own data, you can see the number of bytes which the index key is using by checking the itemlen field. I am betting that you can use this to check against your pre-determined limit which you said was 2712 bytes, and in that case use this info to collect all the ctids which violate your condition.

Broadly speaking then, you need to create a PL/pgSQL function to iterate over the pages of your index(s), checking the itemlen, and collecting the ctids of the heap entries, and once completed, since you state it was OK to drop violating rows, then wrap it all up with a

DELETE FROM my_table WHERE my_table.ctid = *** your ctid list ***;

I hope this helps!


how to know how many instances of an application are running on server I've solaris server running many applications, using Oracle.
I want to know how many different databases are running within Oracle, and how much memory and cpu each of them is consuming

Use pstree -p do display process tree with pids of individual nodes. And then use the particular pid you want, and grep it from ps aux to see the CPU and memory consumption.

So the overall process will be:

$ pstree -p

init(1)─┬─apache2(6532)─┬─apache2(6575)
        │               ├─apache2(6576)
        │               ├─apache2(6716)
        │               ├─apache2(6803)
        │               └─apache2(6860)
        ├─cron(7474)
        ├─events/0(4)
        ├─getty(7507)
        ├─khelper(5)
        ├─klogd(7243)
        ├─ksoftirqd/0(3)
        ├─kthread(6)─┬─aio/0(64)
        │            ├─kblockd/0(19)
        │            ├─kjournald(693)
        │            ├─kjournald(7081)
        │            ├─kjournald(7082)
        │            ├─kjournald(7083)
        │            ├─kjournald(7084)
        │            ├─kjournald(7085)
        │            ├─kseriod(21)
        │            ├─kswapd0(63)
        │            ├─pdflush(61)
        │            ├─pdflush(62)
        │            ├─xenbus(8)
        │            └─xenwatch(7)
        ├─master(7414)─┬─pickup(6847)
        │              └─qmgr(7416)
        ├─migration/0(2)
        ├─mysqld_safe(7260)─┬─logger(7281)
        │                   └─mysqld(7280)───mysqld(7282)─┬─mysqld(7283)
        │                                                 ├─mysqld(7284)
        │                                                 ├─mysqld(7285)
        │                                                 ├─mysqld(7286)
        │                                                 ├─mysqld(7287)
        │                                                 ├─mysqld(7288)
        │                                                 ├─mysqld(7289)
        │                                                 ├─mysqld(7290)
        │                                                 └─mysqld(7291)

And then say you want to see the mysqld instance of pid 7285 's memory and cpu usage details:

$ ps aux | grep 7285
mysql     7285  0.0  2.9 58720 6016 ?        S    Feb04   0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/blah.pid --skip-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock
metin     6935  0.0  0.3  3300  724 ttyp0    R+   11:57   0:00 grep 7285

The third and fourh columns are CPU and memory respectively.


Multiple filters with joins I just read this qestion: SQL Multiple Filter - Optimal Way

I tried this with Northwind.
I filter like this:

declare @supplier int = 1
declare @category int = 1
select * from products p

WHERE (@supplier  = '' or SupplierId = @supplier)
  AND (@category = '' or CategoryId = @category)

This seems to work as expected, I get two hits: Chai and Chang.
Now I want to add filter ShipPostalCode:

declare @supplier int = 1
declare @category int = 1
declare @shippostal nvarchar(50) = '01307'
select * from products p
inner join [Order Details] od on p.ProductId = od.ProductId
inner join Orders o on o.OrderId = od.OrderId
AND (@category = '' or CategoryId = @category)
AND (@shippostal = '' or ShipPostalCode = @shippostal)

Because of the join I now have multiple rows of Chai and Chang.
These filters are users who makes selection in dropdown lists, so even if shippostalcode is chosen, I still need one hit.

distinct productName

will give me one row per product, but that's not the way to solve it, is it?

EDIT:
Thank you guys,
Yes, supplier is an int.

So If there be would another many to many relation from the Products table I can just add another AND, I guess.

Sorry, this is a very sad example, but if I add OrderDetails2 and Orders2 where OrdersDetails2 will be a junction table between Products and Orders2, I can just add add another AND (and keep on filtering), like this:

declare @supplier int = 1
declare @category int = 1
declare @shippostal nvarchar(50) = '01307'
declare @city nvarchar(50) = 'boston'
select * from products p
WHERE (@supplier  = '' or SupplierId = @supplier) AND (@category = '' or CategoryId = @category)
AND ProductId in
(
Select od.ProductId from [Order Details] od
inner join Orders o on o.OrderId = od.OrderId
Where (@shippostal = '' or ShipPostalCode = @shippostal)
)
AND ProductId in
(
Select od.ProductId from OrderDetails2 od
inner join Orders2 o on o.OrderId = od.OrderId
Where (@city = '' or City = @city)
)

As you can see, the Order2 table has a column 'City'
What do you think?

You could look for ProductIds contained in a subselect

declare @supplier int = 1
declare @category int = 1
declare @shippostal nvarchar(50) = '01307'

select * from products p
WHERE (@supplier  = '' or SupplierId = @supplier) AND (@category = '' or CategoryId = @category)
AND ProductId in
(
Select od.ProductId from [Order Details]
inner join Orders o on o.OrderId = od.OrderId
Where (@shippostal = '' or ShipPostalCode = @shippostal)
)


How to change db owner after the associated AD account is disabled The new Senior DBA requested that the previous Senior DBA's Active Directory account be disabled. He forgot to check, however, for databases where the previous DBA was the owner. We now have ~60 databases with an orphaned owner. Is there a way to fix this without temporarily re-enabling the disabled AD Account?

Note: I'm aware of best practices regarding not setting the owner to a user. My previous boss probably was too but neglected to make the change everywhere (60 out of thousands isn't as bad as it could have been).

Run this for each database.

   -- Change the database owner
    ALTER AUTHORIZATION ON DATABASE::databasename TO sa;
    GO

To generate the script to change them all:

SELECT 'ALTER AUTHORIZATION ON DATABASE::' + db.name + ' TO sa;'
FROM sys.databases db
WHERE owner_sid != 0x01


Designing Calendar table - datatypes I'm in the process of designing the Calendar table. I took some inspiration from Database design book by Louis Davidson.

What I'm confused about are the data types.

Example:

MonthName varchar(10) NOT NULL,
Year varchar(60) NOT NULL,
Day tinyint NOT NULL,
DayOfTheYear smallint NOT NULL,
Month smallint NOT NULL,
Quarter tinyint NOT NULL,

I think the Year might be a typo, but not sure why it's VARCHAR when other numeric dateparts are of numeric data types.

But why is Month SMALLINT when it could be TINYINT as well?

I thought I should design table to use the smallest correct data type to save some page space (even insignificant).

On the other hand the DATEPART function in TSQL returns INT data type, so maybe I should make all the numeric columns INT to avoid conversion cost?

From my view so far, Year field needs being as text one in some cases e.g. to support Chinese Year Name - this year 2019 is called 'Ji Hai'

But anyway, we should always have Year field as INT.


Conversion fails when converting date average to date in sql I have take date average from date and trying to get result of average date but it raising error like: Conversion failed when converting date and/or time from character string.

sql query executed as:

select
CONVERT
(
    DATE,
    LEFT(
          convert(
                  varchar(10),
                  AVG(
                        convert(
                                 int,
                                 convert(
                                           varchar(10),
                                           OnMarkDate,
                                           112
                                         )
                                 )
                       )
                  ),8
          )
)
from @tbl_XMLResult

Column values are as:

   '2001-10-11',
   '2001-10-03',
   '2001-09-24',
   '2000-05-31',
   '2001-10-15',
   '2008-01-01'

with datatype date.

This query:

gets the number of days between each of your dates and 2000/01/01.
calculate the average number of days
add the average back to 2000/01/01

SELECT
    DATEADD(DAY
        , AVG(
            DATEDIFF(day, '2000/01/01', OnMarkDate)
        )
        , '2000/01/01'
    )
FROM @tbl_XMLResult;

Output:

2002-07-30 00:00:00.000

Return type of DATEADD is datetime when a string is used. You can either:

cast the string to date
use a variable with a date type
cast the output back to date (ie. remove the time part).

Your data:

DECLARE @tbl_XMLResult TABLE(OnMarkDate date);
INSERT INTO @tbl_XMLResult(OnMarkDate) VALUES
    ('2001-10-11')
   , ('2001-10-03')
   , ('2001-09-24')
   , ('2000-05-31')
   , ('2001-10-15')
   , ('2008-01-01');