-->
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.

Partitioning 5: The extra partition

So, we’ve set up a table with table partitioning on the RIGHT for CreationDate. We defined 11 partitions, and in Partitioning 4, we saw 11 partitions. However, if we look in sys partitions, there’s actually 12.

SELECT 
p.partition_number,
p.rows
FROM sys.partitions AS p
JOIN sys.tables AS t ON t.object_id…

Read more

0 comments, 26 reads

Posted in Arthur's Blog on 18 February 2019

Partitioning 4: Finding partitioning in DMVs and Object Explorer

Happy Friday! Today’s post will be light on the performance side and instead, I’m going to talk about how to take a look at your server’s partitioning.

First, partitioning in SQL Server Management Studio

Object Explorer, that thing on the left side of the SSMS window, will show partition info.… Read more

0 comments, 619 reads

Posted in Arthur's Blog on 15 February 2019

Partitioning 3: Beyond just partition elimination

In Partitioning 2, I showed how to analyze which partitions were accessed by our Index Seek. However, we were searching the entire year’s partition for data. What if we filtered more specifically on the partitioning key?

Yesterday’s query: redux

Taking the same query, but this time let’s just search… Read more

0 comments, 119 reads

Posted in Arthur's Blog on 14 February 2019

Partitioning 2: Getting and measuring partition elimination

In Partitioning 1, my query searched through all 1-12 partitions. Let’s see if we can reduce that number.

Adding the partitioning key to your query

Since I created the index in part 1 on the Posts_Partition_Scheme, the index was split up among the partitions. What if I only want… Read more

0 comments, 624 reads

Posted in Arthur's Blog on 13 February 2019

T-SQL Tuesday #111 – Why I blog

This month’s T-SQL Tuesday is hosted by Andy Leonard at andyleonard.blog . Andy asks us, what is your “Why?”

So what is my Why? Well, I want to talk about a few things I do.

My current job and why

Currently I work primarily as a performance focused DBA. I… Read more

0 comments, 622 reads

Posted in Arthur's Blog on 12 February 2019

Partitioning 1: Non-partition aligned queries

Here’s my take on partitioning. I’ll be focusing on getting queries to perform on partitioned tables, and not on partition maintenance, which can be its own headache. This is the first part in a series I’m planning to write, so this post may not answer all the questions.

Partitioning disclaimer

Read more

0 comments, 219 reads

Posted in Arthur's Blog on 11 February 2019

Performance on readable replicas in Availability Groups

I apologize in advance if I mess up the terminology.

I’ve worked with a moderate read workload on a readable replica and I wanted to share some of the things I’ve seen.

First, how in sync is your replica?

To make an Availability Group replica readable, you have to set… Read more

0 comments, 672 reads

Posted in Arthur's Blog on 8 February 2019

Dynamic Data Masking in the execution plan

I think Dynamic Data Masking is pretty cool. The idea is basically to provide a mask for certain users when they might see protected data. The documentation on this feature is actually pretty deep, it’s worth a look.

I just want to show you how you can see the… Read more

0 comments, 677 reads

Posted in Arthur's Blog on 7 February 2019

Bad indexing can show up in wait statistics

At first, this statement might sound a bit confusing. Usually, we expect wait statistics to show us what a query is waiting on, whether it’s waiting on memory, loading pages from disk, or any of the other numerous wait types.

Once you start collecting wait statistics, you’ll have a… Read more

0 comments, 131 reads

Posted in Arthur's Blog on 6 February 2019

Two T-SQL features that I appreciate

We occasionally get cool improvements in T-SQL in newer versions of SQL Server. Here’s a short post on some of the improvements that I like a lot.

DROP [x] IF EXISTS

Introduced in SQL Server 2016.

When I first saw this command, I was like, awesome! This makes deploy… Read more

0 comments, 129 reads

Posted in Arthur's Blog on 5 February 2019

SQL Server Monitoring: Keeping track of missing index requests

A lot of us turn to execution plans when we see a slow running query, and it’s not uncommon to see missing index requests. If you’re a developer who comes across a missing index request in their execution plan, keep in mind that there can be more than one request… Read more

0 comments, 124 reads

Posted in Arthur's Blog on 4 February 2019

SQL Server Monitoring: What queries should you monitor?

I previously wrote about measuring wait statistics. This matters a lot, because you can track historically what wait statistics are generated during your normal workload, and then compare to your current wait statistics.

Once you’re measuring that, what else should you be measuring? Well, I’d say your plan cache.… Read more

0 comments, 157 reads

Posted in Arthur's Blog on 2 February 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, 136 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, 166 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,018 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, 185 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, 285 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, 915 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,304 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,510 reads

Posted in Arthur's Blog on 22 January 2019

Older posts