How can I add multiple partitions to a Greenplum table using dynamic SQL I have a function that adds a partition to a table, that works just fine. It takes a schema name, a table name, and a date, and generates and executes an ALTER TABLE statement to add a partition for the specified date.

I have another function that generates a date range based on the data present in the overflow partition, and calls the first function to split the overflow partition into a new partition for each date.

However, I don't think that it is possible to do it this way, as it fails with this error:

ERROR: relation 6712928 is still open (relcache.c:2417)

I suspect that this is because it's trying to do all the alter statements in parallel. Can anyone think of a way to execute the statements one at a time?

I tried executing the query in a FOR loop and calling the function once per iteration of the loop, but it does the same thing.

Here are my two functions:

 CREATE OR REPLACE FUNCTION dv_util.create_partition(p_schema character varying, p_table character varying, p_date date) RETURNS boolean AS
 $BODY$
 DECLARE
   splitter varchar(10000);
   counter integer;
 BEGIN

   splitter :=  'ALTER TABLE '||p_schema||'.'||p_table||' SPLIT DEFAULT PARTITION START ('''||p_date::text||'''::date) INCLUSIVE END ('''||(p_date + interval '1 days')::date::text||'''::date) EXCLUSIVE INTO (PARTITION "'||p_date::text||'",      default partition)';

   SELECT count(*) INTO counter FROM information_schema.tables WHERE table_schema = p_schema AND table_name = p_table;
   IF counter > 0 THEN -- table exists
     SELECT count(*) INTO counter FROM information_schema.tables WHERE table_schema = p_schema AND table_name = p_table||'_1_prt_'||p_date::text;
     IF counter = 0 THEN -- partition does not exist
       EXECUTE splitter;
       RETURN true;
     ELSE
       RETURN false;
     END IF;
   ELSE
     RETURN false;
   END IF;
 END;
 $BODY$
 LANGUAGE plpgsql VOLATILE;

 CREATE OR REPLACE FUNCTION dv_util.add_partitions(p_schema character varying, p_table character varying) RETURNS boolean AS
 $BODY$
 DECLARE
   adder varchar(10000); -- Query to generate all the partition parameters
   c_adding refcursor;   -- Cursor to iterate over the partiton parameters
   q_add RECORD;
   result boolean;
   counter integer;
 BEGIN

   adder :=          'with source as (SELECT * FROM '||p_schema||'.'||p_table||')';
   adder := adder || ', over as (SELECT * FROM '||p_schema||'.'||p_table||'_1_prt_overflow)';
   adder := adder || ', series as ';
   adder := adder || '(SELECT generate_series( greatest(';
   adder := adder || '        (   least( coalesce((select min(std_date_utc) from over),current_date)';
   adder := adder || '                 , coalesce((select (max(std_date_utc) + interval ''1 day'')::date from source)),current_date) - ''2001-01-01'')::integer';
   adder := adder || '                 , 0 )::integer';
   adder := adder || '      , (greatest( (select max(std_date_utc) from over)';
   adder := adder || '                 , current_date + interval ''30 day'')::date - ''2001-01-01'')::integer ))';
   adder := adder || 'select ((''2001-01-01''::date) + (generate_series * (interval ''1 day'')))::date dd from series';

   SELECT count(*) INTO counter FROM information_schema.tables WHERE table_schema = p_schema AND table_name = p_table||'_1_prt_overflow';
   IF counter > 0 THEN
     FOR q_add IN EXECUTE adder LOOP
       SELECT dv_util.create_partition(p_schema,p_table,q_add.dd) INTO result;
     END LOOP;
     RETURN true;
   ELSE
     RETURN false;
   END IF;
 END;
 $BODY$
   LANGUAGE plpgsql VOLATILE;

This is happening because you are trying to alter the table to add partitions when there is a cursor open (active select) on the table. The required locks conflict. You can fix that by first fetching the partition dates into an array, close the cursor and then create partitions from that array. Code sample is below.

CREATE OR REPLACE FUNCTION dv_util.add_partitions(p_schema character varying, p_table character varying) RETURNS boolean AS
 $BODY$
 DECLARE
   adder varchar(10000); -- Query to generate all the partition parameters
   c_adding refcursor;   -- Cursor to iterate over the partiton parameters
   q_add RECORD;
   result boolean;
   counter integer;
   p_part_dates date[];
   part_cntr int;
 BEGIN

   adder :=          'with source as (SELECT * FROM '||p_schema||'.'||p_table||')';
   adder := adder || ', over as (SELECT * FROM '||p_schema||'.'||p_table||'_1_prt_overflow)';
   adder := adder || ', series as ';
   adder := adder || '(SELECT generate_series( greatest(';
   adder := adder || '        (   least( coalesce((select min(std_date_utc) from over),current_date)';
   adder := adder || '                 , coalesce((select (max(std_date_utc) + interval ''1 day'')::date from source)),current_date) - ''2001-01-01'')::integer';
   adder := adder || '                 , 0 )::integer';
   adder := adder || '      , (greatest( (select max(std_date_utc) from over)';
   adder := adder || '                 , current_date + interval ''30 day'')::date - ''2001-01-01'')::integer ))';
   adder := adder || 'select ((''2001-01-01''::date) + (generate_series * (interval ''1 day'')))::date dd from series';

   SELECT count(*) INTO counter FROM information_schema.tables WHERE table_schema = p_schema AND table_name = p_table||'_1_prt_overflow';
   IF counter > 0 THEN
     part_cntr := 0;
     FOR q_add IN EXECUTE adder LOOP
       part_cntr := part_cntr + 1;
       p_part_dates[part_cntr] = q_add.dd;
       -- SELECT dv_util.create_partition(p_schema,p_table,q_add.dd) INTO result;
     END LOOP;
     for i in 1..part_cntr loop
       select dv_util.create_partition(p_schema,p_table,p_part_dates[i]) INTO result;
     end loop;
     RETURN true;
   ELSE
     RETURN false;
   END IF;
 END;
 $BODY$
   LANGUAGE plpgsql VOLATILE;


Function is counting -1 char ( wrong ) I have this function ( It counts how many characters I have in a certain string, separated by ,:

ALTER FUNCTION [dbo].[fContaRespostas]
(
 @List VARCHAR(6000),@SplitOn VARCHAR(5),@Resposta VARCHAR(50)
)
returns integer
AS
BEGIN
Declare @Qtd int
Set @Qtd = 0

Declare @Value varchar(255)
Set @Value = ''

WHILE (Charindex(@SplitOn,@List)>0)
 BEGIN

    Set @Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))

    IF ( @Value = @Resposta)
        begin
            Set @Qtd = @Qtd + 1
        end
    SET @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))

 END

 return @Qtd
END

This is the test. It needs to display 8, But it shows 7.

select dbo.fContaRespostas('5,5,5,5,55,5,5,5,5',',','5') as Qtd

If i use , in the end, it counts as 8 5.

select dbo.fContaRespostas('5,5,5,5,55,5,5,5,5,',',','5') as Qtd

What's wrong in the code? I really can't find it.

The problem with your code is probably that the logic looks for the @SplitOn character to know where the end of the SUBSTRING should be. The fix would be to simply add the @SplitOn character to the end of @List at the start of the function.

HOWEVER, I think you are going about the operation in the wrong way. First, splitting using a WHILE loop is horribly inefficient, especially since you have to recalculate @List in each iteration of the loop.

You would be far better off using an efficient string splitter and then doing a COUNT(*) combined with a WHERE val = '5'.

For example:

SELECT COUNT(*)
FROM   SQL#.String_Split4k(N'5,5,5,5,55,5,5,5,5', N',', 1) split
WHERE  split.SplitVal = N'5';
-- 8

Please note that the example uses a SQLCLR function from the SQL# library (which I am the author of, but String_Split4k is in the Free version) to do the string splitting.


How to write stored procedure use SP_HELPTEXT for getting information of objects belong to another database I am using SQL Server 2012.

I have a db lets called it A in that database I have procedure backup which get all creation information of another database which is B.

Now the problem when I am trying to use SP_HELPTEXT inside my procedure backup it give the information of database A. Any suggestion to make within database B?

Scenario:

I need to backup all functions in database B. My procedure which do that job is in database A. I used dynamic SQL to reach the names of functions when I want to get the body of them by using SP_HELPTEXT I stoped here

I would recommend against sp_helptext in general.

SELECT [schema] = s.name, [object] = o.name, m.definition
  FROM otherdatabase.sys.sql_modules AS m
  INNER JOIN otherdatabase.sys.objects AS o
  ON m.[object_id] = o.[object_id]
  INNER JOIN otherdatabase.sys.schemas AS s
  ON o.[schema_id] = s.[schema_id];

If you need the database name to be dynamic, you can do:

DECLARE @db sysname = N'otherdatabase';

DECLARE @exec nvarchar(520) = @db + '.sys.sp_executesql';

DECLARE @sql nvarchar(max) = N'SELECT [schema] = s.name,
    [object] = o.name, m.definition
  FROM sys.sql_modules AS m
  INNER JOIN sys.objects AS o
  ON m.[object_id] = o.[object_id]
  INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id];';

EXEC @exec @sql;


How To Prevent Live Servers From Detecting a Forced Failover? We are using an AlwaysOn Availability Group in SQL 2017 on Windows Server 2016 which consists of two servers on our primary site and one DR server on a remote site.

We recently did an internal IT DR test as proof-of-concept during which the rest of the company continued using the primary site. We isolated our remote DR network to simulate a catastrophic failure of our main site and tested the DR site to make sure we could bring it up and use it successfully.  The test went very well up to this point, but we had a major issue when setting things back to normal.

Because the main site was still in use during the test, my main concern was to ensure that the live database would not be corrupted by the DR database which had also been used during the test, so I dropped the DR database before we opened the VPN and I expected the main site to start automatic seeding back to the DR site.  But, because I had to force failover on the DR site, as soon as the network link was re-established, the primary site detected that the DR server had been started with the Force Quorum switch ( /fq) and it basically shut down the two live servers.  I could not force failover back to the Live servers and had to rebuild the cluster.

Question: How do I prevent the Live servers from detecting that a forced failover was performed on the DR site?

I did consider shutting down the DR server before opening the network link, but am still not sure what would happen when it’s brought online.  In any case, the remote database would need reseeding from Live.

Snapshot
Assuming the use of VMs, take a snapshot of the DR server after the source site is disconnected.  I might recommend shutting down the server first just so the snapshot is in a good, clean state.  After the DR testing is done, shut down the DR server and then revert it to the snapshot.  This is easy and cheap.  The only downsides are that the source site is unprotected during the test, and the DR node may be a little slower due to the snapshot depending on your storage infrastructure.  Oh, and your primary will have to have enough disk space to queue database replication until the sites are reconnected.

Use a Clone
Assuming that you're using VMs and have adequate disk space, clone the DR node, shut down the original DR node, and do the testing on the clone. When done, delete the clone and bring the original DR node online.  This solves two problems: 1) You don't have to resynchronize the entire database(s), and 2) You don't have to evict/join cluster nodes.

The downside of this approach is that during the time of the testing, the primary server has to queue all of the data, so transaction logs may get quite large.  In the test you performed, this was occurring so you apparently had enough disk space for your logs that it wasn't a problem.

Use Even More Clones
Some organizations create a "bubble" network at the DR site that cannot communicate with the production DR network.  Then infrastructure servers (domain controllers, DNS, etc.) are cloned and the clones are brought online in the bubble.  Then the other servers that need to be part of the DR test are cloned into the bubble.  This allows you to keep your real DR servers online and functional during the DR test.  Other than truly failing over everything, this is about the safest, because what happens if the primary site gets destroyed 5 hours after the DR test starts?  If you've disconnected the sites, you lose 5 hours of data.

The downside of this, of course, is that it can become a massive undertaking and require a lot of staff time, hardware, etc.

Evict and Rejoin
As mentioned in the comments above, you can evict the DR node from the cluster before the DR site is reconnected, and then rejoin it back to the cluster and add the node back to the availability group.  This is fairly simple and requires the least hardware, but it does have the downside of leaving your primary site unprotected during the time of the DR test.


Join Operation using index defined? I need to optimize some queries with several joins. I plan to create some indexes for these queries.

I am currently using MariaDB with TokuDB storage engine.

Should I expect the indexes for the join ?

TokuDB is a storage engine in MariaDB, as is XtraDB/Aria/MyISAM. Queries are planned by the upper layers of MariaDB (and MySQL) calling into the storage engine to help it make decisions about optimization.

What you need to do is

create your tables and load your data
create the indexes you think will help optimize your query workload
run your queries with EXPLAIN

The EXPLAIN output will help you see that good decisions are made based on your indexes.


How can I check available space of an index's pages in PostgreSQL? For tables, I can simply use pg_freespace to check available spaces like below:

postgres=> SELECT count(*),avg(1000*avail/8192)/10 FROM pg_freespace('tablename');
  count  |        ?column?
---------+------------------------
 1978140 | 1.00397105361602313000

But for indexes, it does not work.

postgres=> SELECT count(*),avg(1000*avail/8192)/10 FROM pg_freespace('tablename_pkey');
  count  |          ?column?
---------+----------------------------
 1224867 | 0.000000000000000000000000

Additionally, this is the result of same queries after VACUUM FULL:

postgres=> SELECT count(*),avg(1000*avail/8192)/10 FROM pg_freespace('tablename');
  count  |          ?column?
---------+----------------------------
 1833413 | 0.000000000000000000000000
postgres=> SELECT count(*),avg(1000*avail/8192)/10 FROM pg_freespace('tablename_pkey');
 count  |          ?column?
--------+----------------------------
 836994 | 0.000000000000000000000000

Albeit not very prominently, the documentation of pg_freespacemap mentions the following:

  For indexes, what is tracked is entirely-unused pages, rather than free space within pages. Therefore, the values are not meaningful, just whether a page is full or empty.

Your query results simply mean that you don't have a single fully free index page.


What reason could cause AWS RDS connections to spike It's my first time using RDS on AWS, I use a t2.medium instance running MySQL Aurora with default configs. The CPU usage and DB connections is quite normal until "something" happens, which causes the DB connections go all the way up to its maximum (with t2.medium it is 80 connections).

I only have one web app, running on an EC2 instance. When the DB connections reaches its maximum, the EC2 instance's CPU usage is absolutely normal (25-30%), but all attempts to connect to the DB instance results in "Too many connections".

I also checked the DB instance's CPU Utilization at the time -- it was showing no signal of high load. During the strike time, CPU Utilization dropped to 20% and kept that rate consistently.

The thing I don't understand: DB connections is at its maximum, but why is DB CPU Utilization dropped? Shouldn't it be at its maximum as well because of the computation for the queries in those connections?

Please help me understand, thank you very much.

(I had to resize the RDS instance to r4.large when the second strike happens; I'm still running it now until I find out the problem...)

If you run show processlist; it'll show you all the connections that are running against your DB.
Running show status like 'Conn%' can show how many connections are active

You may think that a high number of connections would increase the CPU but ultimately it depends what those connections are trying to accomplish, if they aren't doing much but are preventing you're main application from running their connections then the normal processing level will go down while you are not running your normal processes

The annoying thing is to be able to run the show processlist command you need to actually be connected to the DB which you wont be able to as you're at max connections.

A slight work around is to increase your max connections (if you're able to) and set up some monitoring somewhere that runs every so often (every 5 minutes would have caught that) and when you're over 50 connections, run a command that dumps out all the active connections somewhere so you can review later.


Transfer database backup from Production to Staging during business hours We have a nightly Production to Staging database sync that failed this morning. I need to copy a backup from 1 Production server to 1 Staging server and I'm not sure of the best way to do it without interrupting services or affect performance.

The backup is 15GB and the servers have a high speed connection between them. Would it be best to simply copy the backup from Production to Staging and perform the restore manually, or would this affect performance too much?

Usually copying your latest database backup (from the production backup location) to an area where you can restore it over the top of your staging area should have no effect on your production database.  The only time where this would affect it is if you're storing the backups on the same drive(s) that is hosting the live database, but hopefully you're not.  In this instance, the file copy operation will just generate extra, and competing, I/O with your normal database operations.

Your question doesn't provide enough context around how this sync process works, but a 15GB file copy isn't usually large enough (in my experience) to crash a server or cause any severe performance degradation.


Intermittent errors sending email from SQL Server Database Mail I've been seeing some SMTP errors in my database mail logs.

So I have a set up a test SQL Agent job that runs every ten minutes (it's only action is to SELECT NULL). It is set to send email notifications whenever the job completes. The addresses are internal email addresses. I have configured database mail to retry failed emails up to 4 times.

In the last day, almost half the emails encounter an error at least once but eventually get delivered. 15 of the emails failed completely. A little more than half get sent without any error at all.

Here's a list of each job execution since midnight, along with the number of errors encountered by each attempt to send mail: http://screencast.com/t/wMqolB7b
(note: there are gaps in the mailitem_id sequence because we have other jobs that also send emails).

The errors are a mix of the following two messages:

  The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2016-03-08T05:41:21). Exception Message: Could not connect to mail server. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 192.168.60.26:25). )

-or-

  The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2016-03-08T05:42:42). Exception Message: Cannot send mails to mail server. (Failure sending mail.). )

I see no pattern for the errored or failed emails. The emails themselves are all identical... it is only the time of the notification that changes.
I have verified the SMTP account settings and they are correct. The
same SMTP account settings are set on two other servers, along with a
similar test job. None of the emails on these other servers are failing at
all.
I see no evidence of problems on the Exchange server
I see no useful related info in the server's event logs

BIG QUESTION: Why are these emails failing?

Smaller question: What steps can I take to further troubleshoot this to ultimately answer the big question?

This appears to be the result of a "bug" in Windows Server firewall. We disabled the firewall service, and after rebooting the production server (yuck) all appears to be working as expected. We have an open MS case for this, but I don't have great confidence that anything will come of it.... If we do come up with something conclusive I will post back here.


CTE Error (nvarchar to numeric) I'm using a CTE to convert SSRS stored proc's into BO stored proc's, as apparently I can't use temp tables with Business Objects.

I'm having this query:

;WITH cte1
AS
(
    SELECT  cv.issue
          , cv.customfield
          , CAST(STRINGVALUE AS NUMERIC) AS priority_num
    --INTO    #temp_priority_val
    FROM    proddb1.customfieldvalue cv WITH (NOLOCK)
            INNER JOIN proddb1.customfield e WITH (NOLOCK)
                 ON  cv.CUSTOMFIELD = e.id
                 AND e.cfname = 'Issue Priority')

,cte2
AS
(
    SELECT  a.ISSUE
          , f.customvalue priority_num
    --INTO    #temp_priority
    FROM    cte1 a
            INNER JOIN proddb1.customfieldoption f WITH (NOLOCK)
                 ON  a.CUSTOMFIELD = f.CUSTOMFIELD
                 AND CAST(a.priority_num AS NUMERIC) = f.id)

SELECT * FROM cte2

I keep getting an Error converting data type nvarchar to numeric. error on this execution.  The priority_num column is an nvarchar column that is CAST in the first CTE. When I execute this within the original statement that had the temp tables it works perfectly fine. It must be something with the CTE scope?

You can't easily control the order in which SQL Server will evaluate the contents of a column that does not use the correct data type (or has mismatched precision). If you try to cast a column that is nvarchar to numeric, even if you have filters that should eliminate all non-numeric values from consideration, SQL Server can still try those first (see Erland's complaint about this on UserVoice). There are cases where you can nest CTEs until the cows come home, but SQL Server will still push or pull that evaluation to a place where you didn't expect it.

As long as aggregates and fulltext functions aren't involved (see here and here), you can force this evaluation before conversion by using a CASE expression. Here is a simplified version of your query with no CTEs:

SELECT cfv.issue,
  priority_num = CONVERT(NUMERIC(something, something),
    CASE WHEN ISNUMERIC(cfo.customvalue)=1 THEN cfo.customvalue END)
FROM
  proddb1.customfieldvalue AS cfv
  INNER JOIN proddb1.customfield AS cf
  ON cfv.CUSTOMFIELD = cf.id
  INNER JOIN proddb1.customfieldoption AS cfo
  ON cfv.CUSTOMFIELD = cfo.CUSTOMFIELD
  AND CONVERT(NUMERIC(something, something),
    CASE WHEN ISNUMERIC(cfo.customvalue)=1 THEN cfo.customvalue END) = cfo.id
  WHERE cf.cfname = 'Issue Priority';

You will need to change something, something to the proper precision/scale (or use one of the int types if you don't need decimal places). You should never declare varying types without specifying the length - this blog post is about varchar, but it really applies to all of the types.

If you are on SQL Server 2012 (it's always useful to include information like what version of SQL Server you're using), you can simplify this:

CONVERT(NUMERIC(something, something),
    CASE WHEN ISNUMERIC(cfo.customvalue)=1 THEN cfo.customvalue END)

to this:

TRY_CONVERT(NUMERIC(something, something), cfo.customvalue)

And it will actually be more reliable (since ISNUMERIC can return 1 and still fail at conversion time for specific types). I blogged about this way back in 2002.


Where to look for possible deadlock causes? We've been having several problems the past days in our production environment, and some of them seem to boil down to database issues.

Just realized we are having deadlocks (68 actually), on database "waypoint":

Can anyone please provide hints on where / how to search for possible deadlock causes?

select version();
=> PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

Found this evidence in log:

2016-03-06 06:58:39 UTC [11807-1] waypointtx@waypoint ERROR:  deadlock detected
2016-03-06 06:58:39 UTC [11807-2] waypointtx@waypoint DETAIL:  Process 11807 waits for ShareLock on transaction 370917997; blocked by process 11788.
    Process 11788 waits for ShareLock on transaction 370917865; blocked by process 11807.
    Process 11807: select * from telemetria_data.insert_tabla_instantaneo($1,$2,$3,$4,$5,$6 ) as result
    Process 11788: select * from telemetria_data.insert_tabla_instantaneo($1,$2,$3,$4,$5,$6 ) as result
2016-03-06 06:58:39 UTC [11807-3] waypointtx@waypoint HINT:  See server log for query details.
2016-03-06 06:58:39 UTC [11807-4] waypointtx@waypoint CONTEXT:  while updating tuple (2836,88) in relation "instantaneo"
    SQL statement "UPDATE telemetria_data.instantaneo SET utc = '2016-03-06 06:58:34', registro=now(), power=0, voltaje = 0, valor ='{2147}'::double precision [] WHERE imei=354676055362536 and clase=40;"
    PL/pgSQL function telemetria_data.insert_tabla_instantaneo(bigint,timestamp without time zone,integer,double precision[],integer,double precision) line 13 at EXECUTE statement
2016-03-06 06:58:39 UTC [11807-5] waypointtx@waypoint STATEMENT:  select * from telemetria_data.insert_tabla_instantaneo($1,$2,$3,$4,$5,$6 ) as result

So it seems that the following part is the problem:

UPDATE telemetria_data.instantaneo ....... WHERE imei=354676055362536 and clase=40;

Am I right?

Possible offending function:

CREATE OR REPLACE FUNCTION telemetria_data.insert_tabla_instantaneo(
    bigint,
    timestamp without time zone,
    integer,
    double precision[],
    integer,
    double precision)
  RETURNS boolean AS
$BODY$
DECLARE
    imei ALIAS FOR $1;
    utc ALIAS FOR $2;
    clase ALIAS FOR $3;
    valor ALIAS FOR $4;
    power ALIAS FOR $5;
    voltaje ALIAS FOR $6;
    num_rows int;

BEGIN

    EXECUTE 'UPDATE telemetria_data.instantaneo SET utc = ''' || utc || ''', registro=now(), power='|| power ||', voltaje = '|| voltaje ||', valor =''{' || array_to_string(valor,',')  ||'}''::double precision [] WHERE imei='|| imei ||' and clase=' || clase || ';';

    GET DIAGNOSTICS num_rows = ROW_COUNT;
    IF num_rows > 0 THEN
    RETURN TRUE;
    ELSE

    EXECUTE 'insert into telemetria_data.enabled_units (imei,clase) values ('|| imei ||','|| clase ||');';
    EXECUTE 'insert into telemetria_data.instantaneo (imei,utc,clase,valor,registro,power,voltaje) values ('|| imei ||',''' || utc || ''','|| clase ||',''{' || array_to_string(valor,',')  ||'}''::double precision [],now(),'|| power ||','|| voltaje ||');';

    RETURN TRUE;
    END IF;

    EXCEPTION
    WHEN UNIQUE_VIOLATION THEN
        EXECUTE 'insert into telemetria_data.instantaneo (imei,utc,clase,valor,registro,power,voltaje) values ('|| imei ||',''' || utc || ''','|| clase ||',''{' || array_to_string(valor,',')  ||'}''::double precision [],now(),'|| power ||','|| voltaje ||');';
        RETURN TRUE;

    WHEN foreign_key_violation THEN
        RETURN FALSE;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Related table structure:

CREATE TABLE telemetria_data.instantaneo
(
  imei bigint NOT NULL,
  utc timestamp without time zone NOT NULL,
  clase integer NOT NULL,
  valor double precision[],
  registro timestamp without time zone DEFAULT now(),
  power integer DEFAULT (-1),
  voltaje double precision DEFAULT 0.0,
  CONSTRAINT pk_telemetria_data_instantaeo PRIMARY KEY (imei, clase),
  CONSTRAINT fk_telemetria_data_clase_id FOREIGN KEY (clase)
      REFERENCES telemetria_data.clase (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);

Seem to have resulved the issue adding a SELECT FOR UPDATE to pre-lock the affected rows, as follows:

CREATE OR REPLACE FUNCTION telemetria_data.insert_tabla_instantaneo(
    bigint,
    timestamp without time zone,
    integer,
    double precision[],
    integer,
    double precision)
  RETURNS boolean AS
$BODY$
DECLARE
    imei ALIAS FOR $1;
    utc ALIAS FOR $2;
    clase ALIAS FOR $3;
    valor ALIAS FOR $4;
    power ALIAS FOR $5;
    voltaje ALIAS FOR $6;
    num_rows int;
dummy INTEGER;
BEGIN

    SELECT 1 into dummy FROM telemetria_data.instantaneo a WHERE a.imei=$1 and a.clase=$3 FOR UPDATE;
    EXECUTE 'UPDATE telemetria_data.instantaneo SET utc = ''' || utc || ''', registro=now(), power='|| power ||', voltaje = '|| voltaje ||', valor =''{' || array_to_string(valor,',')  ||'}''::double precision [] WHERE imei='|| imei ||' and clase=' || clase || ';';

    GET DIAGNOSTICS num_rows = ROW_COUNT;

    IF num_rows > 0 THEN
    RETURN TRUE;
    ELSE

    EXECUTE 'insert into telemetria_data.enabled_units (imei,clase) values ('|| imei ||','|| clase ||');';
    EXECUTE 'insert into telemetria_data.instantaneo (imei,utc,clase,valor,registro,power,voltaje) values ('|| imei ||',''' || utc || ''','|| clase ||',''{' || array_to_string(valor,',')  ||'}''::double precision [],now(),'|| power ||','|| voltaje ||');';

    RETURN TRUE;
    END IF;

    EXCEPTION
    WHEN UNIQUE_VIOLATION THEN
        EXECUTE 'insert into telemetria_data.instantaneo (imei,utc,clase,valor,registro,power,voltaje) values ('|| imei ||',''' || utc || ''','|| clase ||',''{' || array_to_string(valor,',')  ||'}''::double precision [],now(),'|| power ||','|| voltaje ||');';
        RETURN TRUE;

    WHEN foreign_key_violation THEN
        RETURN FALSE;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

There are not that many deadlocks, but they are still appearing as you can see in the following graph:


Why I got NoHostAvailable:? if I run this simple query:

SELECT * from myapp.latests WHERE organization_id = 1 and user_id = 1;

I get error:

NoHostAvailable:

Running nodetool status results into:

Datacenter: eu-central
======================
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
--  Address    Load       Tokens       Owns (effective)  Host ID                               Rack
UN  10.0.0.53  190.96 KiB  256          0.0%              80f8bf0a-c46d-41ce-bb2e-498def5b3792  1a
UN  10.0.20.5  189.35 KiB  256          0.0%              f25fe4bf-29b2-4403-8e3e-4e973a5f26ab  1b
UN  10.0.0.54  183.13 KiB  256          0.0%              c46ac05a-6cb2-48f1-a776-2c57c33e7719  1a

And here is the description:

DESCRIBE myapp;

CREATE KEYSPACE myapp WITH replication = {'class': 'NetworkTopologyStrategy', 'eu-central-1': '2'}  AND durable_writes = true;

CREATE TABLE myapp.locations (
    organization_id int,
    user_id int,
    date text,
    unix_time bigint,
    lat double,
    long double,
    PRIMARY KEY ((organization_id, user_id, date), unix_time)
) WITH CLUSTERING ORDER BY (unix_time ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE myapp.latests (
    organization_id int,
    user_id int,
    lat double,
    long double,
    unix_time bigint,
    PRIMARY KEY (organization_id, user_id)
) WITH CLUSTERING ORDER BY (user_id ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

Any idea?

Replication says eu-central-1 but the actual name is eu-central. It can't find eu-central-1 because it doesn't exist so it throws NoHostAvailable. Change the replication strategy to match the DC name.


Postgres, MVCC, and Locking I have a series of SQL statements that look like the following:

BEGIN;
SELECT counter FROM table WHERE id=X FOR UPDATE;
REALLY COMPLEX QUERY;
UPDATE table SET counter=Y WHERE id=X;
END;

I'd like to prevent the counter from being read while I recalc its value, but according to the Postgres docs "Row-level locks do not affect data querying; they block only writers to the same row."

Questions:

What's the point of an "exlusive" row lock if it doesn't prevent reads?  Is it only to prevent other transactions from taking share locks?
If I read the row with SELECT ... FOR SHARE, does that achieve the same affect as an "exlusive" lock?
Is is possible to turn off MVCC for a table/schema/database and allow in-place writes?

to 1) Any other session will read the data modified by your transaction as it was before your "BEGIN" statement as long your transaction didn't commit. As soon your transaction committed, it will read the new value of the counter. The point being that others don't have to wait and will always see a consistent data base.

to 2), 3) Why don't you try it with "ACCESS EXCLUSIVE"? (see http://www.postgresql.org/docs/current/static/explicit-locking.html)

EDIT: If you dislike locking the whole table with an "ACCESS EXCLUSIVE" lock, you could also use an "Advisory Lock" (see section 13.3.4 in the link above).


StorageEngineUsed set to TabularMetadata. For databases in this mode, you must use Tabular APIs to administer the database I am an administrator in SSAS.

I have many data warehouse servers.

In some servers I have SQL Server and SSAS on the same machine.

I successfully backup the SSAS databases and even check if the backups are healthy

Recently, however, one of our servers which is managed by a third party company based in Canada, needed to be replaced, and while migrating all the ssas databases from the old server to the new one

this is the old server:

this is the new server:

I am getting this error message while processing the database in the new server:

  This command cannot be executed on database 'DWCA' because it has been
  defined with StorageEngineUsed set to TabularMetadata. For databases
  in this mode, you must use Tabular APIs to administer the database.

It sounds like you are trying to process your tabular model (which is of version >= 1200) with XMLA script (so all in all command is in XML). That kind of tabular model has to be processed with JSON command. I had the same error message and it just turned out that I was trying to refresh the newer tabular model (of version >=1200) with XMLA command. After using JSON command - it all worked. Probably for some reason while moving the tabular model from one server to another - the version of that tabular model was somehow upgraded to >= 1200. Or you are using old SSMS which is only capable of producing XMLA commands.


Incorrect GrantedQueryMemory(KB) Value in SSMS Dashboard report Just checking by to see if someone else has witnessed the same and tried a fix or has been fixed:

Issue:

I ran below query on stackoverflow2013 database

select
   ParentId as [Post Link],
   count(id)
from posts
where posttypeid = 2 and len(body) <= 200
  and (body like '%hank%')
group by parentid
having count(id) > 1
order by count(id) desc;

and when checking in SSMS dashboard report for GrantedQueryMemory the value shows around 62 GB which does not seem correct when collecting same from dmv

From dmv it shows 62 MB

It looked to me like a bug so reported here

Using XE to capture the query being executed by the dashboard, it just pulls the raw value from sys.dm_exec_requests.  Here's the query:

exec sp_executesql @stmt=N'
          SELECT master.dbo.fn_varbintohexstr(sql_handle) AS sql_handle,
          master.dbo.fn_varbintohexstr(plan_handle) AS plan_handle,
          case when LEN(qt.text) < 2048 then qt.text else LEFT(qt.text, 2048) + N''...'' end as query_text,
        r.session_id,
        r.request_id,
        r.start_time,
        r.status,
        r.statement_start_offset,
        r.statement_end_offset,
        r.database_id,
        r.blocking_session_id,
        r.wait_type,
        r.wait_time,
        r.wait_resource,
        r.last_wait_type,
        r.open_transaction_count,
        r.open_resultset_count,
        r.transaction_id,
        r.cpu_time,
        r.total_elapsed_time,
        r.scheduler_id,
        r.reads,
        r.writes,
        r.logical_reads,
        r.transaction_isolation_level,
        r.granted_query_memory,
        r.executing_managed_code
    FROM sys.dm_exec_requests AS r
        JOIN sys.dm_exec_sessions s on r.session_id = s.session_id
        outer APPLY msdb.sys.dm_exec_sql_text(sql_handle) as qt
    WHERE s.is_user_process = CASE when @include_system_processes > 0 THEN s.is_user_process ELSE 1 END',@params=N'@include_system_processes Bit',@include_system_processes=0

Notice the r.granted_query_memory in the select, with no multiplication or division being performed on it.  Note that this number is actually in pages.  When I run your query I get these different numbers:

sys.dm_exec_requests.granted_query_memory = 1,464 pages (times 8KB per page = 11,712 KB)
sys.dm_exec_query_stats.last_grant_kb = 11,712 kilobytes
Performance Dashboard = 11,993,088 "kilobytes"

This is exactly the number of bytes granted (11,712 * 1,024 = 11,993,088).

So it appears the bug is in the SSMS user interface code here, rather than in the query, as it's getting 1,464 back from the query, and must then be multiplying by 8 (conversion from pages to KB) and then 1,024 (accidental conversion from KB to bytes).  Good call reporting it to Microsoft.

I recently reported the exact same type of calculation problem to Microsoft in a Query Store report, which is documented in this Q&A: What is 'Log Memory' in Query Store 2017.  Seems like a common problem =)


What are the circumstances where autovacuum can be disabled? I have a table where I am performing only inserts, never deletes/updates. I notice sometimes that autovacuum runs on this table, even though this is the case.

autovacuum: VACUUM ANALYZE public.twitter_shares (to prevent wraparound)

It is taking a long time, and it is having an impact on the performance of my DB. Is it safe for me to just disable autovacuum for this table? Since I am not performing deletes/updates, I don't understand why autovacuum is even needed, and why postgres decides to run it.

(to prevent wraparound)

is the key bit. PostgreSQL must do freeze vacuums where transaction IDs are set to XID_FROZEN to prevent transaction ID wrap-around, and as a result massive data corruption.

If you disable these or cancel them all the time your database will eventually go into emergency shutdown.

See the user manual on VACUUM.

You can do a manual VACUUM FREEZE during a quiet period to get this all over and done with in one go if you want.


Mysql sort results by condition I have the following scenario:

table:

    +----+-----------+
    | id | name      |
    +----+-----------+
    | 1  | zx name1  |
    | 2  | zx name7  |
    | 3  | zx name3  |
    | 4  | name5     |
    | 5  | name2     |
    | 6  | name3     |
    | 7  | zx name4  |
    +----+-----------+

I want to order the results like this:

first results will be those having zx in name ASC
second the rows that does not have zx in name in order of id

The ordered result set should look like this:

    +----+-----------+
    | id | name      |
    +----+-----------+
    | 1  | zx name1  |
    | 3  | zx name3  |
    | 7  | zx name4  |
    | 2  | zx name7  |
    | 4  | name5     |
    | 5  | name2     |
    | 6  | name3     |
    +----+-----------+

I tried the following queries:

    select id, name from table
    where id < 100
    order by (name like 'zx%'), id

    select id, name from table
    where id < 100
    order by case when name like 'zx%' then name else id end

and many variations of this but with no luck.

Can you please give me some hints

Thank you

You need to separate the 3 ordering cases:

whether the column starts with 'zx' or not
order by name for the first group
order by id for the second group

Then because the first one needs a case expression, you need to use the same case expression in the following two, with the different orders and null accordingly:

order by
    case when name like 'zx%' then 0 else 1 end,        -- 1st ordering column
    case when name like 'zx%' then name else null end,  -- 2nd
    case when name like 'zx%' then null else id end     -- 3rd

The 1st ensures that all rows that match the like 'zx%' condition will be first and the rows that don't match afterwards.

The 2nd column will be used for ordering by name the first group (rows that match) and ignored for the second group (rows that don't match) because it will be null for all of them.

The 3rd column will be ignored for the first group (rows that match) as it will be null and the rows will already have been ordered anyway by the 1st and 2nd column. But ti will be used for ordering by id the second group (rows that don't match).


Return a fully dynamic table from an Oracle function I'd like to write a function with two IN parameters where the first one is a varchar and the second a list of varchars. Based on these I want to return a table with varying column amounts and names of type varchar.

As far as I have seen, I have to always to create an object/record and a table type of it. This means that my idea won't work? The underlying goal is to pass a system command output back to a callee as a table.

Edit: more on the task. I want to issue an OS command, consume the output and return it back as a table. The output from the OS command is going to be CSV-formatted data. At the time of execution I do not know the amount of rows to be returned but only the amount of columns which is passed as the second arg. I was thinking about using Java with a dynamic STRUCT and an ARRAY containing them. Though I would prefer the former approach.

It should look like this:

create function(clob query, list of varchars cols) returns table
begin
  execute system command(query, cols);
  examine sysout from command;
  return tabular data from syscmd as table;
end

It is possible, though quite complicated, to write a pipelined table function that returns a variable structure.  Your pipeline table function could take the two arguments and use the Oracle Data Cartridge interface and the magic of the AnyDataSet type to return a dynamic structure at runtime.  You can then use that in subsequent SQL statements as if it was a table, i.e.

SELECT *
  FROM TABLE( your_pipelined_function( p_1, p_2 ));

A couple more references that discuss the same sample implementation

Dynamic SQL Pivoting
The Implementing the Interface Approach section of the Oracle Data Cartridge Developer's Guide


How would one create an SSRS datasource to quickbooks? My team is stuck. We are attempting to create reporting in SSRS to connect to quick books. We were successful in creating DAO connections in ms access, however we want to have reporting in SSRS and not ms access.

I believe you need to set up an ADO.Net DataSource in SSRS:

https://www.cdata.com/kb/tech/quickbooks-ado-ssrs-server.rst


MySQL Multisource slave as a master? When deploying MySQL multi-source replication, can the multi-source slave be a master for other 'normal' slaves?

I can't find this particular case covered in the documentation

Yes it can.  The multi sourced transactions will go into the mutli source slave's binlog with the same domain id as it receives them.  To further slaves, it just looks like a single server, the same as any other server.


Cross join table row with generate_series Currently I have
SELECT x, plans.name
FROM plans
CROSS JOIN generate_series(timestamp '2021-01-01 06:00'
                     , timestamp '2021-01-07 22:00'
                     , interval  '1 hour') t(x)

Table definition
create table plans
(
    id       serial not null
        constraint plans_pk
            primary key,
    name     varchar  default 50,
    duration interval default '01:00:00'::interval
);

which gives me a cartesian product of the plans table and timestamp generated series.
I would like to introduce a specific duration for each plans row, so one row could have "1 hour", mean while the other have "30 minutes" and then generate each combination for every row. A CROSS JOIN here doesn't work, I tried:
SELECT x, plans.name
FROM plans
CROSS JOIN generate_series(timestamp '2021-01-01 06:00'
                     , timestamp '2021-01-07 22:00'
                     , interval  plans.duration) t(x)

SQL Fiddle: http://sqlfiddle.com/#!17/97a12/10

Use a lateral join:
SELECT x, plans.name
FROM plans
CROSS JOIN LATERAL
   generate_series(timestamp '2021-01-01 06:00',
                   timestamp '2021-01-07 22:00',
                   plans.duration::interval) t(x)


Is this table really in first normal form (1NF)? Does this table comply with 1NF, even if CourseNo and StudentNo?

Student(StudentNo, StudentName, Major, CourseNo, CourseName,
InstructorNo, InstructorName, InstructorLocation, Grade)

If all the attributes/columns of that relation/table always contain atomic values (i.e., they accept exactly one value —neither no value, nor two or more, nor NULL marks— of the corresponding simple domain1, 2 per tuple/row), then yes, that relation/table meets first normal form (1NF); otherwise, no, it is not in 1NF, it is unnormalized.

Naturally, I do not know the informational characteristics of the business environment of relevance (e.g., what meaning is ascribed to each attribute/column by the end users and business experts, how each attribute/column is associated with the others, etc.), so who knows.

The question so far lacks any sample values (paramount factor to determine the respective domains), lacks any description about the business scenario at hand, lacks details about how the data points of significance are associated with each other, etc. (yes, the attributes/columns are represented by certain words, but the same word may carry different meanings in different contexts, thus an unrelated reader cannot know with exactitude what their connotations are in the scenario under consideration); therefore, as the post stands, it is impossible to evaluate properly the relation/table included in it.

The fact that the question does not contain that kind of necessary information is understandable if you are starting to learn about normalization according to the relational paradigm, but be aware that making guesses when laying out a database is counterproductive. In this regard, it is worth to point out that working closely with the business experts is indispensable in any professional database design project (including normalization at the logical layer, of course).

In case you are involved in a training/school course, I would highly recommend that you request an appropriate contextualization of the exercises from your teacher. If, on the contrary, you are learning on your own, you should look for sound materials in the relational field to optimize your efforts (this advice is more fitting now that you have clarified via comments that you are learning by yourself).

In agreement with the deliberations above, it is opportune to remark that relational database design is a craft that demands high precision.

1 Basically speaking, a domain is a set of values of the same type. N constraints can be attached to a domain. N relations/tables of a database can have n attributes/columns which draw their values from the same domain. An attribute/column can have, in turn, specific constraints only applicable to itself.

2 A domain is simple if (a) it is not comprised of relations/tables and (b) its values cannot be decomposed by the database management system. Avoiding non-simple domains when delineating a database is useful so as to take full advantage of the declarative power of a data sublanguage, which in practice facilitates the implementation of constraints and manipulation operations.


Is there another/better way to create and maintain large amounts of procedures? So I've decided to take a step back, and scrutinize the way I implement procedures on my databases.

I'll list what I have done, give my reason for doing so and hopefully I will be told that I'm a moron, and should be doing it a better way

All my procedures are stored in an SQL file, in the the following way:

SELECT 'Creating guestuser user' AS ' ';
DROP USER 'guestuser'@'appserver';
CREATE USER 'guestuser'@'appserver' IDENTIFIED BY 'Password';

SELECT 'Dropping mydatabase guest procedures' AS ' ';
DROP PROCEDURE IF EXISTS myProcOne;
DROP PROCEDURE IF EXISTS myProcTwo;

SELECT 'Creating mydatabase procedures' AS ' ';
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `myProcOne`(_id BIGINT)
    DETERMINISTIC
    COMMENT 'selects name from fluffy cats table given id'
BEGIN
    SELECT id FROM FLUFFYCATS WHERE id=_id;
END
$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `myProcTwo`(_name varchar(100))
    COMMENT 'inserts a new name into fluffy cats'
BEGIN
    INSERT INTO FLUFFYCATS (name) VALUES (_name);
END
$$
DELIMITER ;

SELECT 'Creating mydatabase guest permissions' AS ' ';
GRANT EXECUTE ON PROCEDURE db_mydatabase.myProcOne TO 'guestuser'@'appserver';
GRANT EXECUTE ON PROCEDURE db_mydatabase.myProcTwo TO 'guestuser'@'appserver';

I'll start from the top. I drop and create the user, so that it looses all it's procedure permissions. As you can see, I don't give the use any permissions until the end of the file. The reason I have chosen to do this, is so that the permissions are as restrictive as possible (And therefore more secure?) ...

Then I drop the procedures (If they exist) so that they will be created with any edits I have made

Then onto the actual creation of the procedures ... I don't think I've done anything out of the ordinary here, but always open for suggestions.

Then the script finishes with giving the user the permissions for each procedure.

Of course this is just an example file, but my real one has just reached the 100th procedure --Brings out cake-- ... And would be grateful for any suggestions as to how I could maybe reduce the workload when creating a procedure (Drop,Create,Permission) ...

I'm trying some ways to make something that could work for you but MySQL has limitations as I see. I'm trying to make a Stored Procedure that you could read all the information of the procedures from a table.

Example:

The Stored Procedure:

    DELIMITER $$

    CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`(
    IN eProcedureID int(11),
    IN eUser VARCHAR(50),
    IN ePass VARCHAR(50),
    IN eHost VARCHAR(50))
    sp_np:BEGIN
    /*  | Variable Declaration | */
        DECLARE vProcedureID int(11) DEFAULT 0;
        DECLARE vUser VARCHAR(50) DEFAULT '';
        DECLARE vPass VARCHAR(50) DEFAULT '';
        DECLARE vHost VARCHAR(50) DEFAULT '';
        DECLARE vReg INT(8) DEFAULT 0;
    /*  | Variable Asignation | */
        SET vProcedureID=eProcedureID;
        SET vUser       =eUser;
        SET vPass       =ePass;
        SET vHost       =eHost;
    /*  | WorkPlace |   */
    SET vReg=(SELECT COUNT(*) FROM PRUEBA.new_table WHERE new_table.id=vProcedureID);
    IF (vReg>0) THEN # If Exists EXECUTE
        SET @DROP_USER=CONCAT(" DROP USER ","'",vUser,"'@'",vHost,"';");
        SET @CREATE_USER=CONCAT("CREATE USER ","'",vUser,"'@'",vHost,"'"," IDENTIFIED BY '",vPass,"';");
        SELECT
            new_table.create,
            new_table.proname,
            new_table.prodb
        INTO @PROCEDURE_CREATION,@PRONAME,@PRODB
        FROM PRUEBA.new_table WHERE new_table.id=vProcedureID;
        SET @GPRIVILEGES=CONCAT(" GRANT EXECUTE ON PROCEDURE ",@PRODB,'.',@PRONAME," TO '",vUser,"'@'",vHost,"';");

        SELECT vProcedureID,vUser,vPass,vHost,@PRODB,@PRONAME,@DROP_USER,@CREATE_USER,@GPRIVILEGES;

        PREPARE DROPUSER FROM @DROP_USER; EXECUTE DROPUSER; DEALLOCATE PREPARE DROPUSER;
        PREPARE CREATE_USER FROM @CREATE_USER; EXECUTE CREATE_USER; DEALLOCATE PREPARE CREATE_USER;
        PREPARE PROCEDURE_CREATION FROM @PROCEDURE_CREATION; EXECUTE PROCEDURE_CREATION; DEALLOCATE PREPARE PROCEDURE_CREATION;
        PREPARE GPRIVILEGES FROM @GPRIVILEGES; EXECUTE GPRIVILEGES; DEALLOCATE PREPARE GPRIVILEGES;

        LEAVE sp_np;
    ELSE
        SELECT '01' AS sERROR,'ProcedureID doesnt exists.' AS sDESC_ERROR;
        LEAVE sp_np;
    END IF;
    END;

As MySQL documentation says:

  SQL syntax for prepared statements cannot be used within stored routines (procedures or functions), or triggers. This restriction is lifted as of MySQL 5.0.13 for stored procedures, but not for stored functions or triggers. However, a cursor cannot be used for a dynamic statement that is prepared and executed with PREPARE and EXECUTE. The statement for a cursor is checked at cursor creation time, so the statement cannot be dynamic.

And all you had to do it was:

    CALL PRUEBA.new_procedure(
    '0000000001', #ProcedureID int(11),
    'guestuser', #User VARCHAR(50),
    'Password', #Pass VARCHAR(50),
    'appserver'); #Host VARCHAR(50)

Hope the SP helps you to find one better way.


How to design a graph database in this scenario? here is my scenario. I have a pre-defined data type structure for books. Just take it as an example for the sake of simplicity. The structure looks like the image below. It's a Labeled Property Graph and the information is self-explained. This data type structure is fixed, I cannot change it. I just use it.

When there is 1 book, let's call it Harry Potter, in the system, it might look like below:

So, the book has its own property (ID, Name,...) and also contains a field type MandatoryData. By looking at this graph, we can know all information about the book.

The problem happens when I have 2 books in the system, which looks like this:

In this case, there is another book called Graph DB, with those information as highlighted.

The problem of this design is: we don't know which information belong to which book. For example, we cannot distinguish the publishedYear anymore.

My question is: how to solve or avoid this problem? Should I create 1 MandatoryData for each book? Could you propose me any design?

I'm using Neo4j and Cypher. Thank you for your help!

Should I create 1 MandatoryData for each book?

Yes.

If you cannot get rid of MandatoryData object, you should have one such object per book.

Otherwise I would simplify the model and move any mandatory attributes to the Book object, which will reduce ne04j database size. Modeling relationships which are always 1:1 is possible, but not optimal. You can improve query performance by keeping the data in one object.


Add newest values until current date with the year and month separated as 2 INT columns So I have this query to insert data into a table (year and month are when the row was created)

INSERT INTO Table#1 (ID, col2, Year, Month)

SELECT B.ID, B.col2, A.InvoiceYear, A.InvoiceMonth

FROM Table#2 AS A

INNER JOIN Table#3 AS B ON B.ID = A.ID

ORDER BY A.InvoiceYear DESC, A.InvoiceMonth DESC

The thing is that everytime i run that query, it add all the data all over again, and i want only the newest data from the last update.
So the thing i wanted to do is adding:

WHERE A.InvoiceYear >= MAX(table#1.Year)
AND A.InvoiceMonth > MAX(table#1.Month)

The problem with it, first of all, it didnt work (error 1111: Invalid use of group function), and second, the maximum value for month its going to be 12, because i have data from 4 years. Is there a way to add data that the year and month is more recent that the oldest value added?

Thanks for the help, if something wasnt clear please ask me, it's not my native language

You could use WHERE NOT EXISTS to achieve what you need:

  INSERT INTO Table#1 (ID, col2, Year, Month)
  SELECT B.ID, B.col2, A.InvoiceYear, A.InvoiceMonth
    FROM Table#2 AS A
    JOIN Table#3 AS B ON B.ID = A.ID
   WHERE NOT EXISTS (SELECT *
                       FROM Table#1 AS C
                      WHERE C.Year = A.InvoiceYear
                        AND C.Month = A.InvoiceMonth)
ORDER BY A.InvoiceYear DESC, A.InvoiceMonth DESC;

You can find more information here: EXISTS (Transact-SQL)


DBCC PAGE read page from buffer pool or directly from the file system? We know that SQL Server always read pages from disk to memory for all its operations. So it's possible that a page in memory is different from it's counterpart in disk. I want to know the command DBCC PAGE is reading a page in memory or on disk. Thanks.

We know that SQL Server always read pages from disk to memory for all its operations.

The pages are asked for from memory like most things in SQL Server. The same process applies for DBCC PAGE as it does for other read operations in SQL Server.


Parallelism Best Practices What are the best practices with setting parallelism in general?  I know that SQL Server defaults to 0 to use all available processors, but in what instance would you want to change this default behavior?

I remember reading somewhere (I'll have to look for this article) that for OLTP workloads you should turn off parallelism (set maxdop to 1).  I don't think I completely understand why you would do this.

When would you keep maxdop up to SQL Server (0)?
When would you turn off parallelism (1)?
When would you explicitly state the maxdop to a particular number of processors?

What causes parallelism?

You usually don't want to disable parallelism as that will also disable it for admin tasks.  Your best bet is to fix the queries that are causing the parallelism through adding or fixing indexes or through making full on schema changes.

Based on your updated questions...

Some people will change MAXDOP to 1 for vendor built applications because they can't control the database or schema and they don't want a single query to take over the entire system.

Personally I always keep MAXDOP at 0 except for some rare cases.

Parallelism is caused by a single operation within an execution plan having an execution cost which goes over a pre-set setting (the cost threshold for parallelism setting).  When this happens the SQL Server will kick in parallelism so that it can multi-thread the request in an attempt to speed up the process.  The default value for the cost threshold for parallelism is 5.  In many OLTP platforms you'll want to raise that up to 30 or 40 so that parallelism only kicks in for the really expensive queries.


How do you calculate how much hardware resources you need for a database? We are in the process of scaling our database servers. I was wondering how should we calculate how much hardware resources we need for our databases?

Here is a little bit information about our current database server:

MySQL Database
InnoDB for all tables
About 80 tables
Largest tables are: 15 GB, 13 GB, 12 GB, 5 GB, rest smaller than 1 GB
Size of database on disk is 175 GB with ibdata1, and 56 GB without it
Database grows about 10% per month -- 12 month ago it was about 5-6%
Around 60 connections running in normal use
InnoDB buffer size is 16 GB of total 24 GB and is 99% utilised
CPU usage is around 30% on a 2.27GHz Intel Xeon 8-core L5520
We have about 33% write and 66% read
We have about 2.31 TPS and 1126 QPS according to the below snippet -- QPS seems to spike up and down between 750 and 1500

.

use information_schema;
select VARIABLE_VALUE into @num_queries from GLOBAL_STATUS where VARIABLE_NAME = 'QUESTIONS';
select VARIABLE_VALUE into @uptime from GLOBAL_STATUS where VARIABLE_NAME = 'UPTIME';
select VARIABLE_VALUE into @num_com from GLOBAL_STATUS where VARIABLE_NAME = 'COM_COMMIT';
select VARIABLE_VALUE into @num_roll from GLOBAL_STATUS where VARIABLE_NAME = 'COM_ROLLBACK';
select (@num_com + @num_roll) / @uptime as tps, @num_queries / @uptime as qps;

Our current service provider offers us the following upgrade:

Active/Passive Design (2 servers, each as described below)
Single Processor, Octo Core Intel Xeon E5-2630 v3 2.4Ghz
64 GB of memory
RAID 1; 300 GB 12 Gbps (15K SAS 2.5") x 2

My questions are:

How do I know if this is an overkill or not? despite the fact that this is obviously a huge upgrade, I want to know if that's what we really need and it won't be wasted resources or unnoticeable performance increase. Any formula, article or book recommendation is appreciated.
Are there any automated tools that can help us find out how much resources we might need, let's say in a year from now, given our current database details?

Update #1

SHOW VARIABLES

SHOW GLOBAL VARIABLES

SHOW GLOBAL STATUS

RIAD Controller

Dell PERC 6i; RAID 5
Write policy: Write-through and write-back
Cache memory size: 256 MB

The short answer:  Can't tell.  Not enough info.

The long answer...

If your data is growing at 10%/month, it will be about a year before it is 64/24 times as big.  So if you grow the RAM and the buffer_pool by 64/24, you are somewhat likely to have the same cache performance of the buffer_pool.  After only one year.

The 99% utilized doesn't really say anything other than your working set is at least 16GB.  That is not surprising since the dataset is much bigger than that.

30% CPU means that you have an average of 1.3 cores running at any time?  If so, then I suspect you have some slow queries.  Let's look at them to see if they can be improved.  With luck, fixing a couple of queries could delay the need for a beefier machine.

8 cores (versus 1.3) says you are likely to have plenty of cores.  But you may outgrow your current 4.

Since you will need to move all the data, I strongly recommend setting innodb_file_per_table=1 on the new machine before starting the move.  (I assume you are using InnoDB.)

Upgrade MySQL when you make the move.

Do not set query_cache_size bigger than 50M; that may be a source of the high CPU.  If you are writing to all the tables "all the time", then it would be better to completely turn off the Query cache.  (This may be another way to stall the purchase.)

What percent of I/O capacity are you using now?

Will the new RAID have a Battery Backed Write Cache?  That makes writes virtually free.

If you want more analysis, please provide

RAM size (currently 24GB)
SHOW VARIABLES;
SHOW GLOBAL STATUS;   -- STATUS, not VARIABLES.

ADDENDA - Review of VARIABLES and STATUS

**Observations

Version: 5.6.13-log
24 GB of RAM
You are running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues

innodb_buffer_pool_size should be increased to about 70% of RAM.
increase innodb_log_file_size to about 300M.  Luckily, this is easier as of 5.6.8
Do something about frequently changing databases ("USE"); see Com_change_db, below.
read_buffer_size = 128K
See if it makes sense to clump multiple statements into transactions.
Tackle the slow queries.

Details

( innodb_buffer_pool_size / _ram ) = 12,884,901,888 / 24576M = 50.0% -- % of RAM used for InnoDB buffer_pool
( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((21354175 + 179458342) ) / 1821852 = 110 /sec -- InnoDB I/O
-- Increase innodb_buffer_pool_size?

( Innodb_log_writes ) = 306,041,138 / 1821852 = 167 /sec

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 1,821,852 / 60 * 48M / 314550301184 = 4.86 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size.

( tmp_table_size ) = 179M -- Limit on size of MEMORY temp tables used to support a SELECT
-- Decrease tmp_table_size to avoid running out of RAM. Perhaps no more than 64M.

( Handler_read_rnd_next ) = 766,168,067,814 / 1821852 = 420543 /sec -- High if lots of table scans
( Select_full_join ) = 29,673,902 / 1821852 = 16 /sec -- joins without index
( Select_scan ) = 70,915,674 / 1821852 = 39 /sec -- full table scans
( Select_scan / Com_select ) = 70,915,674 / 771524414 = 9.2% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
( Created_tmp_tables ) = 75/sec
-- Add indexes / optimize queries

( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (30050199 + 842 + 264 + 0 + 287315111 + 278) / 1821852 = 174 /sec -- writes/sec
-- 50 writes/sec + log flushes will probably max out I/O write capacity of normal drives

( long_query_time ) = 10.000000 = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2

( Com_change_db / Connections ) = 953,574,484 / 23878 = 39,935 -- Database switches per connection
( Com_change_db ) = 953,574,484 / 1821852 = 523 /sec -- Probably comes from USE statements.
-- Consider connecting with DB, using db.tbl syntax, eliminating spurious USE statements, etc.
( Com_admin_commands ) = 523/sec
-- Admin commands -- is some 3rd party cluttering the traffic?

( Threads_created / Connections ) = 1,339 / 23878 = 5.6% -- Rapidity of process creation
-- Increase thread_cache_size (non-Windows)

( read_buffer_size ) = 65536
-- http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_read_buffer_size
-- 128K might be better, depending on things that are hard to predict at this point.

ADDENDA 2 - back to the question

Generally, it seems that scaling in the direction you mentioned should work well for you.

You seem to be running pretty well now.
I itemized several things that will help you live for a short while with what you have.
A tough one:  getting rid of the unnecessary 3rd-party-isms (change_db, etc)
A tough one:  locating the slow queries and improving them
You already have a recent 5.6, so handling more connections on the 8 cores should be easy.
There is a lot of InnoDB I/O, but nothing to indicate that the cache is "inefficient".  With more ram, the most important thing to change will be innodb_buffer_pool_size (to 70% of available RAM).
If you grow by 10%/month, a year or two from now, you will be happy to have that "huge upgrade", and not have to upgrade again.

One thing worries me...  You are at about peak capacity of ordinary drives now.  (I see 110 IOPs from InnoDB.)  If that grows at 10%, you may run out of I/O capacity first.  Your new config helps only a little.  Alternatives:  RAID-5 (striping and parity, but need at least 3 drives) or SSDs (more costly).


database with same schema and same AWS instance type, but different performance Hi I am getting a new set of production servers ready. I generated a DB script from SSMS using the schema from Server A to deploy to a new AWS instance of the same type as Server A. After populating the required configuration tables on the new DB I ran and saw a performance degradation on this new server. When I look at the execution plans for a query I see that they are very close to the same other than the value for "Memory Grant". Memory Grant is more on the original server (1656 vs 1112 on the new server). Any ideas on what could be causing this performance issue on the new server?

There are several things which are needed to know here.
Some EC2 Instances on AWS are using shared CPU, this could be one reason why your instances are performing different.
Also the same EC2 instances in different regions might be different from each other from hardware perspective.
Also interesting is to know when you did the performance test, if you setup your server for the first time and run some tests, these will perform not so well compared to a machine which already calculated the same queries before.
The reason is the caching of course, caching of raw data, caching of query plans and re-using instead of compiling them and deciding which one to take.

Now we have to counters about memory grant, I think we are talking about memory grants pending.

Memory Grants Pending

This counter shows you the number of processes waiting on a workspace memory grant.
Of course the lower the number, the better your performance.

But for you the new server has a lower value, so it is performing at least better for this specific value.

Brent Ozar, the king of performance, has a nice list of counters which are worth looking at:

http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/

Monitoring only one performance counter will in most cases not give you enough vision.


Parameterize table name in dynamic SQL I have been working on a few stored procedures that have conditional parameters to them, but one of them is giving me a problem that I can't quite figure out.  This is the code for the procedure:
CREATE PROCEDURE dbo.GetTableData(
    @TblName   VARCHAR(50),
    @Condition VARCHAR(MAX) = NULL,
) AS
BEGIN
    IF(EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TblName))
        BEGIN
            DECLARE @SQL NVARCHAR(MAX) = N'
            SELECT * FROM @TblName WHERE 1=1'
            + CASE WHERE @Condition IS NOT NULL THEN
            ' AND ' + @Condition ELSE N'' END

            DECLARE @params NVARCHAR(MAX) = N'
                @TblName   VARCHAR(50),
                @Condition VARCHAR(MAX)';

            PRINT @SQL

            EXEC sys.sp_executesql @SQL, @params,
                @TblName,
                @Condition
        END
    ELSE
        RETURN 1
END

The way I would like the procedure to work, is that it's suppose to allow me to do quick table look ups.  So if I want to see everything from my Parts table, I'd just run
EXEC GetTableData 'parts'

Or if I wanted to see everything in the Parts table with a specific supplier I'd run
EXEC GetTableData 'parts', 'supplier LIKE ''A2A Systems'''

Now in the above example, when I run it, the PRINT @SQL line prints out the query as:

SELECT * FROM @TblName WHERE 1=1 AND supplier LIKE 'A2A Systems'

So the query it being put together properly (it seems).
However after it prints I am getting the following error:

Msg 1087, Level 16, State 1, Line 4
Must declare the table variable "@TblName"

I still get this error if I change the EXEC line to:
EXEC GetTableData @TblName='parts', @Condition='supplier LIKE ''A2A Systems'''

So what am I doing wrong here?  Why isn't it taking my @TblName variable value?

You can't parameterize entity names (tables, columns, views, etc). You need to do it the riskier way:

        DECLARE @SQL NVARCHAR(MAX) = N'
        SELECT * FROM ' + QUOTENAME(@TblName) + N' WHERE 1=1'
        + CASE WHERE @Condition IS NOT NULL THEN
        ' AND ' + @Condition ELSE N'' END

        DECLARE @params NVARCHAR(MAX) = N'
            @Condition VARCHAR(MAX)';

        PRINT @SQL

        EXEC sys.sp_executesql @SQL, @params,
            @Condition

QUOTENAME() is typically enough to protect from dangerous execution (leading, potentially, to SQL injection), but to make it a little more secure you should consider (a) prefixing the table name with the correct schema prefix (e.g. ...FROM dbo.' + QUOTENAME(@TblName) + ... and (b) checking for existence first:

IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = @TblName)
BEGIN
  RAISERROR(N'Nice try, robot.', 11, 1);
  RETURN;
END

DECLARE @SQL ...


How to list the missing multicolumn statistics? I have used the database engine tuning advisor, DTA, in order to improve the performance of one of my databases.

It produced a list of recommendations, mostly multicolumn statistics.

These recommendations need then to be cross checked and tested, before applying them in production.

Statistics are used by the query optimiser (whitepaper on MSDN) to track distribution of values in indexes and/or columns.

Inspired by the following reading:

When is it better to create STATISTICS instead of creating an Index?

MY QUESTION IS

Other than the DTA, is there any other way to find out the missing multicolumn statistics?

Other than the DTA, is there any other way to find out the missing multicolumn statistics?

Nope ! query optimizer assumes that columns within the same table are independent. Joe Sack talks about it here

  The query optimizer assumes that columns within the same table are independent. For example, if you have a city and state column, we may intuitively know that these two columns are correlated, but SQL Server does not understand this unless we help it out with an associated multi-column index, or with manually-created multi-column statistics. Without helping the optimizer with correlation, the selectivity of your predicates may be exaggerated.

and @paulwhite talks about it here.

  A certain amount of trial-and-error may be necessary to identify the multi-column statistic.


The transaction log for database 'SolarWindsOrion' is full due to 'XTP_CHECKPOINT' To start, I am a database novice. I thank you for any help in advance.

I have a solarwinds SQL windows 2017 database in full recovery mode in an availability group. The database is roughly 2GB.

Yesterday, I went through the process of shrinking my log file that had continuously grown to ~120gb.

To prevent this issue in the future I:

Configured the log file for an auto growth of 1gb and max size of 2gb (based on some solarwinds recommendations I had found)
Scheduled regular Transnational log backups every 30sec (again based on some recommendations I had found)

... well, i accidentally configured the log backups to be every 30 minutes in error and i believe that is what then allowed the log file to fill to it's maximum size?

I am now receiving the error "The transaction log for database 'SolarWindsOrion' is full due to 'XTP_CHECKPOINT'"

I am unable to shrink the log file and I am also unable to update the log file to a bigger size (get the same error message)

I am wondering what the best course of action is and if there is anything else i need to do to prevent this issue in the future once resolved.

I am wondering what the best course of action is and if there is
  anything else i need to do to prevent this issue in the future once
  resolved.

As Kin Shah mentioned,

  To get you out, change the log size to unlimited, run a checkpoint and
  take a log backup (probably few). Why cap the tlog size ?

Setting the log size to unlimited and not capping it at 2GB whilst taking regular log backups should fix your problem.

In your case, that was not possible yet.

  I get the same error when trying to change the size to unlimited.

The error

  The transaction log for database 'SolarWindsOrion' is full due to
  'XTP_CHECKPOINT'"

A temporary solution could be adding another log file, taking a log backup & changing the size of the original log file.

An example of creating the log file, taking a log backup, changing the size to unlimited and removing the log file.

USE [master]
GO
ALTER DATABASE [DB_NAME] ADD LOG FILE ( NAME = N'DB_log2', FILENAME = N'F:\Log1\DB_log02.ldf' , SIZE = 65536KB , FILEGROWTH = 65536KB );
GO
ALTER DATABASE [DB_NAME] MODIFY FILE ( NAME = N'DB_log', MAXSIZE = UNLIMITED);
GO
BACKUP LOG [DB_NAME] TO DISK = '\\LogBackupLocation\DB_LogIssue.trn';
USE [DB_NAME]
GO
ALTER DATABASE [DB_NAME]  REMOVE FILE [DB_log2]
GO

--The file 'DB_log2' has been removed.

  That seems to have done the trick. As far as best approach moving
  forward. Now that i have the transnational backups happening every
  30secs (as originally planned) should that prevent this issue from
  happening in the future?

Depends on the Always On Availability Group settings and the size of some transactions if you keep the max size at 2GB.

Setting the max size of your log file to unlimited and on a separate log drive where it could grow should be best.
As far as scheduling log backups go, 30 seconds should be pretty good.


How to identify the db orphan mdf and ldf files I want to identify the orphan mdf and ldf files on the disk and remove them. After searching on the internet, found the script below on MSDN forum. But when I run the script it returns many system-like db files that don't seem to orphan or at least not safe to delete!

Do you know any better way to identify the orphan files? Or can you explain what these db files are referring to (is it safe to delete them)?

For example the files below came in the result of this query:

/*
exec sp_configure 'show advanced' ,1
reconfigure
exec sp_configure 'xp_cmdshell' ,1
reconfigure
*/
if object_id('tempdb.dbo.#os_files') is not null
    drop table #os_files
create table #os_files([filename] varchar(2000))

--list all .mdf and .ldf files on the c drive
-- you will need to call this again to
-- populate the #os_files table, if you have
-- db files on other databases eg. d:, e:

insert into #os_files exec xp_cmdshell 'DIR C:\*.mdf /b /s'
insert into #os_files exec xp_cmdshell 'DIR C:\*.ldf /b /s'

delete from #os_files where filename is null

update #os_files set filename=rtrim(filename)

select
    os.filename as orphaned_files
from
    #os_files os
    left outer join master.dbo.sysaltfiles db on rtrim(db.filename) = os.filename
where
    db.dbid is null
order by 1

Thank you.

The files in C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL\MSSQL\Binn\Templates are basic copies of the system databases that are needed for repairing and rebuilding your system dbs in case of problems - http://msdn.microsoft.com/en-us/library/dd207003(v=sql.105).aspx

While you could delete them, you'll be in for a longer recovery period if you need to rebuild the system dbs as you'll need to find the templates from somewhere else.

But other than that, that script appears to do what it proclaims to do. Though, it should be updated to:

select
    os.filename as orphaned_files
from
    #os_files os
    left outer join master.sys.master_files db on rtrim(db.physical_name) = os.filename
where
    db.database_id is null

as sysaltfiles is deprecated and will be removed from SQL Server in a future version.


Similar indices in MS SQL Server I currently have a table which has 2 indices on the same table that are being used to speed up slightly different sets of queries.  They both have 2 index columns and 1 included column and all 3 columns are the same in both.  The table stores sales ranks at particular points in time.  The 3 fields are the ID of the product that the rank applies to (int), the date and time it was checked (smalldatetime) and the rank (int).  In one index I have the rank and checked date/time as indexed cols and the product ID included, in the other I have the product ID and the checked date/time as indexed cols and the rank included.

Am I right in thinking that this isn't a sensible way of having it organised and it would make more sense to have a single index with all 3 columns indexed or does that depend on the way the database is being used?

Create statements are: -

CREATE NONCLUSTERED INDEX [ix_SalesRankCheck_ChannelSKUID_Checked_SalesRank]
ON [dbo].[SalesRankCheck]
(
    [ChannelSKUID] ASC,
    [Checked] ASC
)
INCLUDE ([SalesRank])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
      DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

CREATE NONCLUSTERED INDEX [ix_SalesRankCheck_SalesRank_Checked_ChannelSKUID]
ON [dbo].[SalesRankCheck]
(
    [SalesRank] ASC,
    [Checked] ASC
)
INCLUDE ([ChannelSKUID])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
      DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

UPDATE 2: These are the stats on the indices: -

OBJECT NAME INDEX NAME  USER_SEEKS  USER_SCANS  USER_LOOKUPS    USER_UPDATES
SalesRankCheck  ix_SalesRankCheck_ChannelSKUID_Checked_SalesRank    1140    18  0   4091
SalesRankCheck  ix_SalesRankCheck_SalesRank_Checked_ChannelSKUID    36  0   0   4091

The indexes are similar but not identical. The order of the columns in an index matters - a lot - and affects what queries might benefit from them.

We don't know your queries but for example a query like the following would benefit most from index A:

SELECT TOP (3) ChannelSKUID, Checked, SalesRank
FROM dbo.SalesRankCheck
WHERE ChannelSKUID = 'XXX'
ORDER BY Checked ;

while this one would benefit from index B:

SELECT TOP (3) ChannelSKUID, Checked, SalesRank
FROM dbo.SalesRankCheck
WHERE SalesRank = 1
ORDER BY Checked ;

If only one of the two indexes were available, the query plans might still use it but the plan would not be optimal, due to the difference in column order in the index and the mismatch with the optimal column order needed for the query. The index would still be "covering" the query but the plan would probably be a full index scan instead of an index seek and would have additional (sort) operations as the rows provided by the index would not be in the order the query wants.

For the question now:

  Am I right in thinking that this isn't a sensible way of having it organised and it would make more sense to have a single index with all 3 columns indexed or does that depend on the way the database is being used?

No, it is a sensible way and I wouldn't expect a single index to cover queries that might choose either index otherwise.

But it depends on what queries run against the table. Any analysis should be based on the workload you have and the performance you expect. Analyze the queries (all of them or the ones most often run) and what indexes they use and what performance you get, before you decide dropping or adding an index.

Run tests in test installations with different indexing, if performance is not good. Or experiment (say with dropping an index), if you think that the index is not used.

For a quick and dirty advice, if the number of indexes on the table is small and the performance is good, don't bother changing anything. The extra load that indexes impose on write operations is usually small, unless you have a table with 10+ indexes or a table that is under extreme write load (thousands of writes per second).


Job not running as agent but as engine account I recently created a new SQL Server environment and I am trying to use best practices where I can, especially the least privileged approach. Other servers in our environment just have jobs running with combinations of Domain Admin rights and SA rights which is "simpler" and scary.

That aside, I have the Database Engine running with the domain account DOMAIN\sqlengine and the Agent running with DOMAIN\sqlagent.

I have a simple maintenance plan that does a full backup of all databases on the server to a remote UNC Path. I had assumed/planned that the agent account (DOMAIN\sqlagent) would be executing the jobs of the plan. The jobs were failing with an OS-driven Access Denied error.  So I added the DOMAIN\sqlagent account modify rights to the directory so that it could perform the backup.  To my surprise it failed again.  When I check the security logs of the target server I see that DOMAIN\sqlengine was the account authenticating.  That account was not given rights to the UNC path.  Why is the engine account, not the agent account, running the job?

If I look at the job owner I had set DOMAIN\sqlagent for all of the jobs in that Maintenance Plan.  Under the job step it is set to run as the "SQL Server Agent Service Account".

What I am doing wrong or not understanding?

SQL Server itself is performing the backup; therefore the DOMAIN\sqlengine account needs access to the share.

SQL Server Agent is simply starting the job.

This is exactly the same as if you started a backup using the T-SQL BACKUP DATABASE [xyz] TO ... command.  SQL Server would still require access to the destination path.  You can still lock-down the destination path, and should do so, to prevent unauthorized access to the data stored in the backup files.

Also, instead of using Maintenance Plans to perform maintenance, I'd recommend using Ola Hallengren's scripts; they are excellent, well-supported, and widely-used.


import .db files/tables into sqlite database I have a zip file containing several .db files (~ 30 files). Each of these .db files correspond to a table, e.g., the "fuel_transaction.db" file contains a "fuel_transaction" table, "payment_transaction.db" contains "payment_transaction" table and so on.

I am very new to SQLite and whatever I've found is about importing CSV or sql files.. I was wondering if there is any way to import all these tables, i.e., all .db files (all at once) into a database in SQLite? Should I first create an empty database in sqlite and then import each file separately into this new database?

Thanks,

For each database file (other.db) that must export tables to main.db:

sqlite3 other.db .dump | sqlite3 main.db

Or if you want to check the generated commands:

sqlite3 other.db .dump > log
sqlite3 main.db < log

Another method: (not so good as it does not import the primary key and column types)

First open the main database:

sqlite3 main.db

Then for each database containing tables to be imported:

ATTACH DATABASE 'other.db' AS other;
CREATE TABLE tablename AS SELECT * FROM other.tablename;
DETACH DATABASE other;


How the xlogs files travels form Master to Slave I have some doubts about how the workflow proccess in PostgreSQL replication using archiving logs.

If I have this configuration in Master postgresql.conf:

archive_command = 'test ! -f /path/to/archive/%f && cp %p /path/to/archive/%f'

After the %p and %f parameters have been replaced, the actual command executed might look like this:

test ! -f /path/to/archive/00000001000000A900000065 && cp pg_xlog/00000001000000A900000065 /path/to/archive/00000001000000A900000065

Ok, if the file isn't into archive, then it will be copied.

If I have this configuration in Slave recovery.conf:

restore_command = 'cp /path/to/archive/%f %p'

Then if I don't configure anything like this :

