Central Management Server and server groups provide a convenient way to manage multiple servers and databases in one place and at the same time.You can register and manage any servers but only those in SQL Server versions 2008 and higher can be designated as Central Management Server. You can execute TSQL scripts on any or all the servers registered under the Central Management Server at the same time. If you have a Policy-Based Management structure in place, the Central Management Server makes it easy to execute policies on multiple servers at once. I’ve said that enough – at the same time. Click once, and you’re done with all the servers.

But what if, for some reasons, you cannot use Central Management in your environment?

This is the reason why tools like SQL Multi Script from Red Gate Software exist. Some of the features I like are:

• Set the execution order of multiple scripts
• Query databases in parallel
• Messages from SQL Server displayed for all databases
• Execute just the selected text in a script (just like in SSMS)

Let’s take a look. For this demo, we’re using two SQL Server 2012 instances from two separate servers on a domain. This demonstrates that we can use the SQL Multi Script tool on multiple servers across a network.

The first time you run the tool, you need to build your Database Distribution List:

1. Click the Configure button 2. On the Configure Database Distribution List Dialog, click the New button. Type in a name for the new distribution list, then click Create. Let’s put SQL2012_Dev_Servers for this demo. This list is for databases, but I’d still like that “servers” in the nomenclature to have that logical grouping by servers. Using distribution lists is a good way of having that separation among server groups; e.g., Test, Dev, QA, Production, etc. So name your lists as intuitive as possible. 3. Still on the Configure Database Distribution List Dialog, click the Add a SQL Server Not Listed button. Type in the SQL Server whose databases you want to add to the distribution list, specify your authentication credentials, then click Add. Remember the distribution list is for databases, not for servers. Repeat this step for all the servers you want to add. 4. Now that we have added the servers, let’s add the databases we want to list in our SQL2012_Dev_Servers distribution list. Select the databases you want to add in the distribution list. You can multi-select databases among the servers by pressing CTRL + Right Click key. Then, click the Add button. 5. You can now see the databases in the Databases to Execute Against pane under the Distribution list. Click OK. 6. You can create a new script via the SQL Multi Script editor; and, save that script for later use. You can also add an existing script. For this demo, let’s do the latter. Click the Add button. Select the script you want to add from the Add File Dialog box. I’m adding two scripts for this demo: a script that creates a database called DBTools and sp_Blitz by Brent Ozar Unlimited. I don’t intend to violate the sp_Blitz trademark here. I just want to demo that an enterprise-ready script such as sp_Blitz can execute in SQL Script.

 7. Like I mentioned in the beginning of this post, one of the features that I really like in SQL Multi Script is the ability to set the execution order of multiple scripts. The first script, Create_Database_DBTools.sql, creates a database called DBTools. The second script, the sp_Blitz (Brent Ozar Unlimited trademark), creates the sp_blitz stored procedure on the DBTools. Let’s order the two scripts so that the Create_Database_DBTools runs first before the sp_blitz script; otherwise, it will throw a missing object error. I modified the sp_blitz to run on the DBTools database instead of the default database (master).

Let’s parse the script to make sure our scripts are error-free. In this demo, I’m getting a “DBTools database does not exist” error, which makes sense, because the DBTools database has not been created at this point.

One of the features of SQL Multi Script is its ability to display Messages from SQL Server, which exactly is what we see here:

8. If you’re executing long and complicated scripts, encountering errors is a possibility. SQL Multi Script provides a list of actions to be taken when an error occur; i.e., Continue Executing, Skip script on database with error, Stop executing on database with error, and Stop executing.

9. Let’s execute our scripts. We should get a confirmation that the scripts ran successfully. The tools did not encounter an error this time because we executed the two scripts in the correct order.

10. Let’s check our databases through SSMS, and make sure that the objects we just created through SQL Multi Scripts exist. The following screen captures show that the DBTools database and sp_blitz stored procedure objects are indeed created on both the SQL2012-A and SQL2012-B servers.

11. But here’s more. Here’s another feature I like in the tool – it’s ability to display the results of the script. Let’s execute the sp_Blitz via the SQL Multi Script tool. But before we do that, let’s add the DBTools from both SQL2012-A and SQL2012-B to our SQL2012_Dev_Servers distribution list. This time, instead of adding an existing script, let’s create a new script in the editor:

EXEC [DBTools].[dbo].[sp_Blitz]
@CheckUserDatabaseObjects = 1 ,
@CheckProcedureCache = 0 ,
@OutputType = 'TABLE' ,
@OutputProcedureCache = 0 ,
@CheckProcedureCacheFilter = NULL,
@CheckServerInfo = 1

Let’s execute that TSQL. SQL Multi Script should be able to show us the results of the sp_Blitz stored procedure, like this:

The SQL Multi Script is a neat tool. I recommend it if you’re looking for something like this.

The post Executing scripts on multiple servers the easy way appeared first on SQL, Code, Coffee, etc..

Recently we worked with a customer who reported a query that used to run a few seconds in SQL Server 2000 but it never finishes in SQL Server 2008 R2 following upgrade.

We went around and tried quite a few things but couldn't get SQL Server 2008 R2 to generate similar plan. Upon closer look at 2008 R2's query plan, we noticed something unusual. The plan has a warning "NO JOIN PREDICATE". What this means is that a cartesian product is introduced.

To illustrate the problem, let's use an example setup:

drop function dbo.myfunc
go
drop view v1, v2
go
drop table t1, t2
go
create table t1 (c1 int not null, c2 varchar(100))
go
create table t2 (c1 int not null, c2 varchar(100))
go
set nocount on
go
declare @i int
begin tran
select @i = 0
while (@i < 1000)
begin
insert into t1 (c1, c2) values (@i, 'a')
insert into t2 (c1, c2) values (@i, 'b')
select @i = @i + 1
end
commit tran
go
drop function dbo.myFunc
go
create function dbo.myfunc (@c1 int)
returns int
--with schemabinding
as
begin
return (@c1 * 100 )
end
go
create view v1 as select c1, c2, dbo.myfunc(c1) as c3 from t1
go
create view v2 as select c1, c2, dbo.myfunc(c1) as c3 from t2
go

Now, let's run the following query

dbcc freeproccache
go
set statistics profile on
go

-- But by pulling UDF above join in this query we actually introduce a cartesian product (NO JOIN PREDICATE)
-- UDF is called 1 million times instead of 1000 times each for the two views!
select count(*) from v1 as t1 join v2 as t2 on t1.c3 = t2.c3
go
set statistics profile off
go

The above query is very slow as illustrated in the query plan below. In the line 6 for the query plan, there is a warning "no join predicate". The join resulted in 1,000,000 rows (1,000 x 1,000 rows from each table).

In line 5, the myfunc is called 2,000,000 times (1,000,000 for computing t1.c1 and 1,000,000 for t2.c1).

This is because starting SQL Server 2005, optimizer has rule changes that will disallow non-deterministic scalar functions to be 'pushed down' in some situations (like this one).

Solution

Many times, you can simply make a function deterministic by adding schemabinding option. In the above example, re-write the function with schemabinding, it will be much faster.

From the query plan, you will no longer see that the "NO JOIN PREDICATE". The scalare UDF is pushed down right after table scan and applied only 100 times on each table.

drop function dbo.myFunc
go
create function dbo.myfunc (@c1 int)
returns int
with schemabinding
as
begin
return (@c1 * 100 )
end

Obviously, the function can be made deterministic. If you use following, the function will not be deterministic even you use schemabidning because of getdate(). In such cases, you will continue to see "NO JOIN PREDICATE" Cartesian product joins.

drop function dbo.myFunc
go
create function dbo.myfunc (@c1 int)
returns int
with schemabinding
as
begin
return (@c1 * 100 * datepart (mm,getdate()))
end

Jack Li | Senior Escalation Engineer | Microsoft SQL Server Support

BACKUP TO URL was introduced as an add-on in Cumulative Update 2 for SQL Server 2012 Service Pack 1 and as a built-in feature for SQL Server 2014. I previously described this in a blog post.

We have been using this in a variety of ways from on-premises systems:

For example, it is an easy way to distribute a backup of a database to a large number of systems. Imagine you have a chain of retail stores that needs product and other reference information updated regularly. You can keep this data in a separate database at the head office, back it up to an Azure Storage account, and have each store download it separately.  This has major bandwidth and reliability improvements over other solutions such as having each store maintain a VPN connection to the head office.

As another example, we have clients who simply aren’t able to gain enough space on their SANs to keep enough local copies of their backups.

The more common scenario though is to use it for backups from Azure Virtual Machines that are running SQL Server. Rather than performing a backup to a virtual machine virtual disk, we achieve better performance by bypassing the file-system on the virtual disk and backing up directly to a URL. Both end up in Azure storage but backing up directly to storage gives us both better performance, and can help to avoid the drive limit and size restrictions for virtual machines.

Regardless of why you are using BACKUP TO URL, one of the problems that you are likely to run into at some point is the dreaded:

                Msg 3271, Level 16, State 1, Line 60
                A nonrecoverable I/O error occurred on file
                “https://somestorageaccount.blob.core.windows.net/backups/somedatabase.bak: “ Backup
                to URL received an exception from the remote endpoint. Exception Message: The remote
                server returned an error: (412) There is currently a lease on the blob and no lease ID was
                specified in the request...
                Msg 3013, Level 16, State 1, Line 60
                BACKUP DATABASE is terminating abnormally.

Applications using Azure storage can take leases on files that are held in the storage containers. This avoids issues with other applications concurrently changing (or even deleting) files that the application needs. The BACKUP TO URL feature in SQL Server takes an infinite lease on the backup file that it creates. That lease is removed when the backup completes. (The process of dealing with leases in Azure Storage is described here).

However, if you interrupt a backup (or network issues interrupt it for you), and this is a prolonged interruption, the lease can remain and when you try to overwrite that backup blob (or even delete it), you’ll see the error above.

Now, the design feature that makes this a bit easier to deal with is that the BACKUP TO URL command always uses a well-known lease ID: BAC2BAC2BAC2BAC2BAC2BAC2BAC2BAC2

What is needed to delete it then, is a tool that can break leases, or to run a PowerShell  script as described in this article. You should also take this as yet another hint to learn about PowerShell if you haven’t done so already.

Details about other aspects of BACKUP TO URL troubleshooting are given in this article.

One of the projects I have been working on as time permits is to construct a way to create a shared access signature using native T-SQL. Shared access signatures are needed when working with SQL Server data files in Azure.

Mostly, DBAs would use a programmatic utility such as Azure Storage Explorer to generate these signatures.

It is also straightforward to do so with SQL CLR based code but this is not enabled in all environments.

So I set about trying to create the required signatures using native T-SQL. I’m not concerned about the slow cryptographic performance as this won’t be a regular operation.

I found some excellent code from Ryan Malayter on github: https://gist.github.com/rmalayter/3130462 that does a SHA –1 or SHA – 512 hash. I’ve modified and enhanced it a bit so that it does SHA2-256 and performs the functions I need.

After the function code, there is a set of test code that shows how the function satisfies the HMAC_SHA256 test vectors. Hope that helps someone. I’ll post more as I complete the next step in building a SAS generator.

USE tempdb;

GO

ALTER FUNCTION dbo.HMAC_SHA256

(

    @HashKey varbinary(8000),

    @ValueToHash varbinary(8000)

)

RETURNS binary(32)

AS

BEGIN

    -- Based on concept code from Ryan Malayter: https://gist.github.com/rmalayter/3130462

    DECLARE @k_ipad_partial bigint = CAST(0x3636363636363636 AS bigint);

    DECLARE @k_opad_partial bigint = CAST(0x5C5C5C5C5C5C5C5C AS bigint);

    IF LEN(@HashKey) > 64

    BEGIN

        SET @HashKey = CAST(HASHBYTES('SHA2_256', @HashKey) AS binary (64));

    END ELSE BEGIN

        SET @HashKey = CAST(@HashKey AS binary (64));

    END;

    DECLARE @k_ipad binary(64)

      = CAST((SUBSTRING(@HashKey, 1, 8) ^ @k_ipad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 9, 8) ^ @k_ipad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 17, 8) ^ @k_ipad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 25, 8) ^ @k_ipad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 33, 8) ^ @k_ipad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 41, 8) ^ @k_ipad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 49, 8) ^ @k_ipad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 57, 8) ^ @k_ipad_partial) AS binary(8));

    DECLARE @k_opad binary(64)

      = CAST((SUBSTRING(@HashKey, 1, 8) ^ @k_opad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 9, 8) ^ @k_opad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 17, 8) ^ @k_opad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 25, 8) ^ @k_opad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 33, 8) ^ @k_opad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 41, 8) ^ @k_opad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 49, 8) ^ @k_opad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 57, 8) ^ @k_opad_partial) AS binary(8));

    RETURN HASHBYTES('SHA2_256', @k_opad + HASHBYTES('SHA2_256', @k_ipad + @ValueToHash));

END;

GO

-- Test with RFC4231 test vectors

DECLARE @KeyToUse varbinary(4000);

DECLARE @ValueToHash varbinary(4000);

DECLARE @HashedValue varbinary(32);

DECLARE @ExpectedResult varbinary(32);

-- Test 1

SET @KeyToUse = 0x0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b;

SET @ValueToHash = CAST('Hi There' AS varbinary(1000));

SET @ExpectedResult = 0xb0344c61d8db38535ca8afceaf0bf12b881dc200c9833da726e9376c2e32cff7;

SET @HashedValue = dbo.HMAC_SHA256(@KeyToUse, @ValueToHash);

IF @HashedValue = @ExpectedResult

BEGIN

    PRINT '>>Test 1 passed';

END ELSE BEGIN

    PRINT '>>Test 1 failed';

    PRINT 'Expected 0x' + CONVERT(varchar(1000), @ExpectedResult, 2);

    PRINT 'Received ' + COALESCE('0x' + CONVERT(varchar(1000), @HashedValue, 2),'NULL');

    PRINT ' ';

END;

-- Test 2

SET @KeyToUse = CAST('Jefe' AS varbinary(4));

SET @ValueToHash = CAST('what do ya want for nothing?' AS varbinary(1000));

SET @ExpectedResult = 0x5bdcc146bf60754e6a042426089575c75a003f089d2739839dec58b964ec3843;

SET @HashedValue = dbo.HMAC_SHA256(@KeyToUse, @ValueToHash);

IF @HashedValue = @ExpectedResult

BEGIN

    PRINT '>>Test 2 passed';

END ELSE BEGIN

    PRINT '>>Test 2 failed';

    PRINT 'Expected 0x' + CONVERT(varchar(1000), @ExpectedResult, 2);

    PRINT 'Received ' + COALESCE('0x' + CONVERT(varchar(1000), @HashedValue, 2),'NULL');

    PRINT ' ';

END;

-- Test 3

SET @KeyToUse = 0xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

SET @ValueToHash = 0xdddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd;

SET @ExpectedResult = 0x773ea91e36800e46854db8ebd09181a72959098b3ef8c122d9635514ced565fe;

SET @HashedValue = dbo.HMAC_SHA256(@KeyToUse, @ValueToHash);

IF @HashedValue = @ExpectedResult

BEGIN

    PRINT '>>Test 3 passed';

END ELSE BEGIN

    PRINT '>>Test 3 failed';

    PRINT 'Expected 0x' + CONVERT(varchar(1000), @ExpectedResult, 2);

    PRINT 'Received ' + COALESCE('0x' + CONVERT(varchar(1000), @HashedValue, 2),'NULL');

    PRINT ' ';

END;

-- Test 4

SET @KeyToUse = 0x0102030405060708090a0b0c0d0e0f10111213141516171819;

SET @ValueToHash = 0xcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcd;

SET @ExpectedResult = 0x82558a389a443c0ea4cc819899f2083a85f0faa3e578f8077a2e3ff46729665b;

SET @HashedValue = dbo.HMAC_SHA256(@KeyToUse, @ValueToHash);

IF @HashedValue = @ExpectedResult

BEGIN

    PRINT '>>Test 4 passed';

END ELSE BEGIN

    PRINT '>>Test 4 failed';

    PRINT 'Expected 0x' + CONVERT(varchar(1000), @ExpectedResult, 2);

    PRINT 'Received ' + COALESCE('0x' + CONVERT(varchar(1000), @HashedValue, 2),'NULL');

    PRINT ' ';

END;

-- Test 5

SET @KeyToUse = 0x0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c;

SET @ValueToHash = CAST('Test With Truncation' AS varbinary(4000));

SET @ExpectedResult = 0xa3b6167473100ee06e0c796c2955552b;

SET @HashedValue = CONVERT(varbinary(16),dbo.HMAC_SHA256(@KeyToUse, @ValueToHash));

IF @HashedValue = @ExpectedResult

BEGIN

    PRINT '>>Test 5 passed';

END ELSE BEGIN

    PRINT '>>Test 5 failed';

    PRINT 'Expected 0x' + CONVERT(varchar(1000), @ExpectedResult, 2);

    PRINT 'Received ' + COALESCE('0x' + CONVERT(varchar(1000), @HashedValue, 2),'NULL');

    PRINT ' ';

END;

-- Test 6

SET @KeyToUse = 0xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

SET @ValueToHash = CAST('Test Using Larger Than Block-Size Key - Hash Key First' AS varbinary(4000));

SET @ExpectedResult = 0x60e431591ee0b67f0d8a26aacbf5b77f8e0bc6213728c5140546040f0ee37f54;

SET @HashedValue = dbo.HMAC_SHA256(@KeyToUse, @ValueToHash);

IF @HashedValue = @ExpectedResult

BEGIN

    PRINT '>>Test 6 passed';

END ELSE BEGIN

    PRINT '>>Test 6 failed';

    PRINT 'Expected 0x' + CONVERT(varchar(1000), @ExpectedResult, 2);

    PRINT 'Received ' + COALESCE('0x' + CONVERT(varchar(1000), @HashedValue, 2),'NULL');

    PRINT ' ';

END;

-- Test 7

SET @KeyToUse = 0xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa...

Last week ended up better than expected when PASS decided to release feedback to speakers (selected or not) who requested it. I imagine many did and the feedback on the feedback was surprisingly good – and that is good. That’s a good step and I hope it will be become standard to deliver that feedback to anyone who submits a session. The next step is to provide the same level of feedback on the speaker evaluation. Right now we don’t know what matters or doesn’t, and there is no reason for that. If people can work hard and improve their standing, that’s never going to be bad for PASS except in one way – it will make the final choice of the sessions for the schedule even harder!

If you haven’t already, I encourage you to read How Conference Organizers Pick Sessions by Brent Ozar, a great write up. Building a schedule worthy of a Summit size/cost event is non-trivial.

I also wonder if there hasn’t been an interesting dynamic in past years where those that (arguably) wanted/needed feedback the most were the ones not selected, and those are the people (like me, last year) who are apt to not make much of a fuss.

I hope the program review committee will include some outside voices, and that it will start soon, and that it will do transparently. We need to see the discussion and the challenges.

Changing focus to growing speakers, Chapters and SQLSaturday (and maybe 24HOP) are the farm club that feeds the Summit, and I think do so in grand fashion, but neither have anything close to the demands/process that go into the Summit selection. We (me) had hoped that SQLRally would be the piece that plugged that gap. With the abandoning of SQLRally in the US I haven’t seen much in the way of thinking about whether the problem remains or a way to fix it. I think it does, and I still think “regional” events are the key, something that is more selective than our Chapter/SQLSaturday events, but not as selective as the Summit. Or maybe exactly as selective as the Summit, but with the same idea we had for SQLRally, largely exclude any previous year Summit speaker so that “new” speakers can get a chance to grow.

I’d like to see regional events, but I don’t know that PASS (the Board) has the passion for them, so how else can we do it? Trained evaluators are one way, and we’ve seen the beginnings of that with well known people in the community offering to review abstracts. We could charter a program to train people to do abstract and presentation evaluations in a detailed way (somewhere back in the archives I have a 60 point eval sheet I suggested at one point) and as many (if not all) would be speakers, they could do one here and there as they attend events if someone has requested (and is ready for) an in-person evaluation. Maybe there is an opportunity to do some regional classes where experienced speakers attend for a day or two for instruction and then evaluation by the instructor – I’d have PASS pay the instructor and the overhead, make it free for qualified attendees. That’s something we could easily trial at the Summit. I think how great it would be to have the discussion about what we’d teach. Maybe it can be done online. Maybe there are other ways?

We’ve addressed the quantity problem of speakers, though I think we have to continue our efforts to find more people to step to the front of the room. What we need now is a deeper focus on quality. That would not just lift the Summit, but all the events that tree up to it.

I was just thinking that we hold meetings each year at the Summit for Chapter Leaders as a place to hear and share ideas, and we do the same for SQLSaturday leaders. Why don’t we have something for the speakers? Is it a different dynamic, or a so far missed opportunity? In the early days of SQLSaturday we knew that to grow we had to find that one person in each city that would take on the huge task of putting an event together, but we also saw that the entire success of the eco-system was based on speakers. That’s something I’m reminded of every time speakers sign up for SQLSaturday Orlando and fly in from some other city to attend. I wonder if that lesson hasn’t been lost a bit at the Board level, and I’d suggest that maybe it’s time to have a portfolio that focuses on growth of speakers.

I’m not arguing for doing more for the sake of doing more. There are ways here we can serve our members, directly and indirectly, just be putting some more effort into the growth and education of our speakers.

Microsoft SQL Server 2014 brings new performance and scalability gains by introducing In-Memory OLTP.  In-Memory OLTP contains tables and indexes optimized for in memory. Transactions execute under lock-free algorithms to provide linear scalability and Transact-SQL stored procedures can be compiled in native machine code for maximum efficiency in processing.

Working with SQL Server customers on In-Memory OLTP engagements, a common pattern emerged around the desire for increased performance and scalability when using ASP.NET session state. Some early adopters modified their SQL Server objects to take advantage of In-Memory OLTP for ASP.NET session state, with great success. To learn more, read the bwin.party case study “Gaming site can scale to 250,000 requests per second and improve player experience”. To further enhance this scenario, we have created a new provider to make it easier for customers to take advantage of SQL Server In-Memory OLTP when using ASP.NET session state.

This ASP.NET session state provider is fully optimized for In-Memory OLTP by calling natively compiled Transact-SQL stored procedures and by creating all tables as memory-optimized. The functionality of the provider was tested both internally and by external customers. The results showed the implementation was able to provide some significant gains at scale levels which would have previously exhibited a bottleneck on the database.

NOTE: While some testing has been done before the release, we recommend executing your own testing and validation to understand how this implementation behaves in your specific environment.

Getting Started

Setting up the provider requires two steps, installing the provider into the ASP.NET application and creating the In-Memory OLTP database and objects in Microsoft SQL Server 2014.

The provider and scripts can be accessed in two ways:

1. The package has been uploaded to NuGet: https://www.nuget.org/packages/Microsoft.Web.SessionState.SqlInMemory/

2. The source code is also accessible through CodePlex: https://msftdbprodsamples.codeplex.com/releases/view/125282

NuGet Installation

Download the ASP.NET Session State Provider for SQL Server In-Memory OLTP from the NuGet gallery by running the following command from the Visual Studio Package Manager Console:

PM> Install-Package Microsoft.Web.SessionState.SqlInMemory

More information about the NuGet package can be found here:

https://www.nuget.org/packages/Microsoft.Web.SessionState.SqlInMemory/

Installing the package will do the following things:

• Add references to the ASP.NET Session State Provider assembly.
• Add to the web.config file a customProvider equals to "SqlInMemoryProvider", where the connectionString attribute needs to be updated.
  

  <?xml version="1.0" encoding="utf-8"?>
   <configuration>
     <system.web>
       <sessionState mode="Custom" customProvider="SqlInMemoryProvider">
         <providers>
           <add name="SqlInMemoryProvider"
                type="Microsoft.Web.SessionState.SqlInMemoryProvider"
                connectionString="data source=sqlserver;initial catalog=ASPStateInMemory;User ID=user;Password=password;" />
         </providers>
       </sessionState>
     </system.web>
   </configuration>
  

• Adds an ASPStateInMemory.sql file that includes the script for creating the SQL Server database configured to support In-Memory OLTP.

Setting up In-Memory OLTP Database and objects

Open the T-SQL script file "ASPStateInMemory.sql" and update the 'CREATE DATABASE' statement to replace the 'FILENAME' attributes to specify a path that will exist in your SQL Server machine where the memory-optimized filegroup should exist. For further considerations on placement of this filegroup see Books Online section Creating and Managing Storage for Memory-Optimized Objects

CREATE DATABASE [ASPStateInMemory]
ON PRIMARY (
  NAME = ASPStateInMemory, FILENAME = 'D:\SQL\data\ASPStateInMemory_data.mdf'
),
FILEGROUP ASPStateInMemory_xtp_fg CONTAINS MEMORY_OPTIMIZED_DATA (
  NAME = ASPStateInMemory_xtp, FILENAME = 'D:\SQL\data\ASPStateInMemory_xtp'
)
GO

After updating the 'FILENAME' attributes, run the entire script for creating the In-Memory tables and the natively compiled stored procedures.

Additionally, create a periodic task in SQL Server to run the stored procedure 'dbo.DeleteExpiredSessions'. This procedure removes the expired sessions and frees up the memory consumed.

NOTE: The memory-optimized tables are created with a durability of SCHEMA_ONLY to optimize for performance. If session data durability is required, then change the 'DURABILITY' attribute from 'SCHEMA_ONLY' to 'SCHEMA_AND_DATA'. More information can be found in Books Online sections Defining Durability for Memory-Optimized Objects and Durability for Memory-Optimized Tables.

Conclusion

SQL Server In-Memory OLTP has shown to greatly improve the performance of ASP.NET session state applications. This provider allows customers to optimize ASP.NET web farms to take advantage of SQL Server In-Memory OLTP using a packaged solution with ease.

For further considerations on session state with In-Memory OLTP, along with other solution patterns which have shown success with SQL Server In-Memory OLTP, please reference the whitepaper: In-Memory OLTP – Common Workload Patterns and Migration Considerations.  

Download the Microsoft SQL Server 2014 Evaluation and see how in-memory processing built into SQL Server 2014 delivers breakthrough performance.

I am excited to announce the Microsoft Azure StorSimple solution. Available August 1, this new offering builds on the success of previous StorSimple offerings that automate and eliminate one of the biggest problems facing IT organizations – double digit data growth and the storage capacity and data protection complexities that come with it. Customers around the world such as Mazda, SK Telecom, Paul Smith, Sundance Film Festival, GF Health Products, Black and Veatch, and the City of San Jose, California have all simplified their storage infrastructures with StorSimple hybrid cloud storage solutions.  

The new StorSimple 8000 series hybrid storage arrays are the most powerful StorSimple systems ever and have even tighter integration with Azure, including two new Azure-based capabilities to enable new use cases and centralize data management. These new solutions demonstrate how Microsoft is bringing the best of on-premises storage together with the cloud in order to deliver bottom line savings to customers by cutting storage costs from 40 to 60% and helping IT teams focus more on business strategies than infrastructure management.

The new StorSimple 8000 series arrays come in two flavors to meet a variety of capacity and performance needs:  the StorSimple 8100 and the StorSimple 8600, which you can read about here.  These are enterprise hybrid storage arrays with a twist - instead of being limited to only SSDs and HDDs, these arrays use Azure Storage as a hybrid cloud tier for automatic capacity expansion and off-site data protection. That means IT teams don’t have to spend so much time and effort working on the next inevitable storage capacity upgrade or managing the complex details of data protection. Data stored on StorSimple 8000 series arrays is automatically protected off-site by cloud snapshots, which fill the enormous gap between problematic tape solutions and costly remote replication solutions. 

To go with the new arrays, there is the Microsoft Azure StorSimple Virtual Appliance, which is an implementation of StorSimple technology running as an Azure virtual machine in the cloud. With a matching Azure StorSimple virtual machine, StorSimple 8000 series customers can run applications in Azure that access snapshot virtual volumes in the cloud. Customers will be able to run new applications that search and analyze historical datasets without disrupting production work in their datacenter. This new StorSimple Virtual Appliance not only works for data from Windows Server and Hyper-V, but on-premises Linux and VMware servers, as well, providing hybrid cloud capabilities for the most common server platforms today. 

The Virtual Appliance also enables disaster recovery (DR) in the cloud. Virtualized applications that store their data on an Azure StorSimple array in a customer’s datacenter can be restarted in VMs in Azure with access to previously uploaded data. Updates to data made during recovery operations can be downloaded later to StorSimple arrays on-premises when normal operations resume.

DR is an area of concern for many customers and they seldom get a chance to test their abilities. Microsoft Azure StorSimple 8000 Series arrays and Virtual Appliances have a feature called Instant Reco

Back in April I wrote about some native methods within SQL Server that can be used to track automatic updates to statistics. The three options I provided were SQL Trace, Extended Events, and snapshots of sys.dm_db_stats_properties. While these three options remain viable (even in SQL Server 2014, though my top recommendation is still XE), an additional option I noticed when running some tests recently is SQL Sentry Plan Explorer.

Many of you use Plan Explorer simply for reading executing plans, which is great. It has numerous benefits over Management Studio when it comes to reviewing plans – from the little things, like being able to sort on top operators and easily see cardinality estimate issues, to bigger benefits, like handling complex and large plans and being able to select one statement within a batch for easier plan review. But behind the visuals that make it easier to dissect plans, Plan Explorer also offers the ability to execute a query and view the actual plan (rather than running it in Management Studio and saving it off). And on top of that, when you run the plan from PE, there is additional information captured that can be useful.

Let's start with the demo that I used in my recent post, How Automatic Updates to Statistics Can Affect Query Performance. I started with the AdventureWorks2012 database, and I created a copy of the SalesOrderHeader table with over 200 million rows. The table has a clustered index on SalesOrderID, and a nonclustered index on CustomerID, OrderDate, SubTotal. [Again: if you are going to do repeated tests, take a backup of this database at this point to save yourself some time.] I first verified the current number of rows in the table, and the number of rows that would need to change to invoke an automatic update:

SELECT
OBJECT_NAME([p].[object_id]) [TableName],
[si].[name] [IndexName],
[au].[type_desc] [Type],
[p].[rows] [RowCount],
([p].[rows]*.20) + 500 [UpdateThreshold],
[au].total_pages [PageCount],
(([au].[total_pages]*8)/1024)/1024 [TotalGB]
FROM [sys].[partitions] [p]
JOIN [sys].[allocation_units] [au] ON [p].[partition_id] = [au].[container_id]
JOIN [sys].[indexes] [si] on [p].[object_id] = [si].object_id and [p].[index_id] = [si].[index_id]
WHERE [p].[object_id] = OBJECT_ID(N'Sales.Big_SalesOrderHeader');

Big_SalesOrderHeader CIX and NCI Information

I also verified the current statistics header for the index:

DBCC SHOW_STATISTICS ('Sales.Big_SalesOrderHeader',[IX_Big_SalesOrderHeader_CustomerID_OrderDate_SubTotal]);

NCI Statistics: At Start

The stored procedure that I use for testing was already created, but for completeness the code is listed below:

CREATE PROCEDURE Sales.usp_GetCustomerStats
@CustomerID INT,
@StartDate DATETIME,
@EndDate DATETIME
AS
BEGIN
  SET NOCOUNT ON;
 
  SELECT CustomerID, DATEPART(YEAR, OrderDate), DATEPART(MONTH, OrderDate), COUNT([SalesOrderID]) as Computed
    FROM [Sales].[Big_SalesOrderHeader]
    WHERE CustomerID = @CustomerID
    AND OrderDate BETWEEN @StartDate and @EndDate
    GROUP BY CustomerID, DATEPART(YEAR, OrderDate), DATEPART(MONTH, OrderDate)
    ORDER BY DATEPART(YEAR, OrderDate), DATEPART(MONTH, OrderDate);
END

Previously, I either started a Trace or Extended Events session, or set up my method to snapshot sys.dm_db_stats_properties to a table. For this example, I just ran the above stored procedure a few times:

EXEC Sales.usp_GetCustomerStats 11331, '2012-08-01 00:00:00.000', '2012-08-31 23:59:59.997'
GO
EXEC Sales.usp_GetCustomerStats 11330, '2013-01-01 00:00:00.000', '2013-01-31 23:59:59.997'
GO
EXEC Sales.usp_GetCustomerStats 11506, '2012-11-01 00:00:00.000', '2012-11-30 23:59:59.997'
GO
EXEC Sales.usp_GetCustomerStats 17061, '2013-01-01 00:00:00.000', '2013-01-31 23:59:59.997'
GO
EXEC Sales.usp_GetCustomerStats 11711, '2013-03-01 00:00:00.000', '2013-03-31 23:59:59.997'
GO
EXEC Sales.usp_GetCustomerStats 15131, '2013-02-01 00:00:00.000', '2013-02-28 23:59:59.997'
GO
EXEC Sales.usp_GetCustomerStats 29837, '2012-10-01 00:00:00.000', '2012-10-31 23:59:59.997'
GO
EXEC Sales.usp_GetCustomerStats 15750, '2013-03-01 00:00:00.000', '2013-03-31 23:59:59.997'
GO

I then checked the procedure cache to verify the execution count, and also verified the plan that was cached:

SELECT
OBJECT_NAME([st].[objectid]),
[st].[text],
[qs].[execution_count],
[qs].[creation_time],
[qs].[last_execution_time],
[qs].[min_worker_time],
[qs].[max_worker_time],
[qs].[min_logical_reads],
[qs].[max_logical_reads],
[qs].[min_elapsed_time],
[qs].[max_elapsed_time],
[qp].[query_plan]
FROM [sys].[dm_exec_query_stats] [qs]
CROSS APPLY [sys].[dm_exec_sql_text]([qs].plan_handle) [st]
CROSS APPLY [sys].[dm_exec_query_plan]([qs].plan_handle) [qp]
WHERE [st].[text] LIKE '%usp_GetCustomerStats%'
AND OBJECT_NAME([st].[objectid]) IS NOT NULL;

Plan Cache Info for the SP: At Start

Query Plan for Stored Procedure, using SQL Sentry Plan Explorer

The plan was created at 2014-09-29 23:23.01.

Next I added 61 million rows to the table to invalidate the current statistics, and once the insert completed, I checked the row counts:

Big_SalesOrderHeader CIX and NCI Information: After insert of 61 million rows

Before running the stored procedure again, I verified that the execution count had not changed, that the creation_time was still 2014-09-29 23:23.01 for the plan, and that statistics hadn't updated:

Plan Cache Info for the SP: Immediately After Insert

NCI Statistics: After Insert

Now, in the previous blog post, I ran the statement in Management Studio, but this time, I ran the query directly from Plan Explorer, and captured the Actual Plan via PE (option circled in red in the image below).

Execute Stored Procedure from Plan Explorer

When you execute a statement from PE, you have to enter the instance and database to which you want to connect, and then you are notified that the query will run and the actual plan will be returned, but results will not be returned.  Note that this is different than Management Studio, where you do see the results.

After I ran the stored procedure, in the output I not only get the plan, but I see what statements were executed:

Plan Explorer output after execution SP (after insert)

This is pretty cool…in addition to seeing the statement executed in the stored procedure, I also see the updates to statistics, just as I did when I captured updates using Extended Events or SQL Trace. Along with the statement execution, we can also see CPU, duration, and IO information. Now – the caveat here is that I can see this information if I run the statement that invokes the statistics update from Plan Explorer. That probably won't happen often in your production environment, but you may see this when you're doing testing (because hopefully your testing doesn't just involve running SELECT queries, but also involves INSERT/UPDATE/DELETE queries just like you would see in a normal workload). However, if you're monitoring your environment with a tool like SQL Sentry Performance Advisor, you might see these updates in Top SQL as long as they exceed the Top SQL collection threshold. Performance Advisor has default thresholds that queries must exceed before they are captured as Top SQL (e.g. duration must exceed five (5) seconds), but you can change those and add other thresholds such as reads. In this example, for testing purposes only, I changed my Top SQL minimum duration threshold to 10 milliseconds and my read threshold to 500, and Performance Advisor was able to capture some of the statistics updates:

Statistics updates captured by Performance Advisor

That said, whether monitoring can capture these events will ultimately depend on system resources and the amount of data that has to be read to update the statistic. Your statistics updates may not exceed these thresholds, so you may have to do more proactive digging to find them.

Summary

I always encourage DBAs to proactively manage statistics – which means that a job is in place to update statistics on a regular basis. However, even if that job runs every night (which I'm not necessarily recommending), it's still quite possible that updates to statistics occur automatically throughout the day, because some tables are more volatile than others and have a high number of modifications. This is not abnormal, and depending on the size of the table and the amount of modifications, the automatic updates may not interfere significantly with user queries.  But the only way to know is to monitor those updates – whether you're using native tools or third-party tools – so that you can stay ahead of potential issues and address them before they escalate.

The post Another Way to View Automatic Updates to Statistics appeared first on SQLPerformance.com.

Most PASS Chapter meetings are supported by sponsors who either provide money which is often used for food and drink, or sometimes bringing the food, or picking up the check at a nearby restaurant for the “after” event. In return sponsors get a mention in the meeting opening and if they are in town, a few minutes to address the group. A system that works and has the potential to be good for all involved, but I think sponsors often manage to short change themselves.

If you’re a sponsor, here’s some things you might want to do, or not do:

• Use your five minutes (or whatever time you were allotted). I see too many get up, do an awkward and short talk, and then sit down. You can accomplish a lot in five minutes!
• Practice your pitch before the meeting
• Explain what your company does, and I’d suggest starting at the beginning – I promise there is someone in the audience that will appreciate it
• Explain why you’re sponsoring
• If you’re in a competitive sector (staffing for example), help us understand why we should call you instead of Company A, B, or C. You might do that by showing competence. How many SQL positions have you filled in the last 3 months? What’s the average salary? The most common skill asked for (and perhaps not found)? How many openings do you have today? Or if you sell software, maybe talk about how you’re better at support, revisions, renewal fees – some clear differentiator
• Work the room if time is provided. It’s a great time to find out if people know about your company/product, and a great time to hand out business cards too.

On the Chapter side, I’d like to see us do more coaching. Sponsor spend a $100 or $200 dollars (or more) for just a few minutes. Helping them be effective makes it easier to justify the cost, and increases the chance they’ll sponsor again. Beyond that though, a sponsor who ‘does it right’ adds value to the meeting. A sponsor who isn’t really prepared (or doesn’t understand the dynamic) just becomes a commercial that we’re required to sit through.

I have a lot of books on my shelves dealing with business intelligence, data warehousing, master data management, and consulting.  Below are my favorites:

Data Warehouse Books:

Ralph Kimball Books

Building the Data Warehouse

Corporate Information Factory

DW 2.0

Mastering Data Warehouse Design

Data Warehouse from Architecture to Implementation

Microsoft SQL Server 2012 Internals

Star Schema: The complete reference

Microsoft Big Data Solutions

Business Intelligence Books:

Expert Cube Development with SSAS Multidimensional Models

Pro SQL Server 2012 BI Solutions

Business Intelligence Competency Centers

Data Mining with Microsoft SQL Server 2008

Performance Dashboards

How To Measure Anything

Dimensional Modeling: In a Business Intelligence Environment

The Balanced Scorecard

Information Dashboard Design

Microsoft SQL Server 2014 Business Intelligence Development

Reporting with Microsoft SQL Server 2012 (shameless plug of my book)

Master Data Management Books:

Master Data Management

Microsoft SQL Server 2012 Master Data Services

Data Quality: The Accuracy Dimension

Consulting Books:

How to win friends and influence people

Influence: Science and practice

The Secrets of Consulting: A Guide to Giving and Getting Advice Successfully

More Secrets of Consulting

The Rational Guide to: IT Consulting

The Nomadic Developer

Agile Project Management With Scrum

SQL Server online documentation makes very clear that you need to 'drain' your replicated transactions before doing any upgrade if you have replicated databases. Below are requirements for transactional replication:

1. Make sure that the Log Reader Agent is running for the database. By default, the agent runs continuously.
2. Stop user activity on published tables.
3. Allow time for the Log Reader Agent to copy transactions to the distribution database, and then stop the agent.
4. Execute sp_replcmds to verify that all transactions have been processed. The result set from this procedure should be empty.
5. Execute sp_replflush to close the connection from sp_replcmds.
6. Perform the server upgrade to SQL Server 2012.
7. Restart SQL Server Agent and the Log Reader Agent if they do not start automatically after the upgrade.

A recent customer issue further confirms the need of following the steps before upgrade. The customer has transaction replication configured and they needed to upgrade from SQL Server 2008 to SQL Server 2012. This customer's publisher database was mirrored. They used 'rolling upgrade approach. They allowed publisher to continue to accept incoming transactions on primary server while upgrading the mirrored server. Then they failed over to the upgraded server and made it primary server. After that, they upgraded the original primary server. During the upgrade process, some transactions occurred on SQL Server 2008 but log reader didn't get a chance to read them and copy them to distribution. After the upgrade, they experienced the following error and engaged us.

Error 542 An invalid datetime value was encountered. Value exceeds the year 9999. (Source: MSSQLServer, Error number: 542)

Via some internal testing, we also reproduced additional error below if there are replicated transactions left prior to upgrade.

Error 515 Cannot insert the value NULL into column 'column1', table abc'; column does not allow nulls. UPDATE fails

Upon further investigation, we have discovered that SQL 2012 has some minor difference in terms how certain log records are handled. For those SQL 2008 log records (that weren't 'drained' before upgrade), Log reader agent made some incorrect assumptions and ended up reading the log records incorrectly. For the above errors 542 and 515, the underlying the issue is the same (reading some incorrect data). For 542, log reader agent was able to catch the fact that the value read was invalid for date. So log reader stopped processing the log record. So the error is raised by log reader agent. For 515, log reader didn't even know NULL is invalid for a non-NULL column. So log reader processed the log record and put it in distribution database. But this was caught by actually executing the update to subscriber and error was raised by distribution agent.

This further supports requirement documented above that you must ensure you 'drain' all replicated transaction before upgrade. However, it's undesirable to have log reader read incorrect values in such situation either. So our product team decides to fix this. Currently, we have a fix for SQL 2012. Fix for SQL 2014 is being built.

Solutions

Let's summarize the solutions here.

To avoid the problem entirely:

1. If you follow online documentation prior to upgrade to ensure no replicated transaction left on 2008 or 2008 R2, you won't experience the above issue.
2. If your situation doesn't allow you to stop accepting incoming transactions during upgrade (using mirror for rolling upgrade as an example), you should follow these steps:
   1. First call our support to obtain the fix/patch for this issue
   2. Prior to upgrade, disable log reader agent
   3. Upgrade and then immediately apply the fix/patch you obtained from above step.
   4. Enable and start log reader agent

If you have already upgraded and experience the errors (542, 515), the following are the options

1. You can re-initialize your replication and everything will be fine
2. If you only experience 542 error, you can obtain the fix/patch from us and new log reader will process the log record correctly.
3. If you see 515 error, you only option is to re-initialize. This is because log reader already incorrectly process the record and it can't go back and reprocess.

I want to point out this: The fact we are providing a fix for this particular situation doesn't mean that you should avoid the requirement of 'draining' replicated transactions per online documentation. You should still follow the upgrade requirement documentation.

Jack Li | Senior Escalation Engineer | Microsoft SQL Server Support

Nic Cain (@SirSQL) has a blog post that I highly recommend reading if you attend User Group meetings or SQLSaturdays: An Open Letter To SQLSaturday & User Group Organizers.  I think Nic tells a good story with a very relevant example of how a new speaker could have a negative first speaking experience.  And he has a great call to action for organizers and presenters.

I suggest that we raise that call to action to include veteran speakers.  For example…my local user group is the Ohio North SQL Server User Group.  To anyone who is also a member of this group and wants present at a local meeting: let me know.  I am more than happy to help you get started, provide feedback, and be there for your first session.  Further, I’m attending SQLSaturday #304 in Indianapolis next month.  If you’re presenting there for the first time and want me to be there for your session, let me know!

This is an open offer, with no expiration, and I do hope that someone takes me up on my offer.  And I would be remiss if I did not mention the following individuals who were there for my some of my first sessions and supported me:

• Allen White (@SQLRunr) – my first session was at our user group in December 2010, and Allen stood in the back the entire time, in my line of sight in case I needed him
• Mike Walsh (@mike_walsh) – with whom I co-presented at my first SQLSaturday in Feburary 2011, something I would recommend new speakers consider (it’s not a great fit for everyone, but I enjoyed presenting with Mike)
• Kendra Little (@kendra_little) – who sat in on my first solo session at that SQLSaturday in 2011, and laughed at my jokes
• Rob Farley (@rob_farley) – even though Rob fell asleep during my first solo Summit session (Friday afternoon, end of the week, jet lag, and too many late nights, etc. :), he provided feedback I still remember to this day
• Ted Krueger (@onpnt) – he helped me fine tune one of my favorite sessions (during a speaker dinner no less…I still owe Jes for that) and then sat through it and helped fill in some gaps when I needed help

To those of you that have been speaking for a while, I encourage you to seek out potential speakers – whether it’s in the community or at your office – and offer your help.  And for new speakers, please do not be afraid to ask for guidance.  Everyone starts at the beginning, with the same pile of nerves and fears about what could happen.  There are so many people who are willing to help make the process easier – seek them out, and have fun!

The post Helping First Time Presenters appeared first on Erin Stellato.

Consider the following AdventureWorks query that returns history table transaction IDs for product ID 421:

SELECT TH.TransactionID
FROM Production.TransactionHistory AS TH
WHERE TH.ProductID = 421;

The query optimizer quickly finds an efficient execution plan with a cardinality (row count) estimate that is exactly correct, as shown in SQL Sentry Plan Explorer:

Now say we want to find history transaction IDs for the AdventureWorks product named "Metal Plate 2". There are many ways to express this query in T-SQL. One natural formulation is:

SELECT TH.TransactionID
FROM Production.TransactionHistory AS TH
WHERE TH.ProductID = 
(
    SELECT P.ProductID 
    FROM Production.Product AS P
    WHERE P.Name = N'Metal Plate 2'
);

The execution plan is as follows:

The strategy is:

1. Look up the product ID in the Product table from the name given
2. Locate rows for that product ID in the History table

The estimated number of rows for step 1 is exactly right because the index used is declared as unique and keyed on the product name alone. The equality test on "Metal Plate 2" is therefore guaranteed to return exactly one row (or zero rows if we specify a product name that does not exist).

The highlighted 257-row estimate for step two is less accurate: only 13 rows are actually encountered. This discrepancy arises because the optimizer does not know which particular product ID is associated with the product named "Metal Plate 2". It treats the value as unknown, generating a cardinality estimate using average density information. The calculation uses elements from the statistics object shown below:

DBCC SHOW_STATISTICS 
(
    'Production.TransactionHistory', 
    'IX_TransactionHistory_ProductID'
)
WITH STAT_HEADER, DENSITY_VECTOR;

The statistics show the table contains 113443 rows with 441 unique product IDs (1 / 0.002267574 = 441). Assuming the distribution of rows across product IDs is uniform, cardinality estimation expects a product ID to match (113443 / 441) = 257.24 rows on average. As it turns out, the distribution is not particularly uniform; there are only 13 rows for the "Metal Plate 2" product.

An Aside

You might be thinking that the 257-row estimate should be more accurate. For example, given that product IDs and names are both constrained to be unique, SQL Server could automatically maintain information about this one-to-one relationship. It would then know that "Metal Plate 2" is associated with product ID 479, and use that insight to generate a more accurate estimate using the ProductID histogram:

DBCC SHOW_STATISTICS 
(
    'Production.TransactionHistory', 
    'IX_TransactionHistory_ProductID'
)
WITH HISTOGRAM;

An estimate of 13 rows derived this way would have been exactly correct. Nevertheless, the estimate of 257 rows was not an unreasonable one, given the statistical information available and the normal simplifying assumptions (like uniform distribution) applied by cardinality estimation today. Exact estimates are always nice, but "reasonable" estimates are perfectly acceptable too.

Combining the two queries

Say we now want to see all transaction history IDs where the product ID is 421 OR the name of the product is "Metal Plate 2". A natural way to combine the two previous queries is:

SELECT TH.TransactionID
FROM Production.TransactionHistory AS TH
WHERE TH.ProductID = 421
OR TH.ProductID =
(
    SELECT P.ProductID 
    FROM Production.Product AS P
    WHERE P.Name = N'Metal Plate 2'
);

The execution plan is a little more complex now, but it still contains recognizable elements of the single-predicate plans:

The strategy is:

1. Find history records for product 421
2. Look up the product id for the product named "Metal Plate 2"
3. Find history records for the product id found in step 2
4. Combine rows from steps 1 & 3
5. Remove any duplicates (because product 421 might also be the one named "Metal Plate 2")

Steps 1 to 3 are exactly the same as before. The same estimates are produced for the same reasons. Step 4 is new, but very simple: it concatenates an expected 19 rows with an expected 257 rows, to give an estimate of 276 rows.

Step 5 is the interesting one. The duplicate-removing Stream Aggregate has an estimated input of 276 rows and an estimated output of 113443 rows^*. An aggregate that outputs more rows than it receives seems impossible, right?

* You will see an estimate of 102099 rows here if you are using the pre-2014 cardinality estimation model.

The Cardinality Estimation Bug

The impossible Stream Aggregate estimate in our example is caused by a bug in cardinality estimation. It is an interesting example so we will explore it in a bit of detail.

Subquery Removal

It may surprise you to learn that the SQL Server query optimizer does not work with subqueries directly. They are removed from the logical query tree early in the compilation process, and replaced with an equivalent construction that the optimizer is set up to work with and reason about. The optimizer has a number of rules that remove subqueries. These can be listed by name using the following query (the referenced DMV is minimally documented, but not supported):

SELECT name 
FROM sys.dm_exec_query_transformation_stats
WHERE name LIKE 'RemoveSubq%';

Results (on SQL Server 2014):

The combined test query has two predicates ("selections" in relational terms) on the history table, connected by OR. One of these predicates includes a subquery. The whole subtree (both predicates and the subquery) is transformed by the first rule in the list ("remove subquery in selection") to a semi-join over the union of the individual predicates. While it isn't possible to represent the result of this internal transformation exactly using T-SQL syntax, it is pretty close to being:

SELECT TH.TransactionID
FROM Production.TransactionHistory AS TH
WHERE EXISTS
(
    SELECT 1
    WHERE TH.ProductID = 421
 
    UNION ALL
 
    SELECT 1
    FROM Production.Product AS P
    WHERE P.Name = N'Metal Plate 2'
    AND P.ProductID = TH.ProductID
)
OPTION (QUERYRULEOFF ApplyUAtoUniSJ);

It is a little unfortunate that my T-SQL approximation of the internal tree after subquery removal contains a subquery, but in the language of the query processor it doesn't (it is a semi join). If you would prefer to see the raw internal form instead of my attempt at a T-SQL equivalent, please be assured that will be along momentarily.

The undocumented query hint included in the T-SQL above is there is to prevent a subsequent transformation for those of you that want to see the transformed logic in execution plan form. The annotations below show the positions of the two predicates after transformation:

The intuition behind the transformation is that a history row qualifies if either of the predicates are satisfied. Regardless of how helpful you find my approximate T-SQL and execution plan illustration, I hope it is at least reasonably clear that the rewrite expresses the same requirement as the original query.

I should stress that the optimizer does not literally generate alternate T-SQL syntax or produce complete execution plans at intermediate stages. The T-SQL and execution plan representations above are intended purely an aid to comprehension. If you're interested in the raw details, the promised internal representation of the transformed query tree (slightly edited for clarity/space) is:

Notice the highlighted apply semi join cardinality estimate. It is 113443 rows when using the 2014 cardinality estimator (102099 rows if using the old CE). Bear in mind that the AdventureWorks history table contains 113443 rows in total, so this represents 100% selectivity (90% for the old CE).

We saw earlier that applying either of these predicates alone results in only a small number of matches: 19 rows for product ID 421, and 13 rows (estimated 257) for "Metal Plate 2". Estimating that the disjunction (OR) of the two predicates will return all rows in the base table seems entirely bonkers.

Bug Details

The details of the selectivity computation for the semi join are only visible in SQL Server 2014 when using the new cardinality estimator with (undocumented) trace flag 2363. It's probably possible to see something similar with Extended Events, but the trace flag output is more convenient to use here. The relevant section of the output is shown below:

The cardinality estimator uses the Fixed Join calculator with 100% selectivity. As a consequence, the estimated output cardinality of the semi join is the same as its input, meaning all 113443 rows from the history table are expected to qualify.

The exact nature of the bug is that the semi join selectivity computation misses any predicates positioned beyond a union all in the input tree. In the illustration below, the lack of predicates on the semi join itself is taken to mean every row will qualify; it ignores the effect of predicates below the concatenation (union all).

This behaviour is all the more surprising when you consider that selectivity computation is operating on a tree representation that the optimizer generated itself (the shape of the tree and the positioning of the predicates is the result of it removing the subquery).

A similar issue occurs with the pre-2014 cardinality estimator, but the final estimate is instead fixed at 90% of the estimated semi join input (for entertaining reasons related to a inversed fixed 10% predicate estimate that is too much of a diversion to get into).

Examples

As mentioned above, this bug manifests when estimation is performed for a semi join with related predicates positioned beyond a union all. Whether this internal arrangement occurs during query optimization depends on the original T-SQL syntax and the precise sequence of internal optimization operations. The following examples show some cases where the bug does and does not occur:

Example 1

This first example incorporates a trivial change to the test query:

SELECT TH.TransactionID
FROM Production.TransactionHistory AS TH
WHERE TH.ProductID = (SELECT 421) -- The only change
OR TH.ProductID =
(
    SELECT P.ProductID 
    FROM Production.Product AS P
    WHERE P.Name = N'Metal Plate 2'
);

The estimated execution plan is:

The final estimate of 403 rows is inconsistent with the nested loops join's input estimates, but it is still a reasonable one (in the sense discussed earlier). If the bug had been encountered, the final estimate would be 113443 rows (or 102099 rows when using the pre-2014 CE model).

Example 2

In case you were about to rush out and rewrite all your constant comparisons as trivial subqueries to avoid this bug, look what happens if we make another trivial change, this time replacing the equality test in the second predicate with IN. The meaning of the query remains unchanged:

SELECT TH.TransactionID
FROM Production.TransactionHistory AS TH
WHERE TH.ProductID = (SELECT 421) -- Change 1
OR TH.ProductID IN                -- Change 2
(
    SELECT P.ProductID 
    FROM Production.Product AS P
    WHERE P.Name = N'Metal Plate 2'
);

The bug returns:

Example 3

Although this article has so far concentrated on a disjunctive predicate containing a subquery, the following example shows that the same query specification expressed using EXISTS and UNION ALL is also vulnerable:

SELECT TH.TransactionID
FROM Production.TransactionHistory AS TH
WHERE EXISTS
(
    SELECT 1
    WHERE TH.ProductID = 421
    UNION ALL
    SELECT 1
    FROM Production.Product AS P
    WHERE P.Name = N'Metal Plate 2'
    AND P.ProductID = TH.ProductID
);

Execution plan:

Example 4

Here are two more ways to express the same logical query in T-SQL:

SELECT TH.TransactionID 
FROM Production.TransactionHistory AS TH 
WHERE TH.ProductID = 421
UNION
SELECT TH.TransactionID 
FROM Production.TransactionHistory AS TH 
WHERE TH.ProductID = 
(
    SELECT P.ProductID
    FROM Production.Product AS P
    WHERE P.Name = N'Metal Plate 2'
);
 
SELECT TH.TransactionID 
FROM Production.TransactionHistory AS TH 
WHERE TH.ProductID = 421
UNION
SELECT TH.TransactionID 
FROM Production.TransactionHistory AS TH 
JOIN Production.Product AS P
    ON P.ProductID = TH.ProductID
    AND P.Name = N'Metal Plate 2';

Neither query encounters the bug, and both produce the same execution plan:

These T-SQL formulations happen to produce an execution plan with entirely consistent (and reasonable) estimates.

Example 5

You may be wondering if the inaccurate estimation is important. In the cases presented so far, it isn't, at least not directly. Problems arise when the bug occurs in a larger query, and the incorrect estimate affects optimizer decisions elsewhere. As a minimally-extended example, consider returning the results of our test query in a random order:

SELECT TH.TransactionID
FROM Production.TransactionHistory AS TH
WHERE TH.ProductID = 421
OR TH.ProductID =
(
    SELECT P.ProductID 
    FROM Production.Product AS P
    WHERE P.Name = N'Metal Plate 2'
)
ORDER BY NEWID(); -- New

The execution plan shows the incorrect estimate affects later operations. For example, it is the basis for the memory grant reserved for the sort:

If you would like to see a more real-world example of this bug's potential impact, take a look at this recent question from Richard Mansell on the SQLPerformance.com Q & A site, answers.SQLPerformance.com.

Summary and Final Thoughts

This bug is triggered when the optimizer performs cardinality estimation for a semi join, in specific circumstances. It is a challenging bug to spot and work around for a number of reasons:

• There is no explicit T-SQL syntax to specify a semi join, so it is hard to know in advance if a particular query will be vulnerable to this bug.
• The optimizer can introduce a semi join in a wide variety of circumstances, not all of which are obvious semi join candidates.
• The problematic semi join is often transformed to something else by later optimizer activity, so we can't even rely on there being a semi join operation in the final execution plan.
• Not every weird-looking cardinality estimate is caused by this bug. Indeed, many examples of this type are an expected and harmless side-effect of normal optimizer operation.
• The erroneous semi join selectivity estimate will always be 90% or 100% of its input, but this will not usually correspond to the cardinality of a table used in the plan. Furthermore, the semi join input cardinality used in the calculation may not even be visible in the final execution plan.
• There are typically many ways to express the same logical query in T-SQL. Some of these will trigger the bug, while others will not.

These considerations make it difficult to offer practical advice to spot or work around this bug. It is certainly worthwhile checking execution plans for "outrageous" estimates, and investigating queries with performance that is much worse than expected, but both of these may have causes that do not relate to this bug. That said, it worth particularly checking queries that include a disjunction of predicates and a subquery. As the examples in this article show, this is not the only way to encounter the bug, but I expect it to be a common one.

If you're lucky enough to be running SQL Server 2014, with the new cardinality estimator enabled, you may be able to confirm the bug by manually checking trace flag 2363 output for a fixed 100% selectivity estimation on a semi join, but this is hardly convenient. You will not want to be using undocumented trace flags on a production system, naturally.

The Connect bug report for this issue can be found here. Please vote and comment if you would like to see this issue investigated (and possibly fixed).

The post A Subquery Cardinality Estimation Bug appeared first on SQLPerformance.com.

When doing ETL, you have the choice of using T-SQL or SSIS (see When to use T-SQL or SSIS for ETL).  If you decide T-SQL is the way to go and you just want to execute a bunch of T-SQL statements (individually or within a stored procedure), it’s still a good idea to wrap them in SSIS Execute SQL Tasks because you can use logging, auditing and error handling that SSIS provides that T-SQL does not.  You can also easily run SSIS Execute SQL Tasks in parallel, so if those tasks are calling stored procedures that means you are able to run stored procedures in parallel.  Other benefits for using SSIS instead of a SQL Server Agent job include:

• The ability to use a project data connection manager, so if the connection info changes you only need to change it in one spot
• You can create checkpoints for restarting
• You can add logic to check if packages have run by querying the status log (SSISDB catalog) instead of manually looking at the SQL Server agent job steps
• You can do reporting off of the auditing info you capture
• You can use select statements against the SSIS history for analysis (history stored in SSISDB catalog), which you don’t have for job steps in SQL Server agent
• Ease of maintenance (but depends on knowledge of SSIS vs knowledge of SQL Server)

By Bradley Schacht

Click here to view Brad's entire blog.

SharePoint uses a series of SQL Server databases to save configuration information, security setups and all the user data that has been created. The Central Administration site is no different and has its own content database that is created when running the SharePoint configuration wizard. Unfortunately, unlike when creating service applications, you do not get to choose the name of this database during the wizard. The database name will default to SharePoint_AdminContent_<GUID>.

This could be problematic for a number of reasons. Many companies have a set of standards for database names. Other times you will want to change it simply because you have a small OCD issue (that’s me). While the task is not necessarily as easy as going into SQL Server and renaming the database in the object explorer it isn’t necessarily difficult either. Just follow the steps below and you will clear up that OCD (or naming convention) issue in no time.

1. Run the SharePoint 2013 Management Shell as Administrator.
   
2. Make note of what you would like the new database name to be as well as the current web application name for Central Administration.
   New Database: SharePointCentralAdmin
   Web Application: http://kerberos-sp:36000
3. With that information run the following command. This will create a new content database named SharePointCentralAdmin in my case.
   New-SPContentDatabase –Name SharePointCentralAdmin –WebApplication http://kerberos-sp:36000
   
   
4. The next step is to migrate the contents of the previous database (SharePoint_AdminContent_8449cb1b-4a84-4048-9425-0ec6e783ec37) to the new database (SharePointCentralAdmin). To do this we will need to pass database IDs rather than database names to the commands in the next step. To first find the IDs assigned to each of the databases using the Get-SPWebApplication and Get-SPContentDatabase commands.
   Get-SPWebApplication –Identity http://kerberos-sp:36000 | Get-SPContentDatabase | SELECT ID, Name, WebApplication | Format-List
   
5. Make note of the two IDs and which database each belongs to.
   Original Database: c87506a9-b87d-40b8-9582-aac9ee89c8f8
   New Database: f79cb9d8-8e45-4405-82c9-081f58bce7a0
6. With the IDs in hand use the Get-SPSite and Move-SPSite commands to migrate the content from one database to the other. For each of these commands we need to feed the ID for the database. When running the command you will be prompted to confirm the action, press Y to confirm each prompt or A to accept all the prompts at the beginning.
   Get-SPSite –ContentDatabase c87506a9-b87d-40b8-9582-aac9ee89c8f8 | Move-SPSite –DestinationDatabase f79cb9d8-8e45-4405-82c9-081f58bce7a0
   
7. Note that IIS must be restarted. To do so simply type IISReset into the management shell.
   
8. Next, to remove the database association from the Central Administration web application run the following command and press Y to confirm each action.
   Remove-SPContentDatabase c87506a9-b87d-40b8-9582-aac9ee89c8f8
   
9. Optionally confirm the association has been removed by running the same command from Step 4.
   Get-SPWebApplication –Identity http://kerberos-sp:36000 | Get-SPContentDatabase | SELECT ID, Name, WebApplication | Format-List
   
10. Drop the original database with the GUID in the name use SQL Server Management Studio.
    
    
11. Relax because your OCD is now happy. Also go tell the DBAs they owe you a cookie for removing the GUID, they will be happy too.

Correct now, optimize later. is one of the most important developer mantras and Scott K. followed it to a fault. He was on a team of programmers debugging a C# package management application, which used Microsoft SQL for revision tracking. Make sure it works right the first time; you can always tease out more performance after launch.

But if your program takes ten minutes to extract a C# package, as Scott discovered, you might want to optimize sooner rather than later.

Scott went looking for the usual performance bottlenecks and typical slip-ups -- a WHERE clause instead of an ON, the wrong kind of table UNION. Instead, he found this generated SQL monstrosity:

SELECT 
    [Extent5].[Name] AS [Name]
    FROM   (SELECT [Extent1].[Id] AS [Id1], [Extent1].[LineId] AS [LineId], [Extent2].[Id] AS [Id2], [Extent2].[PackageId] AS [PackageId], [Extent2].[FileId] AS [FileId], [Extent3].[Id] AS [Id3], [Extent3].[Path] AS [Path], [Extent3].[Hash] AS [Hash], [Extent4].[Id] AS [Id4], [Extent4].[VersionString] AS [VersionString], [Extent4].[UserInstalled] AS [UserInstalled], [Extent4].[InstallTime] AS [InstallTime], [Extent4].[ManifestId] AS [ManifestId]
        FROM    [Redacted1] AS [Extent1]
        INNER JOIN [Redacted2] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]
        INNER JOIN [Redacted3] AS [Extent3] ON [Extent2].[FileId] = [Extent3].[Id]
        INNER JOIN [Redacted4] AS [Extent4] ON [Extent2].[PackageId] = [Extent4].[Id]
        WHERE  EXISTS (SELECT 
            1 AS [C1]
            FROM  (SELECT 
                [UnionAll54].[C1] AS [C1]
                FROM  (SELECT 
                    [UnionAll53].[C1] AS [C1]
                    FROM  (SELECT 
                        [UnionAll52].[C1] AS [C1]
                        FROM  (SELECT 
                            [UnionAll51].[C1] AS [C1]
                            FROM  (SELECT 
                                [UnionAll50].[C1] AS [C1]
                                FROM  (SELECT 
                                    [UnionAll49].[C1] AS [C1]
                                    FROM  (SELECT 
                                        [UnionAll48].[C1] AS [C1]
                                        FROM  (SELECT 
                                            [UnionAll47].[C1] AS [C1]
                                            FROM  (SELECT 
                                                [UnionAll46].[C1] AS [C1]
                                                FROM  (SELECT 
                                                    [UnionAll45].[C1] AS [C1]
                                                    FROM  (SELECT 
                                                        [UnionAll44].[C1] AS [C1]
                                                        FROM  (SELECT 
                                                            [UnionAll43].[C1] AS [C1]
                                                            FROM  (SELECT 
                                                                [UnionAll42].[C1] AS [C1]
                                                                FROM  (SELECT 
                                                                    [UnionAll41].[C1] AS [C1]
                                                                    FROM  (SELECT 
                                                                        [UnionAll40].[C1] AS [C1]
                                                                        FROM  (SELECT 
                                                                            [UnionAll39].[C1] AS [C1]
                                                                            FROM  (SELECT 
                                                                                [UnionAll38].[C1] AS [C1]
                                                                                FROM  (SELECT 
                                                                                    [UnionAll37].[C1] AS [C1]
                                                                                    FROM  (SELECT 
                                                                                        [UnionAll36].[C1] AS [C1]
                                                                                        FROM  (SELECT 
                                                                                            [UnionAll35].[C1] AS [C1]
                                                                                            FROM  (SELECT 
                                                                                                [UnionAll34].[C1] AS [C1]
                                                                                                FROM  (SELECT 
                                                                                                    [UnionAll33].[C1] AS [C1]
                                                                                                    FROM  (SELECT 
                                                                                                        [UnionAll32].[C1] AS [C1]
                                                                                                        FROM  (SELECT 
                                                                                                            [UnionAll31].[C1] AS [C1]
                                                                                                            FROM  (SELECT 
                                                                                                                [UnionAll30].[C1] AS [C1]
                                                                                                                FROM  (SELECT 
                                                                                                                    [UnionAll29].[C1] AS [C1]
                                                                                                                    FROM  (SELECT 
                                                                                                                        [UnionAll28].[C1] AS [C1]
                                                                                                                        FROM  (SELECT 
                                                                                                                            [UnionAll27].[C1] AS [C1]
                                                                                                                            FROM  (SELECT 
                                                                                                                                [UnionAll26].[C1] AS [C1]
                                                                                                                                FROM  (SELECT 
                                                                                                                                    [UnionAll25].[C1] AS [C1]
                                                                                                                                    FROM  (SELECT 
                                                                                                                                        [UnionAll24].[C1] AS [C1]
                                                                                                                                        FROM  (SELECT 
                                                                                                                                            [UnionAll23].[C1] AS [C1]
                                                                                                                                            FROM  (SELECT 
                                                                                                                                                [UnionAll22].[C1] AS [C1]
                                                                                                                                                FROM  (SELECT 
                                                                                                                                                    [UnionAll21].[C1] AS [C1]
                                                                                                                                                    FROM  (SELECT 
                                                                                                                                                        [UnionAll20].[C1] AS [C1]
                                                                                                                                                        FROM  (SELECT 
                                                                                                                                                            [UnionAll19].[C1] AS [C1]
                                                                                                                                                            FROM  (SELECT 
                                                                                                                                                                [UnionAll18].[C1] AS [C1]
                                                                                                                                                                FROM  (SELECT 
                                                                                                                                                                    [UnionAll17].[C1] AS [C1]
                                                                                                                                                                    FROM  (SELECT 
                                                                                                                                                                        [UnionAll16].[C1] AS [C1]
                                                                                                                                                                        FROM  (SELECT 
                                                                                                                                                                            [UnionAll15].[C1] AS [C1]
                                                                                                                                                                            FROM  (SELECT 
                                                                                                                                                                                [UnionAll14].[C1] AS [C1]
                                                                                                                                                                                FROM  (SELECT 
                                                                                                                                                                                    [UnionAll13].[C1] AS [C1]
                                                                                                                                                                                    FROM  (SELECT 
                                                                                                                                                                                        [UnionAll12].[C1] AS [C1]
                                                                                                                                                                                        FROM  (SELECT 
                                                                                                                                                                                            [UnionAll11].[C1] AS [C1]
                                                                                                                                                                                            FROM  (SELECT 
                                                                                                                                                                                                [UnionAll10].[C1] AS [C1]
                                                                                                                                                                                                FROM  (SELECT 
                                                                                                                                                                                                    [UnionAll9].[C1] AS [C1]
                                                                                                                                                                                                    FROM  (SELECT 
                                                                                                                                                                                                        [UnionAll8].[C1] AS [C1]
                                                                                                                                                                                                        FROM  (SELECT 
                                                                                                                                                                                                            [UnionAll7].[C1] AS [C1]
                                                                                                                                                                                                            FROM  (SELECT 
                                                                                                                                                                                                                [UnionAll6].[C1] AS [C1]
                                                                                                                                                                                                                FROM  (SELECT 
                                                                                                                                                                                                                    [UnionAll5].[C1] AS [C1]
                                                                                                                                                                                                                    FROM  (SELECT 
                                                                                                                                                                                                                        [UnionAll4].[C1] AS [C1]
                                                                                                                                                                                                                        FROM  (SELECT 
                                                                                                                                                                                                                            [UnionAll3].[C1] AS [C1]
                                                                                                                                                                                                                            FROM  (SELECT 
                                                                                                                                                                                                                                [UnionAll2].[C1] AS [C1]
                                                                                                                                                                                                                                FROM  (SELECT 
                                                                                                                                                                                                                                    [UnionAll1].[C1] AS [C1]
                                                                                                                                                                                                                                    FROM  (SELECT 
                                                                                                                                                                                                                                        N'22176' AS [C1]
                                                                                                                                                                                                                                        FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
                                                                                                                                                                                                                                    UNION ALL
                                                                                                                                                                                                                                        SELECT 
                                                                                                                                                                                                                                        N'22177' AS [C1]
                                                                                                                                                                                                                                        FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]) AS [UnionAll1]
                                                                                                                                                                                                                                UNION ALL
                                                                                                                                                                                                                                    SELECT 
                                                                                                                                                                                                                                    N'22178' AS [C1]
                                                                                                                                                                                                                                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable3]) AS [UnionAll2]
                                                                                                                                                                                                                            UNION ALL
                                                                                                                                                                                                                                SELECT 
                                                                                                                                                                                                                                N'22179' AS [C1]
                                                                                                                                                                                                                                FROM  ( SELECT 1 AS X ) AS [SingleRowTable4]) AS [UnionAll3]
                                                                                                                                                                                                                        UNION ALL
                                                                                                                                                                                                                            SELECT 
                                                                                                                                                                                                                            N'22180' AS [C1]
                                                                                                                                                                                                                            FROM  ( SELECT 1 AS X ) AS [SingleRowTable5]) AS [UnionAll4]
                                                                                                                                                                                                                    UNION ALL
                                                                                                                                                                                                                        SELECT 
                                                                                                                                                                                                                        N'22181' AS [C1]
                                                                                                                                                                                                                        FROM  ( SELECT 1 AS X ) AS [SingleRowTable6]) AS [UnionAll5]
                                                                                                                                                                                                                UNION ALL
                                                                                                                                                                                                                    SELECT 
                                                                                                                                                                                                                    N'22182' AS [C1]
                                                                                                                                                                                                                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable7]) AS [UnionAll6]
                                                                                                                                                                                                            UNION ALL
                                                                                                                                                                                                                SELECT 
                                                                                                                                                                                                                N'22183' AS [C1]
                                                                                                                                                                                                                FROM  ( SELECT 1 AS X ) AS [SingleRowTable8]) AS [UnionAll7]
                                                                                                                                                                                                        UNION ALL
                                                                                                                                                                                                            SELECT 
                                                                                                                                                                                                            N'22185' AS [C1]
                                                                                                                                                                                                            FROM  ( SELECT 1 AS X ) AS [SingleRowTable9]) AS [UnionAll8]
                                                                                                                                                                                                    UNION ALL
                                                                                                                                                                                                        SELECT 
                                                                                                                                                                                                        N'22186' AS [C1]
                                                                                                                                                                                                        FROM  ( SELECT 1 AS X ) AS [SingleRowTable10]) AS [UnionAll9]
                                                                                                                                                                                                UNION ALL
                                                                                                                                                                                                    SELECT 
                                                                                                                                                                                                    N'22187' AS [C1]
                                                                                                                                                                                                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable11]) AS [UnionAll10]
                                                                                                                                                                                            UNION ALL
                                                                                                                                                                                                SELECT 
                                                                                                                                                                                                N'22188' AS [C1]
                                                                                                                                                                                                FROM  ( SELECT 1 AS X ) AS [SingleRowTable12]) AS [UnionAll11]
                                                                                                                                                                                        UNION ALL
                                                                                                                                                                                            SELECT 
                                                                                                                                                                                            N'22189' AS [C1]
                                                                                                                                                                                            FROM  ( SELECT 1 AS X ) AS [SingleRowTable13]) AS [UnionAll12]
                                                                                                                                                                                    UNION ALL
                                                                                                                                                                                        SELECT 
                                                                                                                                                                                        N'22191' AS [C1]
                                                                                                                                                                                        FROM  ( SELECT 1 AS X ) AS [SingleRowTable14]) AS [UnionAll13]
                                                                                                                                                                                UNION ALL
                                                                                                                                                                                    SELECT 
                                                                                                                                                                                    N'22192' AS [C1]
                                                                                                                                                                                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable15]) AS [UnionAll14]
                                                                                                                                                                            UNION ALL
                                                                                                                                                                                SELECT 
                                                                                                                                                                                N'22193' AS [C1]
                                                                                                                                                                                FROM  ( SELECT 1 AS X ) AS [SingleRowTable16]) AS [UnionAll15]
                                                                                                                                                                        UNION ALL
                                                                                                                                                                            SELECT 
                                                                                                                                                                            N'22195' AS [C1]
                                                                                                                                                                            FROM  ( SELECT 1 AS X ) AS [SingleRowTable17]) AS [UnionAll16]
                                                                                                                                                                    UNION ALL
                                                                                                                                                                        SELECT 
                                                                                                                                                                        N'22196' AS [C1]
                                                                                                                                                                        FROM  ( SELECT 1 AS X ) AS [SingleRowTable18]) AS [UnionAll17]
                                                                                                                                                                UNION ALL
                                                                                                                                                                    SELECT 
                                                                                                                                                                    N'22198' AS [C1]
                                                                                                                                                                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable19]) AS [UnionAll18]
                                                                                                                                                            UNION ALL
                                                                                                                                                                SELECT 
                                                                                                                                                                N'22199' AS [C1]
                                                                                                                                                                FROM  ( SELECT 1 AS X ) AS [SingleRowTable20]) AS [UnionAll19]
                                                                                                                                                        UNION ALL
                                                                                                                                                            SELECT 
                                                                                                                                                            N'22200' AS [C1]
                                                                                                                                                            FROM  ( SELECT 1 AS X ) AS [SingleRowTable21]) AS [UnionAll20]
                                                                                                                                                    UNION ALL
                                                                                                                                                        SELECT 
                                                                                                                                                        N'22201' AS [C1]
                                                                                                                                                        FROM  ( SELECT 1 AS X ) AS [SingleRowTable22]) AS [UnionAll21]
                                                                                                                                                UNION ALL
                                                                                                                                                    SELECT 
                                                                                                                                                    N'22202' AS [C1]
                                                                                                                                                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable23]) AS [UnionAll22]
                                                                                                                                            UNION ALL
                                                                                                                                                SELECT 
                                                                                                                                                N'22204' AS [C1]
                                                                                                                                                FROM  ( SELECT 1 AS X ) AS [SingleRowTable24]) AS [UnionAll23]
                                                                                                                                        UNION ALL
                                                                                                                                            SELECT 
                                                                                                                                            N'22205' AS [C1]
                                                                                                                                            FROM  ( SELECT 1 AS X ) AS [SingleRowTable25]) AS [UnionAll24]
                                                                                                                                    UNION ALL
                                                                                                                                        SELECT 
                                                                                                                                        N'22207' AS [C1]
                                                                                                                                        FROM  ( SELECT 1 AS X ) AS [SingleRowTable26]) AS [UnionAll25]
                                                                                                                                UNION ALL
                                                                                                                                    SELECT 
                                                                                                                                    N'22208' AS [C1]
                                                                                                                                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable27]) AS [UnionAll26]
                                                                                                                            UNION ALL
                                                                                                                                SELECT 
                                                                                                                                N'22209' AS [C1]
                                                                                                                                FROM  ( SELECT 1 AS X ) AS [SingleRowTable28]) AS [UnionAll27]
                                                                                                                        UNION ALL
                                                                                                                            SELECT 
                                                                                                                            N'22210' AS [C1]
                                                                                                                            FROM  ( SELECT 1 AS X ) AS [SingleRowTable29]) AS [UnionAll28]
                                                                                                                    UNION ALL
                                                                                                                        SELECT 
                                                                                                                        N'22211' AS [C1]
                                                                                                                        FROM  ( SELECT 1 AS X ) AS [SingleRowTable30]) AS [UnionAll29]
                                                                                                                UNION ALL
                                                                                                                    SELECT 
                                                                                                                    N'22212' AS [C1]
                                                                                                                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable31]) AS [UnionAll30]
                                                                                                            UNION ALL
                                                                                                                SELECT 
                                                                                                                N'22213' AS [C1]
                                                                                                                FROM  ( SELECT 1 AS X ) AS [SingleRowTable32]) AS [UnionAll31]
                                                                                                        UNION ALL
                                                                                                            SELECT 
                                                                                                            N'22214' AS [C1]
                                                                                                            FROM  ( SELECT 1 AS X ) AS [SingleRowTable33]) AS [UnionAll32]
                                                                                                    UNION ALL
                                                                                                        SELECT 
                                                                                                        N'22215' AS [C1]
                                                                                                        FROM  ( SELECT 1 AS X ) AS [SingleRowTable34]) AS [UnionAll33]
                                                                                                UNION ALL
                                                                                                    SELECT 
                                                                                                    N'22217' AS [C1]
                                                                                                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable35]) AS [UnionAll34]
                                                                                            UNION ALL
                                                                                                SELECT 
                                                                                                N'22219' AS [C1]
                                                                                                FROM  ( SELECT 1 AS X ) AS [SingleRowTable36]) AS [UnionAll35]
                                                                                        UNION ALL
                                                                                            SELECT 
                                                                                            N'22220' AS [C1]
                                                                                            FROM  ( SELECT 1 AS X ) AS [SingleRowTable37]) AS [UnionAll36]
                                                                                    UNION ALL
                                                                                        SELECT 
                                                                                        N'22221' AS [C1]
                                                                                        FROM  ( SELECT 1 AS X ) AS [SingleRowTable38]) AS [UnionAll37]
                                                                                UNION ALL
                                                                                    SELECT 
                                                                                    N'22222' AS [C1]
                                                                                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable39]) AS [UnionAll38]
                                                                            UNION ALL
                                                                                SELECT 
                                                                                N'22226' AS [C1]
                                                                                FROM  ( SELECT 1 AS X ) AS [SingleRowTable40]) AS [UnionAll39]
                                                                        UNION ALL
                                                                            SELECT 
                                                                            N'22227' AS [C1]
                                                                            FROM  ( SELECT 1 AS X ) AS [SingleRowTable41]) AS [UnionAll40]
                                                                    UNION ALL
                                                                        SELECT 
                                                                        N'22228' AS [C1]
                                                                        FROM  ( SELECT 1 AS X ) AS [SingleRowTable42]) AS [UnionAll41]
                                                                UNION ALL
                                                                    SELECT 
                                                                    N'22229' AS [C1]
                                                                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable43]) AS [UnionAll42]
                                                            UNION ALL
                                                                SELECT 
                                                                N'22230' AS [C1]
                                                                FROM  ( SELECT 1 AS X ) AS [SingleRowTable44]) AS [UnionAll43]
                                                        UNION ALL
                                                            SELECT 
                                                            N'22231' AS [C1]
                                                            FROM  ( SELECT 1 AS X ) AS [SingleRowTable45]) AS [UnionAll44]
                                                    UNION ALL
                                                        SELECT 
                                                        N'22232' AS [C1]
                                                        FROM  ( SELECT 1 AS X ) AS [SingleRowTable46]) AS [UnionAll45]
                                                UNION ALL
                                                    SELECT 
                                                    N'22233' AS [C1]
                                                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable47]) AS [UnionAll46]
                                            UNION ALL
                                                SELECT 
                                                N'22234' AS [C1]
                                                FROM  ( SELECT 1 AS X ) AS [SingleRowTable48]) AS [UnionAll47]
                                        UNION ALL
                                            SELECT 
                                            N'22235' AS [C1]
                                            FROM  ( SELECT 1 AS X ) AS [SingleRowTable49]) AS [UnionAll48]
                                    UNION ALL
                                        SELECT 
                                        N'22236' AS [C1]
                                        FROM  ( SELECT 1 AS X ) AS [SingleRowTable50]) AS [UnionAll49]
                                UNION ALL
                                    SELECT 
                                    N'22237' AS [C1]
                                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable51]) AS [UnionAll50]
                            UNION ALL
                                SELECT 
                                N'22238' AS [C1]
                                FROM  ( SELECT 1 AS X ) AS [SingleRowTable52]) AS [UnionAll51]
                        UNION ALL
                            SELECT 
                            N'22239' AS [C1]
                            FROM  ( SELECT 1 AS X ) AS [SingleRowTable53]) AS [UnionAll52]
                    UNION ALL
                        SELECT 
                        N'22240' AS [C1]
                        FROM  ( SELECT 1 AS X ) AS [SingleRowTable54]) AS [UnionAll53]
                UNION ALL
                    SELECT 
                    N'22244' AS [C1]
                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable55]) AS [UnionAll54]
            UNION ALL
                SELECT 
                N'22245' AS [C1]
                FROM  ( SELECT 1 AS X ) AS [SingleRowTable56]) AS [UnionAll55]
            WHERE [UnionAll55].[C1] = [Extent1].[LineId]
        ) ) AS [Filter2]
    LEFT OUTER JOIN [Redacted5] AS [Extent5] ON [Filter2].[ManifestId] = [Extent5].[Id]
    WHERE [Filter2].[Path] = @p__linq__0

What could possibly have generated that? he wondered. A bit of hunting brought him to this innocuous C# method:

  public IList<string> CheckForConflicts(Context ctx)
  {
   var conflictsWith = Actions.Select(a => a.LineId);

   return ctx.DbContext.Redacted1
    .Where(a => a.File.Path == Filename && conflictsWith.Any(b => b == a.LineId))
    .Select(a => a.Package.Manifest.Name)
    .ToList();
  }

That .Any() call shouldn't have caused all that, he thought. But that poor method seemed a bit out of place. He changed conflictsWith.Any(b => b == a.LineId) to conflictsWith.Contains(a.LineId) and checked the generated SQL.

SELECT 
    [Extent5].[Name] AS [Name]
    FROM   (SELECT [Extent1].[Id] AS [Id1], [Extent1].[LineId] AS [LineId], [Extent2].[Id] AS [Id2], [Extent2].[PackageId] AS [PackageId], [Extent2].[FileId] AS [FileId], [Extent3].[Id] AS [Id3], [Extent3].[Path] AS [Path], [Extent3].[Hash] AS [Hash], [Extent4].[Id] AS [Id4], [Extent4].[VersionString] AS [VersionString], [Extent4].[UserInstalled] AS [UserInstalled], [Extent4].[InstallTime] AS [InstallTime], [Extent4].[ManifestId] AS [ManifestId]
        FROM    [Redacted1] AS [Extent1]
        INNER JOIN [Redacted2] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]
        INNER JOIN [Redacted3] AS [Extent3] ON [Extent2].[FileId] = [Extent3].[Id]
        INNER JOIN [Redacted4] AS [Extent4] ON [Extent2].[PackageId] = [Extent4].[Id]
        WHERE [Extent1].[LineId] IN (N'22176', N'22177', N'22178', N'22179', N'22180', N'22181', N'22182', N'22183', N'22185', N'22186', N'22187', N'22188', N'22189', N'22191', N'22192', N'22193', N'22195', N'22196', N'22198', N'22199', N'22200', N'22201', N'22202', N'22204', N'22205', N'22207', N'22208', N'22209', N'22210', N'22211', N'22212', N'22213', N'22214', N'22215', N'22217', N'22219', N'22220', N'22221', N'22222', N'22226', N'22227', N'22228', N'22229', N'22230', N'22231', N'22232', N'22233', N'22234', N'22235', N'22236', N'22237', N'22238', N'22239', N'22240', N'22244', N'22245') ) AS [Filter1]
    LEFT OUTER JOIN [Redacted5] AS [Extent5] ON [Filter1].[ManifestId] = [Extent5].[Id]
    WHERE [Filter1].[Path] = @p__linq__0

Still not pretty, but the execution time shrank from 10 minutes to 10 seconds.

Among the other mantras Scott followed, he added one more. Never confuse CONTAINS with ANY.

[Advertisement] Have you seen BuildMaster 4.3 yet? Lots of new features to make continuous delivery even easier; deploy builds from TeamCity (and other CI) to your own servers, the cloud, and more.

DBAs are supposed to bring knowledge of the underpinnings of databases to the table. How to lay out tables and indices across disks for linear vs. striped access. How to properly set up partitioning for different types of access. Granting assorted privileges and roles. Managing backup and aging off data in a controlled manner, and so forth.

Some take pride in showing developers the "right" way. Some are maniacal in their tight-fisted my-way-or-the-highway approach. Others seem better suited to a career of asking do you want fries with that?

Bob works at a large, lumbering bureaucracy where there is a procedure for everything, and everything has a procedure. The fundamental premise is that everything should be automated. Requisitioning supplies. Personnel. Software. Deployments. Everything.

To that end, every support team has a plethora of forms where you just click all of the check boxes next to the stuff you want, then click "submit" and a script will automatically do it for you. Sure, it pings a task-owner for approval for certain things, but the managers are so conditioned to just hit approve that you could requisition a GAU-8 as a management training tool, and it would likely get approved. In development. The UAT and production environments were subject to the usual plethora of approvers before anyone could actually do anything.

As such, it should come as no surprise that creating a database, and allocating space, as well as permissions to users is fully automated as well. There is a huge web application that allows you to request that a database be created with a given name for a given system. After you click 'OK', your brand new 10MB database is ready for use and you can begin to administer it.

Bob wasn't a DBA, but had a pretty good idea of what his team needed. Of course, there are things that DBAs do behind the scenes to keep things running (hopefully smoothly), but this was of no consequence at this point.

Bob requested additional space, as 10MB wasn't going to be enough for scratch space, let alone indices, let alone actual data. As the database administrator, he granted himself select, insert, update, delete and execute permissions. He added his co-workers and granted them permissions as well. Then he began to create tables.

After discussions with his team, they decided that partitioning on run-date would be the way to go. He created each table with a bunch of partitions, and local and global indices. He even included a catch-all partition just in case the script to create the daily partition didn't run or failed. Of course, since there was only one table-space in the database, all he could do was assign everything to be thrown together in one big pile.

He made a point of putting a task in the project schedule to have an actual DBA review everything that he was doing so that it would work properly when deployed and had to scale under load.

Fast forward a few months and it was time to have a DBA review the database. The request was made for a DBA to sit down and look at what had been designed. Four days later, the answer came: No!

Bob replied: What do you mean "No"? Everything that's in this database was created by clicking on forms, but was never holistically reviewed for sanity. What table-spaces are needed in a real environment? Do we need to create nicknames, and for what? What about temp space? Did we allocate enough? Are the partitions set up in the best way? What about the local/global indices? Did we set up roles correctly? How will all of this migrate to the UAT environment? What about production? We need an expert who does this for a living to review it with a critical eye and make sure it's the way it needs to be!

The DBA team replied: We only manage the farm of disk space and the physical servers. We don't manage individual databases; that's your responsibility. If something doesn't work, give us the commands to execute to change whatever you need to change and we'll run them for you. Normally, we have a turn-around time of about 3-4 days, but it can be longer if we're busy. This applies across all environments.

Bob was somewhat taken aback by the sheer idiocy of this policy. In an attempt to locate sanity in the midst of this madness, he sought out peers who had used this mechanism to see how they got their stuff into working-form for production. Sadly, he discovered that they did it by trial and error. When something didn't work in production, they made their best guess as to what might fix it and asked the DBAs to make the change. Then they waited for the approvals to be made and the work to be done in order to see if it would make a difference.

Bob reported this as a huge risk to the potential success of the project, but was told that it's just the way things are, and to roll with it...

[Advertisement] Have you seen BuildMaster 4.3 yet? Lots of new features to make continuous delivery even easier; deploy builds from TeamCity (and other CI) to your own servers, the cloud, and more.

"Chinese buffet2". Licensed under CC BY-SA 3.0 via Wikimedia Commons. Assembler. C. C++. C#. PHP. Javascript. Bash. Perl. Ruby. Java. These were just some of the technologies featured on the resume of a candidate Christian recently interviewed for a senior Linux sysadmin position. The impressive list of programming languages (and related data-interchange acronyms like XSLT and JSON) made the candidate, let's call him Rob, seem more qualified for a developer position, but he went on to list common web server databases like MySQL and Postgres (plus a couple flavours of NoSQL), and, finally, the qualifications Christian was actually interested in: Tomcat, JBOSS, the Hotspot JVM, and every major Linux distro. While the resume reeked of keyword-baiting, Christian didn't want to risk missing out on an excellent sysadmin who just happened to spend a lot of time hacking, and brought Rob in.

Christian kicked off the interview by describing their infrastructure. Working for a major enterprise, his division was responsible for fifteen hundred Java application servers, clustered into groups of three or four. He explained to Rob how they managed the large number of identical deployments using Puppet, with SVN to track changes to their enormous catalog of scripts. He got through most of their rollout and monitoring processes before Rob cut in with a question.

"You mentioned Subversion earlier. You developed this in-house?"

Christian stared at Rob for a while, trying to understand the question in the context of the seasoned developer's resume on the table in front of him. He realized Rob was getting a little uncomfortable, still waiting for his answer.

"We, um... No. No, Subversion is an open-source version control system. It's, well, quite popular. You... hadn't heard of it?"

"Not at all," Rob replied. "Version control, you said?"

"Yes. Sometimes also called configuration management software. Perhaps you have more experience with git, or mercurial?" Rob shook his head. Christian tried moving in the other direction. "CVS?" Another shake. "Er... Visual SourceSafe?"

"No, I'm afraid not. Version control sounds very interesting, though. I look forward to using one!"

The interview went careening downhill from there. Christian turned to some questions about Linux, and Rob's sysadmin training turned out to come mostly from Googling how-tos. Questions about processes and threads, memory management, and—most importantly—the JVM, flew over his head. Nearing his wit's end, and desperate to salvage the interview, Christian asked Rob to describe one of the web applications he claimed to have built, hoping to discover an unusual insight or something, anything, that would account for the gap between the resume and the human.

"I once took an order-management application and ported the whole thing from MySQL to PostgreSQL."

Interest piqued, Christian leaned forward. Could this be Rob's redeeming point? Maybe this was where he could offer value to his team.

"That's a pretty major change," Christian said. "What prompted you to make it, and how did you carry it out?"

"Well," Rob opened his hands, as though the answer were obvious. "MySQL didn't work anymore."

"You mean... what, it didn't scale?"

"No, I mean the service wouldn't start anymore."

"The... mysqld?"

"Yeah, somehow it broke and wouldn't start anymore. I tried init.d and rebooting, but it didn't help. So, after I uninstalled MySQL and installed Postgres instead, it worked."

Christian pressed for more details. "Okay... but changing the DBMS must have had a big impact on the application, right? MySQL and Postgres are very different, after all..."

"No," Rob said, "I mean, come on, they're both SQL. It wasn't that big of a change."

"Well... how did you plan the migration at least? This was an order-management system, right? So you'd have to do it in a way that preserved order history."

"Migration? We didn't do that," Rob said. Now it was his turn to fix Christian with an incredulous gaze. "I told you, MySQL wasn't working, so I uninstalled it. The data was gone, obviously. We entered it again from scratch."

This raised more questions than it answered—no backups? What about transferring the MySQL DB to another machine? Did he at least post the log file on StackOverflow with a terse "why doesn't this work?"—but by this point, Christian was desperate to get back to real life. He complimented Rob on his extensive experience and told him they'd let him know if he got the job. On the way back to his desk, Christian tore Rob's resume into pieces, one for each technology he'd pretended to understand.

[Advertisement] Have you seen BuildMaster 4.3 yet? Lots of new features to make continuous delivery even easier; deploy builds from TeamCity (and other CI) to your own servers, the cloud, and more.

Fred S. never much cared for zebra striping, the UI pattern that was all the rage after Mac OS X launched in 2001. It found its way into other Mac applications, web pages, even onto Linux. Like a tsunami of alternating grey-and-white waves, it overtook everything in its path.

After numerous requests from users, the project manager for WeightTracker asked Fred to add zebra striping to the weight journal window. Fred had inherited oversight of the application after the original developer, Louis, had been poached by their underperforming rival.

Louis, not a fan of Visual Basic's built-in functions, had written his own toolkit for WeightTracker, so Fred used Louis's evenOrOdd function to determine what color each row of the weight table should display. He incremented WeightTracker's version number and pushed up his changes.

QA immediately pushed them back: the zebra striping wasn't displaying in France.

Fred had never bothered to look at the evenOrOdd code, but it didn't sit well not knowing how it worked. He laughed when he found it.

iBoucle = 0 
While (FileImg.ListCount > 0) 
 iBoucle = iBoucle + 1 
 If InStr(Str(iBoucle / 2), ".") > 0 Then 
 ...

evenOrOdd takes a number, divides it by two, converts it to a string, and returns true if there is a . character present. It works perfectly if you use periods for decimal markers.

However, in France (and most of Europe), the decimal marker is ,, not .. Louis, who Fred knew was a French programmer, should have known this.

[Advertisement] Have you seen BuildMaster 4.3 yet? Lots of new features to make continuous delivery even easier; deploy builds from TeamCity (and other CI) to your own servers, the cloud, and more.

Security is challenging to get right. It's always a complex balancing act between what users want and what administrators need. Between placing the server in a hermetically sealed container with no cables running the outside world, and setting the server up on the busiest street corner in town with an already logged-in administrator account pulled up on the attached monitor. Depending on the O/S update policy in practice at your company, that last example can be roughly the equivalent of connecting your server to the Internet.

Here at TDWTF, security is a common topic of submissions. If only because there are so many different (and creative) ways to set things up that are wrong and only a couple of ways to set it up that are correct. And there is a non-zero percentage of administrators that are, shall we say, less than diligent in how they go about their job. We're sure that none of you fit into that category. We're talking about other people.

So with that in mind, consider Jim's plight.

For the past year, Jim had been working, along side a group of foreign developers, on a Magento installation. The reasons for the length of the project could be the result of a number of happenstances. Maybe it was the fact that requirements had the malleability of the an un-fired lump of clay. Perhaps it was the challenge of translating these requirements into sufficiently concise and clear descriptions to be passed off to the foreign development team. Or, just perhaps, some of the company's own policies (or anti-policies) got in the way.

The current goal was to move a recently completed module to the production server. Under normal circumstances (for those of you who are not Magento-familiar), this is a routine and straightforward task. However, this was not the case for Jim. Once deployed, the new module did not appear in the Magento customization panel. And Jim could no longer view the Permissions panel.

The quite reasonable conclusion is that there was a problem with security. A problem that hadn't existed at the time of the last module deployment, since it was, you know, successful and all. So Jim went directly to the super-admin user.

The response was mildly surprising. Jim was told that his permissions had been restricted. At the request of management.

Huh? Okay then. Next stop: management. Jim went to his manager and asked what, if anything, he knew about the request. Turns out that he did.

A few days earlier, Jim's manager was unable to delete a test order. His belief (through his black-and-white-colored manager glasses) was that Jim must have changed something. So he made the request to the administrator that he be given full access. And the Jim have his permission reduced.

Deep breath. After all, this wasn't the first time.

"But when it comes time to deploy and enable modules, I need to have administrator permission", responded Jim.

"You can get the super-admin user to do that. No need for you to have those privileges."

"Well, yes. But that will delay things. And doesn't really do much for security, as I have full access to the source code, server and database."

As an aside, it's rarely good to argue that you should be given a security exemption by suggesting that if you wanted to screw the company, it was already within your power to do so. Just keep that in the back of your mind as you move through your career. Now, back to our story.

"Just do what I ask", was the not unexpected reply.

Being the good corporate citizen that he was, Jim followed his manager's instructions. He sent his request to the administrator and waited a couple of days for it to be fulfilled. As it turns out, when the super-admin user deployed the module, he too was unable see the Permissions panel in the Magento console. So, to help troubleshoot the problem, Jim was temporarily given full access to the system.

If this isn't irony, it's pretty close.

As Jim was looking at the dog's breakfast that was the permissions allocated to each role, Jim noticed that his manager didn't actually have full access. The administrator had only restricted Jim without actually increasing the manager's capability. And that neither of them belong to the role that would allow them to deploy modules to the Magento installation. That ability, based on Jim's digging, seemed to reside solely in the persona of Montgomery.

Montgomery was the WordPress designer. While Jim was responsible for the customization of the modules and implementing additional functionality on the server side, Montgomery was in charge of the design of the site. In terms of the access he required on the server, it could have been limited to the theme folder and maybe some XML files. But he was not a framework coder, nor could he build a module on his own. In other words, his need to have full access ranged from minimal to non-existent.

So Jim is faced with a collection of options, none of which were good. He could send every one of his future production requests to the administrator, wait the day or two for it to be acted upon and deal with the delays in scheduling that causes. He could make a request to his manager, one that had already complained that Jim's permissions were too high, to increase his privileges. Or he could talk Montgomery through the steps necessary to get his modules and other updates installed.

Suggesting that facing options like this was a Sophie's Choice is going over the top. Instead, Jim preferred to think of it as a Full Monty.

[Advertisement] Have you seen BuildMaster 4.3 yet? Lots of new features to make continuous delivery even easier; deploy builds from TeamCity (and other CI) to your own servers, the cloud, and more.

Editor’s note: The following post was written by SharePoint MVP Mikael Svenson

Best Bets or Promoted results is a great way to give end-users the answer to their search query without the additional click to open a page or document to locate the precise information they were looking for. Bing and other search engines already have many examples of this if you for instance look for weather information or currency conversion.

Out of the box approach

Out of the box in SharePoint Online/2013 you can set up this type of functionality using Query Rules. But, there are some hitches to the default experience provided by SharePoint.

First of all, the UI is not very user friendly (unless you’re a search expert). Each best bet or promoted result, which is what it is called in SharePoint, need a separate query rule with associated trigger terms. And for each query rule you have to specify the information about the promoted result(s) itself for that rule. All in all it takes a while to get used to, set up and maintain.

Secondly you can only trigger on exact terms/phrases or the start/end of the queries, not getting partial matches or terms spread apart. For on-premises you also have the option to write regular expressions, but now you are moving away from your regular search keyword manager in hurry.

Thirdly you won’t get any lemmatization or stemming on your trigger terms. As an example the trigger term red car will match the following text with lemmatization turned on:

• red car
• reds car
• red cars
• reds cars

Using lists and one Query Rule to Rule Them All!

I’m not going to take full credit for this idea as it was introduced to me by Petter Skodvin-Hvammen. But I have taken it a bit further to get the lemmatization working. The idea is to create a regular SharePoint list using Enterprise Keywords as the trigger term matcher. An easy and familiar way to add new entries, as well as easy to maintain. Then display the best bet hits from this list in a result block at the top of the result page.

Note: In order to display result blocks your users has to be assigned Enterprise licenses.

To set this up you need only one query rule targeting the best bet list. Once it has been set up you never have to maintain it again, and good bye quirky UI. Adding new best bets is as simple as adding a new row to the best bet list. Something most SharePoint user should be familiar with.

One issue with a list like this is that you would have to query only the Enterprise Keyword column, in order not to get hits from matches in the title, description or URL fields of the list items. This is all good, but when executing a property search you get no lemmatization/stemming on your trigger terms.

My post “What makes a SharePoint column searchable?” serves as background material to my refined approach to get lemmatization going without doing a property query or matching on title and description.

By querying against the full-text index and not one property you get the added benefit of partial matches on the trigger terms. In the scenario of the trigger term being red car the best bet block rule would trigger for the below queries as well.

• red
• reds
• car
• cars
• (network OR red) AND (test OR car)

If you have multi-word trigger terms, make sure all words are pretty good/unique to avoid unwanted partial matches. Or stick with the out of the box query rule matching with a promoted result instead.

The re-written query I will use at the end will limit results to items where the content type is BestBets, and execute a full-text query on those items.

spcontenttype:bestbets {searchTerms}

What’s needed to set this up is

1. A Best Bet list hosted on a SharePoint site. Don’t use the Search Center as it’s marked as not to be indexed. A separate site collection or sub-site anywhere outside the Search Center is fine, or even as a sub-site to a Search Center as long as you make sure it’s being indexed in some way.
2. Editing or mapping of your crawled properties to reduce recall on title, description and URL columns.
3. Custom Display Template for the Best Bets
4. Custom Result Type and Result Source to target the Best Bet list
5. Query Rule with result block targeting the Best Bet list using the custom result source

Step 1 – Creating a site, columns, content type and the list

Start by creating a new site or sub-site based on the Team Site template as there is no blank site template in the SharePoint UI.

On your newly created site, add site columns as defined below and add them to a new content type named BestBets. The name in parenthesis are the internal column names.

• Title (bbTitle – Single line of text *Required)
• Description (bbDescription – Multiple lines of text)
• URL (bbLink – Single line of text)
• Start Date (bbStartDate – Date & Time)
• End Date (bbEndDate – Date & Time)
• Enterprise Keywords (enable on the list)

You might be curious as to why I’m not using a HyperLink column for the URL, and the reason is that a URL column will always be included in the recall, even though mapped to an unsearchable managed property (bug anyone?).

You can also add more columns to suit your management and display of information needs.

Next, hide the default Title column so you don’t have to deal with triggering on that column as you have no control over where it’s mapped in regards to content recall.

The reason for using site columns is to get automatic managed properties for retrieval (as mentioned in “What makes a SharePoint column searchable?”). The automatically generated managed properties will be used in the Display Template.

Once set up, your content type should look similar to the below image.

Create a new custom list on your site, turn on management of content of content types (Settings->Advanced Settings->Allow management of content types) and add the BestBets content type to your list and make it the default content type for the list. Make sure everyone has read access to this list. If not they won’t get any best bets. You may also utilize the security of list items to limit who get’s which best bet. An added bonus.

Step 2 – Add content and tune the crawled property settings

Add one best bet to your best bets list and kick off or wait for a crawl to pick up your data and create the crawled and managed properties needed.

If you issue the query: spcontenttype:bestbets rose, after the initial indexing you will get a hit on the word roses from the description column, which is undesirable, as you only want matches in the Enterprise Keywords column.

This is where it gets tricky. If you are on-premises you can edit the crawled properties in the search schema on the SSA and turn off the option to include them in the full-text index. For SharePoint Online you have to take a different approach as you cannot edit crawled properties.

To ensure you don’t get recall from unwanted text, create a new managed property which is marked as not searchable, and map all the textual ows_bbInternalName crawled properties to it.

As I’m using SPO for my prototyping I have creating a new managed property at the site collection root of my search center called BestBetsNoRecall, and mapped the following crawled properties to it:

• ows_bbDescription
• ows_bbTitle
• ows_bbUrl

The beauty and perhaps a side effect of this Prevent Recall type of managed property is that even if it’s done locally at the site collection/site, you won’t get recall for those columns if you search from another site/site collection either. You have effectively made that column non-searchable.

Troubleshooting: If you cannot get the NoRecall property to work, try to create the property and mappings at the SSA/tenant level instead.

If you want to add support for start date and end date of the best bets, map the crawled property ows_bbStartDate to the managed property RefinableDate00 and ows_bbEndDate to RefinableDate01.

Once you have completed the property mappings, go to advanced settings for your list and click the Reindex List button to make sure the best bets are re-processed on the next crawl.

Step 3- Create a Display Template

The Display Template are stored in the master page gallery, and the result type and result source has to be created at either the Search Center level or globally on the SSA/tenant in order to make them available. Basically you have to create search settings at the same level or at a parent level to where you are using them.

For the display template create a copy of the file _catalogs/masterpage/Display Templates/Search/Item_BestBet.html and name it Item_BetterBestBet.html.

Open the copied file in a text editor and edit the <title> tag to read Better Best Bet Item. Next add the following to the header of the display template:

<mso:ManagedPropertyMapping msdt:dt="string">'bbTitleOWSTEXT':'bbTitleOWSTEXT','bbDescriptionOWSMTXT':'bbDescriptionOWSMTXT','bbUrlOWSTEX':'bbUrlOWSTEXT'

</mso:ManagedPropertyMapping>

Below the line $setResultItem(itemId, ctx.CurrentItem); insert the following lines to quickly map the properties to the default ones and get the template working.

ctx.CurrentItem.Title =  $getItemValue(ctx,"bbTitleOWSTEXT").value;

ctx.CurrentItem.Description =  $getItemValue(ctx,"bbDescriptionOWSMTXT").value;

ctx.CurrentItem.Url =  $getItemValue(ctx,"bbUrlOWSTEXT").value;   

There are many ways to modify a display template to get this working, but this is one quick way to get it up and running. See SharePoint 2013 Design Manager display templates for more information on display templates. You might also want to create a custom control template to customize or remove the border around the best bet items.

Note: Make sure you publish your display template once complete to make it accessible to all users.

Step 4 – Create Result Type and Result Source to target the Best Bet list

Navigate to Manage Result Types on your Search Center (Site Settings->Search Result Types - site collection) and add a new result type with settings like depcited below. For ContentType you must use Contains any of… and not Equals, as this property is a but quirky and SPContentType is not accessible in the dropdown. A better option might be to use the ContentTypeId.

Next create a new Result Source (Site Settings->Search Result Sources) with the following properties:

• Name: Better Best Bets
• Protocol: Local SharePoint
• Type: SharePoint Search Results
• Query Transformation: {?{searchTerms} SPContentType:BestBets ((RefinableDate00<=today AND RefinableDate01>=today) OR (RefinableDate00<>"this year" AND RefinableDate01<>"this year")) }

I want to point out the RefinableDate<>”this year” parts of the query, which will include any best bet which is NOT tagged with a  start and end date. It’s a workaround to include results which don’t have a value.

Step 5 – Create a Query Rule to serve up Best Bets

Now for the final piece of the puzzle. Add a new Query Rule (Site Settings->Search Query Rules->Context=Local SharePoint )

• Rule name: Better Best Bets
• Query Conditions: Remove any conditions
• Add a Result Block
• Block title: Best Bets for “{subjectTerms}”
• Query->Select this Source-> Better Best Bets
• Items: 5
• Settings->This block is always shown above core results
• Change ranked results by changing the query: {searchTerms} -spcontenttype:bestbets

What the rule does is include up to five best bet results from the Best Bet list at the top of the results, and also exclude those items from the regular results themselves.

The End Result and next steps

Executing a search for red cars you now get a best bet at the top, even lemmatized.

Next steps would be to improve on the Control and Display Template to make it more visual appealing, and you could for example incorporate an image link or other actions.

I’m not saying it’s easy to set up if this is the first time you work with SharePoint 2013 search settings, but once set up your keyword managed for Best Bets will be eternally happy for providing him or her with a UI they actually can manage.

About the author

Mikael Svenson is a principal Consultant at Puzzlepart where he develops SharePoint Business Apps and consults on SharePoint in general. Mikael is a search enthusiast at heart having authored "Working with Microsoft FAST Search Server 2010 for SharePoint". A four time SharePoint Server MVP, Mikael puts his local community efforts into being a board member of both the Norwegian SharePoint community and SharePoint Saturday Oslo. In addition to organizing, he also speaks at conferences, events and user groups. Mikael has a blog at techmikael.blogspot.com where he mostly blogs about SharePoint and search, but you can find other nuggets there as well. You can follow @mikaelsvenson on Twitter or check out some forum goodness over at TechNet (http://social.technet.microsoft.com/profile/mikael%20svenson/)

About MVP Monday

The MVP Monday Series is created by Melissa Travers. In this series we work to provide readers with a guest post from an MVP every Monday. Melissa is a Community Program Manager, formerly known as MVP Lead, for Messaging and Collaboration (Exchange, Lync, Office 365 and SharePoint) and Microsoft Dynamics in the US. She began her career at Microsoft as an Exchange Support Engineer and has been working with the technical community in some capacity for almost a decade. In her spare time she enjoys going to the gym, shopping for handbags, watching period and fantasy dramas, and spending time with her children and miniature Dachshund. Melissa lives in North Carolina and works out of the Microsoft Charlotte office.

I've long believed that the design of your software has a profound impact on how users behave within your software. But there are two sides to this story:

• Encouraging the "right" things by making those things intentionally easy to do.

• Discouraging the "wrong" things by making those things intentionally difficult, complex, and awkward to do.

Whether the software is doing this intentionally, or completely accidentally, it's a fact of life: the path of least resistance is everyone's best friend. Learn to master this path, or others will master it for you.

For proof, consider Dan Ariely's new and amazing book, The (Honest) Truth About Dishonesty: How We Lie to Everyone – Especially Ourselves.

Indeed, let's be honest: we all lie, all the time. Not because we're bad people, mind you, but because we have to regularly lie to ourselves as a survival mechanism. You think we should be completely honest all the time? Yeah. Good luck with that.

But these healthy little white lies we learn to tell ourselves have a darker side. Have you ever heard this old adage?

One day, Peter locked himself out of his house. After a spell, the locksmith pulled up in his truck and picked the lock in about a minute.

“I was amazed at how quickly and easily this guy was able to open the door,” Peter said. The locksmith told him that locks are on doors only to keep honest people honest. One percent of people will always be honest and never steal. Another 1% will always be dishonest and always try to pick your lock and steal your television; locks won’t do much to protect you from the hardened thieves, who can get into your house if they really want to.

The purpose of locks, the locksmith said, is to protect you from the 98% of mostly honest people who might be tempted to try your door if it had no lock.

I had heard this expressed less optimistically before as

10% of people will never steal, 10% of people will always steal, and for everyone else … it depends.

The "it depends" part is crucial to understanding human nature, and that's what Ariely spends most of the book examining in various tests. If for most people, honesty depends, what exactly does it depend on? The experiments Ariely conducts prove again and again that most people will consistently and reliably cheat "just a little", to the extent that they can still consider themselves honest people. The gating factor isn't laws, penalties, or ethics. Surprisingly, that stuff has virtually no effect on behavior. What does, though, is whether they can personally still feel like they are honest people.

This is because they don't even consider it cheating – they're just taking a little extra, giving themselves a tiny break, enjoying a minor boost, because well, haven't they been working extra specially hard lately and earned it? Don't they of all people deserve something nice once in a while, and who would even miss this tiny amount? There's so much!

These little white lies are the path of least resistance. They are everywhere. If laws don't work, if ethics classes don't work, if severe penalties don't work, how do you encourage people to behave in a way that "feels" honest that is actually, you know, honest? Feelings are some pretty squishy stuff.

It's easier than you think.

My colleagues and I ran an experiment at the University of California, Los Angeles. We took a group of 450 participants, split them into two groups and set them loose on our usual matrix task. We asked half of them to recall the Ten Commandments and the other half to recall 10 books that they had read in high school.

Among the group who recalled the 10 books, we saw the typical widespread but moderate cheating. But in the group that was asked to recall the Ten Commandments, we observed no cheating whatsoever. We reran the experiment, reminding students of their schools' honor codes instead of the Ten Commandments, and we got the same result. We even reran the experiment on a group of self-declared atheists, asking them to swear on a Bible, and got the same no-cheating results yet again.

That's the good news: a simple reminder at the time of the temptation is usually all it takes for people to suddenly "remember" their honesty.

The bad news is Clippy was right.

In my experience, nobody reads manuals, nobody reads FAQs, and nobody reads tutorials. I am exaggerating a little here for effect, of course. Some A+ students will go out of their way to read these things. That's how they became A+ students, by naturally going the extra mile, and generally being the kind of users who teach themselves perfectly well without needing special resources to get there. When I say "nobody" I mean the vast overwhelming massive majority of people you would really, really want to read things like that. People who don't have the time or inclination to expend any effort at all other than the absolute minimum required, people who are most definitely not going to go the extra mile.

In other words, the whole world.

So how do you help people who, like us, just never seem to have the time to figure this stuff out becase they're, like, suuuuper busy and stuff?

You do it by showing them …

• the minumum helpful reminder
• at exactly the right time

This is what I've called the "Just In Time" theory of user behavior for years. Sure, FAQs and tutorials and help centers are great and all, but who has the time for that? We're all perpetual intermediates here, at best.

The closer you can get your software to practical, useful "Just In Time" reminders, the better you can help the users who are most in need. Not the A+ students who already read the FAQ, and studied the help center intently, but those users who never read anything. And now, thanks to Dan Ariely, I have the science to back this up. Even something as simple as putting your name on the top of a form to report auto insurance milage, rather than the bottom, resulted in a mysterious 10% increase in average miles reported. Having that little reminder right at the start that hey, your name is here on this form, inspired additional honesty. It works.

Did we use this technique on Stack Overflow and Stack Exchange? Indeed we did. Do I use this technique on Discourse? You bet, in even more places, because this is social discussion, not technical Q&A. We are rather big on civility, so we like to remind people when they post on Discourse they aren't talking to a computer or a robot, but a real person, a lot like you.

When's the natural time to remind someone of this? Not when they sign up, not when they're reading, but at the very moment they begin typing their first words in their first post. This is the moment of temptation when you might be super mega convinced that someone is Wrong on the Internet. So we put up a gentle little reminder Just In Time, right above where they are typing:

Then hopefully, as Dan Ariely showed us with honesty, this little reminder will tap into people's natural reserves of friendliness and civility, so cooler heads will prevail – and a few people are inspired to get along a little better than they did yesterday. Just because you're on the Internet doesn't mean you need to be yelling at folks 24/7.

We use this same technique a bunch of other places: if you are posting a lot but haven't set an avatar, if you are adding a new post to a particularly old conversation, if you are replying a bunch of times in the same topic, and so forth. Wherever we feel a gentle nudge might help, at the exact time the behavior is occurring.

It's important to understand that we use these reminders in Discourse not because we believe people are dumb; quite the contrary, we use them because we believe people are smart, civil, and interesting. Turns out everyone just needs to be reminded of that once in a while for it to continue to be true.

Read more of this story at Slashdot.

Read more of this story at Slashdot.

It's the 4th of July, which is the day the US attempts to forget they ever pretended to like soccer through wild displays of patriotism and fireworks. It's also a holiday, so enjoy this WTF from the archives, The Program Generator Program from 2012.

When you've been in IT for as long as Pat McGee, you're bound to have survived at least one or two COBOL horror stories. While COBOL is certainly not the worst platform to develop software on (MUMPS will most certainly hold that title through at least our grandchildren’s lifetimes), its extreme verbosity and unique idiosyncrasies make it a challenge for organizations to develop clean, maintainable code.

To COBOL's credit, it was one of the first attempts – actually, it was probably the first attempt – at self-obsolescence. Like today, the programmers of old were far too talented to meddle in trite matters like "business rules." After all, if the managers and analysts could conjure up these business rules, they could certainly write them up in a business-oriented language.  A COmmon Business-Oriented Language, if you will. Of course, we all know how that story ends, and five decades later, COBOL programmers are still paying for that arrogance today.

Back in the late '90s, Pat found himself doing exactly that. Unlike many of his colleagues, he wasn't working on any exciting Y2K bugs, but instead was tasked with something much more mundane: write a program to import several million records of COBOL-format, tape-based files into Oracle. While the hardware had long since had been upgraded to use "virtual tapes", they had not aged well.

At the heart of the system was a 10,000 line COBOL record descriptor from a design that started back in the 1960s – long before anyone had heard of 3rd Normal Form back then, much less believed it would be a good thing. Record descriptors aren't terribly difficult to follow; they mostly just map field names and data types to positions in a record.   For example, a simple descriptor would look like this:

   01 Employee-Rec.
       02 Employee-ID        PIC X(10).
       02 Employee-Name.
          03 Last-Name       PIC X(20).
          03 First-Name      PIC X(12).
          03 Middle-Init     PIC X.
       02 Position.
          03 Job-Code        PIC X(4).
          03 Department      PIC X(3).
          03 Manager-ID      PIC X(10).
       02 Hourly-Pay         PIC 9(3)V99.
       02 Past-Job-Codes.
          03 Past-Job-Code1  PIC X(4).
          03 Change-Date1.
             04 Change-Month1 PIC 99.
             04 Change-Day1   PIC 99.
             04 Change-Year1  PIC 99.
          03 Past-Job-Code2  PIC X(4).
          03 Change-Date2.
             04 Change-Month2 PIC 99.
             04 Change-Day2   PIC 99.
             04 Change-Year2  PIC 99.
          03 Past-Job-Code3  PIC X(4).
          03 Change-Date3.
             04 Change-Month3 PIC 99.
             04 Change-Day3   PIC 99.
             04 Change-Year3  PIC 99.

While a corresponding record would look like this:

ABCD123456MCGEE               JAMES       PACCTAR ABCD65432104250CLRK010195INTN010397

COBOL-format record and record descriptors don't respond to change very well, and like any piece of business software,  they are changed very often.  On the system Pat was working on, they changed very, very often. And this meant that any program that had to deal with the COBOL program's data (such as the Oracle record importer that Pat maintained), had to change just as often.

To make matters worse, Pat had absolutely no influence or visibility into the update process; he simply had to take the COBOL output and make it work. It was boring, tedious work, and Pat had all sorts of ideas on how to improve the process. Of course, it would have taken an Act of God for the customer to be willing to make any changes, and He definitely wasn't on Pat's team. What this meant was that Pat had to update the Oracle importer tool every week or so, whenever the customer made tweaks to the descriptor and corresponding files.

After the third or so week, Pat found that this simple tweak represented a whole lot hassle. The code changes were relatively easy, but they just kept coming and coming and coming. He thought about it for a bit, and figured that he could probably write something that would do exactly what he did: read the COBOL record descriptor and generate a new transfer mapping each time the format changed.

What he ended up with was a LEX/YACC grammar that described the COBOL record format, and some C sections for each parsed item. Those C sections generated a C++ program that implemented the translation. A quick compile of the C++ program and the translator program could chug along, happily reading the virtual tapes and writing text files that we could import into Oracle with the standard tools.

At least, that was the theory. As soon as he passed in the actual COBOL record descriptor, he learned that his LEX/YACC/C/C++ program couldn't quite handle all the oddities that the customer managed to include in the COBOL record descriptor. So he wrote some SED scripts to rewrite sections of the COBOL stuff before feeding it into the program. The SED script worked like a charm, and the program generator program spit out a perfect field translation map.

Then the customer requested another change. And then another. And then another. As it turned out, Pat hadn't quite managed to capture rules for all the really weird changes they could make in the COBOL record format. This meant that, almost every time the customer requested a change, Pat would then have to change the SED/LEX/YACC/C/C++ code, and then re-run it to regenerate the translator.

After a few months, Pat had re-written the SED/LEX/YACC/C/C++ code several times over, each time adding more and more validation capabilities. Despite all this, a change to his program-generator was required at least 50% of the time. And since no one else on his team was willing to learn anything about LEX and YACC, much less SED, the maintaining and executing the program generator became his primary responsibility.

The end came much sooner than Pat had expected. Not to the COBOL program or the project as a whole – just his particular assignment. He maintained ties with the folks in his group and learned that, the very next week after his last, the SED/LEX/YACC/C/C++ program stopped working. In response, the whole project was shut down for a month while someone wrote another C++ program, by hand, to do the translation. Of course, that person got stuck spending a couple of days each week updating the program.

But at least he had job security.

[Advertisement] Have you seen BuildMaster 4.3 yet? Lots of new features to make continuous delivery even easier; deploy builds from TeamCity (and other CI) to your own servers, the cloud, and more.

Today I received an email notifying me that I have been awarded the Microsoft “MVP” award. An excerpt from Microsoft’s website states

“The Microsoft Most Valuable Professional (MVP) Award is our way of saying thank you to exceptional, independent community leaders who share their passion, technical expertise, and real-world knowledge of Microsoft products with others.”  reference

It is a huge honor to be given this award just as it has been extremely humbling to be nominated for this award by friends within the SQL Server community. I feel very lucky to be part of the awesome SQL Server community, to have made friends all over the globe and now to be part of the MVP community.

Little did I know how much my life was going to change when on 10/28/2008 my boss told me that our training budget got reinstated and did I want to go to the PASS Summit. I quickly registered and when I arrived in Seattle and felt the energy of our community I was hooked. During that Summit I was attending a session on DMV’s when Grant Fritchey shared with the audience about SQL Saturday’s.  I took note of it, attended the next two in the South East USA and then started speaking at them.

My passion and desire for learning never let up. I have attended every PASS Summit since, attended or spoke at dozens of events the past few years, contributed on forums, written blogs, been a PASS Regional Mentor, Chapter Leader, PASS Big Brother, etc.  As much as I try to give back I still feel like I am receiving more from this community then I can contribute. The friendships I have made and the lives I have been able to touch make all the time and energy put into it worth it.

There are so many others deserving of this award world wide. If you know someone who consistently gives of themselves back to the Microsoft community please take time to nominate them today.

Even though way early in my career I had a SQL Server MVP as a mentor (by pure chance), it wasn’t until 2008-2009 that I sort of researched a bit more about the program, and what it really meant. I was beginning to understand how powerful networking in our community really is, and how attending and participating on SQL Saturdays and other events would make all the difference in my career.

I thought having that “badge” would be cool, but I really didn’t know how to get one.

I remember I asked K. Brian Kelley [twitter|blog], somebody who I consider a friend (even though I’ve never met him in person) how could I become a SQL Server MVP. He mentioned helping others through any vehicle (forums like MSDN’s, SQL Server Central, etc.) would help, but that I really needed to ask myself what it would mean to me to be an MVP. And I often wondered, really, what it all meant. Was it just an award given to somebody who would answer questions from others relentlessly? Would it be just some award given to members of this semi-secret inner circle?

Over time I became more and more involved with the community. I started speaking, and continued helping others, mostly through #SQLHelp on Twitter (funny how a hashtag changes your life!). I was seriously turned off by forums (still am) and didn’t care much about blogging very frequently. After all, other folks out there blog about just about every single topic on SQL Server, and I didn’t one to be just one more guy. Yet I blogged, but it was only when I thought my post could really make a difference to somebody facing a similar situation or working with an under-documented feature.

The fact that I tweeted incessantly helped get my name out there, and I kept being accepted to speak at SQL Saturdays and even the PASS Summit. Then I got my MCM certification, which really gave my name some exposure out there.

I was really becoming influential.

I eventually delivered pre-conferences at premier events like SQLBits, spoke at massive events like DevConnections and TechEd, and started toying with the idea of helping PASS even more. Then, with the help of Robert Davis [twitter|blog], and also from K. Brian Kelley himself, we launched the Security Virtual Chapter for PASS.

I was doing all this high profile stuff, which was fantastic, but I never lost sight of my roots: volunteering and helping others. I kept signing up to help PASS as member of the Program Committee, something that I have proudly done for 3 years in a row (and I’m not going to discuss current controversy on the subject here), helped moderate Lightning Talks at the Summit even when I wasn’t chosen as a speaker, spent hours talking to people at the Community Zone at the Summit and the BA Conference, moderated online web casts for the Security VC, and helped moderate online events like 24 Hours of PASS. And, all along, learning and sharing on #SQLHelp.

All the little things that not a lot of people sign up for (or avoid altogether), I happily help with.

Yet “MVPness” is not something that I feel entitled to. Not something that I demanded every quarter. I find it funny that I look forward to the MVP Summit taking place in Seattle because that means I get to hang out with that group – I have met a LOT of them along the way, and some of them I consider extremely close friends. These are people that I deeply respect. Still, I am not expecting an award by association.

I do the things I do for the SQL Server community because I love it, not because I want another three letter acronym on my resume.

Earlier on this year I was nominated for the award by several very influential folks in the community (Thank you!!). I seriously thought I wasn’t going to get it, because I thought I hadn’t done enough for the community to deserve it.

Today, I am _insanely_ honored to be recognized for my contributions to the community.

This is one badge I will wear with pride.

Today I was awarded the SQL Server MVP award.

And now the real work begins.

This award is yours, #SQLFamily, and also goes to the unsung heroes of this community that have been working hard for YEARS and still have not been awarded; you know who you are. Never give up, my friends. Stay true to your north.