Home Forums SQL Server 2014 Development - SQL Server 2014 SQL uses 2 different executions plan (slow and fast) for a simple query. Need a new index ? RE: SQL uses 2 different executions plan (slow and fast) for a simple query. Need a new index ?

  • Hard to tell without selectivity / row count info, as Chris noted.

    But, if the partition and dataareaid are how you typically query this table, and guessing that PARTITION is more selective than DATAAREAID, I suggest trying this:
    Cluster the table on:
    (PARTITION, DATAAREAID, DATASOURCE)
    Make DATAAREAID [var]char rather than n[var]char if you can.  If it's not nchar, it's critical to get rid of the N'.

    In fact, it's best just to get rid of it anyway.  If it's a unicode string, SQL will convert it to unicode:

    (((PARTITION = 5637144576) AND (DATAAREAID = N'dat'))  ...

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.