dbcc memorystatus - buffer pool target on VM higher than max memory I'm doing some digging into potential memory/configuration problems on a sql server 2012 SP2. I stumbled upon something that seems a bit strange in the performance counters as well as the dbcc memorystatus information.
here's the info from the buffer pool section of dbcc memorystatus:

If i'm reading things correctly the value is the number of 8 KB pages. The server which is a VM has 16 gb of memory total and SQL server is assigned a min/max value of 12GB.
The "Target" value of 32751616 gives me about 262GB which just so happens to be the total amount of memory in the VM host. If i check the "target pages" performance counter on the server i see the same number. I'm guessing this hints of the VM not being configured properly but what i'm really wondering is how the sql server will handle this. Will it impact performance / will the server try to commit more memory than is actually available?

Looking at the memory manager things seem to be looking more normal:

I've been searching the web for information about target buffer pool exceeding max memory but I haven't been able to come up with much so any input is much appreciated.

Edit. Adding a picture of the performance counter.

Value Buffer pool target is not in amount of 8 KB pages but the Is actually number of pages which you were correct to assume . So as per output the target buffer pool memory is would be 8*32751616 which comes to 249G. Which is really absurd

Reason:

Now for reason why Bpool target is showing value 250 G in DBCC MEMORYSTATUS and  memory manager is showing 12 G as Target committed is because the Counter SQL Server Buffer Manager: target Pages is DEPRECATED from SQL Server 2012 onwards. Microsoft did not specifically mentioned this anywhere but I found it from SQL Server Buffer Manager Object BOL Document. If you read this article you wont find counter target pages because its deprecated. You would also see the same counter present in SQL Server 2008 R2.

Other reason may be I assume your system is NUMA system with NUMA nodes. I am sure if you see MEMORYSTATUS output it would have memory allocation breakdown for various nodes like Node0, Node 1...Node 64. So target includes target memory allocation for all NUMA nodes. If you note Buffer pool in MEMORYSTATUS output, it has single entry and is not restricted to particular node. Plus this value is calculated during startup and does not actually shows correct value after a period of time so you should not refer to it. Instead refer to memory manager output

Edit:

As per question regarding buffer pool target pages on NUMA node believe me its not showing correct value for buffer pool. For my system with 4 G of RAM target pages are 7979008 with almost nothing running on the system. This is totally absurd and I would request you not to look at this counter. I had a chat with one of the experts on this field to confirm the behavior and he told me you should avoid looking at buffer pool output and target pages specially on NUMA system its calculation( way of calculation) is not correct. I can raise a connect Item but it would take few months to get response from MS team.

Not every thing in DBCC MEMORYSTATUS output matters to normal users many things are internal to MS and they would just not give any information regarding the same. If you want to monitor SQL Server memory usage please use below query.

select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

If you really want to monitor SQL Server target memory I would suggest you to look at

SQLServer:memory Manager--Target Server Memory: This is amount of memory SQL Server is trying to acquire.

SQLServer:memory Manager--Total Server memory This is current memory SQL Server has acquired.

There are other counters as well you can get from This Link.

Few other interesting counters are

Page reads/sec – Number of physical database page reads that are issued per second. This statistic displays the total number of physical page reads across all databases. Because physical I/O is expensive, you may be able to minimize the cost, either by using a larger data cache, intelligent indexes, and more efficient queries, or by changing the database design

Free List Stalls/sec – Number of requests per second that had to wait for a free page

PS: Please let me know if I have missed anything or if something is not clear

Edit: Here is the Connect Item for your issue lets wait what MS folks have to say about it. Please vote it


how to partition the mysql table I have a badly designed table and to top that number of columns is increasing (~500)because each time a new machine is discovered.(eg: router,adapter windows server etc)..it will store the output of commands which we after discovering that machine which is handled by back-end.

  For Example: We discover  a docker running in a machine. We will get the containers and image details....saving those as Blueprints and its properties in the table with appropriate columns;

table contains 1 millions records. We frequently do joins because this particular table is our main table. As to normalize the database now is not feasible, Can anybody give a workaround or any suggestions(like partition or sharding).
Thanxin advance ..Comment if you need more details

I think you should analyze the scenario from a different perspective, because it looks quite likely that (a) continuing to work with the current structure of the table in question might end up being (b) far less feasible than (c) redesigning it at all the corresponding tiers, i.e.:

the conceptual (modeling and defining [i] the types of things that the table represents and [ii] the respective rules of the business context in which said types of things are involved with [ii] the appropriate precision);
the logical (reflecting the types of things of interest in a database structure with new normalized elements and accurate data types, and setting up the proper constraints so that the data contained complies with the pertinent business rules); and
the physical (establishing a proper indexing strategy based on the exact data manipulation tendencies, installing more powerful hardware, perhaps partitioning and sharding, upgrading the network bandwidth, etc.) levels of abstraction.

Keeping the present table structure (~500 columns!) demands approaching it (manipulation- and administration-wise, etc.) in non-efficient ways, and if you expect that the volume of data will grow considerably then the system will scale very poorly and could become inoperable. It might be much better to employ (1) all the effort devoted to (2) handling this non-optimal situation in (3) a major reorganization.

Of course, there well may be current aspects of the table that can be reused later (e.g., the quite valuable data, which probably needs a clean-up), but I would decidedly consider rearranging—at least partially, at the major hot spots— all the factors mentioned above, instead of putting up workarounds that are in fact palliatives. Better late than never.


Online Learning: Different User Roles Different Fields I'm trying to build an online learning system ( similar to Udemy ), where users have different types ( or roles ).

For now "roles" are : students , instructors  and admins ,and it may increase in the future.Where each role has its own permissions.

A user can have 1 role or more, so
a user can be a student of some course and an instructor of ( another course )

see below:

the problem :
now, each role may have different fields ,for example:
an instructor role has a unique field like "biography" which no any of the other user roles has.

how can this be accomplished with this database design , I was thiking of creating tables like:

**instructors_users Table**
user_id
some unique fields for instructors role

**students_users Table**
user_id
some unique fields for students role

Is this right ?

Use Table Inheritance

If you don't mind nulls, use Single Table Inheritance. Add a "type" column, then add nullable columns for the different types, and check constraints for integrity

https://www.martinfowler.com/eaaCatalog/singleTableInheritance.html

if you do mind nulls, add one table per subtype and join to super type with a foreign key

https://www.martinfowler.com/eaaCatalog/classTableInheritance.html

ORMs like Hibernate can automate this for you


order by case with multiple order criteria I would like to know how to use multiple order columnes with case. For example case 1, order by FirstName, GivenName case 2 order by GivenName, FirstName.

I tried this, but it looks like the colon introduce an error

Order by case
     when 1 then FirstName, GivenName
     else GivenName, FirstName end

Case is supposed to return one value, not a tuple. Beside, you should compare 1 with something. If you want conditional ordering you can do that with two case statements:

ORDER BY CASE WHEN x = 1 THEN FirstName ELSE GivenName END
       , CASE WHEN x = 1 THEN GivenName ELSE FirstName END


When was data last inserted/updated/deleted in a table? I have found this solution, but this information is lost everytime the SQL Server service is restarted, or in other words, it is not persistent.

Is there another way I can find out when a table was last updated even if the server has been shut down after the fact?

If the data in the other solution is acceptable to you, then you could simply roll your own data collection to make it persist. For example, create a table to store object_id and last seek/scan/update. Every n minutes, take a snapshot of the DMV. Then check the max in the snapshot, and if it's greater than the max in the rollup, update the rollup. You can keep the snapshots as history or you can clean them up over time once you've coalesced.


SQL Server one-to-many and index fragmentation I'm currently writing code to update an entities child collection (one-to-many relationship), and while thinking about how to write code to determine which entities have been added/removed/modified, I realized that not only is it easier to just  recreate the whole list, but probably better for SQL server performance?

Let's say I have a table Student with a one-to-many relationship to Course, and I have there 2 courses Math and Physics, with primary keys 1 and 2 respectively, and obviously a foreign key to Student which is 1.

If I one day decide to update the first course, remove the other, and add a new one, I would end up with the following entities

Math (upated), primary key is: 1
Physics (deleted), primary key was: 2
History (added), primary key is: 5000

Now the 2 courses are no longer next to each other, and this probably causes big performance problems in the long run since my keys are now fragmented, is this correct?

A related question is whether I should have a primary key on the Course table at all? I usually add it by default to all of my (except association) tables, but in this case I never query courses individually, but always in the context of a student. Does it make sense to keep the primary key, even if I know I'll never reference it from anywhere? Would the lack of a primary key hurt performance?

EDIT: I've decided to keep the primary key, in-case in the future I do need to query this child table without the parent.

My primary keys are integers and have a clustered index (default on SQL Server) and I'm using SQL Server 2016, and Entity Framework 6 as my ORM.

UPDATE:

Here's my CREATE TABLE from SSMS. I'm using EF Code-First so I didn't write any of this by hand.

CREATE TABLE [dbo].[Courses](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Description] [nvarchar](max) NOT NULL,
    [StudentId] [uniqueidentifier] NOT NULL,
    [Fk1Id] [int] NOT NULL,
    [Fk2Id] [int] NOT NULL,
    [Fk3Id] [int] NULL,
 CONSTRAINT [PK_dbo.Courses] PRIMARY KEY CLUSTERED
 (
     [Id] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Courses]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Courses_dbo.Students_StudentId] FOREIGN KEY([StudentId])
REFERENCES [dbo].[Students] ([Id])
GO

ALTER TABLE [dbo].[Courses] CHECK CONSTRAINT [FK_dbo.Courses_dbo.Students_StudentId]
GO
--Omitted other alter tables for Fk1Id,Fk2Id, and Fk3Id

StudentId is the foreign key that I will always use to fetch these rows, they are not relevant in any other context (at least for now).

I have renamed the table and column names for privacy reasons, and removed 3 foreign key columns for simplicity! I hope they're not too relevant.

A related question is whether I should have a primary key on the
  Course table at all? I usually add it by default to all of my (except
  association) tables, but in this case I never query courses
  individually, but always in the context of a student. Does it make
  sense to keep the primary key, even if I know I'll never reference it
  from anywhere? Would the lack of a primary index hurt performance?

Every table should have Primary Key/Clustered Index.Since ID is seldom use in join condition.You can drop Clustered index from ID column.

Make Studentid +courseID in Course table as Clustered index.

In Future or Present, Studentid will be most frequently use in join condition and in all important query.On ther hand Course ID column will be very less use and not so important query.

I think [StudentId] [uniqueidentifier]  should have been INT or Bigint.

  Now the 2 courses are no longer next to each other, and this probably
  causes big performance problems in the long run since my keys are now
  fragmented, is this correct?

You can rebuild index.It is very normal in database.So when index is rebuild then id=1 and next id=5000 will be very much together.

First of all you can reconsider [Description] [nvarchar](max) NOT NULL

If it is possible to make it VARchar (2000),(3000) etc.

Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages.

Is manually keeping the Id's together bad practice?

Just because there is gap in ids that doesn't mean it is Index Fragmentation or it will hurt performance in any way.Gap in id is ok.Even if ids are continuous there will be index fragmentation.

Index Fragmentation


Index-Only versus Covering Index What’s the difference between an index-only query and a covering index one (in PostgreSQL 11)?

Both seem to fit https://use-the-index-luke.com/sql/clustering/index-only-scan-covering-index, based on my understanding.

An Index Only Scan (not "query") is a step in an execution plan that retrieves data only from an index and does not require a lookup to the actual table data.

This optimization can only be used if there is an index that contains all columns that are needed by the query. If such an index exists, that index is called a "covering index" because it "covers" all needed columns.

Or to put it the other way round: only if there is a covering index, the query optimizer can use an Index Only Scan.


Finding data recently created/updated in a whole PostgreSQL database I have inherited a web application running on a PostgreSQL database, which I don't know well, and there is little documentation for the code and the database...

So I try to understand things like "which data is created/updated in the database when I perform this action in the web interface ?".

I know I could just look at the code of the application, but it's quite confuse and opaque.

I also know I can use track_commit_timestamp=on to find the last commit on a given table (see https://dba.stackexchange.com/a/168752/144567).

But what I would like is find the last changed data in the whole database (there is more than 900 tables), is it possible ? Thanks.

Postgres version is 9.6.

Rather than trying to look at the data that changed, I would start by looking at the commands that changed it.  Set log_statement = mod in the postgresql.conf.


Import datadump in schema mode why am I encountering this error when executing impdp command?

My command looks like this:

impdp directory=dp_dir dumpfile=testdb.dmp logfile=testdb.log

Error message:

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

One way to find the cause of ORA errors is to use oerr tool by Oracle. In the error stack the top most is the application error which is followed by other secondary errors.

For example, the ORA-39002 and ORA-39070 are Datapump errors. Lets see what the oerr tool says for ORA-39070.

  [oracle@localhost ~]$ oerr ORA 39070
  39070, 00000, "Unable to open the log file."
  Cause:  Errors were detecting while opening the log file.  Subsequent
           messages will detail the problems.
  Action: Fix the problems outlined in the secondary messages.

The action is to fix the problem outlined by the secondary error message that is ORA-29283.

  [oracle@localhost ~]$ oerr ORA 29283
  29283, 00000, "invalid file operation"
  Cause:  An attempt was made to read from a file or directory that does
           not exist, or file or directory access was denied by the
           operating system.
  Action: Verify file and directory access privileges on the file system,
            and if reading, verify that the file exists.

And that's it.


Change owner of relation in PostgreSQL database I just started using PostgreSQL and have problems in changing the ownwer of a table. I have a database postgis_in_action and a user postgis_in_action. As I worked as postgres user and as well as postgis_in_action user my tables ch01.highways and ch01.highways_staginghave different owners postgres and postgis_in_action. They are in the same schema ch01. I checked the following command in psql as postgres user in my database postgis_in_action:

ALTER TABLE ch01.highways_staging OWNER TO postgis_in_action

Do I have to add or modify something if I want to change the owner for ch01.highways_staging to postgis_in_action?

In psql I don't get any error message and the result of the command is that the ownwer remains postgres.

I'm logged in to my database with user postgres because I wanted to have the priviledge to change the owner of a table/relation.

Community wiki answer:

A generic reason that affects those new to psql is forgetting the semicolon at the end of the command, so that it doesn't get executed. Otherwise your command looks good and should do what you want.


Sql Server pivot string results into columns I am trying to transpose a dataset of string values, into columns, depending on the value of an integer
example:
create table imgText (
rowindex int,
imgName varchar(50)
)

insert into imgText (rowindex,imgName) values (0,'dog')
insert into imgText (rowindex,imgName) values (1,'plant')
insert into imgText (rowindex,imgName) values (0,'cat')
insert into imgText (rowindex,imgName) values (1,'tree')
insert into imgText (rowindex,imgName) values (0,'mouse')

My desired output would look like this

index0
index1

dog
plant

cat
tree

mouse
null

I tried doing a pivot, but a pivot requires a function (sum, max, average etc)
e.g.
WITH pivot_data AS
(
select rowindex,imgName from imgText
)
select [index0],[index1]
from pivot_data
PIVOT ( max(imgName) for rowindex IN ([index0],[index1]) )  as p;

but my error is
Error converting data type nvarchar to int.

I assume because I am trying to aggregate a varchar
note: rowindex can ONLY be 0 and 1

You need to add another column so that the aggregate function you have for the pivot operation has something to bite into.  Try the below.
DROP TABLE IF EXISTS #imgText

create table #imgText (
rowindex int,
imgName varchar(50)
)

insert into #imgText (rowindex,imgName) values (0,'dog')
insert into #imgText (rowindex,imgName) values (1,'plant')
insert into #imgText (rowindex,imgName) values (0,'cat')
insert into #imgText (rowindex,imgName) values (1,'tree')
insert into #imgText (rowindex,imgName) values (0,'mouse')

;WITH CTE_Raw AS
    (
    SELECT rowindex
        , imgName
        , GRP = ROW_NUMBER() OVER (PARTITION BY rowindex ORDER BY rowindex)
    FROM #imgText
    )
SELECT [0] AS index0
    , [1] AS index1
FROM CTE_Raw AS T
    PIVOT (MAX(imgName) FOR rowindex IN ([0], [1])) AS pvt


Why my Postgres 9.3 database is logging crazily autovacuum alerts The latest days, Postgres has started to log very frequently (about 100 logs per second!!!!) this kind of traces:

autovacuum: found orphan temp table "pg_temp_5"."p14528" in database "ma_prc_lim_absa_z5"

Is this a real problem?
If it isn't, how can I filter them?

It's a temporary table that wasn't removed automatically when it should have.

The fix from the point of view of a DBA is to simply drop the table manually.

There are several references to this problem on mailing lists. See for instance
[bug fix] Suppress "autovacuum: found orphan temp table" message
for a discussion on this subject.


Restoring multiple files to multiple database with pg_restore I have multiple dump files and want to restore them with single command. something like this

pg_restore  -h localhost -p 5432 -U postgres
    -d gl_election gl_world gl_tourism
    -c "gl_election" "gl_world" "gl_tourism"

This command doesn't work and I can't find how to do it correctly. It works fine for one file.

pg_restore doesn't take multiple filenames, nor multiple databases (-d) end of story. So use a simple shell loop. I assume you're trying to

Clear out the old database
Load up a dump to the database with the same name

That loop will look like this

for dump in gl_election gl_world gl_tourism; do
  echo Clearing and reloading "$dump"
  pg_restore -h localhost -p 5432 -U postgres -d "$dump" -c "$dump"
done;


How can I restore ##MS_SSISServerCleanupJobLogin##? At some point the SSIS cleanup job login (##MS_SSISServerCleanupJobLogin##) got deleted from my sql server (2012). I'm trying to upgrade to SP2 and I'm getting an error message I believe is related to this user being missing from the server.

I still have the appropriate User in the SSIS database, but I need to re-create the login. However, I can't find what settings (passwords etc) to use.

The only thing I could find about restoring this user was here, but it doesn't give enough detail.

  6.1. Create login [##MS_SSISServerCleanupJobLogin##] using CREATE LOGIN TSQL statement. This login is for internal use in SSISDB maintenance.

  6.2. Map SSISDB user ##MS_SSISServerCleanupJobUser## to server login ##MS_SSISServerCleanupJobLogin##

 USE SSISDB
 ALTER USER [##MS_SSISServerCleanupJobUser##] with LOGIN = [##MS_SSISServerCleanupJobLogin##]

How can I recreate this login properly?

I decided to experiment with creating the user with a random password, and everything worked.

CREATE LOGIN [##MS_SSISServerCleanupJobLogin##] WITH PASSWORD=N'blahblahblah', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE SSISDB
GO
ALTER USER [##MS_SSISServerCleanupJobUser##] with LOGIN = [##MS_SSISServerCleanupJobLogin##]


Merge Replication identity field issues One of our clients is using our software with merge replication of a database on a SQL Server 2008 R2 machine. There are two production environments in separate geographical locations only one of which is live at any one time, so basically one and live one on standby. Only the live database is updated by teh applications. Every couple of months they failover between the datacentres and the standby environment become the live centre. There is an instance of SQL Server 2008 in each datacentre and merge replication is used to keep them in sync. This was all working ok until the beginning of the year when we started getting replication errors with some lTID columns in various tables that have the Identity property set.

The errors were like this one:

  The insert failed. It conflicted with an identity range check constraint in database 'GateMain', replicated table 'dbo.tGateCalcsLog', column 'lTID'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent.

Then after the last failover we noticed we had an issue with the lTID values in one specific table. Our application relies on the lTID value always having incremented in order such that the highest lTID value is always the newest entry in the table. We've found that due to how the identity ranges are being managed by replication that when the system is failed over that the lTID range of the now live database server may have a range of values that are lower than those already present in the table. Is there a way to manage this in merge replication so we can guarantee that the next identity value allocated to the lTID column in greater than any lTID currently in the table? Or do we need to use a different type of replication or possibly mirroring?

You could turn off automatic identity management and allocate your ranges manually.
Another option could be forcing a range re-allocation by issuing sp_msrefresh_publisher_idrange and specifying the range boundaries.

However, both options would not guarantee that the lTID column is populated with ever-increasing values: you will always have at least one range for the publisher and one range for the subscriber and if users are inserting at both sites you can't have ever-increasing IDs.

This is by design and there's no way around it.

If your goal is keeping the identity values generated at the subscribers ever-increasing, that's a whole different story and it can be achieved with one of the above options.


Error while installing Oracle 11g in Ubuntu 18.04 Also getting a runfixup.sh script which is also giving error

root@szh-server:/tmp/CVU_11.2.0.1.0_zuman# ./runfixup.sh
Response file being used is :./fixup.response
Enable file being used is :./fixup.enable
Log file location: ./orarun.log
./orarun.sh: 186: [: true: unexpected operator
./orarun.sh: 848: [: unexpected operator
./orarun.sh: 864: [: unexpected operator
./orarun.sh: 882: [: unexpected operator
./orarun.sh: 903: [: true: unexpected operator
./orarun.sh: 1052: [: unexpected operator
./orarun.sh: 1057: [: unexpected operator
./orarun.sh: 1075: [: unexpected operator
./orarun.sh: 1085: [: unexpected operator
./orarun.sh: 1115: [: unexpected operator
./orarun.sh: 1143: [: unexpected operator
./orarun.sh: 1189: [: unexpected operator
./orarun.sh: 139: [: unexpected operator
./orarun.sh: 139: [: unexpected operator
./orarun.sh: 1228: [: unexpected operator
./orarun.sh: 1284: [: unexpected operator
./orarun.sh: 1342: [: unexpected operator
./orarun.sh: 1426: [: unexpected operator
./orarun.sh: 1451: [: unexpected operator

I'm getting the error from the script which is supposed to fix the installation errors. -_-

Any solution for this?

Looking at the Oracle provided fixup scripts, I got the answer. The main script is orarun.sh which runs on bash.
So the following command does the trick!
PS: Remember, you need to be login as root.

bash ./orarun.sh fixup.response fixup.enable .


How to use Unicode chars on PHPMyAdmin? On a WordPress site I want to replace " !" and " ?" by " !" and " ?". The challenge is that the replacement string not uses a common space char but a thin space char (U+2009).

PHPMyAdmin is used for database editing with this query:

UPDATE wp_posts SET post_content = REPLACE (post_content,' !',' !');

(Yes, it's the correct thin space char, but Courier is a fixed-width font not showing a difference.)

It seems that PHPMyAdmin is not correctly handling the special char, because simulating the query delivers zero hits.

But trying another replace query works, so the statement should be correct. Tables are using "utf8mb4_unicode_ci" as collation, which seems to be the default and correct setting of WordPress. Database server is MariaDB (10.1.26).

I'm not familiar enough how to specify Unicode chars in SQL queries. Can someone help me out there?

Your UPDATE statement looks correct.  Here's "proof":

mysql> SELECT HEX(REPLACE( " !", ' !',' !'));
+----------------------------------+
| HEX(REPLACE( " !", ' !',' !'))   |
+----------------------------------+
| E2808921                         |
+----------------------------------+

E28089 is the hex for the utf8 encoding for "THIN SPACE" (Unicode codepoint 8201=x2009).

And I suggest you do want utf8, not unicode.

You can nest the replaces:

UPDATE wp_posts SET post_content =
        REPLACE(
        REPLACE(post_content,' !',' !'),
                             ' ?',' ?');

Or, for something wrong with the client (phpmyadmin),

UPDATE wp_posts SET post_content =
        REPLACE(
        REPLACE(post_content,' !', UNHEX('E2808921')),
                             ' ?', UNHEX('E280893F'));

This assumes that post_content is declared CHARACTER SET utf8 or utf8mb4.


SQL Server Service does not start automatically after system restart I have a SQL Server system that reboots weekly (after installing Windows Updates).

The last three weeks I have seen that the SQL Server service does not start when the server comes back up (Configufation Manager shows the service as stopped) the System Event log shows the following errors:

Event ID 7000

  The SQL Server (MSSQLSERVER) service failed to start due to the
  following error:  The service did not respond to the start or control
  request in a timely fashion.

Event ID 7001

  The SQL Server Agent (MSSQLSERVER) service depends on the SQL Server
  (MSSQLSERVER) service which failed to start because of the following
  error:  The service did not respond to the start or control request in
  a timely fashion.

Event 7009

  The SQL Server (MSSQLSERVER) service failed to start due to the
  following error:  The service did not respond to the start or control
  request in a timely fashion.

However, I can start the service manually and it starts up fine.

The service is set to automatically start.

The SQL Server error log does not include any clues either, unfortunately.

One issue that can cause this is that some systems, particularly VMs with the system volume on solid state storage, is that it boots up so fast and gets to the point of starting services before some of the networking has been fully initialized.  This, then, results in a failure of the service account authentication, and the service start fails accordingly.

To address this, set the SQL Server services to Automatic (Delayed Start).  This will put a 2 minute delay on starting them.  If 2 minutes is too much or not enough, you can tweak it by changing HKLM\SYSTEM\CurrentControlSet\Control\AutoStartDelay, or you could create a Task Scheduler job that runs on Windows startup and continually attempts to start the services until they are started.


Index Rebuild Operation I'm planning to do an index rebuild operation for over 30+ indexes. Some index rebuild require almost >30min because the Table Record is around 60,000,000. Command used for this table is

ALTER INDEX [T_MAIL_RCVLIST_IDX1] ON [dbo].[T_MAIL_RCVLIST] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

However, I would have to run this command for every single index, which is a big hassle and I decided to use a cursor and run it at one go. But I'm worried there could be an unexpected issue and my boss also warned me of this and suggested I run each index build individually. Could there be an issue? If yes, what kind and how would I fix this?

Here is the cursor index build query.

DECLARE @i int, @sql varchar(1000)
DECLARE @tablename varchar(1000),@ownerName  varchar(1000)

SET @i = 1

DECLARE DB_Cursor CURSOR FOR
 SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_SCHEMA, TABLE_NAME
OPEN DB_Cursor

FETCH NEXT FROM DB_Cursor
INTO @ownerName, @tablename
WHILE @@FETCH_STATUS = 0
BEGIN

 SET @sql = 'ALTER INDEX ALL ON ' + @ownerName + '.' + @tablename + ' REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)'
 EXEC (@sql)

 PRINT CONVERT(VARCHAR, @i) + '__' + @ownerName + '.' + @tablename + '............ OK'
 SET @i = @i + 1

 FETCH NEXT FROM DB_Cursor
 INTO @ownerName, @tablename

END

CLOSE DB_Cursor
DEALLOCATE DB_Cursor;

Ola Hallengren has already done the work for you here, no need to roll your own code.  You can even cherry pick specific indexes or tables.

https://ola.hallengren.com/

I'd also recommend going ONLINE with those rebuilds if you're on Enterprise Edition, that'll save you locking of the tables for writes.


debugging an ssis package which is invoked from a .net application? can we attach a debugger to an SSIS package which is invoked to execute from a .net assembly?

Your SSIS package would be stored as a .dtsx file, or perhaps in the MSDB database. Either way, you should be able to locate it, open it in a copy of BIDS that you run as the user that executes the application, configure BIDS to have the same set of conditions as how you call the package, and then debug it from there.


Is a temporary table a good solution for importing temporary data into a database i am facing the following scenario. I have a student database in which exam results are stored. Every now and then i get a list with the names of certain students for which i need to find out how well they performed in an exam. After i have finished with my queries i don't need the temporary student data anymore. I am trying to think of an elegant way of how to store this temporary student data in my database.  The only thing i found out  that in my opinion could be a solution to my problem is a temporary table, however i am not sure. My tables have the following structure.

Student
id(PK), matriculation_number, first_name, last_name, course_of_study, email

Exam
id(PK), student_id(FK), grade

Temporary student list
matriculation_number, first_name, last_name

All in all what i am trying to achieve here is import the temporary student list into a table, then compare it with the student table, using the matriculation number, in order to get the id of the student and finally use the student id to access all the  exam results for that particular student.

What do you think is a temporary table a good solution for the above problem? If not how would you go about it?

I am using MySQL as RDBMS.

I see 2 ways to do this, your temporary table idea or running them through one at a time.  Which way to go depends on how many records we are talking about.  If its 3 or 4 names at a time then running them individually may be faster but if we are talking larger than 20 or so a temp table would be the way to go.

Overall the best way depends on your requirements.  I use temporary tables for things like this all the time, they are quick and easy and definitely not a "Wrong" choice.

My personal choice would be to write a program to have some one else do it but if that is not an option we see where we go.


Create a table based on a table depending on the values in the initial table column I have a table like:

+----+--------+----------------+
| id |  name  |    address     |
+----+--------+----------------+
|  1 | Ivan   | Moscow,Moscow  |
|  2 | Petr   | Samara,Samara  |
|  3 | Grigor | Moscow,Korolev |
+----+--------+----------------+

How can I create a new table (or view) from this table, so that if the 'address' column contains the different values on the sides of the separator, insert two rows into the new table with all other data using Oracle PL/SQL? For this example the result should be:

+----+--------+---------+
| id |  name  | address |
+----+--------+---------+
|  1 | Ivan   | Moscow  |
|  2 | Petr   | Samara  |
|  3 | Grigor | Moscow  |
|  3 | Grigor | Korolev |
+----+--------+---------+

Well this may solve your problem.

SQL> desc tbl1
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                         NUMBER
 NAME                           VARCHAR2(20)
 ADDRESS                        VARCHAR2(20)

SQL> select * from tbl1;

    ID NAME         ADDRESS
---------- -------------------- --------------------
     1 Ivan         Moscow,Moscow
     2 Peter        Samara,Samara
     3 Grigor       Moscow,Korolev
     4 John         Moscow,Moscow

SQL> create or replace view vtest as
select id, name, substr(address,1,instr(address,',')-1) address from tbl1
union
select id, name, substr(address,instr(address,',')+1, length(address)) address from tbl1;

View created.

SQL> select * from vtest;

    ID NAME         ADDRESS
---------- -------------------- --------------------
     1 Ivan         Moscow
     2 Peter        Samara
     3 Grigor       Korolev
     3 Grigor       Moscow
     4 John         Moscow

SQL>


postgres - c  =  not working for postgres 11 in docker I'm running a Postgres inside a docker. I want to change the default config of Postgres so I'm running :

docker container run -d postgres -c max_connections=200 -c shared_buffers = 1GB -c effective_cache_size=3GB -c maintenance_work_mem=256MB -c checkpoint_completion_target=0.7 -c wal_buffers=16MB

But when I'm connecting to Postgres running:

 docker exec -it container_name psql

And then the result of :

SHOW max_connections;

is

 max_connections
-----------------
 100
(1 row)

And it's not just max_connections. None of the parameters are changed. And I don't know what is the problem with what I'm doing?

Update: I already have a running docker container and I want it to apply these parameters to it without needing to restart it.

You cannot put spaces around the equals sign when using the "-c" option.  So you need something like postgres -c max_connections=200

When I try it with the spaces, it refused to start at all.  So I don't know what you are connecting to in order to SHOW max_connections;.  Maybe you already had a server running before you tried to start with the invalid syntax?


Periodically update a table with new records from another server I have databases on two different servers. I need to regularly retrieve new records from a table on server A, and process them in order to use them to update a table on server B (with a different schema).

I was going to use a trigger for this, but if this fails, the inserts on server A are rolled back. The inserts on table A must not fail, so the update of server B needs to be as decoupled from this as possible.

I am now thinking of using a scheduled stored procedure on server B to retrieve the results from server A and update server B. This would need to run every 30 seconds.

Is there anything wrong with this approach, or is there a better or more 'correct' way of achieving this?

Create a linked server connection with read access from Server B to Server A.  Have a process running on Server B that does the following:

Examine the table on ServerB to identity the last inserted record from Server A.  Hopefully you have an auto-incrementing primary key (int).  If not, look for the greatest "last updated timestamp".
Connect to Server A and retrieve all records from Server A that are greater than this primary key / timestamp
Insert these new records into Server B.

I always recommend full error trapping and process logging in any sort of automated system like this.  You need to know how it is behaving, and you need to be alerted if there are any problems.

I would put all the logic in a stored procedure, connect from A to B using a linked server, and have the procedure sleep for X seconds after it is finished processing.

Put the stored procedure in a SQL Server job that is set to run continuously.  It will appear to always be running, but the stored procedure will be sleeping most of the time.

Note: The reason I create jobs to run continuously and then having the procedure sleep (using a wait statement) is to prevent clutter in the job history logs.  I actually create the job to run on a schedule (every minute) but because the stored procedure sleeps, the job never finishes.  You can chalk it up to personal preference.


How to swap postgresql rows physical locations? I'm looking for a way to reorganize my database tables rows incrementally based on their clustered index.

I would like to avoid using the CLUSTER command since it requires an ACCESS EXCLUSIVE lock on tables being clustered, making large tables unavailable for a considerable amount of time.

The pg_repack extension seems like a better approach, but it's also an "all or nothing" operation, which could take up to several hours to complete for large tables, risking to affect replication performance.

That being said, I came up with the idea of sorting table's rows in place, swapping their physical location in incremental steps, to gradually improve table's rows physical organization. Is this possible/safe with plain SQL? (ex: issuing updates based on the ctid column)

Thanks in advance.

That being said, I came up with the idea of sorting table's rows in place, swapping their physical location in incremental steps, to gradually improve table's rows physical organization. Is this possible/safe with plain SQL? (ex: issuing updates based on the ctid column)

You can't swap CTIDs in plain sql. You'll get an error.

ERROR:  cannot assign to system column "ctid"

Further, you can't modify a row at all without totally rewriting the row. It's how MVCC works.


Two Triggers affect the same row I've have triggers on two separate tables that are designed to affect a third table, the problem is each trigger affects different rows.

The third table, C, currently looking like this:

00000010 | R00000020 | R00000030 | R00000040 | R00000050 | R00000060
---------- ------------ ---------- ----------- ----------- ----------
R000000   |     I     |   0002    |          |           |
          |           |           |     2    |     2     |    0025

I want something like this:

00000010 | R00000020 | R00000030 | R00000040 | R00000050 | R00000060
---------- ------------ ---------- ----------- ----------- ----------
R000000   |     I     |   0002    |     2    |     2     |    0025
          |           |

I could use a view to simply combine the output of the two source tables, however I'd rather use an actual table in this case.

Regarding your question about how to have triggers from two independent tables (A, and B) insert rows into a third table (C), you can certainly do that.  Presumably both A and B have a column that identifies the specific row in C that needs to be updated.

To prove this, I've created the two source tables A and B, with a trigger on each table, that inserts into the C table.

CREATE TABLE dbo.A
(
    RowID INT NOT NULL
        CONSTRAINT PK_A
        PRIMARY KEY CLUSTERED
    , SomeDataA VARCHAR(386) NOT NULL
);

CREATE TABLE dbo.B
(
    RowID INT NOT NULL
        CONSTRAINT PK_B
        PRIMARY KEY CLUSTERED
    , SomeDataB VARCHAR(386) NOT NULL
);

CREATE TABLE dbo.C
(
    RowID INT NOT NULL
        CONSTRAINT PK_C
        PRIMARY KEY CLUSTERED
    , SomeDataA VARCHAR(386) NULL
    , SomeDataB VARCHAR(386) NULL
);
GO

CREATE TRIGGER dbo.A_Update_C
ON dbo.A
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @t TABLE
    (
        RowID INT NOT NULL
    );
    UPDATE dbo.C
    SET SomeDataA = i.SomeDataA
    OUTPUT i.RowID INTO @t (RowID)
    FROM inserted i
    INNER JOIN dbo.C c ON i.RowID = c.RowID;

    INSERT INTO dbo.C (RowID, SomeDataA)
    SELECT i.RowID
        , i.SomeDataA
    FROM inserted i
    WHERE NOT EXISTS (
        SELECT 1
        FROM @t t
        WHERE t.RowID = i.RowID
        );
END
GO

CREATE TRIGGER dbo.B_Update_C
ON dbo.B
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @t TABLE
    (
        RowID INT NOT NULL
    );
    UPDATE dbo.C
    SET SomeDataB = i.SomeDataB
    OUTPUT i.RowID INTO @t (RowID)
    FROM inserted i
    INNER JOIN dbo.C c ON i.RowID = c.RowID;

    INSERT INTO dbo.C (RowID, SomeDataB)
    SELECT i.RowID
        , i.SomeDataB
    FROM inserted i
    WHERE NOT EXISTS (
        SELECT 1
        FROM @t t
        WHERE t.RowID = i.RowID
        );
END
GO

Here we insert test data into A and B, then show the rows in C:

INSERT INTO dbo.A (RowID, SomeDataA)
VALUES (1, 'x');

INSERT INTO dbo.B (RowID, SomeDataB)
VALUES (1, 'y');

SELECT *
FROM dbo.C;

The rows in C are:

The code relies on the RowID value being identical in both A and B; without some commonality, it is impossible to know which rows in C need to be updated.

The triggers above only inserts new rows into C if they don't already exist by using the OUTPUT clause to insert the RowID values into a temporary table which is subsequently used to prevent those rows from being inserted into the C table.  Only rows that don't already exist in the @t table variable get inserted into C.

If there is a high degree of concurrent activity against A and B, with different processes potentially updating the same rows in those tables, it may be possible to attempt to insert duplicate rows into C.  This would fail, causing the entire transaction to fail, rolling back the changes made by both the trigger in C, as well as the changes made to either A or B.  The code above does not account for this edge-case, and in fact has no error checking at all.

The below answer and code is regarding the first "version" of your question, which was "can multiple triggers write to the same row".

You can very easily test that using:

USE tempdb;

CREATE TABLE dbo.TriggerTest
(
    TriggerTest_ID INT NOT NULL
        CONSTRAINT PK_TriggerTest
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , SomeData VARCHAR(255)
);
GO
CREATE TRIGGER dbo.TriggerTest1 ON dbo.TriggerTest
FOR INSERT
AS
BEGIN
    UPDATE dbo.TriggerTest
    SET SomeData = COALESCE(tt.SomeData, '') + 'TriggerTest1'
    FROM dbo.TriggerTest tt
        INNER JOIN inserted i ON tt.TriggerTest_ID = i.TriggerTest_ID;
END
GO
CREATE TRIGGER dbo.TriggerTest2 ON dbo.TriggerTest
FOR INSERT
AS
BEGIN
    UPDATE dbo.TriggerTest
    SET SomeData = COALESCE(tt.SomeData, '') + 'TriggerTest2'
    FROM dbo.TriggerTest tt
        INNER JOIN inserted i ON tt.TriggerTest_ID = i.TriggerTest_ID;
END
GO
TRUNCATE TABLE dbo.TriggerTest;
INSERT INTO dbo.TriggerTest DEFAULT VALUES;
SELECT *
FROM dbo.TriggerTest;

If you run this, you'll see the following results:

This clearly proves that two separate FOR INSERT triggers can, and in fact do, update rows independently of each other.  If both triggers are coded to affect the inserted rows, then that is what they will do.

Unless you specify the trigger order using the sys.sp_settriggerorder stored procedure, it is not guaranteed that the trigger created first will fire first.  If the logic contained in the these triggers depends on a certain logical firing order, you must ensure you specify the order.  For instance:

--make TriggerTest2 run first:
EXEC sys.sp_settriggerorder
    @triggername = 'dbo.TriggerTest2'
    , @order = 'First'
    , @stmttype = 'INSERT';

--set TriggerTest1 to run in any order.  The @order parameter could be
--  changed to 'Last' to ensure it runs after all other triggers on
--  that table
EXEC sys.sp_settriggerorder
    @triggername = 'dbo.TriggerTest1'
    , @order = 'None'
    , @stmttype = 'INSERT';

Also of note, you should realize triggers fire once for any given INSERT statement.  Take for instance:

TRUNCATE TABLE dbo.TriggerTest;

INSERT INTO dbo.TriggerTest (SomeData)
SELECT o.name
FROM sys.objects o;

SELECT *
FROM dbo.TriggerTest;

In my tempdb, this created 101 rows, a sample of which is:

I coded my triggers to properly handle this by using a set-based approach to updating the underlying table with a JOIN to the special table, inserted, which is a system-created virtual table containing the rows to be inserted into the table.


Car Dealarship Database Design I have to design the following sentences for a company that sells different vehicles:
"The database must keep track of everyone involved(customer and staff) in the sales for vehicles. The sale price, profit and commission from that profit also needs to be recorded."
I came across many ER diagrams to model such a scenario which involves a ternary relationship between Vehicles, Staff and Customer as can be seen  here.(ER1)
And another one where the Sales in actually an Entity? like this.(ER2)
Initially, I was drawing my diagram like this(3) but as it says, the diagram is not accurate and I'm not sure why.
What exactly is the difference between the concept that these two diagrams(ER1 and ER2) explain? It would be a lot of help if someone could explain the difference between the two models and which one is better suited for what I want to record in the database.
If any clarification is needed please let me know.

the first diagram you can't realized in a rdms, you need to make sales always to an entity, to represent the relation ship.
if we take the third diagram, you would have all the wanted data, but you couldn't make foreign keys work, because of the ring relationship.( every row that is referenced mus exist before, this doesn't work in a ring relationship).
now you have two possibilities:

you have  to represent the trinary relationship in an entity, and you wouldn't have the eer 1 any more, as sales is an entity with foreign keys to all three tables(entities)
you put the relationship in your application code

the first gets you your second approach.
The second doesn't show the relationship with foreign keys, so you would add the information by adding comments to the tables.


How can I get my server's uptime? I am working with postgresql-9.4 and for support and monitoring tasks I need to know how long my server has been up for? Is there any function or way to know this? Is there an sql way to know when my server started?

I really need to know the elapsed time since it started?

Use the built-in function pg_postmaster_start_time

select current_timestamp - pg_postmaster_start_time() as uptime

More details in the manual: http://www.postgresql.org/docs/current/static/functions-info.html

If you want seconds, use extract:

select extract(epoch from current_timestamp - pg_postmaster_start_time()) as uptime


Update table with data from table in a separate database TableA
ID Address Location Country
1   NULL     Region1  Austrailia
2   NULL     Region2  Antartica
3   NULL    Region3   Egypt

TableB
ID  Address        Name
1   test avenue    james
2   test avenue 2  jessica
3   test avenue 3  joshua

I am working on a query to migrate data Address data from Database 1, TableB to Database 2 TableA. So far I have tried to do the query below however that only inserts one record for the entire database, I want to insert each new sequential address into TableA as it appears in TableB.

UPDATE [dbo].[TableA]
SET [dbo].[TableA].[Address]= Table2.ID
FROM (SELECT [TableB].[ID]
        FROM [TableB]) AS Table2
WHERE [dbo].[TableA].[ID] <= 1449

This results in all records only having the first ID which is 1, so all the rows in TableA are Address = 1, instead of 1,2,3 etc. I think I am close but not quite sure what I am missing

Desired Result

TableA
    ID Address Location Country
    1   1        Region1  Austrailia
    2   2        Region2  Antartica
    3   3        Region3   Egypt

You can try this

UPDATE a
SET a.Address = b.ID
FROM TableA a
JOIN TableB b ON b.ID = a.ID
WHERE a.ID <= 1449


firebird 3 identity column - return last inserted id I am using FireDAC in Delphi 101. Berlin and I want to get the last inserted id of IDENTITY DATA TYPE column after executing insert query but, the returned value is empty.

Query: INSERT INTO ACC_ACCOUNT_HEAD (ACCOUNT_HEAD_TITLE) VALUES ('TEST') returning ACCOUNT_HEAD_ID;

table structure

CREATE TABLE ACC_ACCOUNT_HEAD (
 ACCOUNT_HEAD_ID       INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  ACCOUNT_HEAD_TITLE    VARCHAR(208),
  PRIMARY KEY (ACCOUNT_HEAD_ID)
);

Note: I know it is possible with generators but I want to know if it is possible with IDENTITY column.

Complete Delphi Code:

var
aQry : TFDQuery;
begin
aQry := TFDQuery.Create(Self);
aQry.Connection := frmMain.aCon;

 aQry.SQL.Text :=
      'INSERT INTO ACC_ACCOUNT_HEAD (ACCOUNT_HEAD_TITLE) VALUES (:P1) returning ACCOUNT_HEAD_ID';
    aQry.ParamByName('P1').AsString := 'Testing';
    aQry.ExecSQL; // or Should I use aQry.Open(); to get returned value???

Have a look at Delphi docs about ExecSQL. (Bold is mine)

  ExecSQL returns an integer value reflecting the number of rows affected by the executed SQL statement.

I'm mostly a CBuilder++ (old) user, but you can try by using:

aQry.Open();
result := aQry.Fields[0].AsInteger;
aQry.Close();

And let me suggest to enclose the query inside a try-catch block.


Difficulty understanding Postgres synchronous_commit = off and data loss I am confused what "data loss" means the context of synchronous_commit = off.

It sounds like synchronous_commit = off is pretty safe to increase write perf, and may result in "data loss", but not "data corruption" which sounds a lot worse.

So as I understand, if things crash, I might have lost some data a second before the crash. But the database is still consistent and not "corrupted", so I can simply re-run what didn't go through once it's back up, correct? My database does not end up in a state where it "kinda happened" and all the data is messed up?

I think it may also help me understand to give me examples where synchronous_commit = off is NOT ok to use.

In the documentation (https://www.postgresql.org/docs/11/wal-async-commit.html) it says:

  Thus asynchronous commit should not be used if the client will take external actions relying on the assumption that the transaction will be remembered. As an example, a bank would certainly not use asynchronous commit for a transaction recording an ATM's dispensing of cash. But in many scenarios, such as event logging, there is no need for a strong guarantee of this kind.

I am confused because the ATM dispensing cash is too obvious of an example, and the event logging example does not go into enough depth for me to understand the difference.

Say we wanted to sequentially save 3 items in our database table called numbers: [1, 2, 3]
And we have a client that queries the table numbers for what was last saved, and will resume from where it left off.

numbers
---
id
---
1

So if we ran our client, it will find 1 and start at 2. Now if we write 2, with asynchronous commit we get a premature success signal back, but then our database suddenly crashes before it can actually persist. Now if we were to restart our client, would it resume from 2 again (correct) or skip it and start with 3 (incorrect)?

so I can simply re-run what didn't go through once it's back up, correct?

How would you know to do so?

  Say we wanted to sequentially save 3 items in our database table called numbers: [1, 2, 3] And we have a client that queries the table numbers for what was last saved, and will resume from where it left off.

So it saves all three.  It queries the database and sees all three.  Then it says "OK, I'm good" and exits.  But then the database crashes, and when it comes back up only the 1st one is there.  Unless the client is always periodically rechecking, it won't know anything has been lost in order to fix it.

Another scenario.  One client commits all three rows, and a second program immediately queries and sees all three, and uses that data to make a decision.  If the decision of the second program is recorded exclusively into the database, then you are fine.  If any of those 3 rows are lost, the recorded decision is also lost.  But if that second program sent its decision to something outside the database, then you can have a problem because the external decision survives the crash, but the data upon which the decision was based might no longer exist once the database comes back up.


cloud computer to speed up sql big data queries? i have been doing a lot of data mining at my new job.
we collect TONS of data, and it's my job to visualize that sea of information in real time with statistic/heuristic analysis.

we're currently using mysql on a dedicated virtual server.
i'm commonly getting the sum of 100,000+ rows in a single query. and let me say, that some of my more complex multi-table queries are taking 14sec+ :P i'm constantly trying to optimize my sql routines to increase performance, but there's only so much i can do.

my boss keeps asking me if we need to move our data to the cloud. and honestly, i'm not sure. i have read a lot about "how great it is", "how fast it is", etc... but it's hard to tell what's marketing and what's legit.

i have also been thinking about caching data. i have done a bit of this in the past and can see a real benefit from it. but i'm not really sure how applicable it is in my situation. we're getting live data all the time, and give the users the ability to mine that data in a verity of ways. (e.g. hourly, daily, weekly, monthly, quarterly, yearly, all time, or a user defined custom time range) if you looking at a data-set from say last month, that data is finalized. there will be no new data added to it ever. but for this month, new transactions are happening every moment, and i'm not sure if i can even cache that type of thing... sigh

i'm not opposed to moving away from sql either. it's just the model we're currently using. but as our company grows (thus the volume of data read+write) i want to continue to offer a very fast service for both the businesses using it as well as their users.

so can i get some feedback (and suggestions) from real world users?
cloud sql? or using/creating some caching engine?

You've asked a question which often results in real projects (and I freely admit to being a consultant in this area).

I'm just going to give you some things to think about:

If you are getting your results from the cloud, you have to get your data there.  This may involve additional lags.
How real-time is real-time?
Do you have programming expertise in the group/company your work for?  For complex data manipulation tasks, Hadoop (and its derivatives) is a cloud-based technology that works well.
Once you've found something of interest, do you have a way to "productionize" it, so updating the results does not waste analysts' time.
The cloud (such as AWS) can provide very scalable hardware.  However, hardware is pretty cheap.  Part of the question is the balance between expenses versus capital investment.
Are you tied to MySQL?  Other databases have better functionality for complex queries on large data sets.


How to load Excel file into temp table in ORACLE Is it possible to load a excel file with the headers into a temp table on Oracle?

I use TOAD for the interface.

I am needing to do some comparison on a a excel file with what we have in our database. Unfortunately, the data that is in the excel file is NOT in our database but in order for me to report on data, I need to bring it in for comparison.

Is this doable? If so, what would the appropriate approach be for this?

First go to the menu and choose: database → create  → table

The follow prompt, choose schema, select object type (view, table etc).

Also make sure your column structure does not interfere with importing since the same rules apply with importing data when it comes to column spaces etc.. (Column names with spaces etc...)


How do you list all logins for Sql Azure? What queries can I use to list all logins for a server and database in SQL Azure?

I tried running select * from sys.sysusers but I'm guessing that's just showing the users

A login is for authenticating to the server.

A login maps to a database user to access the database.

SELECT * from master.sys.sql_logins will list logins for Azure.


Postgres - How to get all the values from a field that it repeats on every json from an array? I'm storing a lot of json from some websites on PostgreSQL 9.5 in the jsonb format. One of the fields is an array of Jsons that have the same information. Here is an example.

[
  {
    "text": "JIMIN",
    "indices": [
      16,
      22
    ]
  },
  {
    "text": "PUMA",
    "indices": [
      23,
      28
    ]
  }
]

I want to retrieve all the text values, that is, I want to make a query that gives me JIMIN and PUMA on this example. I can't find a way to accomplish that with the commons operators (like #>>). Is there a way to accomplish this?

You didn't provide any code, so I will create a sample table with one row and some queries to extract the information you need.

CREATE TABLE test (myfield jsonb);

Load data:

INSERT INTO test (myfield) VALUES ('
[
  {
    "text": "JIMIN",
    "indices": [
      16,
      22
    ]
  },
  {
    "text": "PUMA",
    "indices": [
      23,
      28
    ]
  }
]
'::jsonb);

To extract a specific item, let's say the text field of the second array item:

SELECT myfield#>>'{1,text}' AS text FROM test;

This will output:

  text
 ------
  PUMA
 (1 row)

In order to extract all the text fields inside the array, a possible solution is:

SELECT (jsonb_array_elements(myfield)::jsonb)->'text' AS text FROM test;

The output is:

  text
---------
 "JIMIN"
 "PUMA"
(2 rows)

Obviously this works with many records: each array item will be ported to one row for each array. This query is compatible with PostgreSQL 9.5. Look at the official docs to create a more efficient or versatile query ( https://www.postgresql.org/docs/9.5/static/functions-json.html ).


How to find Jobs that ran between two times Is there a query to check what a Jobs that ran between to given times. I can check the Jobs that are scheduled between two times but I dont want that.

Example I want to know what the Jobs that ran between 16:00:00 and 17:00:00

There can be cases where job scheduled at 15:00:00 might running for more than 1 hour I want those as well. I did search in google all I got is jobs those are scheduled between two times

Well you could run the following query to retrieve the jobs run between two times or you could change the WHERE expression to catch jobs that ran for a certain duration.

SELECT sj.name,
       sjh.step_name,
       sjh.step_id,
       --sjh.run_status,
       STUFF(STUFF(CAST(sjh.run_date as nvarchar(10)),5,0,'.'),8,0,'.') as HistRunDate,
       STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  CAST(sjh.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') as HistRunTime,
       STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(sjh.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') as HistRunDuration
       --,sjh.run_status AS JobStatus
FROM   msdb.dbo.sysjobs           AS sj
       join msdb.dbo.sysjobhistory AS sjh --- was sysjobschedule sjc
            on sjh.job_id = sj.job_id
WHERE  1=1
--AND sj.enabled = 1
------------------------------------------
-- for a certain time frame
------------------------------------------
AND ((sjh.run_date = 20170617 and sjh.run_time > 200000)
or (sjh.run_date = 20170722 and sjh.run_time < 100000))

------------------------------------------
-- between certain dates and with a long duration
------------------------------------------
--AND (sjh.run_date > 20160501) AND (sjh.run_date < 20160503)
--and sjh.run_duration > 3000

------------------------------------------
-- Job Outcome not required
------------------------------------------
--and sjh.step_name != '(Job outcome)'
--and sjh.step_id = 0

------------------------------------------
-- Find failed jobs
------------------------------------------
--and sjh.run_status != 1

ORDER BY
       sjh.run_date,
       sjh.run_time

As McNets pointed out there are various ways to retrieve the date you require. It's good to know though that the dates and times are defined as INT in the sys.jobsxxxx tables and not as dates and times as one would expect.

Reference: dbo.sysjobhistory (Transact-SQL)(Microsoft Technet)


what is oracle universal installer in oracle database products I am new to oracle database products. I am planning to install oracle 11g express edition in my PC,it looks like a standard installer.But in many of the oracle database installation tutorial mentions oracle universal installer.oracle universal installer provides more open options,but those options and features not available in downloaded oracle 11g express edition.

difference between two https://docs.oracle.com/cd/E17781_01/install.112/e18803/toc.htm#XEINW151 and http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r1/prod/install/dbinst/windbinst2.htm

so anyone explain me exactly what is oracle universal installer??Thx in advance.

Oracle's installers are mostly Java based - see here. Oracle apparently wants to "universalize" (yuck, what a word) the procedure. Apparently, however, for XE on Windows, they decided that it would be best just to have a plain old .exe.

Maybe they figured that Windows users who chose the XE option weren't up to installing a JVM? :-). Maybe they used some sort of Byte code-to-exe tool to convert their Java based installer to a simpler Windows exe?

Apart from the CPU, RAM and data limitations in XE, Java isn't included in the database. If I recall correctly (haven't done it in a while), the Linux installer is a Java based one - even though Java isn't in the db. I'm not sure if the installer has it's own JRE or whatnot.

Basically, Oracle is trying to make installs as generic (univeralize - what was wrong with the word "generic"?) as possible but has decided to keep the .exe paradigm for Windows.


Why does Concatenation operator estimate fewer rows than its inputs? In the following query plan snippet, it seems obvious that the row estimate for the Concatenation operator should be ~4.3 billion rows, or the sum of the row estimates for its two inputs.

However, an estimate of~238 million rows is produced, leading to a sub-optimal Sort / Stream Aggregate strategy that spills hundreds of GB of data to tempdb. A logically consistent estimate in this case would have produced a Hash Aggregate, removed the spill, and dramatically improved query performance.

Is this a bug in SQL Server 2014? Are there any valid circumstances in which an estimate lower than the inputs could be reasonable? What workarounds might be available?

Here is the full query plan (anonymized). I do not have sysadmin access to this server in order to provide outputs from QUERYTRACEON 2363 or similar trace flags, but may be able to get these outputs from an admin if they would be helpful.

The database is in compatibility level 120, and is therefore using the new SQL Server 2014 Cardinality Estimator.

Stats are updated manually every time data is loaded. Given the volume of data, we are currently using the default sampling rate. It's possible that a higher sampling rate (or FULLSCAN) could have an impact.

To quote Campbell Fraser on this Connect item:

  These "cardinality inconsistencies" can arise in a number of situations, including when concat is used. They can arise because the estimation of a particular subtree in the final plan may have been perfomed on a differently structured but logically equivalent subtree. Due to the statistical nature of cardinality estimation, estimating on different but logically equivalent trees is not guaranteed to get the same estimate. So overall no guarantees of expected consistency are provided.

To expand on that a little: The way I like to explain it is to say that the initial cardinality estimation (performed before cost-based optimization starts) produces more "consistent" cardinality estimates, since the whole initial tree is processed, with each subsequent estimation depending directly on the preceding one.

During cost-based optimization, parts of the plan tree (one or more operators) may be explored and replaced with alternatives, each of which may require a new cardinality estimate. There is no general way to say which estimate will be generally better than another, so it is quite possible to end up with a final plan that appears "inconsistent". This is simply the result of stitching together "bits of plans" to form the final arrangement.

All that said, there were some detailed changes to the new cardinality estimator (CE) introduced in SQL Server 2014 that makes this somewhat less common than was the case with the original CE.

Aside from upgrading to the latest Cumulative Update and checking that optimizer fixes with 4199 are on, your main options are to try statistics/index changes (noting the warnings for missing indexes) and updates, or expressing the query differently. The goal being to acquire a plan that displays the behaviour you require. This may then be frozen with a plan guide, for example.

The anonymized plan makes it hard to assess the detail, but I would also look carefully at the bitmaps to see if they are of the 'optimized' (Opt_Bitmap) or post-optimization (Bitmap) variety. I am also suspicious of the Filters.

If the row counts are anything like accurate though, this seems like a query that might benefit from columnstore. Quite aside from the usual benefits, you might be able to take advantage of the dynamic memory grant for batch mode operators (trace flag 9389 may be required).


Query gives correct answer on second run I have the query

SELECT * FROM
(
    SELECT sort.*,
    (
        CASE concat(question_id,`type`)
        WHEN
            @curType
        THEN
            @curRow := coalesce(@curRow,0) + 1
        ELSE
            @curRow := 1 AND @curType := concat(question_id,`type`)
        END
    ) + 1 AS rank
    FROM
    (
        SELECT a.* FROM
        ul_attempt_responses a
        LEFT JOIN us_attempts b ON a.attempt_id = b.id
        WHERE b.user_id = 3
        AND response IS NOT NULL
        AND trim(response) != ''
        AND b.authenticated = 1
        ORDER BY question_id, type, id DESC
    ) sort
) final WHERE rank = 2;

On my local machine, when the query is executed twice, that is when I get the correct response. But on the Production Server, it works on the first time.

What could be the problem? Is there a problem with the query.

There's so much wrong with your query.

In your subquery sort you do a left join, but your where clause actually converts it to an inner join.

You seem to rely on the order by in the sort subquery. You don't do an order by in the outer query. Maybe you can practically rely on it to work, but in theory you can not.

Your variables @curType and @curRow are nowhere initialized. Either do this before the query with a SET @variable = 'whatever' command or in a cross joined subquery in your query. This is most likely the cause, why it gives a "correct answer" on the second run. Variables have session scope! Not query scope. You can not rely on them to be NULL or whatever you want them to be when you don't initialize them.

The keyword AND is not used to do two commands! It's used in boolean expressions, which return either 1 (true) or 0 (false) or NULL (unknown, if one of the operands is NULL. So, better check your result, if it really is correct ;)

Since it's not totally clear to me, what you're trying to do, I can only have a guess. But something like this may be what you're looking for.

SELECT * FROM (
    SELECT a.*,
    @curRow := IF(concat(question_id,`type`) = @curType, @curRow + 1, 1) AS rank,
    @curType := concat(question_id,`type`)
    FROM
    ul_attempt_responses a
    INNER JOIN us_attempts b ON a.attempt_id = b.id
    JOIN (SELECT @curType := NULL, @curRow := 0) var_init_subquery
    WHERE b.user_id = 3
    AND response IS NOT NULL
    AND trim(response) != ''
    AND b.authenticated = 1
    ORDER BY question_id, type, id DESC
) final WHERE rank = 2;


Why use explicit cursors instead of regular loops? I've been writing basic web apps for a year (for an Oracle db), and since the functions are pretty simple, most of us stick with regular FOR loops to get our data:

for i in (select * from STUDENTS) loop
      htp.prn(i.student_last_name || ', ' || i.student_first_name || ' ' || i.student_dob);
end loop;

But, cursors seem to be the 'right' way to do things. I can find lots of information on what cursors are and different ways to loop through them, but I can't find a solid reason why to use them over regular FOR loops.
Is it dependent on the needs of the procedure? Are there inherent advantages I should be aware of?

The code you posted is using a cursor.  It is using an implicit cursor loop.

There are cases where using an explicit cursor loop (i.e. declaring a CURSOR variable in the declaration section) produces either cleaner code or better performance

If you have more complex queries that you can't refactor out into views, it can make the code easier to read if your loop iterates over student_cursor rather than including a 30 line SQL statement that embeds a bunch of logic.  For example, if you were printing out all the students that were cleared to graduate and that involved joining to tables that had their academic records, the requirements of their degree program, tables with information on academic holds, tables with information about overdue library books, tables with information about unpaid fees, administrative overrides, etc. it would probably make sense to refactor the code so that this query wasn't stuck in the middle of code that is concerned with presenting the list to a user.  That might involve creating a view that would encapsulate all this logic.  Or it might involve creating an explicit cursor that was declared either as part of the current PL/SQL block or in some higher-level PL/SQL block (i.e. a cursor declared in a package) so that it is reusable.  Or it might involve doing something else for encapsulation and reusability (say, creating a pipelined table function instead).
If you want to make use of bulk operations in PL/SQL, you generally want to use explicit cursors.  Here is a StackOverflow thread that discusses the performance differences between explicit and implicit cursors.  If all you are doing is calling htp.prn, doing a BULK COLLECT probably doesn't buy you anything.  In other cases, though, it can result in substantial performance improvements.


InnoDB: Tablespace is missing for table One of our database table tblpages seems to be corrupted. When I select the table, its says

show full fields from `favc`.`tblpages`
Table 'tblpages' doesn't exist

So I thought repairing it will be possible. I run a repair on the table and got this unsuccessful message.

tblpages                    repair  Warning   InnoDB: Tablespace is missing for table 'tblpages'
tblpages                    repair  Error     Table 'tblpages' doesn't exist
tblpages                    repair  status    Operation failed

I don't have any latest copy of this table.
Please help with any advise you may have. Thanks!

in my case I could simply fix the table-space-missing problem by:

cd /var/lib/mysql/psa/
cp -a ModuleSettings.ibd.bak ModuleSettings.ibd

psa.ModuleSettings was the table which gave me «#1812 table space is missing»-erros.


RMAN list archivelogs that are needed for to recover specified backup I've taken backup of datafiles in 31.12.2013. It completed datafile backups successfully, but archivelog backups failed.

Now, I want to determine what archivelogs should I backup to be sure that my database will be recoverable after restoring 31.12.2013 backup of datafiles.

Depending on your requirements and what other backups you have done since then, you could use (from http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmreprt.htm#BRADV89594):

REPORT NEED BACKUP RECOVERY WINDOW OF n DAYS
Displays objects requiring backup to satisfy a recovery window-based retention policy.

REPORT NEED BACKUP REDUNDANCY n
Displays objects requiring backup to satisfy a redundancy-based retention policy.

REPORT NEED BACKUP DAYS n
Displays files that require more than n days' worth of archived redo log files for recovery.

REPORT NEED BACKUP INCREMENTAL n
Displays files that require application of more than n incremental backups for recovery.

These will tell you which files you need to backup to provide 'n' days worth of restore capability or 'n' redundancy.


How to optimize a table/db with 200 insert/sec I'm facing a very hard problem.

I have a mysql DB (MyISAM) with a main table (user) where I write a row for every new user (around 10 fields). Every INSERT triggers around 10 new INSERT in 10 different tables (counters, settings, etc).

In normal days (10 users/min) it works fine. In high-traffic days (100-200 users/min) my app just become unusable.

I worked a lot on system variables, tables indexes, etc., without any improvements. Today I figured out that probably the problem is on my main table (user): the slow query log shows that every INSERT can reach 180s to be executed!

This is the main query because I need the user ID in order to go on and let the user open and use the app. Moreover, this table receives a lot of SELECT, some directly and some in JOIN. It means that the locks for INSERT also block all the SELECT.

90% of new users, in high-traffic days, can't "pass" the signup, they get an error and they leave the app.

I'm reading a lot and trying a lot of new settings but probably the problem is not in mysql settings. It's in the app structure or in the first INSERT.

Do you have any suggestion for me? I'm completely open to anything. I would like to discuss with you possible solutions.

Thanks a lot in advance for any help.

p.s. I didn't put any info about DB, query, etc... If you need more information just let me know.

EDIT:

I don't have privilages for commenting so I'll add here this information:

1) do you mean changing InnoDB only the user table or the whole DB?

2) I just checked the values of Max_used_connections (it's 271) and Threads_running (it's 29). This is my file my.cnf

There are multiple design changes to make.

Use InnoDB, not MyISAM.  You need row-level locking, not table-level locking.  (And set innodb_flush_log_at_trx_commit = 2)  A blog on conversion tips:  http://mysql.rjweb.org/doc.php/myisam2innodb .  A MyISAM INSERT always blocks a SELECT.  An InnoDB INSERT rarely blocks a SELECT.

Rethink your counters.  You have reached the "limit" of bumping counters on the fly.  Instead of bumping them one at a time, batch them.  One approach is to write the actions to a table, then do batch operations on it to update the various counters.

At 200/sec or more, I recommend ping-ponging two staging tables, as described here:  http://mysql.rjweb.org/doc.php/staging_table  Note that you can also batch the INSERTs with this technique, thereby speeding them up considerably.

Get rid of all the triggers, unless you can explain something that won't be efficiently covered by the above suggestions.

180s for an insert -- that is what can happen if connections and queries pile up.  Your Max_used_connections and Threads_running were probably very high.  More than a few dozen for either of those can imply things like "180s".

It would help to know the "flavor" of the application -- Data Warehousing?  MOOG?  Search?

EDIT:

Yes, convert all of your tables to InnoDB.  Then lower key_buffer_size to 40M and raise innodb_buffer_pool_size to 70% of RAM.  (Reference:  http://mysql.rjweb.org/doc.php/memory )

max_heap_table_size and tmp_table_size, currently at 512M are dangerously high, especially when hundreds of queries are running at the same time.  Perhaps no more than 1% of RAM is wise.  This change may have no impact, or it may impact large, complex, SELECTs.  But the change should help prevent swapping, which could be the real problem you are experiencing.  (Swapping is terrible for MySQL.)


how to optimize mysql query/ structure/ approach for large datasets? Mysql Database structure:

I have two tables:

Users Table with autoincrementing id contains about 20K rows and growing:

id userName

1 John
2 Doe
3 Alice

Contents Table with autoincrementing id  contains about 10K rows and growing:

id Content

1 Content1
2 Content2
3 Content3

UserContent Table for association with both userId and contentId as foreign Keys and autoincrementing id:

id userId contentId
1  1      1
2  1      2
3  1      3
4  2      1
4  2      2
...

Problem Statement:

The problem is that i have a process which runs everyday and runs a query which picks up the next content item for every user that he or she has not received previously, and adds that unique combination of userId and contentId in the userContents table.

The Contents should be added to each user's account in the same order.

For example, the process runs on day 1 and adds 20k rows (one row for each user) in the table. on Day 2, another 20k rows are added and so on. As you can imagine, this has lead to alot of data in the userContent table which is slowing down the query. Currently, there are about 4.4 Million records on the userContent table and it is growing day by day.

users cannot modify/clear the content that they have received. the records in userContent table will remain the same always.

Current Sql Query:

the current query that i am using is this:

select u.id as userId, content.id as contentId
 from users u, contents c
where
u.id not in
(select distinct uc.userId from userContent uc where uc.userId = u.id and uc.contentId = c.id)
and c.id not in
(select distinct uc.contentId from userContent uc where uc.userId = u.id and uc.contentId = c.id)

What this query does

this query will pick out a unique content for each and every user in the database which that user has previously not received.

Lets dry run this:

Day 1: there are three users in the system: User A, User B and User C. Each of these users will get content A.

Day 2: Each of the three users A, B and C will receive Content B.

Day 3: A new user D signs up. the first three users A, B and C will receive content C whereas User D will receive content A.

Major problems that i have identified in this query:

since there is nothing common between users and contents, i have to make a cross join which results in a total dataset of 200,000,000 rows! (10k content rows and 20k users rows).
For each of these 200,000,000 rows, the query checks whether the combination of userId and contentId exists in the userContent table or not by making two separate sub queries.

Help needed in:

Currently, this query takes well over a minute to execute. Is there any way i can speed up this query by changing the query or the database structure or even the approach itself?

any help in this regard will be highly appreciated! Also, let me know if further clarifications are needed.

thanks!

I believe that I have the (better) solution to your problem.

(tl; dr) I've created a query which CROSS JOINs newly users with newly inserted content (few records presumably), thereby avoiding an expensive CROSS JOIN between the full user and content table (many records, i.e. expensive!).

I created tables user, content and user_content in the dbfiddle here. See DML and DDL at the end of this answer. I changed some of the names of the tables &c. to reflect my own preferences

singular table names (tables are collections anyway)
all lower case table names with underscores)
a composite PRIMARY KEY for user_content (see here for my thoughts on composite PRIMARY KEYs).

I then ran this query:

SELECT u.user_id AS "User ID", x.content_id AS "Content ID"
FROM user u
CROSS JOIN
(
  SELECT c.content_id FROM content c WHERE c.content_id NOT IN (SELECT content_id FROM user_content)
) AS x
UNION
SELECT y.user_id, c.content_id
FROM content c
CROSS JOIN
(
  SELECT u.user_id FROM user u WHERE u.user_id NOT IN (SELECT user_id FROM user_content)
) AS y
ORDER BY "User ID", "Content ID";

Which gives the same results as your one!

What my query is doing is CROSS JOINing the results of a search for new content (in the first subquery) with a search for new users (second subquery) providing, AIUI, this is the desired result, but without a CROSS JOIN over the full tables. There are differences in the result of EXPLAIN EXTENDED.

my query:

mysql> explain extended SELECT u.user_id AS "User ID", x.content_id AS "Content ID"
    -> FROM user u
    -> CROSS JOIN
    -> (
    ->   SELECT c.content_id FROM content c WHERE c.content_id NOT IN (SELECT content_id FROM user_content)
    -> ) AS x
    -> UNION
    -> SELECT y.user_id, c.content_id
    -> FROM content c
    -> CROSS JOIN
    -> (
    ->   SELECT u.user_id FROM user u WHERE u.user_id NOT IN (SELECT user_id FROM user_content)
    -> ) AS y
    -> ORDER BY "User ID", "Content ID";
+----+--------------------+--------------+----------------+--------------------+--------------------+---------+------+------+----------+--------------------------+
| id | select_type        | table        | type           | possible_keys      | key                | key_len | ref  | rows | filtered | Extra                    |
+----+--------------------+--------------+----------------+--------------------+--------------------+---------+------+------+----------+--------------------------+
|  1 | PRIMARY            | <derived2>   | system         | NULL               | NULL               | NULL    | NULL |    1 |   100.00 |                          |
|  1 | PRIMARY            | u            | index          | NULL               | PRIMARY            | 4       | NULL |    4 |   100.00 | Using index              |
|  2 | DERIVED            | c            | index          | NULL               | PRIMARY            | 4       | NULL |    4 |   100.00 | Using where; Using index |
|  3 | DEPENDENT SUBQUERY | user_content | index_subquery | uc_content_user_uq | uc_content_user_uq | 4       | func |    4 |   100.00 | Using index              |
|  4 | UNION              | <derived5>   | system         | NULL               | NULL               | NULL    | NULL |    1 |   100.00 |                          |
|  4 | UNION              | c            | index          | NULL               | PRIMARY            | 4       | NULL |    4 |   100.00 | Using index              |
|  5 | DERIVED            | u            | index          | NULL               | PRIMARY            | 4       | NULL |    4 |   100.00 | Using where; Using index |
|  6 | DEPENDENT SUBQUERY | user_content | index_subquery | PRIMARY            | PRIMARY            | 4       | func |    4 |   100.00 | Using index              |
| NULL | UNION RESULT       | <union1,4>   | ALL            | NULL               | NULL               | NULL    | NULL | NULL |     NULL | Using filesort           |
+----+--------------------+--------------+----------------+--------------------+--------------------+---------+------+------+----------+--------------------------+
9 rows in set, 1 warning (0.01 sec)

then show the 1 warning:

mysql> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select `ptest`.`u`.`user_id` AS `User ID`,'4' AS `Content ID` from `ptest`.`user` `u` union select '4' AS `user_id`,`ptest`.`c`.`content_id` AS `content_id` from `ptest`.`content` `c` order by 'User ID','Content ID' |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Running the query in the warning

select
  `ptest`.`u`.`user_id` AS `User ID`,
  '4' AS `Content ID`
from `ptest`.`user` `u`
union
select
  '4' AS `user_id`,
  `ptest`.`c`.`content_id` AS `content_id`
from `ptest`.`content` `c`
order by 'User ID','Content ID'

gives the correct result. I think that this query is MySQL's optimisation of my query - obviously, the '4' is taken from values in the tables and not hard-coded by me.

Running your orignal query gives 5 warnings:

mysql> explain extended SELECT
    ->   u.user_id as "User ID",
    ->   c.content_id AS "Content ID"
    -> FROM
    ->   user u, content c
    -> WHERE
    ->   u.user_id NOT IN
    ->                (
    ->                  SELECT DISTINCT uc.user_id
    ->                  FROM user_content uc
    ->                  WHERE uc.user_id = u.user_id AND uc.content_id = c.content_id
    ->                )
    ->
    -> AND
    ->   c.content_id NOT IN
    ->               (
    ->                 SELECT DISTINCT uc.content_id
    ->                 FROM user_content uc
    ->                 WHERE uc.user_id = u.user_id
    ->                 AND uc.content_id = c.content_id
    -> )
    -> ORDER BY user_id, content_id;
+----+--------------------+-------+--------+----------------------------+---------+---------+------------------------------------+------+----------+----------------------------------------------+
| id | select_type        | table | type   | possible_keys              | key     | key_len | ref                                | rows | filtered | Extra                                        |
+----+--------------------+-------+--------+----------------------------+---------+---------+------------------------------------+------+----------+----------------------------------------------+
|  1 | PRIMARY            | u     | index  | NULL                       | PRIMARY | 4       | NULL                               |    4 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | PRIMARY            | c     | index  | NULL                       | PRIMARY | 4       | NULL                               |    4 |   100.00 | Using where; Using index; Using join buffer  |
|  3 | DEPENDENT SUBQUERY | uc    | eq_ref | PRIMARY,uc_content_user_uq | PRIMARY | 8       | ptest.u.user_id,ptest.c.content_id |    1 |   100.00 | Using where; Using index; Using temporary    |
|  2 | DEPENDENT SUBQUERY | uc    | eq_ref | PRIMARY,uc_content_user_uq | PRIMARY | 8       | ptest.u.user_id,ptest.c.content_id |    1 |   100.00 | Using where; Using index; Using temporary    |
+----+--------------------+-------+--------+----------------------------+---------+---------+------------------------------------+------+----------+----------------------------------------------+
4 rows in set, 5 warnings (0.00 sec)

mysql>

4 of the warnings are about resolving fields or references, but the final one is a select (which is quite complex!)

select
  `ptest`.`u`.`user_id` AS `User ID`,
  `ptest`.`c`.`content_id` AS `Content ID`
from `ptest`.`user` `u`
join
  `ptest`.`content` `c`
   where
   (
     (
       not(<in_optimizer>
             (`ptest`.`u`.`user_id`,
              <exists>(select distinct 1 from `ptest`.`user_content` `uc`
                 where ((`ptest`.`uc`.`user_id` = `ptest`.`u`.`user_id`)
                 and (`ptest`.`uc`.`content_id` = `ptest`.`c`.`content_id`)
                 and (<cache>(`ptest`.`u`.`user_id`) = `ptest`.`uc`.`user_id`))))))
  and (not(<in_optimizer>(`ptest`.`c`.`content_id`,
              <exists>(select distinct 1 from `ptest`.`user_content` `uc`
                 where ((`ptest`.`uc`.`user_id` = `ptest`.`u`.`user_id`)
                 and (`ptest`.`uc`.`content_id` = `ptest`.`c`.`content_id`)
                 and (<cache>(`ptest`.`c`.`content_id`) = `ptest`.`uc`.`content_id`)))))))
order by `ptest`.`u`.`user_id`,`ptest`.`c`.`content_id`

As you can see, your EXPLAIN EXTENDED query is a good deal more complex than mine. My query provides a CROSS JOIN of new users and content (presumably smaller than the existing table), and I believe will be more performant on large datasets - which I don't have. You'll need to test!

------------------- DDL and DML --------------------------

CREATE TABLE user
(
  user_id INTEGER(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_name VARCHAR (50) NOT NULL
);

CREATE TABLE content
(
  content_id INTEGER(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  content_text VARCHAR (1000) NOT NULL  -- can be a BLOB, whatever
);

INSERT INTO user (user_name) VALUES
('Bob'),
('Alice'),
('Eve');

INSERT INTO content (content_text)  VALUES
('Content1'),
('Content2'),
('Content3');

CREATE TABLE user_content
(
  user_id INTEGER NOT NULL,
  content_id INTEGER NOT NULL,
  CONSTRAINT user_content_pk PRIMARY KEY (user_id, content_id),
  CONSTRAINT uc_user_fk FOREIGN KEY (user_id) REFERENCES user (user_id),
  CONSTRAINT uc_content_fk FOREIGN KEY (content_id) REFERENCES content (content_id),
  CONSTRAINT uc_content_user_uq UNIQUE INDEX (content_id, user_id)

-- will be unique anyway because of PK, but you can never give the optimiser
-- too much information. This index will speed up content by user searches.
 );

INSERT INTO user_content
VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 1),
(2, 2),
(2, 3),
(3, 1),
(3, 2),
(3, 3);

-- now insert a user with no content and content with no user

INSERT INTO user (user_name) VALUES ('Mary');
INSERT INTO content (content_text) VALUES ('Content4');


High Query Wait Times We are seeing the below query as regularly having very high wait stats.

What does this query relate to? System tables? Is it possible to reduce it's wait time?

Thanks!

select @reservedpages = sum(a.total_pages),
@usedpages = sum(a.used_pages),
@pages = sum(
CASE
-- XML-Index and FT-Index-Docid is not considered "data", but is part of "index_size"

When it.internal_type IN (202,204) Then 0
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
)
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id

That looks like a snippet from the definition of the sys.sp_spaceused system stored procedure.

  regularly having very high wait stats

What are the waits pertaining to the execution of this stored procedure?

I'd be even more interested to find out why sys.sp_spaceused is running so frequently that it is negatively affecting performance.  That stored procedure isn't typically going to be run regularly, or frequently if regularly.

Depending on what version of SQL Server you are using, there are a few ways to find out who (host name, application name, etc.) is kicking this stored procedure off.  My favorite way to do this would be through the use of Extended Events (if you are only SQL Server 2008 or higher.  You haven't specified version in your question as of now).


Property column in sys.col$ in Oracle I would like to ask what are the number presenting in Property column in sys.col$ table? When I count distinct, it returns 39 rows. And base on my own research, I know for sure that the property = 0, it means that column uses Byte semantic and property = 8388608, it uses Char semantic. Below is a query that I found on the internet:

"select DECODE (type#,
                  1, DECODE (BITAND (property, 8388608), 0, 'B', 'C'),
                  96, DECODE (BITAND (property, 8388608), 0, 'B', 'C'),
                  Null)
from sys.col$;"

Again, I try to understand what other numbers are.

Thank you so much

Khang

Does it really matter? It is an undocumented internal dictionary table. It is a number representing some flags set.

Just have a look at the definition of views such as DBA_TAB_COLS_V$.

select dbms_metadata.get_ddl('VIEW', 'DBA_TAB_COLS_V$', 'SYS') from dual;

There you can find stuff like below, example:

   ...
   decode(c.type#, 1, decode(bitand(c.property, 8388608), 0, 'B', 'C'),
                  96, decode(bitand(c.property, 8388608), 0, 'B', 'C'),
                  null),
   decode(bitand(ac.flags, 128), 128, 'YES', 'NO'),
   decode(o.status, 1, decode(bitand(ac.flags, 256), 256, 'NO', 'YES'),
                    decode(bitand(ac.flags, 2), 2, 'NO',
                           decode(bitand(ac.flags, 4), 4, 'NO',
                                  decode(bitand(ac.flags, 8), 8, 'NO',
                                         'N/A')))),
   decode(c.property, 0, 'NO', decode(bitand(c.property, 32), 32, 'YES',
                                      'NO')),
   decode(c.property, 0, 'NO', decode(bitand(c.property, 8), 8, 'YES',
                                      'NO')),
   decode(c.segcol#, 0, to_number(null), c.segcol#), c.intcol#,
   -- warning! If you update stats related info, make sure to also update
   -- GTT session private stats in cdoptim.sql
   case when nvl(h.row_cnt,0) = 0 then 'NONE'
        when exists(select 1 from sys.histgrm$ hg
                    where c.obj# = hg.obj# and c.intcol# = hg.intcol#
                      and hg.ep_repeat_count > 0 and rownum < 2) then 'HYBRID'
        when bitand(h.spare2, 64) > 0
          then 'TOP-FREQUENCY'
        when (bitand(h.spare2, 32) > 0 or h.bucket_cnt > 2049 or
              (h.bucket_cnt >= h.distcnt and h.density*h.bucket_cnt < 1))
            then 'FREQUENCY'
        else 'HEIGHT BALANCED'
   end,                                                          /* HISTOGRAM */
   decode(bitand(c.property, 1024), 1024,
          (select decode(bitand(cl.property, 1), 1, rc.name, cl.name)
           from sys.col$ cl, attrcol$ rc where cl.intcol# = c.intcol#-1
           and cl.obj# = c.obj# and c.obj# = rc.obj#(+) and
           cl.intcol# = rc.intcol#(+)),
          decode(bitand(c.property, 1), 0, c.name,
                 (select tc.name from sys.attrcol$ tc
                  where c.obj# = tc.obj# and c.intcol# = tc.intcol#))),
   decode(bitand(c.property, 17179869184), 17179869184, 'YES',
          decode(bitand(c.property, 32), 32, 'NO', 'YES')),
   decode(bitand(c.property, 68719476736), 68719476736, 'YES', 'NO'),
   decode(bitand(c.property, 137438953472 + 274877906944),
                 137438953472, 'YES', 274877906944, 'YES', 'NO'),
   decode(c.property, 0, 'NO', decode(bitand(c.property, 8796093022208),
                                      8796093022208, 'YES', 'NO')),
   ...