I want to see the performance differences between an indexed vs non-indexed column when running the same query.
The table I've created (FileCollection) is very simple and has the following 3 columns
- Id (int) – Primary key which auto generates the number.
- FileName (
nvarchar(MAX))
– Contains a file name. - Description (
nvarchar(MAX))
– Contains a description of the file.
The query I'm using is:
SELECT * FROM FileCollection WHERE FileName='readme.txt'
I've populated the database with more than 100k records. Using the SQL Server Profiler
tool, I initially ran the query with no index on the FileName column and again when adding the index to FileName column. However, I cannot see any difference in performance.
Both tests average around the same stats:
CPU: 500
Reads: 13,000
Writes: 0
Duration: 200
Can anyone help me figure out why? I would assume 100k records is enough to show some difference but do I need more records?
TIA
Edit:
I've now changed the FileName column to nvarchar(255) but still the problem exists. BTW, I'm using SQL Server 2008 R2.
Best Answer
Depending on the amount of RAM your server has, it might just read the whole table into memory and keep it there - so your first query will load it, and any subsequent queries will just operate on those pages loaded in memory.
To get any meaningful data, you need to flush the cache before every run of your query! (run
DBCC FREEPROCCACHE
andDBCC DROPCLEANBUFFERS
before any query execution!)I tried your scenario with 100'000 dummy file names and almost the same table structure (I changed
FileName
to beVARCHAR(260)
).Without index:
Table 'TestFiles'. Scan count 1, logical reads 2137, physical reads 5, read-ahead reads 2136, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Query Subtree Cost 1.68884
With non-clustered index on filename:
Table 'TestFiles'. Scan count 1, logical reads 6, physical reads 4, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Query Subtree Cost 0.0065704
This was done with this statement: