Xml index, slow count of rows I am not getting index seek in the following scenario.
Instead of inserting <Number>xxx</Number> in xml column like in this post Why is the secondary selective index not used when the where clause filters on `value()`?, insert 100k rows with this xml <SomeText>NiceText</SomeText> and similar amount of rows of this <SomeText>MoreText</SomeText>. Doesnt need to be 100k. Just needs to be many.
Then add index

create selective xml index SIX_T on dbo.T(XMLDoc) for
(
    pathXQUERY = '/SomeText' as xquery 'xs:string' maxlength(8) singleton
);

And secondary index

create xml index SIX_T_pathXQUERY on dbo.T(XMLDoc)
  using xml index SIX_T for (pathXQUERY);

Then do a count

select count(*)
from dbo.T as T
where T.XMLDoc.exist('/SomeText[. eq "MoreText"]') = 1;

Notice that it doesnt use index seek and is "slow". Can take several seconds with millions of rows.
If i insert same values in a standard column and add an index to it and do a

select count(id)
    from dbo.T as T where SomeTextColumn = 'MoreText'

i get results instantly.
All tests done on sql server 18.3.1

The question is, how can i make counting by xml as fast as counting by column?

Thanks

Data differences

With a low amount of records to seek on, the optimizer is able to use the SIX_T_pathXQUERY index:

and filter on moretext with a seek predicate:

An interesting part here is that it executes the key lookup to get the path_1_id values that are not null.

Since that is a filter definition on the nonclustered xml index...

...while not being present in the index itself.

As a result, for the optimizer to consider using the index it knows that it has to complete these steps:

Filter on the XML value with the secondary index on the internal
table
Match these returned values with the SIX_T clustered index on the
internal table and filter on path_1_id is not null since path_1_id is
not included in the secondary index
Match these values with the actual table, dbo.T to return the ID to count on

Tipping points

When the expected rows to be returned are higher, it favours using the selective clustered XML Index, to be able to run a merge join & no key lookup instead:

with a residual predicate:

To filter on the xml column and path_1_id

Comparing the plans

You could (but you shouldn't) use the USE PLAN hint to force the plan with the seek on the XML column and see what would happen if we where to search on these values.

With  the execution time =

   CPU time = 218 ms,  elapsed time = 215 ms.

And the execution time for the scan + merge join plan:

   CPU time = 62 ms,  elapsed time = 58 ms.

In short, I believe that the scan with residual predicate + merge join choice was the right choice by the optimizer.

No way around it

While I might be wrong, I don't think there is a way to improve the count query with regular XML Indexes.
We also cannot change these internal tables or even query them:

SELECT * FROM
[sys].[xml_sxi_table_1463676262_256000];

It even gives a missing index hint on the merge join query plan, which you cannot create:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [sys].[xml_sxi_table_1463676262_256000] ([pathXQUERY_1_value])
INCLUDE ([path_1_id])

To improve the queries, you would have to look into non-xml index solutions.

Edit

  How do i use USE PLAN hint? You have example code for this query?

I would advise against this, but for testing purposes it would be fine.

Step 1: You would have to get the actual execution plan of the query and get
    the xml:

If you don't have the query with the low estimates, enter a value that does not exist like:

select count(*)
from dbo.T as T
where T.XMLDoc.exist('/SomeText[. eq "bbb"]') = 1

Step 2: Replace all 's with ''s in the execution plan xml, we will need this later.

Step 3: Paste the plan between OPTION( USE PLAN '')

SELECT count(*)
FROM dbo.T as T
WHERE T.XMLDoc.exist('/SomeText[. eq "MoreText"]') = 1
OPTION(USE PLAN
'');

Step 4: I had to change utf-16 to utf-8 to get the use plan hint to work

From:

OPTION(USE PLAN
'<?xml version="1.0" encoding="utf-16"?>

To:

OPTION(USE PLAN
'<?xml version="1.0" encoding="utf-8"?>

Step 5: Run the query.

My query now looks like this:

select count(*)
from dbo.T as T
where T.XMLDoc.exist('/SomeText[. eq "MoreText"]') = 1
OPTION(USE PLAN
'<?xml version="1.0" encoding="utf-8"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.481" Build="14.0.3223.3" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="70" StatementSubTreeCost="0.00986014" StatementText="select count(*)&#xD;&#xA;from dbo.T as T&#xD;&#xA;where T.XMLDoc.exist(''/SomeText[. eq &quot;bbb&quot;]'') = 1" StatementType="SELECT" QueryHash="0x412154B6AD55BBFC" QueryPlanHash="0x280B174BF20902E3" RetrievedFromCache="true" SecurityPolicyApplied="false">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="1" CachedPlanSize="40" CompileTime="394" CompileCPU="301" CompileMemory="648">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419404" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="2" MaxCompileMemory="3655432" />
            <QueryTimeStats CpuTime="0" ElapsedTime="0" />
            <RelOp AvgRowSize="11" EstimateCPU="0" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00986014">
              <OutputList>
                <ColumnReference Column="Expr1017" />
              </OutputList>
              <ComputeScalar>
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Column="Expr1017" />
                    <ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1020],0)">
                      <Convert DataType="int" Style="0" Implicit="true">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="Expr1020" />
                          </Identifier>
                        </ScalarOperator>
                      </Convert>
                    </ScalarOperator>
                  </DefinedValue>
                </DefinedValues>
                <RelOp AvgRowSize="11" EstimateCPU="1.1E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Aggregate" NodeId="1" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.00986014">
                  <OutputList>
                    <ColumnReference Column="Expr1020" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
                  </RunTimeInformation>
                  <StreamAggregate>
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Column="Expr1020" />
                        <ScalarOperator ScalarString="Count(*)">
                          <Aggregate AggType="countstar" Distinct="false" />
                        </ScalarOperator>
                      </DefinedValue>
                    </DefinedValues>
                    <RelOp AvgRowSize="9" EstimateCPU="1E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Aggregate" NodeId="3" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.00985904">
                      <OutputList />
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
                      </RunTimeInformation>
                      <StreamAggregate>
                        <DefinedValues />
                        <GroupBy>
                          <ColumnReference Database="[adventureworks]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="ID" />
                        </GroupBy>
                        <RelOp AvgRowSize="11" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00985804">
                          <OutputList>
                            <ColumnReference Database="[adventureworks]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="ID" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
                          </RunTimeInformation>
                          <NestedLoops Optimized="false">
                            <OuterReferences>
                              <ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="pk1" />
                            </OuterReferences>
                            <RelOp AvgRowSize="16" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="5" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657038">
                              <OutputList>
                                <ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="pk1" />
                              </OutputList>
                              <RunTimeInformation>
                                <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
                              </RunTimeInformation>
                              <NestedLoops Optimized="false">
                                <OuterReferences>
                                  <ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="pk1" />
                                  <ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="row_id" />
                                </OuterReferences>
                                <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Index Seek" NodeId="6" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="150002">
                                  <OutputList>
                                    <ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="pk1" />
                                    <ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="row_id" />
                                  </OutputList>
                                  <RunTimeInformation>
                                    <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="3" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                  </RunTimeInformation>
                                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                    <DefinedValues>
                                      <DefinedValue>
                                        <ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="pk1" />
                                      </DefinedValue>
                                      <DefinedValue>
                                        <ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="row_id" />
                                      </DefinedValue>
                                    </DefinedValues>
                                    <Object Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Index="[SIX_T_pathXQUERY]" Filtered="true" Alias="[SomeText:1]" IndexKind="SecondarySelectiveXML" Storage="RowStore" />
                                    <SeekPredicates>
                                      <SeekPredicateNew>
                                        <SeekKeys>
                                          <Prefix ScanType="EQ">
                                            <RangeColumns>
                                              <ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="pathXQUERY_1_value" />
                                            </RangeColumns>
                                            <RangeExpressions>
                                              <ScalarOperator ScalarString="N''bbb''">
                                                <Const ConstValue="N''bbb''" />
                                              </ScalarOperator>
                                            </RangeExpressions>
                                          </Prefix>
                                        </SeekKeys>
                                      </SeekPredicateNew>
                                    </SeekPredicates>
                                  </IndexScan>
                                </RelOp>
                                <RelOp AvgRowSize="461" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="8" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="150002">
                                  <OutputList />
                                  <RunTimeInformation>
                                    <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                  </RunTimeInformation>
                                  <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                    <DefinedValues />
                                    <Object Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Index="[SIX_T]" Alias="[SomeText:1]" TableReferenceId="-1" IndexKind="SelectiveXML" Storage="RowStore" />
                                    <SeekPredicates>
                                      <SeekPredicateNew>
                                        <SeekKeys>
                                          <Prefix ScanType="EQ">
                                            <RangeColumns>
                                              <ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="pk1" />
                                              <ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="row_id" />
                                            </RangeColumns>
                                            <RangeExpressions>
                                              <ScalarOperator ScalarString="[adventureworks].[sys].[xml_sxi_table_1463676262_256000].[pk1] as [SomeText:1].[pk1]">
                                                <Identifier>
                                                  <ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="pk1" />
                                                </Identifier>
                                              </ScalarOperator>
                                              <ScalarOperator ScalarString="[adventureworks].[sys].[xml_sxi_table_1463676262_256000].[row_id] as [SomeText:1].[row_id]">
                                                <Identifier>
                                                  <ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="row_id" />
                                                </Identifier>
                                              </ScalarOperator>
                                            </RangeExpressions>
                                          </Prefix>
                                        </SeekKeys>
                                      </SeekPredicateNew>
                                    </SeekPredicates>
                                    <Predicate>
                                      <ScalarOperator ScalarString="[adventureworks].[sys].[xml_sxi_table_1463676262_256000].[path_1_id] as [SomeText:1].[path_1_id] IS NOT NULL">
                                        <Logical Operation="IS NOT NULL">
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="path_1_id" />
                                            </Identifier>
                                          </ScalarOperator>
                                        </Logical>
                                      </ScalarOperator>
                                    </Predicate>
                                  </IndexScan>
                                </RelOp>
                              </NestedLoops>
                            </RelOp>
                            <RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Clustered Index Seek" NodeId="9" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="200002">
                              <OutputList>
                                <ColumnReference Database="[adventureworks]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="ID" />
                              </OutputList>
                              <RunTimeInformation>
                                <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                              </RunTimeInformation>
                              <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                <DefinedValues>
                                  <DefinedValue>
                                    <ColumnReference Database="[adventureworks]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="ID" />
                                  </DefinedValue>
                                </DefinedValues>
                                <Object Database="[adventureworks]" Schema="[dbo]" Table="[T]" Index="[PK__T__3214EC27EF7043C5]" Alias="[T]" IndexKind="Clustered" Storage="RowStore" />
                                <SeekPredicates>
                                  <SeekPredicateNew>
                                    <SeekKeys>
                                      <Prefix ScanType="EQ">
                                        <RangeColumns>
                                          <ColumnReference Database="[adventureworks]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="ID" />
                                        </RangeColumns>
                                        <RangeExpressions>
                                          <ScalarOperator ScalarString="[adventureworks].[sys].[xml_sxi_table_1463676262_256000].[pk1] as [SomeText:1].[pk1]">
                                            <Identifier>
                                              <ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="pk1" />
                                            </Identifier>
                                          </ScalarOperator>
                                        </RangeExpressions>
                                      </Prefix>
                                    </SeekKeys>
                                  </SeekPredicateNew>
                                </SeekPredicates>
                              </IndexScan>
                            </RelOp>
                          </NestedLoops>
                        </RelOp>
                      </StreamAggregate>
                    </RelOp>
                  </StreamAggregate>
                </RelOp>
              </ComputeScalar>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>');


What is the size of currently-existing tmp tables in innodb? This is yet another "how to add a column to a gigantic table" question. The table on disk takes 2.5GB, and I have around 10GB of ram I can use. I have a feeling I can crank up the tmptablesize option so my add column tmptable resides completely in memory so disk i/o isn't a bottleneck. How can I get a sense of what value I should set tmptablesize to?

I have a second vm provisioned for a dry run. I'm running my alter table command and it is chugging along, writing to a temp table. How can I tell how large this temp table is, exactly?

edit for comments

mysql --version
mysql  Ver 15.1 Distrib 5.5.30-MariaDB, for debian-linux-gnu (x86_64) using readline 5.1

The table has 5.5 million rows: 10 decimal columns, 17 integer columns, and 2 datetime columns. No foreign keys. 12 of the integer columns have indexes.

The command:

alter table anonymized_table_name ADD COLUMN `anonymized_column_name` VARCHAR(100) NULL;

The only way to see the temp table needed would be to micromanage the process.

Here is how:

USE mydb
CREATE TABLE anonymized_table_name_new LIKE anonymized_table_name;
ALTER TABLE anonymized_table_name_new ADD COLUMN `anonymized_column_name` VARCHAR(100) NULL;
INSERT INTO anonymized_table_name_new (list of all columns except anonymized_column_name)
SELECT * FROM anonymized_table_name;
ALTER TABLE anonymized_table_name RENAME anonymized_table_name_old;
ALTER TABLE anonymized_table_name_new RENAME anonymized_table_name;
DROP TABLE anonymized_table_name_old;

After the third command you can run

SELECT (data_length+index_length)/power(1024,3) tmptablesize_GB
FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='anonymized_table_name_new';

This may not be all that useful because anonymized_table_name_new may be about the same size as anonymized_table_name since anonymized_column_name has no value. Notwithstanding, you would run that query to see the temp table size in gigabytes.


Strange sql behavior - last character is removed in the Select statement, but actual data has different length We have a table with a column name A with type nvarchar(23).

following query will always return 23 which means that the actual length of all records are 23.

select length(trim(req.A)), count(*)
  from tableName req
group by length(trim(req.A));

|length(trim(req.A))|count(*)|
------------------------------
|23                 |1006    |

But when we select from this table with following query it behaves different and it seems that the last character is always removed in result Gridview in the pl/sql developer.

select LENGTHB(req.A) lenb, length(req.A) len, req.* from tableName req
where req.A = 'NHBBBBB1398052635902235'; -- Note to the equal sign and the last charactar (5) of the where clause

the result is:

|lenb|len|          A           |
---------------------------------
|46  |23 |NHBBBBB139805263590223|

As you can see the last character (5) is removed in the select result.

Also when we rewrite previous query with to_char() or run it in sqlplus it works fine & return correct result with the length of 23 and (5) as the last character.

select LENGTHB(req.A) lenb, length(req.A) len, to_char(req.A) from tableName req
where req.A = 'NHBBBBB1398052635902235';

the result is:

|lenb|len|    to_char(req.A)     |
----------------------------------
|46  |23 |NHBBBBB1398052635902235|

Can you please explain whats happen!? Is this related to pl/sql configs? How to solve this?

It seems that the problem was an old version of PL/SQL Developer which our customers used. When they updated to the newer version, the problem has been solved.


Skip ERD in database implementation AS we know that entity relation diagram(ERD) is an important concept in database implementation, But I was wondering that can we skip this step when creating a small database containing a few tables i.e directly create tables in RDBMS in order to save time. or I should do all steps for better understanding i.e draw the ERD and then implement in RDBMS ?? Although it is a small database.

Just asking this question for general knowledge as I am new to this field.

The ERD is simply a product of the database design phase. You can skip drawing the ERD if you can complete design in your head, but you cannot (should not) skip designing your database.


Diagnose slow SQL in SQL Server I am not a DBA so please be patient with me...

Is it possible to turn on a trace on SQL Server which will tell me any SQL statement that takes longer than 2 seconds to execute, when it happened and what the SQL was? Is it possible to set the time to 2, 3 , 4 , whatever... ?

Thanks

Yes.

In SQL Server Profiler, you can filter on "Duration"

(This is from the "Standard (default)" template on SQL Server 2012 RC0 Profiler)


Recovery mode keeps reverting to full I'm using SQL Server 2016 to manipulate some large research data-sets.

I keep setting the recovery mode to simple, however it keeps reverting somehow back to full. Any guidance or advice on how to make the change permanent?

The data are low value, easily reproducable, etc. so there's no need for full recovery.

SQL Server does not automatically change the recovery model.  If you set it to simple, then see later it is set to full, either:

Someone else changed it back to full
The database has been restored from a backup where the database is set to full recovery
There may be a scheduled agent job that is changing the recovery model.

From a comment on this answer:

This was in AWS and #3 was the case. In this case backups were enabled so they have a process that automatically changes the recovery model back to Full. Changing the backup retention to zero days allows it to stay in simple recovery.


Using 100's of schemas in a database SQL2008R2? We are in SSAS type of environment with 100's of clients.

Currently each client gets a database and we have 200 per instance on SQL Server 2008R2. This presents limitations with snapmanager being able to do backups as the recommended number is 35 databases per instance (netapp).

One of the solutions we are looking into is to consolidate the databases - collecting say 150 of them into one database using separate schemas.

Has anybody done anything like this?
Are there limitations on the number of schemas per database, or performance issues vs using separate databases that we should be aware of?

Thank you for your answers.

The reason we need Snapmanager is because that is what we currently use for our DR replication and their recommendation is 35 databases per instance because otherwise it might take longer than an hour to backups/transfer the data which breaks our 1 hour recovery requirement.

But according to them if we were to have 1 database(or under 35) per instance they should be able to make it no issues.

So that is why we were looking potentially into schemas but that would mean most likely at least 100 schemas per database.

One problem you'll have is with SSMS. I have a client with a lot of objects in a single database, and if we expand the Tables node in Object Explorer, SSMS dies.

As well as this, I suspect your code might need to be changed. Either you need to rely on the default schema for every connection, or else you need to change every query. Hopefully you've been using two-part naming so far, so every dbo.reference will need to change. Far from ideal.

You still have a lot of flexibility over filegroups, which is good, and having a single database might even help avoid disk head movement on the transaction log.

I doubt performance will be affected much either way. But the code changes required could be a huge frustration. And if you need to rely on the default schema, there are extra locks (LCK_M_X) that need to be taken out to work out which objects you're referring to.

I'd be really hesitant to redesign that much, but it really depends on your priorities.


Why should foreign keys be implemented in my relational database? Earlier today, I sparked a rather unexpected debate with my professional peers on the topic of foreign keys in our company's databases. In a nutshell, a software engineer who is senior to me stated that he didn't like a commit I had made to one of our repositories because I implemented a foreign-key relationship between two tables. His argument amounted to him not liking foreign keys because they restrict the data that can be inserted into the database & he believes that makes the database harder to work with. (Note: his argument was against foreign keys in general, not just the anecdotal foreign-key constraint that I proposed in our app. He believes that foreign-key constraints are a design flaw in modern relational database systems.)

At this point, I was caught completely flat-footed, and our boss chose to side with the senior developer for the time being, asking me to remove the foreign keys from my implementation of the bug-fix I was working on. The bug that I was fixing in fact stemmed from relational inconsistencies that were confusing our ORM (which itself leaves a bit to be desired, but that's a different story), and while I tried to explain this to my team, everyone sided with the senior dev & I was told to go back to the drawing board and re-implement my database fix WITHOUT relying upon foreign key constraints. In other words, I've been asked to do relational management without the built-in relational-management tools of our RDBMS (posgres, if it matters).

I reiterated that this was not the proper way to go about fixing the bug, at which point my boss told me that the discussion was taking up too much time and that if I wanted to debate the merits of formal FK-constraints, I would need to do it at a later date... which I intend to do, but I also feel like I've been tasked with arguing why water is wet.

When I go into this debate, I want to come armed with as many facts in support of foreign-key usage as possible. I know that implementing sensible FK-constraints:

serves as an automatic guardrail against relational inconsistencies (ie, prevents "garbage data" (which is a demonstrable problem with the system I'm working on currently)),
clarifies the high-level relational context between two related tables to someone who is inspecting the database schema, and
can increase the performance of database query operations.

... But I'm wondering if there are any other bullet points that I may be missing on this topic with which I should meet this debate. What other benefits should I make sure to bring up with my boss and colleagues? (Or has there genuinely been a paradigm shift away from FK-constraints & I've been living with my head under a rock when it happened?)

I don't have enough points to comment, so an answer.

OMG. I feel your pain ;-)

Yeah, people find foreign key constraints a real pain. But without them, you have to then audit the data for widows and orphans, otherwise you're entirely likely to have bad summary results. At the very least, you don't really know the validity of your results. Are they off by 0.001%? By 2%? It's hard to say...you have go go in and figure out the level of garbage data.

On a human level, okay, I get it, constraints can be a pain. It's an acute pain as it blocks you right at the point of insertion. (Kind of the whole point.) With sloppy rules, then you get the pain, but it's deferred. And speaking of which, since I see a Postgres tag on here, a couple of things to know about. First up deferrable constraint. This is an enormously valuable feature for this very situation. It really can be a pain to insert a child only to get it kicked back because the parent doesn't exist yet when you're going to add that parent shortly, within the same transaction. It's kind of fussy and tedious to sort that out. (Or it can feel that way.) With a deferred constraint, the FOREIGN KEY is still enforced, but not until the end of the transaction. So, you can add your records in less strict order, if that's a help. Here's a piece on the subject that looks pretty thorough, you can find many others if you look:

https://begriffs.com/posts/2017-08-27-deferrable-sql-constraints.html

And, secondly, and for what it's worth, you've also got ON UPDATE CASCADE support, although needing that is often a sign of a problem in the design.

Oh, and for bullet points, a formally defined constant goes into the meta data about your system. So, any automatic tool that knows how to interpret that data can do much, much more for you. For example:

Class and code generators.
Structure diagrams.
Test cases.
Query constructors.
Etc.

Adding an edit here with a suggestion that could help. Opinions are all well and good, but evidence sometimes is better. To that end, what about running some exploratory queries to find out the state of play in the data today? You may find that there are no problems in the data, even without server-side constraints. Some people are just freakishly good at not screwing up. Then again, there might be a total mess. Here's a query off the top of my head which, I'm sure, can be improved:

     select count(*)
       from child
tablesample bernoulli(5) -- If you're only after an estimate, TABLESAMPLE is your friend.
  left join parent on (child.parent_id = parent.id)
      where parent.id is null

P.S. I lost this very same argument once, largely because I was unaware of the deferred option at the time.


Table shows id (auto-increment) is 0 in all rows I am using laravel 5.2 to develop my web application. i recently uploaded the web application from localhost to my hosting account. I also imported MySQL tables from phpmyadmin localhost to my phpmyadmin localhost in my hosting account.
When I opened my phpmyadmin I noticed that in one of my tables, the id (which is set to auto-increment) is not working. It is displaying 0 in all the rows.
When I set the id to auto-increment in phpmyadmin it gives me the following error:

#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

Table structure in phpmyadmin

SHOW CREATE TABLE

The problem is that you can not create AUTO_INCREMENT without a key so try something like this:

CREATE TABLE `project_application` (
`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`project_title` varchar(255) NOT NULL,
`project_id` int(11) NOT NULL,
`team_leader` varchar(255) NOT NULL,
`team_member` text,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE     CURRENT_TIMESTAMP,
`createt_et` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
)

Hope this helps


SQL Server backup media set and file separation SQL Server backup media set and file separation
If I am going to do such a backup plan under full recovery model for a particular database

Weekly full database backup
Daily differential backup
hourly T-log backup

Retention period: 28 days

How should I arrange the files?
Should each and every backup have a unique file name?

I tried using SMSS to do a few and ending up all backups in a single test.bak file.
I can see the content of the backup file test.bak contains a lot of backups including full backups, differential backups and log backups.

I know it wasn't right. What is the best practice for arranging backups into separate physical backup files?

I would say that there isn't necessarily a Best Practice, this is more of a matter of personal preference or your businesses operating procedures.  I've had separate folders per database, then sub folders for FULL, DIFF, LOG at some jobs, others everything goes to a single folder and the backup name is some combination of database name, backup type, and datetimestamp to make each file uniquely named.  I'd recommend looking at Ola Hallengren's maintenance solution:

https://ola.hallengren.com/

This is what I recommend to all my clients for database maintenance and backups and it handles everything dealing with the file naming and folder structure automatically for you.  Just run his script and set the jobs to run at the frequency and schedule that meets your requirements and you are all done.


Best way to configure and maintain a table with many insertions and deletions that's empy on average? There exists a "batch query" table that has two columns (BatchID, RecordID) which are both of TSQL type 'uniqueidentifier'. The unique clustering key is on (BatchID, RecordID), and there are no secondary indexes.

We use SqlBulkCopy to quickly insert thousands or millions of ids into the table under a single batch id, so that various queries can join against it to perform filtering using that set of record ids associated with that batch. That approach much faster than attempting multiple queries that all send lists of ids in the query string. As soon as the queries complete, the batch of record ids is deleted. The table is empty on average when no queries are running, but may have hundreds of active batches as we process thousands of API requests per second that may use the table.

My question is, what kind of maintenance steps, if any, are required of a DBA to maintain these tables. For example, would it benefit at all from periodic index rebuilding, or will SQL Server handle cleanup of the deleted rows (freeing page blocks, etc.) well on its own. Also, is there any special configuration of the table or clustered index that would help, especially considering that the identifiers are random Guids (uniqueidentifiers).

Generally we use the latest version of SQL Server,  Enterprise Edition. Some servers are in Azure, some in AWS.

I'm also interested in hearing alternatives. For example, I assume this approach is best with a permanent table, but I could also create a session-local temp table as part of a transaction that gets deleted when finished, rather than inserting and deleting rows from a permanent table. I just don't know how that would perform in comparison.

In my experience with SQL Server 2016 Enterprise Edition in a similar process where a permanent table is used as a message queue - so it has a very high rate of inserts and deletes - we found rebuilding indexes every 5 to 10 minutes to be beneficial in forcing deleted record clean up.  This dramatically improved read performance on the table.
In our case, the database isolation level is read committed snapshot, and the dequeue/delete process was deleting records faster than the ghost cleanup process was clearing them out.  With high ghost record counts, selecting one record would sometimes take 1-3 seconds instead 5-10 ms because SQL was having to read past all the ghost records, indicated by a high Scan Count (with set statistics io on).
To check the number of ghost records:
SELECT version_ghost_record_count, *
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('[transport].[BatchTable]'), NULL, NULL, 'SAMPLED')

https://docs.microsoft.com/en-us/sql/relational-databases/ghost-record-cleanup-process-guide?view=sql-server-2017#ghost-record-cleanup-task

The ghost cleanup process runs automatically on an interval (every 5 seconds for SQL Server 2012+, every 10 seconds for SQL Server 2008/2008R2) and checks to see if any pages have been marked with ghost records. If it finds any, then it goes and deletes the records that are marked for deletion, or ghosted, touching at most 10 pages with each execution.
On high-load systems with many deletes, the ghost cleanup process can cause a performance issue from keeping pages in the buffer pool and generating IO

I think the "at most 10 pages" part was our limiting factor as the high rate of deletes exceeded the amount of pages the cleanup process could touch each run.


MySQL search comma separated keywords I have keywords column in my table and there are comma (,) separated values. example: hello, world

I have search engine with same codes:

$search = $_GET ['search'];

$query = $db->prepare("SELECT COUNT(*) as num FROM table WHERE keywords LIKE ?");
$query->bindValue(1, "%$search%", PDO::PARAM_STR);
$query->execute();

$sql = $db->prepare("SELECT * FROM table WHERE keywords LIKE ? LIMIT $start , $limit");
$sql->bindValue(1, "%$search%", PDO::PARAM_STR);
$sql->execute();

When I type only 'hello' or only 'world' in my search bar, there is result, but if I type both words together, like 'Hello world' there is no result.

EDIT:
I edited my codes for FULL TEXT SEARCH

$query = $db->prepare("SELECT COUNT(*) as num FROM table WHERE MATCH (id,keywords) AGAINST ('$search*' IN BOOLEAN MODE)");
$query->execute();

and

$sql = $db->prepare("SELECT * FROM table WHERE MATCH (id,keywords) AGAINST ('$search*' IN BOOLEAN MODE) LIMIT $start , $limit");
$sql->execute();

It works better, but now I get some irrelevant results. Is there any way to filter results and order they relevance?
Thanks

If you must keep keywords as a CSV, you can use FIND_IN_SET().

SELECT COUNT(*) as num
FROM table
WHERE FIND_IN_SET('hello', keywords)
AND FIND_IN_SET('world', keywords);


Look at the differences between the two EXPLAINs...

ALL  NULL       NULL   1144
REF  new_index  const     1

The first looked at ALL 1144 (approximate) rows of the table

The second used new_index, checking against a "constant", finding 1 (approximate) row.  Clearly faster.

The 503 may be 2+3*167, Perhaps the column is VARCHAR(167) CHARACTER SET utf8 NOT NULL?  The "2" is overhead for "VAR"; the "3" is the max byte length of a utf8 character.  About the only use for "503" is exactly what I just did -- deduce something about the index in question.

Or maybe it is a VARCHAR(125) CHARACTER SET utf8mb4 NULL?  2+4*125+1; the "1" is for a NULL flag.

The size of the "key_len" does not matter, except that there are limits -- 767, 3072, and possibly others, depending on version, etc.

Index "prefixing" (as danblack mentioned) is problematic.  It is often not used when you think it should.  I recommend avoiding it in most cases.  This is a case where you might need it, assuming you can't shrink the TEXT column:

CREATE TABLE ...
    foo TEXT NOT NULL, ...
    INDEX(foo(167)) ...


How come systemctl status postgresql.service and systemctl status postgresql@10-main show different results? Not sure I really understand what's going on.

Originally, I'm trying to connect to a remote host (which I can ping, access via SSH) but cannot connect through psql

Error: psql: could not connect to server: Connection refused Is the server running on host <HOST> and accepting TCP/IP connections on port 5432?

After checking a couple of things, I stumbled upon different results from postgresql.service and postgresql@10-main :

postgresql.service -- active (exited)

  ~ sudo systemctl status postgresql.service
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled;
vendor preset: enabled)
   Active: active (exited) since Don 2018-01-04 15:24:14 CET; 2min 29s ago
  Process: 10954 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 10954 (code=exited, status=0/SUCCESS)

postgresql@10-main -- active (running)

  ~ systemctl status postgresql@10-main
● postgresql@10-main.service - PostgreSQL Cluster 10-main
   Loaded: loaded (/lib/systemd/system/postgresql@.service; disabled; vendor preset: enabled)
   Active: active (running) since Don 2018-01-04 15:24:14 CET; 5min ago
  Process: 10920 ExecStop=/usr/bin/pg_ctlcluster --skip-systemctl-redirect -m fast %i stop (code=exited, status=0/SUCC
  Process: 10929 ExecStart=postgresql@%i --skip-systemctl-redirect %i start (code=exited, status=0/SUCCESS)
 Main PID: 10936 (postgres)
   CGroup: /system.slice/system-postgresql.slice/postgresql@10-main.service
           ├─10936 /usr/lib/postgresql/10/bin/postgres -D /var/lib/postgresql/10/main -c config_file=/etc/postgresql/1
           ├─10938 postgres: 10/main: checkpointer process
           ├─10939 postgres: 10/main: writer process
           ├─10940 postgres: 10/main: wal writer process
           ├─10941 postgres: 10/main: autovacuum launcher process
           ├─10942 postgres: 10/main: stats collector process
           └─10943 postgres: 10/main: bgworker: logical replication launcher

Jan 04 15:24:12 floppy systemd[1]: Starting PostgreSQL Cluster 10-main...
Jan 04 15:24:14 floppy systemd[1]: Started PostgreSQL Cluster 10-main.

Is this related to my problem with connecting to the remote host ?
Why are two different services running (showing different results) ?

PS: I'm running on Ubuntu 16.04

Thanks a lot

Ubuntu and Debian

If you're using Ubuntu, rather than using systemd's systemctl to gauge the status of your clusters use the distro's own pg_lsclusters. It'll tell you if they're running and the port number,

$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5433 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log


Factoring out subselect (with clause) ends in ORA-00904 I am trying fo factor a repetitive subquery with with but getting ORA-00904 over and over again.

This is my base query which works quite well:

select tka.id, tka.name, txt.text as langname, einh.text as einheit, tka.typ,
  vgd.a_wert, vgd.i_wert, vgd.r_wert, vgd.d_wert, vgd.quelle
from vgd vgd
  inner join vgs vgs on
    vgd.vgs_id = vgs.id and vgs.schluessel = ?
  inner join tka tka on tka.id = vgd.tka_id
  left join (select txt0.* from txt txt0, spr spr
               where txt0.spr_id = spr.id and spr.name = ?) txt on tka.txt_id_langname = txt.id
  left join (select einh0.* from txt einh0, spr spr
               where einh0.spr_id = spr.id and spr.name = ?) einh on tka.txt_id_einheit = einh.id
  inner join mtdb mtdb on tka.name = mtdb.tedabattribut
               and mtdb.tedabattribut is not null and mtdb.baugruppe = ?
  where tka.status = 2
    and tka.felddimension = 0
    and not exists (select 1 from vgd vgd2
                      where vgd2.vgs_id = vgd.vgs_id
                        and vgd2.tka_id = vgd.tka_id
                        and vgd2.ai_id > vgd.ai_id);

Now as you can see, I have to pass the value to  spr.name twice. I would like to avoid that. The query has been transformed to this for starters
EDIT The query has been rewritten to ANSI style as Leigh Riffel wrote but still no avail.

with spr0 as (select id from spr where name = ?)
select tka.id, tka.name, txt.text as langname, einh.text as einheit, tka.typ,
  vgd.a_wert, vgd.i_wert, vgd.r_wert, vgd.d_wert, vgd.quelle
from vgd vgd
  cross join spr0 spr
  inner join vgs vgs on
    vgd.vgs_id = vgs.id and vgs.schluessel = ?
  inner join tka tka on tka.id = vgd.tka_id
  left join (select txt0.* from txt txt0
               where txt0.spr_id = spr.id) txt on tka.txt_id_langname = txt.id
  left join (select einh0.* from txt einh0
               where einh0.spr_id = spr.id) einh on tka.txt_id_einheit = einh.id
  inner join mtdb mtdb on tka.name = mtdb.tedabattribut
               and mtdb.tedabattribut is not null and mtdb.baugruppe = ?
  where tka.status = 2
    and tka.felddimension = 0
    and not exists (select 1 from vgd vgd2
                      where vgd2.vgs_id = vgd.vgs_id
                        and vgd2.tka_id = vgd.tka_id
                        and vgd2.ai_id > vgd.ai_id);

But SQL Developer is still giving me

ORA-00904: "SPR"."ID": ungültiger Bezeichner
00904. 00000 -  "%s: invalid identifier"

Table names have been shortened for brevity.

EDIT 2: I have followed miracle173's advice, reduced the query to a mininum and ran with SQL*Plus. Here is the output:

select spr.*, txt.*, vgd.*
from vgd vgd
  cross join spr spr
  inner join tka tka on tka.id = vgd.tka_id
  left join (select txt0.* from txt txt0
               where txt0.spr_id = spr.id) txt on tka.txt_id_langname = txt.id
  where vgd.vgs_id = 32 and spr.name = 'de';

FEHLER in Zeile 6:
ORA-00904: "SPR"."ID": ungültiger Bezeichner

The error is in line 6. So there must be some issue with the cross join and the alias.

Is anyone able to spot the missing link? Where is the flaw in my idea?

Solution: Thanks for Leigh's tip to outfactor table spr I have a working solution now:

select tka.id, tka.name, txt.text as langname, einh.text as einheit,
  mtdb.camosmerkmal as merkmal, tka.typ, vgd.a_wert, vgd.i_wert,
  vgd.r_wert, vgd.d_wert, vgd.quelle
from vgd vgd
  cross join spr spr
  inner join vgs vgs on
    vgd.vgs_id = vgs.id and vgs.schluessel = ?
  inner join tka tka on tka.id = vgd.tka_id and tka.status = 2
                        and tka.felddimension = 0
  left join txt txt on tka.txt_id_langname = txt.id and txt.spr_id = spr.id
  left join txt einh on tka.txt_id_einheit = einh.id and einh.spr_id = spr.id
  inner join mtdb mtdb on tka.name = mtdb.tedabattribut
               and mtdb.tedabattribut is not null and mtdb.baugruppe = ?
  where spr.name = ?
    and not exists (select 1 from vgd vgd2
                      where vgd2.vgs_id = vgd.vgs_id
                        and vgd2.tka_id = vgd.tka_id
                        and vgd2.ai_id > vgd.ai_id);

There seems to be an issue with the mixture of ANSI and Oracle style joins as well as the referencing of a cross joined table in a subquery.  Converting/removing these constructs, you should end up with something like this:

WITH spr0 AS (
      SELECT id FROM tka WHERE name = ?
   )
SELECT tka.id, tka.name, txt.text AS langname, einh.text AS einheit, tka.typ, vgd.a_wert,
   vgd.i_wert, vgd.r_wert, vgd.d_wert, vgd.quelle
FROM vgd vgd
CROSS JOIN spr0
JOIN vgs ON vgd.vgs_id = vgs.id AND vgs.schluessel = ?
JOIN tka tka ON tka.id = vgd.tka_id
   AND tka.txt_id_einheit = einh.id
JOIN mtdb mtdb ON tka.name = mtdb.tedabattribut
   AND mtdb.tedabattribut IS NOT NULL
   AND mtdb.baugruppe = ?
LEFT JOIN txt ON txt.spr_id = spr0.id ON tka.txt_id_langname = txt.id
LEFT JOIN txt einh ON einh0.spr_id = spr.id AND spr.name = ?
WHERE tka.status = 2 AND tka.felddimension = 0
AND NOT EXISTS
   (
      SELECT 1 FROM vgd vgd2
      WHERE vgd2.vgs_id = vgd.vgs_id AND vgd2.tka_id = vgd.tka_id AND vgd2.ai_id > vgd.ai_id
   );


Master port is disabled from Public IP, MySQL log is not getting refreshed My MySQL Replication setup is over internet.
Master is on Public IP.

If I stop mysql service in master, immediately an error message is thrown in slave. And when I start the service, slave is immediately connecting to master and replication is happening well.

But if the port is disabled from the Public IP, no error is getting thrown in slave log and showing as connection success, but data is not getting replicating from master to slave. And if the port is enabled for Public IP, it is showing as connection success and data is not getting replicating from master to slave.

Until I restart the slave, data is not getting replicating from Master to Slave.
Once I restart the slave, every thing goes fine.

Can I know, what is the reason for not getting replicated.

And Is there any way to come to know the master status from the slave, when this type of behavior occurs.

Also is there any option to stop slave when master port is disabled.

Please help me on this.

I am using MySQL version 5.0.24.

The basic problem stems from the Slave's IO Thread. Sometimes, it has the nasty habit of playing dumb and not properly checking or pinging the master.

MySQL 5.5's semisynchronous replication allows you to have the master time itself out and degrade back to asynchronous replication. Slaves running semisynchronous replication should have a more sensitive IO Thread now.

Concerning your particular situation, you are using MySQL 5.0.24 ??? That's a very old version. There are two bug reports (Bug1 and Bug2) discussing this.

It just dawned on me that you asked this question earlier and DTest answered it. Giving credit where credit is due, the bug reports came from his answer.

It would be nice to to be informed when the Public IP is being disabled from Slave access. That way, you could run STOP SLAVE; on the slave in advance.

You also asked is there a way to come to know the master status from the slave, when this type of behavior occurs.

Here is a typical SHOW SLAVE STATUS\G

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.64.80.136
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000019 <<------
          Read_Master_Log_Pos: 277892198 <<------
               Relay_Log_File: relay-bin.000058
                Relay_Log_Pos: 37535484
        Relay_Master_Log_File: mysql-bin.000019 <<------
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 277892198 <<------
              Relay_Log_Space: 277892637 <<------
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

Please note the following:

Master_Log_File : Log file from Master whose latest SQL was last copied to relay logs
Read_Master_Log_Pos : Log position within Master_Log_File whose latest SQL was last copied to relay logs
Relay_Master_Log_File : Log file from Master whose SQL was last executed from the relay logs
Exec_Master_Log_Pos : Log position within Relay_Master_Log_File whose SQL was last executed from the relay logs

If none of these value are moving, that could mean the all SQL is processed or that the slave is currently processing an SQL statement in the SQL thread that came from position Exec_Master_Log_Pos of the Master Log Relay_Master_Log_File.

Now, look at Relay_Log_Space. This number represents the sum total of all filesizes for all relay logs. If Slave_IO_Running is Yes and Relay_Log_Space is not changing, then go check the master by running SHOW MASTER STATUS;. It should like something like this:

mysql> show master status;
+------------------+-----------+--------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000019 | 682563021 |              |                  |
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)

If the Master's (File,Position) of SHOW MASTER STATUS; is beyond the Slave's (Master_Log_File,Read_Master_Log_Pos) of SHOW SLAVE STATUS\G, then the IO Thread on the Slave is dead for intents and purposes, even if Slave_IO_Running is Yes.


Storing point data for linestring in MySQL Ok, i am have a need to store data on SOME points of a poly line but not others.

The current way I an doing it is i have 3 tables

line:
lineID INT AUTO INCREMENT
lineType INT
forkFrom INT

linePoint:
pointID INT AUTO INCREMENT
lineID INT
lat DECIMAL
lng DECIMAL

pointMarker:
pointID INT
someDataFields...

but a line, lets say it has 20 points, will take up 20 rows in a table.

I would like to have a line table with a linestring field, removing the need for the linePoints table. but then I am left with no way to reference a specific point in a line.

Example: the line is a water pipe, it has a lot of points as it go's around corners and such, on some of these points, it has a tap or a water meter, how do I record this?

do I use the lat/lng as an index and then in my code somehow check for orphaned points if the line is modified? or is there a better way to do this?

also some lines are "forks" or "branches" from a main one, for this I am currently using the "forkFrom" column to store the ID of the point the "fork" would start from. how would i do this with a lineString? is that what a multiLineString is for?

I am having some problems with my front end code that deals with lines so now is a good time to change the the way the data is stored, as I will likely have to re write a large chunk of code that deals with it anyway. may as well write a few more lines and get the database side of things sorted as well, instead of fixing whats broken now, then throwing everything out the window when I end up changing the database.

GIS

Use GIS and stop doing all this madness.

MySQL Provides

LineString(pt1, pt2)
ST_LineStringFromText(wkt) (type-safe)
ST_GeomFromText(wkt)

They're easy to use and any program can output Well-known text or Well-known binary to create the lines. They're also indexable, more compact, and stored with SRID.

  also some lines are "forks" or "branches" from a main one, for this I am currently using the "forkFrom" column to store the ID of the point the "fork" would start from. how would i do this with a lineString? is that what a multiLineString is for?

A multiLineString can hold forks, yes. Or, you can just do it with another linestring entry in the table. It just depends on what you want to do. When you query it do you want to know what "fork" is returned (separate entries in the table make this easier and faster), or do you only care the pipe somewhere regardless of forks is returned.

Also obligatory statement if you're going to use GIS, you'll probably want PostGIS at some point.


Remote connection to the instance with window account and NTLM is failing I have the windows server with 4 SQL server 2012 instances
This is the test server. SPNs are not registered for any of those instances.

I can successfully connect remotely to 3 of those instances using my windows account, however for one of it I got following error:
The target principal name is incorrect.  Cannot generate SSPI context.

SQL Server login connections are working ok.

We've tried registering/de-registering the SPNs, also created new MSA account for that instance.

From what I've read - when the SPNs are not registered - the NTLM should be used. NTLM is working correctly for remote SQL Server logins.

What else can be checked? Is there any other option forcing this instance to try to use Kerberos connection?

This is the SQL Server 2012 patched to the 11.0.7462.6.

This can be caused by one of three things:

The service account has an expired password.
The service account has been locked.
The service account does not have permission to create
an SPN.


At what point does a database update its indexes in a transaction? I'm trying to understand the sequence of events in inserts where both an index and a transaction are involved.

For example, Oracle documentation states:

  If you create [or have] one or more indexes before loading data, the database then must update every index as each row is inserted.

But what happens if I create a transaction, insert five rows, then commit? Are indexes updated for every insert, or just at the commit point?

Logic tells me that they would only be updated at the commit point, because an updated index couldn't possibly be of use until those records are committed. But is that true?

If so, when I have 1m rows to insert, for best performance I should do one large commit of all rows and not 10 transactions of 100k records? Of course I realize this risks a greater rollback if row 999,999 fails.

Apologies if my terminology is a little out. I'm not a DBA by trade. I'm not so much interested in a particular database, as databases in general, although Oracle and Postgres are what I use most. I've searched on this topic but can't really find a definitive answer.

I work with SQL Server and Oracle. There are probably some exceptions, but for those platforms the general answer is that data and indexes will be updated at the same time.

I think that it would be helpful to draw a distinction between when the indexes are updated for the session that owns the transaction and for other sessions. By default, other sessions will not see the updated indexes until the transaction is committed. However, the session that owns the transaction will immediately see the updated indexes.

For one way to think about it, consider at a table with a primary key. In SQL Server and Oracle this is implemented as an index. Most of the time we want there to immediately be an error if an INSERT is done which would violate the primary key. For that to happen the index must be updated at the same time as the data. Note that other platforms, such as Postgres, allow deferred constraints which are checked only when the transaction is committed.

Here's a quick Oracle demo showing a common case:

CREATE TABLE X_TABLE (PK INT NULL, PRIMARY KEY (PK));

INSERT INTO X_TABLE VALUES (1);
INSERT INTO X_TABLE VALUES (1); -- no commit

The second INSERT statement throws an error:

  SQL Error: ORA-00001: unique constraint (XXXXXX.SYS_C00384850) violated

  00001. 00000 -  "unique constraint (%s.%s) violated"

  *Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
             For Trusted Oracle configured in DBMS MAC mode, you may see
             this message if a duplicate entry exists at a different level.

  *Action:   Either remove the unique restriction or do not insert the key.

If you prefer to see an index update action below is a simple demo in SQL Server. First create a two column table with one million rows and a nonclustered index on the VAL column:

DROP TABLE IF EXISTS X_TABLE_IX;

CREATE TABLE X_TABLE_IX (
ID INT NOT NULL,
VAL VARCHAR(10) NOT NULL
PRIMARY KEY (ID)
);

CREATE INDEX X_INDEX ON X_TABLE_IX (VAL);

-- insert one million rows with N from 1 to 1000000
INSERT INTO X_TABLE_IX
SELECT N, N FROM dbo.Getnums(1000000);

The following query can use the nonclustered index because the index is a covering index for that query. It contains all of the data needed to execute it. As expected no returns are returned.

SELECT *
FROM X_TABLE_IX
WHERE VAL = 'A';

Now let's start a transaction and update VAL for almost all of the rows in the table:

BEGIN TRANSACTION

UPDATE X_TABLE_IX
SET VAL = 'A'
WHERE ID <> 1;

Here is part of the query plan for that:

Circled in red is the update to the nonclustered index. Circled in blue is the update to the clustered index, which is essentially the table's data. Even though the transaction has not been committed we see that the data and the index is updated in part of the query's execution. Note that you will not always see this in a plan depending on the size of data involved along with possibly other factors.

With the transaction still not committed, let's revisit the SELECT query from above.

SELECT *
FROM X_TABLE_IX
WHERE VAL = 'A';

The query optimizer is still able to use the index and this time it estimates that 999999 rows will be returned. Executing the query returns the expected result.

That was a simple demo but hopefully it cleared things up a bit.

As an aside, I am aware of a few cases in which it could be argued that an index is not immediately updated. This is done for performance reasons and the end user should not be able to see inconsistent data. For example, sometimes deletes will not be fully applied to an index in SQL Server. A background process runs and eventually cleans up the data. You can read about ghost records if you're curious.


Index on Primary Key: is there any reason for that? I'm auditing the schema of a MySQL database used by a off-the-shelf e-commerce application and I found this as output of SHOW CREATE TABLE thistable:

CREATE TABLE `thistable`
(
    `case_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `company_id` int(11) NOT NULL DEFAULT '0',
    (...)
    `timestamp` int(11) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`case_id`),
    KEY `idx_case_id` (`case_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=21688281 DEFAULT CHARSET=utf8

It explicitly adds an index on a primary key.

Is this a blunder of the database designer, or is there actually a reason why is that?

It is worth noting that this table was MyISAM in origin.

No, there is clearly no good reason.

Most probably the index originally contained another column that has been dropped - at least, this is the only explanation from the top of my mind.


Postgres asynchronous procedure execution? I have an ETL script entirely contained within a Postgres 9.3 procedure.  The script takes several hours to process, which is fine for our purposes.

What I haven't been able to find out is if there is a method within postgres to call a procedure, and then let it run on the server even if the client making the original call becomes disconnected.

Unfortunately, PostgreSQL does not currently support having such an asynchronous call made to it, where the client is able to disconnect before the original call completes.  This has been discussed a number of times and there is ongoing work to implement a capability similar to this using a "pg_background" worker, but it's not in released versions of PG and it isn't clear when that will change.


What are the pros and cons of using my customer codes as a primary key? I am designing a database for one of my sales teams. Right now, the goal is to get a master list of customers and their mailing addresses, but the DB will eventually expand to track some other customer data. So, right now I am just working on the customers table, but want to make sure I follow good normalization and efficiency guidelines in case this thing grows beyond current expectations.

Right now, we have ~3,000 customers. So, we have customer codes we use to ID our customers. Each one can be a max of 20 characters, is alphanumeric, and will always be unique per customer (Foo, Inc. might have a cust code of FOOINC and might have 25 sub accounts, but there will only be one FOOINC master entity. If we ever did biz with another company called Foo Technologies Inc, we would create a new code like FOOTECH or something).

I am not a DBA, but have designed several DB's in the past and traditionally use a SQL identity field as the PK. In this instance, I've been thinking of using the customer code. What are the pros/cons of doing this? On the one hand, it seems logical to use a unique identifier as the PK if I have one, which I do. On the other, I know string PKs are slower than int PKs - this database will definitely grow, but it will never be millions of rows. The 3,000 customers are over 7 years of business, so it will be quite awhile before we even get to tens of thousands of rows.

I've researched this question and the debate usually ends with a "it depends on the data" - so please, teach me...in this situation, what would you consider when planning the table? What would you use for the PK? Any advantage to sticking an autoincrementing INT in there anyway? Any issues with indexing and inserting records?

FYI, the table layout just needs the following:

-CustomerCode(nvarchar(20))
-CustomerName(nvarchar(50))
-Address1(nvarchar(50))
-Address2(nvarchar(50)) - nullable
-Address3(nvarchar(50)) - nullable
-ZipCode (nvarchar(9))

Bonus question - worth sticking with 3NF and making a separate table holding city, state, with ZipCode as a PK and making a relationship to the customers table? Or don't worry about 3NF and keep the city/state in the customer table to avoid some joins?

Thanks for the help! Let me know if you need any other details.

Pros - It is the natural key, it makes sense and it will likely be searched on, I presume?

Cons - The default behavior (which is totally changeable) is for a primary key to be the clustered index. An alphanumeric doesn't make the best candidate because inserts can cause page splits because they aren't set on an ever increasing value like an identity column. The Int identity column will take less space (4bytes) compared to the character data (40+bytes for the unicode) . This makes your other indexes larger since the clustered key is part of them. If you ever change how you identify your customers and make customer codes, this all breaks - going with a surrogate insulates you from those type of changes.

In this situation, I tend to optimize for the insert performance and go with an identity column more often than not for the clustered key and primary key. I really like integer clustered indexes. (Now I know your question was not about clustered index, it was about primary key... You could still choose some other column to be the clustered index and make this your primary key, you could also put a unique constraint on this and treat it as a natural key but not make it your primary key).

I would at the very least index this with a unique constraint and treat it like a natural key. I just don't know if you really need to make it your primary key.

Kimberly Tripp is a trusted resource who has a lot to say about primary keys and (more so) clustered keys on her blog - https://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-the-clustering-key/

This is all just my opinion - YMMV.


Restore job run successfully without error but actually not applied during log ship from older version to newer one Our requirement is for SQL Server 2008 R2 databases to be upgraded/migrated to SQL Server 2016. We want to configure log shipping between SQL Server 2008 R2 (source) and SQL Server 2016 (target). We have also need to open these standby database after a long time (For example 1 month), while transaction logs will be deleted after two days. Is this possible?

Currently we have configured 2 test instances using NORECOVERY with Log Shopping jobs (Backup, Copy and Restore) running every hour, while Log history of these three jobs shows no error. i.e. Backup executes successfully on 2008R2 instance, Copy and Restore jobs execute fine on 2016.

However, when we check the timestamp of data and log files of the configured databases on 2016, it shows the timestamp when this was configured. It seems transaction logs are not being applied. Also, when we try to open the database on 2016 using Restore -> Database, it reports some transaction log missing (because we configured to delete older than 2 days transaction logs and hence these were deleted after 2 days).

It seems that at the time of database open on 2016, it actually applies/restores transaction log files. Please confirm! Would you please show whether there is any way it can apply transaction logs while in this UPGRADE setup as explained above and let older than 2 days transaction log files deleted as per scheduled till we actually upgrade to 2016?

You cannot restore using STANDBY from a lower version. If that is what you mean nu "open these standby databases after a long time).

STANDBY implies a recovery, which would need to physically upgrade the database. An database cannot be downgraded again, which in turn would be necessary when restoring a subsequent log backup.

Timestamp of database files are irrelevant. Don't bother with those. They aren't updated when information inside the file is modified.

You can keep the database in a restoring state for a long time, i.e., log shipping doing restore using NORECOVERY. But you need to have a schedule between the three agent jobs so that the restore job actually have the backup file available. So, for instance backup every hour, copy file every hour, restore (NORECOVERY, of course) every hour. And then delete the backup files after, say 1 day. That way the files neeeded for the restore will be on disk.


Can you use multiple types of indexes in one composite index I've built a covering index for a pretty big query which has already halved the select time.

Now, all the columns are just INT(11)'s so there isn't a problem there but one of the columns is a VARCHAR(255) whose performance I'm able to improve by using a FULLTEXT index.

Right now I'm forcing my query to use my covering index as somehow the optimizer doesn't think my index would be faster even though it is, but now I'm wondering if it's possible to specify which indexing algorithm to use per column on the composite index so that I can do:

id INT(11) - BTREE

code VARCHAR(255) - FULLTEXT

col3 INT(11) - BTREE

col4 INT(11) - BTREE

col5 INT(11) - BTREE

col6 INT(11) - BTREE

If I preview the SQL for adding the index on the table, at the end it says USING BTREE; so I was wondering if it's possible to specify it on a per-column basis

FULLTEXT and BTree cannot be mixed in a single composite index.

Please provide SHOW CREATE TABLE and the SELECT for further discussion of alternatives.

Always(?), the Optimizer will pick a FULLTEXT index over any other.  There are a few cases where complicating the query with a subquery can make it run faster.  (But let's see what you are doing.)


How to interpret the information spit out by innochecksum? MySQL is using up a lot of the disk space. Running du -sh on the data directory shows a humongous ibdata1 file (around 1.7 terabytes).

I found a very helpful article online and ran a modified version of the innochecksum program on the file as it instructed. Here's the (partial) output of the program:

  0      bad checksum

  96334276        FIL_PAGE_INDEX 3173387 FIL_PAGE_UNDO_LOG

  19932  FIL_PAGE_INODE

  45494  FIL_PAGE_IBUF_FREE_LIST

  11692831        FIL_PAGE_TYPE_ALLOCATED

  0      FIL_PAGE_IBUF_BITMAP

  0      FIL_PAGE_TYPE_SYS

  0      FIL_PAGE_TYPE_TRX_SYS

  0      FIL_PAGE_TYPE_FSP_HDR

  0      FIL_PAGE_TYPE_XDES

  0      FIL_PAGE_TYPE_BLOB

  0      FIL_PAGE_TYPE_ZBLOB

  0      other

  49572348        max index_id

  undo type: 331 insert, 1761203 update, 1411853 other

  undo state: 0 active, 297 cached, 0 to_free, 1475925 to_purge, 0
  prepared, 1697165 other

I don't have much knowledge of the INNODB internals, nor I could find any documentation that will help me interpret this information.

Could anyone help me understand what these information signify? Particularly, I want to know what is taking up so much space in the ibdata1 file.

You should see what is inside ibdata1 from an artist's conception (brought to you by Percona)

Before you decide to shrink ibdata1, you need to know the following:

From the size of the ibdata1 file (1.7TB), I can safely assume you are using innodb_file_per_table=0. Thus, data and index pages for all InnoDB tables sits inside ibdata1. You need to full summary of what is inside ibdata1:

SET @pw = 3;
SET @bu = power(1024,@pw);
SELECT
    FORMAT(SUM(DL/@bu),2) DataStorage,
    FORMAT(SUM(DL),2) DataStorageBytes,
    FORMAT(SUM(IL/@by),2) IndexStorage,
    FORMAT(SUM(IL),2) IndexStorageBytes,
    FORMAT(SUM(DL+IL),2), InnoDBStorage
FROM
(
    SELECT data_length DL,index_length IL
    FROM information_schema.tables WHERE ENGINE='InnoDB'
    AND information
) A;

Take the InnoDBStorage and subtract it from the filesize of ibdata1

The difference, which I will call Wiggle Room, is left for all other logical structures inside ibdata1:

Data Dictionary
Rollback Segments
Undo Logs
Double Write buffer
Insert Buffer

It is possible to get table full errors if the Wiggle Room (difference of ibdata filesize - InnoDBStorage) cannot accommodate the two busiest logical structures that can change size quickly: 1) Rollback Segments, 2) Undo Logs. I have seen this happen as recently as 6 months ago (I wrote about this before : How to solve "The table ... is full" with "innodb_file_per_table"?)

Here are you options

OPTION #1

If you decide to shrink ibdata1, make sure you have enough diskspace for doing a mysqldump and gzip of all the data. If none of the InnoDB table have foreign keys constraints, you could convert all InnoDB tables to MyISAM, shutdown mysql, delete ibdata1, delete ib_logfile0, delete ib_logfile1, set innodb_data_file_path=ibdata1:10M:autoextend in /etc/my.cnf, start mysql, and convert all those tables back to InnoDB. I just recently learned that technique for Shlomi Noach.

OPTION #2

If you decide to enable innodb_file_per_table, ibdata1 should still incredibly small for life. However, accessing metadata about InnoDB tables will be a lot slower.

OPTION #3

If you want to leave things the way they are, please add ibdata2 within /etc/my.cnf:

STEP01 : service mysql stop

STEP02 : Get filesize of ibdata1

cd /var/lib/mysql
IBDATA1_FILESIZE=`ls -l ibdata1 | awk '{print $5}'`
echo ${IBDATA1_FILESIZE}

Let's suppose IBDATA1_FILESIZE is 2000000000000.

STEP03 : Add ibdata2 to InnoDB Data File Path in /etc/my.cnf

innodb_data_file_path=ibdata1:2000000000000;ibdata2:10M:autoextend

STEP04 : service mysql start

Give it a Try !!!

CAVEAT Here are some nice links for InnoDB Internals

http://dev.mysql.com/doc/internals/en/innodb.html
http://blog.jcole.us/innodb/
http://www.percona.com/files/percona-live/justin-innodb-internals.pdf

Google searches can give you much more InnoDB Internals Documentation


How to find consecutive non zero values from a column I have a table as follows
user  timestamp            counts
xyz   01-01-2020 00:05:00   12
xyz   01-01-2020 00:10:00   11
xyz   01-01-2020 00:15:00   45
xyz   01-01-2020 00:20:00   0
xyz   01-01-2020 00:25:00   0
xyz   01-01-2020 00:30:00   13
xyz   01-01-2020 00:35:00   12
xyz   01-01-2020 00:40:00   0

I want to slice it based on max consecutive values(non-zero) values in count column and find out start timestamp, end timestamp and total_count in that duration
starttime            endtime               total_count
01-01-2020 00:05:00  01-01-2020 00:15:00   68

How can I achieve this using BigQuery

Test something like
WITH cte AS ( SELECT *, SUM(CASE WHEN counts=0 THEN 1 ELSE 0 END) OVER (ORDER BY timestamp) grp
              FROM sourcetable )
SELECT MIN(timestamp) start_time, MAX(timestamp) end_time, SUM(counts) total_count
FROM cte
WHERE grp = 0

DEMO fiddle (MySQL 8 used)


What Triggers sp_recompile in database without triggers, stored procedures, etc? In a database with only tables and indexes (ie no stored procs, functions, triggers, etc). We get a call to sp_recompile that blocks table selects / updates randomly (ie once a month but in a highly transactional system. Catching this via trace has been a challenge. The local DBA seems to only capture the statement in a blocking lock tree. It is my understanding that SQL Server uses some other method (ie. not a direct call to sp_recompile) to trigger the internal recompiles when needed for plan cache / object updates / aging.

I see from books online that you can call sp_recompile with a table object, which triggers a cascading flag change to recompile any objects related the next time it executes, but this seems to be directed toward other DB objects, not tables and indexes. So does anyone know if SQL Server calls / triggers sp_recompile directly?

The SQL Server engine doesn't explicitly call sp_recompile in order to remove plans from the cache.  As far as I know anyway.  There might be features of SQL Server that do this, but I haven't heard of it.

The mostly likely culprit is that some individual or user process is calling sp_recompile.  Hopefully you can tune the tracing process you mentioned to capture the user executing this call.

I recently noticed that calling this on a table or view takes a SCH-M lock on that table or view, which would explain the blocking (no reads or writes can take place against the table with this lock in place).  It does not take very long on the systems I have access to, so I'm not sure why it would cause blocking for a long time.


Postgres CYCLE and existing values I love that SERIALs can wrap around with CYCLE.

However, I can't seem to figure out if that there are lingering values, will Postgres skip over them or throw an error.

In other words, I have a table that needs UNIQUE auto incremented values.  Many will be destroyed over time, but many will remain.  After the max value has been reached, does Postgres increment past it, or will it throw an error because the next incremented value already exists?

The sequence will return values in order, whether or not they exist in the table. Your code will need to defend against that. Any method to defend against possibly already-in-use serial values will really hurt concurrency - and if your DB is big enough that you're wrapping int4 serial columns, you care about concurrency.

Most people just use bigint keys and don't worry about ID re-use.  You won't run out.


Do Indexes in the T-SQL Data Warehouse automatically go over and optimize into the SSAS Cube? Do Indexes in the T-SQL Data Warehouse automatically go over and optimize into the SSAS Cube?
I am reading that applying indexes, will transfer into SSAS. I thought SQL and SSAS are totally different environments.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/acda0b00-b40c-47cf-b9ca-334ccf2e4212/how-indexing-affect-performance-on-analyses-service-cube?forum=sqlanalysisservices

  So the point is, create appropriate indexes on source tables to
  improve the performance of the query which SSAS fires while processing
  the cube or while retrieving data from the source. If you have access
  to the source data, you can use this DMV to identify missing indexes
  or you can use the Index Tuning Advisor for identifying and creating
  missing indexes on the source.

Additionally, do columnstore indexes translate into SSAS Cubes?

The answer you quoted is worded a bit confusing but it says that it improves the performance of processing the cube, not querying it.

  So the point is, create appropriate indexes on source tables to
  improve the performance of the query which SSAS fires while processing
  the cube or while retrieving data from the source. If you have access
  to the source data, you can use this DMV to identify missing indexes
  or you can use the Index Tuning Advisor for identifying and creating
  missing indexes on the source.

Querying the cube doesn't retrieve data from the source, it retrieves data from the SSAS storage engine.

What can happen however is that your cube has a drillthrough action that action may read from the actual source database and filter your fact table on the dimension keys which were filtering the measure in the cube, in which case it may benefit from indexes in your data warehouse.


WHERE 1 = 2 returns a row Any idea, why the first of the following queries returns a row with -1, NULL, although it has a WHERE 1=2?

Only when I put the query in another subquery it works correct (and returns an empty result set).

Tested on Microsoft SQL Server 2014 and 2016

DECLARE @i INT = 1

SELECT @i i, MAX(sub.id) mid
  FROM (SELECT TOP(@i) x.id
          FROM (VALUES(1), (2), (3), (4)) x(id)
         WHERE x.id > 2 + @i
         ORDER BY x.id) sub
WHERE 1 = 2

SELECT s1.i, s1.mid
  FROM (
        SELECT @i i, MAX(sub.id) mid
          FROM (SELECT TOP(@i) x.id
                  FROM (VALUES(1), (2), (3), (4)) x(id)
                 WHERE x.id > 2 + @i
                 ORDER BY x.id) sub
       ) s1
WHERE 1 = 2

An aggregate query without a GROUP BY is a scalar aggregate and (in the absence of any HAVING clause) returns exactly one row.

So if you do

SELECT COUNT(*)
FROM EmptyTable

Or

SELECT COUNT(*)
FROM SomeOtherTable
WHERE 1 = 0

Both would return the value 0 rather than an empty result set.

If you add a GROUP BY it then becomes a vector aggregate and returns one row per group (So would return nothing in the above examples) - See Vector and scalar aggregates

Your first query just consists of a scalar aggregate with @i added to the SELECT list so you get the single row. The second query has no aggregates of any kind and nothing matches the WHERE so you get no row.

Further Reading

Fun with Aggregates - Paul White


What is a "PARTITIONED OUTER JOIN"? This just came up in a question on Reddit, and I'm wondering

What is a PARTITIONED OUTER JOIN in Oracle? (definition)
What does a simple example look like? (use)
How would you write it in terms of PostgreSQL or Standard SQL that otherwise lacks PARTITIONED OUTER JOIN? (equivalence)

{1} Partitioned Outer Join: Definition

... "Such a join extends the conventional outer join syntax by applying the outer join to each logical partition defined in a query. Oracle logically partitions the rows in your query based on the expression you specify in the PARTITION BY clause. The result of a partitioned outer join is a UNION of the outer joins of each of the partitions in the logically partitioned table with the table on the other side of the join." (documentation)

{2} Simple Example

"Data is normally stored in sparse form. That is, if no value exists for a given combination of dimension values, no row exists in the fact table. However, you may want to view the data in dense form, with rows for all combination of dimension values displayed even when no fact data exist for them."

... "For example, if a product did not sell during a particular time period, you may still want to see the product for that time period with zero sales value next to it." (quotes from the documentation)

Test tables and data (INSERTs)

-- Oracle 12c
create table sales (
  date_ date
, location_ varchar2( 16 )
, qty_ number
);

create table locations (
  name varchar2( 16 )
);

-- dates for locations are "gappy":
-- none of the locations has entries for all 3 dates
-- ( date range: 2019-01-15 - 2019-01-17 )
insert into sales ( date_, location_, qty_ )
  values ( date '2019-01-17', 'London', 11 ) ;
insert into sales ( date_, location_, qty_ )
  values ( date '2019-01-15', 'London', 10 ) ;
insert into sales ( date_, location_, qty_ )
  values ( date '2019-01-16', 'Paris', 20 ) ;
insert into sales ( date_, location_, qty_ )
  values ( date '2019-01-17', 'Boston', 31 ) ;
insert into sales ( date_, location_, qty_ )
  values ( date '2019-01-16', 'Boston', 30 ) ;

-- locations
insert into locations ( name ) values ( 'London' );
insert into locations ( name ) values ( 'Paris' );
insert into locations ( name ) values ( 'Boston' );

Required output

date_       location_  qty_
2019-01-15  London     10
2019-01-15  Paris       0    -- not INSERTed!
2019-01-15  Boston      0    -- not INSERTed!
2019-01-16  London      0    -- not INSERTed!
2019-01-16  Paris      20
2019-01-16  Boston     30
2019-01-17  London     11
2019-01-17  Paris       0    -- not INSERTed!
2019-01-17  Boston     31

Query (partitioned outer join)

select S.date_, S.qty_, L.name
from sales S partition by ( date_ )
  right join locations L on S.location_ = L.name
;

-- result
DATE_      QTY_  NAME
15-JAN-19  NULL  Boston
15-JAN-19  10    London
15-JAN-19  NULL  Paris
16-JAN-19  30    Boston
16-JAN-19  NULL  London
16-JAN-19  20    Paris
17-JAN-19  31    Boston
17-JAN-19  11    London
17-JAN-19  NULL  Paris

Query (version 2, same join)

-- same as above, using NVL(), column aliases, and ORDER BY ...
select S.date_, nvl( S.qty_, 0 ) as sold, L.name as location
from sales S partition by ( date_ )
  right join locations L on S.location_ = L.name
order by S.date_, L.name
;

DATE_           SOLD LOCATION
--------- ---------- ----------------
15-JAN-19          0 Boston
15-JAN-19         10 London
15-JAN-19          0 Paris
16-JAN-19         30 Boston
16-JAN-19          0 London
16-JAN-19         20 Paris
17-JAN-19         31 Boston
17-JAN-19         11 London
17-JAN-19          0 Paris

Dbfiddle here (Oracle 18c)

{3} Equivalence

The following query does roughly the same job as the PARTITION BY ( date_ ) outer join.  We are using a combination of a CROSS JOIN (inner SELECT) and LEFT OUTER JOIN.
(Conversion of NULLs to 0s omitted)

Oracle

select SL.*, S.qty_
from
(
  select *
  from (
    select unique date_ from sales
  ) , (
    select unique name from locations
  )
) SL left join (
  select date_, location_, qty_ from sales
) S on SL.name = S.location_ and SL.date_ = S.date_
order by SL.date_, SL.name
;

DATE_      NAME    QTY_
15-JAN-19  Boston  NULL
15-JAN-19  London  10
15-JAN-19  Paris   NULL
16-JAN-19  Boston  30
16-JAN-19  London  NULL
16-JAN-19  Paris   20
17-JAN-19  Boston  31
17-JAN-19  London  11
17-JAN-19  Paris   NULL

PostgreSQL 10 ( dbfiddle )

-- DDL and INSERTs
create table sales (
  date_ date
, location_ varchar( 16 )
, qty_ number
);

create table locations (
  name varchar( 16 )
);

insert into sales ( date_, location_, qty_ ) values
  ( '2019-01-17', 'London', 11 )
, ( '2019-01-15', 'London', 10 )
, ( '2019-01-16', 'Paris', 20 )
, ( '2019-01-17', 'Boston', 31 )
, ( '2019-01-16', 'Boston', 30 )

insert into locations ( name ) values
( 'London' ), ( 'Paris' ), ( 'Boston' );

Query (Postgres)

-- SL: all date_ <-> location combinations
-- S: all location_ and qty_ values of table sales
select SL.*, S.qty_
from
(
  select *
  from (
    select distinct date_ from sales
  ) S_ cross join (
    select distinct name from locations
  ) L_
) SL left join (
  select date_, location_, qty_ from sales
) S on SL.name = S.location_ and SL.date_ = S.date_
order by SL.date_, SL.name
;

date_        name    qty_
2019-01-15   Boston
2019-01-15   London  10
2019-01-15   Paris
2019-01-16   Boston  30
2019-01-16   London
2019-01-16   Paris   20
2019-01-17   Boston  31
2019-01-17   London  11
2019-01-17   Paris


List of nodes types in a query plan in PostgreSQL? Where can I find an exhaustive list of possible nodes types(e.g. Seq scan, groupAggregate, Nested loop ...) that a query plan could contain in PostgreSQL?

If you are just looking for a comprehensive list of types, without any explanations, this piece of source code might be of help to you.


Audit DML operations on MySQL Tables We have several MySQL 5.0 databases.

We've been having security problems, like records being modified without authorization.

We need to implement some kind of audit on DML operations identifying users that perform UPDATE or DELETE operations on some critical tables.

I have been googling about any my.conf options that would activate this kind of audits, to no avail.

The least disruptive method would be to use the general query log. The query log output can be a database table or a log (text) file; however it does not support filtering by DML operation or database so all SELECT statements on all databases will be logged as well. Obviously you can filter the logging output later.

Also note:

  The session sql_log_off variable can be set to ON or OFF to disable or enable general query logging for the current connection.

This means that with some injected logic, you could conceivably disable logging for SELECTs.

Another solution is to use triggers on UPDATE, INSERT, DELETE. This will directly affect the database in question but not other databases running in the same instance.

See also SO questions here and here.


Reinsert data in new table with new IDs I have the following two tables:

buynsell

CREATE TABLE `buynsell` (
    `id` VARCHAR(20) NULL DEFAULT NULL,
    `msg` VARCHAR(255) NULL DEFAULT NULL,
    `date` VARCHAR(25) NULL DEFAULT NULL,
    `fid` BIGINT(20) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`fid`),
    INDEX `fid_index` (`fid`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
AUTO_INCREMENT=6523;

bsmsgs

CREATE TABLE `bsmsgs` (
    `id` VARCHAR(20) NULL DEFAULT NULL,
    `msg` MEDIUMTEXT NULL,
    `date` VARCHAR(25) NULL DEFAULT NULL,
    `subfid` BIGINT(20) NULL DEFAULT NULL,
    `fid` BIGINT(20) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`fid`),
    INDEX `fid_subfid_index` (`fid`, `subfid`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
AUTO_INCREMENT=11408;

The field bsmsgs.subfid is referenced to bunsell.fid. I was fed up using these tables, and hence created the following two new ones(Engine is still MYISAM):

buynsell

CREATE TABLE `buynsell` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `type` ENUM('B','S','D','H','T') NOT NULL DEFAULT 'B',
    `msg` VARCHAR(250) NOT NULL,
    `nick` VARCHAR(32) NOT NULL,
    `dated` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `msg_nick` (`msg`, `nick`),
    INDEX `type` (`type`),
    FULLTEXT INDEX `msg` (`msg`)
)
COMMENT='The table storing information about users selling/buying/hiring things etc.'
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

replies

CREATE TABLE `replies` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `bns_id` INT(10) UNSIGNED NOT NULL,
    `msg` VARCHAR(250) NOT NULL,
    `nick` VARCHAR(32) NOT NULL,
    `dated` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `bns_id_msg` (`bns_id`, `msg`),
    INDEX `bns_id` (`bns_id`),
    FULLTEXT INDEX `msg` (`msg`)
)
COMMENT='Replies to buynsell messages from interested users.'
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

Now, my problem is even though the AUTO_INCREMENT values are so high, there are actually less than 2000 entries in buynsell table and around 8000 entries in bsmsgs table.

What I can do is use the same entries from previous tables in my new ones but I want an elegant design and hence, would like to insert from scratch.

I can reinsert data from older buynsell by skipping to AUTO_INCREMENT key, fid and generate new rows with ids starting from 1 to 2000.

What I am facing trouble with is referencing the subfid field in bsmsgs to new id values in buynsell while correctly referencing and storing them into bns_id of replies table.

NOTE

If it can not be achieved by purely MySQL, I can use LuaSQL for this task.

I guess you can do the following:
1. In your new table buynsell, add a column , old_id
2. Reinsert data from the old table as you said, skipping auto_increment field, and storing old_buynsell.id as old_id in your new table.
3. Import data to new replies table
4. Update bns_id column in replies :

UPDATE replies a
INNER JOIN (select id, old_id FROM buynsell) b ON (b.old_id = a.bns_id)
SET a.bns_id=b.id

5 Drop old_id column, you don't need it anymore.


Creating a stored procedure and adding date manually to filename I am new to SQL administration and have been tasked with creating some nightly jobs that send an email with certain details contained within a spreadsheet and so far I have the following:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'support',
@recipients = 'test@mail.co.uk',
@subject = 'Post Code Analysis',
@query = N'SET ANSI_WARNINGS OFF;SET NO COUNT ON;select substring(PostOfficeBox,1,4) as ''Postcode''
, COUNT(CASE WHEN New_accounttype = 1 THEN New_AccountType ELSE NULL END) as ''New Connections''
, COUNT(CASE WHEN New_accounttype = 2 THEN New_AccountType ELSE NULL END) as ''Domestic Metered''
, COUNT(CASE WHEN New_accounttype = 3 THEN New_AccountType ELSE NULL END) as ''Commercial Metered Low''
, COUNT(CASE WHEN New_accounttype = 4 THEN New_AccountType ELSE NULL END) as ''Commerical Metered High''
, COUNT(CASE WHEN New_accounttype = 5 THEN New_AccountType ELSE NULL END) as ''Domestic Keypad''
, COUNT(CASE WHEN New_accounttype = 6 THEN New_AccountType ELSE NULL END) as ''Generator''
, COUNT(CASE WHEN New_accounttype = 7 THEN New_AccountType ELSE NULL END) as ''Commercial Keypad''
from be_crm4_MSCRM.dbo.AccountExtensionBase as a
INNER JOIN be_crm4_MSCRM.dbo.CustomerAddressBase as b ON a.AccountId =     b.ParentId
where New_AccountStage = 7
and AddressTypeCode = 1
and substring(PostOfficeBox,1,2) = ''BT''
group by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)
order by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4);',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'pca-test.csv',
@query_result_header =0,
@query_result_separator = ' '

In the @query_attachment_filename I would like to append the date and have tried using SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] and appending this to the file name as follows:

@query_attachment_filename = 'pca-test-' + SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] + '.csv'

Any advice would be greatly appreciated

Declare a variable first - but use format 112 for this. Like this:

DECLARE @query_attachment_filename NVARCHAR(255) =
    N'pca-test-' +
    CONVERT(NCHAR(8), GETDATE(), 112) +
    N'.csv';

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'support',
...
@attach_query_result_as_file = 1,
@query_attachment_filename = @query_attachment_filename,
@query_result_header =0,
@query_result_separator = ' '