Welcome to the world (new face) of Knowledge Sharing Network.
To track the older blog posts refer to our historical knowledge sharing site  and you will find this as your destination for SQL Server knowledge pool.

Follow SQLMaster on Twitter SqlServer-QA.net - Knowledge Sharing Network (@sqlmaster)

July 2008 - Posts - SQL Server Knowledge Sharing Network (@sqlmaster)

July 2008 - Posts

SQL Server error: messages are appearing when the working set of SQL Server 2005 process reaches 50 percent of the memory that is committed to the process.

It is evident that SQL Server 2005 component handles memory differently as compared to SQL Server 2000 version. As one of the KBA refers SQL Server 2000 Enterprise Edition introduces support for the use of Microsoft Windows 2000 Address Windowing Extensions (AWE) to address approximately 8 GB of memory for instances that run on Microsoft Windows 2000 Advanced Server, and approximately 32 GB for instances that run on Microsoft Windows 2000 Datacenter. With AWE, SQL Server can reserve memory that is not in use for other applications and the operating system. Each instance that uses this memory; however, must statically allocate the memory it needs. SQL Server can only use this AWE allocated memory for the data cache and not for executables, drivers, DLLs, and so forth. This is changed as per BOL documentation and you can get more information by referring to the topics such as: memory architecture, Server memory options, enabling AWE memory & Enabling Memory Support for Over 4 Gb of Physical Memory.

Also the compilation below as per one of the SSP member (Dave):

Physical Memory = 8GB
Target Server Memory = 6GB (referred from SYSMON)
Total Server Memory = 690MB (referred from SYSMON)
Minimum Server Memory = 2GB
Maximum Server Memory = 6GB

So the Target Server memory equals to the Maximum Server memory on the server.

Physical Memory = 32GB
Target Server Memory = 28GB (SYSMON reports)
Total Server Memory = 397MB   (SYSMON reports)
Minimum Server Memory = 4GB
Maximum Server Memory = 30GB

Here is the catch where the Target Serve memory is shown less than Maximum Server memory availabile. 

Once for all within SQL Server 64 bit edition ensure you have applied the 'Lock Pages in Memory' to the SQL Server service account in order to take into account on higher memory set. Also bear in mind any resource intensive activity within SQL Server will consume more memory during at that point of time of your testing, so better to run during less traffice hours and no scheduled jobs.

So when you talk about SQL Server 2000 you should think about MemToLeave area which is reserved for SQL alone, that is calculated as follows: 

max worker threads value (default is 255) * Stack Size (default is 512KB) + external needs setting

Bear in mind the default memtoleave is 128MB until SQL 70 version and increased to 256MB within SQL 2000 version. For this reason it is suggested to leave the settings as is in order to ensure that SQL Server always has a contiguous region of address space available in the MemToLeave area. The default setting of DYNAMIC memory for SQL Server grows and shrinks its memory usage by committing and de-committing buffers from the buffers reserved at startup. In any case the LazyWriter process you see within task manager is responsible for growing and shrinking the buffer pool. Always you see is memory usage increase and do not decrease, that means SQL will not release unless it is asked by windows process. What you see above matches to:

    Target Server Memory (KB) – The total amount of dynamic memory the server can consume. This corresponds to the buffers reserved at SQL Server startup.
    Total Server Memory (KB) – The total amount of dynamic memory the server is currently using. This corresponds to the sum of buffers committed in the BPool and OS buffers of type "OS In Use."

In addition to above warning message you would also see the error stated in ThisBlogpost which are generated when the 'working set'  of SQL Server 2005 process reaches half of its allocated memeory, which is 50 percent of memory that is committed for the process. Sometimes this is associated with a decrease in the performance too where the Windows process is trying to reduce the SQL 2005 process usage. Additionally to get what is the usage percent you could take help of DMV sys.dm_os_ring_buffers:

FROM sys.dm_os_ring_buffers

Bear in mind the above you would see within SQL Server 64 bit edition and related blogs on web explains a lot about, SlavaOka- & TonyRogerson blogs and the following
  • SQL-Workingset blog
  • 918483: How to reduce paging of buffer pool memory in the 64-bit version of SQL Server 2005
  • 905865: The sizes of the working sets of all the processes in a console session may be trimmed when you use Terminal Services to log on to or log off from a computer that is running Windows Server 2003
  • 920739: You may experience a decrease in overall system performance when you are copying files that are larger than approximately 500 MB in Windows Server 2003 Service Pack 1.

Additionally if you are getting this constantly better to open up a case with Microsoft support for a hotifx, as most of them are resolved with such fixes.


Get ready for SQL Server 2008 RTM release!

Are you desperate to get Relase To Manufacturing (RTM) release of SQL Server 2008?

Well, long wait for SQL Server 2008 is nearly over.

It is an official announcement that SQL Server 2008 will be on the August price list.  This means that customers and partners will soon be able to place their orders for SQL Server 2008 as we get ready to RTM in Q3 of this year. As per Bob Kelly, Microsoft Corporate Vice President of Infrstructure Server Marketing announced during Worldwide Partner conference on July 09th 2008. Good news for finance people is cost of deploying SQL Server 2008 in terms of licensing will same as SQL Server 2005 costs (no additional or small prints).

You may be aware that we have seen a preview/rehearsal of SQL Server 2008 during June 2008 which is RC0, more from this SQL Server 2008 Release Candidate - public download is available now. post here and for more joy don't forget to upgrade the local SQL 2008 documentation aka BOL - SQL Server 2008 RC0 and 3.5 Compact edition Books Online - download and update your copy  fyi.

So it is defiently a long wait for the users to get hands-on with RTM on SQL Server 2008 which has been announced during June 2007 (CTP) and promised earlier to release by February 2008, as you may refer SQL Server 2008 features and getting through CTP, RC, RTM. Are we there yet?  post here.

So having the official releases of Windows Server 2008, Visual Studio 2008 and now with SQL Server 2008 it will be an interesting career path for IT Pros & Developers in coming years (atleast until 2010). More interestingly I would say how many companies out there will be officially jumping into pool of these 3 hard-core 2008 version products immediately and no wait until next Service pack release, better deploy first than waiting for long time.

SQL Server 2008Being one of the launch leader of HeroesHappenHere & MVP got a free copy of Windows Server/Visual Studio/SQL Server 2008 DVDs and now can't wait to get my hands on RTM which is the final product of version 2008 on SQL Server.

More to come.....

Microsoft Security Bulletin Summary for July 2008, pay attention this is for SQL Server!

I believe it has been a while that we have seen the security hotfix from Microsoft Security team on SQL Server (alone). The latest security bulletin has announced important security patches that are related to SQL Server in addition to Windows operating system. They quote that "...With the release of the bulletins for July 2008, this bulletin summary replaces the bulletin advance notification originally issued July 3, 2008. For more information about the bulletin advance notification service, see Microsoft Security Bulletin Advance Notification....".

The Severity rating has been given as important which means you have to test the referred hotfix from this KBA Vulnerabilities in Microsoft SQL Server Could Allow Elevation of Privilege (941203) link. As that goes in specific to SQL Server the following are for Windows alone: Vulnerabilities in DNS Could Allow Spoofing (953230), Vulnerability in Windows Explorer Could Allow Remote Code Execution (950582) & Vulnerabilities in Outlook Web Access for Exchange Server Could Allow Elevation of Privilege (953747) links.

So what is your practice in deploying such security hotfixes within your environment?

Don't forget to test the patches before deploying them on to the production, that might cause unprecedented downtime and with prior testing you will be able to catch the issues in hand. Also it is recommended to deploy Baseline Security Analyzer tool MBSA that  allows administrators to scan local and remote systems for missing security updates as well as common security misconfigurations.

If you are new to this type of alerts or patches then you must visit the Security Guidance for Update Management link that provides additional information about Microsoft’s best-practice recommendations for applying security updates.

So the recommendation is that to apply the update at the earliest opportunity. For more information on the known issues refer to this KBA 941203 which has documented the currently known issues that customers may experience when installing this security update.

Unable to install SQL Server 2008 RC0 Books Online

In continuation with the BOL update topic on SQL2008-RC0_BOL post here I have had troubles in installing the RC0 Books Online on to my laptop.

As per the error below I thought it may be problem with the Windows Installer service on my laptop (Vista Ultimate) and tried to install afresh on a central monitoring server (Windows 2003 server) & local machine (Windows XP), still no luck to install the documentation.



On my laptop I have Windows Installer service 3.1 and that is not sufficient to carry out this BOL installation, so searched Microsoft KBA to see if there is any latest version is available. Bingo, here it is Windows Installer 4.5 is available and you need to download and apply on to the machine where you want to upgrade the SQL Server 2008 RC0 Books ONline documentation.

Hope this helps.