October 1, 2013 at 5:33 am
Hi
We have a SP which is giving performance issues. As an example it has been executed 4 times and has an total elapsed time of 68961914. It is the costliest procedure in terms of Worker time. By comparison other have ran 18877 times with a total time of 358513681ms, and 261 times with a total time of 11155273. I know the comparisons are not really helpful without giving you more info about each SP but its just a guide.
I have ran an index tuning query and it has recommended an index on the table the Sp uses. The index it recommends is an uniqueidentifier. The table currently has one non-clustered index with read ratio of 180 to a write ratio of 2365.
Is it likely that adding this index will aid in the performance of this Sp ? Also should i got for a clustered index or non clustered ? I realise that adding a clustered index will have a performance hit while it re-organises the data in the table.
October 1, 2013 at 6:03 am
We have a SP which is giving performance issues. As an example it has been executed 4 times and has an total elapsed time of 68961914. It is the costliest procedure in terms of Worker time. By comparison other have ran 18877 times with a total time of 358513681ms, and 261 times with a total time of 11155273. I know the comparisons are not really helpful without giving you more info about each SP but its just a guide.
Without knowing the query, this really doesn't mean a lot. One query can have lots of joins, cursors etc and access billions of rows while the other does a simple select on a small table.
I have ran an index tuning query and it has recommended an index on the table the Sp uses. The index it recommends is an uniqueidentifier. The table currently has one non-clustered index with read ratio of 180 to a write ratio of 2365.
Is it likely that adding this index will aid in the performance of this Sp ?
If the Tuning Advisor recommends this index it's very likely that this will improve performance. But if this is your best option again depends on your query and the table design.
Also should i got for a clustered index or non clustered ?
Ususally I wouldn't recommend a clustered index on a uniqueidentifier column. Not only do you get a rather wide index key (16 byte) but also a lot of fragmentation when inserting new records. But if your data is rather stable and you don't have a lot of other non-clustered indexes it may be an option.
But like I said before, without seeing your table design and the query, it's hard to tell what is the best solution.
[font="Verdana"]Markus Bohse[/font]
October 1, 2013 at 6:38 am
Test it and see. You have to do that with anything tuning advisor suggests. Can't say a thing more without seeing query, table structures and indexes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply