December 2, 2008 at 2:49 am
I have a table with about 50,000 records. There are a lot of ntext fileds (about 20) in this table. The database was migrated from SQL server 2000 to 2005. If I use a query lke this
Select count(*) from table where ntextfield is null
it takes over 3 minutes to execute. On sql server 2000 this was a matter of a few seconds.
What can be a reason of such perfromance loss? Is there any help to convert all ntext fields to nvarchar(MAX)? Any help is appreciated.
December 2, 2008 at 4:36 am
did you perform post-migration maintenance ?
/ * To be executed after a db-restore from a lower version than sqlserver 2005 !
*
*DATA_PURITY
* Causes DBCC CHECKDB to check the database for column values that are not valid or out-of-range.
* For example, DBCC CHECKDB detects columns with date and time values that are larger than or less
* than the acceptable range for the datetime data type; or decimal or approximate-numeric data type columns
* with scale or precision values that are not valid.
*
* For databases created in SQL Server 2005, column-value integrity checks are enabled by default and
* do not require the DATA_PURITY option.
* For databases upgraded from earlier versions of SQL Server, column-value checks are not enabled by default
* until DBCC CHECKDB WITH DATA_PURITY has been run error free on the database.
* After this, DBCC CHECKDB checks column-value integrity by default.
*
* For more information about how CHECKDB might be affected by upgrading database from earlier versions of SQL Server, see the Remarks section later in this topic.
*
* If PHYSICAL_ONLY is specified, column-integrity checks are not performed.
*
* Validation errors reported by this option cannot be fixed by using DBCC repair options.
* For information about manually correcting these errors, see Knowledge Base article 923247: Troubleshooting DBCC error 2570 in SQL Server 2005.
* http://support.microsoft.com/kb/923247
*
*/
-- checkdb with content-re-eveluation
DBCC CHECKDB (0) WITH ALL_ERRORMSGS, DATA_PURITY;
go
print '' ;
print '*********************************'
print '**'
print '** If DBCC CheckDB returned DATA_PURITY errors, you must view http://support.microsoft.com/kb/923247 !!! and repair the issues !!!'
print '*********************************'
print '**'
print '' ;
print '--' ;
Print '-- Now execute SQL: Gen en Execute Rebuild tables and indexesl';
go
print '--' ;
print '' ;
print '--' ;
-- Check DbLevel
if not exists ( select 1
from master.sys.databases
where [name] = db_name()
and compatibility_level = 90 )
Print '-- If possible update dblevel : Exec sp_dbcmptlevel @dbname = '''+ db_name() +''' , @new_cmptlevel = 90 ;'
go
- Rebuild _all_ indexes
- dbcc updateusage(yourdb) with count_rows
and off course
sp_updatestats
If you can, you should indeed alter ntext to nvarchar(max) because that will give you all string-functions and simple update means for these columns.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 2, 2008 at 8:29 am
Have you taken a look at the execution plans? Are different tasks being performed by the system?
Second vote for making sure you did maintenance after the upgrade, especially updating statistics with a full scan.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply