2
votes
1answer
362 views

Join tables in two databases make query slow? is better to partition db?

I have a table1 in db1 and table2 in db2 on SQL Server 2008 R2. If I make a select query joining both tables, it's really slow to get the results. A simple query like SELECT * FROM db1.dbo.table1 ...
2
votes
1answer
23 views

remove a paritition table

I need to remove a partition table for an application upgrade. It is SQL Server 2008 R2. The partition column is on column col1 data type smallint, null. Col1 has length 10 and precision 5. As you ...
0
votes
1answer
273 views

How to get accurate row counts in partitions in a table

I am trying to fetch row counts in each partition for a table. But I got confused as in sys.partitions row counts is on basis of indexes. So how can I get exact row count for partitions in a table.
10
votes
1answer
183 views

How can I interpret the results of these DMVs to help me evaluate our partitioning strategy?

Version: SQL Server 2008 R2 Enterprise Edtn. (10.50.4000) In attempt to evaluate our partitioning strategy, I wrote this query to get the access methods against indexes on partitions (in the broadest ...
6
votes
1answer
544 views

How accurate is the sys.partition.rows column?

The system view sys.partitions has a column "rows" that is the total number of rows in a given partition. For a table that is not partitioned (or only has one partition depending on how you look at ...
2
votes
2answers
354 views

Creating a partition on my soft-delete indicator column

In SQL Server 2008 R2 I am exploring the use of partitioning tables. We have a soft-delete scheme which is sometimes an IsDeleted bit NOT NULL and sometimes a DateDeleted date NULL I want to focus ...
3
votes
1answer
2k views

Large table and poor performance - what to do next?

We have a Windows 2008 R2 / SQL Server 2008 R2 (Standard) server that is used to host a single database. The database itself mainly consists of a single large table containing both live and historical ...
8
votes
2answers
8k views

How to partition an existing non-partitioned table

I have an existing table with data: dbo.Test (col1,col2,col3....) ON [PRIMARY] I need to change this table to be partitioned like this: dbo.Test(col1,col2,col3....) ON Ps_Date(Col2) How I can I ...
3
votes
1answer
1k views

How to take partitions or filegroups out of service

I'm an experienced SQL Server DBA but new to partitioning, and I have a couple of questions. Using SQL Server 2008 R2 Enterprise Edition. I've inherited a large customer metrics database that grows ...
1
vote
0answers
17 views

How to take partitions or filegroups out of service [migration] [duplicate]

I'm an experienced SQL Server DBA but new to partitioning, and a had a couple of questions. Using SQL Server 2008 R2 Enterprise Edition. I've inherited a large customer metrics database that grows by ...
5
votes
2answers
582 views

Recreate Indexes on 1 billion record table

I have a table with over 1 billion records and it has 6 indexes (including Clustered index (ID)). I need to partition this table on a new Clustered index with date column. I have just enough space ...
2
votes
1answer
179 views

How do I know if my database partitioning is done well? [closed]

I have a sneaking suspicion that the guy who wrote the partitioning functions at my place of work did a fairly poor job. For certain queries (maybe 20% of them) we see incredible performance boosts ...
2
votes
1answer
116 views

Unique Non Clustered Column in Partitioned table

We have partitioned one of our SQL Server tables on a Clustered Index datetime column, we have a Primary key Non-clustered index column with Identity data type and this column is setup as a ...
5
votes
2answers
818 views

Large Fact table and partitioning key dilemma

I have fairly large fact table (2 billion records, approx 120 GB). This table is not partitioned and the queries are very slow to respond. I am planning to partition the table and indexes. The table ...
5
votes
2answers
814 views

Get a record's current partition

In SQL Server 2008 R2, how would I determine what partition a record currently lives in?
1
vote
0answers
801 views

Pros/Cons of Using Table/Index Partitioning in SQL Server [closed]

We have a number of tables that were partitioned primarily to reduce problems when purging data (via sliding-window partitioning / truncate). I have found a number of caveats that come with ...
5
votes
2answers
432 views

Partition Function vs. Clustered Index

We have a table that is clustered on identity/datetime2. It is partitioned on the same datetime2. Are there any reasons to cluster on datetime2/identity instead? I understand the reasoning behind ...
1
vote
1answer
90 views

Where does it make sense to store Indexes for partitioned tables?

Prior to table partitioning, all the indexes were stored on a separate drive, per this question. Now that I partitioned a couple of tables, SSMS is offering to store indexes in the partition scheme. ...
3
votes
1answer
422 views

Why am I getting Table scan on a partitioned table query?

I have table Inventory_Break partitioned on IR_ID (int). I also created a non-clustered index for IR_ID on the table. When I run the following: SELECT * FROM dbo.INVENTORY_BREAK WHERE IR_ID ...
12
votes
3answers
2k views

Does the partition key also have to be part of the primary key?

I am partitioning a table based on a column that is not a primary key? I've read some conflicting information today on whether the partition column must be a part of the primary key. My gut says no, ...
1
vote
1answer
588 views

How many files should I create for one filegroup and 9 weekly partitions?

I have 9 weekly partitions on one filegroup. How many files do I need to create? There are 3 tables containing 46 million, 16 million and 10 million rows.
4
votes
1answer
370 views

SQL Server Partitioning - Parent / Child tables

I'm currently investigating implementing partitioning for a fairly hefty (800GB and growing) SQL Server database. All of the data in the tables is relatable to a date/time, however this date/time is ...
2
votes
2answers
925 views

Best practices for creating partition schemes and functions

I'm implementing partitioning for the first time in SQL Server 2008 R2. I've several large tables that I am partitioning. My main question is: Should I use the same Partition Scheme and Function for ...
0
votes
1answer
1k views

Reindexing Partitioned Table Indexes

Historic Reporting Server running SQL 2008 R2. I'm trying to tweak the fill factor. I have a job that records the fragmentation when the jobs runs in the early hours. For every table that had high ...
8
votes
2answers
3k views

SQL Server 2008 - Partitioning and Clustered Indexes

So let me preface by saying I do not have total control over my db design, so a lot of the aspects of the current system cannot be changed for the purposes of this scenario. Comments about how we ...