GitHub user manimovassagh added a comment to the discussion: SQL Server query
hints
The Dosu explanation is correct about the root cause -- Superset wraps your
virtual dataset query in a subquery, and `OPTION (MAXDOP 1)` can only appear at
the outermost level of a SQL Server statement. It gets buried inside the
subquery and SQL Server rejects it.
The database view workaround is the most practical path, but there's a
subtlety: you **cannot** put `OPTION` hints inside a view definition either.
SQL Server doesn't allow query hints in views.
What you _can_ do:
1. **Use a stored procedure + virtual dataset** -- Create a stored proc with
the hint, then query it from SQL Lab:
```sql
CREATE PROCEDURE dbo.sp_my_report AS
BEGIN
SELECT col1, col2, col3
FROM my_table
WHERE ...
OPTION (MAXDOP 1)
END
```
Then in SQL Lab: `EXEC dbo.sp_my_report` and save as virtual dataset. The
catch is that Superset won't try to wrap `EXEC` statements in subqueries.
2. **Set MAXDOP at the connection level** -- In your Superset database
connection settings, add an `engine_params` override:
```json
{
"connect_args": {
"appname": "superset"
}
}
```
Then configure a Resource Governor workload group in SQL Server that caps
MAXDOP for connections from Superset:
```sql
CREATE WORKLOAD GROUP SupersetGroup WITH (MAX_DOP = 1);
CREATE FUNCTION dbo.fn_classify_superset()
RETURNS sysname WITH SCHEMABINDING AS
BEGIN
IF APP_NAME() = 'superset' RETURN 'SupersetGroup';
RETURN 'default';
END;
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION =
dbo.fn_classify_superset);
ALTER RESOURCE GOVERNOR RECONFIGURE;
```
This applies the MAXDOP limit to all queries from Superset without touching
individual queries.
3. **Database-scoped configuration** -- If you can set it for the entire
database:
```sql
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1;
```
This is the simplest option if the database is dedicated to analytics
workloads.
Option 2 gives you the most control without affecting other applications.
GitHub link:
https://github.com/apache/superset/discussions/38694#discussioncomment-16233679
----
This is an automatically sent email for [email protected].
To unsubscribe, please send an email to:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
