Optimize For Ad Hoc Workloads – Observations...... I've been watching this one particular server that has been throwing alerts for high physical memory usage from Red Gate's SQL Monitoring tool.I was discussing this with a friend of mine, Clayton Hoyt and he mentioned that I might want to look at the "Optimize for Ad Hoc Workloads" advanced server configuration options.I took him up on his advice and googled for the "

Someone tried to figure out why his data was showing the next day when he passed in today’s date. If you are not careful to use the same data type and this includes scale and precision as well, you can get some strange results. In this post I will take a look at date, integer, varchar and decimal data types Dates When using dates make sure that you are using the same data type, don’t mix datetime and smalldatetime.

Merge Replication is a powerful beastly thing that can bring great joy and great sorrow all in the same 15 minute time span. There is no doubt that Merge Replication is an Enterprise feature. It has grown over the years from the days when all it took was a swift kick to the side of a server rack to knock an agent into starting, to a fancy event handler that shows us the bulk operations as they fly by our network and into the subscribers.

This week at SQL University we’re talking about Performance Tuning. Performance tuning SQL Server means just about anything that increases the availability of data to the client requesting it. This could be anything from an index to a team configuration on your NICs. During this week’s class, I’m talking about something that is normally not brought up in tuning discussions: Merge Replication and how data partitions and applying snapshots can be optimized.

It’s that time of the year again: quarterly goal review. My 2011 Goals were set very high. I had an amazing 2010, and want this year to be even better. How have I been doing? I’ve been busy. I decided to tackle several major projects at the same time. I’m happy to say most of them succeeded, and I had a great time and learned a lot. However, I need to slow down a little bit so I don’t burn myself out.

SQL Server Management Studio will sometimes show a lock next to a stored procedure, there are several reasons why this might happen. The procedure is encrypted The procedure is a CLR stored procedure The user doesn’t have the view definition permission for a stored proc Let’s take a look at how this all works. I will first create this database with two stored procedures, one is encrypted, the other one is not

One of my new friends that I was fortunate to make while attending the MVP Summit, Dan English (Twitter | Blog | MVP), mentioned last night something that hit home pretty hard and fast. I was writing an SSIS package to go along with one of my SQL University posts and posted an image of my first initial execution of the package. See, all of the boxes were green on that first try and it’s always a smile when you get all green boxes on your first run.

We’ve been conditioned to accept the terms “Programmer, “Software Developer”, and “Software Engineer” as synonymous. Want ads and internal job titles tell us so. When someone gets paid to fill a human-sized hole, they’re going to use the most marketable term that can be attached to a three sentence description, so these terms are used almost interchangeably. We can’t trust that a company hiring for a “Developer” actually needs the skills that make a “Developer” different from a “Programmer”.

Merge Replication: Snapshot Performance with Data Partitions This week is SQL University Performance week. Grant Fritchey (Blog | Twitter) and myself are on point (get it?). There are many things we could talk about when it comes to improving performance for SQL Server. As most of the SQL world knows, Grant is one part human and three parts Optimizer. There are only a few people I know of that have the knowledge of everything that goes into the life cycle of a transaction.

Take a look at this piece of junk code, what pops up in your head when you look at this? CREATE FUNCTION [dbo].[age](@set varchar(10)) RETURNS TABLE AS BEGIN IF (@set = 'tall') SELECT * from player where height > 180 ELSE IF (@set = 'average') SELECT * from player where height >= 155 and height <=175 ELSE IF (@set = 'low') SELECT * from player where height < 155 END If you are thinking silly you, you can’t have an IF statement like that in a function, then you have disappointed me.