-->
SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Arthur's Blog

Arthur Daniels is a database administrator with a passion for improving and troubleshooting database performance. Away from the keyboard, Arthur likes to hike and travel.

Archives: January 2019

How many plans are in the plan cache for a stored procedure?

It depends on where you’re looking and how many statements are in the stored procedure.

Let’s take a look at some demos!

First, let’s create a stored procedure with multiple statements

USE StackOverflow2010
GO
CREATE OR ALTER PROCEDURE TestStatements (@Username NVARCHAR(100)) AS
BEGIN

SELECT Reputation FROM Users
WHERE DisplayName  =…

Read more

0 comments, 131 reads

Posted in Arthur's Blog on 31 January 2019

Index maintenance freebies

I didn’t expect anything for free in index maintenance. After all, it takes a lot of CPU and transaction log space to remove fragmentation.

Let’s talk about two things that SQL Server does for us: one statistic is created per index we create, and when we rebuild that index, we… Read more

0 comments, 161 reads

Posted in Arthur's Blog on 30 January 2019

Query tuning: The IN clause

This post will cover the IN clause and another way to rewrite the same logic. I don’t intend to say that IN is better or worse than other T-SQL, I just want to showcase what it does.

We’ll jump right into the demos. I’m using StackOverflow2010 for this, and I’m… Read more

3 comments, 1,014 reads

Posted in Arthur's Blog on 29 January 2019

My alternative to sp_WhoIsActive

I use sp_WhoIsActive a lot. I’m extremely grateful for Adam Machanic writing it. It has tons of options that let you specify exactly the information that you need for troubleshooting.

So why did I write a different query to get activity?

There’s some systems where sp_WhoIsActive is too slow. It… Read more

4 comments, 184 reads

Posted in Arthur's Blog on 28 January 2019

Memory Grants part 6: Memory used in aggregate functions (Min/Max/etc)

So far in this series, I’ve been focusing on the Sort operator. It’s pretty easy for demos since I can just write a statement with an ORDER BY and there will be memory requested.

However, there’s another way to get a memory grant easily. That’s when you need to do… Read more

0 comments, 283 reads

Posted in Arthur's Blog on 25 January 2019

The Execution plan comparison feature (in SSMS)

Did you know that you can compare two execution plans in SQL Server Management?

It’s really cool. I use it a lot, as my first stop to compare performance. Let’s take two execution plans from my series on parameter sniffing.

Demo comparing execution plans

CREATE PROCEDURE [dbo].[QueryPostType] (@PostType INT)…

Read more

0 comments, 912 reads

Posted in Arthur's Blog on 25 January 2019

It’s always parameter sniffing (Part 2): SET options

This is the sequel to “It’s always parameter sniffing (part 1).” In that post, we identified the stored procedure, and found a plan in cache that had some weird execution times.

This time, we’ll try to reproduce the issue in SQL Server Management Studio, and I’ll show why… Read more

0 comments, 2,254 reads

Posted in Arthur's Blog on 23 January 2019

Memory Grants part 5: Query hints

Proceed with caution, if you’re thinking of using query hints. This post will specifically cover min_grant_percent and max_grant_percent.

My opinion on query hints is that you’re often better off tuning the query, than using a query hint. Especially if you have time to consider using a query hint, you should… Read more

0 comments, 2,498 reads

Posted in Arthur's Blog on 22 January 2019

Memory Grants part 4: The Resource Semaphore

Part of query memory grants, part 4! This post will cover the wait type RESOURCE_SEMAPHORE briefly, but the focus is on what a semaphore is.

What the wait? Why does my system have RESOURCE_SEMAPHORE waits?

SQL Server only has so much memory to distribute to its queries. To decide who… Read more

0 comments, 139 reads

Posted in Arthur's Blog on 21 January 2019

New page on this blog: Contact Me

This is not a technical post! Just wanted to say that I set up a public email address for this blog. Find that and my Twitter/Linked on my Contact Me page.

Have a great weekend!


Read more

0 comments, 105 reads

Posted in Arthur's Blog on 19 January 2019

Memory Grants part 3: How much memory can one query get?

When you build a brand new shiny SQL Server, you want to get a lot of memory so your queries run faster, right? More memory for your data pages in the buffer pool and more memory for all the queries that keep sorting data in the database instead of the… Read more

0 comments, 2,769 reads

Posted in Arthur's Blog on 18 January 2019

When the query plan hash is deceptive

When you’re querying the plan cache, you need to know the four primary objects: the query hash, query plan hash, sql handle, and plan handle. However, the query plan hash recently surprised me.

Take a look at this index (in StackOverflow2010)

CREATE NONCLUSTERED INDEX [ix_Location_CreationDate] ON [dbo].[Users]…

Read more

0 comments, 701 reads

Posted in Arthur's Blog on 17 January 2019

A brief introduction for bcp

I end up using the bcp utility to move tables between environments, and I’ve found that a lot of the guides on the internet go into great detail on how to use it. That’s fine if you have a lot of time. Here’s my brief introduction so that if I… Read more

0 comments, 2,487 reads

Posted in Arthur's Blog on 16 January 2019

It’s always parameter sniffing (part 1?)

This is a blog post about a perennial issue. Have you ever had a call in the middle of the night about a slow webpage and you know nothing in the webpage changed?

Yeah, it’s probably parameter sniffing.

First, some clarification

All parameter sniffing is not bad. It’s usually a… Read more

0 comments, 2,931 reads

Posted in Arthur's Blog on 16 January 2019

SQL Server Monitoring: What to start measuring

So you’ve got a SQL Server that you want to monitor. What should you monitor? In my opinion, at bare minimum, you want to collect wait statistics. Let’s talk about a basic wait statistics collection.

Our new best friend: dm_os_wait_stats

This is the only place we need to query. It’s… Read more

1 comments, 2,498 reads

Posted in Arthur's Blog on 14 January 2019

Execution plans: RetrievedFromCache

I first came across the value for RetrievedFromCache when I was reading a confusing execution plan. At first, I thought this could be really useful. If this value was true, you could assume that another query had compiled the execution plan that your query used and your query had retrieved… Read more

2 comments, 172 reads

Posted in Arthur's Blog on 11 January 2019

Why is the query hash so useful?

An example of how I use the query hash

I wrote a blog on what the query hash is, right here. That post describes the query hash, but it doesn’t help you if you’re looking at a slow query.

This post will try to answer that, in a few… Read more

0 comments, 59 reads

Posted in Arthur's Blog on 10 January 2019

Query memory grants part 2: Varchars and sorting

Why the heck did we make all our columns varchar? That’s a hypothetical question, please.

But now we have to pay for our decisions. Consider a query that has to do some sorting. Let’s make a table that needs some sorting.

The varchar sort

CREATE TABLE Names_Varchar
    (Id INT IDENTITY(1,1),…

Read more

2 comments, 64 reads

Posted in Arthur's Blog on 9 January 2019

Basic PowerShell: Get-Member

Disclaimer: I’m still learning PowerShell. I’ve been using it more lately and I want to talk about it more. I’ll blog about the commands that I find useful.

Get-Member

Think of this command as the help button.

This is my current favorite command for troubleshooting an existing script. In PowerShell,… Read more

0 comments, 29 reads

Posted in Arthur's Blog on 8 January 2019

Query plan hash

This is post 3 in the series about query fingerprints. Here’s the previous posts in this series:

What is it

A query plan hash is a hash made from the operations in a specific execution plan. I’d like to say that every single execution plan has… Read more

0 comments, 35 reads

Posted in Arthur's Blog on 7 January 2019

Older posts