How to take advantage of AWS EC2 instance store (SSD) in a database environment? I am trying to set up a database environment with Ubuntu and PostgreSQL (I didn't want to use RDS because I'm a Ubuntu person).

I looked at description for the R3 instance type:

  Use Cases

  High performance databases, data mining & analysis, in-memory
  databases, distributed web scale in-memory caches, applications
  performing real-time processing of unstructured big data, Hadoop/Spark
  clusters, and other enterprise applications. R3

  R3 instances are optimized for memory-intensive applications and offer
  lower price per GiB of RAM.

  Features:

High Frequency Intel Xeon E5-2670 v2 (Ivy Bridge) Processors
SSD Storage
Support for Enhanced Networking

So, I went for the SSD Storage, and thought I can somehow utilize it for fast database operations.

Now when I begin to launch a Ubuntu instance, e.g. :

  Ubuntu Server 16.04 LTS (HVM), SSD Volume Type - ami-da05a4a0

I realize that I cannot use the SSD storage for the root device hosting PostgreSQL and all the system software. Moreover, the instance store is said to be ephemeral and data are lost if the AWS EC2 instance is stopped. The only use case I was able to find to use the instance store as Linux swap space.

I'm very new to AWS EC2. I'd just like to ask how should the SSD instance store be utilized in a database server (other than as swap)?

So far, the use case I can think of (swap, mount as /tmp) seems to be a waste of the money spent on reserving the SSD storage.

The ephemeral disk is excellent for exactly the two use cases you mentioned, swap and /tmp.  Don't underestimate the performance benefits, here -- the ephemeral disks are not only SSDs, they're also physically inside the host machine, unlike EBS, which is network-attached, so you get high bandwidth and low latency to the storage device.

Otherwise, their applications are somewhat limited for "normal" database setups... but in certain other applications, anything where the machine itself is also more ephemeral by nature (QA/dev environments, scalable read replicas), they can offer excellent performance.

If you're the experimenting/creative type, you might toy with a RAID-1 array split between EBS and ephemeral (which could theoretically improve your read throughput without jeopardizing durability).

  seems to be a waste of the money spent on reserving the SSD storage.

Ephemeral disks don't cost extra -- they are included in the price of the instance, so the cost for a given instance type is the same whether you activate them or not.

Incidentally -- always enable them when you launch an instance that includes them, because you can't activate them later.


Questions on Availability Group Readable Secondary Recently I encountered an issue in my SQL server. Please advise the concept and mechanism behind the AlwaysOn cluster.

Background:
 We built up a AlwaysOn cluster with 1 primary node dbs1 and 1 secondary node dbs2. Given that they configured both readable secondary as "read-intent only". They have also created the read-only routing list. Every connections specifying "ApplicationIntent=ReadOnly" will be redirected to dbs2.

Issue:
 The same agent job ( for insert data from mssql to MySQL) running on dbs2 is unstable - suddenly succeed and suddenly fail. For the success case, the agent session will be displayed on dbs1 instead of dbs2.

For failed case, it displayed an error that

  "Unable to connect to SQL Server '(local)'"   -- Agent Job History

  "the Target database is in an availability group and is currently accessible for connections when the application intent is set to read only".   -- SQL Error Log

But when we changed the readable secondary to be "yes", the agent job can always run successfully with the session on dbs2.

My Questions:

1) What is the behaviour of Read-intent only? Why the agent job failed?

2) Even it succeed, why the session is on dbs1 instead of dbs2, given that the agent job was set on dbs2? Does it relate to insert/delete/update command in the script?

3)What are the difference between read intent only and option yes on readable secondary? I have tested that the readonly routing in latter option still valid after specifying "ApplicationIntent= readonly"

I am really curious of their mechanisms. Please feel free to comment and discuss. Thanks.

Additional Information (newly update on 4 Aug 2017):

Below is my agent job script. Can I specify "ApplicationIntent=ReadOnly" on it? And I am confused that this T-SQL did not write any data in SQL Server table but write on another MySQL database. Why the availability group don't let me pass?  Again, special thanks to Ben and SqlWorldWide's comment and feedback!

EXECUTE ( 'TRUNCATE TABLE MySQL_Table' ) AT MySQL_Server;

INSERT INTO MySQL_Server...MySQL_Table  (c1,c2,c3)  SELECT c1,c2,c3 FROM [dbs2].[SQL_Database].[dbo].[SQL_Table];

"What is the behaviour of Read-intent only? Why the agent job failed?" You're making a promise to the server that this connection won't try to write any data to the database in the availability group. If you do try to write, that write will fail as the database cannot be written to. I believe that you need to go through the availability group listener to get this behavior, though. You've configured your secondary to accept connections only from clients that a) go through the listener and b) specify the ReadOnly application intent. Since your error says it's trying to connect to (local), I suspect that it's connecting to the instance directly.

I believe this also relates to how you've configured your Agent job. What are you specifying for the connection string? If it hasn't specified ApplicationIntent=ReadOnly (or something similar), it will get directed to the primary.
If it is all reads, your connection string likely doesn't specify the database. It has to include the database name so that SQL Server can tell which routing list to use (your server could have multiple Availability Groups running on it).  See: AlwaysOn Secondary readable - can't connect with applicationintent=readonly

"What are the difference between read intent only and option yes on readable secondary?" The difference between "read-intent only" and "any" for a database in the secondary role determines what kinds of connections it will accept. The former says that it will only accept connections from clients that pinky swear that they're not going to write anything (i.e. specify ApplicationIntent=ReadOnly). "Any" says it'll take anything (writes will still fail, but the requirement of the client to say that they're not going to write anything is relaxed).

You asked in a comment:
Can I specify the connection string AppicationIntent=ReadOnly in SQL Agent Job T-SQL script? Also, I am still a bit confused by that behaviours, given that my insert statement indeed is targeting the MySQL platform instead of SQL Server. Why the "Read-Intent Only" settings cannot let me go, especially I am not trying to connect to (local)?

It depends on what kind of SQL Agent job you have. For example, I've used ApplicationIntent=ReadOnly in SSIS packages, but I don't know how to specify that for things like a standard Transact SQL job. As to why it failed, if you have set up your secondary to only accept connections that explicitly set their ApplicationIntent to ReadOnly and your job didn't do that, it will be rejected (even though you know that it isn't going to do any writes).


Most Efficient Way to Retrieve Date Ranges What's the most efficient way to retrieve date ranges with a table structure like this?

create table SomeDateTable
(
    id int identity(1, 1) not null,
    StartDate datetime not null,
    EndDate datetime not null
)
go

Say you want a range for both StartDate and EndDate.  So in other words, if StartDate falls in between @StartDateBegin and @StartDateEnd, and EndDate falls in between @EndDateBegin and @EndDateEnd, then do something.

I know there are a few ways to probably go about this, but what is the most advised?

This is a hard problem to solve in general, but there are a couple of things we can do to help the optimizer choose a plan.  This script creates a table with 10,000 rows with a known pseudo-random distribution of rows to illustrate:
CREATE TABLE dbo.SomeDateTable
(
    Id          INTEGER IDENTITY(1, 1) PRIMARY KEY NOT NULL,
    StartDate   DATETIME NOT NULL,
    EndDate     DATETIME NOT NULL
);
GO
SET STATISTICS XML OFF
SET NOCOUNT ON;
DECLARE
    @i  INTEGER = 1,
    @s  FLOAT = RAND(20120104),
    @e  FLOAT = RAND();

WHILE @i <= 10000
BEGIN
    INSERT dbo.SomeDateTable
        (
        StartDate,
        EndDate
        )
    VALUES
        (
        DATEADD(DAY, @s * 365, {d '2009-01-01'}),
        DATEADD(DAY, @s * 365 + @e * 14, {d '2009-01-01'})
        )

    SELECT
        @s = RAND(),
        @e = RAND(),
        @i += 1
END

The first question is how to index this table.  One option is to provide two indexes on the DATETIME columns, so the optimizer can at least choose whether to seek on StartDate or EndDate.
CREATE INDEX nc1 ON dbo.SomeDateTable (StartDate, EndDate)
CREATE INDEX nc2 ON dbo.SomeDateTable (EndDate, StartDate)

Naturally, the inequalities on both StartDate and EndDate mean that only one column in each index can support a seek in the example query, but this is about the best we can do.  We might consider making the second column in each index an INCLUDE rather than a key, but we might have other queries that can perform an equality seek on the leading column and an inequality seek on the second column.  Also, we may get better statistics this way.  Anyway...
DECLARE
    @StartDateBegin DATETIME = {d '2009-08-01'},
    @StartDateEnd DATETIME = {d '2009-10-15'},
    @EndDateBegin DATETIME = {d '2009-08-05'},
    @EndDateEnd DATETIME = {d '2009-10-22'}

SELECT
    COUNT_BIG(*)
FROM dbo.SomeDateTable AS sdt
WHERE
    sdt.StartDate BETWEEN @StartDateBegin AND @StartDateEnd
    AND sdt.EndDate BETWEEN @EndDateBegin AND @EndDateEnd

This query uses variables, so in general the optimizer will guess at selectivity and distribution, resulting in a guessed cardinality estimate of 81 rows.  In fact, the query produces 2076 rows, a discrepancy that might be important in a more complex example.
On SQL Server 2008 SP1 CU5 or later (or R2 RTM CU1) we can take advantage of the Parameter Embedding Optimization to get better estimates, simply by adding OPTION (RECOMPILE) to the SELECT query above.  This causes a compilation just before the batch executes, allowing SQL Server to 'see' the real parameter values and optimize for those.  With this change, the estimate improves to 468 rows (though you do need to check the runtime plan to see this).  This estimate is better than 81 rows, but still not all that close.  The modelling extensions enabled by trace flag 2301 may help in some cases, but not with this query.
The problem is where the rows qualified by the two range searches overlap.  One of the simplifying assumptions made in the optimizer's costing and cardinality estimation component is that predicates are independent (so if both have a selectivity of 50%, the result of applying both is assumed to qualify 50% of 50% = 25% of the rows).  Where this sort of correlation is a problem, we can often work around it with multi-column and/or filtered statistics.  With two ranges with unknown start and end points, this becomes impractical.  This is where we sometimes have to resort to rewriting the query to a form that happens to produce a better estimate:
SELECT COUNT(*) FROM
(
    SELECT
        sdt.Id
    FROM dbo.SomeDateTable AS sdt
    WHERE
        sdt.StartDate BETWEEN @StartDateBegin AND @StartDateEnd
    INTERSECT
    SELECT
        sdt.Id
    FROM dbo.SomeDateTable AS sdt
    WHERE
        sdt.EndDate BETWEEN @EndDateBegin AND @EndDateEnd
) AS intersected (id)
OPTION (RECOMPILE)

This form happens to produce a runtime estimate of 2110 rows (versus 2076 actual).  Unless you have TF 2301 on, in which case the more advanced modelling techniques see through the trick and produce exactly the same estimate as before: 468 rows.
One day SQL Server might gain native support for intervals.  If that comes with good statistical support, developers might dread tuning query plans like this a little less.


How can i make month appear with two digits SQL Server how can i make monthid column values appear as  example: 201901, not 20191.

INSERT INTO [dbo].[table2](accountno, monthid, active)
SELECT accountno, CONCAT(YEAR(InsertionDate), month(insertiondate)), active
FROM [dbo].[table1]

DECLARE @t TABLE(InsertionDate datetime);
INSERT @t(InsertionDate) VALUES(GETDATE()), ('20190125');

SELECT CONVERT(char(6), InsertionDate, 112) FROM @t;

Result:

202004
201901


Datepart logic for custom sql agent job schedule Wanted help in code for below logic.

Job A needs to be run on 8th of month.
Job B needs to be run on Tuesday only.
Job c needs to be run on Daily(Minus Tuesday & Sunday).
And If Its Tuesday and 8Th Job A takes the precedence of Job.

i have coded logic for half of it.

Else If Begin logic,

--Tuesday Logic(0=Monday,1=Tuesday,2=Wed,3=Thu,4=Friday,5=Saturday,6=Sunday)
IF (SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE())/7*7,0) + 1)=1 -- If it is tuesday execute the Weekly+Daily Job
       Print 'executing weekly and daily tables'

ELSE
BEGIN
       Print 'executing daily job'
END
END

Here is my logic:

BEGIN
--FULL REFRESH Cycle
declare @EightDayofMonth datetime =(DATEADD(DAY, 8-DAY(GETDATE()), CONVERT(date, GETDATE())));
declare @actuladayofmonth datetime = CONVERT(date, getdate());

----checking for 8th day --condition 1 ( 8th of month and NOT Tuesday and not sunday) if any other day job executes
IF (@actuladayofmonth=@EightDayofMonth) AND (DATEPART(WEEKDAY,GETDATE())<>3 OR DATEPART(WEEKDAY,GETDATE())<>1)

BEGIN
Print 'disabe daily job'
print 'executing full job'
    END

--checking for monthly 8th is tuesday logic
ELSE IF (@actuladayofmonth=@EightDayofMonth) AND DATEPART(WEEKDAY,GETDATE())=3
BEGIN

Print 'Disable Tuesday Weekly'
print 'Disable Daily on tuesday'
Print 'Executing full job'
    END

ELSE IF (@actuladayofmonth=@EightDayofMonth) AND DATEPART(WEEKDAY,GETDATE())=1
BEGIN
Print 'Execute disable sunday'
 END
  END


SQL Server Email Pastes Literal HTML I am attempting to use HTML in my sql server send mail, but my email just shows what I type instead of formatting properly.  Can someone show me what needs to be altered in order for this to appear as HTML

declare @body1 varchar(4000)
set @body1 = '

        '

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Mod',
    @from_address = 'modis@modisglobal.com',
    @recipients= 'rsmith@gmail.com',
    @subject= 'Test Email',
    @body = @body1

From the documentation, you can see there's a @body_format option.

  [ @body_format= ] 'body_format' Is the format of the message body. The
  parameter is of type varchar(20), with a default of NULL. When
  specified, the headers of the outgoing message are set to indicate
  that the message body has the specified format. The parameter may
  contain one of the following values: TEXT HTML Defaults to TEXT.

Set it to HTML.

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Mod',
    @from_address = 'modis@modisglobal.com',
    @recipients= 'rsmith@gmail.com',
    @subject= 'Test Email',
    @body = @body1,
    @body_format = 'HTML'


Oracle live data updates for frequently updated table: poll or CQN? My team is looking at getting live updates from our Oracle database from a frequently updated table. I have discovered CQN and configured a test environment to use UTL_HTTP to POST to a webhook when the table changes. However, in the 'Best Practices' section of the documentation for CQN:

Register few queries—preferably those that reference objects that rarely change.

And in other articles for instance:

if you know the table is subject to a lot of change, then your apps will be better off simply polling the table and avoiding any CQN overhead. Note that CQN was designed to be used for relatively infrequently updated tables.

CQN had seemed like a great solution until I read these disclaimers. The table we want to get live updates from has about 1000 inserts per second during busy time bursts.
The alternative to CQN appears to be polling to query this table every second. Is polling like that the best solution for getting live updates from a table, or is there something I am missing? I am working with Oracle 11g.
Thank you.

Polling isn't uncommon; it will depend on what your application and data latency requirements are. Depending on your infrastructure, it may not be possible to poll more than a few times per minute.
If you must have real-time, or as near as possible, you might be able to use Oracle Advanced Queues to stream the updates.

https://www.oracle.com/technetwork/database/features/data-integration/oracle-aq-tech-wp11-2-191324.pdf
https://docs.oracle.com/database/121/LNCPP/queuing.htm#LNCPP010.

It would be an extra cost, but one other alternative is Oracle GoldenGate:

https://www.oracle.com/middleware/technologies/goldengate.html


Do I need "New SQL Server Failover Cluster installation" to run Availability groups with manual failover? I'm trying to set up a test environment here to learn more and apply this on our production environment.

I've created and installed the Windows Cluster with CLuster manager.
Now I'm going to install SQL Server Enterprise 2014  on both machines and create HA with  then, with availability groups. The failover will be manual. They will be in synchronized data transfer.

My question is, Can I normally install "stand-alone installarion or add..." the SQL server, or I need to use "new sql server failover cluster installation".

I cant find anything about this on the internet.

Im going to install sql server on both nodes that will be configured in the cluster.

If you want to just use stand alone instances - which is the most common method of implementing availability groups [stand alone to stand alone] - then you only need to do a stand alone install. So, yes, you can do the stand alone if that's what you're looking for.

It is possible to use a SQL Server failover cluster instance install to also work with availability groups but it is not required.


Is MySQL trigger for 100 Millions daily updates can impact data base cluster performance? I have a MySQL (Aurora 5.7) table in data base which has

700 Million rows
9 Indexes
25 Columns
100 Million daily updates
and 5 Million daily inserts

Overall this table is very busy and now we need to run some extracts on this data base to generate reports .
The issue is not only this but the column involved in Extract is not indexed so it takes 5 to 6 hours to perform extracts.
Extracts is just basically we get counts based on dates which is not indexed and we can not create indexes as this can impact application .
As we don't have index query goes for full table scan and impacts the Aurora cluster and breaks the Global Data base replication .
Even though we run this query in DR reader nodes it impacts  the cluster and AWS Supports and Experts also has confirmed this .
So we dont have option to run extracts on main table and this is where we are exploring other options .
Option 1 :
Take daily snapshot restore and run daily report in new cluster.
This option looks very good but the issue is cost
we have 18 TB data base and my extras daily will run for 10 to 15 hours because 21 such reports has to be generated so monthly cost will be close to $ 7 k
Option 2 :
Takes Daily snapshot  and extras only required  tables for report to s3 and then run Athena query .
But the issue is export to S3 is taking long time and cost again is very very high because even though we export few tables from Snapshot we are getting charged  for complete snapshot ,18 TB export which is again 6 K $ per month .
Option 3:
Have trigger on this bust table and inserts all the inserts and updates to new table and then run report on new table.
I need help to decide which is best option and if we go with Trigger option then there will 100 millions triggers daily and this many trigger can impacts data base ?
I have also looked at using DMS on this table and moving data to another table but that would be continuous replication which again can impact Cluster .
NOTE : Sorry to say but we can not afford any downtime for the application
Please suggest .

It is true that triggers incur some cost to the database. You should think about it in terms of the number of index writes.
When you insert or update a table without triggers, you can estimate the cost by counting the number of indexes, plus the primary key, which is the clustered index. You have nine indexes, so this is 9+1 = 10 writes per insert or update.
If you also have a trigger, the execution of trigger code is a small overhead, but it is more important to count the cost of the index writes by statements in the trigger. The index writes are more costly, compared to the trigger code itself.
So if your insert/update spawns a trigger that writes to another table, the cost will be the 10 index writes for the original table, plus as many index writes as the number of indexes in the additional table. Suppose you have one more index in that second table, it would then be 11 index writes, for a total of 21. So it is slightly more than double the cost.
My knowledge of Aurora is that it disables the InnoDB change buffer, because of the distributed storage. This means that all index writes must be merged at the time of your insert/update.
If I were working on your database, I would just add an index to the original table, using pt-online-schema-change. This allows clients to read and write the table while the index is being built. We use this at my company, and we do hundreds of schema changes per week for large tables, with no downtime.
Pt-online-schema-change uses triggers while it's building the index, but those triggers are dropped after this change is done. Also note that if you have long-running transactions against the table, it will conflict with pt-online-schema-change, because it needs a brief exclusive metadata lock at the start and the end of its work.


MySQLI stored procedure return PK/AI insertID I am using a PHP framework called CodeIgniter for my application. This is using the MySQLI driver for its database work.

This framework has built in support for things like a query builder where I could just code the statement into the model, but I am trying to use a stored procedure setup.

Anyway, I posted some questions with their support and they suggested asking here would be more appropriate.

In my model, I am calling a stored procedure like so:

$data = array("customerID" => 123, "productID" => 456);

...

public function addCustomerToProduct($data){
       $procedure = "CALL addCustomerProduct(?,?)";
       $result = $this->db->query($procedure, $data);
}

As it stands, this works perfectly fine. I send an array of data over, I tell my stored procedure to expect two variables, and I pass them along.

Stored Procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `addCustomerProduct`(IN in_customerID INT, in_productID INT)
BEGIN
    INSERT INTO order_customer_product (customerID, productID, retailAmountAtPurchase, faceValue)
    SELECT
        in_customerID,
        in_productID,
        p.retail,
        p.faceValue
    FROM
        products as p
    WHERE
        p.productID = in_productID;
END

While this works fine, I am not able to get the insert_id from the PK/AI.

The only way this data is passed back (it seems) is when you use :

$this->db->insert('customers', $data);
return $this->db->insert_id();

I am trying to stick with stored procedures as it just makes things easier to debug when its all separate.

How can I return the insert_id to my application when using a stored procedure setup like this?

Add OUT the_id to the argument list.

Just before the END, add this statement:  SELECT INSERT_ID() INTO the_id;

Then decide how you can retrieve the id from whatever variable you pass into the proc.


Create a function to generate a column value in a specific format I am new to databases or, in fact, programming. I want to create a function or maybe a stored procedure (if it works) to insert/create default values for a column of the users table. In this table, each of the registered users should be assigned a userId like K16060600, the format being Kyymmddxx, where

K is constant for every userId,
yy stands for year (e.g. 16 for 2016),
mm stands for month,
dd stands for day,
xx is a number for 00 to 99 and it should increment from 00 whenever a new row/user is added.
It is guaranteed that I will add fewer than 100 each day.

How can I do that?

I'm using Visual Studio 2012 and SQL Server 2012.

One way would be to add an ID column in your users table, with a default value based on a sequence object, then add a computed column to create the key using a date conversion to string.
To create the sequence, something like:
CREATE SEQUENCE [dbo].[userid_sq]
 AS [int]
 START WITH 1
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 500
 CYCLE

I wouldn't limit yourself to a value of 99 if you are not 100% sure you are going to stay under that number of users per day.
You want a fresh cycle every day, right? So you can force it with an evening job/script to ensure it is back at 1 the next day.
ALTER SEQUENCE [dbo].[userid_sq]  RESTART WITH 1;

So just to illustrate the point, say I'm creating a new table, UserDetails, and want to include this logic, I might do something like:
CREATE TABLE UserDetails (
    ID INT NOT NULL DEFAULT NEXT VALUE FOR userid_sq
    ,Created DATETIME DEFAULT GETDATE()
    ,UserKey AS
        'K' +
        CONVERT(VARCHAR(6), Created, 12) +
        RIGHT(CONVERT(VARCHAR(11), 1000 + ID), 3)
    ,UserName VARCHAR(50) NOT NULL
    )

INSERT INTO UserDetails (UserName)
VALUES ('TestUser')

My table content would be:

ID       - 1
UserKey  - K160531001
UserName - TestUser

You asked about indexing, so, ignoring the fact that the above example would be non-deterministic and therefore ineligible to be used a primary key, if you want to index the computed column, you'll need to PERSIST the column in the database. This stores the values to disk rather than as a calculated virtual value.
ALTER TABLE table_name ALTER COLUMN computed_column_name ADD PERSISTED;

Note that there are various restrictions with what you can do with computed columns so I would implore you to read the following information to get a proper understanding.
MSDN Link
Here's a relevant snippet from that article, which also relates to the above example:

A computed column cannot be used as a DEFAULT or FOREIGN KEY constraint definition or with a NOT NULL constraint definition. However, if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns, a computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint. For example, if the table has integer columns a and b, the computed column a + b may be indexed, but computed column a + DATEPART(dd, GETDATE()) cannot be indexed, because the value might change in subsequent invocations.
A computed column cannot be the target of an INSERT or UPDATE statement.


MySQL High Memory / Threads - Ubuntu I am having issues with MySQL 5.7 using a lot of memory and a lot of threads on my prod server, and having some issues getting much results out of any changes I have made.

Is there a reason so many threads are in use?
How can I reduce the memory consumption down as its slowing the server a lot.

This is MySQL install on a fresh copy of ubuntu20. Nothing else was installed except mysql.

After adding performance_schema = 0 to /etc/nginx/my.cnf

By default, MySQL allows up to 150 connections, and each connection is handled by a separate thread. Reduce max_connections to, say, 50 and you'll see the number of threads go down.
Some memory is allocated per thread, other in a pool shared between all the threads. The defaults are generally sensible. If you are not using MyISAM tables, you can reduce key_buffer_size to 1M. Adjust innodb_buffer_pool_size based on the size of your InnoDB data. As a rule of thumb, at the very least your primary keys should fit into the buffer pool, but normally you would want more buffer pool than that.


insert in Mysql innodb table extremely slow I can get 2 million inserts per minute on myisam table, but only 3000 insert per minute if I change to innodb

CREATE TABLE myisam_table ( c1 int default NULL, c2 varchar(30) default NULL, c3 date default NULL) engine=myisam;
CREATE TABLE innodb_table ( c1 int default NULL, c2 varchar(30) default NULL, c3 date default NULL) engine=innodb;

CREATE PROCEDURE load_myisam_table()
       begin
    declare v int default 0;
    while v < 10000000
    do
        insert into myisam_table
        values (v,'testing','2017-01-01');
         set v = v + 1;
    end while;
    end

CREATE PROCEDURE load_innodb_table()
       begin
    declare v int default 0;
    while v < 10000000
    do
        insert into innodb_table
        values (v,'testing','2017-01-01');
         set v = v + 1;
    end while;
    end

And result:

mysql -e "select count(*) from asdf.innodb_table;" ; sleep 60;mysql -e "select count(*) from asdf.innodb_table;" ;
+----------+
| count(*) |
+----------+
|      125 |
+----------+
+----------+
| count(*) |
+----------+
|     2975 |
+----------+
mysql -e "select count(*) from asdf.myisam_table;" ; sleep 60;mysql -e "select count(*) from asdf.myisam_table;" ;
+----------+
| count(*) |
+----------+
|   134446 |
+----------+
+----------+
| count(*) |
+----------+
|  2225277 |
+----------+

Running insert/update queries on an InnoDB table is significantly slower when no transaction is started. I've run the procedures you listed and the result was that load_innodb_table was actually faster than load_myisam_table.
Running load_innodb_table with 10m inserts actually means that you're running 10m transactions, as every insert implicitly starts a transaction.


SQL Server Always On File Share Witness (Quorum vote) on different subnet to other nodes I am currently having issues with some of my Availability groups where node 1 and node 2 loose connectivity between each other "A connection timeout has occurred on a previously established connection to availability replica".

The errors in the failover cluster manager say "File share witness resource 'File Share Witness' failed to arbitrate for the file share" The server the file share sits on hasn't restarted or had any issues and all permissions are working.

The only thing I can see is that the file share server is on a different subnet to the other 2 SQL Server nodes in the cluster.

Could someone confirm is having the file share server on a different subnet a big no no in an AlwaysOn environment? All the firewall rules are in place as it can talk to the other nodes but out of hours (usually) it loses connectivity.

The other weird thing is there are 3 votes in the quorum including the file share, so even if the file share loses connectivity to the failover cluster, node1 & node2 shouldn't lose connectivity between each other as there's enough votes for quorum (2)

Could someone confirm is having the file share server on a different subnet a big no no in an AlwaysOn environment?

It's completely fine to have the FSW on a different subnet, there is absolutely nothing wrong with that. There is no need to have it on the same subnet, in fact there is an Azure witness which definitely won't be on the same subnet and it works without issue.

  "A connection timeout has occurred on a previously established connection to availability replica"

Seems to be pointing to either something in the network being an issue or if this is on a virtual machine something is happening to the guest/host which is giving you this trouble. Given there are a plethora of in-depth configuration settings at the host, guest, and OS level that can contribute to this I won't go into any further depth as it'd be out of scope of this site.

  The errors in the failover cluster manager say "File share witness resource 'File Share Witness' failed to arbitrate for the file share" The server the file share sits on hasn't restarted or had any issues and all permissions are working.

This means that whomever attempted to arbitrate for the witness was only one vote off of having quorum for the cluster. Since it's a two node cluster, if the nodes couldn't talk to each other they'd be in this exact situation.

If neither node can talk to each other (obviously an issue) and neither node can talk to the FSW (another issue) this makes me wonder what's broken in the infrastructure - again, either at the virtual layer or the physical (network) layer. It's clear something is going on to cause this and is specific to your environment, not SQL Server.

  The other weird thing is there are 3 votes in the quorum including the file share, so even if the file share loses connectivity to the failover cluster, node1 & node2 shouldn't lose connectivity between each other as there's enough votes for quorum (2)

Yes, however I'm betting the nodes lost connectivity to each other. There are probably some entries about missed heartbeats, connectivity to ~3343, regroups, etc., in the cluster log.

Connectivity doesn't mean votes, connectivity means health checks. Once health checks fail, nodes become partitioned and that's when these events occur. You'll need to find out what occurred in your environment around the time this happened. If it happens quite often and on schedule, then it's some task or software in the environment, if it happens randomly then it's most likely infrastructure issues such as networking or Host/Guest/OS settings if it happens when under load.


Is there a strategy to use timestamp as primary key? The majority of times we use integer sequences for primary keys to maintain uniqueness of row in a table. Couldn't we use timestamps instead of integers for this purpose? Is there any flaw in that strategy, because I assume that the machine cannot do two things at the same "time"...right?

I am working on a database with huge hibernate sequences in the keys, so I thought of using timestamps as a solution. Are there any approaches like this that are used in practise that work with any database? That would be good to know.

EDIT : What I am asking is that since no two moments of time are the same for us (humans), does that also hold true for computers? I know that the computers track time through the vibration of some crystal (not sure how small a time it can measure).

In the database I have some table where no primary key is applicable except time (Audit trail), and specifying a sequence (hibernate_sequence) increases the number to an extremely high degree which makes it inconvenient to work with other tables as it affects all tables with sequence as primary key.

So, if PostgreSQL can guarantee that no two insert/update operations will occur at the same timestamp, then I could use it as a primary key, BUT if not then obviously I cannot use this solution.

Basically the question is: Does PostgreSQL provide any guarantee (through some approach or configuration) that no two records will be inserted/updated at the same "time"?

Thanks

Not a good idea.

PostgreSQL provides many functions that pertain to current timestamps,

transaction_timestamp()
statement_timestamp()
clock_timestamp()
timeofday()
now()

The problem is none of these are insertion time. You have multiple backends. Each backend can process one transaction. These can transactions can start at the same time, or they can call these functions at the same time (they'll be on different threads/processes).


Compare data between 2 rows of same table I have a table that looks like this:

date1                   date2
2017-03-06 17:02:00     null
2017-03-06 17:02:00     2017-02-17 11:20:00
2017-03-01 17:07:00     2017-03-13 17:07:00
2017-03-17 12:05:00     null
2017-01-24 11:45:00     null

Which join should I use to output 1 column of data that only shows the date1 in the result and if there is a value in date2 column, it will show the date2 value instead of the date1 value?

Based on your requirement if both columns are in the same table we could use ISNULL to achieve this output?

e.g.

select ISNULL(date2, date1) as CalcDate
from <table>

Results:

CalcDate
2017-03-06 17:02:00.000
2017-02-17 11:20:00.000
2017-03-13 17:07:00.000
2017-03-17 12:05:00.000
2017-01-24 11:45:00.000


slave issue with load data When I use "load data infile" on master, the data is properly transferred to slave.
But when I use "load data local infile" from remote server to master, the data does not pass on to slave.

Why does the data from remote server is not read by slave?

The LOAD DATA INFILE statement was not always replicated correctly to a slave running MySQL 5.1.42 or earlier from a master running MySQL 4.0 or earlier. When using statement-based replication, the LOAD DATA INFILE statement CONCURRENT option was not replicated. This issue was fixed in MySQL 5.1.43. This issue does not have any impact on CONCURRENT option handling when using row-based replication in MySQL 5.1 or later (See this bug report http://bugs.mysql.com/bug.php?id=34628)

Also In MySQL 5.1.52 and later, LOAD DATA INFILE is considered unsafe. It causes a warning when using statement-based logging format, and is logged using row-based format when using mixed-format logging.

http://dev.mysql.com/doc/refman/5.1/en/replication-rbr-safe-unsafe.html


How do you gain access to embedded MySQL database via localhost command line? We run an application (Windows 2003 server) and would like to have database connectivity -- contacted the company and they do not have any documentation on how to do so.  A quick glance at the installation and the services running shows that the data lives in an embedded MySQL installation.  Looking at some of the installation scripts reveals a login and password to some of the database tables...but no credential that gives full permissions to create/modify users.

Ultimate goal is to:

modfiy the my.cnf file to allow remote connections, then
grant access to a user who could connection into the database
make a dblink in our primary reporting database (Oracle 11g) to this MySQL installation

I see some writeups on how to reset root:
http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

but I am hesitant to do so given that I don't want to run any risk of disrupting the application that uses this MySQL installation as a data store.

So, question: Is there any reason why I couldn't use a similar  approach as in the documentation above (especially option C - connecting with skip grant) but create a new user with the permissions I need (as opposed to altering the root user, as they do above?)

If it's relevant, mysql shows a server version of 4.1.18-pro-nt.

Thanks!


Accessing ORA_ROWSCN in the triggers body Is it possible to access ORA_ROWSCN pseudo-column in the body of BEFORE triggers? I'm thinking about storing this value in the table itself to emulate SQLServer rowversion behaviour.

It doesn't seem to be possible (even if Oracle itself populates the value before launching BEFORE UPDATE trigger) - referring to :NEW.ora_rowscn in the trigger body generates error, select ora_rowscn from table1 in the trigger causes mutating trigger error.


ORA-00600 argument description I am experiencing several ORA-00600 error events, presumably during exceptionally high I/O. Does anyone know where I can lookup the meaning of the following arguments for ktspfmdb:objdchk_kcbnew_3? I.e. what does 13, 873730 and 4 stand for?

ORA-00600: internal error code, arguments: [ktspfmdb:objdchk_kcbnew_3], [13], [873730], [4], [], [], [], [], [], [], [], []

You are facing a bug. You need to gather more information using RDA in order to perform an in-depth analysis.

These are known bugs related to your ORA-600 message.

10043021    11.2.0.4, 12.1.0.1  ORA-600 [kcbnew_3] after select / analyze on RECYCLEBIN object

17586955    11.2.0.4.4, 11.2.0.4.BP11   ORA-600 [ktspfmdb:objdchk_kcbnew_3] in RAC

12747437    11.2.0.3.8, 11.2.0.3.BP21, 11.2.0.4, 12.1.0.1   ORA-600 [ktspfmdb:objdchk_kcbnew_3] after purging single consumer queue table

12323180    11.2.0.2.7, 11.2.0.2.BP17, 11.2.0.3, 12.1.0.1   ORA-600 [ktspfmdb:objdchk_kcbnew_3] due to re-used block read into cache

11902008    11.2.0.4, 12.1.0.1  SMON may crash with ORA-600 [kcbgcur_3] or ORA-600 [kcbnew_3] during Transaction recovery

10137834    11.2.0.2    ORA-600 running DBMS_XDB.MOVEXDB_TABLESPACE

9456964     11.2.0.2    OERI[kcbnew_3] after shrinking an IOT and reusing the blocks freed by shrink

Look if any of the above descriptions matches your case.

But the short answer would be to open a Service Request with Oracle Support.


psql gives Invalid data directory error I have VM with ubuntu 18.04 server installed. I have installed postgres 10 from the repo.

Default installation. I made some connection count related changes in postgres.conf and connection security related changes in pg_hba.conf.

The install directories are all the default, including the data directory.

Server is running. I was able to create databases and access the same from java client.

But when I start psql from the same VM, it throws the following error.

Error: Invalid data directory

Just psql without any parameters also gives this error.

People have faced this error when they have tried to change the data directory which is not the case for me. Data directory is the default and it is owned by the postgres user.

ubuntu@ubuntu:~$ ls -l /var/lib/postgresql/10/
total 4
drwx------ 20 postgres postgres 4096 Jun 12 02:54 main

Any idea what could be the cause or where should I look for detailed error for psql client?

Error: Invalid data directory

This happens when pg_wrapper is unable to figure out the data directory from the configuration. pg_wrapper is the layer on top of postgres that can juggle between several live PostgreSQL instances on debian-based systems. As installed by the postgresql-client package, psql is a link to pg_wrapper on such systems:

/usr/bin/psql -> ../share/postgresql-common/pg_wrapper

Since you mention slightly modifying postgresql.conf, make sure you didn't change the permissions inside /etc/postgresql/, including postgresql.conf.
That looks like the most plausible change that would immediately lead to the mentioned error. The next one would be that your changes are syntactically wrong, in a way that pg_wrapper would no longer be able to parse the file.


SQL Server-Is It possible 'lock waits and timeouts high but average wait time does not'? I use Windows Server 2008 Standard and SQL Server 2008 version.
I always thought that if the 'Lock Waits' number was high, the 'Average Wait Time' number would be high as well.
In addition, it is a situation that even lock timeouts occur.
However, this time Lock Waits was high, but Average Wait Time was not.
Is this possible? In what cases is this possible?

This looks like you are mixing up counts and duration
lock waits/sec is a count of events per second
average wait time - is the duration of the sum of the lock waits / the number of waits in that second.
so the 2 indicators are only correlated if an increase in the number of waits leads to an equivalent increase in the duration of these waits - but there is no reason for this to be true, Also worth noting that there are many different sorts of lock within these numbers - row, page, extent, table etc


query to replace text with sequential numbers I have an images database, the default sort is the "name" column, and the "description" column content is the exact same in all files in the table.  what I need is to replace certain text in the "description" with numbers respectively.

Example of the "description" entry to all items:

"Photo number # more text text"

The query should replace the "#" symbol with sequential numbers, ordered by 'name' column.  Where the first row should be Photo number 1, and second Photo number 2.. etc.

I tried some queries with row_number (in phpmyadmin), but I am not sure if it is the right way to do that or what..  as they didn't work.

Server version: 5.7.24-0ubuntu0.16.04.1 - (Ubuntu)

Use a variable to get an auto-incremented value and update description using this value. Or use Rick James's answer and add a sequenced column to your table as a number for each picture.

create table t (name varchar(10), description varchar(100));
insert into t values ('b', '"Photo number # - Gallery name"');
insert into t values ('c', '"Photo number # - Gallery name"');
insert into t values ('a', '"Photo number # - Gallery name"');
insert into t values ('x', '"Photo number # - Gallery name"');

update t
join (select
          name, @num := @num + 1 as num
      from
          t, (select @num := 0) t
      order by
          name) t2
on t2.name = t.name
set description = replace(description, '#', cast(num as varchar(10)));

select * from t order by name;

name | description
:--- | :------------------------------
a    | "Photo number 1 - Gallery name"
b    | "Photo number 2 - Gallery name"
c    | "Photo number 3 - Gallery name"
x    | "Photo number 4 - Gallery name"

db<>fiddle here


select rows older then column DATETIME - INT value of other column in single query I have 2 columns in my table:

(DATETIME)lastsendattempt
(INT)sendgap

I know I can select all rows that have a "lastsendattempt" DATETIME which is older then the current time. But, am I able to use the value of another column to select the rows that are older than the current time - that sendgap value ?

I want it all in a single query instead of getting the sendgap value in a query and then doing another one with a datetime value

Something like...

SELECT * FROM table WHERE lastsendattempt <= (2012-04-10 01:30:15)-(INT)sendgap

If sendgap is the number of seconds, the correct syntax is this

SELECT * FROM table WHERE lastsendattempt <= (NOW() - INTERVAL sendgap SECOND);


SQL agent job for long running queries I am trying to setup an alert where the application team can get the notification on their event viewer for any queries which are running longer than 2 minutes. This is what I have so far. For some reason, the application team is not receiving the alert format which they are expecting(spid number,and the exact message text which is in the store proc) Can someone suggest what I am missing here? The time threshold is being passed as 2 minutes from the SQL Agent

create PROCEDURE [dbo].[usp_long_running_Query]
@timeThreshold time --  '00:02:00.0000000'
AS
    BEGIN
        DECLARE @tmpTime time
        DECLARE @dbname varchar(20)
        DECLARE @vmsg varchar(2000)
        DECLARE @vstatus numeric
        DECLARE @vstrstatus varchar(100)
        DECLARE @spid varchar(10)
        declare @hostName varchar(30) = CONVERT(nvarchar(30),SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))
        declare @Date nvarchar(25) = CONVERT(nvarchar(25),GETDATE())
        DECLARE @svr varchar(30) = @@SERVERNAME
    SELECT TOP 1
        right(convert(varchar,dateadd(ms, datediff(ms, P.last_batch, getdate()), '1900-01-01'), 121), 12) as 'batch_duration', DB_NAME(qt.dbid) as 'db_name',P.spid
    INTO #queryTime
    FROM master.dbo.sysprocesses P
        INNER JOIN sys.dm_exec_query_stats b
            ON p.sql_handle = b.sql_handle
        CROSS APPLY sys.dm_exec_sql_text(b.sql_handle) as qt
        LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
    WHERE P.spid > 50
        AND P.status not in ('background', 'sleeping')
        AND P.cmd not in ('AWAITING COMMAND'
                        ,'MIRROR HANDLER'
                        ,'LAZY WRITER'
                        ,'CHECKPOINT SLEEP'
                        ,'RA MANAGER')
        AND DB_NAME(qt.dbid) = 'xxx'
        AND (P.loginame like 'xyz%'
                OR P.loginame like'xyz%'
                OR P.loginame like'xyz%'
                OR P.loginame like'xyz%'
                OR P.loginame like'xyz%'
                OR P.loginame like 'Ipa%')
        --AND o.name = 'usp_OpSec_getScreeningComplianceSummaryMvmDetail'
    ORDER BY batch_duration DESC

    SET @dbname = (SELECT db_name FROM #queryTime)
    SET @tmpTime = (SELECT batch_duration FROM #queryTime)
    SET @spid = (SELECT spid FROM #queryTime)
    SET @vstatus = (SELECT db_status FROM Dxxx.dbo.TDBSTATUS WHERE db_na = @dbname)
    SET @vstrstatus=CAST(@vstatus as varchar(100))

    IF @tmpTime > @timeThreshold
        BEGIN
            SELECT @vmsg='AlertType=DBMaint Host='+@hostName+' Instance='+@svr+' Date='+@Date+' MSG= Error: 82192 The database ' + @dbname + ' has long running queries > 2 min. Please monitor this database.  The status is ' + @vstrstatus  + '.' + ' The query has been running for ' + CAST(@tmpTime as varchar(50)) + '. The spid of the longest running query is ' + @spid
            INSERT INTO Dxxx.dbo.TDBMSG(DB_NA,MSG_TXT,SEVERITY) VALUES (@dbname,@vmsg,'FATAL')

            SET @vmsg = 'AlertType=DBMaint Host='+@hostName+' Instance='+@svr+' Date='+@Date+' MSG= Error: 82192 The database ' + @dbname + ' has long running queries > 2 min. Please monitor this database.  The status is ' + @vstrstatus  + '.' + ' The query has been running for ' + CAST(@tmpTime as varchar(50)) + '. The spid of the longest running query is ' + @spid
            RAISERROR (82192,17,1,@dbname,@vstrstatus,@vmsg)with nowait
            DROP TABLE #queryTime
        END
    ELSE
        BEGIN
            DROP TABLE #queryTime
        END
END
GO

The expected alert should be something as mentioned below.  Please note DB mail is not being used, when raiseerror happens, the alert gets pushed into centralized server and from there the app team receives it in their event viewer

Error: 82192 Severity: 17 State: 1 AlertType=DBMaint Host=xxxx Instance=Cxxxxx Date=May 20 2019  9:45PM MSG= The database has long running queries > 2 min. Please monitor the database.

The way I usually go about this is to use the procedure sp_WhoIsActive, and log that to a table. This way I don't have to worry about about the result of the procedure being right, cause I'm sure it is.

There are some great articles on the Brent Ozar website about it.

Brent Ozar logging sp_WhoIsActive

Brent Ozar Monitoring activity

Afterwards you can do whatever you want with that data.
You can transfer data from the logging table to the Dxxx.dbo.TDBMSG table, or send mails directly from the WhoIsActive table.


How to get the greatest element of a GROUP BY? As you can see I would like to get the latest purchase order for each SKU.

Sample Data

I have this data set,

CREATE TABLE PurchaseOrders (
        id            int  PRIMARY KEY AUTO_INCREMENT,
        sku           varchar(6),
        purchase_date date
);

INSERT INTO PurchaseOrders VALUES
    ( 1, 'ABC123', '2017-12-23' ),
    ( 2, 'ABC123', '2016-11-11' ),
    ( 3, 'DEF456', '2011-01-03' ),
    ( 4, 'DEF456', '2011-10-21' ),
    ( 5, 'GHI789', '2017-01-23' ),
    ( 6, 'GHI789', '2017-11-21' );

Desired Result

1 | ABC123 | 2017-12-23
4 | DEF456 | 2011-10-21
6 | GHI789 | 2017-11-21

Probably easiest to do with a join against a derived table:

select a.*
from PurchaseOrders as a
join (
    select sku, max(date) as date
    from PurchaseOrders
    group by sku
) as b
   on a.sku = b.sku
   and a.date = b.date;

The derived table b contains the max dt for each sku. To get the id we join that against the original table.

As Evan Carroll points out you can shorten this a bit since the name of the join attributes is the same for both left and right operand:

select a.*
from PurchaseOrders as a
join (
    select sku, max(date) as date
    from PurchaseOrders
    group by sku
) as b
   using (sku, date);


Separate tables or partition one huge table? I'm building a rather large table that includes a particular column, my_column.  There are a small-ish number of possible values for my_column, and each unique value of my_column should have the same number of rows associated with it.  Furthermore, it is likely that queries will only be focused on a single, specific value of my_column at a time.  I see that one option would be to partition this large table based upon the values of my_column.  The other option as I see it would be to create a separate table entirely such that each table associates with exactly one value of my_column.  I'd like to know what would lead me to pick one option over the other.

As far as I understand it, one large table that is partitioned seems like the "correct" or "pure" way to do it, as that seems to be a primary use-case for partitions.  However, I suspect that for practical reasons the second option is better, and that's what I'm leaning towards.  I don't know if it matters, but I'm dealing with MyISAM tables.

I have an suggestion regarding your second option.

If you need to keep the MyISAM tables with each unique my_column value separated from other MyISAM tables, you may want to look into the MERGE (Mrg_MyISAM) Storage Engine as an alternative to table partitioning.

This will allow your multiple MyISAM tables within the same database that have identical table structures and index layout to be mapped together in such a way that single query hits all MyISAM tables.

Suppose you have a table as follows:

CREATE TABLE tb1
(
    my_column INT NOT NULL
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(30),
    PRIMARY KEY (my_column,id),
    KEY name (my_column,name)
) ENGINE=MyISAM;

and you have 3 such tables (tb1, tb2, tb3) where

my_column for tb1 = 1
my_column for tb2 = 2
my_column for tb3 = 3

You can merge them under a single table mapping as follows:

CREATE TABLE tbmerge LIKE tb1;
ALTER TABLE tbmerge ENGINE=Mrg_MyISAM UNION=(tb1,tb2,tb3);

To perform a search through all the tables, just use tbmerge. For example, suppose you want to see every name from tb2 that starts with 'Jack', you run this query:

SELECT name FROM tbmerge WHERE my_column=2 AND name LIKE 'Jack%';

Given the design of the table, you should always specify the value for my_column. In fact, for every index tb1 has, make sure my_column is always the first column. The reason? A query against tbmerge is always a query against tb1, tb2, tb3 (all underlying tables). Otherwise, this query

SELECT name FROM tbmerge WHERE name LIKE 'Jack%';

will experience horrible performance because it will perform table scans against all underlying tables. Please plan carefully the indexes you will be using, following that simple rule (using my_column as the first column of every index)

There is an additional benefit: You can INSERT into the underlying tables at your convenience, thus working with the

I discussed this using MERGE tables in an earlier post (Jan 4, 2012).


Error when creating an index in SQL Server 2012 I'm trying to create the following index on our test environment (SQL Server 2012):

CREATE NONCLUSTERED INDEX [iTransaction-OriginatingTransaction] ON [dbo].[Transaction]
(
    [OriginatingTransactionID_EOT] ASC,
    [OriginatingTransactionID] ASC
)

on our transaction table:

CREATE TABLE [dbo].[Transaction](
    [TransactionID] [int] IDENTITY(1,1) NOT NULL,
    [OriginatingTransactionID_EOT] [varchar](50) NULL,
    [OriginatingTransactionID] [int] NULL,
    [Status] [int] NOT NULL,
    [BusinessID] [int] NOT NULL,
    [CreatedUserID] [int] NOT NULL,
    [CreatedDateTime] [datetime] NOT NULL,
    [ModifiedUserID] [int] NOT NULL,
    [ModifiedDateTime] [datetime] NOT NULL,
 CONSTRAINT [PK_Transaction] PRIMARY KEY CLUSTERED
(
    [TransactionID] ASC
)

I get the following error:

  Msg 681, Level 16, State 3, Line 2
  Attempting to set a non-NULL-able column's value to NULL.

If I run the same create index statement on our live database (SQL Server 2005) then it works without any errors.

Why can't I create this index on SQL Server 2012?

UPDATE: Results of dbcc checktable:

  Msg 8944, Level 16, State 13, Line 1
  Table error: Object ID 954995248, index ID 1, partition ID 72057594796965888, alloc unit ID 72057595543093248 (type In-row data), page (1:701352), row 13. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 7198 and 186.
  Msg 8944, Level 16, State 13, Line 1
  Table error: Object ID 954995248, index ID 1, partition ID 72057594796965888, alloc unit ID 72057595543093248 (type In-row data), page (1:701352), row 13. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 7198 and 186.
  Msg 8928, Level 16, State 1, Line 1
  Object ID 954995248, index ID 1, partition ID 72057594796965888, alloc unit ID 72057595543093248 (type In-row data): Page (1:701352) could not be processed.  See other errors for details.
  Msg 8976, Level 16, State 1, Line 1
  Table error: Object ID 954995248, index ID 1, partition ID 72057594796965888, alloc unit ID 72057595543093248 (type In-row data). Page (1:701352) was not seen in the scan although its parent (1:709848) and previous (1:700903) refer to it. Check any previous errors.
  Msg 8978, Level 16, State 1, Line 1
  Table error: Object ID 954995248, index ID 1, partition ID 72057594796965888, alloc unit ID 72057595543093248 (type In-row data). Page (1:701353) is missing a reference from previous page (1:701352). Possible chain linkage problem.
  DBCC results for 'Transaction'.
  There are 2354636 rows in 57941 pages for object "Transaction".
  CHECKTABLE found 0 allocation errors and 5 consistency errors in table 'Transaction' (object ID 954995248).
  repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (Redebiz.dbo.Transaction).
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The problem was with data corruption in the table. Once the issues found in dbcc checktable were resolved (in this case we deleted the affected rows as it's only test data) the index was able to be created.


What is the easiest way to start a mongodb copy server? Our mongodb server is running on AWS EC2 - it is using up a lot of the cpus of this server - which is also our Apache server - How Can I easily migrate the mongodb to another EC2 dedicated server?

If you want to migrate with minimal downtime, a straightforward approach would be to use MongoDB replication:

Convert your current standalone node to a replica set by restarting your current mongod with a --replSet name and then running rs.initiate(). NOTE: The rs.initiate() step is a once off command that only needs to be run on a primary when the replica set is first created.
If all went well, you should now have a single node replica set with your former standalone node as the primary (check rs.status() and rs.conf() in the mongo shell).
Create your new EC2 instance and start up a new mongod process as a member of the same replica set (i.e. including the same --replSet name in the configuration). To avoid any unexpected issues, the new node should be running the same version of MongoDB as your original standalone server.
Connect to your primary via the mongo shell and add the new mongod process as a secondary node:

rs.add("mynewec2:27017")

You should now have a two node replica set with a primary node and secondary node (which may be in a "startup" or "recovering" phase while data is syncing).
NOTE: For a production MongoDB replica set configuration you would normally want to have a third node to maintain a quorum. This can either be a secondary or an arbiter, which is a voting-only node that does not hold any data and only participates in elections. For the purposes of your migration a third node is optional, but recommended in the event there are any problems with the secondary while the data is being synced. With a two node replica set, both nodes must be online in order to maintain a primary. A three node replica set allows for fault tolerance of 1 node failure.
Assuming there were no firewall or communication errors, the node you added as a secondary should begin to sync data over the network from your original (primary node). Note: there are alternative ways to sync a replica set member (for example starting from a file copy backup).
You can follow the sync progress using db.printSlaveReplicationInfo() on the primary.
Once your secondary node has caught up with the current primary (or is within 10s of the oplog time of the primary) you can use rs.stepDown(60) to force the current primary to stepdown. This should cause your secondary (newly provisioned host) to become the primary.
At this point the migration is complete: you can now shutdown your original mongod host, and restart the new mongod in standalone mode.

To minimise the impact to application writes during this period, you can either:

change your application to use a replica set connection so writes will automatically change to the current primary
coordinate changing your application connection string with the stepdown of the (former) standalone primary


In SQL server during Log Shippping which portion (active\Inactive) of the Log file (.ldf) backup is taken and further restored in SQL Server I am confused, like if we take the backup of the whole log file, wont it clear the transaction log though we have copy only backup.

In log file we have 2 parts, like active and inactive logs, in inactive log all the log records are present, who transaction is already committed.

So we take backup of which portion of the log file.

Thanks In Advance.

I have difficulties understanding your question. Perhaps you can clarify or re-prase? Anyhow:

A regular log backup will clear the contents of the ldf file (a bit simplified). Using COPY_ONLY for the log backup will not clear the contents.

The .tuf file is not related to above. When you restore a log backup, you can specify that you can restore further backup, but still be able to SELECT in the database. This is the STANDBY option. SQL Server need to keep track of some things on this scenario, for the next restore, and this is the .tuf file.


howto choose the right character set in MariaDB if there are multiple character sets that can properly encode my data, how to choose one?

Is there any difference in disk or memory usage?
When joining tables do they need the same char set?

Other toughs?

Choosing character set usually based on the environment, each set has pros and cons. for example if your system has mixed character I strongly recommend utf8 but even utf8 has many types. for example: utf8_general_ci is faster than utf8_unicode_ci but less accurate for sorting. and if you want to use specific language Czech for example then you need to make the character set utf8_czech_ci to get full language function.

I found the following URLs regarding encoding hope they will help:
http://dev.mysql.com/doc/refman/5.0/en/charset-charsets.html
http://dev.mysql.com/doc/refman/5.0/en/charset-unicode-sets.html
http://code.openark.org/blog/mysql/mysqls-character-sets-and-collations-demystified

the following URL contains some examples about disk usage for character set:

http://spin.atomicobject.com/2010/08/23/on-the-importance-of-character-sets-and-character-encodings-in-mysql/


Using newly inserted values in trigger function in PostgreSQL I have this table:

CREATE TABLE accounts (
  id BIGINT NOT NULL UNIQUE,
  balance INTEGER DEFAULT 0
);

And I need to update each user's balance after inserting rows to 2 different tables, transfers and deposits.

I've created 2 functions to calculate new user balances and updating it:

/* function for getting user balance */
CREATE FUNCTION get_balance(bigint) RETURNS bigint
    AS 'SELECT (
  SELECT COALESCE(sum(CASE WHEN won THEN amount * (multiplier - 1) ELSE -amount END), 0)
  FROM transfers
  WHERE user_id = $1
) + (
  SELECT COALESCE(sum(amount), 0)
  FROM deposits
  WHERE user_id = $1
) as sum;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

/* function for updating user balance */
CREATE FUNCTION upsert_balance(bigint) RETURNS VOID
    AS 'INSERT INTO ACCOUNTS (id, balance)
    VALUES ($1, get_balance($1))
  ON CONFLICT (id) DO
  UPDATE SET balance = get_balance($1);'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

But I'm not sure how to use the newly inserted values in a trigger callback, because I need to pass the id as an argument to the upsert_balance function.

How can I accomplish this?

EDIT: I fixed it, thanks to Adam's answer, here is the working version in case anyone needs it:

CREATE FUNCTION upsert_balance() RETURNS trigger AS $trigger_bound$
BEGIN
    INSERT INTO ACCOUNTS (id, balance)
    VALUES (NEW.user_id, get_balance(NEW.user_id))
    ON CONFLICT (id) DO
    UPDATE SET balance = get_balance(NEW.user_id);

    RETURN NEW;
END;
$trigger_bound$
LANGUAGE plpgsql;

CREATE TRIGGER update_balance_on_inserting_transfer
AFTER INSERT OR UPDATE ON transfers
FOR EACH ROW
EXECUTE PROCEDURE upsert_balance();

Within the trigger function you can use NEW.column_name to refer to the newly inserted / updated value.  In the reverse, OLD.column_name will refer to the value prior to the update / delete.

More info here: https://www.postgresql.org/docs/current/static/sql-createtrigger.html

Hope that helps.


Grouping data by MONTH on DATETIME column in SQL Server I have a table Employee which has the columns Id, Date of joining and Name

Date of joining is a DATETIME column.

I want to know how many users have joined in the month of October?

If you're just looking for October regardless of the year, then the easy way would be to just COUNT the records from that month:

SELECT COUNT(*) AS NumberOfJoiners FROM Employee WHERE MONTH(DateOfJoining) = 10;

or

SELECT COUNT(*) AS NumberOfJoiners FROM Employee WHERE DATEPART(MONTH, DateOfJoining) = 10;

If you want to group by year, then you'll need a group by clause, otherwise October 2013, 2014, 2015 etc would just get grouped into one row:

SELECT
  DATEPART(YEAR, DateOfJoining) AS YearOf Joining,
  COUNT(*) AS NumberOfJoiners
FROM Employee
WHERE DATEPART(MONTH, DateOfJoining) = 10
GROUP BY DATEPART(YEAR, DateOfJoining);


what does SHOW /*!50000 GLOBAL */ STATUS do ? What is the difference between

SHOW /*!50000 GLOBAL */ STATUS

and

SHOW GLOBAL STATUS

I saw that in few scripts and tuning presentations the first command is being used. Is there a difference, If there is, what does the 50000 signify ?

Also, this seems to be a good script : https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl
Are there any similar scripts that list important stats and display recommendations

The "50000" refers to a MySQL version -- it means that only version 5.0.0 and above should pay attention to that command.  5.0.0+ will see SHOW GLOBAL STATUS, while earlier versions will see just SHOW STATUS.

Example:

mysql> /*!50511 select 1 */;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> /*!50512 select 1 */;
Query OK, 0 rows affected (0.00 sec)

(I have MySQL 5.5.11 -- i.e. 5.05.11).


Differentiate SQL Server Express from SQL Server installed in a server I own a VPS server and I was told SQL Server 2008 R2 Express edition was installed on Windows OS 2008R2.

When I opened the SQL Server configuration manager I saw this:

I thought I should see only the SQL Server Express but there is also SQL Server (MSSQLServer). Does that mean both SQL Server Express and SQL Server of different version are installed? If so how will I know which version of SQL Server and SQL Server Express are installed ?
I also see SQL Server full text filter daemon service running which means full text is installed but unfortunately when I right click the table I see the full text index is disabled.

I am confused as to what is installed and what is not installed. Can some one throw a light on this one

When I connect to the default instance with servername as .
I get :

  Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30
  Copyright (c) Microsoft Corporation
  Express Edition (64-bit) on Windows NT 6.1
  (Build 7601: Service Pack 1) (Hypervisor)

When I connect to the instance with servername as machinename\sqlexpress
I get:

  Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30
  Copyright (c) Microsoft Corporation
  Express Edition with Advanced Services (64-bit) on Windows NT 6.1
  (Build 7601: Service Pack 1) (Hypervisor)

The MSSQLSERVER name in the services does not mean anything other than SQL Server (any edition) was installed as the default (unnamed) instance. It does NOT give you any information as to what version or edition it is! This can be SQL Server Express!

You need to connect to that database instance and run

SELECT @@VERSION

to find out what exact version/edition it is.

Having the two SQL Server service entries just means you have two instances - which edition each instance is cannot be determined from their names alone. OK - SQLEXPRESS is the default name used for SQL Server Express - but you could install a Standard or Enterprise edition with that instance name - just for fun.


Measure Agent Job failure and running jobs with 'execution_status' Considerations

I need to create a Dashboard with job failure AND running jobs.
I have a code to get run_status etc.
I am not interested in succeed jobs.
I extract failure from run_status = 0.

Query as of today:

select
   j.name as 'JobName',run_date, run_time, run_status,
   msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
   run_duration,
   ((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100
+ 31 ) / 60) as 'RunDurationMinutes',
   DateADD(MINUTE,((run_duration/10000*3600 + (run_duration/100)%100*60 +
run_duration%100 + 31 ) / 60) ,
   msdb.dbo.agent_datetime(run_date, run_time)) as RunStopDate,
   step_id, Step_name
From
   msdb.dbo.sysjobs j
INNER JOIN
   msdb.dbo.sysjobhistory h
ON
   j.job_id = h.job_id
where
   j.enabled = 1  --Only Enabled Jobs
and
   msdb.dbo.agent_datetime(run_date, run_time)  > DATEADD(DAY,-90, GETDATE())

How and where do I add a case to below query? I want all my columns but also a new execeution_status as below.
I also assume I can get from run_duration the amount an on-going job has run so far.

    else
    CASE h.execution_status
    WHEN j.[execution_status] = 0 THEN ' Returns only those jobs that are not
    idle or suspended. '
    WHEN j.[execution_status] = 1 THEN 'Execution'
    WHEN j.[execution_status] = 2 THEN ' Waiting for thread '
    WHEN j.[execution_status] = 3 THEN ' Between retries '
    WHEN j.[execution_status] = 4 THEN 'Idle'
    WHEN j.[execution_status] = 5 THEN 'Suspenden'
    WHEN j.[execution_status] = 7 THEN 'Performing complet actions'
    End
    AS ExecutionStatus,

Source execution_status: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-help-job-transact-sql.

We did came up with this simple solution, and I want some feedback about pros and cons.

    SELECT
    ja.session_id,
    ja.job_id,
    j.name AS job_name,
            CASE
        WHEN ja.start_execution_date IS NULL THEN 'Not running'
        WHEN ja.start_execution_date IS NOT NULL AND ja.stop_execution_date
    IS NULL THEN 'Running'
        WHEN ja.start_execution_date IS NOT NULL AND ja.stop_execution_date
    IS NOT NULL THEN 'Finished'
    END AS 'RunStatus'  ,
    ja.last_executed_step_id,
    jh.step_name,
    jh.run_status,
    ja.start_execution_date,
    ja.stop_execution_date,
    ja.job_history_id,
    j.[enabled]
    FROM
    (msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON
    ja.job_history_id = jh.instance_id)
     join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id
            WHERE session_id = (SELECT MAX(session_id) FROM
     msdb.dbo.sysjobactivity)
            and j.enabled =1

If you're willing to use an undocumented and, therefore, unsupported extended stored procedure, you can get the current execution stats from msdb.dbo.xp_sqlagent_enum_jobs.

I use the following to get details about running jobs:

DECLARE @can_see_all_running_jobs int = 1;
DECLARE @job_owner sysname = 'sa';
DECLARE @job_id uniqueidentifier;

DECLARE @job_states TABLE
(
    job_state_id int NOT NULL
    , job_state_desc varchar(30) NOT NULL
);

INSERT INTO @job_states (job_state_id, job_state_desc)
VALUES (0, 'Not idle or suspended')
    , (1, 'Executing')
    , (2, 'Waiting for Thread')
    , (3, 'Between Retries')
    , (4, 'Idle')
    , (5, 'Suspended')
    , (6, 'Waiting for Step to Finish')
    , (7, 'Performing Completion Actions');

DECLARE @xp_results TABLE
(
      job_id                UNIQUEIDENTIFIER NOT NULL
    , last_run_date         INT              NOT NULL
    , last_run_time         INT              NOT NULL
    , next_run_date         INT              NOT NULL
    , next_run_time         INT              NOT NULL
    , next_run_schedule_id  INT              NOT NULL
    , requested_to_run      INT              NOT NULL
    , request_source        INT              NOT NULL
    , request_source_id     sysname          COLLATE database_default NULL
    , running               INT              NOT NULL
    , current_step          INT              NOT NULL
    , current_retry_attempt INT              NOT NULL
    , job_state             INT              NOT NULL
);
INSERT INTO @xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs
    @can_see_all_running_jobs
    , @job_owner
    , @job_id;

SELECT
    JobName = j.name
    , states.job_state_desc
    , LastRunDateTime = CASE
        WHEN COALESCE(xr.last_run_date, 0) > 0
            THEN msdb.dbo.agent_datetime(xr.last_run_date, xr.last_run_time)
        ELSE NULL
        END
    , xr.current_step
    , step_name = (
        SELECT '' + sjs.step_name
        FROM dbo.sysjobsteps sjs
        WHERE sjs.job_id = j.job_id
            AND sjs.step_id = xr.current_step
        FOR XML PATH ('')
        )
FROM msdb.dbo.sysjobs j
    INNER JOIN @xp_results xr ON j.job_id = xr.job_id
    LEFT JOIN @job_states states on xr.job_state = states.job_state_id
WHERE j.enabled = 1 /* enabled jobs only */
    AND xr.running = 1 /* running jobs only */
ORDER BY j.name;

This shows results like:

╔══════════╦════════════════╦═════════════════════════╦══════════════╦═══════════╗
║ JobName  ║ job_state_desc ║     LastRunDateTime     ║ current_step ║ step_name ║
╠══════════╬════════════════╬═════════════════════════╬══════════════╬═══════════╣
║ Test Job ║ Executing      ║ 2018-04-10 12:40:09.000 ║            1 ║ Step 1    ║
╚══════════╩════════════════╩═════════════════════════╩══════════════╩═══════════╝

Items of note:

The "last run date" columns only reflect the time the job was last started.  i.e. this does NOT indicate the start of the job if the job is currently running.  There appears to be no way to get that data from objects contained in msdb.  I do have a way to get those details from sys.dm_exec_sessions, etc, as in:

/*
      Shows the progress of any running SQL Agent jobs
*/
DECLARE @Actions TABLE
(
      ActionID INT
      , ActionText VARCHAR(50)
);
INSERT INTO @Actions (ActionID, ActionText)
VALUES ( 1,'Quit with success')

INSERT INTO @Actions (ActionID, ActionText)
VALUES (2,'Quit with failure')

INSERT INTO @Actions (ActionID, ActionText)
VALUES (3,'Go to next step')

INSERT INTO @Actions (ActionID, ActionText)
VALUES (4, 'Go to step on_success_step_id');

SELECT [Job Name] = sj.name
      , [Step Name] = sjs.step_name
      , SQLStatement = SUBSTRING(t.text, ISNULL(r.statement_start_offset / 2 + 1,0), CASE WHEN ISNULL(r.statement_end_offset, 0) = -1 THEN LEN(t.text) ELSE ISNULL(r.statement_end_offset / 2, 0) END - ISNULL(r.statement_start_offset / 2, 0))
      , [On Success Action] = ASuccess.ActionText
      , [On Fail Action] = AFail.ActionText
      , r.session_id
      , r.blocking_session_id
      , [Estimated Completion] = CASE WHEN r.estimated_completion_time = 0 THEN 'UNKNOWN' ELSE CONVERT(VARCHAR(50), DATEADD(MILLISECOND, r.estimated_completion_time, GETDATE()), 120) END
      , Duration = DATEDIFF(MINUTE, r.start_time, GETDATE())
      , r.last_wait_type
      , r.start_time
      , s.host_name
      , SQLText = t.text
      , s.program_name
FROM sys.dm_exec_sessions s WITH (NOLOCK)
      LEFT JOIN sys.dm_exec_requests r WITH (NOLOCK)on s.session_id = r.session_id
      CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
      LEFT JOIN msdb.dbo.sysjobsteps sjs WITH (NOLOCK) ON SUBSTRING(s.[program_name],30,34) = master.dbo.fn_varbintohexstr(sjs.job_id) AND SUBSTRING(s.[program_name], 72, LEN(s.[program_name]) - 72) = SJS.step_id
      LEFT JOIN msdb.dbo.sysjobs sj (NOLOCK) ON sjs.job_id = sj.job_id
      LEFT JOIN @Actions AFail ON sjs.on_fail_action = AFail.ActionID
      LEFT JOIN @Actions ASuccess ON sjs.on_success_action = ASuccess.ActionID
WHERE s.program_name like 'SQLAgent - TSQL%';

Limiting selected jobs with msdb.dbo.agent_datetime(run_date, run_time)  > DATEADD(DAY,-90, GETDATE()) in the WHERE clause means currently running jobs that have never been ran before will not show up, and will return an error for jobs that have never been ran.

I've tied all the above details into a single package, and made it available on my blog.


How to partially merge a row in Postgres? I have 3 tables:

Customers:      Items:    Purchases:
  id              id        id
  first_name      name      customer_id
  last_name       price     item_id

I need to present data in a way that a can see all the purchases of a customer, per item, the amount (sum) of each item and the total price of this amount(again, per item), like this:

Name:   | Last Name:  | Item:     | Price:  | Amount: | Total:
John    | Doe         | Pencil    | 0.5     | 5       | 2.5
John    | Doe         | Paper(A4) | 3.0     | 20      | 60.00
Michael | Smith       | Pencil    | 0.5     | 3       | 1.5
Michael | Smith       | Paper(A4) | 3.0     | 10      | 30.00

I manage to do that with this query:

SELECT
  customers.first_name AS "Name",
  customers.last_name AS "Last Name",
  items.name AS "Item",
  items.price AS "Price",
  COUNT(items.id) AS "Amount",
  SUM(items.price) AS "Total"
FROM
  items
INNER JOIN
  purchases ON items.id = purchases.item_id
INNER JOIN
    customers ON purchases.customer_id = customers.id
GROUP BY
  customers.id, items.id
ORDER BY customers.last_name

Is there a way to do it like you would in Excel, for example, where you can merger those cells that have the first and last name, so the customer name is listed only once? I now that it is not the most relevant "problem", but I have to do this twice a week in a meeting with 5 other people and it would be great to improve this layout.

You can simple use lag window function to get the value of the previous row and compare with the current one, then you use CASE to conditionally format as you please. Mapping the "merged" values as NULL (so it can work for any type), it would be:

SELECT
  CASE
  WHEN customers.id = lag(customers.id) OVER w_ordered THEN
    NULL
  ELSE
    customers.first_name
  END AS "Name",
  CASE
  WHEN customers.id = lag(customers.id) OVER w_ordered THEN
    NULL
  ELSE
    customers.last_name
  END AS "Last Name",
  items.name AS "Item",
  items.price AS "Price",
  COUNT(items.id) AS "Amount",
  SUM(items.price) AS "Total"
FROM
  items
INNER JOIN
  purchases ON items.id = purchases.item_id
INNER JOIN
    customers ON purchases.customer_id = customers.id
GROUP BY
  customers.id, items.id
WINDOW
  w_ordered AS (ORDER BY customers.last_name)
ORDER BY customers.last_name

Notice that I used the same ORDER BY clause in the window function as the full query, this is really important because you do want to verify the "merging" following the same rule.

Now, this is not a presentation mode, as in SQL you only have a table as a result, if you wish to do a real row merging in your presentation layer, you can use another column to just inform if it should merge the rows or not (although it shouldn't be hard to do the full logic there).


Optimal value for LOG_BUFFER parameter The wait event “log buffer space” according to the documentation can indicate that the log buffer is too small or the I/O is slow.  We have a system seeing this event in which the redo buffer allocation retries is going up in V$SYSSTAT as observed with...

SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'redo buffer allocation retries';

We recently increased the LOG_BUFFER value from 8 MB to 16 MB with only a small reduction in the retries.  It is still increasing at about 1,000 a day.

Should the LOG_BUFFER value be increased further?

Since the system is 64 bit I can take it up to 256 MB, but I don’t want to use the memory for this buffer if it won’t be beneficial and I don’t know if there are any downsides to increasing this value that significantly.

Take it all the way up. FWIW I have systems where 500m and even 1g ORLs are normal. Don't be afraid of this on modern hardware - set archive_lag_target to give you a reasonable log switch (I go for 230 seconds to keep it off 5-minute boundaries).


Memory limitations on db2 express-c version 10.1 seems stuck at 2 gig, why? I just updated from 9.7 to 10.1, to leverage the new 4gig memory limitation.  However, my app only appears to be using 2.  So i searched for a way to find out how memory is used and found this command:

D:\DB2\scripts>db2pd -dbptnmem

Database Member 0 -- Active -- Up 0 days 04:40:54 -- Date 09/16/2012 14:37:08

Database Member Memory Controller Statistics

Controller Automatic: N
Memory Limit:         2097152 KB
Current usage:        2057664 KB
HWM usage:            2096960 KB
Cached memory:        390464 KB

Individual Memory Consumers:

Name             Mem Used (KB) HWM Used (KB) Cached (KB)
========================================================
APPL-DOMINOS              2752        160000        1024
DBMS-DB2                109888        168832           0
FMP_RESOURCES            22528         22528           0
PRIVATE                  20480         86656        9344
DB-MYAPPLI             1901760       1901760      380096
LCL-p5088                  128           128           0
LCL-p5088                  128           128           0

The line that really stands out is

  Memory Limit:         2097152 KB

What does this mean?  Why does my app not use 4 gig?  How can I change it so that it does?  Where does one configure this?

Found the answer in the doc (I should have RTM). You check the setting using:

db2 get dbm cfg

And looking for INSTANCE_MEMORY, then update with:

db2 update dbm cfg using INSTANCE_MEMORY <memoryDividedBy4KB>


SQL Profiler Shows Update (EventClass) with No Errors, But Data in Table is Not Changed I am trying to debug a web app that uses a database and I need a little help.  The problem is that part of the code throws a timeout error.  I started using SQL Server Profiler to check the communication between the app and the database.  (I am not really a database expert and this is a new tool for me).

The app is supposed to call an UPDATE on a specific record.  Shortly after, it calls a SELECT on the same record (because it refreshes the screen).  I can see both the UPDATE and the SELECT event classes in the Profiler trace.  (There are a few other event classes between them).  The SELECT is showing with a duration of 30499 and an Error of 2 - Abort.  I am guessing this is the source of my timeout error.

This is where it gets interesting.  By selecting the EventClass in the Profiler trace, I can see the SQL SELECT statement that is being called.  I copied and pasted this into a new query in Management Studio and tried it out.  The SELECT statement worked fine and returned the record before my finger left the f5 key.  However, the data in the record was the old data.  It seems that the previous UPDATE call did not change the record.

Looking at the Profiler trace again, the UPDATE event class shows an Error of 0 - OK.  When I select the UPDATE event class and copy the SQL code into Management Studio it runs fine and does update the record.  Is anyone able to help me work out what is going on please.

If the UPDATE statement is shown in Profiler with no error, then does this mean that it should have updated the database?
What could be causing the SELECT statement to abort (considering that it runs fine when I try it in SQL Server Management Studio)?
Could problems with the UPDATE be causing the record to lock up which stop the SELECT from running?

Any advice to help me debug and fix this would be appreciated.

Managed to fix this problem.  Turns out that the data access handler in the application code should have called commit after the UPDATE call, but this was missing.

Based on my limited understanding, the access handler (based on Interop.COMSVCSLib) is using a buffer(?) built in to SQL Server.  For some reason, I expected this buffer to be external to the database.

Thus SQL Server Profiler was capturing the UPDATE call even though the update was not being committed.  When the SELECT was called, presumably SQL Server was still waiting for the update to be committed and so caused the timeout.  When the UPDATE failed to be committed then SQL Server caused the data to be thrown away and the old data was preserved.

(If someone wants to write a more detailed answer that can help me understand this process better then I will mark it as the answer, since I don't really like answering my own questions).


How to undo DROP TABLE statement? I’ve accidentally executed DROP TABLE statement on a wrong table. I have backups that are several days old that I can use to recover table and most of the data but not all.

I know that SQL Server can’t rollback already committed transaction but I was wondering if there are any alternative ways to get the rest of the data back. Any help such as some uncommon sql scrpts or anything like that would be greatly appreciated.

When you drop table data is still in the database but SQL Server is treating it as free space and it will overwrite it soon. You need to stop all database activities or make a copy of mdf and ldf files and see if you can find a third party tool that supports this. One tool I know is ApexSQL Recover but I guess there are probably others out there.


How to join values from multiple rows in a single column? I'm working on a project to find the association rule of items for Market dataset. I have a huge database that contains multi-tables. The output I got from this query

select sales_fact_1997.customer_id, sales_fact_1997.product_id,
product.product_name
from sales_fact_1997 , product
where sales_fact_1997.product_id = product.product_id
group by sales_fact_1997.customer_id , sales_fact_1997.product_id
order by sales_fact_1997.customer_id, product.product_name;

is like that:

customer_id     product_id      product_name
13               1466           Hot Dogs
13               937            American Cole Slaw
13               10             Akron City Map
...

Is there a way to get the output in this format:

customer_id     product_id      product_name
13              1466,937,10     Hot Dogs, American Cole Slaw, Akron City Map

SELECT customer_id,
       GROUP_CONCAT(product_id) AS product_ids,
       ...
    FROM tbl
    GROUP BY customer_id;


Saving SQL Server DMV's for later use Is it possible to save DMV's in SQL server database backup, which can be restored on different server, I mean with master db details( views and all)? If not, what's the best way to maintain 2 environment( one for Performance Testing and replica of it for analysis), so that database which has been used for testing can be analysed by team for optimizing queries without stopping further test execution window.

The DMV's themselves are the same between same builds of SQLServer and give you a snapshot or a cumulative counter of the status of your server at any given time.

You cannot restore the database to a new server with the metadata from the old one. You can make snapshots of some of the performance metrics into a table but you cant restore the state of the DMV's. You can however grab the query plans from your production server and use plan guides to have the same behavior in test, if you want to mimic specific behavior of a single query that is not behaving in production environment but runs ok in test.

You can then of course use the Management Data Warehouse to collect information about performance of queries and the system to find which queries to optimize or third party tools that can collect query plans and wait statistics on the server. Here is a way to collect the query plans using sp_whoisactive


How to optimize information schema table in mysql? When I checked slow running queries log, I found out that

select count(*) into @discard from information_schema.PARTITIONS;

This query is taking 6 seconds to execute (a bit higher for this execution). I am not really sure about usage of this query and would look advise on optimizing it.

Information Schema is not like other databases:

  They are actually views, not base tables, so there are no files
  associated with them, and you cannot set triggers on them. Also, there
  is no database directory with that name.

source - https://dev.mysql.com/doc/refman/5.7/en/information-schema.html

and not a lot of ways for optimise queries over it
there are few recommendations (very standard for any queries)

https://dev.mysql.com/doc/refman/5.7/en/information-schema-optimization.html

recommendations is really standard and in Your case - with count(*) not help.


Easily, quickly replace MySQL table? We have a process which will regenerate a table from scratch, drop the original table, and move the new table into the original's place. Now this should happen very quickly, but sometimes a website request hits the table after it's been dropped but before the new one is renamed. Apart from coding the website to be more robust when there's a database error, is there an easier way to do this?

The RENAME TABLE syntax allows for atomic, multiple renames. See as follows:

CREATE TABLE myschema.mytable_new LIKE myschema.mytable;
RENAME TABLE myschema.mytable TO myschema.mytable_old, myschema.mytable_new to myschema.mytable;
DROP TABLE myschema.mytable_old;

In the above it is impossible for your code to access the table during the RENAME operation, and this it cannot happen that the code "does not find the table". It just blocks until RENAME operation completes, and the table is always there.

Also worth noting that unless using Percona Server with "fast DROP TABLE patch", DROPping a table might hold locks on your database (namely the table cache) for the duration of the operation. And the operation may take a while since it involves evacuating table pages from InnoDB's cache and deleting the table file(s) from the filesystem.

Waiting for some time (eg a couple hours) after the RENAME operation can do good since your old-table's pages are more likely to be flushed out by that time, reducing the overall time from DROPping the table.


Permission to create Spatial Index Permissions required to create a Spatial Index on a table, for this BOL says:

  Requires ALTER permission on the table or view. User must be a member
  of the sysadmin fixed server role or the db_ddladmin and db_owner
  fixed database roles.

However, I find that the Alter permission alone is sufficient to create a Spatial Index on a table. (without having server Sysadmin rights)

The test I have created below: (run each batch to find out what is happening)

USE adventureworks;
GO
/* Start these scripts with a priveledged account */

CREATE TABLE employees
(employeedid INT PRIMARY KEY IDENTITY(1,1),
empaddress GEOGRAPHY
)
INSERT INTO employees VALUES(geography::STGeomFromText('LINESTRING(47.656 -12.360, 47.656 -12.343)', 4326))
GO

/* Create our test SQL account with limited access = no securables yet */
CREATE LOGIN LimitedPerms WITH Password = 'pass';
CREATE USER LimitedPerms FOR LOGIN LimitedPerms;
GO

EXEC AS USER='LimitedPerms';
SELECT SUSER_NAME() AS 'CurrentlyLoggedInUser'
GO

/* Should give an error = does not have any rights yet */
CREATE SPATIAL INDEX emp_spatIx on employees(empaddress);
GO

REVERT;
SELECT SUSER_NAME() AS 'CurrentlyLoggedInUser';
GO

/* Give alter permission on table: employees to our test user */
GRANT ALTER ON OBJECT::employees to LimitedPerms;
GO

EXEC AS USER='LimitedPerms';
SELECT SUSER_NAME() AS 'CurrentlyLoggedInUser';
GO

/* Allows user to create index without being a sysadmin! */
CREATE SPATIAL INDEX emp_spatIx on employees(empaddress);
GO

REVERT;
GO

Anyone care to test and report back? or inform why documented like this?

Thanks

EDIT1: This was tested on SQL Server 2008 Enterprise Edition

EDIT2: I have put feedback on Microsoft Connect

Microsoft connect ticket was resolved with wording changed:

Please refer to link below (changes have not been made on BOL yet, 1 dec)

https://connect.microsoft.com/SQLServer/feedback/details/706766/books-online-suggested-permission-to-create-spatial-index-incorrect


SELECT JOIN same table prevent duplicates MySQL 8.x I have a table called 'price' with the following rows
idRequest | idPair | idExchangePlatform | lastPrice
    1         1               1               95
    1         1               2               100
    1         1               3               40
    2         1               1               80
    2         1               2               250
    2         1               3               10
    3         1               1               110
    3         1               2               300
    3         1               3               60
    1         2               2               800
    1         2               3               1300
    2         2               2               1110
    2         2               3               1950

i need to make a SELECT QUERY WHERE idRequest and exchangePair match with the same table values but different idExchangePlatform
For example for this set of values:
idRequest | idPair | idExchangePlatform | lastPrice
    1         1               1               95
    1         1               2               100
    1         1               3               40

i need to get the following output:
idRequest | idPair | idEP_a | idEP_b | lastPrice_sum
    1         1         1       2           195
    1         1         1       3           135
    1         1         2       3           140

i'm using this query but it duplicates the result because is matching the idExchangePlatform in twisted order.
select p1.idRequest, p1.idPair, p1.idExchangePlatform AS idEP_a, p2.idExchangePlatform AS idEP_b, (p1.lastPrice + p2.lastPrice) AS lastPrice_sum
from price as p1
JOIN price AS p2
ON p1.idRequest = p2.idRequest AND p1.idPair = p2.idPair AND p1.idExchangePlatform <> p2.idExchangePatform;

This is the output i get from this query:
idRequest | idPair | idEP_a | idEP_b | lastPrice_sum
    1         1         1       2           195
    1         1         1       3           135
    1         1         2       3           140
    1         1         2       1           195
    1         1         3       1           135
    1         1         3       2           140

Is there a way to prevent this with some CLAUSE?

You can use GROUP BY
select p1.idRequest, p1.idPair, p1.idExchangePlatform AS idEP_a, p2.idExchangePlatform AS idEP_b, (p1.lastPrice + p2.lastPrice) AS lastPrice_sum
from price as p1
JOIN price AS p2 ON (p1.idRequest = p2.idRequest AND p1.idPair = p2.idPair AND p1.idExchangePlatform <> p2.idExchangePlatform)
GROUP BY p1.idRequest,p1.idPair, (p1.idExchangePlatform + p2.idExchangePlatform )

The Result:


Transparent Data Encryption Are there any best practices when configuring TDE in SQL Server 2008? On SQLMag, the article "Transparent Data Encryption FAQs" says the CPU may have increased usage by up to 30%?

Other than adding server horsepower, is there anything else DBAs typically do when turning on TDE?

Some additional points that I have noticed is that in case you are using the backup compression feature, this feature together with TDE does not go that well. We have noticed a very minimal compression rate, almost negligible. Therefore consider this point of backup compression if you are using one.
I am sure you would be aware, but just to add, TDE is available for Enterprise edition, so therefore consider this as well during setting up SQL server for TDE.
TDE does not provide the same granular control, specific to a user or database role, as is offered by cell-level encryption.
Make sure the encryption keys are stored safely in a secure location that can be accessed in the event of a restore scenario. Familiarize yourself with restoring a database that has been encrypted to a new server. (originally a comment by Jonathan Fite).


Write Oracle proc/function to create dynamic views with changing column headers I had similar requirement earlier but now i have been given a clear approach that needs to be implemented.
I need to write a procedure(preferably)/function, which would on basis of app_id passed as parameter, change the column headers for corresponding values pivoted in the dynamic view.
The entire sample data and structure is available here:
https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=63c08123fbf2fb13de81df0bff360c0b
The table with column headers is DATA_HEADER.

The table with value is DATA_VALUE.

The column headers and values need to be pivoted in order.
Both tables have app_id on common. So for app_id=1, labels from DATA_HEADER would be used against values in DATA_VALUE. However, the values change on basis of PID only, so they would remain constant, only headers would change per app id.
When app_id is passed in proc/funct, the expected view should be:

So basically, the headers change for each app_id, and the max number of column headers will be 20. So the number or name shall vary as in the table DATA_HEADER.
The values are uniquely identified on the basis of pid.
The order of column headers would be as per the seq column in DATA_HEADER.
Similarly the order of values would be as per seq column in data value, so the sequence must be followed and pivoted accordingly.
P.S. Application at the end is Oracle apex from where the proc/function would be called.
Oracle Version: 12.1

Why does it have to be one query?
Why not retrieve the column headers in one step, pull the real data in another and then stitch the two together, if that's even required?
If this is being done under application control (as opposed to a single database "query"), you may not need to do this "reassembly" at all.  It might be possible to populate your column headers separately from the real data.