Performance is degraded drastically after migrating to SQL Express 2005 from MSDE 2000

SasiInfy

Hi,

Recently we have migrated our application from MSDE 2000 to SQL Server Express 2005(SP 1). This has significantly reduced the performance of our Windows.NET application which is developed using C#.

For example : While logging in to the application two databases are being attached.

Time taken in MSDE: 16 secs

Time taken in SQL Server Express 2005 : 58 secs

Also note performance is degraded for normal screens where data is retrived from database using inline queries.

Questions:

1) Is there any special(optimum) configuration(installation parameters) while installing the SQL Server Express 2005 setup

2) Is there any query optimization to be done w.r.t SQL Server Express 2005

We highly appreciate any help towards resolving the above problem.

Regards,

Sasi

Andrea Montanari

hi Sasi,

SQL Server 2005, and thus SQLExpress, is "heavier" on resources than SQL Server 2000 and MSDE 2000... personally I noticed a "degradation" as well on "older" machines , but not to the extremes you are pointing out..

AFAIK there are no optimizations to be done at intallation time that can boost the service but the "traditional" considerations available for MSDE as well.. in "supported" scenarios (where I know there's someone somehow skilled to follow and troubleshoot the IT "stuffs") I do usually turn off the autoclose database's property in order to give little improvements...

regards

William Vaughn

SQL Express supports an entirely new paradigm triggered by the "User Instance=True" keyword in the ConnectionString. This is the default behavior in Visual Studio--especially if you include the MDF in your project (which I don't recommend). If enabled, this option copies the master, model and tempdb to your user area on disk along with a(nother) copy of your MDF before launching an entirely new (private) instance of SQL Server. Yup, this can take some time. It's another reason I don't (universally) endorse it. See www.hitchhikerguides.net for more information...

SasiInfy

Hi,

We are using User Instance = False in connection string. Anybody can provide any resolutions

Thx

Sasi

klaus_b

Hi,

in sql2005 express you can use the same connectionstrings like in all other sql2005 editions. The following i use in a webproject and it works very fine:

<connectionStrings>
<clear/>
<add name= "Sup" connectionString= "Server=.\SQLEXPRESS;Database=Support;Trusted_Connection=True;
Asynchronous Processing=True;MultipleActiveResultsets=True;" providerName="System.Data.SqlClient"/>
...
</connectionStrings>

The "Server=.\" means the local machine. Here you can say every name where you will connect to.
The "SQLEXPRESS" is the instance of the sql Server installed on the machine.

Hope this helps
Servus Klaus

Andrea Montanari

hi Klaus,

klaus_b wrote:

Hi, in sql2005 express you can use the same connectionstrings like in all other sql2005 editions. The following i use in a webproject and it works very fine: <connectionStrings> <clear/> <add name= "Sup" connectionString= "Server=.\SQLEXPRESS;Database=Support;Trusted_Connection=True; Asynchronous Processing=True;MultipleActiveResultsets=True;" providerName="System.Data.SqlClient"/> ... </connectionStrings>

AFAIK, "Asynchronous Processing" is not a valid nor recognized property, but "Async"..http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

The "Server=.\" means the local machine. Here you can say every name where you will connect to.
The "SQLEXPRESS" is the instance of the sql Server installed on the machine.

"Server=." means local machine, as "Server=(Local)" and the like... the slash is not included .. it's required when a named instance is involved, where the full name becames ComputerName\InstanceName (and/or all the variations of LocalComputer)..

anyway all these properties, MARS included, do not provide actual speed benefit as "required" by Sasi..

sometimes MARS could be such a benefit but I usually doubt..

regards

Arnie Rowland

After migrating to SQL 2005, it is suggested to rebuild the indexes and update any other needed statistics.

Query optimization 'could' be necessary since SQL 2005 uses a different query optimization process in the query processor. (Though I haven't seen much necessity for this.)

Andrea Montanari

yep... nice shot, Arnie...

regards

SasiInfy

Hi all,

We tried rebuilding indexes in our application databases using SQL Express. But, it does not help anything as it gives same performance as earlier.

Through profiler, we identified that performance is degraded drastically (200%) in "Insert" queries. Pls. note that we have similar indexes in MSDE 2000 & SQL Express 2005 databases.

Sasi