jwcolby
jwcolby at colbyconsulting.com
Thu May 1 07:10:05 CDT 2008
I have many indexes set up which include the PKID and one or more other fields. One would think that the optimizer would use one of those indexes instead of the clustered PKID index. I am thinking that a hint is in order to force SQL Server to use a different index. That is beyond my expertise however. John W. Colby www.ColbyConsulting.com Shamil Salakhetdinov wrote: > <<< > No matter how you slice it, it has to process > more physical disk sectors etc. > Hi John, > > Yes, but I thought MS SQL wouldn't need to scan the values of all data rows' > fields but it would be enough to scan index entries, and even maybe non-leaf > index pages' entries for the query returning every 1000th row - then the > difference I thought between the time to get results from 1+ million rows > data table and 90 millions wouldn't be that significant. It's obvious now > that that was my wrong assumption because clustered index has actual data > rows on its leaf-nodes: > > Clustered Index > http://msdn.microsoft.com/en-us/library/ms177443.aspx > > Non-Clustered Index > http://msdn.microsoft.com/en-us/library/ms177484.aspx > > > New hypothesis based on info on clustered and non-clustered index structure: > With *Non-clustered* index defined for your PKID the following query > performance should be significantly better. Although I'd not guess now how > much time it could take to get this query finished with your db: > > SELECT RowNum, PKID from ( select > Cast(ROW_NUMBER() over (ORDER BY [PKID]) as int) as RowNum, PKID > from [Names]) s > where (s.RowNum % 1000) = 1 > union all > Select NULL as RowNum, Max(PKID) as PKID from [Names] > > When there exists a non-clustered index together with clustered for the same > field and this field is used in a query as 'order by' or as selection > criteria then MS SQL uses non-clustered index as far as I see from query > execution plan... > > Here is how I did create non-clustered index to the test table called > [dbo].[Names]: > > if exists (select * from dbo.sysindexes where name = 'ALT_KEY_NamesParser') > DROP INDEX [ALT_KEY_NamesParser] ON [dbo].[Names] > GO > > CREATE UNIQUE NONCLUSTERED INDEX [ALT_KEY_NamesParser] ON [dbo].[Names] > ( > [PKID] ASC > ) > WITH (PAD_INDEX = OFF, > STATISTICS_NORECOMPUTE = OFF, > SORT_IN_TEMPDB = OFF, > IGNORE_DUP_KEY = OFF, > DROP_EXISTING = OFF, > ONLINE = OFF, > ALLOW_ROW_LOCKS = ON, > ALLOW_PAGE_LOCKS = ON) > ON [PRIMARY] > GO > > (BTW, did you ever try to create non-clustered indexes on non-PRIMARY > filegroups of an MS SQL 2000/2005 database? - another tip, which could > improve query performance, especially if to keep indexes' filegroups on > different HDDs - and the latter could be not backed-up because indexes can > be rebuilt anytime, and building non-clustered indexes should be non that > time consuming even for 90 million records DB? ) > > Please feel free to check or ignore the new hypothesis with your 90 million > db table... > > Thank you. > > -- > Shamil > > > > -----Original Message----- > From: dba-vb-bounces at databaseadvisors.com > [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby > Sent: Thursday, May 01, 2008 1:16 AM > To: Discussion concerning Visual Basic and related programming issues. > Subject: Re: [dba-VB] vb.net - Updates > > I do have the clustered stuff exactly as you show: > > > CONSTRAINT [PK_Names] PRIMARY KEY CLUSTERED > > ( > > [PKID] ASC > > ) > > WITH (PAD_INDEX = OFF, > > STATISTICS_NORECOMPUTE = OFF, > > IGNORE_DUP_KEY = OFF, > > ALLOW_ROW_LOCKS = ON, > > ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] > > ) ON [PRIMARY] > > Likewise I am getting the same 99% cost clustered index scan. > > I have to guess it is something else. There is a lot of empty space in > the database file which I will compact out tonight. It takes hours > because of the size and when it is doing that I cannot use the db. > Perhaps I have severe fragmentation or something. The database size is > 300 GIGS. And finally just remember that when all is said and done it > is still a hundred times larger than your database. No matter how you > slice it, it has to process more physical disk sectors etc. > > John W. Colby > www.ColbyConsulting.com > > > _______________________________________________ > dba-VB mailing list > dba-VB at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-vb > http://www.databaseadvisors.com > >