Disaster recovery plan - Having multiple servers I work in an organization where departments are in different buildings. I have a data centre in the main building. All the in-house applications connect to the SQL Server residing in the server in the main building datacenter.

Sometimes during weekends, if the network fails or the software throws a bug, it is very hard to come back to office and fix the problem.
It was suggested that a local machine be placed in one building (other than the datacenter) and install SQL Server on it. Have this SQL Server store the data first, and then replicate it on main datacenter every few minutes.

This will help in case the network gets disconnected with the main datacenter.

I would like to know if this the only solution we can adopt? What problems would we face with this strategy?

It sounds like what you're talking about is more aligned with High Availability.

If you are already on SQL Server 2012, or you have the ability to upgrade then it sounds like AlwaysOn Availability Groups could be right up your alley.  This is a technology that will allow you to create a cluster and you could have two geographically separate servers (i.e. your current database server and your remote database server that you're planning on standing up).  If the network latency isn't a performance impact between the two servers you could set up a two node cluster and have each of those nodes hosting a replica of the Availability Group (or Groups).  With synchronous commit, you could configure automatic failover when there is a network outage or some other service-hindering event.

By creating an Availability Group Listener you could have the client applications connecting to the listener which will direct traffic to the current primary replica.

If you don't have the ability to get onto SQL Server 2012 to leverage AlwaysOn Availability Groups, then Database Mirroring could also be another high availability solution for you.  If the session is operating synchronously with a witness server, you could also have the ability for automatic failover in the event of the principal becoming disconnected.

The reason I am recommending these two is two-fold: replication is pushing out articles, and it sounds like you want your entire database (or databases) to be ready for a failover.  Traditional failover clusters could also be another solution but you would have to also consider shared storage between the two nodes.  That might get complicated with the geographically separate servers.

  if the network fails or the software throws a bug, it is very hard to come back to office and fix the problem.

What do you mean by "the software throws a bug"?  There will need to be the proper error handling and error recovery in order to ensure this type of behavior doesn't cause an outage.


Mysql Select vs Select SUM performance I need to check whether the surfer has alrady voted this can be done using

Method 1

Select record_num FROM table where etc etc.

i.e.

SELECT record_num
FROM content_votes_tmp
WHERE up = 1
AND ip = INET_ATON('$_SERVER[REMOTE_ADDR]')
AND content = $_POST[id]
AND UNIX_TIMESTAMP(datetime) > '$old_time'

Method 2

Select Sum(votes) FROM table where etc etc.

i.e.

SELECT SUM(up) as up_count
FROM content_votes_tmp
WHERE ip = INET_ATON('$_SERVER[REMOTE_ADDR]')
AND content = $_POST[id]
AND UNIX_TIMESTAMP(datetime) > '$old_time'

Using storage engine as MyISAM ,
Table has around 1 million rows,
ROW Format  is static.

I am looking for query which is faster in terms of performance.

So which query will be faster ? this query will be fired every time someone clicks the vote up or vote down button.,

here is a table structure

CREATE TABLE IF NOT EXISTS `content_votes_tmp` (
  `up` int(11) NOT NULL DEFAULT '0',
  `down` int(11) NOT NULL DEFAULT '0',
  `ip` int(10) unsigned NOT NULL,
  `content` int(11) NOT NULL,
  `datetime` datetime NOT NULL,
  `is_updated` tinyint(2) NOT NULL DEFAULT '0',
  `record_num` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`record_num`)
  KEY `content` (`content`),
  KEY `datetime` (`datetime`),
  KEY `is_updated` (`is_updated`),
  KEY `ip` (`ip`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=25 ;

The 2 queries that you show are different in meaning.

The first one will select all upvotes for that ip and content in that timeframe. If you create the index:

mysql> ALTER TABLE content_votes_tmp ADD INDEX(up, ip, content, datetime, record_num);
Query OK, 753676 rows affected (3.85 sec)
Records: 753676  Duplicates: 0  Warnings: 0

You can get good performance results- the first 2 columns will be used and you will get the Using Index optimization:

mysql> EXPLAIN SELECT record_num
               FROM content_votes_tmp
               WHERE up = 1 AND
                     ip = 1  AND
                     content = 1 AND
                     UNIX_TIMESTAMP(datetime) > @number\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: content_votes_tmp
         type: ref
possible_keys: up
          key: up
      key_len: 12
          ref: const,const,const
         rows: 1
        Extra: Using where; Using index
1 row in set (0.00 sec)

We can do even better than that. Remove the UNIX_TIMESTAMP() function -compare dates instead of ints by transforming $old_time- and will be able to apply all conditions using the index (3 consts and 1 range):

mysql> EXPLAIN SELECT record_num
               FROM content_votes_tmp
               WHERE up = 1 AND
                     ip = 1  AND
                     content = 1 AND
                     datetime > now()\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: content_votes_tmp
         type: range
possible_keys: up
          key: up
      key_len: 17
          ref: NULL
         rows: 1
        Extra: Using where; Using index
1 row in set (0.00 sec)

Be careful if the logic of the application requires to know if a downvote was done, this query will not help you with that.

The second one will require a slightly different index:

mysql> ALTER TABLE content_votes_tmp ADD INDEX(ip, content, datetime, up);
Query OK, 753676 rows affected (5.95 sec)
Records: 753676  Duplicates: 0  Warnings: 0

In order to get a good query plan, also needing the "move the function to the other side of the operand":

mysql> EXPLAIN SELECT SUM(up) as up_count
       FROM content_votes_tmp
       WHERE ip = 1 AND
             content = 1 AND
             datetime > now()\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: content_votes_tmp
         type: range
possible_keys: ip
          key: ip
      key_len: 13
          ref: NULL
         rows: 1
        Extra: Using where; Using index
1 row in set (0.00 sec)

I do not like the range + SUM(), so I would prefer the first one, which is simpler (unless you intend to return lots of rows). But there is nothing on the query plan to support my fears (it has the covering index optimization too, and no filesort, so both can be very fast with the appropriate indexes- they take 0.00s with my fake 1M data).

Remember that the logic is slightly different, so be aware of that- if you allowed upvotes and downvotes, that could be a problem.


Re-Run Backup Job in SQL Agent My question is regarding re-running backup jobs only for those databases for which the job initially when it ran failed due to some reasons, like server reboot, path not accessible, newly created databases, etc.
In our environment, windows patching happens every month. Due to server reboot at this time, backup job fails. Also, sometimes if user creates a new database then a scheduled differential backup job fails.
Is there any way to run the same job but for only those databases which were not backed up earlier?

Please provide your suggestions

While it depends on how many jobs and your backup methods, one of the simplest options is to have the job use retry attempts and intervals to re-run the job.

You can set the intervals at a high amount to account for a full system reboot and use these jobs specifically during reboot windows. Just go to Job Properties -> Steps -> Advanced

(Job pictured is from Ola Hallengren's scripts which contain great backup jobs)

Include a cleanup step first to remove any backups from recent history (say, 1 hour) that may no longer be valid/desirable and then perform a complete backup. I think this is preferable as it gives you a set of backups all from the same time frame and not staggered depending on when the shutdown occurred.

Really though, you should be able to reliably control and schedule your reboots and thus schedule key backup jobs around that time frame. We all update our boxes (I hope) and work around a set schedule for reboots so as to not have this issue. Stronger change control management might be the true solution if this server is important enough to stay on top of backups.


SQL Server to XML files alternatives I am currently looking for an alternative to writing a large, complex XML file based on a known specification. Currently the file is written in t-sql as a stored procedure. The output is in a results column which is pretty useless when there are thousands of rows and each needs to be written to individual files. It is also a nightmare (for me) to debug. Break points frequently encompass 1000's of lines of code.

To overcome the XML to file problem I altered the stored procedure to insert the XML to a staging table and then use a query in SSIS to pull the data out of the table, a C# script to create the files and then an SSIS extension from codeplex to zip and send the file to an external SFTP server. With a little more automation this process is workable and meets the project criteria.

I have searched google and these forums to try and find a feasible alternative and I was unsuccessful. The ideal solution would not require additional commercial software and open source would be a bit of an issue as well. I am hoping CLR would be an alternative as writing the code in .NET and its xml libraries seem to be more abstract rather than hand coding text. I see no support for CLR or much at all about SQL to XML. XML to SQL is by far more prevalent.

I hope I have covered what I have tried and the efforts I put in to find an alternate solution on my own.

Let me know if anyone has some suggestions, even if they do not meet the ideal solution criteria.

First question post by the way.

Your current setup seems to be on the right track. I would cut SSIS out of the equation - the stored procedure INSERTs into the staging table, and then a C# app pulls the data out, creates the XML files, and SFTPs the files off to the destination. I realize that's more a 'developer' solution than an 'DBA' solution, but flat files and SFTP are not things well-suited to SQL environments.


Must declare the scalar variable "@VarName" Could anyone shed some light on this error for me? To a beginner of course. This will probably be no more than a simple fix for someone else.
I'm trying to read in the Product to be used in the where clause.

alter proc Proc1
@ProductID int
as
go
select ProductName
from Products
where ProductID = @ProductID

Take out the go after the variable declaration.  It is causing SQL server to end the batch which makes your variable declaration go out of scope.

alter proc Proc1
@ProductID int
 as
 select ProductName
 from Products
 where ProductID = @ProductID

Here is some details from Microsoft on what the go keyword/statement does.


Join with keys of both tables I try to bring to tables together in a join-like way. Consider the column id_ as "on clause" key. In the tables below, there are more id_s in table 1 than 2. I would like to have all ids and values of both tables. If there is not a respective id_, the value is 0 or null.
CREATE TABLE IF NOT EXISTS t1 (
   id_ INTEGER  NOT NULL,
   value1 INT NOT NULL
)

CREATE TABLE IF NOT EXISTS t2 (
   id_ INTEGER  NOT NULL,
   value2 INT NOT NULL
)

INSERT INTO t1 (id_, value1) VALUES
(1, 10),
(2, 20),
(3, 30)

INSERT INTO t2 (id_, value2) VALUES
(2, 40),
(3, 60)
(4, 80)

I use the platform Metabase, a BI tool.

In MySQL (which does not support FULL JOIN) you must use gathering subquery:
SELECT id_, t1.value1, t2.value2
FROM ( SELECT id_ FROM t1
       UNION
       SELECT id_ FROM t2 ) total
LEFT JOIN t1 USING (id_)
LEFT JOIN t2 USING (id_);


Add MS SQL Server to virtual machine in Azure I'm trying to get TDE in Azure. I read that it's possible only on a virtual machine. I built the virtual box, but didn't add SQL Server when it was created. Can I just install SQL or will I have to start from scratch and recreate the virtual machine.

Thank you for your help.

Ernie_M

SQL Azure Transparent Data Encryption is possible, but it is currently still in preview.

If you do not want to accept the supplemental terms of use for this then you can simply install SQL Server on the VM you just created or provision a new machine and use one of the numerous pre-built images that are available.

I would recommend you simply spin up a new machine as it is relatively painless, offers configuration by Microsoft, and ensures you have a SQL Server License since it is bundled with the VM image's pricing.

If you install software on a standalone VM you need a license for that software since the basic VM image is likely priced for and only includes a Windows license. There are a lot of plans and maybe you have a MSDN license... you'd have to check the details that pertain to your situation/subscription etc. to know that aspect of the decision for sure.


How to tune mysql to be inmemory-like I've given MySQL 5GB of memory (I use innodb), but when I insert lots of data (dumpfile is 1GB), hard drive I/O is still a bottleneck (CPU is not busy and hard drive is).

Is it possible to force MySQL not to make the hard drive a bottleneck?

I have five(5) aspects to discuss here

ASPECT #1 : innodb_buffer_pool_size

You need to run this query

SELECT CEILING(SUM(data_length+index_length)/power(1024,3)) BPSIZE
FROM information_schema.tables WHERE engine='InnoDB';

This will give you the ideal sized buffer pool because InnoDB caches data and index pages.

If the DB Server has 32G RAM, the biggest buffer pool would be 75% of the Machine (which would be 24G) or BPSIZE, whichever is smaller. In other words, for 32G RAM, if BPSIZE is 10G, then use 10G. If BPSIZE is30G, use 24G.

ASPECT #2 : InnoDB Log Buffer

Setting the size of the Log Buffer can be a dream come true or your worst nightmare. How?

Dream Come True

The MySQL Documentation on innodb_log_buffer_size says the following:

  The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value is 8MB. A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger saves disk I/O.

Your Worst Nightmare

According to the Book

Page 428 Paragraph 8 says the following:

  The InnoDB storage engine logs information about current transactions in a memory buffer. When a transaction commits or rolls back, the log buffer is flushed to disk. If the log buffer is small, it might fill up before the end of the transaction, requiring a flush to the log file before the outcome of the transaction is known. For a committed transaction, this results in multiple disk operations rather than one. For a rolled-back transaction, it results in writes that, with a larger buffer, would not need to be made at all. To set the size of the log buffer, use the innodb_log_buffer_size option. The default is 1MB. Typical values range from 1MB to 8MB. Values larger than 8MB are of no benefit.

This excerpt is for MySQL 5.0.13. InnoDB has matured a lot since then, but the principle remains the same: Setting innodb_log_buffer_size too high can have two adverse affects:

It can waste memory for low write environments or very small transactional changes
Produce much longer commits

Lesson Learned

Look at the default innodb_log_buffer_size

5.0 (Default: 1M)
5.1 (Default: 1M)
5.5 (Default: 8M)
5.6 (Default: 8M)

You will have to experiment with larger values, perhaps 16M, 32M, 64M, or 128M.

Just remember

the lower innodb_log_buffer_size is set, the more frequent larger transactions may flush
the higher innodb_log_buffer_size is set, the longer commits may take

So, don't crazy with the values. Be as conservative as possible but look for reduced disk I/O you desire.

ASPECT #3 : Dirty Pages in the Buffer Pool

90% by default on MySQL 5.0/5.1
75% by default on MySQL 5.5/5.6

You should set this to 90 regardless of the version of MySQL. The higher it is, the more delay on flushing for heavy write loads. Given the increased performance of MySQL 5.5/5.6, this may not be necessary.

ASPECT #4 : Disk Issues

Run this query:

SHOW VARIABLES LIKE 'innodb_file_per_table';

If you get OFF for innodb_file_per_table, you need to Cleanup the InnoDB Infrastructure. I wrote post back on October 29, 2010 on how to do this.

With regard to having InnoDB Data sitting on a different disks, this may reduce Disk I/O but at the risk of reduced overall InnoDB Performance. Why ? Back on Feb 06, 2012, I wrote about why spreading InnoDB on different disks is not all that performant.

What is also noteworthy is this regard is the system tablespace ibdata1. Every time you access an InnoDB tables, the data dictionary is always consulted. Thus, having spread out tables onto other disk does not negate accessing ibdata1 back in datadir. Accessing two disks would always be in order.

InnoDB has many moving parts in ibdata1:

Data Dictionary
Double Write Buffer

Safety Net to Prevent Data Corruption
Helps Bypass OS for Caching

Insert Buffer (Streamlines Changes to Secondary Indexes)
Rollback Segments
Undo Logs
Click Here to see a Pictorial Representation of ibdata1

Expect the Highest I/O around ibdata1. In light of this, whatever disk tuning you do, give weight to the location of ibdata1.

ASPECTS #5 : Transaction Flushing

I am not a big fan of setting  innodb_flush_log_at_trx_commit to 0 or 2 since it does the following:

relaxes ACID compliance
risks 1 second of data loss in the event of a crash
puts more faith and trust in the OS and hardware for flush disk changes

EPILOGUE

Please keep this foremost in mind: Your goal here should be to get InnoDB to cache transactional changes as much as possible and flush to disk as little as possible. This will certainly curtail disk I/O without expending effort on hardware too quickly. Only after the needed due diligence fails to reduce disk I/O should you explore hardware.


Does MySQL/InnoDB use table caches when dealing with foreign tables? TL;DR: Does a table referred to via schema.table get held in table cache?

I have a large number of client databases that our application connects to based on an http request. These are used for every part of the application except for access logging. We use a second central schema for recording of login attempts across all instances of the application.

The login table, however, is accessed via the connection to the local schema as a foreign table. For example:

 UPDATE central_schema.login SET column = 'value';

Rather than using a second connection directly to the central schema and just dealing with login as a local table:

UPDATE login SET column = 'value';

Or switching schemas:

USE central_schema;
UPDATE login SET column = 'value';
USE client_schema;

Given that during busy times I see a lot of simple update or select queries waiting for the table to close is it possible that MySQL doesn't cache tables when referred to cross-schema? Is it possible that, because it's connected to client_schema, it wants to use the client schema cache and thus the foreign table can't be properly cached?

And, if I can't get a definitive answer to that, how would I even begin to test what's in the cache and what isn't?

Let's see if you have issues with table_open_cache:

SHOW VARIABLES LIKE 'table%cache';
SHOW GLOBAL STATUS LIKE '%open%';
SHOW GLOBAL STATUS LIKE 'Up%';

How many tables do you have in the system (not just the one database)?

FOREIGN_KEYs need to reach into an index of the 'other' table.  This is usually the cost penalty for FKs.  However all such accesses are cached in the innodb_buffer_pool, so it is often not a disk hit.


Inconsistent ResultSet from Procedure with Variables I'm working on building a procedure with two inputs, and the Syntax checks out, but the result-set is not consistent with the logic.

In this question, there are in actual fact two questions. Number 1 is the most important in this context. Number 2 is less important.

1. Inconsistency

Data Sets

    [tbl] employees (PRIM)           [tbl] loc_offices (SEC)        [tbl] emp_types (SEC)
+-----------------+-----------+  +-----------------+-----------+  +-----------+-----------+
|     COLUMNS     | DATATYPE  |  |     COLUMNS     | DATATYPE  |  |  COLUMNS  | DATATYPE  |
+-----------------+-----------+  +-----------------+-----------+  +-----------+-----------+
| emp_id          | <VARCHAR> |  | office_id       |   <INT>   |  | emp_type  | <VARCHAR> |
| emp_lastname    | <VARCHAR> |  | office_name     | <VARCHAR> |  | type_desc | <VARCHAR> |
| emp_firstname   | <VARCHAR> |  | office_town     | <VARCHAR> |  +-----------+-----------+
| emp_replocation | <VARCHAR> |  | office_province |   <INT>   |
| emp_type        | <VARCHAR> |  +-----------------+-----------+
| emp_status      |  <INT>    |
| emp_image       |  <BLOB>   |
+-----------------+-----------+

Procedure

CREATE PROCEDURE `getEmployees`(
    IN location INT(1),
    IN stafftype VARCHAR(1)
)
BEGIN
    SELECT
        emp_id,
        emp_lastname,
        emp_firstname,
        office_name,
        type_desc,
        emp_status
    FROM
        employees t1
    INNER JOIN loc_offices t2
        ON t1.emp_replocation = t2.office_id
    INNER JOIN emp_types t3
        ON t1.emp_type = t3.emp_type
    WHERE
        IF (
            location = '%' AND stafftype = '%',
            (
                t1.emp_replocation = location
                AND t1.emp_type = stafftype
            ),
            IF (
                location = '%' AND stafftype != '%',
                t1.emp_type = stafftype,
                IF (
                    location != '%' AND stafftype = '%',
                    t1.emp_replocation = stafftype,
                    '%'
                )
            )
        )
    ORDER BY
        emp_id ASC
    ;
END

Test Calls

CALL getEmployees();

• Expected Outcome:
Return all records in data set

• Outcome:
"Incorrect number of arguments for PROCEDURE db.getEmployees; expected 2, got 0"
CALL getEmployees(NULL, NULL);

• Expected Outcome:
Return all records in data set

• Outcome:
Empty Result-set
CALL getEmployees(1, NULL);

• Expected Outcome:
Return records in data set filtered by emp_replocation only

• Outcome:
Empty Result-set
CALL getEmployees(NULL, "C");

• Expected Outcome:
Return records in data set filtered by emp_type only

• Outcome:
All records, with correct emp_type
CALL getEmployees(1, "C");

• Expected Outcome:
Return records in data set filtered by emp_type and emp_replocation

• Outcome:
Empty Result-set

Changes Attempted

Replacing (!)= '%' with IS (NOT) NULL
Replacing '%' with FALSE

The Question

Based on this information, why is the location parameter ignored, and what is the correction recommended in this situation?

2. The second question

I've researched this, but unsure of the correct approach. How can I handle empty Procedure Calls?

In the case of getEmployees(), it currently results in an incomplete call error, which is expected, instead of selecting all records without filters.

CREATE PROCEDURE `getEmployees`( IN location INT(1),
                                 IN stafftype VARCHAR(1) )
SELECT ... /* output columns list */
... /* source tables with JOIN conditions */
WHERE COALESCE(parameter, table.field) = table.field /* replace with real names */
... /* another conditions, grouping, ordering */
;

If parameter (location or stafftype) is not NULL then the condition is equal to WHERE parameter = table.field.

If parameter value is NULL then the condition is equal to WHERE table.field = table.field which is TRUE until table.field is NULL (if such records must be returned too then use <=> operator instead of =).


Creating SQL Server Job on each replica in availability group I have an availability group with 2 nodes (one primary and another secondary). The availability database has a stored procedure which is used to create a SQL Server job dynamically, based on user input. With this the jobs are always created only on the primary server. How can I create the same job on each replica in the availability group?

The listener redirects to primary server and the stored procedure on the primary server creates the job on the primary server only. I want to know if there is a way to modify the procedure on the primary server so that it can create jobs on other replicas in the availability group? While running the job, I will put a simple check, so that the job will run only on the current primary server only.

Why not use a master server for the SQL Server agent? You could even use a third server to create and schedule the jobs on both the servers and you get a central repository and log server for the jobs. A small check in the agent job will make certain that it's running on the active server.


How can I make PQExecPrepared() not hang if the network connection has been lost? We're using prepared statements and have found that if, after successfully connecting and executing some statements, the network link the connection was usning goes away,  PQExecPrepared() hangs forever.  Is there a way to dictate from the client side, a maximum time to wait?  In comparison, if the Postgres server goes down, rather than the network, PQExecPrepared() returns immediately with an error.

When the remote PostgreSQL server goes down, the remote OS knows that and tells your OS that the connection has closed, by sending a TCP FIN.  When the network goes down, there is no one who knows what happened (or at least no way for them to tell you what happened) so no TCP FIN.  So you have to wait for the TCP connection itself to give up.

If you want more control over this, you can use PQsendQueryPrepared instead.


how see parameter value on xml_deadlock_report i have some xml_deadlock_report i got from this step

management > sessions > system_health > package().even_file

but when i see xml_deadlock_report i got sql query but i can't see what parameter use in where condition

<deadlock>
 <victim-list>
  <victimProcess id="process30e9e810c8" />
 </victim-list>
 <process-list>
  <process id="process30e9e810c8" taskpriority="0" logused="5608" waitresource="PAGE: 31:1:8080697 " waittime="982" ownerId="3428828901" transactionname="implicit_transaction" lasttranstarted="2020-06-02T16:57:47.850" XDES="0x2bea7c6d28" lockMode="U" schedulerid="1" kpid="23404" status="suspended" spid="150" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-06-02T16:58:06.750" lastbatchcompleted="2020-06-02T16:58:06.747" lastattention="1900-01-01T00:00:00.747" hostname="172.16.12.40" hostpid="0" loginname="user_xxxx_admin" isolationlevel="read committed (2)" xactid="3428828901" currentdb="31" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="40" sqlhandle="0x02000000adf830280fe6a3ada9d7a7b50732f1aa6cf6359e0000000000000000000000000000000000000000">
DELETE FROM TTADATTSTATUSDETAIL
            WHERE Attend_ID = @P1    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@P1 nvarchar(4000))DELETE FROM TTADATTSTATUSDETAIL
            WHERE Attend_ID = @P1    </inputbuf>
  </process>
  <process id="process30e9db1c38" taskpriority="0" logused="7004" waitresource="PAGE: 31:1:8635778 " waittime="1040" ownerId="3428815086" transactionname="implicit_transaction" lasttranstarted="2020-06-02T16:57:35.037" XDES="0x29609d83a8" lockMode="U" schedulerid="5" kpid="12076" status="suspended" spid="130" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-06-02T16:58:06.030" lastbatchcompleted="2020-06-02T16:58:06.023" lastattention="1900-01-01T00:00:00.023" hostname="172.16.12.40" hostpid="0" loginname="user_xxxx_admin" isolationlevel="read committed (2)" xactid="3428815086" currentdb="31" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="40" sqlhandle="0x02000000adf830280fe6a3ada9d7a7b50732f1aa6cf6359e0000000000000000000000000000000000000000">
DELETE FROM TTADATTSTATUSDETAIL
            WHERE Attend_ID = @P1    </frame>
   </executionStack>
   <inputbuf>
(@P1 nvarchar(4000))DELETE FROM TTADATTSTATUSDETAIL
            WHERE Attend_ID = @P1    </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <pagelock fileid="1" pageid="8080697" dbid="31" subresource="FULL" objectname="dbSF_BizNet_KPKRI.dbo.TTADATTSTATUSDETAIL" id="lock29105cff80" mode="U" associatedObjectId="72057594477346816">
   <owner-list>
    <owner id="process30e9db1c38" mode="U" />
   </owner-list>
   <waiter-list>
    <waiter id="process30e9e810c8" mode="U" requestType="wait" />
   </waiter-list>
  </pagelock>
  <pagelock fileid="1" pageid="8635778" dbid="31" subresource="FULL" objectname="dbSF_BizNet_KPKRI.dbo.TTADATTSTATUSDETAIL" id="lock29808f0600" mode="IX" associatedObjectId="72057594477346816">
   <owner-list>
    <owner id="process30e9e810c8" mode="IX" />
   </owner-list>
   <waiter-list>
    <waiter id="process30e9db1c38" mode="U" requestType="wait" />
   </waiter-list>
  </pagelock>
 </resource-list>
</deadlock>

how i see what value at parameter @P1 ??

You cannot see that information in the deadlock report, because it is a parameterized query. However, maybe it helps you to use Brent Ozar's sp_blitzLock to troubleshoot your deadlocks. https://www.brentozar.com/archive/2017/12/introducing-sp_blitzlock-troubleshooting-sql-server-deadlocks/

Edit:

You can only see what parameters are used to compile the query plan. However, that probably is not what you are looking for, because the query that caused the deadlock has most likely different parameters then the one used to compile the plan. If you want to, refer to this article:

https://www.mssqltips.com/sqlservertip/4992/how-to-find-compiled-parameter-values-for-sql-server-cached-plans/

In order to see the compiled values you probably can use Extended Events. I looked it up and found this article that explains how to do that:

https://www.scarydba.com/2018/09/24/extended-events-and-stored-procedure-parameter-values/

Be aware that running Extended Events can have an impact on your server in terms of performance.

The issue with performance you are running into is probably parameter sniffing. Suddenly your Stored Procedure runs slow. There is already tons of stuff written about it. Refer to this article:

Why is my query suddenly slower than it was yesterday?


possible/available appointments table I am building an appointments web app...the user will be able to see available/closed appointment slots for a business entity.
I am trying to use this schema  here

In it, there is a possible appointments table...left joining it with booked appointments table we arrive at the available appointments table.

It's structure though as depicted in the example does not reflect my needs...in my app the business entity  enters its schedule...for example:
Monday-to-Friday 10:00AM-16:00PM...

The question is what structure a table must have that will hold data like this...as I said this table will hold all possible appointment slots and its data must be based on the schedule...like the one above.

Appointment scheduling is too complex for doing in SQL.  Do it in application code.

Given that, focus on having the database table(s) contain the minimal information to indicate times available and times taken.

The SELECT may end up being "select all rows having to do with a particular day", then the app munches on the data.


Storage and presentation of database data across multiple languages Apologies if this has been asked before.  After extensive searching I cannot find anything that serves as a solution (a similar answer is posted below).  I am afraid this is an open-ended (broad) question simply because I don't know what I don't know.  Please don't downvote if not suitable for this site; some pointers in the right direction would be much appreciated instead.

We have a web application running on SQL Server 2012 with ASP.Net.  There are around 150 tables and 600 stored procedures.  We have successfully managed to manage data across three different languages historically, fortunately because each client only required data in their own language.

For example:

Client enters database data from web forms in their own language (using nvarchar/UTF16 across all respective fields)
ASP.Net uses resource files to provide the interface in the local language

We now have a prospect who requires a standard set of global database data to be available in 20 languages.  While the interface translation (although a big job) isn't technically difficult, I am struggling to see how this is achieved in SQL Server, and would appreciate any input from people who have achieve this historically.  Given that enterprise-level companies such as Oracle and SAP must do this regularly (I assume), I'm sure it's possible somehow.

A related answer, albeit for MySQL seems to be feasible...

  Normally I use two tables, main table has default language, just in case you forget to add corresponding values into translations tables.

  create table books (
      book_id int,
      book_name varchar(200),
      description varchar(500)
  )
  create table books_language (
      book_id int,
      language_id vachar(10),
      book_name varchar(200),
      description varchar(500)
  )

  This returns all records including default language.

  select book_id,
         isnull(books_language.laguage_id, 'default')
         isnull(books_language.name, books.name) as name,
         isnull(books_language.description, books.description) as description
  from books
       left join books_language
       on books.book_id = books_language.book_id

...but I can't figure out the following practicalities, assuming my database doubles to 300 tables in size.

Because each user requires data in their own specific locale, how do you pass every query without doing this dynamically, for the application to bind to the respective display fields?
How do you handle a table having 20 data fields, rather than just one as in the example?
How do you deal with text searches, when the table required for the data may differ every time?

Or is there a better way entirely?

I've tried widgets such as Google Translator and Bing, but they don't work properly.  While they both struggle with data from partial page reloads, they just translate everything, and often not very well.  I've also read about plug-in software, although these were for Oracle only.

Examples

Take a skills database for example, where peoples' skills are recorded and managed.  The person table won't obviously need translating, and date formats (e.g. date of birth) would be handled by the interface (.Net based on browser settings).  However, the skills table is more complex:

Id int,
SkillTitle nvarchar(100),
Description nvarchar(max),
Objectives nvarchar(max)

Then there's the table that defines abilities against the skills:

Id int,
SkillLevel int,
Title nvarchar(100),
Description nvarchar(max),
ContextualData nvarchar(max)

Basically in my database there's about 70 tables where text-based data would need to be managed in different languages.  Some have a couple of text columns, and some as much as 10.

Well, a lot of this depends on exactly what data elements need to be translated, and at the moment I think you might be focusing too much on technical specifics instead of where the need truly lies.

Given this:

  ASP.Net uses resource files to provide the interface in the local language

what elements need to be translated in the database?

Application / System lookup values? Probably not these since the UI is handling that via resource files.
User data? No, this wouldn't make sense. If someone fills out a form, they don't expect it to magically appear in 20 different languages.
Customer configured lookup values? Possibly, but they would be the ones supplying the translations.

Can you provide 1 or 2 examples of something that would need this structure? It seems that this structure is what would be used if one were not using resource files.

The only globalization issue I can see here is how to handle sorting and comparison according to each user's preferred language, given that many cultures have different rules, even if sharing many / most of the characters. In this respect, you will need to make use of Dynamic SQL because unfortunately you cannot specify collations dynamically (please vote for my suggestion to allow for this: Allow collation set by variable when using the COLLATE clause (at least in expressions) ). But for now (or possibly forever given the likelihood of that request being implemented :-(  ), you will need to attach the COLLATE keyword to relevant parts of queries (WHERE / GROUP BY / ORDER BY / etc) based on the current user's language. So one person might get French_100_CI_AS and someone else might get Latin1_General_100_CI_AS, and so on. This doesn't require multiple fields.

The only thing that different columns for different languages would get you is the ability to index the different collations (i.e. locales). So if there is a place that really needs performance, you can consider specifying the primary language as the collation of the column, and then create non-persisted computed columns that simply return the main column along with a different collation, and then index the non-persisted computed column(s).

If you feel that your app really will need to store multiple languages worth of a single item that would not be handled by the resource files, then please provide at least one, if not two examples. But even if there are some, I can't see how it would ever be all, or even most, columns. Things like customer names don't change based on what language they come from. I mean, I did adopt a Spanish form of my name for Spanish class in Junior High and High School, but that was for class. If I become a customer of a company in a Spanish-speaking country, I will still give them my real name. And the name of my street, city, and state won't change either.


How to optimize this SQL Server query? Anyone have any suggestions for how to optimize this query?
Running SQL Server 2008..

The object is to look at accounts that were created yesterday, compare their email addresses and phone numbers to existing accounts to identify if it is a duplicate account to one already in the system.

SELECT DISTINCT Customer.CustId
    , Customer.PriceClassID
    , Customer.Name
    , CustomerEDI.S4Future01 as First
    , CustomerEDI.S4Future02 as Last
    , Addr1
    , Addr2
    , City
    , State
    , Zip
    , Country
    , Customer.emailAddr
    , Customer.Phone
    , lastorder
    , CONVERT(varchar(8000), snotetext)
FROM Customer
WHERE
    EMailAddr IN (
        SELECT EmailAddr
        FROM Customer
        WHERE EmailAddr != ''
        GROUP BY EmailAddr
        HAVING COUNT(*) > 1)
        AND EMailAddr IN (
            SELECT EMailAddr
            FROM Customer
            WHERE Crtd_DateTime >= DATEADD(day, -1, CAST(GETDATE() AS DATE))
            )
    OR Phone IN (
        SELECT Phone
        FROM Customer
        WHERE Phone != ''
        GROUP BY Phone
        HAVING COUNT(*) > 1)
        AND Phone IN (
            SELECT Phone
            FROM Customer
            WHERE Crtd_DateTime >= DATEADD(day, -1, CAST(GETDATE() AS DATE))
            )
ORDER BY EMailAddr
    , Phone ASC
    , Name
    , Last
    , First ASC;

First of all, if custId is the Customer's Primary Key, then there's no need to specify DISTINCT at all. Second, grouping and then filtering by the grouped key may be very inefficient when the filtering will likely render a small subset: filter first, then group. Third, I assume you don't require casting to varchar when removing the DISTINCT clause. Fourth, no need to include the alias of every selected field when there's only one table in the selection, it just makes reading the query more cumbersome!

This query can really benefit from indexes on EMailAddr and Phone.

This may perform better, especially if those indexes exist:

SELECT DISTINCT CustId
    , PriceClassID
    , Name
    , S4Future01 as First
    , S4Future02 as Last
    , Addr1
    , Addr2
    , City
    , State
    , Zip
    , Country
    , emailAddr
    , Phone
    , lastorder
    , snotetext
FROM Customer
WHERE
    EMailAddr IN (
        SELECT EmailAddr
        FROM Customer
        WHERE EMailAddr IN (
            SELECT EMailAddr
            FROM Customer
            WHERE EmailAddr > '' AND Crtd_DateTime >= DATEADD(day, -1, CAST(GETDATE() AS DATE))
            )
        GROUP BY EmailAddr
        HAVING COUNT(*) > 1)
    OR Phone IN (
        SELECT Phone
        FROM Customer
        WHERE Phone IN (
            SELECT Phone
            FROM Customer
            WHERE Phone > '' AND Crtd_DateTime >= DATEADD(day, -1, CAST(GETDATE() AS DATE))
            )
        GROUP BY Phone
        HAVING COUNT(*) > 1)
ORDER BY EMailAddr
    , Phone ASC
    , Name
    , Last
    , First ASC;


Best method to synchronize two tables across SQL servers I have one large table in a MySQL table that when updated, I want to synchronize with a duplicate of the table on a Microsoft SQL server. Here is the process that happens when a new row is inserted into the MySQL table, and how it is sent to the MS SQL server table:

When a new row is inserted into the MySQL table, there's a trigger that inserts the same row into a temp table on the MySQL server
A job on the MS SQL server periodically checks the temp table, and from there takes any rows and inserts them into the MS SQL using a MERGE statement.
Temp table is cleared out of any processed rows.

I have recently found out that very randomly some rows have just not been transferred over to the MS SQL server. What I am wondering is, is there a better way to do this, with reliability being the first priority, and speed being less of a priority, what other methods are out there? Thanks for any help, feel free to ask for clarification if I did not explain it well.

I suspect you grab the list of rows to move, then an insert occurs, then you delete the rows.  Instead...

Ping-pong a pair of tables.  You are always inserting into one table, always copying from the other.  Then do an atomic RENAME TABLE to switch.

See my high speed ingestion blog for something very similar.  It explains the flip-flop technique.


Combining full-text and scalar index Let's say we have a database of 12 million names and addresses that need to be searchable using full-text, but each row contains an integer value too, let's say COMPANYID. The table contains around 250 distinct COMPANYIDs over those 12 million rows.

Is it possible, when defining the full-text indices, to give each COMPANY its own "branch" in the tree?

No is the short answer, and you don't really need this.  Full-text indexes are inverted indexes so they store the split words by the unique doc_id that you have to specify when you create the full-text index.  This must be a "unique, single-key, non-nullable column" ideally an integer.  What is essentially a foreign key does not figure and there is no easy way to partition them on that basis.

You could spoof something like this it with a table per company and full-text index per table.  You would need some kind of code logic sat in front to determine which table to insert to / fetch from. This would be a considerable headache to manage at is almost certainly not worth it.

If you had some serious volume ( eg more like 23 billion records ) then you could look at a sharding solution, eg something like an Azure VM per company with an app sat in front of them to determine which machine to connect to.  But clearly you don't need that either.

There were also a number of improvements in SQL 2008 to full-text which is now more integrated into the database engine.  One scenario, where you specify a WHERE clause against a normal column and use the full-text functions, is referred to as a 'Mixed Query' and discussed here.  This is still a great article even though the information is for SQL 2008.

If you're generally concerned about performance and plans, why not spin up some test data, introduce some skew and try it out.  I knocked up this script with ~2 million rows in a few minutes:

!!TODO introduce some skew
USE master
GO

SET NOCOUNT ON
GO

DBCC TRACEON(610)   -- Minimal logging
GO

GO

IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'fullTextDemo' )
BEGIN
    ALTER DATABASE fullTextDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE fullTextDemo
END
GO

IF NOT EXISTS ( SELECT * FROM sys.databases WHERE name = 'fullTextDemo' )
CREATE DATABASE fullTextDemo
GO

ALTER DATABASE fullTextDemo SET RECOVERY SIMPLE
GO

USE fullTextDemo
GO

IF OBJECT_ID('dbo.yourAddresses') IS NOT NULL DROP TABLE dbo.yourAddresses
IF OBJECT_ID('dbo.companies') IS NOT NULL DROP TABLE dbo.companies
GO

CREATE TABLE dbo.companies (
    companyId       INT IDENTITY NOT NULL,
    companyName     NVARCHAR(50) NOT NULL,

    CONSTRAINT PK_companies PRIMARY KEY ( companyId )
)
GO

CREATE TABLE dbo.yourAddresses (
    rowId           INT IDENTITY,
    companyId       INT NOT NULL FOREIGN KEY REFERENCES dbo.companies ( companyId ),
    searchTerms     NVARCHAR(2048) NOT NULL

    CONSTRAINT PK_yourAddresses PRIMARY KEY ( rowId )
)
GO

-- Populate the companies
;WITH cte AS (
SELECT TOP 250 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.companies ( companyName )
SELECT NEWID()
FROM cte
GO

-- Generate 2,636,000 records
INSERT dbo.yourAddresses ( companyId, searchTerms )
SELECT c.companyId, m.[text]
FROM dbo.companies c
    CROSS JOIN ( SELECT * FROM sys.messages ) m
WHERE m.language_id = 1033
AND m.[text] Like '[a-z]%'
GO

CREATE INDEX _idx ON dbo.yourAddresses ( companyId ) INCLUDE ( searchTerms )
GO

-- !!TODO look at compression
--ALTER INDEX PK_yourAddresses ON dbo.yourAddresses REBUILD WITH ( DATA_COMPRESSION = PAGE )
--GO

-- Create the catalog
IF NOT EXISTS ( SELECT * FROM sys.fulltext_catalogs WHERE name = N'ftc_yourAddresses' )
CREATE FULLTEXT CATALOG ftc_yourAddresses
GO

-- Create the full-text index
CREATE FULLTEXT INDEX ON dbo.yourAddresses ( searchTerms ) KEY INDEX PK_yourAddresses ON ftc_yourAddresses WITH CHANGE_TRACKING MANUAL  -- CHANGE_TRACKING OFF, NO POPULATION
GO

SELECT 'before' ft, * FROM sys.fulltext_indexes
GO

ALTER FULLTEXT INDEX ON dbo.yourAddresses START FULL POPULATION;
GO

DECLARE @i INT
SET @i = 0

WHILE EXISTS ( SELECT * FROM sys.fulltext_indexes WHERE has_crawl_completed = 0 )
BEGIN

        SELECT outstanding_batch_count, *
        FROM sys.dm_fts_index_population
        WHERE database_id = DB_ID()

        --SELECT *
        --FROM sys.dm_fts_outstanding_batches
        --WHERE database_id = DB_ID()

    WAITFOR DELAY '00:00:05'

    SET @i = @i + 1
    IF @i > 60 BEGIN RAISERROR( 'Too many loops!', 16, 1 ) BREAK END

END

SELECT 'after' ft, * FROM sys.fulltext_indexes
GO

SELECT TOP 1000 *
FROM dbo.yourAddresses ft
WHERE companyId = 42
 AND CONTAINS ( searchTerms, 'data' )
GO

SELECT TOP 1000 *
FROM dbo.yourAddresses a
    INNER JOIN CONTAINSTABLE ( dbo.yourAddresses, searchTerms, 'data' ) ct ON a.rowId = ct.[key]
WHERE a.companyId = 42
GO

SELECT TOP 1000 *
FROM dbo.yourAddresses a
    INNER JOIN CONTAINSTABLE ( dbo.yourAddresses, searchTerms, 'data' ) ct ON a.rowId = ct.[key]
WHERE a.companyId = 42
OPTION ( MERGE JOIN )
GO

SELECT TOP 100 *
FROM sys.dm_fts_index_keywords (DB_ID(), OBJECT_ID('dbo.yourAddresses') )

SELECT TOP 100 *
FROM sys.dm_fts_index_keywords_by_document(DB_ID(), OBJECT_ID('dbo.yourAddresses') )
ORDER BY document_id
GO


Postgres jsonb column or standard normalized table? I'm implementing an app with a payment system, and I need to record transactions that are made through the app. Also I need to use some of the information about the transaction for rendering some KPIs. I have an implementation already in Postgres in which my table has two columns, id and transaction (jsonb).
Inside my transaction column I have an object that looks like this:

2018: {
   November: {
      list_of_transactions: [],
      totalAmountEarned: 0,
      numberOfTransactions: 0,
      avarageSpending: 0,
      numberOfCoins: 0,
      numberOfUsers: 0
   }
}

Now, whenever I make a transaction, I check that the year and the month that comes with the request exists, else add them to the object, and push the transaction inside list_of_transactions, and update all the other keys accordingly.

I was wondering whether this is a good way of tackling the problem, or is in fact a really bad way. Is creating different tables and normalizing them in a "SQL fashion" a better solution? Have you got any suggestion?

Additional considerations

A side question: since there's going to be many many transactions is it a good idea to create a new table for each year?

The structure of all the data involved will be exactly the same, so I should create multiple databases and do joins. Since there's going to be many, many transactions: is it a good idea to create a new table for each year?

The rule of thumb in every relational database is normalizing (usually up to 3NF or even 4NF).  With the advent of JSON into the relational world, people are often tempted to solve everything using JSON, as it probably means a very simple way of channeling some data moving in the application into the database.  However, while it has its own place, I see it more often to be the case than not, that the data could be mostly or in its entirety normalized.

Basically whenever you see that your JSONs share the same keys (having values of the same data type), you should go and extract those keys into proper columns.  If the whole structure is always the same, extract everything, create tables as needed (if you have arrays in the JSON, for example, it can be a sign you need a table for that key), and - as basically always in the relational world - do joins between them.

Besides all the problems normalization solves, this has performance benefits, too.  First, you don't store the same key again and again with every transaction, sparing probably a lot of storage space.  Second, in the typical case you can have more efficient indexes over your columns (or groups of them).  Third, you can use primary and foreign keys, and constraints to enforce data integrity (well, this is partly covered in the normalization part...).  Finally, RDBMSes are very good at joining tables and coming up with efficient query plans to retrieve the data you need.

As for keeping a separate table for each year: this can be achieved by paritioning (with recent PostgreSQL versions having important improvements with it).  If you need to do that or not, is hard to tell - it depends on the data volume in the first place, but other factors, too.  You can possibly create your transaction table as partitioned, but only with a single default partition, at least if you opt to use version 11 or later.  In this case, if necessary, you can decide to add yearly partitions later.


Why has deadlock behaviour changed between MariaDB 10.1.22 and 10.2.14? Since upgrading MariaDB 10.1.22 to 10.2.14 our MariaDB slaves are encountering deadlocks that are not handled in less than 600 seconds thus the classic semaphore decision to crash the server.  The server has crashed 3 times. The extremely high volumes have not changed; only the MDB performance has improved with the upgrade.

Note we have Insert on Duplicate Updates that process super high volumes on our master. The deadlocks on same queries occur on the slaves so it has to be related to the slave parallel replication locking.  Reducing slave_parallel_workers has mitigated some of this.

In summary looking to understand what has changed with mdb 10.2.x regarding threads, timeouts, etc. to zoom in on this issue.  Why MDB is unable to determine the deadlock and rollback one of the offending transactions.

I ACKNOWLEDGE all deadlocks should be addressed but as stated above they are not occuring on the master, only on the slave for same statements.

We had the deadlocks prior to the upgrade but MDB always managed same with NO problems.

  2018-06-11 10:32:02 139519224362752 [Note] InnoDB: A semaphore wait:
  --Thread 139518736328448 has waited at read0read.cc line 579 for 910.00 seconds the semaphore: Mutex at 0x7f2b63dc13a0, Mutex TRX_SYS created trx0sys.cc:554, lock var 2

  2018-06-11 10:32:02 139519224362752 [Note] InnoDB: A semaphore wait:
  --Thread 139518749968128 has waited at dict0dict.cc line 1160 for 910.00 seconds the semaphore: Mutex at 0x7f2b63dcb500, Mutex DICT_SYS created dict0dict.cc:1096, lock var 2

  2018-06-11 10:32:02 139519224362752 [Note] InnoDB: A semaphore wait:
  --Thread 139518750574336 has waited at dict0dict.cc line 1160 for 890.00 seconds the semaphore: Mutex at 0x7f2b63dcb500, Mutex DICT_SYS created dict0dict.cc:1096, lock var 2

  InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic
  info: InnoDB: Pending reads 2, writes 0 InnoDB: ###### Diagnostic info
  printed to the standard error stream 2018-06-11 10:32:32
  139519224362752 [ERROR] [FATAL] InnoDB: Semaphore wait has lasted >
  600 seconds. We intentionally crash the server because it appears to
  be hung. 180611 10:32:32 [ERROR] mysqld got signal 6 ;

Answer originally left in comments by the question author

Finally I discovered mdb addressed this bug in 10.2.13 but it remains in 10.2.14.

In summary have gotten around the problem by turning INNODB_ADAPTIVE_HASH_INDEX = ON and have not had a problem since Monday AM. See https://jira.mariadb.org/browse/MDEV-14441 for the supposed 10.2.13 fix.

Evidently MDB was bypassing releasing the latches associated with the semaphores when deadlocks occurred, when INNODB_ADAPTIVE_HASH_INDEX  = OFF

The mdb 10.2.13 fix explains mdb hanging during deadlocks in 10.2.13. The root cause of this bug is that in the function btr_cur_update_in_place() we are skipping this call if the adaptive hash index was disabled during the execution:

if (block->index) {
    btr_search_x_unlock(index);

When debugging the code, I mistook the leaked X lock for a leaked S lock. I did not find any other rw-lock leaks during the MDEV-14952 review/refactoring effort.

The lock leak was introduced by me in MDEV-12121.


Populate main table with random employees (shuffled) from employee table based on department I have a task which I have been working on but am struggling finding the end result.

Task Description
Check each row in Main table

If column a = ‘x’ then assign one of the 4 ‘x’ employees from employee table picked randomly to that row
If column a = ‘y’ then get one of the 4 ‘y’ Standards officers from
employee table picked randomly to that row

Employee Table

Id  Dept Employee
1   x   x1
2   x   x2
3   x   x3
4   x   x4
5   y   y1
6   y   y2
7   y   y3
8   y   y4

Main table (DATA) contains many columns and I need end column (Employee) to be populated by a Employee based on Dept and shuffled in employee table to randomize also.

Thank you

You can use dbatools Invoke-DbaDbDataMasking and have the New-DbaDbMaskingConfig adjusted as per your requirements.

Above has some limitations but it will help achieve what you want to.


Need Help in building database I am developing an app that send messages (email and sms) to users. The db must also maintains the list of messages send to users. And also the status of messages send to user like send or failed.

I have build this scheme that 1 message have many users and 1 reciept is generated to  show the status against each message.

Here the confusion is that how would i maintain the status of a message for every user that message is particular user is send or failed.

To find the latest status of all message (assuming receiptID is an auto-incrementing key) you should use a window function:

SELECT Status
     , messageID
FROM (SELECT Status
           , messageID
           , Row_Number() over (partition by messageID order by receiptID desc) priority
      FROM Receipt) PrioritizedStatus
WHERE PrioritizedStatus.priority = 1


Getting error "String or binary data would be truncated" even for 'text' data type I have created a linked server (PostgreSQL). When I try to import data from
SQL Server to linked server I get the following error:

  String or binary data would be truncated

Even though I am familiar with this error, I have never faced it for the text data type. There is only text datatype in my table.

I would like to know is there are any limitation in linked server?

I have created a test table with a text column. When I try to insert from my linked server I can't insert more than 4000 characters. For testing purpose only I did this but actually I need to import data from SQL to p-sql.

Created following table in p-sql using pg admin

create table test_sql ( col text )

When I try to then inserted values

insert into linkedserver.database.schema.table (col)
values ('4001 char')

but it fails to insert.

I have successfully created linked server with below query:

EXEC master.dbo.sp_addlinkedserver
@server = N'Test_server',
@srvproduct=N'PostgreSQL Unicode(x64)',
@provider=N'MSDASQL',
@provstr=N'Driver=PostgreSQL Unicode(x64);uid=postgres;Server=######;database=test_db;pwd=password;SSLmode=disable;PORT=5432'

You are trying to put data into a column that is not wide enough for it, as the message suggets.

IIRC Postgre's text columns are open sized much like SQL Server's [N]VARCHAR(MAX) so I suspect there are some long values in there and the target column(s) in your table in SQL Server is a more limited character type (VARCHAR(50) or some such.

That is only a guess though. We can't be any more speicif without much more detail from you. What are the table definitions in Postgres and SQL Server? What tool(s) are you using for to migrate the data (SSIS, plain SQL, something else?)?


Is using CRUD stored procedures against a view with NOLOCK bad? (I am crossposting this from SO on the suggestion of a commenter)

Our DBAs have created a pattern where our database layer is exposed to EF via views and CRUD stored procedures. The CRUD works against the view. All the views have the NOLOCK hint. From what I understand, NOLOCK is a dirty read, and that makes me nervous. Our databases are not high volume, but it seems like blanket NOLOCK is not very scalable while maintaining data integrity. I get that the decoupling is a good idea, but the problem there is we don't. Our externally exposed objects look just like our views which map 1 to 1 with our tables.

"If we want to change the underlying data model, we can." ... but we don't. I won't touch on what a PITA this all is from a VS/EF tooling viewpoint.

Is NOLOCK used in this situation bad? Since our database looks exactly like our class library, I think it makes sense to just get rid of the whole view/stored procedure layer and hit the DB direct from EF, does it?

Using NOLOCK blindly is idiotic. There is no best practice at all for this. This could be construed as utter arrogance: that someone knows better than MS who chose READ COMMITTED as the default

From SO: "Using NOLOCK Hint in EF4?". As well as me calling the DBA a muppet, a guy from the MS EF team answers too. And SQL - when should you use “with (nolock)”

In addition, NOLOCK is ignored for updates, inserts, deletes.

Using 1:1 views is idiotic too. It adds no value. From programmers.se "What popular “best practices” are not always best, and why?".

Using a view to hide table changes ad interim is OK, but having the extra layer based on dogma is pointless. And I bet you don't have WITH SCHEMABINDING so the view can differ from the table anyway (SO)


Can we use old primary to sync with standby after failover in PostgreSQL? If my standby (postgres) is behind by few seconds before failover. What is the easiest way to sync back my old primary after failover in PostgreSQL.

In Oracle we have the option of Reinstating a Failed Primary Database, if flashback is enabled.

Do we have any such option in postgres if I have all the WAL archives generated after failover in new primary server? Or do we need to go for complete rebuild of standby?

In current version (Pg 9.0-9.3) there is no fast "fail-back" mechanism. You will have to follow official guidelines which state:

  The former standby is now the primary, but the former primary is down and might stay down. To return to normal operation, a standby server must be recreated, either on the former primary system when it comes up, or on a third, possibly new, system.

So your second guess is correct: you need to go for complete rebuild of standby.

This arises from PostgreSQL internals, WAL format and timelines. Every time fail-over is done, it creates a new timeline. You can not safely use WAL files from new server (the one that was promoted) to replay on old server after it's revived.

From version 9.1 on, you have a tool for fast cloning of postgreSQL instances: pg_basebackup. You can also use filesystem snapshots (eg. LVM2 + XFS).

If you have a snapshot of primary postgres cluster from before the crash, and a series of WAL files from that time on, you can revive primary cluster from that snapshot and replay its WAL files. This is covered in the docs.

PS. Thanks for a good question - this is not so clear right out of the docs.


Ensuring no unwanted tables have changed after a risky operation is performed Sometimes I want to make sure that after a risky operation such as database moving into another server or schema changes via migration scripts no data corruption or loss (due to human error and not the DBMS itself) has occurred to tables that is supposed not to change.

Therefore, I want somehow to check that all my data located in tables are safe and sound in a quick and easy way, therefore I thought it would be good idea to hash each one of my table via a cryptographically secure hash function to current database. Afterwards, once the action has performed, I could recalculate the hashes for each table and check the differences via custom scripts.

The question is how I can hash each table and its data in order to store the hash result?

For example if My database has the following tables:

table1
table2
table3

If I keep hashes on each table then coparing each hashes for each table I could have a quick view which tables has changed. Then if a hash is different on a table that I expect not to, I could smell a data corruption

My database layer is PostgreSQL 11.

psql -Atq -c 'SELECT * FROM atable ORDER BY id' | md5sum


Limits of table partitioning (in PostgreSQL) I have the following use case (PostgreSQL, currently v9 but I'm planning to upgrade to the latest version).

I have a data warehouse application with a central table that has a sizeable number of columns, two of these being JSONB columns.
As common for data warehouse applications, I have a number of indices on the central table, roughly one for each query type that is running.
The central table has a case column that contains an integer value and identifies one subset of data.
After the import, the records for a particular case are immutable (but I might need to purge all records for a case and re-import it once in a while).
For each case, I have ~100k to 3M records (a pretty much bimodal distribution with 70% making up ~100k, and 30% making up ~3M records).
I currently have one thousand cases and would expect this to grow by one thousand cases a year.
95% of all queries to go a single case. 5% of all queries go to a dozen or maybe 200 cases.

Now my questions:

Would it make sense to partition by case ID for performance improvements? I'm currently not so much concerned about query performance (Postgres is really awesome) but more about import times.
If I'm partitioning by case, would I hash the case ID integer or would I start a new one-element range for each case?
If I'm getting 10k or 100k cases, would Postgres scale to this number of partitions?
I'm considering using something like Citrus for sharding over multiple physical servers. Is there anything to keep in mind here?

I did quite some online search regarding this topic but did not find much beyond table partitioning for log files.
I'd be more than happy if you could direct me to good resources for research.

100k partitions is an awful lot.  I would not try that without doing some serious testing first.  Include in the testing things like your backup scripts, and pg_upgrade runs (you don't want to design a database schema that cannot be feasibly upgraded to the next major version). Or just don't do it.

You would probably get most of the advantages of partitions without resorting to one partition per case.  You could use hash partitioning (new in v11) or range partitioning, for example, to have a couple hundred partitions with a few hundred cases per partition.

  5% of all queries go to a dozen or maybe 200 cases.

How does that get expressed?  A app-generated in-list of literal case_id? A join to a parent case table with a where clause to identify the dozen to 200?


What is the cause of high index fragmentation in SQL server? We have an application which is using a SQL Server database. As the application is in use for many years, its database is of huge size, resulting in slowness of application. It was noticed that the data has high fragmentation. So we defragment and build indexes twice a week. After the defragmentation, the following is the fragmentation report of our db.

Results still shows high fragmentation, even the application is only used for 2 to 3 days after defragmentation. Can any expert tell if the results are normal and satisfactory? Can some one suggest some reason of this results?

Things to consider are :

Fill factor
Sort order on the index/table. If table cluster index is not based on identity. When new record is being inserted, the new record could enter in the middle of table. It will screw up the cluster index
Update and delete operations
The last time you rebuilt the index


What is the difference between different utf8mb4 binary collations? What is the difference between utf8mb4_0900_bin vs utf8mb4_bin binary collations?

There are three differences as far as I can tell (according to their documentation):

Case-mappings (for LOWER() / UPPER() functions):
https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html#charset-unicode-sets-uca

The LOWER() and UPPER() functions perform case folding according to the collation of their argument.

The difference between the two collations in this context is that the _0900_ version, being based on a newer version of Unicode, quite likely has more mapping definitions (and possibly even some corrections).

Padding vs No Padding (of trailing spaces):
https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html#charset-unicode-sets-pad-attributes

The pad attribute for utf8mb4_bin is PAD SPACE, whereas for utf8mb4_0900_bin it is NO PAD. Consequently, operations involving utf8mb4_0900_bin do not add trailing spaces, and comparisons involving strings with trailing spaces may differ for the two collations

Essentially, utf8mb4_bin ignores trailing spaces while utf8mb4_0900_bin does not ignore them. See the documentation (linked above) for an example.

Sorting (performance only, not the ordering):
https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html#charset-unicode-sets-collating-weights

For _bin collations except utf8mb4_0900_bin, the weight is based on the code point, possibly with leading zero bytes added.

For utf8mb4_0900_bin, the weight is the utf8mb4 encoding bytes. The sort order is the same as for utf8mb4_bin, but much faster.

Translating that into human, they are saying that for a code point such as U+FF9D, utf8mb4_bin will see the UTF-8 encoded byte sequence of EF BE 9D and convert that into 00 FF 9D. But, utf8mb4_0900_bin will not convert it into the code point value. This is due to the UTF-8 byte sequence already being sequential, hence the ordering is the same as it for the code point values. So why bother with that extra conversion step?


Query notification equivalent for SQL Server 2016 I was looking for query notification to get notified on multiple client for a specific table changes. It seems that this feature has been removed in SQL Server 2008 R2. Documentation is not very clear about that.

When exactly has this been removed (which version exactly)?

Is there any other way to manage that without additional components?

Query Notifications have not been removed or changed.  The Documentation has just not all been ported to docs.microsoft.com.

See, eg:

Working with Query Notifications

Query Notifications - sys.dm_qn_subscriptions

Detecting Changes with SqlDependency


Tighten query caching scope, and periodical recaching Let's say I have a query that I want to be cached, and only that query, any other query is not relevant.

Since my database receives a lot of traffic, sooner or later my caching space would run out, and that cached query would get invalidated. Can I set up some sort of mechanism (in MySQL only, no external tools) that enables caching only on that query and lets say, every night it updates the cached results?

There are two types of "caching" in MySQL

the basic caching of data and index blocks
the "Query cache".

Both have configurable limits.

"Cache" implies that a finite amount of space is reserved.  When you "run out" of the space, something is bumped out of cache to make room for the new item.  The one bumped out is usually the "least recently used", which, without other info, is the least likely to be needed again soon.

So, I don't see what the problem is.

On the other hand, maybe you are asking a different question...  With the "Query cache", SELECTs are saved in it.  This cache keeps the queries and their result sets.  (Again, when it fills up, old entries are bumped out to make room for new ones.)  A major drawback of the QC is that when any write occurs, all entries in the QC for that table are purged.  So, for tables that are frequently written to, the QC is virtually useless.

For the few systems that can effectively use the QC, here are my recommendations:

query_cache_type = DEMAND
query_cache_size = 50M  -- no more than this, else purging is too costly
SELECT SQL_CACHE ...    -- on queries that can benefit from the QC
SELECT SQL_NO_CACHE ... -- for the rest


Identity management in Transaction replication with updatable subscription I have migrated my publisher DB to new server yesterday. For migration what I did was

removed subscriptions and publication completely
backed up DB on which publication was configured
restored DB to new publisher server
Ran sp_removedbreplication on newly restored DB to give a clean start
recreated publication and subscribers on my respective servers

I was able to setup replication properly and it came in sync normally. But when user tried to insert new records at subscriber I got primary key violation errors for lot of tables. Primary key is on identity columns for most of my tables and have not for replication true on both publisher and subscriber end. Upon checking the identity check constraint on subscriber I saw that it had old ranges than my current max values. It wasn't getting the new ranges even though it had automatic identity range management set. I had to manually RESEED the identity on subscriber to make it work and had to go through manual process of reseeding it for all tables. My environment is :

Replication type: Transaction replication with updatable subscription with immediate updating

1 publisher -- SQL server 2008 R2 + SP2
1 distributor -- SQL server 2008 R2 + SP2
2 subscribers -- SQL server 2008 R2 + SP2

I have below questions:

What could be the reason for automatic identity management not working?
How to fix this problem permanently? Is there a script available which can check my max values on publisher and subscribers and then RESEED it accordingly?
What is the fastest way to fix this?

My solution for your problem is skip the primary key error in the replication.

Through the SQL Monitor create a Agent Profile with the code 2627 and let the primary keys error pass. After that the replication should be normal again.

When you create and mark the agent profile you will need stop and run the subscriber job.


Oracle 12c - Create materialized view results in ORA-00942 I have a view that I can access through a database link as such:

SQL> select count(*) from REMOTE_SCHEMA.REMOTE_VIEW@REMOTE_DB;

COUNT(*)
--------
110

I can create and successfully query the remote view through a synonym:

SQL> create synonym REMOTE_VIEW for REMOTE_SCHEMA.REMOTE_VIEW@REMOTE_DB;

Synonym created

SQL> select count(*) from REMOTE_VIEW;

COUNT(*)
--------
110

If I try to create a materialized view using the synonym however, it fails:

SQL> create materialized view REMOTE_MV
build immediate
refresh complete
next sysdate+1
with ROWID
as
select * from REMOTE_VIEW;

ORA-00942: table or view does not exist

Creating the materialized view without the synonym succeeds:

SQL> create materialized view REMOTE_MV
build immediate
refresh complete
next sysdate+1
with ROWID
as
select * from REMOTE_SCHEMA.REMOTE_VIEW@REMOTE_DB;

Materialized view created

Any ideas why using the synonym fails? Ideally I would like to use the synonym to insulate the materialized view from the database link. The database link could differ based on environment and I want to ensure the materialized view query remains the same regardless.

Use the CREATE MATERIALIZED VIEW statement to create a materialized view. A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views.
  More

You cannot create Materialized View from Synonym. I don't know why would you like to that but we create materialized view as your working example.


uniqueness in array column type throughout all records There is a table

id serial primary key,
names varchar(20)[]

it contains such record

1, {"one", "two", "three"}

Then, next record is inserting (notice one array element already exists in existing record)

2, {"four", "one"}

or existing record is updated by adding one more element to the array, but such string element is already used in some other record.

Is it possible to create a unique constraint which wouldn't allow repeating elements inside arrays globaly throughout all rows and not just inside the array value itself?

An exclusion constraint would seem like the weapon of choice:

ALTER TABLE tbl ADD CONSTRAINT tbl_names_overlap EXCLUDE USING gist (names WITH &&);

Alas, as of Postgres 11, still no GiST indexes for arrays. And GIN indexes are not (yet) allowed to implement exclusion constraints. There is a TODO item "Allow GIN indexes to be used for exclusion constraints", but I wouldn't know of any recent progress.

There would be a way with integer numbers after installing the additional module intarray - which provides GiST operator classes. So if your "numbers" are actual numbers ...

Else, here is a workaround I came up with some years ago:

Can PostgreSQL have a uniqueness constraint on array elements?


Why none of the sql_mode(s) are recognized by MySQL? When trying to launch MySQL from the terminal using mysql -u <username> -p<password> I get the following error:

  mysql: [ERROR] unknown variable 'sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

Out of the possible three places that MySQL is looking for the .cnf file (found them by running sudo /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"), only /etc/mysql/my.cnf exist. In it there is only a directory including !includedir /etc/mysql/conf.d/. There, in /etc/mysql/conf.d/mysql.cnf there are the following lines:

 [mysql]
 sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Looked around for answers, couldn't find any. Tried, one by one, to rule out specific "bad mode" from the list of modes, they're all unrecognized.

Only by completely commenting out this line, I was able to login and launch MySQL.

Does anyone knows what the problem might be?

Platform and OS aspects:

The MySQL version I am using is 5.7.17
The operating system is Lubuntu version 16.04, 4.7.3-generic, x86_64

Your problem is very simple

You have the following

[mysql]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

sql_mode is not an option of the mysql client. That's why the error message is unknown variable.

Note how sql_mode and sql-mode do not appear in the mysql client options:

$ mysql --help | grep mode
                      Automatically switch to vertical output mode if the
                      work in batch mode. Disable with --disable-pager. This
  --ssl-mode=name     SSL connection mode.
  --ssl               Deprecated. Use --ssl-mode instead.
                      Deprecated. Use --ssl-mode=VERIFY_IDENTITY instead.
                      also. Does not work in batch mode. Disable with
  --binary-mode       By default, ASCII '\0' is disallowed and '\r\n' is
                      \C and DELIMITER, in non-interactive mode (for input
                      expired password sandbox mode.
binary-mode                       FALSE

SOLUTION

sql_mode is an option for mysqld.

Simply change the group header to [mysqld]

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

You don't have to restart mysql. Just login and run

mysql> SET GLOBAL sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

GIVE IT A TRY !!!


Cast as int in CASE expression in SQL I have a query that is pulling data from 10 tables and a sub-query. One of the selects is for a location and I am using a case expression to clean up the data on output. The field it is pulling the data from is varchar, and the data can be values such as: USA800, admin, ccc-ulw, ccc-ury, 002-Carson, 066-Nellis BX, 042-Junction City (there are more).

I want the case express to report back a specific value if it one of the locations that don't start with a number, but if it is a number, I want it to return just the number, as an int (dropping the 0's).

Here is the query as I have it now (the entire SQL may not be needed, but I figured it would help context):

    select
    ct.id as CaseID
    ,c.first_name as FirstName
    ,c.last_name as LastName
    ,c.id as CustomerID
    ,SUBSTRING(sdu.User_ID,CHARINDEX('-',sdu.User_ID)+1,(((LEN(sdu.User_ID))-CHARINDEX('-', REVERSE(sdu.User_ID)))-CHARINDEX('-',sdu.User_ID))) as [Username from Navigator]
    ,case sdu.Application_Name when 'Navigator' then sn.[RESP CODE] else '' end as [Responsibility Code from Navigator]
    ,case sdu.Application_Name when 'Navigator' then RIGHT(sn.[EMUL STATION], 4) else '' end as [CStat From Navigator]
    ,ce02.id as JobCode
    ,case l.id
        when 'USA800' then 'USA800'
        when 'admin' then 'Admin'
        when 'ccc-ulw' then 'CCC-Lawrence'
        when 'ccc-ury' then 'CCC-Raytown'
        when 'ccc-usj' then 'CCC-St Joseph'
        when 'ccc-uwf' then 'CCC-Wichita Falls'
        else cast(SUBSTRING(l.id, 0, charindex('-', l.id, 0)) as int)
    end as location
    ,l.id
    ,c2.first_name as SubmittedByFirstName
    ,c2.last_name as SubmittedByLastName
    ,sn.INST as Institution
    ,case sdupc.Application_Name when 'PartnerCare' then sdupc.User_ID else '' end as [Partnercare Username]
from Case_Table ct
    left join Case_Type t on t.case_type_pk = ct.case_type_pk
    left join Case_Category cc on cc.case_category_pk = ct.case_category_pk
    left join (select max(cas.case_pk) as case_pk, customer_pk from Case_Table cas join Case_Type cat on cas.case_type_pk=cat.case_type_pk where cat.id = 'ASR' group by cas.customer_pk) ce01 on ce01.case_pk = ct.case_pk
    left join Custom_Entity02 ce02 on ce02.custom_entity02_pk = ct.Virtual_JobCode
    left join Customer c on ce01.customer_pk = c.customer_pk
    left join Location l on l.location_pk = c.location_pk
    right join Customer c2 on c2.customer_pk = ct.installed_by_customer_pk
    left join Prod.dbo.Staging_DataFeed_Users sdu on sdu.User_Name = c.description and sdu.application_name = 'Navigator'
    left join Prod.dbo.Staging_DataFeed_Users sdupc on sdupc.User_Name = c.description and sdupc.application_name = 'PartnerCare'
    left join Prod.dbo.Staging_Navigator sn on sn.NAME = c.description and sn.NAME = sdu.User_Name
where t.id='ASR'

When running the query, I get a conversion failed error when converting the varchar value to data type int.

Either I am missing something, or there is a better way to do it and I am open to ideas.

The customer is expecting integer in this case (they want it to look like an integer without the leading zeros).

The results of this query are used in a foxtrot script for de-provisioning users from applications and those numbers represent branches in our bank system.  The leading zeroes cause issues.  If the result is a string, it is ignored.

Why are you casting the substring as an int in the first place? The consumer is going to expect that column to output as a string in every other case. So my suggestion: Just remove the cast.

If you only want a value to appear in the resultset when it is a valid int, and you're on < 2012 where TRY_CAST() is not possible, then you can say:

else case when isnumeric(SUBSTRING(l.id, 0, charindex('-', l.id, 0))) = 1
  THEN CAST SUBSTRING(l.id, 0, charindex('-', l.id, 0)) AS int END

Note: ISNUMERIC() is not a perfect validation that it is specifically an integer, but is probably fine here, since you don't really need an int, just a string that is formatted like an int.

Update What you can do now that I understand the requirements better is convert to an int and then back to a string. Since you are on 2014 you can use TRY_CONVERT():

ELSE CONVERT(varchar(12),
  TRY_CONVERT(int, SUBSTRING(l.id, 0, charindex('-', l.id, 0))))
END

You might also consider fixing the design so that you don't have to parse these important bits of information out of bigger pieces of information, or at least move your CASE expression to a computed column or a view so you don't have to put all that logic in all (or any!) of your queries.


SQL Server Object Concurrency Background:  SQL Server Version 2014 and the design is an invoice order system.

I have a few questions to confirm my thoughts on SQL Server concurrency.  As a brief background, the developer who created this model, built every order inventory table (items that are part of an order) and order table (the orders) with an InUse field.  What happens is that when a sales person tries to update an order, by changing the name of an item ordered (say a book named Harry Poter needed to be renamed to Harry Potter), if someone else is reading that data, they can't access the object.

The actual database design works like this: the SalesOrder table would have a SalesPersonId with an InUse column set to 1 where no one could access that OrderId.  This means that for every read, update, insert, or delete, there are three transactions happening: first the update to set InUse to 1 so that no one could access the specific OrderId, then the actual transaction, then a final update to set InUse to 0 to allow people to access that OrderId.  That means multiple reads and writes rather than fewer - hello performance nightmare as it scales!

The developer explained that this solves concurrency issues if two users try to write to, or update items in an order.  Huh?

From my knowledge of SQL Server, if two users try to write to the same order - like add two new items, they will both be added because these will be inserts in the log as metadata and then will be added on disk.  In addition, if the inserts are exactly the same, we should design tables with unique constraints to prevent this (to be fair, this developer uses no constraints and no foreign keys at all), so the second insert wouldn't happen.  This means that two users can write inserts to the same table and generally inserts occur quickly.

In addition, if two users are updating the same record - say an OrderItemId of 1 with the name of "Harry Poter" to update to "Harry Potter" (which is an item of a specific OrderId), even two different updates will occur, but the second won't matter because it's the same update.  Only when two separate updates to the same object occurs that are different like update one being "Harry Potter" and update two being "Hary Potter", would there be an issue because these differ.  However, not allowing user access to objects doesn't solve this problem (his design), as user one's update may be the wrong update, which locks out user two (or vice versa).

I don't get this design; to me, it reflects a complete misunderstanding of SQL Server concurrency, along with how SQL databases handle transactions, and most of what he's created is already handled on the back-end by SQL Server (in addition to not solving the problem of wrong updates).  However, maybe I'm missing something and someone else can enlighten me that this is a standard practice with SQL Server (I've never seen this design before).

This is a perfectly acceptable way to implement pessimistic locking without affecting concurrency.

You need to ensure, at the logical level, that whenever one session edits a record (which can translate to multiple rows in multiple tables at the physical level), no other session can modify any components of that logical record -- this is necessary to ensure consistency of your business data. To enable this you need to place some sort of a lock on all affected table rows. This, however, presents a problem in an interactive application, because humans are inherently slower than computers, they tend to look out of the window, make phone calls, go for a smoke or out for lunch before finishing the edit, which potentially leaves records locked for long time.

If you were to rely on physical U or X row locks in a pessimistic locking scenario, you sometimes might prevent other concurrent transactions from reading (or even reading past) the locked rows, thus affecting overall application performance. A safer, from the concurrency point of view, alternative is to logically mark certain rows as being edited by some session (by setting the InUse flag in your example) without placing physical locks on them.

The cost of these extra updates to InUse in an interactive application is negligible, since, as we noted, the human component in it is still much slower.

This, of course, only works if all applications accessing the database know about and honour these logical locks.


Skip running trigger during cascade delete? I have a legacy application where I have two tables X and Y.  Table Y has a trigger that performs an action automatically on delete. There is also a foreign key with cascade delete defined between X and Y (such that entries in Y are deleted when X is deleted).

Is there any way to have the trigger NOT run during a cascade delete?

The only way I currently know that this could be done is to create a package level variable to use as a flag that the trigger would check to determine if it should run.  I would then change the value of the flag in my stored procedure before and after running the delete on table X.

I was hoping to find something a bit cleaner where you could simply tell oracle not to execute the trigger if it is a cascade delete.

Short of disabling the trigger before executing your DELETE statement (which would affect all sessions and would only be appropriate if you are doing an off-hours bulk delete), setting a package variable (or defining your own context and setting a context variable) that your trigger reads is likely the cleanest option without reworking the existing application.

When you have an action that generally needs to be done when a row in Y is deleted but not when Y is deleted because of the cascade delete, that is probably not an action that should be in a trigger in the first place.  It ought to be part of the stored procedure (or other API) that allows you do do "normal" deletes on Y.  Of course, that sort of refactoring may not be possible with a legacy application so you may be stuck with some of the less elegant workarounds.


Naming The SSRS Report Name based on the Parameters Passed I want to save my SSRS Report Based on the Parameters Passed. i.e. Am passing P1 And P2 as parameters so I want my SSRS
Report name as P1_P2_ReportName.Xtension.

Any suggestions?

What you are asking is to be able to create a report name dynamically, which is not something that can be programmed, on download, say, from Report Manager. You can (although I've never tested this) possibly do this through a  subscription using @ParameterName @ReportName. citation from msdn It's about the best you can do for now, and there's a link on the citation that allows for this feature to be voted up.


Justify NOT using (nolock) hint in every query Have you ever had to justify NOT using a query hint?

I am seeing WITH (NOLOCK) in every single query that hits a very busy server.  It is to the point that the developers think it should just be on by default because they hate seeing it in their code thousands of times.

I tried to explain that it is allowing dirty reads and they will end up with bad data eventually, but they believe the performance tradeoff is well worth it.  (Their database is a mess; no wonder they have performance issues.)

If you have a clear example of how to present the case against this abuse of the NOLOCK hint, that would be appreciated.

You pick your battles and battles like this can't be easily won. We have a system where every DML is hinted with the ROWLOCK hint (irrespective of modifying one row or several thousand rows). I showed several examples why it really hurts performance but as the system is already working, there is resistance to change. Note that I convinced them enough NOT to use this going forward though.
NOLOCK has it place but I can recommend some good references showcasing the troubles of using it:

Microsoft SQL Server Development Customer Advisory Team blog – Previously committed rows might be missed if NOLOCK hint is used
Itzik Ben Gan at SQL Magazine – Clustered Index Scans Part III
Itkiz Ben Gan on sqlpass.org – Beware the NOLOCK hint


SQL datatype choice for flagging a read/ unread message? I have a table where I want to track Boolean type operations. in addition, a business requirement is that I know the date of the action and not just the Boolean value.

for example if a message is read or deleted.

I would like to use a date time field so that I can just quantify all unread messages as having null or a date time value for read. I get Boolean operation and a timestamps.

My only qualm about this is how efficient will selecting all unread messages be?

Lets say in a table with a few million records and a non clustered index on a field such as read would a select perform way worse than if the field were a Boolean?

Performance of a particular design depends on the distribution of data to a large extent, and the access path. Are you expecting most messages to have the flag, or most messages not to? From the docs:

  My tests show that a table scan often
  starts to perform better than a
  nonclustered index access when at
  least 10 percent of the rows are
  selected. I also found that the
  optimizer switches from nonclustered
  index access to table scan prematurely
  (i.e., when nonclustered index access
  still shows better response time than
  the corresponding table scan). In many
  cases, the optimizer forces a table
  scan for queries with result sets of
  approximately 5 percent, although the
  table scan becomes more efficient than
  index access at selectivities of 8 to
  10 percent.

And of course, if there are any other predicates on the query, and the clustered index if any. For example, are you likely to want to access all unread messages within a certain timeframe? Or sent to/from a particular user? How big are the message bodies and are they stored inline? These are just rhetorical questions, mind.

So which is better, a BIT and a DATETIME or just a DATETIME for performance? You will have to benchmark with some representative data and access patterns to find out. I'm afraid this one can't be answered with just theory. But you can of course create the table with the boolean in and just not use it if you find the latter is better - it only adds an overhead of 1 byte per row.

If you were asking the opposite question, how to efficiently query the not-NULL rows, I would have said use a filtered index.


Are #tables and #procedures 'not best practice' or 'bad practice'? I am working on a project and part of it is an interface tool that executes SQL code stored in a xml file to import/export files.

In the file, temp procedures and temp tables are used quite heavily (list of imported/exported records, logs, support tables, stored procedures for easier reading etc).

It has been decided by powers above and out of reach (luckily for both them and for me ;) )
that creating temp tables and temp procedures called during execution is 'not so good, maybe even bad' and has to be removed.....

This got me thinking and I would like to know (as I can't ask THEM), did anyone come over some general knowledge stating that 'using temp tables and procedures is not so good and should be avoided'

I know this question is a bit more vague then usual and is a bit subjective, but I would be really thankful for any insight from other, more experienced, wiser heads.

Details (obviously , i cannot be totally specific, so please bear with some obfuscation):

There is a tool that handsles importing exporting files (mostly
CSV) to the DB
the tool handles the common parts ie logging or
moving file based on success/error
there are more interfaces, depends on deployment configuration, lets assume ... on avarage 10-20 executions per day
XML contains phases for imort - init, validate, import/export, deinit
data size varies (so does speed), usually takes less then 10 min to run, some take 1 hour maybe 1,5 hours. CSV have some 100s some have 1000s  some have 10000s rows, less then 100 'columns in CSV'

I doubt that the example you provide is a valid use case for temporary procedures (I don't see any benefit here to using #temp procedures over permanent procedures), but for #temp tables, which have a much wider set of use cases, the only way to fight these arguments with policy-setters is to run the code - using a full load and during typical workload activity - without using the #temp tables. If you can demonstrate to them that even one of these processes works worse without the #temp table, then that should be a valid counter. If they still won't listen, then this is not a question for this site - maybe workplace.


similar word concatination (merge) I have many records where 2 columns contain words which shall be uniquely concatenated. all words shall be distinct in the result.
The order of both text arrays shall be kept.

Col1: mr. spock eats 13 fish
Col2: he eats 13 fish

The idea is to concatenate only missing words.

result: mr. spock he eats 13 fish

I've tried to create arrays of both text columns with PostgreSQL's function
regexp_split_to_array
This is, what solved my problem:
CREATE OR REPLACE FUNCTION merge_strings(str1 text[], str2 text[])
  RETURNS text[]
  LANGUAGE plpgsql
AS
$body$
DECLARE
    res TEXT[];
    t1 TEXT;
    t2 TEXT;
    pos2 int := 1;
    pos3 int;
begin
    FOREACH t1 in array str1 LOOP
        pos3 := array_position(str2, t1, pos2);
        while (pos2 <= pos3) LOOP
        --iterate over words in second text-array until first found word from first text-array
            t2 := str2[pos2];
            if (t2 != t1) then
                --only use words that are not equal
                res := array_append(res, t2);
            end if;
            pos2 := pos2 + 1;
        END LOOP;
        res := array_append(res, t1);
    END LOOP;
    FOREACH t2 in array str2 LOOP
    --add all missing words from second text-array
        pos3 := array_position(res, t2);
        if (pos3 is null) then
            res := array_append(res, t2);
        end if;
    end loop;
    return res;
END;
$body$

From your question it's unclear whether you only want to search for common text only at the end of strings, or anywhere in the strings. Nonetheless, at the core this seems to build on the Longest Common Substring Problem (wikipedia) in computer science.

I don't know of a readily-available function in PostgreSQL to find longest common substrings; so I suspect that the best solution will be to implement what you need in a procedural language similar to https://stackoverflow.com/questions/30315934/sql-find-longest-common-string-between-rows.

Doing it purely in (set-based) SQL will be rather complicated - ending up something like https://blogs.oracle.com/sql/finding-the-longest-common-starting-substring-using-sql. That particular solution only searches on substrings at the beginning of the field but could probably be modified to do what you want. The idea of using ts_vector to solve this more efficiently in SQL is interesting too - I hope you report back if you find anything useful!


Best way to add a new column to a large table? (MySQL - MyISAM) Here's more info on the table that needs to be altered:

15GB size MyISAM table with Index around 5GB
Over 100 million rows
MySQL 5.1 on RHEL 5 with 4GB RAM
Currently live online

What's the best way to add a couple of new column (minimum 2 columns) to a large table like this? With minimum downtime. Just using direct ALTER table commands would take hours if not a day or two to finish.

Additional question: Will it affect the length of time needed to add new columns if the new column default value are set to NULL instead of having a 'pre-defined' value?

To make online changes to a table you can use pt-online-schema-change

    pt-online-schema-change --alter "ADD COLUMN c1 INT" D=sakila,t=actor

The table will be blocked for a few seconds when it switches new and old tables. The table though must have a primary keys and not have triggers.