Performing Fast Searches of Query Plans

Performing Fast Searches of Query Plans I’ve made blog posts in the past on using the exist() method to search for query plans with specific attributes (e.g., Find Cached Query Plans By Index Name). Another good aspect of the exist() method is that it is a much faster way to search for a specific string inside of a query plan than trying to roll your own search by converting to text. I decided to write this post after reading a
read more

Using Dynamic Values in XMLA

Using Dynamic Values in XMLA A question was raised on Twitter today via the #sqlhelp hash tag about passing parameter values to XMLA for a backup script. The popular answer at the time I saw it was to dynamiclly create and drop a SQL job to run the script every time you need it to run. I proposed a different solution, The Question The following request was sent in a series of tweets: Is there a way to pass variables
read more

T-SQL Tuesday #17: Using Apply to Demystify Deadlocks

T-SQL Tuesday #17: Using Apply to Demystify Deadlocks This blog entry is participating in T-SQL Tuesday #17, hosted this month by Matt Velic (Blog|@mvelic). You are invited to visit his blog to join the blog party. You are welcome to write your own participating blog post for the party or just to read more blogs participating in this month’s theme: APPLY Knowledge. For my part in this month’s T-SQL Tuesday, I’m going to talk about a script I wrote recently
read more

T-SQL Tuesday #16: Using Aggregate Functions in XML

T-SQL Tuesday #16: Using Aggregate Functions in XML This blog entry is participating in T-SQL Tuesday #16, hosted this month by Jes Schultz Borland (Blog|@grrl_geek). You are invited to visit her blog to join the blog party. You are welcome to write your own participating blog post for the party or just to read more blogs participating in this month’s theme: Aggregation. When I first heard the topic, I was worried that I would not be able to come up
read more

Find Cached Query Plans By Index Name

Find Cached Query Plans By Index Name A following question came in via the #sqlhelp hash tag on Twitter: Trying to find the plans using a specific index in the plan cache using XQuery – any ideas out there? #sqlhelp I replied with a little information but felt the full answer could not be shared 140 characters at a time. So here is the full query that I wrote. I am using sql:variable() to pass the index name into the
read more

T-SQL Tuesday #010: Indexes: To Be or Not To Be (a B-tree)

T-SQL Tuesday #010: Indexes: To Be or Not To Be (a B-tree) This blog entry is participating in T-SQL Tuesday #010, hosted this month by Michael J Swart (Blog|@MJSwart). You are invited to visit his blog to join the blog party. You are welcome to write your own participating blog post for the party or just to read more blogs participating in this month’s topic: indexes. For my part in this month’s T-SQL Tuesday, I’m going to talk about index
read more

Transferring Logins to a Database Mirror

I recently discovered that my book (Pro SQL Server 2008 Mirroring) has an older version of the script to copy logins to the mirror partner for database mirroring. The final version of the script handles SQL logins as well as Windows groups and users. I’m really disappointed that the newer script didn’t make it into the book because I put a lot of work into it to make sure it handled SQL logins. There are two main issues with transferring
read more

Building HTML Emails With SQL Server and XML

I’ve written a lot of custom reports in my days that output data as an HTML string and then sends it via email. It makes for an easy to read, colorful report, and PM’s and business folks love easy to read, colorful reports. Until recently, I used simple string concatenation for building my HTML strings. Simple string concatenation is not always simple as it can yield unexpected results due to data type conversions. I knew there had to be a
read more