.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Poor performance on Sql 2005 vs. Sql 2000 - AGAIN!

Posted By:      Posted Date: September 20, 2010    Points: 0   Category :Sql Server

I was hoping I wouldn't be another poster with performance issues after migrating to SQl 2005 from SQL 2000 but here I am.


I am in the process of testing out our databases on Sql Server 2005 for migration from SQL Server 2000 and there are certain portions of code that have been affected negatively. I have read thru many of the posts here and have tried out most of the recommendations. I will start out with things I've done and then provide the actual SQL.


1) I have rebuilt all indexes ( using the DBCC REINDEX using the table option).

2) Updated the db engine to latest hot fix (build 3239) that addresses speed related fixes.

3) I also ran sp_createstats using the 'fullscan' option to create stats on all columns of all tables (minus indexed columns)

4) Since nothing seemed to work, I even ran UPDATE STATICS with FULL SCAN on all tables even though I did not need it as the REBUILD woudl have created stats. But I was willing to try anything.


I have confirmed that the execution plans are different even though the data on both sql 2000 and sql 2005 are identical (i put a copy on 2005). The plans themselves are huge as the queries are huge. Here is the query.

View Complete Post

More Related Resource Links

migration MSDE 2000 SP3 database to SQL 2005 SP3: low performance on 2005 Express

I've detach MSDE 2000 SP3 database. I've attach into SQL 2005 Express SP3 and SQL 2005 Standard SP3. I've change compatibility level to 90. I've update statistics with EXEC sp_MSforeachtable @command1="UPDATE STATISTICS ? WITH FULLSCAN" The execution time of a TSQL into the same hardware (1 CPU, Quad Core) are: SQL 2005 Express SP3: 20" SQL 2005 Standard SP3: 0" The execution plan is different. Why?

sql server 2000 vs 2005


i would like to ask what the difference between sql server 2000 and 2005 

COM+ and Windows 2000: Ten Tips and Tricks for Maximizing COM+ Performance


There are many tricks to getting the most out of COM+, and this article offers the author's top 10. The tips cover the importance of transaction processing, the use of the COM+ catalog, and the design of three-tier distributed systems. Writing components using the correct threading model, knowing when to use compensating transactions, and the importance of stress testing early in the process also make the list. Other indespensible suggestions emphasize the importance of recognizing where an object's state is located, choosing appropriate authentication levels for COM+ applications, using Queued Components correctly, and implementing object pooling.

David S. Platt

MSDN Magazine December 2000

SQL Server 2005 Performance Dashboard Reports user context

HI, We are planning on installing the Dashboard reports. But we'd like to know if the Dashboard is run under admin user or the login user who runs the Dashboard reports? Thanks! Kong

Oracle Linked serve Query performance in 2000 vs 2008 R2 64 Bit

Hi everyone We have started to migrate one of our reporting systems from Sql 2000 to Sql 2008 R2.  One of the steps has been to test the perforance of certain Oracle linked server queries between each server.  We are finding on average 3 fold better perforance stats (in terms of query completion time) on the old server.  This should obviously not be the case.  The new server has signifantly more CPU/Memory/IO resources to play with, and it is 64 bit (not to mention its new!).  Here's what I got so far: Old server: SQL 2000 on W2000 both fully patched.  Old Dell Dual core with 3 GB of Ram running on two soft IDE Mirrored drives (yes I know... it sucks).  It connecting via the MS OLE DB provider for Oracle (9i client) New server: ESX VMware Server with 2 CPU's assigned, 8 GB of ram connected to large HP SAN.  CPU, ram and IO's have all been ruled out as the problem.  We've tried varying network cards with different results so we havent ruled that out yet.  Its connecting via the Oracle provider for OLE DB (11G 64 bit client) The linked server is an Oracle 9i fully patched server.  All three are on the same network backbone. Running a simple select * query on both servers returns the same number of rows (~76 000) .  It takes ~1:20 on the new server and ~0:20 on the old server. In looking at the wait stati

Very poor performance from SQL/CLR

I have been developing with SQL/CLR recently, by creating stored procedures in C# and installing these in SQL Server 2008. It has gradually emerged that performance of the code when run in SQL Server is far far worse than when run as an ordinary .Net app. I have benchmark C# code that runs in 0.5 seconds in a console app, but takes around 8 or 9 seconds when installed and run as a stored proc in SQL Server 2008. We are running the 64-bit edition of SQL Server on 64-bit Vista. Now I just read this: "Managed code may incur additional overhead necessary to maintain the execution environment and perform security checks. This is the case, for example, when running inside SQL Server and numerous transitions from managed to native code are required (because SQL Server needs to do additional maintenance on thread-specific settings when moving out to native code and back). Consequently, extended stored procedures can significantly outperform managed code running inside SQL Server for cases in which there are frequent transitions between managed and native code." and this worries me, because our app uses a library that does a great deal of calling into some native API, so I know the code is transitioning to/from native code a great deal. I have to say that I am astonished at the degree of slow down, I need to find out if there is anything we can do to reduce this excessiv

SQL 2000 backup to SQL 2005

Hi I have this problem  My management application ( using Delphi ) was using a database in SQL 2000 I planning to upgrade the SQL Server 2000 to 2005 same Standard Version  The migration using simple method : 1. Backup the db in SQL 2000 2. Create a db with same name in SQL 2005 with compatibility back to SQL 2000 3. Restore the backup to SQL 2005 and ready to use I can select update insert do all the transaction in SQL 2005 normally but the problem is the managment system always report can't connect to SQL Server and also one problem I cannot change the management system code since it create by somebody outside the company and already long gone or not care about this software anymore. So my only hope is to see if there something wrong with the SQL Server I check the typical sql syntax for delphi to connect to SQL Server there is no different between connecting to 2000 or 2005 the connection string also similar to other programming language. ( I also try using my own built application in C# it works well i just need to change the dsn on the IP part ) From the error report by the old program ( the one using Delphi ) it indicates that it was user pass problem, so I recreate the user in the sql 2005 version to be the same as SQL 2000 than check using the .udl it both connect well, but from the software still report it can't connect. It also appear that if I

SQL Server 2000, or SQL Server 2005 failover clusters on the 32-bit subsystem (WOW64)

Hi All I don't know what is WOW64,some where I read "SQL Server 2000, or SQL Server 2005 failover clusters on the 32-bit subsystem (WOW64)" In my environment SQL Server 2000 version is "Microsoft SQL Server  2000 - 8.00.2282 (Intel X86)   Dec 30 2008 02:22:41   Copyright (c) 1988-2003 Microsoft Corporation  Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2) " is it same ?? How find out my environment is WOW or NOT. Thanks in advance          SNIVAS

SQL server poor performance - Urgent help

I have a windows 2003 Server Processor 880, 2.40GHz and 8Gb RamIts 64 bit  - SQL 2005It is on a SAN with Raid 5 on disksThere is one 50gb database mainly being used.The Server CPU is constantly up and down and Maxing out every few mins.There are runnable processes running and numerous suspended.Running Sp_who2 doesn't give much info and often shows no blocking or anything noticeable.Users are getting timeouts and not able to use the system.I looked at the memory allocation it was ;The Results were;name minimum maximum config_value run_valuemax server memory (MB) 16 2147483647 2147483647 2147483647name minimum maximum config_value run_valuemin server memory (MB) 0 2147483647 0 0 I have updated this and amended BOTH to be 6500, being max and min but not certain this is right.I can see in task manager in the performance window that it's using at 8gb, but if you look at the processes tab sqlserver.exe is only using 149 858k. I do not understand why sqlserver.exe is NOT using the available memory ? For example I ran DBCC checkdb and the only user on the server as it's out of hours and this task alone maxes out the CPU.I am also about to rebuild indexes and complete some other maintenance tasks.I'm very puzzled that simple tasks are maxing out the CPU but no memory is being used. My company is unable to complete ANY work…..this is urgent please help.Thanks

Restoring Databases form SQL Server 2000 and SQL Server 2005 to SQL Server 2008 -Side by Side --he

Hi all I Installed SQL Server 2008 R2 in Server and I took backup of all the User databases backup from 2000 and 2005(two instance are running) Now I am doing restore the Databases,while I am restoring I am information icon in bottom of the restore window that is "The Full-Text Upgrade Option server property controls whether full-text indexes are improved,rebuild or reset" that means I have to upgrade full-text indexes or this just inforamtion.If I want find out whither Database using full-text how to check ? once I done this I have to move the users as well.Please dome body share the script you have.   Thanks in AdvanceSNIVAS

SQL Server 2005 Analysis Services - Performance of Excel Pivot Tables

Hi, I've several cubes available for several users within my organization. I've the SQL Server 2005 and the Analysis Services running on the same machine but it has 30Gb of RAM, 8 processors and a quick enouth disk. The problems is that i've some automatic processes that "build" some excel reports and the users use pivot tables with olap connections to the cubes regularly. I've noticed that the excel "locks" when the user is drilling down the data and the server has only one processor (there are 8...) at 100%... Is there a way to make the analysis services use the maximum processing capacity that is available? The users are complaining about the slowness of the cubes... Thanks in advance Hugo

copy partition data from 2000 cube to 2005 cube



i am upgrading 2000 olap cube to 2005.  i built an ssas project and deploy to the server.

the 2000 olap cube store it's data in partitions - one for each month.

the face table delete it's data and store data for only the last 13 month so basically most of the data

is store onle in the 2000 cube.

while upgrading the cube and processed it , a lot of information had lost.

i know that i cant restore the fact from the partiotn data but is there a way to copy the partition has is

from olap 2000 to 2005?


Can I run Sql server 2000 client tools and SQL server 2005 on teh same machine.



Could you please advise me on this issue.

I have installed sql server 2000 client tools, and complete set up of sql server 2005 on my system.

The enterprise manager on sql 2000, crashes and and window vanishes, when ever i try to create DTS package.

They doubt its becos i am runnning both instance of sql server on the same machine- So its having some compactability issues.?

Can we run SQL server 2000, 2005 and 2008 on same machine with different instance ?

I am using windows 7 and i3 processor.

Thanks in advance.



DTS on sql server 2000 and 2005


Here at work most of us have SQL Server 2000 and 2005 installed on our machines. When one of us creates a 2000 DTS package on our machine, it cannot be opened on on any other machine with the same setup. It will only open on the machine where it was created. We have had to use a machine that has only a 2000 installation in order to create DTS packages that can be opened on other machines.

Is there a solution to this problem?



cannot restore sql 2000 database in sql server 2005 in windows 7


cannot restore the databse made in sql server 2000 in sql server 2005 in windows 7 with thw following error message

TITLE: Microsoft SQL Server Management Studio Express

Restore failed for Server 'KHURANA\SQLEXPRESS'.  (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476


System.Data.SqlClient.SqlError: The media set has 2 media families but only 1 are provided. All members must be provided. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&LinkId=20476



plz help me out

How to restore SQL 2000 DB to SQL 2005

Hi, All
I am trying to restore SQL 2000 DB into SQL 2005 Database.
I backup the database from SQL 2000.

From Management Suit, I try to restore database but I can not see network drive from there even though SQL serveris running under network account.
I could see network drive from SQL 2000 or at least I can type path to find backup file. However I can not do this..

My Q is:
1. How can I restore this SQL 2000 db to SQL 2005 using network path?
2. Since backup is SQL 2000 database, when I restore into SQL 2005, does restore upgrade system tables and other schema to SQL 2005 as well?
If not, what is the best practice to upgrade this database into SQL 2005?
Upgrading SQL 2000 current server to SQL 2005 is not an option at this point.
Eventually porduction server will be scrup and install SQL 2005 and then restore DBs into production machine...

Thx in advance

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend