And farther down on that same page, there is a note stating:
The PERMISSION_SET option is affected by the clr strict security option, described in the opening warning. When clr strict security is enabled, all assemblies are treated as UNSAFE.
The last sentence in each of those quoted statements certainly does seem to be saying that when “CLR strict security” is enabled, then
UNSAFEis effectively the only
PERMISSION_SET, or stated another way: then
PERMISSION_SETeffectively no longer has any effect. However, as usual, things are perhaps not so simple given that the wording is not explicitly stating that. It would be rather easy to add a few words to both statements to indicate that the
PERMISSION_SETproperty is now being ignored. Other documentation makes similar claims (for example: allow updates Server Configuration Option). So, did they fail to explicitly state this, or was this not stated because it is not what is actually happening?
Read on for Solomon’s answer.
We covered this in detail in a previous post, What about
TIMESTAMP? It’s better to pretend that this data type doesn’t exist.
It is not what you think it is.
TIMESTAMPis actually a row version value based on the amount of time since SQL Server was started. If you need to record an actual date and time, use
When should we?
I appreciate that Randolph includes a “when should you not listen to my overall pronouncement?” bit, as there are commonly exceptions to “do not do X” style rules.
But this had to be done.
I thanked Geoff and hung up the phone. I then made another judgment call and exercised yet more of my ETL Architect authority. I assigned the PrUAT ticket to myself, logged into PrUAT, executed the patch, copied the output of the execution to the Notes field of the ticket (as we’d trained all DBAs and Release Management people to do), and then manually verified the patch was, in fact, deployed to PrUAT.
I closed the ticket and called my boss. “Done. And verified,” I said. My boss replied, “Good,” and hung up. He passed the good news up the chain.
A funny thing happened the next morning. And by “funny,” I mean no-fun-at-all. My boss called and asked, “Andy? I thought you said the patch was was deployed to PrUAT.” I was a little stunned, grappling with the implications of the accusation. He continued, “The process failed again last night and vendor checks were – again – not cut.” I finally stammered, “Let me check on it and get back to you.”
It’s a good story and really sells the idea that you have to track deployment details, including when you’re doing manual deployments.
The Segment operator, like all operators, is described at the Books Online page mentioned above. Here is the description, quoted verbatim:
Segment is a physical and a logical operator. It divides the input set into segments based on the value of one or more columns. These columns are shown as arguments in the Segment operator. The operator then outputs one segment at a time.
Looking at the properties of the Segment operator, we do indeed see the argument mentioned in this description, in the Group By property (highlighted in the screenshot). So this operator reads the data returned by the Index Scan (sorted by TerritoryID, which is required for it to work; this is why the Index Scan operator is ordered to perform an ordered scan), and divides it into segments based on this column. In other words, this operator is a direct implementation of the PARTITION BY spefication. Every segment returned by the operator is what we would call a partition for the ROW_NUMBER() function in the T-SQL query. And this enables the Sequence Project operator to reset its counters and start at 1 for every new segment / partition.
Read on to understand the issue and see Hugo’s proof.
According to the documentation for CREATE SERVER AUDIT, I should be able to add a
WHEREclause (starting in SQL Server 2012) to do simple filtering. The documentation states that the list of fields that can be filtered on is found in the documentation for the [sys.fn_get_audit_file] system function. Selecting from that function showed that the
class_typefield contains the “object type” (“FN” = Function, “P” = Stored Procedure, etc.) value. That’s just what I needed. So I’m just about done, right? Not so fast!
Nothing’s ever that easy, it seems. Read on for the full story.
Based on the column names and values above, seems natural to think:
total_page_file_kb – available_page_file_kb = used page file kb
11027476 kb – 3047668 kb = 7979808 kb
Holy cow! Is my laptop using nearly as much paging space as there is RAM on the laptop??
Weird. If something forced that much paging space use relative to RAM on the laptop… I certainly wouldn’t expect system_memory_state_desc = ‘Available physical memory is high’!!
Read on for an explanation of what the columns actually mean.
Now you can simply type <# and your help will be dynamically created. You will still have to fill in some of the blanks but it is a lot easier.
Here it is in action in its simplest form
But it gets better than that. When you add parameters to your function code they are added to the help as well. Also, all you have to do is to tab between the different entries in the help to move between them
Looks like a nice time-saver.
This new DMF is intended to replace the (not so) undocumented DBCC LOGINFO command. I say undocumented as I’ve seen tonnes of blog posts about it but never an official Microsoft page.
This is great imho, we should all be analyzing our database’s transaction log and this will help us to just that. Now there are other undocumented functions that allow us to review the log (fn_dblog and fn_dump_dblog, be careful with the last one).
I’m glad to see this make the cut, as replacing informative / idempotent DBCC commands with DMVs makes it easier to query this data, particularly in conjunction with other DMVs or system tables.
OptimizerStatsUsage is available in cached plans, so getting the “estimated execution plan” and the “actual execution plan” will have this information.
In the above example, I see the ModificationCount is very high (almost as much as the table cardinality itself) which after closer observation, the statistic had been updated with NORECOMPUTE.
And looking and the Seek itself, there is a large skew between estimated and actual rows. In this case, I now know a good course of action is to update statistics. Doing so produces this new result: ModificationCounter is back to zero and estimations are now correct.
This will be a good addition to SQL Server 2017.
One of the challenges I’ve faced as a blogger is quickly reproducing code that looks good in HTML. I’ve tried a few different online code conversion sites, and even a C# library. But I never quite got the results I wanted. I found myself revising the HTML to fix up key words, comments, and operators that sometimes got missed, were the wrong color, or the wrong font. All that HTML editing was tedious and time consuming.
What I really wanted to do was copy my code from SSMS, Visual Studio, or the PowerShell ISE and paste it into my blog so I could spend less time as a web dev and more time writing. Since none of the other tools I found gave me the results I wanted, I wrote my own. The code for my little app is available on GitHub. (It’s my first project, so if something is missing or wrong, let me know.) It was created with Visual Studio 2015–there’s a compiled exe if you prefer that.
Check it out.