SQL Server 2008 :: Index Alignment In Partitioning?
Apr 1, 2015
I've yet to use partitioning in a production environment, and pretty much last ran any partitioning related code a few years back when looking at certification; so I'm definitely not an expert on the matter and only loosely clued up on the concepts.
I've recently started with a new employer, and they have just implemented a new system for sms messaging. The database tables tracking the sms messages being sent are going to get big and so they have created decided to implement partitioning on some of the tables using a partition scheme on the CreatedDate column; the DBA involved in designing the partitioning has left and I'm picking this up.
The relevant DDL for the table is below:-
CREATE TABLE [Message].[Sms](
[SmsId] [bigint] IDENTITY(250000001,1) NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_Sms:CreatedDate] DEFAULT (getdate()),
CONSTRAINT [PK_Sms:SmsId] PRIMARY KEY NONCLUSTERED
[code]....
There are some issues with the above that I will be addressing seperately (e.g. the clustered index should be unique as it contians the unique key, and the fillfactors are daft), but my concerns for this post are below.
1) How to define the Primary Key and enforce it's uniqueness whilst trying to ensure it's aligned with the partition in order to be able to switch out old data once an as yet undefined retention period has passed. In books online it states:- "If it is not possible for the partitioning column to be included in the unique key, you must use a DML trigger instead to enforce uniqueness. " Books online - Special Guidelines for Partitioned Indexes. However, I'm not sure what this means, nor how I create the primary key to use the partition function seeing as it doesn't have the CreatedDate in the unique key?
2) The original partition function was envisaged as the following:-
CREATE PARTITION FUNCTION [DateFunction](datetime) AS RANGE
LEFT FOR VALUES (N'2014-01-01T00:00:00.000'
, N'2014-04-01T00:00:00.000'
, N'2014-07-01T00:00:00.000'
, N'2014-10-01T00:00:00.000'
, N'2015-01-01T00:00:00.000'
, N'2015-04-01T00:00:00.000'
, N'2015-04-02T00:00:00.000'
, N'2015-04-03T00:00:00.000'
, N'2015-04-04T00:00:00.000'
, N'2015-04-05T00:00:00.000')
GO
There is a procedure that has been created and scheduled daily that will create a new partition for each day, and then merge these together at the end of the quarter. My understanding of partitioning is that this is a bad idea, as it will result in merging several populated partitions together. Is my understanding correct? If so, I'm planning on removing the day partitions at the end of the function, and simply adding quarterly partitions, maintaining a spare empty partition at the end of the table. Would this make more sense?
View 9 Replies
ADVERTISEMENT
Jul 1, 2015
Normally it is recommended to leave an empty partition on both the front and back ends of a table to avoid data movement when merging/splitting. But I have some questions based on my scenario, which is a table partitioned by a load date, so all records in a partition contain the same date, not a range of dates.
If I use range left, once I switch out the first partition it would become empty, so would there be data movement when I merge it into the next partition? The real issue though is that we will not just be removing the first partition, but "random" partitions throughout the table. Will this work?
If I use range right, when I split the last partition to create a new one it doesn't seem there would be any data movement there either. Am I missing something?
Basically I'm wondering if I should use range left or right. Most recommend using right, but then the boundary value is not the value in the partition. This could potentially result in someone deleting the wrong data if they are not careful. So is there any reason not to use left in my scenario?
I did a test of removing a partition in the middle and it worked just fine; this was using range right. I have about 6 million rows per partition. I also tested splitting at the end and it worked fine. I'll rebuild it with range left and test.
View 0 Replies
View Related
May 8, 2013
we planning to create partitioning on existing tables. The partitioning is on date column, there should be one partition for each year.
Creating of new partitions should be automated, and also we dont have any plans of archiving old data, all we want is that new partition creation should be automated.
View 6 Replies
View Related
Feb 9, 2015
We have an existing BI/DW process that adds large chunks of data daily (~10M rows) to an existing table, as well as using Deletes to remove stale data. This scenario seems to beg for partitioning to support switching in/out data.
After lots of reading on this, I have figured out the mechanics of the switching, bit I still have some unknowns about the indexes needed to support this.
The table currently has several non-clustered indexes, including one on the partitioning column - let's call that column snapshotdate. Fortunately there are no FKs involved, and no constraints.
Most of the partitioning material I see focuses on creating a clustered PK to assist with switching. Not sure if this is actually necessary, but assume I create one using an Identity column (currently missing) plus snapshotdate.
For the other non-clustered, non-unique indexes, can I just add the snapshotdate to the end of the index? i.e. will that satisfy the switching requirement?
View 1 Replies
View Related
Apr 30, 2007
We had data in tables for multiple users (Logins) .Each user data is identified by a one column named €œUSER€?. No user has direct access to tables and only through views .we have created views and stored proc .Views will perform DML operations on tables using condition WHERE USER=SUSER_SNAME() (i.e Logged in user).So no point of getting others user data.
Each table has a column USER and we are queering data based on login user .this is the foreign key of USER table. Each view contains user column in where clause .So for every query we are searching all records .instead of that is there any way to get data with out searching all records.
I heard about table Partitioning, index Partitioning, view Partitioning. Are they helpful to boost my query performance?
And also let me know is there any good way of designing apart from above options
View 3 Replies
View Related
Sep 24, 2015
I have a table which has cluster index on col1 column. If i insert 10 into my table what would be cluster index key value?Is it going to be 10 as well? How do i get cluster index key value?
View 5 Replies
View Related
Feb 27, 2015
After reading some comments here I decided to look at tables to see if any had a clustered index that was a unique identifier. Yep. So if I have a table with a unique identifier as the primary key/clustered index and an identity column that is indexed, I would like to make the identity a clustered index (maybe even the primary key) and make the unique identifier a unique non-clustered index (not the primary key).
Does this sound reasonable?If I do this will I need to drop and recreate the other indexes? Or maybe just rebuild the other indexes?
Currently:
CREATE TABLE Payments (
IDX INT IDENTITY(1,1) NOT NULL,
GUID UNIQUEIDENTIFIER NOT NULL DEFAULT(NEWID()),
.....
-- many other columns
);
GO
ALTER TABLE [dbo].[PAYMENTS] ADD CONSTRAINT [PK_PAYMENTS_GID] PRIMARY KEY CLUSTERED ([GUID] ASC);
GO
CREATE NONCLUSTERED INDEX [IX_Payments_ID] ON [dbo].[PAYMENTS] ([IDX] ASC);
GO
Would like:
ALTER TABLE [dbo].[PAYMENTS] ADD CONSTRAINT [PK_PAYMENTS_IDX] PRIMARY KEY CLUSTERED (IDX ASC);
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Payments_GUID] ON [dbo].[PAYMENTS] (GUID ASC);
GO
View 9 Replies
View Related
Apr 8, 2015
I just did index defragmentation for some databases include MSDB . I notice there are 3 indexes from MSDB database that fragmented quickly ( I did rebuild last nite at 10 PM - > fragmentation level becomes zero but today at 9 am it become 80 % ).The indexes are backupsetuuid, backup media family uuid, backupmediasetuuid. I am thinking to set the fill factor for those indexes = 80 respectively.
View 6 Replies
View Related
Jul 1, 2015
We are adding 4-5 indexes to one database and dropping 2 unused indexes. I don't have proper testing environment. How to monitor these indexes changes? Do we need to run any baseline but we don't get load all the time same load all the days
View 8 Replies
View Related
Jul 9, 2015
Does including non-key columns work for the performance of an index?
View 8 Replies
View Related
Oct 30, 2015
Give a user table ‘MyTable’. How to know whether the table contains a non-unique clustered index by using SQL query?
View 2 Replies
View Related
May 31, 2015
I am new to mssql server. There is a table on one of my databases that occupies a lot of space. And the space usage is as follows: (all values in KB)
reserved: 42329064
data: 16272288
index: 26050032
unused: 6744
This table takes up almost 80% of my database size, and the information is this table just captures the time spent by a user on the website(not very critical data)
I would like to know how to delete the entire index (which is what is occupying most space) to free up disk space. the index is a clustered index.
View 3 Replies
View Related
Jun 17, 2015
I run a query
select col1, col2, col3, col4
from Table
where col2=5
order by col1
I have a primary key on the column.The execution plan showing the clustered index scan cost 30% & sort cost 70%..When I run the query I got missing index hint on col2 with 95% impact.So I created the non clustered index on col2.The total executed time decreased by around 80ms but I didn't see any Index name that is using in the execution plan.After creating the index also I am seeing same execution plan
The execution plan showing the clustered index scan cost 30% & sort cost 70% but I can see the total time is reducing & Logical reads on that table is reducing.I am sure that index is useful but why there is no change in the execution plan?
View 7 Replies
View Related
Aug 2, 2015
I am extremely new to database design, and I ran into a problem that I know comes up often, however has many opinions...
Basically I have a table that is going to have 50+ columns. The natural key on this table is actually 8 columns wide, 4 of them being Varchar columns by default. (varchar(50)'s).
I have added an identity column, (1,1) to the table, however I put the clustered index on the 8 natural keys... My plan is to rebuild the clustered index once nightly when the system isn't in use (after 7 pm).
I know others would say it would be better to have the clustered key on the 1,1 column and then add indexes on the other 8 fields... However I don't quite understand why honestly...
Every single query against this table will use the 8 columns, and will NOT use the Identity column (1,1) because they are calls from other systems that do not know the Identity column....
Therefore if your database is set up for query speed, and every single query has to have a value for 8 columns to get a valid result, does it make sense to put a clustered index over the 8 columns?
If not why? Why is putting a clustered index on an identity column (that will literally never be used in a query) a better solution?
View 9 Replies
View Related
Sep 30, 2015
Table Name: Denominator
Already has the following constraint:
PK_Denominatorclustered, unique, primary key located on PRIMARY DenominatorID
How can I add a unique key that will cover the 3 fields --> MemberID,MeasureID,TimePeriodID
I also want to know whether we can include the " WITH ( IGNORE_DUP_KEY=ON ) "
View 3 Replies
View Related
Oct 14, 2015
that violates the targets referential integrity?I am getting error Msg 2601, Level 14, State 1, Line 1Cannot insert duplicate key row in object XXX with unique index YYY.The statement has been terminated.I would like to know if there is a way to examine or determine what source rows are not conforming to the unique index.I'm fine with dropping and reestablishing the index, and i know its cataloged somewhere because during index creation, the error message does tell you the row details clobbering index creation. Ideally i would like to be able to trap all the failing rows and see what i can do about rehabilitating them or ignoring them or managing them some other way, but id like to know what the server knows when it will not create the index.
View 2 Replies
View Related
Apr 22, 2015
What is the best way to forecastestimate space for non-clustered index on a table?
Example :
Table name : Test123
Row : 170000000
Reserved : 18000000 KB
Data : 70000000 KB
Index: 40000000 KB
Note: Test123 already has clustered index and 2 non clustered indexes.
View 7 Replies
View Related
Sep 15, 2015
I have query with an expensive Key Lookup on a joined table. The predicate is the column that I'm joining on, and the output list contains two columns from the joined table.
I've created a basic non-clustered index covering the predicate column and include-ing the two output columns. However, the execution plan ignores this, and insists on using the primary key of the joined table to do the expensive key lookup. I've tried adding the included columns to the index directly and there's no change. I've also tried running dbcc freeproccache and no change.
View 3 Replies
View Related
Jul 16, 2015
We noticed a deadlock 3-4 weeks ago on a table (table1) and deadlock graph was captured.
When I am analyzing the deadlock graph, page number using DBCC PAGE, I am getting the object id for a different table (table2). But deadlock graph shows the name of the object as table1.
Is it possible that subsequent defragmentation of indexes would have caused the respective page id to got re-allocated to a different table? I checked the deadlock graph lately only after 3-4 weeks.
View 1 Replies
View Related
Jan 9, 2004
Greeting
I have more than one language installed on my computer, and I tried to use my Enterprise Manager yesterday... What Im getting is with all the GUI im using Im having everything is Right Aligned.....
Any help as to what I should change?
Thanks
This is my first post so would be great to have a qucik and handy answer :D
View 8 Replies
View Related
Jun 29, 2015
I have question about tempdb needs to be configured 100GB 64kb block size.its fresh installation.
how to allocate the file sizes.still im not sure how many log files needs to be created with 100GB equals to 64KB block size.
what is 64KB block size and how to divide the logfiles 64KB into the 100gb or 50GB?
what is 64 KB cluster has 128 sectors?
tempdb drives should be formatted with a 64K allocation? how many files needs to created for good performance with 50GB or 100GB? ot 1TB
View 3 Replies
View Related
Jul 19, 2006
i'm using sql server 2005 partitioning feature, when i update a record in 100 record partition it takes the same time as 1000000 records partition why, could any one help
View 1 Replies
View Related
Jan 8, 2007
Ananth writes "A new feature is established in SQL Server 2005. That is Partitioned Table. I went through several articles in net regarding partioned table. But I did not get any generalized version of Partitioning.
For example I partitioned a table into 4 Filegroups based on Transaction Date. So I used range function to give ranges for 4 quarters of one year. It works fine. But I would like to generalize this scenario. It will work for one year. If new year comes ,I have to write new script and execute the same.
The Partitioned function should work for 12 months in a given year. So here year is the paramter and then that function should be construct based on the year automatically.
Can anybody give me answer asap."
View 1 Replies
View Related
Dec 2, 2006
Is there any benefit in creating seperate file groups for a partitionedtable on a multi-processor server with RAID5 and 1 Logical Drive?
View 8 Replies
View Related
Oct 18, 2012
We have a new database with cdc enabled on all of its tables. This causes the index maintenance task to fail with following message:
"Executing the query "EXEC DBName.dbo.IndexDefrag_sp" failed with the following error:  "The unique index 'PK_TableName' on source table '[dbo].[TableName]' is used by Change Data Capture.  To alter or drop the index, you must first disable Change Data Capture on the table.  The transaction ended in the trigger. The batch has been aborted.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly" We would like to run the index maintenance without losing the cdc data. We plan on installing SP2 on SQL Server 2008 R2 soon, would that solve the issue? Disabling the cdc prior to index maintenance and then re-enabling back upon completion; would delete the data as I found in most discussions, but we would like to retain it.Â
View 4 Replies
View Related
Jan 23, 2004
I was wondering if Microsoft SQL Server support any partitioning method (like range, list, hash or composite partitioning in Oracle) or you have to partition all tables manually?
Thanks in advance,
Bracia
View 1 Replies
View Related
Dec 3, 2014
I need Dynamic Partition of SQL Table.
1. What is the best practice for partitioning (on date column)
2. The project on which i am working correctly have a case where in i get the update of my status flag after few days (Say 15 - 30) in that case if my data got into partition table how to update and how to search which partition has my data
3. Is creating partition occupies more disk space?
4. Is every partition would need index?
View 7 Replies
View Related
Aug 20, 2007
Dear Friends,
my server is sqlserver 2005. iam writing sps in sql 2000 . if i open in sql 2005 the alignment of my sps is changed. i have changed the tab order of 2005 as equal to 2000.then also the problem comes. so plz give me solution to solve the problem.
View 1 Replies
View Related
Feb 20, 2007
Does anyone else have this issue?
When making a report with VS2005, things get spaced out wierd and dropped down when deployed to the web?
I can't get things like images to position properly. Does it have anything to do with the fact that I am using a Matrix?
For example, I create an image and embed it, I have it all the way to the left of the form and when deployed it is more towards the middle of the page?
Very frustrating, just wondering if anyone has any tips/solutions.
View 4 Replies
View Related
May 14, 2007
After i upload the report to server , and try to convert it to excel, the alignment of report is out!!the width of columns is different with the width i set in design..=.= however, the report is pretty when i preview in reporting servcise design.
Anyone face same problem?
View 2 Replies
View Related
Feb 25, 2014
Script to do the table partitioning for a 500gb in sliding window technique?
View 9 Replies
View Related
Aug 11, 2006
Hi there,
I'm using the SQL 2005 Express Edition and i would like to know if there's any way to change the Table alignment to Right To Left.
Please if you can, send me your reply to my E-Mail address: zaki666@zahav.net.il
Hope you can help me,
Isaac
View 1 Replies
View Related
Feb 26, 2007
Hi All,
I am working on a report, in this report a textbox is present, which is expected to show string. This string length is larger than what textbox can accommodate in single line. I want this textbox to show only right part of the string in case it can accommodate full string.
After using the right alignment I assumed it would do show, however it does not work like that. It always shows the left part of string in case, string is bigger than width of textbox.
Restriction from design:
1. Cannot increase the width of textbox.
2. Cannot use the Can Grow = true to show string in multiple lines.
Below is the example of my issue:
Say there is a string: "Microsoft SQL Server Reporting Services".
because the textbox width is not sufficient to see full string, I want to "Reporting Service". Which is of same size which column can accommodate. So I used Right alignment in textbox. however text box shows "Microsoft SQL Server". So, is there a solution to see required text in the textbox.
View 1 Replies
View Related