Arthur Fuller
fuller.artful at gmail.com
Fri Jan 4 08:43:08 CST 2008
John, T-SQL has what are known as Query Hints, which allow you to tell the query engine which indexes to use. The syntax is a tad hairy to document thoroughly here, but you can get to it quickly by searching BOL for Hints, which takes you to this URL: http://msdn2.microsoft.com/en-us/library/ms181714.aspx. Look at the Profiler results and then if you don't like what you see, add a hint or two or three and compare results. hth, Arthur On 1/4/08, jwcolby <jwcolby at colbyconsulting.com> wrote: > > I had an interesting occurrence today. I am processing an order on the > database from hell. As a result of my research a few weeks ago I > discovered > that cover indexes on a field which includes the PK goes a long way in > speeding things up. So I learned to create "single field" indexes where I > index just a specific field (golf or walking for example) and adding in > the > PKID. In this case I need to process eight "hobby" fields, selecting > records where any of these fields are 'Y'. So I created 8 indexes on > these > 8 fields. > > I then created a single query that pulled in all eight hobby fields in an > OR > such that any 'Y' in any field pulls the PKID. I then created a count of > the PKID to see how many I got (7,704,210). I decided to go back in and > create cover indexes on a group of the fields. These fields are > consecutive > in the table and tend to be called out in orders together, IOW Golf or > running or skiing etc. In order to cover the 18 fields I created two > cover > indexes grouping the first 9 and then the second 9 (and the PKID in each > index). The query actually slowed down significantly, going from 2 > minutes > 36 seconds when there was no larger cover indexes to 4 minutes 12 seconds > after building these larger cover indexes. > > Understand that I was not using every single field from these 18 fields in > the where clause, in fact only about 8 of them, but the 8 fields just > happen > to be split across the two larger cover indexes so that both would be > used. > understand too that I still have the "single field" cover indexes for all > of > the fields needed in my where clause. > > So it appears that the presence of a cover that included several of the > fields in the where clause caused SQL Server to use the larger 8 field > cover > indexes rather than the individual "single field" cover indexes, and in so > doing slowed down the query by about 40% or so. > > Hmmmm. > > John W. Colby > Colby Consulting > www.ColbyConsulting.com > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >