How can I investigate why Sql Server is chosing the “wrong” Index?

I have a Transaction table with about 200 million records, one primary key clustered on Id and 2 indexes:

  • IX_SiloId_ChangedTime_IncludeTime
  • IX_SiloId_Time_IncludeContent

I run these 2 statements before I proceed with the actual query to update statistics

Update STATISTICS dbo.(Transaction) IX_SiloId_ChangedTime_IncludeTime WITH FULLSCAN
Update STATISTICS dbo.(Transaction) IX_SiloId_Time_IncludeContent WITH FULLSCAN

This is my query:

DECLARE @Query SiloTimeQueryTableType -- (SiloId, Time) with primary key clustered on SiloId
INSERT INTO @Query VALUES 
(1, '2020-12-31'), -- 1000 total values, though it's still the same problem with just one

SELECT  t.*
FROM    (Transaction) t
INNER JOIN @Query q
    ON t.SiloId = q.SiloId
WHERE 
    t.Time >= q.Time

Now what happens is for whatever reason Sql Server choses IX_SiloId_ChangedTime_IncludeTime. It then takes forever. If I use WITH (INDEX(IX_SiloId_Time_IncludeContent)) I get the result right away.

The correct index is quite obvious here, but yet SQL Server choses the one that is not even indexed on Time.

I cannot understand this behaviour, but from what I read it is best to avoid hints for Indexes, though I made this Index with this query in mind.

So the question is: what can I do to try to figure out why SQL Server prefers the “wrong” index even though a much better one exists and I just run full statistics update?

Query plan for the forced index (here from the temp table instead of TVP to check if this changes anything as the answer suggested, the result seems to be the same):

enter image description here

Query plan without forced index:

enter image description here

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

https://www.brentozar.com/pastetheplan/?id=ByFshGAAP (this one is live, as it takes too long)

beginner – Random index generator in C++

I have a training dataset from which I want to draw samples in a random fashion such that all samples are used before getting randomly shuffled again. For this reason I implemented a simple random index generator.

For a dataset of 10 samples, the output looks something like this:

0 1 2 3 4 5 6 7 8 9 
4 3 7 8 0 5 2 1 6 9 
0 5 7 8 4 3 9 2 1 6 
5 7 6 3 8 4 2 0 1 9 
4 6 0 2 8 1 3 9 5 7 
4 0 5 1 7 9 6 2 8 3 
3 8 5 6 1 7 2 4 0 9 
0 4 6 2 9 5 8 3 1 7 
1 3 6 8 2 7 5 9 0 4 
5 1 7 9 8 0 6 4 2 3 

I would appreciate advice especially in the following areas:

  • Code style (readability, naming conventions, etc…)
  • Class design
  • Efficiency (how to avoid unnecessary complexity)
  • Reinventing the wheel (does the STL offer functionality that I should use?)
  • Are there perhaps bugs that I am not seeing right now?

Please be as hard as possible with this implementation and give me constructive feedback.

main.cpp

#include <iostream>
#include "random_index.hpp"

int main() {
    unsigned int size = 10;
    RandomIndex rand_idx(size);

    unsigned int n = 0;
    for (unsigned int i=0; i<100; ++i, ++n) {
        std::cout << rand_idx.get_index() << ' ';
        if ((n+1) % size == 0) {
            std::cout << 'n';
        }
    }

    std::cout << 'n';
}

random_index.cpp

#include "random_index.hpp"

RandomIndex::RandomIndex(unsigned int _size) {
    size = _size;
    index.resize(_size, 0);
    std::iota(index.begin(), index.end(), 0);
}

unsigned int RandomIndex::get_index() {
    if (counter < size) {
        return index(counter++);
    } else {
        counter = 0;
        std::random_shuffle(index.begin(), index.end());
        return index(counter++);
    }
}

random_index.hpp

#ifndef RANDOM_INDEX_H
#define RANDOM_INDEX_H

#include <vector>
#include <numeric>
#include <algorithm>

class RandomIndex {
    
    public:
        RandomIndex(unsigned int _size);
        unsigned int get_index();
    
    private:
        unsigned int size;
        unsigned int counter = 0;
        std::vector<unsigned int> index;

};

