February 11, 2010 at 12:33 am
Hi
In my system I use sql server 2005 enterprise edition. My db is about 100 gb. I analyzed with DTA and created indexes that is needed but performance didn't improve. when I look execution plan I see a lot of table spools, costant scan, nested loop. what are these and how can I get rid of them? besides I think I have also a problem with recompilation. could you help me about how to prevent recompition?
February 11, 2010 at 1:26 am
We don't have enough information to help you out yet. Please see this guide to posting questions here and follow it's instructions: http://www.sqlservercentral.com/articles/Best+Practices/61537/
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 11, 2010 at 1:49 am
That's a really vague question. There's nothing inherently wrong with nested loop joins and constant scans. Is it slow? Is there blocking? Are there timeouts?
DTA indexes should be tested and evaluated carefully before being applied. DTA is not always right.
I would honestly suggest that, if there's no one inhouse that knows performance tuning, you get a consultant in to advise and teach. It'll be faster in the long run. If that's not an option, take a look at the following two articles.
Finally, if you want us to help you with the query, please post table definitions, index definitions and execution plan, as per 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
February 11, 2010 at 2:15 am
I attached my table and indexes scripts and an execution plan. Execution plan is related to an update query. this is a so sample query but it takes about 4-5 seconds to run. I didn't understand why it takes so long. if you look execution plan there are a lot of "index update" and "table spool" what does it mean?
thank you all very much for your help.
February 11, 2010 at 2:36 pm
sarigulyavuz (2/11/2010)
...if you look execution plan there are a lot of "index update" and "table spool" what does it mean?
"index update" means exactly what it says, it's is updating one of the indexes on your table. When you update a table, you change the values of some columns in some rows. If those columns are also part of any indexes on the table, then those indexes must be updated as well.
"table spool" is the equivalent of a temporary table that the SQL query makes to save data that it has already derived once so that it doesn't have to look it up all over again. It's not a bad thing by itself.
this is a so sample query but it takes about 4-5 seconds to run. I didn't understand why it takes so long.
I counted 75 indexes on your table. That is why your UPDATE is taking so long. Putting that many indexes on a table will always make writes (INSERTs, UPDATEs and DELETEs) slow, because after it updates your table it then has to update every index that has a column that was in your update. In this case that looks like about 35 of your 75 indexes had to be updated.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 11, 2010 at 3:27 pm
You gave me so valuable information thank you so much, but how can I decide which index or indexes must be deleted?
February 12, 2010 at 1:37 am
Analyse the queries on the table, see what indexes they use, drop any that aren't used. Drop any that are true duplicates (same columns in same order) of any other index
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
February 12, 2010 at 1:43 am
Thank you so much.
February 15, 2010 at 12:13 am
I have been appling your recomendations to my queries. thank you so much. but I encounterd key lookup in execution plan and it's cost 100%. is it good or bad? if it is bad how can I get rid of it.
query :
SELECT * FROM ARCPOL
with (index ( IX_ANNETCNO ))
WHERE dbo.ARCPOL.ANNETCNO=@TCKIMLIK
execution plan :
SELECT
Cost 0% ------ Nested Loops----------Index Seek
(Inner join) HB.dbo.ARCPOL.IX_ANNETCNO
Cost 0% Cost 0%
|
|
|
Key Lookup
HB.dbo.ARCPOL.IX_P_KEY_4
Cost 100%
February 15, 2010 at 12:16 am
I attached execution file .
February 15, 2010 at 12:18 am
Why are you using index hints? Are you really, 100%, absolutely sure that you know better than the query optimiser what index is best for this query with this set of parameters?
Two things.
Get rid of the index hint
Get rid of the SELECT * and select only the columns that you need.
Once you've done that, post the indexes on dbo.ARCPOL and the execution plan of the revised 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
February 15, 2010 at 1:22 am
there was an index scan when I didn't use table hint. but I use table hint it turns to index seek and key lookup. but when I use only necessary columns, it seeks index.
February 15, 2010 at 1:37 am
It goes back to an index scan because all the key lookups are more expensive than the scan is. A seek is not always the fastest thing and when you put hints in place you could very well be making the query perform worse than if you left it off.
Remove the hint, specify just the columns that you need and post resulting the execution plan, along with the indexes on that table.
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
February 15, 2010 at 5:43 am
I think I'm losing my mind, I remove all unnecessary indexes, and optimize the remains but I couldn't still solve the problem. processor is still very high. For example I attached scripts, query, execution plan and report. It seems everything is good. but I took this query from Performance Dasboard Report(I attached allso this file-query_stats.xls) and it says this query runs too long. I didn't understand why?
February 15, 2010 at 8:11 am
For those of us who don't have high speed internet, please just the execution plans and indexes, zipped.
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