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 […]
When looking to migrate, consolidate or deprovision parts of a SQL solution it’s key to understand the dependencies on the objects inside. Identifying dependencies can be challenging and I wanted to demonstrate one way to approach this. We’ll start with some objects across a couple of databases: The simplest option to find references within a […]
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 […]
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 […]
The DISTINCT clause in a query can help us quickly remove duplicates from our results. Sometimes it can be beneficial to stop and ask why. Why do we need to use the clause, why are we receiving duplicates from our data? I see this typically due to a JOIN being used where we don’t really want all of those results. This could […]
Andy Brownsword 