#execution plans

[thedbahub]

Unleashing the Power of SQL Server Query Optimizer Fixes

Introduction Hey there, fellow SQL Server enthusiasts! Today, I want to share with you a game-changer in the world of database performance tuning: Query Optimizer Fixes. As someone who has spent countless hours optimizing queries, I know firsthand how frustrating it can be when your carefully crafted queries don’t run as efficiently as you’d hoped. But fear not, because Query Optimizer Fixes are…

View On WordPress

[thedbahub]

Unlocking the Power of LAST_QUERY_PLAN_STATS in SQL Server

Introduction As a SQL Server developer or administrator, you’re always looking for ways to optimize query performance and ensure your databases are running smoothly. One powerful tool that can help you achieve this is the LAST_QUERY_PLAN_STATS feature. In this article, we’ll dive into what LAST_QUERY_PLAN_STATS is, how it works, and whether it’s safe to enable in your SQL Server…

View On WordPress

[sqlcan]

Dissecting SQL Server Execution Plans

Dissecting #SQLServer Execution Plan by @GFritchey.

I remember my days before, Microsoft SQL Server PFE.  I wanted to learn everything and know everything about SQL Server.  However, getting hold of good resources was tough, as I didn’t have any mentor when I started down my journey to becoming a SQL Server Database Administrator. Along the way I did pick up lots of books and references.  One of such books is Dissecting SQL Server Execution Plans.…

View On WordPress

[thzinc]

This is madness! This is CONVERT_IMPLICIT!

Posted on November 2, 2012 by Daniel James

I, like a few others I know around here, enjoy getting the semantics of things right. Even if two things are functionally equivalent, correctly identifying each of the things makes me feel warm and fuzzy. Take for example, SQL Server User Defined Types. No, not the SQL Server 2005 CLR-based User Defined Types. Just the plain ol’ SQL Server 2000-style User-Defined Types. The ones that are just a friendly alias over a system type. Here’s an example of what I’m talking about:

-- Define that all usernames should be a variable, 64-character string CREATE TYPE username FROM VARCHAR(64) NOT NULL

In this example, I defined a type called username. Under the covers, it’s just a simple varchar(64), but anywhere I want to store a username, I can use the alias username instead. It’s functionally equivalent, but semantically more descriptive.

Let’s use it in a table:

-- Define a table that uses the new [username] type CREATE TABLE Users ( UserId INT NOT NULL IDENTITY(1,1) PRIMARY KEY, Username username )   -- Create an index on Username CREATE INDEX idx_Username ON Users (Username) INCLUDE (UserId)   -- Insert some sample users INSERT Users (Username) VALUES ('Administrator'), ('Jim'), ('Sandy'), ('Bob'), ('Miguel')

Now, the username type functions just like a varchar(64), as seen in the INSERT statement. I didn’t have to do anything special to convert a normal varchar string to username before inserting it into the table.

Now, I can query it just as easily, even using a differently sized varchar to pass into the query:

-- Execute the query as a developer might through SQL Server Management Studio DECLARE @Username VARCHAR(13) = 'Administrator'   SELECT UserId FROM Users WHERE Username = @Username

This works just as expected. And looking at the execution plan, everything looks to be in order.

It’s using my idx_Username index to perform a seek, which is optimal in this case. So, as a developer of this query, I’m left to think that executing this query should be no problem in my code (which happens to use ADO.NET). However, this is where things get crazy. ADO.NET takes any parameterized queries and wraps them in sp_executeSql. This typically is a very good way of ensuring that query plans get cached and reused, parameters are dealt with nicely, and keeps generated code in check to a certain degree. ADO.NET does not know about the username alias, so it passes along the string in the most suitable equivalent type it can determine. The sp_executeSql procedure then executes the query:

-- Execute the query as ADO.NET would generate it EXECUTE sp_executeSql N' SELECT UserId FROM Users WHERE Username = @Username ', N'@Username nvarchar(13)', N'Administrator'

But SQL Server chooses a different execution plan this time, using an index scan instead of a seek!

This is crazysauce, and appears to be caused by the fact that sp_executeSql executes in a different context from the calling query, which does not have knowledge of the extant user-defined type, username. So, the query optimizer in this different context does what it thinks is best and performs a CONVERT_IMPLICIT on the value stored in the table before attempting to compare it to the scalar value passed as a system type.

There are a couple of workarounds to this. One is to try to change the type passed in to the sp_executeSql query. Doing this in SSMS works just as well as the non-sp_executeSql query.

-- Execute the query as I want ADO.NET to generate it EXECUTE sp_executeSql N' SELECT UserId FROM Users WHERE Username = @Username ', N'@Username username', N'Administrator'

However, ADO.NET does not support passing a non-CLR User-Defined Type name on a SqlParameter. So the second option is to re-declare a variable of the correct type inside the query text. Hokey, but it functions:

-- Execute the query as ADO.NET would generate it, with the workaround EXECUTE sp_executeSql N' DECLARE @_username username = @Username   SELECT UserId FROM Users WHERE Username = @_username ', N'@Username nvarchar(13)', N'Administrator'

This makes me a sad panda. Won’t Microsoft think of the pandas?

[shsl-soccerplayer]

What is Jamie's execution like?

((Um… to be honest anon I can’t think of a good enough execution for the boy. I remember Athena(Karin-mun if you didn’t know) wrote out an execution for Jamie a while ago, and it worked for the time, but as I developed him more it looses some of the despair…? If that makes sense.

But yeah, I remember the two things I thought of as I was thinking of the execution(but still haven’t managed to do) was that a noose around his neck and possible strangulation might have to be the death at the end, and obviously soccer would have to be involved. I’m just trying to optimize the most despair for my bby, so if anyone could help me with that that would be lovely~))