Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

  1. Id (int) - Primary key which auto generates the number.
  2. FileName (nvarchar(MAX)) - Contains a file name.
  3. 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.

share|improve this question

migrated from stackoverflow.com Feb 13 '12 at 22:07

This question came from our site for professional and enthusiast programmers.

1  
It depends on your data. If most of your rows have Filename with 'readme.txt' SQLServer will still perform a Table-/ClusteredIndex scan (if you have a ClusteredIndex). I think if sql server reads 30% of the data, its faster for him to continue sequential reading and decides to read everything. Edit: I just saw that you are using nvarchar(max) which cannot be indexed (Sorry, cannot find msdn page) –  Bernhard Kircher Aug 17 '11 at 10:05
4  
do you really need to support unicode filenames and descriptions? and varchar(max) is too wide. a filename is 255 chars max –  Mitch Wheat Aug 17 '11 at 10:06
    
JNK pointed out very interesting thing, how was you able to create index on VARCHAR(MAX), anyway try out VARCHAR(N) where N some adequate length for file name like 256 –  sll Aug 17 '11 at 10:27
    
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 and DBCC DROPCLEANBUFFERS before any query execution) –  marc_s Aug 17 '11 at 11:18

2 Answers 2

up vote 6 down vote accepted

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 and DBCC 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 be VARCHAR(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:

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS 

SET STATISTICS IO ON

SELECT * 
FROM testfiles
WHERE FileName = 'File-D8584B44-518F-428A-86A1-7836E0B60502'
share|improve this answer
    
+1 - It's probably caching related. –  JNK Aug 17 '11 at 11:56
    
From suggestions, I've tried DBCC FREESESSIONCACHE, DBCC FREESYSTEMCACHE ('ALL','default'), DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS. However, the only one that seems to reset the cache properly for performance comparison is the DROPCLEANBUFFERS one. Thanks for your help. –  millie Aug 17 '11 at 13:37

I'm pretty sure you're leaving something out:

(n)varchar(max) fields CANNOT BE INDEXED

From MSDN:

Columns that are of the large object (LOB) data types ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be specified as key columns for an index. Also, a view definition cannot include ntext, text, or image columns, even if they are not referenced in the CREATE INDEX statement.

Did you get an error when you tried to create the index?

Both your queries are the same because they are the same - since you can't index that field, they are both creating table scans.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.