full scans in 2005 - 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

Transact-SQL (2005)

full scans in 2005

Author

Topic

mjschwenger
Starting Member

10 Posts

Posted - 2007-07-26 : 15:42:31

I have a problem with a query that performs full table scans on SLQ server 2005 standard/Win 2003 Server, 64b. The same query behaves differently on the same schema on SQL server 2000 – no FTS. This I strange to me because most of the field participating in this query are index (because of their usage throughout that application)
The problem is not when I use these view alone but when I call them from other views/or join in a query.
Indexes on sc.conf_ID1, sc.conf_ID2, main_id, sc.CONF_dt, sc.CONF_id, es.mm_id, sc2.test_id

Here is the base statement:
create view VIS_0DEGREE_RECENT
select sc.conf_ID1, sc.conf_ID2, sc.CONF_id, sc.test_id, sc.CONF_dt
from sep_CONF sc ,main_score es
WHERE sc.conf_ID1 = es.mm_id and
sc.conf_ID2 = sc.conf_ID1 and
sc.conf_ID2 = es.mm_id
and sc.test_id = es.main_id
and sc.CONF_id =
(select max(sc2.CONF_id)
from sep_CONF sc2 , main_score es2
where c2.conf_ID1 = es2.mm_id and
sc2.conf_ID2 = sc2.conf_ID1 and
sc2.test_id = es2.main_id and
sc2.conf_ID2 = es.mm_id )

I also tried 2 views for better performance:
create view VIS_0DEGREE_EXISTS as
select sc.conf_ID1, sc.conf_ID2, sc.CONF_id, sc.test_id, sc.CONF_dt
from sep_CONF sc where exists (select 1 from main_score es
WHERE sc.conf_ID1 = es.mm_id and
sc.conf_ID2 = sc.conf_ID1
and sc.test_id = es.main_id)

create view VIS_0DEGREE_RECENT as
select * from VIS_0DEGREE_EXISTS ve
where ve.CONF_id = (select max(ve2.CONF_id) from VIS_0DEGREE_EXISTS ve2
where ve.conf_ID1 = ve2.conf_ID1 and
ve.conf_ID2 = ve2.conf_ID2)

No problems here – uses indexes:

Select * from VIS_0DEGREE_RECENT where CONF_id = 1000 and mm_id = 1000
Select * from VIS_0DEGREE_RECENT where conf_ID1=1000 and conf_ID2 = 1005

Problem query - FTS on sep_CONF twice because of the case statement...
In SQL server 2000, it uses the index on CONF_id and conf_ID1.
Same setup , same data.
select CONF_ID, CONF_ID1,CONF_ID2,DEG_OF_SEP,CONF_DT,CONF_STAT,
(case
when sc.DEG_OF_SEP = 0 and not exists (select 1 from main_score es
WHERE sc.test_id = es.main_id and es.mm_id = sc.conf_ID1)
then ( select es.main_id from main_score es
where es.main_id = (select ve.test_id
from VIS_0DEGREE_RECENT ve
where ve.conf_ID1 = sc.conf_ID1
and ve.conf_ID2 = sc.conf_ID2)
and es.mm_id = sc.conf_ID1)
else (test_id) END) as TEST_ID
from sep_CONF sc

What can I do to stop the FTS? Also, I see in the plan - INDEX POOL? how exactly to understand that in my case?
Thanks a lot, mj

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-26 : 15:45:43

well it's using index scan because you're scanning the whole table.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

mjschwenger
Starting Member

10 Posts

Posted - 2007-07-26 : 16:37:40

I'm sorry, I was not specific in my post - there is a FTS from the last "from sep_CONF sc" whic is expected.
In the plan tehre's a second FTS comming for the usage of the VIEW -
"where es.main_id = (select ve.test_id
from VIS_0DEGREE_RECENT ve "
If I remove this part of the code, then the second FTS disappear.
What is that?
Thanks a lot, mj

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-26 : 16:39:58

have you updated the statisctics or reindexed the database after moving to sql 2005?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

mjschwenger
Starting Member

10 Posts

Posted - 2007-07-26 : 17:06:37

I sure did update the stats.
I have a similar problem with other queries.
Let's say I have an index on col1, col2, col3,cl4.
The query uses columns 1,2 and 4 and SQl perform table scans. If I change the index to have col1,2,4 and then 3 - then it uses it.
It makes no sense to me - may be my database is just bad - what can I do to make it work?
Thanks a lot ,mj

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-07-26 : 19:26:25

Is the problem that it does a table scan or that it runs slowly? If the former then why do you care? If the latter, then I wonder if you're seeing what we saw when we upgraded. We had some issues with 2005 when the machine has too much resources. The optimiser kept getting it wong and used the wrong query plan. We took away a few GB of RAM and it started working properly! Bizzarre.

mjschwenger
Starting Member

10 Posts

Posted - 2007-07-28 : 12:58:05

Thanks a lot for the advice. The reason for checking thi sout was the extensive time this query was taking on the 2005 instance compare to 2000. Investigating the plan, it looks that the problem is due to the index spool/eager spool that 2005 uses versus lookup bookmark in 2000. I also found the problem piece of code9select max(sc2.CONF_id) ) and rewrote it, but I still get FTS on the table.
Any other idea on how to optimize the spool? It looks that there's no way to influence the optimizer on that?
Thanks a lot,mj

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-28 : 13:39:23

You may need recreate indexes in sql2k5 since optimizer works different from sql2k.

mjschwenger
Starting Member

10 Posts

Posted - 2007-07-29 : 21:32:06

I did drop and recreate the indexes on all tables affected in the queries.I also tried separate to the rebuild indexes using Maintenance plan utility - no difference with both cases.
I think there's something wrong with the tempdb in 2005 but cannot figure out what.
Thanks a lot, mj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources