Skip to content

SQL Server

Optimize for Ad Hoc Workloads

The Optimize for Ad Hoc Workloads server configuration can improve performance, and is extremely unlikely to negatively impact performance.
This was a new feature that was introduced in SQL Server 2008, and as with many new features in SQL Server, it is not turned on by default.

What Optimize for Ad Hoc Workloads Changes.

Without the Optimize for Ad Hoc Workloads feature enabled, when a plan is compiled, it is kept around in memory (cached) until something else pushes it out of the cache.

With the Optimize for Ad Hoc Workloads feature enabled, the first time a plan is compiled, it is not kept around in cache, rather a stub is kept around to indicate that they query has previously been complied. The second time the plan is compiled it is kept around in memory. Over time you end up with fewer one time use queries in memory, reducing the memory impact of the plan cache. All other multi-use ad hoc plans will need to be compiled twice on the first and second time that they are used.

What it doesn’t to

The Optimize for Ad Hoc Workloads server configuration doesn’t change the size of the plan cache in SQL Server, it does however prevent the single use ad hoc plans from using up plan cache space, making more space for other cached plans.

Read More »Optimize for Ad Hoc Workloads

Transactions Rolled Back in Database

IF you are browsing your error log and come across an error message stating that “1 transactions rolled forward in database ‘msdb'”, or “X transactions rolled back in database ‘msdb'” you might be a bit alarmed. You might also notice similar error messages for master, tempdb or user databases.

msdb transactions rolled back in database

 

How can this be a good thing? Why are transactions being rolled back or rolled forward?

Read More »Transactions Rolled Back in Database

SQL Server Mentoring – Now Available

Yesterday I was asked by a client about consulting questions and for my help without the typical minimum hourly commitment. He pointed out that he has things that he could use help with that don’t require a multi-hour type project. After thinking it over a bit, I decided to add a SQL Server coaching program to my consulting business, I am calling it my SQL Server Mentoring program.

SQL Server Mentoring Process

Here is how it works if you want to utilize my mentoring services.

Step 1:  Set up a mentoring agreement. You can start this with the free 30 minute consultation form to schedule a time to talk to better understand the service, and so that I can better understand your needs and environment.

Read More »SQL Server Mentoring – Now Available

TSQL – Change Data Capture

You have a need to keep track of all insert, update and delete actions on a table, or multiple tables. As you consider solutions, you might think about using a trigger, however triggers have their own baggage. You consider using the OUTPUT clause to log to a changes table, but then realize that the output clause cant be enforced.

Then the SQL Server feature called Change Data Capture comes into play. CDC is a SQL Server Feature that monitors the transaction log, looking for changes to specific tables, when the changes are discovered, they are then written into a Change Table that can then be queried to find out what was changed and when it was changed.

Sample Code

Lets take a look, to start with, I create a database called [DemoCDC] to use for the demo. That database contains a single table called [History] that you may recognize from the Week 3 Database Corruption Challenge. Following that are a few insert statements to just start with some data in the table.

CREATE DATABASE [DemoCDC];
GO

USE [DemoCDC]
GO

CREATE TABLE [dbo].[People]
(
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] NVARCHAR(200) NULL,
	[dateAdded] DATETIME DEFAULT GETDATE()
);

INSERT INTO [dbo].[People] ([name])
VALUES ('Simba'), ('Annette'), ('Bobby'),
       ('Fred'), ('Mary'), ('Beth');

Read More »TSQL – Change Data Capture