Mike Fal shows us how to use Powershell and T-SQL to get SQL Agent job status:
This is effective, but I struggle a little with the SQL query. It’s good, but suffers from the structure of the jobs tables in MSDB. We have to account for that and it makes the SQL query a little convoluted. It would be helpful if we could reference a simple data set like the Job Activity Monitor in SSMS.
Of course, this is a leading question on my part. There is a way to do this and it is by leveraging the SQL Server Management Objects (SMO). This .Net library is the API interface for working with SQL Server and is what SSMS is built on. Because it is a .Net library, we can also access it through Powershell.
SMO’s a powerful thing.
Andy Galbraith has a tale of woe and a cautionary message:
Paul’s blog post “Issues around DBCC CHECKDB and the use of hidden database snapshots” discusses the need to have certain permissions to be able to create the snapshot CHECKDB uses. I checked the DATA directory and the SQL Server default path and found that the service account did have Full Control to those locations.
What happened next ultimately resolved my issue, and it reflects something I constantly tell people when they ask me how I research things relatively quickly (most of the time anyway :)) – whenever you read a blog post or article about a subject, MAKE SURE TO READ THE FOLLOW-UP COMMENTS! Sometimes they are nothing beyond “Great Article!” but quite often there are questions and answers between readers and the author that add important extra information to the topic, or just “Don’t Forget This!” style comments that add more detail.
Indeed.
Dan English shows us an easy way to build a SQL Server 2016 CTP 3 instance:
If you have an Azure account (possibly through your MSDN subscription) here is the easiest way to get up and running with SQL Server 2016.
First go to the Azure Portal – http://portal.azure.com
Search and find the SQL Server 2016 CTP3 in the Data and Analytics Marketplace in Azure.
My preference is to grab the ISO and build a local VM, or install it on a server in my environment. But if your server infrastructure lives on Azure or you’ve got those MSDN credits to burn, this is a good alternative.
Bob Dorr has scripts to tell if your statistics are accurate:
The dilemma we all run into is what level of SAMPLED statistics is appropriate? The answer is you have to test but that is not always feasible and in the case of Microsoft CSS we generally don’t have histogram, historical states to revisit.
Microsoft CSS is engaged to help track down the source of a poorly performing query. It is common step to locate possible cardinality mismatches and study them closer. Studying the statistics dates, row modification counter(s), atypical parameters usage and the like are among the fundamental troubleshooting steps.
Good post and great scripts, even if he Microsoftly nouns the verb “ask.”
Kenneth Fisher describes his SSMS setup:
Every time I install a new version of SSMS I make a handful of changes to the default setup. For my own notes, and in case anyone is interested in some of the things you can do with SSMS I thought I’d post a list of those changes.
I also use a darker theme, very similar to Fisher’s; mine is designed to look like vim blue. Of course, personal SSMS settings are personal.
Randolph West has a preview of temporal tables in SQL Server 2016:
Temporal tables allow us to retrieve the state of a table, at a specific point in time, using a method called effective dating. Not only useful for auditing and forensics, temporal tables can help if data is accidentally deleted, or perform trend analysis in a simpler way.
I’m hoping there’s an entry on the potential performance impacts.
Ewald Cress investigates spinlocks:
SQL Server spinlocks are famously elusive little beasties, tending to stay in the shadows except when they come out to bother you in swarms. I’m not going to add to the documentation of where specific spinlock types show up, or how to respond to contention on different types; the interested reader likely already knows where to look. Hint: Chris Adkin is quite the spinlock exterminator of the day.
In preparation for Bob Ward’s PASS Summit session, I figured it would make sense to familiarise myself a bit more with spinlock internals, since I have in the past found it frustrating to try and get a grip on it. Fact is, these are actually simple structures that are easy to understand, and as usual, a few public symbols go a long way. Being undocumented stuff, the usual caveats apply that one should not get too attached to implementation details.
Spinlocks are a testament to the level of engineering complexity in the SQLOS model. I appreciate Ewald’s explanation of the topic.
Jovan Popovic has a couple of posts on JSON. First, using OPENJSON to generate a tally table:
Problem: I want to dynamically generate a table of numbers (e.g. from 0 to N). Unfortunately we don’t have this kind of function in SQL Server 2016, but we can use OPENJSON as a workaround.
OPENJSON can parse array of numbers [1,25,3,5,32334,54,24,3] and return a table with [key,value] pairs. Values will be elements of the array, and keys will be indexes (e.g. numbers from 0 to length of array – 1). In this example I don’t care about values I just need indexes.
Well, that’s one way to do it.
Also, Jovan talks about performance of FOR JSON PATH:
You might notice that table scans take majority of the query cost. Cost of the FOR JSON (JSON SELECT operator) is 0% compared to others. Also, since we are joining small tables (one sales order and few details), cost of the JOIN is minor. Therefore, if you processing small requests there will be no performance difference between formatting JSON on client side and in database layer.
This comment was actually due to a bug in the AdventureWorks CTP 3 database. The good news is that JSON isn’t obviously slow performance problems, but I’d like to see some more thorough performance tests.
Both posts via Database Weekly.
Erin Stellato explains how to filter trace files and Extended Events:
Extended Events
Filtering events from an Extended Events file is even easier. Open the .xel file within Management Studio, then select Extended Events | Filters (you can also select the Filters icon in the Extended Events toolbar).
This may be the only case in which XE is easier than a trace…
Meagan Longoria is keeping a list of all the things changing in Power BI. It’s a long one:
Since it’s part of my job to stay on top of the latest Power BI features and capabilities (and because I like lists), I decided to keep a list of the features released for easy reference. It’s much more convenient for me to have a single place to reference when I’m discussing specific capabilities and updates with clients and colleagues. I’m now sharing my list with you.
I’m glad that somebody’s keeping up with everything changing in Power BI. I’m equally glad that person isn’t me.
Kevin Feasel
2015-11-18
Powershell, SQL Agent