Solved

SQL Sever 2008 R2 64 Bit Stored Procedure performance issues

Posted on 2011-09-28
23
616 Views
Last Modified: 2012-05-12
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
Comment
Question by:DonnaOsburn
  • 10
  • 4
  • 4
  • +3
23 Comments
 
LVL 15

Expert Comment

by:Anuj
ID: 36813055
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 36813655
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
 

Author Comment

by:DonnaOsburn
ID: 36815319
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
 

Author Comment

by:DonnaOsburn
ID: 36815334
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
 
LVL 15

Expert Comment

by:Anuj
ID: 36815483
Interesting, Can you post the execution plans for it?
0
 

Author Comment

by:DonnaOsburn
ID: 36815514
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
 
LVL 15

Expert Comment

by:Anuj
ID: 36815541
Include Actual Execution Plan not the estimated one.
0
 

Author Comment

by:DonnaOsburn
ID: 36815597
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
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 36815647
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
 

Author Comment

by:DonnaOsburn
ID: 36815758
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 36815974
Did you remember to change database compatibility after restore it to SQL Server 2008?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:DonnaOsburn
ID: 36816016
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
 
LVL 15

Expert Comment

by:MohammedU
ID: 36816280
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
 

Author Comment

by:DonnaOsburn
ID: 36816338
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 36816415
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
 
LVL 15

Expert Comment

by:Anuj
ID: 36849314
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36891592
>>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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 36891633
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
 

Author Comment

by:DonnaOsburn
ID: 36891720
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36893238
>>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
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 36893406
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
 

Accepted Solution

by:
DonnaOsburn earned 0 total points
ID: 37768581
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
 

Author Closing Comment

by:DonnaOsburn
ID: 37790431
I tried all of the suggestions above, even posting analysis - but nothing worked.  i had to figure it myself.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now