[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

Estimate the effectiveness of an index in SQL.

I have been advised that adding an index to a table in SQL Server will increase performance in my database when it is queried. I have no doubt that this is true, but I would like to know a way to prove the effectiveness of the index - whether this be a before and after report... I want a way of documenting the effectiveness of an index. Any ideas?

I have tried using a query execution plan, but the results are identical before and after
0
Blowfelt82
Asked:
Blowfelt82
1 Solution
 
AndyAinscowCommented:
Many years ago I made a table filled with many millions of records and a query to filter records.
Then I ran the query.
When the field was NOT indexed it took it a couple of minutes.  
When the field was indexed it took less than one second.
0
 
mcs0506Commented:
Hi,
I also tested index performance on that table contains records in millions and there also that table contain recursive foreign key so when i executing query with some self join make much time but after implementing index it shows difference in fetch of data with less time.  Try to use index on that table which have records in millions and the probability of change (Insert,Update etc.) in table data is less because such type of operations takes more time because of indexing.
Here are some useful links
http://odetocode.com/code/237.aspx
http://www.sqllion.com/2009/07/sql-server-indexes-pros-and-cons-part-1/
http://www.sqllion.com/2009/07/sql-server-indexes-pros-and-cons-part-2/
http://www.sqllion.com/2009/07/sql-server-indexes-pros-and-cons-part-3/


Regards,
Dani
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now