November 10, 2008 at 10:17 pm
Hi,
What do we need to proceed with if Queries getting timed out?
What are changes i must make to make the DB all right?
Kindly help me out!
Regards,
Naga
[font="Arial"]Naga Raju Appani[/font]
November 10, 2008 at 10:35 pm
* Check the Execution Plan and optimize the query.
* Make transaction is very less.
* Do update statistics wherever required.
* Use hints in query.
* Create relevant indexes on table.
* Always use where clause in your query.
Many more things, see more information in SQL BOL.
November 10, 2008 at 11:54 pm
My Remote Query Time out and Query wait found set to Default
as well as
Minimum Memory per Query is also found Default (1024KB)
and more over
How about AWE? How can i implement it?
All the DB Sizes per this instance would come to 2.5TB
What are the recommendations that i must follow for large Databases?
Would somebody please guide me?
Naga
[font="Arial"]Naga Raju Appani[/font]
November 11, 2008 at 12:15 am
nagnrik (11/10/2008)
My Remote Query Time out and Query wait found set to Defaultas well as
Minimum Memory per Query is also found Default (1024KB)
and more over
How about AWE? How can i implement it?
All the DB Sizes per this instance would come to 2.5TB
What are the recommendations that i must follow for large Databases?
Would somebody please guide me?
Naga
For AWE, see the below links:
http://technet.microsoft.com/en-us/library/ms190673.aspx
http://support.microsoft.com/kb/274750
Regarding Timeout:
http://vyaskn.tripod.com/watch_your_timeouts.htm
http://support.microsoft.com/default.aspx?scid=kb;en-us;271509
http://blogs.solidq.com/EN/rdyess/Lists/Posts/Post.aspx?ID=16
November 11, 2008 at 1:32 am
Thanq
I still have a doubt on my mind!
What exactly this AWE?
Is this additional memory other than RAM and Paging memory?
How SQL Server uses it? How about RAM then and paging Memory?
Will RAM not be utilized or what?
I am Oracle Certified Professional and learning SQL Server ..
Please let me know ... I have to implement this coming week end ..
Thaning you in advance,
Regards,
Naga
[font="Arial"]Naga Raju Appani[/font]
November 11, 2008 at 1:51 am
nagnrik (11/11/2008)
ThanqI still have a doubt on my mind!
What exactly this AWE?
Is this additional memory other than RAM and Paging memory?
How SQL Server uses it? How about RAM then and paging Memory?
Will RAM not be utilized or what?
I am Oracle Certified Professional and learning SQL Server ..
Please let me know ... I have to implement this coming week end ..
Thaning you in advance,
Regards,
Naga
Naga,
Generally AWE (Address Windowing Extensions) means use the more than of 2 GB of physical memory. By default SQL server uses max. memory is 2 GB. Suppose you machine have more than 2 GB, now you want to utilize the more ram for SQL Server, then you can enable this option.
The links I have provided to you, guide AWE. Below links are very good explanation of AWE, if you want more info, see SQL BOL.
http://technet.microsoft.com/en-us/library/ms190673.aspx
http://support.microsoft.com/kb/274750
Hope will you get my point
🙂
November 11, 2008 at 2:11 am
Thank you Kishore! Thank you very much!
I never gotta know that SQL Server can only use 2G of RAM for its Cache Memory.. This is really good.
So, AWE is an API that which allows the Application to use more than 2G of RAM....
This is really good.. Becasue.. We have a DB arround 2TB for which Daily Transactions going on...
Enabling AWE is very much helpful as it increases the buffer Cache of the SQL Server...
We have 8GB of RAM on our Sever ......Only SQL Server 2005 installed ...We did not even have any
other application installed on this Server ..... for these many days .... it has been waste having 8G of RAM for our PRODUCTION...am i right?
We have Bit map Images Stored on the DB... Can we enable AWE? As i saw somewhere in the link like this
"AWE address ranges cannot be used to buffer data for graphics or video calls."... What does it mean?
I saw it in the below link
http://msdn.microsoft.com/en-us/library/aa366527.aspx
Thanking you Kishore ...
Naga
[font="Arial"]Naga Raju Appani[/font]
November 11, 2008 at 2:44 am
nagnrik (11/11/2008)
Thank you Kishore! Thank you very much!I never gotta know that SQL Server can only use 2G of RAM for its Cache Memory.. This is really good.
So, AWE is an API that which allows the Application to use more than 2G of RAM....
This is really good.. Becasue.. We have a DB arround 2TB for which Daily Transactions going on...
Enabling AWE is very much helpful as it increases the buffer Cache of the SQL Server...
We have 8GB of RAM on our Sever ......Only SQL Server 2005 installed ...We did not even have any
other application installed on this Server ..... for these many days .... it has been waste having 8G of RAM for our PRODUCTION...am i right?
We have Bit map Images Stored on the DB... Can we enable AWE? As i saw somewhere in the link like this
"AWE address ranges cannot be used to buffer data for graphics or video calls."... What does it mean?
I saw it in the below link
http://msdn.microsoft.com/en-us/library/aa366527.aspx
Thanking you Kishore ...
Naga
Naga,
Actually AWE allocate like: AWE uses physical nonpaged memory and window views of various portions of this physical memory within a 32-bit virtual address space. As of my knowledge, you didn't get any problem while enabled the AWE.
Okay, you do like, just enable it and test it once during offline time.
Note: During Offline: Try run some DBCC command(s). So that your database get works fast.
eg:
DBCC UPDATEUSAGE
for more information see SQL BOL.
Regards,
Kishore.P
November 11, 2008 at 2:49 am
Thankq Kishore!
And one more Doubt ..
We have Windows Server 2003 EE 32-bit X-86 and SQL Server 2005 EE and RAM about 8GB....
I am gonna specify like below,
Minimum Memory : 2048G
Maximum Memory : 8092G
and AWI Enabled.
and i do restart the Server later enabling it.
Is that ok? or do i need to do anything more with the boot file?
Regards,
Naga
[font="Arial"]Naga Raju Appani[/font]
November 11, 2008 at 3:19 am
nagnrik (11/11/2008)
Thankq Kishore!And one more Doubt ..
We have Windows Server 2003 EE 32-bit X-86 and SQL Server 2005 EE and RAM about 8GB....
I am gonna specify like below,
Minimum Memory : 2048G
Maximum Memory : 8092G
and AWI Enabled.
and i do restart the Server later enabling it.
Is that ok? or do i need to do anything more with the boot file?
Regards,
Naga
Hi Naga,
* If you use Max. Memory : 8092 GB means how about Windows Server Running ?
* Before implementing the AWE, one time need to follow the Windows 2003 Help for AWE Option.
* In SQL Server, just enable the AWE option, using Enterprise Manager: Just Right Click on SQL Server -> Properties -> Memory -> check "use AWE to allocate Memory". then press OK button. (if you want to script of this operation, select Script option on the top of the dialog box).
* Need to change the boot.ini file.
* Restart the machine.
* also run some DBCC commands.
Regards,
Kishore.P
November 11, 2008 at 3:35 am
Thanq Kishore..
sp_configure 'min server memory', 1024
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO
In the preceding example for SQL Server and Windows Server 2003, the memory settings direct the buffer pool to dynamically manage AWE mapped memory between 1 GB and 6 GB. (This dynamic AWE example does not apply to SQL Server 2000.) If other applications require additional memory, SQL Server can release the allocated AWE mapped memory if it is not needed. In the example, the AWE mapped memory can only be released up to 1 GB, the min server memory limit.
Note that setting the min server memory option to 1 GB does not automatically force SQL Server to acquire 1 GB of memory. Memory will be allocated on demand, based on current database server load.
According to the above information found in the link below,
http://technet.microsoft.com/en-us/library/ms190731.aspx
The minimum memory specified for SQL Server can be released when ever the OS needs for any other usage. As per my server, i am gonna set it as 2G.... is that right? so, SQL Server can have a minimum of 2G Buffer space to the RAM Maximum available to the OS!
Is that Right?
I do not think, we do need to change the boot file for Windows EE 2003 as it dynamically recognises the AWE enabled for an application SQL Server 2005 (As per the Doc u provided) (and not for SQL Server 2000)....
Am i right?
I am once again thanking you for ur valubale time with me .... really appreciate ur interest on SQL Server ....
Regards,
Naga
[font="Arial"]Naga Raju Appani[/font]
November 11, 2008 at 4:15 am
nagnrik (11/11/2008)
Thanq Kishore..sp_configure 'min server memory', 1024
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO
In the preceding example for SQL Server and Windows Server 2003, the memory settings direct the buffer pool to dynamically manage AWE mapped memory between 1 GB and 6 GB. (This dynamic AWE example does not apply to SQL Server 2000.) If other applications require additional memory, SQL Server can release the allocated AWE mapped memory if it is not needed. In the example, the AWE mapped memory can only be released up to 1 GB, the min server memory limit.
Note that setting the min server memory option to 1 GB does not automatically force SQL Server to acquire 1 GB of memory. Memory will be allocated on demand, based on current database server load.
According to the above information found in the link below,
http://technet.microsoft.com/en-us/library/ms190731.aspx
The minimum memory specified for SQL Server can be released when ever the OS needs for any other usage. As per my server, i am gonna set it as 2G.... is that right? so, SQL Server can have a minimum of 2G Buffer space to the RAM Maximum available to the OS!
Is that Right?
I do not think, we do need to change the boot file for Windows EE 2003 as it dynamically recognises the AWE enabled for an application SQL Server 2005 (As per the Doc u provided) (and not for SQL Server 2000)....
Am i right?
I am once again thanking you for ur valubale time with me .... really appreciate ur interest on SQL Server ....
Regards,
Naga
Naga,
sp_configure 'min server memory', 2048
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO
* min. memory is 2 GB and max. memory is 6 GB. this is ok. (change upto 7 GB also good, not a problem)
* As per link provided by you, for Win 2003, no modification on boot.ini file.
* change the above settings and restart SQL Server or entire machine once.
Regards,
Kishore.P
November 11, 2008 at 5:58 am
Thanq kishore , Thanq Vey much
I will be updating you as soon the performance gets improved..
Regards,
Naga
[font="Arial"]Naga Raju Appani[/font]
November 12, 2008 at 7:02 am
The first response pointed out a number of issues you have'nt talked about addressing. Query tuning, indexing and all the associated work around those issues can and do lead to timeouts. Simply getting more memory on the server and configuring it correctly will help, but it's not necessarily the core issue.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
November 13, 2008 at 6:05 am
Just in case it might apply, queries that time out under SSMS can run through when invoked in another context. Never have found out why, but don't despair if the time out you are seeing is in SSMS.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply