Performance Tuning - Adding Index

Question

Performance Tuning - Adding Index

ramana3327

SSCoach

Points: 19722
More actions
June 3, 2015 at 11:15 am

#308717

Hi,
I am working application performance.
I created a index for one of select statement. It is reducing the execution time more than a min but
How can we say the newly index is not harming to other stuff?

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61152 More actions · Answer 1

ramana3327 (6/3/2015)
Hi,
I am working application performance.
I created a index for one of select statement. It is reducing the execution time more than a min but
How can we say the newly index is not harming to other stuff?

It depends on what you mean by "harm" and "other stuff?". If your query is using that index and is now one minute faster that is probably a good thing. To understand the impact to your server, let's look at the pros and cons of adding an index:

Pros:

Can lead to more efficient query plans

Can increase query performance by reducing I/O, Memory usage, CPU time

Cons:

Adds overhead when modifying the data in that table because it's another thing must be kept current by your SQL instance

Uses up disk space

You can use DMVs/DMFs and Extended Events to better understand the impact (positive and negative) that an index is having on your SQL Instance/Server.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

ramana3327 SSCoach Points: 19722 More actions · Answer 2

It is reducing the execution time but in the execution plan the new index is not showing anywhere. How can we say the index is using or not while it is in testing mode

Sean Lange SSC Guru Points: 286609 More actions · Answer 3

ramana3327 (6/3/2015)
It is reducing the execution time but in the execution plan the new index is not showing anywhere. How can we say the index is using or not while it is in testing mode

You need to better understand what you are seeing in your execution plans. I would highly recommend this article and the book that goes along with it. https://www.simple-talk.com/sql/performance/execution-plan-basics/%5B/url%5D

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 4

Index works like a double edged sword. It helps in retrieval operation when you run SELECT but at the same time it increases time if you are trying to insert/update/delete records from the table where you have applied index.

So think that instead of creating multiple indexes it will be good to have one index with "cover", so to my understanding a covering index works better. Again, situation differs case to case.

Thanks.

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 5

ramana3327 (6/3/2015)
It is reducing the execution time but in the execution plan the new index is not showing anywhere. How can we say the index is using or not while it is in testing mode

If it's not in the execution plan, it's still possible that the stats made by the index are being evaluated to come up with a better plan, but the index itself isn't being used. This is also a fairly rare situation.

Are you sure that the index is actually helping or are you just seeing an improvement due to caching?

I'll also add that there is always a price to pay for every index because, except for the Leaf Level of the clustered index, all indexes are nothing more than a duplication of data with its own B-Tree that will affect all INSERTs and DELETEs and some UPDATEs. To be sure it will also affect memory usage (sometimes in a positive manner) and will always make backups and restores be a little bigger and take a little longer not to mention the potential of causing longer index maintenance along with the related log activity unless you use minimally log index maintenance techniques.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ramana3327 SSCoach Points: 19722 More actions · Answer 6

I use the query

SELECT [text], cp.size_in_bytes, plan_handle

FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

WHERE cp.cacheobjtype = N'Compiled Plan'

AND cp.objtype = N'Adhoc'

AND cp.usecounts = 1

ORDER BY cp.size_in_bytes DESC;

DBCC FREEPROCCACHE(plan_handle)

and clear the cache. Then I run the query but still in the execution plan I didn't find but from sys.dm_physicalstats, I can see one index seek on the newly created index