August 1, 2008 at 9:49 am
I have one performance related issue.One SP for report is taking longer than expected for execution.
It uses some other SPs and User defined functions inside.and performing 30 times union all operation
to fetch data of different criteria.I looked at the execution plans and i can see only some nested joins,table scans and index seek,RID Lookups.After looking at those tables used in table scan i found there are indexes but non-clusterd on columns(thats the design they are using..i m new in this place) used in where or join conditions.tables are also not containing large data all of them have around maximum of 500 rows so my question does it really helpful if i create clustered indexes on those tables?it has not much records.(these tables are not used in OLTP transactions much so i can create clustered index but will it make some difference in my execution time?)
August 1, 2008 at 10:11 am
By default PKs are clustered. Either way a clustered index is actually part of your base table which enforces the physical order in which data is stored. You can have just one clustered index per table.
Would a clustered index fix your issue? hard to say without seeing the queries and knowing your data structure.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 1, 2008 at 10:20 am
I understand that its hard to say without observing but its too long,calls lot of different Functions
And the default is clustered index on primary key But they have created nonclustered index on primary key columns for almost all the tables.So they have purposefully created that but those tables are not getting used in any OLTP transactions any more so I can change it but question is,”it will give some benefits ?? if tables don’t have only few hundreds or thousands rows”? Or its hard to tell??
August 1, 2008 at 10:33 am
Chances are query takes time because it's just a dog.
Is offending query performance getting worst over time or it just sucks from day one?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 1, 2008 at 10:43 am
it sucks from day-1 (so something is wrong in design,??)as well as getting even more worst as the time passes
August 1, 2008 at 10:45 am
I agree with Grasshopper that the query is the most likely cause, but I do have an additional question regarding the query plan generated by the SP.
Is the I/O originating from the Tables themselves, or from the Non-Clustered Indexes? You should be able to tell by looking at the titles of the Icons (Table Scan vs. Index Scan or Index Seek).
Ken
August 1, 2008 at 10:47 am
Would you consider to run a Server Side Trace and check where time is wasted?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 1, 2008 at 10:58 am
What I can see on title is Table scan and below that table name and cost is mentioned. Is that what r u asking?
And u want me to run just standard template fro trace or any custom?
August 1, 2008 at 10:59 am
If you have a test system available you could do the following (after restoring a backup of the prod database):
1. Drop non-clustered indexes.
2. Implement clustered PK indexes on involved tables.
3. Capture the estimated execution plan.
4. Test the responsiveness of the changes.
If the responsiveness improves, you could use this as a case to make clustered PK a table design standard at your shop.
It is possible that the non-clustered indexes were intended to cover certain T-SQL statements, but it sounds like they may be a sign of improper design.
Another question: Where is the majority (or plurality) of cost in the Estimated Execution Plan? Is there a particular table scan, index scan, or sort that accounts for most of the estimated cost?
I hope none of these suggestions seem condescending, and I hope this provides you with a bit of help.
Ken
August 1, 2008 at 11:07 am
ya thats a good idea and ya i can see table scan, index seek and RID lookups in my execution plans with query costs 77% the max
August 1, 2008 at 11:31 am
Is the 77% cost attributed to one particular table scan, index scan, or RID lookup?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply