Licensing SQL Server 2012 Standard Edition for development and test severs Is there an economic way to license SQL Server standard edition on a test server?

Given the following environment:

live: SQL Server 2012 Standard Edition
test: SQL Server 2012 Developer edition
dev: SQL Server 2012 Developer edition

The problem with this scenario is that a developer could code using a feature that works in developer edition, but is not available when deployed to the live server.  The client would be the first to discover the problem!

Thoughts:

Host test database on live server?

Doesn't give the live/test isolation I would like
Uses live server resource (buffers, CPU, etc.)
(Could disconnect test database when not in use to minimise this)

Run a second database instance on the live server

Still poor live/test isolation
More processes using server RAM/CPU

There is certainly an economical way to do this - get an MSDN subscription. The licenses that you get allow you to use the software specifically in development and test environments. It looks like currently $1,199 would cover your needs for SQL Server (and assuming you can make use of some of the other software too), but you could always invest more to get additional software:

https://msdn.microsoft.com/en-US/subscriptions/buy/buy.aspx

Also, we've been asking for the ability to tell Developer Edition what our target edition is for ages. Please vote and comment on these items, explaining your use case and what you have to do to get around the problem:

http://connect.microsoft.com/SQLServer/feedback/details/496380/enable-sql-developer-edition-to-target-specific-sql-version

http://connect.microsoft.com/SQLServer/feedback/details/664953/target-platforms-by-allowing-disabling-features-in-developer-edition

http://connect.microsoft.com/SQLServer/feedback/details/507277/allow-developer-edition-to-emulate-feature-set-of-different-editions

http://connect.microsoft.com/SQLServer/feedback/details/420180/implement-a-configuration-option-that-allows-a-sql-server-developer-edition-instance-to-behave-as-a-standard-edition-instance

http://connect.microsoft.com/SQLServer/feedback/details/331297/developer-edition-in-standard-workgroup-mode

http://connect.microsoft.com/SQLServer/feedback/details/125239/edition-compatability-level-in-developer-edition

The more comments, the better!

This feature should also make it into SSDT at some point. But they're only going to do it if there is enough customer noise about it. :-)


Do you think is necessary to create an auxiliary translate table for table that has only two elements (id and name) I have a question.

In my database I have an Allergen table that has two elements: Id and Name.

I want that my Allergen table has translations, so what is better ? Create an auxiliary translate table that name could be AllergenTranslate or create languangeId directly in the table Allergen and change its name for AllergenTranslate.

Example:

CREATE TABLE [Allergen] (
  [Id] int PRIMARY KEY IDENTITY(1, 1)
)

CREATE TABLE [AllergenTranslation] (
  [Id] int PRIMARY KEY IDENTITY(1, 1)
  [AllergenId] int,
  [LanguageId] int,
  [Name] nvarchar(255),
)

OR DIRECTLY AllergenTranslation table

CREATE TABLE [AllergenTranslation] (
  [Id] int PRIMARY KEY IDENTITY(1, 1)
  [LanguageId] int,
  [Name] nvarchar(255),
)

OR DIRECTLY Allergen Table without Translation table name

CREATE TABLE [Allergen] (
  [Id] int PRIMARY KEY IDENTITY(1, 1)
  [LanguageId] int,
  [Name] nvarchar(255),
)

Definitely the first one with both Allergen and AllergenTranslation tables.

The reason is that Allergen is likely to be referenced in other places. Say you have a table of medicines each of which is effective against a subset of allergens. What would be the foreign key in this Efficacy table? You don't want that FK to have LanguageId in it at all - the medicine's response is not conditional on the patient's speech. Nor do you want to repeat rows in Efficacy for each language as this is redundancy leading to duplication and errors. Think of the changes required to introduce another language.


Does Oracle Streams replicate unused columns? Since you can now mark columns as unused with

alter table <table> set unused column <column>

are columns, set as unused, replicated by Streams? Or is it configurable by rules or restrictions?

It would be an efficient way to delete unused columns from the dictionary by just using streams and switching the database at some point to get free from all the unused columns data.

That seems a large setup effort just to reclaim space from an unused column.  If you really want to recover that space (and presumably not take an outage to achieve it) you could look at DBMS_REDEFINITION

SQL> create table t ( x int primary key , y int, fatcol char(1000) );

Table created.

SQL> insert into t
  2  select rownum, rownum, rownum
  3  from dual
  4  connect by level <= 10000;

10000 rows created.

SQL>
SQL> select bytes from user_segments where segment_name = 'T';

     BYTES
----------
  12582912

1 row selected.

SQL>
SQL> alter table t set unused column fatcol;

Table altered.

SQL>
SQL> CREATE TABLE small_t as
  2  SELECT *
  3  FROM   t
  4  where 1=0;

Table created.

SQL>
SQL> EXEC DBMS_REDEFINITION.start_redef_table(user, 'T', 'SMALL_T');

PL/SQL procedure successfully completed.

SQL>
SQL> ALTER TABLE SMALL_T ADD (CONSTRAINT SMALL_T_PK PRIMARY KEY (x));

Table altered.

SQL> EXEC DBMS_REDEFINITION.finish_redef_table(user, 'T', 'SMALL_T');

PL/SQL procedure successfully completed.

SQL>
SQL> select bytes from user_segments where segment_name = 'T';

     BYTES
----------
    196608

1 row selected.


Monitor MySQL activity per database? What is the best way to monitor activity on each database of my server ?

I tried mtop/mytop which display activity in real time but aren't very practical to see all the queries executed on it.

I activated MySQL logs but there's no option to split them per database.

What tools are you using to monitor MySQL servers ?

I try to stay away from having multiple databases in one instance. Instead I have multiple instances set up on a server where each one is dedicated to a database.

MySQL Cacti templates are a good monitoring solution. If you are enterprise customer then MySQL Enterprise Monitor works. Also check out mycheckpoint from openark.

EDIT
Here are some tutorials for setting up multiple instances on one server:

Running multiple instances of MySQL on the same machine
How-to: Setup Multiple MySQL Instances

You could also use MySQL Sandbox as it automates a lot of different set ups for you.


Should I disable MongoDB balancer to prevent moveChunk to block database due to "W" lock In my team, we have a MongoDB 3.6 with 4 shard cluster.
My teammate built up Ops Manager and we found out at some point the queries are slow and they are all waiting for database level lock.
    "Database": {
      "acquireCount": {
        "w": 1
      },
      "acquireWaitCount": {
        "w": 1
      },
      "timeAcquiringMicros": {
        "w": 5383162
      }
    },

Dig inside and we found out the MongoDB was moving chunk and command require "W" exclusive lock at database level.
We feel quite confusing because

We use hashed _id for shard key, which should be distributed evenly and every document size are quite the same.  Is there any way to find out why MongoDB trigger moveChunk? Or any statistics for me to dig inside ?
List all the slow query, Ops Manager didn't record the moveChunk command as slow query. How should I change my config or anything I should try ?
Based on my case, should I try to disable balancer to prevent moveChunk and observe by myself ?

[Updated].
Here is the query which is lock on database "W" lock.
{
            "host" : "....",
            "desc" : "migrateThread",
            "active" : true,
            "currentOpTime" : "2020-08-31T06:34:34.194+0000",
            "opid" : "sh1:-472303473",
            "secs_running" : 15,
            "microsecs_running" : 15562026,
            "op" : "none",
            "ns" : "collection",
            "command" : {
            },
            "msg" : "step 2 of 6",
            "numYields" : 0.0,
            "locks" : {
            },
            "waitingForLock" : false,
            "lockStats" : {
                "Global" : {
                    "acquireCount" : {
                        "r" : 4,
                        "w" : 4
                    }
                },
                "Database" : {
                    "acquireCount" : {
                        "w" : 3,
                        "W" : 1
                    },
                    "acquireWaitCount" : {
                        "W" : 1
                    },
                    "timeAcquiringMicros" : {
                        "W" : 15665
                    }
                },
                "Collection" : {
                    "acquireCount" : {
                        "w" : 2,
                        "W" : 1
                    },
                    "acquireWaitCount" : {
                        "W" : 1
                    },
                    "timeAcquiringMicros" : {
                        "W" : 7247
                    }
                }
            }
        }

Thanks

To see your "real" chunk distribution count between shards, use command sh.status() at mongos.
As you know, result should show that every shard have more or less same amount of chunks.
Yes, you can disable the balancer and if you really use hashed sharding key, end result should be same as if you keep balancer running. Check that sh.status() carefully, I think that you have there something else sharded too, something what don't use hashed sharding key.
On case when movechunks happen even you have hashed shardingkey is of course situtation where you have added new shard to the cluster. That new shard is empty and system starts to fill it.


TDE on replicated databases I have implemented Transactional replication between two databases. Now I have to implement Transparent Data Encryption (TDE) on the Publisher database.
Is it possible to implement TDE in the master database only? Will the replicated data be encrypted along with replication?

From MSDN:

  Replication does not automatically replicate data from a TDE-enabled
  database in an encrypted form. You must separately enable TDE if you
  want to protect the distribution and subscriber databases. Snapshot
  replication, as well as the initial distribution of data for
  transactional and merge replication, can store data in unencrypted
  intermediate files; for example, the bcp files.  During transactional
  or merge replication, encryption can be enabled to protect the
  communication channel.

Also, a suggested read here.

Yes, you can use TDE with replication as explained here.

TDE is file level encryption. This meaning, TDE encrypts and decrypts at the disk level where the data and log files are retained. Replication is managed in a combination of the system databases and the user database level and in reality, has no awareness of the file storage. This is the same when we would discuss any type of replication, such as Peer-to-peer, Transactional or Merge replication.


PHP -> Mysql persistent connection pooling WITHOUT mysql_pconnect - Possible? I have been trying to figure out a nice way of doing this for a while now. But have had a hard time finding the right pieces to do this. I am guessing this must be possible.

To put it in simple terms here is what I would like to accomplish:

PHP / Other front end -> [SOCKET] ->

Locally hosted 'pooler' -> [Pool of persistent TCP/IP connection(s)]->

Externally hosted MySQLD

Does such a tool / way of doing things exist?

We would basically like to implement persistent mysql connections WITHOUT using mysql_pconnect.

I respectfully ask that we not start to discuss about how persistent connections are not needed etc. They are. We are running out of TIME_WAIT ports, and are having other issues which would be solved if this type of system was implemented.

So yea, to summarize... We would to implement a mysql connection pooler that is socket based on the local end, and persists the connections that are made to a (LAN) externally hosted mysql server.

We do not use transactions or anything else that would be affected from the mysql connections being recycled.

We are running linux on the front end with a master + master percona 5.5 cluster.

Thanks!

After much searching, I have finally found a solution.

I am not much of a writer, so I will do my best to make this as concise as possible.

So as far as I could find, there are 2 possible solutions:

SQL Relay

http://sqlrelay.sourceforge.net/

