July 14, 2010 at 8:56 am
Hi!
Im trying to make a query better in my application through a peformance job.
I got a good plan for this query when Im using Management Studio and the amount for read, writes and CPU is also fine.
But, when the application runs this query another plan is used and the amount of resources keep very high (100% of CPU and very high read costs).
I only found this diference and I found it looking for the trace of this situations.
Does anyone know the diferecen of this situations? Or what I can do to SQL Server chooses the best plan?
Cost - Management Studio:
CPU: 0
Reads: 278
Writes: 0
Duration: 11
Cost - Application:
CPU: 4.500
Reads: 249.427
Writes: 0
Duration:25.904
Query:
DECLARE @P0 nvarchar(2000)
set @p0 = 'Strings de diferentes tamanhos'
selecttop 1 tab1.id, tab1.texto
fromtabela1 tab1
whereexists (select 1
from tabela2 tab2
where tab1.id = tab2.id
and tab2.nome = @P0)
order by tab1.id
Execution Plan - Management Studio:
Execution Tree
--------------
Top(TOP EXPRESSION:((1)))
|--Nested Loops(Inner Join, OUTER REFERENCES:([tab2].[id], [Expr1006]) WITH ORDERED PREFETCH)
|--Sort(DISTINCT ORDER BY:([tab2].[id] ASC))
| |--Index Seek(OBJECT:([base].[dbo].[tabela2].[IX_tabela2] AS [tab2]), SEEK:([tab2].[nome]=CONVERT_IMPLICIT(nvarchar(500),[@P0],0)) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([BASE].[dbo].[tabela1].[PK__tabela1__37FA4C37] AS [tab1]), SEEK:([tab1].[id]=[Base].[dbo].[tabela2].[id] as [tab2].[id]) ORDERED FORWARD)
Execution Plan - Application:
Execution Tree
--------------
Top(TOP EXPRESSION:((1)))
|--Nested Loops(Left Semi Join, WHERE:([Base].[dbo].[tabela1].[id] as [tab1].[id]=[Base].[dbo].[tabela2].[id] as [tab2].[id]))
|--Clustered Index Scan(OBJECT:([Base].[dbo].[tabela1].[PK__tabela1__37FA4C37] AS [tab1]), ORDERED FORWARD)
|--Table Spool
|--Index Seek(OBJECT:([Base].[dbo].[tabela2].[IX_tabela2] AS [tab2]), SEEK:([tab2].[nome]=[@P0]) ORDERED FORWARD)
Looking for the graph excution plan, I realized something that I could not understand. The 'Index Seek' returns 173 lines to be the input of 'Table Spool'. But, 'Table Spool' returns 28111982 lines for 'Nested Loops'.I thought that 'Table Spool' should only put in a temporary table the 173 lines it received. How the other lines appeared?
Server: SQL Server 2005 Standard
Compatibility : 8.0
Tks,
Nat
ps: Im sorry for my poor english
July 14, 2010 at 9:32 am
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
July 14, 2010 at 11:14 am
Hi Gail,
thank you for answer me. I've attached the informations you asked me.
Nat
July 14, 2010 at 11:28 am
Parameter sniffing?
Looks like the app is a stored proc call (or parameterised SQL) and the management studio is ad-hoc SQL. The row count estimates are quite wrong on the one from the app, so it's stale stats (which would have affected both) or parameter sniffing.
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
July 14, 2010 at 11:48 am
Yes, the management studio is ad-hoc SQL. But the app is not using a procedure, I know that the app uses hibernate to send the query and the value of the parameter to be used in the query. On the trace I can see separately a line for the query and other line for parameter (like exec sp_execute 9,N'String').
About Parameter sniffing, I only saw it happens in procs. Is it possible for querys from appl? And how can I solve it in parameterised SQL?
About the stats, I thought about it and I ran 'Update Stats' on the tables involved. And nothing changed. The weird part is, I changed the index on monday and the problem seems to be solved back then. Today it appeared again...
July 14, 2010 at 5:00 pm
I am having same problem
if I wrap the SP code in an
execute ('code here')
then the stored procedure takes 250 ms
otherwise it takes 8500 ms
the exe plans are completely different
the SP is using a massive clustered index scan
whereas adhoc/execute is optimizing correctly
all my indexes and stats are tip top (as proved by the 250 ms)
with or without WITH RECOMPILE or local copies of parameters makes no difference
July 14, 2010 at 11:34 pm
Nat Na (7/14/2010)
About Parameter sniffing, I only saw it happens in procs. Is it possible for querys from appl?
Can happen any time there's parameterised queries.
And how can I solve it in parameterised SQL?
Depends how much control you have over the generated code. Think I'm going to ask Grant to help out here, as he's more familiar with nHibernate.
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
July 14, 2010 at 11:34 pm
doobya (7/14/2010)
I am having same problem...
Please post your question in a new thread and include the stored proc definitions, table and index definitions and both execution plans.
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
July 15, 2010 at 6:16 am
GilaMonster (7/14/2010)
Nat Na (7/14/2010)
About Parameter sniffing, I only saw it happens in procs. Is it possible for querys from appl?Can happen any time there's parameterised queries.
And how can I solve it in parameterised SQL?
Depends how much control you have over the generated code. Think I'm going to ask Grant to help out here, as he's more familiar with nHibernate.
Hibernate and nHibernate generally parameterize their queries right out of the box. The main issue is how they parameterize their queries. Strings are the biggest issue. By default, if you don't do anything about it, they will simply measure the size of the string being passed and use that to declare the variable. For example 'Cow' will become varchar(3) and 'Horse' will become varchar(5). That doesn't lead to parameter sniffing so much as it leads to implicit data conversions (in some cases) which prevents index use. Here's how[/url] you get around that issue.
I hope that helps a bit.
"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
July 15, 2010 at 12:15 pm
Hi Grant,
thank you for answer me. I read the article you told me, but it only works for NHibernate and Im using Hibarnate (the property of hibernate.cfg.xml is not available for Hibernate).
I also read a lot about implicit data conversions and it seems very common on Hibernate + Sql Server. But I saw in the execution plan that my index is beeing used (probably not in the best way as possible) and this made me confuse about it. When implicit data conversions happens, no index will be used in the query?
To try another solution, Im going to change my appl to call a procedure instead of sending a query and see if it will helps me.
I will post the result of this test as soon as possible.
Tks again for your help and Im sorry about my poor english.
July 15, 2010 at 12:40 pm
Nat Na (7/15/2010)
Hi Grant,thank you for answer me. I read the article you told me, but it only works for NHibernate and Im using Hibarnate (the property of hibernate.cfg.xml is not available for Hibernate).
I also read a lot about implicit data conversions and it seems very common on Hibernate + Sql Server. But I saw in the execution plan that my index is beeing used (probably not in the best way as possible) and this made me confuse about it. When implicit data conversions happens, no index will be used in the query?
To try another solution, Im going to change my appl to call a procedure instead of sending a query and see if it will helps me.
I will post the result of this test as soon as possible.
Tks again for your help and Im sorry about my poor english.
No worries on the english. It's my first language and I'm frequently garbled so I have no right to complain about anyone else.
I wasn't aware that Hibernate was that different from nHibernate. Sorry.
As far as "using" the index goes, just because you see an index named in an execution plan doesn't mean it's being used in a seek operation. It's being scanned. That means all of it is read instead of small parts of it.
"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
July 16, 2010 at 9:18 am
I LOVE ORMs!! They create SOOO many business opportunities for me due to the bad stuff they (and the developers who use them) do with databases! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 16, 2010 at 11:19 am
TheSQLGuru (7/16/2010)
I LOVE ORMs!! They create SOOO many business opportunities for me due to the bad stuff they (and the developers who use them) do with databases! 😎
I wish I could be as happy as you. I'm developing knowledge on how they work precisely because of the problems they're creating for me. I don't get paid extra for fixing preventable problems.
"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
July 21, 2010 at 12:42 pm
Thank you all for the help. The solution using stored procedure seems to be working and we choose it to fix this problem for now, even though a better solution can exists.
Nat
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply