An Overview on SQL Server Checkpoints

checkpoint

Commercial database systems like SQL server have many recovery mechanisms to restore data hardware or software failure. Checkpoints are part of such recovery mechanism.SQL Server Database Engine is programmed to perform changes or modifications to database pages in buffer cache (memory) first and after some processing the modification are written on the disk files. This “processing” involves Checkpoint which writes all the dirty pages existing on Buffer Cache to Physical disk. It also enters log records from buffer log to physical file. Checkpoint in SQL Server was introduced to reduce the time required for recovery during an unexpected shutdown or system failure. Database Engine issues a checkpoint for each database on regular intervals.

Various factors and conditions (depending upon the Recovery Interval settings done) commit a checkpoint to issue. It can be an execution of ALTER DATABASE command, manual execution of CHECKPOINT, Server clean shutdown, or even in case SQL database is in SIMPLE mode and its log is 70% full.

Talking about Checkpoints, a regular definition states that once a transaction is made checkpoint writes about it. But this is not true, checkpoint writes about all the pages which have been changed (marked dirty) since the last checkpoint. It doesn’t depend on transaction, whether it is committed or not. tempdb is an exception where data pages are not written to disk as a part of checkpoint. Below section will elaborate in detail, what exactly happens when checkpoint is triggered;

Working Operation of CHECKPOINT

Changes done in memory of respective databases are checked as per last checkpoint and then all the dirty pages of databases are written to the disk. It is independent of the state of the transaction which has made the changes. Then, all the log records along with the most recent log record describing changes made in the database are written to disk first before page is written to disk assuring the recovery can be done through this write-ahead logging.

Sequential entries are made to the log from all the transactions. And it is not possible to write selective records to disk. Thus, when a user writes a dirty page to disk even if having only single log record creating problem in it, all the log records prior to this log record will be written on the page.Later, generation of log records with information about checkpoints takes place.The LSN (Log Sequence Number) of checkpoint is recorded in boot page of that database in the dbi_checkptLSN field along with other critical information. If SIMPLE Recovery mode is assigned, the VLFs in log are checked if it is possible to mark them inactive. These tasks are independent of what type of CHECKPOINT has been made (Manual or Automatic).

Categories of SQL Server Checkpoints

Automatic

Automatic Checkpoint is the most common one, and it issues automatically in the background as per the settings done in Recovery Interval server configuration option. This Recovery Interval parameter is defined at server level. By default, this parameter value is 0(zero) in which target recovery interval is 1 minute. Automatic checkpoints are throttled on the basis of number of outstanding writes and on the fact whether Database Engine senses any rise in write latency above 20 milliseconds.

The following is the query to define the [recovery interval]:

USE [master];
GO

EXEC [sp_configure] '[recovery interval]', 'seconds'
GO;

Indirect

Indirect Checkpoints were added in SQL Server 2012 and this also runs in the background but the difference is it runs on the basis of user-specified target time for recovery for respective databases. If user has used ALTER DATABASE to set TARGET_RECOVERY_TIME as >0, it will be used overriding the Recovery Interval specified at server level completely, avoiding Automatic Checkpoint for that Database. It has been observed that Indirect checkpoint are faster and provide more predictable recovery time as compared to automatic checkpoints.

Recovery time required for database recovery is reduced when indirect checkpoints are preferred. This is done by factoring in cost of random I/O during a REDO operation. These checkpoints also reduceassociated I/O thwarting by continuously writing dirty pages to disk.

Here is the syntax of the query to produce indirect checkpoint:

USE [master];
GO

ALTER DATABASE … SET TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUTES}
GO;

Manual

As the name defines, this command runs like any other T-SQL statement and once issued it will run to its completion. It must be noted that Manual Checkpoint will run for the current database only. Checkpoint_Duration can also be defined in seconds at database level, which defines the time to complete checkpoint and is optional.

Here is the syntax to issue manual checkpoint:

USE [master];
GO

CHECKPOINT [ checkpoint_duration ]
GO;

Internal

User cannot control these Internal Checkpoints. This is issued by various server operations like; backup & database-snapshot creation ensuring that the images taken synchronize with the state of log. Following events will follow up with generation of internal checkpoints;

When databases are added or removed with ALTER DATABASE command. It is triggered when backup of a database is taken or database snapshot is created explicitly or internally. A clean Shutdown (Shutdown with NOWAIT) will also trigger internal checkpoint. Changes in Recovery Model from Full\Bulk-logged to Simple will also initiate internal checkpoint.

How Recovery-Interval Stimulates Recovery Performance?

Normally, default values are enough to provide optimum recovery performance.But changing the recovery interval can be practiced to improve performance. You can opt to change the interval time in some conditions like; if the recovery process takes longer than 1 minute when long-running transactions are not rolled back. Frequent checkpoints are ruining performance, as under high frequency of running checkpoints performance of SQL Server can drop due to heavy I/O activity.

In case recovery interval setting has to be changed, it is recommended to increase it slowly with small increments. You can examine the relative recovery performance with gradual increase in recovery interval. This will help you get effective results.

Conclusion:

Checkpoints are useful repository of information and serves best for recovery of SQL server databases. This article shows Checkpoint’s importance, relevance and categories of checkpoints. Practicing a better checkpoint method will not only enhance the server performance but it also renders a better recovery plan. Recovery Interval can be chosen as per I/O activities and significance of the databases; it must be up-to-date and at the same time should not hamper the performance of server. One thing must be considered that the checkpoint log records are never overwritten by successive checkpoints. It will be only overwritten when log wraps and VLFs (Virtual Log Files) are re-used.

About the author:

This is the guest post by Andrew Jackson, a US based SQL Server DBA, who is currently working for SysTools Group, a company that provides its services across a diverse range that includes data recovery, digital forensics, and cloud backup. 

He can be contacted through his blog or via LinkedIn.

Windows policy Lock Pages in Memory option and SQL Server instance in VM

Memory

As we know, this Windows policy Lock Pages in Memory option determines which accounts can use a process to keep data in physical memory, preventing the Windows operating system from paging out a significant amount of data from physical memory to virtual memory on disk. This Windows policy is disabled by default. This Windows policy (Lock Pages in Memory option) must be enabled for SQL Server service account. That’s because, setting this option can increase the performance of SQL Server instance running on the virtual machine (VM) where paging memory to disk is expected. When not enabled, there is a risk that SQL Server buffer pool pages may be paged out from physical memory to virtual memory on disk.
*Note: Only applies to SQL Server instances running on the virtual machine (VM).

You can use the following Transact-SQL script to check whether or not this Windows policy is enabled for SQL Server Service Startup account:

SET NOCOUNT ON;

DECLARE @CMDShellFlag [bit] ,
		@CheckCommand [nvarchar](256);
		

DECLARE @xp_cmdshell_output TABLE
    (
      [output] [varchar](8000)
    );

IF NOT EXISTS ( SELECT  *
                FROM    [sys].[configurations]
                WHERE   [name] = N'xp_cmdshell'
                        AND [value_in_use] = 1 )
    BEGIN
		
        SET @CMDShellFlag = 1;

        EXEC [sp_configure] 'show advanced options', 1;

        RECONFIGURE;

        EXEC [sp_configure] 'xp_cmdshell', 1;

        RECONFIGURE;

        EXEC [sp_configure] 'show advanced options', 0;

        RECONFIGURE;
    END

SELECT  @CheckCommand = 'EXEC [master]..[xp_cmdshell]' + SPACE(1) + QUOTENAME('whoami /priv', '''');

INSERT INTO @xp_cmdshell_output
        ( [output] )
EXEC [sys].[sp_executesql] @CheckCommand;

IF EXISTS ( SELECT  *
            FROM    @xp_cmdshell_output
            WHERE   [output] LIKE '%SeLockMemoryPrivilege%enabled%' )
    SELECT  'Windows policy Lock Pages in Memory option is enabled' AS [Finding];
ELSE
    SELECT  'Windows policy Lock Pages in Memory option is disabled' AS [Finding]; 

IF @CMDShellFlag = 1
    BEGIN

        EXEC [sp_configure] 'show advanced options', 1;

        RECONFIGURE;

        EXEC [sp_configure] 'xp_cmdshell', 0;

        RECONFIGURE;

        EXEC [sp_configure] 'show advanced options', 0;

        RECONFIGURE;
    END

SET NOCOUNT OFF;

Here are instructions to enable Lock Pages in Memory option Windows policy:

  1. On the Start menu, click Run. In the Open box, type gpedit.msc.
  2. On the Local Group Policy Editor console, expand Computer Configuration, and then expand Windows Settings.
  3. Expand Security Settings, and then expand Local Policies.
  4. Select the User Rights Assignment folder. The policies will be displayed in the details pane.
  5. In the pane, double-click Lock pages in memory.
  6. In the Local Security Setting – Lock pages in memory dialog box, click Add User or Group.
  7. In the Select Users, Service Accounts, or Groups dialog box, add SQL Server Service Startup account.
  8. Restart SQL Server for this change to take effect.

For more information, refer to article “Enable the Lock Pages in Memory Option (Windows)” on the Microsoft Development Network website.

Very Important Note: You must also configure the following two server memory options, min server memory and max server memory, if you enable Windows policy Lock Pages in Memory option. That’s because these memory options help you to control the amount of server memory with SQL Server can consume. Ignoring this recommendation can severely reduce SQL Server performance and even prevent SQL Server from starting. For more information about how to configure these two memory options, refer to article “Server Memory Server Configuration Options” on the Microsoft Development Network website.

I hope you will find this post useful. :)

Have a nice week….

Basit

SQL Server: Converting binary data to a hexadecimal string

Problem

I have a requirement to convert the binary data inside a table column or variable to a hexadecimal string. Is it possible to directly convert the binary data to a hexadecimal string characters?


Solution

Before SQL Server 2005, it was not possible to directly convert the binary data to a string of hexadecimal characters, however, since SQL Server 2005; you can do the binary to hexadecimal conversion with one of the following two Transact-SQL approaches:

Option 1: Using CONVERT function of Transact-SQL

In SQL Server 2008 and later versions, you can use the Transact-SQL CONVERT function with its one of the following two binary styles to convert the binary data into a hexadecimal string:

Convert_function_binary_style

Microsoft Reference: http://msdn.microsoft.com/en-GB/library/ms187928.aspx

The following is the basic syntax for CONVERT function:

CONVERT ( destination_data_type [ ( length ) ] , expression, style )

For example, suppose you want to convert the following binary value to hexadecimal string: 0x0200CDDBC2A60A08B57EDF7622CA52A45EC3BC234CE6A0F7D038F3BAE0EC00. To do that, you use the CONVERT function with binary styles as follows:

SET NOCOUNT ON;

DECLARE @BinaryValue VARBINARY(256) 

SET @BinaryValue = 0x0200CDDBC2A60A08B57EDF7622CA52A45EC3BC234CE6A0F7D038F3BAE0EC00

SELECT  CONVERT([varchar](512), @BinaryValue, 1)
SELECT  CONVERT([varchar](512), @BinaryValue, 2) 

SET NOCOUNT OFF;

It returns the following output:

Convert_function_binary_example_output

For more information, see CAST and CONVERT (Transact-SQL).

Option 2: Using xml value() Method with the XQUERY functions

In SQL Server 2005 and later versions, you can use xml value() method with the XQUERY functions to convert the binary data into a hexadecimal string. The xml value() method let you extract the value of a specified XML node as a scalar value and then converts the value to a specified SQL Server data type. You can use SQL Server XQuery Extension Functions and xs:hexBinary base type Constructor Function within a xml value() method to convert the binary data into a hexadecimal string. The SQL Server XQuery Extension Functions includes sql:column() and sql:variable() functions, which can be used to expose a relational value inside an XQuery expression. For example, sql:variable() Function exposes a variable that contains a SQL relational value inside an XQuery expression while sql:column() Function exposes a column that contains a SQL relational value inside an XQuery expression. The xs:hexBinary base type Constructor Function creates the hexadecimal character sequence.

The following is the basic syntax of the value() method with the XQUERY functions to convert the binary data into a hexadecimal string:

value(xs:hexBinary(sql:variable("VariableName") , SQLType)
-- or -- 
value(xs:hexBinary(sql:column("ColumnName") , SQLType)

For example, suppose you want to convert the following binary value to hexadecimal string: 0x0200CDDBC2A60A08B57EDF7622CA52A45EC3BC234CE6A0F7D038F3BAE0EC00. To do that, you use the xml value() Method with the XQUERY functions as follows:

SET NOCOUNT ON;

DECLARE @BinaryValue	[varbinary](256) ,
		@x				[xml]

SET @x = '<root></root>;'

SET @BinaryValue = 0x0200CDDBC2A60A08B57EDF7622CA52A45EC3BC234CE6A0F7D038F3BAE0EC00

SELECT  N'0x' + @x.value('xs:hexBinary(sql:variable("@BinaryValue"))',
                         '[varchar](512)')

SET NOCOUNT OFF;
GO

It returns the following output:

value_function_with_XQuery_functions

Option 3: Using sys.fn_varbintohexstr undocumented function and sp_hexdecimal stored procedure

You can use sys.fn_varbintohexstr undocumented function to return a character string which contains the hexadecimal representation of a binary value. For example, the following script shows how you can use sys.fn_varbintohexstr undocumented function to convert binary value to a hexadecimal string:

SET NOCOUNT ON;

DECLARE @BinaryValue [varbinary](256); 

SET @BinaryValue = 0x0200CDDBC2A60A08B57EDF7622CA52A45EC3BC234CE6A0F7D038F3BAE0EC00;

SELECT  [sys].[fn_varbintohexstr](@BinaryValue);

SET NOCOUNT OFF;
GO

It returns the following output:

fn_varbintohexstr_undocumented_version

You can also use sp_hexdecimal stored procedure as described in a Microsoft Knowledge Base Article: “INFO: Converting Binary Data to Hexadecimal String” to convert binary value to a hexadecimal string.

My preferred option

I prefer option 1 and 2 mentioned in this post for converting the binary value to a hexadecimal string. That is why; I wrote the following function using the first two options, which you can use to return a string containing the hexadecimal representation of a binary value:

IF OBJECT_ID(N'dbo.ufn_binvaluetohexdecstr') IS NOT NULL
    BEGIN
        DROP FUNCTION [dbo].[ufn_binvaluetohexdecstr];
    END
GO

CREATE FUNCTION [dbo].[ufn_binvaluetohexdecstr] (@p_binhexvalue [varbinary](256))
RETURNS [varchar](512)
AS
    BEGIN

        DECLARE @x				[xml] ,
				@OutPutStrHex	[varchar](512) ,
				@Version		[numeric](18, 1);

		SET @x = '<root></root>';
        SET @Version = CAST(LEFT(CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)),
						CHARINDEX(N'.', CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128))) - 1) + N'.'
						+ REPLACE(RIGHT(CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)),
						LEN(CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)))
						- CHARINDEX(N'.', CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)))), N'.', N'') AS [numeric](18, 10));
		        
        IF @Version >= 10.5
            BEGIN 
                SELECT  @OutPutStrHex = CONVERT([varchar](512), @p_binhexvalue, 1);
            END
        ELSE
            BEGIN 
                SELECT  @OutPutStrHex = N'0x' + @x.value('xs:hexBinary(sql:variable("@p_binhexvalue"))',
                                                         '[varchar](512)');
            END
		
        RETURN (SELECT @OutPutStrHex) 
    END
GO

I hope you will find this post useful. :)

Have a good week!

Basit

My articles published on SearchSQLServer.com from January 2014 to September 2014

The following is the list of my articles published on SearchSQLServer.com from January 2014 to September 2014. To read these articles, you must be a member of SearchSQLServer.com.

SearchSQLServer.com


Learn how to use SQL Server Import and Export Wizard, Published on September 3, 2014

SQL Server 2014 provides a variety of methods to transfer data between SQL Server databases and Microsoft Excel documents. One method is the SQL Server Import and Export Wizard, and this article provides a step-by-step guide for using this wizard to transfer data between SQL Server databases and Microsoft Excel worksheets.


The key to boosting OLTP with SQL Server 2014 In-Memory, Published on August 8, 2014

The SQL Server 2014 in-memory OLTP engine — project name Hekaton — is one of the key new performance-related architectural enhancements to the SQL Server database engine. It is designed to boost performance of OLTP and data-warehouse workloads and reduce processing times. The SQL Server 2014 in-memory OLTP engine is supported on 64-bit Enterprise, Developer and Evaluation editions of SQL Server 2014.

This article explains how to improve the performance of OLTP applications using SQL Server 2014 in-memory OLTP engine. This article also details what makes SQL Server 2014 in-memory OLTP engine different from other vendors in-memory OLTP engines and how to use that to your advantage.


Top four tips for virtualizing your SQL Server instance, Published on July 28, 2014

The increasing need for SQL Server instances is raising costs. Therefore, controlling the cost of databases and related hardware resources and improving and expanding them as necessary have become increasingly important. An ideal solution to this problem is virtualization.

This article shows you how to control the costs through effective virtualization.


The top three SQL Server third-party tools, Published on June 30, 2014

Monitoring and optimizing SQL Server performance, as well as identifying and fixing performance bottlenecks in SQL Server databases, can be a daunting task for database administrators (DBAs), developers and application support teams. SQL Server performance is influenced by factors like server hardware, operating system setup and database configuration. SQL Server provides a comprehensive set of tools for monitoring its own performance, but many SQL Server DBAs, developers and application support teams choose to use third-party performance monitoring tools.

SQL Server third-party performance monitoring tools are more flexible than the tools that ship with the product, and they provide a centralized repository for performance-related data. Moreover, they are easy to use and provide real-time visibility into all SQL Server performance. Third-party monitoring tools can help monitor SQL Server and database performance, resolve potential problems with threshold-based and trend-based alerts, and isolate the root cause of application performance issues.

This article explores three of the top SQL Server third-party tools for quickly identifying and resolving performance bottlenecks.


Tips to escape a SQL Server pitfall or performance bottleneck, Published on April 29, 2014

Over the last decade, many complex enterprise applications have been developed and deployed using Microsoft SQL Server. Today, SQL Server is a cornerstone of modern business applications and is at the center of the business processes of many leading companies. SQL Server applications range from line-of-business applications in production, to internal customer relationship management and decision support systems, to customer-facing e-commerce and Web self-service applications. Consequently, SQL Server performance and scalability are high on IT priority lists, and delivering optimal SQL Server performance and scalability is one of the key tasks of all SQL Server DBAs.

However, many SQL Server systems suffer from poor performance and scalability, often caused by poor database design, index design and a SQL Server system improperly configured for the workload. The reason for this is that the main goal of the development process of any large-scale SQL Server project is functionality, with performance and scalability frequently treated as an afterthought.

While troubleshooting SQL Server database system performance problems is a difficult task, significant performance improvements can be achieved with a relatively small time investment. This article provides five tips for SQL Server users, from avoiding a hardware performance bottleneck to dealing with database design.


Dive deep into SQL Server 2014 in-memory OLTP, Published on March 14, 2014

The SQL Server 2014 in-memory online transaction processing (OLTP) engine, previously code-named “Hekaton“, allows you to create in-memory optimized OLTP tables within a conventional relational database. SQL Server 2014 in-memory OLTP engine enables customers to build mission-critical applications and Big Data solutions using high-performance, in-memory technology across OLTP. It is one of the key new performance-related architectural enhancements to SQL Server 2014, although it is only supported on 64-bit Enterprise, Developer and Evaluation editions.

Checkout this article to learn how to set it up.


Defining half a dozen daily SQL Server DBA responsibilities, Published on February 20, 2014

SQL Server database administration can be a complex and stressful job. Database administrators’ responsibilities cover the performance, integrity and security of business data and SQL Server databases. To fulfil their duties and to make business data available to its users, database administrators have to perform routine DBA checks on their SQL Servers to monitor their status.

So, what critical aspects of SQL Server should all DBAs include in their daily checklist? This article highlights six daily DBA responsibilities that every SQL Server manager should perform.


SQL Server encryption features in SQL Server 2014, Published on February 6, 2014

Data security is a vital and growing concern for many organizations due to the increasing loss and unauthorized disclosure of confidential data. Regulatory requirements often require robust encryption solution for data such as credit card and Social Security numbers. SQL Server 2014 provides database administrators with several options to encrypt data when transmitted through the network, while creating a backup, or when stored on the server or network.

This article guides you through the different encryption options that are available in SQL Server 2014 to encrypt confidential data in the SQL Server database.


Digging through SQL Server OLAP storage models, Published on January 28, 2014

The physical storage design for your multidimensional applications affects the latency, size and performance of your project. Therefore, when designing the physical storage for it, you must determine how to store the dimensions and measures, and how to design an effective physical storage strategy for multidimensional applications.

This article shows how to distinguish among the three SQL Server OLAP storage models and decide which one is best in different use cases.


Eight key SQL Server 2014 features, Published on January 23, 2014

Microsoft SQL Server is an enterprise database server that is the cornerstone of modern business applications and is in the center of the business processes of many leading organizations. The latest release of Microsoft SQL Server, SQL Server 2014, has many new features that let you design, build, and deploy high-performance mission-critical OLTP applications and Big Data solutions.

This article reviews the eight key new SQL Server 2014 Database Engine features.


I hope you will find these articles useful. :)

Have a good week!

Basit

My articles published on SSWUG.org from March 2014 to July 2014

The following is the list of my articles published on SSWUG.org from March 2014 to July 2014. To read these articles, you must have SSWUG.org standard-level membership.

SSWUG.org logo


SQL Server 2014 Replication, Published on July 29, 2014

You use replication to move data between servers. Replication is important in distributed environments, when archiving data, and as an inexpensive way to support high availability.

This article explores the different types of replication, the components involved in replication, and the steps necessary to configure replication.


Microsoft SQL Server 2014: Database Engine key new features, Published on July 15, 2014

Earlier this year, Microsoft released SQL Server 2014. Like previous releases of SQL Server, Microsoft further enhances the SQL Server Database Engine. Microsoft not only improved existing SQL Server Database Engine features, but also introduces many new Database Engine features, including new in-memory OLTP engine (aka Hekaton), Buffer Pool Extension (BPE), Updateable Columnstore indexes, encryption for backups, Resource Governor enhancements for physical IO control, AlwaysOn enhancements, SQL Server Data Files in Windows Azure, Windows Azure Integrated Backups, new design for Cardinality Estimation, Incremental Statistics, Security enhancements, Transact-SQL enhancements, and so on. With the help of these new features of SQL Server 2014 Database Engine we can design, build and deploy mission-critical database applications.

This three part article series discusses in-detail the key new features of the Microsoft SQL Server 2014 Database Engine.


Developing Report Models, Published on July 8, 2014

A report model is a metadata description of a data source. It contains a business model of a data, a physical model of the underlying database, and a mapping between the two. The business model, also known as a semantic model, describes the data by using familiar business names, usually recorded in a business lexicon.

This article reviews the benefit of using report models.


Guidelines and Best Practices for developing and implementing a Reporting Solution, Published on June 6, 2014

Your reporting solution design should match technologies provided by Microsoft SQL Server™ 2014 Reporting Services to the requirements and abilities of users, and the requirements of the business. The documents that make up your reporting specification should also describe the purpose, data content, layout, and user interaction that are required for each report.

After you have an appropriate design in place, the next step is to develop the reports as effectively as possible by using appropriate tools that Microsoft SQL Server 2014 Data Tools (SSDT 2014) provides. SQL Server 2014 Data Tools (SSDT 2014) is a Microsoft Visual Studio environment with enhancements that are specific to business intelligence solutions. SQL Server 2014 Data Tools (also known as SSDT 2014) is included with Microsoft SQL Server™ 2014. You use SQL Server 2014 Data Tools for creating and managing solutions and projects for Reporting Services reports and report-related items. SQL Server 2014 Data Tools provides the Report Designer authoring environment. In Report Designer, you can open, modify, preview, save, and deploy report definitions, shared data sources, shared datasets, and report parts. For more information about SQL Server 2014 Data Tools solutions, projects, project templates, and configurations used for Reporting Services, and the views, menus, toolbars, and shortcuts that you can use in Report Designer, refer to SQL Server 2014 Books Online article “Reporting Services in SQL Server 2014 Data Tools (SSDT 2014)”.

This two part article series offers up some basic high-level guidelines, best practices and considerations for developing and implementing reporting solutions with SQL Server 2014 Data Tools (SSDT).


Handling blocks and deadlock in SQL Server, Published on May 15, 2014

A database server should be able to service requests from a large number of concurrent users. When a database server is servicing requests from many clients, there is a strong possibility that conflicts will occur because different processes request access to the same resources at the same time. A conflict in which one process is waiting for another to release a resource is called a block. Although in SQL Server a blocked process usually resolves itself when the first process releases the resource but there are times when a process holds a transaction lock and doesn’t release it.

This article offers up several options for troubleshooting and resolving blocks in SQL Server.


Understanding the purpose of SQL Server Agent, Published on April 21, 2014

The SQL Server Agent is a Windows service that runs scheduled jobs. A job is a set of one or more management tasks. The SQL Server Agent service depends on the SQL Server service.

This two part article series shows you how you can configure and manage SQL Server Agent service and jobs.


SQL Server Integration Service (SSIS): Import and Export wizards, Published on April 14, 2014

SQL Server includes SQL Server Integration Services (SSIS). SSIS is primarily a data transform and load utility, but its functionality goes beyond that. SSIS provides a flexible development environment that you can use to identify sources and destinations, processing actions, and decision logic. Several SQL Server data utilities, such as the Database Copy Wizard, are based on SSIS.

This article shows technique of importing and exporting data to and from SQL Server database using SQL Server Import and Export Wizard.


Diving deep into SQL Server Integration Services Transactions, Published on March 31, 2014

A transaction is a logical unit of work made up of one or more tasks. The concept of transaction usually applies to a relational database such as those in Microsoft SQL Server. However, in Integration Services, you can create a transaction that includes any task or container. In general, a transaction is considered to have four primary characteristics: atomicity, consistency, isolation, and durability (ACID).

This article provides an overview of transactions and there characteristics. It also shows how you can implement transactions in an SQL Server Integration Services packages.


I hope you will find these articles useful. :)

Have a good weekend!

Basit

Book: SQL Server 2014 Development Essentials

I am very happy to announce that my first book “SQL Server 2014 Development Essentials” has now been published by Packt Publishing.

SQL Server 2014 Development Essentials, ISBN: 9781782172550

SQL Server 2014 Development Essentials, ISBN: 9781782172550

SQL Server 2014 Development Essentials is an easy-to-follow yet comprehensive guide that is full of hands-on examples, which you can follow to successfully design, build, and deploy mission-critical database applications with SQL Server 2014. You can view and download the table of contents, sample chapter, code files and the preface of this title on Packt Publishing website here.

Who this book is for?

If you are a database developer, architect, or administrator who wants to learn how to design, implement, and deliver a successful database solution with SQL Server 2014, then this book is for you. This book will provide you with all the skills you need to successfully create, design, and deploy databases using SQL Server 2014. You will also learn how to add, modify, and delete data stored within a database. You will use Transact-SQL statements to create and manage advanced database objects that include scalar and table-valued functions, views, stored procedures, and triggers. Finally, you will learn about how SQL Server works, how indexes and statistics improve query performance, and the new SQL Server 2014 in-memory technologies.

From this book, you will learn:

  • Get introduced to SQL Server 2014’s new in-memory database engine
  • Understand SQL Server database architecture and relational database design
  • Use joins, subqueries, CTEs, and windowing functions to write advanced Transact-SQL queries
  • Learn about tools that let you monitor SQL Server database performance
  • Identify and troubleshoot blocks or deadlocks that might slow down a system
  • Design, create, and manage advanced database objects that include scalar and table-valued functions, views, stored procedures, and triggers
  • Use SQL Server 2014’s structured error handling blocks to handle errors that occur in the Transact-SQL batches and programmable objects

You can order it at Packt Publishing, Amazon UK, Amazon.com and Barnes & Noble. It will also be available at O’Reilly Media and Safari Books Online.

Please feel free to contact me via twitter (@BasitAali) or via email, if you have any questions or feedback about this book.