As you probably know, there is a server configuration option called “optimize for ad hoc workloads”. For more details on that option check out the MSDN article. The real question is, how do I know the extent to which my environment uses AdHoc queries? Well, as usual there is a DMV for that. Use the query below to get an idea of how many plan caches are being stored but only used once. The number changes often, so it’s a good idea to baseline your typical number. For instance, I’ve created a SQL Agent job to record the number to a table every ten minutes. If you maintain high numbers then it might be a good idea to optimize for ad hoc workloads. As always, however, I would advise testing thoroughly first. Happy hunting.
INSERT INTO Diagnostics.dbo.AdHocTracking
SELECT COUNT (*) FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc'
AND usecounts = '1'
Credit to the Red Gate blog for my initial background information.
So, here’s the deal. You’ve got Change Data Capture going in your environment and want to move your database to an Availability Group on SQL Server 2012. That’s great, lots of advantages there. But what happens to CDC when your database fails over to a different node? Since the capture process runs through SQL Agent jobs you will no longer be getting the CDC information post-failover. This is the scenario I ran into recently. In order to mitigate the problem I’ve created a simple script to handle the enabling and disabling of jobs on both replicas. Of course, the jobs will need to be created on each replica before it will work. But this is an easy way of making sure you can take advantage of the benefits of AG’s while still keeping up your CDC captures. I have this running every five minutes on each node. Feel free to adapt/improve/delete it to your heart’s content. Note that I have hard-coded the replica_id. I know, not the most elegant method. But good enough for a first version.
Through painful experience I’ve discovered that the replica id can change if you remove the replica and add it back in. A slightly better method of checking utilizes sys.dm_hadr_availability_group_states. In this case we’re checking against the instance name, which will always remain the same.
IF (select primary_replica from sys.dm_hadr_availability_group_states) = ‘YourReplicaName’
For SQL Server 2014 and above you are not allowed to even run the USE statement against a database that is on a read-only replica (ie your secondary replica used for reporting). For that reason I’ve altered it to use sp_start_job instead. The updated version below also simplifies other syntax a bit.
--Check to see if this instance is the Primary Replica.
IF (select primary_replica from sys.dm_hadr_availability_group_states) = 'YourReplicaName'
--If this is the Primary and the capture job is disabled, enable and start it.
IF (select enabled from MSDB.dbo.sysjobs where name = 'cdc.YourJobName_capture') = '0'
EXEC msdb.dbo.sp_update_job @job_name = N'cdc.YourJobName_capture', @enabled = 1;
EXEC msdb.dbo.sp_start_job N'cdc.YourJobName_capture' ;
--If this is the Primary and the cleanup job is disabled, enable it.
IF (select enabled from MSDB.dbo.sysjobs where name = 'cdc.YourJobName_cleanup') = '0'
EXEC msdb.dbo.sp_update_job @job_name = N'cdc.YourJobName_cleanup', @enabled = 1;
--Actions to take if this instance is not the Primary Replica.
--If this is not the Primary and the capture job is enabled, disable it.
IF (select enabled from MSDB.dbo.sysjobs where name = 'cdc.YourJobName_capture') = '1'
EXEC msdb.dbo.sp_update_job @job_name = N'cdc.YourJobName_capture', @enabled = 0;
--If this is not the Primary and the capture job is enabled, disable it.
IF (select enabled from MSDB.dbo.sysjobs where name = 'cdc.YourJobName_cleanup') = '1'
EXEC msdb.dbo.sp_update_job @job_name = N'cdc.YourJobName_cleanup', @enabled = 0;
Check out this post to learn about firing off jobs based on SQL alerts. This would be a better approach than checking on a timed interval.
Over the last year or so I’ve been getting more into web hosting for myself and friends. Below are the solutions I’ve chosen to use and I would recommend them to anyone.
- Name.com: This is the site I’m using both to register my domain and to host the site. They are easy to use, affordable, and have great tools along with instruction on how to use them.
- WordPress: If you’re like me, you enjoy using the web and would like to harness the power of today’s innovations but don’t have the programming skills (or time) to write it from scratch. Enter WordPress. Sign up for a hosting solution (such as Name.com mentioned above), install WordPress, and your site is ready to go. From there it only requires simple gui-based configuration to customize your site and start taking advantage of the online world. Plus, there are tons of free WordPress plugins that will enhance the capabilities of your site for free.
- MailChimp: If you’re in need of a newsletter signup utility look no further. MailChimp will easily integrate with WordPress to have you signing up subscribers in no time.
When they introduced the ribbon menu in Office 2007 it seemed like both the best and worst idea ever. While it provides a more simple approach to those new to Office it is infuriating for us that had the old style down to a science. Many of us did not feel like learning an entirely new way of doing the same tasks, or we just refused out of spite. Well, Microsoft has now made it easy to make the transition. This handy, interactive guide will let you do an action the way you know and love, then it will immediately show you the ribbon way of doing it. Funny thing is they’ve had it out for a while and just didn’t tell anyone. Way to win Microsoft.
I recently decided to go back to Verizon from using Straight Talk. When we had our son we cut back on our expenses a lot and are only just now sorting out how much of that is truly necessary. Anyway, we decided to go back to the smart phone world and to do so on a network we knew was reliable, Verizon.
People had warned me ahead of time that moving your number from a prepaid carrier can be difficult. We did as much research ahead of time as possible and moved forward confidently. My wife was certain she wanted an iPhone, and their prices dropped on Apple’s website first so we ordered it from there. You can choose all of the options for your new Verizon plan from there and everything so it all seemed very streamlined. The problem is, when we received the phone it could not activate. Something became stuck in the process of porting the number. I spent a large portion of a weekend on the phone with Verizon support. They were able to port the number over but it became stuck in their system, which they said could take 7-9 more days to resolve. We were exasperated at this point, so we just went into the Verizon store on Monday and said forget it, we don’t even care if we lose the old number. Their response to us? Can’t touch it. You ordered the phone through Apple so we can’t work with it. We ended up buying two completely different phones and returning the first to Apple. We also both got new numbers instead of having Verizon port our old ones over.
Eventually we were able to run blissfully through fields with new iPhones and everything worked great, but it was a painful process to get there. Below are my tips on how to avoid this.
- Order your phone either through the actual Verizon store or off of their website. According to the guy I talked to they cannot work with any other devices if you bring them in, not even from their resalers in the same area.
- If you’re going to order the phone off of Apple.com or another site instead of the Verizon one, opt to get a new number. The guy I talked to said this is always how they do transfers. They get the phone up and running with a temporary number and then port over your old one.
- Instead of bringing your old number over to the new carrier at all, make it a flexible virtual number by transferring it to Google Voice. This is what we did with my number. I can now point it at any device, including my new cell phone. I can even tell the service to ring the house phone or my wife’s if I don’t answer. There are a wide range of features and there is only a $20 one-time fee for transferring a current number.
- No matter what option you choose for porting an old number over, DO NOT touch the old service until EVERYTHING is up and running. This causes bad things to happen. Just let it sit completely alone. Don’t even have your old phone on. The systems don’t play nicely together otherwise.
I hope you can avoid my pain and suffering. In the end, having known a few simple things would have probably made the process go flawlessly.
Welcome to my site!
I’m glad you stopped by and I hope it will be a blessing to you. My goal is to share all I can about God, family, and tech with you. For more insight into who I am check out the About Me page. Otherwise stay tuned for more content coming soon!