October 27, 2008 at 10:37 am
Hi All,
I was looking at one of the Stored procs that was behaving badly. It had around 13K page reads. Our DB is around 300 Gig. I was looking at each of the SQL that gets executed from that stored Proc. I found one SQL statement that does a Top 1. This Statement was having 6000 Page reads and the Table was empty. Why should it have that much page read when the table is empty? The Stats for the Table gets updated every night. Anyone has any idea?
Roy
-Roy
October 27, 2008 at 10:40 am
Can you post table structure, query and Statistics IO output for running the query? (SET STATISTICS IO ON)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2008 at 10:41 am
You need to post a bit of the code and plan.
October 27, 2008 at 10:54 am
GilaMonster (10/27/2008)
Can you post table structure, query and Statistics IO output for running the query? (SET STATISTICS IO ON)
I did that.
Table 'tbDataList'. Scan count 1, logical reads 6005, physical reads 0, read-ahead reads 0
tbDataList is a table with two columns.
ListIndex int Identity,
HostText varchar(50)
It does not have an Index. Basically because it will not have more than 5 rows. It gets cleaned up regularly.
The query is
Select top 1 Listindex, HostText from tbDataList
order by ListIndex asc
-Roy
October 27, 2008 at 10:58 am
And a SELECT count(*) FROM tbDataList returns 0?
Is it possible that there are two tables named tbDataList in different schemas?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2008 at 11:03 am
Yes, Select count(*) returns 0 rows. That too does 6000 page reads when excuted.. :angry: . In this DB we have only one schema.
I am kind of lost here. I dont know why this could happen.
-Roy
October 27, 2008 at 11:41 am
Are you sure that it's not a view or a table valued function?
Is there an INSTEAD OF trigger on the table (if it is a table)?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 27, 2008 at 11:55 am
It is just a normal table. And there are no triggers on it. It is very strange.
-Roy
October 27, 2008 at 12:18 pm
Can you perhaps query sys.dm_db_index_physical_stats for that table and post the results?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2008 at 12:37 pm
I did a Select * from sys.dm_db_index_physical_stats(DBID,ObjID,NULL,NULL,'LIMITED')
I got one row.
I cant copy it as it is since this system is not connected to the prodiuction network. But the values are like this
index_id = 0
partition_number = 1
index_type_desc = HEAP
alloc_unit_type_desc = IN_ROW_DATA
index_depth = 1
index_level = 0
avg_fragmentation_in_percent = 99.246
fragment_count = 659
avg_fragment_size_in_Pages = 7.9605
Page_count = 5246
And rest of the columns are NULL
-Roy
October 27, 2008 at 12:53 pm
Interesting. Despite having no rows, there are over 5000 pages that belong to the table. I would hazard a guess that they all have ghost records on them (marked as deleted) that haven't been cleaned up. Since the base table is a heap, it can't be rebuilt. The pages are part of the table and, since there's no index and the only way to read the table is to scan all the pages, all the pages must be read by the query processor to make sure that a row hasn't been added somewhere.
Can you stick a clustered index on the ListIndex column? It shouldn't add much, if any, overhead.
Edit: I have absolutely no idea how this could have happened. I'm going to see if I can get an answer from one of the storage engine gurus.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2008 at 1:00 pm
Ah Darn... Thanks a Lot GilaMonster. That did the trick. So these are Ghost records. That thought did not go into my mind at all.
Now the question is why does not get cleaned up?
-Roy
October 27, 2008 at 1:05 pm
Roy Ernest (10/27/2008)
Now the question is why does not get cleaned up?
If/when I find out, I'll be sure to let you know.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2008 at 1:10 pm
Thanks Gail Shaw. You helped me out here. Really appreciate it.
Roy
-Roy
October 27, 2008 at 1:13 pm
That's one of the reasons we always urge people to put a clustering index on every table !
( unless it is proven to really hurt performance 😉 )
It's a pain since sql7. These ghost rows are better managed when having a clustered index.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply