Tag: create temp table sql

Attribution

SQL Server doesn’t do much to help you figure out what’s wrong with it. Sure, it makes a lot of information available, but logging, decoding, and assembling all that information is hard work.

And it sure doesn’t help that a lot of it is stored in XML. Even when there are graphical options for displaying it, they end up hiding a lot of the really useful information.

It also has a lot of optional things that you have to turn on, and all of these things require you to monitor and alert on them.

In this post, I’m going to talk about why you should turn on Query Store, the Blocked Process Report, and capture deadlocks, and link you to free tools to analyze them.

This is all stuff that I help clients with as well, along with actually helping them fix the problems, but if you’re feeling keen on getting to it yourself, here’s what you should do.

Query Store

This thing is great for most SQL Servers out there. It’s like the plan cache, except it won’t keep disappearing on you.

To get it up and running, just run this command:

ALTER DATABASE 
    [YourDatabase]
SET QUERY_STORE 
(
    OPERATION_MODE = READ_WRITE,
    MAX_STORAGE_SIZE_MB = 1024,
    QUERY_CAPTURE_MODE = AUTO
);

After it’s turned on, you can check in on it in two ways:

• Through the GUI in SSMS
• With my stored procedure, sp_QuickieStore

Whichever one you use, you’re going to be in much better shape than hoping what you need is in the plan cache after something went bump in the night.

EXEC sp_QuickieStore
    @database_name = 'your awesome database';

Just running this will go back up to seven days and give you the top 10 queries by average CPU time. In other words, it’s the stuff that uses the most CPU whenever it runs.

If you find stuff you don’t care about, just copy and paste the contents of the query_id column, and do this to ignore them:

EXEC sp_QuickieStore
    @database_name = 'your awesome database',
    @ignore_query_ids = '1, 2, 3, 4, 5, 6, 7, 8';

Blocked Processes

The next thing you’ll wanna do is start logging which queries are blocking each other. You never know when this will ratchet up and become a real big problem.

The number of times I hear “we had a bad blocking problem” but no one even has a screenshot of it hanging out would boggle the very depths of your mind.

To avoid that, you need to:

• Enable the blocked process report
• Enable something to collect it

First, do this:

EXEC sys.sp_configure
    N'show advanced options',
    1;
RECONFIGURE;
GO
EXEC sys.sp_configure
    N'blocked process threshold',
    5; --Seconds
RECONFIGURE;
GO

This will get SQL Server capturing any blocking events that go on longer than five seconds. This is about the level where people start hitting problems, but you can set it to a higher number if you feel like it.

Next, you’ll want to set up an Extended Event to house the data from it:

CREATE EVENT SESSION 
    blocked_process_report
ON SERVER
    ADD EVENT 
        sqlserver.blocked_process_report
    ADD TARGET 
        package0.event_file
    (
        SET filename = N'bpr'
    )
WITH
(
    MAX_MEMORY = 4096KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = ON
);
GO

ALTER EVENT SESSION
    blocked_process_report
ON SERVER 
    STATE = START;
GO

If you’re using Azure SQL DB, you’ll need to tweak this to use it successfully. It’s a lot harder to successfully set up and access event files there, and the ring buffer is far less valuable because it doesn’t hold onto data very long.

The easiest way to get at the data in there is to use my stored procedure sp_HumanEventsBlockViewer.

It’s as simple as pointing it at the event session you created, but there are a whole bunch of other parameters to filter data in or out.

EXEC dbo.sp_HumanEventsBlockViewer
    @session_name = N'blocked_process_report';

Deadlocks

The final thing you should do is set up something to collect deadlocks in the long term.

To do that, Extended Events is your friend all over again.

CREATE EVENT SESSION 
    deadlock
ON SERVER
    ADD EVENT 
        sqlserver.xml_deadlock_report
    ADD TARGET 
        package0.event_file
    (
        SET filename = N'deadlock'
    )
WITH
(
    MAX_MEMORY = 4096KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = ON
);
GO

ALTER EVENT SESSION
    deadlock
ON SERVER 
    STATE = START;
GO

While this stored procedure isn’t in my GitHub repo, it’s one that I do a heck of a lot of work on. The easiest way to parse everything out is with sp_BlitzLock.

It’s as simple as pointing it at your extended event session.

EXEC dbo.sp_BlitzLock
    @EventSessionName = N'deadlock';

Nice To Have

Having these three things set up and running will make your life a lot easier the next time you face a performance emergency with your SQL Server.

Sure, there are a lot of other things that could go wrong, but these are the most common areas I see clients having problems in.

If you need to do a post mortem on an issue, you might want to look at:

• sp_LogHunter: Dig through your error logs for high severity issues
• sp_HealthParser: Dig through the system health extended event for a wide variety of issues

And as always, if you need help working through these kinds of problems, hit the link below to get some consulting help from me.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

A Little About Select Queries That Cause Writes In SQL Server

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Oh, Just Do it.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Big Noise

I was helping a client with an issue recently where they wanted to allow certain admin users to override changes currently in a table, but not allow anyone else to make changes.

The thing is, users had to be allowed to make other changes to the table, so it wasn’t something that could be handled easily with security features.

The example I’m going to show here is simplified a bit to get the code across to you, so keep that in mind.

Now, at first I was thinking about using some fancier features that SQL Server has to offer:

• CONTEXT_INFO
• sp_getapplock

But after talking over with the Most Valuable Kiwi, those ideas quickly lost their luster. They would both require additional handling in case of errors, and sp_getapplock could have issues under concurrency.

What turned out to be the best idea was actually the simplest: create a temp table with a very random name (in this case, running SELECT NEWID() and removing the dashes from the result) and having the trigger look for that to decide whether or not it should allow the update.

See below for the demo code.

Thanks for reading!

USE Crap;
GO 

/*Out ye devil*/
DROP TABLE IF EXISTS
    dbo.the_table;

/*Okay you can come back*/
CREATE TABLE
    dbo.the_table
(
    id int PRIMARY KEY IDENTITY,
    dt datetime NOT NULL DEFAULT SYSDATETIME()
);
GO 

/*One row is fine.*/
INSERT
    dbo.the_table
(
    dt
)
VALUES
(
    DEFAULT
);
GO 

/*Check me out*/
SELECT
    tt.*
FROM dbo.the_table AS tt;
GO 

/*
The trigger, and why:

Other options like SESSION_CONTEXT and sp_getapplock
require a whole bunch of other handling and can be weird
with connection pooling and under high concurrency
 * Clearing out session context when done
 * Error handling for sp_get applock to release locks
 * A whole list of other stuff!

*/
CREATE OR ALTER TRIGGER
    dbo.the_trigger
ON dbo.the_table
INSTEAD OF UPDATE
AS
BEGIN
    IF 
    (
           ROWCOUNT_BIG() = 0
        OR OBJECT_ID(N'tempdb..#B7E66DC66D9C4C4182ECCF583D126677') IS NULL
    )
    BEGIN
        RAISERROR('Only admins can update that column!', 11, 1) WITH NOWAIT;
        RETURN;
    END;
    ELSE
    BEGIN
        SET NOCOUNT ON;

        IF UPDATE(dt)
        BEGIN
            UPDATE t
              SET t.dt = i.dt
            FROM dbo.the_table AS t
            JOIN Inserted AS i
              ON i.id = t.id;
        END
    END;
END;
GO 

/*
Create a #temp table for the trigger to pick up on
*/
CREATE OR ALTER PROCEDURE
    dbo.the_admin_procedure
(
    @id int,
    @dt datetime
)
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;

    CREATE TABLE
        #B7E66DC66D9C4C4182ECCF583D126677
    (
        the_crumb bit
    );

    UPDATE t
        SET t.dt = @dt
    FROM dbo.the_table AS t
    WHERE t.id = @id;
END;
GO 

/*
Non-admin users don't get the temp table
*/
CREATE OR ALTER PROCEDURE
    dbo.the_normal_procedure
(
    @id int,    
    @dt datetime
)
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;
    
    UPDATE t
        SET t.dt = @dt
    FROM dbo.the_table AS t
    WHERE t.id = @id;
END;
GO 

/*
In real life, you might be able to figure it out based on login names
*/
CREATE OR ALTER PROCEDURE
    dbo.the_real_procedure
(
    @id int,    
    @dt datetime
)
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;

    IF SUSER_SNAME() = 'sister_minnie'
    BEGIN
        CREATE TABLE
            #B7E66DC66D9C4C4182ECCF583D126677
        (
            the_crumb bit
        );        
    END;
    
    UPDATE t
        SET t.dt = @dt
    FROM dbo.the_table AS t
    WHERE t.id = @id;
END;
GO 


/*
Show and tell
*/
EXEC dbo.the_normal_procedure 
    @id = 1,
    @dt = '19000101';

EXEC dbo.the_admin_procedure 
    @id = 1,
    @dt = '99991231';

SELECT
    tt.*
FROM dbo.the_table AS tt;

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Triggering

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

The Old Joke Goes

A security engineer walks into a bar and then

• Runs into a bar.
• Crawls into a bar.
• Dances into a bar.
• Flies into a bar.
• Jumps into a bar.

And orders:

• a beer.
• 2 beers.
• 0 beers.
• 99999999 beers.
• a lizard in a beer glass.
• -1 beer.

When you’re designing and testing SQL Server stored procedures (or views or functions or queries), you need to do the same thing.

While most of it isn’t a security concern, though it may be if you’re using Row Level Security, Dynamic Data Masking, or Encrypted Columns, you should try executing it as other users to make sure access is correct.

When I’m writing stored procedures for myself or for clients, here’s what I do.

Try To Reveal Bad Parameter Sniffing

Sometimes it’s easier than others, but here’s what you should try:

• For equality predicates, run some count queries against those columns to find data skew
• For range predicates (like dates) try small and large ranges to see if the plan changes
• Try passing in NULL or blank values, especially for any string parameters

When you do this, grab and compare the execution plans. If you get crazy different plans, see what happens when you don’t recompile, and plans get shared across executions.

If performance is bad, think about these things:

• Can you improve indexing?
• Do you need a temp table?
• How many CTEs do you need to throw in the garbage?
• Should you use dynamic SQL to generate different plans?
• Maybe a recompile hint would be good for a specific query?

This is at the top of my list for new code, because I don’t want it to get called in a weird way and go haywire. That’s not what people pay consultants for.

Speaking Of Dynamic SQL

If you’re not sure if yours is safe from SQL injection, now is the time to find out.

Especially for long-ish string parameters, try passing in some nefarious commands. In general, what you don’t want to see is dynamic SQL like this:

DECLARE
    @s nvarchar(MAX) = N'',
    @d nvarchar(40) = N'Jon Skeet';

SELECT
    @s += N'
SELECT
    c = COUNT_BIG(*)
FROM dbo.Users AS u
WHERE u.DisplayName = ''' + @d + N''';'

EXEC sys.sp_executesql
    @s;

This is unsafe dynamic SQL, because it accepts user input and concatenates it into a string.

There are safe ways to accept user input, as long as either:

• The user input dictates a static string to append to the dynamic SQL
• The user input is parameterized within the dynamic SQL

Something like this is an example of taking user input and having it dictate a static string:

DECLARE
    @s nvarchar(MAX) = N'',
    @t nvarchar(40) = N'Votes';

IF @t = N'Users'
BEGIN
    SELECT
        @s += N'
    SELECT
        c = COUNT_BIG(*)
    FROM dbo.Users AS u;'
END;

IF @t = N'Votes'
BEGIN
    SELECT
        @s += N'
    SELECT
        c = COUNT_BIG(*)
    FROM dbo.Votes AS v;'
END;

EXEC sys.sp_executesql
    @s;

But this is a case where you should see what happens when you pass a lizard in a beer glass.

And of course, parameterized dynamic SQL looks like this:

DECLARE
    @s nvarchar(MAX) = N'',
    @d nvarchar(40) = N'Jon Skeet';

SELECT
    @s += N'
SELECT
    c = COUNT_BIG(*)
FROM dbo.Users AS u
WHERE u.DisplayName = @d;'

EXEC sys.sp_executesql
    @s,
    N'@d nvarchar(40)',
    @d;

Run It From The Application

In SQL Server, there are these things called ANSI settings, and they can really screw with performance and execution plans.

Even if you’re not using indexed views, computed columns, or filtered indexes, you may see oddball things if you’re testing in SSMS and running code from somewhere else.

This is what SSMS uses, and what SQL Server needs to effectively use those indexed views, computed columns, and filtered indexes.

+-------------------------+----------------+
|       SET options       | Required value |
+-------------------------+----------------+
| ANSI_NULLS              | ON             |
| ANSI_PADDING            | ON             |
| ANSI_WARNINGS 1         | ON             |
| ARITHABORT              | ON             |
| CONCAT_NULL_YIELDS_NULL | ON             |
| NUMERIC_ROUNDABORT      | OFF            |
| QUOTED_IDENTIFIER       | ON             |
+-------------------------+----------------+

You’ll need to check your application(s) to see what they’re using, and make adjustments where necessary.

Bonus points if you’re using the Python or JDBC drivers, and you turn off those pesky implicit transactions.

This Is A Good Start

You may have other things to test, like if you’re offloading reads in an Availability Group, using security features, or other logical constructs.

If your code has a lot of variable assignment in it, you may want to override the lookups with static values to see what happens if some out of band values (especially NULLs) come around.

I do this a lot in the SQL Server Troubleshooting stored procedures that I write to make sure things go down the right path and the right things happen.

You should too.

No one expects the unexpected.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Another One

This month, Grant has chosen the form of his destructor T-SQL Tuesday topic as Extended Events.

While my relationship with Extended Events is complicated for many reasons:

• Awful documentation
• Hardly any guidance on usage
• Almost nothing useful about what type of target to use when
• Everything stored in XML
• Slow, unfriendly GUI in SSMS

My need to use them while consulting outweighs my gripes and grievances about how Microsoft has chosen to write about, use, and present the data to you.

That’s where my stored procedure sp_HumanEvents comes in handy. It’s totally free, and open source. I built it because I needed to be able to get things going quickly without a lot of fuss and clicking around.

It will set up and pull data to help you track down issues in the following areas:

• Blocking
• Query Performance
• Compiles
• Recompiles
• Wait stats

I chose to leave deadlocks out of it, because the system health extended event session captures a good deal of those by default. It has a very limited amount of data from a toned-down blocked process report in it too, but it’s missing a lot of good information.

You can find the full documentation for it here, and also by using the @help parameter in the procedure itself.

My Favorite Martian

While it can do many things, the way I use it most often is to capture the long-running queries and plans from a stored procedure I have running in SSMS, so I can filter out the little query plans that don’t need my attention, and focus on the really bad parts.

To do that, just run this code:

EXEC sp_HumanEvents
    @event_type = 'query',                   
    @query_duration_ms = 5000,               
    @session_id = N'58',                    
    @keep_alive = 1;

That will set up an extended event that captures the following events, focused in on a single session id, and only collect details about queries that run for more than 5 seconds.

Don’t forget to replace the @session_id with whatever your session ID is running locally.

• sqlserver.module_end    
• sqlserver.rpc_completed  
• sqlserver.sp_statement_completed
• sqlserver.sql_statement_completed
• sqlserver.query_post_execution_showplan   

Some of them are a bit duplicative, but this was really designed to be flexible for a lot of different situations.

Once that runs, look for a session called keeper_HumanEvents_query in your Extended Event sessions in SSMS.

From there, you can right click to Watch Live Data, and then start running whatever is in your query window.

Every time a query takes more than 5 seconds, you’ll see the statement and query plan entries show up in the data viewer.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Because I’m A Consultant

And generally, the more NOLOCK hints I see, the more money I know I’m going to make.

It shows me four things right off the bat:

• The developers need a lot of training
• The code needs a lot of tuning
• The indexes need a lot of adjusting
• There are probably some serious bugs in the software

Perhaps the only other thing that signals just how badly someone needs a lot of help is hearing “we’re an Entity Framework only shop”.

Cha-ching.

Because No One Knows What It Does

With the utmost confidence, I’ve heard hundreds of developers say “it keeps my query from taking locks”.

Would that I could take that confidence and share a shred of it with everyone in the world, we could conquer space in a fortnight.

So I get to do fun things like explain to them how it still takes some locks, but mostly how it ignores locks taken by other queries, and that’s why it can:

• See the same row twice with the same values
• See the same row twice with different values
• Miss rows entirely

And in highly transactional systems, that can cause real problems. Even in systems where the load isn’t anything insane, it can cause all sorts of bugs and race conditions.

And best of all, because I get to hang around deleting those hints after switching over to Read Committed Snapshot Isolation.

Because I Get To Keep Writing About It

And no matter how much I do, no one listens. At all. Ever.

Week in and week out, I work with brand new people who have no idea that their results are full of incorrect, inconsistent data, and general bugginess.

I could schedule this same post every week for the next five years, and I’d still see the same amount of people with the same problems, over and over again.

Seriously. I love it. It’s the gift that keeps on giving.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

A Reintroduction To sp_PressureDetector

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

How I Set Up Debugging In SQL Server Stored Procedures

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.