THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Joe Chang

Azure database_id inconsistencies

I am working on improvements to my SQL ExecStats tool, including Azure support. Previously, I had put try catch blocks around everything that bombs in Azure. Now I am putting IF EXISTS tests around SQL referencing system views and procedures not in Azure an other. One of issues I encountered is that there is an inconsistency between DB_ID() and sys.databases.

SELECT database_id FROM sys.databases WHERE name = DB_NAME()

On my Azure database, I have just one user database. We would normally expect that the first user database has database_id = 5.

And indeed SELECT DB_ID() does produce 5, but sys.databases shows database_id 1 for master, and 4 for my user database.

The reason this is annoying is because ExecStats attempts to correlate index usage to execution plans, I need consistent values to be reported by each of many queries. Most of the ExecStats code has already replaced DB_ID(), with a query to sys.databases WHERE name = DB_NAME().

But sys.dm_db_index_usage_stats reports the user database as having database_id = 5.

So for now, I am just subtracting 1 from the database_id in sys.dm_db_index_usage_stats but I have not tested this in an Azure instance with more than 1 user DB.

By the way, the latest ExecStats is today, 2017-05-23 2017-05-25

Edit, note date change above

Edit 2017-06-4

Same is true of dm_exec_sql_text, dm_exec_text_query_plan and probably more

Published Tuesday, May 23, 2017 5:11 PM by jchang

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Razvan Socol said:

This seems to be fixed. Can you check again?

September 13, 2018 12:48 AM

Razvan Socol said:

I see now that this is "documented" to be unreliable: in a Microsoft employee wrote:

"One shouldn't rely on database id / db_id bultin in azure as it changes whenever database moves to a different sql instance (because of the database failover). Use name instead of the id. Also please note that the db_id can potentially be different than in the sys.databases"

September 13, 2018 1:04 AM

michellesanders said:

The objective is to achieve a large gain in performance via elapsed time. Only so much can be gained on the numerator D-S, so much depends on the latency of I.

soundcloud to mp3 free

October 22, 2018 11:54 PM

Leave a Comment


About jchang

Reverse engineering the SQL Server Cost Based Optimizer (Query Optimizer), NUMA System Architecture, performance tools developer - SQL ExecStats, mucking with the data distribution statistics histogram - decoding STATS_STREAM, Parallel Execution plans, microprocessors, SSD, HDD, SAN, storage performance, performance modeling and prediction, database architecture, SQL Server engine

This Blog


Privacy Statement