SQLU HA/DR Week – Database Mirroring Performance Counters

SQLU HA/DR Week – Database Mirroring Performance Counters Database mirroring debuted in SQL Server 2005 and had eleven performance counters for monitoring mirroring. There were ten new performance counters released with SQL Server 2008. We now have twenty-one performance counters to use for monitoring database mirroring. A list with a brief description of each can be found in Books Online or online at http://msdn.microsoft.com/en-us/library/ms189931.aspx. Many people find themselves without a clear understanding of what the counters mean and how to
read more

SQLU DBA Week – Be an Efficient DBA

SQLU DBA Week – Be an Efficient DBA Welcome back for another day of Administration Week 1 for SQL University. This is day four, and I want to focus in on a term that I hear quite a bit, lazy dba. DBAs are anything but lazy, but the term seems to have come about by the way that we are always lookign for the simplest and quickest way to do something. That’s not being lazy, that’s being efficient. Don’t be
read more

Does a Mirroring Failover Clear the Procedure Cache?

Does a Mirroring Failover Clear the Procedure Cache? I saw a question on the MSDN forums asking what happens to the procedure cache (also known as the plan cache) on the principal and mirror when a failover occurs. My reply was starting to get long, so I decided to move it out to a blog post. The original question poses the theory that the procedure cache is flushed on both mirror and principal when a failover occurs. How Mirroring Transfers
read more

Find Cached Query Plans By Index Name

The 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 exist() method. I’m using a relative XML
read more

Finding the Text of the Previously Executed Query

Finding the Text of the Previously Executed Query This interesting question was posed on an internal discussion group today: Hello All, I apologize for reaching to this alias but I have not found any answers from another SQL alias. I have a situation with a customer that is looking to retrieve the last SQL statement executed in a stored procedure. For example, take the following stored procedure with 3 TSQL: USE [AdventureWorks2008R2] GO /****** Object: StoredProcedure [dbo].[uspTest] Script Date: 11/17/2010
read more

T-SQL Tuesday #011 – Top 5 Myths of Database Mirroring

T-SQL Tuesday #011- Top 5 Myths of Database Mirroring This blog entry is participating in T-SQL Tuesday #011, hosted this month by Sankar “@SankarReddy13” Reddy(Blog|Twitter). 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: Misconceptions in SQL Server. For my part in this month’s T-SQL Tuesday, I will be discussing my top 5
read more

Advanced Tuning Presentation

Advanced Tuning Presentation Today was my presentation for the West Michigan SQL Server User Group, a SQL PASS chapter. I was presenting via Live Meeting, and it was open for anyone to attend. The files have been uploaded and are available for download Advanced Tuning PowerPoint Slide Deck (.pptx, 1.79 MB) Live Meeting Recording (.zip, 46.03 MB) SQL Scripts: TopQueriesByCPU (.sql, 576 Bytes) dba_RunCPUUtilizationTrace (.sql, 9.48 KB) dba_CheckReplicationLatency (.sql, 3.31 KB)
read more

Looking forward to Optimize for Ad hoc Workloads in Sql Server 2008

One of the features of SQL Server 2008 that I think my application will be able to take advantage of once we migrate is Optimize for Ad hoc Workloads. In short, what this feature does is not store the cached plan the first time it is used. It stores just the plan hash that it can use to identify when a matching plan is created the second time. When the plan is created a second time, it is cached. This
read more

T-SQL Tuesday #004: IO — Where Are My TempDB Objects?

T-SQL Tuesday #004: IO — Where Are My TempDB Objects? This blog entry is participating in T-SQL Tuesday #004, hosted this month by Mike Walsh. You are invited to visit his bloG to join the party and read more blogs participating in this month’s theme: IO. The question was raised recently in a discussion group about how to tell if your temporary tables and table variables were being maintained in memory or on disk. Here is my attempt to solve
read more

T-SQL Tuesday #004: IO — Where Are My TempDB Objects?

T-SQL Tuesday #004: IO — Where Are My TempDB Objects? This blog entry is participating in T-SQL Tuesday #004, hosted this month by Mike Walsh. You are invited to visit his blog to join the party and read more blogs participating in this month’s theme: IO. The question was raised recently in a discussion group about how to tell if your temporary tables and table variables were being maintained in memory or on disk. Here is my attempt to solve
read more