Encounter deadlock even when there is no relation between tables I've been studying about deadlock and it's different causes .
I found an example regarding this issue but I could not understand the cause of the deadlock exactly because two DIFFERENT tables are involved and what I have read so far was when only one table is involved !!

First I run This Query in Connection 1 :

Begin Tran

 Update  Purchasing.PurchaseOrderHeader
 Set Freight = Freight * 0.9
 Where PurchaseOrderID = 1255;

Again I run this query in connection 2 :

update Purchasing.PurchaseOrderDetail
Set OrderQty = 4
Where ProductID = 448 and
      PurchaseOrderID = 1255

and back in connection 1 I run this query  (which leads to the deadlock):

Begin Transaction

             Update Purchasing.PurchaseOrderDetail
             Set OrderQty = 2
             where ProductID = 448 and
                   PurchaseOrderID = 1255

As you can see , there are two tables involved in the transaction , So what exactly causes the deadlock??
Thanks in advance

I could not understand the cause of the deadlock exactly because two
  DIFFERENT tables are involved and what I have read so far was when
  only one table is involved !!

Deadlocks occur when 2 or more sessions are waiting on each other. It doesn't matter if the deadlocked resources are rows in the same table, different tables, entire objects, etc.

I ran your scripts to reproduce the deadlock, extracted the xml_deadlock_report column from the system_health XE trace, and saved the deadlock report XML to a file with an .xdl extension. Below is the graphical view from SSMS.

As you can see, the SPID 58 victim was waiting on a Purchasing.PurchaseOrderDetail update keylock, blocked by the SPID 53 exclusive keylock on the same key. SPID 53 was waiting on an update keylock on Purchasing.PurchaseOrderHeader, blocked by the exclusive lock SPID 58 held on the same key.

Below is the raw xml_deadlock_report XML.

<deadlock>
  <victim-list>
    <victimProcess id="process1a668c9eca8"/>
  </victim-list>
  <process-list>
    <process id="process1a668c9eca8" taskpriority="0" logused="400" waitresource="KEY: 6:72057594048348160 (4ab5f0d47ad5)" waittime="1316" ownerId="2500187" transactionname="user_transaction" lasttranstarted="2018-07-24T06:02:13.303" XDES="0x1a85b00c490" lockMode="U" schedulerid="3" kpid="2932" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-07-24T06:02:31.437" lastbatchcompleted="2018-07-24T06:02:31.433" lastattention="2018-07-24T06:00:22.400" clientapp="Microsoft SQL Server Management Studio - Query" hostname="SQLSERVER" hostpid="15904" loginname="DOMAINNAME\USERNAME" isolationlevel="read committed (2)" xactid="2500187" currentdb="6" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
      <executionStack>
        <frame procname="adhoc" line="1" stmtstart="64" stmtend="278" sqlhandle="0x02000000d0c7f31a30fb1ad425c34357fe8ef6326793e7aa0000000000000000000000000000000000000000">  unknown    </frame>
        <frame procname="adhoc" line="1" stmtstart="26" stmtend="326" sqlhandle="0x02000000b66e4532b6686980baf466a9597258f2464dd4f50000000000000000000000000000000000000000">  unknown    </frame>
      </executionStack>
      <inputbuf>               Update Purchasing.PurchaseOrderDetail               Set OrderQty = 2               where ProductID = 448 and                     PurchaseOrderID = 1255   </inputbuf>
    </process>
    <process id="process1a66883d848" taskpriority="0" logused="9484" waitresource="KEY: 6:72057594048413696 (4bc08edebc6b)" waittime="16176" ownerId="2500193" transactionname="user_transaction" lasttranstarted="2018-07-24T06:02:16.577" XDES="0x1a85e004490" lockMode="U" schedulerid="5" kpid="20416" status="suspended" spid="53" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-07-24T06:02:16.577" lastbatchcompleted="2018-07-24T06:02:16.570" lastattention="2018-07-24T06:01:28.820" clientapp="Microsoft SQL Server Management Studio - Query" hostname="SQLSERVER" hostpid="15904" loginname="DOMAINNAME\USERNAME" isolationlevel="read committed (2)" xactid="2500193" currentdb="6" lockTimeout="4294967295" clientoption1="673319008" clientoption2="390200">
      <executionStack>
        <frame procname="AdventureWorks2014.Purchasing.uPurchaseOrderDetail" line="39" stmtstart="2732" stmtend="3830" sqlhandle="0x0300060004cc856a0ee00a016ba3000000000000000000000000000000000000000000000000000000000000">  UPDATE [Purchasing].[PurchaseOrderHeader]              SET [Purchasing].[PurchaseOrderHeader].[SubTotal] =                   (SELECT SUM([Purchasing].[PurchaseOrderDetail].[LineTotal])                      FROM [Purchasing].[PurchaseOrderDetail]                      WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID]                           = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID])              WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID]                   IN (SELECT inserted.[PurchaseOrderID] FROM inserted    </frame>
        <frame procname="adhoc" line="3" stmtstart="64" stmtend="278" sqlhandle="0x02000000d0c7f31a30fb1ad425c34357fe8ef6326793e7aa0000000000000000000000000000000000000000">  unknown    </frame>
        <frame procname="adhoc" line="3" stmtstart="28" stmtend="250" sqlhandle="0x02000000cac3a70ebbbc20e8360adb397f4986f2ced020140000000000000000000000000000000000000000">  unknown    </frame>
      </executionStack>
      <inputbuf>  Begin Tran    update Purchasing.PurchaseOrderDetail  Set OrderQty = 4  Where ProductID = 448 and        PurchaseOrderID = 1255   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594048348160" dbid="6" objectname="AdventureWorks2014.Purchasing.PurchaseOrderDetail" indexname="PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID" id="lock1a7fea46a00" mode="X" associatedObjectId="72057594048348160">
      <owner-list>
        <owner id="process1a66883d848" mode="X"/>
      </owner-list>
      <waiter-list>
        <waiter id="process1a668c9eca8" mode="U" requestType="wait"/>
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594048413696" dbid="6" objectname="AdventureWorks2014.Purchasing.PurchaseOrderHeader" indexname="PK_PurchaseOrderHeader_PurchaseOrderID" id="lock1a815dda900" mode="X" associatedObjectId="72057594048413696">
      <owner-list>
        <owner id="process1a668c9eca8" mode="X"/>
      </owner-list>
      <waiter-list>
        <waiter id="process1a66883d848" mode="U" requestType="wait"/>
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

The sample AdventureWorks database includes an UPDATE trigger on Purchasing.PurchaseOrderDetail table that contributed to this deadlock. The trigger inserts into Production.TransactionHistory and also updates both Purchasing.PurchaseOrderHeader and Purchasing.PurchaseOrderDetail whenever ProductID, OrderQty, or UnitPrice columns are updated.

In summary, the sequence leading to this deadlock was:

1) connection 1 updated a Purchasing.PurchaseOrderHeader but did not commit

2) connection 2 updated the Purchasing.PurchaseDetail table and the fired trigger was blocked when trying to update Purchasing.PurchaseOrderHeader

3) connection 1 tried to update Purchasing.PurchaseDetail but was blocked by connection

4) SQL Server chose connection 1 as the deadlock victim, allowing connection 2 to autocommit


Performance issues on a MySQL database with 150 million rows Current setup:

A managed vps server
InnoDB storage for big tables
10.0.36-MariaDB
18gb ram
8 x 2.40 GHz CPU.

I have a pretty big MariaDB database table which is used for Business intelligence analytics data. The current table data fluctuates from 150-180 million rows depending on data that is being imported.

The database is filled through a java file that was custom written, and the data is being read through a custom PHP api, through a method that uses the table indexes. The data is date separated and currently the table holds around 2-3 years of data.

My problem is that whenever we see an error with the data we usually need to re-import large volumes of data going back a long time. Meaning tens of thousands of report files that each is being consumed by the java file and imported one by one.

I've done some speed optimisation to the import process, and a single report doesn't take long, unless the report is 300mb or so - then it takes around 10-15 minutes depending on the busyness of the database at that moment.

The daily report import usually has 200-400 files and it takes an hour in the early morning when no-ones in the office. It's all ran by cronjobs and custom report downloading scripts from various sources.

The daily imports and data fetches are pretty fast - however when one wants to make let's say a years data fetch where every day would be visible in the graph for one metric - it can take 10-15 minutes or longer if multiple people are trying to do the same, or if something big is being imported

The data that is being fetched is not a single query, I separate every day as a separate request - that sped up the fetches 10 times or more, depending on how long the time period is.

In a perfect world I'd like to optimize the structure or any settings I could so I could import the data faster and fetch longer timeframe data faster. Our current approach is to have a single database separated for analytics data, with a couple of helper tables.

What kind of optimisation could I do for this amount of data, knowing that we grow around 5-8 million rows per month (that could increase)?

INSERTing one row at a time can be sped up by gathering 100 rows and inserting them in a single batch:  INSERT INTO t (a,b,c) VALUES (1,2,3), (11,22,33), ...  This may speed up things by a factor of 10.
Check for errors after each batch.  If the batch fails, write it to a file for fixing and re-importing.  (This should improve the error-handling pass.)
Assuming the data is only "new" data, it should be easy to summarize the data for each day and write this to a new table.  This may speed up fetching a years's data for graphing by 10x.
The daily 200-400 files might be handled by multiple processes.  Suggest not breaking it into more processes than you have CPUs.

Putting all those together might shrink the elapsed time from hours to minutes.

If you would like to discuss things further, please provide SHOW CREATE TABLE, and maybe some more details on the data and its processing.  What's the value of innodb_buffer_pool_size?


Find filtered indexes on an Oracle table How can I find if an index is filtered?
Filtered indexes (borrowing the term from SQL Server, or known as "partial indexes" in PostgreSQL) can have very high performance when they focus on small subsets of rows (a table that represents a queue of unprocessed orders or messages), or can work as concealed constraints.
For example:
create table product_price (
  product_id int not null,
  price numeric(12, 2) not null,
  since date not null,
  active int not null check (active in (0, 1))
);

Now, to ensure only one price is active per product, we can create the following filtered index:
create unique index i1 on product_price (
  case when active = 1 then product_id end
);

If I try to insert a second active price for the product (4th insert), it fails:
insert into product_price (product_id, price, since, active)
  values (123, 9.99, date '2020-06-01', 0); -- succeeds
insert into product_price (product_id, price, since, active)
  values (123, 10.99, date '2020-12-01', 1); -- succeeds

insert into product_price (product_id, price, since, active)
  values (456, 15.99, date '2020-07-01', 1); -- succeeds
insert into product_price (product_id, price, since, active)
  values (456, 17.99, date '2020-11-01', 1); -- fails as expected
-- ORA-00001: unique constraint (I1) violated

In my real use case I need to identify these concealed constraints. They were created in the DEV and TEST schemas but not in PROD. I need to find the growing bad data in PROD (400+ tables) and decide how to tackle it.

It is incorrect to call functional indexes partial, because the term means something else in Oracle (specifically, indexes on subsets of partitions of partitioned tables).
Also, as you note, creating a unique index, functional or not, does not create a named constraint, and you cannot create a named constraint referencing an expression afterwards. As a result, the only place you can find these "constraints" are the *_INDEXES and *_IND_EXPRESSIONS views:
select i.table_owner, i.table_name, i.index_name, e.column_expression
from dba_indexes i inner join dba_ind_expressions e
on i.index_name = e.index_name and i.owner = e.index_owner
where i.uniqueness = 'UNIQUE' and i.index_type like 'FUNCTION-BASED%'


Speed up COUNT(*) when using an index I have a table similar to this (simplified):

CREATE TABLE books (
    id INT AUTO_INCREMENT,
    category INT NOT NULL,
    PRIMARY KEY (id),
    KEY (category)
);

This table has over 10,000,000 rows, at around 12 categories. So each category has an average of 833,333 books.

When querying for count:

SELECT COUNT(*) FROM books WHERE category=1;

Even though it's using an index when querying, this takes quite a long time to complete (several seconds.) How would you optimize this?

Previously, I had increased a number every time I inserted to books (into a table that relates category -> book count.) But our code is complicated, and many places insert or delete books. I know it's possible to solve this with EVENTS, but I'm asking maybe there's a MySQL feature I missed.

The query will be slow because cardinality of category index is low. There are 12 categories, so in average the query will read 1/12 part of the index. You can't improve this query.

Your original approach can improve overall performance. Just instead of manually updating book_count create a trigger on INSERT and DELETE event.

UPDATE: To prove the query will partially read index category

mysql> select count(*) from books;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

mysql> select category, count(*) from books group by 1;
+----------+----------+
| category | count(*) |
+----------+----------+
|        0 |       50 |
|        1 |       77 |
|        2 |       88 |
|        3 |       84 |
|        4 |      102 |
|        5 |       79 |
|        6 |       79 |
|        7 |       73 |
|        8 |       84 |
|        9 |       76 |
|       10 |       87 |
|       11 |       83 |
|       12 |       38 |
+----------+----------+
13 rows in set (0.01 sec)

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from books where category = 6;
+----------+
| count(*) |
+----------+
|       79 |
+----------+
1 row in set (0.00 sec)

mysql> show status like 'Hand%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 79    |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
18 rows in set (0.01 sec)


AdventureWorks2012: "The FILE SIZE property is incorrect" error while trying to attach sample database "Adventure Works for SQL Server 2012" I am trying to attach the sample databases, for example, AdventureWorks2012_Data.mdf from codeplex "Adventure Works for SQL Server 2012" into a MS SQL Server 2012 (default) instances on a a virtual machines under MS Windows Server 2008 and MS Windows 7.

Each time I am getting the error:

  The header for file 'C:\Program Files\Microsoft SQL
  Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Data.mdf' is
  not a valid database file header. The FILE SIZE property is incorrect.
  (.Net SqlClient Data Provider)

  ------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.3128&EvtSrc=MSSQLServer&EvtID=5172&LinkId=20476

What can be done to attach the MS SQL Server 2012 sample databases?

Tried to install these sample databases ак the script(s), also always getting the error, the different one, about which I shall ask in a separate question.

Try downloading from AdventureWorks Databases – 2012, 2008R2 and 2008.

This might be due to corrupt installs.


Getting the last executed command time I have tables stored in a Trucrypt partition and I would like to disconnect that if the users not using the mysql. There is the INFORMATION_SCHEMA.PROCESSLIST but I would like to know the users last activation time. For example, if nobody run a mysql command within 10 minutes, then I can unmount the Trucrypt. How can I check that?

If the last executed command is something that updates a table you could check:

SELECT COUNT(1) TablesAccessedInLast10Min
FROM information_schema.tables
WHERE update_time >= (NOW() - INTERVAL 10 MINUTE);

If you cannot login to MySQL, binary logging is enabled, and the binary logs are located in /var/lib/mysql, you could examine the time stamp of thew last binary log:

cd /var/lib/mysql
ls -l mysql-bin.0* | tail -1

If you want to monitor any movement as far as logging in, you may have to check out the general log. In mysql 5.1.38, I think you can convert it to a MyISAM table. You will have to index it on the datetime. There is a field called user_host. You could examine that field within the last 10 minutes.


Compatibility level for published database and transactional replication I am running a database on compatibility level 80 because the application using it does not yet support a higher level.

It is my understanding that transactional replication on SQL Server 2005 and up has some new features like schema changes replication and improved schema locking.

Can I benefit from these new features if my database is on a lower compatibility level?

In other words, do these features depend on the compatibility level of the published database or does this rely only on the version of the server?  I know merge replication has a publication compatibility level (independent from database compatibility level) but I can't find a clear answer about transactional replication.

Do these features depend on the compatibility level of the published database or does this rely only on the version of the server?

The short answer is Yes they do depend but to a certain extent only.

Compatibility level can have an affect on the publishing and subscribing databases, with respect to object ownership and the existence (or not) of a particular schema.

Also, have a look at sp_vupgrade_replication

From BOL:

  SQL Server 2000 supports common schema changes to an existing publication database. You can add columns to, and drop columns from, a published table without dropping and recreating the publications and subscriptions referencing that table.
  Schema changes can be replicated during snapshot replication, transactional replication, and merge replication. Column additions and deletions are implemented at the table level and propagated to all Subscribers that receive data from that table.

  Important:  Schema changes to a published table must be made only through the replication publication properties dialog box in SQL Server Enterprise Manager or through replication stored procedures. Do not make schema changes to published tables using the SQL ALTER TABLE statements in a tool such as SQL Query Analyzer or by using SQL Server Enterprise Manager visual database tools. Changes made to the schema of a published table using these tools will not be propagated to Subscribers.

Refer to :

Compatibility Levels and their effects.
Using Multiple Versions of SQL Server in a Replication Topology


Deleting multiple tables in a single delete query in Mysql I have a task to delete records from multiple tables (say 4 or more). I came to know that I can do the delete in a single MySQL query as follows

DELETE table1,table2,table3,table4
  FROM table1
  LEFT JOIN table2 ON table1.pk=table2.pk
  LEFT JOIN table3 ON table3.pk=table2.pk
  LEFT JOIN table4 ON table4.pk=table3.pk
 WHERE table1.pk IN (101,102,106,...)

How does it works internally? Any other performance caveats compared to spliting the query into 4 separate delete queries?

How does it works internally?

I assume that it performs the JOINs as if you were SELECTing data out of those tables, nothing which rows matched, and then removing them.

  Any other performance caveats compared to spliting the query into 4 separate delete queries?

That depends on what you intend. If you are otherwise doing:

DELETE FROM table1 WHERE pk IN (101,102,106,...)
DELETE FROM table2 WHERE pk IN (101,102,106,...)
DELETE FROM table3 WHERE pk IN (101,102,106,...)
DELETE FROM table4 WHERE pk IN (101,102,106,...)

the joining of data may well make the process slower. If you need to join to only delete rows that exist in all four table (see below) then this will be faster as that operation only needs to be done once.

  Any other performance caveats compared to spliting the query into 4 separate delete queries?

A very important consideration here is not standard SQL syntax that will be usable elsewhere. It will limit portability and may confuse future maintainers of the code, so I would not recommend it.

Also, it may not be doing what you actually intend. The JOINs there will enforce only deleting rows that match in the first table, so if pk=102 exists in tables 2, 3, and 4 but not in 1, it will not be deleted from the three it does exist in. Unless your sample query is incorrect: if table 1 contains parent entities for entities in the other tables, and so forth, therefore you are matching a foreign key to a primary key, like so:

DELETE table1,table2,table3,table4
  FROM table1
  LEFT JOIN table2 ON table1.pk=table2.fkReferecingTable1
  LEFT JOIN table3 ON table3.pk=table2.fkReferecingTable1
  LEFT JOIN table4 ON table4.pk=table3.fkReferecingTable1
WHERE table1.pk IN (101,102,106,...)

If this is the case then you might want to consider defining the foreign keys with ON DELETE CASCADE in which case you could simply execute

DELETE table1 FROM table1 WHERE table1.pk IN (101,102,106,...)

and let the database engine handle the rest. I say "might want to consider" because IMO ON DELETE CASCADE can be rather dangerous, so I usually avoid it unless it is the only efficient way.


Oracle sort order changed We recently deployed a new environment with a newer version of Oracle (12c) instead of 11r2.

Only recently I noticed my data returned is sorted differently.

Instead of having small letters, capital letters and numbers... (on Oracle 11r2)
I get my data sorted the opposite way:
numbers, capital letters, small letters (Oracle 12c)

This is realy confusing the end-user of various applications querying the database. What settings do I need to check/compare to fix this problem in Oracle 12c?

The queries I refer to have an order by clause.
EDIT
The settings on for these parameters are the same on both evironments:

NLS_SORT
type: string
value:

nls_language
type: string
value: DUTCH

So there is no value for NLS_SORT, what is the default then?

@phil

select * from NLS_INSTANCE_PARAMETERS;

Parameter                     Oracle11r2          Oracle12c
NLS_LANGUAGE                       DUTCH              DUTCH
NLS_TERRITORY            THE NETHERLANDS    THE NETHERLANDS
NLS_SORT
NLS_DATE_LANGUAGE
NLS_DATE_FORMAT
NLS_CURRENCY
NLS_NUMERIC_CHARACTERS
NLS_ISO_CURRENCY
NLS_CALENDAR
NLS_TIME_FORMAT
NLS_TIMESTAMP_FORMAT
NLS_TIME_TZ_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_DUAL_CURRENCY
NLS_COMP                          BINARY           BINARY
NLS_LENGTH_SEMANTICS                BYTE             CHAR
NLS_NCHAR_CONV_EXCP                FALSE            FALSE

select * from NLS_SESSION_PARAMETERS;

these are the same as my NLS settings in Oracle SQL developer, so not very relevant I guess...

select * from NLS_DATABASE_PARAMETERS;

These are the same, only NLS_RDBMS_VERSION differs

So only NLS_LENGTH_SEMANTICS differs. Is there a way to check the values for other sessions?

NLS_LANGUAGE determines the sort order. If you've went from, for example,  FRENCH to the defaut, AMERICAN, you would get the difference in sort order you describe. To set the default NLS_LANGUAGE execute

alter system set nls_language ='FRENCH' scope=spfile;

and bounce the instance. You can always override this in the session using

alter session set nls_language = 'FRENCH';

To bounce the instance is to restart it. You need to do that when changing some system parameters like NLS_LANGUAGE. If you only change the parameter in the current session you don't need to restart the instance.

To check the parameter values you can, like Phil said earlier, query NLS_DATABASE_PARAMETERS, NLS_INSTANCE_PARAMETERS, and NLS_SESSION_PARAMETERS. To check the setting in the current session you can also run show parameter nls_language.


Most efficient and practical way to store time values (HH:MM) in Oracle that makes it easily searchable I have a set of starting and ending times that I need to store in an Oracle database.  I need to store them in a manner that makes them easily searchable (i.e. a typical search is find all rows where a time value, such as 9:30AM, falls in between the start time and end time).  In SQL Server I would just use a TIME datatype field, but Oracle does not appear to have an equivalent.

I have seen a number of blogs and forums that recommend just using a DATE field and doing conversions with TO_CHAR then comparing, or to store the time values as varchar(4 / HHMM) or varchar(6 / HHMMSS) fields.  Both of these seem needlessly inefficient.

Is there a better or more efficient way to accomplish this in Oracle?

If you've no need to store the date component, then an interval day(0) to second is the most practical approach. The biggest advantage comes if you need to do any calculations finding how long has elapsed between two times as you don't need to do any extra processing out of the box.

For example:

create table times ( t1 interval day(0) to second, t2 interval day(0) to second);

insert into times values ( interval '0 09:30:00' day(0) to second, interval '0 12:30:00' day(0) to second);
insert into times values ( interval '0 10:30:00' day(0) to second, interval '0 13:45:00' day(0) to second);
insert into times values ( interval '0 11:30:00' day(0) to second, interval '0 12:05:01' day(0) to second);

commit;

SELECT * FROM times
where  t1 between interval '0 10:00:00' day to second and interval '0 11:00:00' day to second ;

T1          T2
----------- -----------
0 10:30:0   0 13:45:0

SELECT max(t2-t1) FROM times;

MAX(T2-T1)
-----------
0 3:15:0.0

You also have standard functions to extract the various components of the time (hour, minute, etc.):

SELECT extract(hour from t1) hr, extract(minute from t1) mn, extract(second from t1) sec
FROM times;

        HR         MN        SEC
---------- ---------- ----------
         9         30          0
        10         30          0
        11         30          0


Optimizing many to many sql query I have two tables with many to many relationship, which I join on `userId and then sum/count values so I would get the summary of them. This is how it looks:
            select COUNT(DISTINCT [s].[UserId]) AS NumberOfCustomers,
            sum(s.[ProbabilityAlive]) as ProbabilityAlive,
            avg([s].[ExpectedMonetaryValue]) as ExpectedMonetaryValue, sum(ExpectedTransactions) as ExpectedTransactions,
            sum(s.[CustomerLifetimeValue]) as CustomerLifetimeValue, sum(s.[ResidualLifeTimeValue]) as ResidualLifetimeValue,
            sum(s.[Transactions]) as Transactions, sum(s.[TotalSpend]) as TotalSpend,
            sum(s.[DeltaProbabilityAlive]) as DeltaProbabilityAlive, sum(s.[ProbabilityActive]) as ProbabilityActive,
            sum(s.[DeltaProbabilityActive]) as DeltaProbabilityActive,
            sum(s.[DeltaEstimatedNumberOfTransactionsShort]) as DeltaEstimatedNumberOfTransactionsShort, sum(s.[EstimatedNumberOfTransactionsLong]) as EstimatedNumberOfTransactionsLong,
            sum(s.[DeltaEstimatedNumberOfTransactionsLong]) as DeltaEstimatedNumberOfTransactionsLong, sum(s.[EstimatedNumberOfTransactionsLongLeft]) as EstimatedNumberOfTransactionsLongLeft,
            sum(s.[ResidualLifeTimeValueLeft]) as ResidualLifeTimeValueLeft, sum(s.[CustomerLifetimeValueLeft]) as CustomerLifetimeValueLeft,
            SUM(a.PeakClv) as PeakClv
            FROM (
            SELECT DISTINCT([Clv].[UserId]), sum([dbo].[Clv].[ProbabilityAlive]) as ProbabilityAlive,
            avg([Clv].[ExpectedMonetaryValue]) as ExpectedMonetaryValue, sum([dbo].[Clv].[EstimatedNumberOfTransactionsShort]) as ExpectedTransactions,
            sum([Clv].[CustomerLifetimeValue]) as CustomerLifetimeValue, sum([dbo].[Clv].[ResidualLifeTimeValue]) as ResidualLifetimeValue,
            sum([dbo].[Clv].[NumberOfTransactions]) as Transactions, sum([dbo].[Clv].[TotalSpend]) as TotalSpend,
            sum([dbo].[Clv].[DeltaProbabilityAlive]) as DeltaProbabilityAlive, sum([dbo].[Clv].[ProbabilityActive]) as ProbabilityActive,
            sum([dbo].[Clv].[DeltaProbabilityActive]) as DeltaProbabilityActive,
            sum([dbo].[Clv].[DeltaEstimatedNumberOfTransactionsShort]) as DeltaEstimatedNumberOfTransactionsShort, sum([dbo].[Clv].[EstimatedNumberOfTransactionsLong]) as EstimatedNumberOfTransactionsLong,
            sum([dbo].[Clv].[DeltaEstimatedNumberOfTransactionsLong]) as DeltaEstimatedNumberOfTransactionsLong, sum([dbo].[Clv].[EstimatedNumberOfTransactionsLongLeft]) as EstimatedNumberOfTransactionsLongLeft,
            sum([dbo].[Clv].[ResidualLifeTimeValueLeft]) as ResidualLifeTimeValueLeft, sum([dbo].[Clv].[CustomerLifetimeValueLeft]) as CustomerLifetimeValueLeft
            FROM [Clv]  WHERE Clv.[ClientId] = '2' AND   Clv.[CalculationDate] = '02/28/2021' AND  Clv.[Product] = 'Total'  GROUP BY UserId ) s
            LEFT JOIN (
                SELECT  DISTINCT(UserId), SUM([PeakClv]) as PeakClv
                FROM [dbo].[AdditionalClvData]
                WHERE ClientId = '2' AND CalculationDate = '02/28/2021'
                GROUP BY [UserId]
            ) a ON a.[UserId] = s.[UserId]

I take almost all values from one table, and one from the other and use pre-aggregating in subqueries. Select from single table takes couple of seconds, however this query takes around 20seconds. Am I doing joining/aggregating incorrectly? Is there a way to optimise so it would work faster?

Firstly, you can remove the DISTINCT operator from each subquery - you're grouping by UserId and aggregating all other columns, so DISTINCT isn't doing anything:- You will always get a single row per distinct UserId value because of the grouping.
Secondly, because of this 'single row per UserId' behaviour courtesy of the grouping, you should actually use an INNER JOIN. You will always return a single row per UserId in both subqueries, so you've effectively turned your many-to-many into a one-to-one by grouping on the key column used to link the two tables.
Thirdly, you can potentially remove the subqueries altogether. Test the results and performance, but the below query should return the same results and should be quicker:
SELECT
    s.[UserId]                                       AS UserId                                 ,
    SUM(s.[ProbabilityAlive])                        AS ProbabilityAlive                       ,
    AVG(s.[ExpectedMonetaryValue])                   AS ExpectedMonetaryValue                  ,
    SUM(s.[EstimatedNumberOfTransactionsShort])      AS ExpectedTransactions                   ,
    SUM(s.[CustomerLifetimeValue])                   AS CustomerLifetimeValue                  ,
    SUM(s.[ResidualLifeTimeValue])                   AS ResidualLifetimeValue                  ,
    SUM(s.[NumberOfTransactions])                    AS Transactions                           ,
    SUM(s.[TotalSpend])                              AS TotalSpend                             ,
    SUM(s.[DeltaProbabilityAlive])                   AS DeltaProbabilityAlive                  ,
    SUM(s.[ProbabilityActive])                       AS ProbabilityActive                      ,
    SUM(s.[DeltaProbabilityActive])                  AS DeltaProbabilityActive                 ,
    SUM(s.[DeltaEstimatedNumberOfTransactionsShort]) AS DeltaEstimatedNumberOfTransactionsShort,
    SUM(s.[EstimatedNumberOfTransactionsLong])       AS EstimatedNumberOfTransactionsLong      ,
    SUM(s.[DeltaEstimatedNumberOfTransactionsLong])  AS DeltaEstimatedNumberOfTransactionsLong ,
    SUM(s.[EstimatedNumberOfTransactionsLongLeft])   AS EstimatedNumberOfTransactionsLongLeft  ,
    SUM(s.[ResidualLifeTimeValueLeft])               AS ResidualLifeTimeValueLeft              ,
    SUM(s.[CustomerLifetimeValueLeft])               AS CustomerLifetimeValueLeft              ,
    SUM([a.PeakClv])                                 AS PeakClv
FROM [Clv] s
LEFT JOIN [AdditionalClvData] a ON a.[UserId] = s.[UserId]
WHERE
    s.[ClientId]            = '2'
    AND s.[CalculationDate] = '02/28/2021'
    AND s.[Product]         = 'Total'
    AND a.[ClientId]        = '2'
    AND a.[CalculationDate] = '02/28/2021'
GROUP BY
    s.[UserId]


mysql - import only schema from full dump? I have a full database export (single file, schama + data), but I would like to import only the database schema. Is there any magic option or trick to achieve this?
I'm NOT able to perform a "mysql_dump --no-data" at the original database at this moment. Is removing all inserts and updates manual (awk/grep/vim) from the dump file the only option?

A mysqldump is only inserts, not updates. So grep -v "^INSERT" dump.sql should be sufficient. Take a look before using


How do I count and order by unique entries? I have this:

SELECT tblClient.[Printed Name], tblClient.[Address 1]
     , tblClient.[Address 2], tblClient.City, tblClient.State
     , tblClient.Zip, tblClient.Phone, tblClient.[Email Address]
FROM tblClient
INNER JOIN tblCurrent
    ON tblClient.[Client Code] = tblCurrent.Client
GROUP BY tblClient.[Printed Name], tblClient.[Address 1]
       , tblClient.[Address 2], tblClient.City, tblClient.State
       , tblClient.Zip, tblClient.Phone, tblClient.[Email Address]
       , tblCurrent.[Date Ordered]
HAVING (((tblCurrent.[Date Ordered])>#6/27/2013#))
ORDER BY tblClient.[Printed Name];

Now I need to get a count and sort by the number of times each unique "Printed Name" shows up.

I've looked all over but can't figure this out from other questions people have asked. I'm not usually the one at my company that has to mess with Access. It seems like this is probably a simple problem, but I'm noobier than noob.

Any help would be appreciated.

Try this

SELECT
    tblClient.[Printed Name],
    tblClient.[Address 1],
    tblClient.[Address 2],
    tblClient.City,
    tblClient.State,
    tblClient.Zip,
    tblClient.Phone,
    tblClient.[Email Address],
   (select count(*)
    from tblCurrent
    where tblCurrent.[Date Ordered]>#6/27/2013#
      and tblClient.[Client Code] = tblCurrent.Client
   ) AS Qty
FROM tblClient
ORDER BY Qty desc;


Adding a column to aggregated data where new value only applies to a subset of the aggregation Having trouble explaining this.

I have a table like this:

Label | Value
A     |   3
B     |   1
C     |   4

And another like this:

Label | Value | ID
A     | 1     | ID1
A     | 1     | ID2
C     | 4     | ID3

I need to add the ID column to the first table, splitting some rows according to the Value in the second table. Resulting in this:

Label | Value | ID
A     | 1     | ID1
A     | 1     | ID2
A     | 1     | NULL
B     | 1     | NULL
C     | 4     | ID3

If the Label and Value columns were identical for both tables, I could easily do join table2 ON table1.Label=table2.Label and table1.Value=table2.Value but the unaggregation aspect of this is throwing me off.

Apologies if this is worded poorly.

SELECT  `Label`, `Value`, ID
    FROM  table1
    LEFT JOIN  table2 USING(Label, Value);


What are the implications of upgrading statistics SQL Server I Have a SQL Server 2005 DB, with 500Gb of Data. The Statics do not upgrade from 2010 and this past saturday were updated. Yesterday the querys on DB were very slow and caused bad response times.

Nothing more change in the database since Saturday, so I think it may be the work in the statistics.

Tell me about it?

Thanks

Tell me about it?

You are speculating. I recommend that instead you investigate. Read How to analyse SQL Server performance. Find the bottlenecks, address them appropriately, based on your findings. Measure before you cut.


How do I stamp nothing? I get an LDAP feed in for my employee database - basically a big csv file.  This updates once a night and I revise all of our internal apps based on this spreadsheet.

When a new person comes on the feed I have a trigger in my table that fires and spits out a "created" field so that I can run queries based on when the employee row entered our system.

My question is, is there a way to trigger when nothing is queried?  I would like to date (time) stamp a user row based on that row no longer being on the feed.  But also I don't want that overwritten every night since I would like my "left_date" field to be accurate.

Short answer would probably be no to the actual question. That being said it should be a fairly simple thing to fix.

1) Have your process bulk insert the CSV file to the same table every night after a TRUNCATE.

2) Do a LEFT JOIN to find with a null against your employee table to find the new ones and insert them.

3) Do a right join with null against your employee table to find the "users no longer on the feed" and update their "left_date" field.

If you need to run these updates manually (sounds like it from your question) you could still have the SQL just create the statements and so you could review and run them manually.


Is there any SQL Database that will use UDP replication? I have to find a database that will ensure the UDP replication intercontinental.

Do you know if there is any SQL database that will support UDP replication?
If not, can I use MySQL replication (TCP/IP protocol) intercontinental (from China to Switzerland)? Will this work fine? Won't be any meltdowns? How can I prevent them?
The master will be in China and the slave in Switzerland. If the slave will be down for a long period of time, and the logs used to update the replications will be too big or not enough, how can I handle the replication? What is the solution?

Thank you and kind regards,

Andreea

Years ago, I was DBA for a system that was replicating from USA to China (many years ago).  An earthquake happened in the Pacific, disrupting most traffic to/from east Asia.  Connections were flaky for most of a day, by which time traffic had been rerouted (I guess) through the Suez Canal instead of the Pacific Ocean.  Slave connections came and went, but MySQL replication survived.

"UDP is faster" -- I suggest that this is an 'uninformed' reason.  MySQL uses TCP/IP because it needs to know that the packets arrive at the other end.  With UDP, replication can break due to network issues, and MySQL would not know it.  That leads to a much worse mess than being "slower".

CH:CN might be about 250ms -- that is network travel time.  One packet will take that long, whether you are using TCP/IP or UDP.  TCP/IP will seem slower because it will send only a certain number of packets before waiting for ACK/NAK.  That can (and should) probably be tuned due to the long distance.

Once you have done that low level tuning (at the OS, not MySQL), TCP/IP will be about as fast as UDP.  But reliability is the real reason to tell your client to jump in a lake.  (Sorry, I don't know the OS tunables.)

As for Replication being "down for a long time"...  (I assume you mean hours or days.)  Yes, you should plan for such.  Set expire_logs_days to a high enough number.  I like 14; that's days.)  Plus have monitoring to see if things have gone bad.  UDP can't help with the network being down.


Question about Dumps in Oracle (REMAP_SCHEMA) To export a dumpfile, I can:

EXPDP system/Password@TNS
DUMPFILE=FileName.dmp
Schemas=TheSchemaIWantToDump
DIRECTORY=dumpdir

So, let's say I want to restore this DMP file, but with a different name (I'm a SQL Server Dba, and I'm still learning some Oracle). I would do this:

1)create a new user

2)GRANT CONNECT to this user

3)

IMPDP System/Password@TNS
dumpfile=ThefileName.dmp
REMAP_SCHEMA=OldUser:NewUser
directory=dumpdir

My question is about REMAP_SCHEMA.When I'm restoring schemas inside my own server, I know the OldUser , so I can fill it, and then :NewUser.

But if I'm sending this dump file to our client. Should I mention this OldSchema?  They don't know the schema's name I'm using before the dump. Because on SQL Server I can RESTORE DATABASE , then name with the name a want FROM DISK....

How can I restore a schema with a different name?

Thanks.

Even if not told, OldUser can be easily found as:

IMPDP System/Password@TNS
dumpfile=ThefileName.dmp
directory=dumpdir
sqlfile=ThefileName.sql
include=user

This does not import anything, just creates ThefileName.sql under dumpdir with the SQL statements used for creating OldUser, including the username as well of course. Using that information, they will know what to specify in REMAP_SCHEMA=OldUser:NewUser.


Standard Edition High Availability without Shared Storage I'm a software dev with little experience managing SQL Server so please bear with me.

We are developing a piece of software that needs to be highly available, with a significant degree of resilience. This application will use SQL Server to store much of its operational data.

This application will be running from 2 separate physical locations and no shared disk (SANs or whatnot) is allowed between these 2 locations, shared SMB is a possibility but not well liked by our security people.

This application won't commit loads of data to SQL Server, but there will be 26 instances of this system deployed in the real world, 52 SQL Server instances altogether so to cut costs we are pretty sure SQL standard is as expensive as we can afford.

What are my options, since Database Mirroring seems to be being obsoleted in the not too distant future?

EDIT We have the possibility for a SAMBA shared to be replicated between our sites using DFS-R, does this open up any windows? With SQL 2014 STD, you can set up AlwaysOn for 2 nodes is this a viable approach?

As per this BOL reference, here are the only options you would have with Standard edition:

Log Shipping
Database Mirroring (sync/safety only)
2-node Failover Cluster Instance

Because you don't want to use shared storage between different participants, that would automatically rule out the failover cluster instance, leaving you to choose between log shipping and database mirroring.

Those are two very different solutions, with two very different needs driving them.  What are your requirements for failover?  Reporting?  How many copies of the data do you want?  Until those questions are answered, you wouldn't be able to choose between these two rock-solid solutions.

EDIT

I must have completely missed your requirements in your question, but with Paul's excellent edit and graphic there, it seems as though a lot of the requirements are already there (although you don't seem to point out reporting or warm data, so I'll assume that's not necessary).

Physical Separation
You could get yourself into trouble here with database mirroring if you're talking about cross-datacenter or geographic separation type of distance, as the restriction for only sync/safety mode could introduce performance implications.

Failover Tolerant Application
Unless you have the logic in your application, log shipping wouldn't be able to help you here.  With database mirroring, depending on your provider and capabilities you could specify the failover partner though.

Failover (preferably automatic)
This an easy one.  Log shipping doesn't have this capability, you'd have to manually intervene.  Database mirroring would be able to fulfill this.

No Shared Storage
Both of these solutions do not require shared storage.

Must work with EF
I don't see why EF would have a problem with database mirroring, but I couldn't say for sure on this without doing more EF research.

SQL Server 2014 Standard
If you mean Standard edition, that is answered above.  If you mean "standard" as in "current", then database mirroring is currently deprecated for what it's worth.


Data-Driven Subscription - how to iterate through all parameter values? I am trying to build a data-driven subscription in SSRS (In SharePoint integrated mode, but I believe that's irrelevant). The rsds file I am running against has a certain parameter that is a hard-coded list of about 20 values. In the subscription, I need to run the report and generate a separate Excel file for each of the 20 values. Where do I assign those those values from within the subscription definition? I can specify a query but don't see where to point to the parameter list that is hard-coded inside the report file. Can't I just point to the list and have this run 20 different files automatically or do I need a separate subscription for each value?

You need to put those 20 hard-coded values into a table, instead, and then you can refer to that in your data-driven subscription. That will also make it easier to maintain because you can now just add or remove records from that table instead of having to change and re-deploy the entire report.


Creating a table with Rank ordering in Oracle I am trying to create a table in Oracle as follow:
create table hops.t_distribution AS
(select
UID,
CONTACT_EMAIL,
PRODUCT_ID,
rank() over(Partition by UID order by to_number(PRODUCT_ID)) as PRIORITY_ORDER
from hops.staging_test);

hops.staging_test is basically the same table without the PRIORITY_ORDER column.
However, I am getting an ORA error because you can't order by in a create table statement. Is there another clever way to do this?
Thank you!

The way you put it, it is not that ORDER BY causes problems, but the fact that UID is a reserved word and can't be used as a column name:
SQL> create table test (uid number);
create table test (uid number)
                   *
ERROR at line 1:
ORA-00904: : invalid identifier

But, if you enclosed it into double quotes while creating the table, then yes - it'll "work" (but - do you really want to do that? I wouldn't):
SQL> create table test ("uid" number);

Table created.

SQL>

So: presuming that this is the case, then enclose it into double quotes while running your query. Pay attention to letter case! You MUST match it every time you use that column. Presuming it is in upper case, then you'd
SQL> create table t_distribution as
  2    select "UID", contact_email, product_id,
  3           rank() over (partition by "UID" order by product_id)
  4             priority_order
  5    from staging_test;

Table created.


How do I make a citation table where work can't refer to itself? CREATE TABLE Work(id INT NOT NULL AUTO_INCREMENT,
    title CHAR(20),
    date DATETIME,
    description VARCHAR(1000),
    CONSTRAINT Work_PK PRIMARY KEY (id),
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- In the following, do we have to worry about work_1 being = to work_2, how can we prevent this ?

CREATE TABLE Work_Citation(id INT NOT NULL AUTO_INCREMENT,
                       work_1 INT NOT NULL,
                       work_2 INT NOT NULL,
                       CONSTRAINT Work_Citation_PK_1 PRIMARY KEY (id),
                       CONSTRAINT Work_Citation_PK_2 UNIQUE (work_1,work_2),
                       CONSTRAINT Work_Citation_FK_1 FOREIGN KEY (work_1) REFERENCES Work(id),
                       CONSTRAINT Work_Citation_FK_2 FOREIGN KEY (work_2) REFERENCES Work(id)) ENGINE=InnoDB DEFAULT  CHARSET=utf8;

-- A second query, can I do without the id column in Work_Citation? Any comments on when we should / should not use it?

you can use check constraint, add this to table definition:

CONSTRAINT check_work_ids_are_not_equal CHECK work_1 <> work_2

yes, you can create PK on (work_1, work_2)
it has a sense to add index on Work_citation(work_2). This way you will not need to scan the whole Work_Citation table when delete rows from Work table to check that the are no referencing rows


SELECT TOP 1 from a very large table on an index column is very slow, but not with reverse order ("desc") We have a large database, about 1TB, running SQL Server 2014 on a powerful server. Everything worked fine for a few years.  About 2 weeks ago, we did a full maintenance, which included: Install all software updates; rebuild all indexes and compact DB files. However, we did not expect that at certain stage the DB's CPU usage increased by over 100% to 150% when the actual load was the same.

After a lot of troubleshooting, we have narrowed it down to a very simple query, but we could not find a solution. The query is extremely simple:

select top 1 EventID from EventLog with (nolock) order by EventID

It always takes about 1.5 seconds! However, a similar query with "desc" always takes about 0 ms:

select top 1 EventID from EventLog with (nolock) order by EventID desc

PTable has about 500 million rows; EventID is the primary clustered index column (ordered ASC) with the data type of bigint (Identity column). There are multiple threads inserting data into the table at the top (larger EventIDs), and there is 1 thread deleting data from the bottom (smaller EventIDs).

In SMSS, we verified that the two queries always use the same execution plan:

Clustered index scan;
Estimated and actual row numbers are both 1;
Estimated and actual number of executions are both 1;
Estimate I/O cost is 8500 (Seems to be high)
If run consecutively, the Query cost is the same 50% for both.

I updated index statistics with fullscan, the problem persisted; I rebuilt the index again, and the problem seemed to be gone for half a day, but came back.

I turned on IO statistics with:

set statistics io on

then ran the two queries consecutively and found the following info:

(For the first query, the slow one)

  Table 'PTable'. Scan count 1, logical reads 407670, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(For the second query, the fast one)

  Table 'PTable'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Note the huge difference in logical reads. The index is used in both cases.

Index fragmentation could explain a little bit, but I believe the impact is very small; and the problem never happened before. Another proof is if I run a query like:

select * from EventLog with (nolock) where EventID=xxxx

Even if I set xxxx to the smallest EventIDs in the table, the query is always lightning fast.

We checked and there is no locking/blocking issue.

Note: I just tried to simplify the issue above. The "PTable" is actually "EventLog"; the PID is EventID.

I get the same result testing without the NOLOCK hint.

Can anybody help?

More detailed query execution plans in XML as follows:

https://www.brentozar.com/pastetheplan/?id=SJ3eiVnob

https://www.brentozar.com/pastetheplan/?id=r1rOjVhoZ

I don't think it matters to provide the create table statement. It is an old database and has been running perfectly fine for a long time until the maintenance.  We have done a lot of research ourselves and narrowed it down to the info provided in my question.

The table was created normally with the EventID column as the primary key, which is an identity column of type bigint. At this time, I guess the problem is with the index fragmentation. Right after index rebuild, the problem seemed to be gone for half a day; but why it came back so quickly...?

The Clustered Index Scan shows 423,723 logical reads to return the first row, taking 1926 ms:

This seems rather a lot to locate the first row in index order.
Most likely your ghost cleanup task is running a long way behind, or has stopped. You should check the ghost_record_count for the clustered index in sys.dm_db_index_physical_stats and monitor changes over time.
The ordered scan from the end of the index that is seeing constant delete activity has to scan over an awful lot of ghosted records before it finds the first 'alive' row to return. This explains the extra logical reads. A seek down the b-tree to the lowest value of the index will encounter much fewer ghosted records.
Another performance-affecting factor is that the scan itself becomes responsible for removing the ghost records as mentioned in Inside the Storage Engine: Ghost cleanup in depth by Paul Randal.
You should check that trace flag 661 (disable ghost cleanup) is not active.
Solutions

You may find that running sp_clean_db_free_space provides relief.
Changing the process that deletes rows from that end of the index to use a PAGLOCK hint would enable ghost cleanup on the spot, which could well solve the problem as well.

If the ghost cleanup process has stopped completely, the most effective solution is normally to restart the SQL Server instance. You should also ensure that the SQL Server is running one of the latest Cumulative Updates. There have been many ghost cleanup bugs over the years.
In your specific case:

It turned out the problem was caused by another test database on the same server. That test database was restored with "data loss", and is corrupt. Surprisingly, the ghost cleanup process apparently was stuck in that database. Once we deleted that corrupted database from SMSS, the problem resolved by itself (took a long time and might have caused DB to lockup for a short while).


SQL Server 2008 - Return records in chronological order, given a separation factor This question was originally posted here https://stackoverflow.com/questions/8492149/return-records-in-chronological-order-given-a-separation-factor and re-posted on this forum given a user's advice.

I have a massive table in SQL Server 2008, it contains the position reported by technicians every minute. I need to report on this table but in order to control the amount of records that are displayed in the report both a time and distance separation factors need to be taken into account.

So, a query may look like : "Return all records with no less than 5 minutes and/or 300 feet between them".

The time part is done, but I'm having a hard time with the distance factor. I have the latitude and longitude for each point, and I have no problem if I need to include a SQL Server 2008 spatial UDT in order to resolve the problem.

Things I have considered:

Bring the records by the time factor, and apply the separation constrain in the client by calculating the distance between adjacent points and discard those which falls inside the the factor. (the easiest, but it must be the one consuming more resources).
Keep the last record per technician in a cache, pre-calculate the distance between the record and its predecessor, and resolve the constrain in the client. (should consume less resources than 1) since the distance is pre-calculated, however and since the table is BIG It will increase the size of the dataset, not sure if the space is worth the processing savings).
Use the spatial functions in SQL Server 2008, but honestly I had been reading and I couldn't find anything that helps me resolve this type of requirement. Any GIS expert??

I would like to go with the best option possible (maybe not listed above?) and IMO should be the one using the SQL Server features most efficiently.

Prior to SQL 2008, the most common solution was to use a UDF to calculate the great-circle distance between two points on a sphere. The Haversine formula is probably the most commonly used method.

Of course the Earth is not actually a perfect sphere, but this was considered "good enough" for most uses.

In SQL 2008, as you anticipated, such calculations are simplified and made more accurate by the introduction of the Geography and Geometry data types. Here's a brief sample of how you can use them to simplify distance calculations.

DECLARE @locations TABLE(locname VARCHAR(100), coord geography)
DECLARE @loc1 geography
DECLARE @loc2 geography

INSERT INTO @locations
VALUES('HOME', geography::Point(-81.810194, 41.478156, 4326))   --Note: Lat, Long, SRID
                                                                --The 4326 is the SRID (spatial reference id) used by SQL as
                                                                --a reference to the WGS 84 Standard. This is the same reference
                                                                --used by the GPS system
INSERT INTO @locations
VALUES('WORK', geography::Point(-81.687771, 41.498227, 4326))

SELECT * FROM @locations

SELECT @loc1 = coord FROM @locations WHERE locname = 'HOME'
SELECT @loc2 = coord FROM @locations WHERE locname = 'WORK'

SELECT @loc1.STDistance(@loc2) * 3.2808399  --STDistance is in meters so we multiply to convert to feet

SELECT @loc1.STIntersects(@loc2.STBuffer(300 / 3.2808399)) as isWithin300Ft  --This formula returns True when the point @loc1 intersects with the 300ft buffer zone around @loc2

The SRID is the key to the improved accuracy. The WGS 84 specification to which it refers includes a standardized coordinate system and a reference ellipsoid.  In other words, it accounts for the non-spherical nature of the Earth, giving better results than a pure spherical Great Circle calculation.

If GIS accuracy is important to your work, this is the simplest way to implement it in SQL 2008.


How can I prevent mysqldump from prepending the database name in CREATE VIEW? When I execute the following:

mysqldump -u root -n mydatabase > mydatabase.sql

The tables are all dumped without the database name, but the views oddly get the database name prepended:

/*!50001 DROP TABLE IF EXISTS `vfiscalperiodcombobox`*/;
/*!50001 DROP VIEW IF EXISTS `vfiscalperiodcombobox`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8 */;
/*!50001 SET character_set_results     = utf8 */;
/*!50001 SET collation_connection      = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `mydatabase`.`vfiscalperiodcombobox` AS select `mydatabase`.`fiscalperiod`.`name` AS `text`,`mydatabase`.`fiscalperiod`.`id` AS `value` from `mydatabase`.`fiscalperiod` order by `mydatabase`.`fiscalperiod`.`start` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

Because of this I cannot import this file into another database (without manually removing the database names from the file).

This happens with the following MariaDB version on Windows:

mysql  Ver 15.1 Distrib 10.1.9-MariaDB, for Win32 (AMD64)

It does NOT happen with the following version on Debian:

mysql  Ver 15.1 Distrib 10.0.28-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Is this simply a bug? Or am I doing something wrong? Or is there a flag with which I can force mysqldump to never prepend the database name?

They are not bug, but a feature of mysql. Just make sure you are using root or admin account.


How do I escape backslashes and forward slashes in Neo4J Cypher? When I run,

MATCH (p:person {id:'1'}), (a:Address {street:"11\34, Wall street"})RETURN p, a;

  Invalid input 'S': expected '\', ''', '"', 'b', 'f', 'n', 'r', 't', '_', '%', UTF16 or UTF32

How do I handle \ and / special characters

Don't use double quotes " for a string, use single quotes ' and use double slash \\ to insert single backward slash \. The \ will escape the character after it (like in C).

MATCH (p:person {id:'1'}), (a:Address {street:'11\\34, Wall street'})RETURN p, a;


select single row from table for a particular column combination I have a table as below

Id... ColA... ColB... Isdefault

1...      100...    96...   1
2...      100...    96...   0
3...      101...    98...   0
4...      102...    99...   1

I want to have a select statement, where for a particular combination of ColA and ColB, I should get only one Row with the condition that if Isdefault with values both 0 and 1 present for a particular ColA,ColB combination, I should Only select one row with Isdefault=1 .

The output should look like below:

Id... ColA... ColB... Isdefault

1...      100...    96...   1
3...      101...    98...   0
4...      102...    99...   1

Note: Row with Id=2 is not present in the output because the same combination of ColA and ColB exists with isdefault=1

kindly help, thanx in advance.

declare @t table(Id int, ColA int, ColB int, Isdefault bit);
insert into @t values
(1, 100, 96, 1),
(2, 100, 96, 0),
(3, 101, 98, 0),
(4, 102, 99, 1);

with cte as
(
select *, row_number() over(partition by ColA, ColB order by Isdefault desc) rn
from @t
)

select Id, ColA, ColB, Isdefault
from cte
where rn = 1;


Partition Function vs. Clustered Index We have a table that is clustered on identity/datetime2. It is partitioned on the same datetime2. Are there any reasons to cluster on datetime2/identity instead? I understand the reasoning behind clustering generally, but with partitioning included, do things change?

Partitioning a table only divides it into "chunks" based on the partition function. The clustered index will give order to the data within each partition.

If you're planning to run queries that involve parts of a partition (i.e., show me sales between Jan 5th and Jan 12th), then it can be advantageous to those queries to have the date as the leading column of the clustering key. This type of structure will result in clustered index seeks, instead of partition scans. (Assuming there are no other suitable indexes on the table.)

If the queries only touch entire partitions, it doesn't matter as much, as partition elimination is enough to isolate the needed data. That said, ordering by date first could eliminate the need for an expensive sort operation, depending on what you're doing.

But this also depends on which columns you need from the table. If you only need to do something like aggregate total sales amounts over a date range, it may be sufficient to use a covering nonclustered index with the date as the leading (or only) column of the key, instead of rebuilding the entire table just for that.

If you do change the clustered index, this will affect singleton lookups (probably by the identity column which I assume is the primary key) as they will now involve a nonclustered index seek + key lookup. If this type of activity isn't a major part of the workload, this will be fine, but you have to be really careful that not too many rows are selected by these queries, or the optimizer will revert to a partition scan on the assumption that it's cheaper. Again, depending on which columns you need, it may be advantageous to create a covering nonclustered index that includes only the columns you need.


Divide by Zero Causing Error transaction to fail with Arthimetic Abort and ANSI WARNINGS FALSE after upgrade This is a strange issue..

We are receiving Divide by zero errors that are causing SQL Server to terminate transaction and crashing the test website.

Arithmetic Abort is set to FALSE along with ANSI warnings and all the other defaults... No code change has happened so it is not explicitly set in the code either.

This is only happening on most of the databases on the server that contain very similar data and the same metadata, but not all.

This has started happening after I performed a in place upgrade of our 2008 test server to 2012. (the issue occurs in both compatibility modes)

If I explicitly turn ARITHMETICABORT to false inside the query window it runs fine... something seems to be ignoring the database wide settings. this happens from within management studio or from code

here is the first piece of code we detected it in, for reference, but I think this is not related to the code

    SELECT  sm.MarkupId, sm.WebSiteCode, sm.CurrencyCode, sm.OfferTitle, sm.Priority, sm.MarkupPercent, sm.MarkupDownAmount, sm.Cumulative,
            sm.AllowAdditionalDiscounts, sm.StartDate, sm.EndDate, sm.DayOfWeek, sm.StartTime, sm.EndTime, sm.DepartureStartDate, sm.DepartureEndDate,
            ISNULL(s.name, '') AS Supplier, ISNULL(s.Supplierid, '') AS SupplierId, ISNULL(sb.CommissionPercent, 0) AS CommissionPercent, eg.Title AS EstabGroup,
            d.DomainName, sm.DurationFrom, sm.DurationTo, sm.BookingTotalFrom, sm.BookingTotalTo,
            ActualCommission = CASE WHEN ISNULL(sb.CommissionPercent, 0) = 0 THEN CAST(( ( sm.MarkupPercent - 100 ) / sm.MarkupPercent ) * 100 AS DECIMAL(18, 2))
                                    ELSE ROUND(ISNULL(sb.CommissionPercent, 0) - ( 100.0 - sm.MarkupPercent ), 2)
                               END, sm.BoardTypeId, BoardTitle = ISNULL(b.Title, '')
    FROM    SupplierMarkup_tbl sm
            INNER JOIN Domain_tbl d ON d.DomainId = sm.DomainId
            LEFT JOIN Supplier_tbl s ON sm.SupplierId = s.SupplierId
            LEFT JOIN acomEstabGroup_tbl eg ON sm.EstabGroupId = eg.EstabGroupId
            LEFT JOIN SupplierBookedItemType_tbl sb ON sb.SupplierId = s.SupplierId
                                                       AND sb.WebsiteCode = sm.WebSiteCode
                                                       AND sb.BookedItemTypeId = 17
            LEFT JOIN acomBoardType_tbl b ON b.BoardTypeId = sm.BoardTypeId
    WHERE   1 = 1
    UNION
    SELECT  CASE WHEN cm.IsExtranet = 1
                      AND cm.GrossAdjustment = 1 THEN -2
                 WHEN cm.IsExtranet = 1
                      AND cm.NetAdjustment = 1 THEN -1
                 ELSE 0
            END AS MarkupId, '' AS WebSiteCode, '' AS CurrencyCode, mo.Title AS OfferTitle, -1 AS Priority, cm.MarkupPercent * 100 AS MarkupPercent,
            0 AS MarkupDownAmount, cm.Cumulative, cm.AllowAdditionalDiscounts, cm.StartDate, cm.EndDate, cm.DayOfWeek, cm.StartTime, cm.EndTime,
            cm.CheckInStartDate AS DepartureStartDate, cm.CheckInEndDate AS DepartureEndDate, ISNULL(s.name, '') AS Supplier, ISNULL(s.Supplierid, '') AS SupplierId,
            0 AS CommissionPercent, '' AS EstabGroup, d.DomainName, cm.DurationFrom, cm.DurationTo, cm.BookingTotalFrom, cm.BookingTotalTo, ActualCommission = 0,
            cm.BoardTypeId, BoardTitle = ISNULL(b.Title, '')
    FROM    conContractEstabMarkup_tbl cm
            INNER JOIN Domain_tbl d ON d.DomainId = cm.DomainId
            LEFT JOIN Supplier_tbl s ON s.SupplierId = 97
            LEFT JOIN acomBoardType_tbl b ON b.BoardTypeId = cm.BoardTypeId
            LEFT JOIN dbo.MarkupOffer_tbl mo ON mo.MarkupOfferId = cm.MarkupOfferId
    WHERE   cm.EstabId IS NULL
            AND cm.Deleted = 0
    ORDER BY Priority, Supplier

This is not strictly an answer as I cant tell why this would happen, but I have found the root cause. Hopefully someone can tell me why this behavior happens or if it is a bug ?

The error happens when the 2nd statement after the UNION doesn't return any rows.

not seen one like this in a while !

The problem is in the expression:

ActualCommission =
    CASE
        WHEN ISNULL(sb.CommissionPercent, 0) = 0
        THEN CAST(((sm.MarkupPercent - 100) / sm.MarkupPercent) * 100 AS decimal(18, 2))
        ELSE ROUND(ISNULL(sb.CommissionPercent, 0) - (100.0 - sm.MarkupPercent), 2)
    END

A divide-by-zero error could occur for any row where CommissionPercent = 0 and MarkupPercent = 0.  Whether the error actually occurs in practice depends on the order of evaluation at runtime, which in turn depends on the shape of the execution plan and the detailed behaviours of the individual plan operators.

For example, some plan shapes might physically eliminate the problematic rows before the expression is evaluated.  Others might defer evaluation of the expression until the result is required by another operator later in the plan.  There are many possibilities and subtleties here, leading to the apparently 'strange' behaviours you observe.

In general, SQL Server makes no guarantees about the order of evaluation of scalar expressions or the number of times a particular expression might be evaluated at runtime.  The exception to this is the CASE statement, which does provide guarantees about the order of evaluation in most circumstances.  From the documentation:

  The CASE statement evaluates its conditions sequentially and stops
  with the first condition whose condition is satisfied. In some
  situations, an expression is evaluated before a CASE statement
  receives the results of the expression as its input. Errors in
  evaluating these expressions are possible. Aggregate expressions that
  appear in WHEN arguments to a CASE statement are evaluated first, then
  provided to the CASE statement.

The way to protect against the error is to modify the CASE expression so the divide-by-zero is explicitly handled using an earlier WHEN clause.  How you choose to account for rows with CommissionPercent = 0 and MarkupPercent = 0 is something only have the information to decide about.  That said, the behaviour with ARITHABORT set to OFF is to return a NULL. The following change to the CASE statement produces that result with ARITHABORT set to the highly-recommended ON:

ActualCommission =
    CASE
        -- New
        WHEN (sb.CommissionPercent = 0 OR sb.CommissionPercent IS NULL) AND sm.MarkupPercent = 0
        THEN NULL

        WHEN ISNULL(sb.CommissionPercent, 0) = 0
        THEN CAST(((sm.MarkupPercent - 100) / sm.MarkupPercent) * 100 AS decimal(18, 2))
        ELSE ROUND(ISNULL(sb.CommissionPercent, 0) - (100.0 - sm.MarkupPercent), 2)
    END

Explicitly accounting for the possibility of a divide-by-zero error is very much better than relying on the ARITHABORT setting.  That link contains the following statement:

  You should always set ARITHABORT to ON in your logon sessions. Setting
  ARITHABORT to OFF can negatively impact query optimization leading to
  performance issues.

ARITHABORT is required to be ON for many newer engine features (e.g. indexes on computed columns and indexed views).  Most client libraries explicitly set ARITHABORT to ON when connecting, which overrides any database- or server- level setting.

My strong recommendation is to always use the recommended SET settings and code defensively against error conditions.

SET ANSI_NULLS,
    ANSI_PADDING,
    ANSI_WARNINGS,
    ARITHABORT,
    CONCAT_NULL_YIELDS_NULL,
    QUOTED_IDENTIFIER
    ON;

SET NUMERIC_ROUNDABORT OFF;


Efficiently get the last record I have a table order which contains OrderId (WarehouseId and OrderId are composite primary key).

WarehouseId | OrderId | ItemId | OrderDate
-------------------------------------------
          1 |       1 |      1 | 2016-08-01
          1 |       2 |      2 | 2016-08-02
          1 |       3 |      5 | 2016-08-10
          2 |       1 |      1 | 2016-08-05
          3 |       1 |      6 | 2016-08-06

(table is simplified and only shown required fields)

How to efficiently select the last order for particular Warehouse? I currently do:

SELECT TOP 1 * FROM tblOrder WHERE WarehouseId = 1 ORDER BY OrderId DESC

My concern is, when I have a million (or more) orders for particular warehouse, by doing sorting and select the first record, it will be too slow (I think?). From advice I get so far, it is not an expensive operation, therefore it should be fine. Is that right?

Or, is there any more efficient way to select the last order record?

Where appropriate, the database engine can do a backwards scan, ie go straight to the end of the index and work backwards so it's nice and efficient.  You can see this on your query execution plans under the 'Scan Direction' attribute of scan operators.  For your example, with your composite primary key on WarehouseId,OrderId then this will work:

As you are doing select * the clustered index already covers the whole query so no additional bookmark lookups are required.


SQL Server 2012 Unable to Utilize Defined Amount of Locks So I've been beating my head against the wall for two days about this and I can't find a solution. I'm running a script to stage a large amount of data. This script requires roughly 35 million locks in order to complete; this translates into needing 3.36GB of memory available for locks. I set the number of locks to 37 million in the configured values, however after the reboot the running values are only allowing for 16,695,296 locks.

This corresponds to roughly 14% of available memory. As SQL Server 2012 can use 40% of available memory for locks, I should have plenty a room, but I don't. My question is: why can't SQL Server utilize the extra 26% of available lock memory? Everything I've read says that it should be able to.

Also, I did try running the locks on dynamic, but that also failed for the same reasons and with the same error codes: error 1204, instance can not obtain a lock resource. I know it's the server configuration and not the script, because I ran the exact same script, with the exact same data set on an older server (SQL Server 2008) without issue.

I appeal to the database admin gods for help; help me oh great ones!

Edit: This is a VM on a fail-over cluster, the VM has 16 GB of memory allotted to it. I've given SQL Server 12GB and left 4GB available for the host operating system.

Is there any trace flag enabled or any tweaking into startup parameters of SQL server can you please check that.Have a look at below Microsoft support article

http://support.microsoft.com/kb/2199576/en-us
Keep max lock value to default

EXEC sp_configure 'locks', 0 ( I guess you tried this option already)

Now if you face this issue again best possible workaround will be to decrease the amount of rows being transferred. Every lock has memory associated with it so if there is lock contention I believe its somehow related to SQL Server buffer pool memory. Did you run DBCC MEMORYSTATUS and check value for

OBJECTSTORE_LOCK_MANAGER (Total)       KB

please do tell us what is value of single page and multi page allocator for above clerk


No, it is not a problem. The documentation sums things up.

  Every table has a table character set and a table collation. The
  CREATE TABLE and ALTER TABLE statements have optional clauses for
  specifying the table character set and collation

The default database character set/collation values are used for a table if the relevant clauses are not specified at table creation time.


Find statements related to sys.dm_db_missing_index... tables (SQL Server) I have read up different articles related to SQL Server 2008 on how to find out missing indexes using the tables sys.dm_db_missing_index_groups, sys.dm_db_missing_index_group_stats etc.

One thing I'd like to know: Is it possible to extract the queries that would benefit from these indexes?

Thanks all!

One approach that I like to use, is to focus just on the most frequently executed queries and not all queries that might benefit from having indexes. The reason for this is the impact and cost (CPU time, I/O) that additional indexes would have on DML (DATA MODIFICATION LANGUAGE) statements.

Imagine a table customers that has 4 columns (id_customer, first_name, last_name, address) that has no nonclustered indexes.

The query UPDATE customers SET first_name = 'fred'  where id_customer = '1' only updates the table customers. If you add a nonclustered index on first_name and last_name called idx_cust_first_last, that same update statement above now has to modify not only the table customers but also the index idx_cust_first_last for the affected row or rows. This results in an additional IO and CPU cost that isn't there without the index.

In short, just because an index shows up in the missing index views, doesn't mean you should add it until you are certain that the benefit gained from that index outweighs the evenutal cost to the modifications made to your data.

Here is a link to an article written by Kimberly Tripp on the subject which you may find useful.

That said, here is a query I use to display queries that are missing indexes in desending order based on total cumulated execution time. So queries that are taking a long time to finish show up on top of the list. In my experience, the execution_count column is extremely important. This is because the execution count shows just how "popular" that query truly is. If a query takes a very long time to run, shown by the total_elapsed_time column but was only executed five times then adding an index may not be useful because that query is only run occassionaly.

Here is the script. Its not mine.

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
SELECT p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Database)[1]', 'NVARCHAR(256)') AS DatabaseName
,s.sql_handle
,s.total_elapsed_time
,s.last_execution_time
,s.execution_count
,s.total_logical_writes
,s.total_logical_reads
,s.min_elapsed_time
,s.max_elapsed_time
,p.query_plan
,p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Table)[1]', 'NVARCHAR(256)') AS TableName
,p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Schema)[1]', 'NVARCHAR(256)') AS SchemaName
,p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/@Impact)[1]', 'DECIMAL(6,4)') AS ProjectedImpact
,ColumnGroup.value('./@Usage', 'NVARCHAR(256)') AS ColumnGroupUsage
,ColumnGroupColumn.value('./@Name', 'NVARCHAR(256)') AS ColumnName
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) AS p
CROSS APPLY p.query_plan.nodes('/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/sp:ColumnGroup') AS t1 (ColumnGroup)
CROSS APPLY t1.ColumnGroup.nodes('./sp:Column') AS t2 (ColumnGroupColumn)
WHERE p.query_plan.exist(N'/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan//sp:MissingIndexes') = 1
ORDER BY s.total_elapsed_time DESC

And here is an example of the output

This queries displayed here were either currently being executed or already in cache. If the cache has recently been cleared by a server restart or by a dbcc command, then there may not be much information to display.

Microsoft's documentation on sys.dm_exec_query_plan and sys.dm_exec_query_stats which are being used to return information about cached query plans


Get 2 counts in single query I need to get count of 2 columns with group by

This is my sample data

I need to get count of persons from particular state and count of persons from particular country

My output should be like

I need to manipulate data like this; how to create query for this

SELECT country,
       COUNT(state) total,
       SUM(state='xxx') xxx,
       SUM(state='yyy') yyy,
       SUM(state='zzz') zzz
FROM table
GROUP BY country


Overestimated query resulting in poor performance - TVF with xml processing Today i asked question about bad query performance. That query had a lot of joins over a lot of views. I think i have found which view is a trouble for SQL server. Here is actuall query plan for select run on that view https://www.brentozar.com/pastetheplan/?id=BJB4WOV6S As you can see SQL Server highly overestimates number of rows and needed memory. Outter apply is used to run TVF that is returning processed XML, xmls are rather very small. I wonder how to avoid such overestimation(and what cuase them)? Should i create xml indexes for my table?

One of the TVF

FUNCTION [schema].[functionname](@xmlData  XML)
                RETURNS  TABLE
            AS
            return
                SELECT Tbl.Col.value('PN[1]', 'nvarchar(32)') as PPN
                    FROM   @xmlData.nodes('PI') Tbl(Col)
GO
example

declare @para as xml
set @para = '<PI><PGID>00000000000000000000000000000000</PGID><PN>0</PN></PI>'
Select * from [schema].[functionname](@para)

I think the base cardinality estimate for XML TVF is 10,000 rows - I read it in a presentation by Michael Rys

To improve estimates, you can either add a schema to your XML or create an XML index.

Other than that I could only direct you to row goal hints and estimates trick I first heard from Adam Machanic here


Is it possible to install oracle 12c without X window system I am looking at installing Oracle 12c on a new server but the system admins are insisting that all new Linux servers don't have a GUI installed and that any administrative tasks are performed from a command line.

Now I have never installed Oracle this way and haven't found any information about it.

So is it possible and if so are there any links to documentation on how it's done.

Yes, you can do that by running the runinstaller passing the silent argument. Also, it is possible to save and use response files, which basically contain standard configurations and parameters for the software installation. It's very useful if you will need to perform installations following a specific standard on a frequent basis.

Here's the docs:
https://docs.oracle.com/database/121/LADBI/app_nonint.htm

Another option:
You don't need to have "GUI" installed, you can emulate the Graphical Interface. There are plenty of tools. tigervnc is a great one. To install it, run as sudo: yum install tigervnc and after installation is completed, start it by running vncserver. You will be prompted for the user's password. Then you can issue: ps -ef | grep vnc, which will show you details about the program execution, being the port the most important. Now you can externally gain access to the server through VNC Viewer (needs to be installed), by specifying server's  ip and port/ display #. Note: Yum if your OS support it. Otherwise, use rpm (problem is every dependency will need to be separately installed).

Hope it helps.


Strange UTC offset time zone parsing in Postgres Postgres exhibits some strange behaviour when parsing time zones, or I just don't understand how it works.

From the documentation:

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
Result: 2001-02-16 18:38:40

  The example takes a time stamp specified in EST (UTC-5) and converts it to local time in MST (UTC-7).

This seems to imply that 'MST' is interchangeable with 'UTC-7' but the behaviour is opposite to what you'd expect.

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'UTC-7';
Result: 2001-02-17 08:38:40

Instead of using the time zone 'UTC-7' it's using 'UTC+7', which is 14 hours different.

Using ISO 8601 time zone notation also yields the opposite from the expected result:

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE '-7';
Result: 2001-02-17 08:38:40

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE '-07';
Result: 2001-02-17 08:38:40

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE '-07:00';
Result: 2001-02-17 08:38:40

The only ISO 8601 notation that threw an error was the -0700 notation.

Other gibberish notations are accepted, even though they don't make any sense at all:

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE '-7MST';
Result: 2001-02-17 08:38:40

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST-7';
Result: 2001-02-17 08:38:40

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE '-7+7';
Result: 2001-02-17 08:38:40

Can someone help me understand how time zones, specifically offset notation works, or is supposed to work, in Postgres.

Instead of using the time zone 'UTC-7' it's using 'UTC+7', which is 14 hours different.

It depends whether +/- means east or west of Greenwich, and it turns out both conventions exist. PostgreSQL doc warns about that:

http://www.postgresql.org/docs/current/static/datatype-datetime.html

Excerpt from 8.5.3. Time Zones (but you really want to read the whole paragraph):

  Another issue to keep in mind is that in POSIX time zone names,
  positive offsets are used for locations west of Greenwich. Everywhere
  else, PostgreSQL follows the ISO-8601 convention that positive
  timezone offsets are east of Greenwich.

Using ISO 8601 time zone notation also yields the opposite from the expected result:

In the same page, the accepted formats for time zone literals are listed, here's a condensed version:

  PostgreSQL allows you to specify time zones in three different forms:

  A full time zone name, for example America/New_York [...]
  A time zone abbreviation, for example PST[...]
  In addition to the timezone names and abbreviations, PostgreSQL will accept POSIX-style time zone specifications of the form STDoffset
  or STDoffsetDST, where STD is a zone abbreviation, offset is a numeric
  offset in hours west from UTC, and DST is an optional daylight-savings
  zone abbreviation,[...]

Note that it never pretends to accept an ISO 8601 time zone designator. And as you found out with -0700, it doesn't. You should just use one of the forms above.

Also beware of (false) conclusions that because the parser doesn't reject a time zone, it's valid:

  One should be wary that the POSIX-style time zone feature can lead to
  silently accepting bogus input, since there is no check on the
  reasonableness of the zone abbreviations


Why Mysql Fabric is end of life, and what is an alternative? Recently I am looking for an application level Sharding solution for MySql, and come across Mysql Fabric, but after some investigation, I find it was already announced to be end of life. It seems Mysql Fabric is not suggested to use any more. If so, my questions are,

Is Mysql Fabric ever a mature tool for production usage?
Are there any successful examples of using Mysql Fabric for sharding?
Is there any specific reason Mysql Fabric is no longer maintained since 2017?
What could be alternatives?

In my opinion, Fabric was doomed because it had a single-point-of-failure, namely the 'fabric node'.  Otherwise, it was probably considered mature.

The replacement: Group Replication (InnoDB Cluster).  This has a robust design that seems to have no major holes.  Galera Cluster (built into MariaDB and PXC, add-on for Oracle's MySQL) competes head-to-head with it and is more mature; there are advocates on both sides.

Group Replication (and Galera) have valid solutions for HA (High Availability) in that they use multiple servers and can recover from any single server failure.  (This, of course, requires geographical disbursement of the servers.)

Group Replication is becoming mature and has only one flaw (arguably minor): It has no built-in way to handle the "critical read" problem.  (This is where a user writes something to the database, but the subsequent SELECT does not see it because it is sent to a different server.)

I am not prepared to address "sharding" in GR.  Clustrix is a 3rd party that claims to have a very good sharding solution.  What do you expect from "application level Sharding"?  And is Sharding, not HA, your main goal?

Perhaps you saw this?  Mysql Group replication vs Mysql Cluster


Azure SQL Databases: High number of SA connections with "BRKR TASK" and "TASK MANAGER" commands Found out that all our Azure SQL Databases (pricing tiers S1-S2) have high number of connections/sessions from SA

SA - BRKR TASK - around 60 sessions
SA - TASK MANAGER - around 20 sessions

Here are two questions:

1. What are those ?
2. Why so many connections from them on Azure SQL databases ?

The reason I ask is sometimes it causes error

"The request limit for the database is (paste a number here) and has been reached"

Regards,

Those BKRK are related system processes and back end connections and do not count upon the limit of connections associated with every service tier. They can safely be ignored. BKRK processes are related to service broker on on-premises SQL Server instances and Azure SQL Database may be using it as part of the service it provides.

With Azure SQL Database you will always see many connections and processes related to the automated features the PaaS provides to the customer. Learn more about back end connections here.

Instead of focus on system processes, please try to know if blocking is the culprit.

select r.session_id,r.blocking_session_id,r.wait_type,r.wait_time,r.wait_resource,r.total_elapsed_time,r.cpu_time,r.reads,r.writes,
s.nt_user_name,s.program_name,s.total_elapsed_time
from sys.dm_exec_requests r
join sys.dm_exec_sessions s
on s.session_id=r.session_id

Consider also deadlocks may be a possibility, and you can find them using below query.

select *
from sys.event_log
where event_type <> 'connection_successful'
and start_time >= CAST(FLOOR(CAST(getdate() AS float)) AS DATETIME)
order by start_time desc  -- event type = deadlock


Is it possible to replicate a collection in MongoDB I would like to replicate/synchronize a collection named "CollectionA" from a mongoDB "DataBaseA" to a mongoDB "DatabaseB" in real-time

DatabaseB is a normal database (not shared and not a replica)
The DatabaseB will have writes to it, but not for collection "CollectionA"
The synchronization/replication should be done in real-time with a delay defined based on network latency

I'm trying to achieve this without writing any custom code. Is this possible?

Depending on what is your MongoDB server version... Solution is use method "Change Streams" or "Oplog tailing"
Where you are "listening" changes of source DB and replicating those commands to destination DB (at other MongoDB installation)


Starting PostgreSQL server after a HDD crash results in FAILED STATE I am using Fedora 15 with PostgreSQL 9.1.4. Fedora crashed recently after which:
An attempt to start the PostgreSQL server :
service postgresql-9.1 start

gives
Starting postgresql-9.1 (via systemctl):  Job failed. See system logs and 'systemctl status' for details.
                                                       [FAILED]

Although, the server starts normally when I start the server for the first time after system reboot.
But, an attempt to use psql gives this error :
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

.s.PGSQL.5432 file is not present anywhere on the system.
A locate .s.PGSQL.5432 outputs nothing.

The system log has this :
Aug 14 17:31:58 localhost systemd[1]: postgresql-9.1.service: control process exited, code=exited status=1
Aug 14 17:31:58 localhost systemd[1]: Unit postgresql-9.1.service entered failed state.

A
systemctl status postgresql-9.1.service

gives
postgresql-9.1.service - SYSV: PostgreSQL database server.
          Loaded: loaded (/etc/rc.d/init.d/postgresql-9.1)
      Active: failed since Tue, 14 Aug 2012 17:31:58 +0530; 58s ago
     Process: 2811 ExecStop=/etc/rc.d/init.d/postgresql-9.1 stop (code=exited, status=1/FAILURE)
     Process: 12423 ExecStart=/etc/rc.d/init.d/postgresql-9.1 start (code=exited, status=1/FAILURE)
    Main PID: 2551 (code=exited, status=1/FAILURE)
      CGroup: name=systemd:/system/postgresql-9.1.service

I had not changed the default setting of fsync so I am guessing, it was set to on. I am on a HDD. The HDD crashed.
HDD crash
The HDD crash resulted in running a manual fsck on a prompt and not gui based. With it repairing gazillion inodes etc.. After which I restarted the system with a Ctrl+Alt+Delete.
PostgreSQL's log has this:
LOG:  database system was interrupted; last known up at 2012-08-14 17:31:57 IST
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  record with zero length at 0/41A4E58
LOG:  redo is not required
FATAL:  could not access status of transaction 1
DETAIL:  Could not open file "pg_multixact/offsets/0000": No such file or directory.
LOG:  startup process (PID 13016) exited with exit code 1
LOG:  aborting startup due to startup process failure

Update
Trying to start the server after taking a file system level copy of the /var/lib/pgsql directory, and running ./pg_resetxlog -f /var/lib/pgsql/9.1/data/ with the result xlog -f /var/lib/pgsql/9.1/data/ still yields in :
LOG:  database system was interrupted; last known up at 2012-08-14 18:46:36 IST
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  record with zero length at 0/6000078
LOG:  redo is not required
FATAL:  could not access status of transaction 1
DETAIL:  Could not open file "pg_multixact/offsets/0000": No such file or directory.
LOG:  startup process (PID 13766) exited with exit code 1
LOG:  aborting startup due to startup process failure

The real answer will be in the PostgreSQL logs, in /var/lib/pgsql/data/pg_log.

However, before you take any action: It is vital that you take a file system level copy of your database before attempting repair  if any of your data is valuable to you. See http://wiki.postgresql.org/wiki/Corruption . You must copy the whole data directory. On Fedora that's /var/lib/pgsql/data by default, but verify that's correct for your install.

Based on the logs you've posted you certainly have some degree of database corruption. The storage that the database is on (the hard drive or file system) is most likely damaged. Take a copy NOW, and put it on a different hard drive or system.

Only once you have made a full file-system level copy of your data directory, try using pg_resetxlog to clear the damaged transaction logs and start your database. Even if it starts it is highly likely to be corrupt; you should pg_dump it then re-initdb it and restore the dump to the fresh instance.

If you still can't start it after a pg_resetxlog then post an updated log of the startup attempt after resetxlog. It's possible you'll need to start Pg in stand-alone mode with:

sudo -u postgres postgres --single -D /var/lib/pgsql/data -P -f i postgres

If that works, giving you a backend> prompt, try again after replacing the last "postgres" with the name of the DB you want to connect to. You should be able to SELECT, COPY data from tables, etc.

If that doesn't work, ie you can't start a standalone backend, then it's probably time to restore from backups - since you're sensible enough to have them. If anyone else reading this is in the same position, contact an experienced PostgreSQL consultant to see if they can recover data from your database. Be prepared to pay for their time and expertise.

Your file system is probably damaged

The severity of the damage to the PostgreSQL install suggests that your whole file system is probably damaged. You may wish to consider restoring the whole system from a backup or reinstalling it.

I would not trust this file system, fsck or no fsck.

SMART-test your drive

I also recommend that you run a SMART check on your hard drive with smartctl from smartmontools; assuming it's /dev/hda that'd be smartctl -d ata -a /dev/sda | less . Look for a failed health test, uncorrectable_sectors, a high read error rate, a reallocated_sector_count of more than 2 or 3, or a non-zero current_pending_sector. Run smartctl -d ata -t long /dev/sda to execute a non-destructive self test on your HDD; it won't interrupt normal functioning of the system. When the estimated time has elapsed run smartctl -d ata /dev/sda again and look at the self test log to see if it passed.

If anything looks less than perfect, replace the drive.

In future, consider automating this testing via smartd for early warning of drive failures.

(Content in this post was obsoleted by updates to the question. If you're troubleshooting a similar problem, look at this answer's edit history).


ORA-04020: deadlock detected while trying to lock object - Oracle Package I have an Oracle Package (Schema1) that is calling another Oracle Package (Schema2) over a DB link. There are no recent code changes.

When I try to compile the package in (Schema1) it throws the following errors.

(replaced the actual schema names with 1 and 2 for clarity)

  ORA-04052: error occurred when looking up remote object
  SCHEMA2.PKG_NAMEHERE@SCHEMA2.LINKNAME.INT@SCHEMA1

  ORA-04020: deadlock detected while trying to lock object
  SCHEMA2.PKG_NAMEHERE@SCHEMA2.LINKNAME.INT@SCHEMA1

What we have tried

Killed all user sessions
Recompiled the package in SCHEMA2 even so SCHEMA1 that calls it over the DBLINK failes with "ORA-04020: deadlock detected"

We have 4 Database regions, and this is only happening in two of the regions.

Notes:

SCHEMA1: Oracle 11g

SCHEMA2: Oracle 12c (on ExaData)

Look at this webpage https://docs.oracle.com/cloud/latest/db112/LNPLS/nameresolution.htm#LNPLS017, especially the INNER CAPTURE section.
Does the package exist on both sides of the link or only one?
What does the ORA-60 deadlock trace file show?
What does DBMS_UTILITY.NAME_RESOLVE show for "SCHEMA2.PKG_NAMEHERE@SCHEMA2.LINKNAME.INT@SCHEMA1"?
Do the DBLINKS loopback?


SQL server 2008R2 default encryption? I was wondering if SQL server 2008 R2 has any default security if you're using it via ODBC or SQL Server Management Studio?

I was reading about SSL here
https://technet.microsoft.com/en-us/library/ms189067(v=sql.105).aspx

The issue is forcing encryption right now will break a lot of things and would take a while. I was wondering if there is anything that's set up to encrypt by default to buy some time while we figure ssl out.

We have hundreds of users and some people designed their own database to pull from this one. It would be a huge task with limited amount of time. I have a trusted cert it's just force encryption plan fell out of place. So I guess right now nothing's encrypted - am just trying to see if anything exist by default.

SQL Server communicates using TDS, or Tabular Data Stream, protocol.  By default, TDS is not encrypted.

Encryption can be negotiated between the client and the server at time of connection.  Essentially, you enable encryption at the server, then roll it out by making the necessary changes at the client end.  Since encryption is not forced at this point you are simply reducing the visibility of some data over the wire.  Security is about layers of protection; encrypting communications, even if only some communication, is still a valuable layer in the security stack.

Once you have all clients configured to use TDS encryption, you configure the server to only accept encrypted connections.  At that point, all communication between the server and its clients will be encrypted.