Query speed optimization I have several tables that I'm trying to combine with the below query. I'm using the following tables:

LoanOrigination: This contains loan characteristics such as asset value, loan term, etc. There is a single unique observation per loan. This table is indexed on LOAN_ID,SUB_SAMPLE and COLLATERAL_TYPE.
LoanPerformance: This contains the performance of all loans in LoanOrigination. Each row is a unique MONTHLY_REPORTING_PERIOD and LOAN_ID combination, and the table has been indexed on both.
CollateralData: This contains historical values based on the COLLATERAL_TYPE. The purpose of this is to estimate what loan's current value to the collateral.

The purpose of the query below is to combine these tables so that each row contains loan characteristics along with the delinquency statuses in the current and following month. However, the query is extremely slow. Is there anything that can be done to speed it up?

with

COLLATERAL_VALUES as (
    select
        COLLATERAL_TYPE,
        dateadd( day, 1-day(AsOfDate), AsOfDate) as ASOFDATE,
        Value as INDEX
    from LoanData.CollateralData
),

SAMPLE_LOANS as (
    select
        a.*,
        b.INDEX as INDEX_T0
    from LoanData.LoanOrigination a
    join COLLATERAL_VALUES b on b.ASOFDATE = a.ORIG_DATE and b.COLLATERAL_TYPE = a.COLLATERAL_TYPE
    where SUB_SAMPLE = 0
),

LOAN_STATE as (
    select
        a.LOAN_ID,
        MONTHLY_REPORTING_PERIOD AS CUR_DATE,
        CURRENT_ACTUAL_UPB as CUR_UPB,
        LOAN_AGE,
        cast(CURRENT_LOAN_DELINQUENCY as smallint) AS CUR_DLQ_STATUS
    from LoanData.LoanPerformance a
    where
        CURRENT_LOAN_DELINQUENCY <> 'XX' and
        exists ( select LOAN_ID from SAMPLE_LOANS )
),

LOAN_TRANSITION as (
    select
        c.*,
        a.CUR_DATE,
        a.CUR_DLQ_STATUS,
        a.CUR_UPB,
        a.LOAN_AGE,
        b.NEXT_DLQ_STATUS
    from LOAN_STATE a
    join (
        -- adding next state transition
        select
            LOAN_ID,
            DATEADD( month, -1, CUR_DATE ) as PRIOR_DATE,
            CUR_DLQ_STATUS as NEXT_DLQ_STATUS
        from LOAN_STATE
    ) b on a.LOAN_ID = b.LOAN_ID and a.CUR_DATE = b.PRIOR_DATE
    join SAMPLE_LOANS c on a.LOAN_ID = c.LOAN_ID
)

select
    a.*,
    CUR_UPB / ( ASSET_VALUE * ( b.INDEX / a.INDEX_T0 )) AS LTV
from LOAN_TRANSITION a
join COLLATERAL_VALUES b on a.CUR_DATE = b.ASOFDATE and a.COLLATERAL_TYPE = b.COLLATERAL_TYPE

You have a problem whenever you join using ASOFDATE , such as here:

on a.CUR_DATE = b.ASOFDATE

, because ASOFDATE is defined as dateadd( day, 1-day(AsOfDate), AsOfDate). To me this seems to mean “first of the month”, and hopefully there’s no time component involved.

So, I’d add a new column to the first CTE, called maybe AsOfDateOrig.

COLLATERAL_VALUES as (
select
    COLLATERAL_TYPE,
    dateadd( day, 1-day(AsOfDate), AsOfDate) as ASOFDATE,
    Value as INDEX
    ,AsOfDate as AsOfDateOrig
from LoanData.CollateralData
),

And then involve that in your join clause too.

on a.CUR_DATE = b.ASOFDATE
and b.AsOfDateOrig >= a.CUR_DATE and b.AsOfDateOrig < dateadd(month,1,a.CUR_DATE)

It might seem redundant to have both, but it allows the QO to use either method as the main Seek Predicate.

Now do the same for everywhere else you join using that.

I’d consider doing the same for PRIOR_DATE too, basically avoiding joins that involve an expression unless you have the inverse of it too.

Finally, look at your indexing strategy.

You’ll need an Index on LoanData.CollateralData (COLLATERAL_TYPE, AsOfDate) INCLUDE (Value), for example, and similar indexes on the other tables.


Laggy slave OK to have only one system user in processlist? In all the examples of a processlist on a mysql slave I've seen, there are two system users.  I have a slave that is very far behind the master.  When I do show a processlist, there is only one system user (State: "Waiting for master to send event"), the event scheduler, and a trigger user.  Is this a symptom, cause, or nothing to do with excessive lag?

Both servers are 5.6.19 on FreeBSD.  Topology: master --> slave --> slave in question.  Slave IO and SQL threads show as running.  Relay_Log_Space is very large.

show slave status\G
*************************** 1. row ***************************
           Slave_IO_State: Queueing master event to the relay log
              Master_Host: 10.02.00.0
              Master_User: repl
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: master-bin.004634
      Read_Master_Log_Pos: 473351239
           Relay_Log_File: slave-relay-bin.004460
            Relay_Log_Pos: 506886175
    Relay_Master_Log_File: master-bin.004515
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
          Replicate_Do_DB:
      Replicate_Ignore_DB:
       Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
               Last_Errno: 0
               Last_Error:
             Skip_Counter: 0
      Exec_Master_Log_Pos: 506886012
          Relay_Log_Space: 62863753813
          Until_Condition: None
           Until_Log_File:
            Until_Log_Pos: 0
       Master_SSL_Allowed: No
       Master_SSL_CA_File:
       Master_SSL_CA_Path:
          Master_SSL_Cert:
        Master_SSL_Cipher:
           Master_SSL_Key:
    Seconds_Behind_Master: 245653
Master_SSL_Verify_Server_Cert: No
            Last_IO_Errno: 0
            Last_IO_Error:
           Last_SQL_Errno: 0
           Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
         Master_Server_Id: 42
              Master_UUID: a36f5f4b-0904-11e4-a130-b8ca3a622758
         Master_Info_File: /var/lib/mysql/master.info
                SQL_Delay: 0
      SQL_Remaining_Delay: NULL
  Slave_SQL_Running_State: closing tables
       Master_Retry_Count: 86400
              Master_Bind:
  Last_IO_Error_Timestamp:
 Last_SQL_Error_Timestamp:
           Master_SSL_Crl:
       Master_SSL_Crlpath:
       Retrieved_Gtid_Set:
        Executed_Gtid_Set:
            Auto_Position: 0
1 row in set (0.01 sec)

show processlist\G
*************************** 1. row ***************************
 Id: 1
User: event_scheduler
Host: localhost
 db: NULL
Command: Daemon
Time: 74
State: Waiting for next activation
Info: NULL
*************************** 2. row ***************************
 Id: 87720
User: system user
Host:
db: NULL
Command: Connect
Time: 8650
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
 Id: 87721
User: routine_user
Host: localhost
 db: db_name
Command: Connect
Time: 246665
State: closing tables
Info: SELECT
    IFNULL(CAST(LEFT(CONCAT('0', charValue), 10) as decimal(18,9)), 0)
INTO
    converted_

Having replication with only one system user sounds a lot like MySQL 4.1 replication. It was single threaded. That does not sound anything like MySQL 5.x replication.

When you run SHOW SLAVE STATUS\G, you will see something like this:

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.48.20.253
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000262
        Read_Master_Log_Pos: 803779735
             Relay_Log_File: relay-bin.067799
              Relay_Log_Pos: 402744
      Relay_Master_Log_File: mysql-bin.000262
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 803779735
            Relay_Log_Space: 402598
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

Please note the two lines

           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes

When you see Yes to both of these lines, there must exist two process IDs with system user as the user. If you only see one, then either the IO thread (network connection back to the Master) is gone, or the SQL thread (load thread to read relay logs) encountered a MySQL error.

Thread States for Replication

IO is Yes and SQL is Yes : Replication should be running
IO is No and SQL is Yes : Network Connection to Master is gone
IO is Yes and SQL is No : SQL Error reading from Relay Logs
IO is No and SQL is No

Someone or Something ran STOP SLAVE;
MySQL started up with skip-slave-start configured

If both Slave_IO_Running and Slave_SQL_Running are Yes and Relay_Log_Space keeps on increasing, then my guess would be that LOAD DATA INFILE was executed on the Master and all the data is being shipped through the relay logs as a single unit (See my post MySQL 5.6 showing wrong "second_behind_master").

The only other thing that can cause

Slave_IO_Running to be Yes
Slave_SQL_Running to be Yes
Relay_Log_Space to be be increasing

Would be a very heavy write transaction with row-based replication executed as a group commit.

UPDATE 2014-11-17 17:30 EST

Based on the chat room conversation, we have discussed

Over 2TB of InnoDB data, so setting up replication from scratch is out of the question
Discovered that binary logging is enabled on the Slave
I proposed that binary logging on the Slave maybe causing the lag

I recent encountered this with a client
They lag one extra second every two seconds
I disabled binary logging on the Slave and it worked

My suggestion for this post is  to disable binary logging on the Slave like this

STOP SLAVE;
SET GLOBAL innodb_fast_shutdown = 0;
vi /etc/my.cnf
Comment out log-bin=master-bin
service mysql restart
START SLAVE;


grant permissions in oracle from one database to another I have two databases, employeedb and contractordb. The employeedb has a user called employeeuser and contractordb has a user called contractoruser. (These are the users I log in with in Oracle SQL Developer). When I am logged into contractordb as contractoruser and I issue the command grant select on table_name to employeeuser; it fails saying 'ORA-01917: user or role 'employeeuser' does not exist'. I get the same thing if I log in as SYS. If I use the GUI and go to "Privileges->Grant" I also don't see the user there. How can I allow employeeuser to select from one of contractoruser's tables?

If you actually have different databases (and it sounds like you do), you can't grant permissions for users in one database to access data in another database.  You'd need to create a database link in the EmployeeDB database that connects to the ContractorDB database.  That database link can either use a fixed username and password in the ContractorDB database (i.e. create an EmployeeUserRemote user in ContractorDB with a password that doesn't change that can be hard-coded into the database link definition) or that database link can be a CURRENT_USER database link in which case the username and password would need to match in the two databases for every user that wants to use the database link.  Queries that use the database link log in to the remote database (ContractorDB) as the specified user and have the privileges of that user in the remote database.


SQL Server 2008 upgrade to SQL Server 2012 on a cluster I have a Windows 2008R2 2 node cluster with SQL Server 2008 clustered. How do I prevent the cluster from failing over while I am applying the SQL Server 2012 to the alternate node first? Any pointers on upgrading the SQL Server cluster to 2012?

The Setup.exe will do the dirty work for you and take the passive node (currently upgrading node) away from being a possible owner of the SQL FCI's virtual network name (VNN).  But if you want to ensure that this is happening (which I recommend, it's what I always do), then you can manually take the passive node you're about to upgrade off of the possible owners list for the FCI's VNN.

You could use the GUI (failover cluster admin) or PowerShell to accomplish this task.  Below is an example of the latter:

Get-Cluster -Name "your cluster name" |
    Get-ClusterGroup -Name "SQL Server (your instance name)" |
    Get-ClusterResource -Name "SQL Network Name (your FCI's VNN)" |
    Set-ClusterOwnerNode -Owners "current active node"

All of the parameter strings there need to be specific to your environment.  Note, calling Set-ClusterOwnerNode will set the list of possible owners for that cluster resource.  Depending on how many nodes are in your cluster, that could contain more than one string (i.e. Set-ClusterOwnerNode -Owners "ACTIVENODE", "SOMEOTHERNODE", "ETC.").

Something to be knowledgeable about, there is a KB for a fix for a failover cluster instance upgrade from SQL Server 2008 [R2] to SQL Server 2012.  I recommend you review it before the operation.

As for other tips and tricks, I would recommend you read this BOL reference on upgrading a SQL Server Failover Cluster Instance.  There is a lot of great information in that article that should be considered.


How to delete row from table later than date? I have a simple table:

MyTable:
-Id int primary key
-Name varchar(MAX)
-Deleted bit

Now i want delete rows created later that 15.12.2014. But how can you see i don't have a any date columns in my table.
It's possible to do in MS SQL SERVER 2008 R2?

Restore the backup you have from 15.12.2014 to a new database and then you can delete the rows from your production database that does not exist in your restored backup database.

There is no hidden datetime information in the table that you can use.


What is the correct way to declare and use MarisDB user defined variables? I'm using MariaDB version 10.3.28-MariaDB-cll-lve
This site https://mariadb.com/kb/en/user-defined-variables/ says

User-defined variables names must be preceded by a single at character (@)

That's what I usually do but recently I was rushing, forgot the '@ and wrote the following function which seems to work fine without the variables having '@' at the front.
So is having a '@' in front of a user defined variable a firm requirement or not?
If it is, why does my function work correctly without it?
DELIMITER $$
CREATE  FUNCTION get_next_redacted_member_id() RETURNS INT(11)
BEGIN
       DECLARE result INT SIGNED;
       DECLARE lastid INT;
       INSERT INTO redacted_member_id ( date_used) VALUES ( NOW() );
       SET lastid = (SELECT LAST_INSERT_ID() );
       SET result =  -1 * lastid ;
       RETURN result;
END$$
DELIMITER ;

The main difference ist that you can use DECALRE ony in stored function and procedzres

DECLARE var_name

This statement is used to declare local variables within stored programs.

see manual

User-Defined Variables

User-defined variables are variables which can be created by the user and exist in the session.

see manual
Which means every user defined variable is accessible every where(in a session) not only in a stored procedure/function. So you set your user defined Variable in a stored procudure, you can get the value eve after the stored procedure ends. That is very helpful for debugging such procedures/functions.
Also when you DECLARE a variable, you set also its data type and size.
With user defined MysQL decides what it needs when You SET @a = 123455


standard steps to Restart Seconday server in Logshipping setup I have to reboot my Logshipping secondary server.
what are the steps need to be taken on primary and secondary servers to make the reboot clean and without hurting logshipping.

env : SQL server 2005 SP4  64bit
windows server 2008 R2 enterprise

What I usually do with our 15-minute log shipping secondary servers is just wait until the last log is copied over and restored. Then you can reboot the secondary server (assuming that by server you mean a separate physical server). As soon as the secondary comes back up, it will copy over the missing transaction log file and restore them.

We restore our secondary in STANDBY, so some people query them once in a while, just make sure you let the appropriate people know that you're about to reboot the server.


Is there a way to "SELECT and TRUNCATE" without losing new data? I have an application that writes raw log files to a table called new in a PostgreSQL database -- lots of rows and lots of duplicate data. Via a cron job running every minute, I normalize the raw data and insert the normalized results into a table called summary, after which I truncate the new table.

The normalization is done all via SQL, but I realize that it may take some time do execute the SQL statement, during which time additional data may get inserted into the new table. This leads me to believe that said additional data inserted while the SQL statement is running may get lost when the table is truncated.

Not knowing exactly how PostgreSQL (or any other SQL-compliant database) handles access to tables when SQL statements are being executed, I'm not exactly sure what to do to ensure no data is lost. Is there a way to "move" all the data in the new table into a temporary table and perform the normalization on the data in the temporary table, leaving the new table empty until new data gets inserted (which will likely happen during the normalization of the data in the temporary table)? Perhaps there's additional/better approaches to this problem, perhaps using transaction blocks?

---- EDIT ----

It never fails... every time I write up a question on Stack Exchange, it gets me to think about the question in different ways and thus leads me to new terms to search for on Google. Happened this time too. As a result, is this answer a potential solution for what I'm wanting to do here? Is it the best solution, given it's now quite dated?

There is more than one possibility here.

One is what Peter Eisentraut suggested in the post you linked, using a writable CTE which deletes the rows from the table which will be processed or inserted elsewhere in the same query.

This approach may be viable with small datasets.  However, there are chances that some table bloat will occur with time, or concurrent inserts and deletes clash on the disk I/O level.

Another way to solve this is using transaction blocks, as you also suggested.  If there are open transactions (that do the inserts, for example) on new, the TRUNCATE command will wait until these are closed, then it will remove everything.  This means that the problem you anticipate really exists :)

Consider the following:

BEGIN;

LOCK new; -- takes an exclusive lock

INSERT INTO staging_table SELECT * FROM new;

TRUNCATE new;

COMMIT;

In this case, the exclusive lock will make all concurrent INSERT statements wait until the COMMIT.  As the INSERT in this block should be relatively fast, the waiting time won't be terribly long, while your existing data is safely moved to an other table.  Then comes the TRUNCATE, finally the COMMIT will release the lock and the waiting inserts can be done.

This way you can move the data processing from new to the newly introduced table.


How do I merge and delete duplicated rows in SQL using UPDATE for SQL server 2014? For example, I have a table of:
id | code | name | type | deviceType
---+------+------+------+-----------
1  | 23   | xyz  | 0    | web
2  | 23   | xyz  | 0    | mobile
3  | 24   | xyzc | 0    | web
4  | 25   | xyzc | 0    | web

I want the result to be:
id | code | name | type | deviceType
---+------+------+------+-----------
1  | 23   | xyz  | 0    | web&mobile
2  | 24   | xyzc | 0    | web
3  | 25   | xyzc | 0    | web

How do I do this in SQL Server using UPDATE and DELETE statements?
Note: Unfortunately I can't use the solutions given because SQL server 2014 does not have 'STRING_AGG' function.
Any help is greatly appreciated!

please, try this:
with stable as (
SELECT
    ROW_NUMBER() over(order by [code]) as [ID]
    , [code]
    , [name]
    , [type]
    , CASE
        WHEN (SUM([web]) = 1 AND SUM([mobile]) = 1) THEN 'web&mobile'
        WHEN (SUM([web]) = 1 ) THEN 'web'
        WHEN (SUM([mobile]) = 1) THEN 'mobile'
        ELSE null END AS deviceType
FROM (
    select [id], [code], [name], [type], [web], [mobile]
    from (
        SELECT [id], [code], [name], [type], [deviceType] FROM [dbo].[table]
    ) as source
    pivot
    (
        COUNT(deviceType) FOR deviceType IN ([web],[mobile])
    ) as tb
) source
group by [code], [name], [type])
,ttable as (
SELECT [id], [code], [name], [type], [deviceType] FROM [dbo].[table]
)
MERGE
    INTO ttable as T
    USING stable as S
    ON t.id = S.id
    WHEN MATCHED THEN
        UPDATE SET
            T.[code] = S.[code], T.[name] = S.[name], T.[type] = S.[type], T.[deviceType] = S.[deviceType]
    WHEN NOT MATCHED BY SOURCE THEN
        DELETE
;


How often does the Impala StateStore refresh? I am using Hive and Impala on the same cluster. I find that when I create new tables, the Impala StateStore does not refresh automatically even after a few hours.

I know that I can accomplish this by running "refresh" in impala-shell (in a cron job if need be), but I would like to know: how often does impalad refresh metadata and can I set this interval to be shorter? If so, how?

It is my understanding that impala does not automatically refresh the metadata.  If you create new tables etc through impala (1.2 and higher) it will update the other impala nodes, however if you create new tables etc in hive you will have to manually refresh the impala metadata.

You can manually refresh the metadata on either a table basis with 'REFRESH table_name' or you can do this for all tables with 'INVALIDATE METADATA'.  Also, note that if you update the files on HDFS you will have to refresh the table to pickup the new data as well.


Migrating from Oracle to MySQL via MySQL workbench: Table list is empty MySQL workbench offers a migration tool that I'm using to migrate an Oracle database to MySQL.
I'm using the migration wizard to migrate the complete Oracle schema to MySQL. I used that for the migration wizard and a RDBMS source thru odbc to connect the oracle source.
Step 1 : Source is configured and tested
Step 3 : Introspection is OK

Step 4 : Table list is empty

Unfortunately, the object list is empty, so I can't get the next step.
A short test of the odbc driver through excel confirmed that the odbc is correctly configured and displays the table list.

mysql workbench 6.3
odbc driver oracle 11.2
java 1.8
Oracle database 10g

Like @bauerInHsv said, Oracle has a vested interest in providing minimal support for migrating off of its enterprise offering (Oracle) and onto its open-source RDBMS (MySQL).

As an alternative to MySQL Workbench, you could use etlalchemy.

It is an open-source Python tool, that lets you migrate between any 2 relational database with 4 lines of Python.

To install:

pip install etlalchemy
# On El Capitan:
#### pip install etlalchemy --ignore-installed

To run:

from etlalchemy import ETLAlchemySource, ETLAlchemyTarget

oracle_db_source = ETLAlchemySource("oracle+cx_oracle://username:password@hostname/SID")

mysql_db_target = ETLAlchemyTarget("mysql://username:password@hostname/db_name",
                                       drop_database=True)
mysql_db_target.addSource(oracle_db_source)
mysql_db_target.migrate()

This will handle the migration in the following order:

Migrating the Schema
Migrating the Data
Migrating the Indexes
Migration the Constraints

(P.S. I wrote this tool, so feel free to reach out if you find something broken.)


Remove an IP address from server config What is the safe way to remove an old IP address from SQL Server configuration?

The scenario is, I have a server with 2 NIC's that should have been configured for teaming but weren't at the time SQL was installed.  Each had a different IP so SQL picked up on that and added each IP to its configuration.

The NICs are now teamed and the box is down to a single IP.  I want to remove from SQL configuration the IP that is no longer assigned to the server.  I don't want to just deactivate it, but rather I want to completely remove it.

Is there a safe way to do this?  Or does one just delete the keys under HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.[Instance Name]\MSSQLServer\SuperSocketNetLib\Tcp\ related to the IP in question and restart?

Having searched quite a bit and come up empty I decided to just go ahead and remove the registry keys for the IP address I wanted to delete from the configuration.

Steps:

Backup the registry key HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\
Stop SQL services
Go to HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.[Instance Name]\MSSQLServer\SuperSocketNetLib\Tcp\ (must do this for each SQL instances on the server)
Find the IP# key that contains the IpAddress value of the address you want to remove where # is a number from 1 to the number of IP addresses SQL found on your machine during install
Delete the IP# key found in step 4
If the number in the keyname is not the last one, rename the remaining IP# keys to be sequential starting with 1.
Restart SQL Services

After doing this the old IP address was no longer available in SQL Server Configuration Manager and SQL Server no longer tried to listen on that IP address.  There were no longer any "Listening on [old address]" entries in the SQL Log and netstat -an showed no listeners as well.


Alter in PL SQL Loop I just started learning PL/SQL an hour ago... I'm trying to set profile settings dynamically... I select distinct profile names from dba_users and I'm trying to run an alter profile command for each profile name. How can I get the alter command to work in a loop like this?

SQL> SET SERVEROUTPUT ON
SQL> declare
  2  begin
  3     for rec in (select distinct Profile from dba_users) loop
  4       EXECUTE IMMEDIATE 'alter profile :1 LIMIT PASSWORD_LIFE_TIME UNLIMITED' using rec.PROFILE;
  5       EXECUTE IMMEDIATE 'alter profile :1 LIMIT PASSWORD_REUSE_MAX UNLIMITED' using rec.PROFILE;
  6     end loop;
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-00931: missing identifier
ORA-06512: at line 4

Figured it out. I saw documentation about the using keyword which apparently wasn't what I needed. Instead I needed to use string concatenation ||.

Here's the working version:

SET SERVEROUTPUT ON
declare

begin
    for rec in (select distinct Profile from dba_users) loop
     EXECUTE IMMEDIATE 'alter profile ' || rec.PROFILE || ' LIMIT PASSWORD_LIFE_TIME UNLIMITED';
     EXECUTE IMMEDIATE 'alter profile ' || rec.PROFILE || ' LIMIT PASSWORD_REUSE_MAX UNLIMITED';
    end loop;
end;
/


Verifying Oracle*Net network encryption I've been struggling to implement Oracle's network encryption between two of my test boxes.  I think I've finally been able to do it, but I can't seem to find a way to verify that it is indeed enabled.

I know that tracing is enabled by default on the server side, but is there a command I can run on the remote sqlplus instance that will generate a trace and allow me to see if the specific sql is encrypted?  Is there a better way to test this?

Thanks everyone!

I too am looking for a better way to do this, but what I've found is that you can enable tracing on the client side and then search the tracefile that was created.

I have this in my client-side sqlnet.ora file:

DIAG_ADR_ENABLED=off
TRACE_DIRECTORY_CLIENT=/tmp
TRACE_FILE_CLIENT=nettrace
TRACE_LEVEL_CLIENT=16

If you're running the client from the same server, you can override TNS_ADMIN to point to a different directory (you may need to copy tnsnames.ora, etc, to that directory as well).


Outputting result as 2 separate columns So I created a procedure that calculates the average and median of a string array of numbers:

CREATE PROCEDURE dbo.spAvg_Median
    @input nvarchar(20)
AS
BEGIN
    declare @split table(id int primary key identity (1,1), number int)

    INSERT INTO @split SELECT value FROM string_split(@input, ',')

    SELECT Average FROM (
        SELECT SUM(number)/COUNT(number) as [Average]
        FROM @split
        ) AS T1
    UNION
    SELECT Median FROM (
        SELECT o.number AS [Median], rn=ROW_NUMBER() OVER (ORDER BY o.number), c.c
        FROM @split AS o
        CROSS APPLY (SELECT c=COUNT(*) FROM @split b) AS c
        ) AS x
        WHERE rn IN ((c + 1)/2, (c + 2)/2)
END
RETURN 0
GO

However, when you run this query you get the following result:

EXEC dbo.spAvg_Median'1,5,9,8,7'

What I want it to output is Average in one column and Median in another column next to it.

The UNION command is there so it doesn't output it as two different queries like this, since that is not what I want:

According to rule to find median(average if even number of values & middle number if odd number of values). It can be done like this.

CREATE PROCEDURE dbo.spAvg_Median
    @input nvarchar(20)
AS
BEGIN
    declare @split table(id int primary key identity (1,1), number int)
    declare @count int

    INSERT INTO @split SELECT value FROM string_split(@input, ',')
    select @count=count(*) from @split

        SELECT AVG(A.number) as [Average],B.Median
            FROM @split A
        CROSS APPLY
                    (
                    SELECT Median FROM (
                    SELECT
                              o.number AS [Median]
                            , rn=ROW_NUMBER() OVER (ORDER BY o.number ASC)
                    FROM    @split AS o
                    ) AS x
                    WHERE rn =ROUND(@count/2.0,0)
                    ) B
        GROUP BY B.Median
END
RETURN 0
GO

Thanks!


How to changes or update local server connection in Maintenance plan job Two days back our client changed one of our Dev Server name

After Server Renamed, All my maintenance jobs and other jobs are failing because server name mismatch.

We are using sql server 2012 version and server 2008 OS

So today morning i renamed my Sql server 2012 name to updated given name and made table, procedures updates

I tried to update Local server connection in maintenance job but it is uneditable.
Then i added new server connection,still no use i am getting below error, while executing jobs.

After i tried with target page in jobs property option, there also only target server is selected and multiple target server is disable.

Error below

Executed as user:
NT Service\SQLSERVERAGENT.
Microsoft (R) SQL Server Execute Package Utility  Version 11.0.2100.60 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.
Started:  12:01:28 AM  Error: 2013-12-16 00:01:43.98     Code: 0xC00291EC     Source: {410F7661-F71A-4B68-9584-BA422AB00F02} Execute SQL Task
Description: Failed to acquire connection "Local server connection".
Connection may not be configured correctly or you may not have the right permissions on this connection.  End Error
Error: 2013-12-16 00:02:00.00
Code: 0xC0024104
Source: Territory_Update
Description: The Execute method on the task returned error code 0x80131904
(A network-related or instance-specific error occurred while establishing a connection to SQL Server.
The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)).
The Execute method must succeed, and indicate the result using an "out" parameter.  End Error
Error: 2013-12-16 00:02:15.00
Code: 0xC0024104
Source: {4E2AF328-0B8D-4905-83BE-839FDDEFC09C}
Description: The Execute method on the task returned error code 0x80131904
(A network-related or instance-specific error occurred while establishing a connection to SQL Server.
The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)).
The Execute method must succeed, and indicate the result using an "out" parameter.
End Error  DTExec: The package execution returned DTSER_FAILURE (1).
Started:  12:01:28 AM
Finished: 12:02:15 AM
Elapsed:  46.641 seconds.
The package execution failed.
The step failed.

Help me out in this guys, Thanks in advance

Maintenance plans make use of SSIS packages that are stored in MSDB. These packages use connection strings, which are not changed after a server rename.

Use the script (reproduced below) posted by NancySon in the comments of How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server to get some inspiration on how to change these connection strings. Or recreate the maintenance plans.

  Maintenance plans don't get their connections changed to the new server name and so they may break. After a rename you may find that you cannot delete or rename the existing maintenance plans, so either delete them before renaming the server and recreate them afterwards or run the following script to fix them:

   use msdb

   DECLARE @oldservername as varchar(max)
    SET @oldservername='<server name>\<instance name>'

   -- set the new server name to the current server name

   declare @newservername as varchar(max)
    set @newservername=@@servername

   declare @xml as varchar(max)
    declare @packagedata as varbinary(max)
    -- get all the plans that have the old server name in their connection string
    DECLARE PlansToFix Cursor
    FOR
    SELECT    id
    FROM         sysssispackages
    WHERE     (CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX)) LIKE '%server=''' + @oldservername + '%')

   OPEN PlansToFix

   declare @planid uniqueidentifier
    fetch next from PlansToFix into @planid

   while (@@fetch_status<>-1)  -- for each plan

   begin
    if (@@fetch_status<>-2)
    begin
    select @xml=cast(cast(packagedata as varbinary(max)) as varchar(max)) from sysssispackages where id= @planid  -- get the plan's xml converted to an xml string

   declare @planname varchar(max)
    select @planname=[name] from  sysssispackages where id= @planid  -- get the plan name
    print 'Changing ' + @planname + ' server from ' + @oldservername + ' to ' + @newservername  -- print out what change is happening

   set @xml=replace(@xml,'server=''' + @oldservername + '''','server=''' + @newservername +'''')  -- replace the old server name with the new server name in the connection string
    select @packagedata=cast(@xml as varbinary(max))  -- convert the xml back to binary
    UPDATE    sysssispackages SET packagedata = @packagedata WHERE (id= @planid)  -- update the plan

   end
    fetch next from PlansToFix into @planid  -- get the next plan

   end

   close PlansToFix
    deallocate PlansToFix

  ----- This will also handle the packages that have a tag such as
    ----- <DTS:Property DTS:Name="ConnectionString">Data Source=servername;Integrated Security=SSPI;Connect Timeout=30;</DTS:Property>

   DECLARE @oldservername as varchar(max)
    SET @oldservername='<server name>\<instance name>'-- set the new server name to the current server name
    declare @newservername as varchar(max)
    set @newservername = @@servername
    declare @xml as varchar(max)
    declare @packagedata as varbinary(max)-- get all the plans that have the old server name in their connection string
    DECLARE PlansToFix Cursor FOR
    SELECT id
    FROM sysssispackages
    WHERE (CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX)) LIKE '%Data Source=' + @oldservername + '%')

   OPEN PlansToFix
    declare @planid uniqueidentifier
    fetch next from PlansToFix into @planid
    while (@@fetch_status<>-1) -- for each plan
    begin
    if (@@fetch_status<>-2)
    begin
    select @xml=cast(cast(packagedata as varbinary(max)) as varchar(max))
    from sysssispackages where id= @planid -- get the plan's xml converted to an xml string
    declare @planname varchar(max)select @planname=[name] from sysssispackages where id= @planid -- get the plan name
    print 'Changing ' + @planname + ' server from ' + @oldservername + ' to ' + @newservername -- print out what change is happening
    set @xml=replace(@xml,'Data Source=' + @oldservername,'Data Source=' + @newservername) -- replace the old server name with the new server name in the connection string
    select @packagedata=cast(@xml as varbinary(max)) -- convert the xml back to binary
    UPDATE sysssispackages SET packagedata = @packagedata WHERE (id= @planid) -- update the plan
    end
    fetch next from PlansToFix into @planid -- get the next plan
    end
    close PlansToFix
    deallocate PlansToFix


Limiting Generated Sequence Values in Multi-table Insert In Oracle, given the following objects:

create table a (x number );
create table b (val number );
create table c (val number );
create sequence my_seq;

Suppose I populate table a as follows:

insert into a (x) values (1);
insert into a (x) values (2);
insert into a (x) values (3);
insert into a (x) values (4);

Using the values in a, I want to populate tables b and c as follows: when a.x is even, generate a new number from the sequence and insert my_seq.nextval into b.val and c.val; otherwise, insert my_seq.currval (the most recently generated value but not a new one) into c.val.

What I currently have is an insert all statement that looks like this:

insert all
    when mod(x,2) = 0 then
        into b (val) values (my_seq.nextval)
        into c (val) values (my_seq.currval)
    else
        into c (val) values (my_seq.currval)
select x from a

Since Oracle's treatment of sequences in an insert all statement is to generate a new value for each row returned in the subquery, I'm getting 4 newly generated values inserted into c. How can I attain the behavior I want, 2 newly newly generated values inserted once into b and twice into c?

It can't be done because you're mixing curval and nextval (happy to be proven incorrect, by the way). You also can't use WITH xxxx AS in an INSERT ALL, which was my first thought as a way around this.

Anyway, this is a logical workaround for you:

insert all
    when mod(x,2) = 0 then
        into b (val) values (my_seq.nextval/2)
        into c (val) values (trunc(my_seq.currval/2+0.5))
    else
        into c (val) values (trunc(my_seq.currval/2+0.5))
select x from a;

Obviously the sequence number is always going to be double what you expect if you use it in other code, but I'm assuming that this is a 1-off statement.


Financial Inst - Model Role+Relationships b/w Person and Account A Person can be on one-or-more Accounts. For each Account a Person is on, Person has a certain role on that account i.e.

Person 1 is primary on Account 1

Person 1 is joint on Account 2

Person 1 is beneficiary on Account 3

Note:
An Account only has 1 primary Person, but can have any number (0+) of joint or beneficiary

How should this be modeled? I at least forsee these tables:

Person
- person_id
- name
- dob
- address

Account
- account_id
- creation_date

I'm unclear on representing a many-to-many relationship between Person and Account while simultaneously capturing the role (primary, joint, beneficiary).

My idea: Have multiple role Link tables like so:

Link_Primary_Person_Account
- person_id
- account_id

Link_Joint_Person_Account
- person_id
- account_id

Link_Beneficiary_Person_Account
- person_id
- account_id

What do you think? What are other ways of doing this? This is all I can come up with.

Four tables will solve and normalize the data:

Person
- person_id
- name
- dob
- address

Account
- account_id
- creation_date

PersonAccountRole
- person_id (PK)
- account_id (PK)
- role_id
- etc...

Role
- role_id
- role_descrip
- etc...


Database Wrapper Design issue: No operator matches the given name and argument type(s) I'm building a wrapper for DB access using AWS' Lambda functions Java, the issue I'm having is that Lambda parameters are JSON, thus typeless, so all incoming params are de-JSON'd into Strings, and when adding them to a PreparedStatement  using the setObject(param, value) method they all get treated as if you were using setString(param, value), this is fine for Strings and numbers, but when it comes to a parameter that is a timestamp or date, we get a parameter type mismatch like this:

org.postgresql.util.PSQLException: operator does not exist: timestamp with time zone >= character varying
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

I'm aware that passing parameter types as an extra field for the JSON might help, but I'm worried about both the extra overhead and, the need of manually adding the parameter type to the JSON, as it's currently being built "automagically" from the underlying DTO using GSON.

Can anyone figure out a different approach, perhaps on passing the parameters to the PreparedStatement.?

You'll need to explicitly convert your datetime strings to appropriate data types. Since you chose not to include the SQL statement in question, I can only guess what it's like, but if it looks anything like

...WHERE timestamp_column > ?...

you'll need to replace it with

...WHERE timestamp_column > TO_TIMESTAMP(?, 'YYYY-MM-DD HH24:MI:SS')...

using, of course, an appropriate format string.


What are the right ways to analyse and tune up innodb_thread_concurrency in mysql 5.7? I'm in a plan to increase thread concurrency in my production MySQL server 5.7 but not sure, before what are the params/configurations that need to be measured and configured to increase the thread concurrency to the right optimal value. When checked with the MySQL official documentation, there seems to be a note stating

The correct values of these variables depend on your environment and workload.Try a range of different values to determine what value works for your applications. Before limiting the number of concurrently executing threads, review configuration options that may improve the performance of InnoDB on multi-core and multi-processor computers

When referred with other blogs, came up with some formulas as

concurrency = 2 x no.of cpu

And in some other similar blogs, there they stated along with disk as

concurrency = 2 * (NumCPUs + NumDisks)

How to benchmark my MySQL workload to tune up with the thread concurrency?

Currently, I'm having

thread concurrency = 8
RAM = 64 GB
CPU = 20 core

Occasionally, There seems to be sudden increase in overall thread usage by slow queries and all 8 threads were under operation resulting in making normal running queries to get slowed down :(

Hence what's the right way to tune up with thread concurrency ?

Set it to 0 and let InnoDB pick the concurrency.

20 cores with currency=8 means that at least 12 cores will always be idle.

What is the value of Max_used_connections?  If that is not very high, the "concurrency" is not your main problem.

Meanwhile, let's see one of the 'slow' queries, together with SHOW CREATE TABLE; there may be ways to speed it up, possibly decreasing the I/O.

For more analysis of your VARIABLES and GLOBAL STATUS, let's see them.  http://mysql.rjweb.org/doc.php/mysql_analysis#tuning


What Triggers This Warning: Type Conversion in Expression May Affect "CardinalityEstimate" in Query Plan Choice Using SQL Server 2016 I'm trying to understand why I receive this warning while running this T-SQL statement (I chose the table variable for ease of testing. This happens with DB tables that have columns of int type as well):

declare @test as table(
    col1 int
)

insert into @test (col1)
values(30500600)

select LEFT(test.col1, 2)
from @test as test

30 is returned as expected, but upon examining the Execution Plan I see a warning for the SELECT statement that reads:

  Type conversion in expression
  (CONVERT_IMPLICIT(varchar(12),[test].[col1],0)) may affect
  "CardinalityEstimate" in query plan choice

If I run the same above without pulling from a table:

select LEFT(30500600, 2)

30 is returned as expected and there is no warning in the Execution Plan.

Both statements should be performing LEFT() on int type so I don't understand why one returns a warning and the other doesn't. What's going on here?

Edit:

I've tried changing the col1 datatype declaration to varchar and that clears up the error. But that still doesn't explain to me why select LEFT(30500600, 2) by itself doesn't cause a warning if I'm passing an int directly to the LEFT() function.

I've tried cast to varchar in the original query: LEFT(cast(test.col1 as varchar(12)), 2) which returns the same warning.

Possible Duplicate

The linked duplicate states that computed columns were the issue for that user. There are no computed columns in my example, so that isn't the issue. Which leads me to the second part of the answer regarding implicit conversion to string types using the CONCAT function which is also the same for the LEFT function. In the second paragraph of my edit above I used CAST to explicitly convert to varchar so I wouldn't think that a conversion would be taking place? I'm obviously missing something here.

The reason there is no "type conversion / cardinality estimate" warning in this query:

SELECT LEFT(30500600, 2);

...is because there is no cardinality estimate.  It's a constant expression being passed into a deterministic function - guaranteed to only return one "row."  I think the explanation is as simple as that, since the warning is more about the potential cardinality problems (rather than just the simple existence of the type conversion).

If I get the estimated execution plan for that statement, it's just a single "select without query" element:

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.518" Build="13.0.5081.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementId="1" StatementText="select LEFT(30500600, 2)" StatementType="SELECT WITHOUT QUERY" RetrievedFromCache="false" />
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

For what it's worth, even the table variable version doesn't display the warning in SQL Server 2017 (because it's really a pointless warning when the conversion can't possibly affect a cardinality estimate - as in your example).

To be clear, that warning isn't always pointless.  Take this example:

SELECT message_id
INTO #SomeNumbers
from sys.messages;

SELECT *
FROM #SomeNumbers
WHERE LEFT(message_id, 2) = '50';

This puts 280,192 rows into a temp table.  The SELECT query has this warning, and the warning is legit.  SQL Server estimates there are 28,019.2 matching rows (10% of the table), when in fact there are only 2,156 matching rows (<1% of the table).

Regarding your example where you cast this input into the LEFT function, notice that the warning has changed slightly from CONVERT_IMPLICIT to just CONVERT:

  Type conversion in expression (CONVERT(varchar(12),[test].[col1],0)) may affect "CardinalityEstimate" in query plan choice

So you're just replacing an implicit conversion with an explicit one, which still warrants a warning.


database duplication VS database replication Is there a difference between duplication and replication with reference to a database?

I am not sure if I am correct at all, but duplication means to me that we are copying bit by bit without understanding what the data is about, while replication is more like logical copying, where we are copying at say, database level. Am I correct?

Is there more to duplication and replication?

Database duplication generally refers to restoring a physical backup of a database to a different server (preferably using RMAN).  That is normally done periodically to refresh lower environments from production.

Database replication generally refers to the process of copying a subset of data from one database to another on an ongoing basis.  Replication generally implies that the data is being copied from one production database to another production database (or a test database to a test database, etc.)


How can I check if one specific login has any database users mapped on it? I've create some logins and database users. So I would like to accomplish the following.
Drop user from database:
USE [dbname]
GO
IF EXISTS (SELECT 1 FROM sysusers WHERE NAME = 'username')
    DROP USER [username]

Here I would like to check if the login has any database users in other databases and if not drop login:
USE [master]
GO
DROP LOGIN [username]
GO

use this query:
IF OBJECT_ID(N'tempdb..#results', 'U') IS NOT NULL
    DROP TABLE #results;

CREATE TABLE #results(
    LoginName sysname
    ,LoginType nvarchar(60)
    ,IsMustChange bit
    ,DatabaseName sysname NULL
    ,DatabaseUserName sysname NULL
    ,DatabaseRoleName sysname NULL
    );

EXEC sp_MSforeachdb '
USE [?];
INSERT INTO #results
    SELECT
        sp.name AS LoginName
        ,sp.type_desc AS LoginType
        ,CAST(LOGINPROPERTY ( sp.name , ''IsMustChange'' ) AS bit) AS IsMustChange
        ,DB_NAME() AS DatabaseName
        ,dp.name AS DatabaseUserName
        ,r.name AS DatabaseRoleName
    FROM sys.server_principals sp
    LEFT JOIN sys.database_principals dp ON
        dp.sid = sp.sid
    LEFT JOIN sys.database_role_members drm ON
        drm.member_principal_id = dp.principal_id
    LEFT JOIN sys.database_principals r ON
        r.principal_id = drm.role_principal_id
    WHERE sp.name = ''SomeLogin''';

SELECT * FROM #results;

IF OBJECT_ID(N'tempdb..#results', 'U') IS NOT NULL
    DROP TABLE #results;


unloading data from oracle dbf files I have a list of oracle dbf files and I need to restore the information inside these files, there are no control files and anything else. only DBF file
http://s9.postimg.org/mxmx0g73j/Untitled.jpg

Is there any parsing tool or recovery tool that can open DBF files and unload the information in it..

There are 3 alternatives:

create a new controlfile and try to add those file in one or more tablespaces, they look part of a partitioned object (you might want to use dbverify before running create controlfile)
use UNIX strings command, this should recover some information, but not all...
use the BBED tool as documented here:
http://orafaq.com/papers/dissassembling_the_data_block.pdf
this is a powerful tool but takes a bit of time to master

Also, if you have Oracle Support contract you might try contacting them, it is their format,
it is the easiest path to "success".. but probably the most expensive.


Suppressing the SSMS prompt to save changes Is it possible to configure SQL Server Management Studio (SSMS). so that the dialog box shown above does not appear when I close an unsaved query window?

In SQL Server 2016 this is apparently coming.

The pre-release docs says

  New option to skip prompt to save T-SQL files We have added a new
  option under "Tools|Options|Query Execution|SQL Server" to control if
  a prompt is shown for unsaved T-SQL files.

In previous versions there is no setting to suppress this warning.

A connect item was raised about this by Erland Sommarskog and closed as won't fix with the explanation.

  Hello Erland -

  I did a review with some of the other product teams and to stay
  consistent within our guidelines we're not going to be able to do
  this. Excel, Visual Studio and other products won't let you save with
  changed content, so we have to pop the message.

  Thanks!

  Buck Woody, SQL Server Program Manager

The Connect page is returning an error at the moment so here is a cached copy of it.


Storing historical records and extracting data from a given period This a question about database modeling and PostgreSQL. This post partially answers the question but I need advice on more technical stuff: columns and query to extract the data.

I need to store administrative boundaries history within a given territory in order to be able to track boundaries movements back over the passed years.

Here is an example on how boundaries move:

Martin Land area has always existed
Bulwers Land area has always existed too
Those areas were merged together in 2015:

The new area was renamed as Martin-Bulwers
The new area kept Bulwers Land code (Martin Land code becoming deprecated)

Here is the way I store the data:

gid  | code   | name            | change_date
-----+--------+-----------------+-------------
1    | 86001  | Martin Land     | 2000-01-01
2    | 86002  | Bulwers Land    | 2000-01-01
3    | 86002  | Martin-Bulwers  | 2015-01-01

I have set a default change_date to 2000-01-01 for historical situation of an area, i.e. before the first changement occurs.

Then, I have many different cases: areas that have remained unmerged, areas that have merged with others at different dates. This can produce the following example:

CREATE TEMPORARY TABLE foo AS
SELECT gid,code,area,change_date::date FROM ( VALUES
  ( 1,86001,'Martin Land'   ,'2000-01-01' ),
  ( 2,86002,'Bulwers Land'  ,'2000-01-01' ),
  ( 3,86002,'Martin-Bulwers','2015-01-01' ),
  ( 4,86003,'Coveral Land'  ,'2000-01-01' ),
  ( 5,86004,'Big Tom Area'  ,'2000-01-01' ),
  ( 6,86005,'Small Tom Area','2000-01-01' ),
  ( 7,86004,'Tom Land'      ,'2016-01-01' )
) AS t(gid,code,area,change_date);

Then I have difficulties writing a query that could return the list of areas of a given year. I tried to rely on DISTINCT ON() clause but this does not the job I need...

For instance, the following query will return the following table:

SELECT DISTINCT ON (code) code, area, change_date

  FROM myTable WHERE change_date < '2016-01-01'

  ORDER BY code, change_date DESC ;

-- Result:

code   | area            | change_date
-------+-----------------+-------------
86001  | Martin Land     | 2000-01-01
86002  | Martin-Bulwers  | 2015-01-01
86003  | Coveral Land    | 2000-01-01
86004  | Big Tom Area    | 2000-01-01
86005  | Small Tom Area  | 2000-01-01

This is partially right as before 2016-01-01, Big Tom Area and Small Tom Area had not merged together but Martin Land and Bulwers Land had! There were 5 areas in 2014, then 4 areas in 2015 and 3 in 2016.

In fact, I would need the following result:

code   | area            | change_date
-------+-----------------+-------------
86002  | Martin-Bulwers  | 2015-01-01
86003  | Coveral Land    | 2000-01-01
86004  | Big Tom Area    | 2000-01-01
86005  | Small Tom Area  | 2000-01-01

Is there another way to store these informations or to write the query?

The problem is that when an area is merged and its code is reused, you have that information, but when an area is merged and its code is deprecated, you don't have it. In other words, you don't have the information that an area is deprecated.

Now, I see two solutions. First, keep the schemas as is and whenever an area is deprecated, add a new row as it was a new area and just states it is deprecated, e.g.

INSERT INTO myTable(code, area, change_date)
VALUES(86001, 'deprecated', '2015-01-01')

So in your query it will show as 'deprecated' or you filter it out.

A second, and possible best option, is to have a new column that states when (and if) an area was deprecated:

ALTER TABLE myTable ADD deprecated DATE;
UPDATE myTable SET deprecated = '2015-01-01' WHERE gid = 1;

So, on your query you can simple add the filter:

(deprecated IS NULL OR deprecated >= '2016-01-01')

The full code:

SELECT DISTINCT ON (code) code, area, change_date
FROM myTable
WHERE change_date < '2016-01-01'
    AND (deprecated IS NULL OR deprecated >= '2016-01-01')
ORDER BY code, change_date DESC;

As another option for non-deprecated areas, you can set them as 'infinity' instead of NULL, so the query does not need the deprecated IS NULL condition, as 'infinity' will always be higher or equal than any other non-null value.


Large Database backup approx 2 TB and restore with limited disk space I am working on DB migration from server A to server B.

Server A:- DB size  - 2 TB - Sql 2008R2 - Multiple Data files stored in diff drives.

Server B:- Disk space:- 999 GB - Sql 2014 Enterprise

Suggest me the best possible way.

I thought of using backup compression but later came to know it can be used only between same Sql server Editions. Is it TRUE ?

Also let me know if I use Sql compression, will there be any change between the original DB and compressed DB restored on the new DB ?

I would answer your specific question

  I thought of using backup compression but later came to know it can be used only between same Sql server Editions. Is it TRUE ?

NO this is not true. From BOL document about Compression

  Every edition of SQL Server 2008 and later can restore a compressed backup.

You can take backup with compression on any version of SQL Server that supports backup compression and can restore to any version you like provided the version on which you are restoring does not gives error about enterprise features

  Also let me know if I use Sql compression, will there be any change between the original DB and compressed DB restored on the new DB ?

The database would not be changed at all, whatever would be changed is backup size. So if you have 2TB of database on source then when you take backup with compression it might be around 1 TB(A simple guess, size may vary) but when you restore the same backup on destination the size required would again be 2 TB.

Compression is just to make backup size small, it has NO affect on database original size


Postgres privileges: psql \dp - what is about the plus-sign I used psql-command "\dp" to find out the given grants.

Versions on test-server: PostgreSQL 9.1.15 on armv7l-unknown-linux-gnueabi, compiled by gcc (Debian 4.6.3-14) 4.6.3, 32-bit

Out of the postgres-handbook:

rolename=xxxx -- privileges granted to a role
    =xxxx -- privileges granted to PUBLIC

        r -- SELECT ("read")
        w -- UPDATE ("write")
        a -- INSERT ("append")
        d -- DELETE
        D -- TRUNCATE
        x -- REFERENCES
        t -- TRIGGER
        X -- EXECUTE
        U -- USAGE
        C -- CREATE
        c -- CONNECT
        T -- TEMPORARY
  arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
        * -- grant option for preceding privilege

    /yyyy -- role that granted this privilege

When I fire up the command there is an additional plus-sign at the end of the acl-column (right after postgres). I could not find any documentation about it.

postgres=# \dp test.*
                               Access privileges
 Schema | Name  | Type  |     Access privileges     | Column access privileges
--------+-------+-------+---------------------------+--------------------------
 test   | films | table | postgres=arwdDxt/postgres+| code:                   +
        |       |       | =arwdDxt/postgres         |   candando=r/postgres

What is the plus sign about?

That plus sign is the indicator that the data spans more than one column. You'll get that with other multi-line values as well, not just the output of \dp


grant access to user 'root' to all databases, but least specific one I've got the users: root, lmanager.
And too, a few databases: db1, db2, db3,...
I want to grant all privileges to user 'root' over all databases, except for the db2.
The user 'lmanager' will get all permissions only to db2.

It is possible to make it work?
it is possible to make db2 'invisible' to user root?
If it's possible, how i can do it?
Thanks in advance!

Unfortunately, you must do lots of these:

GRANT ALL ON db1.* TO ...;
GRANT ALL ON db3.* TO ...;  -- skipping db2
...

The syntax of GRANT/REVOKE is too clumsy to get what you want in any easier way.

You could write a query something like this to generate them:

SELECT
       CONCAT('GRANT ALL ON ',
              SCHEMA_NAME,
              '.* TO ...;')
    FROM information_schema.SCHEMATA
    WHERE SCHEMA_NAME NOT IN ('db2', 'mysql');

Then manually execute them.

Precede that by doing a GRANT USAGE TO ... to provide the password.


SQL Server Management Studio 2016 (Release 16) keeps on crashing After updating to Release 16, my SSMS keeps on disconnecting automatically and brings up the Connect to Database Engine pop-up (screenshot below). Additionally, I keep on getting the "System.OutOfMemoryException" when I run queries, even though my machine has 16GB RAM and is only currently using about 4GB. These errors happen with simple queries like SELECT GETDATE(); Any suggestions on what could be the issue?

You are not alone. See more details here.

Posted by Microsoft:

  Turns out there's a thread leak in a utility class. The number of
  threads leaked will be proportional to the number of registered
  servers you have, among other things. A fix is coming in the next
  release


Why isn't SQL Server using the Max Server Memory setting? I have a SQL Server machine with the following:

Windows 2003 Enterprise SP2 (32-Bit)
SQL Server 2005 (32-Bit)
12GB RAM
/PAE switch set in boot.ini
6 SQL instances, all with AWE enabled
No other applications running on the machine.

I have the Max Server Memory usage set at what I believe are acceptable values (about 10GB combined, leaving 2GB for Windows). I am using the following query to show a combined total RAM usage of buffer pool and non-buffer pool. I am also showing the MAX SERVER MEMORY setting. The databases on each of the instances are far bigger than the allocated RAM, even though a couple instances get far more use than others. Shouldn't the actual memory in use always be pretty close to the MAX SERVER MEMORY setting? I am finding a gap that made me curious.

DECLARE @TotalRam as decimal (10,2)

--NON-BUFFER POOL
SET @TotalRam = (SELECT sum(pages_allocated_count)/128.0 [Non-Buffer (MB)]
from sys.dm_os_memory_cache_entries
where pages_allocated_count > 0)

--BUFFER POOL
SET @TotalRam = @TotalRam + (
SELECT CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2))  AS [Buffer Pool (MB)]
FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id > 4 AND database_id <> 32767 )

SELECT @TotalRam as RAMinUSE, value as MaxServerMemory
FROM sys.configurations
WHERE name like '%max server memory%'

Query results:

I would like to add 12GB more RAM to this machine for the two heavy instances and I want to be sure that it will be used and that I do not have something configured incorrectly. I have verified that the editions of SQL and Windows installed will support 24GB RAM.

Is it normal to see a gap between these values? Perhaps my queries are missing something.

Do you folks see any issues with going from 12GB to 24GB RAM?

Also, Available MBytes remaining in PerfMon shows around 600MB. That number must be based on what's left after the allocation of MAX SERVER MEMORY, right? Otherwise I would expect to see a good bit higher.

The actual memory in use won't increase until you actually load data into memory. It's not going to jump to 10GB combined allocated just because that's what you've set max memory to and because your databases are at least that large. It's a max, not a min (and never mind that min doesn't work that way either), and it isn't going to try to guess - before you've run enough queries - which tables / indexes it should load into buffer pool memory.

So yes, it is normal to see a gap in these values. You can probably get much closer to the max values if you pump the buffer pool by issuing a bunch of SELECT * FROM dbo.BiggestTables; on each instance, but it is usually better to let SQL Server allocate the memory as it deems appropriate based on actual usage by your real application. And remember too that max server memory doesn't just cover the buffer pool.

In short, SQL Server is pretty good at memory management; let it do its thing, and worry about adjusting max memory on your various instances when you have an actual performance issue.


simulate sequence on mysql in preparation for move to oracle I've been developing an application against MySQL while a new development server has been slowly being set up.  The new dev server will use oracle, and since we're getting close to it being ready, I want to remove the auto_increment attribute from my primary keys, and simulate Oracle's sequences.  I haven't used oracle for several years.

Since every pre-insert trigger is going to do the same thing - get the next id for table foo out of *foo_sequence*, does Oracle make any allowances for implementing some form of dynamic/lexical triggering ?

Any tips, tricks or gotchas?

EDIT #1: 1550 - this is not a heavy-use app. a single use will cause 13 inserts of small, simple records in the biggest transaction. Concurrency is unknown, but the likely hood of one user causing lock starvation of any significance is estimates very low IMO.

EDIT #2 1612: the intent is to simulate oracle sequences in mysql, and then use oracle sequences on oracle.

Thanks.

Resolution:

I'm implementing a class to act as my interface to the sequence.

on mysql, I'm implementing a table to hold sequence values, and a stored procedure to get the next value and update the row.  The class will call this stored proc and return the value.
on oracle I'll just get the next value from an oracle sequence. The class will make this request. The Sequence class will request the next value from the sequence.

the application code will use the Sequence class to obtain the next sequence value for the insert, abstracting the "nextval" implementation details away from the insert statement that consumes the value.

The 'gotcha' is that in nearly every case you really don't want to take the performance and maintenance hit for triggers just to hide the pk insert.

You really should write the queries to insert sequence.nextval with the INSERT.  Do it during the migration before you regret it a few years from now.


Access denied for user 'root'@'localhost' What proces is causing this? I got this connections constantly in logs, how to trace what app is trying to connect?

root@localhost is allowed to connect and working for some apps. I think there is some app without changed password. How to trace it?

  190310 22:38:57 [Warning] Access denied for user 'root'@'localhost'
  (using password: YES) 190310 22:38:59 [Warning] Access denied for user
  'root'@'localhost' (using password: YES) 190310 22:38:59 [Warning]
  Access denied for user 'root'@'localhost' (using password: YES) 190310
  22:39:03 [Warning] Access denied for user 'root'@'localhost' (using
  password: YES)

One way to do this might be by finding out what local processes have the MySQL socket file open. First, determine where your socket file is. Not sure where it is on Debian, try /tmp/mysql.sock or /var/lib/mysql/mysql.sock, or check my.cnf for the location.

Next, since the failing connection comes and goes, you will need to "freeze" it temporarily in order to catch it in the act. You can do it by stopping the mysqld process. For example, run pgrep -l mysqld to determine its process ID, then sudo kill -STOP <PID> (replace <PID> with the actual ID, obviously).

Now you can run sudo lsof | grep mysql.sock (or whatever your socket file happens to be) to see every process that has the file open.

To "un-freeze" mysqld, run sudo kill -CONT <mysqld PID>


Remove some auto created indices from Entity Framework? I'm using Microsoft's Entity Framework.  It automatically creates indices for every foreign key, but in many cases, I can see from the index stats that these are never used for reading - they are only ever updated (which doesn't surprise me as I doubt I am ever actually doing a join on many of them).  My gut feeling is that I should remove these indexes as they are just slowing down updates (albeit probably not that significantly) and taking up space.  I like to try to keep things clean and lean.  Is there a good reason to keep them?

Foreign keys should be indexed as a best practice. If you're not using them now, you can disable them or drop them, but you may want to use them later. The impact on performance is negligible in most cases if you keep them.

I'd leave them as is.


How to Locate Resource System Database in MS SQL Server There is a database called Resource database, which I know that is read only and contains information about system objects and does not save any user related data or metadata. I am using SSMS to manage my database instance and I can only see four system databases - master, model, msdb, tempdb. I'd like to know where can I find Resource database using SSMS? Or is Resource database intentionally invisible for users?

Thanks for your time.

The mssqlsystemresource database is hidden intentionally. The mssqlsystemresource is largely an implementation detail that allows the SQL Server installation and upgrade process to deploy new system objects versions without recreating objects in the master database as was done in SQL Server 2000 and earlier.

The physical location the mssqlsystemresource database files is a well-known documented location. In SQL Server 2008 and later versions, this is the instance BINN folder (e.g. C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn) together with other SQL Server binaries. In SQL Server 2005, the mssqlsystemresource were located in the same data folder as the master database files.

For learning, you can copy the mssqlsystemresource.mdf and mssqlsystemresource.ldf to a user data folder, change file permissions, and attach the database using a different database name. This should only be done on an isolated test instance and will allow you to easily view the system objects in the mssqlsystemresource database using SSMS.

CREATE DATABASE mssqlsystemresource_copy
    ON(NAME='data', FILENAME='D:\SqlDataFiles\mssqlsystemresource.mdf')
    LOG ON(NAME='log', FILENAME='D:\SqlDataFiles\mssqlsystemresource.ldf')
    FOR ATTACH;
GO


On checking the validity of a large set of identifiers In the research group where I work, we must solve following problem hundreds, maybe thousands of times every day: given a set of putative gene names (typically a few hundred of them), flag those that are not in our (MySQL) database.  This problem is solved in a number of ways by our various applications and scripts.  I would like to optimize the process.

The simplest approach, of course, is to iterate over the list of gene names (after removing any duplicates, of course), and for each gene name perform something like

SELECT count(*) FROM gene WHERE genename = 'GENENAME-XYZ';

or

SELECT * FROM gene WHERE genename = 'GENENAME-XYZ';

and detect the condition where the search fails.

This means performing one such query for usually hundreds, often thousands, of genes.

(BTW, most of these queries are usually performed by scripts written in Python, R, Perl, MATLAB, etc. using those language's DBI facilities.)

Alternatively, one could concoct a (typically huge) SQL statement of the form

SELECT DISTINCT genename FROM gene \
    WHERE genename IN ('GENENAME-001', 'GENENAME-002', ..., 'GENENAME-789');

...and from the result deduce the subset of invalid gene names.

Maybe it's just me, but I pick up some potent code smell from such huge SQL statements.

Bottom line, I don't have an entirely satisfactory solution, but since the problem must be very common, I figure that there must be standard solutions for it.  If this is correct, please let me know what they are.

BTW, assume that access to the DB is read-only.  In particular, please rule out any solution that involves creating temporary tables.

Also, in case it matters, ours is a MySQL database.

Enter the names into table x.

The list of all "New names":

SELECT x.name
    FROM x
    LEFT JOIN gene  ON x.name = gene.name
        WHERE gene.name IS NULL;

gene should have an index on name.

I don't think it can be done with just an IN.


Postgres create Index im running out with ideas for creating postgres index for a better performance.
This example below is a selection of a big table with many entries for a diary to person relation.
usually, i create an index just of person_id with btree but this has a less impact of the performance. How i should create the right index for this example?
select * from diary2person where now() between valid_from and valid_until and person_id = ?
thanks in advance!
regards Lars

The way to go is using a GIST index. This sort of index helps checking if a value is contained within a range.
Because you want to filter also by person_id, you will need to install the btree_gist extension. In addition, you should convert the valid_from and valid_until columns to a single tstzrange column, which is a range column that holds timestamp with time zone range limits.
After doing that, you can create an index on person_id and the new range column which you can call valid_range:
CREATE INDEX ON diary2person USING GIST (person_id, valid_range);

Good luck!


Disable trigger for just one table Is it possible to disable a trigger momentarily but for just one table.

For example I have table, TableA with an on insert, update and delete trigger. I also have a table B with the same triggers but they only affect certain columns in table A.

I now have an update query that uses both tables. I know the updates in table A need to fire the triggers but the updates in table B definitely do not need to fire the triggers. So I would like to disable those triggers until the updates are done.

Is this possible? I'm using MySQL 5.1

[ADDENDUM]

Here is a trigger table A essentially

BEGIN
    IF (OLD.status != 1 AND NEW.status = 2) THEN
        IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL) THEN
            DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id;
        END IF;
    ELSEIF (OLD.Status = 1 AND NEW.Status != 2) THEN
        IF (NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN
            INSERT INTO geo (datatype, foreignid, long, lat, hostid, morton, status) VALUES  (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status);
        END IF;
    ELSEIF (NEW.status != 3) THEN
        IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL AND (NEW.geo_lat IS NULL OR NEW.geo_long IS NULL)) THEN
            DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id;
        ElSEIF ((OLD.geo_lat IS NULL OR OLD.geo_long IS NULL) AND NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN
            INSERT INTO geo (datatype, foreignid, longitude, latitude, hostid, morton, status) VALUES  (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status);
        ELSEIF (OLD.geo_lat!=NEW.geo_lat OR OLD.geo_long != NEW.geo_long OR OLD.status != NEW.status) THEN
            UPDATE geo SET lat = NEW.geo_lat, long = NEW.geo_long, status = NEW.status WHERE datatype IN (3,4) AND foreignid = NEW.id;
        END IF;
    END IF;
END

Here are the triggers on table B essentially

CREATE TRIGGER `usergroups_comments_insert` AFTER INSERT ON `usergroups_comment`
    FOR EACH ROW
    BEGIN
       CALL sp-set-comment_count(NEW.`gid`);
    END;

Here is the stored procedure that's fired from table B

DELIMITER $$

CREATE PROCEDURE `sp_set-comment_count` (IN _id INT)
BEGIN
   -- AC   - All Count
   -- OLDAC- Old All Count
   DECLARE AC, OLDAC INT DEFAULT 0;

   SELECT COUNT(*) AS ac
     INTO AC
     FROM usergroups AS ug
LEFT JOIN usergroup_comments AS ugm ON ugm.`gid` = ug.`id`
LEFT JOIN mediagallery AS dm ON ugm.mid = dm.`id`
    WHERE dm.`status` NOT IN (200, 201, 202, 203, 204, 205)
      AND ug.`id` = _id;

   SELECT allCount
     INTO OLDAC
     FROM usergroups
    WHERE ug.`id` = _id;

IF (OLDAC <> AC) THEN

   UPDATE usergroups
      SET allCount = AC,
    WHERE usergroups.`id` = _id;

END IF;

END $$

Actually, if you place an if then block in every trigger, you could effectively shutdown all triggers. Here is such a code block

IF @TRIGGER_DISABLED = 0 THEN
    ...trigger body
END IF;

In the mysql environment, you could

run SET @TRIGGER_DISABLED = 1;
do your data maintenance
run SET @TRIGGER_DISABLED = 0;

So your trigger for table A should look like this:

BEGIN
    IF @TRIGGER_DISABLED = 0 THEN
    IF (OLD.status != 1 AND NEW.status = 2) THEN
        IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL) THEN
            DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id;
        END IF;
    ELSEIF (OLD.Status = 1 AND NEW.Status != 2) THEN
        IF (NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN
            INSERT INTO geo (datatype, foreignid, long, lat, hostid, morton, status) VALUES  (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status);
        END IF;
    ELSEIF (NEW.status != 3) THEN
        IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL AND (NEW.geo_lat IS NULL OR NEW.geo_long IS NULL)) THEN
            DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id;
        ElSEIF ((OLD.geo_lat IS NULL OR OLD.geo_long IS NULL) AND NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN
            INSERT INTO geo (datatype, foreignid, longitude, latitude, hostid, morton, status) VALUES  (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status);
        ELSEIF (OLD.geo_lat!=NEW.geo_lat OR OLD.geo_long != NEW.geo_long OR OLD.status != NEW.status) THEN
            UPDATE geo SET lat = NEW.geo_lat, long = NEW.geo_long, status = NEW.status WHERE datatype IN (3,4) AND foreignid = NEW.id;
        END IF;
    END IF;
    END IF;
END

So your trigger for table B should look like this:

CREATE TRIGGER `usergroups_comments_insert` AFTER INSERT ON `usergroups_comment`
    FOR EACH ROW
    BEGIN
        IF @TRIGGER_DISABLED = 0 THEN
            CALL sp-set-comment_count(NEW.`gid`);
        END IF;
    END;

If you want the triggers for table A to launch but not to table B, then add the code block only to table B's trigger.


Finding what's making VSS/VDI backups This one's got me a bit perplexed. How might one determine what process/program is making file-level backups via VSS/VDI and the SQL Writer service? I've tried using Profiler to trace backup/restore events, and that just shows me SQL Writer itself creating the backup events. I don't see anything in the Windows event log indicating what's talking to SQL Writer, nor does SQL Server put anything meaningful in its own error log.

I've got a situation where something is making snapshot backups and breaking the differential chain, but we're not sure what the culprit is. It's undoubtedly some kind of backup agent, or a virtualization assistant that talks to SQL Writer to allow for VM snapshots, but we need some way to pin it down.

First, check the Windows event log. Most backup software logs there. (It's likely taking a whole-OS backup, not just SQL Server.)

Generally, when you see something taking a backup, it's got sysadmin-level permissions. (You can also use the db_backupoperator role, but that's fairly unusual.)

So to catch the thing doing the backups:

Make a list of logins with sysadmin level permissions
Start auditing their login events (like with login triggers, Profiler, Extended Events, etc.)
Correlate the login times with the backup events

And if you're like Evil Admin Brent, change the SA account's password while you're at it. You should probably be rotating it periodically - especially if it's been the same for years, and you've had turnover in the team. If the backup app uses that login, presto, it will stop working. (Of course, so will your backups, so hopefully you have another solution for backups like natives.)


Database organization for mirroring / partitioning / multistore I know that is kind of generic question, but there is not other way to find a good answer.

I have a requirement to create a network of a server and few simple PCs.
My boss wants to have the database in the server but also mirroring (the whole DB) it in all the machines, because he want the PC's to work even in the case that the network is down!!!

We'll be working with a lot of data, each PC (a) controls an industrial production line and (b) records information about every action into the DB. The data under consideration are not sensitive.

My idea is that each machine will have a part (same tables but only part of the data) of the whole DB and they will update the "mother" DB.

Is my idea a good approach and, if yes, how could I accomplish such a scenario?

Is there a better approach?

Would service broker be an option? It can handle disconnected situations.
Use a local database on each PC ("clients") and one central database on the server. Each client sends Service Broker messages (XML files e.g.) to the server. For instance send information about an "action" from the production line.
Whenever the connection is lost, all messages are queued until you reconnect.
At the server you need to write a handler that processes these messages.


SQL 2008 Developer Edition, not using all CPU´s My SQL Server doesn´t want to use the second CPU for our Vault database.  I tried to use different configurations for the CPU affinity in SQL Management Studio, but doesn't seem to fix it.
I've read an article about different values for affinity mask but I'm not sure which values are correct, the article was about 4 CPU´s and someone suggested to set the config value to 15.
My Values for affinity mask are min: -2147483648; max: 2147483647; config_value = 0; run_value = 0;
The server is virtual server.

Thanks in advance

Check the SQL Log Viewer in SSMS from the last time the database service was restarted, it will note how many CPU's are detected; and like @Aaron Bertrand noted, be sure you are running queries that will take advantage of parallelism, and that your MAXDOP is set correctly.


SQL server monitoring tools I am looking for a robust open source tool for monitoring SQL server. Ours is a small organization and we don't have any monitoring tool in place and would like to know if any open source tool is available so that we can start monitoring lower environments to start with.

Stack Exchange offers their monitoring tool as open source. It's really powerful, if you can get over the installation process.

https://github.com/opserver/Opserver


How to select different format of the date in mysql I have this table and I can't change its format to yyyy-mm-dd because I have so many scripts that are related to this format dd-mm-yyyy. It would be a mess if I change its format.

Id            date

1         01-07-2014
2         02-07-2014
3         03-07-2014
4         05-07-2014
5         07-07-2014
6         14-07-2014
7         18-07-2014
8         19-07-2014
9         21-07-2014
10        01-08-2014
11        02-08-2014
12        03-08-2014

On the php file

$from = '01-07-2014';
$to = '02-08-2014';

I need to update some values from all the dates that are between 01-07-2014 and 01-09-2014 to the format dd-mm-yyyy.

I am using

UPDATE successlog
   SET successlog.overtime ='00:00'
WHERE  date >= '$from'
       AND date <= '$to'

It is not working. I tried using the key between $from and $to. This does not work either.

When the format was yyyy-mm-dd it was working normally, but after I changed the format to dd-mm-yyyy, it is not working.

You'll have to bite the apple. There's no way you can do a proper compare without changing any scripts.

You can spare yourself the trouble to adjust your scripts in the way they are generating the dates, by using STR_TO_DATE() function like this:

UPDATE successlog
       SET successlog.overtime ='00:00'
           Where date >= STR_TO_DATE('$from', '%d-%m-%Y') AND
                 date <= STR_TO_DATE('$to', '%d-%m-%Y')

but you have to adjust your scripts somehow. Store your dates with the proper datatype in your database!

Read more about str_to_date() here.


Monitor stored procedure statistics with extended events I want to log the duration and time of user defined stored procedures each time they are invoked. I believe using extended events would be the way to go to achieve this.
Can you please help me in defining the session and in querying the results?
I am not quite sure about which event to add (sqlserver.rpc_completed?) and which target to choose (synchronous_event_counter,asynchronous_file_target or ring_buffer?).
I also need help in querying the results (For example: grouping the result set so that each sp will show from top execution duration and execution time.)
We use SQL Server 2008 R2.

Your options are a bit limited in SQL Server 2008 R2 and Extended Events.  You could try rpc_completed but remember it will only register for remote procedure calls.  Here's some sample code tested in SQL 2008 R2 which shows how to create a session then read it.  Work through it, make sure you understand it and try it out:
-- xe_rpc_completed
IF EXISTS ( SELECT * from sys.server_event_sessions WHERE name = 'xe_rpc_completed' )
DROP EVENT SESSION [xe_rpc_completed] ON SERVER
GO

-- rpc_completed
CREATE EVENT SESSION [xe_rpc_completed] ON SERVER
ADD EVENT sqlserver.rpc_completed
    (
    ACTION ( package0.callstack, sqlserver.session_id, sqlserver.sql_text, sqlserver.tsql_stack )
    )
ADD TARGET package0.asynchronous_file_target (
     SET filename = 'd:\temp\xe_rpc_completed.etl', metadatafile = 'd:\temp\xe_rpc_completed.mta' )
WITH ( MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 30 SECONDS, MAX_EVENT_SIZE = 0 KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY =OFF, STARTUP_STATE = OFF )
GO

-- Start the session, collect some data
ALTER EVENT SESSION [xe_rpc_completed]
ON SERVER
STATE = START;
GO

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL drop table #tmp

SELECT IDENTITY(INT,1,1) rowId, object_name, CAST( event_data AS XML ) event_data
INTO #tmp
FROM sys.fn_xe_file_target_read_file('d:\temp\xe_rpc_completed*etl', 'd:\temp\xe_rpc_completed*mta', NULL, NULL )
GO

-- Optionally add primary XML index;
--!!NB this will cause table to grow up to 5x!
ALTER TABLE #tmp ADD CONSTRAINT pk_tmp PRIMARY KEY (rowId)
GO
CREATE PRIMARY XML INDEX xmlidx_event_data ON #tmp ( event_data )
GO

SELECT
    e.c.value('@timestamp', 'DATETIME2') [timestamp],
    e.c.value('(data[@name = "cpu"]/value/text())[1]', 'INT') cpu,
    e.c.value('(data[@name = "duration"]/value/text())[1]', 'INT') duration_s,
    e.c.value('(data[@name = "reads"]/value/text())[1]', 'INT') reads,
    e.c.value('(data[@name = "writes"]/value/text())[1]', 'INT') writes,
    e.c.query('(action[@name = "sql_text"]/value/text())[1]') sql_text,
    e.c.query('.') event_data

FROM #tmp t
    CROSS APPLY t.event_data.nodes('event') e(c)
ORDER BY [timestamp]
GO

-- Collect some data
-- ...

-- When you're ready stop the session
ALTER EVENT SESSION [xe_rpc_completed]
ON SERVER
STATE = STOP;
GO

synchronous_event_counter probably won't help you as you just get an event and a count.  It's good for say, counting logins or deadlocks in a day, something like that.
ring_buffer probably won't help you because it's a circular area with only limited space allowed.  Basically it could run out of room and cycle over.
HTH


How to protect SQL Server 2012 Backups on Standard Editions I understand that the password option to protect the SQL Server backups has been discontinued beginning with SQL Server 2012 and the feature to encrypt backups using certificates is available only from SQL Server 2014. So in such cases, are there any other In-Built options available to protect SQL Server backups that I might have missed?

TDE is only available on Enterprise editions. But am looking something for Standard Editions.

For SQL Server 2012 standard edition you do not have in-built option to protect the backups. You would either have to reply on third party tools like one provided by RedGate or you can use WinZip or 7Z  to Zip the backups with password.

PS: SQL Server 2014 standard edition do have option to encrypt the backups


Good practices for proactively preventing queries from randomly becoming slow We support several deployments of an in-house application based on SQL Server.
We've had a problem several times now where some query that used to be quick and small suddenly becomes very slow or erratic, while not necessarily producing more data or being run on a larger dataset.
Every time this happens, it is a different query that suddenly starts running slowly.  Whenever this happens, we have to spend some development time to debug the problem, maybe write the query again and benchmark against the previous version, etc. Yet the original query often worked just fine for years, and we had no prior indication that its execution time might explode.
What are some good practices that would prevent issues of queries randomly becoming slow?
What I am after is not "how to fix a query that we already observe being slow", but how to reduce the likelihood of queries becoming slow in the first place—a proactive approach, as opposed to reactive.  I need a way to force SQL Server to not try to optimize the query too much: I don't really need fast, I need consistent.  If there were some choices to have SQL Server be conservative in building the query plan, as opposed to trying to take advantage of data distributions and such, I'd be after them.

Parameter Sniffing
If the data is literally not changing at all between when the query was fast to when it became slow, then likely you're experiencing a Parameter Sniffing issue. (Note this can also happen even when the data has changed, but it doesn't require the data to change for it to happen either.) This happens when parameters (such as in a stored procedure for example) are used in the query.
The reason it happens is because on the first run of the procedure when the Execution Plan is being generated, it's optimized for those first set of values supplied to the parameters of the procedure, then that query plan is cached and re-used for future runs of the same procedure (regardless of what values are passed in as parameters again).
The subsequent executions of the procedure with varying parameters might not always have the most optimal Execution Plan when the original cached plan is reused. This is dependent on a few things, bust mostly the Cardinality of your data. Generally it happens when the Cardinality of the parameters used when the Execution Plan was originally generated and cached varies a lot from the Cardinality of the values of the parameters passed in to the procedure on subsequent executions.
For example, imagine a Cars table that had 1,000,000 rows, and on 999,999 rows the Manufacturer was Honda, but in 1 row the Manufacturer was BMW. The most optimal Execution Plan to find and return the 1 row for BMW would certainly not be the same as to find and return the 999,999 rows for Honda. The uniqueness of the values within the table is the difference in Cardinality between the two values. But with a stored procedure because the Execution Plan is cached on first run, the same plan which would be sub-optimal for Honda would be used if the first time the procedure was ran BMW was passed into the parameter. (E.g. in this case, an Index Seek operation would be most performant for BMW but would be slow for Honda which would be better off with an Index Scan.)

Cardinality Estimate Issues
The previously discussed Parameter Sniffing issue is usually the result of Cardinality Estimate issues (i.e. it is a sub-problem to another problem). But you can run into Cardinality Estimate issues even without Parameter Sniffing issues.
In simplest of terms, Cardinality is the uniqueness or number of rows that exist for a specific value of a column in a Table. The Cardinality of your JOIN, WHERE, HAVING clauses' predicates impact how SQL Server's Engine chooses to find and filter down the data. This is because different operations are more performant for returning something with low Cardinality (low number of records contain that value, so it's highly unique) vs something with high Cardinality (large number of records contain that value, so not very unique).
For example, scanning the data is likely to be more performant of an operation when a large amount of records are expected to be returned for your JOIN, WHERE, HAVING clauses as opposed to seeking which is more performant for a small number of rows. Therefore different Cardinalities result in different operations for finding and filtering the data, which ultimately results in different Execution Plans.
One common thing that can cause bad Cardinality Estimates are using functions (especially non-deterministic functions) in the JOIN, WHERE, or HAVING clauses. This is because it complicates the ability of the engine to accurately determine the Cardinalities of the values of those functions during the Execution Plan generation process. Other things that can complicate your predicates are using arithmetic operators, and comparisons between different data type fields which result in implicit conversion (e.g. if one field is NVARCHAR and is compared to another that is VARCHAR).
If your queries operate on small tables or only return a small amount of data, then you may not realize you have a potential Cardinality Estimate issue. Sometimes they don't expose themselves initially until after only a small amount of data has changed in one of the underlying tables. This is definitely something you be experiencing but we wouldn't know for sure without your Execution Plans.

Outdated Statistics
The other things you could be experiencing if it's not a Parameter Sniffing issue, and your data has changed between fast and slow runs, is outdated Statistics. (Though this is less likely the case unless your data is changing a lot or your server was incorrectly configured to not auto-update statistics.)
SQL Server routinely stores Statistics about the Cardinality of the values within the Table so that it can make accurate decisions on the operations to use when generating the Execution Plan. This is to try to ensure the most optimal Execution Plan is generated.
On Tables that change rather frequently, their Statistics sometimes get stale and can result in sub-optimal Execution Plans being generated for them, and therefore those Statistics need to be manually updated.

Poor Indexing
Indexes are a way of storing the data in your tables in an efficient logical data structure, a B-Tree. Without a Clustered Index the logical data structure your tables are stored in is a Heap. A Clustered Index converts that Heap into a B-Tree which is a lot more efficient for searching. The Clustered Index determines the actual ordering of your entire table itself.
Nonclustered Indexes store copies of specific fields from your table into their own B-Trees sorted on those fields themselves. They also store a reference to the Primary Key of the table in case they need to find more information from the table that isn't stored in the Nonclustered Index itself.
It is possible that if your tables were missing the appropriate indexes that your queries would be fast enough with a small amount of data but continue to slow down as the tables grew. Furthermore it is possible if you had the appropriate indexes when the queries were originally written, and someone added, changed, or removed one of those indexes, a different Execution Plan was generated that was sub-optimal. (Yes, something as simple as adding an additional index on a table can cause an existing optimal Execution Plan to no longer be used for a specific query and instead a sub-optimal plan can be used. This is on the fault of the query optimizer of the SQL Server Engine and the statistics on that table.)

Additional Resources - Specific Topics

RedGate - Execution Plan Basics - This is a great start to end overview of what is an Execution Plan, how are they created, and will help you understand their purpose and how to use them to debug performance issues. I find the Execution Plan can answer 95% of the issues someone is experiencing, and even proactively tell you about an issue that might not currently be affecting performance but can certainly affect performance in the future.

Brent Ozar - How Execution Plans Are Made - This is a good video to give a little bit of a visual with examples on how Execution Plans are formed and to help you think a little bit like the SQL Server Engine.

Brent Ozar - Watch Brent Tune Queries - This is a good video to understand the key things to look for when query tuning. You'll get a more intimate understanding of how to use the Execution Plan to help you determine what the cause of your performance problems were. You can even check for some of these things proactively after you've written a query.

Brent Ozar - Watch Brent Tune Queries (Again) - This is Brent doing basic query tuning again, so while not the exact same video the concepts are redundant (in a good way) to help you catch on to the things one should do, even proactively after you write the query, to verify its performance will hold true in the future.

SQL Server Central - Statistics And Cardinality Estimation - Cardinality Estimation is a very important thing to understand (and is slightly more complex than understanding basic things like proper indexing). This article introduces you to what it is, and what it's purpose is in SQL Server. I highly recommend looking into other resources as well (I'll continue to update my answer when I find other good ones). This is something you can also proactively check a lot of times by looking at the Execution Plan of your query right after you've written it. There's specific things you can look at in the plan like the Estimated Number of Rows vs Actual Number of Rows being far off from each other (generally a magnitude off) that indicate when you have a Cardinality Estimate issue.

Additional Resources - General
(Hardening @LadyBug1's comments into my answer for historical reference since they are great suggestions.)
These references are generally great places to learn about SQL Server, it's engine, and how to develop for your database optimally. They are of some of the most well regarded members of the SQL community:

Brent Ozar
Kendra Little
Paul Randal
Any of Aaron Bertrand's Blogs: SQLPerformance.com, SQLBlog.org, MSSQLTips.com
Erik Darling
Jonathan Kehayias
Paul White
Grant Fritchey
RedGate's Simple Talk Blogs

If you can update your answer with a specific example (query, data used for parameters, and the Execution Plan(s)) we can more definitively tell you what the issue is and point out how to determine it.