Lock Pages In Memory Option On SQL 2005 STD 64-bit With W2K3 R2 Standard 64-bit - Does It Work?
Nov 9, 2007
Hey Folks -
I'm setting up a new production SQL 2005 64-bit server running on a dedicated Windows 2003 R2 Standard system w/8GB of RAM.
I've enabled the "Lock Pages in Memory" option (http://msdn2.microsoft.com/en-us/library/ms190730.aspx) but when I run dbcc memorystatus all of my AWE counters show 0.
According to http://blogs.msdn.com/sqlprogrammability/:
"On 64 bit machines, execute 'dbcc memorystatus'. If the AWE Allocated memory is 0 then lock pages in memory privilege has not been granted to the service account or it has not taken effect. "
Does the "Lock Pages in Memory" option work on W2K3 R2 Standard? Just searching across the web I find different answers. In some places it clearly says that Lock Pages in Memory only works on W2K3 Enterprise systems. In other places it says that it should work on any Windows 2003 64-bit OS.
Does anyone have any ideas? Assuming we cannot "lock pages in memory" due to the Windows 2003 Standard OS, are there any other memory management recommendations out there?
thanks!
View 2 Replies
ADVERTISEMENT
May 18, 2007
All,
I have a new database server running W2K3/MSSQL2K5 x64. I read in BOL that you can use the Lock Pages in Memory option to improve performance on your database server. I did some research on the internet and some sources are stating that you can only use that option with MSSQL2K5 Enterprise but in BOL they state that you can use this option in Standard/Enterprise.
Can anyone confirm that "Lock Pages in Memory on x64 Standard" works in MSSQL2K5 Standard?
Thanks in advance for the help.
View 1 Replies
View Related
May 8, 2002
I have a remote site/SQL 2000 box that is not under our control. We have SQL 2000 Ent Edition up on a Win2000 box that has 8 gig of memory. We think their NT person setup the SQLExec account to have this right to Lock memory pages, but having trouble verifying this. AWE was enabled on SQL Server and has a max memory setting of 6 gig. It shows AWE enabled in the SQL error log. The box only has had a few users (and in some case only running perfmon) but has locked up on several occasions. Nothing in either WIn2000 log or SQL to show why. MY question after stating all these facts is that if we have AWE enabled,(and the boot.ini file is correct per Microsoft Specs) will AWE come up if the SQLExec does not have these rights. And if he doesn't have these rights could it come up and crash unexplained like this.
View 1 Replies
View Related
Feb 6, 2008
I've read all the posts... set the security policy (for the service account running SQL Server) to lock pages in memory... ran gpupdate... still same error... HELP !!!!
View 1 Replies
View Related
Feb 10, 2015
So I started a new job recently and have noticed a few strange configurations. Typically I would never mess with min memory per query option and index create memory option configuration because i just haven't seen any need to. My typical thought is that if it isn't broke... They have been modified on every single server in my environment.
From Books Online:
• This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server technician.
• The index create memory option is self-configuring and usually works without requiring adjustment. However, if you experience difficulties creating indexes, consider increasing the value of this option from its run value.
View 3 Replies
View Related
Jun 8, 2007
Is AWE enabled on SQL 2005 Standard? We currently have it setup using "locked pages in memory" for a domain account and haven't noticed any performance problems.
This article however states that only the SQL2005 Enterprise Edn supports "locked pages in memory". Can anyone confirm this?
http://support.microsoft.com/kb/918483
If I'm mistaken, how should the memory options be set for a server with 8gb ram running on SQL 2005 Standard which runs on Win2003 Enterprise? Should the min and max memory be set or left alone as default?
View 5 Replies
View Related
Mar 9, 2006
Hi all,I have recently implemented a SQL 2005 cluster using SQL 2005 Standardon Windows 2003 Enterprise edition.Both nodes have 4GB of RAM and according to the datasheet, SQL 2005Standard can support the OS maximum memory amount and Win2K3 EntEdition can support 64GB!However, in Enterprise Manager, if I go to the "Memory" tab of theinstance properties I can't increase the memory beyond 2147483647(which I assume is around 2GB).I don't have AWE enabled but am unsure as to the ramifications of this.Any advice anyone could provide would be greatly appreciated.Many thanks,Ian
View 2 Replies
View Related
Mar 3, 2008
I've got some applications which need .NET 3. And since theoretically .NET3 is using .NET2's runtime (CLR), I'm told that applications for .NET2 should work in .NET3 as well. But I would still like to ask to have a confirmation:
Can SQL 2005 Standard edition and Express work with .NET 3 or .NET 3.5? Has Microsoft tested this and can confirm this?
TIA
View 1 Replies
View Related
Mar 3, 2008
I've got some applications which need .NET 3. And since theoretically .NET3 is using .NET2's runtime (CLR), I'm told that applications for .NET2 should work in .NET3 as well. But I would still like to ask to have a confirmation:
Can SQL 2005 Standard edition and Express work with .NET 3 or .NET 3.5? Has Microsoft tested this and can confirm this?
TIA
View 2 Replies
View Related
Mar 3, 2008
I've got some applications which need .NET 3. And since theoretically .NET3 is using .NET2's runtime (CLR), I'm told that applications for .NET2 should work in .NET3 as well. But I would still like to ask to have a confirmation:
Can SQL 2005 Standard edition and Express work with .NET 3 or .NET 3.5? Has Microsoft tested this and can confirm this?
TIA
View 2 Replies
View Related
Nov 9, 2007
Hi,
I'm having trouble finding the optimum memory settings for SQL Server 2005. I have 4 instances running on a macine with 8 dual-core processors and 18GB of RAM. I have tried the following memory settings so far:
No maximum - one instance used about 12GB of RAM so then the others struggled
Maximum of 4GB each (2GB left for Windows) - meant that 3 instances could be using 1GB each and then another at 4GB and needing more whilst 9GB sat unused.
Minimum of 2GB each - one instance would use up 10GB and then never give any back to the other instances.
I also find that setting a maximum then just causes a high amount of paging. What I would like to do is have each instance use a minimum amount, say 1GB, and then have each instance use a maximum of 13GB (3GB for other 3 instances running at a minimum level and 2GB for Windows). This 13GB should then be released and allocated to another instance when necessary, assuming it is no longer all being used. I do not want paging to occur if an instance reaches 13GB.
How do I go about configuring SQL Server to behave like this? Is it possible?
Thanks.
View 1 Replies
View Related
Oct 11, 2007
I've been researching AWE to determine if we should enable this for our environment.
Currently we have a quad core box with 4 gb of RAM (VMware). OS: Windows 2003 std, SQL Server 2005 std. 3GB is not set but will be as soon as we can perform maintenance on the server.
I have read mixed feedback on AWE, either it works great or grinds you to a hault. I would assume that the grinding to a hault is due to not setting the min/max values correctly or not enabling the lock page in memory setting.
We only have one instance of SQL on the server and this box won't be used for anything else aside from hosting SQL services. We do plan on running SSRS off of this server as well.
1. Will running SSRS and enabling AWE cause me problems? Will I have to reduce the max setting by the SSRS memory usage or will it share and play nice?
2. How do I go about setting the Max value? Should it be less than the physical RAM in the box? Right now its set to the default of 214748364, even if I don't enable AWE should this default value be changed?
3. It seems that even at idle the SQL server holds a lot of memory and the page file grows. If I restart the process in the morning, memory usage in taskmon is at 600mb or so. By the end of the day, its up around 2gb. How can I track down whats causing this, should this even concern me?
4. The lock Page in memory setting worries me. Everything I've read on this seems to give a warning about serious OS and other program support degradation. In some cases to the point where they have to restore the settings on the server before they can bring it back up. What are your thoughts on this.
View 3 Replies
View Related
Aug 3, 2007
Hi,
I am testing SQL 2005 Standard (32 bit) on a Windows 2003 Server 64 bit with 8GB of RAM? Should I enable the AWP Setting or not and should I change the maximum server memory (currently saying 2GB)?
Thanks!
Tom
View 1 Replies
View Related
Nov 30, 2007
Is there a way around this?I have a SQL 2005 with 8 GB of memory that I want to set AWE on.But the local security policy won't let me, it says:This setting is not comaptiple with computers running Windows 2000Service Pack 1 or earlier.Apply group Policy objects containing this setting only to computersrunning a later version of the operating system.Why does it say Windows 2000 SP1 or earlier? I'm running on Windows2003, sp1.
View 2 Replies
View Related
Jul 12, 2001
I was running a query and it didn't execute stating you have run out of locks
How could I set the locks
thanks
Sejal
View 2 Replies
View Related
May 5, 2008
Does the Table Lock option in the OLE DB Destination task in a data flow refer to a lock on the destination table or on the source table from which the table is loading from?
The reason I ask is because I have a package run twice simultaneously pulling from the same server and table onto two different tables on two different servers. When I kick off my job to run the two data pulls, one of the jobs terminates with a
"TCP Provider: An existing connection was forcibly closed by the remote host." message. I believe this is due to a table lock on the server, but can't figure this out.
Thanks in advance for your help.
View 1 Replies
View Related
Jun 2, 2015
What is lock page memory. How it will work and why it will increase the performance.then what is the difference between setting the - max/Min server memory and Lock page memory
View 1 Replies
View Related
May 23, 2002
Hello all,
I have a Production server which has 2gb of memory. SQL server memory is set to configure dynamically.
The Server also has a third party application on it. The third party application was running poorly, and the software vendors recommended running the following sql on a job every hour:-
exec sp_configue 'max server memory(MB)',500
reconfigure
exec sp_configure 'max server memory(MB)'1400
reconfigure
The hourly job has the effect of flushing out sql server memory every hour and setting the max amount down to 500mb and back up to 1400mb. SQL server still manages memory dynamically, so within the hour before the next time the job runs, sql server could use more than 1400mb.
I' feel that this job will degrade sql server performance because it will empty the buffer cache and procedure cache. My approach would be to select the 'use a fixed memory' option and set it to 1400mb, and not use their recommendation.
Does anybody have experience of this, and perhaps explain the consequence of running the recommended job every hour.
Any advice would be great.
Pargat
View 2 Replies
View Related
Apr 17, 2008
I have DBA that is convinced that they need domain admin rights to install SQL 2005 into an existing cluster. The domain groups and service accounts for SQL have been created already. Is having domain admin rights required during the install of SQL 2005 in a cluster?
View 1 Replies
View Related
Feb 26, 2004
If I change the Maximum Memory option (sp_configure or vai EM), do I need to restart the MSSQL service?
Clive
View 2 Replies
View Related
Jul 17, 2007
Using the -SkipError option in Sql Server 2005 SP2, 64 bit edition does not work anymore. I am trying to skip error 20598, but the error is not skipped. This was working but it "magically" stopped some time ago. Does anyone have any ideas?
View 3 Replies
View Related
Apr 10, 2008
First of all I am planning to install sql 2005 standard and sql standard sp2
on vista ultimate professional using vs 2008 pro
I have some questions about installing sql 2005 what is the best way to install sql 2005 on
vista My article I got this from ms 403393 (upgrading sql standard 2005 to sql standard 2005sp2)
1 sql standard .(shortcut right click from cd/dvd not possible) I have heard command prompt start menu, click cmd
type run D: cd/dvd exe
From a wizard (sql sp2)
2. sql standard sp2
a. upgrade using a wizard
b. open a command prompt (not sure if right click is best option)
install dvd media example dqlserver2005sp2-kb921896-exe SKUUPGRADE=1(is run as administrator option on media)
do I type run or what do I type ?
c. go to system configuration page(where is this) you should see a single warnining you need to install
sp2, the edition should say success if it does not please reapply
d. on the registration page please enter your registeration number
e. on the comments to install please enter database services, reporting services
workstation components, books online and development tools( I wish to install
all components)
f. on the instance page click default instance(default install)
g. finish rest of wizard
h. apply sql sp2
I also have of seperate question of upgrading sql from vista using a Configuring local admin account
account (configuring administration from a wizard-will this notify
me by default) I do not want my administrative priviledges elevated.
I will be using default configurations for my installations.........
I often wish there would a nice picture examples of doing this
Please help
View 5 Replies
View Related
Aug 8, 2000
I have been unsuccessful in getting the Backup Log with
No Truncate option to work.
Any tips, tricks, suggestions, etc., would be appreciated.
Thanks!!!
View 2 Replies
View Related
Apr 15, 2008
The company that I work for has multiple database instances across two data centres in two different cities. All of these databases have front end applications reading/writing data to them.
Now, we have taken an initiative to merge all databases into one consolidated database. Then we want this database to be available in the other data centre. We'll have front application running at both locations reading/writing data to the databases and want it to be written to the other database too. These systems are in production 24/7 and we cannot afford any downtime over 5 minutes.
Replication, Log shipping, Mirroring or combination?
View 4 Replies
View Related
Feb 15, 2007
Hi~, I have 3 questions about memory based bulk copy.
1. What is the limitation count of IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit(true)?
For example, how much insert row at below sample?(the max value of nCount)
for(i=0 ; i<nCount ; i++)
{
pIFastLoad->InsertRow(hAccessor, (void*)(&BulkData));
}
2. In above code sample, isn't there method of inserting prepared array at once directly(BulkData array, not for loop)
3. In OLE DB memory based bulk copy, what is the equivalent of below's T-SQL bulk copy option ?
BULK INSERT database_name.schema_name.table_name FROM 'data_file' WITH (ROWS_PER_BATCH = rows_per_batch, TABLOCK);
-------------------------------------------------------
My solution is like this. Is it correct?
// CoCreateInstance(...);
// Data source
// Create session
m_TableID.uName.pwszName = m_wszTableName;
m_TableID.eKind = DBKIND_NAME;
DBPROP rgProps[1];
DBPROPSET PropSet[1];
rgProps[0].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProps[0].colid = DB_NULLID;
rgProps[0].vValue.vt = VT_BSTR;
rgProps[0].dwPropertyID = SSPROP_FASTLOADOPTIONS;
rgProps[0].vValue.bstrVal = L"ROWS_PER_BATCH = 10000,TABLOCK";
PropSet[0].rgProperties = rgProps;
PropSet[0].cProperties = 1;
PropSet[0].guidPropertySet = DBPROPSET_SQLSERVERROWSET;
if(m_pIOpenRowset)
{
if(FAILED(m_pIOpenRowset->OpenRowset(NULL,&m_TableID,NULL,IID_IRowsetFastLoad,1,PropSet,(LPUNKNOWN*)&m_pIRowsetFastLoad)))
{
return FALSE;
}
}
else
{
return FALSE;
}
View 6 Replies
View Related
May 24, 2004
I want to optimize a query for fast retrieval of the first row
and after the rest of them automatically.
I used the hint OPTION (FAST 1) but I don't get the hoped results, any idea?
Thanks.
View 5 Replies
View Related
Aug 1, 2001
I learned that SQL2000 Standard Edition can only support up to 2GB RAM, we have a WIN2K system on a server with 4CPU, 4GB RAM,
if we run SQL2000 Stanard Edition on it, will the 2 extra 2GB of RAM be wasted ?
Since there is a big diff. between licensing cost of SQL Standard and Enterprise, that's why the user choose Std. edition.
Any opinion or comment...
View 1 Replies
View Related
Feb 20, 2002
Hello,
I have been trying to find out what the maximum memory we can have on SQL7 standard edition server. Have not been able to find it anywhere...
Anuy ideas would be greately appreciated.
TIA
B.
View 1 Replies
View Related
Jun 26, 2007
Hi all,
We have an SQL 2000 Standard SP4 system with 3gb of RAM running on Windows 2003 Server R2 Standard.
The SQL memory usage of this server flatlines at around 1665mb and will not go any higher.
I understand SQL 2000 Standard on Windows 2003 Server Standard has a 2gb memory limit, however why wont the memory push up to this limit? The OS shows there is around 700mb of the 3076 unused (SQL uses 1660, windows uses around 700 making the maximum used around 2300 of the 3076)
I have set the /3gb switch on windows boot, I have changed the SQL memory configurations many different times. If I try and set 'min server memory' to anything over around 1500mb I get the error on SQL startup;
Warning: unable to allocate 'min server memory' of 1720mb
In the above example surely if the Server has a 2gb limit it should let me set say 1720/2048 ? or 1920/2048 or even 2048/2048, however anything over around 1500 generates the above error.
Our server continues to flatline at 1660mb, any help to squeeze out an extra 200-400mb of RAM would be appreciated or an explination as to why it can not be done.
Thanks for your help
Jim
View 1 Replies
View Related
Mar 30, 2007
Hi all ,
Is there any way to get memory utilization for a Database.
Regards
Sufian
View 3 Replies
View Related
Jul 25, 2007
my package contains
Two connections ==> one is using OLEDB connecting to SQL server 2005 and the other using ADO.NET's ODBC option to connect to mysql;
Two "Execute SQL Task" ==> one gets maximum ID(bigint) from a SQL server table and the other gets the maximum ID(unsigned) from a mysql's table and bind them to two variables ID1(string) and ID2 (int 64). http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1902297&SiteID=17&mode=1 ;
And one DataFlow whose data source is DataReader Source which queries mysql "select * from table where ID > cast(@ID1 as signed) and ID <= cast(@ID2 as signed) ". To debug, I put a DataViewer between Data Flow Source and Data Flow Destination.
Run the package, I found no data coming into DataViewer and no error message as well. However if I remove the "<=" sign and change the DataReader Source query to "select * from table where ID > cast(@ID1 as signed)", It works perfectly. Initially I thought it's variable @ID2's issue. But if I replace "<=" sign with ">=" sign in the query ==> "select * from table where ID >= (cast(@ID2 as signed) -100)", there is no problem. After tried many times, I found that query just doesn't work with "<" sign or "between". Plus the result of running package tells it's successful but just no records got inserted into Data Flow Destination.
Anyone could help? Thanks!
View 3 Replies
View Related
Feb 1, 2008
Assume you have a query SELECT Column1 from Table WHERE Column2 = 'x' Say you have a non-clustered index on Column2. The engine is going to load the non-clustered index into memory to search for that record then do a lookup to the clustered index to grab column one also but quickly becuase it knows the RowID.. Right?
Is that safe to assume that every table that is queried would need to be in memory? ie the buffer pool? So if I have a table that has 2 million records, any operation on that data requires the table to be in memory?
View 6 Replies
View Related
Nov 29, 2006
Is the correct procedure for SUS patches to a server running SQL Server 2005, to set the client to download only and then manually shutdown SQL Server, apply patches, reboot, and restart SQL Server?
Do SQL Server 2005 patches download through SUS or is it manual SP only, except for hotfixes?
Thanks, Tom
View 1 Replies
View Related