I'd have to agree, I have seen that in our own environment as we have moved databases from SQL 2000-2005 and up to SQL 2008.
Why I said a profiler trace run through the best practice analyzer for SQL 2005 is a good start, that and reviewing the PLAN that SQL is using based on its statistics in graphically form will let you know more about how SQL is trying to satisify the batch request from the client/application, which might not be optimal. Inner/Outer Loops, Joins, Table scans, missing or unused indexes all could tribute. Z Edward E. Ziots CISSP, Network +, Security + Network Engineer Lifespan Organization Email:[email protected] Cell:401-639-3505 From: Michael B. Smith [mailto:[email protected]] Sent: Thursday, February 24, 2011 6:35 PM To: NT System Admin Issues Cc: Jim Kibbie; Laura Clarke Subject: RE: 2003 R2 32-bit Std Edition Memory If I were a betting man, I would bet that you've lost a few indices when you moved from 2000 -> 2005, or you haven't updated statistics. Both are KEY to optimizing query performance, especially on large databases and/or complex queries. Regards, Michael B. Smith Consultant and Exchange MVP http://TheEssentialExchange.com From: Phil Hershey [mailto:[email protected]] Sent: Thursday, February 24, 2011 2:48 PM To: NT System Admin Issues Cc: Jim Kibbie; Laura Clarke Subject: RE: 2003 R2 32-bit Std Edition Memory Thanks, Michael. We're not averse to spending some funds on quality consulting. We'll talk it over. J I should point out that our databases are in SQL2000 compatibility mode. Phil From: Michael B. Smith [mailto:[email protected]] Sent: Thursday, February 24, 2011 11:17 AM To: NT System Admin Issues Cc: Jim Kibbie; Laura Clarke Subject: RE: 2003 R2 32-bit Std Edition Memory I'm not a real DBA, although I'm certified as one. :-P If you have money for consulting, I can put you in touch with a SQL MVP who (like me) is an independent consultant. That being said, you can probably find some really good information about tuning at http://www.mssqltips.com, http://www.sqlservercentral.com/ and http://www.simple-talk.com/. Regards, Michael B. Smith Consultant and Exchange MVP http://TheEssentialExchange.com From: Phil Hershey [mailto:[email protected]] Sent: Thursday, February 24, 2011 1:50 PM To: NT System Admin Issues Cc: Jim Kibbie; Laura Clarke Subject: RE: 2003 R2 32-bit Std Edition Memory Thanks, Michael. As usual you're the first with a solid answer. Glad to hear we can upgrade in place. Of course that means there goes another weekend. At least then we can throw more RAM at the system. It's already using over 3 GB of the total 4 GB. It's definitely not CPU or network constrained. On our new system, which has much faster hardware, queries that took noticeably under 2 minutes on the Windows 2000 SP4/SQL 2000 system now take 4-10 minutes. Something is definitely wrong. Wish we had an actual DBA. It's that 'with proper tuning' that's killing us. We have programmers, but no actual DBA with any SQL 2005 training. Phil Hershey Carpinteria, CA From: Michael B. Smith [mailto:[email protected]] Sent: Thursday, February 24, 2011 8:59 AM To: NT System Admin Issues Subject: RE: 2003 R2 32-bit Std Edition Memory You can upgrade in-place to enterprise edition. In almost every case, a properly tuned SQL 2005 database will run rings around a SQL 2000 database. Before you spend USD $3000 on an upgrade, you might should spend a little time with "DBCC UPDATE STATISTICS" and with SQL Profiler. Regards, Michael B. Smith Consultant and Exchange MVP http://TheEssentialExchange.com From: Phil Hershey [mailto:[email protected]] Sent: Thursday, February 24, 2011 11:52 AM To: NT System Admin Issues Subject: 2003 R2 32-bit Std Edition Memory Is the limit for the 32-bit OS 32 GB on x64 processors with PAE enabled? (Been too long since I had to think about this.) We have internal applications that have been moved from an old Server 2000 system with SQL 2000 to a brand new HP G7 server with Server 2003 R2 32-bit (application won't run on 64-bit OS or Server 2008). Now that it's on the new hardware, OS and SQL 2005, the queries are super slow and timing out. Can you upgrade in place from Std Edition to Enterprise? Any ideas? J ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to [email protected] with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to [email protected] with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to [email protected] with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to [email protected] with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to [email protected] with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to [email protected] with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to [email protected] with the body: unsubscribe ntsysadmin
