Cost Optimization

These are queries for AWS Services under the AWS Well-Architected Framework Cost Optimization Pillar.

Use the clipboard in the top right of the text boxes below to copy all of the text to be pasted.

CUR Query Library uses placeholder variables, indicated by a dollar sign and curly braces (${ }). ${table_name} and ${date_filter} are common placeholder variables used throughout CUR Query Library, which must be replaced before a query will run. For example, if your CUR table is called cur_table and is in a database called cur_db, you would replace ${table_name} with cur_db.cur_table. For ${date_filter}, you have multiple options. See Filtering by Date in the CUR Query Library Help section for additional details.

Prior to deleting resources, check with the application owner that your analysis is correct and the resources are no longer in use.

Table of Contents

Elastic Load Balancing - Idle ELB

Cost Optimization Technique

This query will display cost and usage of Elastic Load Balancers which didn’t receive any traffic last month and ran for more than 336 hours (14 days). Resources returned by this query could be considered for deletion. AWS Trusted Advisor provides a check for idle load balancers but only covers Classic Load Balancers. This query will provide all Elastic Load Balancer types including Application Load Balancer, Network Load Balancer, and Classic Load Balancer.

The assumption is that if the Load Balancer has not received any traffic within 14 days, it is likely orphaned and can be deleted.

Copy Query

Click here - to expand the query

Please refer to the ELB AWS CLI documentation for deletion instructions. The commands vary between the ELB types.

Back to Table of Contents

EC2 Unallocated Elastic IPs

Cost Optimization Technique

This query will return cost for unallocated Elastic IPs. Elastic IPs incur hourly charges when they are not allocated to a Network Load Balancer, NAT gateway or an EC2 instance (or when there are multiple Elastic IPs allocated to the same EC2 instance). The usage amount (in hours) and cost are summed and returned in descending order, along with the associated Account ID and Region.

Pricing

Please refer to the EC2 Elastic IP pricing page.

Download SQL File

Copy the query below or click to Download SQL File

SELECT
  line_item_usage_account_id,
  line_item_usage_type,
  product_location,
  line_item_line_item_description,
  SUM(line_item_usage_amount) AS sum_line_item_usage_amount,
  SUM(line_item_unblended_cost) AS sum_line_item_unblended_cost
FROM
  ${table_name}
WHERE
  ${date_filter}
  AND line_item_product_code = 'AmazonEC2'
  AND line_item_usage_type LIKE '%ElasticIP:IdleAddress'
GROUP BY
  line_item_usage_account_id,
  line_item_usage_type,
  product_location,
  line_item_line_item_description
ORDER BY
  sum_line_item_unblended_cost DESC,
  sum_line_item_usage_amount DESC;

Back to Table of Contents

Graviton Usage

Cost Optimization Technique

AWS Graviton processors are designed by AWS to deliver the best price performance ratio for cloud workloads, delivering up to 40% improvement over comparable current gen x86 processors. Due to the improved price performance, many organizations track Graviton usage as a KPI to drive cost savings for their cloud workloads. Graviton-based EC2 instances are available, and many other AWS services such as Amazon Relational Database Service, Amazon ElastiCache, Amazon EMR, and Amazon OpenSearch also support Graviton-based instance types.

This query provides detail on Graviton-based usage. Amortized cost, usage hours, and a count of unique resources are summed. Output is grouped by day, payer account ID, linked account ID, service, instance type, and region. Output is sorted by day (descending) and amortized cost (descending).

Download SQL File

Link to Code

Copy Query

SELECT 
  DATE_TRUNC('day',line_item_usage_start_date) AS day_line_item_usage_start_date,
  bill_payer_account_id,
  line_item_usage_account_id,
  line_item_product_code,
  product_instance_type,
  product_region,
  SUM(CASE
    WHEN line_item_line_item_type = 'SavingsPlanCoveredUsage' THEN savings_plan_savings_plan_effective_cost
    WHEN line_item_line_item_type = 'DiscountedUsage' THEN reservation_effective_cost
    WHEN line_item_line_item_type = 'Usage' THEN line_item_unblended_cost
    ELSE 0 
  END) AS sum_amortized_cost, 
  SUM(line_item_usage_amount) as sum_line_item_usage_amount, 
  COUNT(DISTINCT(line_item_resource_id)) AS count_line_item_resource_id
FROM 
  ${table_name}
WHERE 
  ${date_filter}
  AND REGEXP_LIKE(line_item_usage_type, '.?[a-z]([1-9]|[1-9][0-9]).?.?[g][a-zA-Z]?\.')
  AND line_item_usage_type NOT LIKE '%EBSOptimized%' 
  AND (line_item_line_item_type = 'Usage'
    OR line_item_line_item_type = 'SavingsPlanCoveredUsage'
    OR line_item_line_item_type = 'DiscountedUsage'
  )
GROUP BY
  DATE_TRUNC('day',line_item_usage_start_date),
  bill_payer_account_id,
  line_item_usage_account_id,
  line_item_product_code,
  line_item_usage_type,
  product_instance_type,
  product_region
ORDER BY 
  day_line_item_usage_start_date DESC,
  sum_amortized_cost DESC;

Back to Table of Contents

Lambda Graviton Savings

Cost Optimization Technique

This query will output all Lambda queries and their processor architecture. Lambda functions which are running on X86 may be cost optimized by moving to ARM64 architecture. On average functions using the Arm/Graviton2 architecture, duration charges are 20 percent lower than the current pricing for x86. Thus the query calculates a 20% savings on each X86 Lambda.

Copy Query

Copy the query below or click to Download SQL File

WITH x86_v_arm_spend AS (
SELECT
   line_item_resource_id      AS line_item_resource_id,
   bill_payer_account_id      AS bill_payer_account_id,
   line_item_usage_account_id AS line_item_usage_account_id,
   line_item_line_item_type AS line_item_line_item_type,
   CASE SUBSTR(line_item_usage_type, length(line_item_usage_type)-2)
      WHEN 'ARM' THEN 'arm64'
      ELSE 'x86_64'
   END AS "processor",
   CASE SUBSTR(line_item_usage_type, length(line_item_usage_type)-2)
      WHEN 'ARM' THEN 0
      ELSE line_item_unblended_cost * .2
   END AS "potential_arm_savings",
   SUM(line_item_unblended_cost) AS sum_line_item_unblended_cost
FROM 
${table_name}
WHERE 
   line_item_product_code  = 'AWSLambda'
   AND line_item_operation = 'Invoke'
   AND ( 
      line_item_usage_type    LIKE '%Request%'
      OR line_item_usage_type LIKE '%Lambda-GB-Second%'
   )
   AND line_item_usage_start_date > CURRENT_DATE - INTERVAL '1' MONTH
   AND line_item_line_item_type  IN ('DiscountedUsage', 'Usage', 'SavingsPlanCoveredUsage')
GROUP BY 1,2,3,5,6,4
)
SELECT 
line_item_resource_id,
bill_payer_account_id,
line_item_usage_account_id,
line_item_line_item_type,
processor,
sum(sum_line_item_unblended_cost)           AS sum_line_item_unblended_cost,
sum(potential_arm_savings) AS "potential_arm_savings"
FROM 
x86_v_arm_spend
GROUP BY 2,3,1,5,4

Back to Table of Contents

EC2 Instance Cost by Pricing Model

The latest generation of instances are more performant and cheaper to operate. Identifying which accounts are using pervious generation instances and determining if those instances are running on-demand or covered by a commitment based pricing model (On-Demand, Reserved Instance or Savings Plan) is challenging. This query may be used to group instance usage by account in a given time period and filter by pricing model. It will help customers find old generation instances running on-demand which may be candidates for an upgrade.

Copy Query

Click here - to expand the query

Amazon WorkSpaces - Auto Stop

Cost Optimization Technique

AutoStop Workspaces are cost effective when used for several hours per day. If AutoStop Workspaces run for more than 80 hrs per month it is more cost effective to switch to AlwaysOn mode. This query shows AutoStop Workspaces which ran more that 80 hrs in previous month. If the usage pattern for these Workspaces is the same month over month it’s possible to optimize cost by switching to AlwaysOn mode. For example, Windows PowerPro (8 vCPU, 32GB RAM) bundle in eu-west-1 runs for 400 hrs per month. In AutoStop mode it costs $612/month ($8.00/month + 400 * $1.53/hour) while if used in AlwaysOn mode it would cost $141/month.

Copy Query

Click here - to expand the query

Please refer to the AWS Solution, Amazon WorkSpaces Cost Optimizer. This solution analyzes all of your Amazon WorkSpaces usage data and automatically converts the WorkSpace to the most cost-effective billing option (hourly or monthly), depending on your individual usage. This solution also helps you monitor your WorkSpace usage and optimize costs. This automates the manual process of running the above query and adjusting your WorkSpaces configuration.

Back to Table of Contents

NAT Gateway - Idle NATGW

Cost Optimization Technique

This query shows cost and usage of NAT Gateways which didn’t receive any traffic last month and ran for more than 336 hrs. Resources returned by this query could be considered for deletion.

Besides deleting idle NATGWs you should also consider the following tips:

  • Determine What Types of Data Transfers Occur the Most - Deploy the CUDOS dashboard to help visualize top talkers
  • Eliminate Costly Cross Availability Zone Transfer Charges - create new NAT Gateways in the same availability zone as your instances
  • Consider Sending Amazon S3 and Dynamo Traffic Through Gateway VPC Endpoints Instead of NAT Gateways
  • Consider Setting up Interface VPC Endpoints Instead of NAT Gateways for Other Intra-AWS Traffic

Copy Query

Click here - to expand the query

Data Transfer Costs Explained

Back to Table of Contents

Amazon EBS Volumes Modernize gp2 to gp3

Cost Optimization Technique

This query will display cost and usage of general purpose Elastic Block Storage Volumes and provide the estimated cost savings for modernizing a gp2 volume to gp3 These resources returned by this query could be considered for upgrade to gp3 as with up to 20% cost savings, gp3 volumes help you achieve more control over your provisioned IOPS, giving the ability to provision storage with your unique applications in mind. This query assumes you would provision the max iops and throughput based on the volume size, but not all resources will require the max amount and should be validated by the resource owner.

If you are running this for all accounts in a large organization we recommend running the query below first to confirm export size is not over ~1M rows. If the count shown in the query is greater than 1M you will want to filter to groupings of accounts or feed this query into a BI tool such as QuickSight

Click here - to expand the query

Copy Query

Click here - to expand the query

Back to Table of Contents

Cost Optimization Technique

This query looks across your EC2 EBS Snapshots to identify all snapshots that still exist today with their previous month spend. It then provides the start date which is the first billing period the snapshot appeared in your CUR and groups them so you can see if they are over 1yr old. Snapshots over 1yr old should be tagged to keep, cleaned up, or archived.

Copy Query

Copy the query below or click to Download SQL File

Click here - to expand the query

Back to Table of Contents

Cost Optimization Technique

This query breaks out the previous month’s costs and usage of each S3 bucket by storage class and includes and separates out identifiers that can be used to identify trends or potential areas to look into for optimization across Lifecycle Policies or Intelligent Tiering. The query uses this information to provide a variety of checks for each S3 bucket including:

Click here - to see Bucket Trend Checks

Copy Query

Click here - to expand the query

Back to Table of Contents

CUR queries are provided as is. We recommend validating your data by comparing it against your monthly bill and Cost Explorer prior to making any financial decisions. If you wish to provide feedback on these queries, there is an error, or you want to make a suggestion, please email: curquery@amazon.com