If you are using always on availability groups and need to connect to a read only replica, you might get the following error message in SSMS: Msg 978, Level 14, State 1, Line 1 The target database (‘Database’) is in an availability group and is currently accessible for connections when the application intent is set […]
Read MoreIndex seeks on date and CONVERT
Using functions on columns typically prevents SQL Server from being able to efficiently use an index with a seek (sargability). When it comes to DATE and DATETIME data types, there are some exceptions. A common use case I see is where a column contains a DATETIME data type; however, a query needs to look at […]
Read MoreFixing an implicit type conversion without a code change
If you have ever run into the problem of implicit type conversions you will be aware that the fix for it involves a code change or a table change. In a previous post I showed a common cause of such implicit conversions. In this post I will show a fix for this problem that does […]
Read MoreDealing With “Bad” Plans
SQL Server does the best it can to generate an efficient execution plan when a query is executed. This process can be expensive. To avoid the expense, SQL Server attempts to cache a plan once it is generated. This eliminates the need for subsequent calls to the same query to generate the plan. In an […]
Read MoreMaking Queries With Leading Wildcards Faster
Queries with leading wildcards generally are slow because they are not sargable. If by chance they pick up an index, they will usually full scan the index. Below is one example of a typical query that you might see when using a leading wildcard. SELECT * FROM CustomerAccount WHERE AccountNumber LIKE ‘%518370’ From looking at […]
Read MoreCreating a database per customer VS single database for all customers (SQL Server)
With so many applications being built as SAAS offerings, an important decision that must be made when defining the data architecture is: Should each customers’ data be in a separate database, or can a shared database (keyed by a customer identifier) be used for all customers. Pros of database per customer There’s a variety of […]
Read MoreWhat’s filling up tempdb?
If you’ve ever received an alert that tempdb was running out of space, you probably want to know what session/query is consuming tempdb. There are two ways tempdb gets consumed. User tempdb usage and system tempdb usage. User temdpb usage is related to creating and populating @table variables or #temporary tables and explicitly populating them. […]
Read MorePlan guides made easy
Plan guides are a useful tool for changing an execution plan when despite your best efforts the optimizer just isn’t coming up with the optimal plan. This becomes useful for situations where you can’t make code changes to a query or stored procedure. People are sometimes hesitant to use them due to the complexity in […]
Read MoreTuning Substring Query Without Changing the Query
Introduction The blog shows how to tune a query using substring in the WHERE clause. The tuning solution is to use indexing on a computed column. Performance Issue A General Ledger financial report execution in Financial system takes a long time finishing more than 4 hours. Performance Analysis The first step in the performance tuning […]
Read MoreHow to view query store id’s related to a sql/plan handle or query/plan hash.
On occasion you will have a sql_handle, plan_handle, query_hash, or query_plan_hash and need to know the query store ID related to it. One such example is with a deadlock. Deadlocks contain sql_handles. A key part of diagnosing deadlocks is viewing the execution plan. Query store is a good place to view the execution plan and […]
Read More