jwcolby
jwcolby at colbyconsulting.com
Fri Jan 4 09:09:31 CST 2008
Arthur, I knew these existed but have never used them. Given everything on my plate and my current state of knowledge or lack thereof, I might just delete the larger cover indexes back out. the use of the single field cover indexes has worked wonders on my processing times already. The client tends to ask for the same select fields over and over. in this case I had to add 8 new indexes but had three in place already. Luckily the creation of the cover indexes only takes 2 minutes or so per. Over time I will end up with every field already indexed and the process of filling an order will get less time consuming. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller Sent: Friday, January 04, 2008 9:43 AM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] Large cover index slows things down 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 > > _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com