Question on Statistics/Performance

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

Transact-SQL (2005)

Question on Statistics/Performance

Author

Topic

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-03-11 : 10:06:26

Hey all,

I am relatively new to this whole optimization gig, in fact this is my first real dive into it. I read a lot of the stuff on this forum and find much of it extremely useful.

I remember from all my reading and such that logical reads are 1 way to judge how efficient a query was/is. So as a quick preliminary test I ran some stats on a table I have, no indexes or anything.

Table 1 has 4608 rows and was imported by myself just to get some data in a table.

I then used the following code to duplicate the table:

Select * INTO t2 from t1

I figured all would be the same.

Then I ran

Select * from t1

Select * from t2

My results came back as such...

(4608 row(s) affected)
Table 't1'. Scan count 1, logical reads 1841, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(4608 row(s) affected)
Table 't2'. Scan count 1, logical reads 66, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

When you use the INTO function does SQL optimize this table in any special way? Could someone explain why I am getting two different reports for essentially duplicate tables?

Thanks!

shebert
Yak Posting Veteran

85 Posts

Posted - 2010-03-11 : 10:51:16

Hello
are you sure both tables have the same data types for columns.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-03-11 : 10:57:07

One word: Fragmentation

When you create the new table, the pages will be full. The old table could have partially-full pages, hence more pages for the same data. Check sys.dm_db_index_physical_stats.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-03-11 : 11:19:25

You can fix T1 by rebuilding its indexes (assuming it has some!)

But nonetheless the "Scan count" is 1 (efficient) and if you alter the query / try adding indexes, and it reduces the LOGICAL READS you have most probably made an improvement.

But ... before doing any optimisation it is best to make sure indexes are freshly rebuilt and run UPDATE STATISTICS .. WITH FULLSCAN so that you are sure that the query optimiser has got the best information at its fingertips

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-03-11 : 14:08:56

Thanks a ton all for your information and insight, apprecitate it :)

T2 had no indexes at all either, so it must of had to do with fragmentation. Again, the table was imported from an Excel file and modified only with a few update/Alter queries.

Thanks again!

Kristen
Test

22859 Posts

Posted - 2010-03-11 : 14:31:34

"T2 had no indexes at all either"

SELECT * INTO xxx FROM yyy

should create the new table XXX with the same primary key (index) as yyy - but it would not create any other, non-PK, indexes.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-03-11 : 14:35:24

No. Select into creates a table. Nothing more. Defaults, constraints, indexes, etc you have to add on manually afterwards

--
Gail Shaw
SQL Server MVP

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-03-11 : 14:43:06

Well it should not matter in this case because T1 was just an arbitrary data set with no PK, FK, or indices either.

All I did was upload a table (do nothing to it) select it into another...and the number of reads was different :)

Kristen
Test

22859 Posts

Posted - 2010-03-11 : 15:42:07

quote:
Originally posted by GilaMonster

No. Select into creates a table. Nothing more. Defaults, constraints, indexes, etc you have to add on manually afterwards

Sorry, my mistake. I was thinking of the IDENTITY property that SELECT ... INTO duplicates. That is, indeed, "all you get" ...

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-03-11 : 17:35:29

You said that did nothing to it, but earlier you said you'd done some updates/Alter. Which is it?

--
Gail Shaw
SQL Server MVP

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-03-12 : 10:43:33

I did updates/alters to t1 before ever copying it to t2.

So t2 should be an exact replica of t1.

Process:
1. Import Table t1
2. Update/Alter Table t1
3. Select * INTO t2 from t1
4. Run both and check stats, they are, in fact, different.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-03-12 : 13:36:51

Those updates/alter could very well have cause page splits (depending what you're doing) and so t1 would have more pages in it than necessary.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources