November 16, 2009 at 11:05 am
Hi everyone,
I have a query which takes 16 minutes to execute .it uses more joins and columns as u can see below and sql profiler suggested me to create indexes and estimated improvement is 4%(i want 100% in performance).do you think creating indexes is the best way or can anyone please give me suggestions to improve the performance of the query........
November 16, 2009 at 11:17 am
AllIcansuggestispostthenexttimeyoucopypasteyourcodeusingCODETAGESavailablebeside.
Cant understand what's that above?
Now looks at this
All I can suggest is post the next time you copy pste your code using CODE TAGES available beside.
That's what you have done, to understand your query I need to take the rest of the day off and start trying to understand.
The query looks to be a complex query (that's Understood) but without knowing some more details about what it does, how many tables it uses and # of records in each table ... it is a puzzle without a clue.
But one thing I can see is it would be a better way to try rewriting the code using temp tables and test.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 16, 2009 at 11:34 am
I have just posted the code as an example i don't want to waste your time by analyzing the code .and can you tell me weather i should create indexes or is there a better way to proceed?
Thank you very much for the reply
November 16, 2009 at 11:46 am
Copy pasting the SQL code in the Post would have been better if posted using CODE TAGS for easier readability.
Any way, you are still asking the same question about Indexes,
but explain in short, what does that query does and also some info about how many tables, Table Records etc you are using in the query.
You have also not said if you can rewrite the query using temp tables, Views etc.
Without knowing these and looking at the actual execution plan it is difficult.
Attach the actual execution plan of that query.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 16, 2009 at 12:18 pm
There is not enough information here to help you. If you want good help on this, please read this article and post the necessary information to help you.
http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/
Also, use SET STATISTICS IO ON, run your query in SSMS and post the results.
November 16, 2009 at 3:32 pm
Hi,
ISSUE:
- We started seeing SQL query timeout errors in the server application event log starting 10/30/09. The number of errors has increased over time.
- The errors strongly correlate to the execution of long-running Business Objectsreports (A new series of reports )
- A particular pain point is the 4am-5.45am period. During that time:
o The longest running report took 1 hour 44 minutes
o 27 of the 150 reports that ran during that 1 hour 45 minute period took over 30 minutes to complete
o The 16 processor threads provided approximately 33.5 hours of processing time. 27 hours and 39 minutes of the 33.5 hours were consumed processing the 27 long running reports.
- The reports run acceptably fast when run in isolation
- When those same reports are re-scheduled to run in parallel at a different time of day, the long-running behavior can be reproduced (suggesting there isn’t another cause such as backups, ETL, anti-virus scanning).
This is the issue my colleagues are facing and i have been asked to give some suggestions and the query i have posted which is not clear at all is the back end of this business objects report which is pulling the data from sql server .There are many quieres used but we took this as an example and ran it on Sql server management studio which took 16 minutes.
Now i want to post the execution plan but my database is not giving me any permissions to save execution plan:
SHOWPLAN permission denied in database 'oncx'.
I have used sql profiler and created indexes but no use ,and statistics are on.
Now would it be helpful to post each table definitions used for the queries.
Thank you
November 16, 2009 at 3:43 pm
I have used sql profiler and created indexes but no use ,and statistics are on.
Now would it be helpful to post each table definitions used for the queries.
A couple of overservations here. First, it sounds like you're not really sure if this one query is your heaviest hitter and you just pulled this one out of the hat and it happened to be a 16 minute query. Is that accurate?
You need to set up a Profiler trace and capture everything that happens in your over night run. You can then use the results of the trace to find out which SQL statements consume the most resources and focus your performance tuning efforts based off of the most inefficient queries first. Use Profiler to capture the execution plans. If you don't have the proper permissions to do something, work with someone who does.
As far as the query in question, work with someone who can get you the execution plan and post it as per the instructions in the link. Also, run the query in SSMS with SET STATISTICS IO ON and post the results. This means that you actually put the SET statment in the query window prior to running your query, like this:
SET STATISTICS IO ON
SELECT......
FROM.....
WHERE.....
November 16, 2009 at 3:47 pm
Another thing. Since this has to do with a slowdown during a batch run, I'd also suggest running a Windows Performance Monitor session during the batch to see what kind of impact your batch is having on the CPU, Memory (SQL Server Memory), and Disk.
November 16, 2009 at 3:52 pm
From the explanation you have given, I would suggest, try running the Longest running report individually (schedule it that way) and run the other 27 odd reports each one after the other and see.
There seems to be heavy locking on the tables when they are all running parallel.
Just a simple suggestion from what I understood.
But still strongly advocate for the usage of Temp Tables AND/OR views to avoid the multiple joins.
to correct you, it must be Database Engine Tuning Advisor that suggested Index creations not the Profiler.
DTA must have already suggested this, but making a point that you check manually.
See that you have Indexes on all the Columns that are used in the JOIN condition.
For Example look at the following post
http://qa.sqlservercentral.com/Forums/Topic817390-392-1.aspx#bm817424
Updated: Started to reply a while back and had not seen the replies by John.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 17, 2009 at 6:18 am
Thanks i will try that....
November 18, 2009 at 6:53 am
I have seen the execution plan ,in that it is scanning rows .i heard that seek performs good than scan.
but i don't know how to create clustered index seek can anyone please tell me how to create clustered index seek........
If i am wrong please correct me....
November 18, 2009 at 7:20 am
srilu_bannu (11/18/2009)
I have seen the execution plan ,in that it is scanning rows .i heard that seek performs good than scan.but i don't know how to create clustered index seek can anyone please tell me how to create clustered index seek........
If i am wrong please correct me....
It would be better if you can post the actual execution plan of that query rather than describing about some part of the execution plan.
What you heard about seek is correct, but you cannot create a clustered index seek, you can only create a clustered index which eill enforce the seek.
I recommend you read this article by Gail Shaw, to understand more about Indexes.
http://qa.sqlservercentral.com/articles/Indexing/68439/[/url]
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 18, 2009 at 7:26 am
Please post entire query, table definitions, index definitions and execution plan, as per http://qa.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
November 18, 2009 at 11:21 am
Will this be helpful................
November 18, 2009 at 12:36 pm
Yes, but would still like table definitions, index definitions and the query that we're trying to help you with.
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 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply