You are talking about two things: permission and schema (same as owner in SQL 2000).

grant statement just Grant the permissions of the commands.

e.g. Grant create procedure to User1

Whether User1 can...

Do you use sp_XML_xxx stored procedure a lot? If you have memory leak, the server will be getting slower.

You are right to set the boost to default. But for paralisam, you can try to turn it off (by default SQL server uses the actual number of available in paralell plans)....

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1fnd.asp

Check the above link.

Windows 2000 Usage Considerations

Before you configure Windows 2000 for AWE memory, consider the following:

• To enable Windows 2000 Advanced Server or Windows 2000...

try this:

DECLARE @n nvarchar(4000)

SET @n='

<Medications>

<Medication>

<Description>Medicine1</Description>

<Route>ORAL</Route>

<Dose>100ml</Dose>

<Frequency>DAILY</Frequency>

<Duration>twice</Duration>

</Medication>

<Medication>

<Description>Medicine2</Description>

<Route>INHALE</Route>

<Dose>200mg</Dose>

<Frequency>WEEK</Frequency>

<Duration>1</Duration>

</Medication>

<Medication>

<Description>Medicine3</Description>

<Route>REC</Route>

<Dose>10mg</Dose>

<Frequency>Q1H</Frequency>

<Duration>2 Months</Duration>

</Medication>

<Medication>

<Description>Medicine4</Description>

<Route>EYE</Route>

<Dose>10ml</Dose>

<Frequency>WEEK</Frequency>

<Duration>1</Duration>

</Medication>

<Medication>

<Description>Medicine5</Description>

<Route>IM</Route>

<Dose>10ml</Dose>

<Frequency>MONTH</Frequency>

<Duration>1</Duration>

</Medication>

</Medications>'

declare @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @n

SELECT * FROM OPENXML (@idoc, 'Medications/Medication', 1)

WITH  (Description  varchar(50) 'Description',

 Duration varchar(50) 'Duration',

 Route varchar(50) 'Route')

Starting from the easy ones:

"Is a data file in SQL Server necessarily physically contiguous? "

No. But if physically continous e.g. allocating enough space when db is created, the db files...

In windows boot.ini, add /3GB /PAE

Enable AWE in SQL server set the max server mem to 5.5GB.

Try the following code. ( Not tested, Check the syntax and correct it if it has problem). If RefNo is string in your tables, change the function accordingly.

CREATE FUNCTION dbo.udf_GetProNo(@RefNo...

Check your DB have auto shrink or not. If it has, uncheck it.

If it does not have, you don't need to worry about it. Since your db is set to...

Just want to clarify one thing, according to the following blog, sp_OaXXX family procedures do not cause memory leak even if you forget to call sp_OADestroy. Take a look:

http://blogs.msdn.com/khen1234/archive/2005/10/13/480829.aspx

First of all, reindex the tables and update statistics. If the problem is still there,

Set the MAXDOP to 1 for the query or go to the server property to change...

It's mostly a locking issue. You have about 1800 trans a minute (1009964/9 working hours a day) and have some long queries. Check your transaction isolation level the query is using....

No. AWE is usually used when you have more than 4GB RAM.

"Are you saying this includes a situation where the data is all in one file and the indexes are all in another file?"

It's upto what you are scanning, the table...

Based on your post, you are configuring a 2-node Active/Active cluster.

Yes, you need to consider the ram that OS needs. But the SQL server MAX RAM does not need to be...