SQL Server Performance

Stored procedure in SQL 2005 runs 20x slower than SQL 2000

Discussion in 'SQL Server 2005 General DBA Questions' started by fourfives, Oct 5, 2008.

  1. fourfives New Member

    I'm at my wits end. I have 2 servers. One is a 2000 SQL server, the other a 2005 SQL server. The 2005 SQL server has MUCH better hardware than the older 2000 SQL server. Running queries off of the 2005 box is MUCH faster compared to our old 2000 box except stored procedures!
    A stored procedure that ran literally faster than the blink of an eye on the 2000 box takes upwards of 5 minutes(!) on the much faster 2005 box on the EXACT same database. Another thing that i've noticed out of the ordinary is that if I execute the stored procedure (in the server management studio) by going to the database-->Programability-->Stored procedure and click execute stored procedure it takes 5 minutes to run. HOWEVER if I right click from the same place...and click modify....then click execute it runs instantly.
    Its killing me because we use ADP project files to excute gobs of stored procedures. At this point, I flat out can't run stored procedures in 2005 but on the 2000 box which has 5 year old hardware it runs like a champ.
    Help.
    2005 box is Windows 2003 64x , 32 gigs of ram. RAID 10 SAS drives, 2x Quad cpus, SQL 2005 SP2.
  2. FrankKalis Moderator

    What is your patch level on the SQL Server 2005 box? And can you please post a example procedure that runs slow?
  3. fourfives New Member

    The SQL version is 9.00.3073.00. The stored procedure below runs faster than 1 second on a 5 year old SQL 2000 box. On the 2005 box .....................5 minutes if i'm lucky.
    -------------------------------STORED PROCEDURE BELOW---------------------------- USE [Sqlmaster]
    GO/****** Object: StoredProcedure [dbo].[SelectChecksDDL] Script Date: 10/06/2008 08:10:01 ******/SET
    ANSI_NULLS ONGOSET
    QUOTED_IDENTIFIER ONGOALTER
    PROCEDURE [dbo].[SelectChecksDDL]AS INSERT INTO dbo.ChecksDDL
    (BranchID, Description, Payee, CheckNumber, ChkDate, Amount, GLNumber, AmountGLDist, ContrNumber, Acct, CheckType, VoidStatus, Branch, CheckID
    , BankAccount, CheckText, CheckPre, GLCashAccount, SysDateTime, Status)SELECT dbo.ChecksInfo.BranchID, dbo.Checks.Description, dbo.Checks.Payee, dbo.Checks.CheckNumber, dbo.Checks.ChkDate, dbo.ChecksInfo.Amount, dbo
    .ChecksGLDist.GLNumber, dbo.ChecksGLDist.Amount AS Expr1, dbo.Contracts.ContrNumber, dbo.Names.Acct, dbo.Checks.CheckType, dbo
    .Checks.VoidStatus, dbo.BranchesSQL.[3digitbranch], dbo.Checks.CheckID, dbo.BankAccounts.AccountNumber, dbo.Checks.CheckNumber AS Expr2,dbo
    .BranchesSQL.Checkprefix, dbo.BankAccounts.GLCashAccount, dbo.ChecksInfo.SysDateTime, dbo.ChecksInfo.StatusFROM
    dbo.Checks INNER JOINdbo
    .ChecksInfo ON dbo.Checks.CheckID = dbo.ChecksInfo.CheckID AND dbo.Checks.BranchID = dbo.ChecksInfo.BranchID INNER JOINdbo.DDLBusinessPeriods ON dbo.ChecksInfo.BranchID = dbo.DDLBusinessPeriods.BranchID AND dbo
    .ChecksInfo.PeriodID = dbo.DDLBusinessPeriods.PeriodID INNER JOINdbo
    .BranchesSQL ON dbo.Checks.BranchID = dbo.BranchesSQL.BranchNumber INNER JOINdbo
    .BankAccounts ON dbo.Checks.BranchID = dbo.BankAccounts.BranchID AND dbo.Checks.BankID = dbo.BankAccounts.BankID LEFT OUTER JOINdbo
    .Names ON dbo.Checks.NamesID = dbo.Names.NamesID AND dbo.Checks.BranchID = dbo.Names.BranchID LEFT OUTER JOINdbo.Contracts ON dbo.Checks.BranchID = dbo.Contracts.BranchID AND dbo.Checks.NamesID = dbo.Contracts.NamesID AND dbo
    .Checks.ContrID = dbo.Contracts.ContrID LEFT OUTER JOINdbo
    .ChecksGLDist ON dbo.ChecksInfo.BranchID = dbo.ChecksGLDist.BranchID AND dbo.ChecksInfo.ChkInfoID = dbo.ChecksGLDist.ChkInfoIDWHERE
    (dbo.ChecksInfo.Status <> 'A')
  4. Adriaan New Member

    How did you migrate the database? If you scripted the objects in SQL2000, recreated them on SQL 2005, then copied over the data, perhaps you scripted the tables without the full options (e.g. indexes).
    You're copying data into an existing table, so I'd start looking at the file growth settings for the file(s) containing (the indexes on) dbo.ChecksDDL
  5. fourfives New Member

    Actually all I did to migrate the data was backup the DB on the 2000 box and restore it onto the 2005 box. Indexes came over along with data, stored procedures etc. The only thing that didn't come over correctly was a couple of security settings (which I fixed).
    I'm pretty sure its not a growth issue because even stored procedures that do absolutly nothing but select (view) data take forever to run as well. No appending, updating etc.
  6. Adriaan New Member

    Is it a dedicated SQL 2005 box? Does it have anti-virus software that wasn't told it should not interfere?
  7. fourfives New Member

    Its dedicated. I turned AV off just to test it and no differences.
    Remember if I excute the stored procedure a certain way from the management studio it runs fine. If I excute a different way in management studio it takes forever. It also takes forever if I excute from a microsoft project file (ADP).
  8. fourfives New Member

    Even more information from a test I just ran:
    I created 2 access databases. One just a regular MDB and linked to a table via ODBC. Opened the table and went to the end of the recordset. Watched the cpu on the SQL 2005 box while I did this. The server didn't even bat an eye and I got to the end of the recordset almost instantly.
    Next my 2nd access database was a project file. I create a NEW SQL database but linked to the same table as above. I went to the end of the recordset, but this time the cpu on the server pegged out and it took about 5 seconds to get to the end of the recordset.
    Extreamly WEIRD.
  9. Adriaan New Member

    Add SET NOCOUNT ON to the start of the procedure, see if that helps when accessing from an ADP.
  10. ndinakar Member

    did you reindex and update stats after you migrated to 2005? did you change the compatibility level to 90? the following steps in the order are very important :
    • After migration (either via backup/restore or detach/attach or objecets migration), change compatibility mode to 90
    • Reindex ALL tables
    • Update stats for all indexes with full scan
    • run dbcc updateusage
  11. moh_hassan20 New Member

    The above advice by ndinakar is first to do.
    I assume you applied best practice in creating storage for data , logs and tempdb in sql 2005 for the multi core processors.
    Here a hidden problem in migration from 2000 to 2005 (the way we write code in sql 2000, and schema separation in sql 2005)
    for example:
    If two users user1 , user2 , login to server and run the query:
    select * from tab1
    in sql 2005 , the optimizer will use two different plans ( not reuse cached plan , and recomiplation may increase )


    but in sql 2005 if they run
    select * from dbo.tab1 , optimizer will use one plan (re use cached plan) and avoid recomilation

    To resolve that problem
    set default schema dbo for all login users by ALTER USER... DEFAULT SCHEMA
    in that case server assume dbo schema for any login, and reuse cached plan
    -------------------------------
    Also, Set the option “Forced Parameterization.” for the database
    That sets the SQL Server 2005 engine to ignore the format of the SQL queries coming from the application ,and will force the query as possible to be reusable.
    -------------------------------------
    If you have base performance counters for sql 2000 , try to collect the new measures for sql 2005 to find something change.
    (It is better to get a copy of the server configuration parameters , and performance counters for sql 2000 before migration)
    --------------------
    have a look at: http://blogs.msdn.com/sqlprogrammability/archive/2007/01/21/2-0-diagnosing-plan-cache-related-performance-problems-and-suggested-solutions.aspx

    ------------------
    i have two questions:
    do you upgrade inplace or new installation?
    what the H/w configuration of the sql server ?
  12. fourfives New Member

    New instalation. Actually the 2005 box isn't 'new', its been in production for about a year. All I did was migrate another database to it. The databases in use already were never a problem beacuse they don't have many stored procedures.
  13. moh_hassan20 New Member

    Can you compare the execution plan for the above query in second post for both 2000 , 20005
    and post number of logical read and phsical read for both.
    i hope that they are typical to try to find alternatives to fix the problem.
  14. moh_hassan20 New Member

    Also , to find where we go , genaerate performance counter
    have a look for: http://blogs.technet.com/vipulshah/archive/2006/11/30/understanding-perfmon-counters-while-troubleshooting-sql-server-performance-issues.aspx
  15. fourfives New Member

    Hardware between the boxes:
    2000 box: 1 core cpu, Raid 5. 4 gigs of ram. Windows 2003 Server, SQL 2000
    2005 box: is Windows 2003 64x , 32 gigs of ram. RAID 10 SAS drives, 2x Quad cpus, SQL 2005 SP2.
    Running a stored procedure beats the 2005 box by 20 fold if the 2005 box even manages to finish it...without dying in a pile. Running regular queries though....2005 box beats the 2000 box every time. ..........................I'm so stumped.
  16. fourfives New Member

    Just did all of that and didn't make a difference. Not sure what optimzing anything would have to do with this. Re-read my first post. I was able to run a stored procedure exteamly fast using the server management studio..........but using the server management studio and running the stored procedure in a place slightly differently it takes forever to run. If indexes were messed up, i'd never be able to get anything to run quickly.
  17. moh_hassan20 New Member

    Hi fourfives
    can you share your knowledge if you fix the slow of the server.
  18. fourfives New Member

    It was a complex problem:
    The stored procedures were using views. These views accessed data both on and off the local server. Because the views were accessing data on another server it would timeout...or make things extreamly slow. I simply 'gave up' and moved the OTHER database that the views were accessing to the same local server. Once this was done everything ran fast again. I didn't like this solution but at some point you gotta do what you gotta do. It bothers me that going off server slows down things THAT much because I kept tabs on nic cards on both servers...and they were not even breaking a sweat.
  19. moh_hassan20 New Member

    Thanks for sharing information. and i am glad that server ran fast again.
    That raise a useful lesson on upgrading to 2005:
    "Test views that is reference remote servers after upgrading , and take necessary corrections"

Share This Page