January 26, 2011 at 2:21 pm
I have a stored procedure which performs a search operation.It takes 1 minutes or more for execution.When there are more users logged in it causes the thread to stuck.
Please provide help as to how to improve the performance.
There are 5 tables used in the proc.All of them have index.There is very little of subquery in it.The same kind of joins are used in other procedures as well,they all run fine,dont know hwta issues there?
Please suggest methods for improving its performance.I am a beginner and dont have much knowledge.
January 26, 2011 at 2:28 pm
Help us to help you.
Please post table definition(s), the T-SQL statement, sample data and the desired results using the sample data. For ease in doing this please click on the both links in my signature block.
January 27, 2011 at 11:09 am
In addition to what the BitBucket asked for, please post the actual execution plans. Since we're not sitting in your chair, it's as close as we can get to seeing what you see.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 27, 2011 at 1:38 pm
Thanks for your reply.
I am a begineer .
I tried to check the actual execution plan.
There is an index seek on Table A which having cost as 37%.There are other parameters as
predicate,seek predicate,object,output list which I am not able to understand.It would be of help if you could let me know what they are.
Also this index seek is having the maximum cost of 37%.Is this the cause for slow performance?
There is another Clustered index scan on table b which is having cost 20%.
Your help would be very useful for me.
Thanks in advance
January 27, 2011 at 1:44 pm
Please post the stored procedure, the table definitions and the actual execution plan. See http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
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
January 27, 2011 at 2:21 pm
Pink123 (1/27/2011)
Thanks for your reply.I am a begineer .
I tried to check the actual execution plan.
There is an index seek on Table A which having cost as 37%.There are other parameters as
predicate,seek predicate,object,output list which I am not able to understand.It would be of help if you could let me know what they are.
Also this index seek is having the maximum cost of 37%.Is this the cause for slow performance?
There is another Clustered index scan on table b which is having cost 20%.
Your help would be very useful for me.
Thanks in advance
Like I said before, we're not sitting in your chair. We can't see what you can see. Posting the information as requested makes it possible for us to help out.
If, for security purposes, or some other reason, you can't post the information, then I can point you to Gail's articles as a great way to start understanding tuning. Once you get through those, you might want to take a look at my books.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 28, 2011 at 1:56 am
As per my standards, the best execution plan should look like when you can see the 100% Index seek from a table (within a query). So try to check other parts of the queries as well. any inner expressions, evaluations etc. Also check if there are comparison of fields from different tables having different datatypes. Proper selection of indexes should be done on tables as well.
January 28, 2011 at 2:24 am
sql_butterfly (1/28/2011)
As per my standards, the best execution plan should look like when you can see the 100% Index seek from a table (within a query).
I disagree. Only if the query is trivial (select column from table where <condition>) would I expect and want 100% cost on one index seek. If the query joins, aggregates or does anything else then finding 100% of the cost on a single operator tells me that that operator is a bottleneck for the query.
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
January 28, 2011 at 5:50 am
GilaMonster (1/28/2011)
sql_butterfly (1/28/2011)
As per my standards, the best execution plan should look like when you can see the 100% Index seek from a table (within a query).I disagree. Only if the query is trivial (select column from table where <condition>) would I expect and want 100% cost on one index seek. If the query joins, aggregates or does anything else then finding 100% of the cost on a single operator tells me that that operator is a bottleneck for the query.
Right there with Gail. That would be a red flag, not a desired result.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 28, 2011 at 10:12 am
1) In my experience 'search' type stored procedures are often best done as dynamic sql. Non-trivial to construct but WAAAAYYYYYYY more efficient. Beware SQL Injection if you go this route.
2) Given that you are a beginner, I HIGHLY recommend you get a professional in to mentor you on how to do this type of query.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply