A Fine Slice Of SQL Server
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.
Comments closedJen 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.
Comments closedBrian 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.
Comments closedRichie 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.
Comments closedIn 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.
Comments closedDrew 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.
Comments closed