How to structure table in MySQL for group chat application? I'm making a messaging application, and I need a way to organize and store messages. I was thinking something like a table that had all the group chat IDs and who was in which group chat, but MySQL doesn't have a list functionality. For the actual messages, I was thinking one large table with the group chat IDs and the message ID. To be honest, I really don't have a good way to approach this. I was even considering making a table for every group chat. How should I structure a database for a group messaging application?

With relational databases, or mostly-relational databases as is the case with MySQL, you should think mostly in terms of entities and relationships.

You might want one table for each of the following entities:

messages: the actual messages and a reference to the user (author)
users: details such as user name, email address, ...
groups: maybe just the name of the group
group memberships: one row per user per group. This allows for a many-to-many relationship, so that many users can be in many groups at the same time.

So then you could have tables maybe like such:

CREATE TABLE users (
  user_id bigint unsigned auto_increment PRIMARY KEY,
  username varchar(60) NOT NULL UNIQUE
  -- other user columns here
);

CREATE TABLE groups (
  group_id bigint unsigned auto_increment PRIMARY KEY,
  groupname varchar(60) NOT NULL UNIQUE
  -- other group columns here
);

CREATE TABLE group_users (
  group_id bigint unsigned,
  user_id bigint unsigned,
  PRIMARY KEY (group_id, user_id),
  CONSTRAINT group_users_fk1 FOREIGN KEY (group_id) REFERENCES groups (group_id),
  CONSTRAINT group_users_fk2 FOREIGN KEY (user_id) REFERENCES users (user_id)
);

CREATE TABLE messages (
  message_id bigint unsigned auto_increment PRIMARY KEY,
  user_id bigint unsigned,
  message TEXT,
  -- other columns here, e.g. timestamp
  CONSTRAINT messages_fk1 FOREIGN KEY (user_id) REFERENCES users (user_id)
);

Edit

The lines starting with CONSTRAINT specify foreign keys which enforce integrity of data between the tables. E.g. they will prevent rows from being deleted in the users table that are referenced in the messages table.

To retrieve a list of users in a particular group, use a query like this:

SELECT u.user_id, u.username
FROM users u
  INNER JOIN group_users gu USING (user_id)
WHERE gu.group_id = 3;

The INNER JOIN means the query will only retrieve the users who have at least one row in the group_users table, and the WHERE ensures that we only retrieve results for group_id 3.


Changing field length when foreign keys reference primary key field in table I have a situation where a number of foreign keys are referencing a column in Table "A". I would like to change the length of the column in table "A". What is the best way to accomplish this in SQL Server 2008 R2? I am really hoping I don't have to drop all 30 foreign key constraints and re-create them again...

Script out Drop and Create All 30 JF.
Drop all 30 KF
Alter Columns
Create all 30 KF


TSQL Remove Characters from Text Value I have been using the REPLACE function to remove characters from a text value in a table. It has just grown over time and now the query is quite messy.

What can I do to make this more efficient? Is there a Regex example that I could implement?

Query:

SELECT
        REPLACE(
           REPLACE(
              REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(
                            REPLACE(
                                REPLACE(
                                    REPLACE(dbo.[RemoveNumericCharacters](Filename), '.xlsx', '')
                                        , '.xls', '')
                                        ,'csv', '')
                                        ,'---_pdf', '')
                                        ,'.', '')
                                        ,'-Jun-', '')
                                        ,'-Jul-', '')
                                        ,'Jul', '')
                                        ,'__', '_')
           as 'ConvertedFilename'
FROM @Files

Function:

ALTER Function [dbo].[RemoveNumericCharacters](@Temp nvarchar(1000))
Returns nvarchar(1000)
AS
Begin

    Declare @NumRange as varchar(50) = '%[0-9]%'
    While PatIndex(@NumRange, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@NumRange, @Temp), 1, '')

    Return @Temp
End

I think you'd better create a filter table in which you put your replacement string pairs and then you can create a function to remove source string with your filter table.

Here is the example

use tempdb
-- filter table
create table dbo.Filter (id int identity, src varchar(100), tgt varchar(100));
-- populate a few filtering pairs
insert into dbo.Filter (src, tgt)
values ('.xlsx', '')
     , ('csv', '')
     , ('__', '_')
     , ('-Jun-', '');

-- your source table
create table #s (mycol varchar(1000))
-- populate source column
insert into #s (mycol)
values ('this .xlsx file is not __ created in -Jun- but in June')
     , ('this csv file should be removed  -Jun-')
go

-- filtering function
create function dbo.funcReplace (@s varchar(1000))
returns varchar(1000)
as
begin
    declare @src varchar(100), @tgt varchar(100);
    declare @curS cursor;
    set @curS = cursor for select src, tgt from dbo.Filter
    open @curS
    fetch next from @curS into @src, @tgt;
    while @@fetch_status = 0
    begin
        set @s=replace(@s, @src, @tgt);
            fetch next from @curS into @src, @tgt;
    end
    return @s;
end
go

-- before replacement
select bfr_replacement = mycol from #s

-- after replacement
select  after_replacment=dbo.funcReplace(mycol)
from #s

In future, if you have more replacement needed, just add them to the table dbo.Filter and you do not need to change anything elsewhere.


Is it faster to split a large table into 12 rolling monthly tables & use UNION them for reports or keep large table & delete rows older than 1 year? My co-worker wants to split a large 158M row stats table into stats_jan, stats_feb, ... and use UNION to select from them for reports. Is that standard practice and is it faster than to just use the large table in place and delete rows older than one year? The table is many small rows.

mysql> describe stats;
+----------------+---------------------+------+-----+---------+----------------+
| Field          | Type                | Null | Key | Default | Extra          |
+----------------+---------------------+------+-----+---------+----------------+
| id             | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| badge_id       | bigint(20) unsigned | NO   | MUL | NULL    |                |
| hit_date       | datetime            | YES  | MUL | NULL    |                |
| hit_type       | tinyint(4)          | YES  |     | NULL    |                |
| source_id      | bigint(20) unsigned | YES  | MUL | NULL    |                |
| fingerprint_id | bigint(20) unsigned | YES  |     | NULL    |                |
+----------------+---------------------+------+-----+---------+----------------+

I did manually split the table up and copy the rows into the appropriate month tables and created a giant UNION query. The large UNION query took 14s versus 4.5m for the single table query. Why would many smaller tables take a significantly shorter time than one large table, when it's the same number of rows total?

create table stats_jan (...);
create table stats_feb (...);
...
create index stats_jan_hit_date_idx on stats_jan (hit_date);
...
insert into stats_jan select * from stats where hit_date >= '2019-01-01' and hit_date < '2019-02-01';
...
delete from stats where hit_date < '2018-09-01';
...

The monthly tables have from 1.7M rows to 35M rows.

select host as `key`, count(*) as value from stats join sources on source_id = sources.id where hit_date >= '2019-08-21 19:43:19' and sources.host != 'NONE' group by source_id order by value desc limit 10;
4 min 30.39 sec

flush tables;
reset query cache;

select host as `key`, count(*) as value from stats_jan join sources on source_id = sources.id where hit_date >= '2019-08-21 19:43:19' and sources.host != 'NONE' group by source_id
UNION
...
order by value desc limit 10;
14.16 sec

Do not split the table. Use Range Partitionig instead. Study MySQL 8.0 Reference Manual  /  Partitioning. Use MySQL 8.0 Reference Manual / ... / ALTER TABLE Partition Operations. Keep in mind that it is best to create partitions for future periods in advance (and do not forget to create LESS THAN MAXVALUE partition). Creating new partitions and moving existing data to them at the same time can be more expensive.

Do not delete data permanently. Move it into separate archive table. If you do not have enough disk space - make a backup of such an archive table, check its validity, and only if successful then delete the table. If necessary (it will - be sure!), you can recover and use this data.


Clear PostgreSQL logs in AWS Ubuntu Server I am trying to clear the logs of an postgresql under /var/log/postgresql/ but I am getting error message as below:
 /var/log/postgresql$ sudo > postgresql-11-main.log
 -bash: postgresql-11-main.log: Permission denied

Below is folder files structure:
 /var/log/postgresql$ ls -la
 total 1097296
 drwxrwxr-t  2 root     postgres       4096 Apr 25 06:56 .
 drwxrwxr-x 12 root     syslog         4096 Apr 28 06:35 ..
 -rw-r-----  1 postgres adm      1107919237 Apr 29 06:33 postgresql-11-main.log
 -rw-r-----  1 postgres adm        14480619 Apr 25 06:56 postgresql-11-main.log.1
 -rw-r-----  1 postgres adm         1199751 Apr 18 06:49 postgresql-11-main.log.2.gz

Also, is it okay to clear the logs of an PostgreSQL? will it harm any existing database or work flow?

That is a bash problem. The redirection (>) is not part of the command executed as root, but as your current user.
Use this workaround:
sudo sh -c "> /var/log/postgresql/postgresql-11-main.log"


SELECT DISTINCT TWO columns not ranged How can I select distinct two columns not ranged correctly.
for example:
if I have this table t:

================
|| a   || b   ||
=======||=======
|| 1   || 2   ||
|| 1   || 3   ||
|| 2   || 3   ||
|| 3   || 1   ||
|| 2   || 1   ||
================

If I choose 1 in my query the result should be:

================
|| a   || b   ||
=======||=======
|| 1   || 2   ||
|| 1   || 3   ||
================

This table is a result of selecting the rows which has a=1 or b=1 and do not repeat a,b or the inverse.
Details:

The result of SELECT * FROM t WHERE a=1 OR b=1; will be:

================
|| a   || b   ||
=======||=======
|| 1   || 2   ||
|| 1   || 3   ||
|| 3   || 1   ||
|| 2   || 1   ||
================

I should eliminate the repeated rows normally and inversely.

|| 2   || 1   || will be eliminated because it's the inverse of || 1   || 2   ||

|| 3   || 1   || will be eliminated because it's the inverse of || 1   || 3   ||

The result was shown above.

A more efficient (and somewhat shorter) version of what Jon of All Trades mentioned in his answer could be something like this:

SELECT DISTINCT
CASE WHEN A < B THEN A ELSE B END AS value1,
CASE WHEN A < B THEN B ELSE A END AS value2
FROM T

Of course if you had to only check for tuples that contain 1, you would add something like WHERE A = 1 OR B = 1


Change referenced index for a Foreign Key I have something like this:

CREATE TABLE T1 (
    Id INT
    ...
    ,Constraint [PK_T1] PRIMARY KEY CLUSTERED [Id]
)

CREATE TABLE T2 (
    ....
    ,T1_Id INT NOT NULL
    ,CONSTRAINT [FK_T2_T1] FOREIGN KEY (T1_Id) REFERENCES T1(Id)
)

For performance (and deadlock) reasons I created a new index on T1

CREATE UNIQUE NONCLUSTERED INDEX IX_T1_Id ON T1 (Id)

But if I check to which Index references the FK, keeps referencing to the clustered index

select
    ix.index_id,
    ix.name as index_name,
    ix.type_desc as index_type_desc,
    fk.name as fk_name
from sys.indexes ix
    left join sys.foreign_keys fk on
        fk.referenced_object_id = ix.object_id
        and fk.key_index_id = ix.index_id
        and fk.parent_object_id = object_id('T2')
where ix.object_id = object_id('T1');

If I drop the constraint and create again it references the nonclustered index, but this lead to check all t2 FK again.

Is there a way to change this so the FK_T2_T1 uses IX_T1_Id instead of PK_T1 without dropping the FK and locking the table on FK checking?

Thanks!

Well, after continue searching I found this article

  Unlike a normal query, it won't pick up a new index due to statistics being updated, a new index being created, or even a server being rebooted. The only way I'm aware of to have a FK bind to a different index is to drop and recreate the FK, letting it automatically select the index with no options to control it manually.

Whereupon, unless someone can say otherwise, I will have to look for a time window to perform this task.

Thanks


Filtered index statistics refresh threshold We have filtered indexes in our production environment. While doing some research about them, I came across this article "Filtered indexes and filtered stats might become seriously out-of-date"

It's a fairly simple filtered index based on a code value of 0

CREATE NONCLUSTERED INDEX
    [IX_InsuranceOffer_FIX_OfferCode0]
    ON [dbo].[InsuranceOffer]
(
    [OfferId] ASC
)
WHERE ([OfferStatus]=(0))
WITH (PAD_INDEX = OFF,   STATISTICS_NORECOMPUTE = OFF
, SORT_IN_TEMPDB = OFF,  DROP_EXISTING = OFF
, ONLINE = OFF,          ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

distribution looks like

code   codeCount   code_distribution
------ ----------- -----------------
6      26186769    93.7526
0      1743401     6.2416
5      1107        0.0040
7      495         0.0018

Our intention was to modify the existing index to include code 5. Based on this tipping point article, I believe both queries should continue to use the filtered index.

I have questions out to the system owners trying to understand the volatility of the codes.

Until then, I looked at sys.dm_db_index_physical_stats an attempt to understand whether our current index rebuild/reorganization strategy is sufficient for keeping up with the filtered index. I suspect it isn't but my internal-fu is weak.

index_level avg_fragmentation_in_percent            fragment_count       avg_fragment_size_in_pages page_count           avg_page_space_used_in_percent          record_count
----------- --------------------------------------- -------------------- -------------------------- -------------------- --------------------------------------- --------------------
0           0.6276                                  20                   143.4                      2868                 90.0984                                 1743401
1           42.8571                                 7                    1                          7                    86.0285                                 2868
2           0.0000                                  1                    1                          1                    1.4455                                  7

sys.stats for this index shows it was last updated '2012-01-06 22:03:11.147'

Is the above data sufficient information to base an index rebuild decision upon or would I need to have additional metrics involved?  Or, for filtered indexes do we even care about fragmentation and should just explicitly update statistics at X interval?

I claim the answer "it depends"

Only semi-related question was Minimum rowcount for filtered index?

You've got two questions in here:

  Is the above data sufficient information to base an index rebuild
  decision upon or would I need to have additional metrics involved?

One missing link is the size of the index.  If you're talking about an object with less than, say, 1000 pages, then index rebuilds aren't all that critical.

Another missing link would be the churn of the index.  Typically I see filtered indexes used when they're a very, very small subset of the entire table, and the subset changes fast.  Guessing by the name of your filtering field (OfferStatus = 0), it sounds like you're indexing just the rows where you haven't made an offer yet, and then you're going to immediately turn around and make an offer.  In situations like that, the data's changing so fast that index rebuilds usually don't make sense.

  Or, for filtered indexes do we even care about fragmentation and
  should just explicitly update statistics at X interval?

SQL Server updates stats on objects when ~20% of the data changes, but filtered indexes & stats are a special case.  They're also updated when 20% of the data changes - but it's 20% of the base table, not 20% of the filtered subset.  Because of that, you probably want to manually update stats on them periodically.  I love Ola Hallengren's maintenance scripts for this - the index maintenance stored proc has a parameter for updating statistics, and another parameter for choosing what level of sampling you want, and another parameter for choosing whether to update stats on all objects or only the ones with changed rows.  It's fantastic.


sql 2005: Timeout- Error 1222 Severity 16 State 18 A user complained that a SQL query has started timing out. I couldn't see the timeouts in sql logs but when I started a trace, I could see timeouts: "Error 1222 Severity 16 State 18"

It was from spid 17 which is a system process but in user database. After some research I found that "This error means that a lock was requested in msdb and timed out. Usually, that's going to mean it's a big transaction on a big temp table or a big sort, or something of that type"

Source: Link.

As per this link, I tried disabling ghost record removal process using:

DBCC TRACEON (661,-1)

But that didn't help either so I changed it back.

Any idea what I can do?

Thanks!

I'd start by doing three things:

Make sure of what the user actually means by "the query is timing out". Sometimes you get false positives from someone who doesn't understand that a connection timeout and a query timeout are different things.
If it's not a connection timeout, go ahead and analyze the execution plan on the query the user is running. Especially if by "user" you mean "non-DBA who has ad-hoc query access to the database". 10 to 1 that query has a Cartesian join in it. Or nested cursors on a large dataset. Or some other such tomfoolery.
Check the Activity Monitor for recent long-running queries and also analyze those. Usually the SPID that kicks off a timeout message is not the actual culprit. It times out because another process is consuming resources inefficiently, shutting out subsequent processes that need those resources.

Even though #3 is the most likely solution, go ahead and do #2 because I've found by experience that any query a user complains about is likely to have something in it that can be significantly tuned for better performance, even if the main issue was something else outside the query.

Plus, it's good customer service to give their complaint a reasonable amount of due consideration. Doctors check the vitals even of known hypochondriacs.  :)


UNF to 3NF is this relation workable? I've inherited a flat table of data consisting of around 2 million records.  Looking at the data its already in 1NF, i'd like to get this into 3NF.

The data is Australian so suburb can't determine state as suburb names exist in more than one state, plus one postcode often covers more than one suburb.  Meaning suburb, state, postcode should be a super key?

There are multiple companies at different sites, some have the same phone number (freephone 1300 or 1800) and others have distinct phone numbers, every record has a fax number and most have a phone number.   ContactName is mostly blank so phone and fax numbers relate to companies not contacts.

Selection of records by users is typically RLIKE on the field BusinessTypeDescription coupled with geographic criteria which of course means a full table scan on every query and therefore super slow results.

Is the proposed table structure below in 3NF and is it practical?

EXISTING TABLE
-----------------------
ID (PK)
CompanyName
Address
Suburb
State
Region
Postcode
Country
ContactName (this field is mostly empty)
Phone
Fax
Mobile
Email  (this field is mostly empty)
Website  (this field is mostly empty)
PremiseType
BusinessTypeDescription
ANZSICCode
ANZSICDescription
RecordSource

This is the proposed structure...

COMPANY
---------------
ID (PK)
CompanyName
Address
LocationID (FK)
PhoneID (FK)
FaxID (FK)
MobileID (FK)
ContactName
PremiseType
BusinessTypeID (FK)
ANZSICCode (FK)
Email
Website
RecordSource

LOCATION        COUNTRY         REGION
--------------  --------------  ------------
ID (PK)         ID (PK)         ID (PK)
Suburb          Country         Region
State
Postcode
RegionID (FK)
CountryID (FK)

PHONES
------------
ID
Phone

MOBILES
------------
ID
Mobile

FAXES
------------
ID
Fax

BUSINESSTYPE
----------------------
ID
BusinessTypeDescription

ANZSICS
----------------------
ANZSICCode
ANZSICDescription

You need to learn the difference between repetition and redundancy.  Sometimes a field value is repeated coincidentally.  This kind of repetition cannot be normalized out.

Normalization is about studying the functional dependencies between key and non-key elements.  It is not about putting everything that might occur twice into a table with a surrogate key.

For example, you say that a phone number may be used by multiple companies and have modeled phone number as an independent table.  This would prevent update anomalies if when one company changed its number all of the other companies using that number changed theirs at the same time and in the same way.  Does any of that actually make business sense?  It doesn't to me.

Also, normalizing geography into a hierarchy is a questionable proposition.  However, you haven't even normalized into a hierarchy.  Is region ID not determined by country ID?  If so, your LOCATION table is not 3NF.  Similarly, are there postcodes that bridge states?  I don't know the Australian system well enough, but I know that in Canada, the US and the UK this doesn't happen.

You've designed everything like its a star schema and company is the fact.  Facts in star schemas are usually transactions, not static entities.  Transactions don't tend to change their properties, because they usually represent something that actually happened and one doesn't generally go back in time to change history.  Static entities live for a long time in your data (maybe forever) and change their properties fairly often.  Star schema is not a good, efficient model for this type of data.

What you should do is run queries on the frequency distributions of each column and on combinations of columns that you think may be keys combined with columns that you know aren't keys.  This will help you test your assumptions about which columns are truly able to act as keys.  Then you should temper those findings with some common sense around what could potentially happen to your data in terms of changes to column values.  Once you know your actual functional dependencies found in your data, then follow the relatively mechanical process of moving your relation to 3NF.


What are the optimal mysqldump settings? After some search I ended up with the following settings:

    mysqldump\
    --host=localhost\
    --port=3306\
    --databases ****\
    --user=****\
    --password=****\
    --default-character-set=utf8\
    --add-drop-database\
    --add-drop-table\
    --add-locks\
    --complete-insert\
    --extended-insert\
    --lock-all-tables\
    --create-options\
    --disable-keys\
    --quick\
    --order-by-primary\
    --set-charset\
    --tz-utc\
    > dump/test.sql

So far so good but I have some questions regarding the parameters as well as the execution.

Regarding my first concern, I just want to make sure that all of them are required and do not cause any conflicts when used together. Ultimately, I would like to make a very robust and consistent dump file, with millions of records, that creates tables, databases, and inserts data. The good thing is that making the database not-available for a while is not a problem for me. My only goal is to make a robust and consistent dump file.

Regarding my second concert, I would like to know how to get informed when then command goes wrong and if so throw an exception.

Any ideas?

EDIT

This is my updated mysqldump command based on RolandoMySQLDBA's feedback.

mysqldump\
--host=localhost\
--port=3306\
--databases ****\
--user=****\
--password=****\
--default-character-set=utf8\
--opt\
--single-transaction\
--routines\
--triggers\
--events\
--add-drop-database\
--add-drop-table\
--complete-insert\
--delayed-insert\
--tz-utc\
> dump/test.sql

You are going to find this shocking, but you only need one major option : --opt

What is --opt ?

  This option, enabled by default, is shorthand for the combination of --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It gives a fast dump operation and produces a dump file that can be reloaded into a MySQL server quickly.

  Because the --opt option is enabled by default, you only specify its converse, the --skip-opt to turn off several default settings. See the discussion of mysqldump option groups for information about selectively enabling or disabling a subset of the options affected by --opt.

Since --opt is already enabled, you do no need to specify --opt. Notwithstanding, you may need some necessary options that are not included.

Run this query on your database

SELECT engine,COUNT(1) TableCount
FROM information_schema.tables
WHERE engine IN ('InnoDB','MyISAM')
AND table_schema NOT IN ('information_schema','mysql','performance_schema')
GROUP BY engine;

If you have all InnoDB tables, then you need to specify --single-transaction. This will automatically deactivate --lock-tables and allow you to dump all table in the same point-in-time and allow new writes to come at the same time.

If you have one or more MyISAM tables, then you need to specify --lock-all-tables. This will automatically deactivate --lock-tables, deactivate--single-transaction, lock all tables across all databases, then create the dump. Writes to InnoDB tables can still occur, but they will just get queued up until the locks are released. Any DB Connection attempting writes to any MyISAM tables will actually get suspended until all locks are released.

Run this query : SELECT COUNT(1) Number_Of_Stored_Procedures FROM mysql.proc;

If Number_Of_Stored_Procedures is greater than zero, use --routines.

Run this query : SELECT COUNT(1) Number_Of_Triggers FROM information_schema.triggers;

If Number_Of_Triggers is greater than zero, use --triggers.

CAVEAT : Please Do Not Use --order-by-primary for dumping all databases because it can potentially make BTREE indexes rather lopsided on reload. --order-by-primary should only be used when dumping an individual table that you know has an integer primary key and will have lots of range scans from your application.

If you need more creative types of mysqldump backups, see my old post How can I optimize a mysqldump of a large database?.

Please read all the options for mysqldump.

UPDATE 2014-12-29 09:44 EST

  I have updated my mysqldump command (please see my edit). I have a last question though. Do you think it would worth using all of the following arguments as well? --add-drop-database\ --add-drop-table\ --complete-insert\ --delayed-insert\ --tz-utc

Looking at your comment and at your latest edit, let's look at each of these options and see if you need any of them

--opt : I already said you do no need to specify it because it is enabled by default.
--delayed-insert : I adamantly stay away from this with an all InnoDB database. In fact, I adamantly stay away from this PERIOD !!!. Since 1) it is possible for INSERT DELAYED can lose data, 2) it is converted to INSERT for MySQL Replication slaves, 3) there is an open bug report about it use with triggers back in MySQL 5.6 and is not considered a bug, 4) it is deprecated in MySQL 5.6, and 5) Morgan Tocker (well-known MySQL Guru) foresaw its deprecation back in 2012, you should forget this option ever existed. Don't ever, ever (infinity) use it !!!
--complete-insert : This will use INSERT INTO tblname (colnam_1,colnam_2,...colnam_n) VALUES ... instead of INSERT INTO tblname VALUES .... This could bloat the mysqldump if there are many columns in the table definition and many rows in the table. Don't use it.
--add-drop-table : Since --opt enables it for you, you don't need to specify it.
--add-drop-database : If you drop --add-drop-database, it just makes the DROP TABLE IF EXISTS (which was added by --add-drop-table) go faster.  Not using it simply lets DROP TABLE IF EXISTS occur for each table. Thus, using --add-drop-database is a matter of personal choice.
--tz-utc : If you plan to restore data to the same server you backup up from, you do not need --tz-utc. If you restore the data to another data center in a different time zone and ...

if you want to keep the same timezone you backup from, you don't need --tz-utc.
if you want the data to use the time zone of a new data center, you need --tz-utc.
EXAMPLE : Say you backup a database in New York. That's EST. If you have another data center in Seattle, that would be PST. If you want the New York Backup to be restored in Seattle and you want the timestamps in the database to still represent New York, you do not want to use --tz-utc.

--default-character-set

If you are restoring the dump to the same server, you do not need --default-character-set.
If you have an all InnoDB database, you do not need --default-character-set
Since utf8 is the default value, you do not need --default-character-set

Adjusting your latest edit, this is what you particularly need

mysqldump\
--host=localhost\
--port=3306\
--databases ****\
--user=****\
--password=****\
--single-transaction\
--routines\
--triggers\
--events\
--add-drop-database\
> dump/test.sql

Again, I say please read all the options for mysqldump.


Unexpected results from MariaDB IF EXISTS ... THEN I am having unexpected results from MariaDB IF EXISTS... in a stored procedure. I created a minimal example to demonstrate the issue:

    PROCEDURE testdb.testSearchResults(IN pParam1 INT, IN pParam2 CHAR(2))
BEGIN

  IF EXISTS(SELECT COUNT(*) FROM test WHERE intval = pParam1 AND text = pParam2) THEN
    SELECT 'Found' AS result;
  ELSE
    SELECT 'Not Found' AS result;
  END IF;

END

No matter what the contents of the file "test" or the parameters I give to the call, this procedure always returns "Found".

Am I doing something wrong?

Thanks.

SELECT COUNT(*) always returns at least one record (maybe with zero). So IF EXISTS is always true.

You may:

Replace SELECT COUNT(*) ... with SELECT 1 ...
Replace IF EXISTS(SELECT ... with IF 0 < (SELECT ...

I think that 1st variant is more fast. It only checks does at least one record exists, and so it must stop searching after first record found, whereas 2nd query must obtain all matched records, count them, and then compare the result with zero.


What is MySQL's "Implicit Default Value" and how does it work with "strict mode"? From this question comments,

  In the documentation (dev.mysql.com/doc/refman/5.7/en/insert.html), inserting with INSERT INTO tbl_name () VALUES(); is mentioned. It is allowed except for what the doc says : "If strict mode is not enabled, MySQL uses the implicit default value for any column that has no explicitly defined default. If strict mode is enabled, an error occurs if any column has no default value."

We were then wondering what that actually means because with strict mode,

CREATE TABLE foo ( x varchar(255) );
INSERT INTO foo VALUES (x);

is still permitted, so it seems like strict mode fails. Moreover, the CREATE TABLE implicitly creates a DEFAULT null just as if it was created explicitly.

An "Implicit Default Value" seems to be in reference to the type; the "Explicit Default Value" or simply "Default Value" seems to be in reference to the column's DEFAULT,

Also from the docs,

  Inserting NULL into a column that has been declared NOT NULL. For multiple-row INSERT statements or INSERT INTO ... SELECT statements, the column is set to the implicit default value for the column data type. This is 0 for numeric types, the empty string ('') for string types, and the “zero” value for date and time types. INSERT INTO ... SELECT statements are handled the same way as multiple-row inserts because the server does not examine the result set from the SELECT to see whether it returns a single row. (For a single-row INSERT, no warning occurs when NULL is inserted into a NOT NULL column. Instead, the statement fails with an error.)

you can see this documented here in "11.7 Data Type Default Values"

Running through a quick check,

SET sql_mode='';
CREATE TABLE foo ( a int NOT NULL );
-- all of these insert the value 0
INSERT INTO foo (a) VALUES (DEFAULT);
INSERT INTO foo (a) VALUES (a);
INSERT INTO foo () VALUES ();

Whipe it and set strict mode,

TRUNCATE TABLE foo;
SET sql_mode='strict_all_tables';

Retry,

INSERT INTO foo (a) VALUES (DEFAULT);
ERROR 1364 (HY000): Field 'a' doesn't have a default value

-- still inserts "implicit default", doesn't gaf about strict mode.
INSERT INTO foo (a) VALUES (a);
Query OK, 1 row affected (0.01 sec)

INSERT INTO foo () VALUES ();
ERROR 1364 (HY000): Field 'a' doesn't have a default value

You'll see now only one row in the table which is a result of the goofy syntax here that is still permitted with "strict mode".


Availability Group Upgrade 2012 to 2016 Data Move I'm working with a team to upgrade a four node AlwaysOn Availability Group from SQL Server 2012 to 2016. Two of the nodes are local and two are in Azure. The database holds a large amount of data (2 Tb) and it can take hours to sync the nodes.

We're trying to minimize the downtime during the changeover. We were thinking that we could take a full backup of the primary and install it on the other three nodes right before the 'go live' but we're not sure if that will work. If you have experience setting up AG's what would be your advice on how to best handle this?

You could look at log shipping to keep the new 2016 instances up to date well ahead of your upgrade. If you log ship from the current primary to all four new replicas then the cutover and sync is much quicker.

Assuming you're creating a second 2016 AG (and not joining the 2016 instances to the 2012 AG) then at the point of cutover:

Take a log backup on 2012 Primary and restore on 2016 servers with norecovery
Recover DB on 2016 primary
Add DB to AG on 2016 primary and take a log backup
Restore log backup on 2016 secondaries and join to AG
Remove listener from 2012 AG
Add listener to 2016 AG.

If you run into issues post-upgrade and need to revert to 2012, simply remove the listener from 2016 AG an add it back to 2012 AG. The 2012 AG will have carried on like normal and will continue working. The caveat here is that once 2016 is live (after any post-upgrade testing) you won't be able to rollback to 2012 without data loss.

If you're adding 2016 to 2012 AG then log shipping can still be used, but the cutover process is much more complicated as you have to replace the 2012 replicas in a piecemeal fashion as 2012 only allows 4 replicas. Rollback is also more complicated and may leave you without HADR after rolling back until you resynchronise the 2012 replicas.

You need to define this process thoroughly and run through the whole process in a lower environment multiple times to make sure you've got everything aligned and working.


Unattended SQL install on a remote machine My product installation does an unattended install of SQL Server (SQL Server Express by default). I use command line arguments rather than a configuration file, so I'm quite familiar with this process.

This unattended install occurs on the same machine that my install media is running on. However I need to look at installing the SQL Server instance on a separate machine to what the install media is running on. The target machine will be on the same network.

Is it possible to do this unattended? If so, is it simply extra command line arguments or is it a different process (i.e. a PowerShell script)?

To perform unattended install to a remote machine you should consider providing a separate installation option that allows just the database engine to be installed on a machine, and forget about doing it remotely.  Remote installation will be very difficult to troubleshoot and is not transparent for the installing individual.

Having said that you could look at using SysInternals PSexec.exe to remotely spawn the unattended install.


SET CONNECTION in PostgreSQL gives "unrecognized configuration parameter" According to the PostgreSQL documentation, there appears to be support for SET CONNECTION.

However, when I try to execute it from the psql prompt, I get

  ERROR:  unrecognized configuration parameter "connection"

I'm well aware of using \connect but I want to be able to switch databases within the execution of a .sql file.

Is this a bug in PostgreSQL?  Or does some flag need to be set to create the parameter?

Converting question comments into an answer

Abelisto:

It seems that you missed a section of the documentation: Chapter 34. ECPG - Embedded SQL in C. The PostgreSQL documentation is not ideal (but IMO it is much better then many others). Hint: to avoid inconveniences look at the page name in the address, for example plpgsql-declarations.html is about DECLARE variables in the plpgsql blocks and sql-declare.html is about DECLARE cursors in the pure SQL.

dezso:

If using psql, you still can make use of \c - especially if it does not require typing a password.


Is it possible to prevent SQL Agent Job from running if related job or previous instance has not finished? I have three SQL Agent jobs, one for each of three registries being managed. These go off on a staggered schedule every two hours (i.e. Reg 1 at 9am, 3pm; Reg 2 at 11am, 5pm; Reg 3 at 1pm, 7pm).
In most every circumstance, it takes less than two hours for one of these jobs to run. However, if it is taking longer than expected, I do NOT want the other job to run. So, if Reg 1's 9am run is taking more than 2 hours, I do not want Reg 2's 11am job to run UNTIL Reg 1's job is completed.
How can I work this conditional run functionality in? I am not seeing anything in the schedules properties of the job. Let me know if you need more clarification!
Thanks!!

Simplest thing is to have all the steps in a single job. It's built-in that a two instances of a job can't run at the same time.


MySQL Assuming unsigned result in subtractions, what's the motive? I just found out that if I try to subtract an unsigned integer from another unsigned, MySQL automatically assumes the result will be unsigned, which in turn generates the error BIGINT UNSIGNED value is out of range whenever the actual result of the operation is negative.

I tried googling the reason for this design decision, but didn't find any explanation.

What are the motives for such a decision? does it make sense to assume an unsigned result when subtracting two unsigned integers?

Catch-22.
I'll use TINYINT to make the numbers easier.  SIGNED range is -128..127; UNSIGNED is 0..255

Your question, as I see it, is "Why not make the result SIGNED?"

Your case is (10) - (12) = (-2), which cannot be represented as UNSIGNED.

A different case is (200) - (1) = (199), which cannot be represented as SIGNED.

Either decision leads to some arguments producing an value that is "out of range".

(I don't know the history of the decision.  Also, I don't know if there is an SQL standard that is involved.)


SQL Server's Equivalent in MySQL's binary log Is there any MySQL's binary log equivalent in SQL Server.If yes, how can we retrieve data changes(with data) from that log and store it in a table or in a file? I cant enable CDC since I am using SQL Server 2012 Standard Edition. Kindly suggest.

SQL Server's transaction log is for SQL Server's internal uses, and Microsoft doesn't really give us an API to use it for other purposes.

For SQL Server 2012, the closest built-in tool is the undocumented fn_dump_dblog function (linking to a Paul Randal post - former developer at Microsoft), but make sure you read about the gotchas in there about hidden worker threads that don't go away. I wouldn't rely on that for regular data change polling.

If you describe your business goal in more detail (what you want to do with the data changes), you'll get better answers about the right feature or third party tool to use instead. For example, if you're looking for compliance auditing, there's one set of tools, but if you're looking to debug an application, I'd recommend a different set of tools.


Postgres database insert become slow after 10 days, and then it needs a full vacuum We have a postgres database which is almost 15GB's in size after vacuum. With a dedicated hardware i,e 32GB of ram with 12 Cores. The data automatically gets inserted around 300k inserts per day, and we also process the data. The data is deleted after 3 months, in order to keep the db size down.
NORMAL INSERT SPEED USED TO BE: 1200 INSERTS / MINUTE
One of the important table has quite a large trigger on it (We cant do anything about it unfortunately), the DB can insert roughly around 1200 records per minute, which is quite acceptable for us. These records are not inserted in bulk, but rather individually after processing.
AFTER THE PROBLEM STARTED: 200 INSERTS / MINUTE
About 10 days ago, our database suddenly got extremely slow i.e 200 inserts per minute. So after a few sleepless nights we vacuumed top 10 largest tables, but only increased the performance to 300 inserts per minute.
AFTER VACUUM 10 LARGE TABLE: 300 INSERTS / MINUTE
So we vacuum analyzed the whole database, and also REINDEXED the 10 largest tables. This worked extremely well and we were quite satisfied with the results
AFTER FULL VACUUM ANALYSE AND REINDEX: 1400 INSERTS / MINUTE
But just after 10 days we began to have the same problem again, now the inserts are back to 200 inserts/min. We didn't change any configurations of postgres.
AFTER 10 DAYS, BACK TO: 200 INSERTS / MINUTE
Can you please help me identify the problem? i know this is not much information to work with, but if u guys did have this problem before you might instantly identify it. Can this be problem with Disk or too many deadlocks?
Hard disk might not be a problem, as we did gain good speeds after the vacuum, but please let me know if you think that.
Thank you for helping me out.

If performance is restored after a VACUUM ANALYZE, I would question whether the table(s) in question are being auto_vacuumed and auto_analyzed.
If you look at the pg_stat_user_tables view, do the values for last_autovacuum and last_autoanalyze seem reasonable?
If tables are constantly being locked then auto vacuum might not be able to run.


ALTER DATABASE SET ARITHABORT not taking effect I've executed the following query as described here:

ALTER DATABASE [{database_name}] SET ARITHABORT ON WITH NO_WAIT;

SELECT DATABASEPROPERTYEX('{database_name}', 'IsArithmeticAbortEnabled'); returns 1, but sys.dm_exec_sessions shows arithabort set to 0 for all relevant connections. Tracing in Profiler during connection establishment - audit login rows show set arithabort off with no subsequent sets and SELECT @@OPTIONS returns 5432 (ARITHABORT off).

Why isn't this setting taking effect?

Compatibility level is 80. Seeing this across both ODBC and SqlClient connections.

Why are you comparing the database arithabort setting to the settings for a connection (which are not scoped to a database)?

If the client connections are explicitly setting ARITHABORT OFF for that connection, then that's their setting. The code you wrote might not be issuing set arithabort off explicitly; it could be the application, middle tier, data layer, ORM, driver, or any helper code you are inheriting from projects etc. You should figure out where this is being set, and remove whatever is setting it, because after compat 80 there is simply no reason to ever set this off.

Anyway, try this example, and notice where the explicit session setting overrides the database setting in every case:

USE Testing;
GO
SET ARITHABORT ON; -- default
ALTER DATABASE Testing SET ARITHABORT OFF;
SELECT arithabort FROM sys.dm_exec_sessions WHERE session_id = @@SPID; -- 1
SET ARITHABORT OFF;
SELECT arithabort FROM sys.dm_exec_sessions WHERE session_id = @@SPID; -- 0
ALTER DATABASE Testing SET ARITHABORT ON;
SELECT arithabort FROM sys.dm_exec_sessions WHERE session_id = @@SPID; -- 0
SET ARITHABORT ON;
SELECT arithabort FROM sys.dm_exec_sessions WHERE session_id = @@SPID; -- 1

This was also true if I created the database, set it to arithabort off, created a login, added the login to the database, set that database to be the default database for the login, and connected directly to that database in the connection string. It still had the setting the application (in this case Azure Data Studio) defined for the session: arithabort on. And all of that in reverse (with the additional step of setting the initial session setting to off).

You could probably use a LOGON TRIGGER to hack sp_configure to set this option for all new connections, but step back for a second. What exactly are you trying to accomplish by attempting to turn arithabort on for all existing connections to this database, and why do you think the database setting should apply to all connections (existing and future), even if they set their own session setting? And why are you still using compatibility level 80?


How do I ensure consistent MySQL backups? How can we ensure MySQL backups are consistent when they are taken as full backups as well as incremental backups using binlogs?

According to me, I record the COUNT(*) from random tables after issuing flush logs on the database.  I then take binlogs generated until it flushes the binary file and ensuring the table is not altered while doing SELECT COUNT(*).  I then restore the binary logs in different database server and cross check COUNT(*) to ensure the random tables are the same or not against production database and restored database.

Any other thoughts?  Keep in mind that hits from application to tables can be paused only for a few minutes.

Are you using innodb? If so, Percona's XtraBackup is a great choice for automating backups while keeping the server operational and responsive. It also supports incremental backups to shorten the backup duration.

Another option is to have another database that is a replication slave and you backup this slave. You can shut down replication, backup, and resume, never impacting the production data.

I personally use a combination of the two solutions. We have a backup sync and we use xtrabackup to backup this server, thus allowing us consistant backups on demand without impacting production load.


Optimizing view to read rows as per usage statistics There are two modules in the project:

Module 1: patient registration module

patient_admitted table holds information of patient admitted in hospital. patient_id is primary key.

patient_discharged table holds information of patient that got discharged from the hospital.

patient_personal_details view represents patient personal information from patient_admitted table such as name,age,gender etc.
It also reads from patient_discharged table in scenario as below:
A row in patient_admitted table is moved to patient_discharged table once the patient gets discharged, If medical_reports table consists such row  then the view retains that row information by reading it from patient_discharged table.

Module 2: Medical test module

medical_reports table holds medical report details of patient along with the personal information. If the patient is currently admitted then it reads the personal information from the patient_personal_details view.

Problem- Optimization needed:

Over the time the view would read bulk records from patient_discharged table.
Is it possible to discard records(row) in patient_personal_details view that haven't been accessed from past 120 days using some access statistics.

If there better way to design such scenario please suggest.

This sounds like you want to build a materialized view that satisfies your data condition. A materialized view (previously called a snapshot) physically stores a copy of the data rather than a standard view which is simply a query on the underlying tables.
You should get some benefit when querying as the older records will not be in the view and you can define indexes appropriate specifically to viewing.

There are lots of options and restrictions around refreshing of these views and you will need to do some research to establish the right approach in your situation.
https://docs.oracle.com/cd/B28359_01/server.111/b28326/repmview.htm


pg_dump format => custom vs sql There are 2 main formats for pg_dump: custom vs sql. For custom, it's compressed by default, and not readable if you try to open it. But is it faster to dump into this format as opposed to SQL format. Or is the speed the same?

Time it and see. Which is fastest will depend on the balance of CPU to disk I/O on your system. If CPU is abundant and the disk is slow, a -Fc dump will be faster. If CPU is very limited and the disk is fast, an SQL dump might be faster.

I strongly recommend using -Fc custom format dumps anyway. They're much more flexible, and while you can convert a custom format dump into an SQL format dump you can't do the reverse easily.


Two primary keys but not a composite, what is this sort of relationship called? I am integrating employee/position data with another system.

The first system is HR. It has an Employee table and a Positions table. The second system tracks office space and tracks who the office belongs to. It uses one table [em] to store both employees and position data.

If an employee changes position then the position id changes. If the position becomes vacant and a new employee fills the position then employee id is changed.

Both position id and employee ID act like primary keys for this table. I am trying to figure out if there is a name for this type of relationship in a database terminology?

It's called a composite key.

It doesn't matter how you use a composite key. If two pieces of data uniquely identify a row, it's called a composite key. In fact, the use case you're defining is almost always the use case of a composite key.

They're usually natural keys (having both fields related to the data), rather than surrogate keys (having both fields independent and abstract from the data). A store location, and product ID is the canonical example of a natural composite key. You're just using employees.

If, by chance, you mean to ask if both inputs used in the composite key have a name when they have a UNIQUE property, then no.. You can do it, but even in your case it's a bit awkward right? I mean, what if one employee is managing two spaces?

CREATE TABLE em ( emid serial PRIMARY KEY, name text, position text );
CREATE TABLE space ( spaceid serial PRIMARY KEY, owner text );
CREATE TABLE em_space (
  emid    int REFERENCES em,
  spaceid int REFERENCES space,
  PRIMARY KEY (emid, spaceid)
);


Oracle - Tracking events that lead to a deadlock Executing the below query from http://www.orafaq.com/node/854

SQL> select s1.username || '@' || s1.machine
  2  || ' ( SID=' || s1.sid || ' )  is blocking '
  3  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
  4  from v$lock l1, v$session s1, v$lock l2, v$session s2
  5  where s1.sid=l1.sid and s2.sid=l2.sid
  6  and l1.BLOCK=1 and l2.request > 0
  7  and l1.id1 = l2.id1
  8  and l2.id2 = l2.id2 ;

I found that "SID=52 is blocking SID=730" and "SID=730 is blocking SID=52", a clear case of a deadlock. However all I could find from the v$session, v$sqlarea views is the SQL that is currently being blocked by both sessions. How do I see which SQL executed in both sessions that caused the locks.

We are using Oracle 11g (11.2.0.4) and recently noticing deadlock issues everywhere.

Deadlocks are automatically detected by the engine, and one of the statements will be rolled back.  You can find queries by examining alert.log file (it will have a reference to trace file where detailed deadlock information is stored). It's not gonna give you the full stack of queries per sessions that contribute to the deadlock though .

The query you are using shows deadlock until it's resolved by the database.
You can try checking not just v$session.sql_id, but v$session.prev_sql_id (previous query executed within the session context). If you're running Enterprise Edition (and as far as I remember Diagnostic+Tuning Packs) you may examine v$active_session_history view.

If you have an option to modify client applications that use the database, you can use DBMS_APPLICATION_INFO package to identify modules and actions that are being executed.


How to change entire SQL server location from C:\ program file to another drive? scenario:

one of my client was asked me to move entire program files folder from C:\ drive E:\ drive and change the all databases location to E:\ drive. They have copied entire program file folder through third party tool and pasted it on E:\ drive and they asked me to change path of databases and SQL installation to E:\ drive. Now how to change all databases paths and how to do this kind of activity? i know how to move system and user databases manually from one drive to another but i haven't face this king of scenario. Please suggest me how to do this task without any errors.

Trying to move a SQL Server installation in this manner is very dangerous, time consuming, and unsupported.  If someone did this to my server I would not sleep well.

A better approach would be to:

Take note of exactly what service pack and CU level is running, and any non-default configuration items in SQL Server Configuration Manager
Backup all system and user databases
If you need to move user database files, detach the user databases and move the files
Uninstall SQL Server
Install SQL Server with the new drive configuration
Install service packs and/or cumulative updates to get to the version noted in step 1
Restore the master and msdb databases
Apply non-default configurations in SQL Server Configuration Manager
If the user database files have been moved, attach them

Please note that these are general steps and don't account for things specific to your environment, such as shutting down application servers, etc.


Finding Data in File/Filegroup I am having an issue trying to migrate data out of a filegroup to empty and remove the filegroup. I've moved all indexes/tables and all partition schemes using the filegroup to the new filegroup, but I cannot remove the last file from the old filegroup. The error I get is that the file isn't empty.
I've tried the EMPTYFILE trick but this doesn't work as it says there is still data in the file, which seems to be true as querying sys.allocation_units shows around 30 MB of data still in the file. Querying index, partition, object and other DMVs doesn't report anything still using this filegroup as data space.
What other DMVs/DMFs are there to show me what the data left in the file actually is?
SQL Server 2016 Enterprise Edition

So I was never able to determine why the allocated pages couldn't be linked to the underlying object using sys.allocation_units, however, I was eventually able to identify the objects using a modified version of a script from Paul Randal and DBCC PAGE.
Firstly, running this script (which is based on Paul Randal's sp_AllocationMetadata but modified to filter based on filegroup name and not object name):
SELECT
    [sa].[allocation_unit_id] AS [Alloc Unit ID],
    [sa].[type_desc] AS [Alloc Unit Type],
    '(' + CONVERT (VARCHAR (6),
        CONVERT (INT,
            SUBSTRING ([sa].[first_page], 6, 1) +
            SUBSTRING ([sa].[first_page], 5, 1))) +
    ':' + CONVERT (VARCHAR (20),
        CONVERT (INT,
            SUBSTRING ([sa].[first_page], 4, 1) +
            SUBSTRING ([sa].[first_page], 3, 1) +
            SUBSTRING ([sa].[first_page], 2, 1) +
            SUBSTRING ([sa].[first_page], 1, 1))) +
    ')' AS [First Page],
    '(' + CONVERT (VARCHAR (6),
        CONVERT (INT,
            SUBSTRING ([sa].[root_page], 6, 1) +
            SUBSTRING ([sa].[root_page], 5, 1))) +
    ':' + CONVERT (VARCHAR (20),
        CONVERT (INT,
            SUBSTRING ([sa].[root_page], 4, 1) +
            SUBSTRING ([sa].[root_page], 3, 1) +
            SUBSTRING ([sa].[root_page], 2, 1) +
            SUBSTRING ([sa].[root_page], 1, 1))) +
    ')' AS [Root Page],
    '(' + CONVERT (VARCHAR (6),
        CONVERT (INT,
            SUBSTRING ([sa].[first_iam_page], 6, 1) +
            SUBSTRING ([sa].[first_iam_page], 5, 1))) +
    ':' + CONVERT (VARCHAR (20),
        CONVERT (INT,
            SUBSTRING ([sa].[first_iam_page], 4, 1) +
            SUBSTRING ([sa].[first_iam_page], 3, 1) +
            SUBSTRING ([sa].[first_iam_page], 2, 1) +
            SUBSTRING ([sa].[first_iam_page], 1, 1))) +
    ')' AS [First IAM Page]
FROM sys.system_internals_allocation_units AS [sa] (NOLOCK)
INNER JOIN sys.allocation_units au (NOLOCK) ON au.allocation_unit_id = sa.allocation_unit_id
INNER JOIN sys.data_spaces ds (NOLOCK) ON ds.data_space_id = au.data_space_id
WHERE ds.[name] = '<filegroup name here>'

The output gave me a list of allocation units and the associated page identifiers:
Alloc Unit ID       Alloc Unit Type First Page      Root Page   First IAM Page
72059231029821440   IN_ROW_DATA     (44:2579440)    (21:2832)   (21:2812)
72059231073271808   IN_ROW_DATA     (25:4532384)    (21:3888)   (21:3883)
72059240244248576   IN_ROW_DATA     (21:656)        (21:656)    (21:1040)
72059240310046720   IN_ROW_DATA     (44:1047088)    (21:2592)   (21:2572)
72059240312340480   IN_ROW_DATA     (25:2516760)    (21:1880)   (21:1859)

These results can then be checked against DBCC PAGE to identify the underlying object:
DBCC TRACEON (3604);
DBCC PAGE (19, 21, 688, 0);
DBCC TRACEOFF (3604);

This outputs some Metadata, specifically a line that says Metadata: ObjectId = <object id>. This object ID references the object this data page is related to.
In my case, I had around 300 allocation units to work through. The concerning part is the objects identified were built on partition schemes that never referenced the filegroup in question as a destination data space, but somehow the data ended up allocated in these files. In any case, rebuilding the indexes deallocated the pages from the filegroup in question and the files and filegroup could be removed.


What happens if a client connection is killed while holding a lock? What happens if a client establishes a transaction, locks a row or table, and the client process crashes before it has a chance to rollback or commit the transaction? What, if anything, does SQL Server do with the transaction?

Does SQL Server have a heartbeat mechanism to detect that the socket got killed?
Upon detecting that a socket got killed, does SQL Server rollback the transaction automatically? Or does the row/table lock persist (causing other clients to deadlock)?

Please provide authoritative sources to back up your answer.

UPDATE: The client process is crashing. I am not talking about someone invoking the KILL command.

The session along with the spid attached will be killed/rolled back.

https://stackoverflow.com/questions/3978227/how-to-kill-or-rollback-active-transaction

https://technet.microsoft.com/en-us/library/ms173730%28v=sql.110%29.aspx

EDIT:

I would check your logs to verify, but in essence, the socket would be forcibly closed.

It's not a database issue from what I can see...but do you have an error message you can post to clarify?

Also see Errors During Transaction Processing

  If an error prevents the successful completion of a transaction, SQL
  Server automatically rolls back the transaction and frees all
  resources held by the transaction. If the client's network connection
  to an instance of the Database Engine is broken, any outstanding
  transactions for the connection are rolled back when the network
  notifies the instance of the break. If the client application fails or
  if the client computer goes down or is restarted, this also breaks the
  connection, and the instance of the Database Engine rolls back any
  outstanding connections when the network notifies it of the break. If
  the client logs off the application, any outstanding transactions are
  rolled back


Can putting mysql DB into memory or having the innodb_buffer_pool_size match the size of DB increase performance? We have a mysql database that has roughly 80 gigabytes (GB) of data using inno_db engine. We are looking to increase performance on the database (we looked at the slow log and gone through and optimized the db and indexes) and one avenue is to get better hardware.

Currently our mysql DB is on a 6 year old server with 16GB of mem with 2 x Xeon 2.00GHz (2 cores) with the ability to have 32 GB mem max.

We are looking to buy a new server for the mysql database with the following specs:

Dell PowerEdge R420 - Highlight specs

2 Intel Xeon E5-2450 2.10GHz, 20M Cache
128 GB RAM
400GB Solid State Drive

So if the database is 80 GB and we have 128 GB of Memory on this new server my question (s) are:

Can we put the full database into memory and see performance gains?

Would setting the innodb_buffer_pool_size to 88GB effectively be the same as putting the "the DB in memory"?

Can mysql take advantage of this memory? Are there any limitations on this on the mysql side?

Would there be any pitfalls of loading the full mysql in database or having such a large innodb_buffer_pool_size?

I have a client at my employer's company with 192GB RAM using 162GB Buffer Pool.

Here is what we did for the client

InnoDB Tuning : I set the following options

innodb_read_io_threads (maxed it to 64)
innodb_write_io_threads (maxed it to 64)
innodb_thread_concurrency = 0 (It's default now in MySQL 5.5/5.6)
innodb_io_capacity (set it to IOPs of Disk, for this client, 20000 [2-0-0-0-0 not typo])
innodb_buffer_pool_size=162G
innodb_buffer_pool_instances=2 (Number of Physical Cores)

OS Tuning (numactl --interleave=all)

If you forget numactl --interleave=all, the buffer pool experiences swapping.

Here are my past posts on tuning InnoDB

Oct 22, 2012 : How large should be mysql innodb_buffer_pool_size?
Jul 23, 2012 : How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?
Jul 21, 2012 : InnoDB - High disk write I/O on ibdata1 file and ib_logfile0
Sep 20, 2011 : Multi cores and MySQL Performance
Sep 12, 2011 : Possible to make MySQL use more than one core?


Identifying root cause of slow query outliers from pt-query-digest? I've run pt-query-digest and noticed many outliers with very high query times. Many of these queries do not perform slowly when I run them myself, and I'm having trouble figuring out the root cause of these sporadic slow queries. Is there another tool I might use to shed more light on this situation? What might cause the same query to only be slow some of the time?

Queries can become slow in such cases:

server is shorted out with disk i/o
server is shorted out with memory and swapped
server have too small key_buffer_size and buffer should be refreshed prior to perform the query
server have too small (join|sort)_buffer_size and on-disk temporary tables are created

Any of the mentioned above can cause sporadical slowdown. List is not exhaustive, sure.


Creating one stored procedure for all CRUD operations I'm very inexperienced at writing stored procedures with conditional logic and I could really use some help. I have a data model with 7 tables and I'm trying to write a stored procedure for each table that allows four actions. Each stored procedure should have 4 parameters to allow a user to insert, select, update and delete a record from the table. I want to have a stored procedure that can accept those 4 parameters so I only need to have one stored procedure per table instead of having 28 stored procedures for those 4 actions for 7 tables. I haven't found a good example online yet of conditional logic used in a stored procedure.  You'd think there would be more examples online but still struggling to find a good one.

Is there a way to add a conditional logic IF statement to a stored procedure so if the parameter was INSERT, go run this statement, if it was UPDATE, go run this statement, etc?

I recently found this post on this website: (Separate stored procedures for inserts and updates?) but I don't know how it would be modified to also include the other two operations for selecting data and deleting data.

I have attached my data model for reference. Thanks in advance for your help!

I would strongly advise against this route. While it seems advantageous to only have 7 stored procedures rather than 28, it will turn into a troubleshooting, tuning, and maintenance nightmare.

I don't know what dbms you are using, but the advice should apply to all of them.

From a troubleshooting and usage analysis standpoint you will have a hard time.  Most reporting you get from your dmvs doesn't show the parameters the user submitted to execute the stored procedure. You are going to have to capture them yourself or run traces to see it in real time. This makes it incredibly difficult to determine how these stored procedures are being used, and how the users are using the system.  Are they reading from the db more than inserting or deleting? Without digging deep you won't know. If you break them out into different stored procedures you can see which ones are used when and how often.
From a maintainability standpoint you would think that less files would be better, but it actually makes it worse. Now, whenever you want to change something to the insert portion of a stored procedure you are touching the part that inserts, updates and deletes, which means that they are all added to the regression testing scope. It also means that when you deploy new stored procedure code you'll  impact people that are utilizing that table no matter what they are doing.
From a tuning standpoint you are actually working against most dbms query optimizers. The optimizers will look at the data in the tables needed and try to determine the best way to retrieve data from them.  The issue here is that the operation is not predictiable because your operation relies on parameter sniffing.  Also, the strategy of 1 stored procedure to rule the table makes a few assumptions. Mainly that the way that you select, insert, update, and delete data will always be the same.  What happens when that changes? You won't want to keep adding because it won't be easy to navigate. It's also hard to determine which indexes to make being that it's extremely difficult to determine how the optimizer is going to react.

From a usability standpoint, it makes things difficult. When you insert you'll need every column passed in as a parameter, but when you select it will only be the id you are filtering on, when you update it may only be the column you want to update and you have to make the query understand this. This normally leads to dynamic sql which is its own nightmare.

The long and short of it is that stored procedures are really good for doing very specific things. The more specific the better most of the time. While this doesn't lead to code reusability and a lot more stored procedures to work with, it makes things a lot easier to troubleshoot, maintain and tune along your applications growth. It is also more intuitive to developers that need to use the database.

If you do want to go this route, the only real way to do it would be if statements trying to sniff our what a user wanted to do based on parameter:

IF @action = 'insert'
 INSERT INTO......

I still would not recommend this though.


What permissions are required to return rows from information_schema.schemata? In postgres (I'm on 9.3.4) what permissions are required to return rows for

select * from information_schema.schemata

The 9.3 documentation says "are owned by a currently enabled role". Does that mean the user calling schemata must own (or be part of a role that owns) the various schemata?

Later in 9.5 documentation it says schemata that "the current user has access to (by way of being the owner or having some privilege)".

The account currently trying to run the query and getting 0 rows back has had grant usage on schema applied to it.

What are the privileges that allow a database account to see a schema in the information_schema.schemata` view?

The user needs to be the owner of a schema to get any rows from information_schema.schemata

alter schema $schemaName owner to $user;

Or the user needs to be a member of a group that owns the schema

Usage grant is not sufficient. The user does not need to be a superuser. The user can own a table in a schema, but that is not sufficient either.


Problem with database migrated from SQL Server 2005 to SQL Server 2014 First of all I want to apologize for my English, I hope you understand me correctly.

I have a database migrated from a SQL Server 2005 in a SQL Server 2014.
Well, in addition, I have a .NET process that uses ODBC connectors to execute stored procedures in the database.

My problem is that this process lasts 20 minutes against SQL Server 2005, and against the migrated database in SQL Server 2014 it takes 8 hours.
After many tests, if the level of compatibility of the database to SQL Server 2008, the process takes only 8 minutes.

Any idea of the reason? What problems can I have when lowering the compatibility level?

Any idea of the reason? What problems can I have when lowering the compatibility level?

SQL 2014 made substantial changes to the "Cardinality Estimator" in the query optimizer.  For applications that were built and optimized using the older behavior, you may see a performance regression.

The workaround you selected of using the lower database compatibility level is fine.  You can also use a Trace Flag 9481 to force the legacy cardinality estimator on newer database compatibility levels.

Eventually you should analyze and optimize the queries and indexes under the new cardinality estimator.

See, eg: SQL Server 2014’s new cardinality estimator (Part 1)


Does SQL Server 2016 support foreign keys in an in-memory table from a disk bound table If using an in-memory table in SQL Server 2016, can I define a column in that table as a foreign key from a non in-memory (or disk bound) table? I know that the foreign key does need to be the primary key of the referenced table.

I'm just wondering about the limitations and reliability of in-memory tables and how well they work with disk bound tables in SQL Server 2016.

Does SQL Server 2016 support foreign keys in an in-memory table from a disk bound table

No, not at this time - A foreign key between a disk based table and memory optimized table is not allowed.
You will get below error :

Msg 10778, Level 16, State 0, Line 3
Foreign key relationships between memory optimized tables and non-memory optimized tables are not supported.


Performance impact of updating target columns with same values ON CONFLICT This is a question about the inner-workings of Postgres (v10) and performance.

Given a table, github_repos, with a multi-column unique index on org_id and github_id columns, is there any performance difference (or other issues to be aware of) between the two bulk upsert operations below? The difference is that in the first query, the org_id and github_id columns are included in the UPDATE, whereas in the second query they are not. Since the UPDATE runs ON CONFLICT, the updated values of org_id and github_id will be the same as the old values, but those columns are included in the UPDATE because the underlying library I am using is designed that way. I'm wondering if its safe to use as-is or whether I should be explicitly excluding those columns in the UPDATE.

Query #1:

INSERT INTO "github_repos" ("org_id","github_id","name")
VALUES (1,1,'foo')
ON CONFLICT (org_id, github_id)
DO UPDATE SET "org_id"=EXCLUDED."org_id","github_id"=EXCLUDED."github_id","name"=EXCLUDED."name"
RETURNING "id"

Query #2:

INSERT INTO "github_repos" ("org_id","github_id","name")
VALUES (1,1,'foo')
ON CONFLICT (org_id, github_id)
DO UPDATE SET "name"=EXCLUDED."name"
RETURNING "id"

github_repos table:

      Column       |       Type        | Collation | Nullable
-------------------+-------------------+-----------+----------+
 id                | bigint            |           | not null |
 org_id            | bigint            |           | not null |
 github_id         | bigint            |           | not null |
 name              | character varying |           | not null |

Indexes:
    "github_repos_pkey" PRIMARY KEY, btree (id)
    "unique_repos" UNIQUE, btree (org_id, github_id)

It seems to have no difference from performance perspective , assuming you have no triggers that runs only if certain columns are updated.

The whole row is updated , or to be more Postgres specific (Postgres doesn't have in-place updates), the new tuple will be inserted, and the old one will be marked as dead .  From that regard it doesn't matter if actual change happens for only one column, or all of them , or neither .  You can check values of n_tup_upd, n_dead_tup  columns in pg_stat_all_tables - they both increase after each update . n_dead_tup will eventually get reset after performing vacuum.

The only thing that might be negatively affected is HOT updates (no new index tuple if indexed column doesn't change value). HOT updates are explained in many places, the best one in my opinion is http://www.interdb.jp/pg/pgsql07.html .

Testing HOT updates (done with Postgres 9.6, 10.4 and 10.5)

--- setup
-- useful extension for checking data and index pages
create extension pageinspect;
-- dummy table  (varchar is used instead of text just to avoid any complication related to TOAST )
create table test_update(test_update_id int not null , some_data varchar(50),constraint test_update_pkey primary key (test_update_id) );

-- insert  2 rows
insert into test_update (test_update_id, some_data) values (1, 'test1');
insert into test_update (test_update_id, some_data) values (2, 'test2');

--check table stats :
select relname ,n_tup_upd, n_dead_tup , n_tup_hot_upd  from pg_stat_all_tables where relname ='test_update';
 --(all zeroes)
-- check #rows in index :
select * from bt_page_items('test_update_pkey',1) ;
-- 2 rows

----------------------------------------------
-- Test 1 . perform dummy update :
update  test_update set test_update_id = test_update_id , some_data = some_data;

-- check tables stats again,
--n_tup_upd = n_dead_tup = n_tup_hot_upd  = 2
-- check index page again, results are the same - 2 rows

-- Test 2 . real update non-key column , and dummy update of key column  :
update  test_update set test_update_id = test_update_id , some_data = some_data||'_new';

-- check tables stats again,
--n_tup_upd = n_dead_tup = n_tup_hot_upd  = 4
-- check index page again, results are the same - 2 rows

-- Test 3 . real update non-key column , key column is not in the statement:
update  test_update set some_data = some_data||'_new_2';

-- check tables stats again,
--n_tup_upd = n_dead_tup = n_tup_hot_upd  = 6
-- check index page again, results are the same - 2 rows

-- Test 4. Now do real update of key column :

update  test_update set test_update_id = test_update_id+2;
-- check tables stats again,
--n_tup_upd = n_dead_tup = 8, n_tup_hot_upd  = 6  (no HOT update this time)
-- check index page again, now we see 4 rows

It appears that Postgres is smart enough to identify cases where indexed columns are not changed , and perform HOT updates; thus , there is no difference between having or not having key columns in update statement from performance point of view.  The only thing that matters it whether actual value changed. Surely, this behaviour is limited to B-Tree indexes.


inner join select - group by? I have 2 tables (Oracle database):

ANEXA2A_TABEL1:

id  fk_id_anexa pet1    pet2    alt1    alt2
1   123         10      1       20      2
2   321         11      2       10      3

ANEXA2A_TABEL2_ROWS:

id  fk_id_anexa tip cant
1   123         PET 100
2   123         PET 10
3   123         ALT 200
4   123         ALT 20
5   321         PET 11
6   321         ALT 22

And I need to create a select (used after that in a view) to have a column like in that example:

id  fk_id_a pet1    pet2    pet3    alt1    alt2    alt3
1   123     10      1       110     20      2       220
2   321     11      2       11      10      3       22

I tried:

SELECT a2a.ID, a2a.FK_ID_ANEXA, a2a.PET1, a2a.PET2, a2a.ALT1, a2a.ALT2,
    (SELECT SUM(CANT) FROM ANEXA2A_TABEL2_ROWS where TIP = 'PET') AS PET3,
    (SELECT SUM(CANT) FROM ANEXA2A_TABEL2_ROWS where TIP = 'ALT') AS ALT3
FROM ANEXA2A_TABEL1 a2a
    INNER JOIN ANEXA2A_TABEL2_ROWS a2a2
        ON a2a2.FK_ID_ANEXA = a2a.FK_ID_ANEXA

but I get something like:

id  fk_id_a pet1    pet2    pet3    alt1    alt2    alt3
1   123     10      1       110     20      2       220
1   123     10      1       110     20      2       220
1   123     10      1       110     20      2       220
1   123     10      1       110     20      2       220
2   321     11      2       11      10      3       22
2   321     11      2       11      10      3       22

how can I get just that?

id  fk_id_a pet1    pet2    pet3    alt1    alt2    alt3
1   123     10      1       110     20      2       220
2   321     11      2       11      10      3       22

I guess I have to use a Group By - but I don't know how.
Thanks!

There are several ways to do this. Either remove the join and convert the subqueries to correlated:

SELECT a.ID, a.FK_ID_ANEXA, a.PET1, a.PET2, a.ALT1, a.ALT2,
    (SELECT SUM(b.CANT) FROM ANEXA2A_TABEL2_ROWS b
     WHERE b.TIP = 'PET' AND b.FK_ID_ANEXA = a.FK_ID_ANEXA
    ) AS PET3,
    (SELECT SUM(b.CANT) FROM ANEXA2A_TABEL2_ROWS  b
     WHERE b.TIP = 'ALT' AND b.FK_ID_ANEXA = a.FK_ID_ANEXA
    ) AS ALT3
FROM ANEXA2A_TABEL1  a ;

or keep the join and use GROUP BY (I converted to LEFT join so you also see results for values that the second table has no related rows):

SELECT a.ID, a.FK_ID_ANEXA, a.PET1, a.PET2, a.ALT1, a.ALT2,
    SUM(CASE WHEN b.TIP = 'PET' THEN b.CANT ELSE 0 END) AS PET3,
    SUM(CASE WHEN b.TIP = 'ALT' THEN b.CANT ELSE 0 END) AS ALT3
FROM ANEXA2A_TABEL1  a
    LEFT JOIN ANEXA2A_TABEL2_ROWS  b
        ON b.FK_ID_ANEXA = a.FK_ID_ANEXA
GROUP BY
    a.ID, a.FK_ID_ANEXA, a.PET1, a.PET2, a.ALT1, a.ALT2 ;

There are several more options to do this (i.e. first group by table b in a derived table and the join to table a, or using PIVOT, or using the specific to Oracle function DECODE instead of CASE,  ...)


Microsoft ODBC Driver 11 for SQL Server: Named Pipes Provider: Could not open a connection to SQL Server I am aware this question has been asked before. I have been following the extensive troubleshooting steps at MSQL Tips as well as every answer I can find on Google. I am really stumped.

The error is as follows:

I cannot seem to get past this error. Every time I install/uninstall MS SQL Server I receive the same error. I have even performed a hard reset of the laptop removing even the BIOS information but I still get the same error. I have discovered that installs of MS SQL Server are concurrent so this perhaps explains it.

I even set up a virtual machine running Windows 7 to debug and got the exact same message. The application can be run on a different workstation and works perfectly. It seems the issue is something hard coded on to my computer.

Not knowing the full details of your issue by your question try the following:

Option 1:

Temporarily disable Windows Firewall and test your SQL script once more:

Option 2:

Make sure NetBIOS is enabled in the TCP/IP settings of the network adapter you are using:

Option 3:

Ensure that SQL Server protocols are enabled through SQL Server Configuration Manager:

Option 4:

Try a combination of two or more options above.


How to combine these two queries and have them be as fast? I have a very legacy system with millions upon millions of records. It is very poorly managed and maintained, originally having been an Access database.

So in one particular look-up I am getting the asset deployment history for a particular employee. Their name is searched in the employee table to find their Employee ID. Their employee ID is then searched in the PC history table.

Right now I have two queries that are fast:

SELECT `tbl_employee`.EMPLID
FROM `tbl_employee`
WHERE `tbl_employee`.NAME
LIKE "%Smith,John%"

The above query takes 0.0250 to 0.0850 seconds to resolve in benchmarks. In this case the employee ID is 518266.

I can re-use this result in the next query:

SELECT `tbl_pc history`.SerialNumber, `tbl_pc history`.Date, `tbl_pc history`.ModelID
FROM `tbl_pc history`
WHERE `tbl_pc history`.`EmployeeNumber`
LIKE "%518266%";

This is also very fast, between 0.0340 and 0.0679 seconds in benchmarks.

I now arrive at what I want, a list of 4 items from millions which were at some point issued to this employee.

I have tried to combine this query a couple of ways and they resulted in several second resolution times, instead of fractions of a second:

SELECT `tbl_pc history`.SerialNumber, `tbl_pc history`.Date, `tbl_pc history`.ModelID
FROM `tbl_pc history`
WHERE `tbl_pc history`.`EmployeeNumber`
IN
    (SELECT tbl_employee.EMPLID
    FROM tbl_employee
    WHERE tbl_employee.NAME
    LIKE "%Smith,John%");

The above query gets me what I want, but takes 4.8508 to 6.3010 seconds to execute.

SELECT `tbl_pc history`.`SerialNumber`, `tbl_pc history`.`Date`, tbl_employee.NAME, `tbl_pc history`.`ModelID`
FROM `tbl_employee`
INNER JOIN `tbl_pc history`
ON `tbl_pc history`.`EmployeeNumber` = tbl_employee.EMPLID
WHERE `tbl_employee`.`NAME`
LIKE "%Smith,John%"

This also results in 4-6 second queries.

Please excuse my ignorance in this area - I have next to no knowledge of databases, SQL, PHP, or any of this and have been blindly googling my way to solutions for the past couple of weeks.

Would help if you post SHOW CREATE TABLE for the two tables and also how many rows each table has.

Most likely the slowness is due to use of LIKE "%Smith,John%". This will do a full table scan on tbl_employee.

Try LIKE "Smith,John%" just to see if the query performance is any better this way. I am assuming you have an index on tbl_employee.name column.


Moving a 10g database to 11g with rman backup Following Doc ID 369644.1, I'm trying to restore a 10g database into a 11g database ( version 11.2.0.1.0), which is in other server.

What I did on 11g server:
- Generate full rman backup of 10g database
- Copy pfile, control file and full rman backup from 10g to 11g

C:\> oradim -new -sid BDSPROD -intpwd bdsprod
C:\> SET ORACLE_SID=BDSPROD
C:\> rman
RMAN> connect target sys
RMAN> SET DBID=367107039
RMAN> STARTUP force nomount PFILE=C:\app\oracle\product\11.2.0\dbhome_1\database\PFILE_bdsprod.ora
RMAN> restore controlfile from 'C:\app\oracle\product\11.2.0\dbhome_1\database\CTRLFILE_BDSPROD.CTL';
RMAN> alter database mount;
RMAN> crosscheck backupset;
RMAN> delete expired backupset;
RMAN> catalog start with 'D:\RMAN_bdsprod\';
RMAN> restore database;
RMAN> recover database;

After this step, I followed Doc ID 558408.1

SQL> connect sys/bdsprod as sysdba
Connected.
SQL> alter database open resetlogs upgrade;

Now, how can I startup database? It seems that only way is doing:

SQL> STARTUP upgrade PFILE=C:\app\oracle\product\11.2.0\dbhome_1\databas\PFILE_bdsprod.ora;

In that way, only sysdba can connect to database.
Other ways like just "startup" or "startup pfile=C:\app\oracle\product\11.2.0\dbhome_1\database\PFILE_bdsprod.ora;" it gets errors.
Is there any way to start 11g database?

SQL> startup PFILE=C:\app\oracle\product\11.2.0\dbhome_1\database\PFILE_bdsprod.ora;
ORA-32006: BACKGROUND_DUMP_DEST initialization parameter has been deprecated
ORA-32006: USER_DUMP_DEST initialization parameter has been deprecated
ORACLE instance started.
Total System Global Area 709836800 bytes
Fixed Size 2179136 bytes
Variable Size 201330624 bytes
Database Buffers 499122176 bytes
Redo Buffers 7204864 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 12712
Session ID: 5 Serial number: 3

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'C:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\INITBDSPROD.ORA'

After alter database open resetlogs upgrade the database should be open.This statement replaces the startup upgrade command.  If this kind of upgrade is possible than you are after the startup upgrade step in the upgrade procedure and you should continue after this step. But you should have prepared the database in the way as described in the steps before startup upgrade.

I never tried this way to upgrade. You should do this kind upgrade only if you are very experienced with Oracle know exactly what you are doing. Otherwise you should choose an upgrade path that is recommended by oracle like Database Upgrade Assistant or a method like import/export. Check the manual


Convert Varchar Duration time to time I need my code to add the varchar CPUTM field + varchar CPUZIPTIM field which both has time values to see if greater than 2 hours. How do I do this when both fields are varchar. The value in CPUTM field is  '335:55:20.97' duration time.  My code is below.

      CPUTM =     335:55:20.97 duration time
     CPUZIPTM =     0:00:01.96  duration time

select * FROM [SMF_DATA].[dbo].[System_Management_Facility]
WHERE ((convert(varchar(13), CONVERT(time, CPUTM) + CONVERT(time, CPUZIPTM)))
       > '02:00:00.00')

Using SQL server 2012 Enterprise Edition.

Assuming the format in the 2 columns is hours:mm:ss[.fff] where hours can be even more than 24, I think this would work:

SELECT smf.*
FROM [SMF_DATA].[dbo].[System_Management_Facility] AS smf
  CROSS APPLY
    ( SELECT CHARINDEX(':', CPUTM)    AS i1,
             CHARINDEX(':', CPUZIPTM) AS i2,
             LEN(CPUTM)    AS len1,
             LEN(CPUZIPTM) AS len2
    ) AS a
  CROSS APPLY
    ( SELECT CONVERT(int, LEFT(CPUTM, i1-1))
             + CONVERT(int, LEFT(CPUZIPTM, i2-1)) AS number_of_hours,
             CONVERT(datetime, '00:' + RIGHT(CPUTM,    len1-i1)) AS d1,  -- implicit
             CONVERT(datetime, '00:' + RIGHT(CPUZIPTM, len2-i2)) AS d2   -- dates
                              -- both d1 and d2 get implicit date of '1900-01-01'
    ) AS b
WHERE
    number_of_hours + DATEDIFF(hour, '19000101', d1 + d2) >= 2 ;

The CROSS APPLY are not really needed, they are there so the logic is split in parts and so you don't have a huge complicated final condition.

If you wanted a different check (say 10 minutes instead of 2 hours), it would need only this change:

WHERE
    number_of_hours = 0
  AND
    DATEDIFF(minute, '19000101', d1 + d2) >= 10 ;


Optimizer gets the wrong number of estimated rows even with updated stats I have a procedure running in SQL Server 2008 that contains a query similar to the one below;

SELECT
/* various columns from table 1 and table 2 */
FROM dbo.tbl_1 AS a
INNER JOIN dbo.tbl_2 AS b ON a.AccountNumber = b.AccountNumber
WHERE a.RecordActive = 1 AND b.RecordActive = 1;

tbl_1 and tbl_2 are both partitioned views that equally contain 3 yearly base tables (2014-2016), partitioned by month and unioned in the view. The tables are large and 2016's data contains approximately 34,000,000 records for the first 3 months alone.

The query normally takes 10 minutes to run, when the right plan is chosen. However, recently it has been taken well over an hour.

When I check the estimated plan, it uses an index seek using a filtered index WHERE RecordActive = 1 for the table containing 2016's data. The estimated rows is 18,000, but when the query executes the actual number of rows is closer to 2,000,000 - a clustered index scan probably being a better choice.

This is the case despite updating the statistics for the filtered index with a full scan. As the predicate on the tables for column RecordActive is a BIT datatype yielding either 1 or 0, the histogram for the filtered statistic only contains one entry for the value 1, which shows the number of rows equaling that value is 2,000,0000.

I have turned on OPTION(RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 9204) and I can see that the filtered stats have been considered. So why then does it get the estimate so badly wrong?

Please help, as I am running out of ideas and would like to know what is really happening under the hood!

P.S. If pictures would help explain the problem better please let me know, this is my first post on the site so please excuse my bad etiquette.

Have a look at this DBCC UPDATEUSAGE command:

DBCC UPDATEUSAGE(N'db_name',N'dbo.table_name') WITH COUNT_ROWS


Would CRUD operations get very slow on an mysql innodb table which got more than 50 million rows? I'm about to design a DB for a finance institution and the last normalization of tables shows that the necessity of creating a single table for all the bank's transaction.

As per the calculations, several thousand transactions per day and this table would hit few million entires withing next few months.

1) When querying and writing data, would server perform very slow?

2) Is there any best way to handle the situation?

I have seen a billion-row table hum along fine.

I have seen a thousand-row table cause the bones of the server to creak and groan.

It all depends on the queries.

"several thousand transactions per day" -- No Problem.  "several thousand transactions per second" -- This gets somewhat challenging, but probably doable.

What to do?

Provide SHOW CREATE TABLE
Provide the main queries.
Provide the main transactions.
Don't do COUNT(*)
Don't do anything that requires hitting all 50M rows -- ask for help in reformulating such queries.

If this is really a financial institution, be sure to hire a security consultant.  You do not want the hassle you will get if the data is lost/stolen.


Concurrent requests not running in parallel I am trying to run update statistics in parallel.

Say I start with 2 sessions: S1, S2

In S1 I run
Update Statistics A with fullscan and time it to take 2 minutes.

In  S2 I run Update Statistics B with fullscan and time it to take 2 minutes also.

Now I run both in parallel and I can see that both finish in around 2 minutes as expected.

Now I add a new session S3 (this issue doesnt just happen with the 3rd session or whatever, i'm not really sure when exactly it occurs but I have noticed it)

In S3 I run Update Statistics B with fullscan and time it to take 2 minutes also.

I run S1 AND S3 in parallel, and now notice both queries are taking 4 minutes to complete. If I run S1 and S2 in parallel instead, now it takes 2 minutes for both queries. If I run S2 and S3 in parallel, it takes 2 minutes. Basically it seems like there is some kind of restriction where it does not let S1 and S3 execute requests at the same time, regardless of the query. I have verified this by checking sys.dm_exec_requests while the two queries are executing, S1 and S3 keep alternating between running and runnable/suspended with only one query running at once. Any other combination of sessions has both sessions running at the same time.

I can't see any kind of blocking as well going on here.

What can cause this issue?

Think back to the bad old days when everyone's computer had a single CPU with just one core. If you ran a program that took 2 minutes on its own at the same time as another one that took 2 minutes on its own then both of them combined might take 4 minutes. There's only so many CPU cycles to go around after all and the two programs would be competing for resources. The same thing can happen in SQL Server if you throw too much work at it relative to your server size or if you get unlucky.

Based on your description of the problem, the most likely explanation is that session 1 and session 3 are running at MAXDOP 1 and they end up on the same CPU core. Session 2 is on a different CPU core. That could be why performance degrades when you run sessions 1 and 3 at the same time but running 1 and 2 at the same time leads to no degradation. To validate that, the wait type that you want to look for is SOS_SCHEDULER_YIELD. While the queries are running, you can also use the sys.dm_exec_requests DMV:

SELECT session_id, scheduler_id
FROM sys.dm_exec_requests;

If your two sessions end up on the same scheduler_id (a scheduler maps back to a logical core) then it's reasonable to expect that both sessions will take twice as long as they did when you were just running one query at a time. This is a bit more complicated if you have hyperthreading enabled and exposed to SQL Server, but the same kind of behavior can be observed. You would want to look at sys.dm_os_schedulers as well because you could see a similar degradation on different schedulers if those schedules are on the same physical core.

If you want to get into the details for scheduling, you could start by reading this blog post. A small quote:

  Scheduling assignment starts at the NUMA node level.  The basic
  algorithm is a round robin assignment for new connections.    As each
  NEW connection arrives it is assigned to a scheduler based on round
  robin, NUMA node connection assignment.

If your server has NUMA nodes that consist of just one scheduler then you could easily get yourself into a situation where queries get doubled up unnecessarily. The fix would be to change your NUMA configuration or to run your queries in different sessions until you get the desired behavior.


24x7 vs Night Time Window Where can I find resources on how to better move to a 24x7 operation?  How do the big companies with big databases accomplish this? Our nightly jobs such as

purge old data
reindex
update statistics

all seem to cause critical impact to our system (i.e. online users and real time data feeds).  I've looked on Amazon for any book related to this subject, and so far haven't found anything.

Maintaining 24x7 databases is a fairly large topic with lots of options to consider.  This broad topic has many items to consider, but we can try and touch base on some of the high points.

What you first will want to identify is, while many operations are 24x7, there are usually times of low activity.  You can leverage these times for running your maintenance so you reduce the interference you will have on the database.  The second is you will need to reserve some time for complete outages (for things like service packs or database migrations), so you will need to negotiate full maintenance windows with your management.  For specific items you will need to consider and plan for each one, as well as leverage your tools appropriately.  The important piece is you must PLAN each of these, any examples I provide are very much "your miles may vary".

Backups

Backups commonly won't have a huge impact on workloads, but must be accounted for as they can consume a lot of I/O.  You will want to schedule these appropriately and monitor the amount of time it takes to complete.  The biggest hurdle here is that in a 24x7 operation, you will likely not be able to conduct full nightly backups every night of the week.  You will want to plan out when you can take fulls, when you are taking differentials, and retention periods for both of these in combination with your log backups.

As an example, I run full backups of all my databases on Sunday night (lowest activity), differentials on all other nights (Monday-Saturday).  I keep the last two weeks of fulls and diffs on disk, logs for the last two days.  This gives me enough flexibility for recovery, but I might have to recover backups from tape if necessary.

Index/Statistics Maintenance

This is the most common type of active maintenance you will have to deal with.  You can't avoid it, but you can mitigate the impact.  The initial rule of thumb is that you should only do maintenance on objects that need it.  General guidelines are to only rebuild indexes that are greater than 30% fragmented and larger than 1000 pages.  If you have auto-update statistics, this will handle most of your statistics maintenance, but a nightly job to keep things in sync isn't a bad idea.

If you have Enterprise Edition, you also have access to some other options for managing maintenance.  Foremost is Online Index Rebuilds, which will allow you to rebuild indexes while they are still in use (essentially, it builds the index side by side, then swaps it in).  You also can leverage partitioning for "large" tables to reduce the amount of rebuild time necessary.

Your best bet for this type of maintenance, if you don't have custom scripts that handle these best practices, is to use Ola Hallengren's Maintenance scripts.  These are fairly easy to setup and configure and have many of these guidelines built in.

DBCC Consistency Checks

Depending on your overall workload, you might find DBCC checks to be to disruptive for your operation.  There are two common ways to minimize your DBCC impact for your databases:

PHYSICAL_ONLY - Running this option will check your databases at a physical page level and avoid the more invasive full check.  This will cover identifying the most likely types of corruption.
Checking a restored copy - If you have the space, you can restore the database to another instance and run a DBCC check against the restored copy.  This will tell the same tale about your live database, but you obviously won't interfere with the activity.  Some other alternatives here are running DBCC against a log shipped copy or a mirrored db.

This blog post provides more detail on your options.

Batch jobs/ETL

This really comes down to how you design your processes.  Your ETL can always interfere with live OLTP tables (just as any other application), so some keys to keep in mind:

Schedule such work around your other maintenance and in low activity periods.
Right size the work so that it's batched for both performance and so that the batch is not so big that it locks your table for hours.  Examples of the ends of the spectrum:  Row-by-agonizing-row (RBAR) versus a single million row delete.
Use stage tables and offline your data processing where appropriate.  Only touch the live stuff when absolutely necessary.

Conclusion

Again, there's a lot of ground to cover here.  This is not a comprehensive guide, but a high level overview of some approaches.  I haven't even discussed high-availability options (such as Availability Groups and Failover Clustering).  You will need to review each item and build out a plan for how to handle it.  In many ways, you will also need to iterate and refine your work as you move forward.

Additional resources:

SQL Skills VLDB maintenance best practices