Clustered Index Scan 3

[gmmastros]

I have a stored procedure that I am trying to improve performance on.

ALTER PROCEDURE dbo.MapGetStreetsForGrid  
		@GridNumber Integer  
As  
SET NOCOUNT ON  
Select	MapStreetsGridFeatures.FeatureId,  
	MapStreets.Label, 
	MapStreetsPoints.Longitude,
	MapStreetsPoints.Latitude
From	MapStreetsGridFeatures  
	Inner Join MapStreets On MapStreetsGridFeatures.FeatureId = MapStreets.FeatureId  
	Inner Join MapStreetsPoints On MapStreetsGridFeatures.FeatureId = MapStreetsPoints.FeatureId  
Where 	MapStreetsGridFeatures.GridNumber = @GridNumber  
Order By MapStreetsGridFeatures.GridNumber, MapStreetsGridFeatures.FeatureId, PointNumber

62% of the time is MapStreets.PK_MapStreets. FeatureId is the primary key for the MapStreets table. I've made this a clustered index. For some reason, it is doing a scan instead of a seek.

I have the same problem with the MapStreetsPoints table. FeatureId and Point Number are the primary key for this table. This step takes 21% of the execution time.

The execution plan looks like.

A <- B <- C <- D <- E
            ^     ^
            |     |- F
            |
            |- G

A = Select 0%
B = Sort 2%
C = Merge Join/Inner Join 11%
D = Merg Join/Inner Join 4%
E = MapStreetsGridFeatures - Index Seek 0%
F = MapStreets.PK_MapStreets - Clustered Index Scan 62%
G = MapStreetsPoints.MapStreetsPoints_FeatureId - Clustered Index Scan 21%

What can I do to make this any faster?
Am I correct in assuming that seeks are faster than scans.





-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[JayKusch]

How many records are in the tables. If there is a small set of records, Query Analyzer may forego the indexes and do a scan by it own choice.

Thanks

J. Kusch

 

[gmmastros]

Typically, this SP returns approximately 7000 records.

MapStreetsGridFeatures: 375,290
MapStreets: 362,229
MapStreetsPoints: 1,053,925




-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mrdenny]

Do a dbcc showcontig on the offending tables and post the results. Perhaps the indexes have become overly fragmented and it's the server thinks it's better to scan rather than seek.

You can also try updating the stastics on the tables. If they get stale it can make the query plan generator do stupid things.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[vongrunt]

Any index on MapStreetsGridFeatures.GridNumber?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[gmmastros]

mrdenny -- With your help, I noticed that one of the indexes was fragmented. I did dbcc indexdefrag. It helped a little. Now that step is 60% instead of 62%.

vongrunt -- There is an index on MapStreetsGridFeatures.GridNumber

It's still doing an index scan instead of seek.

I also noticed that I was sorting first by gridnumber eventhough I was filtering on Gridnumber. Removing that sort didn't have any effect.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mrdenny]

Out of curisoty, if you do a query against the MapStreets table does it do a scan of a seek?

Did you update the stats for the tables?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[gmmastros]

I *think* I updated the statistics. In the "Execution Plan", I right clicked the tables, Clicked "Manage Statistics". Clicked Update. Put a check in all the "Statistics to update". I Selected "Amount Of Data To sample" = Sample All Data.

I think this is what you meant by updating the statistics.

If I run...

Select FeatureId From MapStreets Order By FeatureId

The execution plan shows a Clustered Index Scan.

There are only 2 indexes on this table.

CREATE UNIQUE CLUSTERED
INDEX [PK_MapStreets] ON [dbo].[MapStreets] ([FeatureId])
WITH
DROP_EXISTING
ON [PRIMARY]

and

CREATE
INDEX [MapStreets_MajorClassification] ON [dbo].[MapStreets] ([MajorClassification])
WITH
DROP_EXISTING
ON [PRIMARY]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

Hm... what are row/size estimates for each step (mouse over arrows to see that)?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[gmmastros]

Table                    RowCount  Est Row Size Est Row Cnt
MapStreetsGridFeatures   2327      35           2327
MapStreets               285984    198          362229
MapStreetsPoints         839870    45           1053925

Merge Join/Inner Join    2327      71           2327
Merge Join/Inner Join    5221      90           6770

Sort                     5221      63           6770

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

Looks like estimated row cnt for MapStreets missed a lot...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[mrdenny]

That probably updated the stats. I normally use the update stastics T/SQL command in query analyzer.

If you do a select with a where clause on the MapStreets table (select * from MapStreets where FeatureID = 'SomeValidValue') does it do a scan or seek?

Mabey the indexes need to be rebuilt?
Mabey the SQL Server wan't the Clustered index removed from the Primary key. You can try removing the clustered part of the index, and making a seperate clustered index on the FeatureID column.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[gmmastros]

I notice that MapStreetsGridFeatures has a smaller number for Estimated Row Count than the number of records in the table.

For the other 2 tables, the extimated row count = number of rows in the table.

Is there a way to "fix" this?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

mrdenny, When I execute the following query

Select * from MapStreets Where FeatureId = 3948

The execution plan shows a Clustered Index Seek.

I'll try removing the clustered part of the index.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

mrdenny, it wasn't easy to remove the 'clustered' part of the index, but I did. The index was actually the Primary Key. You can't edit an index to remove the 'Clustered' part, you have to drop the index and re-create it. Since this was a primary key, you can't simply drop the index. When I dropped the primary key constraint and then re-added it, the index that it create automatically was set for clustered. So, I dropped again, create another 'temp' index that was clustered, then added the primary key constraint (now it wasn't clustered), then dropped the temp index.

Suffice to say, I now have both tables (MapStreets and MapStreetsPoints) with primary key indexes that are not clustered.

When I excute the SP now, it's doing table scans.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Catadmin]

ACK! Table Scans are worse than Index Scans. For the record, even though the documentation doesn't come right out and state it, Primary Keys are essentially Clustered Indexes. Since you can only have 1 Clustered Index per table, you might as well restore your PKs to the tables.

Were the PKs the only indexes you had on these two tables? If so, try re-creating the PK, then create a non-clustered combined index on the other columns. Sometimes multiple indexes help and sometimes they hinder.

Also, have you tried using hints in your code yet? Looking up "Index Seek" in BOL mentions something about an operatior that uses a Seek) predicate. It doesn't give much detail, but take a look at it and see if you can pull something out of it that I can't.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[gmmastros]

Catadmin,

Thanks for the reply. Yesterday, I was looking at the 'hints' business. BOL does not make it clear how to use them. After some digging, fighting, screaming (and perhaps a little cursing), I finally got the hints to work.

This is what the SP finally looks like.

ALTER   PROCEDURE dbo.MapGetStreetsForGrid  
		@GridNumber Integer  
As  
SET NOCOUNT ON  

Select	MSP.FeatureId,  
		MS.Label, 
		MSP.Longitude,
		MSP.Latitude
From	MapStreetsPoints MSP With(INDEX(PK_MapStreetsPoints))
		Inner Join MapStreets MS With(Index(PK_MapStreets)) On MSP.FeatureId = MS.FeatureId
		Inner Join MapStreetsGridFeatures MSGF on MSP.FeatureId = MSGF.FeatureId
Where 	MSGF.GridNumber = @GridNumber  
Order By MS.FeatureId, 
		MSP.PointNumber

Notice the With(Index(blah)) part in the from clause. Now, everything is using Index Seek. The sp now takes 1/3 of the time it used to.

1 sp down, 30 to go.

I really appreciate all the help. Unless anyone knows of a reason why hints are bad, I will continue to use them.

Again, thank you very much.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Catadmin]

AFAIK, the only reason hints could be bad is when you take a fast query, add hints and cause it to slow down because the execution plan you're using is worse than the one SQL Server is using.

In this particular case, I don't think you'll be having a problem. @=)



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[vongrunt]

> Notice the With(Index(blah)) part in the from clause. Now, everything is using Index Seek. The sp now takes 1/3 of the time it used to.

What happened with the rest of exec plan? Did you get additional sort operator... or one merge join changed to hash join?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[gmmastros]

The execution plan now looks like...

A --- B --- C --- D --- E --- F
               |           |
               |            - G
                - H

A = Select (0%)
B = Bookmark Lookup (56%)
C = Nested Loops/Inner Join (0%)
D = Bookmark Lookup (18%)
E = Nested Loops/Inner Join (0%)
F = MapStreetsGridFeatures Index Seek (0%)
G = MapStreets Index Seek (6%)
H = MapStreetsPoints Index Seek (19%)

F Shows RowCount = 2327, Estimated Row Count = 2327
G Shows RowCount = 2327, Estimated Row Count = 1
H Shows RowCount = 5221, Estimated Row Count = 2

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom