Is partitioning working?

Question

Post reply

Is partitioning working?

sql_er

SSCarpal Tunnel

Points: 4135
More actions
October 30, 2014 at 2:35 pm

#291996

Hi,
Is there a way to find out if partitions are being used?
Our scenario is as follows: We created a set of partitions, but I am not sure whether our queries are properly written to utilize the partitioning.
Are there DMVs out there or any other approaches which can tell us (based on the queries ran in the past) whether partitions are being used?
Please advise.
Thank you!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 1

Start by examining the execution plan.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Can you explain what you mean by is partitioning working?

If you have partitioned the table and have data that belongs on multiple partitions and the data is on the multiple partitions then partitioning is working. Are you getting a performance benefit from partitioning is a different question. Here's a good article by Gail Shaw about partitioning and query peformance, https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/ that I found on Brent Ozar Unlimited's partitioning resources page, http://www.brentozar.com/sql/table-partitioning-resources/

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

ScottPletcher SSC Guru Points: 101118 More actions · Answer 3

Not really any easy way to get on-going accurate data at the partition level.

View sys.dm_db_index_operational_stats might help, particularly if the index/partition is very busy and thus (almost) never leaves the cache. You could try capturing the relevant results from that view at intervals to check for usage.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".