[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 626
  • Last Modified:

SQL Sever 2008 R2 64 Bit Stored Procedure performance issues

Hello all.  I'm pulling out what little hair i have left on a SQL 2005 to SQL 2008 migration.
Old server - Virtual Machine, WS 2003, 32 bit, 4 GB Ram, SQL server 2005, 32 bit, 1 CPU, 30 GB Drive, 3 GB Database.
new server - Virtual Machine WS 2008 R2, 64 bit, 8 GB Ram, SQL Server 2008 R2 64 bit, 1 CPU, 30 GB Drive, 3 GB Database.
Host is Dell Precision M6500 with 500 GB drive and 16 GB ram.

I backed up and restored my SQL 2005 DB into SQL 2008 and all went well.
I have some SQL jobs that run stored procedures.  In particular, in order to build files, i have a stored procedure which loops through a list and builds the files for each person on the list.  In SQL 2005, each time the loop ran it might take 5 seconds.  In SQL 2008, the first time takes 34 seconds and the 2nd time takes 1750 seconds and the third takes 3500 seconds, and it just keeps growing....  For the life of me i cannot determine what is different between SQL 2008 and SQL 2005 that would "break" this.  I have searched EE extensively and learned a lot about parameter sniffing (changed that and it made no difference), inserts vs bulk inserts, etc.

The bottom line is this stored procedure worked fine in SQL 2005 and is totally unacceptable in SQL 2008.  There has to be some sort of "new" parameter in SQL 2008 that defaults one way and i need to set it another way.  I have tried "With recompile" and without it.  no difference.  I watched it in the trace analyzer and it appears to "wait" on locks and buffer I/O.

I'm sure somebody out there can tell me "oh - set this parameter different and it will work like in SQL 2005."  


DECLARE @fchhcd varchar(10)
DECLARE PDAList CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT fchhcd FROM dbo.HandHeldCD WHERE flrebuild = 1 ORDER BY fchhcd
OPEN PDAList
FETCH NEXT FROM PDAList INTO @fchhcd
WHILE (@@fetch_status <> -1)
BEGIN
      IF (@@fetch_status <> -2)
      BEGIN
            EXEC dbo.Build_HHLoadListData_Individual @fchhcd, @ligroup
      END
      FETCH NEXT FROM PDAList INTO @fchhcd
END
DEALLOCATE PDAList
0
DonnaOsburn
Asked:
DonnaOsburn
  • 10
  • 4
  • 4
  • +3
1 Solution
 
AnujSQL Server DBACommented:
On the new upgraded server update the statistics on the under lying table, if the indexes are fragmented defragment it, Also run DBCC UPDATEUSAGE.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
anujnb is right. When you migrate a DB from one version to other, it keeps the execution plans from old version, so you need to update statistics so the new engine can create new execution plans for the queries.

Cheers
0
 
DonnaOsburnAuthor Commented:
I Reindexed all the tables, updated usage, reset the statistics, turned lock pages in memory on, rebooted multiple times...
All before i asked for help :).  I tried the obvious things.  I'm hoping someone has run into this before and it will jog their memory.

The entire database, all services, interfaces, everything upgraded just as i expected.  This is the only place where I call the same stored procedure in a loop (for as many users as they have - maybe 5 maybe 35) and it is the only thing that "broke" .  And it broke on an exponential scale.  

Looking at the time, the first time through there are 370228 logical reads and it takes 13 CPU seconds.
the second loop through the same procedure has 25381002 logical reads and take 1800+ CPU seconds.  The entire table only has 425K records in it, how can it have 25381002 logical reads and take 1800+ CPU seconds???  then the third time is double the second time and so on...

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
DonnaOsburnAuthor Commented:
This database was originally a SQL 2005 database.  I backed it up and restored it to SQL 2005 and not one thing "broke".  Of course those were both 32 bit machines.  Now i have a SQL 2008 R2 64 bit machine.
0
 
AnujSQL Server DBACommented:
Interesting, Can you post the execution plans for it?
0
 
DonnaOsburnAuthor Commented:
I'm trying to figure out how to do that right now.  I know how to get an execution plan for one query but not for a whole stored procedure.  I can't run a plan on just the one long running query because it involves #temp tables.

I am dying to compare the 2005 plan to the 2008 plan if i can figure out how to create them :).

0
 
AnujSQL Server DBACommented:
Include Actual Execution Plan not the estimated one.
0
 
DonnaOsburnAuthor Commented:
I scripted the SP to execute, set my parameters and turned Actual Execution Plan on.  It appears to be showing the plan (taking forever of course)  I will let you know what i find.  Not sure how equivalent that is to being called in "production" but its a start as it is taking forever also.

I meant to say this was originally a SQL 2000 db and then up to 2005 and now 2008 R2 64 bit
0
 
Ryan McCauleyData and Analytics ManagerCommented:
Since you mention that your old server is Windows 2003 and the new one is Windows 2008 R2, I don't think it's SQL-related at all, but instead related to some underlying network settings in the newer version of Windows. There are some network settings enabled by default that can cause your query performance to be abysmal in some cases. It's not all cases, and it's kind of intermittent (issue will occur on every query for a few hours, then be high speed for a week, then be slow again for a day, then fine again).

Try disabling both "TCP Global Autotuning" and "TCP Hueristics" and see if it improves your performance - from an elevated command prompt, run these two commands:

netsh int tcp set global autotuninglevel=disabled
netsh int tcp set heuristics disabled

I did a write-up on my blog that has a little bit more detail:

http://trycatchfinally.net/2011/09/slow-sql-server-queries-over-a-linked-server-from-windows-server-2008/
0
 
DonnaOsburnAuthor Commented:
I saved the .sqlplan and when i try and open it (so i can see it before i post it here) i get the following error.

TITLE: Microsoft SQL Server Management Studio
Error loading execution plan XML file C:\PMEE\Query Performance\test3.sqlplan. (SQLEditors)
ADDITIONAL INFORMATION:
There is an error in XML document (0, 0). (System.Xml)
Root element is missing. (System.Xml)

What does this mean and how to I fix it so i can see the saved plan?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you remember to change database compatibility after restore it to SQL Server 2008?
0
 
DonnaOsburnAuthor Commented:
I did not change it.  it is still set at SQL 2005 compatibility.  Help me understand why this would matter?
Because of the 32 bit to 64 bit?  Is there some basic change i need to do to "convert" from 32 bit to 64 bit?  Reading around on blogs it said it was not important to change it.

ryanmccauley:
i tried your changes and then rebooted.  They ran successfully but made no difference in the time to execute.
0
 
MohammedUCommented:
There is no configuration change other than disable AWE.
One simple advise...
Add out put statement after each SELECT, INSERT, update, delete in your procedure...
and see exactly where it hangs...work it out on the statement...
one morething sometimes when the query plan changes it could result in big table scan or cartician prod...
Are you using any functions in your where clauses if so try removing them for testing...
0
 
DonnaOsburnAuthor Commented:
AWE is disabled.
I know exactly which statement it hangs on (from walking through the debugger) i just don't know why.
It's spending all its time on an insert into a table.  i'm pulling the execution plans now and will compare why each trip through the same stored procedure takes such an incremental time.
No functions.
The table being inserting into only contains 425K records.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you don't change DB compatibility to 2008, that database will act as still in SQL Server 2005. Because it's running in SQL Server 2008 engine may happens lost of performance, as the case.
0
 
AnujSQL Server DBACommented:
Can you check the how much free space is left in your data files? Is your inserts are causing the file groups to be expand?
0
 
Anthony PerkinsCommented:
>>If you don't change DB compatibility to 2008, that database will act as still in SQL Server 2005. Because it's running in SQL Server 2008 engine may happens lost of performance, as the case. <<
Interesting concept.  The only affect setting the compatibility level to 90 is to prevent new functionality in SQL Server 2008.  The engine is still 2008, so changing the compatibility level should have no effect on performance whatsoever.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, it can have acperkins. The Execution Plans are different because are generated from different engines (even if you set compatibility for previous version). If you have time you can make this test and check if isn't true.

Cheers
0
 
DonnaOsburnAuthor Commented:
I have run the plan with SQL 2005 compatability.  i will change it to SQL 2008 and rerun and get back to you.  it took 9 hours... just for 3 list.... argh!
0
 
Anthony PerkinsCommented:
>>The Execution Plans are different because are generated from different engines <<
I am afraid that does not make a lot of sense, so I guess we will have to agree to disagree.
0
 
Ryan McCauleyData and Analytics ManagerCommented:
I'm with ACPerkins on this one - there's only one SQL Server engine installed with the instannce, so the Compatibility Mode set on the database can't effect "which engine is used". Changing the compatibility mode just changes the way SQL Server handles some ANSI-related commands, as well as excluding commands from newer versions of the engine (try building a CTE using WITH when your compatibility is set to 80).

Though the compatibility level chosen might have an impact on the execution plan generated (though I don't imagine it would), it won't affect the execution of the chosen plan at all - if the plans match, then the query is executed the same way, regardless of compatibility level.
0
 
DonnaOsburnAuthor Commented:
Thank all of you for your suggestions.  I finally figured it out.  In SQL 2000 and 2005 i dropped the index on a file before throwing a lot of records into it.  It sped it up tremendously.  In SQL 2008 R2 - i left the indexes and all is well.

This actually seems bizarre to me - but that is the only change i made to the stored procedure.
0
 
DonnaOsburnAuthor Commented:
I tried all of the suggestions above, even posting analysis - but nothing worked.  i had to figure it myself.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 10
  • 4
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now