Showing posts with label Performance. Show all posts
Showing posts with label Performance. Show all posts

Wednesday, September 2, 2026

Keeping Redshift System Table History Past 7 Days with S3 Tables

Redshift keeps its SYS_* monitoring views for seven days in cluster, which is fine until an auditor or an incident review asks for something older. Until now the usual fix was a custom export job to S3 and a Glue crawler to keep it queryable. AWS just added a native system table integration with S3 Tables that skips that pipeline entirely: Redshift can write system table data straight into Amazon S3 Tables, in Apache Iceberg format, with no ETL to maintain.

What it runs on

This applies to Redshift Provisioned on RA3 and RG instance types, and to Redshift Serverless. Older provisioned instance families are not supported. AWS launched it across a wide set of commercial regions rather than a single preview region, but region coverage is the kind of detail that shifts, so confirm your region against the current availability list before you enable it.

Permissions you need

The principal that enables the feature needs:

  • redshift:EnableLogging for a provisioned cluster, or redshift-serverless:UpdateNamespace for Serverless
  • s3tables:CreateTableBucket, s3tables:PutTableBucketEncryption, and s3tables:PutTableBucketPolicy, to stand up the aws-redshift table bucket

That is the whole permission set. Once the bucket exists, Redshift creates namespaces and tables inside it through a service trust relationship with S3 Tables, so nobody needs standing permission to create namespaces or tables themselves.

1. Decide what you are retaining and how it should be organized

You choose which SYS_* views to publish, from SYS_QUERY_HISTORY and SYS_QUERY_TEXT to SYS_CONNECTION_LOG and SYS_VACUUM_HISTORY, or all of them at once. You also pick a deployment model. Per-warehouse keeps each cluster's data in its own set of tables, which matters if SYS_QUERY_TEXT or SYS_PROCEDURE_MESSAGES could contain sensitive literal values. Consolidated writes every warehouse in the account and Region into shared tables, distinguished by a warehouse_name column, which is better for cross-warehouse observability.

Supported system tables

You can select any of these SYS_* views.

2. Enable delivery on the cluster

For a provisioned cluster, this reuses the existing logging API with a new destination type. The principal running this needs redshift:EnableLogging plus permission to create and configure an S3 table bucket named aws-redshift in the account.

-- publish selected system tables to S3 Tables, consolidated across the account
aws redshift enable-logging \
    --cluster-identifier my-redshift-cluster \
    --log-destination-type s3table \
    --log-exports sys_query_history sys_query_text sys_connection_log \
    --s3-table-granularity account
 
-- Redshift Serverless uses update-namespace instead
aws redshift-serverless update-namespace \
    --namespace-name my-namespace \
    --log-destination-type s3table \
    --s3-table-action Enable \
    --s3-table-names all \
    --s3-table-granularity namespace
  

3. Confirm data is actually flowing

Delivery runs in batches at a fixed frequency and only includes completed activity, so a query still running will not show up until it finishes. Check the last ingestion time per view with describe-logging-status on a provisioned cluster or get-namespace on Serverless.

aws redshift describe-logging-status \
    --cluster-identifier my-redshift-cluster
  

4. Register the table bucket with Glue Data Catalog

Querying the retained data from Redshift, Athena, or any other Iceberg-compatible engine requires the aws-redshift S3 table bucket to be integrated with AWS Glue Data Catalog first. This is a one-time step per account and Region, not something you repeat per cluster.

5. Query the history from Redshift

Once the catalog integration is in place, point an external schema at it and query the historical view like any other table. The rows carry warehouse_name, warehouse_namespace_arn, and s3_tables_ingestion_time alongside the original SYS_QUERY_HISTORY columns, which is what lets a consolidated deployment separate one cluster's activity from another's.

-- replace <glue_database> with the database created by the S3 Tables / Glue integration
CREATE EXTERNAL SCHEMA redshift_history
FROM DATA CATALOG
DATABASE '<glue_database>'
IAM_ROLE 'arn:aws:iam::111122223333:role/RedshiftHistoryReadRole';
 
-- queries older than the 7-day in-cluster window
SELECT warehouse_name, query_id, start_time, elapsed_time
FROM redshift_history.sys_query_history
WHERE start_time < dateadd(day, -7, getdate())
ORDER BY elapsed_time DESC
LIMIT 20;
  

What it costs

Writing the data out of Redshift into S3 Tables is free. What you pay for is standard S3 Tables storage and maintenance, meaning compaction and snapshot upkeep, on whatever you retain, plus normal usage pricing for the engine you query it with, whether that is Redshift Spectrum, Athena, or something else. Since data without an expiration policy is kept forever, an unset retention policy is really a storage cost decision, not just a compliance one.

Gotchas worth knowing before you enable this

  • Disabling and re-enabling, or switching between per-warehouse and consolidated, never backfills. Whatever happened during the gap is gone for good.
  • Delivered rows are immutable. You cannot update or delete individual rows through Redshift, only through S3 Tables record expiration.
  • Dropping the S3 Tables permanently deletes everything retained in them and Redshift does not recreate them automatically. Re-enabling starts a fresh table with no history.
  • A customer managed KMS key has to be set the first time you enable the feature. Changing it later means dropping the tables, and the retained data, and starting over.
  • The views marked with an asterisk above need patch P203 or later. On an older patch the tables get created but stay empty.
  • Everything is scoped to a single account and a single region. Cross-account or cross-region analysis means combining results at query time, not a single unified table.
  • Only completed activity is delivered. A query that is still running will not show up until it finishes, aborts, or is canceled.

Before you run this in production

Set a record expiration policy directly in S3 Tables once delivery is running, since without one the data is kept indefinitely and keeps accruing storage cost. Start with the per-warehouse model if SYS_QUERY_TEXT or SYS_PROCEDURE_MESSAGES might carry sensitive literal values, and only move to consolidated once you have confirmed what those views actually capture in your environment. 


If you are pulling SYS_QUERY_EXPLAIN history into this pipeline for performance work, PlanTrace is a free tool for turning those query plans into something you can actually read.

Friday, June 19, 2026

Five Ways Redshift Serverless Quietly Eats Your Budget

It is Friday, the queries are running, and nobody is watching the bill. That is the whole charm of Redshift Serverless: you stop thinking about nodes and resizes. It is also exactly how money slips out the door. Below are five habits that run up RPU charges while you look the other way, all backed by the docs.


1. Running with no maximum RPU-hours limit

By default, Serverless scales to meet load and meters you per second. With no ceiling on total consumption, one rough week of ad-hoc queries can scale well past whatever number you had in your head. Set a daily, weekly, or monthly RPU-hours limit on the workgroup and pick an action when it trips: alert, log, or turn off user queries entirely. See maximum RPU hours usage limit for the setup.

aws redshift-serverless create-usage-limit \
  --resource-arn arn:aws:redshift-serverless:us-east-1:123456789012:workgroup/analytics-wg \
  --usage-type serverless-compute \
  --amount 100 \
  --period weekly \
  --breach-action deactivate
  

2. Leaving idle sessions with open transactions

An open transaction keeps compute alive. If a session sits idle with a transaction still open, RPUs keep getting consumed until the session closes, and Serverless will wait up to six hours before ending it for you. The advice in the billing guide is blunt: close your transactions, and resist the urge to extend SESSION TIMEOUT unless a specific use case demands it.


3. Letting connection pools spam health checks

Here is the sneaky one. Serverless counts every incoming query as billable user activity, and that includes the lightweight keepalive pings your connection pool fires on a schedule. A chatty pool with an aggressive validation interval can keep compute warm during hours when no real work is happening. Check your pool's keepalive and validation settings, because those tiny queries add up.


4. Skipping the MaxRPU scaling cap

The RPU-hours limit from item 1 caps your total spend over a period. MaxRPU is a different lever: it caps how high you can scale at any single moment. Without it, one heavy query is free to grab a large slice of compute all at once. Set MaxRPU to the highest burst level you are actually willing to pay for, and Serverless will scale within that line. The mechanics live in the compute capacity docs.


5. Oversizing the base capacity

Base RPU is the floor that is always ready to serve queries, and the default sits at 128. If your steady-state workload only needs 32, you are paying for headroom you rarely touch. Start the base lower and let autoscaling cover the spikes. You can adjust it anywhere from 8 to 512 in steps of 8, at any time, with no impact on running queries.


Wrapping Up

None of these require heroic tuning. Set an RPU-hours limit and a MaxRPU cap, keep transactions short, audit your pool's keepalive, and right-size the base. The one habit worth building today: configure usage limits from day one rather than after the first surprising invoice arrives.

Monday, June 15, 2026

Killing Explicit Sort Steps in Redshift with the Right SORTKEY

You paste a slow Redshift query plan into PlanTrace and one of the tuning insights reads "SORTKEY optimization candidate", pointing at an explicit sort step that's adding cost you don't need to pay. 

A sort step runs at query time, every time, and on a wide table with millions of rows it can dominate the total cost. 

The fix is usually a SORTKEY that matches what the query is sorting on.


What an explicit sort step actually is

When your query has an ORDER BY, a window function, or a merge join that needs ordered input, Redshift has two options. If the data is already physically stored in the order it needs, it just reads it. If not, it sorts the rows on the fly. 

That on the fly sort is the explicit sort step, and it shows up as an XN Sort operation with its own Sort Key line and a cost range. 

Spotting it in raw EXPLAIN text means scanning indentation and matching cost numbers by eye. This is where reading the plan as a graph helps: PlanTrace renders each node with its cost broken out, so the expensive sort is obvious instead of buried, and the insight names the exact table and column behind it. The underlying mechanics are documented in the AWS guide on reviewing query plan steps.


What causes it?

The root cause is simple: the order the query needs does not match the physical order the table is stored in. A table with no SORTKEY, or with a SORTKEY on columns the query doesn't use, forces Redshift to materialize and sort the result set at runtime. The wider the rows and the larger the row count, the more expensive that becomes. In the graph you'll see the cost concentrated on the sort while the scans underneath look comparatively cheap, which is exactly the pattern the SORTKEY insight keys off of.


How to fix it

Define a SORTKEY that matches the column the query orders or joins on. When the table is already sorted that way, Redshift can skip the runtime sort and read rows in order straight from disk. 

Add the column to the SORTKEY definition with ALTER TABLE, which lets you change sort keys on existing tables without recreating them and without blocking concurrent reads or writes, as noted in the Redshift sort key recommendation announcement.

-- Add a compound sort key matching your ORDER BY / join column
ALTER TABLE lineitem
ALTER COMPOUND SORTKEY (l_quantity);

-- Or let Redshift manage it automatically
ALTER TABLE lineitem ALTER SORTKEY AUTO;
  

After the change, sort the existing data so the new key takes effect, then re-run EXPLAIN:

VACUUM SORT ONLY lineitem;
ANALYZE lineitem;

EXPLAIN
SELECT l_quantity, sum(l_extendedprice)
FROM lineitem
GROUP BY l_quantity
ORDER BY l_quantity;
  

Just note that on very large tables, ALTER table and VACUUM can take significant time to complete.  

Paste the new plan back into PlanTrace and compare it against the old one. 

If the SORTKEY matches, the XN Sort node either drops out of the plan or its cost falls sharply, and the candidate insight no longer fires. That before and after check is the only reliable confirmation. 

A SORTKEY that doesn't line up with the query's order changes nothing. Everything runs client side in your browser, so your plans are never stored or sent anywhere.


Wrapping up

An explicit sort step is Redshift telling you the data isn't stored in the order your query wants. Match the SORTKEY to the ORDER BY or join column, vacuum and analyze, then compare the plans to confirm the sort is gone. Letting PlanTrace surface the candidate and verify the result beats squinting at cost numbers in raw text. And remember that a sort key only helps queries that actually filter, join, or order on those columns, so optimize for the patterns that matter most.


Friday, June 5, 2026

PostgreSQL 18 Finally Makes BUFFERS the Default. Here Is Why That Matters

You run EXPLAIN ANALYZE on a slow query, stare at the plan, and something still feels off. The estimated rows look reasonable, the node timings add up, but you cannot tell whether the database is reading everything from memory or hammering the disk. That missing piece has always been BUFFERS, and for years you had to remember to type it manually. PostgreSQL 18 finally turns it on by default.


1. What BUFFERS actually tells you

The BUFFERS option reports how many 8 kB blocks were satisfied from PostgreSQL's shared buffer cache (hit) versus read from disk (read). It also tracks blocks that were written back to disk during the query (written) and any temporary blocks used by sort or hash operations. Per the official EXPLAIN documentation, the counts are cumulative: a parent node includes the buffer usage of all its children, so the top-level line gives you the whole-query picture at a glance.


2. The old way vs PostgreSQL 18

Before version 18, BUFFERS was off by default and required the parenthesized syntax to combine it with ANALYZE:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM   orders
WHERE  customer_id = 42;

Starting with PostgreSQL 18, EXPLAIN ANALYZE alone is enough, buffer information is included automatically. The PostgreSQL 18 EXPLAIN reference confirms this change: "Buffers information is automatically included when ANALYZE is used."


3. Reading the output

Here is a trimmed example of what you will see under a scan node:

Bitmap Heap Scan on orders
  (actual time=0.012..1.430 rows=980 loops=1)
  Buffers: shared hit=12 read=210

A high read count relative to hit means the data is not in shared buffers and the query is going to disk. The ratio between the two is the fastest signal you have for deciding whether to raise shared_buffers, add caching, or reconsider an index.


4. Spotting temp block usage

Temp blocks appear when a sort or hash operation cannot fit in work_mem and spills to disk. They show up as a separate line in the plan:

Sort  (actual time=45.2..48.9 rows=50000 loops=1)
  Sort Method: external merge  Disk: 7104kB
  Buffers: shared hit=2240 temp read=889 written=889

Any temp read or temp written value above zero is a sign that the operation spilled to disk. Increasing work_mem for that session (or globally if it happens often) is usually the fix. The Using EXPLAIN guide in the PostgreSQL docs walks through how to interpret these lines in context.


Wrapping Up

The BUFFERS option turns EXPLAIN ANALYZE from a timing report into a genuine I/O diagnostic. With PostgreSQL 18 enabling it automatically, there is one fewer thing to remember when chasing down a slow query. If you are still on an older version, make EXPLAIN (ANALYZE, BUFFERS) your default habit. The hit vs read ratio and any temp blocks in the output will often tell you more than the node timings alone.

If reading raw plan text is not your thing, I built PlanTrace exactly for this: a free visualizer that turns EXPLAIN output from PostgreSQL and Redshift into an interactive node graph. Buffer counts, node timings, and scan types all rendered in one color-coded view. Paste your plan and see everything at once, no indentation squinting required.


Monday, June 1, 2026

Data Skew in Data Engineering: What It Is and How to Fix It

You kick off a distributed job expecting it to finish in minutes — but one task keeps running while all others have long since completed. The culprit is almost always data skew. It is one of the most common performance killers in distributed data engineering, yet it is often misdiagnosed or ignored until it starts breaking SLAs.

Data skew happens when data is unevenly distributed across partitions or compute nodes. In systems like Apache Spark, Amazon Redshift, or Azure Synapse, the workload is designed to run in parallel. When one partition holds a disproportionate share of the data — because of a high-frequency key or a poor distribution choice — that one node does all the heavy lifting while the rest sit idle. The job cannot complete until every partition finishes, so your entire pipeline is only as fast as the slowest, most overloaded worker.


1. What causes data skew

The root cause is almost always a skewed join or grouping key. If 60% of your orders belong to a single customer_id, or your event log has millions of rows for one status = 'active' value, Spark or your MPP engine will route all of those rows to the same partition during a shuffle. The result: one executor handles millions of rows while ten others process a few hundred.

In Amazon Redshift, the problem manifests at the storage layer. If your DISTKEY column has low cardinality or a heavily concentrated value distribution, Redshift routes too many rows to the same compute node slice, forcing it to do more work than the others during every query that touches that table.


Types of data skew

Data skew is not a single pattern — it shows up in different shapes depending on the root cause. The four most common types in distributed pipelines:

Key skew — hot partition key

One key (e.g. a single customer_id) floods one partition while all others stay near-empty.

Partition skew — uneven data split

Wide size variation across partitions due to a low-cardinality key like status or country.

Join skew — NULL key explosion

A large share of rows with NULL or a default value all land in the same executor during shuffle.

Temporal skew — time-based spike

Event data clusters at specific timestamps (e.g. end-of-day batch), overloading those time-aligned partitions.

Normal partition Skewed / hot partition Ideal even distribution / spike window

2. Issues data skew causes in your pipelines

The consequences go beyond slow queries. Here are the most damaging effects data skew causes in practice:

  • Pipeline bottlenecks: A single overloaded partition stalls the entire stage. Every downstream task waits on it.
  • Out-of-memory errors: The skewed executor receives far more data than it can fit in memory, causing spills to disk or outright failures.
  • Wasted cluster resources: Most nodes finish quickly and remain idle while one struggles. You are paying for compute that does nothing.
  • Unpredictable SLA breaches: The job duration becomes tied to the size of the hot partition rather than total data volume, making runtime estimates unreliable.
  • Cascading failures in multi-step ETL: A skew-induced timeout in one stage can trigger retries, delay downstream jobs, and corrupt incremental watermarks.

3. Detecting skew before it becomes an issue

In Spark (including Azure Synapse Analytics and Databricks), open the Spark UI and look at the task duration histogram under a shuffle stage. A long tail of one task while all others finish quickly is the signature of skew. You can also profile the key distribution directly before running a heavy join:

# Quick skew check in PySpark
df.groupBy("customer_id")
  .count()
  .orderBy(col("count").desc())
  .show(20)
  

In Redshift, the SVV_TABLE_INFO view exposes a skew_rows column that reports the ratio between the most-populated and least-populated node slices. AWS documentation recommends reviewing the distribution key on any table with a skew_rows value of 4.0 or higher — values below that are generally acceptable natural imbalance. The Identifying tables with data skew guide in the AWS docs walks through how to interpret this output.

-- Check skew_rows ratio in Redshift
SELECT "table", diststyle, skew_rows
FROM   svv_table_info
WHERE  skew_rows >= 4.0
ORDER BY skew_rows DESC;
  

4. Technique: Salting to break hot keys

Salting is the most widely used fix for skewed joins and aggregations. The idea is to append a random integer suffix to the skewed key, artificially spreading one large partition across multiple smaller ones. On the smaller (lookup) side of the join, you replicate each row once per salt value so that both sides still match correctly. After the join, you strip the salt column.

The Azure Synapse Spark performance guide explicitly recommends salting the entire key — or only the subset of skewed keys — as the primary remedy for shuffle-induced slowdowns.

from pyspark.sql import functions as F

SALT_BUCKETS = 10

# Salt the large (skewed) table
df_large = df_large.withColumn(
    "salt", (F.rand() * SALT_BUCKETS).cast("int")
)

# Explode the small (lookup) table to match all salt values
df_small = df_small.withColumn(
    "salt", F.explode(F.array([F.lit(i) for i in range(SALT_BUCKETS)]))
)

# Join on both the original key and the salt
result = df_large.join(df_small, ["customer_id", "salt"]).drop("salt")
  

5. Technique: Broadcast joins for small dimension tables

When one side of a join is small enough to fit in executor memory, a broadcast join eliminates the shuffle entirely. Instead of moving data between nodes, Spark sends a full copy of the small table to every executor, so each worker can join locally. This is the fastest fix when the skewed side is the larger table and the lookup side is compact.

from pyspark.sql.functions import broadcast

result = df_large.join(
    broadcast(df_small), "customer_id"
)
  

In Redshift, the equivalent approach is setting DISTSTYLE ALL on small dimension tables. This stores a full copy of the table on every compute node, so join colocation is guaranteed without any redistribution at query time. The Redshift distribution styles documentation recommends this for slow-moving reference tables under a few hundred megabytes.


6. Technique: Adaptive Query Execution (Spark 3.x)

Since Spark 3.0, Adaptive Query Execution (AQE) can detect and automatically split skewed partitions at runtime without any manual intervention. When AQE identifies a task whose partition size significantly exceeds the median, it breaks that partition into smaller sub-tasks and replicates the corresponding data from the other join side to match. You can enable it with two configuration properties:

spark.conf.set("spark.sql.adaptive.enabled", "true")
spark.conf.set("spark.sql.adaptive.skewJoin.enabled", "true")
  

AQE is a strong first line of defence on modern Spark environments. If it does not resolve the skew fully — for example when the hot key is extreme — fall back to manual salting as described in the previous section.


7. Fixing skew in Amazon Redshift: choosing the right DISTKEY

In Redshift, skew is a storage-level problem. The fix is selecting a distribution key with high cardinality and uniform value distribution. Columns like order_id or event_id make far better distribution keys than status or country_code. The AWS prescriptive guidance on Redshift table design recommends validating cardinality before committing to a key column — if you notice skewness, change the distribution key.

For tables used heavily in joins, align the distribution key with the join column used most often. This ensures that matching rows already reside on the same node slice, eliminating costly data redistribution at query time. If automatic table optimization is enabled, Redshift will manage distribution style changes in the background as table sizes evolve.


Wrapping Up

Data skew is a structural problem that compounds over time: as tables grow, hot keys attract more rows, and pipelines that once ran in minutes begin taking hours. The pattern to follow is consistent — profile key distributions early, detect skew via the Spark UI or system views like SVV_TABLE_INFO in Redshift, and apply the right fix for the context: salting for heavy shuffle joins, broadcast joins when the lookup side is small, AQE for automatic handling in Spark 3.x, and a well-chosen DISTKEY for Redshift table design. The most reliable rule: never use a column with low cardinality as a partition or distribution key.


Monday, May 11, 2026

Quick Tip: Check Most DTU Expensive Queries in Azure SQL Database

Optimizing Azure SQL Database performance often begins with identifying the most resource-intensive queries. Understanding which queries consume the most DTU (Database Transaction Unit) helps prioritize tuning efforts and improve overall efficiency.

Azure SQL Database provides built-in tools to easily surface the queries with the highest DTU usage, making it straightforward for data engineers and DBAs to pinpoint performance bottlenecks.

Step-by-Step Guide

1. Connect to Your Azure SQL Database Using VSCode or SQL Server Management Studio (SSMS).
These tools allow you to run queries directly against your database.

2. Query the sys.dm_db_resource_stats DMV to Get Recent Resource Usage Data.
This dynamic management view shows DTU usage metrics aggregated for your database and is updated every 15 seconds. You can correlate this with queries identified in query stats views.
Example query to find top DTU-consuming queries:

SELECT TOP 10 
    qs.sql_handle,
    qs.plan_handle,
    total_worker_time/1000 AS total_cpu_ms,
    total_logical_reads, 
    execution_count,
    (total_worker_time / execution_count) / 1000 AS avg_cpu_ms,
    SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, 
        ((CASE qs.statement_end_offset 
            WHEN -1 THEN DATALENGTH(st.text) 
            ELSE qs.statement_end_offset 
          END - qs.statement_start_offset)/2) + 1) AS query_text
FROM 
    sys.dm_exec_query_stats AS qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY 
    total_cpu_ms DESC;

This query highlights queries with the highest total CPU time, which mostly correlates with high DTU consumption.



3. Use Azure Portal's Query Performance Insight for Visual Analysis.
Navigate to your Azure SQL Database resource in the Azure Portal, select "Query Performance Insight," and explore the top resource-intensive queries by DTU consumption over a configurable time window.
Learn more: Query Performance Insight Documentation

4. Investigate Execution Plans for Identified Queries.
Analyze execution plans to understand why queries consume high DTUs and target specific improvements such as indexing, query rewriting, or parameter sniffing fixes.

Conclusion

By regularly identifying the most DTU expensive queries using DMV queries and Azure Query Performance Insight, you can effectively target optimization efforts on your Azure SQL Database. 

This proactive monitoring helps maintain smooth performance and cost efficiency without guesswork.

Additional tips:
- Automate these checks with scheduled scripts or alerts.
- Combine resource analysis with Azure SQL Intelligent Insights for AI-driven recommendations.

Thursday, July 4, 2019

SQL Engine basics: Caching data

SQL Server engine does an excellent job to optimize query performance and it has different methods to achieve this goal, one of these methods is the data caching.

Data caching consist of putting 8KB pages from storage into memory as they are needed, so the database engine can access the required data again in a much faster way.

At a glance, the way SQL Engine caches the data is the following:


When a request is made the engine checks if the data is already cached in memory (or buffer pool), if the data is already available, then it is returned. If the data is not available, then it is retrieved from storage and put into memory until the buffer pool is full.

How long the data is stored in the buffer pool depends on various aspects: the memory allocated to SQL Server, size of your database, workload, type of queries executed, In-memory OLTP.. etc.

When the buffer pool does not have enough space to allocate the desired memory, then a data spill occurs, it consists of additional data stored on tempdb, this is usually caused by inaccurate memory grants. Fortunately, in SQL Server 2019 Memory Grant feedback is being implemented, that can help to reduce this problem, you can read more about it here.

Since SQL Server 2014, you can also configure Buffer pool extensions, as the term suggests, this consist on an extension of the memory by using faster storage (usually SSD or Flash memory), this is an advanced feature and you can learn more about it in the Microsoft documentation.

Related T-SQL Commands


You have now a concept on what is the data caching and how it works, now let us see it in action.

we will use WideWorldImporters test database for this example, first we run some queries, does not matter what you execute for this example, can be any SELECT statement:

DECLARE  @RawValue decimal(10,2) = 25000

SELECT SUM(UnitPrice * Quantity) as RawValue
FROM Sales.InvoiceLines
GROUP BY InvoiceID
HAVING SUM(UnitPrice * Quantity) > @RawValue;

SET @RawValue = RAND()*1500

SELECT InvoiceID, InvoiceLineID, UnitPrice,Quantity
FROM Sales.InvoiceLines 
WHERE TaxAmount > @RawValue
OPTION(FAST 100);

After that we use the DMO sys.dm_os_buffer_descriptors to check what data is into memory, after making the query more readable we can use it like this:

--DETAILS BY DATABASE:
SELECT 
    IIF(database_id = 32767,'ResourceDB',DB_NAME(database_id)) AS [Database],
    file_id, -- This File is at database level, it depends on the database you are executing the query
    page_id,
    page_level,
    allocation_unit_id, -- can be joined with sys.allocation_units (database level DMO)
    page_type,
    row_count,
    read_microsec
FROM sys.dm_os_buffer_descriptors BD
ORDER BY [Database], row_count DESC;

Depending on the workload, we obtain something like this:


If you want something more compact, you can execute the query grouping it by database:

-- TO OBTAIN TOTAL BY DATABASE:
SELECT 
    IIF(database_id = 32767,'ResourceDB',DB_NAME(database_id)) AS [Database],
    COUNT(row_count) AS cached_pages
FROM sys.dm_os_buffer_descriptors 
GROUP BY database_id
ORDER BY cached_pages DESC;

We obtain the results grouped by database:



In order to release the data from the cache, you run the DBCC DROPCLEANBUFFERS command:

DBCC DROPCLEANBUFFERS;

NOTE: Avoid running this command in production environments, since all the data will be released from memory and must be pulled from the storage again, incurring in additional IO usage.


If you have Buffer pool extension enabled and want to check their options, or if you want to determine if the instance has it, use the sys.dm_os_buffer_pool_extension_configuration DMO as follows:

-- Check if you have a buffer pool extension enabled:
SELECT * FROM sys.dm_os_buffer_pool_extension_configuration;

The results are something like this (I don't have a buffer pool extension enabled on my machine):



If you want the script for the commands we discussed, you can download it from my GitHub repository.

Saturday, February 16, 2019

SQL Saturday 828 - T-SQL Basics: Coding for performance

A great experience!
Thanks to all the atendess to my session about T-SQL, for being my first time as a speaker for a SQL Saturday it was good!

As I promised, the presentation and session material is available at the following links:

SQLSaturday #828 site:
(Please evaluate my session if you attend)

https://www.sqlsaturday.com/828/Sessions/Details.aspx?sid=87912


My personal GitHub:

https://github.com/Epivaral/Scripts/tree/master/T-SQL%20Basics%20coding%20for%20performance

Some pictures from the event:



SQL Server local users group board!




Monday, February 11, 2019

Quick tip: Speeding up deletes from SSIS execution log

If you have SQL Server Integration Services installed on your server, and you left the default configurations a table named sysssislog is created on MSDB database, it contains logging entries for packages executed on that instance.

If you are not careful enough, this table can grow uncontrollably over time and can make subsequent insertions very slow.

A proper deletion process must be put in place, so you not get into situations like this one in your msdb database:



If you are already on this situation, you can the following T-SQL Script to delete records by batches:


DECLARE @date_del datetime,
  @batch_size int = 1000, -- will delete on batches of 1000 records
  @RowsAffected int =1

-- Time to keep in the history, in our case 1 month
SET @date_del= DATEADD(mm,-1,getdate()); 

SET NOCOUNT ON;

WHILE (@RowsAffected >0)
BEGIN
 DELETE TOP(@batch_size) 
 FROM [dbo].[sysssislog]
 WHERE starttime < @date_del;

 SET @RowsAffected = @@ROWCOUNT;

 -- If you want to know rows affected, uncomment this:
 -- PRINT @RowsAffected;
END

SET NOCOUNT OFF;


After that you can implement the same query to your msdb maintenance job to have all in one place.

Monday, October 15, 2018

How much affects encryption to your database performance?

Transparent data encryption (TDE) helps you to secure your data at rest, this means the data files and related backups are encrypted, securing your data in case your media is stolen.
This technology works by implementing real-time I/O encryption and decryption, so this implementation is transparent for your applications and users.

However, this type of implementation could lead to some performance degradation since more resources must be allocated in order to perform the encrypt/decrypt operations.

On this post we will compare how much longer take some of the most common DB operations, so in case you are planning to implement it on your database, you can have an idea on what to expect from different operations.

Setup of our example


For this example we will use the Wide World Importers sample database, and restore it 2 times, one decrypted, and the other one encrypted. Please note that the restored database is around 3.2 GB size, a relatively small one.

To encrypt one of the databases we use the following T-SQL (more info about how TDE works here):

USE master;
GO


CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Y0uR_P@$$worD_123';
GO


CREATE CERTIFICATE MyServerCert
	WITH SUBJECT = 'WWI Encryption';
GO


USE [WideWorldImporters_encrypted];
GO


CREATE DATABASE ENCRYPTION KEY
	WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO


ALTER DATABASE [WideWorldImporters_encrypted]


SET ENCRYPTION ON;
GO


After the above T-SQL has been executed, now we have the 2 databases ready for our example:


So it is just a matter of testing and measuring different operations and compare them (in no particular order):

DBCC CHECKDB


We run a dbcc checkdb on each database with the following T-SQL:

SET STATISTICS TIME ON;

DBCC CHECKDB([WideWorldImporters]) WITH NO_INFOMSGS

DBCC CHECKDB([WideWorldImporters_encrypted]) WITH NO_INFOMSGS

SET STATISTICS TIME OFF;

we can see the results, it took around 6% more to complete:



BACKUP DATABASE


Before we start this one, read this post about issues found on database backups with TDE enabled, so it is strongly recommended to patch your instances before using it.

SET STATISTICS TIME ON;

BACKUP DATABASE [WideWorldImporters] TO  
DISK = N'D:\DBAWork\WideWorldImporters-Full.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'WideWorldImporters-Full', 
SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 100

BACKUP DATABASE [WideWorldImporters_encrypted] TO  
DISK = N'D:\DBAWork\WideWorldImporters_encrypted-Full.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'WideWorldImporters_encrypted-Full', 
SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 100

SET STATISTICS TIME OFF;

Surprisingly, this one took less time with encryption enabled, around 15% less time... but wait! this is because of the number of pages processed by each backup operation, because if you check the average speed, you can see the encrypted backup operation is in fact, slower (around 33%), so in the case of this operation, it can vary for your environment:



Restore Database


If you plan to restore an encrypted database to a different server, remember that you must back up your master key and certificate and restore it on the new server in order to do it, otherwise you will not able to recover encrypted data.
Since now we are on the same instance, master key and certificate are already there, so we just run the simple RESTORE command:

SET STATISTICS TIME ON;

USE [master]
RESTORE DATABASE [WideWorldImporters] FROM  
DISK = N'D:\DBAWork\WideWorldImporters-Full.bak' 
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 100

RESTORE DATABASE [WideWorldImporters_encrypted] FROM  
DISK = N'D:\DBAWork\WideWorldImporters_encrypted-Full.bak' 
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 100

SET STATISTICS TIME OFF;

As with the backup operation, the restore took less time for the encrypted database, but you can see that is because of the number of pages processed, because if you check the average restore speed, for the encrypted database is slower (167% slower), so also check this one for your own databases:



Rebuild indexes


We execute Rebuild index statements for random tables with the following T-SQL:

SET STATISTICS TIME ON;

USE [WideWorldImporters]
GO

Print '--- Normal DB ---'

ALTER INDEX [CCX_Warehouse_StockItemTransactions] ON [Warehouse].[StockItemTransactions] 
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE)

