Odd performance query behavior on very large 40 TB partitioned table in distributed view, maybe it's tipping?

  • First a bit of background. I recently started a job with a somewhat complicated setup for a 50 TB database. There is a 3 node failover cluster with two named SQL Server 2012 Enterprise instances with a linked server established from the first to the second instance. There are three very large tables in this database, and one is super-extra-large, and accounts for 40 of those TB. Of course I'm having issues with that very same large table, for "Probe" data.

    The data has been sharded by year and month into separate databases, i.e. 2013JUL, 2013JUN, 2013MAY... 2012JAN and there is a main database MAINDB where the large tables have been removed, and instead a distributed view created which does a UNION ALL across the individual database Probe tables, including those on the linked server (data older than 2012 is on this second instance). This approach was taken in order to be as transparent as possible to the leading application. There is a check constraint on each individual probe table to ensure the Probe view in MAINDB allows inserts.

    The Probe table is also partitioned in weeks on the clustering key. The clustering key is a datetime2 column named "reportStamp" in order to support date range based queries, and the primary key is a composite with reportStamp and a bigint Id column.

    There is a single non-clustered index on ProbeData table with the following definition:

    CREATE NONCLUSTERED INDEX [IX1_tblDWProbe_vehId_repSt_paramId_paramValue] ON [dbo].[tblDWProbe]

    (

    [vehicleId] ASC,

    [reportStamp] ASC,

    [parameterId] ASC,

    [paramValue] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)

    GO

    We are having performance issues on a query that is returning the most recent probe parameter value (column paramValue) within the last 7 days. To date the query has been invoked ad hoc from an SSIS package, and my goal is to wrap this in a stored proc.

    We should know vehicleId, parameterId, and a reportStamp range, and should be able to fully leverage this index as we are attempting to return paramValue.

    What we are seeing is sometimes very good performance and takes less than a minute, and other times it takes many minutes, and I have been told as much as a day. The few times I have "caught it in the act" I see the logical and physical read counts in the several millions, so it sorta feels like this query is tipping. I believe the above index should be covering, and my understanding is a covering non-clustered index won't tip. Maybe there is a nuance where paramValue needs to be an included column and is being ignored even though it's a key column? Maybe a parameter sniffing issue?

    Here the query pulled out into a simple testing harness.

    USE TEST_MAINDB

    GO

    DECLARE @BeginDate DATETIME2;

    DECLARE @EndDate DATETIME2;

    DECLARE @VehicleId INT;

    DECLARE @ParameterId INT;

    SET @BeginDate = DATEADD(d, -1, CURRENT_TIMESTAMP);

    SET @EndDate = CURRENT_TIMESTAMP;

    SET @VehicleId = 9940;

    SET @ParameterId = 40024;

    SELECT TOP 1

    COALESCE( pr.[reportStamp], '-') AS [reportStamp]

    ,COALESCE( p.[parameterDesc], '-') AS [parameterDesc]

    ,COALESCE( pr.[paramValue], 999999) AS [paramValue]

    FROM [dbo].[tblDWProbe] pr WITH (NOLOCK, FORCESEEK)

    INNER JOIN [dbo].[tblParameters] p ON p.[parameterId] = pr.[parameterId]

    WHERE pr.[vehicleId] = @VehicleId

    AND pr.[reportStamp] BETWEEN @BeginDate AND @EndDate

    AND pr.[parameterId] = @ParameterId

    ORDER BY pr.[reportStamp] DESC

    OPTION (RECOMPILE)

    There is a great deal of contention from the leading application always doing inserts, thus the NOLOCK hint. We don't have the disk iron for me to be comfortable enabling snapshot isolation. In my limited testing I have observed too much TempDB contention.

    I have tried adding OPTION (RECOMPILE) and observe two very different query plans. Also resorted to the FORCESEEK hint. I've tried to attach the XML to this post. Plan1.sqlpan is very complex, and Plan2.sqlplan appears relative simple.

    As stated before the requirement is to go back 7 days, and I have tried in stages to start with 1 day, then 2, then 3 and observe performance and plans.

    Sometimes I also get a missing index very similar to the above existing index, but with different key order and paramValue is an included column.

    CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[tblDWProbe]

    (

    [parameterId],

    [vehicleId],

    [reportStamp])

    INCLUDE ([paramValue])

    If the optimizer is not leveraging the non-clustered index, that would explain the disparity in performance. I haven't yet spun up profiler or gotten brave enough with extended events yet to see if this is the case. I think the approach before the Probe table was sharded and the distributed view came into the picture was to use explicit INDEX query hints, which has been now been negated by the view.

    Any insights or pointers into how I should troubleshoot this critter are greatly appreciated!

    Thanks!

  • I would LOVE to help you with this, but it goes WAY beyond the primary purpose of a forum post - that being short, targeted, straight-forward assistance. What you have isn't any of those.

    My best advice is to get a really sharp performance tuning professional on board for a review. There are some good ones on this forum and elsewhere. Perhaps someone else will decide to jump in on their free time and try to help you out with this one. Best of luck!

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • I appreciate the response, and yes I don't expect someone to volunteer for a remote DBA session and solve my problem for free. I just tried to flesh out a full background context and asked for some general pointers on approaches to take to solve this problem.

    I was hoping for insights on some general approaches like "I would watch DMV 'abc' or you really need to boot up a profiler trace and watch 'xyz'." I know I've seen helpful general hints like this on this forum before. I'm trying to understand reasons why the optimizer will choose a completely different plan or disregard an index. Yes I know the stock answer is always "it depends", but that list of things it depends on is not infinite. Isn't the point of this forum to encourage learning and foster understanding?

  • UPDATE: For anyone that may encounter a similar issue.

    I've dug a bit deeper, and I think what is happening is that sometimes the generated query plan undergoes partition elimination at compile time versus at runtime. When at runtime, I have verified that the number of executions is zero for tables with check constraints outside my range.

    I still don't quite understand why the non-clustered index is sometimes ignored when it should be covering, but I have had the most luck with the FORCESEEK query hint.

  • jallmond (7/16/2013)


    UPDATE: For anyone that may encounter a similar issue.

    I've dug a bit deeper, and I think what is happening is that sometimes the generated query plan undergoes partition elimination at compile time versus at runtime. When at runtime, I have verified that the number of executions is zero for tables with check constraints outside my range.

    I still don't quite understand why the non-clustered index is sometimes ignored when it should be covering, but I have had the most luck with the FORCESEEK query hint.

    Thanks for posting the follow-up. Lots of posters don't realize that this makes the forum MUCH more valuable as people searching can find problems AND solutions!

    I think another of the root causes is that DPVs just never got any real loving from the product team and haven't had much attention in many years I don't think.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • I don't know that this will help much, but, I'd look at the properties on the SELECT operator. Is the optimizer completing optimization or is it timing out? If the latter, you may just be seeing plan instability. In that case, I think you're already on the right track with a few query hints to move the optimizer in the right direction. If the former... I'm not sure what's going on.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • The optimizer is completing with FULL optimization, and the plan hashes match on subsequent runs.

    I think the FORCESEEK hint is the what got me over the hump and is of the most benefit in this case.

    For some reason I see inconsistent behavior with regard to whether it chooses to use the non-clustered index when using a simple INNER JOIN. With CROSS APPLY it seems always use the index. Weird.

  • TheSQLGuru (7/16/2013)


    I think another of the root causes is that DPVs just never got any real loving from the product team and haven't had much attention in many years I don't think.

    Surely the lack of real love for DPVs has nothing to do with wanting to sell more Enterprise licenses for table partitioning. 😉

  • jallmond (7/17/2013)


    TheSQLGuru (7/16/2013)


    I think another of the root causes is that DPVs just never got any real loving from the product team and haven't had much attention in many years I don't think.

    Surely the lack of real love for DPVs has nothing to do with wanting to sell more Enterprise licenses for table partitioning. 😉

    DING-DING-DING!!! We have a winner!! :Whistling:

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • In difference to Kevin, I think that that there is some hope that we may be able to help you in this forum, and save you the cost from hiring a consultant. But no promises, we'll see.

    In any case, the difference between the two plans is that in the good plan, the optimizer did partition elimination just fine, and only queried the database for July. In the bad plan, it queries all tables in the partitioned view.

    So why would the optimizer do this? I have some theories, but I first need to see the view definition, and the definition of all tables, including their indexes, in the view. (Please post the table scripts as an attachment.)

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Hey Erland!! What are you doing here - are you lost? Welcome to SSC.com! 😀

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Scripts attached. Did you want ALL indexes for EACH monthly shard as well? They should all be identical. I have only included JUL2013 indexes, but here are all table definitions and the view. I have also attached the calling stored procedure I came up with.

    Please let me know if I have omitted anything important.

    I greatly appreciate any insights!

  • TheSQLGuru (7/18/2013)


    Hey Erland!! What are you doing here - are you lost?

    Yes, I lost an NNTP bridge to the MSDN forums. I figured that if I have to answer questions from a web UI, I might as well do it here.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • To be able to troubleshoot something, I need full information, yes. Here I was wondering whether the only the JUL2013 was indexed - that certainly asks for trouble. And I would have appreciated to get everything in one script.

    Anyway, I looked closer at the complex plan, and this is a good plan. If you look at the Filter operators in each branch, they have a startup expression, which means that the shard in question is only visited if the date falls within the interval for the shard.

    I don't understand the indexing. Why is there a separate clustered index on reportStamp, and then a NC primary key on reportStamp and Id? Why not just make the PK clustered?

    Nothing good can come out of these hints. NOLOCK is a hint which is very difficult to use and which can lead to all sorts of nasty surprises. And you should not need it for a query that reads a single row. And FORCESEEK is also pointless here. There is a index all set up to use.

    So why is this query slow some times? We would need to see the plan from such an occasion. If someone fiddles with the constraints so that they are not trusted anymore, the startup expression will disappear. However, it is not impossible that the inappropriate definition of the index is the culprit. It should really be:

    CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[tblDWProbe]

    (

    [parameterId],

    [vehicleId],

    [reportStamp])

    INCLUDE ([paramValue])

    Since there is equality conditions on parameterId and vehicleId, they should come before reportStamp. Thus, I would recommend that you change the index on all shards.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Erland Sommarskog (7/19/2013)


    And I would have appreciated to get everything in one script.

    Apologies, I gathered these around midnight local time, and this simple idea of a single file completely escaped me. My bad.

    Erland Sommarskog (7/19/2013)


    I don't understand the indexing. Why is there a separate clustered index on reportStamp, and then a NC primary key on reportStamp and Id? Why not just make the PK clustered?

    I don't either! 🙂 I've been here about a month, and its one of those situations where the team has turned over a few times and documentation is non-existent. I created the July shard, but at the time I didn't yet know enough to feel comfortable making any changes, so I created July as a simple clone of June. I will create the clustered index as you suggest for the August shard.

    Given we do a lot of date range based queries, is it more optimal to create a clustered primary key as:

    ALTER TABLE [dbo].[tblDWProbe] ADD CONSTRAINT [PK_tblDWProbe] PRIMARY KEY CLUSTERED

    (

    [reportStamp] ASC,

    [dwProbeId] ASC

    )

    Or

    ALTER TABLE [dbo].[tblDWProbe] ADD CONSTRAINT [PK_tblDWProbe] PRIMARY KEY CLUSTERED

    (

    [dwProbeId] ASC,

    [reportStamp] ASC

    )

    Or does the key column order not really matter when used in scans?

    Erland Sommarskog (7/19/2013)


    Nothing good can come out of these hints. NOLOCK is a hint which is very difficult to use and which can lead to all sorts of nasty surprises. And you should not need it for a query that reads a single row. And FORCESEEK is also pointless here. There is a index all set up to use.

    The leading application is always parsing binary files and inserting into this table. I have been told that SELECT with READ COMMITTED blocks the inserts, but have not verified the real impact. It is on my list of things to do to enable READ COMMITTED SNAPSHOT, but the disk subsystem TempDB and its log resides on will need to be shored up before I can enable.

    Erland Sommarskog (7/19/2013)


    Since there is equality conditions on parameterId and vehicleId, they should come before reportStamp. Thus, I would recommend that you change the index on all shards.

    This is an indexing nuance I am not familiar with. So I understand correctly, are you saying for composite indexes, key columns used in equality comparisons are more efficient if they are listed first?

    For our business use case we sometimes query for vehicleId within a date range (usually last 7 days) and never a specific date, without also knowing the parameterId.

    So I will probably create something like:

    CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[tblDWProbe]

    (

    [vehicleId],

    [reportStamp],

    [parameterId]

    )

    INCLUDE ([paramValue]) ON PSchemeName([reportStamp])

    Am I missing out if I do this?

    Also, do I need to explicitly declare the partition scheme when creating this index if the table is already partitioned? Finding conflicting advice online.

    Thanks again!

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply