Index optimization on a view using a full outer join Table structure:

Foo             FooChild                Bar
---             --------                ---
ID              ID                      ID
Date            FooID                   Date
GroupID         UserID                  UserID
Notes           Amount                  GroupID
                                        IsComplete

Foo has a unique index on Date+GroupID
FooChild has a FK to Foo, and a unique index on FooID+UserID, which includes Amount
Bar has a unique index on Date+UserID+GroupID, which includes IsComplete

Now I need to create a report showing the sum of all FooChild amounts alongside the count of complete Bars for any given date range.  The users also want to be able to see the stats per group or per user.  This would seem to be a great place to write a view:

create view vFooBar as
select f.Date, f.GroupID, fc.UserID, fc.Amount, b.IsComplete
from Foo f join FooChild fc on fc.FooID = f.ID
left join Bar b on f.Date = b.Date and f.GroupID = b.GroupID and fc.UserID = b.UserID
union
select b.Date, b.GroupID, b.UserID, x.Amount, b.IsComplete
from Bar b left join
    (select f.Date, f.GroupID, fc.UserID, fc.Amount
    from Foo f join FooChild fc on fc.FooID = f.ID) x
on x.Date = b.Date and x.GroupID = b.GroupID and x.UserID = b.UserID

(Here's why I wrote the view this way.)

Now I can easily write queries like this:

select UserID, sum(Amount) FooAmount, sum(cast(IsCompleted as int)) CompletedBars
from vFooBar
where Date between @fromDate and @toDate
group by UserID

But there's a snag here.  As soon as the date range starts getting relatively big, the execution plan goes all pear shaped.  It uses the date index on Foo, but instead of then using the FooID index on FooChild, it does a clustered index scan, then a hash match on FooID to join with the results from Foo.  And it does that twice in the overall plan; I'm guessing once for each aggregate.  And that really hurts.

I understand that using the index I created on FooChild may not be efficient, since the values of FooID for a given date might be discrete, though typically they are inserted in roughly the same order.

I could denormalize, and add Date and GroupID to the FooChild table, then index those columns, and I'm pretty sure that would improve performance a lot.  But it just doesn't feel right.

Any other ideas?

The optimizer makes choices based on costing estimates. The cost model is generic, and may not always choose optimal plans for your particular hardware, and its assumptions may not always be valid for your circumstances.

In this case, the optimizer assesses a hash join as the cheaper option over nested loops when the estimated number of rows to be joined is large. If you are certain that a nested loops join will always be preferable to a hash join, you could consider (and test!) forcing a seek instead of a scan of the FooChild table in the view:

SELECT
    f.TheDate,
    f.GroupID,
    fc.UserID,
    fc.Amount,
    b.IsComplete
FROM dbo.Foo AS f
JOIN dbo.FooChild AS fc WITH (FORCESEEK) -- New hint
    ON fc.FooID = f.ID
LEFT JOIN dbo.Bar AS b
    ON f.TheDate = b.TheDate
    AND f.GroupID = b.GroupID
    AND fc.UserID = b.UserID
UNION
SELECT
    b.TheDate,
    b.GroupID,
    b.UserID,
    x.Amount,
    b.IsComplete
FROM dbo.Bar AS b
LEFT JOIN
(
    SELECT
        f.TheDate,
        f.GroupID,
        fc.UserID,
        fc.Amount
    FROM dbo.Foo AS f
    JOIN dbo.FooChild AS fc WITH (FORCESEEK) -- New hint
        ON fc.FooID = f.ID
) AS x
    ON x.TheDate = b.TheDate
    AND x.GroupID = b.GroupID
    AND x.UserID = b.UserID;

Side note: while this transformation from the original full join is valid given the current uniqueness constraints on your tables, please review the answer to your previous question and consider rewriting the full join as suggested in my edit.


mysql server cannot launch in windows 7 I have been using MySQL as a windows service successfully on my windows 7 machine for perhaps a year.  It has always worked fine.  Here are the steps which I think caused it to no longer launch:

1.) My computer ran out of battery power and shut down automatically
    while the MySQL windows service was running.
2.) When I tried to restart the computer, it took a very long time
    to restore the windows session.  A dos prompt said it was
    restoring the windows session, and the fan was running very
    loudly.
3.) After perhaps 10 minutes of this, I turned off the computer
    forcibly by using the power switch.
4.) When I rebooted the computer, the dos prompt told me that
    the previous attempt to restore the windows session failed,
    and asked if I wanted to continue loading windows without
    restoring the previous session.  I said yes.
5.) When I tried to launch the MySQL windows service, I got the
    following dialog box indicating error 1067:

6.) I then tried to load MySQL from cmd.exe in order to see the
    error logs, and I got the following output which indicates
    that it cannot read/write:

How can I resolve this problem?

Here are the contents of my.ini:

[client]
no-beep
port=3306
[mysql]
default-character-set=utf8
[mysqld]
port=3306
datadir="C:/ProgramData/MySQL/MySQL Server 5.6/data\"
character-set-server=utf8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log-output=NONE
general-log=0
general_log_file="USER-PC.log"
slow-query-log=0
slow_query_log_file="USER-PC-slow.log"
long_query_time=10
log-error="USER-PC.err"
max_connections=100
query_cache_size=0
table_open_cache=2000
tmp_table_size=51M
thread_cache_size=9
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=100M
key_buffer_size=8M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_additional_mem_pool_size=100M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=4M
innodb_buffer_pool_size=305M
innodb_log_file_size=100M
innodb_thread_concurrency=9
innodb_autoextend_increment=64M
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=70
flush_time=0
join_buffer_size=256K
max_allowed_packet=4M
max_connect_errors=100
open_files_limit=4110
query_cache_type=0
sort_buffer_size=256K
table_definition_cache=1400
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000

EDIT:

I then restarted cmd.exe as an administrator per Rolando's advice, and got the following results, which indicate that windows cannot find a previous session for the MySQL service.  Then cmd.exe remains hung up indefinitely without returning a user prompt:

SECOND EDIT:

I then did both of Rolando's suggestions from his edited response, and got the following results indicating that the log sequence numbers are out of sync with one another, which would seem to be the result of the failed restart of windows.

THIRD EDIT:

Below is the result of telling cmd.exe to echo the PATH variable:

FOURTH EDIT:

Here are results of running dir on the data directory:

I just noticed that you are at the Windows Command Line. You are trying to launch MySQL.

You cannot start MySQL because you are not an Administrator.

Please Open CMD.EXE as an Administrator. Then, you can run

net start MySQL56_1
mysqld --console

According to the latest update, mysqld is running just fine. That's because it says

mysqld: ready for connecitons

A prompt will not return to you.

You can now open another Command Line session and login using the mysql client.

UPDATE 2014-07-28 17:51 EDT

You need to run the following:

cd C:\Program Files\MySQL\MySQL Server 5.6\bin
mysqld --console

UPDATE 2014-07-28 18:28 EDT

When you ran mysqld --console you were not using the my.ini

Call it like this

mysqld -defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" --console

If you want the prompt to come back, run this

start mysqld -defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" --console

UPDATE 2014-07-28 18:48 EDT

My suggestion from the Chat Session

cd "C:\ProgramData\MySQL\MySQL Server 5.6\data"
move ib_logfile* ..
net start MySQL 56_1

Give the service time to regenerate the Log Files, and you should be good to go.


Storing email,phone,fax and url in same table Is it ok to store email,phone,fax and url in same table?
I want user to be able to add multiple email,phone,fax or website url on their profile.

Here's my current table

Contacts

id

user_id

val (eg. test@mail.com,09222229291, http://facebook.com/test,432-2233)

contact_id

ContactTypes

id

name (eg. mobile,tel,email,url,fax)

You should do something like the following:

CREATE TABLE client
(
  client_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
  client_name VARCHAR(100)
  .. other
  .. client
  ., fields
);

and

CREATE TABLE contact
(
  contact_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  client_id INT,
  contact_type VARCHAR(3),  -- (e.g. 'URL', 'PH', 'FAX'  &c.)
  contact_value VARCHAR(256),
  FOREIGN KEY (fk_contact_client) REFERENCES client (client_id)
);

A few things to note:

No need for a contact_type table unless there are many (rule of thumb, more than ~7 and/or if your codes are not intuitive).
Of course, you can add a contact_type child table to contact if you wish, in the same manner as contact is a child to client. Whether you decide you want to do this will depend on your use case and the number of contact_types.
It's a pity that MySQL doesn't have CHECK constraints, which would be ideal for a small number of contact types. Whatever you do, don't use an ENUM type, they are evil
If restricting/controlling contact_type is important to you, use a lookup table. This is not "clutter" - many small reference  tables are a "good thing" and MySQL can cope with 1000's of tables. Take a look at the first reference in my answer here - avoid the OTLT (One True Lookup Table) trap. Also, see here and here for further discussion ( Joe Celko is a big hitter in the world of SQL).


MySQL ran out of disk creating an index, now I can't reclaim my disk I've run a command on my MySQL database to add an index to a large table containing 8 million records (ironically so I could start deleting some of them):

CREATE  INDEXindex_audit_logs_on_idONaudit_logs``

As part of that, MySQL will create a temporary table. I have 40GB of total disk, and this table is 25 GB, so I ran out of disk space while doing this. My commmand errored:

Mysql2::Error: The table '#sql-2b9f_2178' is full: CREATE  INDEX `index_audit_logs_on_id` ON `audit_logs`

However, I am still out of disk space now!

/dev/sdc               40G   38G   20K 100% /var/lib/mysql

I don't see this temporary table. Is there something I can do to reclaim by 15GB of disk space?

CREATE INDEX

If the files #sql-2b9f_2178.* were left behind by the failed operation, rm them.

The CREATE INDEX was copying the table over to create the index.  This is the new copy, before it is swapped into place.

Two important bits of info:  What ENGINE is used for the table?  What version of MySQL?  There have been a number of changes in this area; I suspect you have an antique version and/or are using MyISAM.  Recommend upgrading.

Deleting

Do you have any index on the table?  If so, then the following blog has a number of techniques that may be viable for helping clean up the table:  Big Deletes .

However, there may still be an issue of recovering disk space.  So...

If the amount of data you need to keep is small enough to fit, then focus on this part of the blog:  Deleting more than half a table .

Still, there may be a problem.

If MyISAM, no problem -- simply DROP the old table and RENAME TABLE.

If InnoDB, then you really need to be using innodb_file_per_table = ON.  Set this globally, and re-login in order to get the value.

But... There may still be problems.  We can continue this discussion after you have answered my questions.


Queries show data but not column names I'm getting through  the postgresql tutorial, I've created my db, 2 tables, weather and cities and added a few lines of data.
When I query my db with this sql statement for example:

SELECT city, temp_lo, temp_hi, prcp, date FROM weather;

I should get this:

     city     | temp_lo | temp_hi | prcp |    date
--------------+---------+---------+------+------------
San Francisco |      46 |      50 | 0.25 | 1994-11-27
San Francisco |      43 |      57 |    0 | 1994-11-29
Hayward       |      37 |      54 |      | 1994-11-29
(3 rows)

but I get this:

San Francisco |      46 |      50 | 0.25 | 1994-11-27
San Francisco |      43 |      57 |    0 | 1994-11-29
Hayward       |      37 |      54 |      | 1994-11-29

I'm missing the column names for all queries. Why wouldn't psql show the column names? Do I have something to configure or is this some kind of bug?

From the manual:

  \t

  Toggles the display of output column name headings and row count footer. This command is equivalent to \pset tuples_only and is provided for convenience.


How to determine the root cause of a performance spike during a heavy load on DB2? I am testing the performance of an application that links to a DB2 database engine at the back end.

Looking at the transaction timer logs, I see that on the 37'th minute into the test run, transactions take a very high time to process. This lasts for maybe a few seconds and then the run proceeds at acceptable speed.

I've done three test runs (restoring the data to initial test data, clearing up everything = basically, ensuring each run has the same starting position) and they all experience the same phenomenon: at the 37'th minute, a performance spike:

My current lead is that I notice, on the second run it's more visible, that every 37-ish minutes, there's a "minispike".

I think what's going on is the DB2 is doing some regular maintenance every 37 minutes, and the first maintenance job is a bit harder, as the machine hasn't cached a required program or...?

I am wondering what advice can I get to investigate this further? Debugging my own application did not show any reason to hang as it does there.

Info:
DB: DB2 10.5.0.7
OS: SLES 12 0
App: Precompiled to bind to DB2 in 64-bit mode

This could be one of the autonomic maintenance processes, e.g. automatic collection of table statistics, although the 37 minute interval looks a bit suspect.

You can start with looking at db2diag.log at around the time of the spike -- most auto maintenance tasks would be logged there.

For a quick look at what thread might be busy, run

db2pd -edus interval=<seconds> -repeat

around the time of the spike, choosing the interval appropriate for the spike width.

Another useful tool is the system stored procedure MONREPORT.DBSUMMARY(<seconds>), which will print the summary of various wait times over the specified period -- run it before, during, and after the spike to compare the numbers.


CMD prompt can't display Unicode characters that are stored in oracle database correctly I've been meddling with charset/encoding problems of Oracle database, PL/SQL developer and JDBC and these are what I want to achieve:

Input Unicode characters from a browser into the database
Display Unicode characters that are retrieved from the database correctly in the browser, CMD prompt, and PL/SQL developer

And these are what I've done:

Change the server charset to AL32UTF8 by following the steps:

SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP MOUNT;
SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL>ALTER DATABASE OPEN;
SQL>ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;
SQL>ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL32UTF8;
SQL>update props$ set VALUE$='UTF8' where NAME='NLS_NCHAR_CHARACTERSET';
SQL>ALTER SYSTEM DISABLE RESTRICTED SESSION;
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP

Change the client charset to AL32UTF8 by adding system environment variable NLS_LANG with value AMERICAN_AMERICA.AL32UTF8and changing the value of NLS_LANG to AMERICAN_AMERICA.AL32UTF8 under HKEY_LOCAL_MACHINE>SOFTWARE>ORACLE>KEY_OraDB12Home1 in regedit
Check "Always UTF8" under Tool>Preferences>Files>Format>Encoding in P/SD

Eventually if I added the Unicode characters to the database from either the browser or P/SD they can be displayed properly in the browser and P/SD but not in the CMD prompt. If they are added to the database from the CMD prompt they can be displayed correctly everywhere. For now I'm sure that the characters input from the browser and P/SD are Unicode characters, but not for the CMD input, which is also supposed to be Unicode since both the server and client charsets have been set to AL32UTF8. Here's an example of a correct display and an incorrect one:

In P/SD: (Correct)

SQL> select title from posts where id=134;
TITLE
--------------------------------------------------------------------------------
测试

SQL> SELECT DUMP(title, 1016) FROM posts where id=134;
DUMP(TITLE,1016)
--------------------------------------------------------------------------------
Typ=1 Len=6 CharacterSet=AL32UTF8: e6,b5,8b,e8,af,95

In command prompt: (Incorrect)

SQL> select title from posts where id=134;

TITLE
--------------------------------------------------------------------------------
娴嬭瘯

SQL> SELECT DUMP(title, 1016) FROM posts where id=134;

DUMP(TITLE,1016)
--------------------------------------------------------------------------------
Typ=1 Len=6 CharacterSet=AL32UTF8: e6,b5,8b,e8,af,95

SQL>

Most likely you corrupted your database and you should consider to make a full restore from backup.

ALTER DATABASE CHARACTER SET ... was supported till Oracle version 9 and worked only at certain conditions.

Never use clause INTERNAL_USE or make any updates on Oracle system tables unless you are advised by Oracle Support. If you like to change the character set on an existing database, follow this instruction: Character Set Migration

Perhaps you had good luck, because you changed only WHERE NAME='NLS_NCHAR_CHARACTERSET'. National character set are hardly used nowadays, perhaps you did not destroy everything.

Anyway, items 2.) and 3.) are correct. In case of 32 bit check also registry key HKEY_LOCAL_MACHINE>SOFTWARE>Wow6432Node>ORACLE>KEY_OraDB12Home1

In order to verify if your data is really stored in UTF-8 run

SELECT DUMP(YOUR_COLUMN, 1016) FROM YOUR_TABLE;

And check the binary values. For comparison you can use page Unicode code converter to get UTF-8 binary values (field "UTF-8 code units") of arbitrary strings.


Run queries in parallel I am running a sql script on mysql 5.7.18 ubuntu server 16.04 that does a large number of updates in one table. Each one of the 50k updates takes under a second but the total run time is very long (>20m).

I can see from PROCESSLIST that only one thread is running and is processing the updates one-by-one.

How can I use multiple threads to speed the execution ?

Because you wrote "each one of the..." it sounds like you are updating individual rows. Rather than doing a row at a time, have you explored writing the SQL so you are letting the DB update many rows in one SQL statement? That will likely get you much better performance than trying to multi-thread manually.


How could sequence.nextval be null in Oracle? I have an Oracle sequence defined like so:

CREATE SEQUENCE  "DALLAS"."X_SEQ"
    MINVALUE 0
    MAXVALUE 999999999999999999999999999
    INCREMENT BY 1 START WITH 0 NOCACHE  NOORDER  NOCYCLE ;

It is used in a stored procedure to insert a record:

PROCEDURE Insert_Record
                (p_name    IN  VARCHAR2,
                 p_userid  IN  INTEGER,
                 cur_out   OUT TYPES_PKG.RefCursor)
    IS
        v_id NUMBER := 0;
    BEGIN
        -- Get id value from sequence
        SELECT x_seq.nextval
          INTO v_id
          FROM dual;

        -- Line below is X_PKG line 40
        INSERT INTO X
            (the_id,
             name,
             update_userid)
          VALUES
            (v_id,
             p_name,
             p_userid);

        -- Return new id
        OPEN cur_out FOR
            SELECT v_id the_id
              FROM dual;
    END;

Occasionally, this procedure returns an error when executed from application code.

ORA-01400: cannot insert NULL into ("DALLAS"."X"."THE_ID")
ORA-06512: at "DALLAS.X_PKG", line 40
ORA-06512: at line 1

Details that may or may not be relevant:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
The procedure is executed via Microsoft.Practices.EnterpriseLibrary - Data.Oracle.OracleDatabase.ExecuteReader(DbCommand command)
The application does not wrap the call in an explicit transaction.
The insert fails intermittently - less than 1%

Under what circumstances could x_seq.nextval be null?

I'm pretty certain this will end up being an artifact of your code, or the .net driver you are using. I've knocked up a quick demo for you using pure SQL - PL/SQL and never get a lost sequence value. Incidentally the ref cursor you are using is probably unnecessary and likely impacts performance and readability of the code - my demo includes an insert_record2 procedure that consistently performs over 10% faster -in about 26s on my laptop vs 36 for the ref cursor version. I at least also think is easier to understand. You could obviously run a modified version against your test database complete with audit trigger.

/*
demo for dbse
assumes a user with create table, create sequence, create procedure pivs and quota.

*/

drop table dbse13142 purge;

create table dbse13142(
    the_id number not null
,   name   varchar2(20)
,   userid number)
;

drop sequence x_seq;
CREATE SEQUENCE  X_SEQ NOCACHE  NOORDER  NOCYCLE ;

create or replace PROCEDURE Insert_Record
                (p_name    IN  VARCHAR2,
                 p_userid  IN  INTEGER,
                 cur_out   OUT sys_refcursor)
    IS
        v_id NUMBER := 0;
    BEGIN
        -- Get id value from sequence
        SELECT x_seq.nextval
          INTO v_id
          FROM dual;

        -- Line below is X_PKG line 40
        INSERT INTO dbse13142
            (the_id,
             name,
             userid)
          VALUES
            (v_id,
             p_name,
             p_userid);

        -- Return new id
        OPEN cur_out FOR
            SELECT v_id the_id
              FROM dual;
    END;
/

create or replace PROCEDURE Insert_Record2
                (p_name    IN  VARCHAR2,
                 p_userid  IN  INTEGER,
                 p_theid   OUT dbse13142.the_id%type)
    IS
    BEGIN
        -- Get id value from sequence
        SELECT x_seq.nextval
          INTO p_theid
          FROM dual;

        -- Line below is X_PKG line 40
        INSERT INTO dbse13142
            (the_id,
             name,
             userid)
          VALUES
            (p_theid,
             p_name,
             p_userid);
    END;
/

set timing on

declare
   c sys_refcursor;
begin
for i in 1..100000 loop
   insert_record('User '||i,i,c);
   close c;
end loop;
commit;
end;
/

select count(*) from dbse13142;
truncate table dbse13142;

declare
  x number;
begin
for i in 1..100000 loop
   insert_record2('User '||i,i,x);
end loop;
commit;
end;
/

select count(*) from dbse13142;
truncate table dbse13142;


Communication Link Failure in SQL I have an SSIS package that runs daily in SQL agent, there are days that the job is failing due to "Communication Link Failure" but sometimes successful. I search the net for some solutions and there are recommendations to disabled TCP Chimney Offloading, RSS and NetDMA. So i disabled those features but still the error is recurring. What possibly causes the failure? Thanks

below are the options we have tried in our environment and we are successfull in reducing timeouts to 99%.

1.Change remote login time out options
2.change remote query time out options
3.Disable tcp chimney,rss options

http://blogs.technet.com/b/onthewire/archive/2014/01/21/tcp-offloading-chimney-amp-rss-what-is-it-and-should-i-disable-it.aspx

Once the above settings are in place,its time out to look on queries
1.Gather the most failed step by querying ssisdb catalog by limiting predicate to event of onerror
2.Once the above steps are gathered,concentrate on most failed steps and post the query here if you need any help in fine tuning.
3.further you also may need to check some aspects like
what is the load on the server
are my queries waiting for any resources consistently
are the failed queries using tempdb,if so what is the load on tempdb

once the above changes are done with respect to settings,we concentrated totally on Tuning queries and it yielded results


Do Nested Transactions commit? I am not happy with the Wikipedia article about nested transactions.

I would not use the word "commit" for a nested transaction.

For me "commit" means "durability". A nested transaction does not provide durability.

Is it ok to use the word "commit" for nested transactions?
What word could be used, if it is not ok?

I don't think there is a problem using the word commit and personally I don't see a better wording for the concept.

Most database systems do not support nested transactions, the only one I could find is BerkelyDB.

In their documentation on nested transactions they are using commit as the term.

  Committing a nested transaction has no effect on the state of the
  parent transaction.

In the book Concepts and Applications of Multilevel Transactions and Open Nested Transactions (1992)  they seem to be using the same wording.

  subtransactions can be made persistent independently of their commit
  state


SQL Server 2000 Replication, Performance, and Query Priorities I'm a developer and wanting to confirm the following:

Does anyone here have experience with SQL Server Replication (transactional) on 2000? We are trying to convince the DBAs to allow this in a production environment so that we can concurrently run "tests" -- but the DBAs are telling us that Replication in SQL Server 2000 doesn't work and/or that it has a severe performance impact on the source server.

I tried googling some information on this but haven't found any such information.

Also, do you know if it is true/not true that queries from Stored Procedures or queries called from a Linked Servers will take precedence over other queries (e.g. dynamic queries from a connecting application). I didn't think this was possible and have found no reference to this via my google searches... but the DBA claims this is true for SQL Server 2000, and as a result, causes notable performance slowdown from users of the web app connecting to the database.

Thanks!

Note that You are arguing for an UNSUPPORTED version of sql server.

Apart from that read below :

  but the DBAs are telling us that Replication in SQL Server 2000 doesn't work and/or that it has a severe performance impact on the source server.

This is completely False. Why would T-Rep not work in sql 2000 ? Why would it have a severe performance impact on source server ?

The only impact that I can see is

During the initial snapshot generation as it locks the table while the snapshot is generated and if the table is huge then it will lock it for longer durations. Imagine this causing blocking or possibly timeouts.
when you are replicating the entire database (not only required set of tables/articles) and when you want to reinitialize the replicaiton - meaning new snapshot !
When the n/w bandwithd is not good or there are n/w related issues.

Above holds true even today for sql server 2012 or 2014.

Note that T-Rep on sql 2005 and up has improved a lot, but the argument your DBA has made is baseless. We even has some legacy applications running on sql 2000 and we use replication heavily and is stable in sql 2000 - no issues at all.

  if it is true/not true that queries from Stored Procedures or queries called from a Linked Servers will take precedence over other queries (e.g. dynamic queries from a connecting application).

Again completely baseless or you might have misunderstood it. Stored procedures can have their plan complied and stored in procedure cache and the plan can be reused.

I don't think you can have priority of the kind you have mentioned.


MySQL server Load is 100% CPU i have load on mysql 500%

my server info:
Harddisk    512 GB SSD 2.5"
RAM 32GB
CPU Intel Xeon E3-1230v2
Connection 1GBit

my Max Online: 35
Max. concurrent connections: 250

i use Framework Yii2 and Activerecord

mysql Conf:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
default-time-zone='+00:00'
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size     = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
max_connections        = 1000
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries   = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size   = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

I don't see the configurations are rightly set for memory. Hope you are using innodb engine.

Use this to fill basic my.cnf to effectively make user of your server setup. https://tools.percona.com/wizard


For two triggers(before insert and after insert),is it allowed for you to use where in the NEW keyword I have two triggers.One after insert trigger and the other before insert trigger.The two triggers are using the NEW keyword to get the last inserted message.

Both triggers are watching a table called incoming_sms and the row inserted has three fields. Id,name,service

Service contains a unique number 9999999 for first service and 8888888 for second service.

Is it possible that when using the NEW keyword that i also use where service = 9999999 just to be sure i am referring to the right last inserted message by specific service.

If i just use New.col without specifying the service,i may be referring to the wrong service since i will get the last inserted value by any service.

I hope i explained that clearly.

You will notice that triggers in MySQL include FOR EACH ROW.  The values in NEW alias always, only, ever refer to the specific row that invoked the trigger. There is no ambiguity.

If multiple rows are inserted in a single query, the trigger fires individually for each row, so in each iteration through the trigger you can test any of the NEW columns.


Why dropping all foreign keys from a script is not working? I have sysadmin rights on a SQL Server 2008 R2 box. I found a script that can drop all foreign keys and re-add them for us when we need to after a testing import.
So I execute the below code with no syntax errors as it displays the msg that says "Alter Table [ParentTable] Drop Constraint FK_Name"... for a total of 23 keys, but yet when I refresh the Server or the DB, and Tables, I still see them when I expand the Keys node for each table? so why its not dropping them?
Also, do I need to convert it to a permanent table or put the script in a stored procedure in case if I want to run it again for testing purposes? Please advice.

I used the following script from "Swastik Mishra" posted by "Pinal Dave"
http://blog.sqlauthority.com/2014/04/11/sql-server-drop-all-the-foreign-key-constraint-in-database-create-all-the-foreign-key-constraint-in-database/

    SET NOCOUNT ON
DECLARE @table TABLE(
RowId INT PRIMARY KEY IDENTITY(1, 1),
ForeignKeyConstraintName NVARCHAR(200),
ForeignKeyConstraintTableSchema NVARCHAR(200),
ForeignKeyConstraintTableName NVARCHAR(200),
ForeignKeyConstraintColumnName NVARCHAR(200),
PrimaryKeyConstraintName NVARCHAR(200),
PrimaryKeyConstraintTableSchema NVARCHAR(200),
PrimaryKeyConstraintTableName NVARCHAR(200),
PrimaryKeyConstraintColumnName NVARCHAR(200)
)
INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema,     ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
SELECT
U.CONSTRAINT_NAME,
U.TABLE_SCHEMA,
U.TABLE_NAME,
U.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE
C.CONSTRAINT_TYPE = 'FOREIGN KEY'
UPDATE @table SET
PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
FROM
@table T
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME
UPDATE @table SET
PrimaryKeyConstraintTableSchema = TABLE_SCHEMA,
PrimaryKeyConstraintTableName = TABLE_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME
UPDATE @table SET
PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME
--SELECT * FROM @table
--DROP CONSTRAINT:
SELECT
'
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' +      ForeignKeyConstraintTableName + ']
DROP CONSTRAINT ' + ForeignKeyConstraintName + '

GO’
FROM
@table
–ADD CONSTRAINT:
SELECT
‘
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' +   ForeignKeyConstraintTableName + ']
ADD CONSTRAINT ‘ + ForeignKeyConstraintName + ‘ FOREIGN KEY(‘ +   ForeignKeyConstraintColumnName + ‘) REFERENCES [' + PrimaryKeyConstraintTableSchema + ']. [' + PrimaryKeyConstraintTableName + '](‘ + PrimaryKeyConstraintColumnName + ‘)

GO’
FROM
@table
GO

Here's a better script.

It doesn't use the awful INFORMATION_SCHEMA views. Among other things, these views do not expose foreign keys against unique constraints; only against explicit primary key constraints.
It doesn't place GO inside T-SQL, which won't work if you execute the command dynamically (since GO is a batch separator for interactive tools like SSMS, not a T-SQL keyword).
It doesn't have incorrectly copied apostrophes in place of single quotes (’ vs. ').
It deals with multi-column foreign key constraints (the code you have now assumes that all foreign keys are only defined against single columns).

The remainder is taken from my blog post, Four Practical Use Cases for Grouped Concatenation. It's script #4.

The drop is easy; just build a simple list of ALTER TABLE commands from sys.foreign_keys (I've also added commented-out filters that could be used to limit the scope to referenced tables matching a naming pattern or existing within a certain schema):

DECLARE @drop NVARCHAR(MAX) = N'';

SELECT @drop += N'
ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)
    + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS ct ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs ON ct.[schema_id] = cs.[schema_id];
-- WHERE fk.referenced_object_id IN
-- (SELECT [object_id] FROM sys.tables WHERE name LIKE N'%some pattern%')
-- WHERE fk.referenced_schema_id IN
-- (SELECT [schema_id] FROM sys.schemas WHERE name = N'some_schema')

PRINT @drop;

Don't execute that yet! Of course, you need to generate the CREATE commands while they still exist. Create is a little more complex. We need to generate the list of columns on both sides of the constraint, even though in most cases there is only one column (the multi-column situation is where grouped concatenation comes in real handy):

DECLARE @create NVARCHAR(MAX) = N'';

SELECT @create += N'
ALTER TABLE '
   + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)
   + ' ADD CONSTRAINT ' + QUOTENAME(fk.name)
   + ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(c.name)
    FROM sys.columns AS c
    INNER JOIN sys.foreign_key_columns AS fkc
    ON fkc.parent_column_id = c.column_id
    AND fkc.parent_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id
    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
  + ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name)
  + '(' + STUFF((SELECT ',' + QUOTENAME(c.name)
    FROM sys.columns AS c
    INNER JOIN sys.foreign_key_columns AS fkc
    ON fkc.referenced_column_id = c.column_id
    AND fkc.referenced_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id
    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS rt -- referenced table
  ON fk.referenced_object_id = rt.[object_id]
INNER JOIN sys.schemas AS rs ON rt.[schema_id] = rs.[schema_id]
INNER JOIN sys.tables AS ct -- constraint table
  ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs ON ct.[schema_id] = cs.[schema_id];
--WHERE rt.name LIKE N'%some pattern%'
--WHERE rs.name = N'some_schema'

PRINT @create;

When you're happy with the output (keep in mind that PRINT is limited to 8K, so it may look like the command is truncated), add this to the end, and run it again:

EXEC sys.sp_executesql @drop;

-- drop and re-create the table here, whatever that needs to entail

EXEC sys.sp_executesql @create;


Cannot drop nonexistent constraint and cannot create it either While testing some migration scripts with a copy of production data (scripts run fine with development data) I found a curious situation. A CONSTRAINT has changed so I'm issuing DROP + ADD commands:

ALTER TABLE A_DUP_CALLE
DROP CONSTRAINT A_DUP_CALLE_UK1;

ALTER TABLE A_DUP_CALLE
ADD CONSTRAINT A_DUP_CALLE_UK1 UNIQUE (
    CONTROL_ID,
    CALLE_AYTO_DUPL
)
ENABLE;

The DROP command worked fine but the ADD one failed. Now, I'm into a vicious circle. I cannot drop the constraint because it doesn't exist (initial drop worked as expected):

  ORA-02443: Cannot drop constraint  - nonexistent constraint

And I cannot create it because the name already exists:

  ORA-00955: name is already used by an existing object

I type A_DUP_CALLE_UK1 into SQL Developer's Search box and... there it is! Owner, table name, tablescape... everything matches: it isn't a different object with the same name, it is my original constraint. The table appears in the constraint details but the constraint does not appear in the table's details.

My questions:

What's the explanation for this?
How can I ensure it won't happen when I make the real upgrade in live server?

(Server is 10g XE, I don't have enough reputation to create the tag.)

At a guess I'd say Marian is right and this is caused by a unique index and constraint having the same name, eg:

create table t( k1 integer, k2 integer,
                constraint u1 unique(k1,k2) using index(create unique index u1 on t(k1,k2)),
                constraint u2 unique(k2,k1) using index u1);

select count(*) from user_indexes where index_name='U1';

COUNT(*)
----------------------
1

alter table t drop constraint u1;

select count(*) from user_indexes where index_name='U1';

COUNT(*)
----------------------
1

Normally when you add a unique constraint, a unique index with the same name is created - but the index and constraint are not the same thing. Have a look at all_indexes to see if there is an index called A_DUP_CALLE_UK1 and try and figure out if it is used by something else before you drop it!


REORG command does not work in IBM Data studio but same works in Command Line Processor I'm using the latest version of IBM Data Studio 4.1.2.0

I tried to reorg a table using IBM Data Studio after some alterations on the table with this command.

reorg table MYTABLE;

This command shows an error

  REFRESH was expected instead of reorg

But even after using the REFRESH command, the table doesn't come out of the "reorg pending" state.

The same command when I tried it via the DB2 Command Line Processor worked perfectly fine.

Any suggestions on how to to enable the REORG command in Data Studio or is it not supported at all?

I guess you tried to run it in an editor window.
YOu have to switch to

  DB2 server Command Line Processor

in the Configuration tab of the editor.
Do not forget to explicitly connect to your database

connect to sample ;
reorg table suppliers;

Alternatively you could switch to "Administer Databases" and run the reorg from the contect menu of a table:


How does disabling a network protocol affect SQL server performance The SQL server 2000 process info monitor displays about 100 processes that use a mix of Named Pipes/TCP-IP and (integrated authenticated) users. I was wondering if:

disabling Named pipes - or - TCP-IP (so that all applications are forced to use a single protocol) would affect SQL server performance in anyway
using a single SQL server login instead of multiple windows login would affect SQL server performance in anyway (there is no real need for separate logins)

What I am saying is that if I consolidate all connections into few possible combinations as possible, do I get some performance benefit?

Yes, IMHO you should disable named pipes and use only TCP/IP for better performance. You can force this in the connection strings for your apps and change them one at a time, rather than just shutting off named pipes and then getting a bunch of complaints:

Data Source = tcp:IP-or-host-name[\instance-name]

Don't shut off named pipes until all your apps are using TCP/IP. You can check what your apps are using via sys.dm_exec_connections.net_transport - this will have values such as TCP or Shared Memory. You can check the program too via a query like this:

SELECT c.net_transport, s.program_name, c.client_net_address
  FROM sys.dm_exec_sessions AS s
  INNER JOIN sys.dm_exec_connections AS c
  ON s.session_id = c.session_id;

However, for your second question, using one login or multiple logins won't affect performance much unless you're at the upper end and exceeding your pooled connections limit. In most cases consolidating into a single login will just make it much harder to later audit etc.


Syntax error when a job is executed in anonymous block in Oracle I'm trying to execute a job with an alter system command but I get a error. I know that the error is in SID=''INST1'''', but I don't know how to avoid it.
I can create the job but the error is when the job is executed.

This is the code of the job:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'JOB_Cambia_Planes_Prueba',
   job_type             => 'PLSQL_BLOCK',
   job_action           =>
'BEGIN
    EXECUTE IMMEDIATE ''ALTER SYSTEM SET resource_manager_plan=PLAN1 SCOPE=BOTH SID=''INST1'''';
exception when others then dbms_output.put_line(sqlerrm);
END;
',
   start_date           =>  TO_DATE('16-12-2019 17:33','DD-MM-YYYY HH24:MI'),
   repeat_interval      => 'FREQ=DAILY',
   enabled              =>  TRUE,
   comments             => 'Cambiar Planes');
exception when others then dbms_output.put_line(sqlerrm);
END;

And the error is

ORA-06550: line 2, column 89:
PLS-00103: Encountered the symbol "INST1" when expecting one of the following:

   * & = - + ; < / > at in is mod remainder not rem return
   returning <an exponent (**)> <> or != or ~= >= <= <> and or
   like like2 like4 likec between into using || multiset bulk
   member submultiset
The symbol "* was inserted before "INST1" to continue.

Not enough apostrophes.

SQL> !cat 1.sql
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'JOB_Cambia_Planes_Prueba',
   job_type             => 'PLSQL_BLOCK',
   job_action           =>
'BEGIN
        EXECUTE IMMEDIATE ''ALTER SYSTEM SET resource_manager_plan=PLAN1 SCOPE=BOTH SID=''INST1'''';
exception when others then dbms_output.put_line(sqlerrm);
END;
',
   start_date           =>  TO_DATE('16-12-2019 17:33','DD-MM-YYYY HH24:MI'),
   repeat_interval      => 'FREQ=DAILY',
   enabled              =>  TRUE,
   comments             => 'Cambiar Planes');
exception when others then dbms_output.put_line(sqlerrm);
END;
/

SQL> @1

PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.run_job('JOB_Cambia_Planes_Prueba');
BEGIN dbms_scheduler.run_job('JOB_Cambia_Planes_Prueba'); END;

*
ERROR at line 1:
ORA-06550: line 2, column 82:
PLS-00103: Encountered the symbol "INST1" when expecting one of the following:
* & = - + ; < / > at in is mod remainder not rem return
returning <an exponent (**)> <> or != or ~= >= <= <> and or
like like2 like4 likec between into using || multiset bulk
member submultiset
The symbol "* was inserted before "INST1" to continue.
ORA-06512: at "SYS.DBMS_ISCHED", line 242
ORA-06512: at "SYS.DBMS_SCHEDULER", line 566
ORA-06512: at line 1

SQL> exec dbms_scheduler.drop_job('JOB_Cambia_Planes_Prueba');

PL/SQL procedure successfully completed.

SQL> !vi 2.sql

SQL> !cat 2.sql
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'JOB_Cambia_Planes_Prueba',
   job_type             => 'PLSQL_BLOCK',
   job_action           =>
'BEGIN
        EXECUTE IMMEDIATE ''ALTER SYSTEM SET resource_manager_plan=PLAN1 SCOPE=BOTH SID=''''INST1'''''';
exception when others then dbms_output.put_line(sqlerrm);
END;
',
   start_date           =>  TO_DATE('16-12-2019 17:33','DD-MM-YYYY HH24:MI'),
   repeat_interval      => 'FREQ=DAILY',
   enabled              =>  TRUE,
   comments             => 'Cambiar Planes');
exception when others then dbms_output.put_line(sqlerrm);
END;
/

SQL> @2

PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.run_job('JOB_Cambia_Planes_Prueba');

PL/SQL procedure successfully completed.

To elaborate, this value of job_action

'BEGIN
        EXECUTE IMMEDIATE ''ALTER SYSTEM SET resource_manager_plan=PLAN1 SCOPE=BOTH SID=''INST1'''';
exception when others then dbms_output.put_line(sqlerrm);
END;
',

is a string literal. Obviously, string literals are delimited with single quotes ('). That string, however, is a script containing another string literal, the ALTER SYSTEM command, which must be delimited too. As you know, you escape a quotation mark inside a string by doubling it. So, the ALTER SYSTEM command inside the string literal must be delimited with '' rather than '. This is what you have done, so far so good.

Now, the ALTER SYSTEM command, in its turn, also contains a string literal, namely INST1. This is now the second level of string nesting. You need to double the delimiters again, so INST1 should be delimited with ''''. In your snippet, however, it is delimited with '', which is wrong.

Therefore, the solution is to rewrite the … SID=''INST1'''' bit as … SID=''''INST1'''''', as already illustrated above. The closing six apostrophes are the four closing apostrophes for the INST1 value plus the two closing apostrophes for the ALTER SYSTEM command.


Drop foreign key locks referenced table Recently I wanted to drop a FK on table X referencing the userid of the "user" table. The user table is of course very frequently read from, but still I assumed it would be safe to drop the FK without a database downtime. My reasoning was that the user table should not have to be locked while removing the FK from table X.

When executing

ALTER TABLE x drop constraint fk12345;

though, the query took very long, the database load increased significantly and the query had to be aborted.

So my question is: Does removing a FK lock the referenced table? If not, what else might be the explanation for the long duration?

Extra info: The query was run against two postgres9.3 instances running behind pgpool.

ALTER TABLE x drop constraint fk12345; take long time because the parent table (user) in transaction(delete,insert update,..) or X table itself in transaction(not commit or rollback), it meant ALTER TABLE x drop constraint fk12345 may not make the User table locked.


How to replace column if not null in select statement? I have a Contact (ID, FirstName, LastName, MiddleName).

For example:

| ID | FirstName | LastName | MiddleName |
|====|===========|==========|============|
| 1  | Mark      |Jordan    | E          |
|----|-----------|----------|------------|
| 2  | James     | Blunt    | NULL       |
|----|-----------|----------|------------|

I want to output

Mark E. Jordan
James Blunt

So, if MiddleName is not null, add a '.' after, otherwise output null.

I am aware of isnull function, however what I need is an isnotnull function. How can I finish the query?

My query so far:

select FirstName + ' ' + isnull(...)  + LastName
from Contact

It's great that a NULL plus a value produces a NULL*, means you can do stuff like this...

SELECT FirstName + ' ' + ISNULL(MiddleName + '. ', '') + LastName
FROM Contact;

Hopefully, this works for what you want.

* Assuming CONCAT_NULL_YIELDS_NULL is set ON, as it should be;


Index Strategy for MySQL Query that matches on one column and between two others I have a table that contains a set of measurements for a continuous stream of processes. Although each process is individual, they are categorized into groups. The events have a start and end timestamp and a process group identifier.
The table structure is as follows (InnoDB, MariaDB 10):
Table Name: measurements
 CREATE TABLE `measurements` (
 `row_id` int(11) NOT NULL AUTO_INCREMENT,
 `process_name` varchar(100) COLLATE utf8_bin NOT NULL,
 `process_id` int(11) NOT NULL,
 `process_group_id` tinyint(4) NOT NULL,
 `measurement_1` float NOT NULL,
 `measurement_2` float NOT NULL,
 `measurement_3` float NOT NULL,
 `measurement_4` float NOT NULL,
 `start_timestamp` int(11) NOT NULL,
 `end_timestamp` int(11) NOT NULL,
 PRIMARY KEY (`row_id`),
 KEY `process_group_id` (`process_group_id`,
     `start_timestamp`,`end_timestamp`),
 KEY `process_id` (`process_id`)
) ENGINE=InnoDB
  AUTO_INCREMENT=7294932
  DEFAULT CHARSET=utf8 COLLATE=utf8_bin

I'm designing a query to obtain the sum of measurements 1,2,3 & 4 for all processes running within a group at a particular point in time so that the app can express each measurement for a specific process as a percentage of the total measurements in the group at that time. The start and end times of processes within a group are not synchronized and they are of variable length.
So for a process running in Group 5, at timestamp 1431388800
SELECT  SUM(measurement_1),
        SUM(measurement_2),
        SUM(measurement_3),
        SUM(measurement_4)
    FROM  measurements
    WHERE  process_group_id = 5
      AND  1431388800
           BETWEEN start_timestamp
               AND end_timestamp

This query runs, but takes around 0.5s. The table has 8m records and grows by about 30,000 a day.
I have an index on process_group_id, start_timestamp, end_timestamp. However, the query does not appear to use anything but the process_group_id part of the index. I created an additional index on process_group_id alone to check this, and once created EXPLAIN showed it using this index.
After some searching, I saw a suggestion to modify the query and add an ORDER BY clause. Having done this the query is accelerated to around 0.06s and it seems to use the full index. However, I'm unsure as to why:
SELECT  process_group_id,
        SUM(measurement_1),
        SUM(measurement_2),
        SUM(measurement_3),
        SUM(measurement_4)
    FROM  measurements
    WHERE  process_group_id = 5
      AND  1431388800
  BETWEEN start_timestamp
      AND end_timestamp
    ORDER BY  process_group_id ASC

With 30,000 new records a day that requires their shares to be calculated, 0.06s is still not particularly fast. Is there a better way of structuring either the table or designing the query to get a few orders of magnitude quicker, or is a query which matches on one column and then a range query on two others always going to be fairly slow to run?

(Not an answer, but too clumsy for a comment.)

  have an index on process_group_id, start_timestamp, end_timestamp. However the query does not appear to use anything but the process_group_id part of the index.

It is actually using start_timestamp although it does not say so.  What was the key_len?  That may give a clue.  Also try EXPLAIN FORMAT=JSON SELECT ....

AND  1431388800 BETWEEN start_timestamp AND end_timestamp

Turn that into the following to see if it helps:

AND  start_timestamp <= 1431388800
AND  end_timestamp   >= 1431388800

I suspect it is identical, but I am not sure.

Caution.  The difference between 0.5s and 0.06s could a warmed up cache.  Run timings twice.  Also, use SQL_NO_CACHE to avoid the Query cache.

How wide are the timestamp ranges typically?  How precise is 1431388800?  The values sound like they have a resolution of 1 second.  What if we switched to 1 minute or 1 hour?

After you provide some answers, I will possibly suggest turning this into a Data Warehouse application and discuss Summary tables.

Edit

Consider this approach to storing the data.  (I still don't have enough details to determine what variant of the following would be optimal.)

Since you have a "processing" phase that leads to the table in question, I suggest rewriting it to store into a different table (either in place of the existing one, or in addition)

CREATE TABLE ByMinute (
    process_group_id ...
    ts TIMESTAMP NOT NULL, -- rounded to the minute
    sum_1 FLOAT  -- see below
    sum_2 ...,
    PRIMARY KEY(process_group_id, ts)
);

The table contains one row per minute.  That's 0.5M per year, not terribly big.  If converting from the existing structure do sum_1 += measurement_1 for each row BETWEEN starttime AND endtime.

That is possibly more processing than you are currently doing, but it should not be excessive.  And it makes the SELECT extremely efficient:

SELECT  sum_1, sum_2, sum_3, sum_4
    FROM  ByMinute
    WHERE  process_group_id = 5
      AND  ts = somehow round 1431388800 to a minute

You currently have a daily dump.  The processing should be obvious.  If you switch to "streaming" and use the ping-pong method I mentioned, then very similar code can be used for each transient table.  And you would probably have nearly up-to-the-second data all the time.


What does "Deleting unrecoverable checkpoint table row" mean? When I look at our production SQL Server 2016's server logs I am seeing multiple entries that say:

  [INFO] Database ID: [5]. Deleting unrecoverable checkpoint table row
  (id: 294).

The id at the end is incremented by 1 for each entry.

I Googled for this error and there is literally nothing about it.  What does this entry mean?  It sounds rather ominous.

This is my assumption based on the log entry -

If you are using Memory optimized tables, there are Checkpoint File Pair (CFPs).

  CPFs transition through several states before they can be deallocated. Database checkpoints and log backups need to happen to transition the files through the phases, and ultimately clean up files that are no longer needed. For a description of these phases, see sys.dm_db_xtp_checkpoint_files

The log entry refers to deallocation by garbage collection process.


Does document "depth" affect CouchDB performance? I have a project where it is very tempting to use a deeply nested collection of data. But how does that affect performance? Are there any reasons why I should avoid doing this? I'm talking about lists in lists in lists, etc.

Per this StackOverflow thread, it sounds like lists are created by serializing and then deserializing the data.

If you were deeply nesting your documents with list inside of list, that would be a serious performance hit purely from the serialization and deserialization.

If you have a hierarchy like this, I would consider using MySQL, since it's designed for relational data.  It would be simple to implement a hierarchy using a relational DBMS compared to CouchDB.


Oracle GROUP BY issue I have this query:

Select To_Char(Order.Date, 'IW') As "Week No",
       Order.User As "User",
       Count(Order.OrderNo) As "Total Orders",
       Sum(Order_Totals.Qty) As "Total Items",
From Order Order,
     Order_Totals Order_Totals
Where Order.OrderNo = Order_Totals.OrderNo
And (Order.Date Between '01-JAN-15' And '31-DEC-15')
Group By Order.User

But it throws the error message:

Not a valid GROUP BY expression

I want to show how many orders and items each user has created each week this year, looking at http://www.techonthenet.com/oracle/group_by.php I thought that I am using GROUP BY correctly, but clearly not and I am not sure how I can sort it.

You have to add To_Char(Order.Date, 'IW') in the GROUP BY too.


How do I add a where statement at the end of query for a column that does not have a table to reference from? select
CASE WHEN (to_char(((case when v.trip_order_start_date is null then case when v.manual_start_date is null then t.required_pickup_date else v.trip_order_start_date end else v.trip_order_start_date end  + (interval  '-1 hours' * ofc.offset)) at time zone 'utc' at time zone 'America/Chicago' + (interval  '-1 hours' * ofc.offset)) at time zone 'utc' at time zone 'America/Chicago', 'YYYYMM') < to_char((now() at time zone 'utc' at time zone 'America/Chicago'), 'YYYYMM')
        AND(coalesce(posted_date, paid_date) IS NULL
            OR to_char((coalesce(v.posted_date, v.paid_date) + (interval  '-1 hours' * ofc.offset)) at time zone 'utc' at time zone 'America/Chicago', 'YYYYMM') > to_char((now() at time zone 'utc' at time zone 'America/Chicago' - interval '1' month), 'YYYYMM'))) THEN 'YES' ELSE 'NO' END AS accrual

WHERE
accrual = 'YES'

The problem is there is no table for me to reference the "accrual" column so I get the below error
[Code: 0, SQL State: 42703]  ERROR: column "accrual" does not exist
Position: 10708  [Script position: 10708 - 10715]

Use the WITH clause to get the result set and SELECT only the wanted rows.
WITH CTE As (
select
CASE WHEN (to_char(((case when v.trip_order_start_date is null then case when v.manual_start_date is null then t.required_pickup_date else v.trip_order_start_date end else v.trip_order_start_date end  + (interval  '-1 hours' * ofc.offset)) at time zone 'utc' at time zone 'America/Chicago' + (interval  '-1 hours' * ofc.offset)) at time zone 'utc' at time zone 'America/Chicago', 'YYYYMM') < to_char((now() at time zone 'utc' at time zone 'America/Chicago'), 'YYYYMM')
        AND(coalesce(posted_date, paid_date) IS NULL
            OR to_char((coalesce(v.posted_date, v.paid_date) + (interval  '-1 hours' * ofc.offset)) at time zone 'utc' at time zone 'America/Chicago', 'YYYYMM') > to_char((now() at time zone 'utc' at time zone 'America/Chicago' - interval '1' month), 'YYYYMM'))) THEN 'YES' ELSE 'NO' END AS accrual
)
SELECT * FROM CTE WHERE accrual = 'YES'

Or use the SELECT query as SUBSECT in a FROM clause and use that
SELECT * FROM (
    select
    CASE WHEN (to_char(((case when v.trip_order_start_date is null then case when v.manual_start_date is null then t.required_pickup_date else v.trip_order_start_date end else v.trip_order_start_date end  + (interval  '-1 hours' * ofc.offset)) at time zone 'utc' at time zone 'America/Chicago' + (interval  '-1 hours' * ofc.offset)) at time zone 'utc' at time zone 'America/Chicago', 'YYYYMM') < to_char((now() at time zone 'utc' at time zone 'America/Chicago'), 'YYYYMM')
            AND(coalesce(posted_date, paid_date) IS NULL
                OR to_char((coalesce(v.posted_date, v.paid_date) + (interval  '-1 hours' * ofc.offset)) at time zone 'utc' at time zone 'America/Chicago', 'YYYYMM') > to_char((now() at time zone 'utc' at time zone 'America/Chicago' - interval '1' month), 'YYYYMM'))) THEN 'YES' ELSE 'NO' END AS accrual
    ) t1
WHERE accrual = 'YES'

In both cases you need a valid SELECT


SQL Server: Performance Insert Into vs Select Into I am hearing different things from colleagues/research. What are good guidelines in performance for, Select Into vs Insert into when creating a temp table? I know difference is minimal for small tables.

Eg: Table has 20 columns, 50 million rows.

I've had DBAs state, Insert into is faster, since compiler/parser, does not need to find Column data types on the fly. Others stating Select into is faster.
We conducted performance testing, and seems select into is slightly faster.

What are good principles in figuring which is faster and why? I would think Microsoft would optimize to make insert into , just as fast, for careful programming.

Article states following.

SQL Server Performance of SELECT INTO vs INSERT INTO for temporary tables

  The INSERT...INTO command will reuse data pages which are created in
  cache for insert/update/delete operations. It will also truncate the
  table when it is dropped. The SELECT...INTO command will create new
  pages for table creation similar to regular tables and will physically
  remove them when the temporary table is dropped.

Question is, why wouldn't Microsoft optimize to make insert into as fast as select into?

We have over 500 stored procedures to write for data warehouse, and require good guidelines for temp usage.

This article does not really focus on performance and reasons:

INSERT INTO vs SELECT INTO

Person in article mentioned good point:

  that's mostly because SQL Server knows that there is no contention for
  the destination table. The performance for insert into #temp
  with(tablock) select * from .. is roughly the same as the performance
  for select * into #temp from

What are good principles in figuring which is faster and why? I would
  think Microsoft would optimize to make insert into , just as fast, for
  careful programming.

The principles that I try to follow when analyzing something like this question are:

Avoid making unnecessary assumptions.
Read the official documentation.
Test the workload. The amount of testing depends on how fast I need the code to be.

I'm aware of two pieces of documentation that address your question. The first one is a blog post saying that SELECT INTO for temp tables has different behavior for eager writes as of SQL Server 2014. That is by design. So I don't think that it's correct to say that the difference is minimal for small tables. If anything, the optimization described in the blog post seems designed for smaller tables:

  The change in SQL Server 2014 is to relax the need to flush these
  pages, as quickly, to the TEMPDB data files.  When doing a select into
  … #tmp … or create index WITH SORT IN TEMPDB the SQL Server now
  recognizes this may be a short lived operation.   The pages associated
  with such an operation may be created, loaded, queried and released in
  a very small window of time.

  For example:  You could have a stored procedure that runs in 8ms.  In
  that stored procedure you select into … #tmp … then use the #tmp and
  drop it as the stored procedure completes.

  Prior to the SQL Server 2014 change the select into may have written
  all the pages accumulated to disk.  The SQL Server 2014, eager write
  behavior, no longer forces these pages to disk as quickly as previous
  versions.   This behavior allows the pages to be stored in RAM (buffer
  pool), queried and the table dropped (removed from buffer pool and
  returned to free list) without ever going to disk as long memory is
  available.   By avoiding the physical I/O when possible the
  performance of the TEMPDB, bulk operation is significantly increased
  and it reduces the impact on the I/O path resources as well.

The second piece of documentation explains that parallel insert into temp tables with INSERT INTO ... SELECT is available without a TABLOCK hint in SQL Server 2016 but requires a TABLOCK hint in SP1 and in future versions.

  The issue is first fixed in SQL Server 2016 Service Pack 1 . After you
  apply SQL Server 2016 SP1, Parallel INSERTs in INSERT..SELECT to local
  temporary tables is disabled by default which reduces contention on
  PFS page and improves the overall performance for concurrent workload.
  If parallel INSERTs to local temporary tables is desired, users should
  use TABLOCK hint while inserting into local temporary table.

Going back to your original statement, you can't logically deduce which of the two will be faster. What is faster depends on how Microsoft designed the software and the characteristics of your workload. Making guesses about the amount of time needed to create the column definitions just isn't helpful. Testing is helpful. If your testing suggests that SELECT INTO is faster than go with that. For what it's worth, I also work on data warehouse loading with a close eye on performance and I haven't seen the difference between the two approaches be anything that's worth worrying about.


Changing primary key column type from varchar to integer A while ago we migrated an old database to SQL, surprisingly we found out that some primary key values contain text instead of an integer. This is causing the output column type to be of varchar. We would like to convert this to an integer, because we want to use auto increment.

Our database consists of quite a few tables and many more rows, so we can't do this by hand. There are also foreign keys, which we have to update those as well.

We used SSIS to import the data from the old database, and already used some data conversion blocks to fix some typing problems, however I couldn't find any blocks/functionality to solve this problem. I also considered writing a t-sql script, but because of all the foreign keys, I hope there is a better way to do this.

So, what's the best way to go about this?

Hunker down and write the script. What you're describing isn't difficult just a little time consuming.

I'd normally recommend to install SQL Server Data Tools; create a new database project by pointing it at the existing database and doing an import (this populates all the tables, views, procedures, indexes, etc). That will then likely build with little or no changes, and you can then make changes in the designers and see what the change script that it outputs is like.

In this case it's not a complete solution because SSDT will usually abort when it finds data in the tables when you're making large changes. But it might give you something to compare against insofar as dropping and recreating all the dependencies goes.


re-enabling a table for mysql replication We were able to setup mysql master-slave replication with the following version on both master/slave:

mysqld  Ver 5.5.28-29.1-log for Linux on x86_64 (Percona Server (GPL), Release 29.1)

One day, we noticed that replication has stopped, we tried skipping over the entries that caused the replication errors. The errors persisted so we decided to skip replication for the 4 problematic tables. The slave has now caught up with the master except for the 4 tables.

What is the best way to enable replication again for the 4 tables?

This is what I have in mind but I don't know if it will work:

1) Modify slave config to enable replication again for the 4 tables

2) stop slave replication

3) for each of the 4 tables, use pt-table-sync --execute --verbose --print --sync-to-master h=localhost,D=mydb,t=mytable

4) restart slave database to reload replication configuration

5) start slave replication

I don't use pt-table-sync because I don't agree with the approach it uses:

When synchronizing a server that is a replication slave with the –replicate or –sync-to-master methods, it always makes the changes on the replication master, never the replication slave directly.  This is in general the only safe way to bring a replica back in sync with its master; changes to the replica are usually the source of the problems in the first place. However, the changes it makes on the master should be no-op changes that set the data to their current values, and actually affect only the replica. Please read the detailed documentation that follows to learn more about this.
— http://www.percona.com/doc/percona-toolkit/2.1/pt-table-sync.html

My philosophy is "no, that's okay, you guys can go ahead and stay away from the master."
While it's true that a common cause of replication errors are related to things being changed directly on the slave, it's also true that if two servers have an identical set of data at an identical set of binlog coordinates, that's a valid place to begin replication.  (I would also suggest that the other common cause of replication errors is people trying to replicate only some of their tables and not all, which is almost never a good idea, unless you really know what you're doing, in which case, you'll know better than to try it.)
If you do use it, and I'm sure a lot of people do, then I don't think you actually want to stop the slave.  I think you need to keep the slave running to it can make those "no-op changes to the master" and watch them replicate over to fix the inconsistent tables.  I don't know what it does with errors, perhaps it skips over them.
Here is one of the alternative approaches that I use.
You need three console windows for this.  Do not disconnect your session from the master or you will lose the global lock.  You need a console connection to the master and one to the slave, and you need to have your commands to do mysqldump set up and ready to go in a third window.
First, check the master's binlog coordinates and the slave's binlog coordinates to make sure the slave is not lagging.
master-mysql> SHOW MASTER STATUS;

+-----------------------+-----------+--------------+------------------+
| File                  | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------------+-----------+--------------+------------------+
| pri-master-bin.004421 | 142052212 |              |                  |
+-----------------------+-----------+--------------+------------------+

slave-mysql> SHOW SLAVE STATUS\G

    Relay_Master_Log_File: pri-master-bin.004421
      Exec_Master_Log_Pos: 142052212
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
    Seconds_Behind_Master: 0

There are more values included with SHOW SLAVE STATUS but these are the important ones.  The Exec_Master_Log_Pos needs to be close to or identical to the master, and the log file the same.
Next, obtain a brief read lock on the master.  This locks all of the tables for write allows the master to settle down long enough that we know the slave and master are at the same place in the binlog at this point in time.
master-mysql> flush tables with read lock;
Query OK, 0 rows affected (24.26 sec)

master-mysql>

Note that this sometimes takes a few seconds before your prompt returns.
Do not disconnect this session.  You need this session active because it is holding the lock on the master.  You can use it for the following, but don't close it.
Now, repeat the SHOW MASTER STATUS; on the master and SHOW SLAVE STATUS\G on the slave.  You should find that they have settled down to the same set of binlog coordinates.  Wait until they do.  If they don't settle down quickly, you might want to drop the read lock on the master to figure out why not.
Next:
slave-mysql> STOP SLAVE SQL_THREAD;

This will stop the slave from executing any more events from the master, which is still locked.
In the third window, start a backup of the tables from the master.
shell> mysqldump --verbose --single-transaction database_name table1 table2 table3 table4 > dump.sql

-- Connecting to *hostname*...
-- Starting transaction...
-- Retrieving table structure for table...

As soon as you see "Retrieving table structure..." you can release the global read lock on your master, so your application can start writing to it again.
mysql-master> unlock tables;
Query OK, 0 rows affected (0.01 sec)

If your tables are InnoDB, the --single-transaction option should get a consistent backup from the master at a point in time during the few seconds that you had the global read lock on the master.  Since you also stopped the slave at that point, the slave should be positioned at precisely the appropriate point in time for applying that backup.
When the backup of those tables is complete, apply it to the slave:
shell> mysql --host=slave.host.name database_name < dump.sql

At this point, you have restored the tables onto the slave in a form that should be identical to the way they appeared on the master at the time when the slave was stopped, and replication should resume when you remove the configuration not to replicate those tables, and then:
slave-mysql> START SLAVE SQL_THREAD;

Be sure your backups are solid before you begin.


Caching results of query for part of table that almost never change In our database we have a table with a very large number of rows. Let's call it Table1. The greatest difficulty for us is a COUNT query to this table with some conditions (see example below):

Query1

SELECT
    count (*)
FROM
    Table1
WHERE
    Table1.field1 = 'something'
    AND Table1.field2 = 'something'
    AND Table1.field3 = 'something'
    ..

In our application this query can occure with any number and any combination of conditions so we cannot use any index to somehow speed up this query so a simple sequence scan is being used (actually parallel sequence scan).

The feature of Table1 is as follows: new lines in it change very often and old ones almost never change (lets assume that the lines that often change is lines that created 'today' and the lines that almost never change is lines that created 'not today').

Because of this feature, the following idea emerged: if in our application occurs the need of execution of a Query1 (see above), we can actually not execute Query1, but instead we can execute 2 queries (see below): one for new rows (Query2) and one for old rows (Query3) and further we can summarized the results of these two queries to get total COUNT result. And to speed up overall performance we can perform Caching the results of query for old rows (Query3). Yes, we should clear this cache when some UPDATE occure on old rows, but it happens very rare.

Query2

SELECT
    count (*)
FROM
    Table1
WHERE
    Table1.field1 = 'something'
    AND Table1.field2 = 'something'
    AND Table1.field3 = 'something'
    ..
    AND Table1.creation_date = 'today'

Query3

SELECT
    count (*)
FROM
    Table1
WHERE
    Table1.field1 = 'something'
    AND Table1.field2 = 'something'
    AND Table1.field3 = 'something'
    ..
    AND Table1.creation_date != 'today'

So the question is:

Is this a good idea and it can speed up performance or it is a bad idea and it is better not even consider this approach?

It's best not to use a count(*).  Instead, use a count(PrimaryKey), where the Primarykey is whatever your PrimaryKey happens to be.

For example, if the PrimaryKey is EmplID, then use count(EmplID).  If you don't have a PrimaryKey, create one.  Without a PrimaryKey, the query will always use a table scan (where it looks at every single row to find the data) rather than using an indexed seek. Think of it like a filing cabinet. If all your records are organized alphabetically, you always know where to find customers whose names start with "F". If it's not organized, you have to look through every single piece of paper to find the right customer records.

For each of your WHERE conditions, make sure each field is indexed.  Field1 should be indexed, Field2 should be indexed.  It doesn't matter if the value changes.  As long as the field is indexed and you REBUILD or RECREATE the index often, it should work just fine.

You shouldn't need Query2 and Query3.  If you decide to use Query2, make sure the Creation_Date is indexed as well.


MySQL status variable Handler_read_rnd_next is growing a lot In MYSQL status, Handler_read_rnd_next value is very high.

I am aware that, this value will be incremented when a query is executed which is not having proper indexes.

But, even when we execute show status like 'Handler_read_rnd_next', this value is getting incremented by 2.

Based on this status flag, we are monitoring some stats.

So every time, this stats are showing critical.

Can we exclude these 'show' execution counts from 'Handler_read_rnd_next' count.

One more example for this,

There is a table with 10 rows, table is indexed on column 'data', and if we execute the following query:

select data from test where data = 'vwx' -> returns one row

and if we check value of 'Handler_read_rnd_next', it got incremented by 7.

Following is result of explain command for the above query:

explain select data from test where data = 'vwx';

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra

1, 'SIMPLE', 'test', 'ref', 'data', 'data', '35', 'const', 1, 'Using where; Using index'

Is there any way to restrict this value, or can i know why this value is getting incremented very fast.

First of all, let us look at the definition of Handler_read_rnd_next.

According to the MySQL Documentation on Handler_read_rnd_next:

  The number of requests to read the next row in the data file. This
  value is high if you are doing a lot of table scans. Generally this
  suggests that your tables are not properly indexed or that your
  queries are not written to take advantage of the indexes you have.

Now, look at your query:

select data from test where data = 'vwx';

You said that the table has 10 rows. As a rule of thumb, the MySQL Query Optimizer will dismiss the use of an index if the number of rows that needs to be examined is greater that 5% of the total number of rows.

Let us do the math. 5% of 10 rows is 0.5 rows. Even if the number of rows need to locate your data is 1, that is greater than 0.5. Based of this lower number of rows and that index rule I just mentioned, MySQL Query Optimizer will always do a table scan.

Since the column data is itself indexed, instead of a table scan, mysql was performed an index scan.

If you know for a certainty that the test table will never grow, you can remove all indexes and let table scans happen. Handler status variables should stop incrementing.


Can I update old data and insert new data if not exists currently in a single query I was trying to wrap my head around how to go about doing an update to a table if an updated list of the same table type does not have one or more of its original rows. The list of services comes from an API and is usually the same set of services, however every now and again the list may be updated and services become inactive and no longer show up in the results from the API call so these services should be updated in our database to reflect active as FALSE. Likewise is a new service comes onboard it should be added to the current list of available services. I had in mind to just drop all records and add the new ones fetched from the API however considering I have used the Ids for the existing services in other tables and I would still need to reference them I threw that idea out the window and now I am in a bit of a bind.

Services - (Currently in DB)
Name | Id | Active
Test1   3     true
Test2   4     true
Test3   5     true

I wanted to have a query or trigger of some sort to run when trying to insert duplicate data to table Services where constrained by the following:

If an existing 'Name' is found skip the insert and move on to the next item in the array
If a new 'Name' comes up not found in the DB add it as a new row. Example [Test4  5   true]
If the newly fetched list from an API does not have one of the existing 'Name's, that is Test1,Test2 or Test3 update that existing row to set the Active column to false. So if the new list does not have Test3 the existing Test3 row would be updated to show Active as false.

I  think you will want something like the following - see the fiddle here. It's based on Common Table Expressions (CTEs) and that fact that with PostgreSQL, you can perform not only SELECTs, but also INSERTs, UPDATEs and DELETEs (see here also).

First, your service table:

CREATE TABLE service
(
  name VARCHAR (10) NOT NULL PRIMARY KEY,
  id INTEGER NOT NULL,
  active BOOLEAN NOT NULL
);

populate it with your data:

INSERT INTO service VALUES
('Test1', 3, true), ('Test2', 4, true), ('Test3', 5, true);

Now, you receive your data from your API - I'll assume that you put that into some sort of temporary table - the keyword TEMPORARY just means that the table will be dropped at the end of your session - I've tested with both TEMPORARY and normal tables with the fiddle and the results are the same, so we'll go with TEMPORARY:

CREATE TEMPORARY TABLE api
(
  name VARCHAR (10) NOT NULL PRIMARY KEY,
  id INTEGER NOT NULL
  -- active BOOLEAN NOT NULL
);

I've assumed that your API doesn't know the status of the service, so it only has two fields - the name and the name and the id.

Populate it:

INSERT INTO api VALUES
('Test2', 4), ('Test3', 5), ('Test4', 6);

Notice that service Test1 is missing and that service Test4 is an additional service.

So now, because of PostgreSQL's ability to perform INSERTs and UPDATEs within CTEs, we can now do the following:

WITH cte1 (nom) AS
(
  INSERT INTO service  (name, id, active)
    SELECT a.name, a.id, true FROM api a
    WHERE a.name NOT IN (SELECT name FROM service) RETURNING name
),
cte2 (nom2) AS
(
  UPDATE service s SET active = false
    WHERE s.name NOT IN (SELECT name FROM api) RETURNING s.name
)
SELECT * FROM service;

The first CTE

INSERT INTO service  (name, id, active)
  SELECT a.name, a.id, true FROM api a
  WHERE a.name NOT IN (SELECT name FROM service) RETURNING name

inserts new services from the api table into the service table and the second:

UPDATE service s SET active = false
  WHERE s.name NOT IN (SELECT name FROM api) RETURNING s.name

sets the service status = false where a service in the service table isn't present in the api table.

Now, the result of the SELECT * FROM service at the end of this query is:

name    id  active
Test1   3   t
Test2   4   t
Test3   5   t

So, you might think "Drat, it hasn't worked!" - but in fact, it has worked!

In the next section, you rerun

SELECT * FROM service
ORDER BY name;

and you get:

name    id  active
Test1   3   f
Test2   4   t
Test3   5   t
Test4   6   t

So, we can see that service Test1's active field has been set to false and that service Test4 has been added. The reason it doesn't show up in the SELECT immediately after the CTEs has to do with the scope of the transaction - the first SELECT shows the service table as it was at the beginning of the transaction - the second shows the state of the table after the transaction.


Database Change Management What are ways in which teams handle database change management? I have a team of 8 developers making database changes concurrently.

Each developer is responsible for updating their changes in what we consider a "poor man's database change log" which is essentially a shared file referencing a script. Sometimes the scripts are database alters. Other times the script re-creates a view due to a change (which may have also been changed by another developer later on).

It always happens that scripts are placed out-of-order, listed in duplicate, etc.

My gut tells me we are doing this wrong. How can we do this better?

If it matters, we are using Postgres + git.

to me the most important thing in this context is to use transactions. PostgreSQL supports transactional DDLs and people should make heavy use of it.

the rule of thumb is: COMMIT stuff to the real system as late in the process as possible. this avoids the need to rollback stuff and to produce conflicts of all kinds. so transactions are really the key to making things work in a reasonable way. deployment should always happen in a transactional way as well.

one thing is also to use includes:

BEGIN;
\i module_1.sql
\i module_2.sql ...

it allows people to work on various files concurrently. it makes using git and so on a lot easier.

also, consider looking at CREATE EXTENSION. it allows a pretty nice way to handle versions and all that. i strongly encourage to use that one as well. git is also a good thing to have in general.


How to check the last time any table in database modified in ms sql server I have around 5 database and I am taking daily backup of all 5 database. I know that out of that 5 database 1 of the database is not in use and we are still taking the backup of that database.

My question is how we can check if the database is in used, like how can I verify when was the last time any Insert,update or delete happening in the database.

I think which I think of I can check the database size for 1 week and If the size of the database is same that means it is not in used. But I want to check when was the last time any DML command run.

This should give you the details about when was the last read/write performed on a specific database.
USE server_name;
GO

SET ANSI_WARNINGS OFF;
SET NOCOUNT ON;
GO

WITH agg AS
   (
       SELECT
       last_user_seek,
       last_user_scan,
       last_user_lookup,
       last_user_update
FROM
    sys.dm_db_index_usage_stats
WHERE
    database_id = DB_ID()
        )
           SELECT
             last_read = MAX(last_read),
             last_write = MAX(last_write)
          FROM
      (
SELECT last_user_seek, NULL FROM agg
UNION ALL
SELECT last_user_scan, NULL FROM agg
UNION ALL
SELECT last_user_lookup, NULL FROM agg
UNION ALL
SELECT NULL, last_user_update FROM agg
      ) AS x (last_read, last_write);

Be aware sys.dm_db_index_usage_stats does not return information about memory-optimized indexes.  For information about memory-optimized index use, see sys.dm_db_xtp_index_stats.


During rollback, is lock escalation reversed Generally, SQL Server will take locks during updates. It also supports Lock Escalation:

  Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead while increasing the probability of concurrency contention.

During a transaction ROLLBACK, does SQL Server de-escalate the locks, putting back the fine-grained locks?

SQL Server does not de-escalate the lock.

I investigated using a "Numbers" table with 100,000 rows. Empirically, updating 5,000 rows produced a corresponding number of RID locks in sys.dm_tran_locks. Updating a further 10,000 rows caused escalation to a single table lock.  This was consistently reproducible. To minimise the objects involved the table was a heap without indexes.

I used Extended Event tracing to capture lock_acquired and lock_released events. For ease of analysis I used separate traces during the UPDATE and ROLLBACK phases.

Two sessions (SSMS windows) were used - one for the DML statements and one for the trace DDL. I could not use as single session as I wanted to stop and start traces while the transaction is open and this is not permitted. The isolation level throughout is READCOMITTED.

The result of select @@VERSION is

  Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (Intel X86)   Mar 26 2015 21:49:16   Copyright (c) Microsoft Corporation  Enterprise Edition on Windows NT 6.1  (Build 7601: Service Pack 1)

The steps taken were

Create and start the Acquire trace.

CREATE EVENT SESSION DuringUpdate
ON SERVER
ADD EVENT sqlserver.lock_acquired,
ADD EVENT sqlserver.lock_released
ADD TARGET package0.asynchronous_file_target
    (SET filename = 'c:\temp\LockRollback\DuringUpdate.xel',
     metadatafile = 'c:\temp\LockRollback\DuringUpdate.xem')
WITH(EVENT_RETENTION_MODE = NO_EVENT_LOSS,
     MAX_DISPATCH_LATENCY = 1 SECONDS
    );

ALTER EVENT SESSION DuringUpdate
ON SERVER
STATE = start;

Begin a transaction and update 5000 rows.

begin transaction;

update dbo.Numbers
set Number = Number
where Number between 1 and 5000;

Update a further 10000 rows.

update dbo.Numbers
set Number = Number
where Number between 5001 and 15000;

Stop the acquire trace.

ALTER EVENT SESSION DuringUpdate
ON SERVER
STATE = stop;

Verify there are locks by querying sys.dm_tran_locks (2 - DB, table).
Create and start the Release trace.

CREATE EVENT SESSION DuringRollback
ON SERVER
ADD EVENT sqlserver.lock_acquired,
ADD EVENT sqlserver.lock_released
ADD TARGET package0.asynchronous_file_target
    (SET filename = 'c:\temp\LockRollback\DuringRollback.xel',
         metadatafile = 'c:\temp\LockRollback\DuringRollback.xem')
WITH(EVENT_RETENTION_MODE = NO_EVENT_LOSS,
     MAX_DISPATCH_LATENCY = 1 SECONDS
    );

ALTER EVENT SESSION DuringRollback
ON SERVER
STATE = start;

Roll back the transaction.

rollback;

Stop the Release trace.

ALTER EVENT SESSION DuringRollback
ON SERVER
STATE = stop;

Tidy up

DROP EVENT SESSION DuringUpdate
ON SERVER;

DROP EVENT SESSION DuringRollback
ON SERVER;

If lock de-escalation occurred I would expect complete symmetry in the two trace files - every lock acquired during UPDATE, and the escalation, would have a corresponding release during ROLLBACK. What I observed:

SELECT
    COUNT(*) as DuringUpdate
FROM sys.fn_xe_file_target_read_file(
    'c:\temp\LockRollback\DuringUpdate*.xel',
    'c:\temp\LockRollback\DuringUpdate*.xem', null, null);

SELECT
    COUNT(*) as DuringRollback
FROM sys.fn_xe_file_target_read_file(
    'c:\temp\LockRollback\DuringRollback*.xel',
    'c:\temp\LockRollback\DuringRollback*.xem', null, null);

was 383,889 rows and 166 rows respectively. Examining the event XML for the rollback file there is only one event relating to RIDs, PAGEs or OBJECTS - the release of an X lock on the table. I conclude from this that locks were not de-escalated during rollback.

This echos the documentation for rolling back to a named savepoint:

  A ROLLBACK TRANSACTION statement specifying a savepoint_name releases any locks that are acquired beyond the savepoint, with the exception of escalations and conversions. These locks are not released, and they are not converted back to their previous lock mode.


Forced serialization on inline table valued function Apologies if this is a stupid question. We are attempting to convert our multi-statement table valued functions (MSTVFs) to inline table valued functions (ITVFs), to avoid the forced serialization of queries.

I have the following ITVF function (watered down), but the Execution Plan still says it has a Degree of Parallelism of 1. The actual function in question has three basic SELECT statements separated by UNION ALLs.

Have I missed something? How can I avoid forced serialization here?

CREATE FUNCTION dbo.Test (@i int)
RETURNS TABLE
AS
RETURN
SELECT @i as [i];
GO

SELECT * FROM dbo.Test (2);
GO

https://www.brentozar.com/pastetheplan/?id=Hyn8o50D7

The instance has he following settings:

Cost Threshold for Parallelism = 5
Max Degree of Parallelism = 0

Simply, your query doesn't have a high enough estimated cost.

SQL Server estimates how much effort a query will take, and reveals this unitless measure as estimated cost. If it's below your Cost Threshold for Parallelism, then SQL Server won't bother with a parallel plan. Your plan has an estimated cost of .129, which, since it is less than 5, means no parallelism.

Also in your plan XML is the detail that this query only took 1 millisecond. You have no need to go parallel. There's not enough work to justify the overhead of distributing effort across multiple threads.

As an aside for an earlier version of this question, even queries that would be TRIVIAL will go parallel if their estimated cost is high enough, and all the other conditions for parallelism are met.

A way to prove that something is preventing parallelism is  NonParallelPlanReason="CouldNotGenerateValidParallelPlan" in the plan XML.

Another way to test (I'm assuming you're on SQL Server 2014, because 2016 has an actual hint to use) is to add OPTION(QUERYTRACEON 8649) to your query. Please just use that for testing though, because adding undocumented trace flags to production code is really stupid not a good thing.


how to save an image column in MSSQL to file system? Imagine I have a table like this in MSSQL

-------------------------------------
file_name(varchar) | file_doc(image) |
--------------------------------------
A                  | image_binary1   |
--------------------------------------
B                  | image_binary2   |
--------------------------------------

I want to save contents of this table to file system something like the following

 - c:\somewhere\A.pdf
 - c:\somewhere\B.pdf

how to achive that in MSSQL?

The easiest way is to use SSIS: the Export Column component does
what you are looking for. Here is an example: Export images from a
SQL Server Table to a Folder with SSIS

This is not only the easiest way, it requires minimal permissions:
only SELECT permission on the table and access to file folder.
Also you can write a powershell script: Export SQL Server BLOB
Data with PowerShell

The user should be able not only access a table (SELECT
permission) but the rights to execute powershell scripts.
It can be done using bcp but only for 1 file at a time, so for
multiple files you need to write a cursor and use xp_cmdshell. Use of
xp_cmdshell is not a best practice, it should be enabled on your
server and you should have elevated permissions to be able to use
xp_cmdshell.
In case of bcp you should use format file, otherwise your files will contain additional 8 bytes and have a wrong format.


Able to connect with connection manager but SSIS package does not authenticate I have an SSIS package that connects MSSQL2008 server to MSSQL2000 and pulls data off the 2000 server and transfers it to the 2008 server. The 2000 server was recently virtualized and since then the SSIS package will not authenticate.
I can see both servers with OSQL -L/sqlcmd -L. All services, agents and browsers are running.
The SQL user that runs the SSIS package can connect via Test Connection button in the Package Explorer>Connection Manager, but when I attempt to execute the package the error output indicates that the login failed, the password does not match. Error 18456 Sev 14 State 8. I am sure the password is correct as I just created the new SQL user to test. Am I missing something that the SSIS package may authenticate somewhere else?
Should the connection string include the password, or does that get pulled form elsewhere in the SSIS package?
I am not a DB admin/SQL guy so please excuse my green-ness if I am overlooking something simple here, which I hope I am. Thanks for any input

We where able to solve the Connection Problems with the Package Parameter Tab. Here you are able to "Save" for each connection the Username and Password (In clear Text ^^?!). And then the Package can connect.


Considerations for Adding Indexes to a SQL Server Database I'm a .NET developer. So, I know enough about databases to be dangerous, but do not have the expertise of a proper DBA.

We have a slow running stored proc and one of the devs here ran a query optimization tool which suggested the addition of some indexes.

It suggested 9 Non-clustered indexes and 78 statistics.

I know this is a pretty huge area and I know enough to know that I don't know much. So, given that I don't have access to a proper DBA to review the indexes and statistics, would it be a reckless move to just proceed with those suggested changes?

Rewriting the Stored Proc is another option we are not too keen on, as it is returning the correct data right now and we are reticent to mess with that.

But if there is a big gotcha that I am unaware of, I'd really like to find out about it. So, advice would be greatly appreciated.

I was going to post the most expensive query plan of the batch to Pastetheplan.com . Alas, I was unable to get permission from my manager. I can't see how a bit of random SQL will have any security implications etc., but the business is more paranoid than me. I realise this would have helped a lot and am disappointed I cannot post to it. The 1 index I added has helped a lot and the advice here has been much appreciated. Choosing an answer is tough and I'm still working on that.

If you can review the suggested indexes and maybe try adding a few here and there. However, be cautious of over-indexing. Over-indexing can cause SQL Server to get confused on which index to use and that can lead to "DUI’s", that is, it can hamper the performance of Deletes, Updates or Inserts (DUI’s). Also look up the concept of a covering index. A covering index is a single index that "covers" the query and that index can also be reused by other queries, effectively reducing the number of indexes needed.

Watch this excellent free 30 minute video by Kendra Little that covers Indexing in SQL Server:

https://www.youtube.com/watch?v=wAhr91FBlx4