scp /master/path/to/archive/* /slave/path/to/archive/

How is possible that the xlogs files in /path/to/archive/  travels from Master to Slave, and then the Slave copy them to its pg_xlog/ directory?

You're expected to ensure that /path/to/archive is a shared volume, like an NFS fileshare, that both servers can read. Or you're expected to use an appropriate command to copy the files to/from a shared storage location.

I think this might need to be made more obvious in the documentation.

Re your comment:

  Why it is necessary to create a archive directory and the copy doesn't execute directly into pg_xlog?

the reason is that the master's pg_xlog is generally on a different server to the replica(s). The replicas cannot access it. You really don't want to put pg_xlog on shared storage because it's performance critical.

It also does you no good to have a backup that requires access to the master server's pg_xlog when the master server just melted in a fire.

By archiving WALs, PostgreSQL can also reduce filesystem fragmentation. It recycles WAL archives once they're no longer required, renaming them and using them as if they were new files. This is a significant performance improvement that would not be possible if it had to keep all the old WAL files around.

Finally, it creates a logical separation between "transaction logs the master server still needs to operate" and "transaction logs that are now only required for backup/archival".


How to create an Sql table from SELECT with a multiple row DISTINCT constraint? I want to create a table from a SELECT statement based on a multiple row DISTINCT, but PostgreSQL does not allow me to do it.

I need to create a table where the elements were previously selected from another table and they are all unique.

Here's my try:

CREATE TABLE new_name AS (

   SELECT DISTINCT(table.field1, table.field2), table.field1, table.field2,
   FROM ...
   WHERE ...

);

If I run this statement, PostgreSQL says:

  ERROR: column "row" has pseudo-type record

How could I solve this problem?

distinct is NOT a function. It always operates on all columns of the result.

The difference between select distinct (foo), bar and select distinct foo, bar is the same as the difference between select (foo), bar and select foo, bar. The parenthesis is just "noise".

When you write select (foo,bar) you are actually creating an anonymous record type in Postgres which results in a single column that has two attributes - which is not what you actually want.

As you are using Postgres, you can use the (proprietary) operator DISTINCT ON which - in contrast to the standard DISTINCT - does operate on a sub-set of the columns.

You have to specify an ORDER BY in that case to define which of the rows to take if there is more than one with the same combination of (field1, field2).

CREATE TABLE new_name
AS
SELECT DISTINCT ON (table.field1, table.field2),
       table.field1,
       table.field2,
       .....
FROM ...
WHERE ...
ORDER BY ..

If you want to stick to ANSI SQL you will need a window function for this:

create table new_name
as
select column1, column2, column3, column4, column5, column6
from (
   select column1, column2, column3, column4, column5, column6,
          row_number() over (partition by column1, column2 order by ...) as rn
   from the_table
   where ...
) t
where rn = 1;

For a large table, DISTINCT ON is probably faster than the solution with the window function.


Set InnoDB as default engine and set a default collation What should I do to set InnoDB as the default database engine?
And is possible to set a default collation when I create a schema and tables (e.g. utf8mb4)?

I don't want to have to choose these options every time I create a new database.

I am using MySQL 5.7.

You can set default-storage-engine=InnoDB (Set the default storage engine for tables) and you can even force your customers to use InnoDB with enforce_storage_engine=InnoDB (Force the use of a particular storage engine for new tables). enforce_storage_engine  is a MariaDB only feature.

Default character set and collation you can set in my.cnf

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

The other options like default storage engine need also be in my.cnf to be permanent.

When you don't specify storage engine, collation and character set, MySQL chooses defaults set by you.


Precision of NVARCHAR2 column is 1.5 times lower than displayed by desc command I wanted to update precision of an nvarchar2 column and came across an interesting case. I have tried to replicate it below:

create table test_table1
 (col1 nvarchar2(100));

table TEST_TABLE1 created.

desc test_table1
Name Null Type
---- ---- --------------
COL1      NVARCHAR2(150)

We observed that the displayed precision of the nvarchar2 column is 1.5 times the specified precision. So if I have to replicate the precision of a column of type NVARCHAR2(375), then we need to declare it as NVARCHAR2(250).

I tried searching for an explanation but couldn't find any. Does anyone have any idea on this please? We are using Oracle-11g, connecting with Oracle SQL Developer.

NLS_SESSION_PARAMETERS

select * from NLS_SESSION_PARAMETERS;

+-------------------------+------------------------------+
|        PARAMETER        |            VALUE             |
+-------------------------+------------------------------+
| NLS_LANGUAGE            | AMERICAN                     |
| NLS_TERRITORY           | AMERICA                      |
| NLS_CURRENCY            | $                            |
| NLS_ISO_CURRENCY        | AMERICA                      |
| NLS_NUMERIC_CHARACTERS  | .,                           |
| NLS_CALENDAR            | GREGORIAN                    |
| NLS_DATE_FORMAT         | YYYY.MM.DD HH24:MI:SS        |
| NLS_DATE_LANGUAGE       | AMERICAN                     |
| NLS_SORT                | BINARY                       |
| NLS_TIME_FORMAT         | HH.MI.SSXFF AM               |
| NLS_TIMESTAMP_FORMAT    | DD-MON-RR HH.MI.SSXFF AM     |
| NLS_TIME_TZ_FORMAT      | HH.MI.SSXFF AM TZR           |
| NLS_TIMESTAMP_TZ_FORMAT | DD-MON-RR HH.MI.SSXFF AM TZR |
| NLS_DUAL_CURRENCY       | $                            |
| NLS_COMP                | BINARY                       |
| NLS_LENGTH_SEMANTICS    | BYTE                         |
| NLS_NCHAR_CONV_EXCP     | FALSE                        |
+-------------------------+------------------------------+

NLS_DATABASE_PARAMETERS

select * from NLS_DATABASE_PARAMETERS;
+-------------------------+----------------------------+
|        PARAMETER        |           VALUE            |
+-------------------------+----------------------------+
| NLS_LANGUAGE            | AMERICAN                   |
| NLS_TERRITORY           | SWITZERLAND                |
| NLS_CURRENCY            | SFr.                       |
| NLS_ISO_CURRENCY        | SWITZERLAND                |
| NLS_NUMERIC_CHARACTERS  | .'                         |
| NLS_CHARACTERSET        | AL32UTF8                   |
| NLS_CALENDAR            | GREGORIAN                  |
| NLS_DATE_FORMAT         | DD.MM.RR                   |
| NLS_DATE_LANGUAGE       | AMERICAN                   |
| NLS_SORT                | BINARY                     |
| NLS_TIME_FORMAT         | HH24:MI:SSXFF              |
| NLS_TIMESTAMP_FORMAT    | DD.MM.RR HH24:MI:SSXFF     |
| NLS_TIME_TZ_FORMAT      | HH24:MI:SSXFF TZR          |
| NLS_TIMESTAMP_TZ_FORMAT | DD.MM.RR HH24:MI:SSXFF TZR |
| NLS_DUAL_CURRENCY       | SF                         |
| NLS_COMP                | BINARY                     |
| NLS_LENGTH_SEMANTICS    | BYTE                       |
| NLS_NCHAR_CONV_EXCP     | FALSE                      |
| NLS_NCHAR_CHARACTERSET  | UTF8                       |
| NLS_RDBMS_VERSION       | 11.2.0.4.0                 |
+-------------------------+----------------------------+

nls_length_semantics

show parameter nls_length_semantics;
Show parameters query failed

Resolution

Thanks for the effort guys. The problem is indeed with SQL Developer.

I tried running it after connecting manually in Unix. Got correct result

create table test_table1
 (col1 nvarchar2(100));

Table created.

desc test_table1;
 Name  Null?   Type
 ---------------------------
 COL1          NVARCHAR2(100)

Thanks for both the answers. Thanks Paul for declaring the bounty and thanks jsapkota and Giova for taking your time and helping me out.

The problem is in the NLS_NCHAR_CHARACTERSET = UTF8. I simulated the same table in a different database with NLS_NCHAR_CHARACTERSET = AL16UTF16. The describe statement shows the correct length as defined while creating table.

UTF8 can be 1, 2, 3 or 6 bytes / character, any AL32UTF8 character that is 4 bytes will be stored as 2 time 3 bytes in UTF8. If you want to store 1 byte character in UTF8 it takes 1.5 bytes (1*1.5=1.5).

For further details, see:

AL32UTF8 / UTF8 (Unicode) Database Character Set Implications [ID 788156.1]


Inconsistency in repeatable read http://www.postgresql.org/docs/9.2/static/transaction-iso.html

  The Repeatable Read mode provides a rigorous guarantee that each transaction sees a completely stable view of the database. However, this view will not necessarily always be consistent with some serial (one at a time) execution of concurrent transactions of the same level. For example, even a read only transaction at this level may see a control record updated to show that a batch has been completed but not see one of the detail records which is logically part of the batch because it read an earlier revision of the control record. Attempts to enforce business rules by transactions running at this isolation level are not likely to work correctly without careful use of explicit locks to block conflicting transactions.

Isn't that a phantom read, which is not possible in repeatable read mode?

The documentation says that a query in a repeatble read transaction sees a snapshot as of the start of the transaction, then how could it be possible for a query to read inconsistent data?

Here is my reading of that section.  I will admit it is confusing.

Suppose I have two tables:

CREATE TABLE batch (
   id serial not null unique,
   control_code text primary key,
   date_posted date not null default now()
);

CREATE TABLE details (
   batch_id int not null references batch(id),
   description text,
   primary key(batch_id, description)
);

Now, suppose we insert batch and details records in different transactions.  Session 1 inserts a batch and starts to insert details but before it finishes, session 2 starts up.  Session 2 gets to see the batch heading info, but does not wait on the commit on details to inform the user that no records are found.  Now if your batch and details are entirely in the same transaction then this never is a problem.

this would differ from serializable where you would expect to wait for the previous insert to complete and commit or rollback before determining whether to notify the user that no rows were found.


Prevent record from being inserted in the table in After Trigger I have a scenario where I have to insert a record in TableA only if the RecordId exists in TableB.

I have written an Instead of Trigger on TableA for Insert, in which I am checking if the RecordId exists in TableB, and if it exists then I am allowing the Insert in TableA by explicitly inserting the record in TableA in that trigger.

I want to know can I write an After Trigger on TableA, in which I do the same check, and in case if RecordId is not found in TableB then I delete the inserted record from TableA.

My question is If I fire delete query on TableA for the same record which I am inserting will it work, and prevent the record from being inserted from the TableA actually.

I do not see this as the most desirable implementation. Using a Foreign Key constraint to enforce correctness is (in my opinion) much better than  writing code to handle such issues.

However, regarding your question, Yes, TableA can have both an INSTEAD OF trigger and an AFTER trigger.  (In fact it can have several AFTER triggers.)

https://msdn.microsoft.com/en-us/library/ms189799.aspx outlines some rules, including:

If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger execution and before the AFTER trigger execution. If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not fired.
An AFTER trigger is executed only after the triggering SQL statement has executed successfully. This successful execution includes all referential cascade actions and constraint checks associated with the object updated or deleted. An AFTER trigger will not recursively fire an INSTEAD OF trigger on the same table.