How to solve Index Usage

• WayneS

  SSC Guru

  Points: 95461

  More actions

  March 18, 2011 at 11:11 am

  #1300678

  I just noticed this...

  Of the columns in the suggested index, I don't see any of those columns at all in the DDL you posted for the table definition.

  The query also doesn't show a LIKE operator being used.

  The query plan shows an implicit conversion going on - it appears to be the field that you're comparing the count() to. Though it could be converting the count() from bigint to int.

  Edit: are you sure that the query plan is for the query we're talking about?

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• sql_butterfly

  Ten Centuries

  Points: 1264

  More actions

  March 18, 2011 at 10:19 pm

  #1300885

  The columns are same. BusinessDate first column of index, PName second, DName third....

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  March 19, 2011 at 12:37 am

  #1300894

  Butterfly, I saw someone mention this earlier in the thread but your way out of this is probably Full Text Indexing. It's a bloat on the data layer but it's your best bet in cases like this.

  ---

  - Craig Farrell

  Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

  For better assistance in answering your questions[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA