|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Upgraded to 2005, but its slow??Hi,
For some reasons, our new SQL 2005 is running slower than our old MS Sql server. Is there a tool where I can analyze if there are some differences on indexes on different tables across the two SQL servers? Thnx One of the post-upgrade recommendations in Books Online is to update
statistics. You might want to start with that and see if things improve. From the topic How to: Upgrade to SQL Server 2005 (Setup) a.. Update statistics - To help optimize query performance, we recommend that you update statistics on all databases following upgrade. Use the sp_updatestats stored procedure to update statistics in user-defined tables in SQL Server 2005 databases. -- Show quoteGail Erickson [MS] SQL Server Documentation Team This posting is provided "AS IS" with no warranties, and confers no rights Download the latest version of Books Online from http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx "mavrick_101" <mavrick***@discussions.microsoft.com> wrote in message news:61EE5A97-B67F-44B7-978A-D3E7A43C7965@microsoft.com... > Hi, > > For some reasons, our new SQL 2005 is running slower than our old MS Sql > server. > > Is there a tool where I can analyze if there are some differences on > indexes > on different tables across the two SQL servers? > > Thnx "mavrick_101" <mavrick***@discussions.microsoft.com> wrote in message We just upgraded from SQL Server 2000 to 2005, and we've also found that news:61EE5A97-B67F-44B7-978A-D3E7A43C7965@microsoft.com... > Hi, > > For some reasons, our new SQL 2005 is running slower than our old MS Sql > server. select performance is slower. Insert performance seems to be slightly faster (by around 5% or so), but queries that return medium-to-large result sets are running anywhere from 50% to 100% slower. Updating statistics and rebuilding indexes made no difference. We're very disappointed with the performance of SQL Server 2005. --Rob Roberts I'm not sure if this makes a difference in performance but are your
databases set to compatibility mode 8.0 or 9.0 ? If you just restore them from the old database they will be set to compatibility mode 8.0 (SQL 2000). You might also want to run the profiler and see what it suggests, there are some news in indexes for SQL Server 2005. KL. Show quote "Rob Roberts" <robrRemoveT***@AndThisToo.pcisys.net> skrev i meddelandet news:ui4Ab7SxGHA.1808@TK2MSFTNGP06.phx.gbl... > "mavrick_101" <mavrick***@discussions.microsoft.com> wrote in message > news:61EE5A97-B67F-44B7-978A-D3E7A43C7965@microsoft.com... >> Hi, >> >> For some reasons, our new SQL 2005 is running slower than our old MS Sql >> server. > > We just upgraded from SQL Server 2000 to 2005, and we've also found that > select performance is slower. Insert performance seems to be slightly > faster (by around 5% or so), but queries that return medium-to-large > result sets are running anywhere from 50% to 100% slower. Updating > statistics and rebuilding indexes made no difference. We're very > disappointed with the performance of SQL Server 2005. > > --Rob Roberts > > KL,
> I'm not sure if this makes a difference in performance but are your I changed the compatibility mode from 8.0 to 9.0 immediately after upgrading > databases set to compatibility mode 8.0 or 9.0 ? If you just restore them > from the old database they will be set to compatibility mode 8.0 (SQL > 2000). from SQL Server 2000 to 2005. I updated the statistics and rebuilt the indexes after changing the compatibility mode to 9.0. --Rob Roberts Hi,
Thanks for your response. I am not very familiar with profiler. Do you suggest to keep it running for all possible queries and it would prompt some recommendations, if any as required? Thnx. Show quote "KL" wrote: > > I'm not sure if this makes a difference in performance but are your > databases set to compatibility mode 8.0 or 9.0 ? If you just restore them > from the old database they will be set to compatibility mode 8.0 (SQL 2000). > > You might also want to run the profiler and see what it suggests, there are > some news in indexes for SQL Server 2005. > > KL. > > > "Rob Roberts" <robrRemoveT***@AndThisToo.pcisys.net> skrev i meddelandet > news:ui4Ab7SxGHA.1808@TK2MSFTNGP06.phx.gbl... > > "mavrick_101" <mavrick***@discussions.microsoft.com> wrote in message > > news:61EE5A97-B67F-44B7-978A-D3E7A43C7965@microsoft.com... > >> Hi, > >> > >> For some reasons, our new SQL 2005 is running slower than our old MS Sql > >> server. > > > > We just upgraded from SQL Server 2000 to 2005, and we've also found that > > select performance is slower. Insert performance seems to be slightly > > faster (by around 5% or so), but queries that return medium-to-large > > result sets are running anywhere from 50% to 100% slower. Updating > > statistics and rebuilding indexes made no difference. We're very > > disappointed with the performance of SQL Server 2005. > > > > --Rob Roberts > > > > > > > Hi everyone,
I'm running a simple select statement on one table that contains less than 1M records and it's running and running and running very slooowwly. I have change the compatibility mode and I ran Update Statistics on the table and it is still the same. Any other ideas that I can try? Audrey *** Sent via Developersdex http://www.developersdex.com ***  |
|||||||||||||||||||||||
Other interesting topics