Category Archives: Sql Server

Got a large ShareWebDB_log.ldf file?

My ShareWebDB_log file was huge on my SBS 2008 server.



  


So I fired up the SQL 2005 management express (run as admin)


Choose the ShareWebDB


Clicked on new query and did this:


USE ShareWebDb
 GO
 DBCC SHRINKFILE(ShareWebDb_log, 1)
 BACKUP LOG ShareWebDb WITH TRUNCATE_ONLY
 DBCC SHRINKFILE(ShareWebDb_log, 1)
 GO


 


Now that file is much smaller


Being a bit more paranoid about your SQL databases

While the native backup in SBS backs up everything on the box, sometimes you want to be a bit more… well… paranoid about things.  So one of the things you can do is script extral backups of the Line of business apps that run on SQL.  Now if we had the full SBS 2008 premium this process is trivial as there’s a management piece that you can in the GUI set up the backup…but… since I’m backing up something on the SBS box anyway, we’re going to do this with the native express on SBS 2008 standard.


And we do this by using the GUI to build us a script… yes… a GUI to then build a command line…


So click Start


Find the SQL management and Runas Admin.


Find the SQL instance we want to back up.  Now why SQL calls them “instances” I’ll never know.  they should be called.. SQL databases, or SQL things, but not “instances”..but I digress…


When you launch the SQL management you can see that there’s your database in there



Right mouse click on the database, then click on tasks, then click on backup.


Set up the backup you want (full) and to the location you want it to go to.


Up at the top there’s a little button called “script”



And the resulting script looks something like this —


BACKUP DATABASE [SQL_DATA] TO  DISK = N’E:\\Backup\SQL’ WITH NOFORMAT, NOINIT,  NAME = N’SQL_DATA-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO


Save it as Backup.sql


Now we’re on to part two.


How do you backup a database using sql server 2005 express???:
http://social.msdn.microsoft.com/forums/en-US/sqlexpress/thread/95750bdf-fcb1-45bf-9247-d7c0e1b9c8d2/

As you read through this you finally understand what Steve Foster was muttering about all those years ago in the versions between SQL 2000 and SQL 2005 where they took out the job scheduler in SQL express that used to be in there.  For the record it is kinda a bummer this is way more complicated than it needs to be in SQL 2005 express but I digress again.


sqlcmd -S LOCALHOST\SQLEXPRESS -i backup.sql -o output.txt


We need to build something like that and save it to a .bat file and place it next to the backup.sql you saved previously.  I called it sql.bat (I know real original)


Now click it to see if it runs correctly.  If it did, it will build an output.txt file.  Open it up and it should be successful


0 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 12272 pages for database ‘SQL_DATA, file ‘Practice’ on file 1.
100 percent processed.
Processed 1 pages for database ‘SQL_Data, file ‘PracticeLog’ on file 1.
BACKUP DATABASE successfully processed 12273 pages in 2.456 seconds (40.936 MB/sec).


Now go to your task scheduler on SBS 2008 and build a task to do this as often as you like.


Start/search box and type in task scheduler.  Right mouse click on the task scheduler local and build a basic task


Name it something descriptive and click next.  In my case I want it to run daily and reoccur every day.


I want it to start a program (the SQL.bat file I wrote earlier), and I’m going to click on the Open properties dialog box to review the task and click finish.


Now that the properties box is open, I’m going to click the box to allow the task to run if I’m logged in or not.  You will need to enter in admin credential here.


And voila…


Task Scheduler successfully finished “{81d64fd5-8e57-4430-ad2c-d50e49a2cf62}” instance of the “\Backup SQL” task


You now have a task that runs an independent backup of SQL for extra paranoia.

So you want to know if you’ve ran the preventative SBSmonitoring script on your server and you can’t remember if you did?

I posted this the other day on a listserve and figured I should post it here as well…

So you want to know if you’ve ran the preventative SBSmonitoring script on your server and you can’t remember if you did?


http://blogs.technet.com/sbs/archive/2009/07/14/sbs-2008-console-may-take-too-long-to-display-alerts-and-security-statuses-display-not-available-or-crash.aspx

You could run it again and if it gives a message that it can’t add lines to the table because they are already there … you’ve ran it… or  


One of the lines of the script updates the cleanup period value

UPDATE [SBSMonitoring].[dbo].[Settings] SET [Value] = 30 WHERE [Name] = ‘CleanupPeriod’

Runas admin and open SQL management express
Open sbsmonitoring instance
Click down under the databases and find the SBSMonitoring database
Expand the tables section
Find the dbo.settings
right mouse click
Open table
On the right hand side if it says CleanupPeriod 30 you’ve run it






Edwin from www.thirdtier.net pointed out you could also type in
sqlcmd -S %computername%\SBSMonitoring -E -Q “SELECT Value [SBSMonitoring].[dbo].[Settings] WHERE [Name]=’CleanupPeriod'”
at a command prompt ..but hey, I’m a GUI gal.

So you want to know what version of SQL you are running on SBS 2008

So you want to know what version of SQL you are running on SBS 2008… run this query in your SQL management studio express…

 SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’), SERVERPROPERTY(‘InstanceName’)

Connect to the monitoring database and you get…. 9.00.4035.00, SP3, and Express Edition with Advanced Services, SBSMONITORING

Now you need to use the string  \\.\pipe\mssql$microsoft##ssee\sql\query to connect to the rest of the databases on the server.


The Sharepoint is based on Windows Internal database and is 9.00.4035.00 SP3, Windows Internal Database (64bit) MICROSOFT##SSEE


Kewl huh?!

More tips for SQL 2008

The Official SBS Blog : Requirements for Installing SQL 2008 Standard Edition for Small Business:
http://blogs.technet.com/sbs/archive/2009/03/23/requirements-for-installing-sql-2008-standard-edition-for-small-business.aspx

To follow up on this post, some other issues you need to be aware of if you attempt to install SQL 2008 ON the same server as the SBS 2008. (Keeping in mind it would be best if you put the SQL on a second server or a virtualized server rather than on the SBS itself)


One thing you will have to do is remove the SQL 2005 management tools from the server and then install the SQL 2008 management express tools.  You can’t load the 2k8 Full server tools on the 2k8 box.


Cannot install SQL Server Management tool on Windows XP or Windows Vista platform using the SQL 2008 DVD included in SBS 2008 premium edition:
http://support.microsoft.com/kb/958978


Microsoft SQL Server 2008 – Installation Made Easy
http://www.packtpub.com/article/microsoft-sql-server-2008-installation-made-easy


Windows Small Business Server 2008 Technical FAQ
http://technet.microsoft.com/en-us/sbs/cc817589.aspx

“Can I install SQL Server 2008 from Windows Small Business Server 2008
Premium Edition on the first server?

Yes, this is a supported scenario. However the SQL Server 2008 management
tools will not install on the same server; you must install them on another

server.”



If you haven’t yet installed .NET 3.5 sp1 it will install that first.


Then it will install the 4.5 installer 
http://support.microsoft.com/default.aspx?scid=kb;en-us;942288


Two warnings you will get as well



As I said, consider that you are putting it on a DC.



Then only pick those specific pieces you need.  Check with the vendor to see exactly what parts you need and install only the minimum.



And don’t forget you’ll need to remove the SQL 2005 express tools before you can install it “ON” the server.



 

Top SQL issues from the Managed newsgroup

Top issues from the managed newsgroup


Partner Online Technical Support Communities Newsgroups:
https://partner.microsoft.com/US/40014662


In this Issue:

 

TOP SUPPORT ISSUES

 

NEW & UPDATED KB ARTICLES

 

NEW & TOP DOWNLOADS

 

NEW WEBCASTS

 

 

RECENT SUPPORT ISSUES

===================

Issue

———-

 

You want to move databases to a new SQL server running Windows Server 2003

x64 and SQL Server 2005 x64.



Analysis

——–

Database itself is not related to 32/64 bit. You just back up the database on a 64 bit instance and restore it to 32 bit instance without problem. However, please note that the version of the destination instance should be the same or higher than the source instance.

 

Resolution

————–

Back up the databases on a 64 bit instance and restore them to 32 bit instance without problem. Or backup the databases on 32 bit instance and restore them on 64 bit instance.

 

 

 

NEW & UPDATED KB ARTICLES

=========================

 

Access

——

 

960307  Description of the Access 2007 hotfix package (Access.msp): December 16, 2008

http://support.microsoft.com/default.aspx?scid=kb;EN-US;960307

 

958846  Error message when you try to create a new database in Access 2007: “Template could not be instantiated”

