texts: Filtered indices: Why include the filtered-on field?

texts:
This just drives me mad.

Consider a simple table with irrelevant columns removed:

create table boxes (
  row_id int not null identity(1,1) primary key,
  location varchar(15) null,
  dismantled bit not null default 0,
  rank int not null default 0
  /* irrelevant columns, text and numbers */
)

For a certain important query, I want a filtered index:

create nonclustered index anindex ON boxes (rank)
where (dismantled=0 and location is null)

This creates an index that contains about 150 records out of about 150k. Which is fine.

Now we query the table:

select top (1) row_id
from boxes
where dismantled = 0 and location is null
order by rank;

The execution plan is weird. Index scan on anindex accompanied by a bookmark lookup using the seeked row_id to confirm that location is null. Records that pass the lookup are selected.

Why on Earth? location is null by the very definition of the used index, is it not?

Now, if I do a stupid thing and include location in the filtered index...

create nonclustered index anindex ON boxes (location, rank)
where (dismantled=0 and location is null)

Then all of a sudden the execution plan is great, index seek on anindex with no lookups.

This is just one example, I stumble upon this issue every time I want to use a filtered index. Every time I end up forced to include the useless fields, upon which the index is filtered, to the list of indexed fields, and only then the server uses the index properly; otherwise, it either scans it or ignores it completely.

What gives? Is it a recommended practice to include the useless fields?


texts: Does SSD drive make indexes rebuild and update stats useless with SQL Server?

texts:
Now that SSD is a standard de-facto. Can the maintenance job of rebuilding indexes and updating statistics, using Ola Hallengren https://ola.hallengren.com/ jobs for instance, be done only on demand instead of regularly such as on weekly basis with Microsoft SQL Server 2017 or lower?

If it is still required, what would be the threshold of the database size to decide when it is necessary ?


texts: How to exclude rows containing null values within columns?

texts:
This is probably not the ideal query to retrieve rows containing specified values within a column, but the solution below effectively returns the values specified in a new column-alias but includes NULL values.  How would the below query be constructed to exclude rows containing NULL values within the column alias?

SELECT student_id,
       salutation,
       CASE
           WHEN salutation IN('Dr.') THEN 'Doctor'
       END AS "NewSalutation"
FROM student.student

I welcome alternative approaches-thanks!


texts: Unexpected Implicit conversion in a procedure

texts:
I have a procedure like this (simplified):

CREATE PROCEDURE test @userName VARCHAR(64)
SELECT *
FROM member M
INNER JOIN order O
ON M.MemberId=O.MemberId
WHERE M.Username = @userName

There is a non-clustered index on Username column of the Member table.

Plan cache shows an Implicit Conversion as such:

  Seek Keys[1]: Prefix: [MyDatabase].[dbo].[Member].Username = Scalar
  Operator(CONVERT_IMPLICIT(varchar(64),[@Username],0))

I was just wondering what might be causing this implicit conversion as both the parameter and the field data type "UserName" is varchar(64)?

SP is called from framework like this:

EXEC test @Username=N'webSite.com'

Thank you.


texts: PostgreSQL Select statement produces errors in datagrip but not pgAdmin4

texts:
I can run the following script without errors in pgadmin4

SELECT * from tbl

I have tried the following in datagrip

SELECT * from tbl

and

SELECT * from public.tbl

Neither of the above scripts worked. The following error was returned:

[2018-05-12 21:16:44] [42P01] ERROR: relation "public.tbl" does not exist
[2018-05-12 21:16:44] Position: 15


texts: Are utf8_bin and utf8_general_cs the same?

texts:
Here's the situation:

I have come across several posts for "utf8_bin vs utf8_general_ci".
Similarly, "utf8_general_cs vs utf8_general_ci".

But I can't find a post for "utf8_bin vs utf8_general_cs". So, are they the same? If not, what's the difference between them?

Note: I have checked that utf8_general_cs is not available in default MySQL. May I know why?


texts: Allow Remote Connection to only ONE database SQL Server 2012

texts:
I would like to have 2 db (Dev & Prod). CarsDevDB & CarsDB.
I have 2 users, CarsDevUser (Allow Remote Access, Port 5000) and CarsAdmin (only allow local connection).

I would like to connect to CarsDevDB with the user CarsDevUser from anywhere REMOTELY using (IP):5000.

However, I would like to connect to CarsDB using ONLY LOCAL connection.
127.0.0.1. CarsDB/CarsAdmin should NOT have any remote connection whatsoever.

CarsDevUser will not access to any other database.

How can I proceed with this? I've enabled Remote connection and got it to work, I can't limit it to one database yet though.


texts: How to efficiently swap a portion of a large table's data?

texts:
I run an application (price comparison website) where I need to update a pricing table with dozens of millions of rows every day.

Basically, every product feed I download from a merchant results in several million rows to delete from the pricing table (WHERE merchantId = x), and several million fresh rows to insert in place of the old ones.

So far, I used to download all the feeds sequentially, insert all of them in a temporary table, then swap the old table with the new one in a single RENAME TABLE query.

This forced me to update all the merchants at once, hence I'd like to move away from this method, and have the ability to process once merchant at a time, deleting all records WHERE merchantId = x and insert all new records in a sub-second query.

My first thought was to use partitioning by list, one partition per merchant. This would solve half of my problem: I can use TRUNCATE PARTITION to quickly remove all existing prices for a given merchant, but the other half of the problem remains: how to quickly insert the millions of new rows, without downtime and unnecessary locks, and without running with the table partially loaded for several minutes?

So I was thinking: is it possible to create a temporary table with the same structure as the partitioned table, fill it with the new rows, and once it is complete, swap one partition of the pricing table with this temporary table?

Any suggestion to solve this problem would be appreciated, including suggestions that do not involve partitioning. I have thought about the MRG_MYISAM engine as well, but I'm not sure whether it fulfils my needs. On a final note, any storage engine is fine for this pricing table, including non-transactional engines.


texts: How can I backup a large SQL Server database without impacting performance?

texts:
We've had reports of queries running slowly or timing out early in the morning, and the only job I see running that I think could affect this is our database backup job.

The database itself is about 300GB, and the backup job starts at 4:30am and doesn't finish until a little after 7:00am. The current syntax of our backup job is:

BACKUP DATABASE [DatabaseName]
TO DISK = N'E:\Database Backups\DatabaseName.Bak'
WITH INIT, NOUNLOAD, NAME = N'DatabaseName.Bak',
NOSKIP, STATS = 10, NOFORMAT

E:\ is a partition on the server which holds both the databases and the database backups.

It should also probably be noted that this is a virtual server, not a dedicated standalone server. We started getting complaints about slowdowns during the backup process right after we switched to a virtual server, so I think it may be related.

Is there a way to run this backup job so it doesn't affect the query performance while its running?

We are using SQL Server 2005


texts: Series of questions about ER diagram and database?

texts:
I have newly learned about ER diagrams and how powerful they are.
I am trying to make a very basic ER diagram for hospital and there are few questions and doubts in my mind.
This is the ER diagram that I've made:

I want tests to be stored in hospital database as well as in the patient's report file(all the tests of a specific patient should be stored in his/her report file).
The ternary relationship test should signify that:

A patient and a doctor maintains only one report
A doctor makes a report for specific patient
A patient and his report can have tests from N no of doctors.

Q1- But I am confused how to achive this using ternary relation?
Q2- What is the difference between 3 binary relationship and 1 ternary relationship(and also which of them carrys more information)?
Q3- Can a relationship can have it's own prime(key) attribute?
Q4- If we have concept of surrogate key then why does weak entity exists in first place?
Q5- How to conclude which entity should be made weak and whichshould be not?
Q6- Any good source to learn EER ,advanced topics of database designing(relational algebra,etc..) and about ternary relationships(I have searched youtube and google but none of them was satisfactory)?


texts: How are the RowCounts in SQL Server Profiler calculated?

texts:
I have a problem where the counts returned by the RowCounts column in a SP:StmtCompleted are different from the actual rows returned for execution of a select statement.

I have tried a select @@RowCount after the select query and it returns the same counts as count(*). I'm curious now what could be causing the profiler to return incorrect row counts.

I did read related threads suggesting fragmentation can yield wrong counts and a DBCC UPDATEUSAGE might solve the issue, but I want to understand what I'm doing before applying this. I would appreciate some elaboration.
Also, do indexes play a role in the counts?

Related Q & A: SQL Server Row Count sp_spaceused rows discrepancy

I am using SQL Server 2014 Profiler.

This is how you can see row counts in Profiler, in case any one is wondering: Show Row Count in SQL Profiler


texts: One of the Database Ownerhas been Removed and corresponding property changes in sql server

texts: A database has been created on SQL 2008 by a user named user1 ,on dec 2013.Today ,we are going to remove that user(/owner) from the server.What will be the owner property of the database show then?


texts: Standard Behaviour for mix of 'ON-DELETE-CASCADE' and 'ON-DELETE-RESTRICT' constraints in Mysql

texts:
In mysql 5.6, Consider these 2 examples creating relationships between A, B, C and D.

Example 1

CREATE TABLE `a` (
    id INT UNSIGNED NOT NULL,
    PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE `b` (
    id INT UNSIGNED NOT NULL,
    a INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (a) REFERENCES a (id) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE `c` (
    id INT UNSIGNED NOT NULL,
    a INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (a) REFERENCES a (id) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE `d` (
    id INT UNSIGNED NOT NULL,
    b INT UNSIGNED NOT NULL,
    c INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (id) REFERENCES b (id) ON DELETE CASCADE,
    FOREIGN KEY (id) REFERENCES c (id) ON DELETE RESTRICT
) ENGINE = INNODB;

INSERT INTO a VALUES (1);
INSERT INTO b VALUES (1, 1);
INSERT INTO c VALUES (1, 1);
INSERT INTO d VALUES (1, 1, 1);

DELETE FROM a;

The result is that all rows are deleted.

Example 2

CREATE TABLE `a` (
    id INT UNSIGNED NOT NULL,
    PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE `b` (
    id INT UNSIGNED NOT NULL,
    a INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (a) REFERENCES a (id) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE `c` (
    id INT UNSIGNED NOT NULL,
    a INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (a) REFERENCES a (id) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE `d` (
    id INT UNSIGNED NOT NULL,
    b INT UNSIGNED NOT NULL,
    c INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (id) REFERENCES b (id) ON DELETE RESTRICT,
    FOREIGN KEY (id) REFERENCES c (id) ON DELETE CASCADE
) ENGINE = INNODB;

INSERT INTO a VALUES (1);
INSERT INTO b VALUES (1, 1);
INSERT INTO c VALUES (1, 1);
INSERT INTO d VALUES (1, 1, 1);

DELETE FROM a;

Notice that the only difference is the change of what foreign key is RESTRICT from d. This example however, fails with

  Error Code: 1451
  Cannot delete or update a parent row: a foreign key constraint fails (hello.d, CONSTRAINT d_ibfk_1 FOREIGN KEY (id) REFERENCES b (id))

While logically, it's the same as Example 1. Without having looked at the source code of MySQL, I strongly suspect that the foreign keys are "applied" in lexical order based on their name. What will be the standard behaviour (ANSI-SQL) in this scenario?


texts:
Since all writes and sequential reads are cached by the EMC storage system, the best use for solid state disk is to store data that has a random READ pattern. On Oracle, that is the temp tablespace which is used for disk sorts that cannot fit in memory. How can I isolate the parts of MySQL which are characterized by a random disk read pattern?

Here's a clarification of my original question: This is a reporting database with very large tables, large sorts, etc. Cannot fit into server memory.


texts:
I have implemented the System Versioned Temporal Tables on the table "Orders". The application is using different access patterns to modify the data in this table. There are direct statements coming from the application or the application is running long batches in explicit transactions where it makes several changes to multiple tables. The update on the "Orders" table is not the first statement in those long batches! So, sometimes we are facing the following error.

  Data modification failed on system-versioned table "Orders" because
  transaction time was earlier than period start time for affected
  records.

Apparently this is a standard behavior of System Versioned Temporal Tables. https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-2017#how-does-temporal-work

Is this something that will always need to be handled in an exception routine? Or is Microsoft considering to change that behavior?

--Script to simulate the error message:

CREATE TABLE dbo.Orders
    (
      [OrderId] INT NOT NULL PRIMARY KEY CLUSTERED
      , [OrderValue] DECIMAL(19,4)
      , [ValidFrom] DATETIME2 (2) GENERATED ALWAYS AS ROW START
      , [ValidTo] DATETIME2 (2) GENERATED ALWAYS AS ROW END
      , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
     )
     WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.OrdersHistory));
     GO

     INSERT dbo.Orders ([OrderId], [OrderValue])
     VALUES (1, 9.99), (2, 9.99);
     GO

     SELECT * FROM dbo.Orders;
     GO

       --Run first query
   BEGIN TRAN
      WAITFOR DELAY '00:00:15';
      UPDATE dbo.Orders
      SET [OrderValue] = [OrderValue] + 1;
    COMMIT TRAN

       --Run Query 2 in another session sql server
    BEGIN TRAN
       UPDATE dbo.Orders
       SET [OrderValue] = [OrderValue] + 1;
    COMMIT TRAN


texts:
Hi I want to create a web based resource tracking system which will be used by my manager using php and html. The db i am using is MySql. I have already created tables for employee, project etc. I have to create table to store the weekly resource availability. For eg. an employee A is working in 2 projects this week, and the ratio is like 0.4 and 0.6 (combining both we get 1.00 so he is engaged in project this week completely). Next week he is working on 1 project and his availability for that project is 0.7(so the manager can put him on another project also). I have to store these information for next 20 weeks. I am not getting any logic to implement this idea in DB. The below screenshot is how my db looks like now. The dates are hard coded.

Also next week i need to delete the previous weeks information and add 21st week's information for an employee. I completely stuck with this. I saw this link database schema for timesheet but it is some what complex which deals with hours also. I just need the information on weekly basis. Can somebody help me to do this schema ?


texts: MySQL is insanely slow on fresh installed Ubuntu 17.10

texts:
Yesterday I installed Ubuntu 17.10 on my desktop and today I wanted to migrate a MySQL datababase (php artisan migrate:fresh with laravel), but my database was very slow. The migrations only, took around 30 seconds.

On my macbook these migrations take only a few seconds (under 5 seconds).

I tried google and got a lot of settings I could change in my.cnf.
For example the following settings:

[mysqld]
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64

This didn't made it any faster. I also found the option for slow query logging, but the output file is empty after the migrations.

Does someone have any other solutions?

Ps. I removed the settings from my.cnf because it didn't made it faster and I wasn't sure whether to keep the settings or not.

I have mysql-server-5.7.


texts: Postgresql - Primary key index getting corrupted after upgrade from 9.2 to 9.6

texts:
Im using a Postgresql-9.2 on Centos 6.7. I tried to upgrade the PostgreSQL using --link. After that, I took a table backup and tried to restore this on Dev server. But Im getting an error like,

  ERROR: duplicate key value violates unique constraint "table_pkey"

Then I tried to query the actual database, and I can see the primary key value twice.

And tried to reindex and got this error.

WARNING:  concurrent delete in progress within table "table"
ERROR:  could not access status of transaction 3769823
DETAIL:  Could not open file "pg_subtrans/E0B2": No such file or directory.
CONTEXT:  while checking uniqueness of tuple (13075,93) in relation "table"

Also disabled the wal log, cleaned the wal log files and reset the xlog.

I just wanted to why happened and how to fix them?


texts: Query takes longer as domain user than local admin

texts:
I have an application that runs ad-hoc queries to a remote SQL server. I'm running into a bizarre problem with the application taking significantly longer to return results when the application is running as a user versus a local administration (to the workstation).

I launched my application normally, and then launched a separate instance by right  clicking > run as administrator.

I started profiling the database. Using the exact same steps with the exact same query, the 'user' application takes 15 seconds to return data.
When I run the application 'as administrator' it takes only 3 seconds.

I can't find a reason why the same application using the same steps running the same queries is taking significantly longer to return results. I've run the test several times with similar discrepancies.

I can reproduce the issue using multiple workstation and multiple domain user accounts. Anytime the user runs the application 'as administrator' (invoking local admin), it runs much quicker.

Any insight would be helpful here, I'm completely stumped.

EDIT: The application authenticates to the database using SQL authentication in both cases (using the same credentials).


texts:
I've got Django app with mongoengine, which executes some aggregation-queries often enough.

The main problem is that mongod takes all the RAM and then crashes, so no LRU working, mongod just trying to get more and more memory.

My mongodb version is 3.2 and default engine is MMAPv1. As docs says, for caching MMAPv1 needs dataSize+indexSize amount of RAM. In my case that's about 370Mb (~290 for dataSize and ~80 for indexSize).

Here is my db.stats():

{
    "db" : "random_db",
    "collections" : 14,
    "objects" : 155613,
    "avgObjSize" : 1859.8720672437394,
    "dataSize" : 289420272,
    "storageSize" : 338821120,
    "numExtents" : 32,
    "indexes" : 22,
    "indexSize" : 82512192,
    "fileSize" : 1006632960,
    "nsSizeMB" : 16,
    "extentFreeList" : {
        "num" : 25,
        "totalSize" : 258314240
    },
    "dataFileVersion" : {
        "major" : 4,
        "minor" : 22
    },
    "ok" : 1
}

Actually I've got 1GB RAM and made swap-file for another 1GB.
When my app executes first aggregation query (taking only 50 documents from collection), mongod RAM usage increasing to ~300Mb (when I start service it's about 50-100Mb), and till ~70 such queries been executed - all of my memory+swap ends and mongod crashes.

I've created all the indexes, that needs for aggregation's '$match', if needed, I can add some of them here.

So why ~4000 documents in cache taking more than 2GB of RAM, if my entire database takes only 370Mb and there is only 150k documents in db? I know I'm doing something wrong, the question is very simple, but still I can't find explanation at docs.

BTW, here is my db.serverStatus.mem:

{
    "bits" : 64,
    "resident" : 112,//this value growth when queries executed
    "virtual" : 2523,
    "supported" : true,
    "mapped" : 1136,
    "mappedWithJournal" : 2272
}

So, I guess, my mongod can't take more than 2523Mb for entire db in cache?


texts: SQL Server 2005 - Map login to pre-existing user

texts:
The short of it is that we have several databases that are synchronized between two failover instances. The primary contains logins that are mapped to the users in the databases, and everything's hunky-dory. However, I didn't realize that server logins didn't synchronize between failover instances, so when there is a failover to the backup, nobody can log in. The kicker is that while I can create a login on the backup, I can't map the login to the DBs as a user because a DB user with that name already exists, and the login won't use the existing user, so logins still fail because the login doesn't have rights to connect to the DB.

I think what I have to do is modify the login in the master DB of the backup to have the same GUID as the login from the primary, and manually create the link between the login and user on the backup side, thereby "tricking" the backup instance into thinking the user created on the primary is is its own mapped login as well. However I have no clue how I would go about doing this, and we're having enough trouble with this issue without me flailing at it.

Help?


texts:
I hope you can help me. I am trying to make a data dump on a MongoDB remotely, but am getting an error.

I use the following command from my Windows CMD console to create the backup:

C:\Program Files\MongoDB\Server\3.0\bin\imports>mongodump --host 10.13.9.210 --port 29039 --out E:\Mongo\Data\Backup\Dump

It all seems to run and dump and write,

2015-11-13T15:56:30.159+0200    writing IOWA.VENDORITEM to E:\Mongo\Data\Backup\Dump\IOWA\VENDORITEM.bson
2015-11-13T15:56:30.162+0200    writing IOWA.CALLCLOSURECODE metadata to E:\Mongo\Data\Backup\Dump\IOWA\CALLCLOSURECODE.metadata.json
2015-11-13T15:56:30.171+0200    writing IOWA.PRICELISTITEM metadata to E:\Mongo\Data\Backup\Dump\IOWA\PRICELISTITEM.metadata.json
2015-11-13T15:56:30.193+0200    done dumping IOWA.CALLCLOSURECODE (0 documents)
2015-11-13T15:56:30.194+0200    writing IOWA.BULLETINBOARD to E:\Mongo\Data\Backup\Dump\IOWA\BULLETINBOARD.bson
2015-11-13T15:56:30.205+0200    done dumping IOWA.PRICELISTITEM (0 documents)
2015-11-13T15:56:30.205+0200    writing IOWA.BINLOCATION to E:\Mongo\Data\Backup\Dump\IOWA\BINLOCATION.bson

But then I get this ERROR:

2015-11-13T15:56:30.270+0200    Failed: error creating bson file `E:\Mongo\Data\Backup\Dump\IOWA1\db.getCollection('MASTER').distinct("_t").bson`: open E:\Mongo\Data\Backup\Dump\IOWA1\db.getCollection('MASTER').distinct("_t").bson: The filename, directory name, or volume label syntax is incorrect.

Any help would greatly be appreciated!


texts: Streaming Replication failing from standby with connection issue

texts:
I'm setting up streaming replication between a primary and standby server.

On the standby server, when I start up the instance I see that replication isn't taking place. Looking at the log, I see the following:

2017-02-02 20:37:03 UTC [45618-1] LOG:  database system was shut dow
n in recovery at 2017-02-02 20:36:50 UTC
2017-02-02 20:37:03 UTC [45618-2] LOG:  entering standby mode
cp: cannot stat '/etc/replarchive/000000010000000000000005': No such file or directory
2017-02-02 20:37:03 UTC [45618-3] LOG:  invalid record length at 0/5000098
2017-02-02 20:37:03 UTC [45623-1] FATAL:  could not connect to the primary server: FATAL:  no pg_hba.conf entry for replication connection from host "10.0.0.5", user "replication", SSL on
        FATAL:  no pg_hba.conf entry for replication connection from host "10.0.0.5", user "replication", SSL off

That's a pretty obvious error message, could not connect to the primary server: FATAL:  no pg_hba.conf entry for replication connection from host "10.0.0.5", user "replication", SSL on.

But on the primary server, pg_hba.conf indeed has the following entry:

host    all     replication 10.0.0.5/32     md5

I can't seem to wrap my head around why the standby server is failing a connection with that in place.

So I tried to connect from the standby server to the primary server via psql:

# this is on the standby server (10.0.0.5) connecting to the primary server (10.0.0.4)
psql myrepl replication -h10.0.0.4

Putting in my password when psql prompts me to, I successfully connect to the myrepl database on the primary server.

What am I missing here? Why is recovery on the standby not able to connect to the primary server when it appears obvious that the connection is indeed possible?

Thank you in advance!


texts: Estimated rows very wrong when using declared variables

texts:
I am getting very, very wrong estimates for the clustered index seek step of  this query (estimated rows is 8,637,530; actual is 74,723):

Declare @StartDate as date = '10/1/2014';
Declare @EndDate as date = '10/2/2014';

select Sum(Quantity)
from Daily_GC_Items
where SalesDate between @StartDate and @EndDate;

If I use string literals for the dates like this, the estimate is almost perfect (estimated rows is 75,337.4; actual is 74,723):

select Sum(Quantity)
from Daily_GC_Items
where SalesDate between '10/1/2014' and '10/2/2014'

Table definition:

[SalesDate] [date] NOT NULL,
[Store] [varchar](10) NOT NULL,
[GuestCheckNumber] [smallint] NOT NULL,
[ItemSequence] [smallint] NOT NULL,
[Quantity] [smallint] NOT NULL,
(Several more columns)

Primary Key:

[SalesDate] ASC,
[Store] ASC,
[GuestCheckNumber] ASC,
[ItemSequence] ASC

What are the possible causes for this behavior?


texts:
Currently I have the following happening :

And :

When i run the query i get about 68 entries with most Time set at 0

show full processlist;

What I'm doing wrong ?
How do i free some memory ?
Is it normal to have swap frozen like that ? I tried to modify the swappiness without success.

echo 10 > /proc/sys/vm/swappiness

my.cnf file

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

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

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

log-warnings    = 2
log-error       = /var/log/mysqld.log

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /data/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

#
# * Fine Tuning
#
key_buffer_size         = 16M
max_allowed_packet      = 128M
thread_stack            = 192K
thread_cache_size       = 64
table_open_cache        = 3000
#open_file_limit         = 10000
join_buffer_size        = 128k
# This replaces the startup script and checks MyISAM tables if needed

# the first time they are touched
myisam-recover         = BACKUP
max_connections        = 3000
wait_timeout           = 150
interactive_timeout    = 30

innodb_buffer_pool_size        = 25G
#  The preferred value size for the log_file_size is 25% of the innodb_buffer_pool_size.
innodb_log_file_size           = 1G
#innodb_log_buffer_size         = 512M

innodb_buffer_pool_instances   = 10
tmp_table_size                 = 256M
max_heap_table_size            = 256M
innodb_flush_log_at_trx_commit = 2
query_cache_limit              = 64M
query_cache_size               = 256M

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 1
max_binlog_size         = 100M

[mysqldump]
quick
quote-names

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 16M


texts: How can I position the element generated by XMLTEXT in a FOR XML query?

texts:
I'm trying to use FOR XML to simulate an OData feed. One of the requirements I have is that somewhere I need an XML element which is constant for all records; I was planning to use the XMLTEXT directive for this.

My XML needs to look like this; for every record in the database,

<entry>
    <id>1</id>
    <test type="dummy" />
    <!-- some more nested elements -->
</entry>

When I use the following query, the <test> element appears before the <id> element, even though the declaration appears after it:

CREATE TABLE #temp (id int);
INSERT INTO #temp VALUES (1), (2);

SELECT
  1    AS Tag,
  NULL AS Parent,
  id   AS [entry!1!id!ELEMENT],
  '<test type="dummy" />'
       AS [entry!1!test!XMLTEXT]
  -- other columns
  FROM #temp
  FOR XML EXPLICIT;

DROP TABLE #temp;

This gives the following results (fiddle):

<entry>
    <test type="dummy"/>
    <id>1</id>
</entry>

Am I doing something wrong here, or is this a limitation of XMLTEXT? A workaround might be to treat it as a regular nested element, but would that work even though I have more nested elements further down in the <entry> element?


texts:
I have a server that the database drive is running out of space. i have deleted some of the old log data and historical data to try and free up the space.what is the best way to free allocated space in SQL server. the only way i could find online is to shrink the database but then i read shrinking the database can cause problems
I have tried using shrink database and it works but i'm just concerned if i'm using the correct method


texts: Delayed replication false alerts and migration delays

texts:
I am running mongo 2.6.1 on a 3-shard cluster.

Configuration is: 3 data nodes and 2 arbiters.

The one data node is hidden and delayed for 3 hours (slavedelay).

I have hidden the node in order to start/stop during backup and the 3 hours delay is for safety during new deployments.

The issue that I am facing is: During the weekend there are no write operations on the cluster (that is the nature of application). On Monday morning when the writes begin, the monitoring system reports that the delayed data node is behind for more than 40 hours.

I guess that is because the oplog that has no records during the weekend and when a record gets in on Monday, it needs 3 hours to reach the delayed slave.

Please share your opinion on the matter, is my thoughts correct here or should I check on a different direction?


texts: Copying a database for testing in a safe way

texts:
For testing purposes I have to create a copy of a production MySQL database that cannot be touched.

These are the steps I would follow:

Create a reduced size database backup:

mysqldump prodDB --single-transaction --where="true limit 20" > test.sql
Create a new test database: testDB
Load the dump file in the new test database:

mysql testDB < test.sql

Is this procedure correct?
Can I be sure that the original database will not be modified whatsoever?

Thank you!

System: MySQL 5.1 with InnoDB


texts: Writing a large update/delete script that doesn't provoke locks

texts:
While running a T-SQL script that is performing massive amounts of deletes, I had the unpleasant experience of causing some sort of lock contention that prevented inserts of data in some tables for other connections. I tried to remedy this by breaking down the request into smaller transactions with the hopes of avoiding an overarching transaction and locks:

set implicit_transactions off

declare @idsToDelete table (id bigint)
declare @currentId bigint
declare @deleteCursor cursor

begin transaction
    insert into @idsToDelete
    select someId from someTable with (nolock) where someCondition = 'some value'
commit

set @deleteCursor = cursor for select id from @idsToDelete
open @deleteCursor
fetch next from @deleteCursor into @currentId

while @@fetch_status = 0 begin

    begin transaction

        delete from relatedTable
        where id = @currentId

        delete from someTable
        where id = @currentId

    commit

    fetch next from @deleteCursor into @currentId

end

close @deleteCursor

My understanding is that set implicit_transactions off should enable me to not have a top-level transaction. Therefore, each of the deletes would run, complete, and we'd be done with it. I was able to see progress with sets of rows being deleted in piecemeal fashion, so I thought it was all good. However, in actuality, inserts into this table were being blocked. And worse, when I noticed and cancelled the run, the whole thing rolled back, continuing to block as it did so!

Is there a way for me to get this thing to run with independent transactions and no locks (except row locks which are OK)? I need to write a big update script that could have similar problems if I'm not careful.


texts: Can't connect to SQL Server through SSMS remotely

texts:
Ok, look apologies if this is a really stupid question, but I'm struggling here.

Just started a company and they use VM's hosted externally for SQL Server.  You access the VM by RDP by putting in an IP address and a port number; e.g. 12.34.56.789:1234.

How do I connect to an instance on this server using SSMS on my local machine?

I've reviewed multiple help articles online.  I notice my SQL Server is set to TCP Dynamic Ports, and not a static port like 1433.  Windows Firewall is turned off so no issue there.  I've checked the netstat -ano and see that TCP is listening on:
[::]:49166 and
I'm able to telnet to the server using the 12.34.56.789:1234 address, but not able to telnet using the 12.34.56.789:(dynamic port address).

Am keen to even try to connect to it through the dynamic port but can't get through to it either.

SQL Browser is on.

Thank you in advance for you help.  Going mad here!!


texts:
The aggregate functions MIN,MAX,AVG, and STDEV are not allowed in an Indexed View in SQL Server (Create Indexed Views - MSDN Library).

Does SQL Server have a mechanism to index the results of the following view, even though INSERT, UPDATE, and DELETE operations would become much more expensive?

SELECT
    test.test_id,
    MIN(samp.result) AS tMin,
    MAX(samp.result) AS tMax,
    AVG(samp.result) AS tAvg,
    STDEV(samp.result) AS tStdev
FROM test
INNER JOIN samp ON test.test_id = samp.test_id
WHERE samp.status = 'OK'
GROUP BY test.test_id


texts: MYSQL testing string field contains another altered string field


texts: Selecting specific table for each subscriber when using merge replication in SQL Server with multiple subscribers

texts:
I am using merge replication with SQL Server 2017.

I have one publisher, and two subscribers (all in all 3 database servers).

I need to replicate different tables to each subscriber.

Is this possible?


texts: Point in time recovery from the same database - what does it mean?

texts:
I have two questions. I have a database in full recovery mode. Lets call transaction log an LDF file for short.

SQL Server allows me to restore to point in time in the past when restoring from database to itself. Does this even make sense? When I tried it did not yield expected results. Why is this even allowed if it doesn’t work? Can it have any practical purposes?
When playing with the LDF files I discovered a curiosity: Consider having a database in simple mode. Then I switch it to full recovery mode. Then I insert some 10000 rows into the database, check the LDF and find out that the LDF file size did not increase. When I take a full backup and then insert 10000 rows again into the database only then the LDF file increases. Is this some sort of optimization? NOTE: I had LDF file shrinked before both operations.

Thanks for all explanations.


texts: How can I mysqldump all databases except the mysql schema?

texts:
I want to backup all 200+ databases on a MySQL server to all-dbs.sql.

I want to exclude the mysql schema.

How can I do that?


texts: Splitting the table to get more clusterization options - will it help?

texts:
Complete theory.

Let's assume we have a table with a few millions rows. The table has clustered index over PK, and it is important for quick fetching of particular rows. However, the table also has a column kind of CreatedDateTime (non clustered index), and by reasons of report building over particular time span, obviously, it would be great to cluster it too.

Will it help, if I split the table in two - TMain with clustered PK, and TMainTime, with (MainId) as non clustered PK, and (CreatedDateTime) for clustered index? It looks like I can determine the timespan required using TMainTime, searching over its clustered datetime, and then, with MainId values found, fetch them searching over clustered PK in TMain table. Will it ever add something to performance? Or will it eliminate any possible gain by JOIN operation over the two tables?


texts: Incorrect Sum values the quantity is multiplied by the number of rows

texts:
I have this select query
   SELECT
  info.[OFNumber]
  --Get sum of item quantity from the same category (all quantity before production) PS_ProjectInfo table
  ,SUM(Case WHEN info.[FK_PSCategory]=2 then ((info.Length * info.[Quantity] )/1000)Else 0 end ) as 'm'

 -- Get sum of item quantity from the same category (Only quantity produced) PS_DailyProduction table
 ,SUM(Case WHEN info.[FK_PSCategory]=2 then PS_DailyProduction.Quantity ELSE 0 END) as 'qty'

FROM [dbo].[PS_DailyProduction]
Left JOIN PS_ProjectInfo info on info.id=PS_DailyProduction.[FK_OF]

GROUP BY info.[OFNumber]

Now if each items has produced at once I have one row in my PS_DailyProduction table ,there is no problem, But  whene any item has produced in two or more times (for example half quantity on one day and the rest onther day)her the problem show, in my PS_DailyProduction table i have more than one row and I get wrong result on
--Get sum of item quantity from the same category (all quantity before production) PS_ProjectInfo table
  SUM(Case WHEN info.[FK_PSCategory]=2 then ((info.Length * info.[Quantity] )/1000)Else 0 end ) as 'm'

The quantity is multiplied by the number of rows from PS_DailyProduction
I try this but did not work (dividing on number of rows)
--Count(PS_DailyProduction.FK_OF)
SUM(Case WHEN info.[FK_PSCategory]=2 then ((info.Length * info.[Quantity] )/1000)Else 0 end ) /Count(PS_DailyProduction.FK_OF)as 'm'

I use LEFT JOIN to get all rows from PS_ProjectInfo table even when they not produced yet


texts: Safe and secure implementation of logon trigger in SQL Server 2014 Express edition?

texts:
I've to implement the following requirement:
Access to SQL Server instance shall be allowed only from a C# application.
Users shall not be able to access any database (even those in which they have access) via SQLCMD, SSMS.
Access using SSMS shall be allowed only for logins that are sysadmin, serveradmin.
Connection from one specific host machine shall be denied.
Connections from a specific C# application shall be allowed.
Is it possible, secure and safe to implement this via logon trigger?
If yes, how can I implement it in a reliable manner.
In order to apply the logon trigger, is it necessary to be done via SSMS, only once to be activated.
In case something goes wrong how can I disable it?


texts: Strategy for multiple distinct databases in PostgreSQL

texts:
I have a 30 gb db in PostgreSQL 9.2.
Running On Amazon Linux on an ec2 (web virtual machine), with the data directory mounted from its own drive (EBS).

I'll call the database db30.

On production it's the only user db.

In qa/dev we get the call for multiple versions.

Ex:

can I get a copy of db30 from production restored as db30_dev3 on the qa? Maybe the last full backup is good, sometimes they need a copy from right now.
can you give me a copy of db30_qa2 and call it db30_qajeff? If I hose the data can we recover from the image you restored, not a new copy of db30_qa2?

I've been a mssql dba on physical servers for nearly a decade so I'm totally thinking with a SQL Server mindset.

Right now, I'll pg_dump to a gzip file. 7gb. Might take a while.
To restore I would create the new db name, the psql run the SQL file against it, which promptly takes a few hours to apply all of the SQL and build indexes.

In aws and on sans, you have the option of a disk snapshot, which I'm assuming should be like copying the files and attaching them to the server. But the docs say you must grab both Db files and WAL in the snapshot.

Because the WAL is an object of the postmaster, all db's share it. This snapshot method seems only valid for backing up and resorting the whole cluster (what I'd call a running instance of PostgreSQL). It doesn't seem possible to directly snap and restore only a single Db.

So my rookie psql notion would be:

Only one user Db per cluster
Snapshot the whole data directory
Copy snapshot data to another drive and mount as another data directory, where I can bring it online as another cluster.
Details for configuring user and settings files and maybe renaming the Db. Renaming May not be needed on its own private cluster.

So the qa box would have 10 clusters of 1db rather than 1 cluster with 10 DBS.
I'm sure that's hell for sharing resources, but it's qa, so that's not the priority.

Is this a good strategy? Is there a better strategy that an experienced psql dba would go to instead?


texts: Rebuild master database only in sqlserver 2012

texts: How can I rebuild system database master only when I rebuild system database. Is it possible?


texts:
I have been asked several times in interviews this question.

Why an unique key allows one null value?

I searched for the answers but I couldn't find one.
Please help me on this


texts: Querying foreign table with SP-GiST index results in "ERROR: cache lookup failed for type 0"

texts:
I'm using PostgreSQL 11.3 (compiled by Visual C++ build 1914, 64-bit) and I want to access a table in another Postgres database using postgres_fdw but since the original table has an SP-GiST index on a geometry column, the query on the foreign table fails with "ERROR:  cache lookup failed for type 0", even if the table is empty.

Does anyone knows how to query the foreign table without throwing the error and still keeping the SP-GiST index on the remote table?

Here's a sample SQL to reproduce the error:

Create remote database:

CREATE DATABASE remote
    WITH
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'English_United States.1252'
    LC_CTYPE = 'English_United States.1252'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

Create local database:

CREATE DATABASE localdb
    WITH
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'English_United States.1252'
    LC_CTYPE = 'English_United States.1252'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

Setup remote database (connect to 'remote' database before running the following SQL):

CREATE EXTENSION postgis;
CREATE EXTENSION postgres_fdw;

CREATE SCHEMA remote;

CREATE TABLE remote.locations
(
    id bigserial NOT NULL UNIQUE,
    name character varying,
    lonlat geometry NOT NULL,
    PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
);
CREATE INDEX remote_locations_lonlat_idx
    ON remote.locations USING spgist
    (lonlat);

Setup local database (connect to 'localdb' database before running the following SQL):

CREATE EXTENSION postgis;
CREATE EXTENSION postgres_fdw;

CREATE SCHEMA localdb;

CREATE SERVER remote FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', dbname 'remote', port '5432', extensions 'postgis');
CREATE USER MAPPING FOR postgres SERVER remote OPTIONS (user 'postgres', password 'YOUR_POSTGRES_USER_PASSWORD');

IMPORT FOREIGN SCHEMA remote FROM SERVER remote INTO localdb OPTIONS (import_not_null 'true');

Querying the foreign table...

SELECT * from localdb.locations;

...results in:

ERROR:  cache lookup failed for type 0
CONTEXT:  remote SQL command: SELECT id, name, lonlat FROM remote.locations
SQL state: XX000


texts: Unable to apply patch in Oracle Grid Home

texts:
I need to set up a RAC environment using two nodes. Details of each server are:

OS : Windows Server 2012 R2 64bit ( certified by Oracle )

RAC: Oracle Grid 11.2.0.4

While installing the Grid Software, I am getting following error:

I came to know from Oracle Metalink, this is bug and had been solved in patch 20502905. I tried to apply this patch using the latest opatch 11.2.0.3.12 (required 11.2.0.3.5 or later) but I am unable to apply this patch because I am getting this error:

Since RAC installation is not completed yet that's why database has not been installed. As I know to use opatch we need to set ORACLE_HOME to database home and need to set OPatch directory path in the PATH variable of OS. But I can not set the path of ORACLE_HOME.

Could you please suggest me how to proceed. Any help is appreciated.


texts: Is it possible to write a SELECT statement to obtain all dates between 2 date columns?

texts:
My question is related to these 2 questions:

https://stackoverflow.com/questions/17529860/how-to-list-all-dates-between-two-dates/27319120
https://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function

However in my case, I have a table with an ID,StartDate,EndDate columns and I want to apply the same approach (but instead of 2 days, I want to use the entire table).

And before dropping into functions, I wanted to see if this is possible w/ a select and a date dimension table.

Basically I want to scale this up to the entire table:

SELECT
    t.Id
    ,dd.[Date]
FROM dbo.DateDimension dd
    ,dbo.T1 t
WHERE
    t.Id = 957
    and  dd.Date>=t.StartDate and dd.Date <= t.EndDate

I have Sample Data + Desired Output (The Select Statement) here:
www.sqlfiddle.com/#!18/0e389/2


texts: "Lock request time out period exceeded" when publishing SqlServer databases in parallel

texts:
I am working on a continuous integration environment using SqlServer2014. There I need to publish many SqlServer databases simultaneously on the same instance. Our publish process uses a .sqlproj file executed by MSBuild, which generates a publishing script.

Sometimes, during this publishing process we got an error: "Lock request time out period exceeded". It happens when two or more publishing scripts are being executed in parallel, more specifically on the procedure sp_fulltext_database.

Searching for further explanations, I found out that the default lock timeout limit is -1, which is unlimited. Even so, the error keep showing. Trying to change the lock timeout limit to 20 seconds through the query:

SET LOCK_TIMEOUT 20000;

The error did not happen again.

My question is: Is this the only way to solve the problem? Changing the LOCK_TIMEOUT? Since the publishing process is done by a auto-generated script, I do not think it is a good idea to change the script manually.

Edit:
Using the SqlServer profiler during a deploy I found out that something is changing the lock timeout to 5 seconds. This command does not exists in my publishing script, so I assume that an internal command or configuration is doing it. Going to Sql Server Management Studio and right-clicking on the server > properties > advanced > parallelism I found some configurations, but they do not seem to affect this automatic definition of 5 seconds.


texts: What would cause slow disk access on an AWS EBS SQL Server? (PAGEIOLATCH_SH)

texts:
I have an EC2 instance (r5a.4xlarge) which should be able to do 2Gbps+ in EBS access. This machine has a 7TB mounted gp2 volume, with 16,000 IOPS, which is mounted and contains a SQL Server database on the H: drive. The database is about 5TB in size.
When running SQL Server commands (specifically, reading from the drive and writing to another drive), and monitoring the the volume throughput, on this volume / drive, commands are taking an incredibly long time to process and SQL Server is showing a PAGEIOLATCH_SH lock, with 34ms response times (other volumes are 1ms or 2ms).
Microsoft's literature says this is a disk subsystem issue. We do not have this problem with other drives / volumes on this machine.
What needs to happen to get this from 34ms to < 2ms, like we are on the other drives? Something we're not configuring properly?


texts: Database migration - Simply change connection string?

texts:
We are migrating our database (MySQL/MariaDB) onto a dedicated server.  Previously, our web server hosted the database.  I've already set up master-slave replication between the web server and the new database server, and the actual replication is up-to-date (0 Seconds_Behind_Master).

It would seem that the only remaining step is to change the connection string in our app code to connect to the new database server instead of the old DB hosted on the web server.  Is this correct?

Of course, this assumes that we have not enabled the flags on the database server to prevent writes to the slave (we did not enable this flag, so we should be good there).


texts:
I was hoping someone could steer me in the right direction with this one. I have a table that has student grades.

Create Table StudentGrades
(
  StudentID references Student(StudentID) int not null,
  GradeIssued varchar(1) not null,
  WhenIssued datetime not null default(getdate())
)

I'd like to write a query that does something like: Find students who have grades (in sequence) of a D, then a C, then another C, then an A. I know the direction (because of the date the grades are issued) that the pattern should be in, but I can't think of how to write this without using a cursor.

In my experience with querying I'm filtering through via WHERE and HAVING clauses, but I'm not sure where to go when I want to find a particular pattern.


texts:
I have heard that Postgresql uses an append-only format for it's databases, and if this is true I should be able to 'rewind' the database to a previous point in time by removing the commits that came after it.

Anyone know how I can do this?

Thanks


texts: How to prevent LCK_M_IX waiting/locking on the following Delete and Insert queries

texts:
*Edit: Solution turned out to be nothing in the answers here, I was using SQLAlchemy in Python, which is an ORM. I was executing the delete statement below in a transaction, but never committing it. This lead to around 10 open transactions occuring that eventually all needed to be rolled back, thus locking the entire table until rollback was complete.

Given the following table structure, records are being continuously inserted with the update_time of a very recent datetime. A separate Connection to the DB is periodically trimming old records with dates of older than 2 weeks.

Table Structure:

The following Insert is being run very frequently, with different values:

The following delete statement is repeated until required, running a select right after to see if the process is complete

delete top(5000) from trade_options with (READPAST) where update_time < '<Two Weeks Ago>'

Activity Monitor shows that the Delete is locking, and the Insert is waiting ( LCK_M_IX):

Thanks

**Edit: Here are the Indexes/Keys as script outputs

CREATE NONCLUSTERED INDEX [IX_order_option] ON [dbo].[order_option]
(
    [update_time] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[order_option] ADD PRIMARY KEY CLUSTERED
(
    [exchange_id] ASC,
    [symbol] ASC,
    [update_time] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


texts: Can an index refer to only one or a subset of possible values?

texts:
I am using Postgres.  The table looks something like this:

tran_id SERIAL PRIMARY KEY,
account_id integer NOT NULL CONSTRAINT fk_account_id REFERENCES base.account(account_id) ON DELETE CASCADE,
tran_type base.my_enum not null,
tran_note varchar(64) not null,
process_id integer not null,
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP

I am interested in being able to quickly find only the rows where process_id is 0.  That value means that the row is pending for some kind of processing, non-0 rows are basically old log that I don't want archived because the application also needs to find the number of transactions for a certain account.  So it is convenient to have the processed and pending data in the same table.  I am not, however, interested in being able to quickly find rows where that field is anything other than 0 and I don't want to use resources on indexing those other values.

This table, in one implementation, has close to 200K accounts, each account around 200 transactions on average so about 40M rows, give or take.  At any given time, there are maybe a few dozen new transactions to process, i.e. WHERE process_id = 0.  Once it is processed, the process_id is updated to something other than 0.

I will never be searching for process_id that is other than 0.  So I would be interested in having an index that points to only rows where that value is 0 and not necessarily bother with other values so that the index is more compact and faster.  Is this possible?


texts: How to export specific columns from a SQL table

texts:
I am cleaning some data bases and was wondering that if there is any proper way to export any single column and all it's entries from a SQL table.

Being specific, I am dealing with SQL tables in phpmyadmin; if i am not wrong, there must be a SQL query for such tasks.


texts: I have a client that has a recently expired SQL Server 2008 R2 Enterprise Evaluation Edition.  I've been tasked to get it licensed and running SQL Server 2008 R2 Standard.  How can I achieve this?


texts:
What is the difference between an after update and a before update in PostgreSQL? I couldn't understand the difference between after update and before update because it looks like the function was always executed before update.

So I made the following example:

I made a function that updates a table when the status is typing but with a delay of 10 seconds.

CREATE OR REPLACE FUNCTION fai_prueba()
  RETURNS trigger AS
$BODY$
begin
    if new.status = 'Typing' then
        update image set status = 'ToTyping', path = 'Real path' from pg_sleep(5) where id = old.id;
    end if;
    return null;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION fai_prueba()
  OWNER TO postgres;

then I have the following trigger

create trigger tai
after update on image
for each row execute procedure fai_prueba();

but when I run an UPDATE the query does not end until the delay ends

UPDATE image
    SET  path='fake path'
       , status= 'Typing'
WHERE id=5;

>Query returned successfully: 0 rows affected, 10042 ms execution time.

So is it possible that the update query ends before the trigger?


texts: pg_dump fails to create a backup when running from System User in Windows Task Scheduler

texts:
I am trying to run a pg_dump through a scheduled task in windows. I have two batch files. One creates the task. The other one runs the pg_dump. Even though the pg_dump works with the current user with no problem, I have to run the pg_dump as a System because I want it to run even if the user is not logged on. Unfortunately, to run a task when a user is logged off, you have to run it in System user and that doesn't work for pg_dump. I couldn't see any error. It simply creates an empty backup file and after that, pg_dump keeps on running, blocking future runs.

Part of my pg_dump code is the following.

%PG_BIN%pg_dump -h %PG_HOST% -p %PG_PORT% -U %PG_USER% -F c -b -v -f %BACKUP_FILE% %DB%

Part of my Schtaks code that creates the task.

SchTasks /Create /SC MINUTE /mo 30 /RU "SYSTEM" /TN "Database Backup" /TR "%SCRIPT_DIR%backup_db.bat"


texts: Why are Oracle processes only using half the SGA set?

texts:
I have an Oracle set up on a remote server that's maintained by a third party.  Recently it became corrupt and we replaced it with a new instance.  Since then it's been running slower when performing some searches and even timing out.  I've been told by the people that maintain the database that the memory allocation is set as follows:

SGA: 6GB
PGA: 4GB

However when I run top in Linux I can see that all except one of the processes only use ~2.5GB as indicated by the RES column.

I know that there are other instances of oracle running on the server however there's 32GB of physical memory in the system and so it should be able to handle it.

Is there any reason why processes wouldn't be using as much of memory as should be expected?

NB:  When the system was brought back up the people that maintained it put half the physical memory (16GB) in the server box that should have been put in.  This was rectified a month ago and another 16GB added but didn't fix the problem.


texts:
I am curious as to whether the performance of postgres depends on the amount of RAM available. i.e. will postgres performance(during reads) on several runs get affected by the amount of RAM that is available in the system. For example, would postgres performance after several runs be different for 4GB, 8GB, 16GB, 64GB systems.

I noticed change in postgres's perfomance. I want to acertain whether it is because of the RAM available in the system or not. Can someone please help me with this?


texts: Create Meaningful Visualization for Large Set of Tables

texts:
I'm trying to create a meaningful, usable visualization for my company's ERP database. I've extracted a skeleton of the database so I can make changes without modifying the production environment. Here are some facts:

The DB includes almost 1300 tables, approximately 500 of which have data.
Our ERP provider hasn't added any relationships to the tables.
This is an MSSQL database.

Right now we only want to generate a visualization for the tables we actually use. My question is multifold:

Is there any automated way of suggesting relationships between the tables, or will this have to be done manually?
~500 tables is too large to have in one document while being easily navigable. We're considering breaking this up into multiple diagrams for each logical unit of data (e.g. Customers, Items, Billing, Raw Materials, Inventory, etc). Is there another way that would provide logical separation with manageably sized documents?
What is the best way to generate a diagram of the tables? I know MS SSMS has a built-in function for generating diagrams, but what other options exist? The final document could be printed large sheet paper, PDF, etc.


texts: Differentiating tables and views in INFORMATION_SCHEMA.COLUMNS

texts: I have a view which selects information from INFORMATION_SCHEMA.COLUMNS, which seems to give me both columns and views. Is there an easy way to tell which rows are columns and which are views? Should I do a comparison with INFORMATION_SCHEMA.VIEWS? I need both columns and views, I just want to be able to tell which is which.


texts: MySQL - SELECT from multiple tables by unique attribute, return one row with all values

texts:
I have the following three tables:

t1:
id | name
---------
1  | joe
2  | jon

t2:
id | street
--------------
1  | "8th St."

t3:
id | postcode
--------------
1  | "OC24 8BB"

How, using MySQL SELECT, can I specify a value for the common unique attribute 'id', and receive a single row combining the values from the three tables, with NULL values if none were found?

For example, if id = 1, I would expect the result:

id | name | street    | postcode
----------------------------------
1  | joe  | "8th St." | "OC24 8BB"

Whereas if id = 2, the following would be returned:

id | name | street | postcode
------------------------------
2  | jon  | NULL   | NULL

My current best attempt at the select is the following:

     SELECT A.id, A.name, B.street FROM t1 as A LEFT JOIN t2 AS B
     WHERE A.id = 1
     UNION ALL
     SELECT A.id, B.postcode FROM t1 as A LEFT JOIN t2 as B
     WHERE A.id = 1;

However this does not return the null values I require, I feel there is a simple solution however I have been looking for a while now and not found anything that has worked for me. Thank you if you can help shine some light on this problem.


texts: How to set "PRAGMA foreign_keys = ON" each time I'm connecting to sqlite db via Datagrip

texts:
In my pet project I'm working with small sqlite db and I've just came to realization that, in order to ON DELETE CASCADE actually do the thing, I need to set PRAGMA foreign_keys = ON each time I'm connecting to db.

However I'm using Datagrip and since it's quite new product to me I don't what's the best way to set this option every time.


texts:
I have two queries which are doing basically the same thing, but have different grouping, the first query (query 1) is used to populate a chart, and the second to populate a table.

Query 1:

SELECT key_id
       ,sum(salary)
       ,sum(bonus)
       ,created_at
FROM table
WHERE emp_id = 1
GROUP BY key_id, created_at

Query 2:

SELECT key_id
       ,sum(salary)
       ,sum(bonus)
       ,count(*) OVER() AS full_count
FROM table
WHERE emp_id = 1
GROUP BY key_id

I have created a function that returns those two queries in a json format, chart: [...], table: [...]. The problem is that I need to query the same table twice, because of my grouping. Is there any way of dealing with this situations?


texts: how to edit and migrate DTS package on SQL server 2000

texts:
I currently have an old SQL server 2000, with some DTS packages in system database msdb, table msdbsysdtspackages.

I need to migrate those packages to sql server 2016, is there a tool I can do that?

if not, is there a way I can know what are those packages doing, so I can at least rewrite them? Because now they all stored as hex data, and I don't even know what are they doing...

Thank you and any advice would be welcome.


texts: Sync MySQL database and Quickbooks Database

texts: I use Quickbooks as an accounting software. I also have a MySQL database where I record data on orders, customers, inventory, employees and etc. Is there a way for me to sync the two databases? For example, I would like to enter order details on Quickbooks and would like that order details to be in my MySQL database as well. Can someone please advice?


texts: Will MySQL redirect a read request to the master after a certain time?

texts:
I can't find what variable controls this or any information about the time limit.

Thanks!


texts: How to tell when DBO was changed (and if possible, by whom)

texts:
I am looking for a way to track when the DBO account is changed.  For example, if someone uses sp_changedbowner is there a way, after the fact, to determine when this occurred, and even better, what user was used to make the change?

This would be useful in tracking changes in the event of a database breach where teh attacker changed permissions, for example, so I suspect the possibility must be there.

I've been attempting to google it and look for it in the MSDN documentation, but I'm just not finding this.


texts: Performance improvement on SQL query joining table on itself

texts:
I'm running this query on a table with approximately 20MM records in it.  It's a very slow query, and I'm wondering if there's a better way to accomplish this that might run faster ?

delete previous
output Deleted.*
into table_A
from table_B as previous
inner join table_B as latest on previous.field_1 = latest.field_1
    and previous.field_2 = latest.field_2
    and previous.field_3 = latest.field_3
where previous.[feed_id] < latest.[feed_id]
    and previous.[condition_1] in (
        '1'
        ,'2'
        )
    and latest.[condition_1] = '3'
    and latest.[feed_id] = 500


texts:
On Windows 2008 R2 running SQL Server 2008 R2 how imporatant is the NTFS allocation unit size on DISK IO performance. It appears to me that server admin who built the few servers for a mission critical app left NTFS allocation unit size (cluster size) to default 4 KB instead of 64 KB. SQL server is already installed.

Does it worth to take pain -- to uninstall SQL -- format the drive with 64 KB cluster size and reinstall SQL server?


texts: Does backing up a database shrink the transaction log size?

texts:
I am trying to gather some knowledge on SQL databases and I have some questions about the transaction log file (LDF).

First off, when you create a database, you have to define an Initial File Size for both the database and the log file. From what I can see, once the files are created on the disk, they will be of that specified size regardless if there is actual data in the database or if there has been any transactions (logs).

My understanding was that backing up a database:

Truncates the transaction log, and
Shrinks the size

of the LDF file on the disk by "emptying" the logs inside it.

Now it seems that I was not understanding this correctly because the log file seems to be of fixed size. My actual question goes like this:

What does truncating logs actually do to the log file (LDF)? This process is supposed to prevent disks from getting full.

Please do correct me if I am not understanding some concepts correctly.

Thank you!


texts: How do you read a one-to-many relationship in both directions?

texts:
I'm having issues with reading this relationship.

Let's take the Professor and Class entities in this example. If I read this from Professor to Class, I would say, "A Professor teaches one to many Classes". If I go the opposite direction, from Class to Professor, I would initially read this as "A Class can be taught by one and only one Professor".

Is this the correct way to describe those relationships in both directions? Or, rather, is the direction from Class to Professor a "many to one" relationship?

I think I may be mixing up two different things (cardinality and relationships), but I'm not entirely sure.

Source

This screenshot was taken from California State University, Bakersfield's ERD PowerPoint


texts: Cannot connect to Oracle database server through SQL Developer but Test works OK

texts:
I am new to Oracle (from a SQL server and MySQL background) and have just installed Oracle 11g2 Express Edition.

I have downloaded SQL developer which appears to be the Oracle world's equivalent to SSMS.
I have gone to the new connection window, given my connection a name, used SYSTEM as the user and my password that I set up at installation.
I have left hostname/port/sid as default (localhost/1521/xe respectively)

If I click test I get "status: success" However, if I click the connect button, nothing happens.

What am I missing here?


texts:
In results of Show INNODB Status showed me very weird result:

Pages made young 81821279, not young 0

The official statement says:

Pages made young, not young: The number of old pages that were moved to the head of the buffer pool (the new sublist), and the number of pages that have remained in the old sublist without being made new.

What I get from this is that there were zero pages that sustained in pool or in other words my pool size is too small to sustain pages.

Am I correct? If not what does it mean then?

Should I be increasing buffer pool size? And is there any other ways to resolve this because my buffer pool size is already 80% of my RAM.


texts: Is this a good database design or transition? Normalize to Denormalized for Data Warehousing

texts:
As of now in sql server , we have a  data and a series normalized table structure that is connected to a C# web app (Human Resource / Recruiting System) (slightly medium (or small 12,000 rows.) size as of now) that does create , read , update and delete.

In the future (2 years time) , we are planning to turn this database into a data warehouse. however in data warehousing normalization in not exactly needed.

My senior programmer(who is not a programmer, but more on business intelligence) suggest that we should denormalize all tables on the same database (Kimball's data warehouse) connect that to the C# web app , do the crud and if test succeeds the data put it in azure data warehouse.

So my main question is this a good thing or is there a better way? like extracting the data from the normalized table and putting it into a new database with the denormalized table? Is there any other way?

Please excuse , I'm more on software development usually using an ORM and not much on business intelligence.

Thank you to All.


texts: How do I configure SQL Server 2012 so that it can restore and see files in my user account?

texts:
I have an SQL Server 2012 instance running as a service on my computer, and according to the service page it logs on as account "NT Service\MSSQLSERVER".  However I can't see that account name anywhere else including in the "Local Users and Groups" area in the computer management screen, because, as a link below says, that's not a User Account, it's a service name, in that box that Microsoft so helpfully labelled "account".  At this point I can see many people being confused.

The task I'm trying to accomplish is to restore files using the SSMS dialog "Locate Backup File", which uses a dialog completely unlike any of the standard windows file open dialogs, probably because it's doing a "remote" job and operating from the security context of the SQL server, another rich source of end user confusion, that I hope this question might help clear up.

So far if I want to restore a backup .mdf/.bak file that I have in one of my folders, I have to set that folder to readable by Everyone or else I can't get in there with the SQL Server "Locate Backup File" window.   I find this idea that you're using a GUI talking to a service that has different user accounts and rights than you, that nobody at Microsoft even cared to make clear to you, very confusing even when I have years of experience with windows system administration.

I'm hoping I missed some documentation pages for SQL Server that would tell you, after installing a new SQL server instance, how you might set up security.

Forum posts like this one have even Microsoft staffers saying "this is complicated", and that it "changed, again" in Denali.  How does this work now in SQL Server 2012, and how might I add permission to read files belonging to Users to the SQL Database engine's security SID.


texts:
BRIN indexes seem useful, but I'm not sure how to use one on an ENUM type.
I thought this code would work:

CREATE TYPE test_enum AS ENUM ('a', 'b');
CREATE TEMPORARY TABLE my_table (
    x test_enum
);

CREATE INDEX test_index ON my_table using brin (x);
ERROR:  data type test_enum has no default operator class for access method "brin"

Do I have to create a new operator class from scratch? Aren't enums already ordered?

This commit, from 2014, implies that BRIN indexes should work for ENUM types.


texts: Is it bad practice to launch more than 100 sql statements at once?

texts:
I have PHP code that is meant to updating the billing status of someone. I only want it to update if the bill has been successfully created and therefore I think a possible solution would be to perform an update after each bill. However, this means that I will end up performing over 100 queries in a very short amount of time.

As I am unfamiliar with database management in general I want to pose this question here.

Perhaps there is a query cache limit that I don't know of or perhaps this is a really bad practice in the database community such that I should be expelled for even proposing this.

I could make a work around where I just collect all of the successfully generated bills and then apply updates to those people, but then I would still need a whole lot of "WHERE" clauses to update only those customers.

Suggestions, ideas, concerns, quips?

I am using postgres for this database, but I would prefer answers that are not specific to Postgres as this database has been migrated before.


texts: How does MySQL coerce types during joins?

texts:
Why do these two queries behave differently:

mysql> explain select FOOBAAR_hhs.relationship_group from FOOBAAR_hhs left join FOOBAAR on FOOBAAR_hhs.relationship_group = FOOBAAR.relationship_group where new_hh_id = 15387929;

| id | select_type | table       | type  | possible_keys | key       | key_len | ref   | rows     | Extra       |
+----+-------------+-------------+-------+---------------+-----------+---------+-------+----------+-------------+
|  1 | SIMPLE      | FOOBAAR_hhs | ref   | new_hh_id     | new_hh_id | 9       | const |       15 | Using where |
|  1 | SIMPLE      | FOOBAAR     | index | NULL          | BAARn     | 126     | NULL  | 83236197 | Using index |

mysql> explain select FOOBAAR_hhs.relationship_group from FOOBAAR_hhs left join FOOBAAR on cast(FOOBAAR_hhs.relationship_group as char) = FOOBAAR.relationship_group where new_hh_id = 15387929;

| id | select_type | table       | type | possible_keys | key       | key_len | ref   | rows | Extra       |
+----+-------------+-------------+------+---------------+-----------+---------+-------+------+-------------+
|  1 | SIMPLE      | FOOBAAR_hhs | ref  | new_hh_id     | new_hh_id | 9       | const |   15 | Using where |
|  1 | SIMPLE      | FOOBAAR     | ref  | BAARn         | BAARn     | 22      | func  |    3 | Using index |

?

The relationship_group members of both relations are typed VARCHAR(20) NOT NULL, but it is only when an explicit CAST is invoked that MySQL permits use of the available index?

(This is MySQL version 5.1.52-log)


texts: Is it possible to obtain number of prepared statements a connection holds?

texts:
I have a heavy loaded application that holds connections persistently (it creates them on start and never releases).

During the lifetime it creates and (re)uses prepared statements, which are stored in an LRU structure (so the least used are eventually released).

And the question is: is it possible to obtain the number and other statistics of prepared statements for a given connection?

There is nothing relevant on http://www.postgresql.org/docs/9.3/static/monitoring-stats.html so not sure if there are other interfaces to access postgresql stats.

UPD: I probably was not clear enough: I need to obtain information using the normal DBA connection, since I cannot run arbitrary queries from the production software. For that very reason the pg_prepared_statements solution would not work.


texts: Does mysql make some internal maintenance after some uptime?

texts:
I've got a dedicated mysql server (5.5.44) which runs now about 202 days. Between Friday at about 11:00 a.m. and Saturday at about 3:30 a.m. I've got a very high load on that server (my monitoring shows a system load from up to 25). I've checked serveral log files but can't find anything what goes wrong. I've also checked the mysql-slow.log and there I discovered, that there are queries logged, which are normally never logged. The first query from that file shows me

Query_time: 11.387071  Lock_time: 0.000045 Rows_sent: 8  Rows_examined: 9975109

When I take the exact same query now and run it directly, I got in the explanation that only 19023 rows have to be examined. The slow query log is full of such examples.

So it looks to me, that some internal indexes from mysql have failed. Could that be? Or is there anything internal maintenance running in mysql?

By now, the mysql server is fast as before. But what happened in the time between friday and saturday?


texts:
I would like to put in place a cluster with 2 instances of Graylog. Ideally, one of them will receive logs from servers in the cloud and the other one on premise will receive logs from servers that are running on the premise itself. After reading the documentation, I've understood that I have to put in place a replication of Mongodb instances.
Mongodb recommended to have an odd number of servers in the cluster but I would like to ask whether or not it's mandatory for the cluster to function properly or would it be ok to make a cluster only with 2 servers?

At this point, it seems like my Graylog instance located in the cloud will get logs only from servers in the cloud. Same is applicable for the on premise Graylog instance which will get logs only from on premises servers. According to the documentation, the data from the primary will be replicated on secondary servers. However, I wonder whether or not primary servers will add the data over the data which is already existing on the secondary instance. Will it replace them instead?
Could you please let me know how can I get 1 instance in the cloud and one on premises yet reach all the of my data from one point/location?

Thank you for your help in advance


texts: How to optimize MySQL for large BLOB updates

texts:
I have a table that holds large BLOBs (up to 10M), and is updated frequently. The problem is that UPDATE statements can take up to 1 second to execute, and due to app design this blocks UI. I need to speed up those  UPDATE/INSERT statements. Is there a way to do it by tuning MySQL server/storage engine/etc?

The table in question is InnoDB, and I have tried tunring compression on, but that didn't seem to make a lot of difference. Client is on the same machine as server so there is no network overhead. Server is MySQL 5.5


texts: Deploying and running SSIS packages to SQL Server with protection level EncryptSensitiveWithPassword

texts:
I have an SSIS package that connects to both a SQL Server instance and a MySQL instance so it can feed data from the SQL Server to the MySQL instance. When I deployed this package to the SQL Server and ran it from SSMS, everything went fine. When I tried to run this package as a Job where it was being run by the SQL Server Agent Service Account, it broke. I found out that this was because I was using EncryptSensitiveWithUserKey as the package protection level, and that it will always fail unless I am the one running it. Okay.

So among the other protection levels, I am instead trying to use EncryptSensitveWithPassword. In my SSDT that I run from Visual Studio, I do not have ServerStorage as an option for a protection level.

This opens up a different problem for me. Once I deploy the package to SQL Server and run it using SSMS, How do I insert the password to the job? I have seen the command line as an option. Is this the only way to do it in SSMS?

If I require the command line being used as an option, then it requires the package source be the SSIS Package Store rather than the SSIS Catalog. This ends up being a problem if I get 'Class not registered' errors when trying to connect SSMS to Integration Servers — but this is beside the point.

Having had only several weeks of exposure to SSIS, the underlying conceptual question I am wondering is, what are my options for (and how do I go about) getting a MySQL connection enabled in an SSIS package on SQL Server for a user other than myself?

(FYI, I am using SQL Server 2014 and it has Integration Services 2005 on it, and I understand the version mismatch causes the 'Class not registered' errors.)

Addition: Is there any completely different ways to supply a datasource password to a package run by the SQL Server Agent? If I choose DontEncryptSensitive as the protection level, is there a way to supply the datasource password within the job step properties?


texts: Query against history table not using index (Oracle 12c)

texts:
I have a history table which has an 'ID' and 'TIMESTAMP' column as such

CREATE TABLE hist (
  HIST_ID INTEGER,
  HIST_TIMESTAMP TIMESTAMP,
  ID INTEGER, -- this is the id of the table that is being tracked
  --OTHER COLS
);

I also have an index on this table as such

CREATE INDEX hist_ix ON hist (ID, HIST_TIMESTAMP);

This table has a lot of inserts against it and currently has about 30m rows in it.

When I try to run the following query, oracle does a full table scan instead of using the index (which .. at least I believe .. it should be able to use).

SELECT ID, MAX(HIST_TIMESTAMP) FROM hist WHERE HIST_TIMESTAMP <= <<A TIMESTAMP>> GROUP BY ID;

It seems to me that Oracle should be able to use the index to quickly identify which id/timestamp pair is just to the "left" of a specific point in time quickly via looking at the id/timestamp index on an id-by-id basis, but it's insisting on a full table scan.

Any help would be appreciated to get this query running quicker.

I have ran the following to make sure the statistics were up to date

EXEC DBMS_STATS.GATHER_TABLE_STATS('<meh>','hist');

Also, there are about 1k distinct ID values in the hist table.

With regard to data distribution..  Of the ~1k IDs, 50 have less than 100 entries in the table, 70 have between 100 and 1000 entries, 146 have between 1000 and 10000 entries, and the rest range from 10k to 60k entries. Over half of the entries have at least 30k records.


texts:
As I try to load data into Oracle, I've encountered SQL*Loader-605: Non-data dependent ORACLE error occurred error.

Data is (are?) in .txt file and its size is 95GB.

Here are some more details about the error:

  value used for ROWS parameter changed from 64 to 19

  ORA-01653: unable to extend table M.ECZ by 8192 in tablespace USERS

  SQL*Loader-605: Non-data dependent ORACLE error occurred -- load discontinued.

  Specify SKIP=8351926 when continuing the load.

  Table ECZ:
    8351925 Rows successfully loaded.
    0 Rows not loaded due to data errors.
    0 Rows not loaded because all WHEN clauses were failed.
    0 Rows not loaded because all fields were null.

  Space allocated for bind array:                 245100 bytes(19 rows)
  Read   buffer bytes: 1048576

  Total logical records skipped:          1

  Total logical records rejected:         0

  Total logical records discarded:        0

What do you advice? Thanks.


texts: Join hive tables which are being populated in real time

texts:
I have two tables in hive which are being populated in real time from a set of files. There is a request_time and trx_id columns in both tables where trx_id is the joining key.

I want to join them and put the result in another table (in real time or near-real time). The problem is how do I ensure that I have not missed any records. e.g. what if a trx_id had arrived in one table and not the other?

My initial solution is to keep a track of the trx_id in another table for a particular time period which have been processed and when joining the two tables exclude the records which exist in this table. However it would be a bit time consuming process. Any suggestions?


texts: Why SQL Server's MERGE has such a huge Excessive grant when compared to INSERT + DELETE?

texts:
I am trying to optimize a security computation process for a Web application by trying to making it synchronous (compute on the fly for an entity) instead of asynchronous (large computation for all entities once in a while).
I have broken down the business rules and the final step is to update the security information (INSERT, DELETE several dozens/hundreds records in a table that contains several million rows). The table definition is the following:
CREATE TABLE [dbo].[ComputedSecurityContract](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [int] NOT NULL,
    [ContractId] [int] NOT NULL,
 CONSTRAINT [PK_ComputedSecurityContract] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

My first trial involves a MERGE that seems to take more than 95% of the whole stuff the stored procedure is doing:
MERGE vComputedSecurityContract AS TARGET
USING #tmpContractSecurity AS SOURCE
    ON (TARGET.UserId = SOURCE.UserId AND TARGET.ContractId = SOURCE.ContractId)
-- when matched there is nothing to be done
--When no records are matched, insert the incoming records from source table to target table
WHEN NOT MATCHED BY TARGET
THEN INSERT (ContractId, UserId) VALUES (SOURCE.ContractId, SOURCE.UserId)
--When there is a row that exists in target and same record does not exist in source then delete this record target
WHEN NOT MATCHED BY SOURCE
    AND (@UserId IS NULL OR TARGET.UserId = @UserId)
    AND (@ContractId IS NULL OR TARGET.ContractId = @ContractId)
THEN DELETE
OUTPUT $action,
    DELETED.ContractId AS DeletedContractId, DELETED.UserId AS DeletedUserId,
    INSERTED.ContractId AS InsertedContractId, INSERTED.UserId AS InsertedUserId;

Looking at the query plan, what struck me is the huge Excessive Grant (360MB, used some KB or 0 if no actual changes are to be made).
My second attempt is to break the MERGE into separate INSERT + DELETE statements and the relative cost of these queries is way less than that of the MERGE:
INSERT INTO vComputedSecurityContract (ContractId, UserId)
OUTPUT 'Inserted' AS Inserted, inserted.ContractId, inserted.UserId
SELECT T.ContractId, T.UserId
FROM #tmpContractSecurity T
WHERE NOT EXISTS (SELECT 1 FROM vComputedSecurityContract V where V.ContractId = T.ContractId AND V.UserId = T.UserId)

DELETE V
OUTPUT 'Deleted' AS Deleted, deleted.ContractId, deleted.UserId
FROM vComputedSecurityContract V
WHERE (@UserId IS NULL OR V.UserId = @UserId)
    AND (@ContractId IS NULL OR V.ContractId = @ContractId)
    AND NOT EXISTS (SELECT 1 FROM #tmpContractSecurity T where T.ContractId = V.ContractId and T.UserId = V.UserId)

I assume that the huge Excessive grant of the MERGE has something to do with this. Why these almost identical operations have such different performance results?
Note: @ContractId and @UserId are stored procedure input parameters.


texts:
I am exploring the plan guides topic. Sounds easy, as far as we go through the examples in documentation.

The problem is that I cannot find any working example (nor did I manage to figure it out by myself) for setting a fixed plan for a stored procedure, or a statement inside the procedure.

I've read lots of blog posts, but all of them just copy/paste examples from microsoft docs.

Anyone more creative/expirienced?


texts:
I'm trying to write a query to show the rows containing a code that begins with 11-digit number followed by -1 or -2 then _DataSheet or _RoHS

Examples

02704198976-1_DataSheet
03448106681-1_RoHS

Query

SELECT m.PK, m.code
FROM medias m
WHERE m.code LIKE CONCAT(REPLICATE('[0-9]', 11),'-[1-2]_(DataSheet|RoHS)%')

Results

0 rows returned

If I change the WHERE clause, then it shows too may results.

Query

SELECT m.PK, m.code
FROM medias m
WHERE m.code LIKE CONCAT(REPLICATE('[0-9]', 11),'-[1-2]_%')

Results

02704198976-1_DataSheet
03448100857-1_SDS
03448106681-1_RoHS


texts: How can I return column names in dot notation in MySQL?

texts:
I want to return column names into a table. But I also want to avoid redundancies like 'id' and 'id'.

I did:

INSERT INTO columns(name)
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name='contacts' or table_name='companies';

But as expected I get id from the first table showing up with the same field name.

How do I get it in the form: contacts.id and companies.id?


texts: Does tables under schema uses multiple tablespaces other than default tablespace

texts:
I have schema A in a database and have assigned the default tablespace as myspace.
There are 20 tables that reside under this single schema and all the tables are using different tablespaces.
My question is:
Why are all tables using different tablespaces instead of the default tablespace?


texts: How to structure renting and sale as type of purchase?

texts:
I'm currently planning a database which involves two different types of Products that can be bought outright, or rented for a weekly or monthly amount. I'm just trying to figure out how to structure the database for this.

I'd like to keep the Products all in the same table. The only way I've thought of so far is to have my Product table reference another ProductType table which has rental or sale (not sure on the wording here really), then have another table which is linked to the product which has the cost. I don't think this is correct however.

How can I structure this relationship? (I don't mind any changes to this at all, other than the Products all being listed in that one table still).


texts: I'm trying to make it so that local connections to the database don't require a login. So I added the line local    all   all    trust to the pg_hba.conf file, but then when I load pgAdmin and try to connect to the server it just says FATAL: could not load pg_hba.conf. What am I doing wrong?


texts: Performance implications of combining full-text indexing and FILESTREAM columns?

texts:
I would like to know if full-text indexing performance in SQL Server changes depending on whether a varbinary(MAX) column is stored conventionally or using the FILESTREAM feature. I am talking specifically about index population that occurs when change tracking is set to automatic, and when documents in formats that require iFilters (e.g. docx, pdf, msg) are stored in the column.
My understanding was that FILESTREAM would have very little bearing on full-text indexing performance, in fact it might even offer better performance by allowing FDHost to access the data directly - however, I now think this might not be the case.
Having enabled FILESTREAM for one particular database, I am now finding that index population is regularly timing out, with errors such as this recorded in the SQLFTxxxxxxxxxx.LOG files:
Error '0x80043630: The filter daemon process MSFTEFD timed out for an unknown reason. This may indicate a bug in a filter, wordbreaker, or protocol handler.'

Accompanying this are errors from the database engine when running queries:
Word breaking timed out for the full-text query string. This can happen if the wordbreaker took a long time to process the full-text query string, or if a large number of queries are running on the server. Try running the query again under a lighter load.

And I am also finding that full-text indexes in other databases on the server (which don't use FILESTREAM) are failing to populate from change tracking, requiring me to manually rebuild the index.
Are there any known issues with combining full-text indexing and FILESTREAM?
I will note that this is happening on SQL Server 2008 R2 (all service packs installed), on a production machine which is about to be upgraded to SQL Server 2019. The FILESTREAM data is about 100GB, spread over 200k rows.


texts:
I am trying to add a footer to a flat file output. I have 2 data flows in my control flow. One creates the file from the DB. It goes from OLE DB Source to Flat File Destination. The second control flow uses a second connection manager pointed at the same file path as the first data flow and uses. In the second data flow i am using a script component pointed at the flat file to append the trailer.

The issue is the script is not generating any output. I am a C# novice and have modified code found on the net and would appreciate any help.
Here is the scipt code in C#2012:

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent

 {

     public void Main()
     {
         const string dirPath = @"Q:\General\Operations\Prod_Support\XOOM OPS Procedures\Wells_Testing\";

         var fileBody = AddHeaderAndFooter.GetFileText(dirPath + "WellsPOS.txt");

         var trailerRecord = "9" + DateTime.Today.ToString("ddMMyyyy") + AddHeaderAndFooter.CountRecords(dirPath + "WellsPOS.txt").ToString();

         var outPutData = fileBody + trailerRecord + "\r\n";

         AddHeaderAndFooter.WriteToFile(dirPath + "WellsPOS.txt", outPutData);

     }
 }

 public static class AddHeaderAndFooter
 {
     public static int CountRecords(string filePath)
     {

         return (System.IO.File.ReadAllLines(filePath).Length + 2);

     }

     public static string GetFileText(string filePath)
     {
         var sr = new System.IO.StreamReader(filePath, System.Text.Encoding.Default);

         var recs = sr.ReadToEnd();

         sr.Close();

         return recs;
     }

     public static void WriteToFile(string filePath, string fileText)
     {

         var sw = new System.IO.StreamWriter(filePath, false);

         sw.Write(fileText, System.Text.Encoding.ASCII);

         sw.Close();

     }
 }


texts: how to turn off delimiters in generated SQL scripts for data types in SQL Server Management Studio

texts:
We are a software engineering firm. We use SQL Server Management Studio (SSMS) to generate the few scripts we need to build and upgrade our databases.
While it does an OK job, it bugs me that it insists on surrounding data types with []. So a column definition will be like:

[ID] [INT] IDENTITY(1,1) NOT NULL

while this just works, I don't like it because it does not distinguish the column name from its type in the script. Including syntax highlighting (everything is just black or your default font color). I was wondering if anybody had any idea on how to alter this behavior. We just want the output to be like:

[ID] INT IDENTITY(1,1) NOT NULL

Purchasing a specific tool is not an option at this time based on how much scripting we really do.

Thanks in advance for any help,

Eric.


texts: SSRS 2012 Report with Oracle database as datasource never finishes

texts:
I am investigating a SSRS 2012 Report which used to return data from an Oracle database. However, recently it stopped returning data. The report will sit there and run forever until something times out. Our report catalog database is also on a separate server which does not show any issues in the logs or while monitoring the performance. I do not see any errors in the Event log, SQL Server Logs, Reporting Services logs or the Oracle database logs. If I strip out the query from the report it returns rows in a few minutes. Is there a method for monitoring what is happening with the communication between the report server and the oracle server? How can I troubleshoot this issue if none of the logs are reporting any problems?

Looks like I am now getting Timeout errors in the report logs.

System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

When I run the query against the Oracle database using SQL Plus, it takes about 8 minutes to run.


texts:
I'm a SQL Server DBA, and I like to mess around with Oracle here and there.

I can see with select * from information_Schema.columns everything about a table (everything that I need now).

Is there a way to list these informations on a Oracle Schema?


texts: Unable to restore backup taken on one server onto another server

texts:
I am using SQL Server 2014 version 12.0.2000.8 on both systems. When I try to restore the backup taken from the first system onto the second system it says:

  No backup set selected to be restored

However I can restore it on the first system without any errors. I also tried to restore using the following command:

RESTORE DATABASE EBS_ATM
FROM DISK = 'G:\ebsBkup\ebs_bkup.bak'

The above command threw the following error message:

  Msg 3013, Level 16, State 1, Line 3
  RESTORE DATABASE is terminating abnormally.

I have checked rights and permissions on the folder as well as the .bak file.

Database version was checked through the query DetermineVersionOfSQLServer.sql in the Microsoft Knowledge Base article, How to determine the version, edition and update level of SQL Server and its components.

The database does not already exist on the second instance
RESTORE FILELISTONLY and RESTORE VERIFYONLY also throw error 3103
I know there should be another message before the 3013 eror, but I am getting only the messages I have posted.

The bak file is quite big ~70 GB. First I 7zipped and copy-pasted. Didn't work. Then I 7Zipped in 700 MB tranches. Didn't work. Finally I copied without zipping. Didnt work.

I would appreciate any help on this.


texts:
I am new to mysql administration and I came to know about the data storage in myisam is through .frm, .myd and .myi. I could not find the data storage mechanism in InnoDB rather than its storage in tablespace.

Could anyone help me to sort out the storage mechanism in innodb storage machine ?


texts: Is it a best practice to contrast table and dependent view metadata to target which views to refresh?

texts:
We have non-indexed views created on vendor tables. When they change table structure or column definitions we run sp_refreshview as needed. What is the viability of a script to contrast the table column definitions with the view metadata to verify which views need to be refreshed? Is it a best practice to find only views that change or is it OK to refresh all views? Are there any concerns with running sp_refreshview if it's not needed?

Thanks again.


texts:
I have been tasked with copying select databases from the production SQL server to the Data Warehouse (at least nightly). However, no matter how I try to copy the data over, I run into an issue. Below is our setup:

Production Enviroment

SQL 2014 Enterprise
around 20 TB of data
Weekly Full backups, Nightly Diff (minus on night of weekly), every 15 minutes Transaction Logs backup

Data Warehouse

SQL 2016 Enterprise
In the same network/datacenter as prod
In theory a 10G connection between servers
Both in VMware both on same storage device

Here is the options I have found and the reasons it will be troublesome:

Mirroring - Breaks Full/Diff backups
Log Shipping - Breaks 15 minute transaction logs
Restoring from full/diff nightly - Too much data (still a possibility, but MGMT not happy about it)
Presenting same virtual drive on both servers - Causes issues with vmware/non-standard setup

All I have to do is get the data over, after that its up to our vendor to do the manipulations. Am I overlooking some options? Has anyone else run into this?  I am new to DBA, so any help is appreciated.


texts: How to display the dates without DUPLICATE and ORDER by MAX DATE

texts:
I just want to ask that how can I display the null values along with the MAX date.

SELECT
  MEMB.MEMB_N,
  MEMB.LAST_M,
  PrintDate
FROM
  MEMB
  INNER JOIN tblPrint
    ON MEMB.MEMB_N = tblPrint.MEMB_N
ORDER BY tblPrint.`PrintDate` DESC

My desired output will look like this but without duplicates.


texts: Can I stop a SQL database backup after it has already started?

texts:
I am looking to do a full database backup of a SQL database in SQL Server 2008.

My questions are:

Can I stop, pause, or cancel the backup after it has started if it is taking too long or slowing things down too much? Is a backup just a job, that I would just stop the job?
When doing a backup, are you able to view the progress, and see an estimated completion time of the backup? If so, how do you view its progress?