Determine existence in recursive working table I have a query that needs to check for existence of each input row type in another table, but it is unnecessary and inefficient to check for existence of subsequent rows of the same row type if one is already found.

I tried to reference the working table in an EXISTS subquery in the JOIN to prevent the unnecessary checks, but this gave an error that the recursive query cannot be referenced in a subquery.

For a simplified example modified from the docs only meant to demonstrate my problem in code:

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100 AND NOT EXISTS (SELECT 1 FROM t u WHERE u.n = t.n+1)
)
SELECT sum(n) FROM t;

This is of course logically ridiculous, but the NOT EXISTS is the problem and specifically what gives an error.

Is there a way to determine existence in the working table?

I'm not sure I fully understand what you expect to happen with the SQL you pasted .. (add up the numbers from 1-99 ignoring all even values?)  But this is a SQL to get around the syntax error you have:

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n+1 not in (t.n) and n < 100
)
SELECT sum(n) FROM t;

I would like to point out that the result of my SQL above returns 5050 because the "n+1" is never actually in the result set...  (You'd need to compare for n-1 in the result set if you wanted to ignore even numbers..)


Query fails from application but runs fine in SSMS I have a query that runs fine when executed from SSMS but when done so from the application, it fails with a (database level) exception.

select ksprintid
from kshistory_akt h
where cast(h.serialno as decimal(28)) >= 1
    and h.sku = '11105031'

The application error is

System.Data.SqlClient.SqlException: Error converting data type varchar to numeric.
   bei System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   bei System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   bei System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   bei System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   bei System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
   bei System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
   bei System.Data.SqlClient.SqlDataReader.Read()
   bei NHibernate.Driver.NHybridDataReader.Read()
   bei NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies, IResultTransformer forcedResultTransformer)
   bei NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies, IResultTransformer forcedResultTransformer)
   bei NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters, IResultTransformer forcedResultTransformer)

The code that executes it:

ISessionFactory _factory = new Configuration().Configure().BuildSessionFactory();
CurrentSessionContext.Bind(_factory.OpenSession());
IList printedJobs = _factory.GetCurrentSession()
    .CreateSQLQuery("select 1 from kshistory_akt h where cast(h.serialno as decimal(28)) >= :start and h.sku = :material")
    .SetString("material", "11105031")
    .SetParameter<int>("start", 1)
    .List();

But, the weird thing is that the result set of

select ksprintid
from kshistory_akt h
where h.sku = '11105031'

(without the actual cast) is empty:

So - how can an empty result cause an conversion error? And why doesn't it happen from within SSMS?

And to top it off: This only happens on one of three servers where this application runs. Every other server happily runs the command from the application without any errors (as it should be, because empty results should not cause problems when converting, right?)

SSMS Connection options

ANSI_WARNINGS
ANSI_PADDING
ANSI_NULLS
ARITHABORT
QUOTED_IDENTIFIER
ANSI_NULL_DFLT_ON
CONCAT_NULL_YIELDS_NULL

Client Connection options

ANSI_WARNINGS
ANSI_PADDING
ANSI_NULLS
QUOTED_IDENTIFIER
ANSI_NULL_DFLT_ON
CONCAT_NULL_YIELDS_NULL

Your WHERE clause contains an explicit datatype conversion, which is terrible for performance, and can also cause this type of problem.

This query will probably show you what's wrong:

select serialno AS notVeryDecimalSerialNo
from kshistory_akt h
where try_cast(h.serialno as decimal(28)) IS NULL;

As for possible solutions, try changing the CAST to TRY_CAST - but note that this may lead to results that you don't expect, because TRY_CAST returns NULL instead of a conversion error.

If I were to guess, your problem is that there is a serialno which won't convert to decimal, but that value is not found if you filter on material='11105031'. So if SQL Server filters the material first, your query succeeds, otherwise the conversion fails. For more details, here's a blog post I wrote on the subject.

The performance problem that I mentioned is caused by the CAST in the WHERE clause, which prevents SQL Server from using an index properly. The TRY_CAST won't solve this, it only takes away the actual conversion error. For more on this, check out "sargable" expressions.


Sybase ASE 15.5 : Create a custom function for partitionning I want to create a partitioned table to store messages.

If the message belongs to "an even week number" => go to the partEven partition !
If the message belongs to "an odd week number" => go to the partOdd partition !

Here is my broken code :

CREATE TABLE myTable (
    id NUMERIC(18,0) IDENTITY,
    message_date_time DATETIME,
    PRIMARY KEY (id)
    )partition by list (message_date_time)
        (partEven datepart(week, message_date_time) % 2 == 0,
         partOdd datepart(week, message_date_time) % 2 == 1
        )

Could someone help me with the syntax ? (if it's possible this way)

It seems that I'm only allowed to route records on the "message_date_time" constant but I want to do it on the result of a function applied to "message_date_time".

Please help :)

You cannot write your own partition function. What you need to do is create an additional column, assign a value resulting from the function you want to use, and partition on that column. Unfortunately, you cannot partition on a computed column, which would be the easiest solution.


Best query to pick row based on IP address and DateStamp Supposed I have Table1 that have account_id, ip_address and created_date columns but on the other table, Table2 has only ip_address and created_date columns. Table2 doesn't have an account_id that I can use to join in table1. For some reason from the management, I still need to assume or pick accounts_ids from Table1 to fill it in Table2.

What will be the best way to pick accounts_ids from Table1?
I tried the query below but it seems that there are accounts from Table1 which is obviously doesn't belong to Table2 due to created_date comparison. I wonder if there are ways to do this such as comparing YEAR(create_date) or etc?

SELECT t2.ip_address, t2.created_date, t1.account_id
FROM Table2 t2
LEFT JOIN ( SELECT account_id, ip_address, MAX(created_date) created_date
            FROM Table1 t1
            GROUP BY account_id, ip_address
           ) t1
on t2.ip_address = t1.ip_address

EDIT: Note that I just want to pick the closest date as possible since IP address and dates are not unique on Table1.

I'm not sure if this sample data cover your current issue.

CREATE TABLE Table1(account int, ip_address varchar(15), created_date date);
INSERT INTO Table1 VALUES
(1, '10.0.0.1', '20170101'),
(1, '10.0.0.1', '20170201'),
(1, '10.0.0.1', '20170301'),
(2, '10.0.0.2', '20170201'),
(3, '10.0.0.3', '20170201');

CREATE TABLE Table2(ip_address varchar(15), created_date date);
INSERT INTO Table2 VALUES
('10.0.0.1', '20170201'),
('10.0.0.2', '20170201'),
('10.0.0.3', '20170201');

This solution assumes that, at least, there is a created_date on your Table1 >= of the created_date on Tabel2

SELECT (SELECT TOP (1) account
        FROM     Table1 t1
        WHERE    t1.ip_address = t2.ip_address
        AND      t1.created_date >= t2.created_date
        ORDER BY t1.ip_address, t1.created_date desc) account,
       t2.ip_address,
       t2.created_date
FROM   Table2 t2

account | ip_address | created_date
------: | :--------- | :------------------
      1 | 10.0.0.1   | 01/02/2017 00:00:00
      2 | 10.0.0.2   | 01/02/2017 00:00:00
      3 | 10.0.0.3   | 01/02/2017 00:00:00

dbfiddle here
If there is no an exact date, you can build a list of periods:

WITH ipList as
(
    SELECT account, ip_address, created_date,
           COALESCE(LEAD(created_date) OVER (PARTITION BY account, ip_address ORDER BY created_date), created_date) next_date
    FROM   Table1
)
select * from iplist;
GO

account | ip_address | created_date        | next_date
------: | :--------- | :------------------ | :------------------
      1 | 10.0.0.1   | 01/01/2017 00:00:00 | 01/02/2017 00:00:00
      1 | 10.0.0.1   | 01/02/2017 00:00:00 | 01/03/2017 00:00:00
      1 | 10.0.0.1   | 01/03/2017 00:00:00 | 01/03/2017 00:00:00
      2 | 10.0.0.2   | 01/02/2017 00:00:00 | 01/02/2017 00:00:00
      3 | 10.0.0.3   | 01/02/2017 00:00:00 | 01/02/2017 00:00:00

And then try to find the best match:

WITH ipList as
(
    SELECT account, ip_address, created_date,
           COALESCE(LEAD(created_date) OVER (PARTITION BY account, ip_address ORDER BY created_date), created_date) next_date
    FROM   Table1
)
SELECT    (SELECT TOP(1) ipList.account
           FROM   ipList
           WHERE  t2.ip_address = ipList.ip_address
           AND    t2.created_date >= ipList.created_date
           AND    t2.created_date <=  ipList.next_date) account,
          t2.ip_address,
          t2.created_date
FROM      Table2 t2;
GO

account | ip_address | created_date
------: | :--------- | :------------------
      1 | 10.0.0.1   | 01/02/2017 00:00:00
      2 | 10.0.0.2   | 01/02/2017 00:00:00
      3 | 10.0.0.3   | 01/02/2017 00:00:00

dbfiddle here


Executing try catch block in mysql stored procedure In my mysql stored procedure I want try catch block but it gives error in my mysql stored procedure .

I want my select query to be in try block
and if it executes correctly I want other query to execute.

code statement for mysql

BEGIN
 begin try
     select userId into user from users ;
 end try
 begin catch
    select 1;
 end catch
 begin finally
   select postId from photo where userId=user;
 end finally
END

but it gives error is there any try catch block for mysql.

I think you're mixing up Java (other language?) with MySQL's stored procedure language. Look here and here. Check out this by Guy Harrison, the guy who wrote this book.


Impact of Quotes in T-SQL for Column Name Is there a difference between the two T-SQL commands below ?

Column name with quotes

SELECT SUM(DATALENGTH('ColumnName')) / 1048576.0 AS ColumnName
FROM DatabaseName.Schema.Tablename

Column name without quotes

SELECT SUM(DATALENGTH(ColumnName)) / 1048576.0 AS ColumnName
FROM DatabaseName.schema.Tablename

Yes, the first variant will give you the data length of the text 'ColumnName' whereas the second will most likely get you nearer the results you are looking for (which I assume is the total length of all data within the given column).

In SQL Server if you are looking for quoted identifiers you can use the standard " or square brackets. You only need to quote names that are reserved keywords like TABLE, INDEX etc... but as a general code-style tip I recommend picking a quoting scheme and using it consistently within your code. It makes it easier for other developers to read and pick up your scripts.

SQL Server: SET QUOTED_IDENTIFIER


how to create a data type and make it available in all Databases? If I create a stored procedure in the master database, and I want to execute it from any of my databases I just follow this link:

Making a Procedure Available in all Databases

that give me this code example:

Just by following the example above, I can call my procedure from any database.

what about if I create a table data type in master,
how can I use it in any of my databases?

use master

        IF NOT EXISTS (select * from sys.types where name = 'theReplicatedTables')
                CREATE TYPE theReplicatedTables AS TABLE
                (  OBJ_ID INT NOT NULL,
                  PRIMARY KEY CLUSTERED (OBJ_ID)
                );

use APIA_Repl_Sub
go
declare @the_tables [dbo].[theReplicatedTables]

There is no way to do that with table types. You will need to replicate that in all databases. But, if you want this table type available while creating a new database, you can just add it in the model database.
There are a couple restrictions to using table-valued parameters. You can't even use table-valued parameters across databases. Check some details here.


System Health Extented Event - Security_Ring_Buffer_Error_Recorded I've recently upgraded a Production SQL instance from 2005 SP4 to 2012 SP2 and have noticed a large amount of the following entries in the system_health extended event:-

I've been looking into this and have found the following related articles/questions:-

SQL Server 2012: Security_error_ring_buffer_recorded: ImpersonateSecurityContext

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/65e792ff-8ef5-4ff7-b8b9-e71b262d8bb7/sql-2012-sp1-systemhealth-securityerrorringbufferrecorded-events?forum=sqldatabaseengine

http://blogs.msdn.com/b/sqlserverfaq/archive/2010/10/27/troubleshooting-specific-login-failed-error-messages.aspx

However, these point to the issue being related to failed logins. I've checked the SQL error log and no failed logins are being recorded.

I have been investigating this on a development instance and have noticed that the entries are generated whenever a SQL authenticated login connects to the SQL instance.

Has anyone come across this before? I'd like to get to the bottom of why these entries are being recorded.

If anyone wants to see this happening on their own (development) instance, log into SQL using a SQL authenticated login and then run:-

SELECT
    CONVERT (VARCHAR(30), GETDATE(), 121) as runtime,
    DATEADD (ms, (a.[Record Time] - sys.ms_ticks), GETDATE()) as [Notification_Time],
    a.* , sys.ms_ticks AS [Current Time]
FROM
    (SELECT
       x.value('(//Record/Error/ErrorCode)[1]', 'varchar(30)') AS [ErrorCode],
       x.value('(//Record/Error/CallingAPIName)[1]', 'varchar(255)') AS [CallingAPIName],
       x.value('(//Record/Error/APIName)[1]', 'varchar(255)') AS [APIName],
       x.value('(//Record/Error/SPID)[1]', 'int') AS [SPID],
       x.value('(//Record/@id)[1]', 'bigint') AS [Record Id],
       x.value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
       x.value('(//Record/@time)[1]', 'bigint') AS [Record Time]
    FROM (SELECT CAST (record as xml)
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR') AS R(x)) a
CROSS JOIN sys.dm_os_sys_info sys
ORDER BY a.[Record Time] DESC

You should see entries that correspond with the time that you logged in.

It would be good to know if someone else can replicate what I'm seeing.

Thanks,

Andrew

Based on my understanding, this is just a noise that you can ignore.

This event is a part of sqlserver.security_error_ring_buffer_recorded event. It is detailed logging telling you what it is doing with the security errors (e.g. failed logins) to make sure they don't get lost.

The API ImpersonateSecurityContext (provided by SSPI) allows a server to impersonate the client's security context - meaning : the server can authenticate the client and use the client's security context for local access checks directly or by passing the context to another server on the same machine.

I would safely ignore those.

BTW, I am able see the same output on SQL Server 2012 + RTM + (Build11.0.2100.60)


mysql replication for certain dababases I am new to mysql and I have a query in regards to mysql replication.

Currently, I have a master database that replicates to four slave servers at the moment. I would need to set up a few replication servers(as slave) but these will replicate only a few databases. How would approach this issue?

Should I be using something like this on slave /etc/my.cnf?

replicate-ignore-db=db_name

Can I set something on slave configuration stating exactly which databases and tables to be replicated?

Should the changes happen only on the slave configuration or on master as well? The reason being this master server currently replicates whole lot of databases to other slaves which will remain as it is.

I would avoid the replicate-do-db option due to the restriction on cross-database replication and users not fully qualifying a database name or not specifying "use dbname;" (it will execute on master but not replicate to slave).

Use this option instead (put it in my.cnf):

replicate-wild-do-table=dbname1.%

replicate-wild-do-table=dbname2.%

Or you can specify the tables, replace % with table name, one on each line.
http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html#option_mysqld_replicate-wild-do-table

This link should help get you started:
http://dev.mysql.com/doc/refman/5.0/en/replication-solutions-partitioning.html


Service Broker-Certificate Not Found My organization has been successfully using service broker, dialog security, for a few years.

I have a new instance of service broker that I would like to configure, but I believe I am running into a situation where the order in which the new broker instance is being incorrectly placed into the databases (with existing broker instances in place) causing the new broker instance to fail.

My error is on the target server; Certificate Not Found. Security context could not be retrieved.

My situation is:
Source server A (dmz) sending to target server B, where there are already existing broker instances sending messages both ways but on different databases.

Test 1: I switched the role of the source and target server around, making server B my source and server A (dmz) my target while using the same databases.  This was successful.

Test 2: I dropped the test 1 instance and created 2 new databases on each server. I have again, reconfigured the source and target roles, now sending from server A (dmz) to server B and this was successful. There is only the one service broker instance configured in the new databases, so the order of placement is not an issue.

I have come into this situation in the past when we first set up our dev/test environments; However, the situation was slightly different as many other broker instances were also failing. Not all of them, but some. Once I realized what was happening, dropping all instances and recreating them in a specific order, source instances before target instances, the situation was resolved.

I would think that service broker would add a new instance in the correct place of order, automatically.

Am I correct in thinking that the broker instances must be configured within a specific order? If so, how can I add new broker instances without interrupting what is already there?

Please share your thoughts and suggestions.

Set up a profiler trace and monitor for Security Audit/Audit Broker Conversation. Repro the issue, an event of this class should be produced. Look at the event details. It should shed light. I think two of the event columns (can't remeber which, and MSDN seems to omit the info) contain the problem certificate issuer name/serial number, which should further help narrow down which certificate is being used.

[Edit]
Run this query query on all three databases to find out which certificates are used by which service and remote service binding:

select s.name, c.*
from sys.services s
join sys.certificates c
on s.principal_id = c.principal_id
where c.is_active_for_begin_dialog = 1
and pvt_key_encryption_type != 'NA'
order by c.expiry_date desc

select b.remote_service_name, b.remote_service_binding_id, c.*
from sys.remote_service_bindings b
join sys.certificates c on b.remote_principal_id = c.principal_id
where c.is_active_for_begin_dialog = 1
order by c.expiry_date desc

[Edit after your data]

1) In PRd2DB services tab: all services in the DB are owned by dbo, and dbo owns 4 certificates that are valid for SSB (certificate ids 280, 288, 327 and 357). The xls did not correctly capture the expiry_date (is all 00:00.0) so I cannot know for sure which one is used, but if the results come from the query I posted above then the first one will be used, certificate id 280 with the serial number 54 89 2c 1a.... So this is the certificate used to identify any service from this db.

2) In Prd2 in the RSB tab, the query shows a mixture of certificates with private key and ones without a private key. This should never happen. An user associated with an RSB represents the identity of a remote service and should own only certificates with no private key. Also, there is only one RSB user (the one with principal_id 58) for all remote services. There should be a different identity user for each remote service. Right now any dialog started in PrdDB2, to any service, will use the certificate with serial number 34 99 ....

3) DMZDB1 services: same as prd2, all services owned by dbo, dbo has 3certificates (ids 335, 341, 371) and the one used will be the one with serial number 74 0a ...

4) DMZDB RSBs: there are two RSB users, one principal id 9 and one 30. The principal id 9 has a mixture of certificates, including some with private key, while principal id 30 has only one certificate but that certificate has a private key.

Overall, the configuration is deeply flawed and and no wonder it doesn't work. You do not understand the role and the proper configuration of a remote service binding.

Please follow the following rules. Consider a remote service 'Foo':

create a different user (principal) for each remote service, call it remote_user_Foo
create a RSB for each remote service, using the principal for this service (ie. remote_user_Foo)
The remote_user_Foo should own exactly one certificate, and it should not have a private key
Grant necessary permissions (SEND) to remote_user_Foo

Next time you run the query for showing the RSB it should show a number of rows equal to the number of remote service bindings, the principal_id values should be all distinct and the pvt_key_encryption_type should all be NA.


How to move snapshot Azure SQL db from prod to dev on a recurring basis How can I take an existing production Azure SQL database (or one of its regular backups) and restore it to a dev environment on a regular basis? I want to give developers the option to work on their regular dev database but also have a snapshot of prod available in order to safely reproduce issues found on production. And optionally, to be able to replace our regular dev database with one of these snapshots manually, if and when we choose to.

Right now, I can create a new database with a new name from one of the built in regular backups from prod, but then I have to update all of the connection strings each time. If I can have a few to swap between then I can use deployment slots to keep copies of the configs, so I could have a 1, 2 and 3 database and shuffle between then or something. Any help would be greatly appreciated! Thanks!

You can use CREATE DATABASE AS COPY to create a snapshot of production database, and then use the same CREATE DATABASE AS COPY option to create databases for development environments using the snapshot database. All this can be automated using an Azure Runbook. The runbook can run with a SQL login with elevated privileges to accomplish all copy activities.

CREATE DATABASE db_copy
    AS COPY OF ozabzw7545.db_original ( SERVICE_OBJECTIVE = 'P2' );

Adopting Azure Web Apps may help you, because of their ability to override application settings and connection strings from within the Azure Portal. This way you could allow your developers or infrastructure admins to deploy new code to the Azure Web App without letting them even see the appSettings and Connection Strings that particular environment uses.

Visual Studio Team Services resource groups and Microsoft Release Management Agents may be very useful for you.


Update one table from another table while sorting that table based on one column This is the problem I'm trying to figure out in MySQL. We have an old table contains some forms submitted by our users. Somehow, the previous decision was each time a user comes to this survey, a new form will be submitted. So in the old table we easily have several rows with the same Firstname, Lastname, but different values in the other columns, and there's a timestamp column Date_Submission as well.

Now we are trying to move everything to a new table, but this time, for each person we only keep one row. And we want to keep some of the the latest old data of that user (like email, phone number, etc)

I could do the following:

  update newtable, oldtable set newtable.email = oldtable.email where newtable.firstname = oldtable.firstname and newtable.lastname = oldtable.lastname;

Apparently this won't give me the "latest" old date for each person.

So I tried this one:

  update newtable, oldtable set newtable.email = oldtable.email where newtable.firstname = oldtable.firstname and newtable.lastname = oldtable.lastname order by oldtable.Date_Submission;

But they MySQL will complain with:

  "ERROR 1221 (HY000): Incorrect usage of UPDATE and ORDER BY".

So I'm wondering, what's the correct way to achieve this?

There are two options

OPTION #1 : INSERT INTO ... ON DUPLICATE KEY UPDATE

INSERT INTO newtable
SELECT * FROM oldtable
ORDER BY tmstamp_value
ON DUPLICATE KEY UPDATE email=VALUES(email), address=VALUES(address) ... ;

OPTION #2 : REPLACE INTO

REPLACE INTO newtable
SELECT * FROM oldtable
ORDER BY tmstamp_value;

It mechanically performs a DELETE and an INSERT.

CAVEAT

Notice I applied the ORDER BY against the old table. This should help get the latest data for a give LastName, FirstName.

You should also add a UNIQUE INDEX on the new table as follows:

ALTER TABLE newtable ADD UNIQUE INDEX name_ndx (lastname,firstname);

UPDATE 2013-04-08 17:13 EDT

If you are doing updates, there are two ways to do this:

1. UPDATE JOIN

UPDATE
(SELECT lastname,firstname,phone,email
FROM oldtable ORDER BY lastname,firstname) A
LEFT JOIN newtable B USING (lastname,firstname)
SET B.phone=A.phone,B.email=A.email;

2. UPDATE JOIN with GROUP BY

UPDATE
(
    SELECT BB.id,BB.phone,BB.email FROM
    (SELECT lastname,firstname,MAX(id) id FROM oldtable GROUP BY lastname,firstname) AA
    INNER JOIN oldtable BB USING (lastname,firstname)
) A INNER JOIN newtable B USING (id)
SET B.phone=A.phone,B.email=A.email;

Give it a Try !!!

Both options will take full advantage of the UNIQUE index on name.


Is there any performance difference among MariaDB (xtradb plugin) and XtraDB Server for InnoDB? Title says it all. However i have to enter at least 30 characters.

Percona's Xtradb has some improvements -- mostly in scalability.  They changed the Mutexes, which held back effective use of multiple cores.


Is there a way to rewrite this MySQL query to use window functions, and not use session variables? I have the following query

drop table if exists tradesAndActions;
create table tradesAndActions (transik int, ca_date date, terms float, multiply bit, originalPrice float, originalShares float);
insert into tradesAndActions values
  (1, '2010-01-01', 10, 0, 50.0, 1000.0),
  (1, '2010-02-01', 2, 1, null, null),
  (2, '2010-02-01', 2, 1, 10.0, 50.0),
  (2, '2010-02-01', 10, 0, null, null)

;

set @currentTransik := 0;
set @adjustedPrice := 0.0;
set @adjustedShares := 0.0;

select *,
  @adjustedPrice :=
      if(@currentTransik = a.transik,
        if(a.multiply = 1, @adjustedPrice * a.terms, @adjustedPrice + a.terms),
        if(a.multiply = 1, a.originalPrice * a.terms, a.originalPrice + a.terms)
      ) adjustedPrice,
  @adjustedShares :=
      if(@currentTransik = a.transik,
        if(a.multiply = 1, @adjustedShares / a.terms, @adjustedShares),
        if(a.multiply = 1, a.originalShares / a.terms, a.originalShares)
      ) adjustedShares,
  @currentTransik := a.TRANSIK currentTransik
from
  (
    select *
    from tradesAndActions b
    order by b.transik, b.ca_date, b.multiply desc
  ) a
;

By way of background, this is about taking a bunch of trades, and adjusting the prices to account for corporate actions, to answer the question: "what would the trade price have been if there was no corporate action".
I am pretty sure the query returns the desired result. However, I am wondering if it can be rewritten to not use any session variables, by way of window functions.

Going a bit deeper: some corporate action adjustments require you to add the term to the price, others require you to multiply the term and the price. The effect is cumulative for successive corporate actions. I have arranged for there to be a flag indicating whether a given adjustment term is a multiply type, or an add type.

The difficulty in using window functions here is that you only have things like

sum(x) over (partition by y ...)

or