ALTER INDEX [IX_Sales_OrderLines_Perf_20160301_01] ON [Sales].[OrderLines] 
REBUILD PARTITION = ALL 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ALTER INDEX [UQ_Warehouse_StockItems_StockItemName] ON [Warehouse].[StockItems] 
REBUILD PARTITION = ALL 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ALTER INDEX [PK_Application_People] ON [Application].[People] 
REBUILD PARTITION = ALL 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ALTER INDEX [FK_Purchasing_PurchaseOrderLines_StockItemID] ON [Purchasing].[PurchaseOrderLines] 
REBUILD PARTITION = ALL 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

Print '--- END Normal DB ---'

USE [WideWorldImporters_encrypted]
GO

Print '--- Encrypted DB ---'


ALTER INDEX [CCX_Warehouse_StockItemTransactions] ON [Warehouse].[StockItemTransactions] 
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE)

ALTER INDEX [IX_Sales_OrderLines_Perf_20160301_01] ON [Sales].[OrderLines] 
REBUILD PARTITION = ALL 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ALTER INDEX [UQ_Warehouse_StockItems_StockItemName] ON [Warehouse].[StockItems] 
REBUILD PARTITION = ALL 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ALTER INDEX [PK_Application_People] ON [Application].[People] 
REBUILD PARTITION = ALL 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ALTER INDEX [FK_Purchasing_PurchaseOrderLines_StockItemID] ON [Purchasing].[PurchaseOrderLines] 
REBUILD PARTITION = ALL 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

Print '--- END Encrypted DB ---'

SET STATISTICS TIME OFF;

You can see the relevant execution results:




The encrypted database took around 24% more time (on average for all the operations) to complete.


Update Statistics

 

Now we execute update statistics with full scan on various tables with the following T-SQL:

SET STATISTICS TIME ON;

USE [WideWorldImporters]
GO

Print '--- Normal DB ---'

UPDATE STATISTICS [Warehouse].[StockItemTransactions] 
WITH FULLSCAN

UPDATE STATISTICS [Sales].[OrderLines] 
WITH FULLSCAN

UPDATE STATISTICS [Warehouse].[StockItems] 
WITH FULLSCAN

UPDATE STATISTICS [Application].[People] 
WITH FULLSCAN

UPDATE STATISTICS [Purchasing].[PurchaseOrderLines] 
WITH FULLSCAN

Print '--- END Normal DB ---'

USE [WideWorldImporters_encrypted]
GO

Print '--- Encrypted DB ---'

UPDATE STATISTICS [Warehouse].[StockItemTransactions] 
WITH FULLSCAN

UPDATE STATISTICS [Sales].[OrderLines] 
WITH FULLSCAN

UPDATE STATISTICS [Warehouse].[StockItems] 
WITH FULLSCAN

UPDATE STATISTICS [Application].[People] 
WITH FULLSCAN

UPDATE STATISTICS [Purchasing].[PurchaseOrderLines] 
WITH FULLSCAN

Print '--- END Encrypted DB ---'

SET STATISTICS TIME OFF;

And these are the results:




Even when some of the tables took less time, on average the encrypted database took around 15% more time to complete the statistics update. This should be other to have into account to check for your own database.


For this post we will not test any DML or SELECT statement, but you are free to do your own tests and determine if TDE implementation suits for you.

As we stated earlier, we have demonstrated that additional workload is put on TDE enabled databases in order to support the encrypt/decrypt operations. Now is is your turn to perform your own validations to see how much is impacted your own databases with this, since this can vary on different storage types (for example on fast SSD storages, it could be barely noticed).

How I Built RAG for My Accounting App on Bedrock and pgvector

The first version of my accounting assistant could not find a single purchase of "comida" in a database full of lunches, misse...