http://support.microsoft.com/default.aspx?scid=kb;EN-US;958846

 

SQL

—-

 

957826  Where you can find more information about the SQL Server 2008 builds that were released after SQL Server 2008 and the SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 2

http://support.microsoft.com/default.aspx?scid=kb;EN-US;957826

 

957807  FIX: A new node is not added to a SQL Server 2008 Analysis Services cluster or a SQL Server 2008 Reporting Services cluster if the SQL Server 2008 Database Engine is not installed

http://support.microsoft.com/default.aspx?scid=kb;EN-US;957807

 

 

NEW & TOP DOWNLOADS

====================

SQL Server 2005 Books Online (November 2008)

http://www.microsoft.com/downloads/info.aspx?na=22&p=1&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=%2fdownloads%2fdetails.aspx%3fFamilyID%3dbe6a2c5d-00df-4220-b133-29c1e0b6585f%26DisplayLang%3den

 

Microsoft SQL Server 2008 Express

http://www.microsoft.com/downloads/info.aspx?na=22&p=2&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=%2fdownloads%2fdetails.aspx%3fFamilyID%3d58ce885d-508b-45c8-9fd3-118edd8e6fff%26DisplayLang%3den

 

Microsoft SQL Server 2008 Express with Tools

http://www.microsoft.com/downloads/info.aspx?na=22&p=3&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=%2fdownloads%2fdetails.aspx%3fFamilyID%3d7522a683-4cb2-454e-b908-e805e9bd4e28%26DisplayLang%3den

 

Microsoft SQL Server 2008 Books Online (January 2009)

http://www.microsoft.com/downloads/info.aspx?na=22&p=5&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=%2fdownloads%2fdetails.aspx%3fFamilyID%3d765433f7-0983-4d7a-b628-0a98145bcb97%26DisplayLang%3den

 

NEW WEBCASTS

============                

 

Getting your feet wet with SQL Server 2008: SQL Server Monitoring – February 19, 2009

http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032396920&EventCategory=2&culture=en-US&CountryCode=US

 

Top SQL issues this month

In this Issue:

 

TOP SUPPORT ISSUES

 

NEW & UPDATED KB ARTICLES

 

NEW & TOP DOWNLOADS

 

NEW WEBCASTS

 

 

RECENT SUPPORT ISSUES

===================

Issue

———-

 

After you added a SQL Server 2008 to 2005 cluster, you encountered the following errors:

 

1. The SQL 2008 agent couldn’t be started

 

2. The SQL 2008 didn’t Failover its working only on the node that installed on it.

 

 

 

 

Analysis

——–

From SQL Agent error log we found the following information:

 

2008-11-24 13:09:46 – ! [298] SQLServer Error: 22022, CryptUnprotectData() returned error -2146892987, ‘The requested operation cannot be completed.  

The computer must be trusted for delegation and the current user account must be configured to allow delegation.’ [SQLSTATE 42000]

 

It indicates that the SQL Agent account can’t be delegated. Based on the error, we suspect that SQL Agent account is one of the following: (Could you please double check the credentials )  

 

1. Domain administrator member.

2. The account isn’t configured to be trusted for delegation.

 

 

Resolution

————–

 

1. Change the SQL Server Agent startup account to a non-domain administrator user account.

Note: The user account must have the appropriate rights to start service.

 

2. In the Active Directory Users and Computers snap-in, enable the SQL Server service account to be trusted for delegation.

 

For more information, please refer to:

 

Re: SQL Server 2005 SQL Agent Startup Issue -SQLServer Error: 22022,

CryptUnprotectData()

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3737615&SiteID=1

 

You cannot start the SQL Server Agent service of a failover cluster of SQL Server 2005 if the build of SQL Server is 3179 or a later build

http://support.microsoft.com/kb/943525

 

 

NEW & UPDATED KB ARTICLES

=========================

 

Access

——

957690  Description of the Access 2007 hotfix package (Access.msp): October 28, 2008

http://support.microsoft.com/default.aspx?scid=kb;EN-US;957690

 

SQL

—-

 

957826  Where you can find more information about the SQL Server 2008 builds that were released after SQL Server 2008 and the SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 2

http://support.microsoft.com/default.aspx?scid=kb;EN-US;957826

 

957807  FIX: A new node is not added to a SQL Server 2008 Analysis Services cluster or a SQL Server 2008 Reporting Services cluster if the SQL Server 2008 Database Engine is not installed

http://support.microsoft.com/default.aspx?scid=kb;EN-US;957807

 

 

NEW & TOP DOWNLOADS

====================

 

SQL Server 2005: for Administrators Datasheet

http://www.microsoft.com/downloads/info.aspx?na=22&p=4&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=%2fdownloads%2fdetails.aspx%3fFamilyID%3d1cfb760c-a3fa-4e50-9fc3-0e7c9819d51f%26DisplayLang%3den

 

SQL Server 2005: Performance Tuning and Optimization Datasheet

http://www.microsoft.com/downloads/info.aspx?na=22&p=6&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=%2fdownloads%2fdetails.aspx%3fFamilyID%3d9b180e91-34e7-4274-9a4f-78562b926358%26DisplayLang%3den

 

SQL Server 2000: Performance Tuning and Optimization Datasheet

http://www.microsoft.com/downloads/info.aspx?na=22&p=7&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=%2fdownloads%2fdetails.aspx%3fFamilyID%3d0556317d-66c3-4a6d-a128-4d6f873a28b3%26DisplayLang%3den

 

Risk and Health Assessment for Microsoft SQL Server Datasheet

http://www.microsoft.com/downloads/info.aspx?na=22&p=8&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=%2fdownloads%2fdetails.aspx%3fFamilyID%3d8d54db4d-3232-4cf8-8ba5-e80557835421%26DisplayLang%3den

SQL Server 2005 SP3

http://www.microsoft.com/downloads/info.aspx?na=22&p=13&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=%2fdownloads%2fdetails.aspx%3fFamilyID%3dae7387c3-348c-4faa-8ae5-949fdfbe59c4%26DisplayLang%3den

 

Feature Pack for SQL Server 2005 December 2008

http://www.microsoft.com/downloads/info.aspx?na=22&p=14&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=%2fdownloads%2fdetails.aspx%3fFamilyID%3d536fd7d5-013f-49bc-9fc7-77dede4bb075%26DisplayLang%3den

 

 

NEW WEBCASTS

============                

 

MSDN Webcast: BenkoTips Live and On Demand DemoFest: SQL Server 2008, Spatial Data, and the Virtual Earth SDK (Level 300)

http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032399812&EventCategory=4&culture=en-US&CountryCode=US

 

SQL Server 2008 Governance – January 15, 2009

http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032396918&EventCategory=2&culture=en-US&CountryCode=US

 

How to troubleshoot SQL memory issues part II

Part two of debugging SQL server issues…. from the Partner managed newsgroups.

(There’s good stuff in there)

(II)

 

*Internal Physical memory pressure:

 

 

SQL Server detects high memory consumption internally, causing redistribution of memory between internal components.

 

Internal physical memory pressure may be a result of:

        Responding to the external memory pressure (SQL Server sets lower memory usage caps).

        Changed memory settings (e.g. ‘max server memory’).

        Changes in memory distribution of internal components (due to high percentage of reserved and stolen pages from the buffer pool).

 

 

General troubleshooting steps:

        Identify major memory consumers inside SQL Server.

        Verify server configuration.

        Further actions depend on the investigation: check for workload; possible design issues; other resource bottlenecks.

 

As internal memory pressure is set by SQL Server itself, a logical step is to look at the memory distribution inside SQL Server by checking for any anomalies in buffer distribution. Normally, the buffer pool accounts for the most of the memory committed by SQL Server. To determine the amount of memory that belongs to the buffer pool, we can take a look at the DBCC MEMORYSTATUS output. In the Buffer Counts section, look for the Target value. The following shows part of DBCC MEMORYSTATUS output after the server has reached its normal load.

 

Buffer Counts                  Buffers

—————————— ——————–

Committed                      201120

Target                         201120

Hashed                         166517

Reserved Potential             143388

Stolen Potential               173556

External Reservation           0

Min Free                       256

Visible                        201120

Available Paging File          460640

 

Target is computed by SQL Server as the number of 8-KB pages it can commit without causing paging. Target is recomputed periodically and in response to memory low/high notifications from Windows. A decrease in the number of target pages on a normally loaded server may indicate response to an external physical memory pressure.

 

