Amazing what indexes ( Clustered/Non-Clustered) can do to a query time, glad it got settled, SQL performance and tuning is an art form in its own right, but its kinda fun at the same time.
Z Edward E. Ziots CISSP, Network +, Security + Network Engineer Lifespan Organization Email:ezi...@lifespan.org Cell:401-639-3505 From: Phil Hershey [mailto:phers...@agia.com] Sent: Monday, February 28, 2011 12:28 PM To: NT System Admin Issues Subject: RE: 2003 R2 32-bit Std Edition Memory Thanks, folks. One of our SQL programmers, who'd previously sworn that all the queries were already optimized, added a new index on Friday. Now the queries that were taking 4-10 minutes all complete in under 1 minute. J Now everybody's happy. Myself included. Thanks again. Phil From: Michael B. Smith [mailto:mich...@smithcons.com] Sent: Thursday, February 24, 2011 3: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:phers...@agia.com] 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:mich...@smithcons.com] 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:phers...@agia.com] 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:mich...@smithcons.com] 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:phers...@agia.com] 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 listmana...@lyris.sunbeltsoftware.com 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 listmana...@lyris.sunbeltsoftware.com 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 listmana...@lyris.sunbeltsoftware.com 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 listmana...@lyris.sunbeltsoftware.com 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 listmana...@lyris.sunbeltsoftware.com 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 listmana...@lyris.sunbeltsoftware.com 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 listmana...@lyris.sunbeltsoftware.com 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 listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin
