Performance not same for all tables.

Question

Hi, when i try to do a count from 2 tables in same filegroup the performance differs a lot.  Both have around 200 million rows.  One comes back within 5 secs but the other takes 5 min.  Any ideas as to whhy this would be?  Both have same kind of indexes and are in primary filegroup.

Answer 1

Can you post tables definitions and the query you're trying?

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 2

Run this while putting the indexes on the tables in the IN ('')

SELECT OBJECT_NAME(OBJECT_ID) AS Tablename,s.name AS Indexname
,index_type_desc
,avg_fragmentation_in_percent
,page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') d
join sysindexes s ON d.OBJECT_ID = s.id
and d.index_id = s.indid
and s.name IN ('IX_TestIndex_Index')

Reference: http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/finding-fragmentation-of-an-index-and-fi

---

Ted Krueger Blog on lessthandot.com @onpnt on twitter