Why does my transaction log use so much space? I have a staging table with 20 million rows that uses about 8GB of storage. After populating foreign keys and sanitizing data I need to move this data into a production table (which does not need all the source data used to key and normalize).

The production table ends up using about 6GB of storage. My choice was to use a straightforward method and simply run one large insert/select statement. When I start the transaction I am the only user in the database, and the transaction log is nearly empty.

The max transaction log size was set to 30 GB, which I thought would be adequate. Turns out the transaction needed 59GB of transaction log to complete. The production table has a clustered PK, a handful of nonclustered indexes, and some foreign key constraints that I did not bother removing.

Can anyone explain why SQL Server requires 59GB of transaction log space to copy 6 GB of data from one table to another in the same database?

Transaction logs are, as far as I'm aware, not stored in what we would consider a very disk space friendly manner.  This is because the logs have to be able to do several things.  Almost all the neat tricks of ACID compliance are not because the database data files exist, it's because the transaction log files exist.

Think about some of the things the logs have to do:

Allow a transaction to be rolled back to the state the database was in prior to the query running.  This means the database has to know not just what was in place, but how to get back there.
Keep the rest of the queries and transactions running from interfering with or being interfered by the query you're trying to run.  Even if you're not doing these kinds of things, the database will do them unless you specifically tell it not to.  The database engine needs to be able to seamlessly treat data not yet written to the database as though it were and as though it weren't.  Potentially at the exact same time.
The database employs write-ahead-logging, meaning it will write everything to the log before the transaction is considered complete.  Note that committing a transaction doesn't write the data to the database.  It means it's finished write-ahead-logging!  This means the data is first written to the log, and then later is copied to the actual database.  Writing data to logs is necessarily faster at the cost of not being space-efficient.
Assuming you're using the full recovery model, the database logs must support point-in-time recovery.  This means the system has to be able to recover the database to a specific point in time at all times regardless of what the database has undergone.  So the transaction logs would have to show how to recover the database to how it was before your massive query as well as after it.  This is different than the need to be able to rollback the transaction.  This is about recovery hours or days after the data has been committed.
Save to disk all of the above as fast as possible because performance is really important, too.

You may find it useful to read about the transaction log architecture.  It's really, really not surprising that transaction logs eat up so much disk space.

A database I work on is about 5 GB in size.  We restored the DB to a testing area so I could test log growing.  This was on a DB using Simple recovery.  One table in this database is about 2.5 million rows and is about 5% of the database.  It has a clustered index for the primary key, but no other indexes, foreign keys, triggers, or check constraints.  I ran two statements, essentially update bigtable set int_field = int_field + 1 followed by update bigtable set int_field = int_field - 1.  This does nothing at all but creates an awful lot of work for the database, especially because int_field is part of the clustered index.  Each execution caused the transaction logs to grow about 1 GB.

I'm assuming you ran (essentially):

insert into production.table (id, field, ...)
select id, field, ...
from staging.table

That will work, but it means the database has to keep track of an awful lot by default.  However, you can use it in a minimally logged manner that will help a lot.  If you really have to do this kind of thing, here's what I would do:

Do a full backup of the DB and logs.
Set the database to single-user mode.
Disable any nonclustered indexes on the target table.
Set the recovery mode to bulk logged.

From here, I'd follow MS's best practices for using INSERT INTO ... SELECT for large operations with minimal logging:

  Using INSERT INTO…SELECT to Bulk Import Data with Minimal Logging

  You can use INSERT INTO  SELECT  FROM
   to efficiently transfer a large number of rows from one
  table, such as a staging table, to another table with minimal logging.
  Minimal logging can improve the performance of the statement and
  reduce the possibility of the operation filling the available
  transaction log space during the transaction.

  Minimal logging for this statement has the following requirements:

  The recovery model of the database is set to simple or bulk-logged.
  The target table is an empty or nonempty heap.
  The target table is not used in replication.
  The TABLOCK hint is specified for the target table.

  Rows that are inserted into a heap as the result of an insert action
  in a MERGE statement may also be minimally logged.

  Unlike the BULK INSERT statement, which holds a less restrictive Bulk
  Update lock, INSERT INTO…SELECT with the TABLOCK hint holds an
  exclusive (X) lock on the table. This means that you cannot insert
  rows using parallel insert operations.

Once I was done, I'd undo the three steps I'd done above.  I'd revert the recovery model, index and single-user mode change, and do another backup.  You'll then probably want to rebuild your indexes.

I'm not sure if you actually need to change the recovery mode to bulk logged to get some advantage from the minimal logging.  I've never had to do exactly what you describe, so I can't be sure.


What is the difference between threads connected and threads running I am trying to understand the dashboard in mysql workbench and I saw this chart on bottom left.

threads connected: 149
threads running: 13

This was under a load and threads running increased as I put even more load on the server but it never went high. Is that normal? If threads connected is helping with parallel processing during loads, should it be higher?

Threads connected means the total number of client processes (threads) connected to the database server. This includes the count for threads running.

Thread running means the total number of client processes (threads) currently executing on the database server. The server is holding these connections while the client is waiting for a reply. These thread may be consuming IO/CPU, while others may do nothing while waiting for a table lock to be released. When the database is finished executing the thread, the client gets a reply, and the thread is changed from status "running" to "connected".


How does Transparent Data Encryption actually work? I've been trying to understand how TDE works with databases (particularly SQL Server). I've read numerous MSDN articles, blogs, info websites etc, but still cant get my head around all of it.

This is my understanding of TDE so far (with my assumptions & some questions):

A system key is created on installation of SQL Server -> but where is this stored??
The user creates a master key -> Now... Is this encrypted using symmetric key encryption and stored in the master db? If this is correct, then I assume that KEY = System key and TEXT = master key?
The master key is used to create a certificate -> this is then also stored in the master DB?
A per database encryption key is then generated (which is key for encrypting the DB data with symmetric encryption)
The certificate then encrypts the database encryption key, which is then stored in the database it was created for.

Now, for the last step then, would the process be this:

Encrypt(Certificate,Database Key) = Encrypted Database Key, then store EDK into database, then Encrypt(Database key, Database)?

Are my assumptions correct?
And then how does the data get decrypted? Because surly you cant retrieve the database key? Also, if someone gets hold of the certificate, doesn't that compromise the database?

I appreciate any assistance with this.

OK, In case anyone wants to know, I got to the bottom of this.
The chain is pretty easy to work out:

Create Service Master Key (automatically done upon install)
Create Database Master Key (user creates their own key)
SymetricEncrypt(Service Key,Database Master Key) -> Store Encrypted Master Key into Master DB
Create Certificate
SymetricEncrypt(Database Master Key, Certificate Private Key) -> Store Encrypted private key in Master DB
Create Database Encryption Key (system creates a random key, but must be manually added to database)
ASymetricEncrypt(Certificate, Database Encryption Key) -> Store Encrypted DEK into database.

So, to decrypt the database data, you'll need to:

Use the SMK to decrypt the DMK
Use the DMK to decrypt the cert private key
Use the certificate to decrypt the DEK
Use the DEK to decrypt the database

The starting point is the Service Master Key... how is this stored (if you encrypt it, you can't retrieve data, and if left in plaintext your system is exposed).
The answer is, the SMK is encrypted with Data Protection API, which is a service built into windows.
Basically, data is encrypted with "secrets" from your user profile. So the same user profile must be used to decrypt the SMK.
Now that I know this, I can use DPAPI in my applications to encrypt my encryption keys :)


Load Balancing / MPP with Postgres 9.1 I'm trying to setup a testbed for bigdata analytics (basically around 2 to 3 TB base, slight write [only feeding the db], but possibly read intensive queries for analytics purposes).

Since I'm somewhat experienced with vanilla PostgreSQL (but with 10GB base), I was considering trying the 9.1 new synchronous replication to achieve load balancing. The objective is to get Tableau be very responsive. Thus, I could avoid costly MPP GreenPlum, AsterData and others.

However, I was wondering if :
- going straight to MPP Databases, GreenPlum for example, was a good idea ? (for 2-3 TB, is Greenplum really needed?)
- once Synchronous Replication is up and running, how shall I configure my applications ? Is the load balancing done by the master or is another solution need (Pgpool for example) ?

Thank you for you insights !

In principle, 2-3TB should be possible to do without resorting to a shared-nothing architecture, but Vanilla PostgreSQL still does not have good parallel query functionality.  All you will achieve by replication is the ability to apportion queries out to individual nodes.  I don't believe that PostgreSQL supports federated queries out of the box and I would be very surprised to find that Tableau has direct support for sharding at the client.  I'd guess that PostgreSQL won't perform very well on a data set this big.

There is work in progress on a parallel query facility for PostgreSQL, but AFAIK it's not included in 9.1.  My gut instinct is that some release in the next few years will include this feature but it's not quite there yet.  I don't see evidence of much effort being put into a federated query facility.

Another option: SQL Server

Unless you're married to Postgres you might find that SQL Server provides a cost-effective option to Greenplum for a 2-3TB data set.  It is licenced by the socket rather than the core, so a loaded 2-4 socket Xeon or Opteron box gives good bang for buck as a platform.  For a smaller user base I believe you can still licence Enterprise Edition by CAL as well.

A couple of 24/25 disk arrays on a high-end RAID controller will do sequential reads fast enough to saturate a PCIe-x8 slot (2GB/sec).  A simple table scan query with SQL Server will handle data at this rate without using much CPU (obviously depending on the actual computation), so you have some headroom to add controllers and arrays if you want faster I/O.

SQL Server also comes with a passably good set of B.I. tooling, including an OLAP server.  Tableau is not cheap, around £1,800/seat last time I looked. Depending on the number of users you may find that the 'good-enough' tooling that comes with SQL Server could offset the costs of the DB licence anyway.  Most third party reporting tools also play nicely with SQL Server.

Disclaimer: I'm not any sort of die-hard Microsoftie but I have done a lot of B.I. work with SQL Server and Oracle over the past decade or so.  SQL Server is actually quite a good B.I. platform.


MySQL Group Replication fails to understand "::ffff:" IP Address I'm trying to set up group replication using 3 x MySQL 8.0.16 databases, but when I start up the second Node it fails. The error log reports:

[GCS] Connection attempt from IP address ::ffff:10.12.1.2 refused. Address is not in the IP whitelist.

But if I add a whitelist:

group_replication_ip_whitelist="::ffff:10.12.1.2";

I get:

[GCS] Invalid IP or subnet mask in the whitelist: "::ffff:10.12.1.2";

Is this an issue with the server at my end, or an issue with Group Replication not understanding the IP Address?

My configuration is:

server_id=<different for each one>
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
transaction_write_set_extraction=XXHASH64
group_replication_group_name="8112d18e-9e6e-11e9-a526-0e51be33883f"
group_replication_start_on_boot=off
group_replication_local_address= "10.12.1.1:33061" /*This is different for each one*/
group_replication_group_seeds= "10.12.1.1:33061,10.12.1.2:33061,10.12.1.3:33061"
group_replication_bootstrap_group=off

If I look on the first nodes error log I can see:

Connection attempt from IP address ::ffff:10.12.1.2 refused. Address is not in the IP whitelist.'

But again, adding it to a whitelist throws the above error about there being an invalid IP Address,

That output is done in the most generic possible way, using IPv4-mapped IPv6 address.

It should work if you simply add:

group_replication_ip_whitelist="10.12.1.2";


Ignore a particular column on update trigger I have a PostgreSQL table with 15 columns. I have an on update trigger on it. I'd like to exclude column10 from it. Meaning when there is any update on column10 only then there should not be any action other than it. If full update or any other column update, trigger should fire as normal.

I don't seem to find any easy way to do this. Can someone suggest please?

Documentation shows that you can define specific columns in an on update trigger:

create trigger foo
after update of column1, column2, ... -- other columns, except column10
on your_table ...


Split databases into groups of roughly equal datasize I am trying to split all databases from sys.databases into 7 roughly equally sized Groups based on data ROWS size.

I want a table that contains every database and these databases are in 7 different Groups (Days of weeks - monday thru sunday).

Code that Groups into 7 Groups, but not equal size:

SELECT
    db.name,
    NTILE(7) OVER(ORDER BY DbSize.TotalDataSize) as bucket, --how to order?
    DbSize.TotalDataSize * 8 / 1024 as size_mb
FROM sys.databases AS [db]
CROSS APPLY
    (
        SELECT SUM(mf.size) AS TotalDataSize
        FROM sys.master_files AS [mf]
        WHERE mf.database_id = db.database_id
        AND mf.type_desc = 'ROWS'
    ) AS DbSize

How can I order so not all big databases end up in the same bucket?

Needs to support SQL Server 2008 and newer.

Edit:
it doesn't matter if, let's say, 1 database is huge and others very tiny. The big database would then (optimally) reside in its own Group, but could still be larger than any other. The important thing is that not the 2-3 biggest databases are in the same Group.

I could do a cursor and for each database, see which Group has the least amount of data in it, but I felt it must be some better way to achieve this using set-based code?

The very crude method is to pair up largest database with smallest, then 2nd largest with 2nd smallest and so on. This should work assuming that your database sizes are evenly distributed.
Implementation has been hacked toghether in 5 min, so can probably be improved a lot:

;WITH DBSizes AS
(
SELECT
    db.name,
    DbSize.TotalDataSize * 8 / 1024 as size_mb,
    ROW_NUMBER() OVER( ORDER BY DbSize.TotalDataSize ) AS RankedSizeInc,
    ROW_NUMBER() OVER( ORDER BY DbSize.TotalDataSize DESC ) AS RankedSizeDes
FROM sys.databases AS [db]
CROSS APPLY
    (
        SELECT SUM(mf.size) AS TotalDataSize
        FROM sys.master_files AS [mf]
        WHERE mf.database_id = db.database_id
        AND mf.type_desc = 'ROWS'
    ) AS DbSize
)
SELECT *, NTILE(7) OVER(ORDER BY RankedSizeInc DESC) AS Quartile
FROM
    -- Pair-up largest and smallest DBs toghether, then 2nd largest and 2nd smallest and so on
    -- TOP 50 PERCENT removes duplicates produced by UNION ALL
    ( SELECT TOP 50 PERCENT *
    FROM(
        -- Return DBs in order of increasing size
        SELECT name, size_mb, RankedSizeInc
        FROM DBSizes AS a
        UNION ALL
        -- Return DBs in order of decreasing size
        SELECT name, size_mb, RankedSizeDes
        FROM DBSizes AS b
    ) AS c
    ORDER BY RankedSizeInc
) AS d


Enterprise Manager cannot connect to 11g database instance In Oracle 11g when we are opening OEM in browser it is showing the following message :

"Enterprise Manager is not able to connect the database instance."
Listener status is showing Up and
Agent Connection to instance the status is showing unavailable.

please provide a solution.

I am attaching a screenshot of my problem. I still did not find any solution yet.plz help

You may need to resecure the agent. Here is a link that should help.

http://jhdba.wordpress.com/2008/12/10/clearing-an-enterprise-manager-agent-that-fails-to-upload/


Inconsistent query execution times using trigram indexes? I have a table residences...

I installed trgm: create extension pg_trgm;

After adding trigram indexes to both grp and name columns using:

CREATE INDEX residences_name_trgm ON residences USING GIN (name gin_trgm_ops);
CREATE INDEX residences_grp_trgm ON residences USING GIN (grp gin_trgm_ops);

And checking performance of a simple query... it appears that the total runtime for a query on the name column runs ~100x faster. Why is this?

EXPLAIN ANALYZE SELECT * FROM residences WHERE name ILIKE '%Sutton%';
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on residences  (cost=36.02..47.90 rows=3 width=1872) (actual time=0.390..0.720 rows=21 loops=1)
   Recheck Cond: ((name)::text ~~ '%Sutton%'::text)
   ->  Bitmap Index Scan on residences_name_trgm_gin  (cost=0.00..36.02 rows=3 width=0) (actual time=0.354..0.354 rows=21 loops=1)
         Index Cond: ((name)::text ~~ '%Sutton%'::text)
 Total runtime: 0.814 ms
(5 rows)

and

EXPLAIN ANALYZE SELECT * FROM residences WHERE grp ILIKE '%Sutton%';
                                                                  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on residences  (cost=97.93..8879.41 rows=5927 width=1872) (actual time=5.516..115.634 rows=5968 loops=1)
   Recheck Cond: ((grp)::text ~~ '%Sutton%'::text)
   ->  Bitmap Index Scan on residences_grp_trgm_gin  (cost=0.00..96.45 rows=5927 width=0) (actual time=4.366..4.366 rows=5968 loops=1)
         Index Cond: ((grp)::text ~~ '%Sutton%'::text)
 Total runtime: 119.779 ms
(5 rows)

For reference, both name and grp columns are CHARACTER VARYING(50).

Futhermore... counting distinct values in each column yields:

SELECT COUNT(DISTINCT grp) FROM residences;
421

SELECT COUNT(DISTINCT name) FROM residences
7750

That is, the name column actually has ~20x more distinct values, but returns values much faster.

More information on this technique can be found on depesz's article WAITING FOR 9.1 – FASTER LIKE/ILIKE and another similar tutorial on the PalominoDB blog.

That is, the name column actually has ~20x more distinct values, but returns values much faster.

Exactly so. An index on name is more selective than an index on grp, so index use is more effective.

This is confirmed by the fact that your first query, on the name field, the bitmap index scan onresidences_grp_trgm_gin gets only 21 results as shown by actual( ... rows=21 ...) in the explain analyze output. On grp you get 5968 results.

In a bitmap index scan every heap read and re-check has a significant cost, so it makes perfect sense that finding 21 rows is going to be a lot faster than finding 5968.

BTW, please always show select version() and your server configuration, as well as anything else that's relevant from the wiki's guide to reporting problems.


mysqldump: dump several tables from db. what will be locked? I have active database with dozens of tables. Among them several are unused (no queries/insertions are performing over them for a while).
I want to dump that inactive tables. I would suggest that it will be harmless to use mysqldump -u.. -h.. -p... mydb tbl1 tbl2 > dump.sql and nothing except that tables will be locked?

Given the current way you are doing it

mysqldump -u.. -h.. -p... mydb tbl1 tbl2

each table is locked during the SELECT phase in a specific way

Note the effects during the dump

Each table that is MyISAM should allow SELECTs but block INSERTs, UPDATEs and DELETEs
While dumping tbl1, tbl2 is fully accessible
While dumping tbl2, tbl1 is fully accessible

If those tables (tbl1 and tbl2) are inactive, then such a dump would be harmless to busy tables.


Stop and Start the Instance In a Job I have SQL Server job. In the last step of the job, I want to stop/start SQL Server.

Next, I created .bat file with NET STOP "MsSqlServer"

I tried SSIS package that uses the execute process task with cmd.exe and then execute the .bat file to restart mssqlserver and it works fine...

However, when I called this package in the SQL Server job, it stops the service but does not not start it back.

the question is how to stop/start sql server after another sql serve job complete..?

i have sql server/analysis services/reporting services in one station with 64 GB Memory.and i have nightly job with this steps :

1-ETL that Update DW
2- Process Analysis services Database
3-Update Reporting services Subsciptions

Sometimes, in beginning the AS Processsing in the step 2, it gives me error: there is no resource Available and and the job got failed.

All I want is a good memory configuration for this environment.

what strategy I must choose for Memory Management ?

Another problem: After step 2 for update subscription (i have about 50 Subscriptions), if I try Update Subscription with clean memory (after I restart SQL Server service), the subscription executes very fast.

If i don't restart the SQL Services, it take long time to run and sometimes it never gives me results.

You can't expect a service that is not running to execute a command to start itself. You need to use something external. The SQL Agent service is dependent upon the SQL service running, that's why you can't do this in a job inside of SQL Agent. You could use the default windows task scheduler, or you could look to have the SQL service try to restart itself if it is found to be stopped.

That being said, your reason for restarting the service in order to release memory means that you should focus your efforts on proper memory management and not on how to dynamically restart the instance.


How to import CSV file in SQL server 2008? I am trying to import a CSV file in SQL Server 2008. BULK INSERT is a way to go but it is applicable for CSV from SQL Server 2014 onwards.

What would be an alternative way to achieve this goal?

Any thoughts/ideas much appreciated.

SQL Server has always supported bulk inserting from CSV files, you just have to specify field/row terminators.

file.csv contains:

foo,bar,1
blat,splunge,2

Then we do this:

CREATE TABLE #foo(a varchar(32), b varchar(32), c int);

BULK INSERT #foo FROM 'c:\temp\file.csv'
WITH (ROWTERMINATOR = '\n', FIELDTERMINATOR = ',');

SELECT * FROM #foo;

Results:

a        b        c
-------- -------- ----
foo      bar      1
blat     splunge  2


How to check sybase command line history? How can I check the previous command line history in sybase command line?

Unless you have enabled history in isql, then you are out of luck.
Moving forward, you can enable it by following these directions:
Check http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc30191.1570100/doc/html/san1367605044069.html for details of --history parameter:

--history [p]history_length [--history_file history_filename] – Loads the contents of the command history log file, if it exists, when isql starts. By default, the command history feature is off. Use the --history command line option to activate it.

p – indicates command history persistence; in-memory command history is saved to disk when isql shuts down. If you do not use the p option, the command history log is deleted after its contents are loaded into memory.

history_length – this parameter, which is required if you use --history, is the number of commands that isql can store in the command history log. The maximum value of history_length is 1024; if a larger value is specified, isql silently truncates it to 1024.

history_file history_filename – indicates that isql must retrieve the command history log from history_filename. If p is specified, isql also uses history_filename to store the current session’s command history. history_filename can include an absolute or a relative path to the log file. A relative path is based on the current directory. If you do not indicate a path, the history log is saved in the current directory. When --history_file is not specified, isql uses the default log file in $HOME/.sybase/isql/isqlCmdHistory.log.


Getting error "must declare the scalar variable" in SQL function even though it is declared I am altering function like:

ALTER FUNCTION [dbo].[fn_CalculateListing](@Listing TypeListingDatePrice READONLY)
RETURNS TypePreviousListingResult
AS
BEGIN
    DECLARE @tbl_ListingDateDetails TypePreviousListingResult
    RETURN @tbl_ListingDateDetails
END
GO

But throwing error as:

Msg 137, Level 16, State 1, Procedure fn_CalculateListing, Line 6
Must declare the scalar variable "@tbl_ListingDateDetails".

Why this giving error and asking for declaration even though I have declared that before using that variable?

TypePreviousListingResult is a table type created as CREATE TYPE TypePreviousListingResult AS TABLE....

Previous question: How to write function in sql which accept table as input and return result back as table?

There are two problems here:

For Multistatement TVFs, you just need RETURN; instead of RETURN @variable;.
It does not appear as though you can use a User-Defined Table Type (UDTT) as the return table type. That will need to be specified explicitly (i.e. each column name and datatype).

If this were a Scalar UDF, then the syntax of specifying only the datatype name in the RETURNS clause, and specifying the variable name in the RETURN statement, would be correct. However, you cannot return TABLE (or CURSOR) types from a Scalar UDF.

Please see the MSDN page for CREATE FUNCTION for more details.

You will need something along the lines of:

ALTER FUNCTION [dbo].[fn_CalculateListing]
(
  @Listing dbo.TypeListingDatePrice READONLY
)
RETURNS @ListingDateDetails TABLE (ColumnName DataType,...)
AS
BEGIN
    -- Do stuffs

    RETURN;
END;
GO

For a broader discussion of the available techniques to pass sets of rows around, see: How to Share Data between Stored Procedures by Erland Sommarskog.


Finding the row with the latest date for each row type I've got an SQL database (SQLAzure  V12 version running on Azure) that looks like the following:

DECLARE @VehicleData TABLE (
    id int PRIMARY KEY,
    Registration nvarchar(50) NOT NULL,
    [DateTime] DateTime NOT NULL,
    [Event] nvarchar(20) NOT NULL,
    [Data] nvarchar(200) NULL,
    INDEX IX1 CLUSTERED(Registration, Datetime),
    INDEX IX2 NONCLUSTERED(Registration, Event, DateTime)
    )

An auxiliary table looks like this:

DECLARE @EventList TABLE (
    id int PRIMARY KEY,
    [EventName] nvarchar(20) NOT NULL
    )

The table is filled with location data taken from tracking units on multiple vehicles. Each row might have a particular [Event] type, e.g. Update, HarshBraking, Delivery etc.

I have a query that needs to get the latest date for each event for a particular vehicle BEFORE A SPECIFIED DATE.

I'm using the following query to do that:

@Registration nvarchar(20),
@DateTime datetime

SELECT V.Registration, V.DateTime, V.Event, V.Data
    FROM VehicleData V
    INNER JOIN (
        SELECT Registration, MAX(DateTime) LatestDate, Event
        FROM VehicleData
        WHERE Registration = @Registration AND DateTime < @DateTime
        AND Event in (SELECT Name FROM EventList)
        GROUP BY Registration, Event ) AS L
    ON V.Registration = L.Registration AND V.Event = L.Event AND V.DateTime = L.LatestDate

Note the line AND Event in (SELECT Name FROM EventList) was added later as it forced the query to use the IX2 index, which seemed to improve the query speed.

The query takes some time to run (around 10 seconds or so) and I would ideally like to see if I can optimise it further to take the speed down to less than a second (as it is tied to a GUI action).

How can I optimise this query further?

Edit You can download the execution plan from http://pastebin.com/9nVZzYPL. Just paste the contents into a .sqlplan file and then load it into SSMS to view the plan. Query 1 is the only thing of interest here. The other queries are "post processing" operations that don't contribute much to the overall query time.

How about using windowing functions?

WITH Numbered AS
(
SELECT *, ROW_NUMBER() OVER (PARTITON BY Event ORDER BY DateTime DESC) AS rownum
FROM VehicleData
WHERE Registration = @Registration
AND DateTime < @DateTime
)
SELECT V.Registration, V.DateTime, V.Event, V.Data
FROM Numbered V
WHERE rownum = 1;

Make sure you have an index on: (Registration, Event, DateTime) INCLUDE (Data)

You can use your extra predicate on EventList too if you like, of course.


MAXDOP Value of 0 compared to Value <=8 I have been researching dozens of sites regarding MAXDOP but I seem to be missing something basic. MAXDOP=0 enables SQL to use all available CPU's. MS recommended settings is to set MAXDOP = number of CPU's if less than 8, and MAXDOP = 8 if 8 or more CPU's (noted, there are some finer points, e.g., NUMA, etc., but this is the basic guidance.) If MAXDOP=0 enables all CPU's and a system has 8 or less CPU's, how is a setting of MAXDOP=0 different from setting MAXDOP=#CPU's? Don't they both result in all x CPU's being available? I can see where this makes perfect sense if # of CPU's>8.

Thanks!

MAXDOP=0 enables SQL to use all available CPU's. - This is true upto 64 logical processors . If you have more like 128 MAXDOp=0 will still only use 64 logical processors.

For cores < =8 you will still need to check on hyper threading enabled or not as that is also a factor that counts here.

Also for cores less than or equal to 8 on single numa , ideally is asked to keep as # of processors which is where confusion arises. Ideally in today's world its very rare to find such servers because if you are having issues on less than 8 core and looking to set MAxDOP you have a bigger problem.

But yes for less than 8 more or less will be same. But point to note is query process may or may not use all cores even when set to that MAXDOP. Also this setting should be checked in parallel with Cost threshold of parallelism as well


SSIS SQL Server 2008 errors I just want to know how to import data from source to SQL Server 2008 database using SSIS.

I have a simple txt file (actually it's from this blog : http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/)

1,James,Smith,19750101
2,Meggie,Smith,19790122
3,Robert,Smith,20071101
4,Alex,Smith,20040202

I created a new table in SQL Server :

CREATE TABLE Test
(ID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
BirthDate SMALLDATETIME)

I draged Data Flow Task, then Flat File Source and OLE DB Destination.

I created new Flat file connection, choose the text file, and in Advanced tab, there are 4 column, so I changed their data type as follow:

Column 0 : two-byte signed integer [DT_I2]
Column 1: String[DT-STR] - Outputcolumnwith :20
Column 2: String[DT-STR] - Outputcolumnwith :20
Column 3: database timestamp with precision [DT_DBTIMESTAMP2]

Then, set up new OLB DE Connection, edited it, set [dbo].[Test] under "Name of the table or the view" + mapped each column from input to destination.

But when I run it, these errors occurred  :

  Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (4)
  reached the maximum allowed (1); resulting in failure. This occurs
  when the number of errors reaches the number specified in
  MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

  Error: 0xC02020A1 at Data Flow Task, Flat File Source [52]: Data conversion failed. The data conversion for column "Column 3" returned
  status value 2 and status text "The value could not be converted
  because of a potential loss of data.".

  Error: 0xC0209029 at Data Flow Task, Flat File Source [52]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "output column
  "Column 3" (90)" failed because error code 0xC0209084 occurred, and
  the error row disposition on "output column "Column 3" (90)" specifies
  failure on error. An error occurred on the specified object of the
  specified component.  There may be error messages posted before this
  with more information about the failure.

  Error: 0xC0202092 at Data Flow Task, Flat File Source [52]: An error occurred while processing file "D:\New folder\text.txt" on data row 1.

  Error: 0xC0047038 at Data Flow Task, SSIS.Pipeline: SSIS Error Code   DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component
  "Flat File Source" (52) returned error code 0xC0202092.  The component
  returned a failure code when the pipeline engine called PrimeOutput().
  The meaning of the failure code is defined by the component, but the
  error is fatal and the pipeline stopped executing.  There may be error
  messages posted before this with more information about the failure.

What could be the cause for not import successfully in my case?

I'm using evaluation version on Windows 7.

Thanks so much!

FINALLY I COULD IMPORT FROM TXT FILE TO SQL 2008 DATABASE,

LET'S NAME THE 4 COLUMNS AND THEIR DATA TYPE AS :

id : two-byte signed integer [DT_I2]
firstname : String[DT-STR] - Outputcolumnwith :20
lastname: String[DT-STR] - Outputcolumnwith :20
yeardate: String[DT-STR] - Outputcolumnwith :20

THEN IN DERIVED COLUMN EDITOR :

Derived column name : yeardate
Derived column : replace 'yeardate'
Expression : (DT_DBTIMESTAMP)(SUBSTRING(yeardate,1,4) + "-" + SUBSTRING(yeardate,5,2) +     "-" + SUBSTRING(yeardate,7,2))

You've come across a fairly well-known problem. When SSIS is trying to import the data, it provides locale-aware conversions and the format YYYYMMDD doesn't convert nicely. You can use the "import as string and convert to actual datetime" approach you've outlined in your solution but that's slower and consumes more resources than using the native approach of telling SSIS to quit being so damn smart.

Right click on your flat file source and select the Show Advanced Editor. In the Input and Output Properties, expand Output Columns, find your column (BirthDate) and change the FastParse property from False to True.

With only that change, the package will execute successfully

Also, an excellent answer from SO on the same issue


What can cause what appears to be missing data in a single table I have a situation where it appears as if I've lost data in a single table.

I have a .NET Core API, with a logging middleware. This middleware takes the entire request and response and stores in the database, for debug and support purposes. Other than the request and response, the logging table also contains an IDENTITY(1,1) column (which is the primary key) and other various columns.

Over a duration of about 15 minutes, it appears as if 2798 rows have "disappeared" from the table. The primary key simply jumps from 3561297 to 3564095.

During the same time period, I've got debug logs from clients of this API which indicates that something went wrong during the request. My IIS log notes that any request which causes database access during this time period had taken between 30000 and 40000 ms to complete.

The odd thing is, that other tables does not appear to have similar gaps. In fact other tables have had data inserted and updated in them, without issue it appears. So it doesn't appear to be a full system crash.

Through my Google-Fu I've come across the fact that gaps in a IDENTITY(1,1) key can be caused by a restart of the server -- and in that case the gap is roughly 1000. However, in this case I'm 100% certain that the database has not been restarted, yet I have this gap.

I've tried reproducing this error by inserting rows and adding a THROW statement in the end, in my test environment to no avail. I've tried to simulate timeouts by both adding a WAITFOR DELAY and making a TABLOCKX, HOLDLOCK on the table, while trying to do a lot of inserts through the application. Every database operation is done through stored procedures btw.

I'm quite frankly stumped. Does anyone have any ideas / hints as to why I've got this apparent data loss in one table? Or what can cause a massive gap in the primary key like this?

As far as I can tell I have no transactions in the logging middlewares SQL. And IMPLICIT_TRANSACTIONS is OFF on the server, and I'm not using XACT_ABORT in the query. How can a transaction rollback occur, if I do not execute a BEGIN TRANSACTION? I thought SQL Server only made "auto transactions" if IMPLICIT_TRANSACTIONS was set to ON?

Over a duration of about 15 minutes, it appears as if 2798 rows have
  "disappeared" from the table. The primary key simply jumps from
  3561297 to 3564095. During the same time period, I've got debug logs
  from clients of this API which indicates that something went wrong
  during the request. My IIS log notes that any request which causes
  database access during this time period had taken between 30000 and
  40000 ms to complete.

I don't think the rows have disappeared.

Your debug logs show that something went wrong during requests to the database when the gaps happened. If queries are aborted & rolled back the identity values will not roll back with it.

Another example is that the application has a timeout window of 30 seconds. Queries that take longer than that will be cancelled and the insert will be rolled back.

As mentioned earlier, in the case of rollback, the identity value allocated will not be rolled back. Instead, the next identity value in line will be used when retrying the insert.

There could have been other reasons that the insert was failing, such as network loss, inserting values that would be truncated, wrong datatype conversions, ....

Below is a reproducible example that shows two gaps:

CREATE TABLE dbo.identitytable(id int identity(1,1), val varchar(255))
INSERT INTO dbo.identitytable(val)
VALUES('BLA1');

GO
BEGIN TRAN
INSERT INTO dbo.identitytable(val)
VALUES('BLA2');

ROLLBACK TRAN

GO

INSERT INTO dbo.identitytable(val)
VALUES('BLA3');

GO

SELECT * FROM dbo.identitytable;

GO

INSERT INTO dbo.identitytable(val)
VALUES(REPLICATE('B',256));
GO

INSERT INTO dbo.identitytable(val)
VALUES('BLA5');

SELECT * FROM dbo.identitytable;

With the last select producing a result with two gaps:

id  val
1   BLA1
3   BLA3
5   BLA5

If an insert with multiple rows is rolled back then the gap will be bigger too:

BEGIN TRAN
INSERT INTO dbo.identitytable(val)
SELECT 'BLA6'
FROM master..spt_values;
ROLLBACK TRAN

INSERT INTO dbo.identitytable(val)
VALUES('BLA7');

SELECT * FROM dbo.identitytable;

Result

id  val
1   BLA1
3   BLA3
5   BLA5
2546    BLA7

DB<>Fiddle

  But how can a transaction rollback occur, if I do not execute a BEGIN
  TRANSACTION? I thought TSQL only made "auto transactions" if
  IMPLICIT_TRANSACTIONS was set to ON?

Queries without BEGIN TRANSACTION or without the "bad idea" IMPLICIT_TRANSACTION setting enabled are auto-committed transactions in itself.

When canceling/aborting the modification of data due to the reasons specified above, undoing these changes have to be done at the database level. This is done via a rollback. If this did not exist, your data would be in an incorrect state.


Postgresql Encoded and decoded I have a table abc, in which one column name where some data is encoded and some are not encoded. When I run the decode query the exception is found. How to handle it. There is a large number of rows in this table.

create table abc
(
id serial primary key,
name varchar(500)
)

select decode(name,'base64') from abc;

  exception occurs: SQL state: 22023

Now how can I convert all non-encoded data to encoded? Or how can I handle this exception.

I might be way off because the question is extremely vague, but you could try something like this:

DO $$
DECLARE
x character varying[] := SELECT name FROM abc;
z record;
BEGIN
FOR z IN x
LOOP
    SELECT decode(z, 'base64');
EXCEPTION
    WHEN SQLSTATE '22023' THEN encode(z, 'base64');
END LOOP;
END; $$


RCSI & ETL processing compatibility The Situation:
I have a write-heavy, read-light ETL workload with the basic process of:

get chunk of raw data (generally 1000-5000 records) from an import landing table.
pass the chunk of data from step 1 to the requesting windows service for some heavy duty calculations & processing. Each record ingested in a chunk may result in 100+ downstream records in other tables.
Windows service calls a save SP which has 14 MemOpt table variable parameters containing the processed "chunk" of data (three of these table variables have upwards of 50k rows or more).

The work is insert heavy and there is very little read ops going on in step 3, however, the "chunk" may contain data to be deleted and/or updated.  There is minimal read contention between step 1 & 3.
I've virtually eliminated deadlocks during step 3, although there is
significant blocking during 3 with insert/update ops.

Question:
Would Snapshot or RCSI isolation help when performing the insert/update ops in Step 3?
What are the risks of introducing update conflicts?

With Snapshot or RCSI isolation you get optimistic locking model where Readers don't block Writers and Writers don't block Readers. Beware that with optimistic locking query results for Readers may differ from pessimistic locking, since Readers don't wait for Writers to complete

Would Snapshot or RCSI isolation help when performing the insert/update ops in Step 3?

Writers still block Writers since Exclusive locks are held, but it of course depends which rows / pages / tables are locked. So no, I would say RSCI or Snapshot won't help for step 3
In fact, Snapshot isolation may potentially even make things worse, since it operates on transaction level while RCSI operates on statement level

What are the risks of introducing update conflicts?

According to the table below, you may get the update conflicts in Snapshot isolation (not RCSI)


How to “select * from table from database” with -e? I want to select and show 1 row form a table with the mysql -e.

I was trying out the following but no luck,

mysql -u root -e “select * from table1 from db01 limit 1”

Actually I am trying to dump a single line from hundreads of tables to take a sample from them and redirect to a file. So I want to use shell and don’t want to be in MySQL console.

I have used show the above way but select is not working.

Everyone is suggesting to do select * from table1 but didn’t mentioned how to mention database.
So how can I achieve this?

Got it!

Just had to add the database name before the execute flag (-e) and the sql statement.,

mysql -u root db01 -e “select * from table1 limit 1”

Where,

db01 is the database name
table1 is the table name
limit 1 for showing only 1 record


SQL Regex String Matching of Two Words I'm trying to write a query to show the rows containing a code that begins with 11-digit number followed by -1 or -2 then _DataSheet or _RoHS

Examples

02704198976-1_DataSheet
03448106681-1_RoHS

Query

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

Results

0 rows returned

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

Query

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

Results

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

Without regex, I think you need an OR for the last requirement (for the part that is either DataSheet or RoHS:

WHERE m.code LIKE CONCAT(REPLICATE('[0-9]', 11), '-[1-2][_]DataSheet%')
   OR m.code LIKE CONCAT(REPLICATE('[0-9]', 11), '-[1-2][_]RoHS%')


Return many rows on a plsql Oracle10g I'm doing a little project and I need to do a plsql to return many rows.

I have someting like:

Table(id, data1, data2, from)

And the user gives me the value of from and I have to return all that.

The SQL is easy:

SELECT * FROM Table WHERE from = dataIntroduced

But, I don't know how the plsql can return that.

I read about collections but I think is not what I need

One way of return select data from PL/SQL is using Oracle table functions.  Below is an example where the line SELECT * FROM table_a where test_a = par1; should be replaced by your select statement + change the table name.

Create table Table_a
(test_a varchar2(1))
/

insert into table_a
values('a')
/

create or replace package test_package as
TYPE col_table_1 is table of TABLE_A%ROWTYPE;
function test_plsql_table(par1 varchar2) return col_table_1
  pipelined;
end;
/

create or replace package body test_package as
  function test_plsql_table(par1 varchar2) return col_table_1
    PIPELINED as
    cursor temp_cur is
      SELECT * FROM table_a where test_a = par1;
  begin
    for cur_rec in temp_cur loop
      pipe row(cur_rec);
    end loop;
  end;
end;

/

SELECT * from TABLE( test_package.test_plsql_table('a'))
/


Insert RECORD into a table in a trigger function I would like to insert a RECORD data type variable (NEW variable) into a table in a trigger. What would the SQL look like?
The following attempts were unsuccessful:

EXECUTE 'INSERT INTO my_table VALUES ' || NEW;
EXECUTE 'INSERT INTO my_table VALUES ' || NEW.*;
EXECUTE 'INSERT INTO my_table SELECT * FROM ' || NEW;

Actually, you do not have to expand the record manually. As long as number, sequence and types of columns match between the two tables, you can use this much simpler form:

EXECUTE 'INSERT INTO my_table SELECT ($1).*'
USING NEW;

Since your table name seems to be stable, you do not even need dynamic SQL with EXECUTE. Just a plain INSERT:

INSERT INTO my_table SELECT (NEW).*

The parentheses are required for a composite type to make the syntax unambiguous.

Ask yourself what may change in your app in the future:

If the target table should stay in sync with the source table (or target and source are the same), then it's better not to list columns explicitly.
If you want to select only certain columns and do not want to change that with possible updates to the source or target table, it's better to supply a column list.


Aggressive index tuning Background info - I have a table that stores Job information with a unique JobID (PK) and a ClientID (one client can have many jobs), as well as bunch of columns that are heaped data.

I have ran sp_BlitzIndex and it  returned this:

  dbo.Job.PK_Job_1 (1): Row lock waits: 399; total duration: 21 minutes;
  avg duration: 3 seconds; Page lock waits: 171; total duration: 2
  minutes; avg duration: 1 seconds; Lock escalation attempts: 125,805;
  Actual Escalations: 0.

This table only has 35K rows in it, but it is heavily used and updated every few seconds. The current index is on the PK which is a unique value. I do not know much about index tuning, but if I remove this index and create a nonclustered index for the JobID + ClientID, am I on the right track to reducing the aggressiveness of the index? Or am I making it worse?

We don't have the full picture about the table or the queries that access the table or the load in the database/server but from what we know:

  Background info - I have a table that stores Job information with a unique JobID (PK) and a ClientID (one client can have many jobs), as well as bunch of columns that are heaped data.

OK, so the table has a JobID and a PRIMARY KEY constraint (and therefore an index on it).
There is a ClientID which is an (explicit or implicit) foreign key to Client.
We don't know if there is a FOREIGN KEY constraint defined.   Is a FOREIGN KEY defined?
We don't know the other columns of the table and their sizes. What are the columns, types and sizes?
We don't know if the index on (JobID) is clustered or not (the "heaped data" suggests not but we can't be sure). Is the table clustered or a heap?
It would better to provide the whole CREATE TABLE statement with all the indexes.

  This table only has 35K rows in it, but it is heavily used and updated every few seconds.

OK, but "heavily" is not a number. 100 queries per second or 10K queries per second?
A row insert/update/delete every few seconds or all rows updated every few seconds?
The load on the table and database matters:

What queries are run?
Is the load OLTP or OLAP?
What is the version and edition of SQL Server?
What are the server specs (RAM, CPUs, HDD/SSD)?

  The current index is on the PK which is a unique value. I do not know much about index tuning, but if I remove this index and ...?

No, do not go and blindly remove indexes, especially when the table has so few (i.e. just one).
You could add indexes on columns used in joins and WHERE conditions and expect (usually) no problems and likely improvements.

  ... and create a nonclustered index for the JobID + ClientID, ...?

The specific suggested index on (JobID, ClientID) is likely useless, considering there is already a unique index on (JobID).
Adding an index on (ClientID) on the other hand will likely help some queries (that have joins to Client). Since ClientID is an FK, it is also likely that you have such queries.
Still, adding an index blindly may give you no performance gain (and even make things worse), so test, test, test, before and after the index.

  .. am I on the right track to reducing the aggressiveness of the index? Or am I making it worse?

I'm not sure what "index aggresiveness" means but see the points above about potential actions.
If you really want to see significant improvements in the efficiency of queries, you'll need to do some proper analysis of the structure of the table(s) and the queries that run most often and then testing of different indexing strategies.


how to query multiple junction tables to create 1 record In normalizing data for a Contract bridge tournament, it seems like I am ending up with 13 junction tables. Each tournament will have 2 winners, 2 runner ups, and 4 semi-finalists, and a Tournament director, Assistant Tournament director, and maybe 1-2 Substitutes. For the sake of simplicity, I am working with tables as shown here:

I am trying to create a query which will produce the following result.

Knowing only basic SQL, Querying 1 junction table is not a problem, but 2 or more junction tables has me stumped. Is there a better way to structure my data? I will need to do Insert, Update, and Deletes also. Any help or suggestions will be most welcome.

This answer assumes you can only have one entry per tournament in each of the Winner1Test, Winner2Test, RunnerUp1 and RunnerUp2 tables. You should contrain this with a unique constraint.

SELECT r.TourneyDate,
r.TourneyTime,
r.TournamentID,
w1.Winner1,
w2.Winner2,
r1.RunnerUp1,
r2.RunnerUp2
FROM TournamentResultsTest AS r
LEFT OUTER JOIN Winner1Test AS w1 ON w1.TourneyID = r.TournamentID
LEFT OUTER JOIN Winner2Test AS w2 ON w2.TourneyID = r.TournamentID
LEFT OUTER JOIN RunnerUp1 AS r1 ON r1.TourneyID = r.TournamentID
LEFT OUTER JOIN RunnerUp2 AS r2 ON r2.TourneyID = r.TournamentID

If my assumption is incorrect and each table can have multiple entries per tournament, then you would need to use outer apply (top 1) rather than outer join.


ORA-00001 - But no name We have a huge table with nested tables and such.  We are using Bulk Collect & FORALL to insert.

When loading data, I am getting this error:

ORA-00001: unique constraint (.) violated

There is no constraint name......so, how can I find out 1)  What or where is causing the error, and, 2) Which record it is?

