SQL Server Performance

Tuning for Excessive recompiles using in SQL Server 2005

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by nduckste, May 21, 2009.

  1. nduckste New Member

    I recently started as a DBA for a new company. We're experiencing high CPU usage on our production server. As best I can tell it is due to excessive recompiles in a handful of store procedures. We have separate databases for each of our clients so whenever a stored proc behaves poorly it does so across all our client databases (several hundred).
    Most resource intensive queries in terms of both high CPU and least plan-reuse are one and the same queries. That's how I get to the conclusion that recompiles is the problem. I've already tuned the stored procs so that they compile 3 times instead of 8. The remaining statements causing recompiles are as follows:
    • insert into #temp6 select distinct ...delete from #temp6 where detail_key = ''
    • delete from dbo.xxx_distinct where detail_key not in ( select distinct detail_key from #temp6 )
    • insert into dbo.xxx_distinct ( detail_key, detail_type ) select detail_key, data_type from #temp6 where data_type = 'number' ...
    Everything I read tells me that in SQL Server 2005 the way to tune these queries is by using Plan Guides. However, everything I read also:
    • Only has examples for SELECT statements. Can plan guides be used for INSERT, DELETE, and UPDATE statements?
    • Seems to suggest that using plan guides for #temp tables seems prohibitive. Am I missing something there?
    I realize there is some bad logic in some of these procs but I can't fix all those right away. Seems like the quickest way "out of the woods" in terms of performance is to tune the procs so they stop causing recompiles without changing functionality. Then I'll have some breathing room to deal with rewriting code.
    Thanks in advance
  2. Sandy New Member

    Hi Nick,
    [quote user="nduckste"]
    Most resource intensive queries in terms of both high CPU and least plan-reuse are one and the same queries. That's how I get to the conclusion that recompiles is the problem. I've already tuned the stored procs so that they compile 3 times instead of 8. The remaining statements causing recompiles are as follows:
    • insert into #temp6 select distinct ...delete from #temp6 where detail_key = ''
    • delete from dbo.xxx_distinct where detail_key not in ( select distinct detail_key from #temp6 )
    • insert into dbo.xxx_distinct ( detail_key, detail_type ) select detail_key, data_type from #temp6 where data_type = 'number' ...
    [/quote]
    I faced the same type of issue few days back. I modified few codes and it works fine. It may not be the same case for you but you can try it.
    1. Instead of Temporary Table just create Table Variables and replace it.
    2. Instead of NOT IN keyword just changed to LEFT JOIN.
    3. Don't create Primary key on IDENTITY Column on your Table Variable.
    4. Try to avoid the DISTINCT keyword in your query if possible.
    Thanks,
    Sandy.
  3. nduckste New Member

    Sandy,
    Thanks for the response. A couple questions:
    1. I've been told (or read) that Table Variables don't work that well above 1000-1500 records. Some of our tables are 160k. Have you experienced good performance with large table variables?
    2. Good idea on the LEFT JOIN vs NOT IN.
    3. Why not create the PRIMARY KEY (it was already there and I just left it)?
    4. Good idea on avoiding DISTINCT although I don't think that is possible in a lot of cases the way the database has been designed. I realize that DISTINCT and GROUP BY clauses don't auto-parameterize and thus cause recompiles.
    I'll try what I can and report back.
    Nick
  4. Sandy New Member

    [quote user="nduckste"]1. I've been told (or read) that Table Variables don't work that well above 1000-1500 records. Some of our tables are 160k. Have you experienced good performance with large table variables?
    [/quote]
    Point 1 Ans: I also read the same but when I used the Table variable I got the difference. I am not sure with more records if more than 10,000 but I believe if you have less than the 5,000 it will give you more performance benifit.
    [quote user="nduckste"] 3. Why not create the PRIMARY KEY (it was already there and I just left it)?
    [/quote]
    Point 3 Ans: Nick, I have the same experience regarding the Primary Key on a Table variable. For an example without key I got the result in 335ms and after applying the PK I got 765Ms for the same SP. and more over the Table variable used for several DMLs. Now you check and let me know if it works for you or not.
    I just shared my idea to you, now please check from your end and tell us.
    Thanks,
    Sandy.
  5. nduckste New Member

    I'll give the primary key thing a try and report back. Thanks.
  6. madhuottapalam New Member

    Sandy,
    I understand all other point but PK. I have never seen a code where somebody create PK on a table variable. If it is identity then what is the need of PK there. And also PK are created for data integrity and consistancy i dont see that requires in this scenario. Why one would create PK on table variable that to on identiy i do not uderstand (may be i am missing something).
    Yes temp table causes the sp to recompile. That is a valid reason and when you have high CPU recompilation or any computation query is the first thing we check. You also check if you are mixing up DDL with DML inside the SP. Ie. if you are creating temp table then altering or something like that.
    Madhu
  7. Sandy New Member

    [quote user="madhuottapalam"] Sandy,
    I understand all other point but PK. I have never seen a code where somebody create PK on a table variable. If it is identity then what is the need of PK there. And also PK are created for data integrity and consistancy i dont see that requires in this scenario. Why one would create PK on table variable that to on identiy i do not uderstand (may be i am missing something).
    Yes temp table causes the sp to recompile. That is a valid reason and when you have high CPU recompilation or any computation query is the first thing we check. You also check if you are mixing up DDL with DML inside the SP. Ie. if you are creating temp table then altering or something like that.
    Madhu [/quote]
    Madhu, the Primary key is not only used for the data integrity and consistency but also gives you another advantage of Clustered Index. When you are creating an Identity column in Table variable that does not creates any type of index on your Table Variable.
    Just think a situation when Table variable’s result set is based on some filter then what type of scan happens. Definitely a Table Scan even Identity column exists. But if you are declaring a PK on the Table variable it will go for an index scan and also give you a chance of index seek too.
    So thats why I made this comment, but practically saying if your table variable is only used for "Select" then usage of Index are benefits for you. But SP is having DML command on the Table variable you should not create a Primary Key on the Table variable as I faced the problem before which I mentioned in my above post.
    Also Nick, I am waiting for your comments and solution after testing as you said above.
    Thanks,
    Sandy
  8. madhuottapalam New Member

    Yes ... I understood your point that to create clustered index you create PK , but i do not think it makes much difference, may be i am wrong. When you said " If you are declaring PK on table var it will go for an index scan" in that case it is nothing but Table scan ie. Clustered index scan is nothing but table scan.
    Madhu
  9. Sandy New Member

    Madhu,
    I do understand the Index scan is nothing but the Table scan but the advantage of Index scan you have a chance of index seek where in table scan its not possible. So its better to have a Clustered Index rather than no index on a table.
    Thanks,
    Sandy.
  10. madhuottapalam New Member

    WHat i said is CI scan is nothing but table scan. When it is a heap it will be a table scan , when you have CI on a tbale it will be CI scan. The agree on the other part.
    thanks
    Madhu
  11. FrankKalis Moderator

  12. satya Moderator

    There is no universal rule of when and where to use temporary tables or table variables. Try them both and experiment, in terms of actual volume of data in development/test environment. With these tests, verify both sides of the spectrum small amount/number of records and the huge data sets.
    Also I would like to know whether these queries used to work in SQL 2000 and affected performance when it is upgraded to 2005, and having such migration to SQL 2005 when you use complicated logic in your stored procedures. The same code can run 10-100 times slower on SQL Server 2005!
    As you have talked about PLAN GUIDES I would say have you tried building them up and query from the CACHE for the better performance, occassionally you may need to rebuild them as and when table data volume is comparable in terms of millions of rows.
  13. nduckste New Member

    I'll test both tbl variables vs #temp tables and report back.
    As for SQL 2005 vs 2000 I couldn't tell you...I've only been on this job 3 weeks.
    Finally using PLAN GUIDES is something I am new to. What I don't yet understand is where to apply the plan guide. For example if I have the following statement:
    insert into #t (var1)
    select var1 from tbl where var2 = @x
    What causes the recompile, the insert into the #temp table, the SELECT statement, or both?
    1. If it is the insert statement, I haven't seen examples where putting a plan guide on an insert would help or if that is even allowed.
    2. Furthermore, if it is the insert statement is causing the recompile, will creating a plan guide for for the select statement stop the recompiles.
    3. If I can figure out how to use the plan guides, I'm not too worried about the CACHE being outdated. We don't have that much volume and can cross that bridge when we come to it.
    Thanks for the dialog. I really appreciate it. If you know of some good books that go through these different scenarios in detail (with examples that I can recreate) I'd really appreciate someone pointing them out to me. I'd like to make some presentations to our developers so they stop writing code like this.
    Nick

Share This Page