SQL Server – New Index Causing Table to Lock Frequently

entity-frameworkindexlockingsql server

Here are my tables :

CREATE TABLE [dbo].[Trackers] (
    [IdTracker]             INT           IDENTITY (1, 1) NOT NULL,
    [IMEI]                  NVARCHAR (16) NULL,
    [CreationDate]          DATETIME      NULL,
    [SuppressionDate]       DATETIME      NULL,
    [LastUpdateDate]        DATETIME      NULL,
    [BuyDate]               DATETIME      NOT NULL,
    [Status]                INT           NOT NULL,
    [LastTrackerUpdateDate] DATETIME      NULL,
    [IdLastTrackerPosition] INT           NULL,
    PRIMARY KEY CLUSTERED ([IdTracker] ASC)
);


CREATE TABLE [dbo].[TrackerPositions] (
    [IdTrackerPosition] INT               IDENTITY (1, 1) NOT NULL,
    [TrackerId]         INT               NOT NULL,
    [Position]          [sys].[geography] NULL,
    [Date]              DATETIME          NOT NULL,
    [Speed]             REAL              NULL,
    [NbSatellites]      TINYINT           NULL,
    [Direction]         REAL              NULL,
    [HDOP]              REAL              NULL,
    PRIMARY KEY CLUSTERED ([IdTrackerPosition] ASC),
    CONSTRAINT [TrackerTrackerPosition] FOREIGN KEY ([TrackerId]) REFERENCES [dbo].     [Trackers] ([IdTracker])
 );

Tracker position is used to store positions send by car trackers. So I have a lot of insert going on.
And I often need to get the last position known for a tracker.

So here is a query generated by Entity Framework :

SELECT 
    [Limit1].[IdTrackerPosition] AS [IdTrackerPosition], 
    [Limit1].[TrackerId] AS [TrackerId], 
    [Limit1].[Position] AS [Position], 
    [Limit1].[Date] AS [Date], 
    [Limit1].[Speed] AS [Speed], 
    [Limit1].[NbSatellites] AS [NbSatellites], 
    [Limit1].[Direction] AS [Direction], 
    [Limit1].[HDOP] AS [HDOP]
    FROM   (SELECT DISTINCT 
        [Extent1].[TrackerId] AS [TrackerId]
        FROM [dbo].[TrackerPositions] AS [Extent1]
        WHERE ([Extent1].[Position] IS NOT NULL) AND ([Extent1].[TrackerId] IN (1, 48)) ) AS [Distinct1]
    OUTER APPLY  (SELECT TOP (1) [Project2].[IdTrackerPosition] AS [IdTrackerPosition], [Project2].[TrackerId] AS [TrackerId], [Project2].[Position] AS [Position], [Project2].[Date] AS [Date], [Project2].[Speed] AS [Speed], [Project2].[NbSatellites] AS [NbSatellites], [Project2].[Direction] AS [Direction], [Project2].[HDOP] AS [HDOP]
        FROM ( SELECT 
            [Extent2].[IdTrackerPosition] AS [IdTrackerPosition], 
            [Extent2].[TrackerId] AS [TrackerId], 
            [Extent2].[Position] AS [Position], 
            [Extent2].[Date] AS [Date], 
            [Extent2].[Speed] AS [Speed], 
            [Extent2].[NbSatellites] AS [NbSatellites], 
            [Extent2].[Direction] AS [Direction], 
            [Extent2].[HDOP] AS [HDOP]
            FROM [dbo].[TrackerPositions] AS [Extent2]
            WHERE ([Extent2].[Position] IS NOT NULL) AND ([Extent2].[TrackerId] IN (1, 48)) AND ([Distinct1].[TrackerId] = [Extent2].[TrackerId])
        )  AS [Project2]
        ORDER BY [Project2].[IdTrackerPosition] DESC ) AS [Limit1]

And here an index suggested by SQL Server Studio :