If SQL Server consumed a lot of memory (as determined by Process: Private Bytes or the Mem Usage column in Task Manager), see if the Target count amounts for a significant portion of the memory. Note that if AWE is enabled, you have to account for AWE allocated memory either from sys.dm_os_memory_clerks or DBCC MEMORYSTATUS output.

 

Consider the example shown above (AWE not enabled), Target * 8 KB = 1.53 GB, while the Process: Private Bytes for the server is approximately 1.62 GB or the Buffer Pool target accounts for 94% of the memory consumed by SQL Server. Note that if the server is not loaded, Target is likely to exceed the amount reported by Process: Private Bytes performance counter, which is normal.

 

If Target is low, but the server Process: Private Bytes or the Mem Usage in Task Manager is high, we might be facing internal memory pressure from components that use memory from outside the buffer pool. Components that are loaded into the SQL Server process, such as COM objects, linked servers, extended stored procedures, SQLCLR and others, contribute to memory consumption outside of the buffer pool. There is no easy way to track memory consumed by these components especially if they do not use SQL Server memory interfaces.

 

Components that are aware of the SQL Server memory management mechanisms use the buffer pool for small memory allocations. If the allocation is bigger than 8 KB, these components use memory outside of the buffer pool through the multi-page allocator interface.

 

Following is a quick way to check the amount of memory that is consumed through the multi-page allocator.

 

– amount of mem allocated though multipage allocator interface

select sum(multi_pages_kb) from sys.dm_os_memory_clerks

 

You can get a more detailed distribution of memory allocated through the multi-page allocator as:

 

select

    type, sum(multi_pages_kb)

from

    sys.dm_os_memory_clerks

where

    multi_pages_kb != 0

group by type

type                                      

 

—————————————— ———

MEMORYCLERK_SQLSTORENG                     56

MEMORYCLERK_SQLOPTIMIZER                   48

MEMORYCLERK_SQLGENERAL                     2176

MEMORYCLERK_SQLBUFFERPOOL                  536

MEMORYCLERK_SOSNODE                        16288

CACHESTORE_STACKFRAMES                     16

MEMORYCLERK_SQLSERVICEBROKER               192

MEMORYCLERK_SNI                            32

 

If a significant amount of memory is allocated through the multi-page allocator (100-200 MB or more), further investigation is warranted.

 

If you are seeing large amounts of memory allocated through the multi-page allocator, check the server configuration and try to determine the components that consume most of the memory by using the previous or the following query.

 

If Target is low but percentage-wise it accounts for most of the memory consumed by SQL Server, look for sources of the external memory pressure as described in the previous subsection (External Physical Memory Pressure) or check the server memory configuration parameters.

 

If you have the max server memory and/or min server memory options set, you should compare your target against these values. The max server memory option limits the maximum amount of memory consumed by the buffer pool, while the server as a whole can still consume more. The min server memory option tells the server not to release buffer pool memory below the setting. If Target is less than the min server memory setting and the server is under load, this may indicate that the server operates under the external memory pressure and was never able to acquire the amount specified by this option. It may also indicate the pressure from internal components, as described above. Target count cannot exceed the max server memory option setting.

First, check for stolen pages count from DBCC MEMORYSTATUS output.

 

Buffer Distribution            Buffers

—————————— ———–

Stolen                         32871

Free                           17845

Cached                         1513

Database (clean)               148864

Database (dirty)               259

I/O                            0

Latched                        0

 

A high percentage (>75-80%) of stolen pages relative to target (see the previous fragments of the output) is an indicator of the internal memory pressure.

More detailed information about memory allocation by the server components can be assessed by using the sys.dm_os_memory_clerks DMV.

 

– amount of memory consumed by components outside the Buffer pool

– note that we exclude single_pages_kb as they come from BPool

– BPool is accounted for by the next query

select

    sum(multi_pages_kb

        + virtual_memory_committed_kb

        + shared_memory_committed_kb) as [Overall used w/o BPool, Kb]

from

    sys.dm_os_memory_clerks

where

    type <> ‘MEMORYCLERK_SQLBUFFERPOOL’

 

– amount of memory consumed by BPool

– note that currenlty only BPool uses AWE

select

    sum(multi_pages_kb

        + virtual_memory_committed_kb

        + shared_memory_committed_kb

        + awe_allocated_kb) as [Used by BPool with AWE, Kb]

from

    sys.dm_os_memory_clerks

where

    type = ‘MEMORYCLERK_SQLBUFFERPOOL’

 

 

Detailed information per component can be obtained as follows. (This includes memory allocated from buffer pool as well as outside the buffer pool.)

 

declare @total_alloc bigint

declare @tab table (

    type nvarchar(128) collate database_default

    ,allocated bigint

    ,virtual_res bigint

    ,virtual_com bigint

    ,awe bigint

    ,shared_res bigint

    ,shared_com bigint

    ,topFive nvarchar(128)

    ,grand_total bigint

);

 

– note that this total excludes buffer pool committed memory as it represents the largest consumer which is normal

select

    @total_alloc =

        sum(single_pages_kb

            + multi_pages_kb

            + (CASE WHEN type <> ‘MEMORYCLERK_SQLBUFFERPOOL’

                THEN virtual_memory_committed_kb

                ELSE 0 END)

            + shared_memory_committed_kb)

from

    sys.dm_os_memory_clerks

 

print

    ‘Total allocated (including from Buffer Pool): ‘

    + CAST(@total_alloc as varchar(10)) + ‘ Kb’

 

insert into @tab

select

    type

    ,sum(single_pages_kb + multi_pages_kb) as allocated

    ,sum(virtual_memory_reserved_kb) as vertual_res

    ,sum(virtual_memory_committed_kb) as virtual_com

    ,sum(awe_allocated_kb) as awe

    ,sum(shared_memory_reserved_kb) as shared_res

    ,sum(shared_memory_committed_kb) as shared_com

    ,case  when  (

        (sum(single_pages_kb

            + multi_pages_kb

            + (CASE WHEN type <> ‘MEMORYCLERK_SQLBUFFERPOOL’

                THEN virtual_memory_committed_kb

                ELSE 0 END)

            + shared_memory_committed_kb))/(@total_alloc + 0.0)) >= 0.05

          then type

          else ‘Other’

    end as topFive

    ,(sum(single_pages_kb

        + multi_pages_kb

        + (CASE WHEN type <> ‘MEMORYCLERK_SQLBUFFERPOOL’

            THEN virtual_memory_committed_kb

            ELSE 0 END)

        + shared_memory_committed_kb)) as grand_total

from

    sys.dm_os_memory_clerks

group by type

order by (sum(single_pages_kb + multi_pages_kb + (CASE WHEN type <>

‘MEMORYCLERK_SQLBUFFERPOOL’ THEN virtual_memory_committed_kb ELSE 0 END) +

shared_memory_committed_kb)) desc

 

select  * from @tab

 

 

Note that the previous query treats Buffer Pool differently as it provides memory to other components via a single-page allocator. To determine the top ten consumers of the buffer pool pages (via a single-page allocator) you can use the following query.

 

– top 10 consumers of memory from BPool

select

    top 10 type,

    sum(single_pages_kb) as [SPA Mem, Kb]

from

    sys.dm_os_memory_clerks

group by type

order by sum(single_pages_kb) desc

 

You do not usually have control over memory consumption by internal components. However, determining which components consume most of the memory will help narrow down the investigation of the problem.

 

System Monitor (Perfmon)

 

You can also check the following counters for signs of memory pressure (see SQL Server Books Online for detailed description):

SQL Server: Buffer Manager object

        Low Buffer cache hit ratio

        Low Page life expectancy

        High number of Checkpoint pages/sec

        High number Lazy writes/sec

 

Insufficient memory and I/O overhead are usually related bottlenecks. See I/O Bottlenecks in this paper.

 

 

 

 

* External Virtual memory pressure:

 

Running low on space in the system page file(s). This may cause the system to fail memory allocations, as it is unable to page out currently allocated memory. This condition may result in the whole system responding very slowly or even bring it to a halt.     

 

General troubleshooting steps:

        Increase swap file size.

        Check for major physical memory consumers and follow steps of external physical memory pressure.

 

You need to determine if page file(s) have enough space to accommodate current memory allocations. To check this, open Task Manager in Performance view and check the Commit Charge section. If Total is close to the Limit, then there exists the potential that page file space may be running low. Limit indicates the maximum amount of memory that can be committed without extending page file space. Note that the Commit Charge Total in Task Manager indicates the potential for page file use, not the actual use. Actual use of the page file will increase under physical memory pressure.

 

