Sessions table wackiness


  • Notification Spam Recipient

    @izzion said in The Official Status Thread:

    @tsaukpaetra
    What are the indexes on the AspNetSessions table?

    Create script
    USE [Timefire_db]
    GO
    
    /****** Object:  Table [dbo].[AspNetSessions]    Script Date: 6/29/2017 12:40:24 PM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[AspNetSessions](
    	[SessionID] [bigint] IDENTITY(1,1) NOT NULL,
    	[AspNetUser] [nvarchar](128) NOT NULL,
    	[Token] [varchar](2000) NOT NULL,
    	[Expires] [datetime] NOT NULL,
    	[Meta] [nvarchar](1000) NULL,
     CONSTRAINT [PK_AspNetSessions] PRIMARY KEY NONCLUSTERED 
    (
    	[SessionID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
    )
    
    GO
    
    ALTER TABLE [dbo].[AspNetSessions]  WITH CHECK ADD  CONSTRAINT [FK_AspNetSessions_AspNetUsers] FOREIGN KEY([AspNetUser])
    REFERENCES [dbo].[AspNetUsers] ([Id])
    GO
    
    ALTER TABLE [dbo].[AspNetSessions] CHECK CONSTRAINT [FK_AspNetSessions_AspNetUsers]
    GO
    
    

    Can you pull the actual query plans for these and post to https://www.brentozar.com/pastetheplan/ and provide links (via DM if you prefer)?

    Site doesn't like the XML generated for some reason. I blame Azure.
    Original.sqlplan SimplerSlower.sqlplan

    My guess is that there's a NONCLUSTERED index on token that is selective enough it's being used, and there's either no index on AspNetUser, or the index isn't selective enough for the optimizer to pick it and the plan is doing a full scan of the table in door #blue

    🤞 I'm hoping it's just the fact that expired tokens weren't getting eradicated...



  • @tsaukpaetra
    Hm, yeah, those two plans are identical (doing a full table seek) aside from the TOP limiter, and there aren't any NONCLUSTERED indexes on the table at all, so that's expected behavior.

    Judging by the numbers in the plans you pulled, the table was empty when you pulled the plans :P If anything, I would expect searching by user and token should be much more expensive than searching just by user, since SQL doesn't have to potentially pad out the varchar(2000) token column to match the varchar(8000) input from EF (or do string comparisons on 2000 character strings).

    Edit: Whoops, wrong button. And judging by the "CPU%" on the previous screenshot you posted, I'm not sure that Blue was actually more computationally expensive. I think it may just have much more duration from reading and returning all the extra data (all tokens for the specified user) and waiting for the data to spool to the application over the network. Rather than Yellow's higher CPU usage and much higher execution count for all the separate calls for specific token IDs.


  • Notification Spam Recipient

    @izzion said in Sessions table wackiness:

    doing a full table seek

    Is it? I'm not SQL pro, but you're talking about the clustered index scan on AspNetUsers?

    @izzion said in Sessions table wackiness:

    the table was empty when you pulled the plans

    Yeah, it had 14k records. I think I deleted all the expired stuff, leaving around 1.8k records...

    @izzion said in Sessions table wackiness:

    actually more computationally expensive.

    Problem is, the docs aren't very clear that transferring said data is part of the CPU calculation (IMHO I don't think it should if it is).

    I'm wondering if it would be effective to add in a computed column (with index) to take just the first 20 or so characters of the token (which in experience is more than enough) and search off of that...


  • Notification Spam Recipient

    @tsaukpaetra said in Sessions table wackiness:

    add in a computed column

    Actually, why do that when I can just size down the actual column, I don't actually do anything with the token from that table anyways, so it's superfluous whether it's there or not...



  • @tsaukpaetra
    "Clustered index" is SQL speak for the entire table.

    Depending on how wide the table is (how few rows fit into 8KB of space), a nonclustered index "on" (ordered by) the columns in your WHERE clause can help, because SQL will look through the index to figure out which rows it needs, and then pull each specific row out of the clustered index to get the rest of the data.

    You can also include additional columns in an index without making them part of the sort key, but that is less helpful here than in other cases, because your query is selecting 100% of the data in each row. So to include all of the columns in the index, you would be doubling the size of the table on disk and more than doubling the amount of work to write new rows to the table (because of the overhead of the second sort order for the nonclustered index).


  • Notification Spam Recipient

    @izzion said in Sessions table wackiness:

    wide the table is

    Well, I resized the token column so now I think the rows should be max ~1300 bytes but more typically 200 bytes.
    I also made the primary key the clustered index because WTF how did that get misconfigured.

    Also learned that the reason so many queries were being done is because literally every surface in the level uses these uploaded images, and that can amount to about 100 requests (per person!) to the endpoint, every time they join. :facepalm:

    Seems to be better now?



  • @tsaukpaetra
    Ah, the first rule of SQL performance tuning... Is This Even Needed?



  • @izzion
    If you're getting 100 separate requests for joining a level, can that be batched into a single request to return the entire set of surfaces? Or at least batched into chunks as groups of surfaces come into rendering range?

    So that you don't have to hit the token table and other supporting tables as often?


  • Notification Spam Recipient

    @izzion said in Sessions table wackiness:

    @tsaukpaetra
    Ah, the first rule of SQL performance tuning... Is This Even Needed?

    0_1498783856563_9549ddb7-1574-47bf-9b52-63345e39f0e2-image.png

    You can't die in this game. But, we track it, nonetheless...


  • Notification Spam Recipient

    @izzion said in Sessions table wackiness:

    @izzion
    If you're getting 100 separate requests for joining a level, can that be batched into a single request to return the entire set of surfaces? Or at least batched into chunks as groups of surfaces come into rendering range?

    So that you don't have to hit the token table and other supporting tables as often?

    The way it was written, not really. Essentially, each object that uses this is the originator of the request, it's all highly dynamic, so we can't necessarily predict the names (necessarily).

    Will put it on the trello though, because even though the files themselves are like 50k each, I don't know if you can queue up a batch of them from Azure's blob service...