CREATE NONCLUSTERED INDEX [_dta_index_TrackerPositions_5_352108395__K2_3] ON [dbo].[TrackerPositions]
(
    [TrackerId] ASC
)
INCLUDE (   [Position]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

Performances are way better with the index, but most of my insert got locked…

Do you guys have any idea about a better index/select I could use ?

Edit :
The index you guyrs gave me was fine until then.
But I have some isues regarding performance now that my table is much bigger.

I tried this query, but the results are even worst :

    SELECT 
        [IdTrackerPosition] AS [IdTrackerPosition], 
        [TrackerId] AS [TrackerId], 
        [Position] AS [Position], 
        [Date] AS [Date], 
        [Speed] AS [Speed], 
        [NbSatellites] AS [NbSatellites], 
        [Direction] AS [Direction], 
        [HDOP] AS [HDOP]
FROM
(
    SELECT 
        IdTrackerPosition,
        [TrackerId] AS [TrackerId], 
        [Position] AS [Position], 
        [Date] AS [Date], 
        [Speed] AS [Speed], 
        [NbSatellites] AS [NbSatellites], 
        [Direction] AS [Direction], 
        [HDOP] AS [HDOP],
        row_number() OVER (PARTITION BY TrackerId ORDER BY IdTrackerPosition DESC) AS rank_row
    FROM (
        SELECT
            IdTrackerPosition,
            [TrackerId] AS [TrackerId], 
            [Position] AS [Position], 
            [Date] AS [Date], 
            [Speed] AS [Speed], 
            [NbSatellites] AS [NbSatellites], 
            [Direction] AS [Direction], 
            [HDOP] AS [HDOP]
            FROM TrackerPositions tp
            WHERE tp.Position IS NOT null 
                AND [TrackerId] IN (9, 2, 24, 41, 10, 7, 1, 17, 16, 20, 3, 40, 13, 14, 18, 11, 12, 5, 6, 4, 8, 23, 19, 15, 42, 22, 39, 21, 33, 37, 38, 34, 26, 28, 36, 25, 29, 43, 27, 30, 35, 31, 32)               
        ) AS A
)  AS Z
WHERE rank_row <= 1

Thanks !

Best Answer

A better index would be (assuming ORDER BY IdTrackerPosition DESC is correct, and the query should not specify ORDER BY [Date] DESC instead):

CREATE UNIQUE INDEX i -- Choose a better name!
ON dbo.TrackerPositions 
    (TrackerId, IdTrackerPosition DESC) 
INCLUDE 
    (Position, [Date], Speed, NbSatellites, Direction, HDOP);

The execution plan should look something like this:

Execution plan

If the Position IS NOT NULL predicate is frequent, or reasonably selective, you could add a computed column to the base table, and include that in the index - assuming the query can be expressed to reference the computed column rather than saying Position IS NOT NULL explicitly:

-- Computed column
ALTER TABLE dbo.TrackerPositions
ADD PositionIsNull 
AS 
    CASE 
        WHEN Position IS NULL THEN CONVERT(bit, 1) 
        ELSE CONVERT(bit, 0) 
    END;

-- Modified index
CREATE UNIQUE INDEX i 
ON dbo.TrackerPositions 
    (TrackerId, PositionIsNull, IdTrackerPosition DESC) 
INCLUDE 
    (Position, [Date], Speed, NbSatellites, Direction, HDOP)
WITH (DROP_EXISTING = ON);

Modified query:

SELECT 
    [Limit1].[IdTrackerPosition] AS [IdTrackerPosition], 
    [Limit1].[TrackerId] AS [TrackerId], 
    [Limit1].[Position] AS [Position], 
    [Limit1].[Date] AS [Date], 
    [Limit1].[Speed] AS [Speed], 
    [Limit1].[NbSatellites] AS [NbSatellites], 
    [Limit1].[Direction] AS [Direction], 
    [Limit1].[HDOP] AS [HDOP]
    FROM   
    (
        SELECT DISTINCT 
            [Extent1].[TrackerId] AS [TrackerId]
            FROM [dbo].[TrackerPositions] AS [Extent1]
            WHERE 
                Extent1.PositionIsNull = CONVERT(bit, 0)
                AND ([Extent1].[TrackerId] IN (1, 48)) 
    ) AS [Distinct1]
    OUTER APPLY  
    (SELECT TOP (1) 
        [Project2].[IdTrackerPosition] AS [IdTrackerPosition], 
        [Project2].[TrackerId] AS [TrackerId], 
        [Project2].[Position] AS [Position], 
        [Project2].[Date] AS [Date], 
        [Project2].[Speed] AS [Speed], 
        [Project2].[NbSatellites] AS [NbSatellites], 
        [Project2].[Direction] AS [Direction], 
        [Project2].[HDOP] AS [HDOP]
        FROM 
        ( 
            SELECT 
                [Extent2].[IdTrackerPosition] AS [IdTrackerPosition], 
                [Extent2].[TrackerId] AS [TrackerId], 
                [Extent2].[Position] AS [Position], 
                [Extent2].[Date] AS [Date], 
                [Extent2].[Speed] AS [Speed], 
                [Extent2].[NbSatellites] AS [NbSatellites], 
                [Extent2].[Direction] AS [Direction], 
                [Extent2].[HDOP] AS [HDOP]
            FROM [dbo].[TrackerPositions] AS [Extent2]
            WHERE 
                Extent2.PositionIsNull = CONVERT(bit, 0)
                AND ([Extent2].[TrackerId] IN (1, 48)) 
                AND ([Distinct1].[TrackerId] = [Extent2].[TrackerId])
        )  AS [Project2]
        ORDER BY 
            [Project2].[IdTrackerPosition] DESC 
    ) AS [Limit1]

Execution plan with computed column:

Plan with computed column

Related Solutions

Mysql – Adding index to large thesql tables

The activity of altering big tables are done in phases:

Create a new table with required fields and indexes say in test DB (just structure)
Dump the data from the existing table and load the same to the newly created table in test DB
Now announce your downtime :)
Swap the tables by renaming - RENAME table ur_db.table_name to test.temp, test.table_name to ur_db.table_name, test.temp to test.table_name; This is an atomic operation taking fraction of a second.
Load the extra records to the newly created table ( the records that came after dumping and then loading). This step can be done before Step: 3 also once to reduce your downtime.
And your system is back

Few notes:

You need not hit information schema directly like this, try using SHOW TABLE STATUS from db like 'table_name'
The speed of alter table is more or less linked with the I/O speed. Last time when we ran a direct alter table(without the above steps), we had 40GB+ table size it took around 4hours. If your 20GB data is taking years, you are working on some outdated machine.
Also drop of the unwanted indexes like index_bslt_ondate, index_base_schedule_line_items_loan_base_schedule as some other indexes has the left most column as the indexed column

Please let me know if you need any clarification on any of these steps.

Edit : A simple python script to automate the process https://github.com/georgecj11/hotswap_mysql_table.git

SQL Server Execution Plan – SHOWPLAN vs ‘Include Execution Plan’ Warnings for the Same Query

This:

SET SHOWPLAN_XML ON;
GO
SELECT * FROM sys.objects;
GO

Is equivalent to pressing Display Estimated Execution Plan on the toolbar (or hitting Ctrl + L). You'll notice that no rows are returned from the query, like there is when you use Include Actual Execution Plan (Ctrl + M).

The spill warning is only a runtime warning. There is no way that SQL Server can know, when displaying the estimated plan, that a spill will happen at runtime. This is because a spill is caused by factors that might only be present during certain invocations of the query (for example, when there is memory pressure). The estimated plan knows roughly how much memory it's going to ask for, but it can't know until execution that it isn't going to get it.

As an aside, may I recommend* our free tool, SQL Sentry Plan Explorer? I think it provides much more obvious information than Management Studio. I recently wrote a lengthy blog post that can act as a tutorial, and Jonathan Kehayias has a great PluralSight course on it as well.

_{* Disclaimer: I work for SQL Sentry.}

Best Answer

Related Solutions

Mysql – Adding index to large thesql tables

SQL Server Execution Plan – SHOWPLAN vs ‘Include Execution Plan’ Warnings for the Same Query

Related Question