Create temporary tables per session in a DB other than tempdb Using SQL Server, Is there a way to create a temporary table per user session in a user database in the same exact way they are created in tempdb?

Using tempdb is fine in most cases, but some of the operations I've been seeing generate a lot of IO and in some cases end up saturating the templog file.

So here is a typical situation

-- create the table
create table #some_table(t int)

-- Do some work
declare @rownum int = 1

begin transaction
while 1=1
begin
    insert into #some_table values(@rownum)
    if @rownum > 5000000
        break

    set @rownum = @rownum + 1

end

Work continues on until @rownum reaches 5000000 and because its within a transaction, tempdb can't shrink. In the mean time, alerts start showing up and the LOGS disk fills up. Doing a SHRINKFILE is useless because the transaction is left open.

The work has to be done in tempdb because the process that created that temporary table could be run multiple times or there could be multiple processes that use the same table name.

-- show the contents of #some_table
set statistics io on

select * from #some_table

set statistics io off

-- results
(5000001 row(s) affected)
Table '#some_table_________________________________________________________________________________________________________00000000001F'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So until I find a way to simulate that temporary table per session ability in tempdb, we have to continue tracking down offending queries and basically play air traffic control with tempdb.

Here is the situation I'm currently dealing with

tempdb's log is currently at 40GB+ and climbing fast.

There is no direct way to copy temp table behavior into non user databases. This starts with the easy collision preventions and continues to several advantages of temp tables (like reduced logging) that just can't be reproduced.

To create data sets in user databases that avoid collisions you have basically two options:

1. put all the data in one table and deal with the separation using some kind of session id in the table

The most obvious session id you could use for the single table approach is the @@SPID. But you could use other "random" values too. To achieve greater physical separation, you could partition the table on that column.
The obvious disadvantage is that all sessions have to use the same table layout.

2. create a random table name and use dynamic sql from then on.

The random-table-name option is going to make your code very hard to read. But as SQL Server does not allow statements to access objects based on names stored in variables, dynamic SQL is your only option.

If you go this way I recommend to include the current session_id and request_start_time (from sys.dm_exec_requests) as well as a guid in the name. That way it is easy to write some kind of garbage collector that goes around and deletes tables that got left behind. Depending on your object life time expectations you might have to use a different time stamp like the connect_time in sys.dm_exec_connections but the idea is the same. Once the request with that start time is gone, the object cannot be of use anymore because the variable holding its name went out of scope too.


One Non-Clustered Index that covers every possible combination of predicates? Say we have a table against which a number of different queries can be executed and each query has its own combination of predicates.

So, for example, if we have a table with columns A, B, C, etc we may have a query that filters on columns A and B but not C, a second query that filters on A and C but not B, and so on.

Does it make sense to have a non-clustered index that has as its key columns every column in the table (or tables) that can be a predicate?

To my mind, I'd have thought it is better to have a non-clustered index for each query with the predicates for that query as the key columns.

Any advice on this would be great!

Multiple aspects needs to be considered when creating an index. An index on columns A,B is not the same as an index on B,A. the performance of a query which filters on both column A and B will be different depending on the order of the columns in the index.

Indexes slow down DML operations and increase the size of the database.

You can refer to the stackoverflow link below for more details.
https://stackoverflow.com/questions/5447987/why-cant-i-simply-add-an-index-that-includes-all-columns


Running a complex query for every date in a range I've got a table of orders

   Column   |            Type             |                      Modifiers
------------+-----------------------------+-----------------------------------------------------
 id         | integer                     | not null default nextval('orders_id_seq'::regclass)
 client_id  | integer                     | not null
 start_date | date                        | not null
 end_date   | date                        |
 order_type | character varying           | not null

The data has non overlapping standing orders for a client_id and occasionally a temporary order that overrides the standing order on it's start_date when they have a matching client_id. There is application level constraints keeping orders of the same type from overlapping.

 id | client_id | start_date |  end_date  | order_type
----+-----------+------------+------------+------------
 17 |        11 | 2014-02-05 |            | standing
 18 |        15 | 2014-07-16 | 2015-07-19 | standing
 19 |        16 | 2015-04-01 |            | standing
 20 |        16 | 2015-07-18 | 2015-07-18 | temporary

For example, on 2015-07-18 client 16 has order #20 as it's active order because it overrides the standing order #19. With some fuss I found an efficient way of querying for active order id's on a date.

    SELECT id from (
      SELECT
        id,
        first_value(id) OVER (PARTITION BY client_id ORDER BY order_type DESC) active_order_id
      FROM orders
      WHERE start_date <= ? and (end_date is null OR end_date >= ?)
    ) active_orders
    WHERE id = active_order_id

If you query this with 2015-07-18 as the placeholders, you would get

 id
----
 17
 18
 20

The query plan on this query compared to some of my other ideas (like sub queries counting the number of temporary orders for a client on a date) is quite small and I'm pretty happy with it. (the design of the table, I'm not thrilled about)

Now, I need a to find all the active orders for a date range joined with the dates they are active on. For example, with the date range of 2015-07-18 to 2015-07-19 I would like the following result.

active_date | id
------------+----
 2015-07-18 | 17
 2015-07-18 | 18
 2015-07-18 | 20
 2015-07-19 | 17
 2015-07-19 | 18
 2015-07-19 | 19

Order 20 overrides order 19 on 2015-07-18 but not on 2015-07-19.

I found with generate_series() I can generate a range of dates, but I haven't a clue how to join that with this to get a table of dates and order id's. My hunch is a cross join but I can't figure out how to make that work in this circumstance.

Thanks

UPDATE
Added an sql fiddle.

I would use select distinct on instead of window function, then just join the days.

select
    distinct on (date, client_id) date,
    id
from orders
inner join generate_series('2015-07-18'::date, '2015-07-19'::date, '1 day') date
  on start_date <= date and (end_date is null or date <= end_date)
order by date, client_id, order_type desc

http://sqlfiddle.com/#!15/5a420/16/0

I can elaborate more if something is not clear.


Adding a bool column; tracking untouched vs. false [backstory] I have a simple database of our current Widget inventory. It averages just dozen columns across five or six tables, but has a huge number of records already.
Some Widgets came with caps, some did not, but we've actually never kept track before. Now management wants users to be able to keep track of whether each individual Widget arrives with a cap or not. We will not be doing an inventory to check our current stock, but when I add the new column for "Arrived Capped," there will be no data on everything received before today. What is the usual, best-practices approach to scenarios like this?

When adding a string column, the meaning of "" is obvious; no data has been entered. But I'm adding a bool, thus existing records will default to a value that does indicate something: FALSE.
My first thought was to make the user portal have, rather than a checkbox, a radio button pair for this question. Upon creating a new record, or even returning to old records with false-falses, if they click Yes or No, that value is recorded, plus another bool indicates this question was actually answered manually. That is; if the first bool is F, but the second bool is T, then the first bool is not a 'default false'.
Their hand-waving solution was to have records dated before today displayed as "unknown" (this was nixed as existing records could not then manually be given a user-verified value of false). Should I use a faux-bool int and think of it in trinary? Or, back to using a separate tracking bit/bool, should I make a new table for that sort of data? Is this a common concept? Any naming conventions?

In most DBMSs, or at least the ones I have dealt with, a Bool is really a trinary.  You have 1, 0 and NULL.  NULL is specifically there for "I haven't entered any data yet."  Depending on how you code it your checkbox should default to unchecked for 0 or NULL.  Then 1 for checked of course.  However in your reporting you can actually differentiate between rows where someone has looked at it and entered a value (1 or 0) and rows where no one has updated it yet (rows where the value is NULL).


SELECT shows different amount of rows I have a job which each day (at 6:01pm) copies 516 rows from a table and seeds them with tomorrow's date

INSERT MYDB.MARKET_DATA(
            MARKET_DATE,
            CURRENCY_BASE,
            CURRENCY_TERM,
            PERIOD,
            MID)
             (
               SELECT
                  DATEADD(hh, 6, GETDATE()),
                  MARKET_DATA.CURRENCY_BASE,
                  MARKET_DATA.CURRENCY_TERM,
                  MARKET_DATA.PERIOD,
                  MARKET_DATA.MID
               FROM MYDB.MARKET_DATA with (nolock)
               WHERE cast(MARKET_DATE as date)=cast (getDate() as date)
             )

We also check that the right number of rows are copied by looking at the number of rows for the last 7 days

SELECT count(*)
,CAST(market_date AS DATE)
FROM mydb.market_data
WHERE market_date > dateadd(day, - 7, getdate())
GROUP BY CAST(market_date AS DATE)
ORDER BY CAST(market_date AS DATE) DESC

We ran this SELECT query twice today. At 10am in the morning it returned 517 rows for today, and at 1pm it returns 516 rows for today.

What could possibly account for this discrepancy?

There was no user interaction with this table today (other than running the two SELECT's)
There is no with (nolock) on the SELECT query
Note that there is a with (nolock) on the INSERT/SELECT, because the rows are updated throughout the day with latest information, and I don't want the INSERT/SELECT to fail on any rows that are being updated at the precise moment that it is being executed
There is no stored procedure or job to delete any rows

What you're running into is probably a misunderstanding with how DATEADD works.

If you run these queries, you'll see what I mean:

DECLARE @getdate DATETIME = '2019-01-06 10:00:00.000';

SELECT @getdate AS getdatevariable, DATEADD(DAY, -7, @getdate) AS getdateminus7;

SET @getdate = '2019-01-06 01:00:00.000';

SELECT @getdate AS getdatevariable, DATEADD(DAY, -7, @getdate) AS getdateminus7;

Subtracting 7 days doesn't bring you to the start of seven days ago. It brings you to 7 days ago, but to the hour of the datetime value you passed in.

If you need to flatten a datetime to the beginning of the day, you need to do something like this.

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @getdate), 0);

You can check out my answer here for more information:

How does date math work in SQL Server?


SQL Server Transaction Log File I am currently in an live environment and I am trying to clear some disk space. I noticed that a SQL Server database transaction log file is kinda big (12GB).

The recovery model of that database is set to simple. How can I decrease its size without killing any data or wrecking havoc on the server?

See disclaimer at the end.

First you need to figure out if there is a reason that the log is large/growing. A quick way to do that is to run this query (see http://sqlity.net/en/556/t-sql-tuesday-25-%E2%80%93-sql-server-tips-tricks/ for more info):

SELECT log_reuse_wait_desc,name FROM sys.databases;

If that comes back with "NOTHING" in the log_reuse_wait_desc column for your database you can go ahead and shrink the log file. The easiest way to do that is using SSMS. Right-click your database and select "Tasks", "Shrink", "Files". That brings up this dialog:

Select "Log" as File Type and select "Release unused space". Click OK to execute.

DISCLAIMER: If you follow these steps, you are doing a fairly un-intrusive operation. However, repeated shrinking and growing can have very bad effects on your database performance. So if your file is 12GB because that is the size needed to support normal operations you are better off leaving it that way. As there is no real good way to determine the necessary size (other then letting it grow) I would leave the file alone unless you are running out of disk space. There is no negative performance impact for having a too big log file.


Monitor user to query processlist We have a corporate monitoring system based in Zabbix. Its MySQL monitoring is quite basic, so I want to add more info. To do so, I've done a script that retrieves this info from MySQL and sends it to Zabbix.

Among the retrieved info, I want to get current connections, but I've only managed to do so with the root user. I've created a zabbix user with SELECT and PROCESS permissions* and tried mysqladmin -u zabbix processlist, but I get Access denied; you need the PROCESS privilege for this operation.

show grants for 'zabbix'@'%';
+----------------------------------------------+
| Grants for zabbix@%                          |
+----------------------------------------------+
| GRANT SELECT, PROCESS ON *.* TO 'zabbix'@'%' |
+----------------------------------------------+
SELECT USER(), CURRENT_USER();
+----------------+----------------+
| USER()         | CURRENT_USER() |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+

How can I get zabbix user to retrieve processlist?

* Yes, I've flushed permissions

Perhaps you can create a DB user whose sole purpose in life is to collect the processlist.

Your error message indicates that zabbix does not have the PROCESS privilege.

Without the PROCESS privilege, zabbix can only view the processlist of zabbix users. Not much fun, eh?

But WAIT, you said

mysqladmin -u zabbix processlist

That will attempt to connect as zabbix@localhost which you did not define. Please note:

zabbix@localhost will authenticate via the socket file
zabbix@'%' will authenticate via TCP/IP

Simply run

GRANT SELECT, PROCESS ON *.* TO 'zabbix'@'localhost';

and your troubles should be over.

Without adding another MySQL user, just specify the TCP/IP protocol

mysqladmin -u zabbix --protocol=tcp processlist


Group By part of a strings I have a various date times stored as strings in my sqlserver database.

07/May/2012:00:01:49
07/May/2012:00:02:14
07/May/2012:00:02:26

This column is called datetimesent and it is quite easy to select group by on it. For example:

SELECT DateTimeSent, COUNT(*) FROM MyTable where group by DateTimeSent

This will group be every second. What I'd like to do is group by every ten seconds, so instead of grouping by the entire string, group by all of it except last letter.  How do I do this?

Thanks.

You should consider the use of datetime instead but this will do what you want.

group by left(DateTimeSent, 19)


Are the results of the STRING_SPLIT() function returned in a deterministic order? I need to split a comma delimited string, manipulate it, and then concatenate it back into a single string retaining the original order of the data (if possible).
For example, take a column definition list of a CREATE TABLE statement (as a string) like so 'BrentOzarColumn INTEGER, PaulWhiteColumn DATETIME, ErikDarlingColumn VARCHAR(100)'. I'd like to split the comma delimited list to a result set, such as using SQL Server's built in function STRING_SPLIT() like so: SELECT TRIM([Value]) AS CoolDataPeople FROM STRING_SPLIT('BrentOzarColumn INTEGER, PaulWhiteColumn DATETIME, ErikDarlingColumn VARCHAR(100)', ',').
Without specifying an ORDER BY clause, this repeatedly yields (by coincedence?) the following results that appear to be ordered by their same ordering as they are in the string:

Once I have the result set above, I want to apply some additional string manipulations to each row (such as append some constant text), and then concatenate every row back with a function like STRING_AGG() (goodbye days of STUFF ... FOR XML PATH :) in the same order as the original string. So an example of my final result could be 'BrentOzarColumn INTEGER SQLROX, PaulWhiteColumn DATETIME SQLROX, ErikDarlingColumn VARCHAR(100) SQLROX'.
Ultimately my question is: Are the results of the STRING_SPLIT() function returned in a deterministic order? I know without an ORDER BY clause, ordering is not guaranteed when selecting from a dataset like a Table or View, but was wondering if there's a difference with functions?
As I type this out, I have a hunch the answer is no, the ordering is not deterministic therefor I'm not guaranteed the order of the results. Furthermore, I'm betting there's possible additional nondeterminism added for each function I run on top of the results, especially when I combine them back together with STRING_AGG(). (Regardless of the answer, I appreciate your help, and you're all cool data people. ;)

No, they are not returned in a deterministic order.
While you are unlikely to see them returned in a different order, that doesn't make the current behavior deterministic or reliable. Tricks like applying ROW_NUMBER() OVER (ORDER BY (SELECT 1)) to the output in an intermediate place like a CTE are similarly not guaranteed to work.
It's one of the features notably missing from STRING_SPLIT(), which I've blogged about:

A way to improve STRING_SPLIT in SQL Server - and you can help
Please help with STRING_SPLIT improvements

And so has Andy Mallon:

When STRING_SPLIT() falls short

Vote and comment here:

Add row position column to STRING_SPLIT

(This item specifically asks for an additional column to be returned to indicate position within the original string but, due to backward compatibility issues, would likely need to be delivered via a new function, similar to how CONCAT_WS came to be.)

As an aside, the documentation originally stated:

The sort order of the output rows matches the order of the substrings in the input string.

That was deliberately changed in this commit to remove any notion that the return order is promised. Now the documentation states:

The output rows might be in any order. The order is not guaranteed to match the order of the substrings in the input string.

Why would they need to remove that promise?
I am not familiar with the internal implementation, but I believe the original version of the documentation was written by observers, not the function authors. That statement was likely written that way because that's what behavior they observed when they used the function. Many of us do the same thing when we tell people we don't need ORDER BY when selecting rows from a clustered table: "they will always come out in this order." Pretty reliable, until the optimizer chooses a different index.
My guess is they are covering their butts for future behavior. Think about all the changes they've been making to how functions work, and also ongoing changes to the optimizer.
Currently, with the simple case:
SELECT * FROM STRING_SPLIT('cow,dog,dinosaur','');

There isn't much opportunity or reason for that to be anything but sequential. But what about when you OUTER APPLY against an nvarchar(max) column between two huge, partitioned tables, and the data starts getting processed in batch mode and/or in parallel? Would they have to write additional logic in the code to ensure all that output came back in the right order? Would it be worth it? Would they have to revisit it for every future function processing / optimizer change to maintain that promise?


How can I compare row values as column name to the another table and Get data (Sql Server-2017)? CREATE TABLE Table1(
ID int NOT NULL,
PTA_1 int NOT NULL,
PTA_2 int NOT NULL,
PTA_3 int NOT NULL,
PTA_4 int NOT NULL,
PTA_5 int NOT NULL,
DTA_1 int NOT NULL,
DTA_2 int NOT NULL,
DTA_3 int NOT NULL,
DTA_4 int NOT NULL,
DTA_5 int NOT NULL
)

INSERT INTO Table1 VALUES(1,1,0,1,0,0,0,1,0,0,0)
INSERT INTO Table1 VALUES(2,1,0,1,0,0,0,1,0,0,0)
INSERT INTO Table1 VALUES(3,1,0,1,0,0,0,1,0,0,0)
INSERT INTO Table1 VALUES(4,1,0,1,0,0,0,1,0,0,0)

CREATE TABLE Table2(
ID int  NOT NULL,
PartNum1 varchar(10),
PartNum2 varchar(10),
PartNum3 varchar(10),
PartNum4 varchar(10),
PartNum5 varchar(10),
DataNum1 varchar(10),
DataNum2 varchar(10),
DataNum3 varchar(10),
DataNum4 varchar(10),
DataNum5 varchar(10)
)

INSERT INTO Table2 VALUES(1,'PTA_1','PTA_2','PTA_3','','','DTA_1','DTA_2','DTA_3','DTA_4','')
INSERT INTO Table2 VALUES(2,'PTA_1','','','','','DTA_1','DTA_2','','','')
INSERT INTO Table2 VALUES(3,'PTA_1','PTA_2','','','','DTA_1','','','','')
INSERT INTO Table2 VALUES(4,'PTA_1','','','','','','','','','')

I have 2 tables as "Table1" and "Table2".
"Table1" is transaction table it contains zero's or 1's value and "Table2" contains "Table1" column names.
Compare "Table2" column name and get data as per relevant ID's.
Result should be like this.

Please provide the query

SELECT t1.ID, t1.column_name, t1.column_value
FROM (
SELECT ID, 'PTA_1' column_name, PTA_1 column_value FROM Table1 UNION ALL
SELECT ID, 'PTA_2' column_name, PTA_2 column_value FROM Table1 UNION ALL
SELECT ID, 'PTA_3' column_name, PTA_3 column_value FROM Table1 UNION ALL
SELECT ID, 'PTA_4' column_name, PTA_4 column_value FROM Table1 UNION ALL
SELECT ID, 'PTA_5' column_name, PTA_5 column_value FROM Table1 UNION ALL
SELECT ID, 'DTA_1' column_name, DTA_1 column_value FROM Table1 UNION ALL
SELECT ID, 'DTA_2' column_name, DTA_2 column_value FROM Table1 UNION ALL
SELECT ID, 'DTA_3' column_name, DTA_3 column_value FROM Table1 UNION ALL
SELECT ID, 'DTA_4' column_name, DTA_4 column_value FROM Table1 UNION ALL
SELECT ID, 'DTA_5' column_name, DTA_5 column_value FROM Table1
) t1
JOIN (
SELECT ID, PartNum1 column_name FROM Table2 UNION ALL
SELECT ID, PartNum2 column_name FROM Table2 UNION ALL
SELECT ID, PartNum3 column_name FROM Table2 UNION ALL
SELECT ID, PartNum4 column_name FROM Table2 UNION ALL
SELECT ID, PartNum5 column_name FROM Table2 UNION ALL
SELECT ID, DataNum1 column_name FROM Table2 UNION ALL
SELECT ID, DataNum2 column_name FROM Table2 UNION ALL
SELECT ID, DataNum3 column_name FROM Table2 UNION ALL
SELECT ID, DataNum4 column_name FROM Table2 UNION ALL
SELECT ID, DataNum5 column_name FROM Table2
) t2 USING (ID, column_name);

fiddle
Move subqueries into CTEs if DBMS and its version allows.
Use UNPIVOT or similar instead of UNION ALL if DBMS and its version allows.

I'm using SQL server, Is it possible to use without mention the column names

WITH
cte1 AS ( SELECT ID, column_name, column_value
          FROM Table1
          UNPIVOT ( column_value FOR column_name IN ( PTA_1, PTA_2, PTA_3, PTA_4, PTA_5, DTA_1, DTA_2, DTA_3, DTA_4, DTA_5 )
                  ) AS unpvt ),
cte2 AS ( SELECT ID, column_value
          FROM Table2
          UNPIVOT ( column_value FOR column_name IN ( PartNum1, PartNum2, PartNum3, PartNum4, PartNum5, DataNum1, DataNum2, DataNum3, DataNum4, DataNum5 )
                  ) AS unpvt
          WHERE column_value > '' )
SELECT cte1.*
FROM cte1
JOIN cte2 ON cte1.ID = cte2.ID AND cte1.column_name = cte2.column_value;

fiddle


Wrong estimate on a query on partitioned tables I wonder why SQL Server makes wrong estimations in such a simple case. There is a scenario.

CREATE PARTITION FUNCTION PF_Test (int) AS RANGE RIGHT
FOR VALUES (20140801, 20140802, 20140803)

CREATE PARTITION SCHEME PS_Test AS PARTITION PF_Test ALL TO ([Primary])

CREATE TABLE A
(
  DateKey int not null,
  Type int not null,
  constraint PK_A primary key (DateKey, Type) on PS_Test(DateKey)
)

INSERT INTO A (DateKey, Type)
SELECT
  DateKey = N1.n  + 20140801,
  Type = N2.n + 1
FROM dbo.Numbers N1
  cross join dbo.Numbers N2
WHERE N1.n BETWEEN 0 AND 2
  and N2.n BETWEEN 0 AND 10000 - 1

UPDATE STATISTICS A (PK_A) WITH FULLSCAN, INCREMENTAL = ON

CREATE TABLE B
(
  DateKey int not null,
  SubType int not null,
  Type int not null,
  constraint PK_B primary key (DateKey, SubType) on PS_Test(DateKey)
)

INSERT INTO B (DateKey, SubType, Type)
SELECT
  DateKey,
  SubType = Type * 10000 + N.n,
  Type
FROM A
  cross join dbo.Numbers N
WHERE N.n BETWEEN 1 AND 10

UPDATE STATISTICS B (PK_B) WITH FULLSCAN, INCREMENTAL = ON

So setup is pretty straightforward, statistics are in place and SQL Server can produce correct estimates when we query one table.

select COUNT(*) from A where DateKey = 20140802
--10000
select COUNT(*) from B where DateKey = 20140802
--100000

But in this simple select estimates are way off, and I see no explanation why.

SELECT a.DateKey, a.Type
FROM A
  JOIN B
    ON b.DateKey = a.DateKey
    AND b.Type = a.Type
WHERE a.DateKey = 20140802

Right after Clustered Index Seek estimation is 57% from actual! Real-world query is even worse, estimate is 2% from actual.

P.S. Numbers table to reproduce setup

DECLARE @UpperBound INT = 1000000;

;WITH cteN(Number) AS
(
  SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id]) - 1
  FROM sys.all_columns AS s1
  CROSS JOIN sys.all_columns AS s2
)
SELECT n = [Number] INTO dbo.Numbers
FROM cteN WHERE [Number] <= @UpperBound;

CREATE UNIQUE CLUSTERED INDEX CIX_Number ON dbo.Numbers(n)
WITH
(
  FILLFACTOR = 100,      -- in the event server default has been changed
  DATA_COMPRESSION = ROW -- if Enterprise & table large enough to matter
);

PPS Same scenario but non-partitioned works perfectly.

The estimates (with the new cardinality estimator) are fine for a normal join, but are less accurate when the optimizer considers the option of a colocated join.
A colocated join (aka per-partition join) is available when joining two tables that are partitioned in the same way. The idea is to join one partition at a time, using nested loops apply driven by partition ids provided by a constant scan (in-memory table of values).
Regular join
Since the colocated join involves a nested loops apply, you can force the optimizer to avoid this by specifying OPTION (HASH JOIN) for example:

The two seeks in that plan are:
Seek Keys[1]: Prefix:
    PtnId1000, [dbo].[A].DateKey = Scalar Operator((3)), Scalar Operator((20140802))
Seek Keys[1]: Prefix:
    PtnId1003, [dbo].[B].DateKey = Scalar Operator((3)), Scalar Operator((20140802))

The optimizer has applied static partition elimination in both cases, giving accurate estimates for both seeks, and the following join.
Colocated join
When the optimizer considers a colocated join (as shown in the question), the seeks are:

Seek Keys[1]: Prefix:
    PtnId1000, [dbo].[A].DateKey = Scalar Operator([Expr1006]), Scalar Operator((20140802))
Seek Keys[1]: Prefix:
    PtnId1003, [dbo].[B].DateKey = Scalar Operator([Expr1006]), Scalar Operator((20140802))

...where [Expr1006] is the value returned by the Constant Scan operator.
The cardinality estimator now cannot see that the DateKey value and the partition id are interdependent, as it could when literal constants were used. In other words, it is not apparent to the estimator that the value inside [Expr1006] specifies the same partition as DateKey = 20140802.
As a consequence, the CE chooses (by default) to estimate the selectivity of the two (apparently independent) predicates using the normal exponential backoff method.
This explains the reduced cardinality estimates feeding the join. The lower apparent cost of this option (due to the misestimate) means the optimizer chooses a colocated join instead of a regular join, even though it is obvious (to humans) that it offers no value.
There are several ways to work around this gap in the logic, including using the query hint USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES'), but this will affect the whole query, not just the problematic colocated join alternative. As Erik notes in his answer, you could also hint the use of the legacy CE.
For more information about colocated joins, see my article Improving Partitioned Table Join Performance


Oracle: Can I change the oracle's password in a RAC environment? Can I change the oracle and grid (OS users) passwords in RAC nodes?
Changing it could break the RAC?

The Oracle Grid and Database installers asked me to input that passwords, unfortunately that was easy and I have to change it now, but there are instances running on that in production.

Yes,

you can change the passwords without any worries.

The reason for that is no cluster configuration file stores information about OS password, hence changing it will not affect already existing user equivalence.

Also, keep in mind that passwords do not need to be identical on both RAC nodes. It is common, but not a must.


Postgresql on Windows and custom extensions I'm running Postgresql on Windows 10 and trying to install custom extension (https://github.com/eulerto/wal2json) for logical decoding.

However, I have to build and install this extension manually. While building is possible via Visual Studio I can't find a way how to install extension via pgxs.  Pgxs seems to be missing in Postgresql distribution package for Windows and I can't find a viable way how to create extension.

pg_config --pgxs shows the following path:

C:/Program Files/PostgreSQL/10/lib/pgxs/src/makefiles/pgxs.mk

but this path doesn't exist.

Does anybody know how to install Postgresql extensions from source code on Windows?

Apparently, I somehow got confused about extensions.

It appeared that everything is much simpler: I just copied compiled dll to postgresql lib folder and everything worked.

Detailed information about how to compile PostgreSQL extensions on Windows can be found here:
https://blog.2ndquadrant.com/compiling-postgresql-extensions-visual-studio-windows/

I'd like to add a note about PGDLLEXPORT. For extensions to work you should add PGDLLEXPORT before two procedures: _PG_init and _PG_output_plugin_init. So code will look like:

PGDLLEXPORT extern void     _PG_init(void);
PGDLLEXPORT extern void     _PG_output_plugin_init(OutputPluginCallbacks *cb);


How can I quantify the business value of normalization? My company is developing an internal application with a SQL server database which was started earlier by another developer.
Now I am Looking at the model,the developer has stopped at 1NF. I proposed going all the way to 3NF but my manager said it would add no business value.
Is there a way to quantify the business values of normalization, specifically going to 3NF?

The actual costs are difficult to quantify as they are often diffused.  Often they appear as slower transactions.  For a system used rarely, this may not be significant.  For a system used for an hour a day, that is taking twice as long as is required the cost would be about 100 hrs a year per person using the system.  Slower transactions may also have higher error rates due to users being distracted or interrupted while processing transactions.

Failure to normalize the data often results in in consistent data through-out the application.  Updates to some records may be missed, or incorrect records may be updated.  Inconsistent data can have serious business and/or legal implications.   Such problems can reduce the reputation of the business.

Updates to non-normalized records may take many times longer than with normalized data. (That is time someone is waiting for the system rather than moving to the next task.).  In some cases the update will fail, or lock the table for excessively long periods of time.


Permission to select sys tables in stored procedure I have implemented the stored procedure to check if a specific [schema].[procedure] exists.

CREATE PROCEDURE [BlobCheckExisting].[usp_DoesStoredProcedureExist]
@schemaName varchar(128),
@procedureName varchar(128)
AS
SELECT IIF (EXISTS (SELECT 1 FROM sys.procedures p INNER JOIN sys.schemas s ON p.[schema_id] = s.[schema_id]
    WHERE s.[name] = @schemaName  AND p.[name] = @procedureName), 1, 0)
GO

When I tested with sa it selected 1 as I expected but when I tried with a minimum permission account which only granted EXECUTE to the procedure, it selected 0.

My assumption of stored procedure mechanism was role/user only requires EXECUTE to perform the procedure.
We don't need to concern the permission setting of each DB object like CRUD to table and so on.

But it seems sys tables are exceptions? Or did I miss anything?
Moreover, If I don't want to grant select sys.procedures/sys.schemas to the role. I just want my procedure to work as expected, what should I do?

My assumption of stored procedure mechanism was role/user only
  requires EXECUTE to perform the procedure. We don't need to concern
  the permission setting of each DB object like CRUD to table and so on.

This statement is true when ownership chaining applies. With an unbroken chain, users do not need permissions on indirectly referenced objects.

However ownership chaining does not apply metadata visibility when selecting from the catalog views. Users must be granted some permissions on an object in order for it to be visible when selecting from the catalog views.

Below is a script that shows this behavior for your use case. If you need to return objects users have no permissions to use, you'll need to use EXECUTE AS or certificate signing to elevate permissions for users of the BlobCheckExisting.usp_DoesStoredProcedureExist procedure.

CREATE SCHEMA BlobCheckExisting;
GO

CREATE PROCEDURE [BlobCheckExisting].[usp_DoesStoredProcedureExist]
@schemaName varchar(128),
@procedureName varchar(128)
AS
SELECT IIF (EXISTS (SELECT 1 FROM sys.procedures p INNER JOIN sys.schemas s ON p.[schema_id] = s.[schema_id]
    WHERE s.[name] = @schemaName  AND p.[name] = @procedureName), 1, 0)
GO

CREATE PROCEDURE dbo.usp_TestProc AS RETURN;
GO

CREATE LOGIN MinimallyPrivilegedUser WITH PASSWORD = 'sdf4$$$gFx&';
GO

CREATE USER MinimallyPrivilegedUser;
GO

GRANT EXECUTE ON BlobCheckExisting.usp_DoesStoredProcedureExist TO MinimallyPrivilegedUser;
GO

--test permissions without permissions on object
EXECUTE AS USER = 'MinimallyPrivilegedUser';
GO
--no rows returned because user has no permissions on dbo.usp_TestProc
EXEC [BlobCheckExisting].[usp_DoesStoredProcedureExist] @schemaName = N'dbo', @procedureName = N'usp_TestProc';
GO
REVERT;
GO
--grant permissions
GRANT EXECUTE ON dbo.usp_TestProc TO MinimallyPrivilegedUser;
GO

--test permissions with permissions on object
EXECUTE AS USER = 'MinimallyPrivilegedUser';
GO
--row returned because user has execute permissions on dbo.usp_TestProc
EXEC [BlobCheckExisting].[usp_DoesStoredProcedureExist] @schemaName = N'dbo', @procedureName = N'usp_TestProc';
GO
REVERT;
GO


Why PostreSQL 9.2 does not use index scan if it can? My PosgreSQL planner (9.2) does not pick index and rather choose to do seq scan.

Here is my table. The index I am talking about is  name_left_prefix

cwu=# \d web_city;
                                  Table "public.web_city"
   Column   |         Type          |                       Modifiers
------------+-----------------------+-------------------------------------------------------
 id         | integer               | not null default nextval('web_city_id_seq'::regclass)
 name       | character varying(64) | not null
 latitude   | double precision      |
 longitude  | double precision      |
 time_zone  | character varying(64) | not null
 population | bigint                |
 country_id | integer               |
 location   | geometry(Point,4326)  |
Indexes:
    "web_city_pkey" PRIMARY KEY, btree (id)
    "name_left_prefix" btree ("left"(name::text, 5))
    "web_city_country_id" btree (country_id)
    "web_city_location_id" gist (location)
Foreign-key constraints:
    "country_id_refs_id_55d3b49fd200671" FOREIGN KEY (country_id) REFERENCES web_country(id) DEFERRABLE INITIALLY DEFERRED

Now I run the query that is supposed to use that index.

cwu=# explain analyze select count(*) from web_city GROUP BY (left(name,5));
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=19016.35..21188.28 rows=94677 width=10) (actual time=502.308..691.288 rows=64459 loops=1)
   ->  Sort  (cost=19016.35..19345.84 rows=131796 width=10) (actual time=502.300..660.121 rows=131796 loops=1)
         Sort Key: ("left"((name)::text, 5))
         Sort Method: external merge  Disk: 2048kB
         ->  Seq Scan on web_city  (cost=0.00..5554.45 rows=131796 width=10) (actual time=0.077..46.672 rows=131796 loops=1)
 Total runtime: 694.977 ms
(6 rows)

It does not use it though. But it does use it when I disable seq_scan and the resulting time is considerably better!

cwu=# set enable_seqscan=false;
SET
cwu=# explain analyze select count(*) from web_city GROUP BY (left(name,5));
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.00..21237.14 rows=94677 width=10) (actual time=0.187..114.887 rows=64459 loops=1)
   ->  Index Scan using name_left_prefix on web_city  (cost=0.00..19394.69 rows=131796 width=10) (actual time=0.163..79.829 rows=131796 loops=1)
 Total runtime: 118.095 ms
(3 rows)

Why this is happening? I don't really desire to set enable_seqscan=false; from my application before making that query.

EDIT: I have just found out that this does not happen on my server that has PostgreSQL 9.2.3. But it does happen on my laptop with PostgreSQL 9.2.4. Maybe I'll try to downgrade just test it out.

EDIT2: After setting work_mem = 4MB to avoid Sort Method: external merge  Disk: 2048kB(Thank you, Igor!) the time has got considerably better for the Seq Scan. Now I approve the decision not to use the index:).

cwu=# explain analyze select count(*) from web_city GROUP BY (left(name,5));
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=6213.43..6671.27 rows=36627 width=10) (actual time=95.879..111.447 rows=64459 loops=1)
   ->  Seq Scan on web_city  (cost=0.00..5554.45 rows=131796 width=10) (actual time=0.424..45.285 rows=131796 loops=1)
 Total runtime: 115.838 ms

PostgreSQL only supports index-only scans since 9.2 and only if the visibility map allows that.

If there is but a single concurrent transaction which has updated a record in a page (and hence there exists a record not visible to all transaction), the visibility map record for this page is invalidated and its records need to be checked for visibility (requiring a heap fetch).

A heap fetch is quite an expensive procedure and it's impossible to tell now many of them would be required on the optimization stage.

So optimizer just tries to play safe and chooses the more predictable path.


Is Using Execute Immediate Bad Practice in Oracle? So, I've been writing a script which creates a sequence (if it doesn't exist) then copies some data including a value from that sequence before deleting the original source data.

As the sequence can't be guaranteed to exist when the script runs, the insert seems (I really don't know Oracle) to have to go in an EXECUTE IMMEDIATE statement.

While this works (abbreviated section of code below), it feels like a horrible cludge. Is this bad practice in Oracle? If so then how should it be done?

DECLARE
  SequenceCount NUMBER;

BEGIN

  BEGIN

    -- Check to see whether the sequence exists. If not create it, if so do nothing.

    SELECT COUNT(1) INTO SequenceCount FROM ALL_SEQUENCES WHERE SEQUENCE_NAME = 'USER_DEVICE_MAP_BACKUP_SEQ';

    IF SequenceCount < 1 THEN
      IF DebugMode = 1 THEN
        DBMS_OUTPUT.PUT_LINE('Creating Sequence.');
      END IF;

      EXECUTE IMMEDIATE 'CREATE SEQUENCE USER_DEVICE_MAP_BACKUP_SEQ START WITH 1 INCREMENT BY 1';
    END IF;

  END;

  BEGIN

    EXECUTE IMMEDIATE 'INSERT INTO USER_DEVICE_MAP_BACKUP SELECT USER_DEVICE_MAP_BACKUP_SEQ.NEXTVAL, USER_ID, DEVICE_ID, sysdate FROM USER_DEVICE_MAP';

    DELETE FROM USER_DEVICE_MAP;

  END;

END;

Well, it may seem kludgy, but compared to using DBMS_SQL, it's downright elegant.


Azure sql database how to track table schema change So someone dropped a column in a Azure sql db. How to show the table schema before the change? Restore to a previous version is not a option as that would override everything.

You should had enabled Azure SQL Auditing.
Here you can find all about it.
https://docs.microsoft.com/en-us/azure/azure-sql/database/auditing-overview#subheading-1
Now you have to restore the database as a new database with an alternative name and the examine te differences in table definition.


Notify when database is accessed in any manner I'm using SQL Server 2012. Is there a way to receive a notification (email/log etc..) if a database or table in a database is touched in anyway (e.g. CRUD via Stored Procs or tables opened via Management Studio)?

I've come across C2 audio tracing while trying to find a solution but that seems server wide, whereas I need it for one particular database. Is this even possible?

Thanks

To clarify, it's actually one particular table I'd like to monitor

You should use SQL Database Audit.

Below is an example that you are looking for (based on your comments)

(Modify as per your criteria and needs)

CREATE DATABASE AUDIT SPECIFICATION [AW_Audit_HR_Employee_DML]

FOR SERVER AUDIT [Server_Audit_AW]

ADD(SELECT,INSERT,UPDATE,DELETE,EXECUTE ON HumanResources.Employee BY sys)

WITH (STATE =ON)

More info at : SQL 2012 Security Audit 2 (Database Audit)


How can I partitioning oracle table when the table still working? How can I partitioning oracle table when the table still working?

Explain:
- I need to partitioning table of Oracle but the table didn't partitioned before.
- I have many scheduler I can't close it all.

How can I partitioning table without closing all scheduled tasks?

I try it

BEGIN
dbms_redefinition.start_redef_table(
uname => 'kiku',
orig_table => 'pletest3_pn_kiku_api_db',
int_table => 'pletest4_pn_kiku_api_db');
END;

ERROR::

Error report:
ORA-12008: error in materialized view refresh path
ORA-14400: inserted partition key does not map to any partition
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 2
12008. 00000 -  "error in materialized view refresh path"
*Cause:    Table SNAP$_<mview_name> reads rows from the view
           MVIEW$_<mview_name>, which is a view on the master table
           (the master may be at a remote site).  Any
           error in this path will cause this error at refresh time.
           For fast refreshes, the table <master_owner>.MLOG$_<master>
           is also referenced.
*Action:   Examine the other messages on the stack to find the problem.
           See if the objects SNAP$_<mview_name>, MVIEW$_<mview_name>,
           <mowner>.<master>@<dblink>, <mowner>.MLOG$_<master>@<dblink>
           still exist.

Just copy your table with a new name and apply the partioning,
drop the original table and rename the copied file to the original table.


Do I need a separate IP address per subnet for every database in a Basic Availability Group? I have a pair of SQL Servers running in two different subnets (in AWS if that matters) and a couple of databases that I want to run in a Basic Availability Group.

My understanding is that in SQL Server 2016 Standard I need a separate Availability Group for every database. Also at least in the user interface it looks like an AG Listener can only belong to a single Availability Group.

I get errors when I try to reuse the IP address for another AG listener (i.e. use 10.0.0.1 and 10.0.1.1 for AG listeners LISTENER1 and LISTENER2)

Do I really need to create a separate AG Listener with two unique IP addresses for every AG and thus every database, or is there a way to reuse either the IP addresses or the AG listeners?

Do I really need to create a separate AG Listener with two unique IP addresses >for every AG and thus every database, or is there a way to reuse either the IP >addresses or the AG listeners?

Yes, really, you do.

In fact, you'll need a unique IP address for each listener for each subnet that listener could potentially live in. Why? Well it's not very helpful to have a listener that can't listen. This is true whether Basic AGs are used or not.


Multi-dimensional range queries with single column indexes I am reading a book that covers many database topics on a very high level, but doesn't dive deeper. It introduces multi-column indexes as a better way to query for 2 dimensional ranges. For example, this query is stated to be inefficient, if latitude and longitude each have single column indexes:

SELECT * FROM restaurants
 WHERE latitude > 51.4946
   AND latitude < 51.5079
   AND longitude > -0.1162
   AND longitude < -0.1004
;

A better way would be to use a multicolumn index using R-trees spanning over both latitude and longitude.

I don't understand why first extracting information of latitude > 51.4946 using a single columnd index and then extacting information longitude > -0.1162 AND longitude < -0.1004 using the other single column index is inefficient? Can you use only 1 index per table when performing a query or is there some other reason for inefficiency?

Answer for SQL Server

The short answer is that the one column index could work depending on your workload & data, but there are different options like included columns in sql server, to store an unordered column.

Two indexes could be used, but in most cases (for sql server) a key lookup operator will be applied to get the other column(s) needed. Accessing two indexes / adding a key lookup operator will be slower due to the added JOIN operator between the two.

An example of when it should go fast is when the optimizer thinks that almost no or no values in the specified range of latitute exist (latitude > 51.4946 AND latitude < 51.5079) while having the one column index, the seek should be pretty fast (keeping in mind that the statistics are correct & up to date). See Example 1 for this part.

Another important part of your example query is that with ranges & indexes, the secondary column will be added as a residual predicate in a simple seek even when both columns are key columns.

See Example 3 for the residual predicate explanation.

Start data

CREATE TABLE [dbo].[restaurants]
(
    [RestaurantId] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    [Latitude] float NOT NULL,
    [Longitude] float NOT NULL
    );

INSERT INTO restaurants([Latitude],[Longitude])
SELECT
       ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) * RAND() /10 as lat ,
       ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) * RAND() /10 as long
FROM MASTER..spt_values

Example 1 One column index + (unused) key lookup

Single column indexes

CREATE INDEX IX_latitude on dbo.restaurants([Latitude]);
CREATE INDEX IX_longitude on dbo.restaurants([Longitude]);

Due to no values returned by the seek on latitude, no key lookup (to get the longitude column and apply a residual predicate on that column) operation happened:

Example 2 Removing the key lookup

When we include the longitude in the index, no key lookup operator is found (but there is a residual predicate on this column.

CREATE INDEX IX_latitude2 on dbo.restaurants([Latitude]) INCLUDE([Longitude]);

From these examples we can make up that, if SQL server cannot find a first selective filter on latitude or longitude one of these will be picked and the residual predicate / key lookup will be the operation that will hurt us when the data set increases.

Example 3 When adding the double key column indexes

Here the decision of selectivity becomes important. Will the first key column selected be latitude or longitude, which one will always be more selective? If that question cannot be answered you could opt to create two indexes:

CREATE INDEX IX_latitude_Longitude on dbo.restaurants([Latitude],[Longitude]);
CREATE INDEX IX_longitude_Latitude on dbo.restaurants([Longitude],[Latitude]);

But due to the fact that you are working with ranges <>, the secondary column will be added as a residual predicate:

The best indexes for the query

All this shows us, that for SQL Server & your particular query the better indexes would be

CREATE INDEX IX_latitude on dbo.restaurants([Latitude]) INCLUDE([Longitude]);
CREATE INDEX IX_longitude on dbo.restaurants([Longitude]) INCLUDE([Latitude]);

Where the included columns are not ordered since we are not getting around the residual predicate.

Unless we are also executing queries with different filters on Longitude or Latitude , such as = on both columns or = on one of the columns, right indexing could remove the residual predicate for these types of queries.

Some more information on indexing range queries can be found here.


Index best performance tuning I have 2 tables Customer and Customerkpi

In both tables we have the column Customer_id which should contain unique values.

Customer_id in Customerkpi is a foreign key of the Customer_id of the table Customer which is the primary key.

I need to create indexes on customerkpi in the best way.

Should I create a primary key on Customer_id and mentioned also the foreign key in this way it creates automatically a clustered index, or should I just declare it as a foreign key and then I create a cluster unique index on Customer_id.

which solution give the best performance?

Should I create a primary key on Customer_id and mentioned also the
  foreign key in this way it creates automatically a clustered index, or
  should I just declare it as a foreign key and then I create a cluster
  unique index on Customer_id.

The physical indexes (clustered and unique) will be identical in both cases so it makes no difference from a performance perspective whether the index also supports the primary key constraint.  I would personally use a clustered primary key index since relational tables should have a primary key and add the foreign key too.


Looping through tables where the name increments monthly I have a table that is missing 18 months worth of data in one column (it is currently 0). I have found the data on another database but the tables I am getting the data from are created monthly. I could update the table by manually changing the table name and date range 18 times but I would rather use a loop (or something better) for this. I'm using SQL Server. My actual problem is a little more complicated but I was hoping to get some points to get me started.

My example is something like this -

UPDATE customers
SET Account_Number = (SELECT Account_Number
                      FROM Hist01_12 -- I need this to increase by one month every loop until Hist06_13
                      WHERE Hist01_12.Account_Number = customers.Account_Number) -- I need this to increase by one month every loop until Hist06_13
WHERE Account_Number = 0
AND Date between '2012-01-01' and '2012-01-31' ;  -- I need these months to increase until '2013-06-01' and '2013-06-30'

Here is a quick solution

DECLARE @command VARCHAR(8000)
DECLARE @Date DATE = '2012-01-01'
DECLARE @Year SMALLINT
DECLARE @Month SMALLINT

WHILE @Date <= '2013-06-30'
BEGIN
    SET @Year = Year(@Date)
    SET @Month = month(@Date)

    SET @command = '
  UPDATE customers
  SET Account_Number =
        (SELECT Account_Number
         FROM Hist' + right('00' + convert(VARCHAR(2), @Month), 2) + '_'
                    + right('00' + convert(VARCHAR(4), @Year), 2) + '
         WHERE Account_Number = customers.Account_Number
        )
  WHERE Account_Number = 0
    AND Date >= ''' + convert(varchar(10),@Date,120) +
    ''' and < ''' + convert(varchar(10),dateadd(month, 1, @Date),120) +
  ''';'

    PRINT @command

    SET @date = dateadd(month, 1, @Date)
END


MySQL sort data before using GROUP_CONCAT to concat it together? I have a system that tracks several thousand objects in realtime and stores their locations in a database table.  Rather than store their location every few seconds I simply store their location, direction, speed and time whenever it changes.  This keeps the table relatively small for the number of objects that I'm tracking.

Each row contains data on a single object at a moment in time and whilst these should go into the database in chronological I want to ensure that I pull the data out in chronological order before concat'ing them together so that I can plot out the data.

My table structure is:

CREATE TABLE tracks (
    id int(11) auto_increment,
    objectId int(11) default 0,
    posTime bigint(15) default 0,
    X float(6,4) default 0.0000,
    Y float(6,4) default 0.0000
    Z int(11) default 0,
    hdg float(4,1) default 0.0,
    spd float(5,1) default 0,
    PRIMARY KEY(id));

By using this query:

SELECT   objectId,
         group_concat(X, ',', Y, ',', hdg, ',', spd separator ',') as trail
FROM     tracks
WHERE    objectId > 0
group by objectId;

I get the data that I want in the format that I want e.g.:

14979491 | -26.3286,-48.4679,206.0,362.0,-26.3684,-48.4892,205.0,357.0

i.e. ObjectId | X1, Y1, heading1, speed1, X2, Y2, heading2, speed2, .... etc

However I'm not 100% confident that the position reports are in the correct time order.

How can I sort the position reports for each id by increasing posTime before concat'ing them together ?  In theory it's just a case of adding ORDER BY posTime ASC but I'm not sure how or where to add this to my query as all previous attempts have failed ...

EDITED TO CLARIFY: I know that group_concat supports an ORDER BY clause but I don't want the column that I sort by (posTime) to be returned in the results.

You need to get confidence in GROUP_CONCAT with ORDER BY embedded.
Only way to do that is the following
SUGGESTION 01 : Proper Indexing
ALTER TABLE tracks ADD INDEX objectId_posTime_ndx (objectId,posTime);

This will help the query optimizer retrieve your data ordered properly.
SUGGESTION 02 : Display posTime as a Debug Step
SELECT   objectId,
         group_concat(posTime ORDER BY posTime) as trail
FROM     tracks
WHERE    objectId > 0
GROUP BY objectId;

If the posTime is ordered as desired, then you should be fine with
SELECT   objectId,
         group_concat(CONCAT(X, ',', Y, ',', hdg, ',', spd) ORDER BY posTime) as trail
FROM     tracks
WHERE    objectId > 0
group by objectId;


Oracle imp crashes without error I have Oracle 10g XE database locally. I export schema with something like

exp %USER%/%PASSWD%@XE statistics=NONE owner=%USER% file=dump.dmp

Exp is 10.2.0.1.0 version - so is the DB.

Than I take this dump to another machine and try to import it to another remote machine. With

imp %USER%/%PASSWD%/'192.168.0.100/%SID%' ignore=y file=dump.dmp full=y

Oracle client with imp is version 11.2.0.1.0 and Oracle DB (normal, not express) is version 11.2.0.2.0

The problem is that after printing several messages about importing tables it suddenly stops:

. . importing table                      "TABLE_1"      17180 rows imported
. . importing table                      "TABLE_2"          0 rows imported
. . importing table                      "TABLE_3"         49 rows imported$:

and does not even give a newline symbol in the end.

The problem persists with rows=N and with only selected tables from this schema.

So can anybody tell:

Where can be some error logs to know what is wrong?
Some workaround (still using exp/imp utilities preferably) - we need to import dumps regularly (it is for continuous integration server), we need to do it from remote host (so impdp is hardly an option with need to mount so remote volumes), dependency on other DB is highly undesirable (dblink).

UPD: Unfortunately for all curious we have solved the problem switching continuous integration system to Oracle XE, where is no such problem. Sysadm has already switched off this server, so I cannot try anything. Sorry.

Maybe an error appears in the target database's alert log?  Run this:

SQL> show parameter background_dump_dest

...and go to the directory given to check the alert_<SID>.log.  Or do a trace of the imp session in the target database instance.  (Usually the errors appear in the exp / imp output themselves (saved to a log with LOG=log_file.log ) but I assume the export ran file with no warnings and completes with):

"Export terminated successfully without warnings."

When you are copying the file, are you sure it's not getting corrupted somehow? (md5sum remains the same)
Did you try importing into an empty schema so you don't have to set IGNORE=Y?
And when you wrote:

  The problem persists with rows=N and with only selected tables from this schema.

Did you mean no matter which one or two tables you choose, it still crashes?
Try simulating an import with SHOW=Y?


Get records with max value on grouped SQL results (mysql) How do you get the rows that contain the max value on created_dt for each by conversation_id grouped set?

I've seen some complicated variations on similiar questions, and none with a good answer.
I've put together a simple example:

id | member_a_id | member_b_id | conversation_id | created_dt          | content
1  | 100         | 110         | 1               | 2018-04-01 12:00:00 | aaa
2  | 100         | 110         | 1               | 2018-04-01 13:00:00 | bbb
3  | 105         | 100         | 2               | 2018-04-01 14:00:00 | ccc
4  | 110         | 100         | 1               | 2018-04-01 15:00:00 | ddd
5  | 100         | 110         | 1               | 2018-04-01 16:00:00 | eee

Desired result set:

member_a_id | member_b_id | conversation_id | created_dt          | content
100         | 110         | 1               | 2018-04-01 16:00:00 | eee
105         | 100         | 2               | 2018-04-01 14:00:00 | ccc

You can get max(created_dt) of each conversation and then join it with main table in this way:

select   tbl.*
from     tbl
join     (select   conversation_id, max(created_dt) created_dt
          from     tbl
          group by conversation_id) t1
on       tbl.conversation_id = t1.conversation_id
and      tbl.created_dt = t1.created_dt;

id | member_a_id | member_b_id | conversation_id | created_dt          | content
-: | ----------: | ----------: | --------------: | :------------------ | :------
 3 |         105 |         100 |               2 | 2018-04-01 14:00:00 | ccc
 5 |         100 |         110 |               1 | 2018-04-01 16:00:00 | eee

db<>fiddle here


logical reads on global temp table, but not on session-level temp table Consider the following simple MCVE:

SET STATISTICS IO, TIME OFF;
USE tempdb;

IF OBJECT_ID(N'tempdb..#t1', N'U') IS NOT NULL DROP TABLE #t1;
CREATE TABLE #t1
(
    r int NOT NULL
);

IF OBJECT_ID(N'tempdb..##t1', N'U') IS NOT NULL DROP TABLE ##t1;
CREATE TABLE ##t1
(
    r int NOT NULL
);

IF OBJECT_ID(N'dbo.s1', N'U') IS NOT NULL DROP TABLE dbo.s1;
CREATE TABLE dbo.s1
(
    r int NOT NULL
        PRIMARY KEY CLUSTERED
);

INSERT INTO dbo.s1 (r)
SELECT TOP(10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.syscolumns sc1
    CROSS JOIN sys.syscolumns sc2;
GO

When I run the following inserts, inserting into #t1 shows no stats I/O for the temp table.  However, inserting into ##t1 does show stats I/O for the temp table.

SET STATISTICS IO, TIME ON;
GO

INSERT INTO #t1 (r)
SELECT r
FROM dbo.s1;

The stats output:

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.
Table 's1'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 9 ms.

(10000 rows affected)

INSERT INTO ##t1 (r)
SELECT r
FROM dbo.s1;

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.
Table '##t1'. Scan count 0, logical reads 10016, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 's1'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 45 ms.

(10000 rows affected)

Why are there so many reads on the ##temp table when I'm only inserting into it?

Minimal logging is not being used when using INSERT INTO and global temp tables

Inserting one million rows in a global temp table by using INSERT INTO

INSERT INTO ##t1 (r)
SELECT top(1000000) s1.r
FROM dbo.s1
CROSS APPLY  dbo.s1 S2;

When running SELECT * FROM fn_dblog(NULL, NULL) while the above query is executing,  ~1M rows are returned.

One LOP_INSERT_ROW operation for each row + other
log data.

The same insert on a local temp table

INSERT INTO #t1 (r)
SELECT top(1000000) s1.r
FROM dbo.s1
CROSS APPLY  dbo.s1 S2;

Only going up to 700 rows returned by  SELECT * FROM fn_dblog(NULL, NULL)

Minimal logging

Inserting one million rows in a global temp table by using SELECT INTO

SELECT top(1000000) s1.r
INTO ##t2
FROM dbo.s1
CROSS APPLY  dbo.s1 S2;

SELECT INTO a global temp table with 10k records

SELECT s1.r
INTO ##t2
FROM dbo.s1;

Time and IO Statistics

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
Table 's1'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 10 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

Based on this blogpost we can add TABLOCK to initiate minimal logging on a heap table

INSERT INTO ##t1 WITH(TABLOCK) (r)
SELECT   s1.r
FROM dbo.s1

Low logical reads

Table 's1'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(10000 rows affected)

Part of an answer by @PaulWhite on how to achieve minimal logging on temporary tables

  No. Local temporary tables (#temp) are private to the creating
  session, so a table lock hint is not required. A table lock hint would
  be required for a global temporary table (##temp) or a regular table
  (dbo.temp) created in tempdb, because these can be accessed from
  multiple sessions.

Creating a regular table to test this:

CREATE TABLE dbo.bla
(
    r int NOT NULL
);

Filling it up with 1M records

INSERT INTO bla
SELECT   top(1000000)s1.r
FROM dbo.s1
CROSS APPLY  dbo.s1 S2;

>1M logical reads on this table

Table 's1'. Scan count 17, logical reads 155, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'bla'. Scan count 0, logical reads 1001607, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Paul White's answer explaining the logical reads reported on the global temp table

  Generally, logical reads are reported for the target table when the
  insert is not minimally logged.

  These logical reads are associated with finding a place in the
  existing structure to add the new rows. Minimally-logged inserts use
  the bulk-loading mechanism, which allocates whole new pages/extents
  (and so does not need to read the target structure in the same way).

Conclusion

The conclusion being that the INSERT INTO is not able to use minimal logging, resulting in logging every inserted row individually in the log file of tempdb when used in combination with a global temp table / normal table.
Whereas the local temp table/ SELECT INTO/ INSERT INTO ... WITH(TABLOCK) is able to use minimal logging.


SQL Server query with Linked Server running without Parallelism I have a query that includes accessing a table via linked server. When it runs it's doing so without parallelism. But if I comment out the join to the linked server table it runs with parallelism and completes almost instantly.

The row count of the table accessed by the linked server is 2m.

Could the linked server be causing the query to run without parallelism?

Here is the plan.

Joining to a table via a Linked Server is problematic.  You can workaround the issue by pulling the required rows from the remote table into a local #temp table, and JOINing against that #temp table instead.


Does anyone know of resources for the Oracle 1Z0-072 exam objectives? I am currently studying for the Oracle 1Z0-072 certification exam and I'm finding some of the early objects very ambiguous and difficult to study for.  Topics such as:

Relational Database Models
Connecting to Oracle Databases

What do they mean by those? What skills or knowledge do I need to possess?

Thanks in advance for any direction or insight!

https://education.oracle.com/oracle-database-12c-r2-administration/pexam_1Z0-072

Relational Database Models

It is a basic DBA exam. I think they meant Relational Data Structures, which you would need anyway. Maybe this specific part: Introduction to Oracle Database. Nothing complicated, I have never ever encountered a modeling question on the exams from the DBA track.

Connecting to Oracle Databases

My bet is on Configuration and Administration of Oracle Net Services. Connection Manager is the odd one here, it is nice to know what it is, but I don't think the details are needed, it is a very rarely used feature.

Both of the above chapters cover other exam topics as well.


Using EXCEPT before WITH RECURSIVE in SQLite 3 Is there anyway to do this? I'm trying to do something like this:

SELECT x
FROM table
EXCEPT
WITH RECURSIVE ...();

The WITH goes first in a query. You can use this:

WITH RECURSIVE ct AS (...)
SELECT x
FROM table
EXCEPT
SELECT y
FROM ct ;

For the record, CTEs can be (in standard SQL) inside subqueries but not many DBMS have implemented this syntax:

SELECT x
FROM table
EXCEPT
SELECT y
FROM
  ( WITH RECURSIVE ct AS (...)
    SELECT y
    FROM ct
  ) AS c ;

or this simpler one:

SELECT x
FROM table
EXCEPT
( WITH RECURSIVE ct AS (...)
  SELECT y
  FROM ct
) ;


MySQL 5.7 - Where is the mysql_secret file on Windows 7? I've just installed a copy of MySQL 5.7 for testing, and I love the fact that it now only creates a single root account, and puts the password into a file called .mysql_secret.

There's just one problem. Where can I find the above mentioned file. On Linux it is apparently found in $HOME, but my test DB is on Windows.

I tried various places including %USERPROFILE% but I just can't find it anywhere!!

Can anyone please tell me where to find this file? As otherwise my testing will be over before it has begun.

My blind guess would be to look in %APPDATA%\MySQL on your system as follows:

cd %APPDATA%
cd MySQL
dir .my_secret

For those who use the MySQL no-install Zip File (such as myself), you will not see one.

If .my_secret is not on your Windows servers, I have an alternative. You could start up mysqld manually with skip-grant-tables, set the password

net stop mysql
cd "C:\Program Files (x86)\MySQL\MySQL 5.7\bin"
start mysqld --skip-grant-tables
mysql -ANe"update mysql.user set authentication_string=PASSWORD('mynewpassword') where user='root'"
mysqladmin shutdown
net start mysql
mysql -uroot -p
Enter password: (enter the password 'mynewpassword' and hit <Enter>)

Give it a Try !!!


Is sql_variant compatible with First Normal Form (1NF)? 1NF rule says that we should not keep data of different types in one single column. Does it mean that sql_variant is not compatible with first normal form hence should not be used?

1NF rule says that we should not keep data of different types in one single column.

There's no general agreement about exactly what first normal form (1NF) means in detail. Personally, I prefer Chris Date's current interpretation, which simply states that all relation variables (relvars) are in 1NF by definition.

  Does it mean that sql_variant is not compatible with first normal form hence should not be used?

This is a bit imprecise since normal forms are properties of the logical model, whereas sql_variant is a physical (SQL Server-specific) type. We would need to define a mapping from relational model type (or domain) to physical implementation for this to truly make sense.

Nevertheless, I will say that sql_variant is not inherently an indication that the associated model violates 1NF - so long as the model defines the range of allowable attribute values to match those storable in sql_variant (with or without suitable constraints). The fact that sql_variant can store values of several different underlying types is neither here nor there. We might as well argue that float types violate 1NF since they can contain integers as well as reals etc.

In other words, a physical table using sql_variant can be a valid representation of a relation variable (within the general limitations of SQL anyway). It simply depends on what the underlying logical model says about the range of values allowed in the attribute.

Consider also that whatever we might physically store in a sql_variant might also be stored in a string representation, or as a binary value. String and binary values do not necessarily violate 1NF, so why should sql_variant? Similar arguments can be made about xml or json (and array types for databases that support such things).

All of the above is orthogonal to the question of whether using sql_variant is a feature of a "good" design/implementation or not.

Further reading:

Facts and Fallacies about First Normal Form by Anith Sen
First Normal Form in Theory and Practice by Fabian Pascal
The Third Manifesto - C J Date and Hugh Darwen
First Normal Form: Definitive Definition (Q & A)

Footnote

In relational maths, some domains may have relations as elements.  If such a domain were used as the basis for an attribute in another relation, the result would not be in what Codd originally called normal form. Removing such relations from consideration without reducing the expressive power of the model was the whole point.  Relvars eliminate this kind of nesting by definition, as stated.  Beginners often get around the restriction in physical design by storing CSV strings in columns. There are consequences of such designs with regard to keyed access to data.


Query doesn't respond when adding two columns When I add two columns to my select the query doesn't respond. The type of column is nvarchar(2000). It's a little unusual.

The SQL Server version is 2014.
There is only one primary index.
The whole records is only 1000 rows.

Here is execution plan before (XML showplan):

Execution plan after (XML showplan):

Here is the query:

select top(100)
  Batch_Tasks_Queue.id,
  btq.id,
  Batch_Tasks_Queue.[Parameters], -- this field
  btq.[Parameters]  -- and this field
from
        Batch_Tasks_Queue with(nolock)
    inner join  Batch_Tasks_Queue btq with(nolock)  on  Batch_Tasks_Queue.Start_Time < btq.Start_Time
                            and btq.Start_Time < Batch_Tasks_Queue.Finish_Time
                            and Batch_Tasks_Queue.id <> btq.id
                            and btq.Start_Time is not null
                            and btq.State in (3, 4)
where
    Batch_Tasks_Queue.Start_Time is not null
    and Batch_Tasks_Queue.State in (3, 4)
    and Batch_Tasks_Queue.Operation_Type = btq.Operation_Type
    and Batch_Tasks_Queue.Operation_Type not in (23, 24, 25, 26, 27, 28, 30)

order by
    Batch_Tasks_Queue.Start_Time desc

The whole result count is 17 rows. The dirty data (nolock hint) is not important.

Here is the table structure:

CREATE TABLE [dbo].[Batch_Tasks_Queue](
    [Id] [int] NOT NULL,
    [OBJ_VERSION] [numeric](8, 0) NOT NULL,
    [Operation_Type] [numeric](2, 0) NULL,
    [Request_Time] [datetime] NOT NULL,
    [Description] [varchar](1000) NULL,
    [State] [numeric](1, 0) NOT NULL,
    [Start_Time] [datetime] NULL,
    [Finish_Time] [datetime] NULL,
    [Parameters] [nvarchar](2000) NULL,
    [Response] [nvarchar](max) NULL,
    [Billing_UserId] [int] NOT NULL,
    [Planned_Start_Time] [datetime] NULL,
    [Input_FileId] [uniqueidentifier] NULL,
    [Output_FileId] [uniqueidentifier] NULL,
    [PRIORITY] [numeric](2, 0) NULL,
    [EXECUTE_SEQ] [numeric](2, 0) NULL,
    [View_Access] [numeric](1, 0) NULL,
    [Seeing] [numeric](1, 0) NULL,
 CONSTRAINT [PKBachTskQ] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Batch_Tasks_QueueData]
) ON [Batch_Tasks_QueueData] TEXTIMAGE_ON [Batch_Tasks_QueueData]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Batch_Tasks_Queue]  WITH NOCHECK ADD  CONSTRAINT [FK0_BtchTskQ_BlngUsr] FOREIGN KEY([Billing_UserId])
REFERENCES [dbo].[BILLING_USER] ([ID])
GO
ALTER TABLE [dbo].[Batch_Tasks_Queue] CHECK CONSTRAINT [FK0_BtchTskQ_BlngUsr]
GO

Summary

The main problems are:

The optimizer's plan selection assumes a uniform distribution of values.
A lack of suitable indexes means:

Scanning the table is the only option.
The join is a naive nested loops join, rather than an index nested loops join. In a naive join, the join predicates are evaluated at the join rather than being pushed down the inner side of the join.

Details

The two plans are fundamentally pretty similar, though performance may be very different:

Plan with the extra columns

Taking the one with the extra columns that doesn't complete in a reasonable time first:

The interesting features are:

The Top at node 0 limits the rows returned to 100. It also sets a row goal for the optimizer, so everything below it in the plan is chosen to return the first 100 rows quickly.
The Scan at node 4 finds rows from the table where the Start_Time is not null, State is 3 or 4, and Operation_Type is one of the listed values. The table is fully scanned once, with each row being tested against the predicates mentioned. Only rows that pass all the tests flow on to the Sort. The optimizer estimates that 38,283 rows will qualify.
The Sort at node 3 consumes all the rows from the Scan at node 4, and sorts them in order of Start_Time DESC. This is the final presentation order requested by the query.
The optimizer estimates that 93 rows (actually 93.2791) will have to be read from the Sort in order for the whole plan to return 100 rows (accounting for the expected effect of the join).
The Nested Loops join at node 2 is expected to execute its inner input (the lower branch) 94 times (actually 94.2791). The extra row is required by the stop parallelism exchange at node 1 for technical reasons.
The Scan at node 5 fully scans the table on each iteration. It finds rows where Start_Time is not null and State is 3 or 4. This is estimated to produce 400,875 rows on each iteration. Over 94.2791 iterations, the total number of rows is almost 38 million.
The Nested Loops join at node 2 also applies the join predicates. It checks that Operation_Type matches, that the Start_Time from node 4 is less than the Start_Time from node 5, that the Start_Time from node 5 is less than the Finish_Time from node 4, and that the two Id values do not match.
The Gather Streams (stop parallelism exchange) at node 1 merges the ordered streams from each thread until 100 rows have been produced. The order-preserving nature of the merge across multiple streams is what requires the extra row mentioned in step 5.

The great inefficiency is obviously at steps 6 and 7 above. Fully scanning the table at node 5 for each iteration is only even slightly reasonable if it only happens 94 times as the optimizer predicts. The ~38 million per-row set of comparisons at node 2 is also a large cost.

Crucially, the 93/94 row row goal estimation is also quite likely to be wrong, since it depends on the distribution of values. The optimizer assumes uniform distribution in the absence of more detailed information. In simple terms, this means that if 1% of the rows in the table are expected to qualify, the optimizer reasons that to find 1 matching row, it needs to read 100 rows.

If you ran this query to completion (which might take a very long time), you would most likely find that many more than 93/94 rows had to be read from the Sort in order to finally produce 100 rows. In the worst case, the 100th row would be found using the last row from the Sort. Assuming the optimizer's estimate at node 4 is correct, this means running the Scan at node 5 38,284 times, for a total of something like 15 billion rows. It could be more if the Scan estimates are also off.

This execution plan also includes a missing index warning:

/*
The Query Processor estimates that implementing the following index
could improve the query cost by 72.7096%.

WARNING: This is only an estimate, and the Query Processor is making
this recommendation based solely upon analysis of this specific query.
It has not considered the resulting index size, or its workload-wide
impact, including its impact on INSERT, UPDATE, DELETE performance.
These factors should be taken into account before creating this index.
*/

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Batch_Tasks_Queue] ([Operation_Type],[State],[Start_Time])
INCLUDE ([Id],[Parameters])

The optimizer is alerting you to the fact that adding an index to the table would improve performance.

Plan without the extra columns

This is essentially the exact same plan as the previous one, with the addition of the Index Spool at node 6 and the Filter at node 5. The important differences are:

The Index Spool at node 6 is an Eager Spool. It eagerly consumes the result of the scan below it, and builds a temporary index keyed on Operation_Type and Start_Time, with Id as a non-key column.
The Nested Loops Join at node 2 is now an index join. No join predicates are evaluated here, instead the per-iteration current values of Operation_Type, Start_Time, Finish_Time, and Id from the scan at node 4 are passed to the inner-side branch as outer references.
The Scan at node 7 is performed only once.
The Index Spool at node 6 seeks rows from the temporary index where Operation_Type matches the current outer reference value, and the Start_Time is in the range defined by the Start_Time and Finish_Time outer references.
The Filter at node 5 tests Id values from the Index Spool for inequality against the current outer reference value of Id.

The key improvements are:

The inner side scan is performed only once
A temporary index on (Operation_Type, Start_Time) with Id as an included column allows an index nested loops join. The index is used to seek matching rows on each iteration rather than scanning the whole table each time.

As before, the optimizer includes a warning about a missing index:

/*
The Query Processor estimates that implementing the following index
could improve the query cost by 24.1475%.

WARNING: This is only an estimate, and the Query Processor is making
this recommendation based solely upon analysis of this specific query.
It has not considered the resulting index size, or its workload-wide
impact, including its impact on INSERT, UPDATE, DELETE performance.
These factors should be taken into account before creating this index.
*/

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Batch_Tasks_Queue] ([State],[Start_Time])
INCLUDE ([Id],[Operation_Type])
GO

Conclusion

The plan without the extra columns is faster because the optimizer chose to create a temporary index for you.

The plan with the extra columns would make the temporary index more expensive to build. The [Parameters] column is nvarchar(2000), which would add up to 4000 bytes to each row of the index. The additional cost is enough to convince the optimizer that building the temporary index on each execution would not pay for itself.

The optimizer warns in both cases that a permanent index would be a better solution. The ideal composition of the index depends on your wider workload. For this particular query, the suggested indexes are a reasonable starting point, but you should understand the benefits and costs involved.

Recommendation

A wide range of possible indexes would be beneficial for this query. The important takeaway is that some sort of nonclustered index is needed. From the information provided, a reasonable index in my opinion would be:

CREATE NONCLUSTERED INDEX i1
ON dbo.Batch_Tasks_Queue (Start_Time DESC)
INCLUDE (Operation_Type, [State], Finish_Time);

I would also be tempted to organize the query a little better, and delay looking up the wide [Parameters] columns in the clustered index until after the top 100 rows have been found (using Id as the key):

SELECT TOP (100)
    BTQ1.id,
    BTQ2.id,
    BTQ3.[Parameters],
    BTQ4.[Parameters]
FROM dbo.Batch_Tasks_Queue AS BTQ1
JOIN dbo.Batch_Tasks_Queue AS BTQ2 WITH (FORCESEEK)
    ON BTQ2.Operation_Type = BTQ1.Operation_Type
    AND BTQ2.Start_Time > BTQ1.Start_Time
    AND BTQ2.Start_Time < BTQ1.Finish_Time
    AND BTQ2.id != BTQ1.id
    -- Look up the [Parameters] values
JOIN dbo.Batch_Tasks_Queue AS BTQ3
    ON BTQ3.Id = BTQ1.Id
JOIN dbo.Batch_Tasks_Queue AS BTQ4
    ON BTQ4.Id = BTQ2.Id
WHERE
    BTQ1.[State] IN (3, 4)
    AND BTQ2.[State] IN (3, 4)
    AND BTQ1.Operation_Type NOT IN (23, 24, 25, 26, 27, 28, 30)
    AND BTQ2.Operation_Type NOT IN (23, 24, 25, 26, 27, 28, 30)
    -- These predicates are not strictly needed
    AND BTQ1.Start_Time IS NOT NULL
    AND BTQ2.Start_Time IS NOT NULL
ORDER BY
    BTQ1.Start_Time DESC;

Where the [Parameters] columns are not needed, the query can be simplified to:

SELECT TOP (100)
    BTQ1.id,
    BTQ2.id
FROM dbo.Batch_Tasks_Queue AS BTQ1
JOIN dbo.Batch_Tasks_Queue AS BTQ2 WITH (FORCESEEK)
    ON BTQ2.Operation_Type = BTQ1.Operation_Type
    AND BTQ2.Start_Time > BTQ1.Start_Time
    AND BTQ2.Start_Time < BTQ1.Finish_Time
    AND BTQ2.id != BTQ1.id
WHERE
    BTQ1.[State] IN (3, 4)
    AND BTQ2.[State] IN (3, 4)
    AND BTQ1.Operation_Type NOT IN (23, 24, 25, 26, 27, 28, 30)
    AND BTQ2.Operation_Type NOT IN (23, 24, 25, 26, 27, 28, 30)
    AND BTQ1.Start_Time IS NOT NULL
    AND BTQ2.Start_Time IS NOT NULL
ORDER BY
    BTQ1.Start_Time DESC;

The FORCESEEK hint is there to help ensure the optimizer chooses an indexed nested loops plan (there is a cost-based temptation for the optimizer to select a hash or (many-many) merge join otherwise, which tends not to work well with this type of query in practice. Both end up with large residuals; many items per bucket in the case of the hash, and many rewinds for the merge).

Alternative

If the query (including its specific values) were particularly critical for read performance, I would consider two filtered indexes instead:

CREATE NONCLUSTERED INDEX i1
ON dbo.Batch_Tasks_Queue (Start_Time DESC)
INCLUDE (Operation_Type, [State], Finish_Time)
WHERE
    Start_Time IS NOT NULL
    AND [State] IN (3, 4)
    AND Operation_Type <> 23
    AND Operation_Type <> 24
    AND Operation_Type <> 25
    AND Operation_Type <> 26
    AND Operation_Type <> 27
    AND Operation_Type <> 28
    AND Operation_Type <> 30;

CREATE NONCLUSTERED INDEX i2
ON dbo.Batch_Tasks_Queue (Operation_Type, [State], Start_Time)
WHERE
    Start_Time IS NOT NULL
    AND [State] IN (3, 4)
    AND Operation_Type <> 23
    AND Operation_Type <> 24
    AND Operation_Type <> 25
    AND Operation_Type <> 26
    AND Operation_Type <> 27
    AND Operation_Type <> 28
    AND Operation_Type <> 30;

For the query that does not need the [Parameters] column, the estimated plan using the filtered indexes is:

The index scan automatically returns all qualifying rows without evaluating any additional predicates. For each iteration of the index nested loops join, the index seek performs two seeking operations:

A seek prefix match on Operation_Type and State = 3, then seeking the range of Start_Time values, residual predicate on the Id inequality.
A seek prefix match on Operation_Type and State = 4, then seeking the range of Start_Time values, residual predicate on the Id inequality.

Where the [Parameters] column is needed, the query plan simply adds a maximum of 100 singleton lookups for each table:

As a final note, you should consider using the built-in standard integer types instead of numeric where applicable.


high cached_page_count - when to start to worry and what can I do about it? Below are my top 10 cached page counts.

We're having query performance issues with the top 2 tables; the queries never change but the run times are very volatile.

I cant seem to work out were the issues are and suspect through a bit of online digging its got something to do with available space within SQL Server itself but really have no clue where to best start the investigation.

We have seen a pattern in that the database is growing as expected daily and appears to be hitting its limit routinely forcing an extra GB to be added.

Yesterday we gave it a bit of extra overhead and it's 2 hours ahead of schedule which leads us to believe maybe when it's at its limit and is trying to pinch resources from elsewhere to re-allocate thus the reason for volatile build times rather than consistent build times.

We also noticed there is a Sunday job that runs to re-index fragmented indexes and frees up 40GB but within a day this is swallowed back up, so maybe something there to look into also.

╔════════════════════╦═══════════════════════════════════════╦══════════╗
║ cached_pages_count ║                 name                  ║ index_id ║
╠════════════════════╬═══════════════════════════════════════╬══════════╣
║            3507035 ║ CDI_OP_APPOINTMENT                    ║        1 ║
║            2588843 ║ CDO_OP_APPOINTMENT                    ║        1 ║
║            2035499 ║ __tblDataServicesEventDetail          ║        1 ║
║            1552008 ║ CDI_RTT_PATIENT_PATHWAY_EVENT         ║        1 ║
║            1369916 ║ CDO_RTT_PATIENT_PATHWAY_EVENT         ║        1 ║
║            1136325 ║ WRK_CDI_OP_APPOINTMENT_CODES_CHECKSUM ║        1 ║
║            1120941 ║ LZO_SCHEDULE                          ║        1 ║
║             941882 ║ LZO_CNSPATIENTCLINICALNOTE            ║        1 ║
║             890873 ║ CDO_CLINIC_SESSION_SLOT               ║        1 ║
║             848078 ║ CDI_CLINIC_SESSION_SLOT               ║        1 ║
╚════════════════════╩═══════════════════════════════════════╩══════════╝

The fact that these tables both cause performance problems and use lots of pages in the cache isn't at all surprising.

I would rather find it expected. You call a couple of tables that (probably) both are on the larger side, and you also do lots of activity on those tables.

Perhaps you have lots and lots of inserts, which quickly causes the clustered index to become fragmented (hence freeing up space after rebuild)? If that is the case, then perhaps the key for the clustered index might have been badly chosen.

I would start by collecting more information. Possibly running a trace to see what activity you have going on.


How to prevent misuse of rights in monitoring application? I have a monitoring application that runs on the computer of each user in my organization. This application needs to insert some monitoring data in several tables of a database (MS SQL Server 2008 R2). We are using Windows authentication.

If I give INSERT rights or grant access to execute a stored procedure, how will I make sure that the users cannot misuse these rights and say for example, add a lot of unwanted data in the tables?

Easiest is to not grant the table at all, not to end users. Create a stored procedure that does the dml and grant the procedure to the monitoring users, not to the regular users. That way no misuse is possible.


How to clear gcache pages in Percona PXC I have a 4 node cluster(percona PXC) and one of the nodes have more than 150 gcache disk files each of size ~128 MB. I would like to purge or flush them so that the server user fresh gcache disk file.

I am not sure if the node restart will do it for me.

Thank You.

shutdown the node while maintaining quorum. so that there will be no downtime for application. manually move those all gcache files to some other location(don't delete them for safer side).Restart the server. You will get new gcache file.


Report individual changes to data I need to produce a daily extract on the individual changes to a dataset. So, there are 14 tables containing product and customer information and the extract must only show records from each table that are newly entered or records that have had any column updated.

What would be the best way to do this please?

We are using SQL Server 2008 R2, and develop in C#.

Thanks

We've used Change Tracking to handle this.  We've found it lightweight and easy to set up and administer.  The largest system's moving tens of thousands of rows (from a table containing millions) per day without signs of stress. Of course your configuration will dictate your throughput.

It's big cousin - Change Data Capture (CDC) - will handle the case where you need to know the "before" and "after" values for each and every change that happens.


What determines if a bytea has 1 or 4 extra bytes? When inserting a BYTEA string in PostgreSQL the documentation says that such a string will occupy 1 or 4 bytes plus byte string length.

The question that I can't seem to find the answer to is what determines whether 1 or 4 bytes are added and is this effect consistent or will it vary depending on some factor or other?

I believe the answer is in this header comment of heaptuple.c:

* Before Postgres 8.3 varlenas always had a 4-byte length header, and
* therefore always needed 4-byte alignment (at least).  This wasted space
* for short varlenas, for example CHAR(1) took 5 bytes and could need up to
* 3 additional padding bytes for alignment.
*
* Now, a short varlena (up to 126 data bytes) is reduced to a 1-byte header
* and we don't align it.  To hide this from datatype-specific functions that
* don't want to deal with it, such a datum is considered "toasted" and will
* be expanded back to the normal 4-byte-header format by pg_detoast_datum.


Query to polybase external table giving error "Access to the remote server is denied because no login-mapping exists." we have successfully created all pre-requisite of creating external tables using SQL Server 2017 Standard Edition CU22 Polybase.
The issue we have is the non-sysadmin login can't query those external tables .It shows below error.

Msg 7416, Level 16, State 2, Line 29
Access to the remote server is denied because no login-mapping exists.

The sysadmin login can query external tables without any issue.
Is there anybody saw this issue and can help to resolve it.

I don't know if this is directly related to your issue but the known limitations on PolyBase state that:

In order to use PolyBase, you must have sysadmin or CONTROL SERVER
level permissions on the database

As such you might try to:
GRANT CONTROL SERVER TO [LoginName]

However know that CONTROL SERVER gives you many rights on the instance such as the right to initiate a shut down.


The best MySQL Settings for 32GB RAM on a Dedicated Server I am worrying about my memory usage as it is reaching more than 90% if I will not restart php-fpm.

I have the following configuration on my MySQL:

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8

myisam-recover         = BACKUP
max_connections        = 250
table_cache            = 4K
wait_timeout            = 1200

query_cache_limit       = 1M
query_cache_size        = 128M
join_buffer_size        = 2M

log_error = /var/log/mysql/error.log

log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time = 1

expire_logs_days        = 10
max_binlog_size         = 100M

innodb_buffer_pool_size = 5G
[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[isamchk]
key_buffer              = 16M

!includedir /etc/mysql/conf.d/

I am running nginx & php-fpm.

Two of my sites with lots of traffic are configured with this settings:

PHP-FPM pm.max_children: 64
PHP-FPM pm.start_servers: 20
PHP-FPM pm.min_spare_servers: 15
PHP-FPM pm.max_spare_servers: 64
PHP-FPM pm.max_requests: 500

The rest of my sites are configured using the following settings:

PHP-FPM pm.max_children: 64
PHP-FPM pm.start_servers: 10
PHP-FPM pm.min_spare_servers: 5
PHP-FPM pm.max_spare_servers: 15
PHP-FPM pm.max_requests: 500

When I restart mysql server and php-fpm, the initial size of memory consumed is 85%.
I created a script to monitor the consumption of the memory and if it reaches 90% I automatically restarted php-fpm. I don't know if this is a good idea.

When the number of php-fpm server running increase, the memory usage also increased rapidly.

I used the following command to monitor the number of php-fpm server running:

smem -u -t -k

The example output are:

User     Count     Swap      USS      PSS      RSS
web8        22        0  1017.7M     1.0G     1.4G
web2        20        0     1.1G     1.1G     1.3G

The output above is good when the number of server/count does not increase. But when it increase up to 64, which is the highest value of PHP-FPM pm.max_spare_servers. There come's the problem. The memory increased rapidly.

BTW, here's the specs of my server:

Intel® Xeon® E3-1270 v2 Single Processor - Quad Core Dedicated Server
CPU Speed: 4 x 3.5 Ghz w/ 8MB Smart Cache
Motherboard: SuperMicro X9SCM-F
Total Cores: 4 Cores + 8 Threads
RAM: 32 GB DDR3 1333 ECC
Hard Drive: 120GB
Smart Cache: 8MB

Can anybody suggest the best settings for my server?

Configure some important variables in my.cnf file and restart MySQL service :
max_connections=500
max_connect_errors=100
innodb_buffer_pool_size={ value should be 70% of total Memory }
innodb_log_buffer_size={ less than 4 MB if there are TEXT/BLOB then set to more value between 8-128 MB }
innodb_file_per_table=ON
thread_cache_size={ if threads_connected value varies from 75 to 100, set thread_cache_size to 25 }
table_cache={ more than default }


delete query takes forever I got a reasonable simple query:

With RowsToDelete AS
(
    SELECT TOP 500 Id
    FROM ErrorReports
    WHERE IncidentId = 611
)
DELETE FROM RowsToDelete

However, it do not complete. I have tried several times. The last time I waited 8 minutes before canceling it.

ErrorReports contains about 22 000 rows. ErrorReportOrigins about the same.

Estimated execution plan:

Actual execution plan (for top 10, takes 28 seconds to complete):

Execution plan:
https://www.brentozar.com/pastetheplan/?id=S1jUXDruz

Client stats:

What I've tried:

ErrorReportOrigins did not have a clustered index (id), only a FK to ErrorReports.Id. I've added an id column (pk&identity).
I've rebuilt all indexes (using this).
Tried to delete from ErrorReportOrigins first (using the same CTE). No difference
(The original CTE had a ORDER BY Id, I removed it to see if there was a difference)

I'm lost. Why on earth does it take so long? All SELECT statements are fast.
And the DB isn't really big. The ErrorReports table is the largest one.

(This is an SQL Azure DB in an elastic pool)

Update

CREATE TABLE [dbo].[ErrorReports](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [IncidentId] [int] NOT NULL,
    [ErrorId] [varchar](36) NOT NULL,
    [ApplicationId] [int] NOT NULL,
    [ReportHashCode] [varchar](20) NOT NULL,
    [CreatedAtUtc] [datetime] NOT NULL,
    [SolvedAtUtc] [datetime] NULL,
    [Title] [nvarchar](100) NULL,
    [RemoteAddress] [varchar](45) NULL,
    [Exception] [ntext] NOT NULL,
    [ContextInfo] [ntext] NOT NULL,
PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

Indexes:

CREATE NONCLUSTERED INDEX [Application_GetWeeklyStats] ON [dbo].[ErrorReports]
(
    [ApplicationId] ASC,
    [CreatedAtUtc] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

CREATE NONCLUSTERED INDEX [ErrorReports_IncidentId] ON [dbo].[ErrorReports]
(
    [IncidentId] ASC,
    [CreatedAtUtc] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

The data is fairly large (used datalength in the query):

It seems likely the very large ntext data is highly fragmented, causing a large amount of random I/O (or other inefficiencies) when locating LOB fragments to delete. Maybe the elastic thingy needs more I/O horsepower too.

You may need to export and reload the data to solve this problem. Copying to a new table, dropping the old, then renaming the new would also work.

Unless you have an extremely good reason not to, I suggest you also change the data type from the old, deprecated ntext to the replacement nvarchar(max) while reloading the data.

It may also simply be a SQL Server limitation with large LOBs. See the related Q & A Slow Delete's of LOB data in SQL Server.

The usual recommendation is to move to an alternate storage solution when data averages 1MB or more. See the SQL Server technical paper FILESTREAM Storage in SQL Server 2008 by Paul S. Randal for details. This is not yet supported in Azure SQL Database, unfortunately.

Perhaps you would be better storing the large LOB data in Azure Blob storage instead, and only holding a link in the database itself.