exp(sum(log(x)) over (partition by y ...)

There is no way, as far as I know, to specify some cumulation function that does different things depending on whether or not you should multiply or add.

But perhaps there is some sort of multi stage approach that does the same thing?
Note: this is not a practical question in that I need some help to get a job done; I already have a working query. This is more a case of me exploring the limits of window functions. I am wondering if a window function would be more elegant. For some reason I have some unexplained preference for window functions over iterating session variables.

Ok, I solved it using danblack's suggestion of a recursive CTE.

with recursive rankedTradesAndActions AS
(
  select t.*, ROW_NUMBER() over (PARTITION by t.transik order by t.ca_date, t.multiply desc) rowNumber
  from tradesAndActions t
), adjustedForCAs(transik, adjustedPrice, adjustedShares, rowNumber) as
(
  select t.transik
    , if(t.multiply = 1, t.originalPrice * t.terms, t.originalPrice + t.terms) adjustedPrice
    , if(t.multiply = 1, t.originalShares / t.terms, t.originalShares) adjustedShares
    , 1
  from rankedTradesAndActions t
  where t.rowNumber = 1
  UNION ALL
  select t.transik
    , if(t.multiply = 1, a.adjustedPrice * t.terms, a.adjustedPrice + t.terms) adjustedPrice
    , if(t.multiply = 1, a.adjustedShares / t.terms, a.adjustedShares) adjustedShares
    , a.rowNumber + 1
  from rankedTradesAndActions t
    inner join adjustedForCAs a on t.transik = a.transik and t.rowNumber = a.rowNumber + 1
  where t.rowNumber > 1
)
select * from adjustedForCAs a order by a.transik, a.rowNumber
;

No session variables used, so mission accomplished as far as I am concerned.

If anyone can come up with another interesting (possibly simpler) way to solve this, please post it. For now I consider this to be the answer, but if someone can come up with a simpler way to do it, I will consider that to be the answer.


Does RE-BUILDing a HEAP cause downtime? This is a bit of an embarrassing question, and I can't believe I've missed this for so many years.

I have a vendor third-party database that has 401 heap tables. I've recently got into using Brent Ozar's scripts and setup sp_BlitzFirst to run every 15 minutes to gather wait stats, etc.

What it uncovered was every time it ran across a 24 hour period it was telling me to fix Forwarded Records. What will probably shock some readers that I ran a query across the DMVs and got back some tables with over 150,000 forwarded record values.

I understand to fix this is to have a clustered index across the table, or as a temporary work-around to run ALTER TABLE [tablename] REBUILD.

What I haven't been able to find however is whether this takes the table offline, and whether there are any other problems I should be aware of before running this command.

I am using the Enterprise Edition of 2008 R2, and I wonder whether running it this way will remove the need for an outage?

ALTER TABLE [tablename] REBUILD WITH (ONLINE = ON);

Does anyone have any experience with this?

Good news: 150,000 forwarded records isn't actually that bad, depending on what kind of time span we're talking about. Forwarded records are tracked as long as the server is up (with some gotchas around a bug in specific 2012/2014 builds.)

Even when it does work online, your users can notice it depending on your IO throughput, size of the table, number of nonclustered indexes, and your workloads.

Here's how I tackle it:

List the tables with the forwarded records warnings from smallest to largest table size (not number of forwarded records)
Rebuild those tables in order, by hand, a few at a time. Don't automate this - you want to get a feel for how your system responds to rebuilding small tables. You'll learn fairly quickly if your server can keep up.
Keep an eye on your transaction log usage. If your log file is 10GB, and you're trying to rebuild a 50GB heap, you can run into problems. You can mitigate these issues by growing the log out ahead of time during maintenance windows and by doing frequent log backups while you're doing this - but I'll be honest, at 150k forwarded records across the span of a few days, I wouldn't bother going through this much work.
Going forward, work with the vendor to put a clustered key on those tables. Yes, there are times when heaps are appropriate, but if you're running into the forwarded record problem, you're probably not in one of those heap-friendly situations.


Retrieving month from date field in my database I have dates stored in database with the function : CURDATE() , when a user makes an order . With a form where the admin chooses the month and the year, I want a query to display all the orders made in that month and year . How can I do that ?

This works

SELECT MONTH(CURDATE());

With the final query like

SELECT    *
FROM      tbl_orders
WHERE     MONTH(date) = @month
AND       YEAR(date) = @year;

Here @month and @year are numeric values from the administrator. Defaults could be

SET @month = MONTH(CURDATE());
SET @year = YEAR(CURDATE);

This is the quick and dirty solution applicable for a small table. If performance is an issue, consider putting a binary tree index on the date column or breaking it down into (month, year) with a hash index.

Binary tree index allows comparison like so

WHERE     date >= CONCAT(@year, '-', @month, '-', '01')
AND       date <  CONCAT(@year, '-', @month+1, '-', '01');

But this does not work when @month=12 unless you do some complicated date math, which can be done elsewhere. Note that you can do concatenation elsewhere too, to enable query caching.

With hash index

SELECT    *
FROM      tbl_orders
WHERE     month = @month
AND       year  = @year;


No IMPLICIT_CONVERSION warning in this query plan Take the following minimally complete and verifiable example1 code:

USE tempdb;

DROP TABLE IF EXISTS dbo.t;
GO
CREATE TABLE dbo.t
(
    t_id int NOT NULL
        CONSTRAINT t_pk
        PRIMARY KEY
        CLUSTERED
        IDENTITY(1,1)
    , k sysname NOT NULL
        INDEX t_001
    , s sysname NOT NULL
        INDEX t_002
    , somedata varchar(1000) NOT NULL
        CONSTRAINT t_somedata_df
        DEFAULT REPLICATE('A', 1000)
    , INDEX t_003 (k, s)
);

INSERT INTO dbo.t (s, k)
SELECT sc1.name, sc2.name
FROM sys.syscolumns sc1
    CROSS JOIN sys.syscolumns sc2;

CREATE STATISTICS t_st001 ON dbo.t (k) WITH FULLSCAN;
CREATE STATISTICS t_st002 ON dbo.t (s) WITH FULLSCAN;

The following query purposely uses the wrong variable types.  I was hoping the query plan would include an implicit conversion warning, but it doesn't.

DECLARE @k char(128) = 'a';
DECLARE @s char(128) = '';
SELECT s
FROM dbo.t
    LEFT JOIN sys.syscolumns sc ON t.s = sc.name
WHERE dbo.t.s = @s
    OR dbo.t.k = @k;

Why is that?

The query plan is here.

1 - that's my website, BTW

Your parameters have a lower Data Type Precedence than the columns they are compared to.  In this case the parameter values will be implicitly converted.  This is a good thing.  Converting the column values to the parameter’s type is problematic and will trigger the warning.


Combine Table Hints INDEX and FORCESEEK with Two Joins Not On PK See first query below.
Can NOT combine table hint Index and forceseek with two joins and the joins are not on the PK.
How to make the first query compile?

Interesting
-  if just one join or the other then can combine index and forceseek hints
-  if the index is the PK then can combine 2 joins and have both hints

-- compiler fails
-- Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
Select Count(Distinct([docSVsys].[sID]))
From [docSVsys] with (nolock)
Left Join [docSVtext] with (nolock, Index(IX_docSVtext_value_sID), forceseek )
       On [docSVtext].[sID] = [docSVsys].[sID]
Left Join [docMVtext] with (nolock, Index(ix_docMVtext_value_sID), forceseek)
       On [docMVtext].[sID] = [docSVsys].[sID]
where [docSVtext].[value] = 'doug'  or
      [docMVtext].[value] = 'doug'

--  can do one join
Select Count(Distinct([docSVsys].[sID]))
From [docSVsys] with (nolock)
Left Join [docSVtext] with (nolock, Index(IX_docSVtext_value_sID), forceseek )
       On [docSVtext].[sID] = [docSVsys].[sID]
where [docSVtext].[value] = 'doug'

--  can do the other join
Select Count(Distinct([docSVsys].[sID]))
From [docSVsys] with (nolock)
Left Join [docSVtext] with (nolock, Index(IX_docSVtext_value_sID), forceseek )
       On [docSVtext].[sID] = [docSVsys].[sID]
where [docSVtext].[value] = 'doug'

-- if on the PK then can do forceseek on two join
Select Count(Distinct([docSVsys].[sID]))
From [docSVsys] with (nolock, INDEX(PK_docSVsys))
Left Join [docSVtext] with (nolock, Index(PK_docSVtext), forceseek )
    On [docSVtext].[sID] = [docSVsys].[sID]
Left Join [docMVtext]  with (nolock, Index(PK_docMVtext), forceseek)
    On [docMVtext].[sID] = [docSVsys].[sID]
where [docSVtext].[value] = 'doug'
   or [docMVtext].[value] = 'doug'

This does what I want and runs 100 times faster.
This has almost an identical query plan to the answer from ypercube.
But I cannot combine this with some other conditions I need.

Select count(distinct([docSVsys].[sID]))
From [docSVsys] with (nolock)
LEFT HASH JOIN [docSVtext] with (nolock)
  on [docSVtext].[sID] = [docSVsys].[sID]
 and [docSVtext].[value] = 'doug'
LEFT HASH JOIN [docMVtext] with (nolock)
  on [docMVtext].[sID] = [docSVsys].[sID]
 and [docMVtext].[value] = 'doug'
where [docSVtext].[sID] is not null
   or [docMVtext].[sID] is not null

This also uses the proper indexes and is the fastest.
For now I don't have the option to rewrite the format.
The program builds up queries to other tables.
We incorrectly wanted the same format for where as an order by and that got us where we are.

SELECT  count(distinct(c.sID))
from
(   SELECT sv.[sID]
    FROM [docSVtext] AS sv
    WHERE sv.[value] = 'doug'
    UNION ALL
    SELECT mv.[sID]
    FROM [docMVtext] AS mv
    WHERE mv.[value] = 'doug'
) as c

docSVsys
sID Int PK Identity

docMVtext
sID Int PK FK to docSVsys
fieldID Int PK
value string PK
ix  value, sID

docSVtext
sID Int PK FK to docSVsys
fieldID Int PK
value string
ix  value, sID

MV is a table for multi-value text
SV is a table for single-value text
fieldID is for unique fields
This is a search of all SV and MV fields

I would also test this rewriting (aasuming that sID is the primary key of docSVsys):

SELECT COUNT(*)
FROM [docSVsys] AS d
WHERE EXISTS
      ( SELECT *
        FROM [docSVtext] AS sv
        WHERE sv.[sID] = d.[sID]
          AND sv.[value] = 'doug'
      )
   OR EXISTS
      ( SELECT *
        FROM [docMVtext] AS mv
        WHERE mv.[sID] = d.[sID]
          AND mv.[value] = 'doug'
      ) ;


Why would Vertica let me start a transaction if I can't roll it back? I run the following statements against a Vertica database, one at a time:

BEGIN TRANSACTION;

UPDATE table
SET col1 = 'something'
WHERE col2 = 'something else';

SELECT COUNT(*)
FROM table
WHERE col1 = 'something';

ROLLBACK TRANSACTION;

I run the first line fine... OK, now I'm in a transaction.

I run my update... OK, that worked.

I run my SELECT test to make sure things worked as expected... Oh wait, it looks like I missed a condition in the WHERE clause of my UPDATE statement.

No worries! That's why I did this in a transaction.

Let's rollback:

=> ROLLBACK TRANSACTION;

[Vertica][JDBC](10040) Cannot use commit while Connection is in auto-commit mode.

Jones, hand over that roll of toilet paper you have on your desk.

So Vertica happily accepted my BEGIN TRANSACTION, knowing full well that very soon after that I would try to run either a ROLLBACK or COMMIT.

Yet, I can't! My connection is in auto-commit mode, so ROLLBACK and COMMIT mean nothing. My UPDATE was committed the moment it completed.

Did I miss something, or am I right in thinking this is just a very bad implementation on Vertica's part?

Why would Vertica accept a BEGIN TRANSACTION on a connection in auto-commit mode if the logical consequences (ROLLBACK or COMMIT) are illegal?

Vertica (actually, I think most if not all databases) behaves in this manner because the part that processes the BEGIN TRANSACTION SQL statement, namely the query parsing and execution engine, is not aware of the client AUTOCOMMIT setting. On the other hand, the client is not aware of the meaning of the string 'BEGIN TRANSACTION'.

The flow of control looks something like this, assuming the command line client vsql is used to run the example:

User starts vsql and connects to the database.
User enters \set AUTOCOMMIT on. After that moment the client (vsql) knows to issue an implicit COMMIT statement after each successful statement.
User enters BEGIN TRANSACTION. vsql does not recognize that string as one of its internal commands and sends the string to the server for processing.
Server recognizes the string as a valid SQL statement, compiles it, starts a transaction, and returns a successful result code to the client. Since autocommit is a client-side setting, the server has no idea about it.
Client, upon receiving the successful result code, issues COMMIT to the server.
Server complies and commits the transaction it has just started.

etc.


MongoDB to MySQL Replication Automatically i have Mongodb server and mysql server. i want to use mongodb for "write" and mysql for "read".

so i am looking for some tool or some process by which we can tranfer mongodb to mysql server on regular interval.

also i need to know the steps for mapping of mongo collection and mysql tables.

I cant find something similar on google so posted here.

Any help?

Thanks in advance.

You can use ETL tools like Talend,pentaho,informatica and other tools..my suggestion is that use Talend ETL tool it provides lots of features and its a open source tool.


Percona xtrabackup transaction log corrupted I am attempting to prepare and restore a full backup taken through xtrabackup via a scheduling script called surrogate.

I have tarred and gzipped the backup, transferred to a local machine running the same setup (aside from a slight version difference in xtrabackup - 2.2.12 on source, 2.3.9 local).

Upon extracting I am running the following command to prepare the backup for restore:

innobackupex --use-memory=1G --apply-log /home/matthew/full_2017-08-13_0700/

This is responding:

  innobackupex version 2.3.9 based on MySQL server 5.6.24 Linux (x86_64) (revision id: fde0e3e)
  xtrabackup: cd to /home/matthew/full_2017-08-13_0700/
  xtrabackup: This target seems to be not prepared yet.
  xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(661464509389)
  xtrabackup: using the following InnoDB configuration for recovery:
  xtrabackup:   innodb_data_home_dir = ./
  xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
  xtrabackup:   innodb_log_group_home_dir = ./
  xtrabackup:   innodb_log_files_in_group = 1
  xtrabackup:   innodb_log_file_size = 2097152
  xtrabackup: using the following InnoDB configuration for recovery:
  xtrabackup:   innodb_data_home_dir = ./
  xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
  xtrabackup:   innodb_log_group_home_dir = ./
  xtrabackup:   innodb_log_files_in_group = 1
  xtrabackup:   innodb_log_file_size = 2097152
  xtrabackup: Starting InnoDB instance for recovery.
  xtrabackup: Using 1073741824 bytes for buffer pool (set by --use-memory parameter)
  InnoDB: Using atomics to ref count buffer pool pages
  InnoDB: The InnoDB memory heap is disabled
  InnoDB: Mutexes and rw_locks use GCC atomic builtins
  InnoDB: Memory barrier is not used
  InnoDB: Compressed tables use zlib 1.2.8
  InnoDB: Using CPU crc32 instructions
  InnoDB: Initializing buffer pool, size = 1.0G
  InnoDB: Completed initialization of buffer pool
  InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
  InnoDB: Setting file ./ibdata1 size to 12 MB
  InnoDB: Database physically writes the file full: wait...
  InnoDB: Setting log file ./ib_logfile101 size to 2 MB
  InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
  InnoDB: New log files created, LSN=45781
  InnoDB: Doublewrite buffer not found: creating new
  InnoDB: Doublewrite buffer created
  InnoDB: 1 rollback segment(s) are active.
  InnoDB: Waiting for purge to start
  InnoDB: 5.6.24 started; log sequence number 0

  xtrabackup: error: The transaction log file is corrupted.
  xtrabackup: error: The log was not applied to the intended LSN!
  xtrabackup: Log applied to lsn 0
  xtrabackup: The intended lsn is 661464919534

Any idea what this is and how I overcome it?

DISCLAIMER : Not xtrabackup expert
Your situation is quite similar to a Jan 27, 2016 post on the Percona Forums. The poster, dbaffaleuf, was running percona server 5.6.24-72.2-log, innobackupex 2.3.3 Linux (x86_64).
After encountering your same message, dbaffaleuf wrote

Interestingly the xtrabackup_logfile file has been deleted from the backup path. If I save the xtrabackup_logfile before running the prepare, and restore it after, the first prepare crashes with transaction log corrupted, the next try to prepare works:
Restore the xtrabackup_log file for previous copy after full backup
$ cp /tmp/xtrabackup_logfile /my/path/to/backup/timestamp/

Run the prepare again:
$ /home/products/xtrabackup/bin/innobackupex --use-memory=1G --apply-log /my/path/to/backup/timestamp/ | tee -a /my/path/to/prepare/log/file.log

You may have to do the same

Save the xtrabackup_logfile before running the prepare
Restore the xtrabackup_log file for previous copy after full backup
Run the prepare again


Table1 ID is auto increment and i also want to store the information from table 2 against that auto generated ID This table has an auto increment ID on back end.

"INSERT INTO `test`.`personal_info`
    (`username`, `email`,`password`)
VALUES ('$username','$email','$password')";

This was for signing up of one page. Following is the page code which I want to link to upper table.

"UPDATE `test`.`personal_info`
WHERE Id='10' (`Name`, `Mid_name`, `Last_name`,`d_o_b`)
VALUES ('$Name', '$Mid_name', '$Last_name','$date')";

You want to update field ID and set to 10?

Some sample:

UPDATE test.personal_info
SET ID = '10'
WHERE db1.table1 = db2.table2

OR

 UPDATE test.personal_info
 SET ID = '10'
 ON <DB.TABLE>
 JOIN TABLE1.FIELD = DB.TABLE2.FIELD


Innodb mutex locking ?!? Inserts start stacking after 18-24hours, restart required We have a fairly high end box running mysqld 5.5.20.

The cpu configuration is dual quad core xeons.

It has 32 gb ram.

It has a quad intel ssd configuration running raid 0+1.

On a fairly busy service what starts to happen is that after about 18 to 24 hours of being up, once in a while, every few hours, there will be a 1min period where ALL inserts will start stacking. The strange thing is that using mytop even memory table inserts / updates are shown as executing for 'many' seconds.

More ofen then not even without a restart it will return to normal.... only to do it again.

A restart always fixed the issue, for another 18 to 24 hours.

All munins look ok.

Help!

Similar to my problem if you have the performance_schema enabled, solved after the upgrade of MySql server version.

Starting from 5.5.16 to 5.5.22 is affected by a deadlock.
No queries can be run when it happens:
Bug 13898343 - THREAD LOOPS ENDLESSLY IN LF_PINBOX_PUT_PINS WHILE HOLDING LOCK_THREAD_COUNT

An infinite thread loop could develop within Performance Schema,
causing the server to become unresponsive. (Bug #13898343)

See changelog

First of all try disabling performance_schema

Hope his helps


Data into Star Schema's fact or dimension table? I'm working on creating a Star Schema for my internship that I want to use for reporting on the company's pre-sales process. The aspects of the pre-sales process that I want to report on, are:

Sales proposals;
Task management.

The thing that I'm having trouble with is whether or not to separate proposals and tasks from one another in my Star Schema. For this reason, I've now created two different ones and I wonder if any of them is the "correct" way of doing it.

Star Schema 1, in which the proposal data has been separated from the fact table and put in a dimension table:

Star Schema 2, in which the proposal data and task data have been combined in the fact table:

These are the metrics that I eventually want to be able to report:

% of tasks finished on time
Workload over time
Due and completed proposals
Number of active submissions
Potential total sales
Potential total gross profit
Potential average gross profit % per sales team
% of submissions converted to orders

Which of these schema's do you guys think is better? I've been trying to wrap my head around this for some while now, but I can't figure it out. Star Schema 1 has less redundancy, but does have values that I want to use in a dimensional table. Star Schema 2, on the other hand, has a lot of redundancy because proposals can have multiple tasks (sometimes up to 20).

Also, a more general question regarding my schema's:
- Do all relationships have to be visualised in the Star Schema Diagram? For example: all dates in every table must be linked to the date table. And: all references to employees must be linked to the employee table.

P.S.
The application and its database are still being developed so I have no data that I can use to test it.

This is a very interesting, but perhaps also a bit too general as a question.

My general opinion is that if the cardinality of a dimension starts to approach that of the fact table, then there's no point in keeping a separate dimension. If you read Kimball carefully you'll see that on many occasions there's an implicit assumption that dimensions are small when compared to the fact table (by a few orders of magnitude at least).

In your case, you mention a proposal may have up to 20 tasks; you're pretty much on the verge of having a dimension that starts to be too large to be efficiently handled.

However, there's another thing you may have missed. What you're trying to achieve is counting proposals on any given state of their lifecycle. Again, if you go to Kimball, the solution would be an accumulating snapshot fact table. But this comes at a cost, which is the need to define beforehand how many milestones of an object's lifecycle it is worth tracking.

There's an alternative, though, which I developed a while back and blogged about last week, which is what I call a Status Change Fact table, where you track events (in your case, tasks) and add rows to mark each state change in the proposal's lifecycle (blog post here). The basic idea is that if a proposal has two tasks, A and B, that took place on days D1 and D2, you insert three rows on your fact table:

date;task;count
D1;A;+1
D2;A;-1
D2;B;+1

This model, combined with a periodic snapshot to count how many of which are in each state at any given time, should allow you to run simple queries and still get meaningful answers to your questions.

I've implemented this approach in a couple projects recently where objects are tracked through a system, with a good degree of customer satisfaction, even though customers had to be educated to what negative counts in the status change table mean.


How to compress a non-shrinkable table with nvarchar(max) fields? We have a serious problem which was on our minds for pretty much two weeks already. We have a big database (~250 GB, MS SQL Server 2014) and we needed to shrink it somehow, since our SSD is almost full. Quick analysis showed that 1) there is almost no free space available in the database for shrinking operation, 2) we have two very big tables (~95 GB, ~5M entries each) in the database. The database is being used by external software (ERP system) and we had everything we needed to clean these tables from software side (these are tables are being used by internal journals and old entries are not necessary), so we did just that, which resulted in ~2,7M entries being deleted (and they actually were deleted, which was confirmed by a query).
But there is the problem - the actual size of the tables did not change. The table with 2,3M entries still use about ~94 GB of disk space, almost exactly as it was when there were 5M entries. The amount of detected free space for shrinking (both DB and files) also didn't change. I restored database from fresh backup on another server, and tried to rebuild, and then delete and restore clustered index, but later learned that it wouldn't work, since the table has two nvarchar(max) fields (these fields store XML data and internal statuses as strings). I also tried to execute the following query as an experiment:
use uvs_experiment;

select * into _InfoRg3380Tmp from _InfoRg3380 where 1=2;

truncate table _InfoRg3380Tmp;

insert into _InfoRg3380Tmp select * from _InfoRg3380 where _Fld3382RRef = 0x80C7A4BF011CE3C311E847800C55C223;

This query was an attempt to create a table which would store entries with the only one, actually the most meaningful status. And the result... was the same. The new target table still use about 90GB of space, with ~1,3M entries. Please note that almost all the entries in the table have some data (sometimes heavy) in nvarchar(max) fields, excluded entries is not an exception, i.e. it wasn't blank entries which was deleted earlier or excluded by this query.

Just what is happening here and how we can free some space? I understand that this is some really strange behaviour of SQL Server regarding how it handles pages with nvarchar(max) fields, but we need a solution (aside from moving database to another server). Unfortunately, GZip compression from SQL Server 2016+ is not an option here.

We added compression for LOBs in CCIs exactly for this scenario in SQL 2016+.  If you can't upgrade, then likely you should get more disk space for the server as the easiest fix.


Alter All Data Types To Different Data Type I have a database that has multiple tables with datetime2 fields.  I need to convert all of these to datetime fields.  I ran this syntax that produced over 300 tables that need to be altered with roughly 1200 fields.

select
    so.name table_name
   ,sc.name column_name
   ,st.name data_type
from sysobjects so
inner join syscolumns sc on (so.id = sc.id)
inner join systypes st on (st.type = sc.type)
where so.type = 'U'
and st.name IN ('DATETIME2')
ORDER BY so.name ASC

Now, for speed, I thought I would write some syntax to do this for me, but I must have set-up something incorrectly as I allowed this to run for 30 minutes and it was still running.  If I run the print statements everything prints out as it should (at least to my semi-trained eye).  Is > 30 minutes acceptable time for these alter table statements to complete or is something slowing me down/should I not use a cursor?

Declare @sql nvarchar(max), @tablename varchar(100), @fieldname varchar(100)

Select
table_name
,column_name
FROM #Helper
Order by table_name asc

DECLARE cursor1 CURSOR FOR

Select
table_name
,column_name
FROM #Helper
ORDER BY table_name asc

OPEN cursor1

FETCH NEXT FROM cursor1 INTO @tablename, @fieldname

WHILE @@FETCH_STATUS = 0
BEGIN

Set @SQL = 'Alter Table '+@tablename+' Alter Column '+@fieldname+' datetime'
PRINT @SQL
--EXEC sp_executesql @sql;
FETCH NEXT FROM cursor1 INTO @tablename, @fieldname

END

CLOSE cursor1

DEALLOCATE cursor1

DROP TABLE #Helper

EDIT
I let the statement run a bit longer and discovered that it is actually hitting an error with one of the constraints that (could or could not) be causing the process to take so long.  I am trying to run this statement

Alter Table TestTable Alter Column datecreate datetime

And this error message is thrown:

  Msg 5074, Level 16, State 1, Line 1
  The object 'DF__Test_Table___datec__0D7A0286' is dependent on column 'datecreate'.
  Msg 4922, Level 16, State 9, Line 1
  ALTER TABLE ALTER COLUMN datecreate failed because one or more objects access this column.

Now if I script constraint as - create to this is the output that is generated:

ALTER TABLE [dbo].[TestTable] ADD  DEFAULT (getdate()) FOR [datecreate]

Nothing in there (to me) seems to be keeping this column as a datetime2 data type.  Do I need to add an extra check in my syntax to determine if the column has a constraint, and if it does, then drop the constraint and add it back?

Changing datetime2 to datetime is a fundamental data type change. Each ALTER will update every row in the specified table.  This can take a while depending on the size of your tables, especially if your largest tables have many datetime2 columns. Also, if there is any other concurrent activity, the DDL will be blocked by other activity.


enable replicaSet in mongoDB yaml conf file I want to enable oplog on mongoDB and the recommended approach is to setup a standalone replica set. I tried following the instructions here...

https://docs.mongodb.com/manual/reference/configuration-options/#replication-options

...and added the following to /etc/mongod.conf

replication:
   oplogSizeMB: 1024
   replSetName: rs0

After restarting I tried initialising and checking the the status but see the following...

> rs.initiate()
{
        "ok" : 0,
        "errmsg" : "This node was not started with the replSet option",
        "code" : 76,
        "codeName" : "NoReplicationEnabled"
}

> rs.conf()
2017-08-11T18:08:51.336+0900 E QUERY    [thread1] Error: Could not retrieve replica set config: {
        "ok" : 0,
        "errmsg" : "not running with --replSet",
        "code" : 76,
        "codeName" : "NoReplicationEnabled"
}

The error states run with the --repSet option, but if I try the following the error still appears

sudo /usr/bin/mongod --config /etc/mongod.conf --replSet rs0

Update
The config is being passed in

2017-08-13T21:19:03.708+0900 I STORAGE  [initandlisten] wiredtiger_open config: create,cache_size=63900M,session_max=20000,eviction=(threads_min=4,threads_max=4),config_base=false,statistics=(fast),log=(enabled=true,archive=true,path=journal,compressor=snappy),file_manager=(close_idle_time=100000),checkpoint=(wait=60,log_size=2GB),statistics_log=(wait=0),
2017-08-13T21:19:05.688+0900 I CONTROL  [initandlisten]
2017-08-13T21:19:05.689+0900 I CONTROL  [initandlisten] ** WARNING: Access control is not enabled for the database.
2017-08-13T21:19:05.689+0900 I CONTROL  [initandlisten] **          Read and write access to data and configuration is unrestricted.
2017-08-13T21:19:05.689+0900 I CONTROL  [initandlisten]
2017-08-13T21:19:05.690+0900 I CONTROL  [initandlisten]
2017-08-13T21:19:05.690+0900 I CONTROL  [initandlisten] ** WARNING: You are running on a NUMA machine.
2017-08-13T21:19:05.690+0900 I CONTROL  [initandlisten] **          We suggest launching mongod like this to avoid performance problems:
2017-08-13T21:19:05.690+0900 I CONTROL  [initandlisten] **              numactl --interleave=all mongod [other options]
2017-08-13T21:19:05.690+0900 I CONTROL  [initandlisten]
2017-08-13T21:19:05.690+0900 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/enabled is 'always'.
2017-08-13T21:19:05.690+0900 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
2017-08-13T21:19:05.690+0900 I CONTROL  [initandlisten]
2017-08-13T21:19:05.690+0900 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/defrag is 'always'.
2017-08-13T21:19:05.690+0900 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
2017-08-13T21:19:05.690+0900 I CONTROL  [initandlisten]
2017-08-13T21:19:05.912+0900 I FTDC     [initandlisten] Initializing full-time diagnostic data capture with directory '/var/lib/mongodb/diagnostic.data'
2017-08-13T21:19:05.915+0900 I REPL     [initandlisten] Did not find local voted for document at startup.
2017-08-13T21:19:05.915+0900 I REPL     [initandlisten] Did not find local replica set configuration document at startup;  NoMatchingDocument: Did not find replica set configuration document in local.system.replset
2017-08-13T21:19:05.916+0900 I NETWORK  [thread1] waiting for connections on port 27117

Check your mongod.log file, there should be line what lists all configuration items from you /etc/mongod.conf

2017-08-12T12:44:23.999+0300 I CONTROL  [initandlisten] options: { config: "/etc/mongod.conf", net: { port: 27017 }, replication: { oplogSizeMB: 1024, replSetName: "rs0" }, storage: { dbPath: "/var/lib/mongodb", journal: { enabled: true } }, systemLog: { destination: "file", logAppend: true, path: "/var/log/mongodb/mongod.log" } }

if you cannot see replication: { oplogSizeMB: 1024, replSetName: "rs0" } then it has NOT readed your current config file.

You can see what file it has readed when you look to start of that line config: "/etc/mongod.conf"

And remember, if your mongod is not running on standard 27017 port, you must adjust your commands to use the right port. Command rs.initiate() (without parameter), don't work if the port is something else than the default.

Update
If connecting to a specific port ensure you define it when using the client mongo --port 27117. For some reason the rs.initiate() command checks for a custom port based on the command line parameter and not what is defined in /etc/mongod.cof


join from different tables gives same result, but it shouldn't This is the code I'm using and I'm having a problem with it.

SELECT `disenos`.*, `votos`.`valoracion`, `usuario_contacto`.`nombres`,
`usuario_contacto`.`apellidos`, `users`.`username`, `users`.`avatar` as usuario_avatar,
COUNT(comentarios.id_comentario) as comentarios,
COUNT(votos.id_usuario) as votos
FROM (`disenos`)
LEFT JOIN `votos` ON `votos`.`id_diseno` = `disenos`.`id_diseno` AND votos.id_usuario = 1
LEFT JOIN `users` ON `users`.`id_usuario` = `disenos`.`id_usuario`
LEFT JOIN `usuario_contacto` ON `usuario_contacto`.`id_usuario` = `disenos`.`id_usuario`
LEFT JOIN `comentarios` ON `comentarios`.`id_diseno` = `disenos`.`id_diseno`
WHERE `disenos`.`estado` = 1 AND `disenos`.`votacion` = 1
GROUP BY `disenos`.`id_diseno` LIMIT 5

both COUNTs are giving me the same result although they shouldn't. If I get rid of the

LEFT JOIN `comentarios` ON `comentarios`.`id_diseno` = `disenos`.`id_diseno`

the COUNT(votos.id_usuario) as votos gives the correct result.

I've been dealing with this for the last 4 hours and can't seem to get it solved so I appeal to your wisdom :D

Thanks in advance!

the solution was to add a subquery :D
thanks!!

SELECT
    `disenos`.*,
    `usuario_contacto`.
    `nombres`,
    `usuario_contacto`.
    `apellidos`,
    `users`.`username`,
    `users`.`avatar` as usuario_avatar,
    `total_coment`,
    COUNT(votos.id_usuario) as votos
FROM (`disenos`)
LEFT JOIN `votos` ON `votos`.`id_diseno` = `disenos`.`id_diseno`
LEFT JOIN `users` ON `users`.`id_usuario` = `disenos`.`id_usuario`

LEFT JOIN
    (
        SELECT id_diseno, COUNT(id_comentario) as total_coment
        FROM `comentarios`
        GROUP BY `id_diseno`
    ) `comments`

ON `comments`.`id_diseno` = `disenos`.`id_diseno`
LEFT JOIN `usuario_contacto` ON `usuario_contacto`.`id_usuario` = `disenos`.`id_usuario`
WHERE `disenos`.`estado` = 1
AND `disenos`.`votacion` = 1
GROUP BY `disenos`.`id_diseno`
LIMIT 5

EDIT: correcting a typo, sorry


How many VLF's can be bad I have a database of size 840 GB which has 948 VLF's. On other hand there is one with size of 1.6 TB having 320 VLF's. Does that indicate the smaller database with that high count of VLF's is actually a problem? Also how can we decide that number to be too big or within OK range.

Please suggest

VLF count is only going to be a problem for recovery times if the combination of the count of VLFs and their size means the recovery process will require longer duration than your recovery-time-objective.

There is no single number of VLFs that is good or bad.  Very generally, I keep each VLF under 512MB, and keep the number of VLFs well under 1,000.

In order to know the actual recovery time for a given log file, you'll need to have identical hardware setup in a test environment, and test recovery of a full log where the database has been shutdown while active transactions are running.  If you have a database with hundreds of thousands of VLFs, be very scared about recovery time.  Ask me how I know.

You can view current info about VLFs by looking at DBCC LOGINFO in the context of the desired database.  The output from that command looks like:

╔════════════════╦════════╦═══════════╦═════════════╦════════╦════════╦════════╦═══════════╗
║ RecoveryUnitId ║ FileId ║ FileSize  ║ StartOffset ║ FSeqNo ║ Status ║ Parity ║ CreateLSN ║
╠════════════════╬════════╬═══════════╬═════════════╬════════╬════════╬════════╬═══════════╣
║ 0              ║ 2      ║ 268369920 ║ 8192        ║ 34     ║ 2      ║ 64     ║ 0         ║
║ 0              ║ 2      ║ 268369920 ║ 268378112   ║ 0      ║ 0      ║ 0      ║ 0         ║
║ 0              ║ 2      ║ 268369920 ║ 536748032   ║ 0      ║ 0      ║ 0      ║ 0         ║
║ 0              ║ 2      ║ 268369920 ║ 805117952   ║ 0      ║ 0      ║ 0      ║ 0         ║
╚════════════════╩════════╩═══════════╩═════════════╩════════╩════════╩════════╩═══════════╝

The Status column indicates the status of the given VLF, where 2 indicates the VLF has active log records and cannot be re-used until those records are backed-up/truncated (or in the case of mirroring, written to the mirror's log file).  Shrinking a log file (which is not typically recommended unless you have a problem) cannot reduce the size of the file below the point occupied by the last VLF with a Status of 2.  In my example above, shrinking the log file will allow me to get rid of the last 3 VLFs, however, I cannot shrink the 1st VLF since it is status 2.  If the 4th VLF had a status of 2, I would not be able to shrink the logfile at all until the log had been backed-up (in full recovery) or truncated (in simple recovery).

You can see file size and growth settings using this query:

SELECT [DB Name] = d.name
    , [File Name] = mf.name
    , mf.physical_name
    , Size = mf.size * 8192E0 / 1048576
    , MaxSize = mf.max_size * 8192E0 / 1048576
    , Growth = mf.growth * 8192E0 / 1048576
    , [Recovery Model] = CASE WHEN mf.type = 1 THEN d.recovery_model_desc ELSE '' END
    , [Log Reuse Wait Reason] = CASE WHEN mf.type = 1 THEN d.log_reuse_wait_desc ELSE '' END
FROM master.sys.databases d
    INNER JOIN master.sys.master_files mf ON d.database_id = mf.database_id
WHERE d.name = DB_NAME()
ORDER BY d.name
    , mf.type
    , mf.name;

Sample output for the query above looks like:

╔══════╦══════════╦═════════════════════════╦══════╦═════════╦════════╦══════════╦═════════════╗
║ DB   ║ File     ║ physical_name           ║ Size ║ MaxSize ║ Growth ║ Recovery ║ Log Reuse   ║
║ Name ║ Name     ║                         ║      ║         ║        ║ Model    ║ Wait Reason ║
╠══════╬══════════╬═════════════════════════╬══════╬═════════╬════════╬══════════╬═════════════╣
║ Test ║ MyFile   ║ D:\Data\Test_MyFile.bak ║ 125  ║ 1000    ║ 100    ║          ║             ║
║ Test ║ Test_DB  ║ D:\Data\Test_DB.mdf     ║ 384  ║ 2048    ║ 128    ║          ║             ║
║ Test ║ Test_Log ║ D:\Logs\Test_DB.ldf     ║ 256  ║ 2048    ║ 128    ║ SIMPLE   ║ NOTHING     ║
╚══════╩══════════╩═════════════════════════╩══════╩═════════╩════════╩══════════╩═════════════╝

You can see log space usage for SQL Server 2012+ using this query:

SELECT su.database_id
    , LogSizeMB = su.total_log_size_in_bytes / 1048576E0
    , LogUsedMB = su.used_log_space_in_bytes / 1048576E0
    , LogUsedPercent = su.used_log_space_in_percent
FROM sys.dm_db_log_space_usage su;

The sys.dm_db_log_space_usage output looks like:

╔═════════════╦═════════════╦═══════════╦════════════════╗
║ database_id ║ LogSizeMB   ║ LogUsedMB ║ LogUsedPercent ║
╠═════════════╬═════════════╬═══════════╬════════════════╣
║ 7           ║ 255.9921875 ║ 6.171875  ║ 2.410962       ║
╚═════════════╩═════════════╩═══════════╩════════════════╝

SQL Server 2016+ includes a Dynamic Management Function named sys.dm_db_log_info, which provides details about VLFs.

An example of how to use it:

DECLARE @DatabaseId tinyint = DB_ID('msdb');
SELECT DatabaseName = d.name
    , FileName = mf.physical_name
    , ddli.vlf_begin_offset
    , ddli.vlf_size_mb
    , ddli.vlf_active
    , ddli.vlf_status
FROM sys.dm_db_log_info (@DatabaseId) ddli
    INNER JOIN sys.databases d ON ddli.database_id = d.database_id
    INNER JOIN sys.master_files mf ON ddli.file_id = mf.file_id AND ddli.database_id = mf.database_id
ORDER BY d.name
    , mf.file_guid;

The output looks like:

╔══════════════╦══════════════════════════════╦══════════════════╦═════════════╦════════════╦════════════╗
║ DatabaseName ║           FileName           ║ vlf_begin_offset ║ vlf_size_mb ║ vlf_active ║ vlf_status ║
╠══════════════╬══════════════════════════════╬══════════════════╬═════════════╬════════════╬════════════╣
║ msdb         ║ F:\Data\msdb\log\msdblog.ldf ║         31064064 ║          10 ║          0 ║          0 ║
║ msdb         ║ F:\Data\msdb\log\msdblog.ldf ║         41549824 ║          10 ║          0 ║          0 ║
║ msdb         ║ F:\Data\msdb\log\msdblog.ldf ║         52035584 ║          10 ║          1 ║          2 ║
║ msdb         ║ F:\Data\msdb\log\msdblog.ldf ║         62521344 ║          10 ║          0 ║          0 ║
║ msdb         ║ F:\Data\msdb\log\msdblog.ldf ║         73007104 ║          10 ║          0 ║          0 ║
║ msdb         ║ F:\Data\msdb\log\msdblog.ldf ║         83492864 ║          10 ║          0 ║          0 ║
║ msdb         ║ F:\Data\msdb\log\msdblog.ldf ║         93978624 ║       10.37 ║          0 ║          0 ║
╚══════════════╩══════════════════════════════╩══════════════════╩═════════════╩════════════╩════════════╝

For aggregate details about the log, sys.dm_db_log_stats provides great info:

SELECT *
FROM sys.dm_db_log_stats(@DatabaseId)

Output includes the following columns:

database_id
recovery_model
log_min_lsn
log_end_lsn
current_vlf_sequence_number
current_vlf_size_mb
total_vlf_count
total_log_size_mb
active_vlf_count
active_log_size_mb
log_truncation_holdup_reason
log_backup_time
log_backup_lsn
log_since_last_log_backup_mb
log_checkpoint_lsn
log_since_last_checkpoint_mb
log_recovery_lsn
log_recovery_size_mb
recovery_vlf_count

For further details, I wrote a blog post on SQLServerScience.com about sizing your log file for the optimal number of VLFs.


To apply changes done to "/etc/my.cnf", do we need to restart mysqld service? I need to change max_connections parameter in /etc/my.cnf. After doing the change, do we need to restart mysqld service to apply the changes?
I'm on CentOS 7

Some server parameters are dynamic and can be changed on the fly. Some are not and should be configured in the my.cnf followed by service restart. max_connections can be changed on the fly by command SET GLOBAL max_connections=XXX. This value will be in effect until service restart.

To be clear, the possible options are:

Run SET GLOBAL max_connections = XXX for immediate use until service restarted
Add the max_connections = XXX line to the [mysqld] section of the my.cnf AND restart the service for permanent use
Run SET GLOBAL max_connections = XXX AND add the max_connections = XXX line to the my.cnf but DO NOT restart the service.

The third option allows to get both immediate changes as well as permanent but with zero downtime. Value in the my.cnf will wait for some event/incident/accident causing a service's restart.


How to find the detail cpu high process name which occupying most resource in the SQL Server database? I'm running into a problem when using the following SQL to identify CPU usage in the production SQL Server 2005 env.

DECLARE @ts_now bigint;
SELECT @ts_now = cpu_ticks / CONVERT(float, cpu_ticks_in_ms) FROM sys.dm_os_sys_info

SELECT TOP(10) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
               SystemIdle AS [System Idle Process],
               100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
               DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (
      SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
            AS [SystemIdle],
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
            'int')
            AS [SQLProcessUtilization], [timestamp]
      FROM (
            SELECT [timestamp], CONVERT(xml, record) AS [record]
            FROM sys.dm_os_ring_buffers
            WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
            AND record LIKE '%<SystemHealth>%') AS x
      ) AS y
ORDER BY record_id DESC;

Which shows the other CPU utilization is high in the dedicated SQL Server 2005 env.

SQL_Server_Process_CPU_Utilization  System_Idle_Process Other_Process_CPU_Utilization   Event_Time
1   79  20  11/18/2011 5:07
0   80  20  11/18/2011 5:06
1   77  22  11/18/2011 5:05
1   79  20  11/18/2011 5:04
1   78  21  11/18/2011 5:03
0   79  21  11/18/2011 5:02
0   79  21  11/18/2011 5:01
0   80  20  11/18/2011 5:00
0   80  20  11/18/2011 4:59
0   79  21  11/18/2011 4:58
1   78  21  11/18/2011 4:57
0   79  21  11/18/2011 4:56
0   80  20  11/18/2011 4:55
0   82  18  11/18/2011 4:54
3   77  20  11/18/2011 4:53
2   79  19  11/18/2011 4:52
0   79  21  11/18/2011 4:51
0   79  21  11/18/2011 4:50

I find the Other_Process_CPU_Utilization eat our db resource sometime and how can I get the detail process name for these kind of lists above through DMV? We can't login the SQL Server OS env directly so only DMV could tell us these information.

You can look at sys.dm_exec_query_stats to find queries which are causing the highest total_worker_time.

SELECT      TOP 20
            ST.text,
            QS.*
FROM        sys.dm_exec_query_stats          QS
CROSS APPLY sys.dm_exec_sql_text(sql_handle) ST
ORDER BY    total_worker_time


Applying Quotes Across Multiple Lines In SQL Server Management Studio you can use the shortcut CTRL + K > C or CTRL + K > U to comment/uncomment lines in bulk.

Is there a similar keyboard function that applies single quotes across a list of values? I have 150 IDs that I have copied from an Excel spreadsheet and I would like to execute a query that uses an IN clause on this list, and I dont want to manually add single quotes and comma around each item.

For example:

SELECT * FROM tbl WHERE someValue IN (
ABC1
ABC2
ABC3
ABC4
)

I want to highlight the values and using a keyboard shortcut turn the list into:

SELECT * FROM tbl WHERE someValue IN (
'ABC1',
'ABC2',
'ABC3',
'ABC4'
)

Native tooling approach

Grab a newer copy of Management Studio (2012 SP2 and 2014 are both free, fully functional, and can co-exist with your 2008 R2 tools). Then you can do this.

Put your cursor right before the first leading ABC.
Hold Shift+Alt, then hit the down arrow three times.
Type '.

It's subtle, but you should see a faint blue vertical line here indicating that keystrokes will actually affect all 4 lines (or 150 lines).

Excel approach

If you don't want to use a more modern version of Management Studio, then just add the stuff in Excel. Insert a new column, put a single-quote in the first cell where you want it, hover over the bottom right of the cell until the cursor becomes a solid +, then click and drag to the bottom:

Repeat for the second single-quote, commas, etc. Copy all, paste into SSMS, then do a quick replace for '<tab>, <tab>',, etc.

Regular expression approach

Your third alternative is to use a Regular Expression, valid with all versions of SSMS

Find what: {.+}
Replace with: '\1',
Look in: Selection
Expand Find Option
Use: Regular expression (checked)

That regular expression indicates find everything and remember what we found
Replace everything we found \1 by wrapping it with with tic marks and a comma

If you have more complex requirements, the right chevron next to the drop down arrow on Find what lists the regular expression dialect SSMS/Visual Studio understands


Any potential issues/ DoS risks with frequent connections/ queries to a SQL Server db? A database team suspects there may be an issue with a third-party ETL application opening, and closing, connections to a SQL server database repeatedly.

In terms of exact numbers --- it opens about 30 connections, runs 30 queries in parallel, each query lasts 10 seconds, therefore 10 seconds later, the queries end, and each connection is closed by the application. There is about 10 seconds of other application logic, then the process starts again. So we're talking 30 connections opening and closing every 20 seconds.

This is also over a remote connection, and it would be the same IP address each time making the query.

Would this cause a crash, memory problem, DoS defense?

I for one thought that databases were set up to handle frequent connections and queries.

Anyway this 'loop' would happen about 1000-2000 times (so connections would be opening and closing for about 8 hours or so).

Is there anything in particular I should be monitoring at the database level or wherever else? Would this number of open/ close connections (even with a lightweight query) impact performance on a sufficiently robust database? What exactly should I be looking for?

A shared database on the same server started becoming slow/ laggy/ - though this began two weeks after this ETL process had completed running. I'm not sure there was direct evidence that this number of connections was the issue, but the DBA thinks it's the most probable suspect. I had mentioned it to him before because I was getting a SQL error message:

  The semaphore timeout period has expired

I personally thought maybe this cryptic error meant there was rate-limiting going on. On second thought it may have been network connectivity, but it provided something to point at conveniently.

My question is what to look at, or monitor, or what evidence or data should be retrieved, to ascertain exactly if this process is causing performance issues? What questions should I ask in the ETL tool forum? I'm trying to figure out if this is a design problem in some regard and less connections should be opened at once, or if this database performance issue is actually completely unrelated.

Depending on how the server handles this, it might cause a potential problem. One technology that SQL Server implements to handle this kind of situation is "Connection pooling". With Connection Pooling, when the process closes its connection, SQL Server does not immediately remove the connection object from its memory. This way, if the process requests a new connection, SQL server can reuse the connection object instead of instantiating a new one. This process can be watched by monitoring the performance counters with perfmon. Here is a link to a MSDN blog for more information: http://blogs.msdn.com/b/sql_pfe_blog/archive/2013/10/08/connection-pooling-for-the-sql-server-dba.aspx


Perl DBI takes 60 seconds to connect to an Oracle database, every single time I feel like I must be doing something wrong with my SQL connections.  I've generally ignored it, but I'm finally going to try and get to the bottom of this.

my $dsn = "DBI:Oracle:host=$host;sid=$sid";

my $dbh = DBI->connect($dsn,$dbuser,$dbpass,{ RaiseError => 1,AutoCommit => 0 }) or die("Unable to connect: $DBI::errstr");

It takes 60 seconds any time I run DBI->connect.  Rather large queries might take a bit of time, network latency may induce some delay, but all of that is on top of the 60 seconds it takes to connect.  I can literally run DBI->connect and then immediately close it without actually running a query, and it will take 60 seconds.  I can query this database using different tools with no issue, but any time I run a DBI->connect against it, it takes 60 seconds.  Most of my queries come back with a time of 61.2 seconds, or 63.4 seconds, or 65.3 seconds.  No matter what, it's 60 seconds from the time the script hits DBI->connect to the time it hits the next line.  I've even timestamped it on the line before and the line after.  the only thing between the timestamps is DBI->connect.

What am I doing wrong?

Thanks to @ThisSuitIsBlackNot for pointing me to the tracing functionaility in DBI, I was able to track this down to an error with not specifying port.  I knew that I should be pointing at 1521 when setting up other connections in other programs, but DBI didn't require port to function, it was just 60 seconds of waiting for it to give results.  DBI assumes 1526 to be the target port first unless told otherwise, so when targeting a server running on 1521, it was timing out after 60 seconds before trying 1521.

Original:

DBI -> port 1526 -> no response -> wait 60s -> no response -> fail -> port 1521 -> success

Specifying port:

DBI -> port 1521 -> success


MS SQL Server Performance Issues After Enabling All CPU's My system specifications are;

Windows Server 2008 R2 Enterprise SP1
128 GB RAM
Intel Xeon E5-2620 (3 processors)
MS SQL Server 2012 Standard (11.0.2100.60)

There is 3 NumaNodes and 12 CPU's in them. 6 of them was checked.
Yesterday, I have checked all of them (12 CPU's) and after that performance issues started.

First, I've restarted SQL Server. Problems were still going on. After that I have restarted OS, still the same issues was going on.

At night, user called me for performance problems and i unchecked them to 6. I have restarted OS and still performance problems are going on.

When I look resource usage on Task Manager, only 1-2 of them working on %100 performance for 1 second. 7 GB RAM used.

Do you have any suggestions?

There is 3 NumaNodes and 12 CPU's in them. 6 of them was checked.
  Yesterday, i have checked all of them (12 CPU's) and after that
  performance issues started.

Processor affinity

You should look into these affinity settings, disabling / enabling these binds specific schedulers to specific cores. I would not advise to manually change these unless you absolutely know what you are doing.
More on setting processor affinity here and here.

If you must do this, look into enabling traceflag 8002 as a startup parameter and restarting SQL Server. This way the schedulers are not bound to cores in a 1 on 1 relationship.

The easier solution is letting SQL Server handle the affinity.

You can do this by right clicking the instance name in Object explorer in SSMS and checking the boxes next to Automatically set processor affinity mask for all processors and Automatically set I/O affinity mask for all processors.

  Here is the result: cost threshold for parallelism    5 max degree of
  parallelism   0

Maxdop & CTFP

You should try changing these settings, these are the default settings and not recommended in most cases. An idea / starting point could be to start with setting cost threshold for parallelism to 50.

How to change this can be found here

Setting Max degree of parallelism to 4 could be a starting point. How to change this can be found here.

Prevent worker splits

I would not set my maxdop higher than the least amount of cores per node configured with processor affinity, as to try to not go cross numa.

But this is harder to force than only changing CTFP & maxdop as you can read in a part of this blogpost by Joe Obbish.

  ...This plan won’t work if any of the following occur:

  Any query runs with a query level MAXDOP hint other than 0, 1, 10, or
  Any query is downgraded in MAXDOP but still runs in parallel.
  A parallel stats update happens. The last time I checked these run with a query level MAXDOP hint of 16.
  Something else unexpected happens ...

In the same post, the most reliable method as to not go cross node:

  The most reliable method by far is to have a single hard NUMA node, so
  if you have a VM that fits into a single socket of a VM host and you
  care about performance then ask your friendly VM administrator for
  some special treatment.

Source

Max memory setting for hard numa.

You should also look into setting your max memory leaving some for the OS and divisible by 3 for Hard-NUMA.

Since you are on SQL Server 2012 standard edition, the max amount of memory for your buffer pool = 64 but you could set more for the rest of the caches.
More on that here

Upgrading to at least SQL Server 2014 standard edition will give you the ability to use 128GB for the buffer pool, but I would advise to to a higher version than 2014.

An idea could be capping at 98304MB = 96GB of max memory.

Configuration commands:

EXEC sys.sp_configure N'cost threshold for parallelism', N'50'
GO
EXEC sys.sp_configure N'max degree of parallelism', N'4'
GO
EXEC sys.sp_configure N'max server memory (MB)', N'98304'
GO
RECONFIGURE WITH OVERRIDE
GO

Remember to monitor your queries and act accordingly after changing these settings. If some of these settings do not improve your situation, tweak them.

Getting help in the future

If there are other issues with certain queries, adding the actual query plan & as much information to a different question will help in getting answers faster. You should also check with the infrastructure team if any of these cores are overcommitted.

There should normally not be a reason to set your affinity to use 6 cores if you could use 12 dedicated cores (as you are within 4 sockets / 24 cores for your standard licensing).


Can I find all JSON scalars in PostgreSQL 9.3? We are using Postgres 9.3 and making use of the JSON column which was new to that version. Version 9.4 added a LOT of utility functions for handling JSON, as well as the new JSONB column. You can see this by comparing http://www.postgresql.org/docs/9.4/static/functions-json.html with http://www.postgresql.org/docs/9.3/static/functions-json.html

9.4 has a function, json_typeof which returns the type of a JSON object (as a string: 'string', 'number', 'array', 'object', etc.) I would like to know if there is any practical way in 9.3 of retrieving all rows which are scalars (or, equally useful for us, which are not objects).

We would like the comment column of a given table to always be a JSON object. This means that we can do queries like:

SELECT comment->>'author' as author_name
FROM our_table

If for some row the JSON in the comment column is an object, but which does not have any such key, you simply get back a NULL.

However, if there is even one scalar in the table, the query fails with the error

ERROR: cannot extract element from a scalar
SQL state: 22023

This is a pain, as we have had strings written to this column some of the time, mainly due to errors. Is there any reasonable way of either filtering these out in a query, or identifying them all so that they can be removed in one go?

Horrible as it is, you could write a PL/PgSQL function that attempts to extract an element from the object, traps the exception if it fails, and returns true on success or false on exception.

Untested:

create or replace function json_isobject(obj json)
returns boolean immutable language plpgsql as $$
begin
    begin
        perform obj->'';
    exception
      when invalid_parameter_value then
        return false;
      when others then
        raise;
    end;
    return true;
end;
$$;

This'll be pretty inefficient, since it's creating a sub-transaction for each call.

Or you could just update to 9.4.

On 9.4 I can't reproduce your reported error. Trying to look up a key in a json scalar returns null.

test=> select ('{"k":"v"}'::json) -> 'k' -> 'blah';
 ?column?
----------

(1 row)

... and the same with using ->> to dereference a scalar.

So I'm not sure if this function will work on 9.3, and it won't on 9.4. To be 9.4-compatible you'd have to test if current_setting('pg_version_num') >= 90400 and return a result based on json_typeof instead.

You might need to adapt it to use the same expression you use to trigger your reported error.


How can I edit a replication subscription to include new articles? I have some replication subscriptions and I need to add some tables to one of the subscriptions. Is this possible?

Publisher/Distributor: Sql 2000
Subscriber: Sql 2008

Thank you.

edit: I found this article. http://blogs.msdn.com/b/repltalk/archive/2011/03/15/how-to-add-an-article-to-an-existing-transactional-subscription-initialized-through-backup.aspx

I'm assuming that this is the only way.

It's been a while since I've looked at SQL 2000, but in the newer versions you can just add an article by editing the properties of the publication in the UI then add articles.  You'll have to create and deploy a new snapshot but that's easy enough.  Just start the snapshot agent and it'll build and then push the snapshot to the subscriber.


Meaning of 'show variables LIKE '%_database' Despite being a newbie in db administration, I have been assigned to move our crm from mysql to mssql. I have reached a point where I have met a query I dont understand and it cannot execute in sql server.
show variables LIKE '%_database'

The following is the code it:

public static function checkDbUTF8Support($conn) {
    global $db_type;
    if($db_type == 'pgsql')
        return true;
    $dbvarRS = $conn->Execute("show variables like '%_database' ");
    $db_character_set = null;
    $db_collation_type = null;
    while(!$dbvarRS->EOF) {
        $arr = $dbvarRS->FetchRow();
        $arr = array_change_key_case($arr);
        switch($arr['variable_name']) {
            case 'character_set_database' : $db_character_set = $arr['value']; break;
            case 'collation_database'     : $db_collation_type = $arr['value']; break;
        }
        // If we have all the required information break the loop.
        if($db_character_set != null && $db_collation_type != null) break;
    }
    return (stristr($db_character_set, 'utf8') && stristr($db_collation_type, 'utf8'));
}
}

Can someone assist me with an explanation and a possible solution

Show Variables is not ANSI SQL, it's MySQL specific, so it won't run across database providers.

Reading through that code it looks like it's trying to determine if the database provider will support UTF8.

If you Google it, the Show Variables is a MySQL command which returns information about the current database.

I think you will have to replace this whole section with different code to determine if the database in use will support UTF8, which I suspect is dependent on the collation.

You can find this information in the sys. tables.

Googling MSSQL Server and UTF8 seems to indicate that MS don't explicitly support UTF8, however.

This is an answer to your question "what is show variables", not a solution to your problem "how can my application run a query to determine UTF8 support in the database?"

HTH


Retrieving data from SQLServer mutitenant database I have a multitenant database with something like the following naming structure:

Sch1.Logins   => 2 records
Sch2.Logins   => 4 Records

Now I want to be able to "Execute as" different users , say when user1 tries to run the query "select * from Logins", the results should be returned from the Sch1.Logins (2 records) and when user2 runs the same query, it would return records from Sch2.Logins.

How can I do this?
What settings are required for each user?

Set the default schema for the users. You can see this in the user properties window through SQL Server Management Studio.

Or script:

ALTER USER user1
WITH DEFAULT_SCHEMA = Sch1;


Postgresql Run 'Jobs' At Specific Times I have a normal problem to anyone that can't trust on applications to do their 'work/ AKA be online' and needs a DB Stored Procedure to run every minute .

Scenario :

PostgreSQL 9.2
Need To Run A SP To 'Compile' Information That has been inserted into the database.

No I Can't trust the App to be online and call this on the client side.
Job(s) must execute every minute.
Unfortunately I can't use trigger based or any 'EventBased' Model since I don't know when all the data has arrived.
The cluster is made of headless machines.
I could install  CRON on every server but that could cause unnecessary chatter
inside the Cluster , DeadLocks , Calls at 3 AM , Pain , etc

Problem 1:
Now my problem is PGAgent requires ( As far as I know) a GUI , X and so on things I would like to avoid specially for 'just' running jobs.

Problem 2:
Also what if the server that runs the Agent ( call it Master ) dies the cluster will self heal but will the agent survive?

So how do you do it?

pgAgent does not require a graphical user interface at all. It is a process that runs as daemon on unix.
pgAdmin is used for configuring the job details. The configuration is stored in database postgres where pgAgent will read it.
Once you configured your jobs, you may disconnect pgAdmin safely.


Fixing a query that returns failed job information I' using a SQL Server 2008 Sp3 environment.

I have a query that returns information about failed jobs from the last 10 minutes.
If a job has two schedulers (for example - a job that runs every 10 minutes in the morning, and every 30 minutes in the rest of the day), the query returns two rows.
Can you help me fix the query so it would return only the relevant row of the relevant scheduler that was used ?

The query I'm using is pasted below:

SELECT h.server
,j.name
,h.step_id
,h.step_name
,h.message
,h.run_status
,Rundatetime        = CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121)
,h.run_duration
,s.last_outcome_message
,sc.name AS ScheduleName
,s.last_run_time
,sjc.next_run_time
FROM msdb..sysjobhistory h
INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
INNER JOIN msdb..sysjobservers  s ON s.job_id=h.job_id
LEFT OUTER JOIN msdb..sysjobschedules  sjc ON sjc.job_id=h.job_id
LEFT OUTER JOIN msdb..sysschedules  sc ON sc.schedule_id=sjc.schedule_id
WHERE run_status=0
AND step_name<>'(Job outcome)'
--Looking on failed job from the last 10 minutes.
AND CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121) > DATEADD(mi, -10, GETDATE())
ORDER BY h.run_date DESC ,h.run_time DESC

Thanks in advance,
Roni.

This is a little scrappy, but it is a start...

I have extracted the Schedule ID from the [last_outcome_message] field, and joined it to the Schedule ID.  That should give you the correct result set

I could not find anywhere else where the schedule_id is stored against a History
Event.

SELECT h.server
,j.name
,h.step_id
,h.step_name
,h.message
,h.run_status
,Rundatetime        = CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121)
,h.run_duration
,s.last_outcome_message
,sc.name AS ScheduleName
,s.last_run_time
,sjc.next_run_time
FROM msdb..sysjobhistory h
INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
INNER JOIN msdb..sysjobservers  s ON s.job_id=h.job_id
LEFT OUTER JOIN msdb..sysjobschedules  sjc ON sjc.job_id=h.job_id

     AND CONVERT(INT, LTRIM(RTRIM(REPLACE(LEFT(last_outcome_message,
                   PATINDEX('% (%', last_outcome_message)),
                   'The job failed.  The Job was invoked by Schedule ', ''))))
          = sjc.schedule_id

LEFT OUTER JOIN msdb..sysschedules  sc ON sc.schedule_id=sjc.schedule_id
WHERE
run_status=0
AND step_name<>'(Job outcome)'
--Looking on failed job from the last 10 minutes.
AND CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121) > DATEADD(mi, -10, GETDATE())
ORDER BY h.run_date DESC ,h.run_time DESC


Include non-inserted rows as result of INSERT INTO ... RETURNING I'm using PostgreSQL 11.
I want to conditionally insert values into a table while having the result of the insertion include a null for each row of the input which did not result in an insertion.

For example

CREATE TABLE all_sums (sum INTEGER);
SELECT
    CASE WHEN a_sum IS NULL THEN null
         ELSE (SELECT sum FROM (INSERT INTO sums (sum) VALUES (sum) RETURNING sum))
    END
FROM
    (SELECT a + b FROM (VALUES (1, null), (null, 2), (2, 3)) AS row (a, b))
AS a_sum;

should result in the table all_sums looking like:

all_sums: sum
         ------
           5
         (1 row)

but the output of the query should be:

 null
 null
 5
------
(3 rows)

This example fails due to a syntax error:

ERROR:  syntax error at or near "INTO"

Is there any way to achieve the desired query output?

(For context: My reason for doing this is because there are further queries which rely on knowing whether the insertion occurred for a particular row.

This is part of an effort to insert some data more efficiently from a file by transposing my queries from one per row to one per column. I'm not looking for other tips in improving insert speed though and if it's not possible I'm happy to call it a day at this point.)

I would simply separate the query logic and the insert logic:

with vals (a,b) as (
  VALUES (1, null), (null, 2), (2, 3)
), new_rows as (
  insert into all_sums (sum)
  select a + b
  from vals
  where a + b is not null
)
select a + b
from vals;

Online example