texts: MySQL usage suddenly hitting through the roof

texts:
We have a site with MySQL DB running properly for more than 2 years.

However, 2 weeks ago we noticed a sudden spike in load. We started the MySQL service and load normalized. We assumed the issue was one off and forgot about it.

But for last 1 week are seeing the MySQL load hitting high almost on a daily basis. Sometimes just a few minutes after restart. It has rendered the site almost unussable.

A check on the log does not show any query that would cause a spike. Majority of the queries (about 90%) take less than 10ms. A few (about 9%) takes between 10 and 20ms. There are still some that take between 20 and 30ms. But this are are negligible, accounting for about 1%. Apart from 2 long-running queries that runs when the app starts, I haven't seen any regular query that takes more than 30ms.

$ uname -a
Linux 3.10.0-229.7.2.el7.x86_64 #1 SMP Tue Jun 23 22:06:11 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

$ mysql --version
mysql  Ver 14.14 Distrib 5.6.27, for Linux (x86_64) using  EditLine wrapper

Any reason why MySQL would suddenly become unstable?

If, for some reason (s), we have just hit limitation for MySQL (probably an InnoDB issue) would it help if we moved to MariaDB or Persona Server (that uses XtraDB)?

Edit 1: Additional information

Something I have noticed is at the time when performance degrades there are several SELECT queries. They seem not any big resource intensive queries (the longest takes about 4ms) but they occur in bursts, and probably this could be the problem.

The `'burst queries' looks valid, and not as a result of DOS attacks or XSS.

**Edit 2: MySQL conf*

$ cat /etc/my.cnf
[mysqld]
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under different user or group,
# customize your systemd unit file for mysqld
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
tmpdir=/var/mysqltmp

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

open_files_limit=50000
query_cache_size=2048M
max_connections=100
max_user_connections=25
wait_timeout=20
tmp_table_size=6144M
max_heap_table_size=6144M
thread_cache_size=64
key_buffer_size=8192M
max_allowed_packet=268435456
#table_cache=32768
table_open_cache=32768
table_definition_cache=32768

delayed_insert_timeout=20 # Turn on if max_connections being reached due to delayed inserts
delayed_queue_size=300 # Turn on if max_connections being reached due to delayed inserts

myisam_sort_buffer_size=512M # can be increased per sessions if needed for alter tables (indexes, repair)

query_cache_limit=32M # leave at default unless there is a good reason
query_cache_type=1
join_buffer=32M # leave at default unless there is a good reason
sort_buffer_size=32M # leave at default unless there is a good reason
read_rnd_buffer_size=16M # leave at default unless there is a good reason
read_buffer_size=32M # leave at default unless there is a good reason

collation_server=utf8_unicode_ci
character_set_server=utf8

general_log=0
log-output=TABLE # select * from mysql.general_log order by event_time desc limit 10;

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
innodb_file_per_table=1
innodb_buffer_pool_size=34406M
innodb_additional_mem_pool_size=7424M
innodb_log_buffer_size=7424M
innodb_thread_concurrency=8 # Number of physical + virtual CPU's, preset when server is provisioned to have correct # of cores
innodb_change_buffering=all
innodb_flush_log_at_trx_commit=0
innodb_support_xa=0
innodb_doublewrite = 0

default-storage-engine=MyISAM

[mysqld_safe]
#malloc-lib=/usr/lib64/libtcmalloc_minimal.so.4
malloc-lib=/usr/lib64/libtcmalloc_minimal.so.4
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

my.cnf shows default-storage-engine=MyISAM but all tables I have created are InnoDB.

Environment

How much RAM? 48GB

Using InnoDB? Yes

How many cores? 8


texts: Set FORCESEEK for only one of hinted indexes

texts:
Is there a way to use a TABLE HINT with two indexes on the same table and set the FORCESEEK for only one of them?

The following statement causes an error because the first index is columnstore:

OPTION (TABLE HINT(ta, NOLOCK, INDEX (csi_index, PK_index), FORCESEEK))


texts: Replicate SQL Data Inside Firewall to Outside Firewall - Suggestions

texts:
Currently I have an internal CRM application that houses all my data and runs my entire company.  The front end is ASP, and the back end is SQL.  The external site will run SQL

What I'd like to do is to have an external site for customer's to log in, manage their accounts, etc., but I am very concerned about my data and data security.  Are there any best practices that I can follow to "replicate" data to the external databases?  Are there some suggested methods people have done in this scenario?

My goal is to have the two databases linked with certain data.  Changes inside would be queued somehow to the outside, and vice versa.  If it is live, that wouldn't be the end of the world either.


texts: MySQL Database Schema for Train Timetables

texts:
I am interested in what a MySQL database schema would look like for a train timetable.

Generally this is presented in table format as the final result.

Train No.    11111  22222  11111
Train Day    Mo-Fr  Sat    Sun
Station A  d 06.00  07.00  07.00
Station B  d 06.10         07.10
Station C  d 06.20  07.15
Station D  a 06.30         07.40
Station D  d 06.35  07.25
Station E  d 06.45  07.45

So each train has a train number, but that isn't unique as the number will be duplicated for a Monday-Friday to a Saturday and Sunday.  Then a train will stop at a number of station, but not all, and sometimes the station needs an arrival (a) and departure (d) time, or one or the other.

Stations can be ordered by their distance.

So far I am thinking I need tables for:

Stations (ID, name, distance, line)
Train (ID, train no, Day Operation, details)
Train run (Train.ID, Station.ID, arr/dep, time, notes)

Is this normalised enough?

Should say train 11111 for Mon-Fri be just one entry with lots of combinations of Day Operation, ie 7 columns (M, T, W, Th, F, S, Su) ?
Shouldn't it also handle a one off train that runs on say just one date and doesn't repeat weekly?


texts: Can I convert SQL Server 2008 full db to SQL Server 2005?

texts: Recently I converted my SQL Server 2005 database to SQL Server 2008. Now I can't attach that db to SQL Server 2005. I do not have any backup of that db file as well. I have tried to generate scripts but that way i can only recover the tables. Is there any way to convert the whole database along with existing records? Please help


texts: Why is a connection via an SSH tunnel presenting as 127.0.0.1 and not localhost?

texts:
I have defined a MariaDB user test@localhost and I have skip-name-resolve ON as recommended for performance reasons.

I'm able to connect with -hlocalhost -P3306 and so is my web application using the hostname "localhost".

If I attempt to connect via an SSH tunnel to "localhost" I receive the error that 'test'@'127.0.0.1' can't connect (as that user and host isn't defined).

In all three situations I've requested to connect to "localhost" from the same machine and for connections established from the server directly this appears to work. So why am I presenting to MariaDB as 127.0.0.1 via the SSH tunnel?

Supplementary question: Is there actually any point in enabling skip-name-resolve for this setup as from what I can see my web app is presenting itself as "localhost" and so there is no resolving going on and the only thing it's doing is preventing 127.0.0.1 being resolved to localhost when connecting via an SSH tunnel?

CentOS 7.6, MariaDB 5.5.64


texts: Does "alter table … set data type" functionally differ from "alter table … type"?

texts:
The docs show [SET DATA] TYPE which implies the SET DATA part is optional, but the 9.1 release notes also imply that SET DATA TYPE may be different:

  Allow ALTER TABLE ... SET DATA TYPE to avoid table rewrites in appropriate cases

I'm trying to update a column on a billion-row table from int to bigint and I'm at the four-hour mark. I'm curious if I should cancel the transaction and restart it using the SET DATA TYPE form instead of the plain TYPE form that I used.

Does SET DATA TYPE do something different (and hopefully faster)?


texts: Suggest better design pattern for reverse DNS lookups

texts:
I'm working on a project where I need to look up the hostnames associated with IP addresses who were logged making HTTP requests. The lookups currently happen as part of a daily ETL job. The current method is to use a scalar CLR function (similar code to this is posted a number of places on the web, posted below with my revisions; I'm not sure who the original author was):

using System.Data.SqlTypes;
using System.Net;
using System.Security;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;

public partial class udfn_GetHostName
{

[Microsoft.SqlServer.Server.SqlFunction]
public static string udfn_GetHostname(string IPAddr)
{
    try
    {
        /*
         Using deprecated method intentionally.
         GetHostEntry() is now recommended.
         But it does some irritating things like returning an error if a PTR
         record points to a name that doesn't have an A record.
        */
        IPHostEntry IpEntry = Dns.GetHostByAddress(IPAddr);
        // Test whether the record returned has at least one alphabetic character
        // If it does, then it's a name
        // Otherwise the DNS server might have returned the IP address
        Match match = Regex.Match(IpEntry.HostName.ToString(), @"[a-zA-Z]+");

        if (match.Success)
        {
            return IpEntry.HostName.ToString();
        }
        else
        {
            return "None";
        }

     }
    catch(Exception ex)
    {
        return "Failed";
        //return ex.Message.ToString();
    }
  }
}

I'm not a C# developer so the quality of the CLR code is likely not great.

Then I call the function like this after loading new rows into the dimension:

-- Update only rows that we just inserted
UPDATE DIM.Network_Addresses
SET reverse_dns = dbo.[udfn_GetHostname](client_ip)
WHERE reverse_dns IS NULL
AND is_current = 1
AND created_date = (SELECT MAX(created_date) FROM DIM.API_Network_Address);

This method works but is very slow, for at least a couple reasons.

1) Using a scalar function makes SQL Server call the CLR function
    once per row that needs to be updated, using a new SQL context.

2) The function calls themselves are very slow due to how GetHostname() and other CLR name resolution functions work: long timeouts, sometimes multiple round trips across the network that all time out if a DNS server doesn't respond or there is no PTR record, etc.

Could any recommend a design pattern that would improve the performance of looking up reverse DNS records and updating the table?

I'm considering a few different things:

1) Move this work outside of the database and use a tool such as dig to do the lookups in parallel.

2) Try to find some way to call the function in parallel or convert it to an in-line function (not making much progress on this!)

However, any ideas would be welcome.


texts: How to protect MySQL database from sql-injection?

texts:
I am new to databases and security.

I am planning to put up a website.

I've read that sql-injection vulnerabilities can be very dangerous since the server might leak user passwords and credit-card information.

Are there any steps that I need to follow to prevent SQL injection vulnerabilities?

I was going through a wiki on sql-injection, but I didn't understood the following query:

SELECT * FROM users WHERE name = '" + userName + "';


texts: is it possible to get innobackupex to print the filenames it's busy with?

texts: I am getting crashes when trying to run innobackupex --apply-log. How can I find out what files it's processing at the time? I don't see a relevant option mentioned in the documentation.


texts:
I have an active passive SQL Cluster. When I stop the SQL services on active node from SQL Server Configuration Manager, the SQL cluster doesn't failover instead shows as Partially online.

If SQL services are stopped from Configuration Manager, it will not failover to another node?

I have to prepare test cases for my client, so it is really urgent for me to clear this doubt.


texts:
Considering this table >
TRIP Table :
( Code [pk] , Origin , Destination , Distance )
how to make (Origin , Destination) attributes has the constraint of composite key "can repeat if one of them is different" .


texts: Oracle RMAN - MAXPIECESIZE ignored for Level 0 backups

texts:
I'm having issues with Oracle 12.1 rman - specifically the MAXPIECESIZE parameter not being honored by my Level 0 backups.

This value is set in rman as

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/u01/rman/prdb/rman/%U' MAXPIECESIZE 10 G;

and the level 0 backup is triggered via the following script:

RUN {
  BACKUP INCREMENTAL LEVEL 0 TAG 'prdb_incr' DATABASE;
  BACKUP DEVICE TYPE DISK TAG 'prdb_incr' ARCHIVELOG ALL NOT BACKED UP DELETE ALL INPUT;
}

However, this is creating a single 30G file rather than splitting it up into 10G chunks.

[oracle@localdomain]$ ls -ltrh /u01/rman/prdb/recovery/PRDB/backupset/2018_05_20/
total 32G
-rw-r-----. 1 oracle oracle 309M May 20 09:50 o1_mf_nnnd1_PRDB_INCR_fj2jb825_.bkp
-rw-r-----. 1 oracle oracle 1.2G May 20 09:50 o1_mf_annnn_PRDB_INCR_fj2k6m7d_.bkp
-rw-rw----. 1 oracle oracle  30G May 20 11:35 o1_mf_nnnd0_PRDB_INCR_fj2pj8p0_.bkp
-rw-rw----. 1 oracle oracle 289M May 20 11:36 o1_mf_annnn_PRDB_INCR_fj2qdwf2_.bkp

I have tried explicitly setting the CHANNEL DEVICE as part of the backup script  (immediately before the BACKUP INCREMENTAL line) - this made no difference.

Also maybe related, the location does not seem to match what I am specifying above - /u01/rman/prdb/recovery vs /u01/rman/prdb/rman - this seems to match up with db_recovery_file_dest set in the database:

db_recovery_file_dest            string  /u01/rman/prdb/recovery

but I can't see any options in there that would override the MAXPEICESIZE

To be clear - I'm NOT concerned about the location just the file sizes.

Is there another setting somewhere that I am missing? Do I need to remove the db_recovery_file_dest parameter maybe?

Thanks

Backup summary of the latest backup:

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1961    Incr 0  29.64G     DISK        00:15:06     20-MAY-18
        BP Key: 1964   Status: AVAILABLE  Compressed: NO  Tag: PRDB_INCR
        Piece Name: /u01/rman/prdb/recovery/PRDB/backupset/2018_05_20/o1_mf_nnnd0_PRDB_INCR_fj2pj8p0_.bkp
  List of Datafiles in backup set 1961
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    0  Incr 755820030  20-MAY-18 /home/oracle/app/oracle/oradata/prdb/system01.dbf
  2    0  Incr 755820030  20-MAY-18 /home/oracle/app/oracle/oradata/prdb/users02.dbf
  3    0  Incr 755820030  20-MAY-18 /home/oracle/app/oracle/oradata/prdb/sysaux01.dbf
  4    0  Incr 755820030  20-MAY-18 /home/oracle/app/oracle/oradata/prdb/undotbs01.dbf
  5    0  Incr 755820030  20-MAY-18 /home/oracle/app/oracle/oradata/prdb/example01.dbf
  6    0  Incr 755820030  20-MAY-18 /home/oracle/app/oracle/oradata/prdb/users01.dbf
  7    0  Incr 755820030  20-MAY-18 /data/oracle/app/oracle/oradata/prdb/users03.df
  8    0  Incr 755820030  20-MAY-18 /home/oracle/app/oracle/oradata/prdb/doco_small.dbf
  9    0  Incr 755820030  20-MAY-18 /home/oracle/app/oracle/oradata/prdb/doco_large.dbf
  10   0  Incr 755820030  20-MAY-18 /data/oracle/app/oracle/oradata/prdb/users04.dbf
  19   0  Incr 755820030  20-MAY-18 /data/oracle/app/oracle/oradata/prdb/audi_tbs.dbf


texts: Does anybody know of a relational database schema that can be used to store XBRL data? I am looking to store financial report data (balance sheet, income statement and profit/loss statement data) along with other company data (Name, Stock Ticker, etc.) in MySQL.


texts:
Anyone have any suggestions for how to optimize this query? Running SQL Server 2008

SELECT
    o.id_testu,
    o.pub_int_od,
    p.tk AS stanowisko,
    p3.tu AS jednostka,
    ox.id_testu AS lk,
    ox1.id_testu AS wn,
    ox2.id_testu AS nd,
    o.jozus_wprow AS jozus_utw,
    o.priorytet
FROM
    dbo.TEST AS o
    LEFT OUTER JOIN
        dbo.POLA AS px ON
        px.id = o.id_testu
        AND px.id_definicji_pola = 143
    LEFT OUTER JOIN
        dbo.TEST AS ox ON
        ox.id_testu = px.id_testu
        AND ox.data_pub IS NOT NULL
        AND ox.pub_int_od < GETDATE()
    LEFT OUTER JOIN
        dbo.POLA AS px1 ON
        px1.id = o.id_testu
        AND px1.id_definicji_pola = 138
    LEFT OUTER JOIN
        dbo.TEST AS ox1 ON
        ox1.id_testu = px1.id_testu
        AND ox1.data_pub IS NOT NULL
        AND ox1.pub_int_od < GETDATE()
    LEFT OUTER JOIN
        dbo.POLA AS px2 ON
        px2.id = o.id_testu
        AND px2.id_definicji_pola = 147
    LEFT OUTER JOIN
        dbo.TEST AS ox2 ON
        ox2.id_testu = px2.id_testu
        AND ox2.data_pub IS NOT NULL
        AND ox2.pub_int_od < GETDATE()
    INNER JOIN
        dbo.POLA AS p ON
        p.id_testu = o.id_testu
        AND p.id_definicji_pola = 129
    INNER JOIN
        dbo.POLA AS p3 ON
        p3.id_testu = o.id_testu
        AND p3.id_definicji_pola = 127
WHERE
    (ox1.pub_int_do > GETDATE())
    AND (o.id_typu_testu = 8)
    AND (o.czy_gotowe = 1)
    AND (o.data_apr IS NOT NULL)
    AND (o.data_superapr IS NOT NULL)
    AND (o.data_pub IS NOT NULL)
    AND (o.pub_int_od < GETDATE())
    OR (o.id_typu_testu = 8)
    AND (o.czy_gotowe = 1)
    AND (o.data_apr IS NOT NULL)
    AND (o.data_superapr IS NOT NULL)
    AND (o.data_pub IS NOT NULL)
    AND (o.pub_int_od < GETDATE())
    AND (ox1.data_pub IS NULL)
    OR (ox1.pub_int_do < GETDATE())
    AND (o.id_typu_testu = 8)
    AND (o.czy_gotowe = 1)
    AND (o.data_apr IS NOT NULL)
    AND (o.data_superapr IS NOT NULL)
    AND (o.data_pub IS NOT NULL)
    AND (o.pub_int_od < GETDATE())
    AND (ox2.pub_int_do > GETDATE())
    AND (ox2.data_pub IS NOT NULL)


texts: Listener is not starting in my Standby database

texts:
I'm facing a serious issue in data guard installation. I'm setting oracle_sid and oracle_home and when I start the listener,error is showing as below:

C:\Windows\system32>lsnrctl start
LSNRCTL for 64-bit Windows: Version 11.2.0.4.0 - Production on 31-MAY-2017 16: :38
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting tnslsnr: please wait...
Failed to start service, error 0. TNS-12560: TNS:protocol adapter error

Solutions which I have tried:

I've set the correct path in environment variables which is in System Properties.
I've given correct IP address in Hostname(listener.ora)

But still nothing could help me out....It's urgent


texts: Can SQL Server WebAdmin degrade the performance of my site?

texts:
Once upon a time a had to use the Parallels WebAdmin interface.

After contacting the support team that hosts my website and informing them that I must use the Webadmin, they informed me that:

  MS SQL WebAdmin can degrade the performance of your site, database and the overall server.

Is that true? How can a tool like that degrade performance? Should I try searching for a different web hosting provider?


texts: how to know how many instances of an application are running on server

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


texts: SQL Server - moved index behind PK constraint to different filegroup, but space not freed

texts:
I have a large table Foo.  There are 4 filegroups (FG1, FG2, FG3, FG4)

FG2 is on a disk that is nearly full.  It has 2 indexes:

a) clustered index (on Date)
b) a unique non-clustered index (on ID)

There is a PK constraint on the table, on ID.

I wanted to move index (b) from FG2 to a different file group - FG4.

First I tried simply builiding a second index, like (b), on FG4, then dropping (b).  This failed - An explicit DROP INDEX is not allowed on index due to the PK.

So I went ahead and rebuilt it on the new filegroup:

CREATE UNIQUE NONCLUSTERED INDEX PK_Foo
ON Foo(ID)
WITH (DROP_EXISTING =  ON, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, ONLINE = ON, ALLOW_ROW_LOCKS  =
ON, ALLOW_PAGE_LOCKS  = ON)
ON [FG4]

This succeeded. I can see the index is now definitely on FG4, all is well.
However, no space has been freed up on FG2 at all. If I try to shrink FG2, it shows no space within it is free.

Where has my space gone?

Thanks!

Update - Index sys.data_spaces:

FileGropName    ObjectName  IndexType   IndexName   type_desc   Mb

FG3 Foo NONCLUSTERED    IX_Foo_CorrelationId    IN_ROW_DATA 193802

FG3 Foo NONCLUSTERED    IX_Foo_DeviceIntId  IN_ROW_DATA 97676

FG3 Foo NONCLUSTERED    IX_Foo_DrtId_RC_CorrelationId   IN_ROW_DATA 286386

FG2 Foo CLUSTERED   IX_Foo_LastResponse IN_ROW_DATA 468845

FG4 Foo NONCLUSTERED    PK_Foo  IN_ROW_DATA 104377


texts: MySQL stored procedure create database by month

texts:
I'm a systems admin, and I'm not a dba, I'm wondering if I could use MySQL stored procedure to
Query main database and move records by month to another database located on the same system.

This way we can maintain database small main database, and have data moved to individual database by month.

Thank you


texts:
We are on 11.2.0.1.I have been trying to create a Manual Standby database using Standard Edition.

Although at this point its more like an incomplete recovery.

This is wat i did

Create Standby control file on the source

Backup source(db+arc).

Restore on target.(Followed pretty much the standard RMAN restore procedures)

Am able to mount the instance and apply the most recent ARC generated on the source.But when I try to  open I get the below error

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 03/21/2014 14:02:12
ORA-01666: control file is for a standby database

This is what i see on my alert log

ORA-279 signalled during: alter database recover logfile '/u03/oracle_recovery/flash_recovery_area/XX/1_11478_723733449.arc'...
alter database recover cancel
Media Recovery Canceled
Completed: alter database recover cancel
Fri Mar 21 09:07:25 2014
alter database open resetlogs
ORA-1666 signalled during: alter database open resetlogs...
Fri Mar 21 09:07:57 2014
alter database open resetlogs

Reading up on some posts over the internet I tried to open the DB in RO mode

alter database open read only
Beginning standby crash recovery.
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Media Recovery Waiting for thread 1 sequence 11479
FAL[client]: Error fetching gap sequence, no FAL server specified
Fri Mar 21 09:11:21 2014
Standby crash recovery need archive log for thread 1 sequence 11479 to continue.
Please verify that primary database is transporting redo logs to the standby database.
Wait timeout: thread 1 sequence 11479
Standby crash recovery aborted due to error 16016.
Errors in file /u01/app/oracle/diag/rdbms/xx/xx/trace/xx_ora_21943.trc:
ORA-16016: archived log for thread 1 sequence# 11479 unavailable
Recovery interrupted!
Completed standby crash recovery.
Signalling error 1152 for datafile 1!
Errors in file /u01/app/oracle/diag/rdbms/xx/xx/trace/xx_ora_21943.trc:
ORA-10458: standby database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup

I understand that it is requesting the next archive sequence 11479.This is not yet generated on the source.

Can anyone tell me why 'Open resetlogs' failed in the first place ?

Is this the behaviour when we create a 'Standby' control file ?

Thanks,


texts: How do I load this SAS text file to SQL Server 2005

texts:
Every 3 months, I have to load several fixed-width text files into our SQL Server 2005 database.  They can have anywhere from 5 to 798 columns (seriously) and the columns change pretty regularly.

Here's an example of the fixed-width data file:

010001    2000040620000406001200004060220000406
010001    2000040620001116002        0020000406
010006    2007051620070516001200706220120070516
010006    2010071520100908002        0020100715
010006    2010071520100715001201007300120100715
010007    1993110919931109001199311230219931109

With the text files, I also receive an "SAS" file that documents the columns and data types.  Here's an example:

/************************************************************************
*           Job Number: 119164
*     Procedure Number: 6
*        Record Length: 47
*************************************************************************/

INPUT

         @1   PRVDR_NUM                         $CHAR10.
        @11   SRVY_DT                           $CHAR8. /*YYYYMMDD*/
        @19   CYC_VISIT_DT                      $CHAR8.
        @27   CYC_VISIT_NUM                     3.
        @30   POC_SGN_DT                        $CHAR8.
        @38   LSC_BLDG_CNT                      2.
        @40   SRVY_CMPLTN_DT                    $CHAR8.
;

***************************************************************;
***   SAS LABEL Statement Follows                              ;
***************************************************************;

LABEL

      PRVDR_NUM                         = 'CCN'
      SRVY_DT                           = 'Survey Date'
      CYC_VISIT_DT                      = 'Cycle Visit Date'
      CYC_VISIT_NUM                     = 'Cycle Visit Number'
      POC_SGN_DT                        = 'Administrator Signed POC Date'
      LSC_BLDG_CNT                      = 'Building Count'
      SRVY_CMPLTN_DT                    = 'Survey Completion Date'
;

The person I get this file from calls it "SAS Input Code"

Is there a solution for loading the fixed-width file that can also use the SAS file to create the table?  My current solution is too elaborate, and I really hope there's an alternative.


texts: Reporting services - High availability setup - SQL server 2012

texts:
I need some guidance on best practices and setup for SSRS. I have a SQLServer 2012 setup with data source on 1 instance and SSRS on a separate server. There is a need to setup HA and I understand what is involved with the setup for the data source server. This is a large instance with many databases.

The SSRS instance is very small (350 MB). Should I setup AOAG for the report server db? OR configure a scale-out deployment? The goal is for smooth transition should one server go down. I am not very familiar with SSRS i.e setup beyond report db - the report manager etc. Would failover cluster manager take care of the rest. I will have to guide the application team with this.

Getting some input as to the direction I need to look and research will be gratefully appreciated.

Thank you,
Debbie.


texts: Could not find appropriate mongod in `D:\MMSData\MongoDBReleases

texts:
I have Opsmanager with 3 nodes [Windows version 4.2] . Backup Daemon running on two nodes. When I switch backup daemon from one node to another node my resync failing with below error message:

class com.xgen.svc.brs.job.ApplyOpsJob => ERROR backup.jobs.M1ReplicaSet [ReplicaSetJob.java.startMongo:139] - Failed to start mongod

com.xgen.svc.brs.util.AbstractMongoManager$MongodVersionException: Could not find appropriate mongod in D:\MMSData\MongoDBReleases, versions available to MMS: . Expecting version 3.2.1 or greater, module preference: enterprisePreferred

But I have binaries in zip format on both node

Regards

Naga


texts: Need help with LIKE operator and square brackets

texts:
Sample data in a table column, data type NVARCHAR(MAX). This data is collected by extended event trace capturing database calls generated by Entity Framework.

exec sp_executesql N'SELECT TOP (2)
    [Extent1].[ccusqcode] AS [ccusqcode],
    [Extent1].[ccusname] AS [ccusname],
    [Extent1].[ccusfirstname] AS [ccusfirstname],
    [Extent1].[ccuslastname] AS [ccuslastname],
    [Extent1].[dbirthday] AS [dbirthday],
    [Extent1].[ccellphone] AS [ccellphone],
    [Extent1].[cdriverlicense] AS [cdriverlicense],
    [Extent1].[csocsecno] AS [csocsecno],
    [Extent1].[dfirstvisit] AS [dfirstvisit],
    [Extent1].[dlastvisit] AS [dlastvisit],
    [Extent1].[nmtdrxcount] AS [nmtdrxcount],
    [Extent1].[lduplabel] AS [lduplabel],
    [Extent1].[cemployerid] AS [cemployerid],
    [Extent1].[mnotes] AS [mnotes],
    [Extent1].[cemployercontact] AS [cemployercontact],
    FROM [dbo].[customer] AS [Extent1]
    WHERE ([Extent1].[cusid_PK] = @p__linq__0) AND ([Extent1].[pharminfoid_FK] = @p__linq__1)',N'@p__linq__0 bigint,@p__linq__1 bigint',@p__linq__0=NULL,@p__linq__1=1

I want to use LIKE operator to find all rows with text FROM [dbo].[customer].

SELECT *
  FROM [dbname].[schemaName].[tableName]
  WHERE STATEMENT like '%FROM [[]dbo[]].[[]customer[]]%'

Why it does not work?


texts:
I am building an application in which users will have a score in which they can receive it (+10 XP) when they do certain activities, but they can also lose it when using that score to make "purchases".
What I need is to find the best way to store this. At first I had thought of creating a column in the user table to save the score, so when the user earned points I would select this column, add the points earned to the previous score and update the column with the new data. The same thing would be done if the user lost points.
However, I believe that this is not the best possible way, as I believe that there could be conflicts if that same user tried to do an operation "at the same time" and this would lead to a misuse of these points, which cannot happen. The user could send two requests almost in parallel and that they would cause the score to be debited only once.
Thinking about it, I thought that the best way would be to create a separate table just for these scoring transactions, where if the user earned points I would add a new data to that table containing for example +10 in the "points" field and when the user lost I would add new data to the table containing -21 for example. Thus, I could get the user's current score by selecting all data and passing SUM (points), which would return the exact amount of points the user has.
However, I don't know if this is really the best way to do what I want, since there would be a lot of "unnecessary" information being stored and mainly I would have to make a select on large amounts of data. Anyway, I would like to find out if there is a better way!
To be clear I am attaching a picture of what I intended to do:


texts:
I have a MySQL backend, MS Access front-end system that is nice and responsive over a local network but becomes sluggish over a broadband connection. I haven't done any performance tuning as of yet. What are the recommended tools and tuning methods that I should be using?

Recently, I discovered that MySQL keeps a 'General Query Log'. Maybe I can monitor the changes in this log file to see what requests are actually being made to the server. Is this a good method for fine tuning?


texts: Can i SELECT two user names by id in another table with join?

texts:
I have table sb_bans which including BannerID and UnbannerID.
And another one sb_admins which including aid and name.

This is my query:

SELECT sb_bans.*, sb_admins.authid as admin_authid, sb_admins.user as admin_name
FROM sb_bans
LEFT JOIN sb_admins
ON sb_admins.aid = sb_bans.aid
ORDER BY `sb_bans`.`bid`

Table sb_bans look like this:

| bid | BannerID | UnbannerID |
|=====|==========|============|
|  1  |    26    |     26     |
|  2  |    27    |     13     |
|  3  |    10    |     13     |
|  4  |    11    |     27     |
|  5  |    13    |     10     |

Table sb_admins look like this:

| aid |   name   |
|=====|==========|
| 10  | Tomi     |
| 11  | Dominik  |
| 13  | Michal   |
| 26  | Kristian |
| 27  | Daniel   |

And i want make SELECT what will look like:

| bid | BannerID | UnbannerID |
|=====|==========|============|
|  1  | Kristian | Kristian   |
|  2  | Daniel   | Michal     |
|  3  | Tomi     | Michal     |
|  4  | Dominik  | Daniel     |
|  5  | Michal   | Tomi       |

I only know how to make it when i want use name by aid only once.
It is how i have it in my quary code upper.
Please can someone help me configure SQL code to make it work well.
Thanks so mutch.


texts: MySQL slow queries - Fast using PHPMyAdmin , low CPU and RAM usage

texts:
I have a site running on Amazon EC2 , which runs fast and contains some reasonably complex MySQL queries.

Part of that site is an API which serves out data to another site. These queries are very simple (one is literally returning one row from a table containing only that row).

Making these API calls from the external domain results in a 10-20 second delay. The data always returns fine, no errors are thrown. Nothing is shown in the logs.

Running any of these queries locally in PHPMyAdmin , they complete in around 0.007 seconds .

CPU load is 20%-30%.
RAM usage is about 60%.

My problem is similar to:

This

I've added 'skip_name_resolve' to my my.cnf. This made no difference.

There is a lot of talk about MySQL and DNS , but the docs around that didn't really help me.

This seems like a problem with a simple solution , but I cannot work out what it is.

EDIT1.

To clarify , my MySQL queries are very very basic. One of them is literally just

SELECT * FROM aboutPage

Where aboutPage contains 1 row and 5 columns of tiny data. This runs lightning quick as expected in PHPMyAdmin locally , but has a slow load when accessed remotely.

The main app that runs the API also runs fast . Its literally just when trying to access data from another IP/DNS that its slow.


texts: Understanding mysqltuner Recomendations w/ Unused Catalogs

texts:
I have a MySQL 5.0.77 instance running with three catalogs in it.

Two of the catalogs contain all InnoDB tables and are actively used.

The third is all MyISAM and is basically unused, and is only still in the server as a precaution.  Basically, it is an old version of one of the other catalogs that used to be referenced to ensure that data migration was correct.

I have been running mysqltuner periodically so I can get a decent baseline configuration before I begin real profiling on whole system.

Even though I am not querying or even opening connections to the MyISAM catalog, is its presence throwing off the mysqltuner recommendations?

For example, can I truly ignore the warnings about key_buffer_size, or will bad values for this negatively impact the whole server even when I am not really using MyISAM?

Is having this unused catalog negatively impacting the memory footprint of the server?  If so, is there any way to mitigate this, other than moving the catalog somewhere else and doing a DROP?

Thanks.


texts: How to sync daily the prod database to dev database in Azure SQL Server service?

texts:
I have two Azure SQL Database, prod and dev.
How can i daily replicate the prod database into dev database?
I have tried to automate it by exporting and importing the BACPAC file but it takes too long time.
There is Data Sync service in Azure but I am not sure if it works in one direction.
Do you have any suggestions?


texts:
I'm currently doing a MongoDB aggregation but then have a query looking for all documents where a certain field, whose value is an array, is empty.

obs = db.collection.count({'things.titles': {'$ne': [] }})

To ensure this query uses an index, do I just need to do something like this?:

db.collection.ensureIndex({'things.titles': 1})

I understand this creates a multikey index, but it still takes a very long time (more than an hour) on a collection sized at 4739208 documents.


texts: Oracle 11g data guard in windows physical standby

texts:
I was wondering if there is a difference in setting up physical standby in windows and in Linux flavors.
I don't find any documents or guides in setting up data guard in windows environment.
All I could find was this shady blog
http://dbmentors.blogspot.com/2013/05/configuring-oracle-11gr2-data-guard.html?m=1


texts: How can I output more than 256 characters to a file?

texts:
I am using the following command to write the output from a stored procedure to a file for further processing:

DECLARE @shellCommand VARCHAR(2000)
SET @shellCommand = 'SQLCMD -S ' + @@SERVERNAME + ' -d ' + @dbName + ' -U ' + @UserName + ' -P ' + @Password + ' -Q "' + @sqlCommand + '" -s "," -h -1 -k 1 -W -o "' + @outputfileName + '"'

@sqlCommand is set correctly, to "EXEC StoredProcedureName parameterValue" and when run independently apparently produces the correct data.

The problem I'm having is that one of the fields contains text greater than 256 characters long and is getting truncated when fed through the SQLCMD command.

On reading the documentation I see that the default column width is 256 characters. So I'm looking at using the -y or -Y option to allow more than 256 characters to be output. However, these options aren't compatible with the -W option which I was using to remove trailing spaces from the output. I started by using -y 0 (despite the performance warning - once I get the output working I can look at the performance). However, that produces some strange results.

The file consists of a header row followed by the data rows and should look something like this:

First,ABC,Number,String,ReallyLongString,...
A,0123,14.99,"Short string","Longish string",...
B,0456,23.99,"Normal string","Really, really long string that's causing problems",...

With the -W option the file format is correct but the really long string is truncated. The reason we found it was because the trailing " was missing and the file wasn't being read properly.

With -y 0 the really long string is getting output but large numbers of spaces are being added to apparently random columns. We're getting something like this:

First,ABC ,Number    [...]      ,String,ReallyLongString,...
A    ,0123,14.99     [...]      ,"Short string","Longish string",...
B    ,0456,23.99     [...]      ,"Normal string","Really, really long string that's causing problems",...

(There are many, many more spaces between "Number" and the next column as represented by the "[...]", I'm just showing a few).

There are more numeric values that have to be formatted in a similar way and it does appear that it's these that are causing the extra spaces after the value and before the next comma. We can live with a few extra spaces, but not as many as this as the resultant file is too large to be read by the target program.

The data is generated by a stored procedure that looks like this:

SELECT
    'First' AS First,
    'ABC' AS ABC,
    'Number' AS Number,
    'String' AS String,
    'ReallyLongString' AS ReallyLongString,
    ...

UNION ALL

SELECT
    'A' as First,
    Column1 as ABC,
    REPLACE(FORMAT(Column2, 'N2', 'en-GB'), ',', '') as Number, -- to get the format correct
    '"' + Column3 + '"' as String,
    '"' + Column4 + '"' as ReallyLongString,
    ....
FROM Table
WHERE <condition>

I'm thinking that the problem is the output from the stored procedure. I only added the -W option to the SQLCMD because of the extra trailing spaces being output, but looking at the stored procedure I can't work out where they are coming from. I changed the number formatting to include RTRIM:

RTRIM(REPLACE(FORMAT(Column2, 'N2', 'en-GB'), ',', ''))

but that appeared to make no difference.

Is there something I can do to the stored procedure or is there a combination of options to SQLCMD that will produce the desired output? Or am I going to have to find some other way of producing this file?


texts:
I built the following SQL Server query, but it is encountering the anti-semi join defect in SQL Server 2005 which results in inaccurate cardinality estimates (1 -- urgh!) and runs forever. Since it is a longstanding production SQL Server I can't easily suggest to upgrade versions, and as such I cannot force the traceflag 4199 hint on this specific query.

I'm having a hard time refactoring the WHERE AND NOT IN (SELECT). Can anyone care to help? I've made sure to try and use the best joins based on clustered key pairs.

SELECT TOP 5000 d.doc2_id
    ,d.direction_cd
    ,a.address_type_cd
    ,d.external_identification
    ,s.hash_value
    ,d.publishdate
    ,d.sender_address_id AS [D2 Sender_Address_id]
    ,a.address_id AS [A Address_ID]
    ,d.message_size
    ,d.subject
    ,emi.employee_id
FROM assentor.emcsdbuser.doc2 d(NOLOCK)
INNER JOIN assentor.emcsdbuser.employee_msg_index emi(NOLOCK)
    ON d.processdate = emi.processdate
    AND d.doc2_id = emi.doc2_id
INNER LOOP JOIN assentor.emcsdbuser.doc2_address a(NOLOCK)
    ON emi.doc2_id = a.doc2_id
    AND emi.address_type_cd = a.address_type_cd
    AND emi.address_id = a.address_id
INNER JOIN sis.dbo.sis s(NOLOCK) ON d.external_identification = s.external_identification
WHERE d.publishdate > '2008-01-01'
    **AND d.doc2_id NOT IN (
        SELECT doc2_id
        FROM assentor.emcsdbuser.doc2_address d2a(NOLOCK)
        WHERE d.doc2_id = d2a.doc2_id
            AND d2a.address_type_cd = 'FRM'
        )**
OPTION (FAST 10)

Note that the Employee_MSG_Index table is 500m rows, doc2 is 1.5b rows, SIS is ~500m rows.

Any help would be appreciated!


texts: Using Policy-Based Management to check backup history for an Availability Group database

texts:
I like using Policy-Based Management to do some simple "everything's okay" sanity checks and email me if something goes out of spec. Typically, I validate the time since the last full backup, both to verify backups are running on schedule, and also to make sure that newly created databases are being included in backups. This works perfectly fine on a standalone server.

However, we recently deployed a two-node Always On Availability Group. It's configured to run backups on the current primary node. As you're probably aware, backup history is stored in msdb on the server that performs the backup. This causes a problem with the @LastBackupDate property, which only checks the local backup history for the server the policy is being evaluated on. After a failover, the backup policies almost immediately go out of compliance, as the backups have been running from the other server for however long it was primary (most likely for longer than the span that the policy is checking).

Is there any reasonably simple way to make these policy checks Availability-Group-aware? Or am I going to have to look for some other backup monitoring solution?


texts: How to determine optimal value for pg_stat_ramdisk_size on Amazon RDS?

texts:
Amazon announced they support a RAM disk for the stats_temp_directory in PostgreSQL on RDS.

This sounds like a good idea so I want to enable it. Now I'm wondering how to determine the optimal size for the RAM disk. Is there a way to show the current size of the directory on disk? The file system of PostgreSQL on RDS is not accessible, isn't it?
Also, what happens if the RAM disk is configured too small?


texts: I have a View whose query plan appears to be cached, is there a way to force the View's plan to be recalculated on each access?


texts: Error while creating Extended Events on 2008 SQL Server

texts:
I am trying to create an extended event to capture the login information of my server. While creating an extended event on SQL 2008 using the below query

CREATE EVENT SESSION [SA_Monitor] ON SERVER
ADD EVENT sqlserver.login(
ACTION(sqlserver.server_principal_name,sqlserver.sql_text,sqlserver.username)

I am getting the following error.

  The event name, "sqlserver.login", is invalid, or the object could not
  be found.

On Initial investigation I found that creating extended events using SSMS was introduced from 2012 and the only method to create EE is through SQL query. Is there a generic query that can help me capture this information in the 2008 server.


texts: Pre-production database replication solution

texts:
I would like to start by specifying I am no DBA and have basic understanding of advanced DB concepts. I am trying to find the optimal solution to replicate our production database to our pre-production environment automatically.

Our setup is as follow in production:

Batch runs at night and populate staging database
Once complete then our Mart database is loaded using staging data.
Mart data does not change until the next load the following day.
Mart data is currently 200Gb and we have planned growth up to 1TB
over a few years.
DB currently set to simple recovery mode and backup taken every night.

What I would like to achieve is

One off replication of Mart data to separate pre-prod server
Minimum impact on prod server during business hours or if prod impacted this has to be performed outside of business hours.
Ability to run batch in pre-prod on an adhoc basis to test batch changes from recent prod data. This means pre-prod mart cannot be read only.

Currently this process is manual and due to DB size and resources availability cannot always be done as required.
In order to execute performance tests and reconciliation after changes it would be tremendously useful to have a daily process which can achieve this automatically.

Any personal experience or recommendation on the best way to achieve this would help.
Thanks.


texts: Better way to get months contained in a period for querying sales tables

texts:
I'm trying to automate some sales processing that is currently largely manual. I get the sales by month expressed as
201701, 201702 etc.

Part of what I'm trying to accomplish is better re-use of aggregation queries for historical data, currently there are just a lot of queries saved in the cloud. I need to able to pass in a year and quarter, and get a pattern to match against the sales data. I'd like to accomplish this using SQL's built in datetime functions rather than a case statement and casts.

What's a better way to do the following, that doesn't rely on string manipulation?

-- the @year and @quarter will be passed into a SP
declare @year int = 2017
declare @quarter int = 2

declare @name_timePeriod nvarchar(20) = 'Qtr'+ ' ' + cast(@quarter as nvarchar(2)) + ' ' + cast(@year as nvarchar(4))

declare @contained_periods nvarchar(max) =

case @quarter
    when 1 then cast(@year as nvarchar(4)) + '0[1-3]'
    when 2 then cast(@year as nvarchar(4)) + '0[4-6]'
    when 3 then cast(@year as nvarchar(4)) + '0[7-9]'
    when 4 then cast(@year as nvarchar(4)) + '1[0-2]'
end


texts: SQL Server clustered index, index balancing and insert performance using NewID

texts:
I have a large (6db) trace table. It has a clustered key (DateTime) which is created through GETDATE().

The connection pool for connections to this database/table rises as high as 50 on average across a cluster of 10 computers, so on average we have at ~500 concurrent connections attempting to insert.

The database fits in memory and hardly any IO is seen at all.

I am trying to figure out whether under sustained INSERT load the clustered index gets to a point where it rebalances the tree, and whether this will cause a slowdown in the number of inserts that the system can sustain.

There is some question in my mind as to whether the rebalancing an index is something SQL Server does on a clustered index (and even on a non-clustered index).

Questions-

Are there any reasons for periodic/cyclic slow-down of insert performance?
Do rebalance operations automatically trigger on clustered indexes?
Do rebalance operations automatically trigger on non-clustered indexes?

Other info

SQL Server 2008
Really BIG server - 256Gb, 40 cores, 40mbit LAN...


texts: Convert existing subscriber Database to a publisher database. Current publisher will be subscriber

texts:
We have a transactional replication setup. The publisher is on a separate SQL Server and the distributor and the subscriber together on the other server. The replication was done using restore from backup from the publisher.

Now, I have to make my subscriber database as my main database, which will also be my publisher. And the server that have my current publisher database will now be my subscriber. I have several websites using the database said. I stopped the replication agent and tried to 'point' the websites in the subscriber.

However, insert commands are failing on my subscriber (even after stopping replication) with errors relating to primary keys which is also set as auto-increment. I checked the table schema in the subscriber database and the auto increment as well as the identity specification for that certain column is set to yes.

My question is what are the changes need to be done if I want my current subscriber to be a publisher (or main database for that matter)? Will i need to take backup again from my new publisher if I will make a subscriber from my old publisher database?

If it helps, our current database is now 100 GB in size. There is a table having 40m records and changing a schema, to drop and and recreate the whole table 'may' not be an ideal option for us now.


texts: CLOB vs Varchar2 performance in this case?

texts:
From java application, I need to store the string which can be more than 4000 characters. I just need to store it. Then person having DB access can query and view it. That column won't be accessed from java application. Also I do not have any requirement to index it.

My understanding is varchar2 is better is in performance than CLOB when we there is a need to access the CLOB datatype or there is need to index it. Is that correct ?

My question - is there any impact in term of performance when we just need to store it and access it in sql developer throigh query ?


texts: Database user permissions: GRANT VIEW DEFINITION but DENY on some object types

texts:
I'm trying to figure out a combination of permissions that will allow a partner to see basic db schema but not expose objects including code. So,

VIEW and SELECT on TABLES (including keys)
Prevent/DENY on Views, Stored Procs, Functions.

I've thought about going through and denying on individual objects, but this won't protect any that are newly created.

Added consideration - I need to get this to work in SQL Azure.


texts: Are toast columns compressed also in shared_buffers?

texts:
Reading here I cannot find a clear answer:
http://www.postgresql.org/docs/9.1/static/storage-toast.html

I need to know if setting storage to EXTERNAL for a particular column, I can gain a little performance. Most of my data is retrieved from SHARED_BUFFERS.

I'm curious to know if decompression is done for every query working on that field.


texts: MongoDB replica set: members with different versions of mongod

texts:
The primary is running mongod 3.0.6 and the secondaries running mongod 3.0.7

is a mixing of versions allowed in the same replica set?

For upgrade it's best practise to not all nodes the same time.


texts:
I would like to understand what the SQL standard says about parallel queries and race conditions. If there is no standard, then I would be interested in the approach adopted by popular databases.

I understand that 2+ read-only queries can run in parallel, but can a read/write or write/write pair run in parallel as well?

For example, suppose a simple aggregation such as

SELECT AVG(col1) FROM tbl;

And while that is running, I will issue an

INSERT INTO tbl (col1) VALUES (3);

Is the INSERT executed while the SELECT is running or will the INSERT be blocked until execution finishes?

In other words, are race-conditions possible in SQL?

Additionally, I would appreciate a discussion on how row, page and table locks are used in this context.

Thanks


texts: SQL Server 2000 Named Instance cannot connect remotely

texts:
To facilitate a migration I installed SQL Server 2000 Standard Edition and Service Pack 4 as a named instance on server with an existing instance of SQL Server 2008R2 RTM.

The issue I have is connecting to the named instance outside of the server itself.

This is what worked:

Connecting remotely while specifying the port number in the connection string (SERVER\INSTANCE,PORT)
Connecting locally using just SERVER\INSTANCE

The SQL Server Browser service is enabled and running for SQL Server 2008R2. In the event log there is a warning from the browser service that states:

The configuration of the SQL instance *INSTANCE_NAME* is not valid.

I have been troubleshooting this issue for awhile and cannot find any solid information.

Thanks in advance for any help!


texts: Is there a configuration setting for how many pages ANALYZE will scan?

texts: Is there a setting that controls how many records/pages are scanned by the PostgreSQL ANALYZE command? It looks like by default it scans 30,000 pages and 30,000 records. The PostgreSQL Server Configuration Documentation lists tons of options, but I don't see anything specific to ANALYZE.


texts:
I need to research what caused a high load in our Oracle database some days ago. I wish to see a list of  of high-load SQL statements. We don't use any Oracle tools like  ADDM or AWR.

I tried to research AWR tables. Could someone give me any clue which criteria should I put in query?


texts: Optimization: Delete with Top and minimize the where clause date range

texts:
I have a delete statement that get run repetitive until all the data are removed.

Delete TOP (1000) From Log Where CreationDate < '2011-Dec-31' AND pKey NOT IN (...)

I am considering of truncating the where clause so that the pseudo code is like:

while(@parameter < '2011-Dec-31' AND rowCount > 0)
{
 Delete TOP (1000) From Log Where CreationDate < @parameter AND pKey NOT IN (...)
 SET @parameter = DATEADD(day, 1, @parameter)
}

Would this help speed up the delete because it doesn't have to do cache as much data, or it doesn't matter that much since I have an index on CreationDate?

Thanks


texts: Oracle: how to query a table if I know only its (non-default) tablespace?

texts:
So. SQLPlus simply gives for my first try (after a little bit of postgresql experience):

SQL> SELECT * FROM MYTABLESPACE.MYTABLE;
SELECT * FROM MYTABLESPACE.MYTABLE
              *
ERROR at line 1:
ORA-00942: table or view does not exist

I've tried also SELECT * FROM MYTABLESPACE$MYTABLE (intuitive try) and simply SELECT * FROM MYTABLE as well, but nothing succeed. Googling for that gives a lot of info about the tablespace management, but nothing from this trivial question.

What to do?

Extension #1: Well, it looks there was really a minor misconception about the tablespaces and the schemas. But the problem is still very visible: I have access to the dba_tables table, I have access to one of its records, I want to query table described by that, and it doesn't work.

Extension #2: Here is what I did. I called sqlplus with the system account (sqlplus system/****):

SQL> SELECT * FROM (SELECT * FROM dba_tables WHERE tablespace_name='MYTABLESPACE') WHERE ROWNUM<=1;

OWNER                          TABLE_NAME                     TABLESPACE_NAME                CLUSTER_NAME                   IOT_NAME                       STATUS     PCT_FREE   PCT_USED  INI_TRANS
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------- ---------- ---------- ----------
 MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS LOG B   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- --- - ---------- ---------- ------------ ---------- ---------- -----------
AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE                                   INSTANCES                                CACHE                TABLE_LO SAMPLE_SIZE LAST_ANA PAR IOT_TYPE     T S
------------------------- ------------------- ---------------------------------------- ---------------------------------------- -------------------- -------- ----------- -------- --- ------------ - -
NES BUFFER_ FLASH_C CELL_FL ROW_MOVE GLO USE DURATION        SKIP_COR MON CLUSTER_OWNER                  DEPENDEN COMPRESS COMPRESS_FOR DRO REA SEG RESULT_
--- ------- ------- ------- -------- --- --- --------------- -------- --- ------------------------------ -------- -------- ------------ --- --- --- -------
EXAMPLEOWNER                   MYTABLE                   MYTABLESPACE                                                                                VALID            10                     1
       255          65536     1048576           1  2147483645                                         NO  N
                                                       1                                        1                                   N                ENABLED                       NO               N N
NO  DEFAULT DEFAULT DEFAULT DISABLED NO  NO                  DISABLED YES                                DISABLED DISABLED              NO  NO  YES DEFAULT

SQL> SELECT * FROM MYTABLE;
SELECT * FROM MYTABLE
              *
ERROR at line 1:
ORA-00942: table or view does not exist


texts: Restore server instance using logical backup and wal files

texts:
Is it possible to restore a database instance using logical backup and wal files?

A senior SQL Server DBA asked me to implement below scenario in PostgreSQL

took the logical backup using pg_dumpall of the master then do failover after some time.
Now restore database instance using the logical backup of primary + wal files of primary + wal files of secondary.


texts:
I'm developing a web site for a model agency. I have a "model" table with the details about each model (this is just an example):

CREATE TABLE model {
    model_id INT PRIMARY KEY,
    name VARCHAR(100),
    hair_color_id TINYINT,
    eyes_color_id TINYINT,
    skin_color_id TINYINT,
    city_id TINYINT,
    another_id TINYINT,
    blabla_id TINYINT,
    ...
}

The hair_color_id is a FK of the following table:

CREATE TABLE hair_color {
    id INT PRIMARY KEY,
    name VARCHAR(100),
    visible TINYINT
}

The same applies to eye_color_id and most of the other fields. This way I keep my "main" table small while I can use the other tables to store the available options and list them dinamically in the HTML dropdown lists. They are also handy when adding new models to the "model" table, I don't want people can store wrongs ids either by accident or maliciously, so the constraints keeps my model table with valid data. Of course, when I want to show the models profile I have to make many many JOINs and this will happen a lot.

Having that in mind, my question is: which storage engine would you recommend me to use for these tables (eye colors, hair colors, etc)? I'm using InnoDB (because of its cache function) but I think that maybe it's better to use MEMORY storage engine. Or MyISAM because they will be practically read only. Do you think that I should change this schema and store these values in a PHP file so it's not neccesary to make the JOINs?


texts: Postgres 9.6 reload or restart after config changes

texts:
I need to change a couple of log config settings in postgresql.conf. How do I know which parameters need a restart or reload?

Is there a list of parameters that need a restart?


texts: ERROR 1064 (42000): You have an error in your SQL syntax - Cannot understand what the error means

texts:
I'm trying to import an SQL db, exported from another place. I have the SQL create script. I am not sure what might be going wrong...
My mariadb version is
select version();
+----------------+
| version()      |
+----------------+
| 10.5.8-MariaDB |
+----------------+
1 row in set (0.000 sec)

Table create script:
-- phpMyAdmin SQL Dump
-- version 5.0.4
-- https://www.phpmyadmin.net/
--
-- Host: db
-- Generation Time: Jan 04, 2021 at 05:59 PM
-- Server version: 8.0.22
-- PHP Version: 7.4.11

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `mytestdb`
--
CREATE DATABASE IF NOT EXISTS `mytestdb` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE `mytestdb`;

-- --------------------------------------------------------

--
-- Table structure for table `categories`
--

DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories` (
  `id` int NOT NULL,
  `category` varchar(100) GENERATED ALWAYS AS (json_unquote(json_extract(`json`,_utf8mb4'$.category'))) VIRTUAL NOT NULL,
  `popular` enum('true','false') GENERATED ALWAYS AS (json_unquote(json_extract(`json`,_utf8mb4'$.popular'))) VIRTUAL NOT NULL,
  `imageUrl` varchar(255) GENERATED ALWAYS AS (json_unquote(json_extract(`json`,_utf8mb4'$.imageUrl'))) VIRTUAL NOT NULL,
  `json` json NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Error:
ERROR 1064 (42000) at line 33: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NOT NULL,
  `popular` enum('true','false') GENERATED ALWAYS AS (json_unquote(...' at line 3


texts: PostgreSQL - query first on indexed column?

texts:
I have a 2 tables - listings and users.

CREATE TABLE users (
  id text PRIMARY KEY,
  internal_id serial,
)

CREATE TABLE listings (
  id bigserial PRIMARY KEY,
  created_at timestamptz default current_timestamp,
  posted_by text REFERENCES users ON UPDATE CASCADE ON DELETE CASCADE,
)

When I want to select a listing, I want to first query by listing.id because there is a b index on it and it will give the fastest look up. Then I want to filter out by user_id. In addition, join the users table so I can have both the listing record and the user.internal_id from posted_by.

This query works:

SELECT
    *, users.internal_id
FROM
    listings
    JOIN users ON listings.posted_by = users.id
WHERE
    listings.id = 2
    and posted_by = '1009280753550922';

but I do not think it is querying by listing.id, which is indexed, for best performance:

dev_dolphin_db=# explain SELECT *, users.internal_id FROM listings JOIN users ON listings.posted_by = users.id WHERE listings.id = 2 and posted_by = '1009280753550922';
Nested Loop  (cost=0.14..10.46 rows=1 width=1560)
   ->  Seq Scan on listings  (cost=0.00..2.29 rows=1 width=840)
         Filter: ((posted_by = '1009280753550922'::text) AND (id = 2))
   ->  Index Scan using users_pkey on users  (cost=0.14..8.16 rows=1 width=720)
         Index Cond: (id = '1009280753550922'::text)

How could I build a query that would explicitly query on the indexed listings.id first?


texts: Any reasons at all *not* to use MySQL in this scenario

texts:
I want to know if there is any reasons at all not to use MySQL in this scenario.

  2 servers behind a virtual IP with MySQL master-master replication

Goal: redundancy, want this to run 24/7 servicing a liferay cluster

I have plenty of reasons why to use this (1 I want your most pessimist thoughts.

(1

We are using this already in an similar situation and are considering using something else for a new setup. We have access to s MSSQL cluster that are more than capable of handling the 24/7 demand.

Top reasons to use MySQL (as you see they are manly "political")

It's how we have done it in the past, so less surprises expected
We(2 will have more control over performance as we can add memory/cpu to the (virtual) db-servers ourselves when we want
No need to migrate export data from MySQL to a new DB

(2

The MSSQL cluster is run by the "windows"-crew whilst I belong to the "*nix"-crew. So while the cooperation between us is good there is naturally a bit more fuss/delay when you have to ask someone else to do something rather than doing it yourself.

Update

Two days after this question was asked the decision what solution to use was made, so any answers(3 that would have helped make that decision is now not needed (this time anyhow).

If anyone want to generalize this question (as Jeff suggested) please do.

(3

When this update was written there was only one answer


texts: Remove Dot at end of line of the string in SQLserver

texts:
I would to like to remove the dot at end of the line alone if exists in the string in SQL server.

Example:

ABC Private's co., ltd.

Required output:

ABC Private's co., ltd


texts:
I have a SQL database that is hosted on Azure. The problem is that the size is getting out of control, I can see up to 99% fragmentation in the Primary Key clustered indexes.

I'm able to rebuild all other indexes with online=on option and it won't affect performance. The size of one of the PK Clustered indexes is greater than 200GB, and for this one a REBUILD...WITH (ONLINE=ON) causes locking.

We do have users from all timezones accessing the site so really, I'm unable to find a time where I can rebuild the index offline.

What is the best strategy to rebuild large indexes without having a downtime in the site?

I believe reorganize won't help since fragmentation is 99%. The problem is that the table gets locked even with online. The main problem is that the index is greater than 200GB. The primary key is an integer.


texts: Reviewing Stored Procedure template for handling transaction?

texts:
I've prepare a template for SQL Server stored procedures which will have Insert, delete, Update transaction.

I have a stored procedure to log error in catch block in a table.

How can I improve my stored procedure to handle transactional error or any kind of error
where stored procedure should not give error on screen.

Can this be improved to handle transaction in proper way or it sufficient?
First Begin tran block or Begin Try block?
Rollback in catch block if @@trancount > 0 sufficient?
Use of Xact_abort on in every Insert, delete, update transaction procedure is ok?

Code:

ALTER PROCEDURE [dbo].[DB_INSERT_MY_LOG]
(@Param1 varchar(30))
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE          @ErrNumber         NVARCHAR(100)
    ,@ErrDescription    NVARCHAR(4000)
    ,@ErrorProcedure    NVARCHAR(200)
    ,@ErrState          INT
    ,@ErrSeverity       INT
    ,@ErrLine           INT
    ,@ErrTime           DATETIME

    BEGIN TRAN
    BEGIN TRY
                   --Insert or Update or Delete or (Insert & Update together and vice  versa) & multiple insert into different tables.
    COMMIT TRAN
    END TRY

    BEGIN CATCH

    IF (@@TRANCOUNT > 0)
    ROLLBACK TRAN

    SET @ErrNumber          = ERROR_NUMBER()
    SET @ErrDescription = ERROR_MESSAGE()
    SET @ErrorProcedure    = ERROR_PROCEDURE()
    SET @ErrState           = ERROR_STATE()
    SET @ErrSeverity        = ERROR_SEVERITY()
    SET @ErrLine            = ERROR_LINE()
    SET @ErrTime            = GETDATE()

    EXEC    MyErrorLogInsert
    @ErrorNumber                = @ErrNumber
    ,@ErrorDescription          = @ErrDescription
    ,@ErrorProcedure            = 'DB_INSERT_MY_LOG'
    ,@ErrorState                = @ErrState
    ,@ErrorSeverity             = @ErrSeverity
    ,@ErrorLine                 = @ErrLine
    ,@ErrorTime                 = @ErrTime

    END CATCH

    SET NOCOUNT OFF;
    SET XACT_ABORT OFF;


texts: Can Postgres partition table by column values to enable partition pruning

texts:
I'm using Hive currently and it has partitioned tables.
Let's say this is my data:
Country City DailySale
USA     NY   200.2
USA     SF   190.5
CAN     TR   390.4

The I can define my table

create table SALES_DATA(
Country    STRING,
City       STRING,
DailySale  BIGDECIMAL
) PARTITIONED BY (Country, City)

Then I create catalog structure on HDFS:
SALES_DATA/
          Country=USA/
                      City=NY/
                              data-file-0001
                              data-file-0002
... the rest omitted for brevity...

Then partition pruning can be applied for queries like
SELECT SUM(DailySale) FROM SALES_DATA
WHERE  Country='USA' and City='SF'

Hive engine will prune all partitions except one. Super effective and optimal.
I would like to implement smth similar on Postgres. i'll have immutable tables with aggregated data and would like to apply smth similar for them.
I'm trying to go through it
https://www.postgresql.org/docs/13/ddl-partitioning.html
but don't really follow if it's what I need.


texts: Can I improve my duplicate-detection queries?

texts:
I have a DB table with US domestic flight data which I am in the process of sorta-kinda-de-duplicating. The schema appears here, but it is probably not very important. There are no perfectly-duplicate records; rather, I have a several fields which almost form a unique key: year_, month_, dayofmonth, uniquecarrier, flightnum, origin; and under 0.1% of the records share the same values on these fields with other records.
What I want to do is isolate these "pseudo-duplicates" or key-violators, i.e. pairs of records which have the same values on these columns.
Here's what I'm currently doing:
CREATE TEMPORARY TABLE dupe_keys AS (
    SELECT year_, month_, dayofmonth, uniquecarrier, flightnum, origin
    FROM (
        SELECT count(*) AS cnt, year_, month_, dayofmonth, uniquecarrier, flightnum, origin
        FROM ontime
        GROUP BY year_, month_, dayofmonth, uniquecarrier, flightnum, origin
    ) AS t WHERE cnt > 1
) ON COMMIT PRESERVE ROWS;

This query takes quite a lot of time (relative to an ideal possible implementation outside of a DBMS based on sorting the key columns). I then run:
CREATE TABLE dupes AS (
    SELECT  o.year_,o.quarter,o.month_,o.dayofmonth,o.dayofweek,o.flightdate,o.uniquecarrier,o.airlineid,o.carrier,o.tailnum,bunch_of,other_fields,go_here
    FROM ontime AS o, dupe_keys AS dk
    WHERE
            o.year_         = dk.year_
        AND o.month_        = dk.month_
        AND o.dayofmonth    = dk.dayofmonth
        AND o.uniquecarrier = dk.uniquecarrier
        AND o.flightnum     = dk.flightnum
        AND o.origin        = dk.origin
    ORDER BY o.year_ ASC, o.month_ ASC, o.dayofmonth ASC, o.uniquecarrier ASC, o.flightnum ASC, o.origin ASC
);

which obviously takes less time.
I would like to achieve the same, faster, with better queries. Alternatively, I wouldn't mind something like a pair of tables of the same size, each with one of the two dupes out of a dupe set.
Notes:

You may assume that no combination of key-column values appears more than twice. It's either once or twice.
I'm using MonetDB, a columnar in-memory DBMS. hence the syntax specifics.
I hope my question is not DBMS-specific, but in case it is - answers based on the behavior of other DBMSes are still relevant, just please state which DBMS you're assuming.
This query is run when the data set is loaded, rather than repeatedly, So pre-computation which takes even more time is not relevant.


texts: Upgrading to SQL Server 2014 SP3 _ Clarifications help needed

texts:
We have SQL Server 2014 SP2 GDR with below:

Availability Groups
TDE enabled for structured data
EFS enabled for unstructured data

Now the DBs are with AOAGs and encrypted ,  I am planning to upgrade current SQL Server 2014 SP2 GDR to SQL Server 2014 SP3. Can anyone help me with any issues / pre-requisites or anything that can happen adversely.

Though I am planning first upgrade DEVELOPMENT / STAGING , please share your thoughts as I need to upgrade my PROD . I am little worried as the DBs are with TDE + EFS . Does encryption has any linkage with SP3 upgrade.


texts: Min/ Max dates based on results of query

texts:
Need to find the Max and Min dates using the results of a query. The following query is working but have to add additional code to find the Max/ Min dates based on its results (results of dates from last 2 AND statements). Using Oracle SQL Developer.

SELECT SO.COM_DATE, SO.RAISED_DATE, SE.SERV_NO, SO.OR_NO
FROM S_ORDER SO, S_SERV SE
WHERE SO.CU_NO='286243' AND SE.SERV_NO='69780'
AND SUBSTR (SE.ACCT_NO, 1,8)=SO.CU_NO
AND SO.RAISED_DATE +4/24 BETWEEN TO_DATE('2017-11-01','YYYY-MM-DD')
AND TO_DATE('2018-07-30','YYYY-MM-DD')

Tried to add the Max/ Min statements to the end  but getting an Invalid Statement error. Thanks for any help.

Results of the query will therefore be:

COM_DATE | RAISED_DATE | SERV_NO | OR_NO
2018-07-05 | 2018-07-04 | 69780 | GHYUYR
2018-06-27 | 2018-06-08 | 69780 | HHUYTT
2018-03-10 | 2018-02-04 | 69780 | JJUHYT

Therefore, after the Min/ Max code added, the results should ONLY return:

COM_DATE | RAISED_DATE |
2018-07-05 (max)| 2018-02-04 (min)


texts:
I suspect that my Sql Server is using CONVERT_IMPLICIT a lot because many of my fields are set as string in NHibernate while they are set as varchar in database.
Is there any option to detect when server is using The CONVERT_IMPLICIT function?

Thanks


texts: Linux Volumes for SQL Server + Docker on Linux

texts:
I am testing out a POC project for SQL on Docker containers on Linux and wanted some background info on how to request the storage on the Linux VM. The plan is to spin up 10 containers with SQL 2019 on storage of at least 10 TB
My question is specific to where the sql containers are stored within the Linux VM. This will help me to request storage capacity for that specific mount point accordingly
Thank you


texts: Any way to mitigate the large server load during LOAD DATA LOCAL INFILE operations?

texts:
I'm using MySQL LOAD DATA LOCAL INFILE to load csv files, and have noticed that during these loads, server output speed to other users diminishes eg long page download times, logins timeout. The LOAD DATA LOCAL INFILE operations themselves are very fast and effective.

What is the best way to approach this type of problem? I'm currently on a reasonably powerful VPS server (operates well when not processing LOAD DATA LOCAL INFILE) that contains both the main and mysql server.

Thanks for your time and suggestions.


texts: Batch process to delete table from SQL Server backup files

texts:
I have a set of full backups from a same SQL Server database saved in different files. For example:

01.my-database.bak
02.my-database.bak
03.my-database.bak

I want to delete a specific table from those files to save disk space. There is a batch process to do that?


texts: What can be the cause of sudden leaps in identity? How can it be undone?

texts:
In one of our clients' (testers') databases the Identity column of a SQL Server Local Db datatable looks thus:

So, most of the time the identity increases by one, but sometimes the identity jumps up by 10,000. Possibly this coincides with moving the database between computers and reattaching them to SQL Server Local Db.

What may be related is the fact that this is an Entity Framework Code First generated database with Migrations. At one time there was a migration that executed the following Transact SQL: Sql("DBCC CHECKIDENT (Sessions, RESEED, 10000)"), This database may have seen this migration. However, I deleted the migration afterwards and the _MigrationsHistory table in the database does not have a record for this particular migration.

If I have a look at the design for the table it specifies an identity of (1,1).

So, does anyone have some inkling about what is going on? Can I stop the 10,000 jumps?

EDIT:
The reseed had nothing to do with it. As indeed can be read here the jump by 10,000 is a feature of SQL server for identity columns of type big int (64 bit).

Why I bother? The SQL Server Localdb engine is connected to a legacy VB6 application that we are migrating to .Net. The VB6 code is geared towards using 16 bit identity fields. Jumps of 10,000 at a time are disastrous...


texts: How we can do full text search and facets stuffs on BigQuery?

texts:
In the future, we will have millions ofs record in our web product. So we had used BigQuery for data storage and analysis. We have to build filters on our search page like Flipkart and Amazon provide product filters.

Basically, we need different filters on left side of our search page. With help of these filters, we can see our desired results. Every filters have their count, i.e., record counts in that category/term. So we have to build queries in such way that they give result and count of every category(term). Here, "category" means different types filter on different columns as we see on e-commerce sites, e.g., Flipkart, Amazon etc.

  Faceted search (also called faceted navigation, guided navigation, or parametric search) breaks up search results into multiple categories, typically showing counts for each, and allows the user to "drill down" or further restrict their search results based on those facets.

Is there any framework/plugin available like Solr which can be used with BigQuery to provide above desired functionality?

Is BigQuery not suitable for above purpose? Do we need to stick with any RDBMS database (e.g., PostgreSQL, MySQL, etc.) and search engine (e.g., Solr, Elasticsearch, etc.) for this purpose?


texts: Does the term "page" have the same meaning in DBMS and OS jargons?

texts:
I am learning the data format of DBMS and found we have a term: page.

Pages are the fetched data in main memory from external storage (eg. a disk, SSD) and several pages would be "cached" firstly to the Low Level Cache, when these pages are acquired by a query.

That reminds me of the same term page used in operating systems and computer architecture. Some formal descriptions about this "paging" from Wiki are:

  Paging is a memory management scheme by which a computer stores and retrieves data from secondary storage for use in main memory. In this scheme, the operating system retrieves data from secondary storage in same-size blocks called pages. Paging is an important part of virtual memory implementations in modern operating systems, using secondary storage to let programs exceed the size of available physical memory.

So it comes to the first question:

Does the term page carry the same meaning in DBMS and in OS?

If yes, can you explain question 2 a little bit?
If not, can you explain why? Do they have different way to be implemented?

A step more:

In the lecture we learn the N-ary Storage Model (NSM or slotted
pages) and PAX (Partition Attributes Across) refer to this paper from Prof. Ailamaki.
Assume page has the same meaning in DBMS and OS, why do we specify the NSM and PAX only in DBMS? Can this page format be portable to OS?
(About the paper will be helpful, that you just go quicklythrough the data layout and the figures.)

The question 2 is the main reason, that I raise question here and start to ask myself, why NSM, PAX pages are special for DBMS. This question is more general and not focused on any particular DBMS in mind.


texts: Is it normal for it to take up to a minute for an execution plan to be generated (even an estimated one) on simple queries?

texts:
Once in a while (but uncommonly) my SQL server will take what seems like an odd amount of time to generate an execution plan. It just took 37 seconds to generate an estimated execution plan for the following fairly simple query:

SELECT *
FROM Table1
WHERE IndexedIntField1 = 12345
    AND NonIndexedVarcharField IN ('Value1', 'Value2', 'Value3')

The number of results from this query were roughly 500 rows (from a table that holds about 10 billion rows) and the execution plan was essentially a nonclustered index seek with a key look up.

Is this normal?

Edit:

Table1 is an actual materialized regular disk-based table (nothing special going on here).
It's about 30 columns wide.
There's the 1 clustered index and 4 nonclustered indexes on it.
The "IndexedIntField1" in my example is part of the index key in 2 of the nonclustered indexes, and is an included column on a third nonclustered index.
"NonIndexedVarcharField" is not a key nor included on any of the indexes.
We update statistics on the table and indexes at least once a week (and sometimes as much as once a day or more)
No fancy calculated columns are on this table
The indexes on the table are pretty simple, only a couple of fields in the key columns / included columns EXCEPT one of the indexes that have "IndexedIntField1" as a key column does include about 15 columns on it (so it's a rather unusually big index).


texts: Trying to understand how an auto increment primary key is better than no primary key and some other primary key questions

texts:
I am trying to understand primary keys better, how to use them effectively in table design and in queries.

First, are the primary keys themselves used in WHERE clauses? For example, if I have a table of names and the primary key is setup as 'A' for all entries with last name starting with 'A', 'B' for all last names starting with 'B', etc. would it be best practice to have something like:

WHERE pk_field = 'B' AND last_name = 'Bluthe'

Secondly, I want to understand how an auto increment primary key would be better for performance than just something like:

SELECT last_name FROM names WHERE last_name = 'Bluthe'

If the primary key for this record is 1247 would a

WHERE pk_field = 1247

be much better. Wouldn't the quest still go through each record in that column until it finds a match.


texts:
I have a stored proc which searches "bad" data in our application database (sql server 2016 enterprise, ~500gb data). "bad" in a business/process sense.

It normally takes 10-30s to execute.
After some days it takes suddenly 5+ minutes to execute.

My current solution is to recompute all statistics and the execution time is low again:

EXECUTE dbo.IndexOptimize @Databases = 'myDB',
 @FragmentationLow = NULL,
 @FragmentationMedium = NULL,
 @FragmentationHigh = NULL,
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30,
 @UpdateStatistics = 'ALL',
 @OnlyModifiedStatistics = 'Y'

Index Optimize by Ola Hallengren

Obviously, regenerating the stats is leading into a new and better query plan. Is there a targeted way to identify the misleading statistic which introduces the slow query plan? Or how can I find the cause for this? Tables, indexes, stats and this stored proc are complex so I cannot guess it. Can I maybe programmatically compare the statistics "before" and "after" the updating?

We have many filtered indexes which are usually very tiny so the 20% rule may apply to them frequently.

The indexes are optimized weekly.


texts: Graph Database Constraint Across Multiple Edges

texts:
Backend is Dgraph's Slash GraphQL DBaaS.
Looking for a possible way to improve a portion of my schema while it is young. I have notes and allow users to add reactions to the notes. Users are linked to contacts to hide user info while showing contact names.
What I am looking for is a better way to only allow any one reaction to be made once for any one contact on any one post. So in essence, a three edge unique constraint.
Example Schema:
type User {
  username: String @id
  isContact: Contact! @hasInverse(field: isUser)
}
type Contact {
  id: ID
  isUser: User
  name: String!
  madeReactions: [Reaction] @hasInverse(field: by)
}
type Note {
  id: ID
  content: String!
  author: Contact
  reactions: [Reaction] @hasInverse(field: onNote)
}
type Reaction {
  id: ID
  at: DateTime! @search
  reaction: Reactions! @search
  by: Contact!
  onNote: Note
}
enum Reactions {
  PLUS
  MINUS
  # ...
}

What I don’t want to do is break out every reaction to its own edge. I would rather keep the reactions in an enum. This makes the queries and mutations easier to maintain.
I also did reduce down this schema greatly. I am using the Reaction type on to manage reactions on multiple content types such as posts, comments, and messages which explains why the Reactions.onNote edge is not required. This allows me to pull up a list of a users last reactions across various content types and show them in a feed like manner. I also figure that later on I can do some advanced algorithms to help connect users together by comparing similar reactions if they are stored in a single type.
The problem though is that with this schema, there is no way to limit a user (on the database side) from repeating the same reaction to the same content. This allows a repeat of the following mutation over and over again unless controlled from the client application side.
mutation addReaction {
  addReaction(input: [{
    at: "2020-11-06T03:05:00Z"
    reaction: PLUS
    by: { id: "0x353037" }
    onNote: { id: "0x3432" }
  }]) { numUids }
}

I really want to make this control on the database side. And I would love to do it with GraphQL only without DQL (Dgraph Query Language) upserts.


texts:
My table is like this:

Sitecode    Month  Amount
--------    -----  ------
XX          Jan     1000
XX          Jan     3000
XX          Apr     3000
XX          Apr     1000

What I want is, to show the result something like this:

Sitecode    MonthJAN   MonthAPR
--------    --------   --------
XX          4000       4000


texts:
I am trying to create a SQL Server 2014 data source in weblogic through admin console. When I click on test configuration, I get the following error:

  Message icon - Error Connection test failed.
  Message icon - Error [FMWGEN][SQLServer JDBC Driver][SQLServer]Could not find stored procedure 'master..xp_jdbc_open2'.


texts: No results from traceid from sys.fn_trace_getinfo(0);

texts:
SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#);

The above command returns no results.  There should be a bunch of trace event id being traced.


texts:
My prod_server version is 5.1.47 and want to set the slow query log dynamically for certain period of time.
I refered one site(http://toastergremlin.com/?p=276) there is mentioned below 5.1.6 we need to set the variables into the my.cnf, but my prod_server is critical can't take my server restart is there any way we set variables dynamically for certain period of time?

Thank u in advance please help.


texts: How to combine these two queries and have them be as fast?

texts:
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.


texts: Named Parameters in MySQL Workbench Queries?

texts:
In OpenOffice / LibreOffice I'm used to added :prefixed-parameter-names to my queries.

For example:

SELECT name FROM table1 WHERE id = :id

Is there a similar functionality in MySQL Workbench?


texts: Mongodb background indexing - no progress

texts:
I have added a couple of background indexes on few collections on mongodb replicaset (3.0.2, MMAPv1 storage), and since then I'm seeing following entry in db.currentOp(true) response in all nodes (this one from Primary):

{
    "desc" : "rsSync",
    "threadId" : "0x2923ee0",
    "opid" : 5653,
    "active" : false,
    "op" : "none",
    "ns" : "local.replset.minvalid",
    "query" : {

    },
    "msg" : "Index Bulk Build: (3/3) btree-middle",
    "numYields" : 0,
    "locks" : {

    },
    "waitingForLock" : false,
    "lockStats" : {
        "Global" : {
            "acquireCount" : {
                "r" : NumberLong(2),
                "R" : NumberLong(1),
                "W" : NumberLong(1)
            }
        },
        "MMAPV1Journal" : {
            "acquireCount" : {
                "r" : NumberLong(1),
                "w" : NumberLong(1)
            }
        },
        "Database" : {
            "acquireCount" : {
                "r" : NumberLong(1),
                "R" : NumberLong(1)
            }
        },
        "Collection" : {
            "acquireCount" : {
                "R" : NumberLong(1)
            }
        }
    }
},

It has been running for nearly 5 days now. From "Index Bulk Build: (3/3) btree-middle" I would guess it's still rebuilding indexes, but it seems to be taking unusually long time. Whole database is ~140Gb on disk, and I wasn't adding indexes on biggest collections either.

Database performance is 2-3 times slower than it was before adding background indexes.

One of collections that I added index on has many slow (30ms) "count" queries (had them before new indexes).

Servers are idling, resource usage (CPU load, Disk I/O, Network traffic, RAM usage) looks unchanged in graphs. 3 bare metal machines in use, one Primary and 2 Secondary nodes. Rotational disks are used, but 128G RAM, 122G cached.

Database is doing ~25K RPM, collections that indexes were added upon are in use. Collection sizes that I added background indexes on vary from 100-1K to 100K-1M records.

How to tell what exactly mongodb is doing right now, and if it's possible / safe to kill this indexing operation if it's stuck?

Or perhaps indexing is over, but that message does not have to disappear, and my indexes somehow degraded the performance?

How to troubleshoot this situation?


texts: MySQL FULLTEXT search returns only exact match

texts:
I added a FULLTEXT index to a table like so:

ALTER TABLE TEST ADD FULLTEXT(name, descrip, notes);

The TEST table has 100 rows. I updated the descrip column with 'branch' in one row and 'Branches' in another row.

Then I run

SELECT * FROM TEST WHERE MATCH(name, descrip, notes) AGAINST ('branch' IN BOOLEAN MODE);

The result returned only rows that contain 'branch'. 'Branches' is not included in the result, even though it is comprised of the word that was searched. How to include 'Branches'? I want to avoid using LIKE.


texts: Is inserting into a table with a foreign key constraint slower than inserting without constraint?

texts: When using MySQL (actually, Aurora), will inserting into a table with a foreign key constraint be slower than the same table without the constraint?


texts: How to use RETURNS TABLE with an existing table in PostgreSQL?

texts:
From the PostgreSQL documentation here:

  There is another way to declare a function as returning a set, which
  is to use the syntax RETURNS TABLE(columns). ... This notation is specified in recent versions of the SQL standard, and thus may be more portable than using SETOF.

This sounds as if RETURNS TABLE is a newer, more portable style to return multiple rows. But I am not sure if the two syntaxes are equivalent.

I was wondering if we can actually use RETURNS TABLE to replace RETURNS SETOF?

In particular, one case I haven't figured out is: if we have an existing table foo and its associated composite type, how can we use it in RETURNS TABLE?

Using the example for the above link, can the following be rewritten using RETURNS TABLE:

CREATE TABLE foo (fooid int, foosubid int, fooname text);
CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

So far, I tried  to use RETURNS TABLE (foo.*) and RETURNS TABLE (foo), which didn't work.


texts: How do I use the input from a \prompt in a conditional (\if)?

texts:
Let's say I get value from a prompt?

\prompt 'Write [Yes] or [No]\n' store

Which does this,

Write [Yes] or [No]
Yes

I can verify I have that works,

\echo :store
Yes

But, I can't get it to work with \if

\if :store='Yes'
unrecognized value "Yes=Yes" for "\if expression": Boolean expected

\prompt's doc don't say much on this

  \prompt [ text ] name Prompts the user to supply text, which is assigned to the variable name. An optional prompt string, text, can be specified. (For multiword prompts, surround the text with single quotes.)

  By default, \prompt uses the terminal for input and output. However, if the -f command line switch was used, \prompt uses standard input and standard output.


texts:
I would like to drop all the jobs related to a set of specific databases.

So I have added all the names into a table variable and I was trying to generate the scripts to drop the jobs.

how can I achieve this?

use msdb
go

DECLARE @R TABLE ( NAME VARCHAR(108) NOT NULL PRIMARY KEY CLUSTERED)

INSERT INTO @R(NAME)
SELECT
'UK15SUMMProduct' UNION ALL SELECT
'US15SUMMProduct' UNION ALL SELECT
'DE15SUMMProduct' UNION ALL SELECT
'AT15SUMMProduct' UNION ALL SELECT
'FR15SUMMProduct' UNION ALL SELECT
'EU15SUMMProduct' UNION ALL SELECT
'AU15SUMMProduct' UNION ALL SELECT
'UK15SUMSProduct' UNION ALL SELECT
'US15SUMSProduct' UNION ALL SELECT
'DE15SUMSProduct' UNION ALL SELECT
'AT15SUMSProduct' UNION ALL SELECT
'FR15SUMSProduct' UNION ALL SELECT
'EU15SUMSProduct' UNION ALL SELECT
'AU15SUMSProduct'

SELECT S.*
FROM SYSJOBS S
WHERE NAME LIKE ( SELECT NAME FROM  @R)

something like the select above, I want to see all the jobs that have the database names in them and delete them, for example, like in the script below:

exec sp_delete_job @job_name = 'WebFeed UK15SUMMProduct', @delete_unused_schedule = 0


texts:
We have an SSIS package in our SQL Server 2008 server to load data onto our system every night. The SSIS package consists of several ETLs (around 20) that are currently run sequentially.

On some nights it spits out below deadlock messages for certain ETLs.
Some nights pass with no errors at all. Every night what's run on the server is the same.

The specific ETLs deadlocking are not the same. For instance,

Monday it'll be ETL1, ETL3, ETL9.
Tuesday & Wednesday – all OK.
Thursday – ETL2, ETL5, ETL6, ETL7.
Friday – all OK.

Below is the 19th night's error message in SQL Server Agent:

  Executed as user: localhost. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.5500.0 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  04:26:33  Error: 2016-11-19 04:31:00.39     Code: 0xC0202009     Source: DFT_Extract_ETL1 OLE_DST_ETL_Destination [68]     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft OLE DB Provider for SQL Server"  Hresult: 0x80004005  Description: "Transaction (Process ID 65) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".  End Error  Error: 2016-11-19 04:31:00.61     Code: 0xC0209029     Source: DFT_Extract_ETL1 OLE_DST_ETL_Destination [68]     Description: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "RC_Rows_Inserted_Output_OLE_DST_ACTIVITY" (81)" failed because error code 0xC020907B occurred, and the error row disposition on "input "RC_Rows_Inserted_Output_OLE_DST_ACTIVITY" (81)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.  End Error  Error: 2016-11-19 04:31:00.84     Code: 0xC0047022     Source: DFT_Extract_ETL1 SSIS.Pipeline     Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "OLE_DST_ETL_Destination" (68) failed with error code 0xC0209029 while processing input "RC_Rows_Inserted_Output_OLE_DST_ACTIVITY" (81). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.  End Error  Error: 2016-11-19 04:31:01.11     Code: 0xC02020C4     Source: DFT_Extract_ETL1 OLE_SRC_ETL1[1]     Description: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.  End Error  Error: 2016-11-19 04:31:01.31     Code: 0xC0047038     Source: DFT_Extract_ETL1 SSIS.Pipeline     Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "OLE_SRC_ETL1" (1) returned error code 0xC02020C4.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  04:26:33  Finished: 04:41:09  Elapsed:  875.54 seconds.  The package execution failed.  The step failed.


texts: Tables with the same name in the same DB (very strange bug)

texts:
Have you ever seen something similar to this (picture)?

A table appears multiple times!!
I don't know exactly how this began.
Whenever I make a change (alter) to a table, a new instance appears
I've used 2 different clients (HeidiSQL, DBeaver)
and my web applications work without problems.
Any ideas
Thank you


texts: calling scalar function to assign value to local variable

texts:
In a SQL Server stored procedure, if we declare a local variable and assign the value by calling a scalar function
DECLARE @variable_name INT= dbo.scalarfuntion_name(@p1, @p2).
Would it have any impact on overall performance of SP as in general using scalar function directly into script has performance downgrade most of the times?
Thanks!