Prev: bcp error
Next: Partition ?
From: Jonathan Crawford on 3 Jun 2010 09:34
Hi

We had a sql server databse on the web server which used ntext fields as it
was in 2000

We ugraded to 2005 and recently i changed the ntext fields to nvarchar(MAX)
so i could use distinct in sql

Since then we have had massive problems with the server with sql consuming
90-100% of
memory.

I was not expecting any problems, and wondered if there were any issues with
nvarchar(MAX)
or if this was just a coincidence

thanks

jc



From: Erland Sommarskog on 3 Jun 2010 17:12
Jonathan Crawford (jc(a)jcrawford.co.uk) writes:
> We had a sql server databse on the web server which used ntext fields
> as it was in 2000
>
> We ugraded to 2005 and recently i changed the ntext fields to
> nvarchar(MAX) so i could use distinct in sql
>
> Since then we have had massive problems with the server with sql
> consuming 90-100% of memory.
>
> I was not expecting any problems, and wondered if there were any issues
> with nvarchar(MAX) or if this was just a coincidence

It could be anything. It is not unusual to experience performance
degradation when you move to a new version of SQL Server. The optimizer
is changed, and sometimes it makes a worse judgement in a specific case
then in a previous version.

One thing to be aware of is that statistics from SQL 2000 is invalidated
when you upgrade from SQL 2000, so you should run sp_updatestats after
the upgrade.

If that does not help, you need to make a more closer performance
analysis to identify the queries that are causing the CPU load.

It is also a good idea to set Max Server Memory for SQL Server, particularly
if there are other applications like a web server running on the machine
as well.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: bcp error
Next: Partition ?