A Fine Slice Of SQL Server
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.
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 closedKenneth Fisher has a good case study on tuning with the help of Rob Farley:
Here we are looking at the difference between the estimated and actual number of rows for an element of the plan. To look at this information you can either mouse over the element or right click and open the properties tab. In this case you will see that the estimated number of rows (what the optimizer thought would happen) is fairly low (117) particularly compared to what actually happened (1494900). When you see a big difference like that in a query plan there is something wrong.
This is a really nice and detailed walkthrough in which Rob plays Socrates and Kenneth your favorite of the group (Thrasymachus anyone?).
Comments closedPaul White shows us a scenario in which attempts to update a writable partition could fail:
The where clause is exactly the same as before. The only difference is that we are now (deliberately) setting the partitioning column equal to itself. This will not change the value stored in that column, but it does affect the outcome. The update now succeeds (albeit with a more complex execution plan):
The optimizer has introduced new Split, Sort, and Collapse operators, and added the machinery necessary to maintain each potentially-affected nonclustered index separately (using a wide, or per-index strategy).
Read on for the reason why this happens, as well as a few solutions.
Comments closedVery quick and simple and hopefully of use to people, this could easily be turned into a function. The full script is below and also available here on the Powershell gallery or by running Save-Script -Name Set-ExtendedEventsSessionstoAutoStart -Path <path>
This is indeed a quick and easy script, and quite useful when checking across a large number of instances.
Comments closedThomas Rushton walks us through determining if there’s memory pressure on an instance:
If you’ve been paying attention, you’ll have noticed that I’ve done the rownumbering in reverse order, and added a dummy (RowNum 0) field at the top of the list – this is to make sure that, if the most recent record is a RESOURCE_MEMPHYSICAL_LOW record, that we can get results that include that value.
This all looks OK in theory. But we’re still getting stupidly high values for the SecondsPressure field, and wait – what’s this? Multiple ring buffer records with the same ID?
More importantly, he shows us how bad the situation is: is this something that happened for a couple of seconds, or is it persistent? This is a great walkthrough.
Comments closedMatt Smith links to Analysis Services auditing details and includes an audit table:
There are various types of Auditing in the Microsoft BI stack. There is auditing in SSRS, SharePoint, SSAS and not forgetting SQL has its own auditing.
Today I am looking at the SSAS auditing – you can find out more about it onTechNet.
Olaf Helper has published some TSQL code for querying the audit data -on theScript Center.
Just because it’s in a cube doesn’t mean we shouldn’t be able to audit it.
Comments closed