The AlwaysOn_health event session in Extended Events is intended to make analyzing problems with Availability Groups possible after they have occurred. While this event session goes a long way towards making it possible to piece together the puzzle of what went wrong in a lot of situations, it can still be a difficult task. One […]
Jonathan Kehayias
The Rambling DBA
Simplifying Availability Group Troubleshooting
Posted on: March 19, 2018 7:31 am
Database Master Keys and Availability Groups
Posted on: February 22, 2018 7:04 am
Recently I received an email from a client experimenting with Availability Groups for the first time in a development environment that had run into an issue with adding one database out of fifteen to the Availability Group in the UI. The database in question had Password required beside it in the UI instead of the […]
Downgrading SQL Server Editions
Posted on: July 19, 2017 10:22 am
At some point in your career working with SQL Server, you will run into a situation where the wrong edition of SQL Server has been installed on a server and will need to change the edition for licensing reasons. Whether it is Enterprise Edition where Standard Edition should have been installed, Enterprise Edition where Developer […]
Parallel Maintenance Tasks with Service Broker
Posted on: May 27, 2016 9:33 am
I’ve been doing a bit of work on Service Broker while building a series of new Pluralsight courses and one of the things I recently found a good use for Service Broker on a client system with a 8TB VLDB was for implementing parallel maintenance tasks using multiple threads. The idea for this implementation came […]
Looking at read_microsec in sys.dm_os_buffer_descriptors in SQL Server 2012
Posted on: February 10, 2014 10:18 am
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 […]
Logging Extended Events changes to the ERRORLOG
Posted on: September 25, 2013 7:43 pm
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. […]
ALTER DATABASE failed. The default collation of database ‘%.*ls’ cannot be set to %.*ls.
Posted on: July 30, 2013 3:35 pm
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. […]
The Accidental DBA (Day 29 of 30): Troubleshooting Deadlocks
Posted on: June 29, 2013 11:00 am
This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. You can find all the other posts in […]
The Accidental DBA (Day 24 of 30): Virtualization High Availability
Posted on: June 24, 2013 11:00 am
This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. You can find all the other posts in […]
The Accidental DBA (Day 22 of 30): Determining a High-Availability Strategy
Posted on: June 22, 2013 11:00 am
This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. You can find all the other posts in […]
The Accidental DBA (Day 21 of 30): Essential PerfMon counters
Posted on: June 21, 2013 11:00 am
This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. You can find all the other posts in […]
The Accidental DBA (Day 16 of 30): General Security
Posted on: June 16, 2013 1:39 pm
This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. You can find all the other posts in […]
The Accidental DBA (Day 14 of 30): Index Maintenance
Posted on: June 14, 2013 11:00 am
This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. You can find all the other posts in […]
New Article – How expensive are column-side Implicit Conversions?
Posted on: April 15, 2013 5:20 pm
Last week I blogged about Implicit Conversions that cause Index Scans, and showed two charts for where implicit conversions could result in an index scan operation instead of an index seek during query execution. As a part of writing that blog post I thought it would be interesting to also look at the performance overhead […]
Tracking SQL Server Database Usage
Posted on: March 18, 2013 11:41 am
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
Posted on: January 25, 2013 1:00 pm
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
Posted on: January 17, 2013 12:52 pm
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!
Posted on: December 20, 2012 9:00 pm
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
Posted on: November 29, 2012 2:00 pm
(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)
Posted on: November 29, 2012 1:38 pm
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: Tracking Database File AutoGrowth with Event Notifications
Posted on: November 6, 2012 4:00 pm
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
Posted on: October 24, 2012 12:20 pm
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?
Posted on: October 24, 2012 12:06 pm
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. […]
Announcing the SQLskills Availability Group Demonstrator
Posted on: September 27, 2012 7:55 am
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 […]
New features of ALTER SERVER CONFIGURATION in SQL Server 2012
Posted on: June 27, 2012 9:15 am
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
Posted on: June 22, 2012 9:50 pm
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
Posted on: June 4, 2012 11:49 pm
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!
Posted on: May 24, 2012 11:39 am
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
Posted on: April 30, 2012 3:36 pm
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 […]
How much memory does my SQL Server actually need?
Posted on: April 3, 2012 1:00 pm
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 […]
SQL Failover Cluster without Shared Storage – SQL Server 2012 and SMB
Posted on: March 20, 2012 1:32 am
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 […]
Network Binding Order Warning during SQL Server Failover Cluster Setup
Posted on: February 9, 2012 12:06 am
Yesterday I was asked by email about a problem that someone encountered associated with a SQL Server Failover Cluster configuration that I have run into a number of times myself, and I have had questions about repeatedly in the past. The problem is that during the SQL Server Setup Validation of the environment, a warning […]
SQL Server 2012 Extended Events Update – 1- Introducing the SSMS User Interface
Posted on: February 6, 2012 1:00 pm
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 […]
New Article Online – Great SQL Server Debates: Buffer Cache Hit Ratio
Posted on: January 19, 2012 4:28 pm
My latest article on Simple-Talk was published this morning. In this article I dig into the actual meaning of one of the performance counters I often see mentioned on the forums, but in a completely incorrect context. Great SQL Server Debates: Buffer Cache Hit Ratio
New Article Online – Great SQL Server Debates: Lock Pages in Memory
Posted on: December 14, 2011 8:36 am
After a recent discussion about Lock Pages in Memory on Twitter, I wrote an article that talks about the history of Lock Pages in Memory and the differing opinions on the appropriate usage of Lock Pages in Memory for 64 bit instances of SQL Server. Great SQL Server Debates: Lock Pages in Memory Thanks to […]
Tracking Problematic Pages Splits in SQL Server 2012 Extended Events – No Really This Time!
Posted on: December 8, 2011 9:17 pm
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
Posted on: December 7, 2011 3:27 pm
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 […]
Performing a Distributed Replay with Multiple Clients using SQL Server 2012 Distributed Replay
Posted on: November 19, 2011 1:52 am
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 […]
SQL Server and VMware: A Potentially Fatal Combination? That’s Nonsense!
Posted on: September 30, 2011 2:09 pm
This morning Paul received an email from a member of the community that was asking if it is safe to run SQL Server inside of a VM and whether we would suggest running databases in virtual machines or not. The root of the question being asked was an article that the person had read back […]
Just in time for PASS – Troubleshooting SQL Server: A Guide for the Accidental DBA
Posted on: September 30, 2011 3:36 am
Just over two years ago, right before PASS Summit 2009, I got the insane idea after finishing Chapter 3 for the SQL Server 2008 Internals and Troubleshooting book that I wanted to write another book based on my experiences answering questions on the MSDN forums that would be based on the top 10 most frequently […]
Finding what queries in the plan cache use a specific index
Posted on: September 21, 2011 5:35 pm
In the last 48 hours I have seen two different people having the exact same problem so I thought that I would go about blogging some code I’ve had lying around for a while and been meaning to blog that would help them with finding the root cause of their problems. In both cases, the […]
Building a Completely Free Playground for SQL Server – 4 – Creating the Cluster
Posted on: September 19, 2011 2:12 am
Run the Cluster Validation Report At this point, I have to admit that as a part of trying to streamline the configuration of this environment, I made a mistake in the first post when I added the Failover Clustering feature to the template VM that was cloned to create the two cluster node VMs. To […]
Building a Completely Free Playground for SQL Server – 2 – Setting up Active Directory and the iSCSI Virtual SAN
Posted on: August 29, 2011 1:12 pm
To continue the build out of our Completely FREE Playground for SQL Server, we will begin by setting up an Active Directory Domain Controller and a separate iSCSI Virtual SAN, which are both requirements for setting up a SQL Server Failover Cluster. To make things easier to follow, this post will break these two tasks […]
Creating a new Login on Multiple Servers with the same SID using Powershell
Posted on: June 24, 2011 10:12 pm
Steve Jones (Blog|Twitter) asked a question on the #sqlhelp hash tag tonight that I found interesting so I thought I’d play around with Powershell for a few minutes and see if I could beat Aaron Nelson (Blog|Twitter) to the punch with a Powershell answer for once. Steve’s question was: @wayoutwest: “Anyone have a PSH script […]
Understanding Non-Uniform Memory Access/Architectures (NUMA)
Posted on: May 24, 2011 1:00 pm
Lately I have been doing a lot of work on SQL Server’s that have had 24 or more processor cores installed in them. Many of these systems utilize hardware non-uniform memory architectures, or NUMA, while a few of them were not. This led me into a good bit of additional research into the differences between […]
SQL Rally Presentation – Deadlocking for Mere Mortals
Posted on: May 18, 2011 10:42 am
The first SQL Rally was held last week in Orlando, FL, and I had the honor of being selected for one of the spotlight sessions by the community in the DBA track. SQL Rally was a different experience from the regular PASS Summit; it wasn’t anywhere as big as the normal summit, but it was […]
Changing SQL Agent Job Owner with Powershell
Posted on: May 11, 2011 11:47 am
This blog post came from a question on the MSDN Forums asking how to change the owner of all SQL Agent Jobs on a server without having to do it manually. I had to accomplish a similar task in the past, where I needed to scan a list of servers for jobs that were owned […]
Certificate Signing Stored Procedures in Multiple Databases
Posted on: April 26, 2011 10:17 pm
Yesterday I received an email asking me a question related to my Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail on SQL Server Central that intrigued me enough that I had to put a couple of minutes into actually figuring out what the problem the being encountered was. In the article, I show how […]
Does Index Fragmentation Matter with SSD’s?
Posted on: April 13, 2011 2:25 am
Two weeks ago at SQL Connections in Orlando, FL, I got to participate in a session that Paul and Kimberly do at the end of the conference titled, “Follow the Rabbit.” The premise of the session is that Paul and Kimberly throw a big list of topics up on the screen and anyone in the […]
Reflecting on the MCM Exam Experience
Posted on: February 24, 2011 8:46 pm
When Microsoft announced that changes were being made to the Microsoft Certified Masters program for SQL Server 2008 last year I was initially pretty bummed out. I had been hoping to be able to attend one of the onsite MCM rotations at Microsoft. I wasn’t looking forward to the expense associated with that, but I […]
An XEvent a Day (28 of 31) – Tracking Page Compression Operations
Posted on: December 28, 2010 8:25 pm
The Database Compression feature in SQL Server 2008 Enterprise Edition can provide some significant reductions in storage requirements for SQL Server databases, and in the right implementations and scenarios performance improvements as well. There isn’t really a whole lot of information about the operations of database compression that is documented as being available in the […]
Some Thoughts on Clustering SQL Server Virtual Machines
Posted on: November 24, 2010 9:19 pm
On my blog post Virtualizing SQL on VMware Reference List, Oscar Zamora (Blog | Twitter) asked the following question in a comment: As a virtualized instance has the benefit of "failing over" to another physical box, would you consider clustering a virtualized instance? The answer to this question more than I want to write up […]
Bug in SQL 2008: Why you shouldn’t create large tables in the Model Database
Posted on: June 7, 2010 2:39 am
If you follow me on Twitter (@SQLPoolBoy) you probably saw tweets about a bug I encountered in SQL Server 2008 recently. What was hoped to be a normal Monday morning started off with alerts from a new production server that DBCC CHECKDB had failed for our new Sharepoint 2010 primary content database. Thankfully, the Sharepoint […]
Does tempdb Get Recreated From model at Startup?
Posted on: May 14, 2010 7:32 am
In my last post Does the tempdb Log file get Zero Initialized at Startup? I questioned whether or not tempdb is actually created from the model database or not at startup. There is actually an easy way to prove that this statement, at least internally to the tempdb database is in fact TRUE. Many thanks […]
Does the tempdb Log file get Zero Initialized at Startup?
Posted on: May 14, 2010 6:34 am
While working on a problem today I happened to think about what the impact to startup might be for a really large tempdb transaction log file. Its fairly common knowledge that data files in SQL Server 2005+ on Windows Server 2003+ can be instant initialized, but the transaction log files can not. If this is […]
SQL Server Installation Checklist
Posted on: March 22, 2010 11:31 am
The other night I was asked on Twitter by Todd McDonald (Twitter), for a build list for SQL Server 2005 and 2008. My initial response was to provide a link to the SQL Server Build List Blog, which documents all of the builds of SQL Server and provides links to the KB articles associated with […]
Tuning ‘cost threshold for parallelism’ from the Plan Cache
Posted on: January 19, 2010 10:19 am
A good while ago I was asked if it was possible to consistently produce a parallel plan by Tom LaRock(blog/twitter), and I recalled from reading Grant Fritchey’s(blog/twitter) book on Execution Plans that it was possible to do this by adjusting the ‘cost threshold for parallelism’ sp_configure option from the default of five to a value […]
