Oct 14

PASS Summit 2016! The best one yet?

In a little over a week, I will be attending my 3rd PASS Summit. Over the past 2 years I have been lucky enough to attend the PASS Summits (2014 and 2015) and 2 SQL Cruises. I have made it my goal to attend these 2 things each and every year for a number of reasons. The most important, for me, is the friends that I have made. My first SQL Cruise introduced me to some of the smartest, nicest, and most fun people I have aver had the pleasure of meeting. However, my time with them did not stop after the cruise. Because of people like Tim Ford (@sqlagentman), Grant Fritchey (@gfritchey), Jes Borland (@grrl_geek), David Klee (@kleegeek), and too many more to mention them all I have been able to meet many more SQL Server experts and feel very comfortable at any SQL Server event. They encouraged me to speak at SQL Saturdays and offer advice whenever I need it. Of course, the networking and learning is important as well… And it is no small piece of the pie. Several posts exist to show your employer the importance of these types of events.

But I digress… This is about PASS Summit 2016!

So why is this going to be the best ever? Because Grant Fritchey said so here! For me it is so much more. First, I get to see many of the old friends I have made in addition to the newer ones from more recent events. I also get to start out the week by speaking at SQL Saturday Oregon and then joining my fellow speakers and attendees on the “SQL Train” to Seattle. And have you looked at the schedule for Summit 2016?! Wowsers! I am having a very hard time deciding which sessions to go to because there are so many good ones in each time slot. With the release of SQL Server 2016 and all that it has to offer, there are some amazing talks going on this year. Not to mention the growing popularity of Azure and PowerShell in our field.

There is still time to get in on this. Join me and so many others at PASS Summit 2016! And if you have the time, stop into SQL Saturday Oregon on the way 🙂

Aug 09

SOLVED: “There have been <N> misaligned log IOs which required falling back to synchronous IO. The current IO is on file <logfilename>.” – Trace Flag 1800

Do you have an Availability Group set up that is seeing some strange latencies or behaviors? Have you looked at the error logs on your replicas and seen this?

There have been <N> misaligned log IOs which required falling back to synchronous IO. The current IO is on file <LogFileName>.

What the heck does this mean?

Generally, it means something is different between your primary and your replica(s) in terms of the disks that the t-logs are on. The replica that is showing this error in the log is the one that is having trouble. You should see this demonstrated in a larger Log Send Queue. So what’s the problem? If I google the error, I get a couple good hits.


I’m not going to include the individual links in this post… Let me google that for you.

The first link takes you to a blog “Running SAP Applications on the Microsoft Platform”, but don’t let that deter you! There is a ton of important information there. What you’ll find from any of these is that there are different sector sizes between different vendors and types of disks. Let me start by saying that I am not a storage expert. I may say things that are technically incorrect from this point forward and I will update as I get corrected. Nevertheless, it makes sense to me and my fellow DBAs.

You have 3 basic configurations. For the sake of this post, I’m going to abbreviate: Bytes Per Sector(LBPS because this is really logical) and Bytes Per Physical Sector(BPPS):

1. LBPS = 512 and BPPS = 512: 512-byte native
2. LBPS = 512 and BPPS = 4096: 512E
3. LBPS = 4096 and BPPS = 4096: 4K native

To put this simply:
#1 used to be the norm
#3 is the new ‘norm’ (not necessarily more performant… Thanks @DBArgenis)
#2 was designed to take the new norm and present it to systems that can’t handle the new norm

You can see how your disk is configured by typing the following into an elevated command prompt “fsutil fsinfo ntfsinfo “. You will get something like this:


