Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I have an extremely slow sql server 2000 DB with 8GB, and the cause of this is the 3 ntext columns which are in the more queried table. Table has indexes set and also the full-text ssearch index set for these ntext columns, though when "select ntextcol1, ntextcol2 from table" the query takes up to 5min...crazy! But ofc when one knows which row to fecth, by its id which is indexed its fast!

To try to overcome this, I was trying to use sp_tableoption "text in row", 4000 but according to some Msft Technet reading, this will only be performed on existing records only when they're updated. So I was thinking in force an update in the rows so it would trigger the usage of the new option and hopefully have better performance.

This table as 100000 rows and seems scary stuff to do, so I was looking for some input from you and know if you have done it before, if it's a smooth process if any special advice...

share|improve this question
Why do you think these columns are the cause of your problem? Whats the min/max/avg size of a row and of the ntext columns? Are the ntext columns frequently or rarely accessed by the typical workload for the application? Is this the only database on the server? How large is the database (GB)? – Mark Storey-Smith May 31 '12 at 10:57
Because when i do a select to all the columns but these, the select takes a few seconds. 8GB DB and its running together with other 5 DBs which are less accessed and are smaller. These other DBs work respond fine so far... though they're much smaller – byte_slave Jun 1 '12 at 8:43

2 Answers

up vote 1 down vote

Sounds to me you're missing indexes for the queries your application actually uses. select ntextcol1, ntextcol2 from table takes 5 min is not the point: this is a table scan that retrieves all LOBs. Your application (hopefully...) never issues this query. Pulling ntext (you should be using `NVARCHAR(MAX) btw) in row or pushing them out of row is not what you should be looking at.

Read General Index Design Guidelines and all the related articles in the section (clustered index design guidelines, non-clustered index guidelines etc etc). And learn how to identify the problem queries in your app, the indexes should target those queries: Troubleshooting Performance Problems in SQL Server 2005, Waits and Queues

share|improve this answer
Hi Remus! Thanks for your input, the SQL Server instance is 2000, which doesn't support nvarchar(MAX). The table in question has indexes for the ID column (primary key) and full-text search indexes for the ntext columnns. Thanks for the links I will read them and see if i can get some new elements. – byte_slave Jun 1 '12 at 8:49
up vote 0 down vote

Found the answer I needed at http://decipherinfosys.wordpress.com/2007/08/03/text-in-row-option-in-sql-server/. Hope this can also help someonelse in the future with the same issue.

Thanks for all the suggestions you guys provided!

Cheers!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.