Skip to main content

Synapse Profiler Details

Prerequisites​

1. Download​

2. Authenticate to Azure using Azure CLI​

az login

The profiler uses Azure SDK's DefaultAzureCredential which attempts authentication in this order:

  1. Environment Variables (Service Principal):
    • AZURE_TENANT_ID
    • AZURE_CLIENT_ID
    • AZURE_CLIENT_SECRET
  2. Managed Identity (if running on Azure VM/App Service)
  3. Azure CLI (from az login)
  4. Visual Studio Code
  5. Azure PowerShell

Ensure your user/service principal account has the required roles listed below. See Azure documentation for more details.

3. Required Access to Synapse Workspace​

Attention:

Skip this prerequisite if you are using a standalone SQL Dedicated Pool (formerly Azure SQL DW) and NOT a Synapse Workspace

  • Profiler uses the Python version of Azure SDK libraries to extract information about target Synapse Workspace.
  • For making the Azure API calls using Azure SDK, the authenticated identity (user or service principal) needs the following role assignments. Just giving the Synapse Administrator role is not enough. The below roles must be explicitly assigned.
    • Synapse Artifact User.
    • Monitoring Reader
      • Assign from Synapse Workspace → IAM

4. Setup user_id/password for ODBC connectivity​

Create/Use a user with access to query the following tables in Synapse.

Attention:

The user should not have Multi-factor Authentication (MFA) enabled as ODBC does not support MFA

This user id needs to have read access (SELECT grants) on the following tables. The following permissions are required for Dynamic Management Views (DMVs):

  • VIEW DATABASE STATE - Required for database-scoped DMVs (queries within specific databases)
  • VIEW SERVER STATE - Required for server-level DMVs (queries in the master database)
  • VIEW DEFINITION - Required to view metadata definitions
-- Grant database-level permissions
GRANT VIEW DATABASE STATE TO <user_id>
GRANT VIEW DEFINITION TO <user_id>

-- Grant server-level permissions (CRITICAL for serverless pool DMVs)
-- Connect to master database first
USE master
GO
GRANT VIEW SERVER STATE TO <user_id>
Pool TypeCategoryObjects
Dedicated SQL PoolTables

sys.databases
information_schema.tables
information_schema.columns
information_schema.views
information_schema.routines

DMVs

sys.dm_pdw_exec_sessions
sys.dm_pdw_exec_requests
sys.dm_pdw_nodes_db_partition_stats

Serverless SQL PoolCatalog Views

sys.databases
information_schema.tables
information_schema.columns
information_schema.views
sys.objects (for routines)

Server DMVs

sys.dm_exec_sessions
sys.dm_exec_requests
sys.dm_exec_query_stats
sys.dm_exec_sql_text
sys.dm_exec_requests_history
sys.dm_external_data_processed

Configure Connection to Synapse​

databricks labs lakebridge configure-database-profiler

Please select the source system you want to configure
[0] synapse
Enter a number between 0 and 0: 0

(local | env)
local means values are read as plain text
env means values are read from environment variables, and fall back to plain text if not variable is not found

Enter secret vault type (local | env)
[0] env
[1] local
Enter a number between 0 and 1: 1
Please provide Synapse Workspace settings:
Enter Synapse workspace name: synapse
Enter SQL user: user
Enter SQL password:
Enter timezone (e.g. America/New_York) (default: UTC):
Enter the ODBC driver installed locally (default: ODBC Driver 18 for SQL Server):
Please provide Azure access settings:
Enter development endpoint: synapse.endpoint
Please select JDBC authentication type:
Select authentication type
[0] ad_passwd_authentication
[1] spn_authentication
[2] sql_authentication
Enter a number between 0 and 2: 2
Enter fetch size (default: 1000):
Enter login timeout (seconds) (default: 30):
Exclude serverless SQL pool from profiling? (default: no):
Exclude dedicated SQL pools from profiling? (default: no):
Exclude Spark pools from profiling? (default: no):
Exclude monitoring metrics from profiling? (default: no):
Redact SQL pools SQL text? (default: no):

Back to Configure Profiler