Tunning T-SQL ?????? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Tunning T-SQL ??????

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2008-04-23 : 02:04:26

Hi
I m going through tunning part and fired query on table Code having 12500 rows

SET STATISTICS IO ON
GO
SELECT COUNT(*) FROM Code
GO
SET STATISTICS IO OFF
GO

sp_spaceused Code

got results as

Table 'Code'. Scan count 1, logical reads 60, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Name: Code
rows : 12500
reserved: 7424 KB
data : 6272 KB
index_size: 1008 KB
unused : 144 KB

If we creat table from select * into table syntax and then create PK on the table then why there is difference in results of index_size?

got results as

Table 'TestCode'. Scan count 1, logical reads 394, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Name: TestCode
rows : 12500
reserved: 3208 KB
data : 3136 KB
index_size: 16 KB
unused : 56 KB

here really index not playing part???

help me out

T.i.A

gwhiz
Yak Posting Veteran

78 Posts

Posted - 2008-04-23 : 15:14:22

Please post the DDL for both tables. My guess is that you either have additional indexes on the code table or for some reason the PK on the code table is not a clustered index and the one on testcode is.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-23 : 15:15:40

The answer is most likely fragmentation.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources