[dba-VB] vb.net - Updates

<<<
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