April 15, 2015 at 3:54 am
I have a heavy database , More than 100 GB only for six month .
every Query on it takes me along time and I dont have enough space to add more indexes.
by a way
I decided to do partitioning.
I create a partition function , on date filed and all Data records per month was appointed to a separate file.
I'ts done and ready.
But who to apply this function on current data?
And is partitioning only for Future data entry?
thank you
April 15, 2015 at 4:27 am
Partitioning is not a performance feature and whether it's going to speed up some queries or not heavily depends on how queries are written. If every single query uses the partitioning key as a filter predicate, you'll get partition elimination. If not, you'll end up scanning/seeking all partitions, which may be even worse than a single seek/scan on the unpartitioned table.
Partitioning also brings in additional complexity that you should be aware of.
I suggest that you test your workload thouroughly before even considering partitioning.
If you don't have space for additional indexes, ask for more. If this database really matters to your business, you'll get the space.
-- Gianluca Sartori
April 15, 2015 at 4:31 am
When you create the partitioning, data will be moved to the correct partition.
That said, it's not likely to help your performance problems, partitioning is for maintenance and fast data loads/deletes
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2015 at 5:00 am
When you create the partitioning, data will be moved to the correct partition.
But It didn't work.
I did these steps : (All By script no Wizard)
-- Step 1 -- Choose table and field For partitionng
-------------------------------------------------------------------
-- Step 2 -- Add FileGroups
-------------------------------------------------------------------
-- Step 3 -- Add File And related To file Group
-----------------------------------------------------------------------
-- Step 4 - Create Partition Function
------------------------------------------------------------------------
-- Step 5 - Craete partition schema
------------------------------------------------------------------------
After these steps , All Files have the same size as when craeted. And data didn't move.
April 15, 2015 at 10:01 pm
Befor Partitioning :
Primary FG : data File 1 = 100 GB
--------------------------
After Partitioning :
Primary FG : data File 1 = 100 GB
FG_2015_01 : data file 2 = 10 MB
FG_2015_02 : data file 3 = 10 MB
FG_2015_02 : data file 4 = 10 MB
... And Data about months (according to Partitining function) did not split (move) to related files ...
Why ?
April 16, 2015 at 12:17 am
You need to
alter ixmyindex on mypatitiontable rebuild on partitionscheme(MycolumnIAmPartitioningBy)
Also your index rebuild jobs must be smart enough to rebuild onto the partitionscheme and not the default on primary else it will rebuild your indexes as a contiguous index.
What you can do is give us the partitioning function, scheme and script out the table and alll indexes exactly.
i.e. create table mytable(mycolumns) on [primary] etc. the full statement.
April 19, 2015 at 12:40 am
Hi
Your Comment has error :
alter index [NonClusteredIndex-MydateFiled] on MyTable rebuild on MyParScheme([MyDateFiled])
And I Did this :
My Table had an cluster index on id.
I Remove it And Create New NonCluterIndex On MyDate Filed. (It Took me about 1 Hour and 30 min!!!)
CREATE NONCLUSTERED INDEX [NonClusteredIndex-MydateFiled] ON [dbo].[MyTable]
([MydateFiled] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
But After that I dont see any change in files and file Group.
--------
I was thinking about it and Found that in any Steps of my partitioning I didnt use this table name????
1- Create New FG
2- Create New Files And related them to FG
3- Create Patition Function
4- Create Scheme Function
And Where is my table and my field That I want to Partitioning on that ?
April 20, 2015 at 7:01 am
Any Thing?
April 20, 2015 at 7:05 am
MotivateMan1394 (4/19/2015)
--------I was thinking about it and Found that in any Steps of my partitioning I didnt use this table name????
1- Create New FG
2- Create New Files And related them to FG
3- Create Patition Function
4- Create Scheme Function
And Where is my table and my field That I want to Partitioning on that ?
None of that partitions the table. That's setting up the files and the partition function.
To partition a table, you need to create or rebuild the clustered index on the partition scheme
And once more with feeling... Partitioning is NOT a performance tuning mechanism. You're likely to get little to no improvement in performance just by partitioning the table, you may well get a performance degradation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 20, 2015 at 7:18 am
MotivateMan1394 (4/15/2015)
I have a heavy database , More than 100 GB only for six month .every Query on it takes me along time and I dont have enough space to add more indexes.
by a way
I decided to do partitioning.
I create a partition function , on date filed and all Data records per month was appointed to a separate file.
I'ts done and ready.
But who to apply this function on current data?
And is partitioning only for Future data entry?
thank you
Partitioning is an advanced feature which two experts have already advised is unlikely to resolve your performance problem. Why persist with it?
Have you evaluated the effectiveness of the indexes you already have?
Have you examined the workload on the server?
Have you inspected expensive (slow) or frequently-run queries for opportunities for optimisation?
Each of these is far more likely to help with your performance issues than implementing partitioning. Glen Berry has an excellent suite of queries [/url]covering these investigations and a whole load more, which can help you discover what the real problems are with the server/database.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 20, 2015 at 7:21 am
April 20, 2015 at 11:12 pm
Hi and thank you
Your advices changed my perspective about partitioning and relations to Performance.
OK
But the truth is that I had raised this issue, I must go to the end.(Only Partitioning Not performance)
Then I should First do the partitioning after that I must separated it with performance issue.
again thank you
And about my objects :
This is my partition scheme :
CREATE PARTITION SCHEME [PSchemaLog] AS PARTITION [PFunctionLog] TO ([FG_201408], [FG_201409], [FG_201410], [FG_201411], [FG_201412], [FG_201501], [FG_201502], [FG_201503], [FG_2015])
GO
This is my Partition Function :
CREATE PARTITION FUNCTION [PFunctionLog](date) AS RANGE LEFT FOR VALUES (N'2014-08-31T00:00:00.000', N'2014-09-30T00:00:00.000', N'2014-10-31T00:00:00.000', N'2014-11-30T00:00:00.000', N'2014-12-31T00:00:00.000', N'2015-01-31T00:00:00.000', N'2015-02-28T00:00:00.000', N'2015-03-31T00:00:00.000')
GO
more over
I have a Table : MyTable and a field : MyDateFiled
April 21, 2015 at 12:21 am
Was more looking for table definition.
What you can do is join sys.indexes to sys.partitions and filter on objectname of parentobjectid =yourtable And indindex_id <2.
This will show you your table and the partitions.
If you only have one row then the table is not partitioned.
If you have many rows then look at the rowcount to see if the distribution is what you expect.
April 21, 2015 at 3:58 am
select I.Object_id,name,I.index_id ,partition_id ,partition_number
from sys.indexes I Inner Join sys.partitions P On P.Object_id= I.Object_id
And I.object_id = 1109578991
This is result
1109578991 ---- NULL ---------------------------------------- 0 --- 72057594042515456 ---1
1109578991 ---- NULL ---------------------------------------- 0 --- 72057594042646528 ---1
1109578991 ---- NonClusteredIndex-Regdate --------7 --- 72057594042515456 --1
1109578991 ---- NonClusteredIndex-Regdate --------7 ---72057594042646528 --1
But steal the size of my new mdf files are the default size and all data are in old mdf files.
in the other way :
SELECT o.name objectname,i.name indexname, partition_id, partition_number, [rows]
FROM sys.partitions p
INNER JOIN sys.objects o ON o.object_id=p.object_id
INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
WHERE o.name LIKE '%Mytable'
The result :
MyTable -----NULL -----------------------------------72057594042515456 ---1 ---- 711986018
MyTable -----NonClusteredIndex-Regdate ------72057594042646528 ---1 ---- 711986018
April 21, 2015 at 4:08 am
MotivateMan1394 (4/21/2015)
But steal the size of my new mdf files are the default size and all data are in old mdf files.
Yes, because you haven't partitioned any of your tables yet.
You've created the partition function, which controls what data would go into what partition. You've created the partition scheme which says which partitions go to which filegroup, but you haven't yet rebuild any of the tables or indexes onto the partition schemes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply