CODEWITHSUNDEEP
sqlsql-serveroracle-database
Navyseal
Navyseal

Reputation: 901

Whats the "PARALLEL" equivalent in SQL Server

I have this problem where I need to do a COUNT(COLUMN_NAME) and SUM(COLUMN_NAME) on a few of the tables. The issue is the time it's taking forever 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 a parallel execution for a single query/session by using a 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 couldn't comeup with something concrete where I can specify a table hint for a parallel execution. I believe, SQL Server decides for itself whether to do a parallel or sequential execution depending on the query cost.

The same query in Oracle with a parallel hint takes 2-3 mins to perform whereas on SQL Server it takes about an hour and half.

Upvotes: 6

Views: 9265

Answers (2)

Ramki
Ramki

Reputation: 463

In oracle if do select count() on a column then sql will follow index. In below plan you can see "INDEX FAST FULL SCAN" this will make sql run faster. You can try same in sqlserver, do your table has index. You shall try create index on the column which your counting. But in oracle case it will use any other column index. In below sql has "count(DN)" but it use index of some other 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

Upvotes: -1

Arion
Arion

Reputation: 31239

I am reading the article Forcing a Parallel Query Execution Plan . For me it looks like you could for testing purpose force a Parallel execution. The author says in the conclution:

Conclusion

Even experts with decades of SQL Server experience and detailed internal knowledge will want to be careful with this trace flag. I cannot recommend you use it directly in production unless advised by Microsoft, but you might like to use it on a test system as an extreme last resort, perhaps to generate a plan guide or USE PLAN hint for use in production (after careful review).

This is an arguably lower risk strategy, but bear in mind that the parallel plans produced under this trace flag are not guaranteed to be ones the optimizer would normally consider. If you can improve the quality of information provided to the optimizer instead to get a parallel plan, go that way :)

The article is refering to a Trace Flag:

There’s always a Trace Flag

In the meantime, there is a workaround. It’s not perfect (and most certainly a choice of very last resort) but there is an undocumented (and unsupported) trace flag that effectively lowers the cost threshold to zero for a particular query

So as far my understanding of this article you could do something like this:

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

Upvotes: 2

Related Questions