All of these articles tell you that when SQL Server starts up, it checks to see what the “sector size” is and writes accordingly. If we have a primary with configuration #1 and a replica with configuration #3, there is clearly a mismatch and something needs to be done. However, what if our primary is configuration #1 and our replica is configuration #2 (as was my case)? One would think that SQL Server starts up and recognizes 512-byte sector for both of these and that everything is fine. What these articles DO NOT tell you, is that the LBPS don’t matter in this case… Microsoft repeats “sector size” on its KB article 3009974 and confused the heck out of me. They should have said Bytes per Physical Sector. Even though it treats it as 512-byte in configuration #2, it is still writing to 4K sectors and leaving them misaligned! That’s why you are seeing the error. So how do you fix it?

The first is to get disks that match the BPPS. It is my understanding that if they are spinning disks, you cannot reconfigure them. This is how they are made… Sorry. However, if you are using local SSDs or an all flash array, you “might” be able to change the way that these drives are configured. It depends on the vendor.

If you cannot do the above, Trace Flag 1800 is here to save the day! This is a startup trace flag, it is not something you can just enable. You have to add it as a startup trace flag through SQL Server Configuration Manager and restart the SQL service. Basically, this trace flag overrides the detection and forces it to write in 4K native format whether the BPPS is 512 or 4K. Personally, I recommend to enable this on ALL replicas. (NOTE: THIS AFFECTS ALL DATABASES ON THE SERVER! If you have strange configurations… be sure that this is understood.) I started by enabling it on my remote asynchronous replica and restarting it. Then my synchronous secondary and restarting. Finally, my primary after failing over. You will know that it worked because you will see this entry in your error log on startup:

There have been X misaligned log IOs which required falling back to synchronous IO. The current IO is on file ….

Following the above procedure fixed our latency issues as well as those pesky error messages in the log. That being said, if you are setting up a new set of servers, take the time to check the disk configurations. This situation has lead me to add another step to my pre-build checklist.

EDIT on 10/15/2016: Thanks @DBArgenis for giving me some education and mentioning some things should be mentioned as important notes: “4Kn isn’t necessarily better for perf, and once you enable TF1800 it affects every single database on the instance. If you have a mix of AGs/Mirrors/LS from 512 and 4Kn things get messy.”

Message misaligned log IOs which required falling back to synchronous IO in SQL Server Error Log
SQL Server–Storage Spaces/VHDx and 4K Sector Size
FIX: Slow synchronization when disks have different sector sizes for primary and secondary replica log files in SQL Server AG and Logshipping environments
@DBArgenis educated me that 4k-Native doesn’t necessarily mean better performance and once you enable TF1800 it affects every single database on the instance. If you have a mix of AGs/Mirrors/LS from 512 and 4Kn things get messy.

Feb 25

Extracting Procedure Name from Cached Plans

The other day, a friend of mine shot me an email asking how he could extract the procedure name from cached plans. He was using the query I referenced from Jonathan Kehayias to get subtree cost, but he really wanted to see which procedures aligned with which cost. I assume this was to look at the ones with the highest cost to optimize them. I told him I didn’t have anything, but I liked the idea so I went to work.

Why reinvent the wheel? I started with a query from Jason Strate:


(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT cp.usecounts
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) pa
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE attribute = 'dbid'
AND value = DB_ID()
ORDER BY cp.usecounts DESC;

I use this query a lot to find cached plans for a specific database. In fact, that’s pretty much the title of his post… I needed to take this and modify it such that I could get the object name of any procedure. So, step 1 was to make sure that I was only looking at stored procedures. To do that, I had to filter the results on cp.objtype and limit it to ‘Proc’. The next step was to filter pa.attribute to ‘objectid’. This is because the objectid attribute of a ‘Proc’ objtype is the id of the procedure itself. Since this query is already bringing back the id of the database for each object, I could use the OBJECT_NAME() function to retrieve the name of the procedure. Finally, I removed system objects by making sure the objectid was greater than 0.


(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT cp.usecounts
,OBJECT_NAME(CAST(pa.value AS INT),qp.dbid) AS proc_name
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) pa
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE attribute = 'objectid'
AND cp.objtype = 'Proc'
AND pa.value  > 0;

Voila! Now can can get the names of all of your stored procedures that are currently in the plan cache!

Jan 07

So you want to learn SQL Server? – Part 2

In Part 1 of this series, we learned how to do a very basic install of the free version of SQL Server (Express Ed.) in order to start learning the basics. In Part 2, we will be downloading a sample database called AdventureWorks that we can attach to our SQL Server instance and begin using right away.

The first thing you need to do is download the sample database here. If that link doesn’t start your download for you, go to http://msftdbprodsamples.codeplex.com/releases/view/125550. You will want the .zip file found in the “Recommended Download” section. The file is named  “Adventure Works 2014 Full Database Backup.zip”. Go ahead and save that file to a known location like downloads so that you can reference it when it is complete.

Once you have that file, navigate to where the file is and right-click on the file to being up the options and select “Extract All…”. This will bring up another window that will allow you to extract or unzip the file:




Either choose a new path or just go ahead and click on Extract. It will open up the folder with the extracted file for you. It will look like this:


In SQL Server, we give backup files the extension .bak. This is a backup file that can be restored using SQL Server Management Studio (SSMS) that we installed earlier or by some other options like scripting. Note the file path and name (or just keep it open to copy and paste).

Now we can go and open SSMS and connect to our local instance of SQL Server. If you are using Windows 8.1 or later, use the shortcut Windows+s and search for “SQL Server Management Studio”. If you are on an older version or prefer to navigate, just go to “All Programs” or “All Apps” find the folder for “Microsoft SQL Server 2014” and expand it. Then go to “SQL Server Management Studio 2014”. Open that up and a window will pop up that looks like below, but you will have to type in “localhost” for the server name.TestInstall1

Now, you can click “Connect” giving you the screen below:


If you click on the + to the left of the Databases folder to expand it out, you will only see another folder for “System Databases” that we will ignore for now. What you are really seeing is that you have no “User” databases to query. To restore our AdventureWorks2014 database, you can right-click on the Databases folder and select the “Restore Database” option will will open up a window:


To restore from a file, we’ll change the Source to “Device” and either use the path of the .bak file that we extracted earlier or just use the ellipsis to navigate to the path and click on the file name:


Once you click OK, it will take a couple seconds to a minute to restore, depending on your CPU and disk speed. Now you will see the AdventureWorks2014 database in the window on the left.


Now, you can expand that out and look at some of the objects contained within a database. They are displayed in folders just like a file structure when you look at them using SQL Server Management Studio. In Part 3, we will write our first SQL query against this database. However, if you can’t wait for me to write Part 3, I suggest checking out SQLServerCentral.com’s Stairway to T-SQL DML. I HIGHLY recommend SQLServerCentral.com for their stairways, forums, and articles. If you don’t already have an account there, go sign up now! If you have any questions or this doesn’t work. Email me at jkarney@sqlknowitall.com or send a tweet to #SQLHelp





Nov 12

So you want to learn SQL Server? – Part 1

After attending the PASS Summit last week, I really got pumped to start blogging and speaking. I wanted to talk about spinlocks and latches, parsing xml, and PowerShell. However, I realized that there are so many people out there that want to get started in SQL, but don’t know where to start. Inspired by my wife and by best bud, Brett, I decided I should start a series on getting started with SQL Server.

In this series, I will go through the real basics. We’ll start with installing a local instance of SQL Server and the AdventureWorks database. Then we’ll see how this evolves. My goal is to work with the people around me who know nothing about SQL to make this the best tutorial that exists for learning SQL.

Step 1 – Installing SQL Server for Learning

There are several editions of SQL Server. For now, I want you to stick with the free version, Express. You can download the latest version here. If you don’t have a Microsoft account already, you will need one.

Fill in the required information and then download the “SQL Server 2014 Express with Advanced Services 64 bit”. (32 bit if you have a 32 bit machine, but these are rare in today’s world…) Make sure you save it to a known location, like “Downloads” or “Documents”. Now, navigate to that location and double-click on the file. You may (depending on Windows version and settings) get a prompt that asks if you want to “Do you want to allow this app to make changes to your PC?”. Just click yes which will give you one more prompt asking where you would like to extract the files. You can just click “Ok” and use the default.

After the self-extracting installer is complete you will get a window that looks like this:
If it doesn’t show these options, make sure that “Installation” is selected on the left (see how it is BOLD in the image above?). Then go ahead and click on “New SQL Server stand-alone installation or add features to an existing installation.” This will cause another window to open up which will be your main window to complete the installation. Now, I’m not going to go into best practices for installing SQL Server here. This series is strictly for learning the scripting language and how relational databases work. Therefore, we will be accepting most of the defaults.

The next screen will look like this:


You can just go ahead and click “Next” to take you to the next screen. This is the screen where we will select what to install. The main things that you will need to learn to script T-SQL are Database Engine Services and Management Tools. I also recommend installing Documentation Components to make it easier to navigate “Help” and Books Online (BOL). However, it won’t hurt you to install other components. Especially if you plan on exploring more aspects of SQL in the future. My configuration for this install is in the image below.


Click next to move on to the “Instance Configuration” section. On this screen, I change the radio button from “Named Instance” to “Default Instance”. This should change the instance ID to MSSQLSERVER. This is important because anything other than the default has to be referenced by name. This allows several “instances” of SQL to be installed on the same machine. Make sure your screen looks like mine below and click “Next”.


The next screen is where you can set specific service accounts for the services to run under. We’ll leave this as default for this install.


Click “Next” again to take you to the “Database Engine Configuration” section. Select the “Mixed Mode” option and enter a password you will remember. This becomes the admin (sa) password for your SQL Server instance. Also, click on the “Add Current User” button to add yourself as an administrator.


Again, click “Next” to move on to the “Reporting Services Configuration” screen. If you didn’t select the option to install Reporting Services, you can skip this. Else, you can select the “Install Only” option.


Click “Next” to begin the install. When it completes, you will get a final conformation screen.


That’s it! You’ve completed your install of SQL Server Express! Let’s check to make sure you can connect. Hopefully you are all on Windows 8.1 or later. If you are, use the shortcut Windows+s and search for “SQL Server Management Studio”. If you are on an older version or prefer to navigate, just go to “All Programs” or “All Apps” find the folder for “Microsoft SQL Server 2014” and expand it. Then go to “SQL Server Management Studio 2014”. Open that up and a window will pop up that looks like below, but you will have to type in “localhost” for the server name.


If you did everything as I said above, you can click “Connect” and everything should work fine, giving you the screen below:


WOOHOO!!! Now have the ability to create, restore, and attach databases. In the next post, I will describe how to download the AdventureWorks database and get it onto your system. If you have any questions or this doesn’t work. Email me at jkarney@sqlknowitall.com or send a tweet to #SQLHelp



Mar 26

How to move data or a table from one data file to another in the same filegroup! – Part 2 of 2

This post is a continuation of the previous post, How to move data or a table from one data file to another in the same filegroup! – Part 1 of 2, and will show you how SQL handles data when it has more than 1 file. I will show you what happens when someone adds a second data file because they ran out of space on the first. I have seen this done when a disk is out of space, so someone adds a second file to the file group on a different disk. In this demo, we will use the same disk, but limit the size and growth of the first data file.

To start, here is a section from BOL on Database Files and Filegroups:

For example, three files, Data1.ndf, Data2.ndf, and Data3.ndf, can be created on three disk drives, respectively, and assigned to the filegroup fgroup1. A table can then be created specifically on the filegroup fgroup1. Queries for data from the table will be spread across the three disks; this will improve performance. The same performance improvement can be accomplished by using a single file created on a RAID (redundant array of independent disks) stripe set. However, files and filegroups let you easily add new files to new disks.

Now I will show you how you do not get to decide which file within the group the data will go to.

Demo 1 – Adding a file to a full database in order to give some free space

First we will create a database with one data file in the PRIMARY filegroup and check the space of the table:

USE [master];

--Check for existence of database and drop if exists
 FROM sys.databases
 WHERE NAME = 'TestDataFileAllocation'
 DROP DATABASE TestDataFileAllocation;

--Create a database with 1 data file in the PRIMARY filegroup
 NAME = N'TestDataFileAllocation'
 ,FILENAME = N'F:\DATA\TestDataFileAllocation.mdf'
 ,SIZE = 10 MB
 ,MAXSIZE = 100 MB
 ) LOG ON (
 NAME = N'TestDataFileAllocation_log'
 ,FILENAME = N'F:\Log\TestDataFileAllocation_log.ldf'
 ,SIZE = 1024 MB

--Switch database context to new database
USE TestDataFileAllocation;

--Create a table for some data
 idCol INT IDENTITY(1, 1)
 ,number INT

--Add a clustered primary key to our new table

--Look at the file allocation using system tables and DMVs
SELECT OBJECT_NAME(p.object_id) AS tableName
 ,df.name AS file_logical_name
 ,df.physical_name AS file_physical_name
 ,CAST(df.size AS BIGINT)*8/1024 AS file_size_MB
 ,CAST(df.max_size AS BIGINT)*8/1024 AS file_max_size_MB
 ,df.growth AS file_autogrow_ind
 ,fsu.total_page_count AS file_total_page_count
 ,fsu.allocated_extent_page_count AS file_allocated_extent_page_count
 ,fsu.unallocated_extent_page_count AS file_unallocated_extent_page_count
 ,au.total_pages AS table_allocated_pages
 ,au.used_pages AS table_used_pages
 ,au.data_pages AS table_data_pages
 ,p.rows AS table_rows
FROM sys.allocation_units au
INNER JOIN sys.database_files df ON au.data_space_id = df.data_space_id
INNER JOIN sys.partitions p ON au.container_id = p.hobt_id
INNER JOIN sys.dm_db_file_space_usage fsu ON df.file_id = fsu.file_id
WHERE au.type IN (
 AND OBJECT_NAME(p.object_id) = 'SomeData';

Notice the results of the allocation on the file:


So far, 328 pages are allocated while 952 are unallocated for the file itself. This particular table is currently empty and has 0 pages. Now lets add dome data to the table and see what happens.

--Insert data into the SomeData table
REPLICATE('sqlknowitall', 1000)

And using the same query to look at the data as we used earlier:


Now we have 1 row in the file and we can see that nothing has changed on the file itself, but that the table has added 1 data page. So, not much has changed… However, what happens if we add so much data that the file runs out of space?

--Insert data into the SomeData table and max out the file
 REPLICATE('sqlknowitall', 1000)
GO 10000

Run the query to see what happened:


We now have the file full. The file has no more unallocated pages and the table itself has 953 allocated and used pages. To allow more data into this table, we need to either grow the file or add a new one. For the sake of this article and the point I am trying to make, we will add a second file to the file group.

 --Add a second file to the filegroup
ALTER DATABASE TestDataFileAllocation ADD FILE (
NAME = TestDataFileAllocation_2
,FILENAME = N'F:\DATA\TestDataFileAllocation_2.ndf'
,SIZE = 1024 MB

Look at the allocation:


We now see the new file with 8 pages. The only reason we are still showing table data with this new file is because the DMVs don’t tell us which file the table resides on, only which FILEGROUP. However, since we only have 1 table with no changes, we know that it still all resides on the first file.

The suggestion was to drop and recreate the clustered index to move the table. Well, let’s see what that does:

--Drop the clustered index
--Recreate the same clustered index


Hmm… Well, some of the data moved to the new file. But how much? Well… There are several more technical ways to come to this, but to keep it simple I will just look at the starting. Before we had any data in the table, the first file showed 328 allocated pages. Before we had any data in the second file, it had 8 allocated pages. After the drop and recreate on the clustered index, the first file now has 336 pages allocated while the second has 968. We can see that not all of the data has moved off of the first file, but a majority did. According to Paul Randall:

SQL Server’s allocation system uses a round-robin mechanism where allocations are spread over all the data files in a filegroup – an allocation from file 1, then an allocation from file 2, and so on, then back to file 1 again. Combined with that, it also uses an algorithm called proportional fill that aims to allocate data from the files according to how much free space they have relative to the other files in the filegroup.
The basic premise of proportional fill is that each file has a weighting, where files with less free space will have a higher weighting, and files with lots of free space will have a low weighting. The files with the lower weightings will be allocated from more frequently – i.e. those files with more free space will be allocated from the most.
This means that if you add a new data file to a filegroup that has mostly full data files, the proportional fill weightings will be such that the new file will be the one where allocations come from until it fills up to the same level as the older files. The new file in essence becomes an allocation hot spot.

In closing, you cannot decide how the data moves between files within the same filegroup. SQL Server uses its own algorithm for doing this and it is out of our control. However, what if you decided that having 2 files was hurting the performance or was too hard to manage? Can you somehow get rid of 1 of them? Yes, you can… but ONLY if the file you are trying to remove is not the first file created when the database was created.

--Empty file 1 and remove it


ALTER DATABASE TestDataFileAllocation REMOVE FILE TestDataFileAllocation;

This will result in an error:


The only thing we can do in this case is move our first file to a larger drive (if that was the issue to begin with, who knows) and move everyting from file 2 to the original after growing the file to accomodate the data.

--Grow our first file and move everything there to get rid of second file
USE master;
ALTER DATABASE TestDataFileAllocation
(NAME = TestDataFileAllocation,
SIZE = 1024MB);

USE TestDataFileAllocation;


DBCC SHRINKFILE (TestDataFileAllocation_2, EMPTYFILE);

ALTER DATABASE TestDataFileAllocation REMOVE FILE TestDataFileAllocation_2;

Just double-checking with our allocation query:






Mar 20

How to move data or a table from one data file to another in the same filegroup! – Part 1 of 2

Sorry! This isn’t something you can do. I wanted to title this post in a way that would draw people to it for my rant. I am ranting because I had a friend who went to an interview where he was asked how you move data from one file to another. He didn’t know, so he asked me…

Friend: Do you know how to move data from one file to another?

Me: You mean between filegroups. Yes.

Friend: No, between files in the same filegroup.

Me: You don’t. Why?

Friend: That’s what I thought too, but I learned at an interview today.

Me (laughing): Really? Can you share this knowledge with the rest of the SQL Server community? Well… start with me.

Friend: You rebuild the clustered index on the other file or you limit the growth of the file you want to move off of and then rebuild the clustered index so that it moves to the other file.

Me: They told you this? Have you ever tried to create an object on a file?

Friend: No.

Me: Did you test it out? Because, it can’t be done…

Friend: No, but…

Let me tell you friends… that as far as I know, this cannot be done. Whoever asked this question doesn’t understand the file structure in SQL Server or how data gets allocated. This conversation made me SOOOO mad because it means that there are people out there asking bad interview questions or they clearly explained the question incorrectly. If you are interviewing someone, PLEASE make sure you know the correct answer to your own questions and make sure you are right. In the next post, I will post the DDL and DML to show this.

Mar 02

My Experience on SQL Cruise Caribbean 2015

Other SQL Cruise Links:


This year I had the amazing opportunity to attend SQL Cruise Caribbean 2015. I found out about it from a blog post by David Klee (@kleegeek) (who posted his recap of the cruise here) and immediately talked to my boss about it. I was lucky because he had seen references to this in the past and had asked his boss, at the time, for permission to attend. However, he was shot down on the spot and laughed at. I imagine it went something like this…

DBA Manager: Good morning Director. I came across this amazing opportunity to learn SQL and network with some of the greatest minds in the field. The presenters are SQL Server MVPs, founders of PASS, and real contributors to the community!

Director: What’s PASS? Sounds good, how much does it cost and where is it?

DBA Manager: Well, it is really inexpensive and takes place on a cruise sh–

Director (cutting off DBA Manager, laughing): ARE YOU JOKING? I’m not paying for you to go on a cruise!

I’ll admit, when I saw the words “SQL Cruise Caribbean” I was thinking about sipping on a mojito while enjoying the sun on the pool deck. I was not thinking about all of the things I could learn and the people I could learn from.

I did a quick write up to my boss that focused on the cost and the value of this experience. I told him about all of the knowledge I could put to use when I returned from Jes Borland‘s sessions on restore methods and automation. The skills I could use to improve our infrastrucure and virtualization configuration that I would learn from David Klee. The questions I would ask Grant Fritchey and Kevin Kline about execution plans and performance tuning. I really sold this thing, but I really didn’t understand the true value until I arrived at the Bon Voyage event in Ft. Lauderdale the night before embarkation.

I was a little shy at first because I really didn’t know anybody (except David Klee) and was even a bit nervous about meeting some of the greats. I mean, I read these peoples’ books and blog posts on a daily basis. They are like celebrities to a SQL geek like myself! Tim and Amy Ford introduced themselves right away and made my wife and I feel very comfortable. We sat down and started talking to people right away. I could see that some of the people had met before this and had been on the previous cruises. They seemed like a family; catching up on the events of the year, talking about future endeavors, and (of course) plenty of SQL talk.

Training started immediately aboard the ship on Saturday, even before we left the port. Jeff Lehmann from Slalom Consulting kicked off the trip by teaching us about the offerings of the Amazon Cloud and how it could benefit SQL Server implementations. Almost immediately, I realized that this training was going to be different from any other. The room was small and people were asking lots of questions. Nobody, even the “experts”, held back. This was a group that would share knowledge, test theories, question standards and best practices, and do so all with the selfless intent of making the SQL community stronger. Not only in their knowledge of technology, but also in terms of professional advancement.

I spent 40 hours of scheduled time with this community throughout the week. I spent a total of over 50 hours combined with this new family of mine. I gained months of knowledge in a week. I gained years of networking within a week. I made friends. My wife made friends. I was inspired: to write, to present, to volunteer, to teach, and to inspire others!

During the trip, I made my deposit with the cruise line for the future. I returned from the cruise on February 14th and signed up for SQL Cruise Caribbean 2016 on February 28th because I know that this yearly training/networking is not only beneficial to my company, but also for my personal career.

So, what will your company get out of this? Why should they pay for it? The first is that the training you will receive is hands-on and by far the best training I have ever received. It is small group which allows for very pointed and specific questions to be asked. Even more specific questions can be asked during office hours and normal conversation outside of the formal sessions. The second is the cost of this training. It is significantly less than any training I have seen. Especially when you look at the quality of the training and the ability to tailor a lot of it to your own specific needs. Lastly, the networking is invaluable to you and your company. When you bring in a consultant, you typically have to get an MSA signed, SOW put together, and a minimum amount of hours contracted for. However, after this training you will have a family that you can pose issues and questions to. These people LOVE a puzzle and also love to help you, a friend, solve and learn from them. Ultimately, this does not only help you, but also your company.

In closing, this was the most valuable experience for my career and I intend to attend every year regardless of my company paying for it. Thanks to all those who made it a memorable experience. Also, thanks to all of the sponsors who make this a great value and give us the coveted swag: Amazon Web Services, redgate, SQLSentry, and SQLAgentman.



Apr 26

Determining a setting for “Cost Threshold for Parallelism”

I was asked to share this after a discussion at SQLSaturday Chicago by a presenter, David Klee. So, thank you David for getting me to finally post something!

A while ago, I was tasked with researching Max Degree of Parallelism and Cost Threshold for Parallelism settings for my company’s SQL Servers. While trying to find recommendations, I found that the Cost Threshold for Parallelism was mostly done by picking a number and testing. I could not find any way of even finding a starting number in some sort of objective way. I couldn’t just settle for “start at 25 or 30 and play with it to see how your server responds.” It was just too random for me, and I knew there had to be a better way.

My boss had given me a script from Jonathan Kehayias, some time before this, that returned a list of parallel query plans and their subtree costs that I thought could be used to gather some data for me.

(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
	query_plan AS CompleteQueryPlan
	, n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS StatementText
	, n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)') AS StatementOptimizationLevel
	, n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost
	, n.query('.') AS ParallelSubTreeXML
	, cp.usecounts
	, cp.size_in_bytes
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
WHERE n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1;

I then took this script and modified it to simplify it for what I needed. All I really cared about were the costs. So I removed all other columns. I also thought it would be much easier to work with these numbers in a temp table, so I did that. The script below is now what I use to help me decide what to set my Cost Threshold for Parallelism. It gathers all of the costs, inserts them into a temp table, retrieves the data (not always needed and sometime too large to do anything with, so comment out if not needed), and calculates 3 important statistics; Mean, Median, and Mode.

CREATE TABLE #SubtreeCost(StatementSubtreeCost DECIMAL(18,2));

(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
INSERT INTO #SubtreeCost
	CAST(n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS DECIMAL(18,2))
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
WHERE n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1;

SELECT StatementSubtreeCost
FROM #SubtreeCost

SELECT AVG(StatementSubtreeCost) AS AverageSubtreeCost
FROM #SubtreeCost;

	((SELECT TOP 1 StatementSubtreeCost
		SELECT TOP 50 PERCENT StatementSubtreeCost
		FROM #SubtreeCost
		ORDER BY StatementSubtreeCost ASC
		) AS A
	ORDER BY StatementSubtreeCost DESC
	(SELECT TOP 1 StatementSubtreeCost
		SELECT TOP 50 PERCENT StatementSubtreeCost
		FROM #SubtreeCost
		ORDER BY StatementSubtreeCost DESC
		) AS A
	ORDER BY StatementSubtreeCost ASC))

SELECT TOP 1 StatementSubtreeCost AS MODE
FROM   #SubtreeCost
GROUP  BY StatementSubtreeCost

DROP TABLE #SubtreeCost;

So what do I do with these numbers? In my case, I am trying to get “about” 50% of the queries below the threshold and 50% above. This way, I split the amount of queries using parallelism. This is not going to guarantee me the best performance, but I figured it was the best objective way to come up with a good starting point.

If all of my statistics are very close, I just set the Cost Threshold for Parallelism equal to about what that number is. An average of the 3 and round will work. In many of my cases, this was between 25 and 30. If the numbers are different, i.e. a few very large costs skew the average up but the median and mode are close, then I will use something between the median and mode.

Although this method does not spit out 1 number that will magically solve all of your problems, nor is it completely objective… It is the most objective way I have found to come up with a starting point. If you have any thoughts on this or would like to share your own experiences, please feel free to send me an email and I will respond as soon as I can.

Mar 01

Existing Instance Not Available for Upgrade/Repair/Remove

Yesterday we ran into an issue when upgrading one of our SQL Server 2008 R2 instances to SQL Server 2012.  We ran the install as normal with the upgrade option.  When it came time to choose the instance and features to upgrade, the only options were the shared features.  What happened to our Database Engine instance?

Long story short… We found a registry key that contains the instance name was duplicated in 2 locations:

  • HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL
  • HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names\RS

The solution? Since we did not have Reporting services installed on this server anymore, I took a backup of the registry and then deleted the RS folder. Restarted the install and… VOILA!

Older posts «