A Fine Slice Of SQL Server
Rick Dobson signs a procedure:
This tip introduces the basics of digital signatures based on self-signed certificates for selected T-SQL objects (stored procedures, functions, and triggers) with T-SQL samples. Learn how to detect what happens to a digital signature when a stored procedure is accidentally altered by a database administrator or intentionally modified by a bad actor.
Click through for the process.
Leave a CommentHaha, just kidding. Erik’s code never has mistakes, but he does have to deal with other people who have foolishly erred. This video is a good one. It covers a broad base of error handling in SQL Server, including improper parameter inputs, try-catch blocks, automatic retries, handling lock timeouts, and a lot more.
Comments closedHaripriya Naidu creates an Extended Events session:
If you’re a DBA, how many times have developers asked you to check whether a particular stored procedure is hitting SQL Server?
Yes, we’ve all been there. Typically, you either run Profiler or set up an Extended Events session to trace it. But the question is, have you ever been confused about which event action to use?
Read on for the list of candidates and when you should use each.
I do mildly disagree with Haripriya’s last note around running Extended Events in production. There’s a small performance hit for most events. There are a few events that do have a much stronger impact, but for the large majority of events, you can (and probably should) have them running to provide the relevant diagnostic information. This isn’t like Profiler or even a server-side trace, where there’s a fairly significant overhead cost.
Comments closedYvonne Vanslageren covers a point of frustration for me:
Modern applications often demand lightning-fast performance from their databases, whether they’re handling large transactional workloads or complex analytical queries. SQL Server’s in-memory OLTP feature addresses these needs by using memory-optimized tables and natively compiled stored procedures to boost throughput and reduce latency. This post provides an overview of natively compiled stored procedures, how to create them, and best practices for performance monitoring and maintenance.
My point of frustration is pretty simple: these things work really, really well. But they’re also so limited that I have never been able to use one in production. Memory-optimized tables are already so limited in good use cases, and natively compiled stored procedures have even more limitations, like using an awful collation (from the standpoint of humans working with the data) for string data.
Comments closedAnnamarie Van Wyk makes a call:
The aim of this blog is to take you through one example of executing SQL stored procedures in Power BI. Similar to SQL Server Reporting Services (SSRS) we can create parameters that will be used to return a result set based on the output parameters that were selected. The parameter selection will be available on the Power BI home page. The data used in his example is production line data. The production line is inside a factory where products are being packaged during a certain shift and at a certain production run date. It is literally a factory line with products being packed.
Read on for a simple answer, followed by how to parameterize the procedure call.
Comments closedOlivier Van Steenlant runs a test:
In the previous data recipe, Create a Test Class & First Unit Test with tSQLt, we created our very first T-SQL Unit Test to test the database collation. In this data recipe, we will test the execution of a Stored Procedure. Specifically, we will validate what happens when a new User is added to the user dimension.
Click through to see how it all works.
Comments closedAndy Brownsword brings out the hangman:
We have database roles for reading and writing data but interestingly there’s no role which provides permission to execute procedures.
Most DBAs I’ve worked with – production or development – prefer to use stored procedures for data access rather than an ORM. A role to allow procedure execution would be very handy.
So let’s fix that.
The nice thing about this role is that you can then introduce module signing and allow the stored procedures to do things that you might not want regular users to do, such as truncate tables, start and stop SQL Agent jobs, etc. That way, users don’t have these advanced permissions, but the application (whose account has the stored procedure execution role) can do what it needs to do. You can, of course, also have other roles, like one for the kinds of things I mentioned and another to execute most stored procedures. Maintaining that gets a little trickier, but is doable.
Comments closedSteve Jones susses out a problem:
I had a customer that was looking to document a restore that had occurred on one of their systems and didn’t see it. They had concerns about SQL Server accurately tracking history across time and noted they hadn’t cleaned any history.
We dug through some of their instance jobs and found one that ran sp_delete_backuphistory. The person didn’t realize this removes restore history as well. This post talks a bit about how this works.
Read on for the full story.
Comments closedI love QuickieStore, but I wanted some columns to be at the front end of the results returned. Namely, I wanted top_waits, query_sql_text, and query_plan right after database name. This way I don’t have to scroll over to see those values.
Unfortunately, it would appear that there’s no advanced functionality for column ordering like we have for sp_whoisactive. But that didn’t deter Josephine, and you can grab a copy of an updated script that includes columns in this different arrangement.
Comments closedLet’s start with giving some credit, here, since I hate an overdue bill.
ReeceGoding reported and very capably fixed an issue with sp_QuickieStore. The problem only occurred when you ran the procedure with
@get_all_databasesand the new@escape_bracketsparameter set to true. Each loop would add an escape character to the search string, which could end up looking like this:%\\\\\[AnyStringStartingAndEndingWithSquareBrackets]\\\\\]%'
That’s a fix in place, and you can also see updates to sp_HealthParser and sp_PressureDetector.
Comments closed