Wednesday, March 1, 2017

The Way to Love by Anthony de Mello

The Way to LoveThe Way to Love by Anthony de Mello

My rating: 4 of 5 stars

A book to be read slowly and thoughtfully. Each chapter asks the reader to be different than almost everyone they've met or ever will meet; to leave the world of desire and attachment behind and enter the world with a fresh perspective. Armed with this new perspective. de Mello asserts that true love for others can now begin to take hold and it will be totally different than any love previously experienced. Only through this rebirth can true love and happiness be experienced.

View all my reviews

Tuesday, February 28, 2017

SQL Replication on a Table That Contains a SQL_Variant Datatype

I recently tasked with setting up Transactional Replication in SQL 2008 R2. While this in and of itself isn't necessarily complicated, I did run into an issue that kept the initial snapshot from being created. One of the articles (tables) in the publication had two columns that were defined with a SQL_Variant type and the snapshot agent could not convert those columns to create the snapshot. I tried the various column convert settings in the article properties, but they did not help. Only after changing the snapshot format from Native SQL Server to Character was the snapshot able to be created. All of the other article properties were left as default. Since I want all the other articles in this publication to retain the Native SQL Server snapshot format, I created a specific publication for tables that contain SQL_Variant columns.

Wednesday, February 15, 2017

Memoirs of Hadrian by Marguerite Yourcenar, Grace Frick (Translation) << Read

Memoirs of HadrianMemoirs of Hadrian by Marguerite Yourcenar

My rating: 3 of 5 stars

A multi-faceted look into the life of one of the 5 good emperors who ruled Rome for close to 100 years. This book details Hadrian's rise to power through the military and civil administration, his early years as emperor of "the world", middle age (where he loved and lost), and his final years as the most powerful man in the world. Two themes prevalent through Hadrian's life (with the exception of his final years) were the pursuit of pleasure and meaning, which I believe with his Hellenistic leanings, was trying to find one through the other. Throughout the book his actions probably seem strange or base when viewed from a Christian morality that had not yet permeated Western civilization, but in the context of the times, I can only guess that his actions were seen as normal for someone in the upper class of Rome.

View all my reviews

Monday, February 13, 2017

The Discourses by Epictetus << Read

The DiscoursesThe Discourses by Epictetus

My rating: 5 of 5 stars

There's so much to learn from the Roman Stoics that a review here wouldn't come close to capturing the essence of what this book is about. Those who do read it will be amazed at the amount of 2000 year old wisdom they can apply to their lives today.

View all my reviews

Friday, November 18, 2016

Query for Change Data Capture (CDC) tables and columns

Real quickly, I was in need of a query that would give me a listing of the tables and columns involved in Change Data Capture (CDC). I wanted to see the tracked columns in one column to the right of the table name so I utilized FOR XML PATH. Below is what I came up with.

SELECT OBJECT_NAME(source_object_id) [Table Name],
      (SELECT name + ',' FROM sys.columns SC WHERE SC.object_id = CT.object_id AND name NOT LIKE '__$%' FOR XML PATH('')) [Columns],
 FROM CDC.change_tables CT

It's certainly not perfect but it should give you a good start if you are ever in need of such a query.

Tuesday, February 23, 2016

List of Users Who Recently Changed Their Password

There may come a time when you need to generate a list of SQL logins and the last time their password was changed. By using the LOGINPROPERTY of the name in sys.server_principals we can generate such a list.

Note: This will only work with SQL logins.

The following query will generate the results below.

            ,LOGINPROPERTY(,N'PasswordLastSetTime') 'LastPWReset'
FROM    sys.server_principals SP
WHERE   SP.[type] = 'S'

Thursday, January 14, 2016

Using Extended Events to Find the Actual Execution Plan for a Table Valued Function

While finding the estimated Execution Plan for a Table Valued Function (TVF) isn’t all that difficult, getting to the actual Execution Plan is somewhat of a challenge. Take this example from the AdventureWorks database.
SELECT * FROM ufnGetContactInformation(3)
By clicking on the Display Estimated Execution Plan we get the following:


To get the actual plan, make sure the following option is selected and execute the query.


While the estimated gives us all kinds of information, the actual plan keeps the underlying operations hidden in favor of a Clustered Index Scan and a TVF operator. This isn’t very useful when it comes to troubleshooting performance issues especially if your query has multi-table joins to the TVF.
Thankfully, this is where Extended Events (EE) comes into play. By using EE, we can capture the Post Execution Showplan that will give us the actual full plan behind the Clustered Index Scan and TVF operators.
We’ll use the following code to setup the EE:
ADD EVENT sqlserver.query_post_execution_showplan (
    ACTION (sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text)
    WHERE  (sqlserver.session_nt_user = N'<Your User Name>'))
ADD TARGET package0.ring_buffer

NOTE: This EE can several decrease performance if used on a high transaction environment. It is not recommended to use this in a production environment.
This session will capture the Showplan XML event, as well as the database name, plan handle, and SQL text of the query. The session is filtered on whatever name you choose (I used my NT login name). Finally, the session sends its output to the ring buffer so we can watch it live in SSMS.
Once the session has been created and turned on, right click on the EE and choose the “Watch Live Data” option.

And the following window will open in SSMS.

Now, in another SSMS window, we’ll execute the query with the TVF again and see the following data flow into the live data viewer.

By clicking on the Query Plan tab, we’ll finally see the full execution plan. Since this TVF has several code blocks in it, there are several plans, but I’ll only show the main one here with the knowledge there are several plans.

Check out the following posts for further reading on using Extended Events and the Query_Post_Execution_Showplan event:
Impact of the query_post_execution_showplan Extended Event in SQL Server 2012
Getting Started with Extended Events in SQL Server 2012