#endif

I compiled the code using the following command:

g++ -O -Wall main.cpp random_index.cpp 

How can I get my Instagram index on google?

I am having problem update my Instagram url to index on google? Can somebody tell me how to update on google?

magento2 – Catalog product and Stock index Error

I am getting this strange error while reindex, and I cannot find any solution on google or on stackoverflow.

Stock index exception: SQLSTATE[42S01]: Base table or view already
exists: 1050 Table ‘./mydb/cataloginventory_stock_status_outdated’
already exists, query was: RENAME TABLE cataloginventory_stock_status
TO
cataloginventory_stock_status_outdated,cataloginventory_stock_status_replica
TO
cataloginventory_stock_status,cataloginventory_stock_status_outdated
TO cataloginventory_stock_status_replica

already checked in /var/lib/mysql/mydb/ and there is no cataloginventory_stock_status_outdated table

I am getting the same error for catalog_category_product_index_store1_outdated
Any help please.

sql server – SQLServer – treat table as index

I have table that is more or less as follows:

table Sample (
    location bigint,
    device bigint,
    timestamp datetime,
    type bigint,
    value bigint,
    )

For performance reasons we also have one mighty index with same columns in identical order, except that value is included rather than used in index.

This works as expected, except that AFAIU data is kept twice in database – once in the table and once in an index.

Question: is there some way to, conceptually, keep data only in the index? I believe that would require keeping table organized in the same way index is organized, effectively removing need for the index. I tried to create composed primary key that would emulate aforementioned index, but it boiled down to automatic creation of identical index.

r – Finding rowwise minimum and column index in a tibble

I have the following tibble:

> df <- tibble(
     ID = LETTERS(1:4),
     a  = c(1,5,9,8),
     b  = c(5,9,8,2),
     c  = c(5,4,5,5)
)

> df
# A tibble: 4 x 4
  ID        a     b     c
  <chr> <dbl> <dbl> <dbl>
1 A         1     5     5
2 B         5     9     4
3 C         9     8     5
4 D         8     2     5
> 

What I want is to get the rowwise minimum of columns a:c and also the column index from this minimum.
The output tabel should look like this:

# A tibble: 4 x 6
  ID        a     b     c   Min Col_Index
  <chr> <dbl> <dbl> <dbl> <dbl>     <dbl>
1 A         1     5     5     1         1
2 B         5     9     4     4         3
3 C         9     8     5     5         3
4 D         8     2     5     2         2

I don’t want to use rowwise()!

Thank you!

mysql – A puzzled deadlock: it seems that the lock is acquired/hold on primary index first

We got a puzzled deadlock on MySQL 5.7 (Engine: InnoDB, Isolation Level: RR). The report result of show engine innodb status as below shown

*** (1) TRANSACTION:
TRANSACTION 1739954050, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 4253877, OS thread handle 47904135608064, query id 4259685238 jacky Searching rows for update

        UPDATE fruit_setting set
        value = CASE
            WHEN eid = 'L1XSHY' and key = 'priority' THEN '14343'
            WHEN eid = 'Rtb95t' and key = 'priority' THEN '14344'
            WHEN eid = 'wdsNwr' and key = 'priority' THEN '14345'           
            WHEN eid = 'K1Ikqy' and key = 'priority' THEN '14345'
            WHEN eid = 
            
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 533 page no 65378 n bits 0 index PRIMARY of table `jacky`.`fruit_setting` trx id 1739954050 lock_mode X locks rec but not gap waiting
...


*** (2) TRANSACTION:
TRANSACTION 1739954049, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 94 lock struct(s), heap size 1136, 184 row lock(s)
MySQL thread id 4257460, OS thread handle 47904340621056, query id 4259685231  jacky Searching rows for update

        UPDATE fruit_setting set value = CASE
            WHEN eid = 'M5ecaA' and key = 'priority' THEN '16171'
            WHEN eid = '1o0bdT' and key = 'priority' THEN '16172'
            WHEN eid = 'XNxx5S' and key = 'priority' THEN '16173'
            WHEN eid = '1o0bdT' and key = 'priority' THEN '16174'
            WHEN eid = 
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 533 page no 65378 n bits 0 index PRIMARY of table `jacky`.`fruit_setting` trx id 1739954049 lock_mode X locks rec but not gap
Record lock, heap no 105 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 ...

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 533 page no 46944 n bits 0 index PRIMARY of table `jacky`.`fruit_setting` trx id 1739954049 lock_mode X locks rec but not gap waiting
Record lock, heap no 58 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
...

The sqls in above report has been truncated (might due to size limit of MySQL), one of the whole sql looks like (we only record the prepared statement)

UPDATE fruit_setting set value = CASE
            WHEN eid = ? and key = ? THEN ?
            WHEN eid = ? and key = ? THEN ?
            WHEN eid = ? and key = ? THEN ?
            WHEN eid = ? and key = ? THEN ?
            WHEN eid = ? and key = ? THEN ?
            WHEN eid = ? and key = ? THEN ?
            WHEN eid = ? and key = ? THEN ?
            WHEN eid = ? and key = ? THEN ?
            WHEN eid = ? and key = ? THEN ?
        END
WHERE aid = ? and eid in (?, ?, ?, ?, ?, ?, ?, ?, ?) and key = ?

The table DDL

CREATE TABLE `fruit_setting` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `aid` varchar(32) NOT NULL,
  `eid` varchar(32) NOT NULL,
  `key` varchar(32) NOT NULL,
  `value` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `i_e_key` (`eid`, `key`),
  KEY `i_a_eid` (`aid`, `eid`)
);

Note that the aid and key in two sqls are the same and the eid in in clause could be overlapped; And thus we guess that the deadlock occurred since the locks are acquired in reverse order on index i_e_key or i_a_eid.

Question 1: why the lock is not on index i_e_key or i_a_eid, but on primary index? AFAIK, the secondary index would be locked before primary index if the searching for update used the secondary index.

If a secondary index is used in a search and index record locks to be set are exclusive, InnoDB also retrieves the corresponding clustered index records and sets locks on them.

https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html

Question 2: Does MySQL acquire lock on index record one by one based on the order of eid appeared in in clause during execute update ... where eid in (...)?

By the way, it’s hard to reproduce, I tried to lock on eid in different order, however, the new deadlock report showed that the lock is on secondary index.

Explain update here

enter image description here

seo – Google’s Inspect URL shows incorrect source code and screenshots for my page’s “Google Index” and also in “Live Test”

When I used the URL Inspection Tool in Google Search Console, The index shows the wrong information as shown below:

Different source code is shown:

enter image description here

enter image description here

The screenshot of the website too different.

The search result of a page appeared in google:

enter image description here

I couldn’t understand the problem. Please help me to find the problem and fix it.

sql server – Improve performance of index rebuild on volatile table

This is occurring on SQL Server 2016 (Enterprise).
Example Table structure:

CREATE TABLE (dbo).(Foo)(
    (FooId) (bigint) IDENTITY(1,1) NOT NULL,
    (FirstFkId) (bigint) NULL,
    (SecondFkId) (bigint) NULL,
    (ThirdFkId) (bigint) NULL,
    (FourthFkId) (bigint) NOT NULL,
    (ParentId) (bigint) NULL,
    (Flag1) (bit) NOT NULL,
    (Flag2) (bit) NOT NULL,
    (From) (datetime2)(7) NOT NULL,
    (Until) (datetime2)(7) NOT NULL,
    (CreatedBy) (int) NOT NULL,
    (CreatedOn) (datetime2)(7) NOT NULL,
    (ChangedBy) (int) NOT NULL,
    (ChangedOn) (datetime2)(7) NOT NULL,
 CONSTRAINT (PK_Foo) PRIMARY KEY CLUSTERED 
(
    (FooId) ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON (PRIMARY)
) ON (PRIMARY)

CREATE TABLE (dbo).(FooDetail)(
    (FooDetailId) (bigint) IDENTITY(1,1) NOT NULL,
    (FooId) (bigint) NOT NULL,
    (FirstFkId) (bigint) NOT NULL,
    (SecondFkId) (bigint) NULL,
    (Column1) (bigint) NOT NULL,
    (Column2) (nvarchar)(250) NULL,
    (Column3) (nvarchar)(250) NULL,
    (Column4) (datetime2)(7) NULL,
    (Column5) (datetime2)(7) NULL,
    (Column6) (datetime2)(7) NULL,
    (Column7) (datetime2)(7) NULL,
    (Column8) (datetime2)(7) NULL,
    (CreatedBy) (int) NOT NULL,
    (CreatedOn) (datetime2)(7) NOT NULL,
    (ChangedBy) (int) NOT NULL,
    (ChangedOn) (datetime2)(7) NOT NULL,
 CONSTRAINT (PK_FooDetail) PRIMARY KEY CLUSTERED 
(
    (FooDetailId) ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON (PRIMARY)
) ON (PRIMARY)

ALTER TABLE (dbo).(FooDetail)  WITH CHECK ADD  CONSTRAINT (FK_FooDetail_Foo) FOREIGN KEY((FooId))
REFERENCES (dbo).(Foo) ((FooId))

This tables have data from a sliding window. There is a process that insert into the tables for a month every day and a different process that deletes older data.

Both tables have around 125.000 rows.

The data is read like this:

SELECT -- columns
FROM Foo f
INNER JOIN FooDetail fd on fd.FooId = f.FooId
WHERE f. -- Some condition

When investigating we found that the index rebuild for the clustered index of FooDetail takes between 90 minutes and 120 minutes. With the following command:

ALTER INDEX (PK_FooDetail) ON (DbName).(dbo).(FooDetail) REORGANIZE WITH (LOB_COMPACTION = ON)

Extend event info:

<ExtendedInfo>
  <PageCount>3739</PageCount>
  <Fragmentation>5.00134</Fragmentation>
</ExtendedInfo>

We use ola hallengren solution for index rebuilding.

Disk speed (ms) data file:

  • Avg Read Stall: 6
  • Avg Write Stall: 33

Disk speed (ms) log file:

  • Avg Read Stall: 5
  • Avg Write Stall: 6

My question how can we speed up the index rebuild, how can we get the best improvement?

Some ideas:

  • Should we exclude the table from the general index rebuild and have a seperate job to rebuild the index of these tables when we know there is less write usage? To suffer less from locking. (The application is used 24/7 so locking will always be an issue.)
  • Is it a good idea, to remove the clustered index and use a heap instead, since the data is so volatile?
    (We have a different index on FooDetail for FooId)

Why is this query on JSON values in SQL Server using an index scan instead of an index seek?

I have a table with the following schema:

CREATE TABLE (dbo).(Obj1Json)(
    (ObjectID) (int) IDENTITY(1,1) NOT NULL,
    (PointerToSource) (nvarchar)(255) NULL,
    (CreateDate) (datetime2)(7) NOT NULL,
    (ModifiedDate) (datetime2)(7) NOT NULL,
    (Indexes) (nvarchar)(max) NULL,
    (vAccountID)  AS (json_value((Indexes),'$.AccountID'))

where the Indexes column contains JSON that looks like:

{
  "AccountID": 73786,
  "AccountName": "5869b4e9-f441-463f-8f6d-93b4f4ff8c75",
  "ProcessLocation": "Start",
  "IsPasswordProtected": true,
  "InvoiceDate": "2020-12-30T09:00:32.8473077-05:00"
}

The vAccountID column is used for an index on the JSON in Indexes:

CREATE INDEX IDX_Obj1Json_AccountID
ON Obj1Json(vAccountID)

The table has about 10.5 million rows of randomly generated data (all data in the Indexes column has the same structure, however). The query I’m running is

SELECT JSON_VALUE(Indexes, '$.AccountID')
  FROM (Obj1Json)
  WHERE JSON_VALUE(Indexes, '$.AccountID') = 69725

which returns 110 results. When looking at the execution plan in SSMS, I see that an index scan is being used, whereas I would expect an index seek to be used instead.

Query plan of the above query, showing an index scan being used

The query returns quickly (under a second), but I’m curious why an index seek isn’t being used. Is there any obvious reason why SQL Server is using an index scan instead of a seek?