Why I hate the ring_buffer target in Extended Events

I’ve had so many questions about the same problem with the ring_buffer target in Extended Events lately that I figured I would write a blog post that explains all the information I teach about the ring_buffer target and the problems associated with it.  Since the release of SQL Server 2012, and the new UI for […]

Looking at read_microsec in sys.dm_os_buffer_descriptors in SQL Server 2012

Last week someone sent Kimberly an email asking what the read_microsec column in the sys.dm_os_buffer_descriptors DMV in SQL Server 2012 showed. The email was passed around our team and to be honest it was the first time I’d even heard of the column existing. The questioner also wanted to know if/how the read_microsec column be […]

Mapping wait types in dm_os_wait_stats to Extended Events

A few months back I received an email from a member of the community that was trying to filter the sqlos.wait_info event for some of the wait types that are filtered out by Glenn’s diagnostic queries, and to their dismay wasn’t able to find the specific wait types in the wait_types map in sys.dm_xe_map_values.  This […]

Logging Extended Events changes to the ERRORLOG

A question came up in class today about the difference between SQL Trace and Extended Events for logging information to the ERRORLOG file. Joe and I have both written about the observer overhead of Trace and Extended Events in the past (Observer Overhead and Wait Type Symptoms and Measuring “Observer Overhead” of SQL Trace vs. […]

SQL Server 2012 Extended Events Add-in Updates

Back in July I blogged about the SQL Server 2012 Extended Events Add-in to Manage 2008/R2 Instances. Unfortunately, despite having multiple beta testers using the add-in before the release, I started getting reports of the add-in crashing Management Studio. A new version of the add-in is available for download (http://www.sqlskills.com/free-tools/sql-server-2012-extended-events-add-in/) that addresses this and one […]

Using ‘dbghelp.dll’ version ’4.0.5′ error fixed in SQL Server 2012 SP1 CU6

If you use Extended Events you may have noticed that the ERRORLOG file gets bloated with messages like: Using ‘dbghelp.dll’ version ’4.0.5′ every time you query sys.dm_xe_sessions or read a file using Transact-SQL and the sys.fn_xe_file_target_read_file() table-valued function. This issue can be especially problematic on SharePoint 2013 installations, where a timer job queries Extended Events […]

ALTER DATABASE failed. The default collation of database ‘%.*ls’ cannot be set to %.*ls.

Last week I was working with a client on upgrading one of their systems from SQL Server 2000 to SQL Server 2012, while also performing a collation change of the database and all of the table columns from Latin1_General_BIN to SQL_Latin1_General_CP1_CI_AS.  What started out as a straight forward upgrade, this actually became quite a challenge.  […]

Updated Availability Group Demonstrator

Since the first release of the SQLskills Availability Group Demonstrator, I’ve had an number of requests to add the ability to enable MultiSubnetFailover in the connection string, and to specify a Timeout value. I made these changes months ago but never got around to actually releasing the newer build on my blog. However, after releasing […]

SQL Server 2012 Extended Events Add-in to Manage 2008/R2 Instances

Extended Events are a powerful new way of troubleshooting problems with SQL Server, and the addition of UI support in SQL Server 2012 Management Studio has helped increase the awareness and usage of this feature. One short-coming is that the new UI for Extended Events only works for SQL Server 2012 instances leaving administrators that […]

Synchronize Availability Group Logins and Jobs

An important part of ensuring application functionality with Availability Groups is manually maintaining any uncontained objects across each of the replicas in the Availability Group. As more of our clients upgrade to SQL Server 2012 and implement Availability Groups, we’ve had to face the challenges of maintaining uncontained objects across replicas multiple times and there […]

Implicit Conversions that cause Index Scans

Implicit conversions seem to be a growing trend in the SQL Server performance tuning work that I’ve been engaged in recently, and I’ve blogged in the past about ways to identify when implicit conversions are occurring using the plan cache. For those of you who don’t know, implicit conversions occur whenever data with two different […]

Finding Key Lookups inside the Plan Cache

This is actually a blog post I thought I’d written more than two years ago, but this morning when I went looking for it after receiving a question by email  I realized that I’ve never blogged about this before.  At PASS Summit 2010 I presented a session on performance tuning SQL Server by digging into […]

Tracking SQL Server Database Usage

One of the challenges with inheriting an existing set of SQL Servers and databases when you start a new job can be determining which of the databases are actively being used or not, especially on development and testing systems where there may be multiple copies of the same database that were used by different projects […]

Common Table Expressions (CTEs), Window Functions, and Views

Common Table Expressions (CTEs), window functions, and views are all common things in SQL Server development these days. When CTEs and window functions were introduced in SQL Server 2005, a lot of developers jumped in and began using them to solve problems and simplify code. While these tools can be a great benefit in SQL […]

Performance Tuning SQL Server on Windows Server 2012

Properly configuring Windows and SQL Server to get the best performance from your server hardware is an important task for database administrators.  There is a lot of information available online with different recommendations about how to configure your servers for the best performance.  The challenge is knowing what recommendations are correct and what advice you […]

Migrating Legacy LOB Data Types to Current Ones – A Big Gotcha!

Recently, Erin (Blog|Twitter) and I encountered a bug in SQL Server that can affect some scenarios in a very negative manner.  When working with a database that was designed in SQL Server 2000 using the legacy LOB data types, text, ntext, and image, it is possible to encounter a problem when the ‘text in row’ […]

CPU Ready Time in VMware and How to Interpret its Real Meaning

(If you’re having persistent problems with your virtual machine configurations and SQL Server performance, Jonathan can help you fix it – fast! Click here for details.) In the last month I have had to explain how to interpret CPU Ready Time information for SQL Server VMs running on VMware to a number of people. The […]

Clustering SQL Server on Virtual Machines (Round 2)

Recently there was lengthy discussion on the #sqlhelp hash tag on Twitter about clustering SQL Server on VMs and whether or not that was a good idea or not. Two years ago I first blogged about this same topic on my blog post, Some Thoughts on Clustering SQL Server Virtual Machines. If you haven’t read […]

New Article: Performance Problems with SQL Server 2012 Enterprise Edition Under CAL Licensing

My latest article on SQLPerformance.com was published yesterday and highlights the potential for performance problems while using the default configuration of SQL Server 2012 using the Enterprise Server+CAL upgrade licensing under Software Assurance. http://www.sqlperformance.com/2012/11/system-configuration/2012-cal-problems

New Article: Tracking Database File AutoGrowth with Event Notifications

My latest article on SQL Server Central was published live today.  This article shows how to use Event Notifications to monitor and alert on database file auto growth events inside of SQL Server and is another edition to the series I committed to writing on using Event Notifications in SQL Server to automate common DBA […]

New Article on SQLPerformance.com comparing “Observer Overhead” of Trace vs Extended Events

I have been so busy this week that I didn’t get a chance to blog about this yesterday when it happened, but I had a new article published on SQLPerformance.com that compares the performance impact or "observer overhead" of using SQL Trace and Extended Events.  I had a lot of fun running different benchmarks for […]

How useful are query_hash and query_plan_hash for troubleshooting?

After my last post, I was contacted by email about the usefulness of query_hash in actual troubleshooting scenarios since the query_hash value is based on the statement and not the database in a system.  It just happened that the example template from SQL Server 2012 that I showed had the query_hash included in it’s definition.  […]

Customizing Extended Events Templates in SQL Server 2012

One of the features of the Extended Events UI in SQL Server 2012 is the ability to export an event session definition out to a template for future reuse.  This can be accomplished through the right-click menu for an event session and then by selecting the Export Session menu item as shown in Figure 1. […]

Announcing the SQLskills Availability Group Demonstrator

This spring at SQL Connections, I presented a pre-conference seminar with Kimberly on the new features in SQL Server 2012, including Availability Groups.  While on my flight to Las Vegas, I was working on an application in C# that motivated me to write a new demonstrator application for use with Availability Groups.  I spent a […]

Looking at Non-Updating Updates with Extended Events

For a few weeks now, there has been a long running thread on SQL Server Central about UPDATE operations that don't perform any changes because the statement sets a column to the same value as already exists in the row.  While I believe that one of the posters is incredibly snarky and outright rude to […]

The Availability Group Wizard and the HADR Seeding Test Database

If you are creating an Availability Group in SQL Server 2012 using the New Availability Group Wizard in SQL Server Management Studio, you might not realize some of the background tasks that the wizard performs when it performs its validation steps.  One of those steps is to create a temporary database on the current server […]

Tracking Extended Events for a SQL Agent Job

This blog post is courtesy of a question I received from Chris Adkin (Twitter) by email.  Chris was trying to use Extended Events to track session level wait information, but he had a new twist that I’ve never been asked about before, he wanted to track all of the waits for a specific SQL Agent […]

Identifying High Compile Time Statements from the Plan Cache

If you don’t already know it, I love query the plan cache in SQL Server to identify problems that might exist in a server.  I’ve blogged a number of ways to use plan cache data to identify and diagnose problems the last few years: Finding what queries in the plan cache use a specific index […]

New Article on Tracking Tempdb Contention in SQL Server 2012 with Extended Events

My latest article on Simple-Talk was published recently.  In this article I dig into how to track allocation bitmap contention in tempdb in SQL Server 2012 using Extended Events.  The method described in this article redefines how you troubleshoot this type of problem, leveraging the advanced diagnostic capabilities of Extended Events to track the problem. […]

Missing options in the SSMS Extended Events UI due to poor design

While I was writing about the new features of the Extended Events UI in SQL Server 2012, I realized that I hadn’t written about the TRACK_CAUSALITY session option in the New Session dialog.  I thought I was covering everything, but maybe I missed it so I went back and looked at my screenshots and then […]

AlwaysOn is a marketing brand, not a feature in SQL Server 2012

With the release of SQL Server 2012, one of the big marketing pushes by Microsoft was the new AlwaysOn features that were a part of the release.  However, as a part of this push, a lot of confusion has occurred where articles, blogs, tweets, and forum posts refer to AlwaysOn as if it were a […]

New features of ALTER SERVER CONFIGURATION in SQL Server 2012

While working on a chapter I am writing, I was going through the ALTER SERVER CONFIGURATION topic in the Books Online and noticed some really useful changes in the syntax that I hadn’t seen before.  Starting in SQL Server 2008 R2, ALTER SERVER CONFIGURATION was introduced to allow for process affinity configuration and to replace […]

Resolving Error 33204 SQL Server Audit could not write to the security log

This topic came up on a forum post recently, and after responding a couple of times to the thread I had to jump into one of my VMs to figure out what exactly the problem was.  If you use Server Audits in SQL Server, one of the features that you might be interested in is […]

Identifying External Memory Pressure with dm_os_ring_buffers and RING_BUFFER_RESOURCE_MONITOR

In my last blog post, I provided a script that queried the XML for the resource monitor ring buffer entries in sys.dm_os_ring_buffers to identify when the system was under memory pressure as a result of not having the ‘max server memory’ sp_configure option set correctly for the server. Since that post has gone live, I’ve […]

Wow… An online calculator to misconfigure your SQL Server memory!

Properly configuring SQL Server is an important factor in having a stable environment that performs well.  While there is a lot of good guidance available for how to best configure SQL Server, the specifics of any given implementation is a very big “It Depends…” and one of the things that we do in our Immersion […]

Event Notifications vs Extended Events

I’ve been asked questions about these two features a number of times in the last year where the specific concepts for them have been severely confused.  Since I have used the same information each time I’ve answered these questions I figured it would be worth blogging about here as a reference as well. Event Notifications […]

Understanding the sql_text Action in Extended Events

The sqlserver.sql_text action in Extended Events is one of the worst named objects in Extended Events.  The name suggests that you are going to get back the sql_text that triggered the event being collected to fire, but in reality this is not the case.  I pressed internally with Microsoft to have this action renamed to […]

Converting SQL Trace to Extended Events in SQL Server 2012

With the growing popularity of Extended Events in SQL Server 2012 with the UI enhancements that I’ve blogged about on a number of posts (SQL Server 2012 Extended Events Update – 1- Introducing the SSMS User Interface, SQL Server 2012 Extended Events Update – 2 – The SSMS UI Part 2, SQL Server 2012 Extended […]

How much memory does my SQL Server actually need?

Traditionally questions about how much memory SQL Server needs were aimed at how to appropriately set the 'max server memory' sp_configure option in SQL Server, and in my book the recommendation that I make is to reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 […]

Changes to Extended Events Permission Requirements in SQL Server 2012

This past week at the SQL Server Connections Conference in Las Vegas, I was asked about the permissions required for managing Extended Event Sessions in SQL Server.  In SQL Server 2008 and 2008R2, using Extended Events required the CONTROL SERVER permission for the instance of SQL Server (http://msdn.microsoft.com/en-us/library/bb677289(v=sql.105).aspx).  In SQL Server 2012, a much more […]

SQL Failover Cluster without Shared Storage – SQL Server 2012 and SMB

While teaching last week at our Tampa Immersion Event, I mentioned the support for SMB as a storage in SQL Server 2012, which is allows you to create a Failover Cluster without shared storage on a SAN, and is documented in the following Books Online topics. Install SQL Server with SMB fileshare as a storage […]

SQL Server 2012 Early Adoption Cook Book Content and Videos

Last week during the MVP Summit, Joe (Blog | Twitter) and I spent the days working at Microsoft teaching and recording SQL Server 2012 content for the Developer Platform Evangelism team.  Earlier today, the videos we recorded of the SQL Server 2012 content were posted on the SQL Server 2012 Early Adoption Cookbook site along […]

SQL Server 2012 Extended Events Update – 3 – Viewing Target Data

In SQL Server 2012 there are number of new ways to view target data generated by Extended Events Sessions, including a live streaming view as the events actually generate from the server, similar to the way SQL Server Profiler functions.  For this blog post, I am going to use the Query Detail Tracking template that […]

Extended Events PerfObject Events

SQL Server 2008 R2 Service Pack 1 provides a new set of Events in Extended Events to collect performance counter data from the Windows OS that would be really useful to monitoring SQL Server.  The first place I can find that they were mentioned is on a blog post by Mark Weber, a PFE for […]

SQL Server 2012 Extended Events Update – 2 – The SSMS UI Part 2

In the first post in this series, SQL Server 2012 Extended Events Update – 1- Introducing the SSMS User Interface, we looked at how to use the New Session Wizard in SQL Server 2012 to define an Event Session.  In this post we’ll compare the Wizard to the standard New Event Session dialog that can […]

SQL Server 2012 Extended Events Update – 1- Introducing the SSMS User Interface

For the most part I have been relatively quiet about the coming changes in SQL Server 2012 with regards to Extended Events.  Primarily this has been to allow the new features of the product to become fully baked to ensure that the information would continue to be applicable as the product lifecycle progressed, and there […]

Does filing bugs on Microsoft Connect for SQL Server work?

In a word; YES! In a lot more words, not always in the way that we want it to, but there are plenty of cases where it actually works and changes are made to the product as a result. Now with that said, it doesn’t work all the time, and it helps to realize that […]

Workaround for Bug in Activity Tracking Event Session Template in 2012 RC0

In SQL Server 2012 RC0 there are a number of event session templates provided that make creating a commonly used session easier using the Event Session Wizard in SQL Server Management Studio.  One of these has a bug in it’s definition XML file that was filed in the following connect item: https://connect.microsoft.com/SQLServer/feedback/details/705840/the-object-sqlserver-event-sequence-does-not-exist#tabs If you attempt […]

Tracking Problematic Pages Splits in SQL Server 2012 Extended Events – No Really This Time!

Just over a year ago I blogged about the enhancements that were made to the sqlserver.page_split Event in SQL Server 2012 to make it easier to identify what the splitting object was and the type of split that was being performed.  Sadly what I discovered writing that post was that even with the extra information […]

Finding Replication Schema Options

This afternoon, Orson Weston (Twitter), asked how to find the difference between two binary sets of schema options for replication on the #SQLHelp hash tag on twitter.  The valid values for the @schema_options parameter in replication are documented in the BOL Topic for sp_addarticle (http://msdn.microsoft.com/en-us/library/ms173857.aspx).  However, just having a table of values doesn’t really help […]

New AlwaysOn_health Extended Events Session in SQL Server 2012 RC0

While setting up my new Availability Group using SQL Server 2012 RC0 tonight, I noticed an interesting new addition to Extended Events associated with Availability Group configuration in the Release Candidate.  When you setup an Availability Group in RC0, another default Event Session is created on the servers that participate in the Availability Group to […]

Performing a Distributed Replay with Multiple Clients using SQL Server 2012 Distributed Replay

In the first post in this blog series on using SQL Server 2012 Distributed Replay, Installing and Configuring SQL Server 2012 Distributed Replay, we looked at how to configure a Distributed Replay environment using multiple clients and a dedicated replay controller.  In this post we’ll actually make use of the previously configured servers to perform […]

The AdventureWorks2008R2 Books Online Random Workload Generator

Over time, I’ve had a number of reasons to need to run a random workload against SQL Server to be able to demonstrate troubleshooting, how SQLOS works, and most recently how to capture a Replay Trace for my series on the Distributed Replay Utility in SQL Server 2012.  For a while I’ve maintained a large […]

Installing and Configuring SQL Server 2012 Distributed Replay

This blog post is the first in a series of posts that will cover how to install, configure, and use the new Distributed Replay Utility in SQL Server 2012.  The distributed replay utility can be used to assess the impact of changes and upgrades by replaying workload activity against a test environment based on a […]

An XEvent A Day: 31 days of Extended Events

Back in April, Paul Randal (Blog|Twitter) did a 30 day series titled A SQL Server Myth a Day, where he covered a different myth about SQL Server every day of the month.  At the same time Glenn Berry (Blog|Twitter) did a 30 day series titled A DMV a Day, where he blogged about a different […]

Digging into the SQL Plan Cache: Finding Missing Indexes

This is one of those topics I planned to blog about a long time ago, but never actually got around to it, and consequently I keep having to hit search engines to find the code I provided in a forums post when I need to use it again.  A while back, I helped fellow SQL […]