The SQL Server Buffer Pool and Buffer Cache Hit Ratio

Posted on March 16, 2011 by Shoeb

What is the SQL Server Buffer Cache?

The Buffer Cache is an area of the SQL Server Buffer Pool (which in turn is part of the overall SQL Server Process space) and in simple terms is the amount of your database data (data pages) that is stored in memory.

What is Buffer Cache Hit Ratio?

The performance measure buffer cache hit ratio expresses as a percentage, how often a request for a database data page, can be served from the Buffer Pool. The alternative action is for SQL Server to have to fetch the data page from disk.

A value of 99% indicates that pages were found in memory 99% of the time. The other 1% required physical disk access. A consistent value below 90% indicates that more physical memory is needed on the server. Lower than 90% might be perfectly fine on a datawarehouse but this would probably be unacceptable for an OLTP environment.

How can I determine the Buffer Cache Hit Ratio of my server?

In order to determine you buffer cache hit ratio you can use the Windows Performance Monitor.

Buffer_Cache

Buffer_Cache.jpg

1. Just go to Start > Programs > Administrative Tools > Performance
2. Right click the graph and choose Add Counters
3. Select Performance object, SQLServer:Buffer Manager
4. Add, Buffer Cache Hit Ratio.

Posted in SQL Server 2008 R2, Windows Server 2008 | Tagged | Leave a comment

Dynamic Memory Management – SQL Server 2008 R2

Posted on February 4, 2011 by Shoeb

The default memory management behavior of the Microsoft SQL Server Database Engine is to acquire as much memory as it needs without creating a memory shortage on the system. The Database Engine does this by using the Memory Notification APIs in Microsoft Windows.

Virtual address space of SQL Server can be divided into two distinct regions: space occupied by the buffer pool and the rest. If AWE mechanism is enabled, the buffer pool may reside in AWE mapped memory, providing additional space for database pages.

The buffer pool serves as a primary memory allocation source of SQL Server. External components that reside inside SQL Server process, such as COM objects, and not aware of the SQL Server memory management facilities, use memory outside of the virtual address space occupied by the buffer pool.

When SQL Server starts, it computes the size of virtual address space for the buffer pool based on a number of parameters such as amount of physical memory on the system, number of server threads and various startup parameters. SQL Server reserves the computed amount of its process virtual address space for the buffer pool, but it acquires (commits) only the required amount of physical memory for the current load.

The instance then continues to acquire memory as needed to support the workload. As more users connect and run queries, SQL Server acquires the additional physical memory on demand. A SQL Server instance continues to acquire physical memory until it either reaches its max server memory allocation target or Windows indicates there is no longer an excess of free memory; it frees memory when it has more than the min server memory setting, and Windows indicates that there is a shortage of free memory.

As other applications are started on a computer running an instance of SQL Server, they consume memory and the amount of free physical memory drops below the SQL Server target. The instance of SQL Server adjusts its memory consumption. If another application is stopped and more memory becomes available, the instance of SQL Server increases the size of its memory allocation. SQL Server can free and acquire several megabytes of memory each second, allowing it to quickly adjust to memory allocation changes.

Source: http://msdn.microsoft.com/en-us/library/ms178145.aspx

Enabling AWE Memory for SQL Server 2008 R2

This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible.

Address Windowing Extensions (AWE) allow 32-bit operating systems to access large amounts of memory. AWE is exposed by the operating system, and is implemented with slight differences in Microsoft Windows 2000 Server and Windows Server 2003.

Posted in SQL Server 2008 R2 | Tagged , | Leave a comment

How to Change the Transaction Log File size to less than the Intial Size Setting

Posted on January 25, 2011 by Shoeb

You have L (Log) drive with 50GB.  One of the main databases has 40GB logs. You are backing up log files, truncating log files but size of the log is not decreasing. You have checked the database properties; initial size of the database log is 38GB. How do you reduce this size?

1.      Take a full backup (always)

2.      Check the size of the log:  SP_HelpDB SQLRep (DB Name: SQLRep)

3.      Then run: Alter database SQLRep SET Recovery simple;

SQL 2005 could do: Backup Log <databasename> with truncate_only;

2008/ 2008 R2 will not allow truncate_only

4.      Run: DBCC Shrinkfile (logical_logfile_name, size)

DBCC Shrinkfile(SQLRep_log, 10)

Note: if you give size=10 then it compress ur log file size to 10MB / 10240 kb

Manual Database Shrinking (Must Know):

When using the DBCC SHRINKDATABASE statement, you cannot shrink a whole database to be smaller than its original size. Therefore, if a database was created with a size of 10 MB and grew to 100 MB, the smallest the database could be reduced to is 10 MB, even if all the data in the database has been deleted.

However, you can shrink the individual database files to a smaller size than their initial size by using the DBCC SHRINKFILE statement. You must shrink each file individually, instead of trying to shrink the whole database.

Posted in SQL Server 2008 R2 | Tagged | Leave a comment

The job failed. The owner (sa) of job syspolicy_purge_history does not have server access.

Posted on December 20, 2010 by Shoeb

You have renamed the ‘sa’ account like others have done to avoid login attacks on the ‘sa’ account. Now syspolicy_purge_history is failing!

You can avoid this error by running the following set of T-SQL commands to refresh the SQL Agent job cache: EXECUTE msdb.dbo.sp_sqlagent_notify @op_type = N’J’, @job_id = @job_id, @action_type = N’U’

where @job_id is the job_id for the syspolicy_purge_history job. You can find this job_id using the following query: select * from msdb.dbo.sysjobs where name = ‘syspolicy_purge_history’

So on my server, I ran the following script to update the owner account name for this job in the job cache: EXECUTE msdb.dbo.sp_sqlagent_notify @op_type = N’J’, @job_id = ‘C725203D-5603-4B96-96E7-A0145A80862F’, @action_type = N’U’

Posted in SQL Server 2008 R2 | Tagged | 4 Comments

2008 R2 Policy-Based Management

Posted on December 3, 2010 by Shoeb

Policy-Based Management enables the efficient management of multiple SQL Server instances from a single location. Easily create policies that control security, database options, object naming conventions, and other settings at a highly granular level. Policies can evaluate servers for compliance with a set of predefined conditions and prevent undesirable changes being made to servers.

  • Management is centralized, thereby reducing the need to configure each server separately
  • Administration is simplified, reducing the effort required to maintain standardization and compliance, even in complex environments
  • Configuration is straightforward and can be done entirely within SQL Server Management Studio
  • Out-of-the-box predefined policies make it easy to get started
  • Backwards compatibility supports managing instances of SQL Server 2008, SQL Server 2005, and SQL Server 2000

Additional Resources:

  1. Can I use PBM against a SQL2K & SQL2K5 instances?
  2. Creating A Policy With the GUI
  3. Types of Policies
  4. Using SQL Server 2008 PBM to Monitor Free Space
  5. How to create Policies using PowerShell scripts
  6. Free PowerShell Scripts from Idera
Posted in SQL Server 2008 R2, Uncategorized | Tagged | 7 Comments

2008 – Data Profiling

Posted on April 7, 2010 by Shoeb

SQL Server 2008 Integration Services includes the Data Profiling task, which enables the quality of data to be inspected before adding it to your databases. The task creates a profile that includes information such as the number of rows, NULL values, and distinct values that are present. Read profiles created by the Data Profiling task by using the Data Profile Viewer, and then clean and standardize the data as appropriate.

5 Comments

2008 – Data compression

Posted on March 25, 2010 by Shoeb

Data compression reduces the amount of storage space needed to store tables and indexes, which enables more efficient storage of data. Data Compression does not require changes be made to applications in order to be enabled.

  • Save disk storage
  • Enable compression option for individual tables or indexes
  • Configuration is easy using the Data Compression wizard
  • Applications do not need to be reconfigured as SQL Server handles compression and decompression of data
  • Compression can improve disk I/O and memory utilization

Â

Additional Resources:

Data Compression in SQL Server 2008
Types of data compression in SQL Server 2008
Implementing Data Compression in SQL Server 2008

11 Comments

2008 – Data Auditing

Posted on March 25, 2010 by Shoeb

Data Auditing provides a simple way to track and log events relating to your databases and servers. You can audit logons, password changes, data access and modification, and many other events. Tracking these events helps maintain security and can also provide valuable troubleshooting information. The results of audits can be saved to file or to the Windows Security or Application logs for later analysis or archiving.

Additional Resources:

SQL Server 2008 – Auditing
SQL Server Audit Action Groups and Actions
Understanding SQL Server Audit

Leave a comment

2008 – Conflict Detection in Peer-to-Peer Replication

Posted on March 25, 2010 by Shoeb

In a peer-to-peer replication scenario, all nodes in the replication topology contain the same data and any node can replicate to any other node, leading to the possibility of data conflicts. Use conflict detection to make sure that no such errors go undetected and that data remains consistent.

  • Ensures that data remains consistent across nodes that have been configured to use peer-to-peer replication
  • Has a minimal performance impact if applications are configured to write changes for specific rows to specific nodes, which helps to avoid replication conflicts occurring in the first place

Â

Additional Resources:

Windows 2008 and Replication
New replication features in SQL Server 2008 and what they mean to you!
How to: Configure Conflict Detection for Peer-to-Peer Transactional Replication

Leave a comment

What are the new features in SQL Server 2008 R2?

Posted on March 25, 2010 by Shoeb

In Microsoft SQL Server 2008 R2, several components have new or improved features:

Master Data Services :
Master Data Services is comprised of a database, configuration tool, Web application, and Web service that you use to manage your organization’s master data and maintain an auditable record of that data as it changes over time. You use models and hierarchies to group and organize data to prepare it for further use in business intelligence and reporting tools, data warehouses, and other operational systems. Master Data Services integrates with source systems and incorporates business rules to become the single source of master data across your organization.

PowerPivot for SharePoint :
PowerPivot for Excel and PowerPivot for SharePoint are client and server components that integrate Analysis Services with Excel and SharePoint. You use PowerPivot for Excel to create PowerPivot workbooks that assemble and relate large amounts of data from different sources. On a SharePoint farm, PowerPivot for SharePoint adds shared services and infrastructure for querying and managing PowerPivot workbooks that you publish to SharePoint.

Multi-Server Administration and Data-Tier Application :
The SQL Server Utility forms a central repository for performance data and management policies that tailor the operation of instances of the Database Engine that have been enrolled in the utility. It also includes a Utility Explorer for centralized management, and dashboards that report the state of the managed instances. A data-tier application (DAC) forms a single unit for developing, deploying, and managing the database objects used by an application.

24 Comments