Performance Problem

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi Gail,

    thank you for answer me. I've attached the informations you asked me.

    Nat

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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...

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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.

  • 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

  • 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

  • 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

  • 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