Last week we looked at identifying object dependencies through performing a wildcard search on the objects such as procedures. I noted another option could be to use the views such as sys.sql_expression_dependencies and Kevin also commented about using the supporting functions such as sys.dm_sql_referencing_entities. I wanted to briefly look at both of these options and look at how their results can […]
I’ve recently been reviewing SSIS packages to make some performance fixes and needed a way to validate the results of those changes. I thought I’d share the scripts as they may be useful for others. Rather than relying on run times from the SQL Agent running the packages I wanted to dive deeper into the […]
Dynamic SQL has many uses and one of these can help us fix Parameter Sniffing issues. Here we’ll look at how it can be used to generate multiple execution plans for the same query. Parameter sniffing is a common issue. Even for simple queries we can run into suboptimal plans being produced. There are multiple ways we […]
When building dynamic SQL, safety is crucial. As we saw last week, we have the QUOTENAME function which can help when referencing object names. Another aspect to consider is use of parameters. Integrating them incorrectly can leave us vulnerable to SQL injection attacks. Let’s take a look at how to handle them the wrong way, followed by the right […]
I’m a big fan of dynamic SQL in the right conditions. One key to crafting safe dynamic query of the use of the QUOTENAME function. The issue Using dynamic SQL can leave us vulnerable without proper safeguard. Let’s see an example of this: This is a cut down example of something I’ve seen previously in […]
In the last post we looked at how TOP and MAX operators compared. We saw the execution plan for a MAX function used a Stream Aggregate operator which is one of two which we can use for aggregation I wanted to look at the two operators and how they perform the same tasks in different ways. The way they function is key […]
Both TOP (1) and MAX can be used to identify the largest value in a data set. Whilst they get the same result it isn’t necessarily in the same way. Firstly, what is the difference between the two? The TOP clause limits the number of results which are returned from a query, in this instance we’re focussing on a single result. […]
A Trivial plan is created when SQL Server really doesn’t have any choice in how it’s going to execute. Here’s an example from the StackOverflow database with the indexes removed: There really isn’t any choice but a clustered index seek. It’s the only index and its about as effective as we can get given we’re filtering on […]
Tickets. Each one helps make the solutions we support more feature complete, bigger, faster, and fixed-er. This month’s invitation from Brent asks us to describe one of the last tickets which was closed to give an insight into our day to day. As an introduction: this is a response from a Development DBA who is relatively new into a […]
Blocking in SQL Server will reduce throughput. Excessive blocking can be cause bottlenecks on our environments so helping to mitigate it. Here we’re going back to basics to look at how it happens and how having effective indexes can reduce it. Blocking We’ll start off with an example of blocking. Here’s our customer table and […]
Andy Brownsword 