Equivalent information can be obtained from the following counters: Memory: Commit Limit, Paging File: %Usage, Paging File: %Usage Peak.

You can roughly estimate the amount of memory that is paged out per process by subtracting the value of Process: Working Set from the Process Private Bytes counters.

If Paging File: %Usage Peak (or Peak Commit Charge) is high, check the System Event Log for events indicating page file growth or notifications of “running low on virtual memory”. You may need to increase the size of your page file(s). High Paging File: %Usage indicates a physical memory over commitment and should be considered together with external physical memory pressure (large consumers, adequate amount of RAM installed).

 

 

  

 

 

Internal Virtual memory pressure:

 

Running low on VAS due to fragmentation (a lot of VAS is available but in small blocks) and/or consumption (direct allocations, DLLs loaded in SQL Server VAS, high number of threads).

 

SQL Server detects this condition and may release reserved regions of VAS, reduce buffer pool commit target, and start shrinking caches.

 

General troubleshooting steps:

        Identify major memory consumers inside SQL Server.

        Verify server configuration.

        Further actions depend on the investigation: check for workload; possible design issues; other resource bottlenecks.

How to troubleshoot SQL server memory related issues (Part 1)

From the Partner managed newsgroup:

This section specifically addresses low memory conditions and ways to diagnose them as well as different memory errors, possible reasons for them, and ways to troubleshoot.

 

Background

 

It is quite common to refer to different memory resources by using the single generic term memory. As there are several types of memory resources, it is important to understand and differentiate which particular memory resource is referred to.

Virtual address space and physical memory

 

In Microsoft Windows, each process has its own virtual address space (VAS). The set of all virtual addresses available for process use constitutes the size of the VAS. The size of the VAS depends on the architecture (32- or 64-bit) and the operating system. In the context of troubleshooting, it is important to understand that virtual address space is a consumable memory resource and an application can run out of it even on a 64-bit platform while physical memory may still be available.

 

For more information about virtual address space, see “Process Address Space” in SQL

Server Books Online and the article called Virtual Address Space (http://msdn.microsoft.com/library/en-us/memory/base/virtual_address_space.asp) on MSDN.

 

 

Address Windowing Extensions (AWE)

and SQL Server

 

Address Windowing Extensions (AWE) is an API that allows a 32-bit application to manipulate physical memory beyond the inherent 32-bit address limit. AWE mechanism technically is not necessary on 64-bit platform. It is, however, present there. Memory pages that are allocated through the AWE mechanism are referred as locked pages on the 64-bit platform.

 

On both 32- and 64-bit platforms, memory that is allocated through the AWE mechanism cannot be paged out. This can be beneficial to the application. (This is one of the reasons for using AWE mechanism on 64-bit platform.) This also affects the amount of RAM that is available to the system and to other applications, which may have detrimental effects. For this reason, in order to use AWE, the Lock Pages in Memory privilege must be enabled for the account that runs SQL Server.

From a troubleshooting perspective, an important point is that the SQL Server buffer pool uses AWE mapped memory; however, only database (hashed) pages can take full advantage of memory allocated through AWE. Memory allocated through the AWE mechanism is not reported by Task Manager or in the Process: Private Bytes performance counter. You need to use SQL Server specific counters or Dynamic Management Views to obtain this information.

For more information about AWE mapped memory, see “Managing memory for large databases” and “Memory Architecture” in SQL Server Books Online  topics and Large Memory Support (http://msdn.microsoft.com/library/en-us/memory/base/large_memory_support.asp) on MSDN.

The following table summarizes the maximum memory support options for different configurations of SQL Server 2005. (Note that a particular edition of SQL Server or Windows may put more restrictive limits on the amount of supported memory.)

 

 

Min server memory (MB)

 

Provides floor

Does not automatically commit on startup

May not be able to reach floor

 

 

Max server memory (MB)

Provides ceiling

SQL Server may not be able to back off due to current usage

 

 

 

SQL Server 2000 organizes the memory it allocates into two distinct regions: the BPool (buffer pool) and MemToLeave (memory to leave) regions. If you make use of AWE memory, there’s actually a third region: the physical memory above 3GB made available by the Windows AWE support.

 

The BPool is the preeminent region of the three. It is the primary allocation pool in SQL Server, serves primarily as a data and index page cache, and is also used for memory allocations less than 8KB. MemToLeave consists of the virtual memory space within the user mode address space that is not used by the BPool. The AWE memory above 3GB functions as an extension of the BPool and provides additional space for caching data and index pages. In SQL Server 2005, the definitioin of Bpool is not so clear as SQL 2000 because SQL 2005 memory mangment is based SQLOS and they are usually considered by memory_clerks.

 

Inside SQL Server 2000’s Memory Management Facilities

http://msdn.microsoft.com/en-us/library/aa175282(SQL.80).aspx

 

SQLOS’s memory manager and SQL Server’s Buffer Pool

http://blogs.msdn.com/slavao/archive/2005/02/11/371063.aspx

 

 

 

SQL Server Memory Definitions

 

Data Cache

 

Consumer

        Connection – Responsible for PSS and ODS memory allocations

        General – Resource structures, parse headers, lock manager objects

        Utilities – Recovery, Log Manager

        Optimizer – Query Optimization

        Query Plan – Query Plan Storage

Advanced Consumer

        Ccache – Procedure cache. Accepts plans from the Optimizer and Query Plan consumers. Is responsible for managing that memory and determines when to release the memory back to the Bpool.

        Log Cache – Managed by the LogMgr, which uses the Utility consumer to coordinate memory requests with the Bpool.

 

 

 

 

Memory pressures

 

Memory pressure denotes a condition when limited amount of memory is available. Identifying when SQL Server runs under a memory pressure will help you troubleshoot memory-related issues. SQL Server responds differently depending on the type of memory pressure that is present. The following table summarizes the types of memory pressures, and their general underlying causes. In all cases, you are more likely to see timeout or explicit out-of-memory error messages.

 

 

*External Physical memory pressure:       

 

Physical memory (RAM) running low. This causes the system to trim working sets of currently running processes, which may result in overall slowdown.

 

SQL Server detects this condition and, depending on the configuration, may reduce the commit target of the buffer pool and start clearing internal caches.

 

 

General troubleshooting steps:

        Find major system memory consumers.

        Attempt to eliminate (if possible).

        Check for adequate system RAM and consider adding more RAM (usually requires more careful investigation beyond the scope of this paper).

 

Open Task Manager in Performance view and check the Physical Memory section, Available value. If the available memory amount is low, external memory pressure may be present. The exact value depends on many factors, however you can start looking into this when the value drops below 50-100 MB. External memory pressure is clearly present when this amount is less than 10 MB.

 

The equivalent information can also be obtained using the Memory: Available Bytes counter in System Monitor.

If external memory pressure exists and you are seeing memory-related errors, you will need to identify major consumers of the physical memory on the system. To do this, look at  Process: Working Set performance counters or the Mem Usage column on the Processes tab of Task Manager and identify the largest consumers.

The total use of physical memory on the system can be roughly accounted for by summing the following counters.

        Process object, Working Set counter for each process

        Memory object

        Cache Bytes counter for system working set

        Pool Nonpaged Bytes counter for size of unpaged pool

        Available Bytes (equivalent of the Available value in Task Manager)

 

If there’s no external pressure, the Process: Private Bytes counter or the VM Size in Task Manager should be close to the size of the working set (Process: Working Set or Task Manager Mem Usage), which means that we have no memory paged out.

 

Note that the Mem Usage column in Task Manager and corresponding performance counters do not count memory that is allocated through AWE. Thus the information is insufficient if AWE is enabled. In this case, you need to look at the memory distribution inside SQL Server to get a full picture.

 

You can use the sys.dm_os_memory_clerks DMV as follows to find out how much memory SQL Server has allocated through AWE mechanism.

 

select

    sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb]

from

    sys.dm_os_memory_clerks

 

Note that in SQL Server, currently only buffer pool clerks (type = ‘MEMORYCLERK_SQLBUFFERPOOL’) use this mechanism and only when AWE is enabled.

Relieving external memory pressure by identifying and eliminating major physical memory consumers (if possible) and/or by adding more memory should generally resolve the problems related to memory.

         

SQL Server 2008 Cumulative Update 1 is available

Aaron Bertrand : SQL Server 2008 Cumulative Update 1 is available:
http://sqlblog.com/blogs/aaron_bertrand/archive/2008/09/23/sql-server-2008-cumulative-update-1-is-available.aspx


They just RTM’d SQL 2008 and already there’s an update.  And ya wonder why those of us hold back a bit from deploying?