object_id - ALTER PROC vs DROP and CREATE PROC

Using ALTER PROC to deploy changes preserves the object_id. Using DROP followed by CREATE changes the object_id. This is important for DBAs managing Forced Plans. When object_id changes, Query Store generates a new sys.query_store_query entry with a new query_id, even if the query text remains the same. The forced plan stops working in this case, and DBAs must reconfigure it. 

Testing:

CREATE PROC TestSP
AS
BEGIN
SELECT 1;
END;
GO
SELECT * FROM sys.objects WHERE name ='TestSP';
GO
ALTER PROC TestSP
AS
BEGIN
SELECT 2;
END;
GO
SELECT * FROM sys.objects WHERE name ='TestSP';
GO
DROP PROC TestSP;
GO
CREATE PROC TestSP
AS
BEGIN
SELECT 2;
END;
GO
SELECT * FROM sys.objects WHERE name ='TestSP';

Access Oracle database data from SQL Server using PolyBase

USE master;

GO

-- Verify PolyBase is Installed

SELECT SERVERPROPERTY('IsPolyBaseInstalled') AS IsPolyBaseInstalled;

GO

-- Check PolyBase configuration

EXEC sp_configure 'polybase enabled';

GO

/* -- if needed:

EXEC sp_configure 'show advanced options',1;

RECONFIGURE;

GO

EXEC sp_configure 'polybase enabled',1;

RECONFIGURE;

GO

*/

USE test_db; /* USER DATABASE */

GO

-- Check whether a Database Master Key exists

SELECT *

FROM sys.symmetric_keys

WHERE name = '##MS_DatabaseMasterKey##';

GO

-- List all database scoped credentials

SELECT

    credential_id,

    name,

    credential_identity,

    create_date,

    modify_date

FROM sys.database_scoped_credentials

ORDER BY name;

GO

-- Create Database Master Key in User Database if needed

USE test_db;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<$345>FeQ2@S3PEm+87B';

GO

-- Verify

SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##';

GO

-- Create Database Scoped Credential

CREATE DATABASE SCOPED CREDENTIAL OracleCred WITH IDENTITY = 'POLYBASE_READ', SECRET = '<$345>FeQ2@S3PEm+87B';

GO

-- Verify

SELECT * FROM sys.database_scoped_credentials;

GO

-- Create External Data Source

CREATE EXTERNAL DATA SOURCE OracleDS WITH (

    LOCATION = 'oracle://ORACLESERVER:1600',

    CONNECTION_OPTIONS = 'ServiceName=ORACLESERVER.domain.com',

    CREDENTIAL = OracleCred

);

GO

-- Verify

SELECT * FROM sys.external_data_sources;

GO

-- Create External Table

CREATE EXTERNAL TABLE MODELDB_GLOBAL_RMS_ISSUE

(

    RMS_ID      DECIMAL(38,0) NOT NULL,

    ISSUE_ID    CHAR(10) COLLATE Latin1_General_100_BIN2_UTF8 NOT NULL,

    FROM_DT     DATETIME2(0) NOT NULL,

    THRU_DT     DATETIME2(0) NOT NULL

)

WITH

(

    LOCATION = '[ORACLESERVER.domain.com].MODELDB_GLOBAL.RMS_ISSUE',

    DATA_SOURCE = OracleDS

);

GO

-- Verify (check Actual Execution Plan XML, see whether 'pushdown' occurred)

SELECT TOP 10 * FROM MODELDB_GLOBAL_RMS_ISSUE;

-- USAGE

select * into #modeldb_global__rms_issue from MODELDB_GLOBAL_RMS_ISSUE;

SELECT COUNT(1) FROM #modeldb_global__rms_issue;

SELECT TOP 10 * FROM #modeldb_global__rms_issue;

Azure PostgreSQL built-in Health Recovery

During my testing of CLUSTER and PG_REPACK on Azure PostgreSQL, I found that Azure PostgreSQL has an automatic health recovery mechanism. This feature drops running processes on the Postgres database when it detects an unhealthy server condition, such as full storage.

Enable Audit

SHOW shared_preload_libraries;

-- Azure Portal -> Server parameters -> shared_preload_libraries -> Add PGAUDIT

SELECT name

FROM pg_available_extensions

WHERE name = 'pgaudit';

SHOW azure.extensions;

-- Azure Portal -> Server parameters -> azure.extensions -> Add PGAUDIT

CREATE EXTENSION IF NOT EXISTS pgaudit;

SHOW pgaudit.log;

-- Azure Portal -> Server parameters -> pgaudit.log -> ALL

SHOW log_line_prefix;

-- Azure Portal -> Server parameters -> log_line_prefix -> %t-%c-user=%u,db=%d,app=%a,client=%h

CLUSTER

-- This command is metadata only; it set the default index for future CLUSTER operations. It does not actually re-cluster the table.

ALTER TABLE curves.xc_curve

CLUSTER ON xc_curve_pk;

-- verify the clustered index

SELECT

    c.relname AS table_name,

    i.relname AS index_name

FROM pg_class c

JOIN pg_index x

    ON c.oid = x.indrelid

JOIN pg_class i

    ON i.oid = x.indexrelid

WHERE x.indisclustered

  AND c.oid = 'curves.xc_curve'::regclass;

/*

 * This command is Session wide only (for the duration of that specific connection).

 * It will not apply to the whole server or all users.

 */

SET maintenance_work_mem = '8GB';

SHOW maintenance_work_mem;

\timing on

/*

 * For a 1.8 TB table, I would typically want at least 2–2.5 TB of free storage beyond the current database size before attempting a CLUSTER, otherwise there is a real risk of running out of space and having the operation fail.

 */

CLUSTER VERBOSE curves.xc_curve USING xc_curve_pk;

-- Check CLUSER running

SELECT

    pid,

    usename,

    application_name,

    state,

    wait_event_type,

    wait_event,

    now() - query_start AS runtime,

    query

FROM pg_stat_activity

WHERE query ILIKE 'CLUSTER%';

PG_REPACK

CREATE EXTENSION pg_repack;

 -- PGSQL pg_repack

pg_repack -t curves.xc_curve -d axioma -h us5-dp-d-pg1.postgres.database.azure.com -U breakglass -k

-- Check PG_REPACK running:
SELECT pid,

       usename,

       application_name,

       state,

       wait_event_type,

       wait_event,

       query

FROM pg_stat_activity

WHERE application_name LIKE '%repack%';

Health recovery

An internal Azure process executed the pg_terminate_backend command to kill the CLUSTER connection.

/*
 * SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE backend_type = 'client backend' AND usename<> 'azuresu' AND usename<> 'replication' AND pg_catalog.pg_is_in_recovery() = false and (SELECT setting = 'on' FROM pg_settings WHERE name = 'default_transaction_read_only')

 */

 

Analyzing PostgreSQL Query Performance

To evaluate the performance of a PostgreSQL query, we can utilize the EXPLAIN command. In pgAdmin, upon opening a query window, you will find two buttons located on the top menu bar.

Core Comparison

	EXPLAIN	EXPLAIN ANALYZE
Execution	Does not run the query.	Executes the query completely.
Speed	Instantaneous (takes milliseconds).	Takes as long as the query itself to run.
Data Safety	100% safe for all commands.	Modifies data if used on INSERT/UPDATE/DELETE.
Metrics	Theoretical estimates (cost, rows, width).	Real runtime stats (actual time, actual rows, memory).

Because EXPLAIN ANALYZE actually runs the query, it is important to enclose the entire script with START TRANSACTION and ROLLBACK commands when analyzing DML statements. If you do not take this step, you might unintentionally modify the table data while analyzing the query. To avoid such unintended changes, always use these transaction commands during your analysis.

To analyze your query in the pgAdmin query window, select the START TRANSACTION, EXPLAIN ANALYZE, and the specific query, then hit F5 to run it, such as:

After that, you can highlight the complete output, copy it, and insert it into a text file.

Finally, you should highlight a ROLLBACK command, then execute it.

Be aware that executing the full set of commands (START TRANSACTION, EXPLAIN, your SQL query, ROLLBACK) together will lead to the ROLLBACK canceling the EXPLAIN output, which means you won't be able to access the information you need.

Additionally, you can retrieve the BUFFERS, COST, and TIMING data from the real execution plan by running EXPLAIN (ANALYZE, BUFFERS, TIMING, COSTS).

ANALYZE BUFFERS is the gold standard for query optimization because it reveals the exact I/O footprint of a query. While ANALYZE executes the query to show actual runtime and row counts, BUFFERS shows exactly how many data blocks (pages) Postgres had to read, write, Postgres handles data in blocks, where 1 block = 8 KiB by default.

Why can the same Query Text in the Query Store have multiple Query_IDs?

In Query Store, query_id is not just based on the query text. It is derived from a broader concept of a query signature, which includes additional attributes beyond the literal SQL text.

That’s why you can see the same query_sql_text mapped to multiple query_ids.

✅ Core Reason

A query_id is generated based on:

• Normalized query text (parameterized form)
• Context settings (set_options)
• Database settings / environment affecting compilation

So even if the raw text looks identical, SQL Server treats them as different queries internally.

🔍 Main Cause

1. Different SET Options (set_options)

SELECT DB_NAME(), query_id, OBJECT_NAME(Q.object_id), T.query_sql_text, q.last_execution_time, T.query_text_id, Q.context_settings_id

FROM sys.query_store_query Q

JOIN sys.query_store_query_text T ON Q.query_text_id = T.query_text_id

WHERE Q.object_id = OBJECT_ID('<Stored Procedure/Trigger name>')

AND T.query_sql_text LIKE '%<query_text>%'

ORDER BY q.last_execution_time DESC;

GO

SELECT GETUTCDATE();

SELECT * FROM sys.query_context_settings WHERE context_settings_id IN (1, 6);

DECLARE @set_options INT = 4347; -- change value

SELECT name, value

FROM (VALUES

    ('ANSI_PADDING', 1),

    ('Parallel Plan', 2),

    ('FORCEPLAN', 4),

    ('CONCAT_NULL_YIELDS_NULL', 8),

    ('ANSI_WARNINGS', 16),

    ('ANSI_NULLS', 32),

    ('QUOTED_IDENTIFIER', 64),

    ('ANSI_NULL_DFLT_ON', 128),

    ('ANSI_NULL_DFLT_OFF', 256),

    ('NO_BROWSETABLE', 512),

    ('ARITHABORT', 1024),

    ('NUMERIC_ROUNDABORT', 2048),

    ('DATEFIRST', 4096),

    ('DATEFORMAT', 8192),

    ('LANGUAGE', 16384)

) AS t(name, value)

WHERE (0x000010FB & value) = value;

SELECT name, value

FROM (VALUES

    ('ANSI_PADDING', 1),

    ('Parallel Plan', 2),

    ('FORCEPLAN', 4),

    ('CONCAT_NULL_YIELDS_NULL', 8),

    ('ANSI_WARNINGS', 16),

    ('ANSI_NULLS', 32),

    ('QUOTED_IDENTIFIER', 64),

    ('ANSI_NULL_DFLT_ON', 128),

    ('ANSI_NULL_DFLT_OFF', 256),

    ('NO_BROWSETABLE', 512),

    ('ARITHABORT', 1024),

    ('NUMERIC_ROUNDABORT', 2048),

    ('DATEFIRST', 4096),

    ('DATEFORMAT', 8192),

    ('LANGUAGE', 16384)

) AS t(name, value)

WHERE (0x000000FB & value) = value;

The outcome presented below indicates that the values of query_text_id and the object_name for these two query_store entries are identical, which implies that these two query_store entries essentially refer to the same query within the same object (stored procedure/trigger).

The reason there are two query_ids for the same query is that, as indicated by the context_settings_id value, the two query_store entries are executed under different context settings (SET OPTIONs).

Table Fast Row Count

 sys.dm_db_partition_stats (Transact-SQL) - SQL Server | Microsoft Learn

SELECT

    OBJECT_NAME(object_id) AS TableName,

    SUM(row_count) AS RowCount

FROM sys.dm_db_partition_stats

WHERE index_id IN (0,1)

GROUP BY object_id

ORDER BY RowCount DESC;

If sys.dm_db_partition_stats (or sys.partitions) is giving inaccurate row counts, it’s usually not a bug—it’s due to how SQL Server maintains metadata. Here’s what’s happening and how to deal with it:

Metadata Lag

• Row counts come from internal allocation metadata, not real-time scans.
• Updates happen during:
  • checkpoints
  • index rebuild/reorg
  • statistics updates

👉 Under heavy INSERT/DELETE, the count can drift.

⚠️ Common Causes of Wrong Counts

Cause	Effect
Heavy DML (INSERT/DELETE)	DMV lag
Truncated tables	mismatch temporarily
Bulk operations	metadata delay
Disabled/rebuilt indexes	stale counts
Partition switching	incorrect totals
Heaps with forwarding records	inaccuracies

✅ How to Fix / Improve Accuracy

DBCC UPDATEUSAGE (YourDatabaseName) WITH COUNT_ROWS;

DBCC UPDATEUSAGE (Transact-SQL) - SQL Server | Microsoft Learn

Create CLR assembly in Azure SQL Managed Instance (MI)

Creating a CLR assembly in Azure SQL Managed Instance (MI) follows a similar process to on-premises SQL Server, but with key differences due to the cloud-managed nature of the service—most notably, you cannot reference local file paths.

1. Enable CLR Integration

CLR integration is disabled by default. You must enable it at the instance level using the sp_configure system stored procedure.

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

2. Produce the CREATE ASSEMBLY command including the Binary Hex Literal

Most likely, the developer will give us a .dll file. As mentioned earlier, MI cannot access the local file path. Therefore, as a DBA, you can use your own local machine, install the free SQL Server Express edition, and then use the .dll to create the assembly in your local SQL Express first. After that, generate the CREATE ASSEMBLY from your local SQL Express, connect to the MI, and execute the CREATE ASSEMBLY script there.

-- in your local SQL Express
CREATE ASSEMBLY HelloWorld

FROM 'C:\Path\HelloWorld.dll'

WITH PERMISSION_SET = SAFE;

How to generate the CREATE ASSEMBLY from your local SQL Express: