Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

Index creation help Expand / Collapse
Posted Monday, November 18, 2013 11:18 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 12:28 AM
Points: 43, Visits: 509
sandyinfowave (11/15/2013)
Thanks for your valuable suggestions...I didn't create a covered index as the number of columns in include statement are more( & table has around 40 million records), didn't want to spend too much time/resources in maintaining index's. I forced the query to use an already existing index(using with index statement) which prevents table scans. Is it advisable to force the query to use an index. I guess sql server should already choose best execution plan based on available indexes right??

I also see Hash Match's in my execution plan..What are these??

So analysing the elapsed time is the only option to decide whether an index is helpful??? (not sure how accurate it is as the SP can be cached)

P.S: elapsed time decreased considerably(almost decreased to 1/4th) after making these changes, but higher than what it used to be in the past(3-4 months ago when the query was running faster)...Not sure what changes happened but it forced me to analyse execution plan

These queries are used for our reporting purposes(to insert data into Reporting tables)

for me i always check the query to see if any devil in it, most of the time its the query, which causes the problem, As lynn described well that how include column can be overhead for the OTLP system, and this same goes to NC-Index as well (every Update, insert delete on the table has to the same with its respective column`s indexes as well if that column affect with the transaction),
so before jump into putting Index on your queries, always try to simplify that may be you are forcing it to make to choose the bad execulation plan,
always put index on the table after closely evaluate its overall impact on the server.

forcing the index helping u?
forcing a index can be bad i think may be its wrong cardinality estimations if it is helping u.

please post table schema and actual execution plan here may be WE can help.

read hash match here

performance issue:
(1) Have you update the statistics on all tables of underling query?
(2) Are you using index well on the tables?
(3) Can you simplify your Query by re-writing it .
(4) are indexes are Defragmented well ?

Post #1515432
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse