Tagged Questions
3
votes
2answers
56 views
Table partitioning filegroup
Can I use the same filegroup for multiple tables when partitioning a table? I partitioned a items table by year. It created five filegroups. Can I use the same file group for orders table or will I ...
1
vote
3answers
69 views
Improve performance by partitioning
I'm using SQL Server 2012. I’m using CTE to create an orders table from the items table. The final count for the orders table is around 120 million. It is taking about 2 hours for the whole process.
...
0
votes
2answers
81 views
Sync Indexes between two tables
I need to automate and Sync only indexes between two tables ( Primary and Stage_Table) within the same database.
I tried using SSIS SQL Server Objects Task, but looks like it works only when we sync ...
1
vote
3answers
135 views
Performance issues moving 1 million rows between tables
I want to replicate 1 million rows from table1 to table2. I have written a job with a query like this:
delete from table1 OUTPUT *.delete into table2
I am executing this job every hour, but it ...
1
vote
1answer
40 views
Key lookup partition pruning
I have a query inner joining multiple tables all of which are partitioned and have 10 partitions each:
SELECT A.COL1, B.COL2, C.COL3
FROM A
INNER JOIN B ON A.ID = B.ID
INNER JOIN C ON A.ID = C.ID
...
3
votes
0answers
108 views
How do I know if my database partitioning is done well?
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
2answers
147 views
Partitioning on a single filegroup
I have some very large tables in my database, but a substantial chunk of this data is "old".
Due to circumstances beyond my control, I am not allowed to remove this "old" data. The other ...
2
votes
1answer
68 views
Always On and Partition Swapping
Is it possible to use SQL 2012 Always On over two machines and perform partition swapping on the primary machine ?
4
votes
1answer
75 views
Expanding a partitioned table
I have a table that holds four years of data, where for each month I have a partition that is based on a filegroup with two files. Unexpectedly, I received eight more years of data and prepared the ...
4
votes
1answer
224 views
Why isn't partition-level lock escalation the default?
In SQL Server, locks are normally escalated from row or page -> table. Starting with SQL Server 2008, a new level of lock escalation was added - partition level.
However, this isn't automatically ...
3
votes
1answer
774 views
How to create a partitioned table based on date?
I'm trying to create a partitioned table where partitioning is determined by OrderDate. I'm trying to create a clustered index on OrderDate and a nonclustered primary key on OrderID. But I get an ...
2
votes
1answer
172 views
SELECT multiple sensor values in one query
Background
I have a couple of devices, each with a couple of sensors. I log these every now and then and stores them in a table described below. When someone requests a web page, I fetch a couple of ...
4
votes
1answer
345 views
Best way to increase no of partition in SQL Server Table
I am maintaining a database with a big big table, which portioned for each month. There is already tens of partition there but its till January 2013, So its time to create new partitions.
Can anyone ...
2
votes
1answer
202 views
Migrating database from SQL Server 2005 Enterprise to SQL Server 2008 Standard
I'm attempting to migrate a database from SQL Server 2005 Enterprise to SQL Server 2008 Standard. I've run into an issue because there is a partition function defined in the database of the Enterprise ...
1
vote
1answer
64 views
Partitions and Indexes, in which cases should be used
I know some knowledges:
Partitions used to achieve better performance (like described in this article)
And indexes used to increase performance in selection operations.
And as result my question: ...
1
vote
2answers
95 views
How to check if there is partitions in use in fact tables?
I'm going to take a first look of a large data warehouse with many fact tables. How can I easily check if there is partitions and partition functions in use in these fact tables?
2
votes
2answers
167 views
Investigating/Troubleshooting I/O Spikes
We have been experiencing some relatively large I/O (IOPS) spikes on the SAN that supports our production SQL Server.
The spikes seem to fall at an exact time after the hour, each hour. We have ...
1
vote
1answer
506 views
SSIS: “The buffer manager cannot write 8 bytes to file”
We have about 60 or more SSAS cubes on one server. All the cubes have the same structure. Each cube has a different size because each cube has a different underlying data warehouse. However the schema ...
3
votes
1answer
356 views
Why do we have to use SWITCH in table partitioning?
I'm a beginner in SQL.
I'd like to know what is the importance of SWITCH in table partitioning?
I've created one table partitioned month-wise, with a total of 12 partitions.
The ranges are:
...
1
vote
2answers
294 views
SQL Partioning on date, how does it work for future records?
I am setting up a new system and I would like to partition the data for speed. Basically what I would like to do is have one database with all the latest information (e.g. last 3 months). then have ...
4
votes
1answer
123 views
SQL Server data partitioning
This is for SQL Server:
I have a table that has been partitioned. Sometimes, I need to completely update an existing partition - the updates could be extensive enough to touch all rows of the ...
5
votes
2answers
199 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
190 views
Pros/Cons of Using Table/Index Partitioning in SQL Server
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
240 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 ...
9
votes
2answers
1k views
SQL Server does not optimize parallel merge join on two equivalently partitioned tables
Apologies in advance for the very detailed question. I have included queries to generate a full data set for reproducing the problem, and I am running SQL Server 2012 on a 32-core machine. However, ...
2
votes
0answers
614 views
How to structure Master/Detail tables for performance without denormalizing?
Some Background Info
We have a set of tables that holds all the transactions for our system, TransactionHeaders and TransactionDetails. There are roughly 80k Transactions a day which translates to ...
1
vote
1answer
60 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
152 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 ...
11
votes
3answers
796 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, ...
5
votes
2answers
118 views
Mechanics of partitioning
Imagine the following hypothetical database structure. Basically it's a top down structure (Country has Orders, Order has OrderLines, OrderLine has ShipLines, etc...).
I want to partition my SQL ...
7
votes
3answers
706 views
Table partitioning for archiving data
Scenario:
two databases: DB_A and DB_Archive with one very big table called
tableA.
every day, records older than 60 days are deleted from DB_A and moved
to DB_Archive mainly to leave thing ...
0
votes
1answer
148 views
strange situation with very large partitioning table
I have a very strange situation here...
On my SQL Box there is a large Table this table is partitioned, everything was fine until I forget to make new partition ranges. in the first picture the last ...
1
vote
1answer
476 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.
3
votes
1answer
190 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 ...
3
votes
1answer
187 views
Optimizing queries against a set of tables that contain partitioned data
I have a third party program that creates and populates a series of tables to hold some historical data:
data_33 -- contains data for 12/01/11 - 12/05/11
data_34 -- contains data for 12/05/11 - ...
3
votes
2answers
374 views
do partitions make sense when dates are not involved?
We are having some discussions about partitioning some application tables in SQL Server 2008 based on their quickly growing size. One is an event table that is typically used for INSERTS (80% of the ...
2
votes
1answer
692 views
ALTER TABLE SWITCH statement failed
ALTER TABLE SWITCH statement failed because column does not have the same ANSI trimming semantics in tables...
Does anyone knows how to fix that on already existing table full of data?
Well, I've ...
4
votes
2answers
5k views
How do I get a list of all the partitioned tables in my database?
How do I get a list of all the partitioned tables in my database?
Which system tables/DMVs should I be looking at?
3
votes
1answer
250 views
Full-text index in partitioned table
I have a table with just over a million records with a full-text index.
This table has been split in two in the past, and the data older than a certain date is moved to another table with the exact ...
1
vote
2answers
300 views
Create multiple files for each file group for table partitioning?
I am partitioning a table by date (month). The server has four drives. The partition scheme is defined as
CREATE PARTITION SCHEME ByDate AS PARTITION ByDate TO (
[PRIMARY], [fg2], [fg3], [fg4],
...
1
vote
2answers
786 views
Why the query scan all the partitions?
I have the following partitioned table
create table T (Month char(6), ID int, .... primary key (ID, Month)) on psMonth(Month)
The partition function is
CREATE PARTITION FUNCTION [pfMonth](char(6)) ...
1
vote
3answers
772 views
SQL Server 2008 Partitioning Evaluation
I'm trying to do a performance comparison of a table with partitioning and the same table without partitioning.
So we have Subcriber and SubscriberPartitioned tables.
Tables structure is:
...
2
votes
2answers
613 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 ...
1
vote
4answers
316 views
Top 2 rows per partition from an absolute value date difference
I have a database of security camera footage in a denormalized database. I have Locations which have multiple Cameras which take multiple images.
Location + Camera + image capture_date is the ...
0
votes
1answer
151 views
Multiple Partition Keys?
This is a follow-on from a previous question.
I've never worked with SQL Server partitioning but I currently faced with designing a database for which the volumes probably warrant it. The system is ...
7
votes
4answers
1k views
SQL Server Partitioning - what to use for partition key?
I've never worked with SQL Server partitioning but I currently faced with designing a database for which the volumes probably warrant it. The system is for coupons. The coupons are to be issued ...
2
votes
2answers
391 views
T-SQL: How to switch all partitions from one table to another?
Is it possible in a single statement to switch ALL partitions from one partitioned table to a staging table?
3
votes
2answers
438 views
Will creating partitions reduce locking and how do we implement this in sql-server?
A client wants our application to process more data faster so arranged a meeting with their dba to discuss options.
This application generates quite a lot of data that is used for reporting. Before ...
10
votes
2answers
1k views
Can I move rows between partitions by updating the partition key?
I would think that this would be a fairly simply question, but I've actually had a difficult time finding an answer for this.
The question:
Can you move rows of data within a partitioned table from ...
7
votes
2answers
2k 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 ...
