John Martin looks at combining Availability Groups with a virtualized environment:
Much of the time there is a systems team and a DBA team, and when the DBAs need to build out a new set of SQL Servers, they request X number of virtual servers from the systems team. The servers are handed over and the DBA team works its magic, and then we have our Failover Cluster Instance or Availability Group High Availability solution. But, is it really Highly Available?
The reason I ask is twofold:
- Which physical hosts are your Virtual Machines are located on?
- Which data stores are your virtual disks are located in?
If the answer to either of these questions results in the same answer for any of your Virtual Machines in an Availability Group, or Failover Cluster Instance for that matter. Then you potentially have a massive flaw in your implementation that can affect availability.
The moral of the story is to communicate with the network administrators and SAN folks.
Colleen Morrow gives explanations for various Service Broker components:
Before I jump into the technical details of the Service Broker architecture, I think it helps to have a real-world analogy of what Service Broker is and does. In the last installment, I used the example of ordering something from Amazon.com. This time, I’d like to use an analogy that’s somewhat timely: taxes.
Each year, we fill out that 1040 or 1040EZ form and we send it to the Internal Revenue Service. Maybe we eFile, maybe we mail it in, it doesn’t matter. That form is received by the IRS and goes into a queue, awaiting review. At some point, days, maybe weeks later, our tax return is processed. If all goes well, our return is approved and the IRS cuts us a check. That is a Service Broker application.
When I first started learning Service Broker, it seemed like there were a lot of abstract notions (mostly because I didn’t know anything about message queues). The pieces all start to come together once you get into an application.
John White shows that the Sharepoint Integrated vs Native mode has shifted in SQL Server 2016:
This situation remained exactly the same in SQL Server 2014, but has changed dramatically with SQL Server 2016. SSRS in SQL Server 2016 contains significant advancements, chief among them are a new HTML5 rendering engine, a new report portal, mobile reports, and (soon) Power BI Desktop rendering. This is fantastic news, but it also changes the game significantly with respect to the Integrated/Native mode decision. With SSRS 2016, most of the new investments are in Native mode only – the balance has shifted. The table below shows an (incomplete) list of new features, and their supported modes.
You still need Integrated mode to read Power View reports, and John mentions a few places where Native mode falls short, so take the time to plan out which is right for you.
Tim Radney shows us a new way of clearing the procedure cache in Azure SQL Databases (and in 2016 RC0 or later):
It turns out that
DBCC FREEPROCCACHEis not supported in Azure SQL Database. This was troubling to me, what if I’m in production and have some bad plans and want to clear the procedure cache like I can with the box version. A little Google/Bing research lead me to find the Microsoft article, “Understanding the Procedure Cache on SQL Azure,” which states:SQL Azure currently doesn’t support DBCC FREEPROCCACHE (Transact-SQL), so you cannot manually remove an execution plan from the cache. However, if you make changes to the table or view referenced by the query (ALTER TABLE and ALTER VIEW) the plan will be removed from the cache.In discussing this with Kimberly Tripp after not seeing that described behavior, it does not flush the plan from cache, but it does invalidate the plan (and then the plan will be eventually aged out of the cache). While this is helpful in certain situations, this was not what I needed. For my demo I wanted to reset the counters in sys.dm_exec_cached_plans. Generating a new plan would not give me the desired results. I reached out to my team and Glenn Berry told me to try the following script:
Read on for the new command, and just like DBCC FREEPROCCACHE, be careful where you point that thing.
Kenneth Fisher warns against using SELECT * in queries:
Typing a * sounds a lot easier than actually listing all of the columns you need. However is that really a good idea? You’ll hear lots of people saying it isn’t, but why?
Well there are several reasons.
Read on for his reasons.
Jen McCown has written a function to simplify getting SQL Agent job names:
So yeah, now it takes fewer keystrokes to get the job name. I used to:
SELECT *
FROM msdb..sysjobschedules
WHERE job_id IN ( SELECT job_id
FROM msdb..sysjobs
WHERE name LIKE ‘CollectorDBFilePropertiesGet-%’ );But now I:
SELECT *
FROM msdb..sysjobschedules
WHERE dbo.JobName(job_id) LIKE ‘CollectorDBFilePropertiesGet-%’ ;
Jen’s got the function available on her site.
Brian Davis shows us how to pause Azure SQL Data Warehouse:
This is where automation comes to the rescue again! Most of our SQLDWs can be paused after 6:00 PM on weekdays, as well as the entire weekend. Now, I could manually go and pause each individual SQLDW at the end of the day, but what happens if I have plans for dinner or something else during that time? I decided that I needed an automated process to check each SQLDW and pause it if it is running. Using Azure Automation andAzure Runbooks, I was able to create a scheduled task that looks for any running SQLDW and pauses it.
Here are the basic steps to implement the automated solution I came up with:
Create a credential in your automation account with access to all SQL Data Warehouses.
Create a PowerShell Workflow Runbook with the code below.
Create and link the schedule(s) needed for it to run.
Azure gripe #4 for me is that they’re so inconsistent about what I can do not to pay money. Apparently you can pause Azure SQL Data Warehouse, which is good. But DocumentDB or HDInsight? Nope, deletion is the only way to stop running up charges. Check out Brian’s script if you use Azure SQL Data Warehouse and save your company a bit of cash.
Richie Lee writes some C# code to parse query plans:
XPath is the bane of my life… it takes a while to find the correct value particularly as there are so many node names that are re-used yet embedded into them. So I added a simple example and a couple with more depth. Nevertheless, running the test should produce a green result. This could be used for more than just testing; DBA’s may find it useful in SQLCLR scenarios.
I’ve uploaded the code to GitHub. The repository is calledXQueryPlanPath.
9/10 would prefer F#.
Seriously, though, this is a nice start if you need to dig into execution plans programmatically.
In short, the plan is stored in the query store, even though the plan isn’t stored in cache. Now, this has implications. I’m not saying they’re good and I’m not saying they’re bad, but there are implications. If you’re in a situation where you need to use Optimize For Ad Hoc to help manage your cache, now, you’re going to possibly see negative impacts on your Query Store since it’s going to capture all the plans that you avoided. There are mechanisms for managing Query Store behavior.
I’d consider this correct behavior. I want to be able to see those one-off query plans. A quick note on Query Store, though: it chews up a lot of disk space in a busy environment, so if you’re planning on holding query store entries for a while, keep plenty of disk space available.
Drew Furgiuele digs into why Powershell scripts break with SQL Server 2016:
One of the steps I tried to remedy the problem was removing the SQLPS module directory from the PSModulePath environment variable, to see if the Import-Module would skip over it. Turns out I was only half right: I should have removed non 2016 versions of the module path, as Matteo goes on to explain:
I’m hoping there will be a real fix for RTM. This works, but it’s neither intuitive nor easily decipherable.
Kevin Feasel
2016-03-30
Administration, Virtualization