Logical Reads - 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 2000 Forums

SQL Server Development (2000)

Logical Reads

Author

Topic

ramdas
Posting Yak Master

181 Posts

Posted - 2003-03-18 : 10:45:42

Hi folks,
I have a query which does a SELECT columns(a,b,c...) from
FROM dbo.tblCallCenterXref x
INNER JOIN tblCenterHierarchyWithIDs h ON x.call_center_id = h.call_center_id
INNER JOIN parent_business_group e ON x.business_group_id = e.business_group_id
INNER JOIN tbl_agents_daily a ON h.agent_logon_id = a.agent_logon_id AND x.ip_id = a.ip_id

call_center_id clustered index
business_group_id non-clustered index
ip_id non-clustered index
There are indexes on the columns which are joined. I get a huge Scan count 5425, logical reads 11214 on the table tblCallCenterXref. Why would this be? This table has only 89 rows.

Ramdas Narayanan
SQL Server DBA

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-18 : 11:16:13

Do you have indexes on both tables?

How many rows in each table?

If tblCallCenterXref only has 89 rows, I belive it will be scanned. More likely the huge scans are from the other tables (leading me to belive that they don't have indexes on the join columns).

Brett

8-)

ramdas
Posting Yak Master

181 Posts

Posted - 2003-03-18 : 19:49:21

tblCenterHierarchyWithIDs
parent_business_group
tbl_agents_daily
All of the above tables have lots of rows over 10,0000, the join columns are indexed as well.

Any thoughts.

Ramdas Narayanan
SQL Server DBA

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-20 : 11:30:54

I would imagine the optimizer would do an index seek. You have indexes on:

Table: tblCallCenterXref, 1 each for (although for 98 rows you won't need them now):
call_center_id
business_group_id
ip_id

Table: tblCenterHierarchyWithIDs
call_center_id
agent_logon_id

Table: parent_business_group
business_group_id

Table: tbl_agents_daily
agent_logon_id
ip_id

If you do and it's still scann tens of thousands of rows, maybe you can force the index.

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources