SQL server job deployment I have a SQL Server job which has got different steps, including running a SSIS package. Currently it is on my PC (my local instance).

I should deploy it to UAT and assume some steps need to be taken to deploy it, in terms of creating login which run it, removing my login from it, etc.

What are the steps involved to deploy it successfully? Please note there is a CLR function call in this job as well; what would you recommend for required security settings around that?

Deploying the stuff you need to move a job is pretty much just like packaging the stuff you need to move application code assuming that you correcly put all the new items and changes in source control. This includes your SSIS package, any object creation or alteration scripts such as tables, views, UDFs, stored procs, CLRS (Never ever create database objects using the GUI if you want to deploy later), any scripts to populate tables (such as lookup tables). You may need to number the items in the deplyment folder to ensure they are run in the correct order. Usually I write a deployment document as well because some of what we are deploying will go to differnt servers (Our ssis server is is differnt from our database server).

You can also script out the job, but you will need to review and change the script for the new environment. I often find it just as easy to set up the job on the other server manaully (but I have dba rights to all servers, if you do not, you will probaly need to this).


Is it possible to build Data warehouse by myself? I work for the insurance company little more than a year, my SQL experience around 2-3 years including SSIS, SSRS. We have approximately 1 TB of data.

Is it possible to build data warehouse by myself? Should I do that with my experience?

There are lots of materials and SQL groups I can get help from. But still, is it too complicated?

Thanks

It's a lot like asking, "Can I build a house by myself?"

It depends a lot on what your definition of "house" is. Talk with your internal users to build a set of requirements first - that's going to need to happen regardless of who builds it.

When you're done building requirements, then you can start sketching out what you'll need to build in order to satisfy those requirements.

Then, you can start building time estimates for what needs to be built.

As you work through this process, you'll have a better understanding of what the house looks like and whether you can build it yourself with your existing toolbox and skills.


Query runs quicker when column calculation is done after initial query I have always been led to believe that since SQL evals the SELECT after filtering that putting a calculation in the select of a query should be as fast or faster than running the query and then doing the calculation over the result. So I assume I am doing something wrong in the following query. The JOINS and the WHERE are the same in both, in the first the calculation is done after the result returns (takes about 3 seconds). The second query has the same calculation in the initial query (takes about 1 minute). I have added the query plan. Running on SQL 2008.

  DECLARE @MsgTable TABLE (
    [Type] VARCHAR(64),
    [EventDate] [datetime],
    [LicenseId] bigint,
    [RequestedOn] [datetime]
  );

  -- Calculate AFTER
  INSERT INTO @MsgTable
  SELECT
    Type,
    EventDate,
    EventLog.LicenseId,
    NULL
  FROM EventLog
    INNER JOIN #LTable L ON (EventLog.LicenseId = L.LicenseId)
    INNER JOIN LicenseConfig LC ON LC.LicenseId = EventLog.LicenseId AND ((LC.ConfigKey = 'ConversionStatus'))
  WHERE
    (EventLog.Type IN ('ERPValidationRequest', 'ERPMappingComments'))
    AND
    (EventLog.EventDate >= @StartDate)
    AND
    (EventLog.EventDate < @EndDate)
  ;

  SELECT
    Type,
    EventDate,
    LicenseId,
    (
      SELECT TOP 1 ROEventLog.EventDate
      FROM EventLog ROEventLog
      WHERE
        ROEventLog.LicenseId = MT.LicenseId
        AND
        ROEventLog.Type = 'ERPMapRequest'
        AND
        ROEventLog.EventDate < MT.EventDate
      ORDER BY ROEventLog.EventDate DESC
    ) RequestedOn
  FROM @MsgTable MT;

  DELETE FROM @MsgTable;

  -- Calculate during
  INSERT INTO @MsgTable
    SELECT
      Type,
      EventDate,
      EventLog.LicenseId,
      (
        SELECT TOP 1 ROEventLog.EventDate
        FROM EventLog ROEventLog
        WHERE
          ROEventLog.LicenseId = EventLog.LicenseId
          AND
          ROEventLog.Type = 'ERPMapRequest'
          AND
          ROEventLog.EventDate < EventLog.EventDate
        ORDER BY ROEventLog.EventDate DESC
      ) RequestedOn
    FROM EventLog
      INNER JOIN #LTable L ON (EventLog.LicenseId = L.LicenseId)
      INNER JOIN LicenseConfig LC ON LC.LicenseId = EventLog.LicenseId AND ((LC.ConfigKey = 'ConversionStatus'))
    WHERE
      (EventLog.Type IN ('ERPValidationRequest', 'ERPMappingComments'))
      AND
      (EventLog.EventDate >= @StartDate)
      AND
      (EventLog.EventDate < @EndDate)
  ;

  SELECT * FROM @MsgTable;

In query 4, the one-row from the table variable convinces the optimizer to choose an index seek and key lookup on the EventLog table. The true number of rows returned is 49:

For query 6, the optimizer produces an over-estimate (22,659 vs 49):

The higher number of estimated executions causes the optimizer to choose to build a temporary index (Eager Index Spool) on the EventLog table instead of performing 22,659 key lookups. This temporary index is built and dropped on each execution of the query. The result is a lower estimated cost plan, but it runs much longer in practice.

Adjusting the indexing of the EventLog table is one way to resolve this issue.

For a more detailed answer, please provide the actual (post-execution) plans in xml form e.g. via https://www.brentozar.com/pastetheplan/


Why TRY CATCH does not suppress exception in trigger I have a trigger on a table (source) that data should be copied to the other one (target) in other database. I am trying to implement custom synchronization process for data: I want that target database (table) will be up to date to source database (table). I have 3 hundred tables to synchronize. Some of then have different physical data structure. I cannot use standard approaches of Sql Server (replication, DTS...) because of different data schema and other restrictions (time to implement, environment issues...).
My goal is: this trigger should NOT impact on INSERT, DELETE, UPDATE of records in a source table. I tried so solution:

CREATE TRIGGER dbo.MyTrigger
...
   AFTER INSERT
....
BEGIN TRY
     --RAISERROR('Test error', 16, 2)
END TRY
BEGIN CATCH
  -- nothing
END CATCH

I have inserted RAISEERROR to simulate error. I hoped, try/catch suppressed that error and record was been inserted (deleted, or updated) successfully. Don't. It does not work. I got error:

  An error was raised during trigger execution. The batch has been
  aborted and the user transaction, if any, has been rolled back.

Is it possible to realize my solution in this way. How can I catch and hold (suppress) any error in my trigger?

What sense to use try/catch in trigger if it does not work?

The transaction is doomed with pretty much any exception and must be rolled back.
From "Using TRY...CATCH in Transact-SQL" on MSDN

Inside a TRY…CATCH construct, transactions can enter a state in which the transaction remains open but cannot be committed. The transaction cannot perform any action that would generate a write to the transaction log, such as modifying data or trying to roll back to a savepoint. However, in this state, the locks acquired by the transaction are maintained, and the connection is also kept open. The effects of the transaction are not reversed until a ROLLBACK statement is issued, or until the batch ends and the transaction is automatically rolled back by the Database Engine. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application that indicates an uncommittable transaction was detected and rolled back.
A transaction enters an uncommittable state inside a TRY block when an error occurs that would otherwise have ended the transaction. For example, most errors from a data definition language (DDL) statement (such as CREATE TABLE), or most errors that occur when SET XACT_ABORT is set to ON, terminate the transaction outside a TRY block but make a transaction uncommittable inside a TRY block.

This example shows why. XACT_STATE() doesn't register an implied transaction (eg no explicit BEGIN TRAN)
CREATE TABLE TrgTest (gbn int NOT NULL);
GO
CREATE TRIGGER TRG_TrgTEst_I ON TrgTest AFTER INSERT
AS
BEGIN TRY
     SELECT '1', @@TRANCOUNT, XACT_STATE();
     RAISERROR('Test error', 16, 2);
END TRY
BEGIN CATCH
     SELECT '2', @@TRANCOUNT, XACT_STATE();
END CATCH
GO

BEGIN TRANSACTION
INSERT TrgTest VALUES (1)
SELECT 'will not run'
GO
SELECT 'next batch'
GO

BEGIN TRY
    BEGIN TRANSACTION
    SELECT 'a', @@TRANCOUNT, XACT_STATE();
    INSERT TrgTest VALUES (1)
    SELECT 'b', @@TRANCOUNT, XACT_STATE();
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE()
    SELECT 'c', @@TRANCOUNT, XACT_STATE();
END CATCH
GO

DROP TABLE TrgTest;


Designing a Multiple Reflexive Relationship I'm making a database diagram and I'm facing the situation explained below.

Business rules:

A device could have multiple connections with others devices (It's a graph).
A device could be a server, a switch or store unit (for the moment, could raise).
A device belong a one system.

So, I need it design this multiple reflexive relationship between devices. I believe that I achieved it with the next model, but I'm not sure if it's the best solution.

Reality and Problem:

A device could have multiple devices (links) and belong to multiple devices.

So my diagram is as follows:

Diagram Explanation:

The Device concept was created because the information of all devices is similar relative. So the properties that differ could be optional. I believe that the solution for the multiple reflexive relationship is to create other concept that has a copy of first concept ID (device) to apply the partition and get the link between multiple devices. This copy is generated when some inserts a device (with a trigger or with simple other insert, in chain from logic layer). The purpose of the ID copy is to allow creating relationships between all devices.

Any idea for this case? Thanks.

I'll answer for an RDBMS, like PostgreSQL. Here we use a many-to-many table.

CREATE TYPE device_type
  AS ENUM ( 'SERVER', 'STORAGE', 'SWITCH' );

CREATE TABLE devices (
  id   serial         PRIMARY KEY,
  type device_type
);

CREATE TABLE device_map (
  id_device   int REFERENCES devices,
  id_parent   int REFERENCES devices,
  PRIMARY KEY (id_device, id_parent)
);

INSERT INTO devices(id,type)
VALUES
  (1,'SERVER'),(2,'SERVER'),
  (3,'STORAGE'),(4,'STORAGE'),(5,'STORAGE'),
  (6,'SWITCH');

-- Everything the first and second server is connected to.
INSERT INTO device_map(id_device,id_parent)
VALUES
  (1,2),(1,3),(1,6),
  (2,1),(2,3),(2,6),(2,4),(2,5);

Now I think you want something like this.

SELECT id_device, d1.type AS device, id_parent, d2.type AS parent
FROM device_map JOIN devices AS d1 ON id_device = d1.id
JOIN devices AS d2 ON id_parent = d2.id
ORDER BY id_device, d2.type = 'SERVER' DESC;
 id_device | device | id_parent | parent
-----------+--------+-----------+---------
         1 | SERVER |         2 | SERVER
         1 | SERVER |         3 | STORAGE
         1 | SERVER |         6 | SWITCH
         2 | SERVER |         1 | SERVER
         2 | SERVER |         3 | STORAGE
         2 | SERVER |         6 | SWITCH
         2 | SERVER |         4 | STORAGE
         2 | SERVER |         5 | STORAGE
(8 rows)

That models this schema, assuming simply that non-servers can only be connected to servers.


How to make this query use my multicolumn index? Currently, I have a view which is defined like this:

                       View "public.customer_list"
  Column   |          Type           | Modifiers | Storage  | Description
-----------+-------------------------+-----------+----------+-------------
 id        | bigint                  |           | plain    |
 name      | character varying(100)  |           | extended |
 street    | character varying(100)  |           | extended |
 zip       | character varying(10)   |           | extended |
 city      | character varying(100)  |           | extended |
 country   | character varying(3)    |           | extended |
 phone     | character varying(100)  |           | extended |
 mail      | character varying(100)  |           | extended |
 rating    | integer                 |           | plain    |
 salesnote | character varying(1800) |           | extended |
View definition:
 SELECT c.id,
    c.name,
    a.street,
    a.zip,
    a.city,
    a.country,
    c.phone,
    c.mail,
    c.rating,
    c.salesnote
   FROM crm_customer c
     JOIN crm_address a ON a.id = c.address_id;

To better search through the List I created an index with the following:

CREATE INDEX crm_customer_big_index
    ON crm_customer (name ASC,
                     name text_pattern_ops,
                     (id::text) text_pattern_ops,
                     phone text_pattern_ops,
                     mail text_pattern_ops,
                     rating);

I search through this table with a varying where query mostly it looks like that:

SELECT *
  FROM customer_list
 WHERE lower(name::text) LIKE 'env%'
       AND rating = 3
 ORDER BY name ASC
 LIMIT 20
 OFFSET 0;

But still my analyzer won't ever use an index. Is there a way to use one?

             QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=164.04..164.08 rows=15 width=129) (actual time=3.243..3.244 rows=1 loops=1)
   ->  Sort  (cost=164.04..164.08 rows=15 width=129) (actual time=3.243..3.243 rows=1 loops=1)
         Sort Key: c.name
         Sort Method: quicksort  Memory: 26kB
         ->  Nested Loop  (cost=0.28..163.75 rows=15 width=129) (actual time=0.020..3.225 rows=1 loops=1)
               ->  Seq Scan on crm_customer c  (cost=0.00..111.13 rows=15 width=98) (actual time=0.012..3.216 rows=1 loops=1)
                     Filter: ((rating = 3) AND (lower((name)::text) ~~ 'env%'::text))
                     Rows Removed by Filter: 2978
               ->  Index Only Scan using crm_address_search_index on crm_address a  (cost=0.28..3.50 rows=1 width=47) (actual time=0.006..0.006 rows=1 loops=1)
                     Index Cond: (id = c.address_id)
                     Heap Fetches: 0
 Planning time: 0.580 ms
 Execution time: 3.286 ms

Currently, the query is still "somewhat" fast. However, I could have way more data inside my table. Also, crm_address has an index over street, zip, city, country, which works fine.

The expression lower(name::text) is not covered by your index, which also does not seem to be useful at all, at least for what we see in your question:

CREATE INDEX crm_customer_big_index
    ON crm_customer (name ASC,
                     name text_pattern_ops,
                     (id::text) text_pattern_ops,
                     phone text_pattern_ops,
                     mail text_pattern_ops,
                     rating);

This index would cover your query:

CREATE INDEX crm_customer_foo_index ON crm_customer (
  rating
, lower(name::text) text_pattern_ops
, address_id);  -- for the join

To optimize, I suggest to adapt your query additionally:

SELECT *
FROM   customer_list
WHERE  lower(name::text) LIKE 'env%'
AND    rating = 3
ORDER  BY lower(name::text), name
LIMIT  20;

Also, if you are only interested in the first few characters of name character varying(100), make the index shorter and faster by just indexing those first few characters:

CREATE INDEX crm_customer_foo_index ON crm_customer (
  rating
, lower(left(name::text, 7)) text_pattern_ops  -- example with local optimum
, address_id);  -- for the join

The query must match the index expression:

WHERE  lower(left(name::text, 7)) LIKE 'env%'
...
ORDER  BY lower(left(name::text, 7)), name

Not sure if the view is in the way somehow. But it should be transparent.

I wouldn't use varchar(n) at all. Just text:

Should I add an arbitrary length limit to VARCHAR columns?

About pattern matching and text_pattern_ops:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Why did I put rating first in the index?

Multicolumn index and performance

There are query techniques to better capitalize on non-leading index columns. Consider the Postgres Wiki on "Loose indexscan".


How do I optimize this database design? I need to create a vehicle tracking database.  There are several vehicles that send data, consisting of around 20 parameters, to my database.

The parameters of a vehicle include the constants vehicle number and hardware id, along with a variety of variable items.

I will have a table in this database containing the login credentials of the users and I would like to create a link between the two tables with the vehicle number as primary key in the login table and with the vehicle number in table containing parameters.

Since the vehicle number column is redundant I cannot have it as the primary key.

I would like to know:

What way can I achieve the link between tables
Is there a good way so that I dont store this vehicle number and hardware id so many times.
Should I be storing each vehicle's data in separate table rather than all in one table?

This is the first time I am designing a database.  I have only been a programmer to date.

I expect this model to be a bit more complicated than two tables. If the vehicle tracking provides you with two IDs, then they may probably be foreign keys to some specification. Also, it is entirely possible that different vehicles will have the same hardware, and the other way around, different hardwares will have the same vehicle. In that case, you need to normalize it by placing in different tables.

My proposition is based on the following model:

EDIT

Thanks to @DamirSudarevic I've made some updates in the model.


In MySQL if I create a user and grant privileges to the user, will dropping the user revoke privileges? In MySQL, I created a user and granted all privileges to the user. I even granted proxy to user.

CREATE USER IF NOT EXISTS 'testuser'@'localhost' IDENTIFIED BY 'testpass';
GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'testuser'@'localhost' WITH GRANT OPTION;

Now if I drop the user, will the user's privileges and proxy be revoked? Or do I need to manually revoke the user's privileges and proxy before I drop the user?

DROP USER IF EXISTS 'testuser'@'localhost';

The DROP USER statement removes one or more MySQL accounts and their privileges. It removes privilege rows for the account from all grant tables.

see http://dev.mysql.com/doc/refman/5.7/en/drop-user.html

PROXY is a GRANT privilege so the proxy privilege should be deleted also.


Is it possible to restore a SQL Server Linux Backup on SQL Server Windows I am running SQL Server in docker on a mac. I have taken a SQL Server backup from windows and restored it in the mac SQL Server docker instance. Now I want to take a backup from the SQL Server docker instance and restore it back to windows. When I attempt to restore on Windows I am getting a "System tables sysfiles1 is corrupted".
Is it possible to take a back up from SQL Server Linux and restore it on SQL Server Windows?

I just validated it. Yes. Not only is it possible, other than using the WITH MOVE to change where the files are located, there's literally nothing special involved. I tested it using a shared volume to get the backup out of my docker instance. I also tested it using the docker file copy command. Both worked just fine. It's possible that the backup is corrupt or that the versions are off.


Slave settings not working I have this machine as slave. Below is the command I ran and stuck. I done the change on the config file and set the id=2 too. Anything else I am missing here?

slave start;
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log
mysql> change master to MASTER_HOST='192.168.1.15', MASTER_USER='replication1', MASTER_PASSWORD='*******',MASTER_LOG_FILE='mysql-bin.000001'; ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log ?

Below is the results of

show master status\G
*************************** 1. row ***************************
            File: mysql-bin.000001
        Position: 98
    Binlog_Do_DB: fms,sms
Binlog_Ignore_DB:
1 row in set (0.00 sec)

show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.15
                  Master_User: replication1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 98
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 243
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: fms
          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: 98
              Relay_Log_Space: 399
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

I noticed you did not specify the MASTER_LOG_POS in the CHANGE MASTER TO command.

Another problem could be this:

When you set up MySQL Replication, you have to explicitly set this in /etc/my.cnf

on the master:

[mysqld]
server-id=1

and this on the Slave

[mysqld]
server-id=2

Then CHANGE MASTER TO can work

Since the Master is MySQL 5.0.67 and you loaded the same data into the Slave, here is the CHANGE MASTER TO command you need:

change master to
MASTER_HOST='192.168.1.15',
MASTER_USER='replication1',
MASTER_PASSWORD='*******',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=98;

Here is something you need to know fot setting up other slaves

The Master must be the same version as the Slave or older.
The starting position for any empty Master is

107 for MySQL 5.5
106 for MySQL 5.1
98 before MySQL 5.1

Now you have replication working, take note of the following

Relay_Log_Space is 399
Exec_Master_Log_Pos is 98

Do the following:

Run this command on the Master: CREATE DATABASE rolando;
Do SHOW SLAVE STATUS\G on the Slave
Do SHOW DATABASES; on the Slave

What results should you have?

Relay_Log_Space should be greater than 399
Exec_Master_Log_Pos should be greater than 98
The database rolando should appear in the SHOW DATABASES; on the Slave

If this happens, congratulations !!! Replication is working !!!

From here, you just have to monitor

Slave_IO_Running (make sure it is Yes)
Slave_SQL_Running (make sure it is Yes)
Seconds_Behind_Master (should be zero, long running queries make it increase until all queries are processed)


Is it possible to purge the whole content of Oracle's SYS.INDEX_STATS view? My purpose is to analyze the indexes of a schema for getting the best basic index compression to apply.

I'm going to use the command

ANALYZE INDEX OWNER.INDEX_NAME VALIDATE STRUCTURE;

That will fill up SYS.INDEX_STATS view with the info I need, but considering this view does not have the OWNER column, this is not helping me. Here's why:

In my Oracle instance I have many schemas containing the installation of the same product filled with data from different customers.

I'm filling INDEX_STATS by looping DBA_INDEXES and calling ANALYZE INDEX, so any call to ANALYZE INDEX OWNER.INDEX_NAME VALIDATE STRUCTURE will overwrite the entry from the previous call on the same index on a different schema.

I may save the content of INDEX_STATS after having analyzed each schema, but for not all the customers have the same tables installed because of modularization of the software, some entries of INDEX_STATS may refer to previous calls to a different schema.

Is there a way for purging the content of INDEX_STATS so I can have a clean situation before analyzing each schema? Or the only solution is to join INDEX_STATS with DBA_INDEXES for saving the content of INDEX_STATS to a final table containing the data from all the schemas?

The contents of INDEX_STATS are temporary. Every time you start a new session, the view becomes empty.
Additionally, this view contains data only for the last index that was analyzed.

So there is no need to purge the contents, it is just a matter of organizing your code properly and storing the results in a permanent table if you need them.


Postgres upgrade 9.2 to 10 failure bash-4.2$ /usr/pgsql-10/bin/pg_upgrade --old-bindir=/usr/bin/ --new-bindir=/usr/pgsql-10/bin/ --old-datadir=/var/lib/pgsql/data/ --new-datadir=/var/lib/pgsql/10/data/ --check
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok

*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.

connection to database failed: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/lib/pgsql/.s.PGSQL.50432"?

could not connect to source postmaster started with the command:
"/usr/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql/data/" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directory='/var/lib/pgsql'" start
Failure, exiting

tail /var/lib/pgsql/pg_upgrade_server.log gives me:

server stopped
command: "/usr/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql/data/" -o "-p 50432 -b -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directory='/var/lib/pgsql'" start >> "pg_upgrade_server.log" 2>&1
waiting for server to start....
FATAL: unrecognized configuration parameter "unix_socket_directory"
stopped waiting
pg_ctl: could not start server
Examine the log output.

How can I resolve this?

pg_ctl must call the wrong postgres binary.

PostgreSQL version 9.2 had a parameter unix_socket_directory, which does no longer exist (there is unix_socket_directories now). So a 9.2 server would never die with this message.

Are your PostgreSQL 9.2 binaries really installed in /usr/bin or is the pg_ctl there just a symbolic link or a script?

Try to find the directory where the postgres executable from the 9.2 installation is located and use that directory with the --old-bindir of pg_upgrade.


Why does the query plan still sort a table despite having an index sorted on the columns? I'm using Postgres 9.1
I have two tables I am joining:

wikidb=> \d page
                         Table "public.page"
        Column         |     Type      |          Modifiers
-----------------------+---------------+------------------------------
 page_id               | bigint        | not null
 page_namespace        | integer       | not null default 0
 page_title            | text          | not null default ''::text
 [...]
Indexes:
    [...]
    "page_page_namespace_page_title_idx" UNIQUE, btree (page_namespace, page_title)

wikidb=> \d pagelinks
                 Table "public.pagelinks"
      Column       |  Type   |         Modifiers
-------------------+---------+----------------------------
 pl_from           | bigint  | not null default 0::bigint
 pl_namespace      | integer | not null default 0
 pl_title          | text    | not null default ''::text
 [...]
Indexes:
    [...]
    "pagelinks_pl_namespace_pl_title_pl_from_idx" btree (pl_namespace, pl_title, pl_from)

Notice how both have indices on the (namespace, title) columns.  I am interested in finding out how many (pl_namespace,pl_title) pairs in the pagelinks table do not show up in the page table as (page_namespace, page_title).

If I use a join, then I get the following plan:

wikidb=> explain SELECT COUNT(*)
FROM pagelinks
LEFT OUTER JOIN page
  ON page.page_namespace = pagelinks.pl_namespace AND
     page.page_title = pagelinks.pl_title
WHERE page.page_namespace IS NULL AND page.page_title IS NULL;
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1310748.56..1310748.57 rows=1 width=0)
   ->  Merge Anti Join  (cost=1189384.02..1310748.56 rows=1 width=0)
         Merge Cond: ((pagelinks.pl_title = page.page_title) AND (pagelinks.pl_namespace = page.page_namespace))
         ->  Sort  (cost=1144343.89..1164498.31 rows=8061768 width=19)
               Sort Key: pagelinks.pl_title, pagelinks.pl_namespace
               ->  Seq Scan on pagelinks  (cost=0.00..219551.68 rows=8061768 width=19)
         ->  Sort  (cost=45038.32..45975.52 rows=374880 width=20)
               Sort Key: page.page_title, page.page_namespace
               ->  Seq Scan on page  (cost=0.00..10331.80 rows=374880 width=20)
(9 rows)

Which, as you can see, sorts each of both tables and merges them. I don't understand why it does this if the indices already contain both columns in sorted order.

Any explanations?

PostgreSQL thinks the sort merge is faster. And in my hands on 9.1 it actually is faster than walking both indexes. You can try it for yourself by set enable_sort to off; and see what plan it gives, and how long that one takes to run.

Sorting is pretty efficient. Walking indexes is inefficient in 9.1 because it still has to visit the table to make sure the row is actually visible.  Indexes don't store visibility information. 9.2 introduced index-only scans, which gets around part of that problem.

Also, the index leaf pages are not necessarily logical order, so walking the index in logical order can lead to a lot of random IO.  (Of course, checking the table for visibility off of an index scan will as well.)

There have been a lot of improvements made since 9.1.


Set the file name in "SELECT ... INTO DUMPFILE" I am trying to write a query to dump a single LONGBLOB data into a file.

SELECT data FROM table WHERE id = 123
INTO DUMPFILE fileName

which data, fileName, and id are the fields in the table.

SQL syntax error near fileName occurred when I try to run this query in HeidiSQL 10.2.0.

Does anyone know how to fix this error?

The database is MariaDB 5.5.

The following query works fine by the way:

SELECT data FROM table WHERE id = 123
INTO DUMPFILE 'data.pdf'

Because the fileName value is from the database you don't need to put the single quotes around it.

SELECT data FROM table WHERE id = 123
INTO DUMPFILE fileName

I'm not sure how filenames with spaces will come out though if you have any. You could always protect against that by using REPLACE on the filename to either remove them or swap them out with a different character before hand.


Table Design for Student Attendance hourly for each day I have created a table (attendance_details) for storing every day attendance of Students in MySQL. Need to capture attendance for hourly (1-8hrs) every day. I have the student details in separate table (student_details).

As of now, the fields I have created in attendance_details are: attendance_no (Foreign Key which is Primary Key in the student_details table), date, hour1, hour2, hour3, hour4, hour5, hour6, hour7, hour8.

A User will only mark the absentees, and the remaining students should be marked as present.

Is this a good design, since I want the report for each day attendance based on hours of a all students in a class for every month? The Front-end is being developed C#.

Also, I need to keep track of every hour attendance.

Can you help me with the SELECT query which should generate report as below?

MySQL Database fields are shown in the pic below:

It appears the OP is conflating how the data should be stored in the database with how the data will be displayed and/or entered when taking classroom attendance.

These are not necessarily the same thing.  Display should not drive storage.

The UI could display the data the way the OP shows above in a classroom attendance form and have a middle layer that maps those values to and from more normalized tables in the database.

More work for the middle layer but more extensible and configurable when

(1) the attendance day is longer or shorter
(2) students are added or removed from the class

An entry in an CLASS_ATTENDANCE table will have all the data that changes on a daily basis with foreign keys for the class, student, and status.

(0) CLASS_ATTENDANCE ID
(1) CLASS ID
(2) STUDENT ID
(3) DATE
(4) STATUS CODE
(5) and perhaps TEACHER ID of the person who took attendance

An entry in a CLASS table will have

(0) CLASS ID
(1) COURSE ID
(2) PERIOD ID
(3) and perhaps TEACHER ID of the person who normally teaches the class

An entry in a PERIOD table will have

(0) PERIOD ID
(1) START TIME (for example 08:00)
(2) END TIME (for example 08:55)

Periods usually do not change during a single semester

There would like be a CLASS_STUDENT table that tracks which students are assigned to which class and drives filling in the CLASS_ATTENDANCE table daily.

(0) CLASS_STUDENT ID
(1) CLASS ID
(2) STUDENT ID

This just one way to normalize the data.  And there is still some redundancy between the CLASS_STUDENT table and the CLASS_ATTENDANCE table.


How to maintain Database audits? I am using MySQL database (with Django ORM). I want to maintain the database audits similar to StackOverflow, Quora, Wikipedia, etc. These websites maintain the revisions of the changes in the database so that the changes made by users/ admins can be reverted.

After going through the database design of StackOverflow and the  Quora revisions, I understood two ways of doing this -

StackOverflow

Create a duplicate table to keep the log of the changes made in the    database. For each entry record the changes,timestamp and admin/user    who made this changes. Use these entries to find diffs and revert to any point. SO keeps the history of revisions in a separate PostHistory table.

Quora

Instead of making separate tables for each table in the database, make a table like this for audits.-

  id - revision id
  scope_id - id of database table
  scope_type - Question, Topic, User
  item_id - row id of Question/topic/user in the database table
  event - Edited, Added, Reverted, Removed
  user_id - who triggered the event
  timestamp
  serialized_item_column - serialized data in the json format

Then the serialized data can be used to calculate diffs and revert a particular entry.

In context of crowdsourcing platform like wiki/SO where multiple users/admins can make changes,

Which of the two database design will be better ?
If I use one duplicate table per table for revisions, i.e. one for the current value and one with all the previous revisions,  is that a scalable way for a website with millions of entries and much more revisions ?

Stand on the shoulders of your predecessors.

  If I use duplicate tables is that a scalable way for a website with millions of entries and much more revisions ?

"Duplicate tables"?  Plan A: one table with all revisions.  Plan B: 2 tables, one with the current value, one with all the previous revisions.  Plan C (really bad):  One table per revision.

  Which of the two database design will be better ?

Once you have gone through the exercise of defining "better", you will be halfway to the answer.

  How to change the schema in json based audits if schema changes ?

This sounds like a different dimension to the problem -- tracking of schema changes.  That, itself, is a very big task.  JSON has no problem adding/dropping extra "fields" as a schema changes.  However if a table is split, then JSON gets trickier.  You would probably need special code bridge the gap.

Bottom Line

Since you seem to be just starting in this endeavor, I suggest you flip a coin to decide which one to use.

But...  Plan on revisiting the decision in 6 months.  By then you will have enough experience with the code to have some feel for whether it will work for the direction you want to take it.

Sure, it will be painful (very painful) to switch after 6 months.  But it will be 'impossible' after 12 months.  You will probably decide to patch the chosen schema instead of switching to the other schema.

Or maybe you will spin off another company to use the other database schema.  A prediction:  You will find that the "grass is not greener" and the second company will fail.


Get starting and ending balance, with total credit and debit transactions I have a table that contain transactions like this:

I get the required Data by using scalar function, but it is executing very long time to get the query results. Can any one help me how to get the required data faster?

I read it can be done using a table function, but I didn't know how to or how to use a join with the same table.

For context, TransactionType 1 = debit and TransactionType 2 = credit.

Here is the function I created:

ALTER FUNCTION [dbo].[CardBalanceByDate_FN]
(
    @studentID int ,
    @CurrancyID int,
    @BalanceDate Date
)
RETURNS decimal(18, 2)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result decimal(18, 2)

    -- Add the T-SQL statements to compute the return value here

    select @Result =  ISNULL((((dbo.ProfileCardAmountByDate_FN(@studentID,@CurrancyID,1,@BalanceDate) + (dbo.ProfileCardAmountByDate_FN(@studentID,@CurrancyID,3,@BalanceDate))) )-(dbo.ProfileCardAmountByDate_FN(@studentID,@CurrancyID,2,@BalanceDate))) ,0)

    --******************************************************************************************

    -- Return the result of the function
    RETURN ISNULL(@Result,0)

END

and

ALTER FUNCTION [dbo].[PeriodTotalTransactions]
(
    -- Add the parameters for the function here
    @studentID int,
    @CurrancyID int,
    @TransactionTypeID int,
    @StarteDate Date,
    @EndDate Date
)
RETURNS decimal(18, 2)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result decimal(18, 2)

    -- Add the T-SQL statements to compute the return value here

    select @Result =  ISNULL(SUM(TransactionAmount) ,0)
    FROM     dbo.Student_Card_Transaction CT
    WHERE   CT.StudentID=@studentID
    and ct.IsDeleted=0
    and CONVERT(date, ct.TransactionDate) between @StarteDate  and DATEADD("day", 1, @EndDate)
    and ct.CurrencyID=@CurrancyID
    and ct.TransactionTypeID=@TransactionTypeID

--******************************************************************************************

    -- Return the result of the function
    RETURN ISNULL(@Result,0)

END

and the select query is

SELECT sp.studentcode,
       sp.studentnameen,
       sp.flag,
       dbo.Cardbalancebydate_fn(sp.serial, 1, '2017-12-15')
       StartBalance,
       dbo.Periodtotaltransactions(sp.serial, 1, 2, '2017-12-15', '2018-01-15')
       DebitTransaction,
(
dbo.Periodtotaltransactions(sp.serial, 1, 1, '2017-12-15', '2018-01-15')
+ dbo.Periodtotaltransactions(sp.serial, 1, 3, '2017-12-15', '2018-01-15') ) CreditTransaction,
dbo.Cardbalancebydate_fn(sp.serial, 1, '2018-01-15')
EndBalance
FROM   student_profile sp,
student_cards SC
WHERE  sp.serial = sc.studentid
AND sp.isdeleted = 0
AND sc.isdeleted = 0

the transaction Table is

CREATE TABLE [dbo].[Student_Card_Transaction](
    [Serial] [int] IDENTITY(1,1) NOT NULL,
    [StudentID] [int] NOT NULL,
    [CardID] [int] NOT NULL,
    [TransactionTypeID] [int] NOT NULL,
    [CurrencyID] [int] NOT NULL,
    [TransactionAmount] [decimal](18, 2) NOT NULL,
    [TransactionDate] [datetime] NULL,
    [UserID] [int] NULL,
    [Notes] [nvarchar](2000) NULL,
    [IsDeleted] [bit] NOT NULL CONSTRAINT
)

and the table involved

CREATE TABLE [dbo].[Student_Profile](
    [Serial] [int] IDENTITY(1,1) NOT NULL,
    [StudentCode] [nvarchar](20) NOT NULL,
    [StudentNameAR] [nvarchar](500) NOT NULL,
    [StudentNameEN] [nvarchar](500) NOT NULL,
    [StudentImagePath] [nvarchar](2000) NULL,
    [StudentEmail] [nvarchar](1000) NULL,
    [Flag] [int] NOT NULL,
    [IsDeleted]
)

I solved the problem using CROSS APPLY. The performance is so much better
the code is below:

select sp.StudentCode,sp.StudentNameEN,sp.Flag,DST.DTAmount-CST.CTAmount StartBalance,TDTrans.DTran  DreditTransactions,TCTrans.CTran Credittransactions,DET.DTAmount-CET.CTAmount EndBalance,@StartDate StartDate,@EndDate EndDate
from Student_Profile sp  cross apply
(
    select DTAmount = ISNULL(SUM(TransactionAmount) ,0)    --Charge & Refund
    FROM  dbo.Student_Card_Transaction CT
    WHERE  ct.IsDeleted=0
    and CONVERT(date, ct.TransactionDate) <  @StartDate
    and ct.CurrencyID=1
    and ct.TransactionTypeID!=2
    and ct.StudentID=sp.Serial
)as DST cross apply
(
    select CTAmount = ISNULL(SUM(TransactionAmount) ,0)     --orders
    FROM  dbo.Student_Card_Transaction CT
    WHERE  ct.IsDeleted=0
    and CONVERT(date, ct.TransactionDate) <  @StartDate
    and ct.CurrencyID=1
    and ct.TransactionTypeID=2
    and ct.StudentID=sp.Serial
)as CST cross apply
(
    select DTAmount = ISNULL(SUM(TransactionAmount) ,0)
    FROM  dbo.Student_Card_Transaction CT
    WHERE  ct.IsDeleted=0
    and CONVERT(date, ct.TransactionDate) <  DATEADD("day", 1, @EndDate)
    and ct.CurrencyID=1
    and ct.TransactionTypeID !=2
    and ct.StudentID=sp.Serial
)as DET cross apply
(
    select CTAmount = ISNULL(SUM(TransactionAmount) ,0)
    FROM  dbo.Student_Card_Transaction CT
    WHERE  ct.IsDeleted=0
    and CONVERT(date, ct.TransactionDate) <  DATEADD("day", 1, @EndDate)
    and ct.CurrencyID=1
    and ct.TransactionTypeID=2
    and ct.StudentID=sp.Serial
)as CET  cross apply
(
select DTran =  ISNULL(SUM(TransactionAmount) ,0)    --charge & Refund
    FROM     dbo.Student_Card_Transaction CT
    WHERE   CT.StudentID=sp.Serial
    and ct.IsDeleted=0
    and CONVERT(date, ct.TransactionDate) between @StartDate  and DATEADD("day", 1, @EndDate)
    and ct.CurrencyID=1
    and ct.TransactionTypeID!=2
)as TDTrans cross apply
(
select CTran =  ISNULL(SUM(TransactionAmount) ,0)     --order
    FROM     dbo.Student_Card_Transaction CT
    WHERE   CT.StudentID=sp.Serial
    and ct.IsDeleted=0
    and CONVERT(date, ct.TransactionDate) between @StartDate  and DATEADD("day", 1, @EndDate)
    and ct.CurrencyID=1
    and ct.TransactionTypeID=2
)as TCTrans
inner join  Student_Cards SC on sp.Serial=sc.StudentID
where sp.IsDeleted=0
and sc.IsDeleted=0
and sc.IsActive=1
and (sp.Flag=1)


How to order by 5, 501, 50101, 6, 601, 60101, etc? I've got a list of ledger account numbers. These numbers go like this:

5 (main account, #5)
501 (sub account of main account 5, #01)
50101 (sub account of this account, #01)
etc.

So if I have a list and want to order this by account number, then I'd like to create this order:

5
501
50101
50102
50103
6
601
60101
60102
60103

etc.

However, any ORDER BY will give me:

5
6
501
601

etc.

So what is the proper way of working with this kind of numbering? I imagine I can get another column for ordering, but I really prefer to use the account numbers as IDs that I use for ordering, as not to make things too complicated.

Convert it to CHAR  first:

ORDER BY CAST( AccounNumber AS CHAR )


What are the main principles of SQL Server AGs depending on WSFC? We have WSFC with 4 nodes + a file share witness.

Only nodes 1-3 have SQL server on it with Availability Groups (AGs), and node 4 is shut down gracefully.

Node 1 has the primary AAG server.

Now, we recently had an uncontrollable outage for which the only thing we know for certain is that nodes 1 and 2 were together with power supply and network, but nodes 3-4, and the witness, were cut off - but probably still up.

Since the power supply renewed, WSFC is showing results that I don't understand:

Current host server is Node 3;
In Cluster Core Resources, IP addresses that belong to nodes 1 and 2, are shown as down;
In Nodes section, nodes 1-3 are shown as up, and node 4 down (as expected);

BUT: The primary AG server is still on node 1 as it was before, and it accepts read/write connections as usually.

This make me doubt if I understand correctly how WSFC and AGs work together.

Should not the current WSFC host also be the primary AG server?
How does the listener work? In our case, we have multi-subnet networking, and the listener's DNS name always resolves to 3 IP addresses among which one belongs to the current primary AG server, and works for connections. Does WSFC determine the listener's addresses?
Is there any other principle (except quorum) that AG depends on WSFC?

Thanks.

Should not the current WSFC host also be the primary AG server?

No.  The hosting of the core cluster resources is independent of the AG primary.  Generally speaking, it's not super important which server is the WSFC host when dealing with a WSFC cluster for SQL Server Availability Groups.

  How does the listener work? In our case, we have multi-subnet networking, and the listener's DNS name always resolves to 3 IP addresses among which one belongs to the current primary AG server, and works for connections. Does WSFC determine the listener's addresses?

This part mostly happens on the client that's connecting to SQL Server.  The client gets that same list of IPs from DNS, and then tries each of them until one succeeds.  The current primary server will respond and either accept the connection or redirect the client to another server in the cluster (depending on connection settings and other config like read-only routing).

  Is there any other principle (except quorum) that AG depends on WSFC?

There's a neat breakdown on this in the docs as well: Relationship of SQL Server AlwaysOn Components to WSFC

In general though, the concept of quorum (which I'll take to be a part of the larger health monitoring / failover concept) is the most relevant one for WSFC and AGs.


Increase in number of temp files being generated after increasing checkpoint_timeout I have pgbadger set up to analyze the log files from my postgres db. I recently changed a couple of parameters in my postgres config.

I changed the checkpoint_timeout from the default value of 300 to 1800, i.e. checkpoints are triggered every 30 mins instead of every 5 mins.
I also changed the the autovacuum_scale_factor from 0.3 to 0.1 and autovacuum_threshold from 100 to 10000.

I can see in pgbadger analysis of the log files that autovacuuming is happening slightly more often and checkpoints are triggering every 30 minutes. However, I also see a change in the graph of temp files being created. After this change, more number of tempfiles are being created.

Isn't that a bad thing? Why is that happening and should I be worried?

Using temp files is not necessarily a bad thing.  They are nothing to worry about unless the queries that have started using them have gotten slower, or the amount of them that exist simultaneously is threatening to fill up your storage.  The change to autovacuum setting is more likely to be the cause than the change to the checkpoint setting.

What lead you to make these changes in the first place?  That could provide a clue.


Intermittent "Login timeout expired" after migrating to VmWare SQL Server We are experiencing intermittent Login timeout expired in our application after migration from a physical standalone SQL Server 2008 R2 / W2k8 R2 to a VMWare SQL Server 2014 / Win 2012 R2. It is happening around 5 times per day, so it's difficult to troubleshoot.

Failure Message

  Error: Microsoft SQL Server Native Client 10.0 : Unable to complete
  login process due to delay in prelogin response. Error: Microsoft
  SQL Server Native Client 10.0 : Login timeout expired.

More Details

I am pretty sure the sp_configure are the same in both instances.
We have less cpu compared to the previous server, but Max Worker Threads is OK.
When I was monitoring connections locally on SQL Server it was working fine at the same time I got Login timeout expired from the application.
Configure the remote query timeout will not solve my problem here.
We are using VMXNET3 Virtual NIC drive.

I am here answering my own question to help those who are experiencing the same behavior. After many observations and experiments with searching over the internet, I found the solution to this issue following the next steps.
Make sure you test everything before going to production.

Disable TCP Chimney features on the both the SQL Server & Client application server:
Disable TCPChimney and Offload features on the system level registries.

You can do it by running the command  "Netsh int ip set chimney DISABLED" from command prompt. But you can do it manually.

Go to the registry "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters"

a.    Double-click the EnableTCPChimney registry entry. If any entry is not present, create it by right clicking on the blank space and click on Create DWORD (32 bit).
b.    In the Edit DWORD Value dialog box, type 0 in the Value data box, and then click OK.
c.    Double-click the EnableRSS registry entry.
d.    In the Edit DWORD Value dialog box, type 0 in the Value data box, and then click OK.
e.    Double-click the EnableTCPA registry entry.
f.    In the Edit DWORD Value dialog box, type 0 in the Value data box, and then click OK.
g.    Create the registry key “SynAttackProtect” and – In the Edit DWORD Value dialog box, type 0 in the Value data box, and then click OK.
h.    Restart the server.

Disable TCPChimney and Offload features in the properties of NIC card.  Below are the instructions.

a.    Click Start, click Run, type ncpa.cpl, and then click OK.
b.    Right-click a network adapter object, and then click Properties.
c.    Click Configure, and then click the Advanced tab.
d.    In the Property list, click Receive Side Scaling, click Disable in the Value list, and then click OK.
e.    In the Property list, click TCP/IP Offload, click Disable in the Value list, and then click OK.
f.    Repeat steps 2 through 5 for each network adapter object.

Check if “IPv4 Checksum Offload” & “IPv4 Large Send Offload” OR “Checksum Offload” & “Large Send Offload” are enabled under Advanced Settings of NIC.  If they are, disable them. These options may vary with the model and make of the NIC.   The other properties to look for and disable are:

i.    Offload Receive IP Checksum
ii.   Offload Receive TCP Checksum
iii.  Offload TCP Segmentation
iv.   Offload Transmit IP Checksum
v.    Offload Transmit TCP Checksum

Related articles:

Information about the TCP Chimney Offload, Receive Side Scaling, and Network Direct Memory Access features in Windows Server 2008:    http://support.microsoft.com/kb/951037
Error message when an application connects to SQL Server on a server that is running Windows Server 2003: "General Network error," "Communication link failure," or "A transport-level error":         http://support.microsoft.com/kb/942861

Regarding the performance impact:

TCP Chimney Offload – Possible Performance and Concurrency Impacts to SQL Server Workloads:        http://blogs.msdn.com/b/psssql/archive/2008/10/01/windows-scalable-networking-pack-possible-performance-and-concurrency-impacts-to-sql-server-workloads.aspx

  Note: This would trigger the network breaking for a couple of seconds,
  so schedule this accordingly. Any registry changes would come into
  effect only after a reboot of the machine is done. Also if were
  disabling any NIC card property, the network will go down for a couple
  of seconds for the changes to reflect before it can resume back. So if
  this is a PRODUCTION box, schedule this during your downtime or
  Maintenance window

TCP TIME-WAIT Delay & MaxUserPort :
The following changes needs to be done on machines from where we connect to the SQL Server from the client applications. Add this on your application / web servers universally.

5. TCP TIME-WAIT Delay:
Set the TcpTimedWaitDelay registry key that is available in HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters to 30 secs.
If the registry key is not present, create them and set it to the value that we had suggested above.

6.       MaxUserPort:
Set the MaxUserPort registry key that is available in HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters to 65,000.
Again if the registry key is not present, create them and set it to the value that we had suggested above.

Refer the following article for more information:
TCP TIME-WAIT Delay: https://technet.microsoft.com/en-us/library/cc938217.aspx
MaxUserPort: https://technet.microsoft.com/en-us/library/cc938196.aspx


Does Batch Mode only apply to Agg Functions on ColumnStore Index Azure SQL Database - Standard Edition (S3 Service Tier)

Why does Batch Mode only come into effect when using Aggregate Functions?

DROP TABLE IF EXISTS dbo.TransCS

CREATE TABLE dbo.TransCS (
     Col1 INT
    ,Col2 AS Col1*2
)
CREATE CLUSTERED COLUMNSTORE INDEX CS_TransCS on dbo.TransCS;

WITH
    L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
    L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
    L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
    L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
    L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
    L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
    Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
    AS n FROM L5)
INSERT INTO dbo.TransCS (Col1)
SELECT TOP (1000000) n FROM Nums ORDER BY n;

Insert 1 Million rows

Row Processing

SELECT * FROM dbo.TransCS

Execution Plan

Batch Processing

SELECT Col1, Col2, SUM(Col2)OVER ()
FROM dbo.TransCS

Execution Plan

Is there a way to utilise the performance benefits without using Agg functions?
Documentation on this is thin.

Background
Back in July 2017, Niko blogged about a change in SQL Server 2017

...in SQL Server 2017 there is an internal optimisation for the Query Optimiser, that shall apply FULL optimisation to the execution plans with the Columnstore Indexes, avoiding the pitfall of executing them in the Row Execution Mode

Prior to that, you would get row-mode execution for "trivial plans," even when there were columnstore objects involved.
The execution plan you shared shows that the first query has this problem, which you can confirm in the XML:
StatementOptmLevel="TRIVIAL"

Workaround
One way to work around this, get FULL optimization, and thus get a batch mode scan, is to add a subquery that won't affect your results, like this:
SELECT * FROM dbo.TransCS WHERE (SELECT 1) = 1;

The second query gets full optimization already because of the aggregate function.
Why is this happening?
The only things that come to mind for why this might happen are:

your database has a compatability level of 130 or lower (this seems unlikely because I can see in the execution plan that the 140 cardinality estimator is being used)
this is a change / regression in Azure SQL Database specifically

The "build" indicated in the execution plan is 15.0.1100.504, which looks like a SQL Server 2019 build number (which makes sense, since this is Azure).  I don't have a 2019 instance to test on, but the change in behavior might be there as well

You may want to report this to Microsoft on the feedback site.


Improving performance of mapping standardized values with non equi joins - LIKE operator I have a large-ish data warehouse staging table (~2.5 million rows) that is using a mapping table to standardize a text column. I have an update statement that works perfectly, but has terrible performance, and would like some recommendations on modifications to the statement and/or alternate strategies for standardizing the data.

The source text can contain anything, and I'm basically looking for keywords in the strings. Because of the nature of the data, a longer string is more specific and therefore correct, so I want to get the longest mapping value found for each given row in the staging table.

Source data (simplified):

CREATE TABLE STAGINGTABLE (TextColumn VARCHAR(100));

INSERT INTO STAGINGTABLE VALUES ('This entry has a full foo bar entry');
INSERT INTO STAGINGTABLE VALUES ('Today was a foo day');
INSERT INTO STAGINGTABLE VALUES ('Only found bar data');
INSERT INTO STAGINGTABLE VALUES ('Blah blah blah');

CREATE TABLE MAPPING (SourceMap VARCHAR(100), TargetMap VARCHAR(100));

INSERT INTO MAPPING VALUES ('%FOO BAR%', 'Foo Bar');
INSERT INTO MAPPING VALUES ('%FOO%', 'Foo');
INSERT INTO MAPPING VALUES ('%BAR%', 'Bar');
INSERT INTO MAPPING VALUES ('%%', 'Unknown');

Update statement:

UPDATE STAGINGTABLE
SET TextColumn = (
    SELECT TargetMap
    FROM (
        SELECT TargetMap
        , ROW_NUMBER() OVER (PARTITION BY TextColumn
            ORDER BY LEN(SourceMap) DESC) AS ROWNUM
        FROM STAGINGTABLE
        INNER JOIN MAPPING
            ON UPPER(TextColumn) LIKE SourceMap
        WHERE TextColumn IS NOT NULL
    ) AS STG_MAP
    WHERE ROWNUM = 1
);

Final values in staging table after mapping:

Foo Bar
Foo
Bar
Unknown

Edit: the TextColumn and SourceMap columns do have non-clustered indexes.

Thanks to everyone's comments and suggestions, I tried all of the ideas @mustaccio posted, but in the end the fastest strategy was to abandon the mapping table altogether, and just go with a big ugly CASE statement to handle all of the situations, and insert that into another staging table. Execution time dropped to only a few seconds, and I can't argue with that. The situations to handle are mostly static, so if I have to re-deploy a SSIS package once or twice a year to add new situations if they come up, so be it.

Final example insert statement (not exact, just going off memory):

INSERT INTO STAGINGTABLE_TWO (TextColumn)
SELECT
  CASE
    WHEN TextColumn LIKE '%FOO BAR%' THEN 'Foo Bar'
    WHEN TextColumn LIKE '%FOO%' THEN 'Foo'
    WHEN TextColumn LIKE '%BAR%' THEN 'Bar'
    ELSE 'Unknown'
  END
FROM STAGINGTABLE;


Oracle error ORA-65052: statement involves operations with different container scope I'm a relative newb to Oracle. I have set up an instance of Oracle 12c running on a RHEL 7 VM. I am connecting to the database created during installation through and running some installation scripts.

Below is the output from the script that I am trying to run, can anyone point me in the right direction? I have a feeling I'm doing something fundamentally wrong.

Error starting at line : 4 in command -
GRANT CONNECT TO TDSUSER IDENTIFIED BY "TDSPASSWORD"
Error report -
SQL Error: ORA-65052: statement involves operations with different container scope
65052. 00000 -  "statement involves operations with different container scope"
*Cause:    An attempt was made to combine one operation that applies to all
           containers with another that only applies to the local container
           into one statement.
*Action:   Execute the operations in separate statements.
old:ALTER USER TDSUSER DEFAULT TABLESPACE &TBLSPC_TDS_TABLES TEMPORARY TABLESPACE &TBLSPC_TDS_TEMP PROFILE &DBPROFILE
new:ALTER USER TDSUSER DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp PROFILE TRIDION_SYS

Error starting at line : 6 in command -
ALTER USER TDSUSER DEFAULT TABLESPACE &TBLSPC_TDS_TABLES TEMPORARY TABLESPACE &TBLSPC_TDS_TEMP PROFILE &DBPROFILE
Error report -
SQL Error: ORA-02380: profile TRIDION_SYS does not exist
02380. 00000 -  "profile %s does not exist"
*Cause:    Try to assign a user to a non-existant profile
*Action:

I think this Q & A on the Tridion beta Stack Exchange site may help you:

Oracle Content Delivery CreateTridionSYSUser.sql errors

Basically you need to be in the pluggable database because of the multi-tenancy that is a new feature of 12c.

alter session set container=pdborcl;


MySQL COUNT(*) performance I have a table with more than 15m rows. I need the total number of rows. So:

SELECT COUNT(*) FROM thetable;

Which takes around 50s to complete. Explaining gives me Select tables optimized away. I suppose this means that the result can be found only by using an index, then why does it still take so long? Here are some information about the index on the id column (It's non nullable):

  Index Type: BTREE (clustered)

  Cardinality: 14623100

  Unique: YES

How can I improve the performance of this query? Thanks.

Note: The database is MySQL 5.7.1 and using InnoDB engine.

EDIT:

Create statement:

CREATE TABLE `properties` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `address` varchar(255) DEFAULT NULL,
  `locality` varchar(50) DEFAULT NULL,
  `latitude` decimal(13,9) DEFAULT NULL,
  `longitude` decimal(13,9) DEFAULT NULL,
  `state` varchar(10) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  .....
  PRIMARY KEY (`id`),
  KEY `index_properties_on_address` (`address`),
  KEY `index_properties_on_latitude` (`latitude`),
  KEY `index_properties_on_longitude` (`longitude`),
  KEY `index_properties_on_state` (`state`),
  KEY `index_properties_on_created_at` (`created_at`),
  .....
) ENGINE=InnoDB AUTO_INCREMENT=28267712 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;

Note: I omitted some lines, there are 44 columns.

Explain plan:

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered |            Extra             |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

Back when mysql was not transactionally sound by default (when people regularly used myISAM tables instead of InnoDB because that was the default or, going further back in time, because it didn't exist yet) "SELECT * FROM some_table" without any filtering clauses was one of the query types that peopel banged on about mySQL being much faster at that other database engines.

In a transactionally safe environment generally speaking the database engine will need to check every row and make sure that it should be visible to the current session (i.e. it isn't part of a transaction that is not yet committed (or wasn't committed at the start of this sessions active transaction) or is currently being rolled back) - checking every row implies needing to perform a table scan or (where one is present ) a clustered index scan.

It would be possible for the engine to keep track of the number of rows visible in each object for every active session/transaction, but presumably the designers have not judged this to be worth the extra processing involved so I assume it is not generally considered practical - I can imagine there would be some fairly complex locking requirements to deal with concurrency that would harm performance of other operations too much. You could implement this yourself by keeping a table in which is recorded the count of the rows in the table of interest, and have all your code meticulously maintain that value, but this would be quite some hassle and may be overly prone to errors due to bugs meaning that the count would drift from true over time (and you are probably adding a potential deadlock source and/or locking bottleneck at the application layer).

Situations where row-level security is in use complicate this even more - as well as needing to check the status of a row/page with respect to the current transaction, then engine needs to check again the current user too and as the security rules are dynamic it would be impractical to cache this information further necessitating the scan every time just-in-case. Row-level security is being added to MS SQL Server in the next release (https://msdn.microsoft.com/en-us/library/dn765131.aspx) and is already present in postgres (http://www.postgresql.org/docs/9.5/static/ddl-rowsecurity.html), I don't know about its status in other RDBMSs.


In MySQL, how exactly does data flows from query to disk? I would like to have clarity on how exactly data flows from the the time of firing a query till it goes to disk. (I have rough idea it will go to buffers, then to redo logs then disk files) but then how about bin_log_cache is used.

I have googled this but didn't find much on this topic. I hope to have some explanantion here among the between people of this community.

A Google of "peter zaitsev" file systems buffering double write gives several good references (Peter Zaitsev works for Percona who are big hitters in the MySQL world.

Of these, I picked out 4 as being of particular interest[1, 2, 3, 4].

In the first reference, Zaitsev gives a great précis of the various reasons why partial page writes might occur (section entitled "Now lets talk a bit about partial page writes").

Zaitsev then goes on to a section about how double write works, but I think that a better explanation is to be found in reference 2 - in particular this bit:

  If a partial page write occurs in the data files, InnoDB / XtraDB will
  check on recovery if the checksum of the page in the data file is
  different from the checksum of the page in the doublewrite buffer and
  thus will know if the page is corrupt or not. If it is corrupt, the
  recovery process will use the page stored in the doublewrite buffer to
  restore the correct data.

  If a partial write occurs in the doublewrite buffer, the original page
  is untouched and can be used with the redo logs to recover the data.

Basically, this behaviour is controlled by a system parameter called innodb_flush_log_at_trx_commit. This section is too big to be quoted in its entirety here, I'll just quote the bit about the default value: (I strongly recommend that you read it in full to get a good overview).

  ◾ The default value of 1 is required for full ACID compliance. With
  this value, the contents of the InnoDB log buffer are written out to
  the log file at each transaction commit and the log file is flushed to
  disk.

However, this section of the manual also says:

  Controls the balance between strict ACID compliance for commit
  operations, and higher performance that is possible when
  commit-related I/O operations are rearranged and done in batches. You
  can achieve better performance by changing the default value, but then
  you can lose up to a second of transactions in a crash.

In plain English this means that "ye gets what ye pays for" - i.e. there's a balance to be struck between data integrity and performance. Despite searching high and low however, I haven't been able to find any more than this in terms of detail - unless you want to go rummaging round the code (above my pay grade I'm afraid :-) ).

You can switch off the parameter innodb_doublewrite and then this leads one to another system parameter innodb_flush_method - again it's all about performance vs. data integrity. My references 3 and 4 go into detail on this - basically the debate in these two references is about whether it is a good idea to switch off doublewrite if you have a transactional file system.

The consensus appears to be that if you're using ZFS (and other, perhaps esoteric, file systems/devices), you're safe switching off doublewrites - assuming RAID with battery operated disk caches. Reference 4 IMHO suggests that the debate is still open as to whether ext4 is safe - also see here.

The diagram below provides a good overview of the MySQL's InnoDB architecture.


Deleting rows from tables without FK relationships I have three tables, one of them is the "master" table and each of the other two has a field that contains the PK of the corresponding row in the master table, something like this:

TblA         TblB           TblC
----         ----           ----
ID_A (PK)    ID_B (PK)      ID_C (PK)
FieldA       ID_A (FK)      ID_A (FK)
...          FieldB         FieldC
             ...            ...

The DBA refused to create FK relationships between these tables ("performance hit" was his explanation). So the FK fields are actually just "normal" fields.

When deleting rows from the master table (TblA), the corresponding rows in the other tables have to be deleted as well. As there are no FK relationships (and therefore no cascading deletion), what is the safest way to delete these rows?

What about this transaction? (@RowID is the ID of the row to be deleted in TblA.)

BEGIN TRANSACTION
BEGIN TRY
   DELETE FROM TblA
   WHERE ID_A = @RowID

   DELETE FROM TblB
   WHERE ID_A = @RowID

   DELETE FROM TblC
   WHERE ID_A = @RowID

   COMMIT TRANSACTION
END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION
END CATCH

Your example would allow for the cascading delete that a cascading delete foreign key would.  But I would argue that a foreign key does much more than facilitate simple cascading operations.  Borrowing from the documentation on FOREIGN KEY Constraints:

  A foreign key (FK) is a column or combination of columns that is used
  to establish and enforce a link between the data in two tables.  You
  can create a foreign key by defining a FOREIGN KEY constraint when you
  create or modify a table.

  In a foreign key reference, a link is created between two tables when
  the column or columns that hold the primary key value for one table
  are referenced by the column or columns in another table. This column
  becomes a foreign key in the second table.

  For example, the Sales.SalesOrderHeader table in the
  AdventureWorks2008R2 database has a link to the Sales.SalesPerson
  table because there is a logical relationship between sales orders and
  salespeople. The SalesPersonID column in the SalesOrderHeader table
  matches the primary key column of the SalesPerson table. The
  SalesPersonID column in the SalesOrderHeader table is the foreign key
  to the SalesPerson table.
  Blockquote

  Although the main purpose of a FOREIGN KEY constraint is to control
  the data that can be stored in the foreign key table, it also controls
  changes to data in the primary key table. For example, if the row for
  a salesperson is deleted from the Sales.SalesPerson table, and the
  salesperson's ID is used for sales orders in the
  Sales.SalesOrderHeader table, the relational integrity between the two
  tables is broken; the deleted salesperson's sales orders are orphaned
  in the SalesOrderHeader table without a link to the data in the
  SalesPerson table.

  A FOREIGN KEY constraint prevents this situation. The constraint
  enforces referential integrity by guaranteeing that changes cannot be
  made to data in the primary key table if those changes invalidate the
  link to data in the foreign key table. If an attempt is made to delete
  the row in a primary key table or to change a primary key value, the
  action will fail when the deleted or changed primary key value
  corresponds to a value in the FOREIGN KEY constraint of another table.
  To successfully change or delete a row in a FOREIGN KEY constraint,
  you must first either delete the foreign key data in the foreign key
  table or change the foreign key data in the foreign key table, which
  links the foreign key to different primary key data.


Mysql: select from information_schema.STATISTICS hanging or slow? never saw a result I'm trying to check statistics with
    select from information_schema.STATISTICS
The query never ends. I have to stop it.

I checked innodb_stats_on_metadata=0 like in this post Solving INFORMATION_SCHEMA slowness, it was already set to 0.

Any idea?
I have more or less 20K tables and mysql 5.6.
Could this also give performance problem?

20K tables means opening 20K files on the system.  This takes minutes on the typical server.  How long did you wait?

20K tables often means poor schema design.

BTW, the slowness will be solved in 8.0, when the .frm files are moved into InnoDB tables.  Many I_S queries will be a hundred times as fast.


Find missing folders paths in split hierarchies I have a table which contains folders' paths. This table contains four columns:

DirID - The folder's ID.
BaseDirID - The ID of the first folder in the hierarchy. So all the folders (paths) from the same hierarchy share the same value in this column.
DirLevel - The depth of the folder.
DisplayPath - The folder's path.

I need to find all the "gaps" between those folders in the hierarchy.

Sample data for example:

DirID BaseDirID DirLevel DisplayPath
1         1        1     'A'
2         1        3     'A\B\C'
3         1        5     'A\B\C\D\E'
4         1        3     'A\B\F'
5         1        5     'A\B\F\G\H'
6         2        1     'U'
7         2        3     'U\V\W'
8         2        5     'U\V\W\X\Y'
9         2        3     'U\V\M'
10        2        5     'U\V\M\L\O'

So we need to find the following data:

BaseDirID   DisplayPath
1           'A\B'
1           'A\B\C\D'
1           'A\B\F\G'
2           'U\V'
2           'U\V\W\X'
2           'U\V\M\L'

Comments:

This table contains more than 250,000 records of folders, so we seek for the most efficient way to do so, otherwise, the script will be stuck for a long time, the time we don't have.
I don't have the list of all folders. What I have are the "root" folders and the "leafs" folders which I need to find the "gaps" between them in the hierarchy.
The table can contain more than one hierarchy and we need to find the "gaps" in all of the hierarchies.
Each of the hierarchies can split, as you can see in the sample data the first hierarchy splits to two folders paths from the 'A\B' folder:
'A\B\C' and 'A\B\F'.
And the second hierarchy splits to two folders paths from the 'U\V' folder: 'U\V\W' and 'U\V\M'.
And we need to find all the "gaps" even in such cases when the hierarchies split.
We can make any changes to the table - add pk, indexes etc...
The SQL Server version is 2012 SP3.
The real folder names could be anything - one character or more.
The results must only contain the "gaps".
All missing intermediate folders are required.

Extended sample data and expected results in this dbfiddle.

This question is a continuation of the Stack Overflow question Find missing hierarchy Folders (Paths) in a table. Our question includes also the 4th comment which appears in bold.

I saw that there is a new type called hierarchyid (starting from SQL Server 2008), which I thought might help us. What do you think?

I'd try something like this:

First, create a new table and copy the existing data there.
Then iterate, and in each iteration, remove the last node of each filepath (e.g.: 'A\B\C\D\E' becoming 'A\B\C\D') and add these new filepaths in the table, if they aren't already there.
Stop when an iteration doesn't produce new rows.

The initialization:

-- step 0
CREATE TABLE filepaths
  ( BaseDirID int NOT NULL,
    DirLevel int NOT NULL,
    DisplayPath varchar(1000) NOT NULL,   -- adjust the size according to your data,
    ReverseDisplayPath varchar(1000) NOT NULL,
    Iteration int NOT NULL,
    PRIMARY KEY (Iteration, ReverseDisplayPath),
    UNIQUE (ReverseDisplayPath)
  ) ;

INSERT INTO filepaths
  ( BaseDirID, DirLevel, DisplayPath, ReverseDisplayPath, Iteration )
SELECT
    BaseDirID, DirLevel, DisplayPath, reverse(DisplayPath), 0
FROM
    existing_table ;

And the iterations:

DECLARE @new_items bigint ;
DECLARE @iter int ;
SET @iter = 0 ;

-- repeat
repeat:
    INSERT INTO filepaths
        (BaseDirID, DirLevel, DisplayPath, ReverseDisplayPath, Iteration)
    SELECT DISTINCT
        f.BaseDirID, f.DirLevel - 1, reverse(r.rdp), r.rdp, @iter + 1
    FROM
        filepaths AS f
      CROSS APPLY
        ( SELECT substring(f.ReverseDisplayPath,
                           1 + charindex('\', f.ReverseDisplayPath),
                           1000) AS rdp
        ) AS r
    WHERE
          f.Iteration = @iter
      AND f.DirLevel > 1
      AND NOT EXISTS
          ( SELECT *
            FROM filepaths AS ex
            WHERE ex.ReverseDisplayPath = r.rdp
          ) ;
    SET @new_items = @@ROWCOUNT ;

    SET @iter = @iter + 1 ;
    -- until new_items = 0
    IF (@new_items > 0) GOTO repeat;

After the process is finished, you can get only the missing filepaths with a simple query:

SELECT DisplayPath FROM filepaths
WHERE Iteration > 0
ORDER BY DisplayPath ;

Tested in dbfiddle.uk.

The reverse strings are not strictly needed but they simplify the splitting of the strings. You could go without them (and a bit more complex code). You could also have only the reverse paths stored in the new filepaths table and when the iterations are complete, reverse them again into the target table (the existing one I guess or wherever you want them to go).

The whole thing could also be done with a single pass - using a recursive CTE - but I don't know if that would be more or less efficient.


Performance issue with Top, Order By and Where condition with more than one million rows involved I have a query which looks something like below:
Declare @CompanyID as int = 10;

Select Top 50  UserID, AuditTypeID , [Action], ActionDate , [Description] ,
RecordID, S.ID as SiteUserID from AuditTrial A

inner Join SiteUser S on S.ID = A.UserID

where A.CompanyID = @CompanyID And A.AuditTypeID <> 1
And

UserID in
(
    1,2,3 -- will be a subquery - will have upto 100+ rows.
)

Order by ID desc -- Latest rows

This is a part of a little larger query with some extra and conditions which I have removed temporarily and even this stripped query runs slower.
Key things are Top 50, Order By ID Latest and a where condition with User ID.
The table contains more than 1 million rows.
UserID - can range from 100 to 20000.

Had Primary key with auto generate

Indexes on UserID, CompanyID etc

What I have tried so far
When the UserID where condition has less rows it runs good enough < 1 seconds but when the number of users grow it starts taking more time, i.e. greater than 10 seconds.
Thousands of rows are added in this table sometimes with SqlBulkCopy and regular normal inserts are common.

I had replaced the Subquery to Join - Not much impact
Put userIDs in temporary table first - again not much of a impact
Tried some view with DataSchema (googled) - still slow

Not being a DBA - I am not much aware about clustered/Non clustered index.
Any help is appreciated.
Additional details
Apart from Primary key which already creates an Index others are as follows:
For all in columns that will be used in where columns like UserID, CompanyID, PermissionID and so on. I didn't have any non clustered index with multiple columns
CREATE NONCLUSTERED INDEX [IX_AuditTrial_1] ON [dbo].[AuditTrial]
(
[UserID] ASC
)

If I hard code the UserID with 50 values separated by comma in ,In' Clause than the subquery runs instantly.
Full Live Query

declare  @SiteUserID as int = 6484,
 @CompanyID as int = 34;

DECLARE @ColleageUsers TABLE(
ID int
)

Insert into @ColleageUsers
Select SUB.SiteUserID from SiteUserBroker SUB (nolock)
inner join (Select Count(*) as TotalBrokers , SiteUserID from SiteUserBroker group by SiteUserID)Nested on Sub.SiteUserID = Nested.SiteUserID
where BrokerID in (
Select BrokerID from SiteUserBroker (nolock) where SiteUserID = @SiteUserID)
group by SUB.SiteUserID
Having Count(*) - MIN(TotalBrokers) >= 0

Select Top 50  UserID, AuditTypeID , [Action], ActionDate , [Description] , RecordID, ActionedBy =  S.FirstName + ' ' + S.LastName , S.ID as SiteUserID from AuditTrial (nolock) A
inner Join SiteUser (nolock) S on S.ID = A.UserID
where A.CompanyID = @CompanyID And A.AuditTypeID  1 -- 1 Means Audit Type Login
And
(
184 = (Select Distinct Top 1 PermissionID from PermissionGroup (nolock) where GroupID in
(Select GroupID from SiteUserGroup (nolock) where SiteUserID = @SiteUserID) and PermissionID = 184)
OR
A.AuditTypeID  20   - Not equal to
)
And (A.PermissionID is null Or A.PermissionID in ( Select PermissionID from PermissionGroup (nolock) where GroupID in
(Select GroupID from SiteUserGroup (nolock) where SiteUserID = @SiteUserID)))
And
(
(
UserID in
(
  Select Id from @ColleageUsers

)
)
)

Order by A.id desc

Query Plan
https://www.brentozar.com/pastetheplan/?id=SkQt9mGvr
Plan Link

Below are a few things to try, either separately or altogether.

OPTION(RECOMPILE)

Since you are using variables declared in the query text itself, you could use OPTION(RECOMPILE) to recreate the query plan on each execution. This way the optimizer 'sees' the values in the variables at runtime, and you should get a query plan better suited for these values.

Disable row goals

Since you have a row goal set due to the TOP() operator, you could try disabling that rowgoal with the traceflag: 4138.

Row goal example in your plan on the concatenation operator:

Disable row goals at the query level with: OPTION(QUERYTRACEON 4138);

More on row goals here

Testing these together with your query:

SELECT TOP 50  UserID, AuditTypeID , [Action], ActionDate , [Description] , RecordID, ActionedBy =  S.FirstName + ' ' + S.LastName , S.ID as SiteUserID
FROM AuditTrial (nolock) A
INNER JOIN SiteUser (nolock) S on S.ID = A.UserID
WHERE
A.CompanyID = @CompanyID
AND A.AuditTypeID <> 1 -- 1 Means Audit Type Login
AND
(
184 = (Select Distinct Top 1 PermissionID from PermissionGroup (nolock) where GroupID in
(Select GroupID from SiteUserGroup (nolock) where SiteUserID = @SiteUserID) and PermissionID = 184)
OR
A.AuditTypeID <> 20
)
AND (A.PermissionID
        IS NULL
     OR  A.PermissionID IN
                ( SELECT PermissionID
                    FROM PermissionGroup (nolock)
                    WHERE GroupID in
                                    (SELECT GroupID FROM SiteUserGroup (nolock)
                                     WHERE SiteUserID = @SiteUserID)
                 )
    )
AND
(
(
UserID IN
(
  SELECT Id FROM @ColleageUsers
)
)
)
ORDER BY A.id desc
OPTION(RECOMPILE,QUERYTRACEON 4138 );

Removing the OR's and using UNION ALL

Something else, which does need you to rewrite the query, is separating the OR's with UNION ALL's.

This way indexing / operators could be used more efficiently at the cost of processing more data. This depends on the filters & indexes used.

An example for your query:

SELECT TOP 50  UserID, AuditTypeID , [Action], ActionDate , [Description] , RecordID, ActionedBy,SiteUserID

FROM
(
SELECT UserID, AuditTypeID , [Action], ActionDate , [Description] , RecordID, ActionedBy =  S.FirstName + ' ' + S.LastName , S.ID as SiteUserID , A.id
FROM AuditTrial (nolock) A
INNER JOIN SiteUser (nolock) S on S.ID = A.UserID
WHERE
A.CompanyID = @CompanyID
AND A.AuditTypeID <> 1 -- 1 Means Audit Type Login
AND
(
184 = (Select Distinct Top 1 PermissionID from PermissionGroup (nolock) where GroupID in
(Select GroupID from SiteUserGroup (nolock) where SiteUserID = @SiteUserID) and PermissionID = 184)
OR
A.AuditTypeID <> 20
)
AND (A.PermissionID
        IS NULL
    )
AND
(
(
UserID IN
(
  SELECT Id FROM @ColleageUsers
)
)
)
UNION ALL
SELECT TOP 50  UserID, AuditTypeID , [Action], ActionDate , [Description] , RecordID, ActionedBy =  S.FirstName + ' ' + S.LastName , S.ID as SiteUserID ,A.id
FROM AuditTrial (nolock) A
INNER JOIN SiteUser (nolock) S on S.ID = A.UserID
WHERE
A.CompanyID = @CompanyID
AND A.AuditTypeID <> 1 -- 1 Means Audit Type Login
AND
(
184 = (Select Distinct Top 1 PermissionID from PermissionGroup (nolock) where GroupID in
(Select GroupID from SiteUserGroup (nolock) where SiteUserID = @SiteUserID) and PermissionID = 184)
OR
A.AuditTypeID <> 20
)
 AND  A.PermissionID IN
                ( SELECT PermissionID
                    FROM PermissionGroup (nolock)
                    WHERE GroupID in
                                    (SELECT GroupID FROM SiteUserGroup (nolock)
                                     WHERE SiteUserID = @SiteUserID)
                 )

AND
(
(
UserID IN
(
  SELECT Id FROM @ColleageUsers
)
)
)) AS A;

ORDER BY A.id desc
OPTION(RECOMPILE,QUERYTRACEON 4138 );

Table variables / temporary tables

Another thing I would try is changing the table variable to a temporary table

CREATE TABLE #ColleageUsers(
ID int
)

To get statistics on the temporary object, since table variables have no statistics but temporary tables do.

Splitting the query up into multiple pieces with the use of temporary tables

Another thing to try to get a different execution plan is doing some of the work beforehand, and storing these results into a temporary table. When many tables get touched, it is harder to get correct estimates, splitting up the work makes it easier to calculate the estimates for each part.

An example for your query:

declare  @SiteUserID as int = 6484,
 @CompanyID as int = 34;

SELECT PermissionID
INTO #TEMP
FROM PermissionGroup (nolock)
WHERE GroupID in
(SELECT GroupID FROM SiteUserGroup (nolock)
 WHERE SiteUserID = @SiteUserID)

DECLARE @PermissionID INT
SELECT DISTINCT Top 1 @PermissionID=PermissionID
FROM PermissionGroup (nolock) where GroupID in
(Select GroupID from SiteUserGroup (nolock)
where SiteUserID = @SiteUserID) and PermissionID = 184

SELECT TOP 50  UserID, AuditTypeID , [Action], ActionDate , [Description] , RecordID, ActionedBy,SiteUserID

FROM
(
SELECT UserID, AuditTypeID , [Action], ActionDate , [Description] , RecordID, ActionedBy =  S.FirstName + ' ' + S.LastName , S.ID as SiteUserID , A.id
FROM AuditTrial (nolock) A
INNER JOIN SiteUser (nolock) S on S.ID = A.UserID
WHERE
A.CompanyID = @CompanyID
AND A.AuditTypeID <> 1 -- 1 Means Audit Type Login
AND
(
184 = (@PermissionID)
OR
A.AuditTypeID <> 20
)
AND (A.PermissionID
        IS NULL
    )
AND
(
(
UserID IN
(
  SELECT Id FROM @ColleageUsers
)
)
)
UNION ALL
SELECT TOP 50  UserID, AuditTypeID , [Action], ActionDate , [Description] , RecordID, ActionedBy =  S.FirstName + ' ' + S.LastName , S.ID as SiteUserID ,A.id
FROM AuditTrial (nolock) A
INNER JOIN SiteUser (nolock) S on S.ID = A.UserID
WHERE
A.CompanyID = @CompanyID
AND A.AuditTypeID <> 1 -- 1 Means Audit Type Login
AND
(
184 = (Select Distinct Top 1 PermissionID from PermissionGroup (nolock) where GroupID in
(Select GroupID from SiteUserGroup (nolock) where SiteUserID = @SiteUserID) and PermissionID = 184)
OR
A.AuditTypeID <> 20
)
 AND  A.PermissionID IN
                ( SELECT PermissionID
                FROM #TEMP
                 )

AND
(
(
UserID IN
(
  SELECT Id FROM @ColleageUsers
)
)
)) AS A

ORDER BY A.id desc
OPTION(RECOMPILE,QUERYTRACEON 4138 );


Conditional formatting in SSRS 2008r2 I am new to report writing and am trying to suppress rows that have zero data.  I need to know how to write the conditional iif statement when I have multiple columns of data.  Example

account number  account description  fy 2012 fy 2013 fy 2014 fy 2015

some accounts have zero numbers across the years and I don't want that row to show.

Here's how you hide How to: Hide an Item (Reporting Services)

Here's the expression

=IIF( Fields!yourField1.Value = 0  and Fields!yourField2.Value = 0
  and Fields!yourField3.Value = 0  , True, False )


SQL availability groups log maintenance I have a AG with 2 nodes in a site in sync mode and 1 offsite in async mode.  I'm having trouble understanding how the logspace is maintained in this scenario.  For instance, I'm taking log backups every 15 mins on the primary server and on the secondary server in sync modem.  If I move my log backups off to the secondary node (in sync mode) does this mean it will release space on the primary node too, or do I still need to run tran backups on the primary node, which will in turn release space on the secondary nodes too?

When you run the log backups from a secondary server, when the log backup is complete the secondary replica will tell the primary replica that the log backup has been completed and what the last LSN to be backed up was.  The primary replica will then mark the VLFs which were backed up as inactive.  That change (which is logged) will then be transferred to the secondary replicas.

In other words, no you don't need to take backups from the primary replica as well as the secondary.


SQL Server AlwaysON : start sync with secondary for selected databases I have an SQL Server 2012 AlwaysON cluster composed of :

win-mssql1 : witness file share

win-mssql2 (INSTANCE 1) : primary server

win-mssql3 (INSTANCE 2) : secondary server

Recently I had the host "win-mssql4 (INSTANCE 3)" as a new secondary replica. My goal is to :

Manually synchronise a huge database (let's call it "BaseSyncManual") with this new server.
2.Later, synchronize the other databases which are in the AG.

To do the first step, I have used the followings commands :

On primary server :

BACKUP DATABASE BaseSyncManual TO DISK = 'I:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\Backup\setra_full.bak';
BACKUP log BaseSyncManual TO DISK = 'I:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\Backup\setra_log_2.trn';

On secondary server :

  RESTORE DATABASE BaseSyncManual FROM DISK = 'I:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\Backup\setra_full.bak'
    WITH NORECOVERY

    RESTORE DATABASE BaseSyncManual FROM DISK = 'I:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\Backup\setra_log_2.trn'
    WITH NORECOVERY

On primary : start the wizard to add a replica and choose "skip initial data synchronisation"

On secondary : alter database BaseSyncManual set hadr availability group = AvailabilityGroup1;

Everything runs fine and the result is what I have expected :

Now I want to re-run the "add a new replica assistant" in order to synchronize the remaining databases (Base1,Base2,BaseSyncAuto) with the "full" parameter (automatically backup restore and join to AG). But there is no way to do that ! the replica as already been added to the AvailabilityGroup :

and I can't Add a database using "add database" because they are already member of the AvailabilityGroup on the fist secondary replicate :

What are my options to sync those databases with my new secondary replica ? Am I condemned to do it with manual backup-restore-join to AG ?

Yes, you will need to manually backup and restore the databases, with logs, to the new secondary and then join the databases to the AG.  This might help.


SQL server 2008 Analysis Services DSO properties We are planning to do Sharepoint and project server 2007 databases migration.

The share point website also processes cubes.

So we will be moving SSAS cubes from SQL 2005 to 2008 and then re-pointing the location from the sharepoint admin website.

The following document covers “Requirements for using SQL Server 2008 Analysis Services with the Project Server 2007 Cube Building Service”

http://technet.microsoft.com/en-us/library/dd285466%28office.12%29.aspx

the document is easy to follow but there is a section which mentions:

“You need to configure the Analysis Services DSO properties so that the repository database is configured to be used from DSO in SQL Server 2008 Analysis Services. Changes made during this process will be reflected in the Analysis Services configuration file (Msmdsrv.ini).” So this involves playing with DSO* properties under the SSAS server.

The existing SSAS server has only sharepoint cubes but the new SSAS server has cubes for other applications too.

I can do the settings following this document but I want to know if there are any risks involved in changing DSO properties on SSAS server.

Can anyone please help or put me in right direction?

Answered by An Expert friend:

It wont effect other cubes. DSO component will only be used when a user runs DSO 8.0 application and will have no impact on SQL Server 2008 Analysis Services database.


Postgres not being able to create a tablespace I just mounted an external HD for store my new tablespace.

postgres@vostro ~ $ df -k
Filesystem     1K-blocks     Used Available Use% Mounted on
/dev/sdb1      480589520    71580 456082280   1% /mnt/DB

postgres@vostro ~ $ ls -l /mnt/DB/
total 4
drwxrwxrwx 2 postgres postgres 4096 May 25 17:02 postgres

demisc@vostro /mnt $ mount | grep -i sdb1
/dev/sdb1 on /mnt/DB type ext4 (rw,nosuid,nodev,relatime,data=ordered)

I gave all the necessary permissions so that posgres could create the new ts in /mnt/DB/postgres. Here is the user groups:

postgres@vostro ~ $ id
uid=123(postgres) gid=131(postgres) groups=131(postgres),112(ssl-cert),1000(demisc)

Happens that when running the create tablespace command I get the following:

postgres=# create tablespace ts_project location '/mnt/DB/postgres';
ERROR:  could not set permissions on directory "/mnt/DB/postgres":
Permission denied

What I am missing?

ls -ln /mnt/DB/postgres

returns:

demisc@vostro /mnt/DB $ ls -ln /mnt/DB/postgres/
total 0

Everything looks good. There is no reason why postgres user can not create the tablespace. If i switch to postgres and try to create file or dir in the path it works fine.

I just found a solution. I changed the permission of /mnt/DB/postgres to 700:

postgres@vostro /mnt/DB $ ls -l
total 4
drwx------ 3 postgres postgres 4096 May 25 17:46 postgres

Now it worked the creation of the ts

postgres@vostro /mnt/DB/postgres $ ls -l
total 4
drwx------ 2 postgres postgres 4096 May 25 17:46 PG_9.5_201510051

Community Wiki answer created from a comment left by the question author.
 Community wiki posts have been donated to the community in hopes that others will edit them to keep them up to date, to add useful information, and generally improve their quality.


What to have in the main table and what to have in meta_table? I'm try to figure out the best scheme for the users table for my software. I'm trying to separate the main data and auxiliary data into 2 table called users and user_meta for the sake of extensibility (something like what Wordpress has done).
Now I need to figure out which data to put into which table. Here is the basic data a user might have.

user_id
email
password
user_type -> int
register_date
phone
name
image -> url
description -> text (might contain html)
... so many other possible attributes for a user which is specific to my software

It would be great if you could help me decide which kind of data belongs to which table.

the user_meta table should contain details about the fields that will be in the user table.

for example, user_meta will have rows for each field in the user table

user_meta
---------
user_field_name | data_type  | size  | extra params
---------------------------------------------------
user_name       | varchar    | 220   | primary key
password        |varchar     |220    |not null

while the user table will have rows containing every field stated in the user_meta
using the example above, the user table will have look like this

users
-----
user_name |password
-------------------
user1     |password1
user2     |password2
...


What operations result in automatic creation of SQL Server statistics? My understanding is that creating an index automatically creates statistics for that column.
What else results in auto creation of statistics? For example - Does SQL Server create statistics for every column that has ever appeared in a WHERE clause?

AUTO_CREATE_STATISTICS Option

When the automatic create statistics option, AUTO_CREATE_STATISTICS is ON, the Query Optimizer creates statistics on individual columns in the query predicate, as necessary, to improve cardinality estimates for the query plan.
These single-column statistics are created on columns that do not already have a histogram in an existing statistics object.
When the Query Optimizer creates statistics as a result of using the AUTO_CREATE_STATISTICS option, the statistics name starts with _WA.
You can use the following query to determine if the Query Optimizer has created statistics for a query predicate column.
SELECT OBJECT_NAME(s.object_id) AS object_name,
    COL_NAME(sc.object_id, sc.column_id) AS column_name,
    s.name AS statistics_name
FROM sys.stats AS s
INNER JOIN sys.stats_columns AS sc
    ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
WHERE s.name like '_WA%'
ORDER BY s.name;

That includes WHERE predicate:
SELECT *
FROM tab
WHERE col = ?;    -- here tab.col

and JOIN predicate(to determine physical join type: NESTED LOOPS/MERGE/HASH):
SELECT *
FROM tab1
JOIN tab2
  ON tab1.id = tab2.tab_1_id; -- here tab2.tab_1_id