This does exactly what the question asked for, and a bunch more. I wont go into too much detail on what I was able to find out about this but will mention that it was not a viable solution as it is not transparent. Meaning that the flow is as follows:

PHP -> Queries -> SQL Relay Extension -> SQL Relay -> Externally hosted MySQL

So this would have involved rewriting all of our code from mysql to sql relay. Not an option in our case.

All that being said, if someone is planning a fresh large scale project that requires any of the numerous features that SQL Relay has, it sounds beautiful.

Mysql Proxy

http://forge.mysql.com/wiki/MySQL_Proxy

This is the solution we ended up using.

The key to making this do what we want it to do is the pooling LUA script for mysql proxy.

This LUA extension can be found at:

https://github.com/cwarden/mysql-proxy/blob/315ab806bb95b8223f5afd3d238eff2a40af03d8/lib/ro-pooling.lua

Without going into too much detail, here are some basic stats... Bare in mind, this is tested at LOW usage time:

[root@HOSTNAME etc]# netstat -na | grep ":3306 " | grep TIME_WAIT | wc
   6433   38598  572537

After switching to mysql-proxy, and letting things settle:

[root@HOSTNAME etc]# netstat -na | grep ":3306 " | grep TIME_WAIT | wc
     32     192    2848

As you can clearly see, the TIME_WAIT ports to mysql have dropped to almost none.

The connections are now in fact persistent WITHOUT using mysql_pconnect / mysqli_connect( ... p:hostname ... ).

Worth mentioning there appear to be a few configurable settings near the top of the pooler lua script.

local min_idle_connections

and

local max_idle_connections

These appear to be pretty self explanatory. Except that: It would appear that each username (and password? untested... most likely not tho.) combination creates its own set of persistent connections.

So multiply max_idle_connections by the number of unique mysql users that will be connecting to the database. And that should give you an idea of how many idle connections you will end up having.

So, let me reiterate for so this little blurb hits some keywords for those searching via google:

When using PHP is it possible to have persistent mysql connections WITHOUT mysql_pconnect?

Yes, this can be done via SQL Relay if you dont mind rebuilding most of your code to pipe your queries thru their extension OR transparently using mysql-proxy with the ro-pooling.lua script.

We have been wanting something like this for about a year now.

ENJOY!


Inherit INNER JOIN fields in MySql SELECT QUERY I want to know how I can join one field of an INNER JOIN table in a SELECT QUERY.

Here's what i got so far:
In this query my result would be NameA, NameB and idC, but i want to join tableA and tableC reference as shown in the image schema.

SELECT table_A.idC, table_A.nameA, table_B.nameB
FROM table_Ref
INNER JOIN table_A ON table_Ref.idA=tableA.idA
INNER JOIN table_B ON table_Ref.idB=tableB.idB;

My wanted result would be nameA,NameB, and NameC using the idC field in table_A to know which nameC belongs to that idC.

I hope you can help me or give me a reference of this topic :)!

you can join on table_C

SELECT nameC, nameA, nameB
FROM table_Ref r
INNER JOIN table_A a ON r.idA=a.idA
INNER JOIN table_B b ON r.idB=b.idB
INNER JOIN table_C c ON a.idC=c.idC;


MySQL- reducing memory footprint Our InnoDB/Mysql5.6 database is growing at a fairly steady pace of doubling in size every year, currently at 22Gb. This is sales data; we have a number of merchants each generating millions of rows, all stored in one logical instance. The report queries are served by a few read replicas for load balancing and redundancy with the queries distributed randomly.

The database is hosted in AWS/RDS. We outgrew the 4Gb instance, then 8gb and currently reside in a 16Gb node. We expect to last until the end of the year. While AWS offers "memory optimized" instances we feel this will just defer the problem but not solve the bigger picture. Instead of scaling up, we would like to use a number of smaller instances and serve merchant specific data. We can partition the data the "hard way" where instance A only holds merchant A data and instance B only holds merchant B data and so on. The biggest drawback of this is that in case of an instance downtime we cannot just route traffic to another instance since there is no cross-account data sharing.

So I want to ask if anyone can comment on "logical partitioning" which means that all servers still hold all the data (disk space is not a problem) but queries related to one account are "sticky" i.e. routed to the same server (or server pool) to maximize cache hits. That way if a DB instance, zone or region goes down any other instance elsewhere can pick up the work of the down-instance since the same data was distributed everywhere.

In your opinion, would logical partitioning based on repeated queries to the same set of instances reduce the need for memory, assuming all the queries use indexes where the account ID is the first field in every composite key?

Good explanation.  Good idea.  I'll add my 2-cents...

"Sharding" would have a few merchants on one server (plus its replicas); a few other merchants on another shard, etc.

You are suggesting that it is practical to have all the data everywhere, but "shard" the accesses to minimize RAM requirements.

First let me make a strong suggestion.  Have all PRIMARY KEYs be composite, starting with the merchant_id.  This will cluster all the rows for a given merchant together.  This is in addition to having most of the indexes start with that same column.

If you currently have

PRIMARY KEY(id),   -- auto_increment
INDEX(merchant_id, ...)   -- a few of these

then change to

PRIMARY KEY(merchant_id, id)  -- for m_id for clustering; id for uniqueness
INDEX(id)   -- to keep auto_increment happy
INDEX(merchant_id, ...)   -- fewer of these

Reconsider the secondary indexes; you may not need as many, since the rows are already clustered by merchant_id.

Granted, inserting not-for-this-shard merchant rows will need to some blocks; but they will be transient and not cost too much room in innodb_buffer_pool_size.

If you are pseudo-sharding the slaves, then if a slave dies, all the other slaves are ready to take over for any Merchant's requests.  The only problem is that queries will be slow due to the lack of cached data.  I see the 'instant' failover as being more of a benefit than the 'slower' is a detriment.  Anyway, the caching will catch up pretty soon.

How much write traffic is there, versus read traffic?

A lot of write:  Then you need to shard the Masters (and Slaves).  But would this mean that you are still doing all the writes to all Masters?  This is clumsy?
Mostly read traffic:  (That's what I was commenting on above.)

Keep in mind that every Slave performs every write that happened on its Master.  Corollary:  You can't get write scaling without Sharding the Masters.

PK example

Using your specifics (from Comment):  You currently have an 8-byte id BIGINT?

PRIMARY KEY(id),   -- auto_increment, 0 extra bytes (clustered with data)
INDEX(account_id, timestamp)   -- INT + TIMESTAMP or DATETIME : 4+5 + 8 bytes

-->

PRIMARY KEY(account_id, timestamp, id),   -- 0 extra bytes
INDEX(id)   -- 8 + 4+5 bytes

That is the disk footprint will be virtually identical.  To elaborate:

The PK is "clustered" with the data; this makes the data ordered according to the PK.  Hence, the PK takes zero extra bytes on top of the data.  (OK, there is a small overhead for the non-leaf nodes of the BTree.)
Any secondary index has a copy of the PK in it.  (This is how it gets to the actual row.)
So, in this pair, the secondary index has exactly the same fields, with exactly the same total space, just rearranged.
Here's the feature I am after:  When looking up rows for one account, perhaps over a timestamp range, the data PK will now be used instead of using the secondary key, then jumping through id.  And all the desired rows will be adjacent to each other.  The result:  Such SELECTs will be faster (maybe 2x, but there are a lot of variables).

Back to the "old wives tale" that you should keep the PK "small".  Yeah, that is a general rule.  But I have just explained an exception.

If you have several more secondary keys on the same table, then the extra secondary keys will be bulkier.  But you want to skip on RAM, and don't need to worry about disk.  Again, what I suggest fits well.


Efficient configuration for a purely temporary, small Oracle installation I have a personal Oracle installation on my development machine (Oracle 12.1 EE, Windows 7, 16 GB RAM (about 7 of which is unused at all times), SSD) which is used exclusively for running automated tests.

This means:

Default Oracle installation using their GUI - all defaults were accepted during installation.
One database user/schema.
The schema is wiped before and after every run of the whole test suite (i.e. database objects are regularly dropped and recreated; the user itself is not dropped). This step does not need to be optimized, its time is insignifikant in relation to the total runtime.
During the test run, between individual scenarios, the tables are either wiped by ROLLBACK or TRUNCATE depending on circumstances.
All objects, including all data rows, are recreated everytime the suite runs. There is no permanent data staying around between suite runs or individual tests.
It would be totally irrelevant if there were any data loss at any time, up to including having to wipe the whole schema or even the DB itself.

So.

Obviously, there is a lot of overhead right now, since Oracle doesn't know that I don't care about my data. So it will have its redo log etc. chugging along like usual.

What is a good configuration to make my use case as quick as possible? I.e., turning off as many security/consistency/history features as possible; avoiding writing buffers to the HDD as far as possible, etc.?

The time for the one-time initial setup and teardown (i.e. CREATE TABLE ... DROP TABLE) is insignificant in relation to the runtime of the test suite.

You can either "drop user test_user cascade;", then create the user again with all needed grants and objects, or drop the database and restore a backup of the database while it is empty. Depending on the number of tables, indexes and constraints one of the two methods will be faster. The more tables, indexes and constraints, the more likely just dropping the database and restoring a backup of the empty database will be faster.


LIKE Operator not returning desired result after using LOWER In Oracle SQL Developer, I have the following two queries :

Sample Data for Reference from Employee Table

COMPANY_ID  NAME                                EMP_ID
1           Steven                                  4
2           Steven steven is working                4
3           That work was done by steven            4
4           Bradley is a good guy                   4
5           His name is yoshio                      4

Query #1:

SELECT COMPANY_ID,NAME,EMP_ID FROM employee WHERE emp_id = 4
AND NAME LIKE '%Steven%'

The above query returns the results like the following which is perfectly fine:

COMPANY_ID |  NAME                      | EMP_ID

1             Steven                        4
2             Steven steven is working      4

Query #2:

The LOWER(NAME) as NAME converted all the upper case letters of the NAME column to lowercase and when I used the following
query, I didn't not receive three rows which contains the word steven but only one as shown below:

SELECT COMPANY_ID,LOWER(NAME) as NAME,EMP_ID FROM employee WHERE emp_id = 4
AND NAME LIKE '%steven%'

Here is the output of the above query in Oracle SQL Developer:

COMPANY_ID |  NAME                      | EMP_ID
2             steven steven is working      4

So, basically the pattern I am noticing after running the Query #2 is that if the searchable word(in our case it is steven) is
surrounded by something on its left and right, that row is returned otherwise no other row with the searchable word is returned.
For example, from the sample data above, rows with company_id 1 and 3 containing the word steven wasn't returned.
Could anyone tell me why it's happening like this?

Selecting a column as LOWER(NAME) doesn't automatically apply to your WHERE clause...  You need to have LOWER in WHERE clause, minimally.

SELECT
  COMPANY_ID, NAME, EMP_ID
FROM employee
WHERE emp_id = 4
  AND LOWER(NAME) LIKE '%steven%'

In actuality, they are two completely separate pieces of instructions to the query planner..  The WHERE clause instructs the planner on which records to consider, and the SELECT clause instructs the planner for how to present the data to you.

The following query is equally valid

SELECT
  COMPANY_ID, UPPER(NAME) as NAME, EMP_ID
FROM employee
WHERE emp_id = 4
  AND LOWER(NAME) LIKE '%steven%'


SQL Server Agent 2012 Job Schedule Report I am looking for a report that will display graphically what jobs are scheduled on the server and when. My goal is to very quickly be able to glance at it and find a time where a new job will not interfere with other jobs.

I have been looking at the "Standard" reports but have not seen anything...I'm pretty sure I could make one...but just wanted to be sure I wasn't missing something if the wheel had already been invented and packaged with SSMS.

If you expand the SQL Server Agent in SSMS and double click the Job Activity Monitor you should see what you need.


“Cannot continue the execution because the session is in the kill state” Error? We use SQL Server 2019 Standard edition on Windows Server 2019. We have 200 users and thousands of connections at the same time.
SQL Server 2019 raises an error

Cannot continue the execution because the session is in the kill state

Once a week I restart the SQL Server service and the error is gone.

I installed CU1, CU3, CU4, CU5 and CU6, but that didn't fix the error.

Database's compatibility level: SQL Server 2019 (150)

Our programs use mostly 4 tables. I read that broken indexes could cause this error. I checked these tables' indexes, but no problem was found. Anyway, I rebuilt all indexes for these 4 tables, but that didn't fix the error.

How could I detect the root cause of this error, and how could I fix it?
Thanks.

i modify a stored procedure and test it, error raised.
in sql server management studio :
Msg 596, Level 21, State 1, Line 93
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 93
A severe error occurred on the current command.  The results, if any, should be discarded.

in exception.log file :
09/21/20 12:23:33 spid 697 Exception 0xc0000005 EXCEPTION_ACCESS_VIOLATION reading address 000001BDA9BA6000 at 0x00007FF8BCD099B3

in SQL Server Log:
2020-09-21 12:23:33.62 spid697     CImageHelper::Init () Version-specific dbghelp.dll is not used
2020-09-21 12:23:33.62 spid697     Using 'dbghelp.dll' version '4.0.5'
2020-09-21 12:23:36.07 spid697     ***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\LOG\SQLDump10000.txt
     SqlDumpExceptionHandler: Process 697 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
     * *******************************************************************************
     *
     * BEGIN STACK DUMP:
     *   09/21/20 12:23:36 spid 697
     *
     *
     *   Exception Address = 00007FF8BCD099B3 Module(sqllang+00000000009099B3)
     *   Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION
     *   Access Violation occurred reading address 000001BDA9BA6000
     * Input Buffer 150 bytes -
     *             dbo.SP_STOCK_INFORMATION '', '175812'
     *  , 7
     *
     *
     *  MODULE                          BASE      END       SIZE
     * sqlservr                       00007FF78DE00000  00007FF78DE9DFFF  0009e000
     * ntdll                          00007FF8D4640000  00007FF8D482CFFF  001ed000
     * KERNEL32                       00007FF8D4170000  00007FF8D4222FFF  000b3000
     * KERNELBASE                     00007FF8D0BE0000  00007FF8D0E73FFF  00294000
     * CRYPT32                        00007FF8D07F0000  00007FF8D09CBFFF  001dc000
...

    Stack Signature for the dump is 0x00000001415746C8
External dump process return code 0x20000001.
External dump process returned no errors.

2020-09-21 12:23:39.73 Server      Error: 17310, Severity: 20, State: 1.
2020-09-21 12:23:39.73 Server      A user request from the session with SPID 697 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.

Thanks.

Solution
The solution to your issue is contained in the error message you included in your question:

2020-09-21 12:23:39.73 Server      A user request from the session with SPID 697
                                   generated a fatal exception. SQL Server is
                                   terminating this session.
                                   Contact Product Support Services with the dump
                                   produced in the log directory.

Make sure you can collect the ***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\LOG\SQLDump10000.txt file and open up a service request with (your) Microsoft (representative/consultant). In some cases you might find a minidump *.dmp file located in the same location.
Include everything in the case and hopefully Microsoft will be able provide you with a solution to your issues.
Recommendation (On a Side Note)
I noticed that the log files are generated in the C:\ drive. Normally this drives hosts the Operating System (OS). It isn't recommended to run SQL Server on the OS drive. If the database or anything related to the database produces a large amount of data, then this could fill up the OS drive, which eventually could crash the system.


Closed queries re-open when connecting to DB using MySQL Workbench Using MySQL Workbench latest version (6.3) I've set up an Amazon EC2 connection successfully. Every time I open the connection, I've noticed all queries I did before are being opened, even if I closed them using right click -> "Close All" option. These queries have dynamic names like {0303FFF4-FB6D-4EF3-A9EC-2...

I don't want these queries to remain stored, as most of the time they're just temporary queries I need for some quick data gathering.

Can somebody please help me with these? What do I need to do?

I thought this was some kind of auto-save feature from Amazon EC2 instance or some kind of bug, but nope my bad.
I found bug report and at the bottom of the page this is the answer:

  Andres Giordano:
  This is not a bug!!! You have to go to Edit --> Preferences --> General --> SQL Editor (panel) and uncheck "Save snapshot of open editor...."

That's the correct answer. Once this option is disabled, no more queries are stored and re open.
I hope this tip is useful for somebody. Greetings!


How to get 0 in the null value fields select *
from
(
    select type,SUM(ISNULL(amount,0)) AS amount,month(date) as date from tbl_cashmaster
    where date  between '2016/12/31' and '2017/12/31'
    group by type,month(date)
) src
pivot
(
  sum(amount)
  for date in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) piv;

How can I get zero in null value columns? The above query is returning null's, where there is no data. What changes do I need to make in this to get 0 instead of null?

If you have a fixed number of pivot points, you could do what McNets suggests in the comments:

Change your select * by select isnull([1],0), isnull([2],0), ..

For a variable number of pivot points, you can use dynamic SQL - here is an example:

Note the references to colCoalesceNull

set nocount on
DECLARE @sql AS NVARCHAR(2000);
DECLARE @col AS NVARCHAR(2000);
DECLARE @colCoalesceNull AS NVARCHAR(2000);

IF OBJECT_ID('tempdb..#PaymentHistory') IS NOT NULL drop Table #PaymentHistory
CREATE TABLE #PaymentHistory (
    EmployeeName VARCHAR(100)
    ,DatePaid DATE
    ,TotalCheckAmt DECIMAL(11, 2)
    );
insert into #Paymenthistory values ('James','2016-01-01',100.00);
insert into #Paymenthistory values ('James','2016-02-01',100.00);
insert into #Paymenthistory values ('James','2016-03-01',100.00);
insert into #Paymenthistory values ('James','2016-04-01',100.00);
insert into #Paymenthistory values ('James','2016-05-01',100.00);
insert into #Paymenthistory values ('James','2016-06-01',100.00);
insert into #Paymenthistory values ('James','2016-07-01',100.00);
insert into #Paymenthistory values ('James','2017-01-01',100.00);
insert into #Paymenthistory values ('James','2018-10-01',900.00);

insert into #Paymenthistory values ('Roger','2016-01-01',100.00);
insert into #Paymenthistory values ('Roger','2016-02-01',100.00);
insert into #Paymenthistory values ('Roger','2016-03-01',100.00);
insert into #Paymenthistory values ('Roger','2016-04-01',100.00);
insert into #Paymenthistory values ('Roger','2016-05-01',100.00);
insert into #Paymenthistory values ('Roger','2016-06-01',100.00);
insert into #Paymenthistory values ('Roger','2016-07-01',100.00);
insert into #Paymenthistory values ('Roger','2020-10-01',900.00);
;
;
SELECT @col =
        Coalesce(@col + ', ', '') + QUOTENAME(PvtColumnName)
    ,@colCoalesceNull =
        Coalesce(@colCoalesceNull + ', ', '') + 'coalesce(' + QUOTENAME(PvtColumnName) + ',0) as ' + QUOTENAME(PvtColumnName)
FROM (
    SELECT DISTINCT
    'Q' +
    CONVERT(VARCHAR(1), DATEPART(Quarter, [DatePaid])) +
    CONVERT(VARCHAR(4), year([DatePaid]))
        AS PvtColumnName
    FROM #PaymentHistory
    ) AS PaymentHistory;

PRINT @col
PRINT @colcoalescenull

SET @sql = N'
with cte_PaymentHistory as
(
select
''Q'' + CONVERT(varchar(1),DATEPART(Quarter, [DatePaid])) + CONVERT(varchar(4),year([DatePaid])) as PvtColumnName
,EmployeeName
,TotalCheckAmt
from #PaymentHistory
)
SELECT EmployeeName, ' + @colCoalesceNull + 'FROM cte_PaymentHistory
    PIVOT(SUM(TotalCheckAmt)
    FOR PvtColumnName IN (' + @col + ')) AS PivotPaymentHistory';

EXEC sp_executesql @sql;


What is the different between GDR security update and CU security update What is the different between GDR security update and CU security update?

I am looking to understand difference between the below two updates. If I apply KB3194724, would it also include KB 3194721?

Build 11.00.6567 (3194724 MS16-136: Description of the security update for SQL Server 2012 Service Pack 3 CU: November 8, 2016)

AND

Build 11.0.6248.0 (3194721 MS16-136: Description of the security update for SQL Server 2012 Service Pack 3 GDR: November 8, 2016) for SQL Server 2012?

Which one should I apply if SQL Server 2012 is on SP3?

You should install the latest CU for SQL 2012 SP3.

As of today (10 May 2017) it is CU8

The SQL version number will give you a good indication of what is more recent.

An explanation of what GDR means can be found here.


What's the best way to go about searching for short names? I have a table that has CHAR in Korean for Soccer player names. All of these are under 12 characters, and while LIKE "%name%" works, Slight mistypes or variations don't work, and I've heard that LIKE isn't the fastest solution compared to something like Fulltext Indexes. I've also heard that the NGRAM parser works best with Korean/Japanese/Chinese characters, but it requires that I put in a title/body column; I only want to search for names, nothing else.

I currently have ~30k rows, and it won't grow more than ~45k. What's the best way to go about this?

Here's what I tried (after making an word index table in ngram):

...MATCH (name) AGAINST ('foo' IN NATURAL LANGUAGE MODE);

but it's not as precise as using LIKE, since it returns any word that's remotely connected to the query word.

A trick that sometimes is useful...

MATCH is usually the fastest way to search
MATCH has some drawbacks
LIKE and RLIKE are very slow
When using both MATCH and LIKE, MATCH is done first, thereby providing very few rows to recheck with LIKE:

WHERE MATCH(...) AGAINST(...)
  AND ... LIKE '%...%'

(I am not familiar with how NGRAM works, so I can't provide specifics there.)

From the 5.7.23 and 8.0.12 Changelogs:  "The ngram full-text search parser permitted comma and period characters to be tokenized as words, which caused an inconsistency between boolean and natural language mode search results. Comma and period characters are no longer tokenized."  (This implies that 5.7.23 has a fix for it.)


SQL Query in suspended state causing high CPU usage Environment detail: SQL Server 2008 R2. I have applications running on JBoss that access the DB using JDBC driver.

Issue: My online DB server was experiencing high CPU usage.

Using the following query I was able to determine there are 2 queries (Select command) in suspended status (wait type - CXPACKET) for nearly 10 minutes and going on. I believe this is the cause of high CPU usage.

 USE MASTER GO

 SELECT scheduler_id ,
        runnable_tasks_count ,
        pending_disk_io_count
 FROM   sys.dm_os_schedulers
 WHERE  scheduler_id < 255

 SELECT qs.percent_complete ,
        qs.session_id ,
        scheduler_id ,
        blocking_session_id ,
        qs.status ,
        command ,
        wait_time ,
        wait_type ,
        last_wait_type ,
        wait_resource ,
        ST.text ,
        host_name ,
        program_name /*
 ,SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
     (    (CASE statement_end_offset
           WHEN -1 THEN DATALENGTH(st.text)
           ELSE QS.statement_end_offset END - QS.statement_start_offset  )/2 ) + 1) AS statement_text   */   --, qp.*
 FROM   sys.dm_exec_requests qs
        LEFT JOIN sys.dm_exec_sessions es ON ( qs.session_id = es.session_id )
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS ST
 --     CROSS APPLY sys.dm_exec_query_plan(QS.sql_handle) as qp where qs.session_id = 50 and qs.session_id != @@SPID

 SELECT CASE WHEN Requests.sql_handle IS NULL THEN ' '
             ELSE SUBSTRING(Statements.text,
                            ( Requests.statement_start_offset + 2 ) / 2,
                            ( CASE WHEN Requests.statement_end_offset = -1
                                   THEN LEN(CONVERT(NVARCHAR(MAX), Statements.text))
                                        * 2
                                   ELSE Requests.statement_end_offset
                              END - Requests.statement_start_offset ) / 2)
        END AS StatementText ,
        QueryPlans.query_plan AS QueryPlan ,
        Statements.text AS Batch_Text ,
        Sessions.session_id ,
        Sessions.Login_Name ,
        Sessions.Host_Name ,
        Sessions.Program_Name ,
        Sessions.Client_Interface_Name ,
        Requests.wait_time ,
        Requests.cpu_time ,
        Requests.total_elapsed_time ,
        Requests.reads ,
        Requests.writes ,
        Requests.logical_reads ,
        Requests.row_count ,
        Requests.granted_query_memory * 8 / 1024 AS Granted_Query_Memory_MB ,
        LEN(Statements.text) AS Batch_Text_Length ,
        Requests.statement_start_offset / 2 AS Statement_Start_Offset ,
        CASE WHEN Requests.statement_end_offset = -1
             THEN LEN(CONVERT(NVARCHAR(MAX), Statements.text)) * 2
             ELSE Requests.statement_end_offset
        END / 2 AS Statement_End_Position ,
        ( CASE WHEN Requests.statement_end_offset = -1
               THEN LEN(CONVERT(NVARCHAR(MAX), Statements.text)) * 2
               ELSE Requests.statement_end_offset
          END - Requests.statement_start_offset ) / 2 AS Statement_Text_Length
 FROM   sys.dm_exec_session Sessions
        INNER JOIN sys.dm_exec_requests Requests ON Sessions.session_id = Requests.session_id
        CROSS APPLY sys.dm_exec_sql_text(sql_handle) Statements
        CROSS APPLY sys.dm_exec_query_plan(plan_handle) QueryPlans
 WHERE  Sessions.session_id != @@SPID

 SELECT *
 FROM   sys.dm_exec_requests
 WHERE  blocking_session_id != 0

Any suggestions on how I can kill these queries in suspended state?

Although you've accepted an answer that informs you how to kill the session, this is a kind of problem that could repeat itself and so you should probably be aware that the CXPACKET wait type in itself is not a problem, but tends to indicate an issue with the execution of a parallel query.

Brent Ozar has posted a good explanation (with a video) of what a CXPACKET wait is and how you go about reducing them. As an example, if your parallel query runs across 5 CPUs and 4 of them have completed their allocated workload, the waiting on the 5th CPU to complete is what is identified as the CXPACKET wait.

You can start to alleviate CXPACKET waits by looking into setting the Maximum Degree of Parallelism (MAXDOP) on the individual query and/or the Cost Threshold for Parallelism on the instance, both of which are explained in the article.


SQL Server Express Limitation I would like to use Express instead of Standard. Does Express still have the annoying habit of going to sleep if left unused for a period of time?

No, it does't, and never did.

The post you link links to the blog http://blogs.msdn.com/b/sqlexpress/archive/2008/02/22/sql-express-behaviors-idle-time-resources-usage-auto-close-and-user-instances.aspx that explains that the 'instance going to sleep' is a RANU behavior (user instance), not an Express behavior. As you plan to move from Standard to Express, is obvious that that you do not plan to move to user instances (RANU).


Mongodb - Cluster one host keeps syncing I've setup a mongodb cluster, MongoDB shell version: 2.6.11.
About five days ago I performed a mongorestore on the primary database.
The size of the restore is about 2Gigabytes.

Right now, one database is still syncing. I'm not sure if this normal, or there is an issue somewhere. I restarted mongod once on the mongo-one server.

Output of rs.status()

rsOrder:SECONDARY> rs.status()
{
    "set" : "ReplName",
    "date" : ISODate("2016-11-16T07:52:28Z"),
    "myState" : 2,
    "syncingTo" : "mongo-two:27017",
    "members" : [
        {
            "_id" : 0,
            "name" : "mongo-two:27017",
            "health" : 1,
            "state" : 2,
            "stateStr" : "SECONDARY",
            "uptime" : 167670,
            "optime" : Timestamp(1478864074, 8643),
            "optimeDate" : ISODate("2016-11-11T11:34:34Z"),
            "lastHeartbeat" : ISODate("2016-11-16T07:52:26Z"),
            "lastHeartbeatRecv" : ISODate("2016-11-16T07:52:27Z"),
            "pingMs" : 0,
            "syncingTo" : "mongo-three:27017"
        },
        {
            "_id" : 1,
            "name" : "mongo-one:27017",
            "health" : 1,
            "state" : 2,
            "stateStr" : "SECONDARY",
            "uptime" : 167673,
            "optime" : Timestamp(1478864074, 8643),
            "optimeDate" : ISODate("2016-11-11T11:34:34Z"),
            "self" : true
        },
        {
            "_id" : 2,
            "name" : "mongo-three:27017",
            "health" : 1,
            "state" : 1,
            "stateStr" : "PRIMARY",
            "uptime" : 72642,
            "optime" : Timestamp(1478864074, 8643),
            "optimeDate" : ISODate("2016-11-11T11:34:34Z"),
            "lastHeartbeat" : ISODate("2016-11-16T07:52:28Z"),
            "lastHeartbeatRecv" : ISODate("2016-11-16T07:52:27Z"),
            "pingMs" : 0,
            "electionTime" : Timestamp(1479210113, 1),
            "electionDate" : ISODate("2016-11-15T11:41:53Z")
        }
    ],
    "ok" : 1
}

Output of my log:

2016-11-16T08:52:42.342+0100 [ConnectBG] BackgroundJob starting: ConnectBG
2016-11-16T08:52:42.342+0100 [rsHealthPoll] connected to server mongo-two:27017 (10.144.4.41)
2016-11-16T08:52:43.152+0100 [conn11181] command admin.$cmd command: replSetHeartbeat { replSetHeartbeat: "ReplName", v: 6, pv: 1, checkEmpty: false, from: "clp-dbmg-p03:27017", fromId: 2 } ntoreturn:1 keyUpdates:0 numYields:0  reslen:157 0ms
2016-11-16T08:52:43.772+0100 [conn11179] command admin.$cmd command: replSetHeartbeat { replSetHeartbeat: "ReplName", v: 6, pv: 1, checkEmpty: false, from: "clp-dbmg-p02:27017", fromId: 0 } ntoreturn:1 keyUpdates:0 numYields:0  reslen:157 0ms
2016-11-16T08:52:45.153+0100 [conn11181] command admin.$cmd command: replSetHeartbeat { replSetHeartbeat: "ReplName", v: 6, pv: 1, checkEmpty: false, from: "clp-dbmg-p03:27017", fromId: 2 } ntoreturn:1 keyUpdates:0 numYields:0  reslen:157 0ms
...

Is this normal, or should I take action?

Answer generated from question comments by Stennie:

By "database is still syncing", I assume you are referring to the syncingTo message in the rs.status() output. This just informational and shows which member a secondary is syncing from.

All of your replica set members currently have the same optime so there is no replication lag and this is a normal state. For more detail see my answer to a similar question on Stack Overflow:

MongoDB replica set - always shows “syncingTo”

I would also note that MongoDB 2.6 reached End of Life as of last month (Oct, 2016) which means there will be no further maintenance releases. I would highly recommend upgrading to a supported release, particularly if this is a new deployment. The latest production release series is currently MongoDB 3.2.


What computations can NOT be performed in standard SQL? I know loops cannot be expressed in standard SQL. What other forms of processing can it not perform?

loops cannot be expressed in standard SQL

That is not the case. The relevant part of the Standard is known as SQL/PSM (Persistent Stored Modules). Its procedural paradigm includes 'loops'.

However, consider that core Standard SQL has been relationally complete (by Codd's definition of that term) since at least SQL-92, allowing for 'computations' of arbitrary complexity. Also that a SQL query is more like a specification than an implementation i.e. states the intent for the DBMS to carry out as it sees fit which may itself involve 'looping'.


MySQL compare two columns value at the same row I'm newbie in SQL, I wonder if its possible to compare the values of two columns in the same row(s) using MYSQL.

e.g. I have a table called "Table" with two columns "data_a" and "data_b" which both are (int)

I want to get "data_a" value bigger than "data_b" value .

data_a   data_b
----------------
10        5
----------------
6         4
----------------
2         8
----------------
1         9

First of all, you shouldn't call "table" your table as it is a reserved word and can cause some confusion.

Have you read any documentation about querying ? You should start from here Query information

If you want to select all rows where data_a is bigger than data_b you can do it with

SELECT * FROM table where data_a > data_b


How to Recover My database From Old WampServer? Previously I am using wampserver2 with "mysql5.6.17" version.I have many database in that version.
I uninstall the wampserver and install new latest version wampserver3.1.0_x64.

Before uninstalling my old wamp i copied all files to another drive. so i have some safe backup.

Now i want all database from old wampserver.

Is there any ways to do that..

Before I start, I will say that I am not familiar with wampserver but I am familiar with MySQL and LAMP.

First things first, are your tables within your databases MyISAM, InnoDB or a mixture of both? - You can tell on the file level by looking at the suffixes.

MyISAM tables have the table name with the following suffixes:

.frm
.MYD
.MYI

InnoDB tables have the table name with the following suffixes:

.frm
.ibd

If they are MyISAM, copy the tables straight into the new MySQL database directory and this will work as long as the files have the correct read write permissions and ownership (mysql).

If you have InnoDB tables then this will be an issue because you can't simply copy them into a location, in this case what you would have wanted to do is:

MySQL dump the databases, then MySQL import
Convert them to MyISAM across the board, make a  copy of the file system, then move them into the new database directory, then change back to InnoDB if needed.

** Please note, do not copy the mysql db if you have it within your backup into the new MySQL directory because this will cause all sorts of issues.


Upgrade from SQL Server 2008 R2 Enterprise to SQL Server 2016 Standard Edition? We have SQL Server 2008 R2, which is in production & cannot be shutdown at this stage. I have installed SQL Server 2016 Standard Edition. Can I copy the databases, users and logins, with .ldfs, .mdfs & .ndfs, or use backups to restore to the server running 2016 Standard Edition?

Note that my SQL Server 2008R2 Enterprise databases do not utilize any of the Enterprise features.

A quick Internet search for 'sql server restore enterprise to standard' indicates this is 'theoretically' possible if you're not using 'Enterprise' features.  The only way you'll truly know is to actually attempt the restore of a backup from SQL Server 2008R2 Enterprise to SQL Server 2016 Standard.

Downgrade from SQL Server Enterprise Edition to Standard Edition

Migrating a database from Enterprise to Standard Edition


Storing Dynamic data in relational database (SQL Server 2014) Hi my data model contains some static type properties and one dynamic type property. That property could contain array, string, integer, range. Now, simple solution would be create separate column for each one, but problem with that approach is, I will end up with few of optional columns as only one of the type is present at a time(rest of the columns will be null).

My preferred approach is to save it in one field (preferable text or json) and parses the data according to the type. Well parsing has its cost but I still feel this is better approach, but I am no expert in DB, so I am looking for your opinions.
Thanks

It sounds like you are looking for an Entity-Attribute-Value (EAV) data model design.  Aaron Bertrand has a great post on this which I suggest you look through and see if a similar approach will fit your needs.

As a DBA, I have a deep-seeded hatred for this approach, but sometimes it makes the most sense for the situation at hand.

If you can convince whoever controls the budget to upgrade to SQL Server 2016, there are some new features that take advantage of JSON data, which can give you even more flexibility.


How can I check for stored procedure consistency across multiples servers I've got a stored procedure on 10 distinct servers, how can I check across all servers for stored procedure consistency?

I'm currently pulling the definition for each server manually, and comparing it in notepad++ compare tool.

It's quite time consuming, and surely I'm not the first one to have this problem.

Kankuru is a great and FREE monitoring tool for SQL Server developed here in France. Aside from its monitoring capabilities, it also has a DB compare tool that is easy and simple to use.

It will not only show you if your stored procedures, tables, indexes, types, etc. exist on two servers, but also what those differences are between those two servers.

Here is another compare screen

It's very easy to use and as mentioned before, won't cost you a dime.

To setup Kankuru

Create an empty database in some SQL Server called Kankuru. The kankuru database just stores configuration information and some history and its very compact.
Start Kankuru and from the Home Menu select Configuration

Configure kankuru to use the server\instance and database that you just created.

There is documentation, but its in French. Google translate will be able to get you through one way or another.


Physical Move of Secondary Replica to a new Location We plan on moving a secondary replica of an SQL2014 AlwaysOn group to another physical location although it will have the same IP address after the move.

The shutdown may be between 24 and 48 hours.

Is there anything that would need to be done on the secondary replica prior to shutdown.

Thanks.

Is there anything that would need to be done on the secondary replica prior to shutdown.

I would suggest you to suspend data movement prior to the move (and resume it once the secondary server is up). This will have a side-effect of primary database accumulating the unsent transaction log records in the send queue. This means that for your maintenance time, transaction log on primary database cannot be truncated.

Remember that primary database is exposed - if it goes down, there will be data loss. Make sure you have a good tested full backup + T-log backups.

  Suspending an Always On primary database suspends data movement on all the corresponding secondary databases, and redundancy and failover capabilities cease for that database until the primary database is resumed.

You must remember to resume data movement once secondary database is online.

Also, depending on the database size (if its small), you can think of removing the database from AG and putting it back again once the move is successfully completed.


Disk I/O and PAGEIOLATCH_XX We have been having CPU issues with one of our servers lately and while we have been looking into this we have also noticed queries running slowly with waits of PAGEIOLATCH_XX. In particular, a reindex job is seemingly always having this wait type.

In response, I have run a collect against sys.dm_io_virtual_file_stats and then broken this down into time chunk and worked out the average stall per operation. While there are spikes mostly, the disk seems to have a value of regularly under 20 ms. From what I remember, 20 ms is the recommended value(?).

Further to this I have run Glenn Barry's script:

select db_name(database_id) as DatabaseName, file_id
,io_stall_read_ms
,num_of_reads
,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'
,io_stall_write_ms
,num_of_writes
,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms'
,io_stall_read_ms + io_stall_write_ms as io_stalls
,num_of_reads + num_of_writes as total_io
,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads +
num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'
from sys.dm_io_virtual_file_stats(null,null) --where db_name(database_id) = 'tempdb'
order by [DatabaseName] desc'

Which calculates the average I/O stall also and this also confirms stalls less than 20 ms.

I have also looked in the following to see if any pending tasks are taking longer than recommended, but this isn't throwing up any pending I/O operations taking regularly longer than 20 ms.

SELECT db_name(database_id) as 'Database',
file_name(file_id) as 'File',
io_stall,
io_pending_ms_ticks
FROM sys.dm_io_virtual_file_stats(NULL, NULL) iovfs,
 sys.dm_io_pending_io_requests as iopior
WHERE iovfs.file_handle = iopior.io_handle

My question now is: If the issue is not disk related, why am I seeing lots of PAGEIOLATCH_XX waits? In particular, why is the reindex running extremely slowly with this wait type?

Could this be related to CPU pressure?

================================================================================

I just wanted to update the thread.  After doing more analysis I have tracked down a particular proc that is causing significant reads.  The proc is as follows:

ALTER PROCEDURE [dbo].[GetActiveSessionCount]
    @SessionCount   INTEGER OUTPUT
AS
SET NOCOUNT ON
BEGIN
    DECLARE @Error              INTEGER,
            @RowCount           INTEGER,
            @nExpireAfter       INTEGER
    SELECT  @nExpireAfter = ExpireSessionsAfter FROM KSYSTEM
    SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT
IF(1 <> @RowCount)
BEGIN
RAISERROR (50003, 15, 1, 'GetActiveSessionCount')
RETURN 50003
END
    IF (0 <> @Error)
    BEGIN
        RETURN @Error
    END
    SELECT  @SessionCount = COUNT(SessionID)
    FROM    KSESSION  WITH (NOLOCK)
    WHERE
    (
        (
            Expirable = 0
        )
        OR
        (
            Expirable = 1
            AND
            (   --SessionID IS NOT NULL)
                EXISTS (SELECT SessionID FROM KFILESAWAITINGCOMMIT fac WITH (NOLOCK) WHERE SessionID = fac.SessionID)
                OR
                (
                    LastAccessDateTime IS NOT NULL
                    AND GETDATE() <= (DATEADD(minute, @nExpireAfter, LastAccessDateTime))
                )
            )
        )
    )

SELECT @Error = @@ERROR
IF(@Error <> 0)
BEGIN
RETURN @Error
END
    RETURN 0
END

Using STATISTICS IO I can see the problem line is

SELECT SessionID FROM KFILESAWAITINGCOMMIT fac WITH (NOLOCK) WHERE SessionID = fac.SessionID

Looking at the execution plan it is doing a Clustered Index Scan.  Now there is an non clustered index on that table already specifically for SessionID however it is not being used.

What I am finding in testing is if I run that SELECT by itself then it uses the non clustered index and performs well.  But if I use a hint in the proc to force it to use the non clustered index, then it actually performs worse.

Can anyone explain?

PAGEIOLATCH_XX waits are logged by SQL Server when it is waiting for data to be read from the disk. Index maintenance is a notoriously intensive operation and because of this it should be performed at your quietest times to avoid any impact on production.

You mention you have queries that are causing the same waits. If this is at the same time as the index maintenance then that is not that odd but if it's happening at other times it could be down to memory pressure (not enough room in RAM to store pages so they need to be read from the disk again), large scans or it could even indicate there is a potential problem with your disks. More investigation is needed to rule each of these out.


Entity Relationship Design; The best scenario TLDR; What would be the best design choice for the scenario below and how would each design react at extremely large data volume?
I have experience working with large government database systems servicing 24/7 data collection, processing and reporting. It was fun to poke around all the differently designed schemas and get an idea of how all these features designed by all these different people somehow mishmashed into a working solution. Like I'm sure a few of you know, fun poking times is something of a luxury and most cycles were spent pragmatically, keeping the system alive rather than improving the design.
I have been modelling a new database system and would like to get some thoughts on how you would go about relating these entities.

Scenario: Clients, employers and practitioners require phone numbers.
We have four entities:

CLIENT
EMPLOYER
PRACTITIONER
PHONE

Phone numbers are classified by the technology used to receive calls, technology used informs data format constraints.

Landline
Fax
Mobile

A description field indicates the primary usage of the phone; Home, work, etc.

Ways to relate these entities
1. Denormalize PHONE into CLIENT, EMPLOYER, PRACTITIONER
Let's start with bad design and go from there. De-normalize all the phone numbers!

3 entities, no relationships
Must create new columns if a new phone number is required; Leading to CLIENT.Phone1Number, EMPLOYER.Phone2Type, PRACTITIONER.Phone3Description;
or limit the number of phone numbers that can be entered for each entity
Redundancy, lots of overhead
Doing bulk-updates or maintenance would become tedious

Conclusion: select * from 'no_thanks';

2. Build the nth Bridge; Rename the database Pittsburgh
For each entity that can have a phone number related, create a bridge entity to bring them together.

7 entities, 6 relationships
Data redundancy; PhoneNumber populated in multiple locations
Can use bridge table if only PhoneNumber is required; Does not require JOIN to PHONE
If a new entity, FAMILYMEMBER, requires a phone number, a new bridging table must be created

Conclusion: Maybe, depends on the difficulty of maintaining the relationships

3. A Quasi "Star Schema"
Modify PHONE to include Foreign Keys of CLIENT.ClientID, EMPLOYER.EmployerID, PRACTITIONER.PractitionerID.

4 entities, 3 relationships
If one of these keys is populated, the others cannot be populated. (No case when a client, employer, practitioner would have the same phone number)
If a new entity, FAMILYMEMBER, requires a phone number, a new attribute must be created in PHONE
All FK attributes with NULL by default

Conclusion: Seems like the strongest design in my opinion

If I really have to choose, I'd say your 2nd choice (build nth bridge) will be my favorite as it is clean and stick to rules. While your 3rd choice, though it reduces the # of tables, it actually brings "redundant" columns with null values in the Phone table and this design is "volatile" in the sense that if you need to add a new phone owner type, you need to add an additional column in the [Phone] table.

In an alternative way, I would create a "bridge" table like the following (using t-sql as an example):

    create table PhoneOwner (ID int identity primary key
    , OwnerID int
    , OwnerType varchar(20));

So for the [Phone] table, I will add one column PhoneOwnerID which references to [PhoneOwner] table's [ID] column. OwnerType column can have values as "Client", "Employer" or "Practitioner" in this case, or other values in future. (Of course, we can create [OwnerType] table to hold these values, and put a FK in [PhoneOwner] table to reference this [OwnerType] table)

The problem with this alternative design here is the integrity check, for example if OwnerType = 'Client', we need to ensure OwnerID's value does exists in [Client] table, though this can be done via application layer or via trigger to implement such business rules.

But I have to admit this is a compromise to strict integrity (via FKs) rule, the good side is that it somehow achieves better flexibility, and less mess in table design which will cause many null values in "redundant" columns.


Connection to local SQL Server 2012 can be established from SSMS 2008 but not from SSMS 2012 I have two local SQL Server instances running on my local machine. The first is SQL Server 2008 R2 Enterprise Edition (named MSSQLSERVER) and the 2nd is SQL Server 2012 Business Intelligence Edition.

My problem is with SSMS 2012 which can connect to distant servers but not the local 2012 instance; I can however connect to this instance from SSMS 2008.

The error message I get when trying to login is

  Login Failed. The login is from an untrusted domain and cannot be used with Windows Authentication. (Microsoft SQL Server, Error: 18452)

I must point out that I don't have the necessary privileges to access SQL Server Configuration Manager (blocked by group policy).

Any help would be appreciated.

I saw an item on Pinal Dave's blog recently that may answer your question.  He suggests adding the line
127.0.0.1 localhost
to your hosts file

The full article can be found here

I do note, however, the situation he refers to is a machine that is not part of a domain - if your machine is, then this may not solve it


Securing DB passwords Looking at the structure of most PHP/MySQL-based websites I've seen, it appears that it's not terribly difficult to discern the database password if you dig a bit, as there's invariably a setup or configuration file someplace that stores the information for logging into the DB.  Other than the basic precaution of making sure that my database's privileges are appropriately restricted for remote requests, what options are available that I could implement in my own projects to protect this information?

Not a direct answer about storage of the passwords, but I generally use at least two database connections when building webapps -- one's used 99% of the time for user-related activities, with restricted privileges, and the other's used for 'admin' functionality (delete users, etc.).

In a few cases, where I'm installing someone else's package, I'll install two instances ... a public facing instance which only has database access to do the general user-type stuff, and a second instance that's IP-restricted to my local subnet (possibly even on a different machine) that has to be used for any 'admin' type activities.  Neither one has access to modify tables, etc, though ... I'd rather go in via the native database tools than allow the webapp to run its own update tasks that haven't been vetted.

You can take it even further though, and add more connections specifically for given tasks ... so the user creation & password management tasks go through a user that has extra privileges on the user tables, login has database privs to authenticate and not much else, etc.

In this way, if there's a sql injection attack, on most webpages it can't really do anything significant -- can't see the password hashes, can't add a new admin user (not that they'd be able to do anything anyway), etc.  It still won't help if they manage to get a shell on your machine, but it'll slow them down.


MySQL Workbench: Edit hidden relationships in EER diagram How can I open the Edit relationship options panel of a relationship that is previously been hidden?

Workbench v6.2

To my surprise I can't find any method to access the hidden relationship. It's listed in the table that defines the foreign key, but you cannot use this to open the relationship editor. However, by (temporarily) changing the foreign key the relationship is made visible again and you can then double click it to open the relationship editor. A context menu entry for the FK in the table editor and/or a small button to open that relationship editor would be nice. Open a feature request at http://bugs.mysql.com if you like.


Oracle index with REGEXP_REPLACE is it a good idea? I have a table of contacts with 1 million registers and now the user wants to search in it by the telephone.

Trying to avoid a fullscan I decided to create a new index in this column, but the trick part is that the data in this column is not normalized. I can find telephones separate by spaces, by "-", and even a lot of different formats. (ex: (418) 123-1234, 418 123-1234, 4181231234, 55(11)12321-1234, ...)

Found some articles saying to use REGEXP_REPLACE to take care of that... just removing everything and comparing always with numbers. So, my query for test would by like that:

select * from tb_contc WHERE REGEXP_REPLACE (TEL_CONTC,'[^[:digit:]]+','') = '1234567777';

First problem I've found is that that is not a valid index to create. Trying to create it returns me a " ORA-01450: maximum key length":

CREATE INDEX IX_06_TB_CONTC ON TB_CONTC REGEXP_REPLACE (TEL_CONTC,'[^[:digit:]]+','');

Apparently this kind of function in the index would create a index that is worst than making a full scan in the table... The next step was to add a SUBSTRING in the index, that allows to create it by limiting the size of the VARCHAR returned from my REGEX

CREATE INDEX IX_06_GR_CONTC ON GR_CONTC (substr( REGEXP_REPLACE (TEL_CONTC,'[^[:digit:]]+',''), 1, 20));

Things got real ugly... Besides, to force the use of this index I need to add the exactly same query in my select.

Wouldn't be bad if it it had a good performance, but instead what I got is:

Without the index: 15 seconds the first time I execute the query, all the others would take basically nothing
With the index: 2 seconds every time.

Wraping up: Am I doing a good thing by using this kind of index with REGEX? Is there a better way?

I don't have direct experience of an index with this kind of REGEX so I would avoid it by creating a 'standardised' telephone number column.

Create a process to go through the telephone numbers and store the clean, formatted version in the new column.

Perform the 'standardising' process on the user input and use that as a search against the new telephone column - which you will have indexed.

The other option is to go through a data clean up process to standardise all the telephone numbers and ensure that any on-going input method applies the same process/validation.

EDIT:

The virtual column option mentioned above may be of use to you as well.

ALTER TABLE contacts ADD(search_telephone AS (REGEXP_REPLACE(tel_no, 'pattern', 'replace')).


How can I track the last modified user of a file in an SQL Server 2012 FileTable Other than through a custom interface to store/fetch files using the old filestream way, is there a way to log which user modified a file opened directly from the share exposed by Sql Server 2012?

Turn on auditing at the file system level. The file system APIs should then capture this information to the Security Event log.


How to retrieve all the rows where primary key value found in the other column in the row in MySql Following is MySql Table in the snapshot:

column sno could be found in the other rows in column p_sno. i.e.

sno = 5   linked through p_sno = 5 with sno = 6 (two rows)

sno = 8   linked through p_sno = 8 with sno = 9 while
sno = 9  linked through p_sno = 9 with sno = 10 (three rows)

Similarly

sno = 13 linked to sno = 14 by p_sno = 13 (two rows)

Now, what i need from this table is to return

sno = 5 and sno = 6 if someone search by temp_no = 2017-05 or temp_no = 2017-06

Same thing with other rows for example if somebody search with temp_no = 2017-08 it should return three rows i.e. sno = 8, sno = 9 and sno = 10 and so on so forth.

help appreciated in advance.

You can (easily) do one-level relations, and then UNION the results. That's what would take to get rows related to temp_no = '2017-06' (and with a query which is SQL standard):

SELECT
    *
FROM
(
    -- Direct search
    SELECT
        *
    FROM
        t AS t0
    WHERE
        t0.temp_no = '2017-06'

    UNION

    -- One level related
    SELECT
        t1.*
    FROM
        t AS t0
        JOIN t AS t1 ON t1.sno = t0.p_sno
    WHERE
        t0.temp_no = '2017-06'
) AS u
ORDER BY
    sno ;

Giving:

 sno | temp_no | first_party | second_party | p_sno
 --: | :------ | :---------- | :----------- | ----:
   5 | 2017-05 | Akhtar Ali  | Amir Khan    |  null
   6 | 2017-06 | Akhtar Ali  | Amir Khan    |     5

dbfiddle here

NOTE: I've replaced your p_sno = 0 values by nulls, which seems to me that better represents the meaning of this column.

You seem to want to find all descendants of a certain row, using a parent-child relationship between p_sno and sno. This is an indefinetly-deep multi-level hierarychy. If you use a database that supports recursive CTEs (such as MariaDB instead of MySQL) you can use the recursive CTE explained in: Check CTE to get all children (descendants) of a parent.

If your question is restricted to MySQL, you can use the tecnique described by
How to create a MySQL hierarchical recursive query, that would make you produce the following query for your temp_no = '2017-08' use case:

 SELECT
     t_sorted.*
 FROM
     (SELECT
         *
     FROM
         t
     ORDER BY
         p_sno, sno
     ) AS t_sorted,
     (SELECT
         @pv :=
         (SELECT
             sno
         FROM
             t
         WHERE
             temp_no = '2017-08'
         )
     ) AS init
 WHERE
     -- for first row
     t_sorted.sno = @pv
     -- recursive query
     OR (find_in_set(p_sno, @pv) > 0 AND @pv := concat(@pv, ',', sno)) ;

 sno | temp_no | first_party | second_party | p_sno
 --: | :------ | :---------- | :----------- | ----:
   8 | 2017-08 | Amir Khan   | Saleem       |  null
   9 | 2017-09 | Amir Khan   | Saleem       |     8
  10 | 2017-10 | Amir Khan   | Saleem       |     9

dbfiddle here


PostgreSQL UUID Literal: ERROR: syntax error at or near Whever I use a UUID as a literal, I get

SELECT a82857b6-e336-4c6c-8499;
ERROR:  syntax error at or near "-"

I thought it might be an encoding issue, but that doesn't seem to be it.

As documented in the manual, string constants (or in general: anything that is not a number) need to be enclosed in single quotes:

ALTER TABLE newarts ALTER COLUMN jurisdiction_id
   SET DEFAULT 'a82857b6-e336-4c6c-8499-4242';


manage backups azure sql option This may be a silly question but I recently created an azure sql database and I need to check the backup policy but I cannot find the option manage backups that the documentation shows as you can see in the next picture:

and this is how the blade of my database look like

Am I missing something?

Your screenshot appears to be the SQL Database blade, not the Server blade. If you open the Overview pane on the Database blade, then in the top-right corner there is a property called Server Name with a link. Click the link to open the Server blade and your Manage Backups option is there.


Postgres - Select all if given parameter is null i have a table with the below columns
name varchar(250)
type varchar(25)

is there a way for me to structure a query to return all records if the type parameter is null?
Im aware that i can do something like
select * from tbl where type = Coalesce(@param, 'a');

which will give me all records of type 'a' if i give an empty param.
But what if i want to retrieve all records if param is null?

Use the placeholder for strings:
select * from tbl where type LIKE Coalesce(@param, '%');


PostgreSQL: Change default port used by utilites like psql, createdb, etc I have 2 instances of PostgreSQL running on some servers. One on the default port (5432) and the other instance on port 5433. Some users (and processes) only need access to the second instance (5433) and I'd like to set it up so that when those users use commands like psql or createdb in their shell it will automatically direct them to the right Postgres instance instead of them having to type -p 5433 along with every command.

I tried to look this up on www.postgresql.org but was unable to find it...probably my fault. Anyone know how?

PostgreSQL command-line utilities (and more generally all programs that rely on the libpq library) automatically use the environment variables PGPORT and PGHOST when they're defined.

So if you do in the shell:

$ PGPORT=5433; export PGPORT

any subsequent call to psql will act as if it has been invoked with the -p 5433 command-line option.

See Environment Variables in the libpq documentation for all these variables . They can be used to provide default values to almost every parameter of a connection to the database.


How can I generate object DDL, like the CLI "show" command does? When we run the show command, e.g. show table db.tablename; show view db.viewname, it lists the object definition. I believe it will query the dictionary tables and rebuild .

Could you help us to understand how it queries the internal tables?

For a SHOW TABLE the parser actually creates the CREATE TABLE based on the current definition.

But for SHOW VIEW, etc. it's retrieving the CREATE from dbc.TablesV.RequestText. If the source code is larger than 12.5 KB RequestTxtOverFlow is set and the remainder is found in one or more 32 KB chunks in dbc.TextTbl.

For SHOW PROCEDURE it's different, this returns the internally stored sorce code (unless the SP is created with the NOSPL option), there's no way to access this info using a Select.


SQL Server Non Clustered Index Compression I'm struggling to find out the answer, we currently use compression on all our data warehouse tables, which has improved performance and also greatly reduced the storage space used.

I've only recently realised, that we aren't compressing any of our non-clustered indexes. Should we also be doing this? Are there any gotchas that we should be aware of?

I've only recently realised, that we aren't compressing any of our
  non-clustered indexes. Should we also be doing this? Are there any
  gotchas that we should be aware of?

The main question here is, do you want to save space & memory usage at the cost of more cpu cycles? This is something we will not be able to answer for you.

Since you are able to compress the clustered indexes, no other restrictions noted in Considerations for When You Use Row and Page Compression are possible limitations.

You could always run the sp_estimate_data_compression_savings procedure to calculate the gains you could get from compressing the nonclustered indexes. See here for a solution for every table in your data warehouse.

Speaking of data warehouses, you could also take a look at (clustered / nonclustered) columnstore indexes and see if they are a fit for your (fact) tables.


Send Multiple Attachments With Send DBMail I thought that you just needed to separate two file names with a ; but my syntax fails with an error of

Msg 102, Level 15, State 1, Line 251
Incorrect syntax near '@filename2'.

This is my full syntax, what do I need to do in order to attach both files to my email?

Declare @filename1 varchar(max), @filename2 varchar(max)
Set @filename1 = 'C:\Testfiles\Test1.csv'
Set @filename2 = 'C:\Testfiles\Test2.csv'

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

The semi-colon is a statement terminator, and you haven't correctly escaped your attachments. In other words, you've done this:

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

@filename2; -- Syntax Error

According to the MSDN article on sp_send_dbmail:

  [ @file_attachments= ] 'file_attachments'

  Is a semicolon-delimited
  list of file names to attach to the e-mail message. Files in the list
  must be specified as absolute paths. The attachments list is of type
  nvarchar(max). By default, Database Mail limits file attachments to 1
  MB per file.

You need to do this:

Declare @filenames varchar(max)
Set @filenames = 'C:\Testfiles\Test1.csv;C:\Testfiles\Test2.csv'

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

If you want to programmatically add attachments, you would do something like this (notice how the second and third files begin with a semi-colon):

DECLARE @filenames varchar(max)
DECLARE @file1 VARCHAR(MAX) = 'C:\Testfiles\Test1.csv'
SELECT @filenames = @file1

-- Optional new attachments
DECLARE @file2 VARCHAR(MAX) = ';C:\Testfiles\Test2.csv'
DECLARE @file3 VARCHAR(MAX) = ';C:\Testfiles\Test3.csv'

-- Create list from optional files
SELECT @filenames = @file1 + @file2 + @file3

-- Send the email
EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'Mod',
  @from_address = 'modis@modisglobal.com',
  @recipients= 'rsmith@gmail.com',
  @subject= 'Test Email',
  @body = @body1,
  @file_attachments = @filenames;


mssql-server crashing: Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 8 We have an Azure Linux VM with a local MSSQL server. I recently had to reboot the VM, and since then I haven't been able to start the MSSQL server. The error logs would say Recovery of database 'MyDB' (6) is 1% complete ..., then hang the VM to the point that I could not access it over SSH, and eventually the mssql-server service would just stop. The last time this happened there was finally an error message in the logs:

2017-07-30 10:20:35.19 spid24s      Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 8
2017-07-30 10:20:38.85 spid24s
Process/System Counts                         Value
---------------------------------------- ----------
Available Physical Memory                5837422592
Available Virtual Memory                 140733193256960
Available Paging File                    5837422592
Working Set                              4294967296
Percent of Committed Memory in WS                82
Page Faults                                       0
System physical memory high                       1
System physical memory low                        0
Process physical memory low                       0
Process virtual memory low                        1
2017-07-30 10:21:22.83 spid24s
Memory Manager                                   KB
---------------------------------------- ----------
VM Reserved                                 5883372
VM Committed                                5091232
Locked Pages Allocated                            0
Large Pages Allocated                             0
Emergency Memory                               1024
Emergency Memory In Use                           8
Target Committed                            5700608
Current Committed                           5091232
Pages Allocated                             2923720
Pages Reserved                                    0
Pages Free                                  2024712
Pages In Use                                 217880
Page Alloc Potential                        5197704
NUMA Growth Phase                                 0
Last OOM Factor                                   9
Last OS Error                                     0
2017-07-30 10:21:23.78 Server      Long Sync IO: Scheduler 0 had 1 Sync IOs in nonpreemptive mode longer than 1000 ms
2017-07-30 10:22:40.64 spid24s
Memory node Id = 0                               KB
---------------------------------------- ----------
VM Reserved                                 5883244
VM Committed                                5091196
Locked Pages Allocated                            0
Pages Allocated                             2923752
Pages Free                                  2024680
Target Committed                            5700568
Current Committed                           5091200
Foreign Committed                                 0
Away Committed                                    0
Taken Away Committed                              0
2017-07-30 10:23:21.93 spid24s
Memory node Id = 64                              KB
---------------------------------------- ----------
VM Reserved                                       0
VM Committed                                     36
Locked Pages Allocated                            0

The VM is running Ubuntu with 8GB ram.
Do I need more memory, or are there other solutions?

The error message is indicating internal memory pressure; could you please share the complete errorlog output when the OOM condition had occurred. We would like to validate memory consumption by each clerk.

How large is your database which is still in recovery; It looks like there some long outstanding i/o operations occurring. Are there any latencies at the storage layer?

-Pradeep


Database engine installation failed Being asked to implement an application for purchasing management, the database managing system which I have to install, is SQL Server 2008.

I am having some issues while installing the main component, Database engine.

I've searched a lot for my problem, I found out that during the installation, I have to use the account network service rather than system, and after uninstalling all the components, and then re-installing SQL Server. However it didn't do any better.

Just for the OS I am using, it's a Windows 7 Professional.
As for the message printed during the installation, i have :

  "Wait on the database recovery failed. Check the SQL Server error log for potential causes."

Any ideas where the problem can be ? or do i need another version of windows to install it ?

The answer was to create a user who would the super administrator of the database. So that we can distinguish between Windows authentication and Sql Authentication, in case it doesn't work !!!
Thank you !


Prevent Primary Key increment when an INSERT statement fails in PostgreSQL I have a simple table in my PostgreSQL database like this:

CREATE TABLE person_type
(
  id serial NOT NULL,
  name character(55) NOT NULL,
  CONSTRAINT person_type_pkey PRIMARY KEY (id),
  CONSTRAINT person_type_name_key UNIQUE (name)
)

As you can see the id is automatically incremented and the name must be unique. Every time I run an INSERT sql statements, even when it fails, the id is incremented by one.

How can I prevent this? In order that each time a row is inserted successfully, the primary keys are correlative

this is exactly what you are facing:

test=# CREATE SEQUENCE seq_a;
CREATE SEQUENCE
test=# SELECT nextval('seq_a');
 nextval
---------
       1
(1 row)

test=# BEGIN;
BEGIN
test=# SELECT nextval('seq_a');
 nextval
---------
       2
(1 row)

test=# ROLLBACK;
ROLLBACK
test=# SELECT nextval('seq_a');
 nextval
---------
       3
(1 row)

a sequence makes sure that values are ascending BUT it does not make sure that it does not contain gaps. it is also important to notice that a sequence cannot be rollbacked. you cannot have strictly ascending and gap-free at the same time as it would not work with a mix of long and short transactions.
therefore a sequence should never be used for an invoice-id and so on.


After I kill my clients, MySQL continues to process queries for hours Hi I have a MySQL db with 10 million rows with lots of triggers. A php client connects to mysql and INSERT's hundreds of rows per second.

When the client has been running for hours, and I terminate the client, using mytop i see transactions are still ocurring, even new ones. It lasts for hours before it "catches up".

Now as a DBA, how can I view this "back log"? what variable is it listed as? What my.cnf parameters would control it? Why is this even happening? I thought the

Here is a VERY casual explanation...

MySQL works like this:

You send it a query that has a result set larger than its buffers, so it creates a temp table on disk to hold the data. That is an expensive process as it is usually reading from, swapping and writing to the same disk, unless purposely configured differently. Nonetheless, performance tanks. So now you're thinking "I have to do something, right?" So you kill your PHP process. The question now is "did your query process get killed, or is it running or is it now freeing space?" To find out what state your process is in, you'll need to run this as root or a user with the PROCESS privilege:

SELECT TIME,STATE,ID,HOST,DB,INFO
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE INFO IS NOT NULL
ORDER BY TIME DESC LIMIT 3\G

This will give you the top three longest running processes. Just increase the limit to reveal more. If you really want to end the offending process, look at the ID field and KILL that #.
E.g.

*************************** 1. row ***************************
 TIME: 0
STATE: executing
   ID: 4
 HOST: localhost
   DB: NULL
 INFO: SELECT TIME,STATE,ID,HOST,DB,INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO IS NOT NULL ORDER BY TIME DESC
1 row in set (0.03 sec)

mysql> KILL 4;

This won't simply kill all the things happening with that ID, but at least you'll be assured that it's definitely set into a STATE of "FREEING ITEMS" or equivalent.


Getting null block_st.text when querying for blocks We are using the blocking query from this article which queries sys.dm_tran_locks.

http://simplesqlserver.com/2013/05/21/blocking-capturing-and-monitoring/

The issue we've ran into is that often block_st.text will be null.  We are trying to find out the reasons that this could occur.

If I understand you correctly you are looking to see who is blocking and why, not just gathering overall statistics.  (Both approaches have real value, of course.)

Since every event is transient, so it is not surprising that some values would not return what you expect.

We have been using the approach outlined by Tony Rogerson quite a few years ago that uses Event Notifications.

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/04/06/event-notifications-monitoring-blocked-processes-and-other-events-end-to-end-how-to-set-it-up-and-make-it-work.aspx

This approach will capture the state of blocking based on a timing trigger.  In the sample code Tony has configured to receive a notification when a block has lasted 10 seconds. (And every 10 seconds after until the block is removed.)

This approach will provide a capture of the XML describing the blocking state at the moment of the event. This includes the blocked and the blocking process.  And if none of the blocks are long enough to capture, then your event table will be very quiet.

You can monitor for shorter or longer blocks, depending on your need.  I generally run at 25 seconds, to cut out a lot of chatter from shorter blocking periods.

New software releases can be a good reason to monitor blocking more closely.


MySQL: how to prevent deadlock on concurrent inserts Percona Server 5.6.39-83.1-log

Table structure:

CREATE TABLE `myTable` (
    `column_1` VARCHAR(50) NOT NULL DEFAULT '',
    `column_2` VARCHAR(50) NOT NULL DEFAULT '',
    `column_3` VARCHAR(50) NOT NULL DEFAULT '',
    `column_4` VARCHAR(50) NOT NULL DEFAULT '',
    `column_5` VARCHAR(50) NOT NULL DEFAULT '',
    PRIMARY KEY (`column_1`, `column_2`, `column_3`) USING BTREE,
    INDEX `column_3` (`column_3`) USING BTREE,
    INDEX `column_4_5` (`column_4`, `column_5`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

There are two or more concurrent insert leads to deadlock:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-03-23 18:12:45 7f1c08eb8700
*** (1) TRANSACTION:
TRANSACTION 55665288601, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 10275055, OS thread handle 0x7f1c08c6c700, query id 56392293629 localhost user update
INSERT IGNORE INTO myDB.myTable
(`column_1`, `column_2`, `column_3`, `column_4`, `column_5`)
VALUES
('a1', 'a2', 'a3', 'a4', 'a5'),
('b1', 'b2', 'b3', 'b4', 'b5'),
('c1', 'c2', 'c3', 'c4', 'c5'),
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 337207190 page no 7370 n bits 200 index `PRIMARY` of table `myDB`.`myTable` trx id 55665288601 lock mode S locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 55665288598, ACTIVE 0 sec inserting, thread declared inside InnoDB 4965
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 35
MySQL thread id 10275057, OS thread handle 0x7f1c08eb8700, query id 56392293627 localhost user update
INSERT IGNORE INTO myDB.myTable
(`column_1`, `column_2`, `column_3`, `column_4`, `column_5`)
VALUES
('a1', 'a2', 'a3', 'a4', 'a5'),
('b1', 'b2', 'b3', 'b4', 'b5'),
('c1', 'c2', 'c3', 'c4', 'c5')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 337207190 page no 7370 n bits 200 index `PRIMARY` of table `myDB`.`myTable` trx id 55665288598 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 337207190 page no 83133 n bits 144 index `PRIMARY` of table `myDB`.`myTable` trx id 55665288598 lock_mode X insert intention waiting
*** WE ROLL BACK TRANSACTION (1)

Why is this happening?
Despite the fact that I do not use auto-increment in the primary key and use IGNORE in query.
How should I avoid this problem?

Both insert statements have the same values for the primary key which is why the deadlock occurred.

At the point of the deadlock, the other transaction could rollback. If the INSERT IGNORE did ignore, and the other transaction rolled back, then the definition of 'INSERT IGNORE' inserting if a record doesn't exist is violated.

This is why the deadlock is returned, because its a business logic problem that needs to be resolved by the application. The action to be taken in a deadlock depends on the situation that cannot be known upfront.


Grouping numbers based on closest sum to a specific value with Postgres I have a table like this:

 v |   c
---+-------
 Z |   217
 J |   620
 U |  1891
 F |  3751
 A |  5673
 Y |  5859
 O |  7347
 K |  9827
 I | 11842
 R | 11997
 H | 14818
 M | 18321
 G | 18445
 E | 19220
 D | 22444
 W | 22692
 T | 24428
 P | 26257
 N | 35247
 L | 41416
 C | 42594
 B | 43586
 S | 59613

How can I group those values in a way that every single group has the closest possible value to 60000?

For example (not probably the best fit):

Z + J + U + F + A + Y + O + K + I + R -- 59024
S -- 59613
B + H -- 58404
etc.

A bit of research reveals that this question is known as the subset sum problem  in computer science.

On stackoverflow, Lukas Eder provides an Oracle solution to a similar question, and a longer analysis on jooq's blog.

Here's a postgres version derived from his work:

CREATE TABLE tab(v text, c int);

-- populate table
INSERT INTO tab(v,c) VALUES('Z', 217);
etc...

-- show 10 better results

WITH RECURSIVE sums (subset_sum, max_v, a) AS (
        SELECT
            c,v,array[v] as a
        FROM
            tab

        UNION ALL

        SELECT
            c + subset_sum,
            v,
            array_append(a, v)
        FROM
            sums
        JOIN
            tab
        ON sums.max_v < tab.v
    )
SELECT subset_sum, a FROM sums WHERE subset_sum <= 60000
ORDER BY 60000-subset_sum
LIMIT 10;

The result with the sample data is:

 subset_sum |      a
------------+-------------
      59985 | {A,K,N,O,U}
      59985 | {I,K,R,T,U}
      59985 | {A,G,J,N}
      59985 | {A,F,G,P,Y}
      59985 | {C,K,O,Z}
      59985 | {A,C,F,J,O}
      59985 | {F,H,L}
      59985 | {D,E,M}
      59985 | {A,C,K,U}
      59985 | {E,J,K,M,R}
(10 rows)

Note that the execution time will grow exponentially with the row count in the base table.


How do data checksums interact with streaming replication? Data checksums are a new feature introduced in 9.3, and:

  there is a new GUC parameter "ignore_checksum_failure" which will force PostgreSQL to continue processing a transaction even if corruption is detected

In the event of a checksum failure on a replication master, will that corrupt data replicate to slaves or will replication halt. Does it depend on the setting of ignore_checksum_failure?

This README has some useful related information but doesn't directly answer the question.

If the data was corrupted while on disk, the corruption should not get replicated unless it was already present in the backup from which the replica was created.

Of course if you ignore checksum failures then the master may start going wrong things, and that wrongness will be replicated.

If the corruption is occurring in memory then all bets are off.


Select statement is locking a record We have a process which selects a record from a progress table, then issues an update against that record.

If we do not do the select, then the update works all day long.  If we do the select, then the update times out.

The select query is pretty simple and looks like:

select fg."alphakey", n."first-name", n."last-name"
from pub.name n
inner join pub."family-guardian" fg
  on (fg."name-id" = n."name-id")
where fg."alphakey" = 'somevalue'

We've confirmed that if the above statement is not present then it works.  However, if the above statement is present then the UPDATE fails.  The update goes against the name table.  Unfortunately, I don't have that code as it executes through a third party.

Two questions:

Is this normal for progress?
What is the best way to get around the issue?

If dirty reads is not an issue in this scenario, you could try the table hint readuncommitted or nolock..

pub.name n WITH(NOLOCK)

join pub.[family-guardian] fg WITH(NOLOCK)


Pull every other row, starting at 0, and concatenate them together I have a temp table that will contain two columns ID and Text. The temp table will contain an unknown amount of rows but there will always be an even amount. What I need to do is select every other row starting at 0 (so 0, 2, 4, 6, etc.) and then concatenate the values of Text together. I found https://stackoverflow.com/questions/4799816/return-row-of-every-nth-record which looks to be how to get every other row but I am not sure how to do the concatenation.

Example Table:

Row ID Text
0   0  This
1   1  Do
2   2  is
3   3  not
4   4  the
5   5  use
6   6  text
7   7  text

The result I would want from that table is This is the text.

DECLARE @x TABLE(ID INT, [Text] NVARCHAR(32));

INSERT @x VALUES (0,N'This'), (1,N'Do'),  (2,N'is'),   (3,N'not'),
                 (4,N'the'),  (5,N'use'), (6,N'text'), (7,N'text');

SELECT LTRIM((SELECT ' ' + [Text] FROM
  (SELECT rn = ROW_NUMBER() OVER (ORDER BY ID), [Text] FROM @x) AS x(rn, [Text])
  WHERE rn % 2 = 1 ORDER BY rn FOR XML PATH, TYPE).value('.[1]','nvarchar(max)'));

Result:

This is the text

If you know your strings can't contain characters like <, > and & then it should be slightly more efficient to leave out the TYPE).value bit:

SELECT LTRIM((SELECT ' ' + [Text] FROM
  (SELECT rn = ROW_NUMBER() OVER (ORDER BY ID), [Text] FROM @x) AS x(rn, [Text])
  WHERE rn % 2 = 1 ORDER BY rn FOR XML PATH('')));


Index creation with multiple columns - should I combine as many as possible? When creating indexes and especially those with multiple columns, should I look to combine as many columns in the index as would be useful?

I've ran a script on the stats to pick up missing indexes and there's some suggestions such as:

CREATE INDEX [IX_Tbl_ContractId_OpenClosedStatus] ON [tbl]
([ContractId], [OpenClosedStatus]) INCLUDE ([Id], [StringId], [DateNextDeadline])

And

CREATE INDEX [IX_Tbl_ContractId_OpenClosedStatus] ON [tbl]
([ContractId], [OpenClosedStatus]) INCLUDE ([Id], [DateNextDeadline])

And

CREATE INDEX [IX_Tbl_ContractId_OpenClosedStatus] ON [tbl]
([ContractId], [OpenClosedStatus]) INCLUDE ([Id], [ClientId], [DateNextDeadline])

They all have the same [Id] and [DateNextDeadline] columns in there, and they all are indexed on the same two main columns.

Should I, in this instance, create a single index with ([Id], [StringId], [ClientId], [DateNextDeadline]) and will SQL Server use the same column indexes to and pick the including columns depending on the query? Or would it only use the index if there's an exact match? So I would need to create three indexes?

Based on those 3 specific recommendations, I would use

CREATE INDEX [IX_Tbl_ContractId_OpenClosedStatus] ON [tbl]
([ContractId], [OpenClosedStatus]) INCLUDE ([Id], [ClientId], [DateNextDeadline],[StringID])

The equality/inequality columns are the same, which is the bigger part.  SQL Server should use this for any queries that have all or a subset of those Included cols.

And of course, you run this through a rigorous set of test queries to verify :)