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


No Comments

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