What is the "PARALLEL" equivalent in SQL Server

Question

I have this problem when I need to do COUNT(COLUMN_NAME) and SUM(COLUMN_NAME) in multiple tables. The problem is that this is the time on SQL Server to do this.

We have over 2 billion records for which I need to perform these operations.

In Oracle, we can force parallel execution for a single request / session using the PARALLEL hint. For example, for a simple SELECT COUNT we can do

 SELECT /*+ PARALLEL */ COUNT(1) FROM USER.TABLE_NAME; 

I searched if there is something available for SQL Server, and I could not find something specific where I can provide a table hint for parallel execution. I assume SQL Server decides whether to execute parallel or sequential execution depending on the cost of the query.

The same query in Oracle with a parallel hint takes 2-3 minutes, while on SQL Server it takes about an hour and a half.

Answer 1

I am reading the article "Enforcing a concurrent query execution plan . " For me, it looks like you could check the target force for parallel execution. The author concludes:

Conclusion

Even experts with decades of experience with SQL Server and detailed internal knowledge will want to be careful with this trace flag. I cannot recommend you use it directly in production, if only Microsoft, but you can use it in the test system as a last resort, perhaps to create a plan guide or USE PLAN for use in production (after a thorough analysis).

This may be a lower risk strategy, but remember that parallel plans created under this trace flag are not guaranteed, which the optimizer will usually consider. If you can improve the quality of the information provided by the optimizer, instead, to get a parallel plan, go as follows :)

The article refers to the trace flag:

There is always a trace flag

At the same time, there is a workaround. Its not ideal (and most of course, the choice is a last resort), but there is an undocumented (and unsupported) trace flag that effectively reduces the cost threshold to zero for a particular request

So, as far as I understand this article, you can do something like this:

 SELECT COUNT(1) FROM USER.TABLE_NAME OPTION (RECOMPILE, QUERYTRACEON 8649) 

Answer 2

In oracle if do select count () in a column, then sql will follow the index. In the plan below you can see "INDEX FAST FULL SCAN", this will speed up sql execution. You can try the same in sqlserver, your table has an index. Try creating an index in the column in which you are counting. But in the case of the oracle, it will use any other column index. There is "count (DN)" in the sql below, but it uses the index of another column.

 SQL> set linesize 500 SQL> set autotrace traceonly SQL> select count(DN) from My_TOPOLOGY; Execution Plan ---------------------------------------------------------- Plan hash value: 2512292876 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 164 (64)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| FM_I2_TOPOLOGY | 90850 | 164 (64)| 00:00:01 | -------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 180 consistent gets 177 physical reads 0 redo size 529 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed