SQL Server

Setup and Best Practices

Resource Allocation Considerations

For RDBMS sources like SQL Server, Dremio's query execution is largely single threaded. This means that for each SQL Server directed query, only one Dremio node will experience a computational load. So unlike most other data sources, larger Dremio clusters won't lead to faster individual query execution times. However, if you expect a large number of concurrent queries (as in the case of many simultaneous Dremio users) these will be distributed evenly across the nodes.

NOTE: Ensure that your Dremio cluster has access to the appropriate port for your SQL Server source. By default this is port 1433.

Dremio and SQL Server

Query Pushdowns

For some operations Dremio can tell the data source to execute that part of the query natively, often dramatically improving performance. These operations are called 'pushdowns.'

Since they share a common language (SQL), Dremio supports most operations as pushdowns in SQL Server. These include:

  • Filter (SQL: WHERE)
  • Limit (SQL: LIMIT)
  • Sorting (SQL: ORDER BY)
  • Aggregation (SQL: GROUP BY)
  • Project (with expressions) (e.g. SQL: SELECT columnA + columnB, columnC, columnD)

NOTE: Since SQL Server has no boolean type, project operations that contain SQL expressions which evaluate to true or false (e.g. SELECT username, friends > 0), and filter operations that include boolean literals in a filter (e.g. WHERE currentAccount = true) cannot be executed as pushdowns.

Initial Connection

Depending on the number of tables in your SQL Server source, the final step of adding it to Dremio can take anywhere from a few seconds to a few minutes as the source's metadata is processed. However, this is a one-time cost and further queries to the source will not incur additional metadata reads.

Dremio Configuration

Here are all available source specific options:

Name Description
Host SQL Server host name.
Port SQL Server port number. Defaults to 1433.
Authentication Type No authentication or Master Authentication.
Username SQL Server user name.
Password SQL Server password.
Database Database name. Optional.
Show only the initial database used for connecting If selected, hides the other DBs that the credential has access to.

results matching ""

    No results matching ""