I know you can keep exceptions with FORALL, but we are doing this in lots of 500, so it is hard to find out the actual record, and what constraint / nested table it is, etc.

I'm looking for any help anyone can give.

Many thanks!

A possible bug for not displaying the constraint name with bulk DML and SAVE EXCEPTIONS clause:

Bug 7210333 : CONSTRAINT NAME IN SQLERRM IS NOT OUTPUT WHEN SAVE EXCEPTIONS IS USED

To catch the records causing the error, you could use DML error logging as well:

create table t1(c1 number unique);

begin
  dbms_errlog.create_error_log('T1', 'T1_ERRORS');
end;
/

Insert data:

declare
  type t_varray is varray(4) of number;
  l_varray t_varray := t_varray(1, 2, 2, 3);
begin
  forall i in 1..l_varray.count
  insert into t1 values (l_varray(i))
    log errors into t1_errors
    reject limit unlimited;
end;
/

Actual data:

select * from t1;

        C1
----------
         1
         2
         3

Rows violating the constraint with row data and constraint name:

select * from t1_errors;

ORA_ERR_NUMBER$ ORA_ERR_MESG$                                                ORA_ERR_ROWID$       ORA_ERR_OPTYP$       ORA_ERR_TAG$         C1
--------------- ------------------------------------------------------------ -------------------- -------------------- -------------------- ----------
              1 ORA-00001: unique constraint (BP.SYS_C0013693) violated                           I                                         2


Oracle PK index extremely low performance There's a table MYHIST containing 42M rows.

The table has a primary key column MYHIST_ID NUMBER(22) with a valid index PK_MYHIST on it, and 50 more columns (of types VARCHAR2, NUMBER and DATE), which seems to be not interesting regarding this topic.

Suppose, that statistics for the table (and it's PK index) has been gathered 5 minutes ago.

select index_name, table_name, index_type, num_rows, distinct_keys, sample_size, status
  from user_indexes
 where table_name = 'MYHIST';

 INDEX_NAME | TABLE_NAME | INDEX_TYPE | NUM_ROWS | DISTINCT_KEYS | SAMPLE_SIZE | STATUS
----------------------------------------------------------------------------------------
  PK_MYHIST |    MYHIST  |    NORMAL  | 42297450 |    42297450   |    42297450 | VALID

Unexpectedly, the simplest query with a filter by MYHIST_ID ends with full scan. It's guaranteed that no more than 1000 rows in the table having MYHIST_ID > 4615200.

    select *
      from MYHIST t
     where MYHIST_ID > 4615200
     order by MYHIST_ID desc;

    Plan:

    --------------------------------------------------------------------------------------
    | Id  | Operation            | Name    | Rows     | Bytes       | Cost    | Time     |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |         | 38067431 | 14655960935 | 3872743 | 12:54:33 |
    |   1 |   SORT ORDER BY      |         | 38067431 | 14655960935 | 3872743 | 12:54:33 |
    | * 2 |    TABLE ACCESS FULL | MYHIST  | 38067431 | 14655960935 |  746842 | 02:29:23 |
    --------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ------------------------------------------
    * 2 - filter("MYHIST_ID">4615200)

With an index hint the plan appears to be even worse.

    select /*+ index(t PK_MYHIST) */ *
      from MYHIST t
     where MYHIST_ID > 4615200
     order by MYHIST_ID desc;

    Plan:
    ---------------------------------------------------------------------------------------------------
    | Id  | Operation                      | Name       | Rows     | Bytes       | Cost    | Time     |
    ---------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |            | 38067431 | 14655960935 | 9274232 | 30:54:51 |
    |   1 |   SORT ORDER BY                |            | 38067431 | 14655960935 | 9274232 | 30:54:51 |
    |   2 |    TABLE ACCESS BY INDEX ROWID | MYHIST     | 38067431 | 14655960935 | 6148331 | 20:29:40 |
    | * 3 |     INDEX RANGE SCAN           | PK_MYHIST  | 38067431 |             |   85356 | 00:17:05 |
    ---------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ------------------------------------------
    * 3 - access("MYHIST_ID">4615200)

Could you explain what causes the situation, when the index doesn't give an expected performance?

As there are more than 10% of the rows with access("MYHIST_ID">4615200), a full table scan is the fastest way to select them.

If you would try a much higher number (eg. "MYHIST_ID">46152000), Oracle's optimizer may prefer using that index.


Database Files-Related Issue I am using MySQL database and using Ubuntu linux machine.

I noticed that my database, db_test, is under the path /var/lib/mysql/db_test

There are files with the suffix .frm, .MYD, .MYI like the following:

/var/lib/mysql/db_test# ls

cars.frm
cars.MYD
cars.MYI

customers.frm
customers.MYD
customers.MYI

departments.frm
departments.MYD
departments.MYI

...

Seems each .frm, .MYD, .MYI files group mapped with one table in the database.

I have following two questions to ask:

What are the three files doing exactly?
If I create a new directory under path /var/lib/mysql/ say db_test_2 , and copy every file from db_test_1 directory to db_test_2 , will it also create a new database db_test_2 which has exactly the same contents(tables) as db_test_1's ?

Does this physically database files moving action create the same result as following command-line actions:

dump the database db_test_1 out
create a new database db_test_2
then dump the db_test_1 database back into the new database db_test_2?

If so, it seems moving files are much faster then using mysqldump to copy databases. Any opinions on this?

Answer to your first question: Each file contains info for the table

cars.frm has the table structure
cars.MYD has the table data
cars.MYI has the table indexes

Given this layout, the cars table uses the storage engine MyISAM.

Answer to your second question: Yes, provided the following circumstances

every table uses the storage engine MyISAM
no INSERTs, UPDATEs, or DELETEs are running
DB Connections using TCP/IP are disabled

To check to see if all the tables are MyISAM, run this query:

SELECT COUNT(1) TableCount,engine
FROM information_schema.tables
WHERE table_schema='db_test_1'
GROUP BY engine;

You should only see just MyISAM. If you see other storage engines, you cannot just copy.

If your root@localhost password is mypass, here is all you need to to safely move all the tables regardless of storage engine:

# USERPASS="-uroot -pmypass"
# service mysql restart --skip-networking
# mysql ${USERPASS} -A -e"CREATE DATABASE db_test_2"
# mysqldump ${USERPASS} --routines --triggers db_test_1 | mysql ${USERPASS} -A -Ddb_test_2
# service mysql restart

If the data is huge, do this instead:

# USERPASS="-uroot -pmypass"
# service mysql restart --skip-networking
# mysql ${USERPASS} -A -e"CREATE DATABASE db_test_2"
# mysqldump ${USERPASS} --routines --triggers db_test_1 > /root/MyData.sql
# mysql ${USERPASS} -A -Ddb_test_2 < /root/MyData.sql
# service mysql restart

Give it a Try !!!


Transactions in SQL Server Emergency Mode I know that while the database is marked as suspected users cannot start new transactions, and the only access is via emergency mode which gives only read only access. I want to know how are  transactions handled in this case

firstly when a database is marked as suspected
secondly when we use emergency mode

Are they rolled back normally?

Thanks.

firstly when a database is marked as suspected

There may be other reasons but the most common one is when you have some issue with data file or log file of database and it goes through crash recovery after SQL Server is restarted, since SQL Server will not be able to recover the database due to corrupt, incorrect or missing information in one of the files(data or log) it has to mark database as suspect. Learn how to make database suspect (only for testing purpose. It goes without saying that please do not try this on Prod and even UAT).

  secondly when we use emergency mode.

In any case where you want to gain access to contents of database but are not able to because it has been marked suspect. You can then put database in emergency mode which is "inconsistent database" provided to you without fully doing crash recovery. Please note you might not always be successful.

  Are they rolled back normally?

When you use emergency mode you have lost all possibility to bring database online so you force it to "kind of come online with inconsistent data" using emergency mode. When you do so no recovery happens and you are just given read access to inconsistent database. After this when you run checkdb with repair_allow_data_loss then all kinds of actions are taken to bring database online in consistent state, to do so repair_allow_data_loss will remove any such thing which might block database to come online.

Putting database in emergency and then recovering it should not be treated as method to solve suspect database issue. Restoring from good backup should be the preferred way.

Would also suggest you to read Paul Randal blog


How to get a list of current Oracle CDBs on server If I have a server with one or more CDBs, what command can I run on the server in order to obtain a list where each item uniquely identifies one of the CDBs?

(a "CDB" is an Oracle Container Database)

Note 1:
I realize that it is rare to have more than one CDB on a server.  But in that case I would still want to obtain the unique CDB name of that one CDB.

Note 2: I know that the command cat /etc/oratab will list a bunch of DB installations on the server.  But this includes installations that no longer exist.  I want a list of only the currently existing CDB installations.

This is how I list running database instances. Container-databases or non-container databases:

  cat >> ~/.bash_profile <<EOF
  oinst() {
      /bin/ps -ef | grep ora_lgw[r] | awk '{print $8}' | sed -e 's/ora_lg[w]r_//'
  }
  EOF

  $oinst
  CDB1
  CDB2
  CDB3

If database instances are not running you are left with information from  /etc/oratab, init<SID>.ora, orapw<SID> or database-files on ASM, filesystem or RMAN.


Create two tables with identical superkeys? I have an existing table called Realms.  This has the superkey rlmID (just a surrogate key).  We are planning to add tables that group realms in some context -- let's just calll them RealmGroups.  This will require adding a realmGroupID column to Realms.  No problemo.

Not all Realms will be part of RealmGroups, so Realms.realmGroupID would be nullable.  In this case, is it better to have a separate table (RealmsRealmGroups) that has (rlmID,realmGroupID)?  Since the superkey of Realms and RealmsRealmGroups would be the same, I see no advantage to creating a separate table, but my DBA is suggesting that we should use a separate one for organizational purposes.

Are there any technical or practical reasons to use a separate table to store information for the same superkey?

A separate table would give you the flexibility of having realms that are in more than one group. If it makes sense to do that in your business context, or it might be something you would want in the future, then you might want to seriously consider that option. Otherwise, having the Realms.realmGroupID as nullable is simpler and easier to work with.


How do I use subquery on the same table in MySQL? I have a query like this which takes a really long time to run. The table is around 4 million rows.

DELETE FROM TABLE WHERE value_was IS NULL OR value_was <= value_now;

I'm hoping I could create an index for (value_was, value_now) so I could do something like

DELETE FROM TABLE WHERE
ID1 IN (SELECT ID1 from TABLE where value_was IS NULL)
OR ID2 IN (SELECT ID2 FROM TABLE WHERE value_was <= value_now);

This table doesn't have primary key. It has two composite keys. And I guess I cannot use the same table in subquery, but how do I improve the performance of the first query?

Thanks very much any suggestion would be much appreciated.

You could try to use EXISTS clause instead of IN, since its usually less performance expensive:

  DELETE FROM TABLE T_delete
  WHERE EXISTS (SELECT 1
                FROM TABLE t_join
                WHERE value_was IS NULL
                AND T_delete.ID1 = t_join.ID1)
  OR EXISTS (SELECT 1
                FROM TABLE t_join
                WHERE value_was <= value_now
                AND T_delete.ID2 = t_join.ID2);

Not sure if it would improve the performance, but you could also try to split this query into two instead of using a OR.

There's more info about optimizing queries with EXISTS here.


High Latch Wait time with Logon trigger enabled while full backups are running I have 14 production servers with SQL 2016 standard version, 128 gb of RAM, 16 CPU and SSD drives.

Microsoft SQL Server 2016 (SP2-CU15-GDR) (KB4583461) - 13.0.5865.1 (X64)   Oct 31 2020 02:43:57   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2019 Standard 10.0  (Build 17763: )

All servers have the same hardware but two of my servers are installed on Windows Server 2019 as the others are on Windows Server 2012.
I have the same logon trigger enabled on all my databases. This logon trigger runs fine most of the time. But, on one of my servers that is on Windows Server 2019, the logon trigger fails while full backups are executing. (Note that the other server that is on Windows Server 2019 is new and still only has small databases on it so I can't really be sure it will run fine when we put bigger databases on it.)
When the full backups starts, latch wait time goes high like crazy, as it is normally around 0 (on SSD drives). This is the only server that has this problem with latch wait time during full backups. (all other stats are fine, CPU is fine, ect.)

And while this happens, my logs are full of the errer

Logon failed for login 'login' due to trigger execution

After investigation, I've found out that if I disable the logon trigger on this server, the full backups are able to execute fine, with a small occasional peak to latch time, but stays fine most of the time.

My questions are:

Could it be possible that the OS (Windows Server 2019 vs Windows Server 2012) makes a difference as my only server that has a problem is on Windows Server 2019?
Why would the logon trigger affect the full backups that much?

Right now my only solution is to disable the logon trigger on that server, as clients cannot connect on the server while full backups are taken, since logon trigger execution fails. But we use the logon trigger for security, to log people who login on the servers outside of the application.
Here is the definition of my logon trigger
CREATE TRIGGER [Logon_Trigger_Track_IP]
ON ALL SERVER FOR LOGON
AS
BEGIN
  INSERT INTO [master].[dbo].[TRACETABLE2]
   --the auditing snippet below works fine in a
  --login trigger,
  --database trigger
  --or any stored procedure.
  SELECT
    getdate()                                    AS EventDate,
    HOST_NAME()                                  AS HostName,
    SUSER_SNAME()                                AS sUserName
    WHERE convert(nvarchar(max),ConnectionProperty('local_net_address')) not like '192.168.0%'

  END
GO

The TRACETABLE2 table on master permission has been given to PUBLIC.
CREATE TABLE [dbo].[TRACETABLE2](
    [EVENTDATE] [datetime] NOT NULL,
    [HOSTNAME] [nvarchar](128) NULL,
    [USERNAME] [nvarchar](128) NULL
) ON [PRIMARY]
GO

GO
GRANT INSERT ON [master].[dbo].[TRACETABLE2] TO PUBLIC
GRANT SELECT on [master].[sys].[dm_exec_connections] TO PUBLIC

Collation is Latin1_General_CI_AS on all servers.
Here are the latches from the server that is having troubles.

Here is the Cost threshold for parallelism.

I forgot to mention all my backups are encrypted with a server certificate in AES_256

I did some tests based on the comment of rois and simplifying the trigger to this produces the same problem.
CREATE TRIGGER [Logon_Trigger_Track_IP]
ON ALL SERVER FOR LOGON
AS
   BEGIN
     DECLARE @i INT
  END
GO

Update 2021-03-04 : Microsoft was able to reproduce the problem! Waiting for news from them. Will keep you updated.

Update 2021-05-07 : Okay so after more than two months of troubleshooting with Microsoft, they've figured that I run out of worker threads. But as to why it happens with the logon trigger on servers on Windows Server 2019 and why its not happening on Windows Server 2012, no clue. (The same waits happen but to a lesser extent on Windows Server 2012.)
The final conclusion was that the logon trigger has a high cost on the server, and that I can achieve what I need to do with SQL Audit, which has a less higher cost on the server. So I just stopped using logon triggers and switched to SQL Audit instead.

This isn't an answer really, but it's too big for a comment. I hope it will be helpful.
Using version (got the same results with SQL Server 2016 on Windows 10):

Microsoft SQL Server 2019 (RTM-CU9) (KB5000642) - 15.0.4102.2 (X64)
Jan 25 2021 20:16:12   Copyright (C) 2019 Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Pro 10.0  (Build 19042:
)

I see peculiar behaviour with the trigger in question. I used SqlQueryStress to simulate some workload on my local instance (you probably need to build it from newest commit, older versions were too slow for me).
As query I used: SELECT 1, Number of iterations: 100000, Number of Threads: 10. Everything else left as default. I started the workload with GO button and after few seconds stopped it with Cancel. When the trigger is enabled, the moment I stop the workload, I get following pair(s) of messages in error log:

Logon failed for login '***' due to trigger execution. [CLIENT: ]

The client was unable to reuse a session with SPID 73, which
had been reset for connection pooling. The failure ID is 1. This error
may have been caused by an earlier operation failing. Check the error
logs for failed operations immediately before this error message.

This isn't the same issue, because it seems I can use the instance normally. I also haven't observed any latch problems. The only queries affected are the ones I cancelled. But the underlying cause may be the same. This happens only under certain workload (1k+ batch requests in my case). It could be some race condition in code, which is exposed on newer Windows versions. I would speculate it manifests itself differently under different workloads. Personally I think you should contact Microsoft.


SELECT NOT IN two foreign keys from other table I am trying to find a SELECT query that returns all of tableA.tba_id that are not exists on tableB (note that are two different foreign keys - tbb_tbAID1 and tbb_tbAID2 between data.
You can find DB Model here

What I can do so far
This only tests one column

SELECT tba_id FROM tableA
WHERE tba_id NOT IN
  (SELECT tbb_tbAID1 FROM tableB
  WHERE tbb_date > '$date2')

I get a MySQL error when trying to test on both foreign keys

  #1241 - Operand should contain 1 column(s)

Query:

SELECT tba_id FROM tableA
WHERE tba_id NOT IN
  (SELECT tbb_tbAID1,tbb_tbAID2 FROM tableB
  WHERE tbb_date > '$date2')

Try 2:

SELECT tba_id FROM tableA
WHERE tba_id NOT EXISTS
    (SELECT 1 FROM tableB
    WHERE (tba_id = tbb_tbAID1 OR tba_id = tbb_tbAID2)
  AND tbb_date > '$date2')

But gets more results than the first try (that suggests that it's extremely wrongly done)

Try3:

SELECT tba_id
FROM tableA
  JOIN tableB ON tba_id!=tbb_tbAID1 AND tba_is!=tbb_tbAID2
WHERE tbb_data > '$data'

This results on enormous set of data, far away from first method.

There are various ways to do it.

For NOT IN (not recommended because you will get unexpected results if your columns are nullable), you should have 2 subqueries as the a IN (subquery) requires that the subquery returns a single column:

SELECT tba_id FROM tableA
WHERE tba_id NOT IN
      ( SELECT tbb_tbAID1
        FROM tableB
        WHERE tbb_date > '$date2'
      )
  AND tba_id NOT IN
      ( SELECT tbb_tbAID2
        FROM tableB
        WHERE tbb_date > '$date2'
      ) ;

or a UNION:

SELECT tba_id FROM tableA
WHERE tba_id NOT IN
      ( SELECT tbb_tbAID1
        FROM tableB
        WHERE tbb_date > '$date2'
      UNION ALL
        SELECT tbb_tbAID2
        FROM tableB
        WHERE tbb_date > '$date2'
      ) ;

My favourite is NOT EXISTS and seems you got it almost right, in Try2.

SELECT tba_id
FROM tableA
WHERE NOT EXISTS                  -- notice the difference with your query
      ( SELECT 1
        FROM tableB
        WHERE (tba_id = tbb_tbAID1 OR tba_id = tbb_tbAID2)
          AND tbb_date > '$date2'
      ) ;

Of course, you could use 2 subqueries as well:

SELECT tba_id
FROM tableA
WHERE NOT EXISTS
      ( SELECT 1
        FROM tableB
        WHERE tba_id = tbb_tbAID1
          AND tbb_date > '$date2'
      )
  AND NOT EXISTS
      ( SELECT 1
        FROM tableB
        WHERE tba_id = tbb_tbAID2
          AND tbb_date > '$date2'
      ) ;

You could also use LEFT JOIN / IS NULL check:

SELECT tba_id
FROM tableA
  LEFT JOIN tableB
    ON  (tba_id = tbb_tbAID1 OR tba_id = tbb_tbAID2)
    AND tbb_date > '$date2'
WHERE tbb_date IS NULL ;

or:

SELECT tba_id
FROM tableA
  LEFT JOIN tableB AS b1
    ON  tba_id = b1.tbb_tbAID1
    AND b1.tbb_date > '$date2'
  LEFT JOIN tableB AS b2
    ON  tba_id = b2.tbb_tbAID2
    AND b2.tbb_date > '$date2'
WHERE b1.tbb_tbAID1 IS NULL
  AND b2.tbb_tbAID2 IS NULL ;


Get next row based on current row and conditional ordering I have this table:
Id   Name
1    M
2    P
3    A1
4    C
5    A2
6    T
1009 A2
1011 A1
1010 A3

I can get it sorted by Id if Id < 1000 and by Name if Id > 1000 (thanks to the help of people here) with this query:
SELECT *
FROM table1
ORDER BY
  CASE WHEN Id < 1000 THEN Id ELSE 1000 END,
  Name;

Which gives
Id   Name
1    M
2    P
3    A1
4    C
5    A2
6    T
1009 A2
1010 A3
1011 A1

Now, given an Id, I want the next row in the order I just ordered by. So far, I've managed to get the result with two SQL queries, based on the givenId. If givenId < 1000 I do:
SELECT *
FROM table1
WHERE
  Id > @givenId
ORDER BY
    CASE WHEN Id < 1000 THEN Id ELSE 1000 END,
    Name
LIMIT 1

If givenId > 1000 I do:
SELECT *
FROM table1
WHERE
  Id > 1000 AND Name > (SELECT Name FROM table1 WHERE Id = @givenId)
ORDER BY
    CASE WHEN Id < 1000 THEN Id ELSE 1000 END,
    Name
LIMIT 1

The Id with value 1000 will never exist so I don't have to worry about that case. This works, but I'm wondering if there's a better way to do this? Perhaps with one query instead of two? I've tried to come up with something better but can't seem to.

This should get you started:
SELECT *
FROM (
  SELECT Id, Name, LEAD(Id) OVER w AS Next_Id, LEAD(Name) OVER w AS Next_Name
  FROM table1
  WINDOW w AS (ORDER BY CASE WHEN Id < 1000 THEN Id ELSE 1000 END, Name)
) WHERE Id = 6;

Note that the order by is working correctly as it returns Id = 1011 as the next Id.
https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=ce891ef6631a142500b59ff2c686e108


Add standby database on different OS version? Primary database is installed on RHEL 6.1. Standby database software is installed on RHEL 7.0. I am trying to add new standby database by using Grid Control. Database versions are same 11.0.2.0.3.

I was able to add standby database on RHEL 6.1 using same method.

I used the following link to install ORACLE on RHEL 7.0. Does it have anything to do with oracle group gid is different than RHEL 6.1? On RHEL 7.0, I used oinstall(54321) dba(54322) oper(54323) for gids. On RHEL 6.1, I used oinstall(501) dba(502) oper(503). If it doesnt matter, what should i do?

I was able to resolve the issue by executing root.sh file in the AGENT_HOME folder. I think it was permission issue, I guess.


How do I reindex my SQL Server Express database to free up space when it's already full? Being the naive guy that I am, I let my DB get full (10GB is the max for SQL Server Express), and have now decided to drop columns to reclaim space. I'm dropping 10 columns from a ~50M row table to save space, but even after dropping them, I keep getting "Error 1011: Could not allocate a new page for database ...".

I cannot shrink the database, I cannot reindex the database, I cannot rebuild indexes, I can't add a filegroup, or anything like that. I have 97% fragmentation (even after defragging my hard drive), and I feel like I'm running out of options. Even attempting to run an ALTER INDEX .. REBUILD WITH (SORT_IN_TEMPDB = ON) gives the same error message.

What are my options now? Should I just migrate to a different DB (MYSQL, PostgreSQL)?

Create a new database.

Create the skinnier version of your table.

Create supporting objects (procedures, views, users, other tables, what have you).

Migrate the data (or some subset of the rows) from the original database to the skinnier version of the table in the new database, using methods like:

insert newdb.dbo.table(...) select cols from olddb.dbo.table;
SSIS
BCP
import/export wizard

Stay on top of your new database as it grows. Update your connection strings to point at the new database (or drop the old one and rename the new one to the old name).

Technical problems with specific software or platforms tend to have simpler solutions than throwing hands in the air and switching do different software / platform...


Killed Backup in Rollback State for Days I killed a backup job of an 80 MB database which has been rolling back for days...

I tried to take the database into single user mode, but that too hung in rollback state.

I tried killing all sessions connected to the database, but still cannot drop it.  I used this script to do the kill:

DECLARE @kill varchar(8000) = ''; SELECT @kill = @kill + 'kill ' +
    CONVERT(varchar(5), session_id) + ';'
FROM sys.dm_exec_sessions
WHERE database_id = db_id('fpddev92')
exec(@kill)

The task's most recent wait type is CMEMTHREAD.  I cannot restore the database with a different name since there is an application using it that has a hard-coded database name within the app.

I am allowed to drop and restore the database if need be.  How do I solve this issue???  It is suspended.  No blocking.

I'm afraid if I restart the service it will simply pickup the rollback upon startup.

Any help is greatly appreciated!  I have never seen anything like this in my life...

Since the backup is waiting on a CMEMTHREAD, that is a critical section waiting for a memory allocation, it is likely that a restart of the service will either allow recovery to start on the database, or for the database to be marked "suspect", at which point you could drop and restore from backup.  Recovery on a 70MB database, assuming the log file isn't many many gigabytes, should be nearly instantaneous.

As always, before doing anything of this nature, you need to ensure you have verified-good backups of critical databases on this instance - by "verified", I mean you've restored them onto some other server (or your workstation), to ensure you can actually perform a restore.  There is nothing like getting free advice from the internet only to find you've lost everything.

As an aside, you should ensure you have the most recent service pack and cumulative update installed on your SQL Server; see SQLServerBuilds for details.


Should I record ID numbers in a table where I record who look whom profile page This is how I have designed:

visitorID
visitedprofileID
datetime

Is it a good idea to add an ID (primary key) to the table, I mean recordID (primary key).

(MySQL with PHP)

I think your two choices here are:

make the (visitorID,visitedprofileID) combination a primary key
add a recordID as primary key

If the table is InnoDB (or it will be in the future for Foreign Key usage), I would go with the second option. Given the way InnoDB handles indexing by adding the primary key to secondary indexes for lookup, if you ever wanted to add an index on datetime, the single field would result in a smaller index.


Installing Postgresql parray_gin extension on mac, include not found I am trying to install parray_gin in PostgreSQL 10 on macOS, but I can't get it to build.

I'm running pgxn install parray_gin. This first gave me a missing stdio.h error, so I installed the xcode command line tools, and now it's complaining about

/Library/PostgreSQL/10/include/postgresql/server/utils/pg_locale.h:19:10:
fatal error: 'unicode/ucol.h' file not found

After some Googling it appeared I needed to install an extra library, which I did through brew install icu4c

This printed:

This formula is keg-only, which means it was not symlinked into /usr/local,
because macOS provides libicucore.dylib (but nothing else).

If you need to have this software first in your PATH run:
    echo 'export PATH="/usr/local/opt/icu4c/bin:$PATH"' >> ~/.bash_profile
    echo 'export PATH="/usr/local/opt/icu4c/sbin:$PATH"' >> ~/.bash_profile

For compilers to find this software you may need to set:
    LDFLAGS:  -L/usr/local/opt/icu4c/lib
    CPPFLAGS: -I/usr/local/opt/icu4c/include

But it didn't change a thing and I have no idea how to pass /usr/local/opt/icu4c/include to pgxn

You need to manually download the sources and edit the Makefile

Add this:

PG_CPPFLAGS = "-I/usr/local/opt/icu4c/include"
SHLIB_LINK =  "-L/usr/local/opt/icu4c/lib"

and it works

https://github.com/theirix/parray_gin/issues/5


MySQL ROLL Up question I have a table of Orders from Customers in my database for a retail store (using My SQL) and I am trying to do a ROLL UP of all orders for the month of April and for the final row to have a title of 'Grand Total' next to this total sum figure:

SELECT coalesce (OrderID, 'GrandTotal') AS OrderID,
SUM(OrderPrice) as SumOrderPrice
FROM ORDERS
WHERE OrderDate BETWEEN '2018-04-01' AND '2018-04-30'
GROUP BY OrderPrice WITH ROLLUP;

The problem I am having is that when I run this code, next to the total 'Sum' figure, I am getting a duplicate OrderID number, not the text 'Grand Total'. Any ideas where I'm going wrong?
Many thanks

I'm not sure what it is you are trying to do, the GROUP BY clause that you provided is invalid. Is the following what you meant?

SELECT coalesce (OrderID, 'GrandTotal') AS OrderID,
       SUM(OrderPrice) as SumOrderPrice
FROM ORDERS
WHERE OrderDate BETWEEN '2018-04-01' AND '2018-04-30'
GROUP BY OrderID WITH ROLLUP;

I.e. GROUP BY OrderID instead of OrderPrice?


Is this inconsistent treatment of `void` return from sql and plpgsql functions documented? begin;
create table foo(id integer);
create function f1() returns void language sql as $$delete from foo;$$;
create function f2() returns void language plpgsql as $$begin delete from foo; end;$$;
select f1() is null, pg_typeof(f1()), f1()::text, f1()::text is null;
/*
 ?column? | pg_typeof | f1 | ?column?
----------+-----------+----+----------
 t        | void      |    | t            <----------------------the return is null
*/
select f2() is null, pg_typeof(f2()), f2()::text, f2()::text='';
/*
 ?column? | pg_typeof | f2 | ?column?
----------+-----------+----+----------
 f        | void      |    | t            <----------------------the return is not null
*/
rollback;

Is this a documented inconsistency?

SQLFiddle here

I don't believe it's documented. The behavior is incorrect or at best inconsistent but maintained probably because of a huge existing user code base that would break if it were corrected. The issue is caused by differences in how returns VOID is coded with SQL vs plpgsql. See: https://stackoverflow.com/questions/8319986/postgresql-functions-returning-void


How do I get PostgreSQL to keep inserting after unique index violation? I have ETL (Pentaho DI 8.2) to fill a Data Vault Hub table. Basically the ETL depends on the UNIQUE index to refuse duplicates of the business key column. In SQL Server this works great. With PostgreSQL I notice that it stops inserting after the first violation of the unique index. The ETL write is set to do individual inserts and ignore database errors. I add a rule to ignore the duplicates, but this slows down performance. Also I want to prevent this complication with an extra construct.

How do I get PostgreSQL to continue inserting valid rows into my hub tables?

My first suggestion would be to try avoiding duplicates in the first place: Pentaho has "Unique Rows" and "Unique Rows (hashset)" steps to help with that.
First option would work fast but is only good for pre-sorted input.

The table output step with error handling should be capable of proceeding correctly past the error. Could you check if you have any steps past the output? (E.g., "abort on error" would kill the transform). Posting a log would also help a lot to identify the issue.

UPDATE:
In short, due to PostgreSQL internals, the default behaviour on error within transaction is different to some other DBs. It requires a formal rollback or commit before any other commands could be issued. This behaviour made it's way into the JDBC driver used by Pentaho -- as expected.

SOLUTION:
Update the JDBC driver to the later version (at least 9.4.1210, but I would recommend version 42+). Then in the connection properties in Pentaho go to Advanced and create property "autosave" with value "always". This will effectively ROLLBACK any failing statements by automatically creating a savepoint after every successful statement. For more details see: https://jdbc.postgresql.org/documentation/head/connect.html

This comes at a minor performance hit, but likely not huge. You still have to keep the Commit size at "1" otherwise the whole batch will happily fail and you'll miss data.


Unable to get actual execution plan for SQL Server 2014 SP3 I was reading this blog post https://techcommunity.microsoft.com/t5/sql-server/using-xevents-to-capture-an-actual-execution-plan/ba-p/392136

and based on same created extended event

CREATE EVENT SESSION [PerfStats_Node] ON SERVER

ADD EVENT sqlserver.query_thread_profile(

ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text))

ADD TARGET package0.event_file(SET filename=N'C:\Temp\PerfStats_Node.xel',max_file_size=(50),max_rollover_files=(2))

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

However when reviewing the target file i see the plan captured seems estimated even though some actual number shows up in target events but when i pass plan handle in sys.dm_exec_query_plan, the plan which comes up seems to have all estimates

Blog clearly says it will open actual plan, then why i am not able to see one?

I tried using SSMS 17.9 as well but still no luck.

There's a lot of information in that blog post, and it looks like you've mixed up a couple of the sections.

The query_thread_profile XE available on SQL Server 2014 SP2 and higher puts the actual runtime metrics in the event output.  The cached plan is just a normal, cached (estimated) plan.

Pedro points out that you are responsible for the tedious task of mapping execution plan nodes (and their estimated rows) to runtime stats stored in the extended event output:

  And now comparing with the XE output, I can see the graphical plan and the actual and estimated rows that were read by that Index Scan:
  ...
  It can be an arduous task to do the above...

Later in the blog post, when discussing other XE available in newer versions (query_plan_profile and query_post_execution_plan_profile), Pedro mentions being able to get plans with a subset of the "Actual Plan" runtime metrics in them.  However, these aren't stored back in the plan cache - they are stored in the XE events.


Need help installing ODAC 122011x64 so I can connect ADW to Power BI So I have successfully installed Oracle DB 19c and connected it to Oracle SQL Developer. I am able to create tables and run queries. Furthermore, I can connect to ADW with both python and SQL Developer and any changes that I commit are seen in all locations. I am trying to connect the ADW database to Power BI. I am running all this on a personal Windows 10 Pro laptop with plenty of SSD & RAM.
In a new window of Power BI I click Get Data and then Oracle Database followed by Connect. When I do this I get a pop up that says:
The recommended provider ('Oracle.DataAccess.Client') is not installed. You can continue with your current provider, however it has been deprecated and may not work correctly.

I clock OK. Enter my Server as the TNS Name that I have used for my python and SQL Developer instances. In my ADW database, I have a table called TEST. It's a simple table with 1 column with the years 2015 through 2020 in it. Been using this to test to see if things are working. So I click on the DirectQuery and run the SQL statement SELECT * FROM TEST under the Advanced options menu and select OK. A sign in window pops up and I select the Database table and enter my correct ADW username and password. The same one that I can use to access this table from SQL Developer and then select Connect. When I do, I get this error:
Details: "The provider being used is deprecated: 'System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.'. Please visit https://go.microsoft.com/fwlink/p/?LinkID=272376 to install the official provider."

So I go to the website and download the ODAC122011_x64 file from Oracle and unzip it. When I double click the setup.exe file inside the directory I get a yes/no user control account menu and I select yes and then a command prompt window flashes very quickly and then nothing happens. I ran a super slow motion capture from my phone to capture what the command window showed and below is what I see:
Starting Oracle Universal Starter...
Checking monitor: must be configured to display at least 256 color Higher than 256 . Actual 4294967290   Passed
Preparing to launch Oracle Universal Installer from C:\Users\<my username>\AppData\Local\Temp\OraInstall2020 09:20_09-04 {???}
Please wait .._

It does nothing after this I have tried running the oui.exe file under the install subdirectory and get nothing and I have tried running the install.exe file in Oracle Universal Installer that I acquired when installing Oracle DB 19c and I get the following error when I target the install.exe file for installation:
OUI-10118:C/Users/{my username}/Downloads/ODAC122011_x64/Setup.exe is either a directory or a corrupt file. Specify a valid filename.

This is a file that I just directly downloaded from Oracle's website and unzipped to a directory with no spaces in the directory location. I have the latest version of Java installed (JDK 15).
I am not sure the proper work around here. Why can't I get my Power BI to connect to my Oracle ADW table using the methods described in this article. Those are the instructions that I used. It appears that I need this ODAC application but I don't understand why it won't install.
Can anyone help me resolve this issue? Thanks!
Edit: I am using 64-bit on each application.
Update: Still having issues. I checked the log and this is what I am seeing:
Using paramFile:
C:\Oracle\Oracle_PowerBI_ODAC\ODAC122011_x64\install\oraparam.ini

Checking monitor: must be configured to display at least 256 colors.    Actual 4294967296    Passed

The number of files bootstrapped for the jre is 0.

The number of files bootstrapped for the oui is 0.

I figured out the solution after reviewing this post. Apparently having over 2TB of free space on a hard drive causes this problem. You have to go into command prompt and create a temporary file to get your free space under 2TBs using something like this:
fsutil file createnew temp_1TB_file 1200000000000

Run the setup.exe again and then delete the temp file after.
Update: After doing all this I still was running into the same issues in Power BI. Then I read the troubleshooting notes from this page more closely and realized that I might have found the problem when it said:
If you downloaded Power BI Desktop from the Microsoft Store, you might be unable to connect to Oracle databases because of an Oracle driver issue.

I uninstalled the PowerBI Desktop that I had installed from Microsoft Store and installed it from the download from its website and tried connecting to Oracle ADW again through PowerBI and IT WORKED!!! So much troubleshooting but it finally works!


Is there a certification for Mariadb? Is there an official certification for Mariadb?
And if there is how can I find the next exam days?

I guess you can contact MariaDB to find out how/when/where you can take an exam. They state:

"This certification exam, proctored and administered by MariaDB"

on:

https://mariadb.com/resources/datasheets/certification-mariadb/

There is also preparation material to download there.


SQL Server Extended Events - How to filter statements by Table? I am curious, is it possible to filter the SQL Statement by table (table id or something)?

What I can think of, is to naively add another 'Statement' with Operator 'like' '%MyTable%'. Is that any 'Field' to filter the Table?

The sql_statement_completed event does not have an object_id or object_name field you can use to filter the events you are capturing.

It seems like you are trying to capture all the statements that change the data in one of your tables: you can capture that kind of information using the lock_acquired event and filtering for IX or X mode and the object_id you want to include.

Unfortunately, this will produce a large number of events, of which you probably want just one. You can overcome this problem by capturing the events in a histogram target or by streaming the events with the streaming API.

I blogged about something similar here: Tracking Table Usage and Identifying Unused Objects.

Of course, if you're on Enterprise Edition, you can always use an Audit for this purpose.

Hope this helps


Excluding particular words from a string on a WHERE clause I have a table that stores invoices and I'm trying to get stats out of it, unfortunately the table is poorly build and some crucial information is all mixed up on a nvarchar field, information such as whether the invoice has been cancelled or if part of the charge is exempt resides on this field in a very daliesque string that gets parsed by the frontend. 3453.234;exempt;Invoice Total...

So I want to create a query that would exclude a few words. My problem is how to accomplish a query that can exclude records depending of a list of keywords (cancelled, exempt), so if any of this words is in the field the amount would not be taken in account.

Its not too difficult to build a query that does it. First create a query that joins on a table using LIKE with wildcards. Then exclude everything from this query.

To see what I mean, see this simple example: http://sqlfiddle.com/#!6/619fb/2

Or alternately I have reproduced an example here:

--Create tables for comparing data
CREATE TABLE Invoice
(
  InvoiceID INT NOT NULL IDENTITY,
  InvoiceData varchar(200) NOT NULL,
);

CREATE TABLE BadWords
(
  BadWordID INT NOT NULL IDENTITY,
  BadWord varchar(10) NOT NULL
);

--Insert data
INSERT INTO Invoice (InvoiceData)
VALUES ('This is some invoice data'), ('it is about'), ('something interesting that'),
('you should look at'), ('because its got invoice information');

INSERT INTO BadWords (BadWord)
VALUES ('this'),('invoice');

--Test query:
SELECT *
FROM Invoice
WHERE InvoiceID NOT IN (
  SELECT InvoiceID
  FROM Invoice i
  INNER JOIN BadWords b ON i.InvoiceData LIKE '%' + b.BadWord + '%'
)

Performance may be an issue with this if you have hundreds of thousands of rows. Without more information though it would be difficult to give you a high performing query (for example using a NOT CONTAINS free text query may give better performance).


Snowflake: how to allow only Key Pair Authentication connections? Is there any way to configure Snowflake to allow only key pair authenticated users' connections?
At the moment you can add RSA_PUBLIC_KEY_FP to any user. But he can connect easily without specifying the key file.

I figured out the solution:

Create public/private key pair
Assign public key to a Snowflake user, e.g.
alter user testuser set rsa_public_key='ADNDDBgkqh...';
Set the user's password to null: alter user testuser set password = null;
For connection don't forget to specify properties: private_key_file and private_key_file_pwd


An inconsistency in SQL Server 2008 R2 (could be) possible duplicate with An inconsistency was detected

I have small table that returns from query

  UDA-SQL-0114 The cursor supplied to the operation "sqlOpenResult" is
  inactive.UDA-SQL-0564 [Microsoft SQL Server Native Client 10.0]An
  inconsistency was detected during an internal operation. Please
  contact technical support. (SQLSTATE=HY000, SQLERRORCODE=5243)

before any further steps on my side I want to ask if is there another way as is described in linked thread,
and in this case there isn't any issue to re_creating table with proper contents on fly

The error message looks like it is coming from a DB2 linked server. I would check to make sure the consistency error is not being passed back from DB2.