<\/P><\/p>\n
I am going to search for all animals that contain the word fly. We are going to look at how the query analyzer creates different cost based execution plans. <\/p>\n
For a given plan, we are going to look at the time in milliseconds (SET STATISTICS TIME<\/a>) and the io counts (SET STATISTICS IO<\/a>). <\/p>\n To get a good test, we should write dirty pages to disk (CHECKPOINT<\/a>), drop clean buffers from memory (DBCC DROPCLEANBUFFERS<\/a>) and free the procedure cache of any query plans (DBCC FREEPROCCAHE<\/a>). These commands should be completed before executing the query.<\/p>\n The sql snippet below does just that.<\/p>\n <\/P><\/p>\n Here is the query to select all records that have the word ‘fly’ in the name.<\/p>\n A FULL TABLE SCAN <\/a>is performed because no index was defined. The data is stored as a unordered HEAP<\/a> structure. This execution plan is very expensive when the table contains 1 Million rows.<\/p>\n We can also see 8 records returned in the result set after 1 scans and 2 logical reads.<\/p>\n Let us create a nonclustered index (CREATE INDEX<\/a>) on the [NAME] column. Hopefully, the query optimizer will use the index. The data is stored in NONCLUSTERED INDEX<\/a> structure in which leaf nodes are not data pages.<\/p>\n Oh no, we can see the same plan being used and the same execution results.<\/p>\n Lets drop the index and create a clustered index on [NAME]. Again, we are hoping the query optimizer will use the index. The data is stored in CLUSTERED INDEX<\/a> structure in which leaf nodes are data pages.<\/p>\n This time, we do get the query optimizer to use the index but it is not optimal since it is a FULL CLUSTERED INDEX SCAN. Again, this is very expensive plan when the table contains alot of rows. A SEEK<\/a> would be a better operation.<\/p>\n The main issue with this query is that we are looking for a pattern in a string. Indexing basically orders (logical or physical) the data given a sort collation. However, we are searching for part of a word. Thus, every [NAME] field needs to be examined to look for the ‘fly’ pattern.<\/p>\n The SQL snippet below turns off the messaging for io and time since we are done for now.<\/p>\n Next time, I am going to introduce setting up a full-text index<\/a> using the GUI. Full-text indexes are great for searching on a single word or phrase (and optionally ranking the result set), searching on a word or phrase close to another word or phrase, and\/or searching on synonymous forms of a specific word.<\/p>\n While this speeds up our searching, we will see that it does not completely solve the problem at hand.<\/p>\n-- Show time & i\/o\r\nSET STATISTICS TIME ON\r\nSET STATISTICS IO ON\r\nGO\r\n\r\n-- Remove clean buffers & clear plan cache\r\nCHECKPOINT \r\nDBCC DROPCLEANBUFFERS \r\nDBCC FREEPROCCACHE\r\nGO\r\n<\/span><\/pre>\n-- Select everything with word 'fly'\r\nSELECT * FROM dbo.ANIMALS WHERE NAME LIKE '%FLY%'\r\nGO\r\n<\/span><\/pre>\n<\/p>\n![\"\"](\"https:\/\/craftydba.com\/wp-content\/uploads\/2012\/01\/full-table-scan.jpg\" "\\"full-table-scan\\"")
<\/a><\/p>\n![\"\"](\"https:\/\/craftydba.com\/wp-content\/uploads\/2012\/01\/first-time-n-io.jpg\" "\\"first-time-n-io\\"")
<\/a><\/p>\n-- Add index on name (non-clustered)\r\nCREATE NONCLUSTERED INDEX IDX_ANIMAL_NAME ON DBO.ANIMALS(NAME);\r\nGO\r\n\r\n-- Select everything with word 'fly'\r\nSELECT * FROM dbo.ANIMALS WHERE NAME LIKE '%FLY%'\r\nGO\r\n\r\n-- Drop index on name\r\nDROP INDEX ANIMALS.IDX_ANIMAL_NAME;\r\nGO\r\n<\/span><\/pre>\n<\/p>\n-- Add index on name (clustered)\r\nCREATE CLUSTERED INDEX IDX_ANIMAL_NAME ON DBO.ANIMALS(NAME);\r\nGO\r\n\r\n-- Select everything with word 'fly'\r\nSELECT * FROM dbo.ANIMALS WHERE NAME LIKE '%FLY%'\r\nGO\r\n\r\n-- Drop index on name\r\nDROP INDEX ANIMALS.IDX_ANIMAL_NAME;\r\nGO\r\n<\/span><\/pre>\n<\/p>\n![\"\"](\"https:\/\/craftydba.com\/wp-content\/uploads\/2012\/01\/clustered-index-scan.jpg\" "\\"clustered-index-scan\\"")
<\/a><\/p>\n-- Hide time & i\/o\r\nSET STATISTICS TIME OFF\r\nSET STATISTICS IO OFF\r\nGO\r\n<\/span><\/pre>\n<\/p>\n