Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Index requirement for logical operations Expand / Collapse
Author
Message
Posted Thursday, March 3, 2011 6:03 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 11:29 PM
Points: 466, Visits: 1,925
Please help me build the index for the query.The table has more than 3 million rows and whatever index combination I choose,always there is a index scan for the query.I am not able to convert it into a seek operation.this runs throughout the day and keeps my CPU spike up every 5 minutes

SELECT TOP 20 Primary, Secondary, [Year], DocType, [PageCount], ReturnId, IMAGEPATH
FROM [table] WHERE [Year] = 2009 AND
(Primary IN (123, 234) OR Secondary IN (241, 354))

Please suggest me an index for this.I beleive the 'OR' operator is causing the scan but is there a way to avoid it.
Post #1072477
Posted Thursday, March 3, 2011 8:06 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:11 AM
Points: 40,390, Visits: 36,826
You need two indexes because of the or

One index on Primary, one on Secondary. You may need to make both covering, depends on the rows involved. test and see



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1072585
Posted Thursday, March 3, 2011 8:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 19, 2011 2:39 AM
Points: 1, Visits: 22
If you paste the SQL into a query window in SSMS and press CTL-L you will see the estimated execution plan. SSMS may display a Missing Index suggestion and right-clicking the suggestion and choosing Missing Index details will show the SQL needed to create the index. Alternatively you could use the Index tuning tool available from the query menu.
Post #1072586
Posted Friday, March 4, 2011 12:22 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 11:29 PM
Points: 466, Visits: 1,925
Philip Turtle (3/3/2011)
If you paste the SQL into a query window in SSMS and press CTL-L you will see the estimated execution plan. SSMS may display a Missing Index suggestion and right-clicking the suggestion and choosing Missing Index details will show the SQL needed to create the index. Alternatively you could use the Index tuning tool available from the query menu.



Gail,
I created the two indexes.
One on primary and year including all the columns in select query
Second on secondary or year including all the columns in select query.

But there is still a table scan.I have good amount of memory so all the database stays in cache but the scan makes the cpu go up to 90 percent every time this query runs.Any more suggestions please.
Post #1073100
Posted Friday, March 4, 2011 12:43 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:11 AM
Points: 40,390, Visits: 36,826
GilaMonster (3/3/2011)
You need two indexes because of the or

One index on Primary, one on Secondary. You may need to make both covering, depends on the rows involved. test and see



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1073111
Posted Friday, March 4, 2011 12:58 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 11:29 PM
Points: 466, Visits: 1,925
GilaMonster (3/4/2011)
GilaMonster (3/3/2011)
You need two indexes because of the or

One index on Primary, one on Secondary. You may need to make both covering, depends on the rows involved. test and see


CREATE NONCLUSTERED INDEX [primary] ON [dbo].[Finals]
(
[Primary] ASC
)
INCLUDE ( [ReturnId],
[Secondary],
[Year],
[DocType],
[PAGECOUNT],
[IMAGEPATH])


CREATE NONCLUSTERED INDEX [secondary] ON [dbo].[Finals]
(
[secondary] ASC
)
INCLUDE ( [ReturnId],
[primary],
[Year],
[DocType],
[PAGECOUNT],
[IMAGEPATH])

I created these two indexes but the query does a table scan with the first index to give me the output.I even updated the statistics for the table.
Post #1073114
Posted Friday, March 4, 2011 1:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:11 AM
Points: 40,390, Visits: 36,826
Execution plan please


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1073115
Posted Friday, March 4, 2011 1:11 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 11:29 PM
Points: 466, Visits: 1,925
I have attached the execution plan for the query.I am posting my query and indexes which i created as per your specification.

My query:


SELECT TOP 20 Primary, Secondary, [Year], DocType, [PageCount], ReturnId, IMAGEPATH
FROM finals WHERE [Year] = 2009 AND
(Primary IN (123, 234) OR Secondary IN (241, 354))


Index-1

CREATE NONCLUSTERED INDEX [prim] ON [dbo].[Finals]
(
[Primary ] ASC
)
INCLUDE ( [ReturnId],
[Secondary ],
[Year],
[DocType],
[PAGECOUNT],
[IMAGEPATH])

Index-2

CREATE NONCLUSTERED INDEX [sec] ON [dbo].[Finals]
(
[Secondary ] ASC
)
INCLUDE ( [ReturnId],
[Primary ],
[Year],
[DocType],
[PAGECOUNT],
[IMAGEPATH])
Post #1073118
Posted Friday, March 4, 2011 1:14 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:11 AM
Points: 40,390, Visits: 36,826
Move year from the include to the key. I didn't notice the filter on that earlier. That's for both indexes


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1073121
Posted Friday, March 4, 2011 1:19 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 11:29 PM
Points: 466, Visits: 1,925
GilaMonster (3/4/2011)
Move year from the include to the key. I didn't notice the filter on that earlier


I moved 'year' to the key column in both the indexes but still the same execution plan with non-clustered index scan
Post #1073122
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse