August 15, 2024 at 10:01 am
I work on a multi terabyte warehouse database application which typically ingests between 130 and 180 million rows of data per day and processes into a data mart for them for consumption into a Tableau report. This is an on prem database using a virtual machine with 18 cores and 300 GB allocated to SQL Server. The storage is on a shared SAN. We use SSIS to import data from text files into staging tables in the database with virtually no transformation at that point and all the processing from them on it in stored procedures.
Most of the time, the application just about keeps it head above water taking 20 - 22 hours to process the data. However, when the number of records increases due to increase in activity in up stream systems or we cannot process for whatever reason (remember CrowdStrike?) we have a problem is clearing the backlog.
I can see that our CPU usage is typically between 60 - 80 % so we believe that we are not constrained by CPU. We rarely have RESOURCE_SEMAPHORE waits so we are not constrained by memory. We suspect that we are limited by disk operations.
The application has grown organically over the years and is processing 10x the volume when we started. We know that there are lots of places where we can make improvements. The question we don't have an answer to is how to do assess the impact of proposed change to prioritize the changes that will give us the biggest bang for our buck.
From the query store we can get metrics of CPU, memory and IO for the code that is already deployed and we can identify CPU and IO heavy statements. We have looked at that and could come up with dozens of ideas for improvements ranging in development effort from 1 day to 1 month. The question we cannot answer is how do we quantify the benefits in advance so that we can prioritize changes with the very limited development resources we have available.
We might have two different ideas. One which takes 1 day and another which takes 3 days. Option 1 might give a benefit of X but option 2 gives a benefit of 10X. So we should prioritize option 2. But how do we estimate X? This is just looking at IO as this is where we have the biggest problem.
One idea was to count the number of leaf levels in the clustered index and multiply that by the number of records in that SQL statement. The basis for this was that to insert/delete/update/read a record, the SQL engine must navigate down the leaf levels of the index each of which is a read (logical if in cache or physical if not). We could calculate the IO for the existing process and a number for option 1 and option 2 and that would give us a quantifiable benefit to help with the prioritization of changes. When we did this, the number for the existing process was way over (3 -5 times) the IO metrics from the query store.
Is this a valid approach and are we missing something obvious in our calculation to get approximately to the the number in the query store?
What approach do people use for assessing the benefits of changes? And how well do they work?
August 15, 2024 at 11:02 am
Personally I approach these type of changes by first identifying which executions take the longest, then which queries within that take the longest looking through their ACTUAL explain plan and see what its doing - most of the cases I fix by splitting the existing queries into sub queries with temp tables where this can result in a significant benefit (less IO and CPU) to the overall execution of that block of code.
things to look for are operations within the plan that have a huge read row count, followed by filtering that drastically reduces the volume - and this can sometimes be higher up on the plan chain.
just as an example of this on my shop on a third party system we have a query (which we can't change unfortunately as it is generated automatically) that joins 5 tables - with a filter that can only apply after all 5 tables have been read.
T1 activities - master table 44million rows
t2 to t5 entities - sub tables all linking to master on different columns -
query is basically trying to retrieve all rows from T1 that have a link to one of the entities where a column from the entities match a filter (user entered)
Explain plan flow is
if we were able to change the above we would be doing the joins to each table (t1 + tx) individually, union of results and this would mean that the indexes in each individual table (t2 to t5) could be used and the access to T1 would then be just a few rows for each table - reducing runtime to a few milliseconds and memory requirements from 8GB to just a few MB/KB.
this mostly occurs on SQL statements with multiple joins and lots of conditions - simple 2 table joins normally don't suffer from this if correct indexes are in-place - which is another aspect to look for (lack of correct indexes)
so going back to your idea of using the IO metrics from QueryStore - I don't believe that will be the right approach as higher IO may be on a query that you can't change due to its nature - and lower numbers, while still high in a way, may be where you will get the fastest improvement.
the queries I tend to look for first are the ones with highest Memory Grant requirements - or those with lower ones but with high number of executions and/or longer duration.
August 15, 2024 at 1:19 pm
Thanks for the reply.
Our highest resource use query is this:
Delete a
FROM Big.Table a
JOIN #SmallTable b
on b.<<columns>> = a.<<clustered index columns>>
WHERE a.Date = @Date
Big.Table is 3 billion rows partitioned on Date with 20 - 40 million rows per partition (the next step in the process aggregates by date which optimizes that step) and has a partition aligned clustered index by the columns in #SmallTable
#SmallTable is 500k rows of incoming records.
The problem is that a record for processing can have many changes to it so we receive it many times over the course of several days or weeks and that the date of the record can change (this is a very simplified explanation of what is going on but this is the business requirement). We check the last 2 months of daily partitions and delete the record if it is in any of those partitions and then insert the transaction into the new partition (code not shown).
There isn't much that we can tune on this particular query. However, we have at least 3 ideas of how we might reduce the total compute cost - but they are basically throw away the 20 steps we currently have and replace them with 15 - 30 completely different steps. They are re-engineering the process not tuning a single query. Let's say Option 1 will take ~1 week to code; Option 2 will take ~2 weeks; Option 3 will take ~4 weeks. And all the options are mutually incompatible so we cannot do option 1 and leverage in either of the other options.
There are lots of other questions to be answered about urgency, resource availability, risk of failure etc. But we still need to know what benefits we might get from each option to feed into the discussion of which option to choose. We cannot afford to spend a week trying something out only to find it gives only a small benefit. There is another process which only consumes 50% of the resources compared with the example above. Changes to that process might reduce consumption by 80% (a guess) and might only take 2 days to make the change whereas option 1 might reduce by 25% (another guess) and take a week. 80% of 50 is a bigger saving than 25% of 100.
How can we take the guesswork out?
August 15, 2024 at 1:46 pm
as that table is partitioned, and depending on the volume you are deleting, it may be faster to switch out the partition of that date to a staging table, and then insert back the rows to delete - this will obviously depend on the volume being deleted.
but inserts are a lot faster than deletes so its worthwhile trying it out - and it is a fast and small code change.
and from my own experience guesswork won't go away - you may think a particular change will work based on lots of metrics only to find out that while the change you did does improve that particular step, it may have just moved some of the time/resources of that step to a prior step - thus making it a moot point to change.
pity about your time constraints - this is the type of things I thrive on and it can take a fair amount of time to get right and, even though my experience/gut feeling normally drives these changes pretty fast, I understand that for others with less hands on this type of issues it will take a bit longer and more trial and test approach.
August 15, 2024 at 3:33 pm
(1) more RAM (if you have only 300GB for SQL Server)
(2) do you (page) compress the data?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 15, 2024 at 4:08 pm
The infrastructure and database is provided "as a service". There is only one level up from where we are with 500 GB and we could, potentially, move to that. However, that's a lot of time and effort to prove that the new servers provide any benefit (in DEV and UAT we are limited to 128GB) and we are looking for something sooner. From what I understand, more memory means that the data pages will reside in memory longer reducing the chance that they are flushed from the cache when other data needs to be brought in - it wont really change the number of IO requests (some will change from physical to logical but the queries are still the same).
Yes we have page compression on the tables.
August 15, 2024 at 8:09 pm
how much memory does the SERVER have in total and how much is dedicated to SQL Server? If I am reading your question correctly, you have 300 GB RAM on the server an 300 GB RAM for SQL Server and you are using SSIS to pull the data in and then stored procedures to manipulate the data. IF I read that right, you have 0 GB for SSIS which is likely why it is so slow.
SSIS memory operates separately from SQL Server as they are different applications. If SSIS has no memory, the SSIS operations will be paging to disk which will be incredibly slow. If money allows, I recommend having SSIS on it's own box (SSIS requires a SQL Server license on the server it is installed on). Otherwise, you will need to drop your SQL max memory to give SSIS some room to work.
The reason I know this - I hit this problem with my servers. I had an SSIS package that sometimes completes in 2-10 minutes (depending on the data load), but if the servers hadn't been rebooted in a while, the SQL instances (yes, plural... not the best setup, but it works for us) on that server would eat up all the memory and the SSIS could run for hours (10+ hours). Solution was to drop the max memory on our lower usage SQL instances to free up 20 GB of memory for the SSIS package to run and we've yet to see it take 10+ hours again. Downside is less memory for SQL Server queries, but we've had it set up that way for a few years now and nobody has complained, so we think things are working well...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 15, 2024 at 9:40 pm
The database server is 384 GB. SSIS is on a separate server.
August 19, 2024 at 3:06 am
Please post the Actual Execution plan for the DELETE of just ONE row. Attach the ACTUAL execution plan so we can take a look at it. A graphic of the execution plan won't do it for us.
If my hunch is right, it won't take much to fix your problem AND other things may run faster as well.
My "hunch" is based on a fix that I made once 10 years ago and on using that same fix on another table just 2 months ago.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 19, 2024 at 7:56 am
This is the query with the plan (names have been changed):
DECLARE @DateColumn DATE = '2024-07-01';
DELETE s
FROM [Schema].TableName s
INNER JOIN #WorkingTable q
ON q.Column1 = s.Column1
AND q.Column2 = s.Column2
WHERE s.DateColumn = @DateColumn
StmtText
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Table Delete(OBJECT:([DBName].[Schema].[TableName] AS), OBJECT:([DBName].[Schema].[TableName].[UNQ_Schema_TableName_Column1_Key_Column2_Key_DateColumn] AS))
|--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))
|--Nested Loops(Inner Join, OUTER REFERENCES:(.[Column1_Key],.[Column2_Key]))
|--Table Scan(OBJECT:([tempdb].[dbo].[#WorkingTable] AS))
|--Index Seek(OBJECT:([DBName].[Schema].[TableName].[UNQ_Schema_TableName_Column1_Key_Column2_Key_DateColumn] AS), SEEK:([PtnId1001]=RangePartitionNew([@DateColumn],(1),'2020-01-01','2020-01-02',......
Schema.TableName is partitioned by date. There are typically 30 million rows per date partition.
#WorkingTable contains 500k records. We receive a continual set of records throughout the day (60 million or more) and run batches of 500k.
We know that this step consumes far more resources than anything else (11% CPU, 29% logical IO, 15% physical IO). We believe that the way in which we process data from start to finish is flawed and can be improved. This is just one step of many in that process.
We have several ideas for how the start to finish process could be re-engineered. What we don't know, and this is the original question, is how to do we estimate which would give us the biggest benefit? We don't want to spend 2 weeks or more developing something which doesn't give us much benefit.
Thanks
August 19, 2024 at 4:28 pm
As a thought (I could be completely out in left field here), but my GUESS is that your DELETE is because you are removing the data before re-inserting it based on the values from your working table.
IF that is correct, could you change the process to be an UPDATE instead of a DELETE then INSERT?
If that is wrong, I wonder if you'd get a performance boost by adding an index to your working table so that it is faster to match things up prior to the delete?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 19, 2024 at 4:47 pm
Yes Brian you are right. We delete before inserting again. Historically, the team found than using a MERGE statement (because sometimes we need to insert a new record so it is not all updates) did not perform very well. That might be true. It might not be true. I don't know.
Given this as an idea, how would be estimate the improvement that would give us over our existing process? This is a relatively simple change and could just build it. However, we have multiple ideas (some of which are non trivial) and cannot afford to build all of them to see which one or ones give us the biggest benefit.
August 19, 2024 at 5:30 pm
you said
Schema.TableName is partitioned by date. There are typically 30 million rows per date partition.
#WorkingTable contains 500k records. We receive a continual set of records throughout the day (60 million or more) and run batches of 500k.
does that means that when you kick off this process you have MORE than 500k rows but you split into this size of batches because performance on this (or other steps on the process) would get too slow? if this is the case what is the TOTAL volume when you do kick off this process - per DATE please.
and when this process is running are there any other processes using this table is is it the sole process using it?
and.. how long does it take if you so select those 30 million rows (single partition) into another table with same definition (same indexes/constraints/fk's) - this to compare to the current delete of 500k rows.
August 20, 2024 at 7:05 am
Frederico,
How would you estimate the I/O cost of your idea? How does that compare with Brian's idea of using a MERGE rather than a DELETE and INSERT?
That's the question I'm really asking.
If you really want to know, the number of deletes could vary between 0 and 500K depending on the time of day. In the morning, incoming rows tend to be new (so no deletes) but later in the day they tend to be updates (so a large number of delete/inserts).
August 20, 2024 at 9:01 am
I know you are after how to estimate X - that is not really a good way to go as there are way to many unknowns, and without looking at the ACTUAL explain plans (graphical ones, not the text you gave us above) you won't find out where the big issue are and where biggest changes can be achieved, potentially with minor code changes.
but updates slower than deletes and both are a lot slower than inserts, specially if inserts can be bulklogged so approaches that benefit inserts with a combination of truncate/table switch are always first in my mind - even if not always possible or beneficial.
regarding merge - for volumes I avoid it - see this article, and read well the articles mentioned at the end of it - https://www.mssqltips.com/sqlservertip/7590/sql-merge-performance-vs-insert-update-delete/
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply