30Apr 2012 by Grant Fritchey No Comments

Changing DB_CHAIN Can Clear the Plan Cache

T-SQL
If you make changes to the settings of a database, it can cause the procedure cache to be cleared. Microsoft has documented changes that cause this for all procs within a database (scroll down to just above the examples). But guess what, if you change the DB_CHAINING option, it clears the cache too. Here’s a sample script to show it in action. ALTER DATABASE Testing SET DB_CHAINING OFF; GO CREATE PROCEDURE x AS SELECT * FROM test.dbo.A AS a2; GO CREATE PROCEDURE y AS SELECT * FROM dbo.Table_1 AS t; GO EXEC dbo.x; EXEC dbo.y; SELECT deqs.creation_time FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest WHERE dest.text LIKE 'CREATE PROCEDURE x%' OR dest.text LIKE 'CREATE PROCEDURE y%'; ALTER DATABASE Testing SET DB_CHAINING ON; SELECT deqs.creation_time FROM sys.dm_exec_query_stats AS deqs…
Read More
26Apr 2012 by Grant Fritchey 5 Comments

AlwaysOn Failover Bug

Uncategorized
I just found a little bug in AlwaysOn. It’s actually not that big a deal, but it’s interesting. In a nutshell, if you have a table with an IDENTITY seed less than 1000, when you do a failover the table is reseeded to 1001. The steps to reproduce are posted on this Connect item. If you have feedback on this, please post it directly to the Connect item since I don’t think Microsoft reads my blog much.
Read More
23Apr 2012 by Grant Fritchey 47 Comments

Comments on Acceptable Behavior

Misc
Here’s the situation. We’re absolutely falling down as men and professionals. Ladies, feel free to read on and comment, but I’m talking to the guys. This blog post has been percolating in my mind for quite some time, but a few new stories have brought it to the fore. The most recent was when I heard that, in front of multiple other male MVPs, a female peer was propositioned using language that I wouldn’t condone when speaking to an animal, let alone a peer. And that’s the point, these women are peers. Yes, they happen to be female, but more importantly, they’re our peers. And you know who I’m most upset with? No, not the individual who acted like a jerk. There’s always a few of those around. No, I’m…
Read More
02Apr 2012 by Grant Fritchey 4 Comments

SQL Server Log Backups

T-SQL
While presenting a session on Common Backup Problems both at SQL Saturday in Orange County and at SQL Connections in Las Vegas, questions came up regarding whether, when, and how your databases should be configured in FULL recovery mode and what that means for your recovery plans. To boil the questions down to as simple a single statement as possible: Should your database be in FULL recovery mode at all times which requires you to run log backups on a regular schedule? The same question came up while Brad McGehee(blog|twitter) was presenting a session on using the settings on your SQL Server instance and your database to ensure you got proper performance. We both answered the question the same way, but our emphasis was slightly different. Brad’s answer was that,…
Read More