SQL 2000 Std on Win 2003 Ent R2 - MEMORY setings

Question

Hi!
I have SQL 2000 standard SP4 installed on Win 2003 enterprise R2 SP2.
Hardvare: HP ProLiant DL 380 G5; Intel Xeon 5110 x 2; RAM 8GB (all drivers are up to date)
System crashes every once in a while (freezes) with no usefull informations in log files (win and sql) and has to be manually restarted. It usually hapens arround the time of scheduled index rebuild (but not allways).
It seems to me that it has some memory related problem.
-No /3GB /PAE switch in boot.ini (because of SQL 2000 Std)
-AWE off
-Dynamic memory allocation
-Minimum querry memory: 1024

Q: What would be recomended memory settings for this system?
     (/3GB; /PAE; AWE; Page file size; Virtual Memory; etc...)

P.S. I have to work with this versions of sql and win for next year so please help!

Answer 1

Well , even if you Windows server can address up to 32 Gbytes (as it is ENT edition)  , still SQL server STD won't be able to address more than two Gbytes .

AWE is not available on SQL Server 2000 STD Edition.

I don't think you have much to do here with memory if you are stuck with SQL 2000 STD but I can suggest few points :

1- Install SQL server 2000 post Sp4 patch : http://support.microsoft.com/kb/916287

2- How do you rebuild you indexes ? using maintenance plans ?

I would recommend maintaining your indexes based on fragmentation level to reduce the footprint rebuilding indexes will have on resources.

The following code is based on Kimberly Trip article to rebuild/defrag indexes  based on scan density , I added fragmentation level check as well.

YOU MUST test that  SP very well. Both SPs should be created in Master database then mark them as system SPs

Alter  PROCEDURE sp_RebuildIndexes
(
	@TableName		sysname		= NULL,
	@ScanDensity 	tinyint		= 70,
	@Fragmentation tinyint = 20
)
AS
--
-- This procedure will get the Fragmentation information
-- for all tables and indexes within the database. 
-- Programmatically it will then walk the list rebuilding all
-- indexes that have a scan density less than the value 
-- passed in - by default any less than 100% contiguous.
-- 
-- Use this script as a starting point. Modify it for your
-- options, ideas, etc. - and then schedule it to run regularly.
-- 
-- NOTE - This gathers density information for all tables 
-- and all indexes. This might be time consuming on large
-- databases. 
-- 

SET NOCOUNT ON

IF @ScanDensity IS NULL
	SET @ScanDensity = 70
 
IF @ScanDensity NOT BETWEEN 1 AND 70
	BEGIN
		RAISERROR('Value supplied:%i is not valid. @ScanDensity is a percentage. Please supply a value for Scan Density between 1 and 100.', 16, 1, @ScanDensity)
		RETURN
	END
IF @TableName IS NOT NULL
	BEGIN
		IF OBJECTPROPERTY(object_id(@TableName), 'IsUserTable') = 0 
			BEGIN
				RAISERROR('Object: %s exists but is NOT a User-defined Table. This procedure only accepts valid table names to process for index rebuilds.', 16, 1, @TableName)
				RETURN
			END
		ELSE
			BEGIN
				IF OBJECTPROPERTY(object_id(@TableName), 'IsTable') IS NULL
					BEGIN
						RAISERROR('Object: %s does not exist within this database. Please check the table name and location (which database?). This procedure only accepts existing table names to process for index rebuilds.', 16, 1, @TableName)
						RETURN
					END
			END
    END

-- Otherwise the Object Exists and it is a table so we'll continue from here. 
-- First thing to do is create a temp location for the data returned from DBCC SHOWCONTIG

CREATE TABLE #ShowContigOutput
(
	ObjectName			sysname,
	ObjectId				int,
	IndexName				sysname,
	IndexId					tinyint,
	[Level]					tinyint,
	Pages					int,
	[Rows]					bigint,
	MinimumRecordSize		smallint,
	MaximumRecordSize	smallint,
	AverageRecordSize		smallint,
	ForwardedRecords		bigint,
	Extents					int,
	ExtentSwitches			numeric(10,2),
	AverageFreeBytes		numeric(10,2),
	AveragePageDensity	numeric(10,2),
	ScanDensity			numeric(10,2),
	BestCount				int,
	ActualCount			int,
	LogicalFragmentation	numeric(10,2),
	ExtentFragmentation	numeric(10,2)
)                            

IF @TableName IS NOT NULL -- then we only need the showcontig output for that table
	INSERT #ShowContigOutput
		EXEC('DBCC SHOWCONTIG (' + @TableName + ') WITH FAST, ALL_INDEXES, TABLERESULTS') 
ELSE -- All Tables, All Indexes Will be processed.
	INSERT #ShowContigOutput
		EXEC('DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS,NO_INFOMSGS ') 
PRINT N' '

-- Quick test to see if everything is getting here correctly
-- SELECT * FROM #ShowContigOutput

-- Walk the showcontig output table skipping all replication tables as well as all tables necessary for
-- the UI. This is also where you can list large tables that you don't want to rebuild all at one time.
-- NOTE: If you take out a large table from rebuilding this script may have already checked density
-- meaning that the expense in terms of time may have been expensive.
-- Also, you should use a different procedure to rebuild a large table specifically. 
-- Even when you pass in the tablename it will be avoided here if MANUALLY added to the 


DECLARE @ObjectName				sysname,
			@IndexName			sysname,
			@QObjectName			nvarchar(258),
			@QIndexName			nvarchar(258),
			@IndexID				tinyint,
			@ActualScanDensity	numeric(10,2),
			@LogicalFragmentation numeric(10,2),			
			@IndexRebuildLogID bigint
--select * from #ShowContigOutput
DECLARE TableIndexList CURSOR FAST_FORWARD FOR 
	SELECT ObjectName, IndexName, IndexID, ScanDensity,LogicalFragmentation 
	FROM #ShowContigOutput AS sc
		JOIN sysobjects AS so ON sc.ObjectID = so.id

	WHERE (sc.ScanDensity < @ScanDensity or LogicalFragmentation > @Fragmentation)
		AND (OBJECTPROPERTY(sc.ObjectID, 'IsUserTable') = 1 
				OR OBJECTPROPERTY(sc.ObjectID, 'IsView') = 1)
		AND so.STATUS > 0
		AND sc.IndexID BETWEEN 1 AND 250 
		AND sc.ObjectName NOT IN ('dtproperties') 
			-- Here you can list large tables you do not WANT rebuilt.
	ORDER BY sc.ObjectName, sc.IndexID

OPEN TableIndexList

FETCH NEXT FROM TableIndexList 
	INTO @ObjectName, @IndexName, @IndexID, @ActualScanDensity,@LogicalFragmentation

WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
		SELECT @QObjectName = QUOTENAME(@ObjectName, ']')
		SELECT @QIndexName = QUOTENAME(@IndexName, ']')
			IF @IndexID = 1 
		BEGIN
	
			If @LogicalFragmentation > 30
			Begin
				
							
				EXEC sp_RebuildClusteredIndex @ObjectName, @IndexName
				
			
			End
			
			If @LogicalFragmentation <= 30 and @LogicalFragmentation >= 20
			
			Begin
			
											
				EXEC('DBCC INDEXDEFRAG (0,' + @QObjectName + ', ' + @QIndexName + ') WITH NO_INFOMSGS')
				
				
			End
			
		END
		ELSE
		BEGIN
			If @LogicalFragmentation > 30
			Begin
			
											
				EXEC('DBCC DBREINDEX(' + @QObjectName + ', ' + @QIndexName + ') WITH NO_INFOMSGS')
							
			End
			
			If @LogicalFragmentation <= 30 and @LogicalFragmentation >= 20
			
			Begin
											
				EXEC('DBCC INDEXDEFRAG (0,' + @QObjectName + ', ' + @QIndexName + ') WITH NO_INFOMSGS')
				
								
			End
		END
		
		FETCH NEXT FROM TableIndexList 
			INTO @ObjectName, @IndexName, @IndexID, @ActualScanDensity,@LogicalFragmentation
	END
END
PRINT N' '
Close TableIndexList
DEALLOCATE TableIndexList 

GO

CREATE PROCEDURE sp_RebuildClusteredIndex
(
	@TableName		sysname		= NULL,
	@IndexName		sysname		= NULL
)		
AS

IF @TableName IS NOT NULL
	BEGIN
		IF (OBJECTPROPERTY(object_id(@TableName), 'IsUserTable') = 0 
				AND OBJECTPROPERTY(object_id(@TableName), 'IsView') = 0) 
			BEGIN
				RAISERROR('Object: %s exists but is NOT a User-defined Table. This procedure only accepts valid table names to process for index rebuilds.', 16, 1, @TableName)
				RETURN
			END
		ELSE
			BEGIN
				IF OBJECTPROPERTY(object_id(@TableName), 'IsTable') IS NULL
					BEGIN
						RAISERROR('Object: %s does not exist within this database. Please check the table name and location (which database?). This procedure only accepts existing table names to process for index rebuilds.', 16, 1, @TableName)
						RETURN
					END
			END
    END

IF @IndexName IS NOT NULL
	BEGIN
		IF INDEXPROPERTY(object_id(@TableName), @IndexName, 'IsClustered') = 0
			BEGIN
				RAISERROR('Index: %s exists but is a Clustered Index. This procedure only accepts valid table names and their clustered indexes for rebuilds.', 16, 1, @IndexName)
				RETURN
			END
		ELSE
			BEGIN
				IF INDEXPROPERTY(object_id(@TableName), @IndexName, 'IsClustered') IS NULL
					BEGIN
						SELECT @TableName, @IndexName, INDEXPROPERTY(object_id(@TableName), @IndexName, 'IsClustered')
						RAISERROR('There is no index with name:%s on this table. Please check the table name and index name as well as location (which database?). This procedure only accepts existing table names and their clustered indexes for rebuilds.', 16, 1, @IndexName)
						RETURN
					END
			END
    END
		-- Get the Column List and Index Defintion (Use the output from sp_helpindex)
		-- Figure out if it's UNIQUE - to specify in CREATE INDEX statement
		-- Build and Execute the CREATE INDEX command through dynamic string execution

DECLARE @ExecStr		nvarchar(4000) -- more than enough even if 16 cols of 128 chars, 
									   -- Tablename of 128 and Indexname of 128...
									   -- but if this is the case you have other problems :).
		, @ColList		nvarchar(3000)
		, @Unique		nvarchar(7)	   -- Will be either '' or 'Unique ' and added to CR Index String
		, @FillFactor	nvarchar(100)

CREATE TABLE #IndexInfo
(
	IndexName	sysname,
	IndexDesc	varchar(210),
	IndexKeys	nvarchar(2126)
)

INSERT INTO #IndexInfo 
	EXEC sp_helpindex @TableName

SELECT @ColList = IndexKeys
		 , @Unique = CASE 
						WHEN IndexDesc LIKE 'clustered, unique%' 
							THEN 'Unique '
						ELSE ''
					END --CASE Expression
		 , @FillFactor = ', FILLFACTOR = ' + NULLIF(convert(nvarchar(3), 
						(SELECT OrigFillFactor 
							FROM sysindexes 
							WHERE id = object_id(@TableName) 
								AND Name = @IndexName)), 0)
FROM #IndexInfo
WHERE IndexName = @IndexName

SELECT @ExecStr = 'CREATE ' + @Unique + 'CLUSTERED INDEX ' 
						+ QUOTENAME(@IndexName, ']') + ' ON ' 
						+ QUOTENAME(@TableName, ']') + '(' + @collist 
						+ ') WITH DROP_EXISTING ' + ISNULL(@FillFactor, '')

SELECT @ExecStr = REPLACE(@ExecStr, '(-)', ' DESC')

-- For testing the String
-- SELECT @ExecStr

-- Create the Clustered Index
EXEC(@ExecStr)
go

mark Sp as a system SP

USE MASTER
GO
EXECUTE sp_ms_marksystemobject 'sp_RebuildIndexes'
GO

EXECUTE sp_ms_marksystemobject 'sp_RebuildClusteredIndex'
GO

HTH

---

Please mark as answer if you think this answers your questions

Answer 2

Thank you for fast reply.

I'll install patches for sure.
Will try this procedures as well. (Yes, rebuilding is done through maintenance plans)

But, as I mentioned, server stuck sometimes for no particular reason. Tested rebuilding on XP with 1GB RAM, as well as memory demanding queries, and everithing works - slowly but works. Everything worked well at old server (DELL with 2GB RAM)(Databases size: 2 x 2GB)

I haven't seen anything in SQL log that could say that crash is caused by SQL.

Answer 3

Some other points to check :

1- Gather SQl server waits.

 create a job to gather waitstats , Use following code (based on a technet blog) ,

create table Waits ([wait_type] varchar(100),

                        requests numeric(20,1),

                        [wait_time] numeric (20,1),

                        [signal_wait_time] numeric(20,1),

                        time_ datetime default getdate())

While (1=1)

BEGIN

insert into Waits ([wait_type],requests,wait_time ,signal_wait_time)

               
exec ('dbcc sqlperf(waitstats)')

waitfor delay  '00:00:30'

END

 

-----------------------------------------------------

 

After you run the job for a day or two , you can get the waits by running this

 

select wait_type,wait_time, '%'=cast (100*[wait_time]/ 

(select sum([wait_time]) + 1 from waits

where [wait_type] not in ('WAITFOR','SLEEP','RESOURCE_QUEUE', 'Total', '***total***')
)
 as numeric(20,1))

from waits

where [wait_type] not in ('WAITFOR','SLEEP','RESOURCE_QUEUE','Total')

and time_ = (select  max(time_) from waits where [wait_type] = 'Total')

order by [%] desc

2- Your RAID controller firmware and drivers.

make sure they are up to date , consult your server manufacturer

THX

---

Please mark as answer if you think this answers your questions

Answer 4

Thanks a lot!

It's a production server so I can't play with it on line.
I'll be back to say what hapened.

Answer 5

At least gather the waits for now , shouldn't harm your server at all
Cheers

---

Please mark as answer if you think this answers your questions

Answer 6

Thank you for fast reply.

I'll install patches for sure.
Will try this procedures as well. (Yes, rebuilding is done through maintenance plans)

But, as I mentioned, server stuck sometimes for no particular reason. Tested rebuilding on XP with 1GB RAM, as well as memory demanding queries, and everithing works - slowly but works. Everything worked well at old server (DELL with 2GB RAM)(Databases size: 2 x 2GB)

I haven't seen anything in SQL log that could say that crash is caused by SQL.

What does your Windows System log show?  If SQL isn't logging that it had a failure, I'd be looking at the OS as the suspect.  SQL is only going to be as stable as it's installed OS, and what you are describing is a OS, not a SQL problem.

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

Answer 7

This is first time that I expirience such a problem. So far, on many servers an workstations, if SQL Server works after instalation - it works until recycled.

No usefull info in Widows logs too. Server just stops responding.

Current SQL build is 8.00.2055 and I saw there are a lot of FIX-es after that.
I've tried to install SQL2000-KB916287-v8.00.2187-x86x64-ENU.exe (on XP with SQL v8.00.2050) and it says "The product instance MSSQLServer has had update 2050 installed. You do not need to install this update. An update equal to or greater than 2273 should be downloaded."

How could I obtain all available FIXes?

Answer 8

Updated Storport.sys.

No freezes for over a week now.
Thanks again for everyone.

Answer 9

Updated Storport.sys .

No freezes for over a week now.
Thanks again for everyone.

Good to know

Adding a KB related to "Storport storage driver"

http://support.microsoft.com/kb/932755#top

HTH

---

Please mark as answer if you think this answers your questions