On occasion I’m asked to come and look into some SQL performance problems on systems. Normally when I’m doing this the customer doesn’t have any third party tools installed and they are actively having problems, and these problems are leading to lost revenue or at least really pissed off customers and/or employees. As the systems are currently in a system down situation there’s usually no time to pitch, buy, install and configure a performance monitoring solution from one of the many vendors.
When I run across these sorts of problems the first thing that I do is put sp_whoisactive and sp_who3 on the server. As these are just stored procedures getting approval to install them is usually pretty easy.
Once the stored procedures are installed sp_whoisactive will pretty quickly tell you the stored procedures which are having performance problems. If the output is showing a CXPACKET wait type that’s when sp_who3 comes into play. Running sp_who3 with a SPID number after the procedure like “exec sp_who3 125″ will give you the wait type for each thread within the process.
When doing this recently on a system sp_whoisactive showed me that CXPACKET was the wait type. After digging into the process with sp_who3 I saw that one of the threads was waiting on SOS_SCHEDULER_YIELD. I then used sp_whoisactive to get the execution plan showing me the missing index which needed to be created. In this case there was a clustered index on the table which was being scanned. Based on the page count output from SET STATISTICS IO we were scanning the entire table every time the query was run. This massively expensive query was causing the query to parallelize and the run time to go insanely high.
Once we added the index we figured out the man hours wasted per day by having the index missing. We assumed that the procedure was run 3 times per second over the course of a 12 hour period and the query was taking 3 seconds to complete before the index was added. After the index was created the query was running in just a few milliseconds. This single query running for 3 seconds, 3 times per second, for 12 hours comes out to 108 man hours wasted while this one query was being run.
Using two community written stored procedures and a few minutes of troubleshooting time 108 man hours per day are now being saved for that specific company.
Hopefully you’ll find these stored procedures to be useful in your performance troubleshooting. They aren’t hard to use, but they sure are useful.
So I’ve run into a bit of a strange issue with Windows 8 on my laptop that I wanted to share in case anyone else runs into the same problem. My basic problem is that about 50% of the time when my laptop boots up into Windows 8 it can’t find the network. I know that the WiFi card is working correct because it can see the list of networks in my area, I just can’t actually talk to any of them. If I do an ipconfig from the command line I can’t even see the LAN or WiFi network adapters even though they are showing up correctly in the network adapter window in the control panel. (All pictures can be clicked on to view them full size.)
As you can see the WiFi card found the WiFi network there was just no TCP information coming down from it.
What I was able to figure out is that there’s some TCP/IP setting which isn’t being saved correctly (or at some times is being wiped out from the registry for some unknown reason). To fix the problem in the Network Connections window (shown above) double click on of the network icons (I used the WiFi one). To verify the problem is the same you should see no bytes being passed at all.
If you click details the box which would normally have all sorts of useful information will be blank.
Close the Network Connection Details and on the Network Status (WiFi Status in my picture above) click the “Properties” button.
In the list in the middle scroll down until you find “Internet Protocol Version 4 (TCP/IPv4)”. Select this and click the properties button. This will show you a window similar to the following.
If you have values specified in this screen that are different from my settings shown in the screenshot DO NOT CHANGE THEM! If you change these settings without knowing what you are doing you won’t be able to get online and only the person who setup your network will be able to help you fix the problem!
Now click the “OK” button on this screen (without changing anything), and click the “Close” button on the prior screen.
You will notice that under the Activity section the numbers are no longer 0, which means that data is flowing through the network card to the rest of the network (and in my case to the Internet as well). If you click the “Details” button you’ll see you now have an IP address and everything is happy again.
If you look at the output from ipconfig again we’ll see that the network adapter is now listed and is receiving IP address information.
If you do end up running into this problem yourself, remember these steps as you’ll probably have to do this pretty regularly.
So I’ve taken the plunge and installed Windows 8 Client Preview on one of my laptops. The install process was pretty simple and straight forward. The installer found my WiFi card so I was able to get everything up and running right out of the box. The next thing to do was to install SQL 2012 and Office. I started with SQL 2012 and it installed without issue. Office 2010 installed without issue as well.
Thankfully Windows Live Mesh also installed without a problem and was able to sync up the My Documents folder to my desktop and other laptop so everything showed up on the laptop just as I expected it to be.
The lack of the classic start menu is a little annoying. You can however hit <Windows>+W to get something pretty similar up on the screen. I’ve only had an hour or so to play with Windows 8 so far, so I haven’t done a whole lot with it yet. Task manager is pretty cool looking with a lot of additional information being included.
Now so far I pretty much hate the “Start Menu”. Everything is sitting there with no folders. Needless to say this isn’t exactly efficient with a bunch of applications installed.
I like the changes to the Windows explorer with the ribbon. I think I actually like it better than the old drop down menus. One great new feature is that Windows 8 can, without any 3rd party software, mount ISO images to make it easier to install downloaded software. Is makes life much easier when installing all the various Microsoft software that one uses to work, as Microsoft distributes most everything on MSDN and TechNet as ISO files these days.
So by default (if Windows found a network when installing) the Windows accounts are tied to your Windows live account (if you don’t have a live account it’ll either prompt you for one or make the accounts local accounts like they used to be). This allows you to sync your settings between your Windows 8 computers. Once I’ve got several Windows 8 machines this will probably be handy. Until then this feature doesn’t really do anything for me.
As I use it more I’ll post another update.
Everyone says that Windows 8 doesn’t have a start menu, and it’s true there’s no Start Button on the task bar like in the last several versions of Windows. However if you press <Windows>+W you’ll get a sort of start menu.
There’s an Apps button on that menu, which when pressed will give you the pretty classic looking Start Menu.
I’m sure that I’ll be using this feature pretty often as most apps don’t have a Metro icon.
Interested in my SQL Bits pre-con, but you just can’t make it out to London for SQL Bits? Have I got a deal for you. It has just been announced that I will be giving my Storage and Virtualization pre-con at SQL Saturday 120 on March 23rd, 2012.
SQL Saturday is bringing you this pre-con for just $99. All this is available without leaving the good old USA. Sign up now at http://sqlsat120precon.eventbrite.com to reserve your seat and I’ll see you there.
If you can’t make it to the pre-con don’t forget to register for the full day of training at SQL Saturday 120. We’ve got some great local speakers as well as a ton of speakers from around the US who are flying in just to see your smiling face in their sessions. So don’t stand them up. Some of the out of town speakers include Kendra Little, Jeremiah Peschka, Randy Knight, Grant Fritchey, Aaron Nelson and Audry Hammonds.
When setting up AlwaysOn Availability Groups you may receive Error 41158 which references error 41006 when you attempt to join the the replica to the Availability Group. What these errors in a nut shell mean is that it ain’t going to work with your current configuration.
Assuming that you ran through your SQL Server installation and went next, next, next through the install this result is to be expected. The reason for this is that your SQL services are all running under local accounts which don’t have the ability to log into each other. There’s two solutions to this problem at this point. 1 is supported, the second isn’t.
Option #1 – aka. The Supported Option
Reconfigure the SQL Services which will be hosting the Availability Group Replicas to run under a single domain account. Restart the services. Give the SQL Account that the services are running under sysadmin rights. The replicas should sync up automatically at this point. If they don’t you can use the ALTER AVAILABILITY GROUP command to join the AG.
Option #2 – aka. Totally Unsupported, but works great for a demo
Add the domain computer account for each of the nodes of the cluster to each others SQL instance so that they can log in. For example the four computers which I use for my demo are called ALWAYSON1, ALWAYSON2, ALWAYSON3, and ALWAYSON4. So on machine ALWAYSON1 I added the domain accounts BACON\ALWAYSON2$, BACON\ALWAYSON3$, and BACON\ALWAYSON4$ as members of the sysadmin fixed server role (again this is for my demo lab so I’m going for working not secure). On machine ALWAYSON2 I add BACON\ALWAYSON1$, BACON\ALWAYSON3$, and BACON\ALWAYSON4$ and so on for machines 3 and 4. Once that was done the replication should being syncing up automatically. If they don’t either use ALTER AVAILABILITY GROUP or use the UI to force retrying.
Some seats are still available for my SQL Server 2012 class which kicks off March 19th, 2012 in Los Angeles, CA. If you are planning on deploying SQL Server 2012 in the near future this training class is for you. But don’t wait to get signed up. The sooner you get signed up the better off you’ll be.
This training class is 4 days long and will be focusing on 4 key areas of SQL Server 2012.
- Planning and Installation
- Mission Critical Deployments (aka High Availability, programming and migrations)
- Breakthrough Insights (aka BI)
- Manageability and Security
This full four day class includes not only lecture, but lots of hands on labs which are only available through this class. All this is available for just $1200 which covers all four days.
So get signed up today!
I’m sad to say that I’m going to have to cut back on the number of SQL Saturday’s that I’m going to be able to attend this year. It’s not because I don’t love PASS, or SQL Saturday, or the attendees as much as I did before, because it’s not. I’m just so busy that I’ve been royally screwing up the whole work live balance thing so far this year so far. In the first two months of the year I’ve been home for something like 10 days, and 4 of those I was sick with the flu (by the end of March I’ll be home for 2-3 weeks total depending on if a trip happens or is canceled).
Between work and the conferences like Tech Ed, SQL Days 2012, etc. that I’ll be at I just need to make sure that I’ll be at home at least a little bit so that Kris doesn’t kill me.
I’ll be at my local Code Camps and SQL Saturday’s (I’ve even got to leave SQL Saturday Huntington Beach early for my flight to SQL Bits) for sure, I’ll be in Atlanta for sure. If there’s one the weekend before or after PASS I’ll try and hit that one. Other than that I’m afraid that I’ll probably have to keep it pretty light. Hopefully next year I can cut back all this other travel and get back on the SQL Saturday circuit a bit more.
Hopefully I’ll see you at one of the few SQL Saturday events that I’m able to attend, or one of the bigger conferences. SQL Saturday 120 is next followed by SQL Bits.
Over the weekend I had the pleasure of presenting to the great folks at SQL Saturday 109. I’m pretty sure this was my largest SQL Saturday to date to attend with over 400 people attending the session. Because of the massive number of great speakers, including MVPs, Microsoft Employees and local speakers I only got to present one session. But my session went really well, and I think everyone who attended got something out of it.
Several people asked for my slide deck, which you can download here.
Hopefully everyone had as much fun attending the session as I did presenting the session.
One of the great features with SQL Replication is the ability to initialize a subscription from backup instead of from a snapshot. The official use for this is to take a database backup and restore it to a subscriber then replicate any additional changes to the backup.
However this technique can be used to get replication back up and running after moving the publisher to another SQL Server. Simply setup the publication just like normal, then backup the database and add the subscription using the “initialize with backup” value for the @sync_type parameter as shown in the sample code below.
If you were going to actually initialize a new subscription using a backup like the feature was written to be used, then after the backup has happened restore the database to the subscriber under the correct database name.
BACKUP DATABASE YourDatabase TO DISK='E:\Backup\YourDatabase.bak' WITH FORMAT, STATS=10 GO USE YourDatabase GO EXEC sp_addsubscription @publication = N'YourDatabase Publication', @subscriber=N'ReportServer', @destination_db = N'ReportingDatabase', @article='all', @sync_type='initialize with backup', @backupdevicetype='disk', @backupdevicename='e:\Backup\YourDatabase.bak' GO
This technique should work on all versions of SQL Server from SQL Server 2000 up through SQL Server 2012 without issue.