Table Optimization Problem - SQL Server

[Jacobus01]

Hi All, I currently have a problem with two tables in SQL Server 2005. The tables have grown very large and sometimes lock up when small amounts of data are requested from them. The current record count is 9731495(PrintMedia) on the one table and 4126084(PrintArticles) on the other one. Table names: PrintArticles and PrintMedia. Whats worse is that these two tables are joined on each other in sql queries. This is because the database is non-relational. So joins are used to substitute for foreign keys. The performance needs to be improved. The SQL server version is 2005 standard edition - So partitioning is not possible :( Here is a breakdown of the Tables with their fields, types and Max sizes: PrintArticles: Analysis int NULL width float NULL height float NULL ArtSize float NULL ID (PK) int NULL Cutting text 2147483647 -This field contains on average 3000 characters CutID int NULL NewbaseNo varchar 20 AnalysisApplic int NULL AnalysisUser nvarchar 50 Translations ntext 1073741823 AnalysisApplicDate datetime NULL AnalysisApplicTime nvarchar 5 PerTranslation text 2147483647 PerTranslated int NULL This table also has 3 indexes: 1 clustered on the PK and 2 non clustered PrintMedia PrintID (PK) int NULL CuttID int NULL Graphic int NULL FpageSection int NULL Caption varchar 1000 PDFPath varchar 300 Branch varchar 30 UploadTime varchar 10 AnalysisTag varchar 500 UserID varchar 30 Modifier varchar 30 DateModified varchar 10 PubID int NULL NewBaseArticleNo varchar 20 Edition varchar 800 IS_HardCopy int NULL Seen_by varchar 300 DTPDate varchar 10 ColourPDF int NULL SpokesPerson varchar 200 Mention varchar 200 NLPU int NULL Server varchar 10 Repl text 2147483647 GroupID int NULL ArticleID int NULL PubDate 5 varchar 10 CreatedDate varchar 10 Publication varchar 200 SubPublication varchar 200 Headline varchar 800 SubHeadline varchar 800 Journalist varchar 500 SubJournalist varchar 500 Page varchar 20 Client varchar 50 Category varchar 200 CategoryValue varchar 200 CategoryDisplayName varchar 200 OrderID varchar 5 TagID varchar 5 Language varchar 20 Section varchar 200 CCM real NULL SizeX real NULL SizeY real NULL RandValue real NULL FrontPageCover int NULL This table has 12 non clustered indexes I have to restart the sql server service at least once a day to remove locks. If that fails I have to reorganize the indexes or rebuild them to get the tables working again. The previous developers wrote applications

[Lost User]

Jacobus01 wrote:

Will it help if I modify all the select statements in these apps to not have locks eg "select [columns] from [tablename] with (nolock) where..."?.

It feels faster, doesn't it? The documentation on MSDN reveals why it's faster;

MSDN[^]:

Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement.

That means that you could be reading data that's not committed. I'd be very carefull if the database contains a lot of stored procedures and utilizes transactions a lot. How are the indexes, and what does the profiler say?

I are Troll :suss:

[Mycroft Holmes]

I assume that CutID is the FK to the tables, just wondering why it is not the primary on one of the table? Totally irrelevant to your problem. I would first put in place a nightly maintenance job that cleaned up the indexes. As to Eddys issue with uncommitted data, I would assume that queries are filtered and therefore this dodgy data should not be an issue except where you are specifically getting the latest information. There tends to be a small window of data that is subject to change, the last few records in the table. If you give your users a caveat that it may be dodgy I see no reason (nolock) should not be used. CAVEAT - note I have never used (nolock) so take this cautiously.

Never underestimate the power of human stupidity RAH