SQL Server Optimization/Configuration for Inflexible Application I'm responsible for overseeing an application which uses SQL Server as its backend storage solution. Unfortunately, the application has no concept of how a RDBMS should be utilized and seems to use it mainly like a NoSQL solution. What the application needs most is fetching and storing of single records and doesn't use any foreign keys for data integrity whatsoever.

As expected, this brilliant design leads to rather poor performance because throwing a RDBMS at a problem which merely requires a hash-on-disk style scheme is like using a cannon on a mosquito. But alas, I'm not able to modify the software as it is third party.

I've seen many tips that are happy to tell me I should use more efficient queries, but this simply isn't an option. SQL Server seems to be CPU bound mainly, and most of the data also seems to be cached in memory. Is there a way I can optimize/configure the way SQL deals with requests from this application? (The entire instance of SQL Server is available exclusively to the application.) Alternatively, is there another solution that behaves like SQL Server but acts like NoSQL in speed for simple queries? Is there some sort of single-process mode for SQL server that makes locking records unnecessary?

Any off-the-wall ideas I should give a shot?

Update:
So after a lot of mucking about with traces laced with auto-generated cursor preps, execs, fetches, and so forth, it looks like most of the time in the particular application feature of concern is wasted on populating tables for which only the first row is actually fetched. This actually appears to be a common problem the way the application was written.

Since the select statements behind the cursors are ordering the data, the cursors cannot effectively use a dynamic plan. Ultimately, the data the application is trying to get at is the next record where field A = document_id and the next field B value that's larger than the previously sought field B value. This is, of course, a typical application of cursors -- if only the application was using cursors the typical way.

I'm looking to contact the vendor again because this is... bad. I figure they're more likely to pop in an easy two-line already-existing fix. As such, I'm looking to generate a more efficient version of their query (still using cursors, with the same client-side semantics of 'give me the next record, k?'). I'm not exactly sure how to formulate such a query in TSQL since everything I've given a shot results in a scan, even with indexes I'd consider useful. (Really, only one b-tree search should be required, but somehow I just can't seem to get there.)

We've all been there. Those that haven't are going to run into this situation one day!

There is a text book approach to dealing with third party horror software:

Prove there is a problem. Identify and document crazy queries and data access patterns.
Approach the vendor with your evidence of a problem. If you can show that a re-write of a query shows improvements are possible, it gets harder for the vendor to ignore your concerns.
If your getting no response from your contact at the vendor, escalate. If you can't make enough noise to be heard, maybe your CTO can.
If you've reached step 4 without any success, you're likely to be sacrificing follicles or noticing the telling first signs of grey. This is where a voodoo doll of the third party development lead can ease the stresses of the day. A dartboard featuring the company logo is an acceptable alternative.

Back in the real world, what practical approaches do we have:

Kill it with iron. It always pains me to say it but throwing hardware at the problem can be the fastest, cheapest, lowest risk solution at times.
Understand the root causes. Run the same analysis as you would for a system that was fully under your control. Identify the most frequent queries, those with highest CPU, highest reads, highest duration, look at tempdb usage, analyse the plan cache (single use ad-hoc queries crop up often in these scenarios).
Indexing. The Database Tuning Advisor (DTA) is a useful tool for analysing workloads you have little control over. In particular, it can spot candidates for indexed views and missing multi-column statistics which may not be obvious.
Plan guides. If you spot a query that you can re-work the execution plan for, plan guides give you a way of forcing that plan without modifying the source query.


Execute grant for MySQL stored procedure revoked when stored procedure is altered I have created the following stored procedure in a MySQL database (MySQL 5.5).

DELIMITER $$

CREATE DEFINER=`remote_admin`@`%` PROCEDURE `usp_insert_deal_and_segment`()
BEGIN

    "code here"

END$$

This stored procedure is then used by multiple different scripts to insert data into the database.

There are a number of external scripts calling this stored procedure. These scripts use a different user when accessing the database (i.e. 'script_tradecap'@'127.0.0.1' rather than 'remote_admin'@'%').

So that the scripts are able to access this stored procedure the user 'script_tradecap'@'127.0.0.1' is given the following grants.

mysql> show grants for `script_tradecap`@`127.0.0.1`;
+------------------------------------------------------------------------------------------------------------------------+
| Grants for script_tradecap@127.0.0.1                                                                                |
+------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'script_tradecap'@'127.0.0.1' IDENTIFIED BY PASSWORD '*59195B949A078D5B540E25E9800C85AF83344E9D' |
| GRANT EXECUTE ON PROCEDURE `gudrun`.`usp_insert_deal_and_segment` TO 'script_tradecap'@'127.0.0.1'                     |
+------------------------------------------------------------------------------------------------------------------------+

Note that I specifically want to limit this user so that it can only execute the usp_insert_deal_and_segment stored procedure. I don't want the user to be able to execute other stored procedures.

After that the scripts are able to access the stored procedure via this user.

The problem occurs when I alter the stored procedure. This results in the execute on procedure grant for usp_insert_deal_and_segment being revoked from the 'script_tradecap'@'127.0.0.1' user.

Why is this happening and is it possible to prevent the grant being revoked if the stored procedure is altered?

Thanks,

Gerard

This happens because the stored procedure is actually dropped and recreated.  When the procedure is dropped it loses all its permissions.  Therefore, when the 'altered' version is created there are no permissions attached.

Your process to update the procedure needs to include a re-grant of the needed permissions.

I don't know your full environment, but here was a 2013 forum discussion for one MySQL tool set:
http://code.google.com/p/heidisql/issues/detail?id=3300


Is it possible to make an index for an OR query? I have a query like this:

select * from things where person_1_id=123 OR person_2_id=123

Is it possible to make some sort of index that covers this? Assume that the id (123) matched against will always be the same for both columns.

You could try splitting it into a union and indexing the two individual columns.

Your query would be

SELECT  *
FROM    Things
WHERE   person_1_id = 123
UNION
SELECT  *
FROM    Things
WHERE   person_2_id = 123

and index person_1_id AND person_2_id

Note we use a UNION rather than a UNION ALL so we don't get superflous records

Disclaimer:

I'm more experienced with SQL Server than any other platform, the above method can work in SQL Server but YMMV on other platforms


migrate SQL Server geography data to PostGIS geometry data I'm trying to use SSIS to transfer point data from a Geography column in SQL Server 2008 R2 to a geometry column in a Greenplum (Postgres 8.2.15) database with PostGIS.  I know there's a way to do the conversion outside of SSIS because I did it once a while back, unfortunately I don't remember what I did.

I figured it out.  I used a query for the SQL Server source that converts to POINT text using:

  geometry::STGeomFromWKB(myPointColumn.STAsBinary(), myPointColumn.STSrid).STAsText()

That was all it took.  Now whether the point will be represented in PostGIS the way I intend it to I can't say at this point, but I'm hoping so.


Should I opt for a new database or adding more tables? this is a question around best practice that I'm currently dealing with.
I have a database (SQL Server 2008 R2) with a heap of tables (around 40)
with data, now the need has risen to have logging of which Changes are made
not from a technical standpoint but for instance if a row gets updated some
users needs to have a "tracking view" in the application in which they can see
which fields were changed and when.

So to my question, should I add a new table and store all this or is it better
to add a new database to handle all history stuff in? What are the pros and cons?

I would definitely err on the side of keeping the tables in the same database. It makes it easier to maintain referential integrity (unless you hard delete records in which case foreign keys won't work for you anyway) and create consistent backups (simply backup one database and you know it is in a consistent state, instead of taking two+ backups that my be slightly out of sync).

If you concern is wanting to spread the data between different storage pools (for instance keeping history and audit data on something big & cheap but potentially slow, while keeping active data on something faster but smaller & more expensive), or just keeping the different data on different spindle-sets to reduce potential IO contention, then you can achieve this within a single database by distributing your tables between files/filegroups instead of just having one filegroup containing everything. See http://technet.microsoft.com/en-us/library/ms189126(v=sql.105).aspx (amongst other reference locations in MS's sites) for more details on this.


cons of linked server providers not running in "allow in process" what are the cons of having linked server providers not running in "allow in process"? SQL box crashed due to oracle provider crashing and it provider was running in "Allow in process", now have been advised to un-check allow in process.

That option limits the datatypes you can access through the linked server - you'll not be able to access text / ntext / image columns through an externally instantiated linked server, nor row spilling ( MAX ) columns.  Not a huge loss, generally.


Workflow for SQL Server + Azure SQL development? Lets assume the following:

5 devs all working on laptops
Each develops/debugs on Azure compute emulator (code) and SQL Server (db) - both running locally on their laptops.

I'd like to have

These 5 SQL 'Servers' need to sync with each other
BONUS: If the 5 SQL servers can also sync with (non-production) Azure SQL database!

How can I set this up? The way we're setup, all dev's eventually diverge from the 'reference' database (both schema and data) and pruning it is a PITA. I'm hoping there are some tools to alleviate this issue.

Developers should produce code, and there are obviously many ways to sync code changes (source control). Never touch the database. Ever. If they need any change in the database, modify the code that deploys/upgrades the database. Rails's Migrations are an excellent example of doing it the right way. Visual studio has Database Projects. You can always roll your own custom solution. Post-fact comparison tools I don't like much, but you can say is a personal opinion.


SQL Server 2008 - Cross database performance on same physical machine and server instance Is there any performance hit when doing a select across another DB on the same physical machine? So I have 2 databases on the same physical machine running within the same SQL 2008 instance.

For instance in SomStoreProc on_this_db I run SELECT someFields FROM the_other_db.dbo.someTable

So far from what I have read on the internet, most people seem to indicate NO.

You should not see performance degredation when querying across databases on the same instance. The same query analyzer, buffers, etc. are in use.

Now, if you were to attempt the same across distinct SQL instances on the same server you could see a performance impact. The query would have to be analyzed by two distinct query analyzers and more system resources would be used for caches, buffers, etc. You would also force results to go through the VIA protocol (assuming it's enabled) between the instances. MS DTC could be involved, and if permissions are inadequate on one of the instances a poor(er) query plan may be created.


Why does Postgres generate an already used PK value? I'm using Django, and every once in a while I get this error:

  IntegrityError: duplicate key value violates unique constraint "myapp_mymodel_pkey"
  DETAIL:  Key (id)=(1) already exists.

My Postgres database does in fact have a myapp_mymodel object with the primary key of 1.

Why would Postgres attempt to use that primary key again? Or, is this most likely my application (or Django's ORM) causing this?

This issue occurred 3 more times in a row just now. What I've found is that when it does occur it happens one or more times in a row for a given table, then not again. It seems to happen for every table before it completely stops for days, happening for at least a minute or so per table when it does occur, and only happening intermittently (not all tables right away).

The fact that this error is so intermittent (happened only 3 or so times in 2 weeks - no other load on the DB, just me testing out my application) is what makes me so wary of a low-level problem.

PostgreSQL will not try to insert duplicate values on its own, it is you (your application, ORM included) who does.

It can be either a sequence feeding the values to the PK set to the wrong position and the table already containing the value equal to its nextval() - or simply that your application does the wrong thing.  The first one is easy to fix:

SELECT setval('your_sequence_name', (SELECT max(id) FROM your_table));

The second one means debugging.

Django (or any other popular framework) doesn't reset sequences on its own - otherwise we would have similar questions every other day.


Create cyclic pointers in postgresql 9.5 The problem is to write the "ADD-VALUE" request defined below.
For each "cat" value from the table below, keep only 3 records. Figure this table:

id
cat
value
updatedAt

1
cat1
v1
06/01/2021 00:00:01

2
cat1
v2
06/01/2021 00:00:02

3
cat1
v3
06/01/2021 00:00:03 (pointer cat1 is here)

4
cat2
v1
06/01/2021 00:01:01

5
cat2
v2
06/01/2021 00:01:02  (pointer cat2 is here)

INSERT case: Calling "ADD-VALUE(cat=cat2, value=v3)" will produce the result in bold:

id
cat
value
updatedAt

1
cat1
v1
06/01/2021 00:00:01

2
cat1
v2
06/01/2021 00:00:02

3
cat1
v3
06/01/2021 00:00:03 (pointer cat1 is here)

4
cat2
v1
06/01/2021 00:01:01

5
cat2
v2
06/01/2021 00:01:02

6
cat2
v3
06/01/2021 00:01:02  (pointer cat2 is now here)

UPDATE case: Calling "ADD-VALUE(cat=cat1, value=v4)" will produce the result in bold:

id
cat
value
updatedAt

1
cat1
v4
07/01/2021 00:00:04  (pointer cat1 is now here)

2
cat1
v2
06/01/2021 00:00:02

3
cat1
v3
06/01/2021 00:00:03

4
cat2
v1
06/01/2021 00:01:01

5
cat2
v2
06/01/2021 00:01:02  (pointer cat2 is here)

Any advice is welcome. UPDATE or INSERT in one request is maybe impossible? I think about using row-num to have the count per category.

Your idea would be a pain to enforce under concurrent load. Instead, just keep adding new rows (INSERT only). There are simple and fast queries to get the current row(s) for each cat.
Make sure, that updated_at is current. I added a column default and a trigger for that.
To rule out duplicate entries for the same cat and the same timestamp, add a UNIQUE constraint.
CREATE TABLE cat(
  id         serial PRIMARY KEY
, cat        text NOT NULL
, value      text
, updated_at timestamptz NOT NULL DEFAULT now()
, CONSTRAINT uni_cat_updated_at UNIQUE (cat, updated_at)
);

-- trigger func & trigger
CREATE FUNCTION public.trg_cat_updated()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   NEW.updated_at = now();
   RETURN NEW;
END
$func$;

CREATE TRIGGER cat_befupd
BEFORE UPDATE ON public.cat
FOR EACH ROW EXECUTE PROCEDURE public.trg_cat_updated();

Or, to be absolutely sure, make that:
BEFORE INSERT OR UPDATE ON public.cat

The default value normally takes care of inserts cheaply, but it can be overruled with explicitly inserting a value. The trigger overrules no matter what.
To get the "current" row for a given cat:
SELECT value
FROM   cat
WHERE  cat = 'cat1'
ORDER  BY updated_at DESC
LIMIT  1;

This is extremely fast while you have a matching index:
CREATE INDEX on cat (cat, updated_at DESC, value);

In modern Postgres versions you could use a unique covering index to replace index and UNIQUE constraint. See:

Does a query with a primary key and foreign keys run faster than a query with just primary keys??

To get the last three (live) rows, use the same query with LIMIT 3:
From time to time (as your db load and schedule permit/require) delete deprecated rows:
DELETE FROM cat c
USING (
   SELECT id, row_number() OVER (PARTITION BY cat ORDER BY updated_at DESC) AS rn
   FROM cat
   ) d
WHERE  d.rn > 3
AND    c.id = d.id;

db<>fiddle here


Error launching sqlplus I try to run sqlplus on my server with the command sqlplus /as sysdba

and I got this error

  sqlplus: error while loading shared libraries: /u01/app/oracle/product/12.1.0/dbhome_1/lib/libclntsh.so.12.1: file too short

Update

[user@server]$echo $PATH
/u01/app/oracle/product/12.1.0/dbhome_1/bin:/usr/local/bin:/usr/bin:/bin:/usr/local/games:/usr/games

the output of which sqlplus is /u01/app/oracle/product/12.1.0/dbhome_1/bin/sqlplus

I set up all the necessary Oracle shell variables.

I took a look at the libclntsh.so.12.1 file and it is empty.

Can you help me ?
Thanks

It is very recommendable to do a dump in another media of all your database before trying anything else, because your filesystem is probably not going well.

You will need to restart the server and run fsck not only because of that file is corrupted, but because the risk of more data corruption, then try to run sqlplus again, if it does not help, you will need the file libclntsh.so.12.1 for an another installation.

But dont try to simple replace the file, it is not a good idea, first you will need to run fsck to ensure the file system is ok or something worse can happen.


Cleaning up MSrepl_errors, SQL Server Replication Our MSrepl_errors table is around 15GB and got records from the beginning of time.
What is the correct way of cleaning up this table.  Do we simply delete from it?

Edit
The distribution agent clean up is already running.

Cheers

There a stored procedure named [sp_MShistory_cleanup] called by the job [Agent history clean up: distribution].

That job should take its params from the following setting:

Check if your job is enabled and running as scheduled.

Later edit: mentioned that job because its stored procedure has this following piece of code inside:

-- Delete MSrepl_error entries
DELETE FROM MSrepl_errors
    WHERE time <= @replerr_cutoff
    OPTION(MAXDOP 1)
SELECT @error = @@error, @num_replerror_rows = @@rowcount
IF @error <> 0
    GOTO FAILURE

This should handle the errors cleanup. I checked several of my instances and they all have only new errors, not old, so I suppose the job is also doing this part correctly.

Can you run the procedure manually and see if it runs correctly?


AWS RDS (mysql) strange IO behavior I'm dealing with s db.r3.4xlarge RDS instance with 2000GB all purpose SSD (theoretically capable of up to 6000 IOPS), I'm restoring a bunch of table backup files of over 200GB. There are 26 simultaneous threads running, but Write IOPS is stuck at about 800. Queue depth is at 2 (give or take) and CPU is at 4-5%. Does this make sense? Why isn't the server using more IOPS available to it?

In your DB Parameter Group change innodb_flush_log_at_trx_commit from 1 to 0. This should speed up the writes since the default value writes completed transactions to the Redo Log Files (ib_logfile0, ib_logilfe1).

If you have binary logs enabled (when you enable Automated Backups), you can also change sync_binlog from 1 to 0.

You will not need to reboot since these two options are dynamic.

When your restore is complete, set them back their original values (innodb_flush_log_at_trx_commit back to 1, sync_binlog back to 1).

Give it a Try !!!

UPDATE 2018-02-02 14:27 EST

The only other thing I can think of would to increase the threads the CPUs use

If you look back at my 6.5 year old post Possible to make MySQL use more than one core?, I mention the following options:

innodb_read_io_threads (Reboot Required)

Default for MySQL 5.6 is 4
Percona Server default is 8
Please set this is 8

innodb_write_io_threads (Reboot Required)

Default for MySQL 5.6 is 4
Percona Server default is 8
Please set this to 8 or 16

innodb_thread_concurrency (Dynamic Option, Reboot Not Required)

Default for MySQL 5.6 is 0
Make sure it is 0
Percona recommended using 64 if you are using MySQL 5.7.


Filtering out duplicate domains from URL column using Postgres full-text search parsers I have a PostgreSQL database containing pages and links downloaded by a web crawler, with the following tables:

pages
----------
id:          Integer (primary key)
url:         String  (unique)
title:       String
text:        String
html:        String
last_visit:  DateTime
word_pos:    TSVECTOR

links
----------
id         Integer (primary key)
source:    String
target:    String
link_text: String
UNIQUE(source,target)

crawls
---------
id:         Integer (primary key)
query:      String

crawl_results
-------------
id:       Integer (primary key)
score:    Integer (constraint 0<=score<=1)
crawl_id: Integer (foreign key, crawls.id)
page_id:  Integer (foreign key, pages.id)

The source and target fields in the links table contain URLs. I am running the following query to extract scored links from the top-ranking search results, for pages that haven't been fetched yet:

WITH top_results AS
    (SELECT page_id, score FROM crawl_results
     WHERE crawl_id=$1
     ORDER BY score LIMIT 100)
SELECT top_results.score, l.target
FROM top_results
    JOIN pages p ON top_results.page_id=p.id
    JOIN links l on p.url=l.source
WHERE NOT EXISTS (SELECT pp.id FROM pages pp WHERE l.target=pp.url)

However, I would like to filter these results so that only one row is returned for a given domain (the one with the lowest score). So for instance, if I get (0.3, 'http://www.foo.com/bar') and (0.8, 'http://www.foo.com/zor'), I only want the first because it has same domain foo.com and has the lower score.

I was able to find documentation for the builtin full text search parsers, which can parse URLS and extract the hostname. For instance, I can extract the hostname from a URL as follows:

SELECT token FROM ts_parse('default', 'http://www.foo.com') WHERE tokid = 6;

    token
-------------
 www.foo.com
(1 row)

However, I can't figure out how I would integrate this into the above query to filter out duplicate domains from the results. And because this is the docs for "testing and debugging text search", I don't know if this use of ts_parse() is even related to how the URL parser is intended to be used in practice.

How would I use the host parser in my query above to return one row per domain? Also, how would I appropriately index the links table for host and url lookup?

After asking around on IRC, the Postgres mailing list, and a few other places, this is the best I've been able to come up with. I'm going to put it up in case it helps someone else with similar problem,and also because I'm hoping that someone can tell me how to improve on it. I first created the following views:

    CREATE VIEW scored_pages AS (
        SELECT crawl_results.crawl_id, crawl_results.score,
            crawl_results.page_id, pages.url
        FROM crawl_results
        JOIN pages ON crawl_results.page_id = pages.id
    );

    CREATE VIEW scored_links AS (
        SELECT scored_pages.score, links.source,
            links.target, links.link_text
        FROM links
        JOIN scored_pages ON scored_pages.url = links.source
    );

Then, using these views, I did the following query to extract the links from the lowest scored pages in the results:

SELECT score, host, target
FROM (
        SELECT DISTINCT ON (token) token AS host, score, target
        FROM scored_links,
        LATERAL ts_parse('default', target) WHERE tokid = 6
        ORDER BY token, score
) as x
WHERE NOT EXISTS (SELECT pp.id FROM pages pp WHERE target=pp.url)
ORDER BY score;

I would appreciate any feedback on how to improve this, or anything I'm doing wrong. In the meantime, I'll leave this question open in case someone comes by later with a better answer.


MySQL Workbench - Error Getting DDL Object (It doesn't exist?) The problem started when a table, started not to fetch a single SELECT. It was only a SELECT * FROM table; and it started to run and never stopped. After several attempts, all of them failed, and then I tried to drop the table (it has backups), but the SAME problem happened.

My last, and failed attempt was to rename the table and then restore a backup table.

I tried to use MySQL Workbench feature to Alter Tables but, Windows error (I use Windows 7 at my job). Windows offered me to "Try to restore the program" and it indicates me it was an "Error Getting DDL Object". Then I checked the tables information, and DDL tab was empty.

So.... I don't what to do. Is there a way to change this DDL? Or maybe another way?

I discovered, after a long research, that it could be a process which wans't interrupted (somehow).

Then it was only:

show processlist;
kill [ID of the running process];

And then I was able to drop/alter that table.


Show data and disk use breakdown by table I have a SQL Server 2008 R2 database being used by several deployed programs.

Question: Is there an easy way to display how much space each table consumes, for all of the tables in the database, and distinguish logical space from disk space?

If I use SSMS (Management Studio), the storage properties shown for the database reads 167 MB with 3 MB "available" (about the right size, but I'm concerned about the 3 MB available - is this a limit to be concerned about, assuming I know I have enough disk space?)

I can drill into each table, but that takes forever to do.

I know I can write my own queries and test around, but I'd like to know if there's already an easy (built-in?) way to do this.

In SSMS, right-click on the database and go to "Reports", "Standard Reports", "Disk Usage by Table". It will tell you the total size, the data size, the index size, and the unused size for each table (as well as the row count).


After moving database (backup, restore), I have to re-add user I occasionally move a database (SQL Express 2012) from a development machine to a server, or vice versa, using database backup and restore in SQL Server Management Studio.

Whenever I do this, the applications on the target machine cannot access the database until I delete the user they use, "george", from the database users (Database, Security, Users in SQL Server Management Studio), and re-add it as owner under Security, Logins, george/properties, user mapping.

Is there a better way to do this? It seems a little convoluted.

This is the difference between logins and users and how they relate to each other:

Logins - Instance level principals that allow an entity to connect to the SQL Server instance.  They do not, by their nature, grant any access to databases on the instance.  The exception to this is a login with sysadmin rights can use a database because they are sysadmin, but because of sysadmin level permissions.
Users - Database level principals that allow an entity to connect to a SQL Server database.  Users are associated with logins via SIDs, creating a relationship between the two and allowing a login to connect to the instance and then use the associated user to connect to the database.

What commonly happens with SQL authenticated logins and database users on a restore is that the SIDS will be out of sync, thus breaking the relationship.  This relationship must be repaired before you can connect to the database using that login, because in the eyes of SQL Server those principals are no longer connected.  You can fix this with the following SQL:

ALTER USER [foo] WITH LOGIN=[foo]

You can use the following query in the context of your database to check for orphans:

select
    dp.name [user_name]
    ,dp.type_desc [user_type]
    ,isnull(sp.name,'Orhphaned!') [login_name]
    ,sp.type_desc [login_type]
from
    sys.database_principals dp
    left join sys.server_principals sp on (dp.sid = sp.sid)
where
    dp.type in ('S','U','G')
    and dp.principal_id >4
order by sp.name


Where is documented that no other transaction can update a locked row? In the manual for InnoDB Locking, it is stated how shared and exclusive locks work.

As I found out by trial and error, both LOCK IN SHARE MODE and FOR UPDATE ensure no other transaction can update the rows that are selected.

What I found in the documentation, is that if a row is locked FOR UPDATE then no other transaction can get a LOCK until the transaction is commited or rolled back. Also if there are multiple LOCK IN SHARE MODE then no updated are possible. But I did not find that if there is any LOCK on a row, than no transaction (even without LOCK) can update the row.

I did not find that rule in the docs. Is it missing, or did I just overread it?

You say:

  if there is any LOCK on a row, [then] no transaction (even without LOCK) can update the row

On the page you have linked to you can read that

  An exclusive (X) lock permits the transaction that holds the lock to update or delete a row

which means that before a transaction can update a row it must acquire an X lock on it, which is impossible1 if there is already an S or X lock on that row placed by a locking read in another transaction.

1 -- because X, which as we know stands for "exclusive",  is incompatible with any other lock.


Example of loading into RDS from S3 using SQL*Loader Does anyone have a working sample of loading data into an Amazon AWS RDS Oracle database using SQL*Loader where the source file is on S3?

In principle it's clearly possible. The Amazon RDS Data Import Guide for Oracle documents using sqlldr. But the only sample there loads data from a local EC2 instance.

I have worked with RDS and with S3 and with SQL*Loader before, but I haven't worked with all 3 together. One sample would be really useful. (Related advice would be welcome too... but a sample is all I'm really looking for.)

I thought of two ideas how you could (at least theoretically) access the data if you are on Linux.

mount S3 bucket using some kind of S3 FUSE. E.g. first hit on Google https://code.google.com/p/s3fs/wiki/FuseOverAmazon
create named pipe: mkfifo oraclepipe then configure SQL*Loader to read from the pipe probably using conventional path. And feed S3 file to the pipe: wget -O oraclepipe http://s3.com/hash.

We were using the second method on 9i to compress export online. And I do not recall problems with this approach. We ran two sessions from the shell script:

    gzip < oraclepipe > database.exp.gz &
    exp "username/passwd" FULL=y BUFFER=2048000 FILE=oraclepipe


Procedure showing different duration / reads in Profiler vs. SSMS I have a database which is getting a lot of calls to the following procedure:

exec dbo.SM_SP_MAILEVENTNAMES 167260

In Profiler using the TSQL_Duration trace these calls show up as:

EventClass: RPC: Completed
Duration:   900 to 1500 ms
Reads:      8818

Running the same query in Management Studio with Statistics I/O:

set statistics io on
exec dbo.SM_SP_MAILEVENTNAMES 167260

Runs instantaneously and has the following I/O output:

  Table 'SM_MAIL_CONTACTS'. Scan count 0, logical reads 29, physical reads 0, read-ahead
  reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'SM_MAIL_EVENT_CONTACTS'. Scan count 13, logical reads 68, physical reads 0,
  read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'SM_MATTER_EVENTS'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The query itself returns about 3 rows.  Why the disparity in what I am seeing in Profiler and Management Studio?

The difference in logical reads leads me to believe that the two executions are getting different plans. This could be because:

there are different set settings between the app that is calling the procedure that profiler is picking up and your instance of SSMS.
you have a different default schema (you really should say exec dbo.SM_SP...).
see this post for more details

You can see if there are two or more different plans for the procedure using something like this, which will also identify if this is caused by either (or both) of the two most likely factors for different plan choice:

SELECT p.plan_handle, p.usecounts, p.size_in_bytes,
  set_options = MAX(CASE a.attribute WHEN N'set_options' THEN a.value END),
  dflt_schema = MAX(CASE a.attribute WHEN N'user_id'     THEN a.value END)
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
CROSS APPLY sys.dm_exec_plan_attributes(p.plan_handle) AS a
WHERE t.objectid = OBJECT_ID(N'dbo.SM_SP_MAILEVENTNAMES')
AND a.attribute IN (N'user_id', N'set_options')
GROUP BY p.plan_handle, p.usecounts, p.size_in_bytes;


SQL Server High CPU usage and RESOURCE_SEMAPHORE waits Last week had an issue on one of SQL Servers, CPU started burning over 80 % (normal is 10-30 %)
This lasted for about 2 hours until I manually failed over to secondary replica in AG (and this has resolved the issue)
Issue start: 12:15
Issue end:   14:15 (after manual AG failover)

Server Info:
SQL Server 2017
32 logical processors (max DOP = 8)
256 GB RAM (Max Server Memory = 180 GB, used 179 GB)

Below metrics DIDN'T change noticeably before the issue vs. after the issue started

User Connections / sec ( avg. 200-300 )
Batch Requests / sec ( avg. 200 and lower )
Database Cache Memory ( ~ 150 GB )

Below metrics PEAKED considerably, which is NOT typical for this server (usually these are low):

CPU ( over 80 % )
Memory Grants Pending
Lock Waits/sec, Avg. Lock Wait Time, Deadlocks
Latch Wait Time
Granted Workspace Memory and Reserved Server Memory

Queries:
I did not notice change in workloads for this server when issue had started
Developers also confirmed that applications did their usual job and were running usual queries, no peaks in application load
During this "high CPU usage" issue, top 10 queries by CPU didn't look unusual
All the same top 10 queries that we usually see even when CPU is normal (10-30 %)
Problem:
The problem seemed to be in a couple of related stored procedures, that application usually runs 1-4 times / second, and those usually complete within 50 ms, but during the issue, anytime I've checked the sys.dm_exec_requests (also used exec ViewSessionsConnections 'running' https://github.com/aleksey-vitsko/Database-Administrator-Tools/blob/master/Sessions%20-%20ViewSessionsConnections.sql), there were sitting like 50-70 sessions from 1 application, all trying to complete above mentioned procedures, and it was slow
When looking at top 10 queries by Duration in a Monitoring tool, top 1 and 2 these were two statements from above procedures - they did not consume a lot of CPU, BUT had excessive waits (RESOURCE_SEMAPHORE, LCK_M_IS)
Usually these complete within 10 ms or less, executed 1-4 times per second and not causing any issues, and now these started having duration of 4000-8000 ms per 1 execution, which was the issue
RESOURCE_SEMAPHORE is absolutely NOT typical for this server, but during the issue it was among top waits
(RESOURCE_SEMAPHORE - Queries waiting for memory to be granted; total  135400234 ms in 2 hours;  avg 4174 ms )
Granted Workspace Memory and Reserved System Memory in SQL Server rocketed from 0 to ~110 GB during the issue

Questions:

What are your thoughts and experience on above ?

Can constant RESOURCE_SEMAPHORE waits and Memory Grants Pending cause pressure on CPU just to allocate workspace memory for queries? Because when looked at top 10 queries by CPU during the issue, CPU numbers looked normal / usual

How can it be that Granted Workspace Memory and Reserved Server Memory started consuming ~ 112 and 110 GB when the issue started, given that Max Server Memory is 180 GB and Database Cache Memory remained ~ 150 GB all the time? Did it over-commit memory or something like that ?

Why would a statement inside SP that usually completes within 10 ms for months, starts to experience RESOURCE_SEMAPHORE wait and complete in 4000-8000 ms ?

How can issue be solved in a more surgical way, without doing manual failover to secondary replica? How can I calm down the query and bring it back to 10 ms ?
Plan needs to be dropped, or query recompiled, etc. ?
What is the best way to do it and monitor it?

Brent Ozar First Responder Kit or other diagnostic procedures - in which sequence should be executed during performance issues, to better understand what is going on ?

What are your thoughts and experience on above ?

Why would a statement inside SP that usually completes within 10 ms for months, starts to experience RESOURCE_SEMAPHORE wait and complete in 4000-8000 ms ?

CPU pressure caused by bad plans.  You should track and manage plan stability with Query Store, as well as investigating bad plans and remediating with additional index and statistics, and perhaps changes to the queries.

Can constant RESOURCE_SEMAPHORE waits and Memory Grants Pending cause pressure on CPU just to allocate workspace memory for queries?

No, it's the other way around.  Bad plans are resource intensive, driving both large memory allocations and CPU use.


SQL Server 2012 Page Life Expectancy resets to 0 after about 50 days I've noticed an odd behavior on a 2-server HA cluster and I was hoping someone could confirm my suspicion, or maybe offer some other explanation... Here is my setup:

A 2-server SQL 2012 SP1 installation
SQL AlwaysOn HA has been enabled for a few databases
CPUs are 2.4GHz, 4 cores
RAM is 34 GB (it is an AWS instance, hence the odd number)
Resource utilization is relatively low - each server has 14+ GB memory free, and SQL is not capped on how much memory to use
Disk access time is fine - rarely going over 15ms/Read or Write
Databases are not big - 1 GB, 1.5 GB, 7.5 GB
SQL server process is using 16 GB Private Bytes, 15 GB Working Set

Overall, no resource issues are noted. Now for the odd part. SQL is not restarted (process has been running for almost 6 months) but it seems that every ~50 days, the Page Life Expectancy counter drops to (almost) 0. Up until that point it climbs steadily, no drops. Here is a perf graph:

When I look at the counter data (I don't have the exact number, just an hourly aggregation) it seems the PLE counter value reached about 4,295,000 sec (roughly 50 days) every time (at least every time I have data for).

My crazy theory is that the PLE number is held as milliseconds as an unsigned long int (which has a limit of 4,294,967,295) and at 49.71 days it resets, either by design, or because of a bug. This would explain the behavior of the two servers and the identical pattern they have. Or it could be something totally different and I am just not making any sense. :)

Has anyone seen anything like that, or can explain this behavior?

P.S. I saw this post, but my case seems slightly different.

P.P.S. This is a repost - I originally posted it here, but was advised the audience here is more appropriate.

Thanks!

I've seen this behaviour on a client site running SQL2012 SP1.
The specifics here were NUMA, and PLE demonstrating a 'sawtooth' pattern but on an hourly cycle.

A couple of threads on SQLServerCentral discussed around this:

http://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspx
http://www.sqlservercentral.com/Forums/Topic1424826-2799-1.aspx

the end result being that applying SP1 CU4 seemed to fix the problem.

CU4 contains the innocent looking fix An update is available for SQL Server 2012 Memory Management KB2845380

Worth a try?


How do I generate a date series in PostgreSQL without hard coding the dates? I want to generate a series of months. The start should be the first month of the current year, the stop is the current date with an interval of 1 month.

I used the function generate_series (see below) and it gives me the desired result, but I don't want the starting date to be hard coded. It should change depending the year it is in.

select to_char(generate_series('2018-1-1'::date,
                now()::date,
                '1 month'), 'YYYYmm') as dat

Output:

201801
201802
201803
201804
201805
201806
201807
201808
201809
201810

Any ideas on how I can solve this?

I want to generate a series of months. The start should be the first month of the current year, the stop is the current date with an interval of 1 month.

I would just keep it like d::date but you can format it however you want,

SELECT d::date, to_char(d, 'YYYYmm')
FROM generate_series(
  date_trunc('year', now())::timestamp without time zone,
  now()::timestamp without time zone,
  '1 month'
) AS gs(d);

But you can reformat it with to_char() if you want.

For more information see How do I generate a date series in PostgreSQL?


Restoring a FrontBase database I have a FrontBase database that I am trying to restore. This is a database system I have no experience up until now so this may be a basic question. I am issuing the following command to restore

c:\usr>FrontBase\bin\FrontBase.exe -console -create -restore=c:\usr\B_2017_03_08-20_36_14 MyDB

And get the error

2017-03-16 11:28:38 FrontBase Server - 8.2.8 64-bit on Windows NT/2000
2017-03-16 11:28:38 Cannot determine keys identity, Wrong magic number - not a backup file
2017-03-16 11:28:38 Failed to restore database from the backup

The backup was actually created on a Mac, I am not sure if the format is portable.

If anyone has experience with Frontbase I would love to hear from them, it doesn't seem to be a common DB.

Is the mac and Windows machines running same version of FrontBase? if not you have to export the database to file format and import it:

On mac :

WRITE ALL OUTPUT(DIR = '<path>', TYPE = 'FrontBase',
CONTENT = TRUE);

On Windows:

script <path>/schema.sql;

See more in release note: http://www.frontbase.com/cgi-bin/WebObjects/FBWebSite


Reason to not use nullable number in Oracle? Our company is interfacing with another software company for a joint project, and we were told that, if a particular value should not be displayed, we should pass in a -5000 (their arbitrary sentinel value); the reason is that no number column in their Oracle database supports null values, on the recommendation of their (now former) Oracle dev. This company also writes the vast majority of their code in VB6 (slowly transitioning to VB.NET, which is another topic for another day...).  Out of pure curiosity, is there any valid reason for this recommendation? I can't think of any on my side.

--- edit

Thanks for the feedback all. I posed the same question on CodeProject.com (link) and received very similar feedback. It appears the only time one could possibly begin to justify this practice is related to foreign keys, and I can state that they use no foreign keys anywhere in the system. The developer that made this determination (I used to work at that company) has significantly more experience than I, so I wanted to make sure there wasn't a valid reason for this before derision ensues.

Realistically, the requirement is crazy.  Like all great crazy ideas, however, it is probably based on a nugget of potential reasonableness taken far out of context by people that have no understanding of the underlying rationale.

It can be reasonable to design a database schema such that no NULL values are allowed.  If you do that, however, you are committing to a level of normalization where every non-required element is broken out into an separate table with an appropriate foreign-key reference back to the parent.  It's not often done in practice but in cases where it makes sense to do, there can be benefits.

If you are going to design a database schema such that no NULL values are allowed, it makes no sense to allow let alone require magic values to indicate that something is unknown.  That introduces all the problems that allowing NULL values has plus it adds additional code to check for the magic values that has to get repeated all over the place.  It makes no sense to develop an API that requires magic values to be passed in regardless of the database design-- if you're going to hobble your code with checks for magic values, you really ought not let that insanity propagate out to other systems.


Optimising a MySQL script with 1.7 billion records I am trying to optimise a MySQL script that I have created. Here is a bit of background on the project.

We have 2 system which we are now merging into one system, I am currently writing the scripts to merge the databases together. I need this to run as fast as possible, while this script is running we have to shut down the systems and we don't want too much down time.

Both systems have a table called venue, zone and device_vendor which are identical they just have different data. When I merged the tables together obviously the IDs for the values in those tables have changed and they are foreign keys in a lot of other tables. The table I am currently working on has 1.7 billion records and all has the values venue_id, zone_id and device_vendor_id so I have to change these values to the new IDs.

Here is the script that I have written to change the IDs to the new values. All these values are also indexes which is why it is taking so long.

INSERT INTO `intelli_sense`.`tracking_daily_stats_zone_unique_device_uuids_per_hour` (day_epoch, day_of_week, hour, venue_id, zone_id, device_uuid, device_vendor_id, first_seen, last_seen, is_repeat)

SELECT day_epoch, day_of_week, hour, (CASE WHEN intelli_sense_venue.id!=0 THEN intelli_sense_venue.id ELSE 0 END), (CASE WHEN intelli_sense_zone.id!=0 THEN intelli_sense_zone.id ELSE 0 END), device_uuid, (CASE WHEN intelli_sense_device_vendor.id!=0 THEN intelli_sense_device_vendor.id ELSE 0 END), first_seen, last_seen, is_repeat
FROM geo_sense.daily_stats_zone_unique_device_uuids_per_hour AS tracking_daily_stats_zone_unique_device_uuids_per_hour
LEFT JOIN geo_sense.venue AS tracking_venue ON tracking_venue.id = tracking_daily_stats_zone_unique_device_uuids_per_hour.venue_id
LEFT JOIN intelli_sense.venue AS intelli_sense_venue ON intelli_sense_venue.name = tracking_venue.name
LEFT JOIN geo_sense.zone AS tracking_zone ON tracking_zone.id = tracking_daily_stats_zone_unique_device_uuids_per_hour.zone_id
LEFT JOIN intelli_sense.zone AS intelli_sense_zone ON intelli_sense_zone.name = tracking_zone.name AND intelli_sense_zone.lat = tracking_zone.lat AND intelli_sense_zone.lon = tracking_zone.lon
LEFT JOIN geo_sense.device_vendor AS tracking_device_vendor ON tracking_device_vendor.id = tracking_daily_stats_zone_unique_device_uuids_per_hour.device_vendor_id
LEFT JOIN intelli_sense.device_vendor AS intelli_sense_device_vendor ON intelli_sense_device_vendor.name = tracking_device_vendor.name AND intelli_sense_device_vendor.description = tracking_device_vendor.description;

As you can see from the script above I have used LEFT JOINS to update these values. I have basically imported both databases into one server and now I am merging them together to create a new database called intelli_sense.

In the script above this isn't merging the 2 databases together it is just updating the values in the tracking_daily_stats_zone_unique_device_uuids_per_hour table which is from the geo_sense database because after merging the venue, zone and device_vendor tables the IDs have changed. I need to update all the tables which are related to those tables.

I have ran this script and it took 3 days to insert 50mil records which is way to long so I stopped it.

I need this script to finish in less then a day because I have 3 tables with this amount of data and I don't want too much system downtime.

I can't share the data since there is so much so I hope that script explains it enough, the actual script works its just a case of speeding it up. The data is also personal data so I wouldn't be able to share it anyway.

If you don't think this is impossible let me know and I will find a new way to do it while leaving both systems on so it can take as long as it wants.

Thanks in advance.

EDIT: Here is the explain statement for the above query.

You may notice a lot of ALLs in the type but I believe this is due to joining on the name. I have to do that because I no longer know what the IDs are since this is the reason I am using the LEFT JOINs to change them values.

Several thoughts and requests.  (Not yet a complete answer.)

Please provide SHOW CREATE TABLE, at least for daily_stats_zone_unique_device_uuids_per_hour.  How many partitions in that table.

I assume it is practical and possible to migrate a few rows at a time, as long as we don't mess up the ids, either the old ids or the new ids.

We should walk through the main table, 1000 rows at a time, probably by the PRIMARY KEY. (I may choose to modify this advice after seeing the CREATE TABLE, especially the partition definition.)

It would involve finding all the ids and changing them for the batch.  Possibly add a "sleep" between iterations.

Do the batch as a single transaction. -- The time it takes for the transaction to run becomes the length of the "downtime".  Adjust the "1000" and the "sleep" time to avoid interfering too much with the rest of the system.

1.7 B rows in that table?  What about the other tables?

A technique that may be useful:  Adjust all the ids by N, where N is big enough to "shift" all the values to a currently-unused range of values in the ids.

I see UUIDs.  How pervasive are they?  How big is innodb_buffer_pool_size?  How much RAM?  How big (in GB) is the dataset?  I ask these because UUIDs are notoriously inefficient when the index becomes bigger than can be cached; I worry that you are already there.  The first thing to help with this will be to change the 1000 to, say, 100.  This is because (in the 'worst' case) nearly every read will have to go to disk because of the randomness of UUIDs.

You say there are a bunch of indexes; I assume they are not UNIQUE?

In the SELECT...

Isn't (CASE WHEN id!=0 THEN id ELSE 0 END) the same as IFNULL(id, 0)?

7 tables are mentioned, we can't see which tables some of the SELECT values come from (eg first_seen).  Do you really need all the LEFT JOINs?

What is the relevance of the SELECT to the merging of two tables?  Isn't there an issue of shifting some ids?

More discussion of chunking: http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks


Why Does the Transaction Log Keep Growing or Run Out of Space? This one seems to be a common question in most forums and all over the web, it is asked here in many formats that typically sound like this:

In SQL Server -

What are some reasons the transaction log grows so large?
Why is my log file so big?
What are some ways to prevent this problem from occurring?
What do I do when I get myself on track with the underlying cause and want to put
my transaction log file to a healthy size?

A Shorter Answer:

You probably either have a long running transaction running (Index maintenance? Big batch delete or update?) or you are in the "default" (more below on what is meant by default) recovery mode of Full and have not taken a log backup (or aren't taking them frequently enough).

If it is a recovery model issue, the simple answer could be Switch to Simple recovery mode if you do not need point in time recovery and regular log backups. Many people, though, make that their answer without understanding recovery models. Read on to understand why it matters and then decide what you do. You could also just start taking log backups and stay in Full recovery.

There could be other reasons but these are the most common. This answer begins to dive into the most common two reasons and gives you some background information on the why and how behind the reasons as well as explores some other reasons.

A Longer Answer:
What Scenarios can cause the log to keep Growing? There are many reasons, but usually these reasons are of the following two patterns: There is a misunderstanding about recovery models or there are long running transactions. Read on for details.

Top reason 1/2: Not Understanding Recovery Models

(Being in Full Recovery Mode and Not Taking Log Backups - This is the most common reason - the vast majority of those experiencing this issue are.)

While this answer is not a deep dive in SQL Server recovery models, the topic of recovery models is critical to this problem.

In SQL Server, there are three recovery models:

Full,
Bulk-Logged and
Simple.

We'll ignore Bulk-Logged for now we'll sort of say it is a hybrid model and most people who are in this model are there for a reason and understand recovery models.

The two we care about and their confusion are the cause of the majority of the cases of people having this issue are Simple and Full.

Intermission: Recovery in General

Before we talk about Recovery Models: let's talk about recovery in general. If you want to go even deeper with this topic, just read Paul Randal's blog and as many posts on it as you want. For this question, though:

Crash/Restart Recovery
One purpose of the transaction log file is for crash/restart recovery. For the rolling forward and rolling back of work that was either done (rolling forward/redo) before a crash or restart and the work that was started but not finished after a crash or restart (rolling back/undo). It is the job of the transaction log to see that a transaction started but never finished (rolled back or crash/restart happened before the transaction committed). In that situation It is the log's job to say "Hey.. this never really finished, let's roll it back" during recovery. It is also the log's job to see that you did finish something and that your client application was told it was finished (even if it hadn't yet hardened to your data file) and say "Hey.. this really happened, let's roll it forward, let's make it like the applications think it was" after a restart. Now there is more but that is the main purpose.
Point in Time Recovery
The other purpose for a transaction log file is to be able to give us the ability to recover to a point in time due to an "oops" in a database or to guarantee a recovery point in the event of a hardware failure involving the data and/or log files of a database. If this transaction log contains the records of transactions that have been started and finished for recovery, SQL Server can and does then use this information to get a database to where it was before an issue happened. But that isn't always an available option for us. For that to work we have to have our database in the right recovery model, and we have to take log backups.

Recovery Models

Onto the recovery models:

Simple Recovery Model
So with the above introduction, it is easiest to talk about Simple Recovery model first. In this model, you are telling SQL Server: "I am fine with you using your transaction log file for crash and restart recovery..." (You really have no choice there. Look up ACID properties and that should make sense quickly.) "...but once you no longer need it for that crash/restart recovery purpose, go ahead and reuse the log file."

SQL Server listens to this request in Simple Recovery and it only keeps the information it needs to do crash/restart recovery. Once SQL Server is sure it can recover because data is hardened to the data file (more or less), the data that has been hardened is no longer necessary in the log and is marked for truncation - which means it gets re-used.
Full Recovery Model
With Full Recovery, you are telling SQL Server that you want to be able to recover to a specific point in time, as long as your log file is available or to a specific point in time that is covered by a log backup. In this case when SQL Server reaches the point where it would be safe to truncate the log file in Simple Recovery Model, it will not do that. Instead It lets the log file continue to grow and will allow it to keep growing, until you take a log backup (or run out of space on your log file drive) under normal circumstances.

Switching from Simple to Full has a Gotcha.

There are rules and exceptions here. We'll talk about long running transactions in depth below.

But one caveat to keep in mind for Full Recovery Mode is this: If you just switch into Full Recovery mode, but never take an initial Full Backup, SQL Server will not honor your request to be in Full Recovery model. Your transaction log will continue to operate as it has in Simpleuntil you switch to Full Recovery Model AND take your first Full Backup.

Full Recovery Model without log backups is bad.

So, what is the most common reason for uncontrolled log growth?
Answer: Being in Full Recovery mode without having any log backups.

This happens all the time to people.

Why is this such a common mistake?

Why does it happen all the time? Because each new database gets its initial recovery model setting by looking at the model database.

Model's initial recovery model setting is always Full Recovery Model - until and unless someone changes that. So you could say the "default Recovery Model" is Full. Many people are not aware of this and have their databases running in Full Recovery Model with no log backups, and therefore a transaction log file much larger than necessary. This is why it is important to change defaults when they don't work for your organization and its needs)

Full Recovery Model with too few log backups is bad.

You can also get yourself in trouble here by not taking log backups frequently enough.
Taking a log backup a day may sound fine, it makes a restore require less restore commands, but keeping in mind the discussion above, that log file will continue to grow and grow until you take log backups.

How do I find out what log backup frequency I need?

You need to consider your log backup frequency with two things in mind:

Recovery Needs - This should hopefully be first. In the event that the drive housing your transaction log goes bad or you get serious corruption that affects your log backup, how much data can be lost? If that number is no more than 10-15 minutes, then you need to be taking the log backup every 10-15 minute, end of discussion.
Log Growth - If your organization is fine to lose more data because of the ability to easily recreate that day you may be fine to have a log backup much less frequently than 15 minutes. Maybe your organization is fine with every 4 hours. But you have to look at how many transactions you generate in 4 hours. Will allowing the log to keep growing in those four hours make too large of a log file? Will that mean your log backups take too long?

Top reason 2/2: Long Running Transactions

("My recovery model is fine! The log is still growing!)

This can also be a cause of uncontrolled and unrestrained log growth. No matter the recovery model, but it often comes up as "But I'm in Simple Recovery Model - why is my log still growing?!"

The reason here is simple: if SQL is using this transaction log for recovery purposes as I described above, then it has to see back to the start of a transaction.

If you have a transaction that takes a long time or does a lot of changes, the log cannot truncate on checkpoint for any of the changes that are still in open transactions or that have started since that transaction started.

This means that a big delete, deleting millions of rows in one delete statement is one transaction and the log cannot do any truncating until that whole delete is done. In Full Recovery Model, this delete is logged and that could be a lot of log records. Same thing with Index optimization work during maintenance windows. It also means that poor transaction management and not watching for and closing open transactions can really hurt you and your log file.

What can I do about these long running transactions?

You can save yourself here by:

Properly sizing your log file to account for the worst case scenario - like your maintenance or known large operations. And when you grow your log file you should look to this guidance (and the two links she sends you to) by Kimberly Tripp. Right sizing is super critical here.
Watching your usage of transactions. Don't start a transaction in your application server and start having long conversations with SQL Server and risk leaving one open too long.
Watching the implied transactions in your DML statements. For example: UPDATE TableName Set Col1 = 'New Value' is a transaction. I didn't put a BEGIN TRAN there and I don't have to, it is still one transaction that just automatically commits when done. So if doing operations on large numbers of rows, consider batching those operations up into more manageable chunks and giving the log time to recover. Or consider the right size to deal with that. Or perhaps look into changing recovery models during a bulk load window.

Do these two reasons also apply to Log Shipping?

Short answer: yes. Longer answer below.

Question: "I'm using log shipping, so my log backups are automated... Why am I still seeing transaction log growth?"

Answer: read on.

What is Log Shipping?

Log shipping is just what it sounds like - you are shipping your transaction log backups to another server for DR purposes. There is some initialization but after that the process is fairly simple:

A job to backup the log on one server,
a job to copy that log backup and
a job to restore it without recovery (either NORECOVERY or STANDBY) on the destination server.

There are also some jobs to monitor and alert if things don't go as you have them planned.

In some cases, you may only want to do the log shipping restore once a day or every third day or once a week. That is fine. But if you make this change on all of the jobs (including the log backup and copy jobs) that means you are waiting all that time to take a log backup. That means you will have a lot of log growth -- because you are in full recovery mode without log backups -- and it probably also means a large log file to copy across. You should only modify the restore job's schedule and let the log backups and copies happen on a more frequent basis, otherwise you will suffer from the first issue described in this answer.

General troubleshooting via status codes

There are reasons other than these two, but these are the most common. Regardless of the cause: there is a way you can analyze your reason for this unexplained log growth/lack of truncation and see what they are.

By querying the sys.databases catalog view you can see information describing the reason your log file may be waiting on truncate/reuse.

There is a column called log_reuse_wait with a lookup ID of the reason code and a log_reuse_wait_desc column with a description of the wait reason. From the referenced books online article are the majority of the reasons (the ones you are likely to see and the ones we can explain reasons for. The missing ones are either out of use or for internal use) with a few notes about the wait in italics:

0 = Nothing
What it sounds like.. Shouldn't be waiting
1 = Checkpoint
Waiting for a checkpoint to occur. This should happen and you should be fine - but there are some cases to look for here for later answers or edits.
2 = Log backup
You are waiting for a log backup to occur. Either you have them scheduled and it will happen soon, or you have the first problem described here and you now know how to fix it
3 = Active backup or restore
A backup or restore operation is running on the database
4 = Active transaction
There is an active transaction that needs to complete (either way - ROLLBACK or COMMIT) before the log can be backed up. This is the second reason described in this answer.
5 = Database mirroring
Either a mirror is getting behind or under some latency in a high performance mirroring situation or mirroring is paused for some reason
6 = Replication
There can be issues with replication that would cause this - like a log reader agent not running, a database thinking it is marked for replication that no longer is and various other reasons. You can also see this reason and it is perfectly normal because you are looking at just the right time, just as transactions are being consumed by the log reader
7 = Database snapshot creation
You are creating a database snapshot, you'll see this if you look at just the right moment as a snapshot is being created
8 = Log Scan
I have yet to encounter an issue with this running along forever. If you look long enough and frequently enough you can see this happen, but it shouldn't be a cause of excessive transaction log growth, that I've seen.
9 = An AlwaysOn Availability Groups secondary replica is applying transaction log records of this database to a corresponding secondary database.
About the clearest description yet..


Selecting distinct rows from two tables, using multiple conditions I'm trying to figure out a query.  I have an offices and meetings table in my DB.  Offices can have multiple meetings (office ID is a FK to the offices table).  Some meetings allow lunch, some do not.  I'm trying to find a list of all offices with meetings in January that allowed lunch, which also had meetings in February that did NOT allow lunch.

My sanitized schema looks something like this:

+---------------+   +---------------+
| meetings      |   | offices       |
+---------------+   +---------------+
| id            |   | id            |
| meeting_date  |   | name          |
| allowed_lunch |   | address_id    |
| office_id     |   | phone         |
+---------------+   +---------------+

I want a list with office id's, names, phone numbers, as well as some information from other tables I need to join to, such as street address, zip, state, etc.  So far I've only been able to come up with a clunky way of doing this; I use two queries.

The first query gets a distinct list of office ID's, using a subquery:

select distinct offices.id from offices
join meetings on offices.id = meetings.office_id
where offices.id in (
  select
    distinct offices.id
  from offices
  join meetings on meetings.office_id = offices.id
  where
    DATE(meeting_date) < '2020-01-31' and DATE(meeting_date) >= '2020-01-01' and allowed_lunch = 1
)
and DATE(meeting_date) < '2020-02-28' and DATE(meeting_date) >= '2020-02-01' and allowed_lunch = 0;

I then manually take that list of office id's and look them up again in a separate query to pull the additional information I need from other tables.  So something like this:

SELECT office.name, office.phone, ..., address.zip, address.state
FROM offices
JOIN addresses on offices.address_id = addresses.id
WHERE office.id in (
... big list from first query ...
);

What I need is a distinct list of offices, which satisfy the two conditions listed at the top.
 Is there a better way to go about doing this?  Preferably within a single query?

(I could take the first query and stick it under the WHERE clause in the second query.  I had performance issues doing this though.  The first query takes about 10 seconds, and the second query is pretty quick, but when I combine them in an additional subquery, it becomes very slow.  Plus it seems a rather messy way to handle it.)

Schematically:

SELECT offices.id
-- join tables
FROM offices
JOIN meetings ON offices.id = meetings.office_id
-- select only rows for Jan and Feb 2020
WHERE DATE(meeting_date) BETWEEN '2020-01-01' AND '2020-02-29'
-- grouping - prepare calculations for separate offices
GROUP BY offices.id
-- check that the amount of `allowed_lunch` in Feb is zero
HAVING NOT SUM(DATE(meeting_date) BETWEEN '2020-02-01' AND '2020-02-29' AND allowed_lunch)
-- but total is not zero (taking into account prev. check - they're in Jan)
   AND SUM(allowed_lunch)


Replication of specific tables using Postgres 9.4.4 and Amazon Web Services I have currently setup a Postgres 9.4.4 RDS instance on Amazon Web Services. On this instance I hold 20 tables. In addition I also have Postgres 9.4.4 installed on my local server (OS: 64-bit Linux) which holds about 30 tables under one database. Both the cloud instance and my local server instance have overlapping tables i.e. there are some tables on the cloud that I replicate to my local and vice versa. I am not well versed with Postgres Replication procedures as I have never needed them and at the moment have been doing the transfer rather inefficiently via python scripts to read/write/append tables between these two databases.

I would appreciate any guidance from the community on what is the best procedure. Ideally I would like a solution where some tables on the cloud can be MASTER and SLAVE locally as well as allow other other tables to be a MASTER locally and a SLAVE in the cloud. Is this possible?

Have heard that maybe Burcardo or Slony could do this? Any good documentation/sites on this area would also be helpful.

According to the AWS Documentation, logical replication is possible from RDS Postgres instances, starting with version 9.4.9.  That would solve the RDS -> local direction.

The documentation doesn't make it very clear, but it seems that from version 10.4 an RDS instance can even be the receiver end (the subscriber) of logical replication:

  Beginning with PostgreSQL version 10.4, RDS supports the publication and subscription SQL Syntax for PostgreSQL 10 Logical Replication.


RBS and Fail Over Cluster with SQL Server 2012 I am trying to install one SQL Server 2012 cluster with two nodes. I will also be enabling Remote Blob Storage (RBS) on server so I was wondering if I need to take something in account while installing cluster?
Or I can install cluster like we normally do and then later on enable RBS like we normally do?  I mean does RBS require some special configuration/setting in SQL Fail Over Cluster?

The setup is pretty strait forward. As the TechNet article below states you need to make sure the Windows Share Name is the same across all nodes.

http://technet.microsoft.com/en-us/library/cc645886.aspx


How mysql process where condition I have a query like

Select * from tbl1

WHERE
  ( price > 10
AND qty > 200
AND condition=1
AND name like '%abc%')

My question is how MySQL will process this? My concern is that I want to run query in this way that results should be reduced on basis of conditions.

For example

( price > 10 AND qty > 200 AND condition=1 And name like '%abc%')

First, MySQL should filter records having price > 10 and apply next condtion qty > 200 on resultant dataset and at the end, it should put this condition AND name like '%abc%' on it.

How should I write query to achieve this?

Another question is: Does MySQL start reading conditions from start to end or from end to start?

How MySQL processes the query largely (and primarily) depends on the indexes on the table.

If no indexes exist, then of course a full table scan is required. Now, you are looking for a way to reduce the search space based on conditions. However:

Your query uses one "equals" condition
Your query uses two "range" conditions
It uses one LIKE clause with a prefix '%'

The LIKE clause, beginning with '%' negates any use of an index, unless it is a covering index, which is not the case in your query. This part of the clause negates any use of an index on the name column.

Well, you can use as many "equals" conditions as you like, where possible, but up to one range condition only, when using an index.

Any index you'll want to put on that table will start with the condition column, since it is the one column where you say condition = 1, which is an equality check. You are then left to choose any of the two columns on which you place a range condition. Choose the one which will most likely reduce more rows (leaving less rows in the search space).

So, you options are:

KEY (condition, qty)
KEY (condition, price)

So that MySQL will first match the condition column in the index, then follow up to the next column, whichever it is.

MySQL does not read conditions from start to end nor from end to start. It just notes down the various conditions, then tries, when possible, to find an index which satisfies them (or part of them). It then depends on the order of columns in the index -- not on the order by which the columns appear in the query.


Error - enabling bin log in mysql I have a mysql server with 5.5. I tried to enable the binlog using the below config file.

# BINARY LOGGING #
server_id                      = 1
log_bin                        = /mysql/binlog/mysql_bin
expire_logs_days               = 14
sync_binlog                    = 1
max_binlog_size                = 100M

/mysql/binlog/ is owned by mysql:mysql

After restart mysql I got the below error.

mysqld: File '/mysql/binlog/mysql-bin.index' not found (Errcode: 13)

So I tried this command.

added this to my.cnf

binlog_do_db                   = mydb
log_bin_index                  = /mysql/binlog/mysql-bin.index

And executed this commands.

touch /mysql/binlog/mysql-bin.index
chown mysql:mysql /mysql/binlog/mysql-bin.index

But still its throwing the same error.

Please never mind, I found a solution on this blog.

Still the mysql data directory points to /var/lib/mysql

https://schworak.com/blog/e53/move-mysql-binlog-to-another-disk-or-folder/

Then did chmod 700 binlog.


MariaDB slave replication stopped after slave upgrade to 10.1.41 After the recent MariaDB 10.1.41 update which happened on 01/Aug/2019, few of our slaves have stopped syncing relay logs from master which is on 10.1.40. Slaves which got automatically updated to 10.1.41 is now having the following status. Slave IO thread is in Preparing state and the logs are not getting written. I did a reset slave and initiated a change master with the positions again but still getting the same. The other slave servers which is in MariaDB 10.1.40, 10.1.33 etc are running as normal. These which got upgraded to 10.1.41 has issue?

Does anyone have any clue on this?

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: NULL
                  Master_Host: a.b.c.d
                  Master_User: xxxxxxxx
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: xxxxxxxxxxxxxx-bin.001135
          Read_Master_Log_Pos: 262316421
               Relay_Log_File: xxxxxxxxxxxxxxxxxxxxx-bin.003410
                Relay_Log_Pos: 4
        Relay_Master_Log_File: xxxxxxxxx-bin.001135
             Slave_IO_Running: Preparing
            Slave_SQL_Running: Yes
              Replicate_Do_DB: xxxxxxxxx
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 262316421
                        Relay_Log_Space: 498
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 111
               Master_SSL_Crl:
           Master_SSL_Crlpath:
                   Using_Gtid: No
                  Gtid_IO_Pos:
      Replicate_Do_Domain_Ids:
  Replicate_Ignore_Domain_Ids:
                Parallel_Mode: conservative

Looks like this issue:
https://jira.mariadb.org/browse/MDEV-20247

Body must be 30 characters.


Can SQL Server execute two BULK INSERTs in parallel? I have two heavy queries to run, and both of them take about 1 minute to run. Both queries are using the Bulk Insert command to insert data from text files to tables in the database.

For the two tables importing data, there are no indexes/triggers/constraints, just two empty tables to load data into.

For the two text files to load, each of them has around 20M rows.

Query 1:

Bulk insert table1
FROM 'table1.txt'  WITH
        (FIELDTERMINATOR ='|',
         ROWTERMINATOR = '0x0a',
         TABLOCK)

Query 2:

Bulk insert table2
FROM 'table2.txt'  WITH
        (FIELDTERMINATOR ='|',
         ROWTERMINATOR = '0x0a',
         TABLOCK)

I want to compare the running time under different scenarios.

Scenario 1: two queries running serially in one editor:

Query 1: 1 m 18 s
Query 2: 1 m 2  s
The total running time is 2m 20s.

Scenario 2: each query in a separate editor of the same Studio instance, running concurrently:

Query 1: 2 m 36 s
Query 2: 2 m 09 d
The total running time is 2m 40s

Scenario 3: each query in a separate Studio instance, running concurrently:

Query 1: 2 m 29 s
Query 2: 2 m 19 s
The total running time is 2m 29s

While scenario 2 and 3 seem to run queries concurrently, why the running time hasn't change much? For all scenarios, the CPU usage is between 17% to 21%, the disk usage is 50MB to 70 MB per second. No significant differences between the usage of CPU and disk are observed.

I am wondering what's happening under the hood? How does SQL Server execute these queries? Why is the running time nearly the same for all three scenarios? Is there any way to speed it up?

I'm using SQL Server 2016 Developer Edition on Windows 10 64-bit. I have quad core i7 and SSD in my laptop.

After testing more scenarios with 4 queries running together, I think the possible cause of low usage of CPU and disk is the hyperthreading with my Core i7:

With two editors running two queries concurrently, the most CPU usage is 25%.
With 4 editors running 4 queries concurrently, the most CPU usage is 50%.

Are you guys aware of ways to let one query exclusively use one core? Turning off hyperthreading is not an option for me, since my ThinkPad T460p doesn't support such an option in the BIOS.

After a long and beneficial discussion with Dan Guzman in this MSDN forums thread, the underlying scheduling scheme is finally clear to me. Here is a brief answer borrowing from the thread above:

  So what's happening here is that the 2 SSMS connections both initially
  use the same scheduler because it has the least load. The BULK INSERTs
  then both run on the same scheduler, which is the reason for the high
  SOS_SCHEDULER_YIELD waits.

  The choice of which scheduler is used is not based on query cost. SQL
  Server (actually SOS) basically tries to balance the workload among
  schedulers by examining the load_factor column of sys.dm_os_schedulers.

  When a new task is created for a query execution, SOS prefers to use the same
  scheduler it used for the last request on the session.  However, it
  will use a different scheduler if the load factor of the preferred
  scheduler is greater than a certain percentage of the other
  scheduler's average load factor.

In depth discussion could be found in the link above.

Thanks all guys for your enthusiastic help!


Is there a concept of an "include file" in SQL Server? I have a set of scripts that need to be run in a certain order. I would like to create a "master file" that lists all of the other files and their correct order. Basically like an include file from C++ or ASP/VBScript.

If you are using SQLCMD, you can use :r FileName to include a separate .sql file.

  :r FileName

  Parses additional T-SQL statements and SQLCMD commands from the file
  specified by FileName into the statement cache. FileName is read
  relative to the startup directory for Visual Studio. The file is read
  and executed after a batch terminator is encountered. You can issue
  multiple :r commands. The file can include any SQLCMD command,
  including the batch terminator that is defined in Tools, Options. You
  can use :r in a pre-deployment or post-deployment script to include
  other scripts.


SequelizeDatabaseError: cannot execute UPDATE in a read-only transaction I'm currently using Sequelize, NodeJS and PostgreSQL. I have users who update their accounts password on first login. Well, today I had this error appear after 3-4 successful logins.

What does this even mean? I did nothing out of the ordinary. A google search does not help me at all as they dont seem to have my problem.

This error appeared on this query:

UPDATE "Users"
SET "password"='-hash-',
"verificationCode"=0,
"startedOn"='2018-04-03 20:45:31.801 +00:00',
"emailVerified"=true,
"updatedAt"='2018-04-03 20:45:31.802 +00:00'
WHERE "userId" = 84

Any help?

It can be caused by these reasons

1) you connected to a follower. Check it, using select pg_is_in_recovery(); – on master, it will return false:

test=# select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 f
(1 row)

Of course, you can run UPDATEs only on the master DB.

2) (I suppose this is not your case, but just for completeness I add it) You're working with master DB, but transaction or session was set to be "read only":

set session characteristics as transaction read only;

or, inside transaction where UPDATE is being executed:

set transaction read only;


How does the Distributor know you have seeded/initialized your Subscriber using a backup? Im building my first transactional replication database for our Sharepoint team.  The Publisher database is fairly large 330GB+  Because the Publisher database is in a different data center than the Distributor and Subscriber database I will be perfoming the initial seed using a database backup instead of using a snapshot.

I understand that the Publisher will know the last log sequence number (LSN) from when the backup was taken.  The Subscriber will be populated from the back up and have it's own last LSN from the restore.

Im not sure how the process will work once I turn the synchronization on after everything has been initialized/restored.

Does the Publisher talk to the Subscriber through the Distributor to know how far out of sync they are?  I thought the Publisher and Subscriber did not have any real direct communication?
How will the proper records be sent to the Distributor from the Publisher when the Distributor is unaware of the initialization from backup.

Extra Details:
Publisher: SQL Server 2008 R2
Distributor: SQL Server 2012
Subscriber: Sql Server 2012

The link posted as a comment to your question explains the technical steps but may not have made clear how the setup really works.

When you create the subscription you inform the publisher that the subscriber has been restored from a backup (specifying the backup file used).  The backup you use  has to be taken after the publication was created.  When you setup replication you tell the distributor how long to keep transactions around (Transaction Retention Period).  The distributor then keeps these commands until they are delivered to all subscribers, or until the retention period has been reached.  So when the subscription is created, the LSN of the backup is checked to see if it was taken from within the retention period.  If so, then the backup and therefore the subscriber is valid.  Only the undistributed transactions created after the backup will need to be sent to the subscriber.

In your case you would:

setup the publication
take a backup
copy that to the subscriber before the retention period expires
restore the backup to the subscriber
create the subscription (using the initialize from backup option)
watch as the undistributed commands then get sent to the subscriber
finally see that the entire thing works!

Initializing from backup makes the entire thing much easier than snapshots, because you generally have backups running anyhow and, more often than not, can use compression.  This does mean that the entire DB is on the subscriber though, which may be desired or may not.


Postgres, how insert "0" value into serial? I have a serial id in my application table. However, I would like to insert a single 0 id record to mean global.

Is there a way I can insert this into my table without affecting the counter?

Sure, just do it. The counter only gets incremented when you call a Sequence Manipulation Function. In the case when you don't explicitly specify a value, the DEFAULT triggers a call to nextval(sequence_name)

CREATE TABLE foo ( id serial );
INSERT INTO foo (id) VALUES (0);

PostgreSQL 10+

It should be noted, that serial is the old and antiquated method of doing this. You should be using the standardized IDENTITY COLUMNS

CREATE TABLE foo (
  id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
);
INSERT INTO foo (id) VALUES (0);

See also

What are Identity Columns?
CREATE TABLE (syntax for IDENTITY COLUMNS)
CREATE SEQUENCE (which has the options for the columns)


MS SQL Server - Increase allocated memory My problem is that SQL Server takes a long time to ramp up it's memory usage for instances with TB worth of RAM, all the while we get intermittent waits of MEMORY_ALLOCATION_EXT that slow down our processing until SQL Server reaches its max memory.
We have Failover Clustered Instances (FCI) of SQL Server 2019 Enterprise Edition with terabytes worth of memory on the nodes. In usual use cases we only allow 1 instance of SQL Server per node, and so we set max server memory close to ~85% of the memory on the node, but we also set min server memory relatively low just in case SQL Server fails over to another node and needs to be brought online with a reduced memory footprint to limp along.

I am quite aware that if I set min memory up higher that SQL Server will consume the memory all at once. Turns out SQL Server does not allocate the min memory at startup.
I am aware that SQL Server will dynamically consume more memory from the OS as it needs it and that eventually it will reach the max server memory.
I am aware that running a big query or DBCC checkdb that pulls in a lot of data will force SQL Server to consume more memory from the OS.

Are there any other ways to force SQL Server to increase its memory usage quickly?

SQL Server will not use memory for buffer pool (its biggest use) until it needs to load pages from storage because they are referenced. Unless you are doing something odd to make memory allocation slow[†] then your delay is unlikely to be that but reading the data that it is being allocated in order to hold. I suspect that at the same time as those memory allocation waits, you have matching IO related waits.
To force SQL server to load things into memory, and therefore to allocate memory if available and needed, access things so that they need to be loaded. After starting your SQL instance, run a read-only workload so that your application's normal common working set is loaded into the buffer pool.
Don't just SELECT * FROM HugeTable as this is likely to load a bad balance of data (not the applications core working set) so the core working set will still need to be loaded from disk/network on next access anyway so you'll have the same IO delays. Also if HugeTable is truly huge SQL Server may see that it'll not all fit and not bother keeping a significant amount of it in memory anyway[‡].
[†] Running in a VM on an overloaded host so what looks like real RAM is really on-disk pages?!
[‡] Under normal circumstances you don't want an unexpected table/index scan on a large object to evict everything else from memory. A limit on how much is kept from one object for one query is an optimisation to try stop this from happening).


Is there a more efficient way to resolve this "one to many" circular reference? In our ERP database, there is 1 table for products called PRODUCTS, which contains both kits and single SKU product information, and a table called PRODUCT_KIT_ITEMS that contain the relationships between kit SKUs and their components.

In the PRODUCT table, each kit has a KIT_ID on the parent SKU row and the PRODUCT_KIT_ITEMS table lists each component PRODUCT_ID from the PRODUCTS table and the kit id of the parent SKU associated with it.

What I wanted was a list of all the SKUs of components associated with a parent SKU, both of which would be selected from the same column on the PRODUCTS table.

This was the solution I came up with:

SELECT   Parent.[PRODUCT_SKU] AS KitParent,
         Child.[PRODUCT_SKU] AS ChildSKU
FROM     [PRODUCT_KIT_ITEMS]
JOIN     (
          SELECT [PRODUCT_ID],[PRODUCT_SKU]
          FROM   [PRODUCTS]
         ) AS Child
ON       Child.[PRODUCT_ID] = [PRODUCT_KIT_ITEMS].[PRODUCT_ID]
JOIN     (
          SELECT [PRODUCT_ID], [PRODUCT_SKU],[KIT_ID]
          FROM   [PRODUCTS]
          WHERE  [KIT_ID] IS NOT NULL
          AND    [KIT_ID] != 0
         ) AS Parent
ON       Parent.[KIT_ID] = [PRODUCT_KIT_ITEMS].[KIT_ID]
ORDER BY Parent.[PRODUCT_SKU];

I'm a bit of a newbie and was wondering if there was a better way to accomplish this, for instance, without running two sub-queries on the PRODUCTS table.

If this is your current table schema:

CREATE TABLE PRODUCTS
(
   PRODUCT_ID INT PRIMARY KEY,
   PRODUCT_SKU INT,
   KIT_ID INT
);

CREATE TABLE PRODUCT_KIT_ITEMS
(
    KIT_ID INT,
    PRODUCT_ID INT REFERENCES PRODUCTS(PRODUCT_ID),

    PRIMARY KEY (KIT_ID, PRODUCT_ID)
);

INSERT INTO PRODUCTS VALUES
(1, 10, 1), (2, 20, 0), (3, 30, 0), (4, 40, 2),
(5, 50, 0), (6, 60, 0), (7, 70, 0), (8, 80, 0);

INSERT INTO PRODUCT_KIT_ITEMS VALUES
(1, 2), (1, 3), (1, 6), -- KIT_ID(1) HOLDS PRODUCT_ID(2, 3, 6)
(2, 5), (2, 7);         -- KIT_ID(2) HOLDS PRODUCT_ID(5, 7)
GO

13 rows affected

You can get it by selecting all products with KIT_ID <> 0 (Parent rows) and then use CROSS APPLY to select all child rows that match parent KIT_ID.

SELECT      P.PRODUCT_SKU AS PARENT_SKU,
            CHILDS.PRODUCT_SKU AS CHILD_SKU
FROM        PRODUCTS P
CROSS APPLY (SELECT P2.PRODUCT_SKU
             FROM   PRODUCT_KIT_ITEMS PKI
             JOIN   PRODUCTS P2
             ON     P2.PRODUCT_ID = PKI.PRODUCT_ID
             WHERE  PKI.KIT_ID = P.KIT_ID) CHILDS
WHERE        COALESCE(KIT_ID, 0) <> 0
ORDER BY     P.PRODUCT_SKU;
GO

PARENT_SKU | CHILD_SKU
---------: | --------:
        10 |        20
        10 |        30
        10 |        60
        40 |        50
        40 |        70

Your current query:

SELECT   Parent.[PRODUCT_SKU] AS KitParent,
         Child.[PRODUCT_SKU] AS ChildSKU
FROM     [PRODUCT_KIT_ITEMS]
JOIN     (
          SELECT [PRODUCT_ID], [PRODUCT_SKU]
          FROM   [PRODUCTS]
         ) AS Child
ON       Child.[PRODUCT_ID] = [PRODUCT_KIT_ITEMS].[PRODUCT_ID]
JOIN     (
          SELECT [PRODUCT_ID], [PRODUCT_SKU], [KIT_ID]
          FROM   [PRODUCTS]
          WHERE  [KIT_ID] IS NOT NULL
          AND    [KIT_ID] != 0
         ) AS Parent
ON       Parent.[KIT_ID] = [PRODUCT_KIT_ITEMS].[KIT_ID]
ORDER BY Parent.[PRODUCT_SKU];
GO

KitParent | ChildSKU
--------: | -------:
       10 |       20
       10 |       30
       10 |       60
       40 |       50
       40 |       70

dbfiddle here


ORA-01031 Insufficient Privileges Raised From Within Function When I run a CREATE USER statement within Sql Developer, the user is created just fine.  When I try to call a function which performs CREATE USER (followed by a bunch of other statements) I get the ORA-01031 error when that statement is executed.  On another machine this function is apparently completing successfully.  I have checked all the roles and system privileges but nothing stands out.  What could it be?

When you have permission to perform an action at the command line but not within a definer's rights stored procedure (and I hope you're using a stored procedure here, not a stored function), the problem is almost certainly that the privilege you need has been granted through a role, not directly to the user that owns the procedure.  Inside a definer's rights stored procedure, only privileges that have been granted directly to the owner of the procedure can be used-- privileges granted through a role (such as DBA) cannot be used.

You can verify that the problem is, in fact, that the privilege is granted through a role by disabling roles and then trying to create the user, i.e.

SQL> set role none;
SQL> create user ...

Assuming that fails, as I expect it will, you would need to grant the owner of the function the CREATE USER privilege directly

SQL> grant create user to <<owner of procedure>>

Then, you should be able to create users even with roles disabled and the stored procedure should work.


SQL Server Agent won't start When I'm trying to start SQL Server Agent, I got this error:

  TITLE: Microsoft SQL Server Management Studio
  Unable to start service SQLAGENT$SQL_NAGA on server NAGA. (mscorlib)
  ADDITIONAL INFORMATION:
  The SQLAGENT$SQL_NAGA service on NAGA started and then stopped. (ObjectExplorer)

Can you guys help me to solve this problem? I'm using 2014 Enterprise Edition.

Using @@version I got this:

Microsoft SQL Server 2014 - 12.0.4213.0 (X64)
Jun  9 2015 12:06:16
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 10586: )

I also found this error event related to SQL Server Agent in the Windows Event Log

Log Name:      Application
Source:        SQLAgent$SQL_NAGA
Date:          1/31/2016 6:17:24 PM
Event ID:      103
Task Category: Service Control
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      Naga
Description:
SQLServerAgent could not be started
(reason: Unable to connect to server 'NAGA\SQL_NAGA'; SQLServerAgent cannot start).

UPDATE:

Error log of SQL Server Agent

SQL Server and SQL Server Agent both logged on as Local System Account

Set the SQL Agent service to Automatic(Delayed). The first thing it will do is try to connect to SQL Server, and if SQL Server hasn't finished starting yet, SQL Agent can fail. Delaying the start for SQL Agent will give SQL Server the chance to finish.


PG fulltext search: Boost factor for word in the title I use PostgreSQL full text search and it works fine.
The database schema is simple. A page has a title and a content.
Up to now I just index the string which I get by joining title+content.
In the future I would like to adapt the ranking. If a search term is in the title
of a document, then I would like to rank this page higher.
How can I influence the search ranking in PostgreSQL?

Yes, you can do that using ts_rank:
You can use setweight to give different parts of the text search vector different weight:
SELECT * from atable
WHERE (to_tsvector('english', title) ||
       to_tsvector('english', content)
      ) @@ to_tsquery('english', 'happy <-> home')
ORDER BY ts_rank(setweight(to_tsvector('english', title), 'A') ||
                 setweight(to_tsvector('english', content), 'C'),
                 to_tsquery('english', 'happy <-> home'));

Then the title will be ranked higher (weight A) than the content (weight C).