Table Partitions & RAID 5.

Aug 28, 2007

Hi experts,

We have a huge table with around 250 million records and have implemented SQL server 2005's new table partitioning feature. Now the data seems to be evenly spread across 20 different filegroups ( each 5 GB approx ) for the same table that was occupying 100 GB itself in the PRIMARY filegroup earlier.

Still the query response times have not come down drastically but we could see a good improvement in the execution plans now.

WE ARE USING RAID 5 IN OUR PRODUCTION ENVIRONMENT. ANY IDEA / THOUGHT ON HOW TO PLACE THE PARTITIONED FILEGROUPS AND THE LOG FILES IN THE RAID 5 (BTW , I'm very new to RAID concepts , any detailed instruction would be helpful ).

Any help would be greatly appreciated.

Thanks,

Hariarul

View 8 Replies


ADVERTISEMENT

Any Benefit To Logical Partitions On A RAID 5 Array?

Mar 23, 2000

Hi all,

Quick question in setting up a 3-disk SQL 7.0 system - can anyone think of a benefit to segregating a single RAID 5 disk array into numerous logical partitions for separating out the OS, the database files and the transaction logs? I would assume performance would be unaffected (as the drives are acting as a single array for reads & writes anyway) so other than general organization what (if any) advantage would be gained over making a single large logical partition?

TIA
A

View 2 Replies View Related

SQL Server Databases On RAID 5 Or RAID 10

Apr 4, 2007

I am configuring a new database server, without SAN access, and want to know what is the best practice for SCSI RAID configuration. Do most folks prefer RAID 5 or RAID 10 configurations where their databases will reside?

View 8 Replies View Related

Better Table Management (partitions?)

Oct 31, 2006

Hi,

For my work I am now learning Sql server 2005 and I have been given a database that has been set up by someone else to work with. It is my job to get the database ready for use in reports.

My problem is that the current database has one huge table with almost 8GB of data. The table contains data from 2004 to present (and growing) from 14 different countries. The reports we use are mostly per country, but we also want to compare the 14 countries to eachother for say, whole 2006.  At the moment the table is stored in one single file instead of using partitions.

I believe partitions can give a good performance boost when running the queries. But how do I do this? Currently the country codes are just plain text, can they be used for partitions?

Any advice would be welcome,

Thanks!

View 5 Replies View Related

Table Partitioning On A RAID 5 Server

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

SOS - Table Partitions Or Indexed Views.

Feb 26, 2008

Hi Experts,

We have a very huge database that stores 12 years of data(120 Million records). But our application mainly accesses past 3 years data i.e , the queries would scan the 120 million records even when it actually has to scan 30 million records alone (for 3 years).

Since few other important applications needs access to all the 12 years data, we are in a position to have 12 years data in the same database.

Right now we are looking for an approach that would help us to efficiently access the 3 years data alone and boost the performance.

1. Will SQL server table paritioning help in this scenario ?

Or

2. Indexed views would help us ? Is it possible to create indexed views based on year range and access the views in the stored procedures ?

Any help would be greatly appreciated.

Thanks in advance,

Hariarul

View 4 Replies View Related

Table Partitions On Existing Tables.

Jun 4, 2007

Hi guys , assuming right now I already create partition function (PF_Date) and partition scheme (PS_Date). Let say I would like to implement the partition on the existing tables ( eg: transaction table which is in PRIMARY filegroup), how am I switch it from PRIMARY to PS_Date ? Is it I have to re-create the particular table then only able to put in the partition scheme? Hope can get any assistance here. Thanks alot.



Best Regards,

Hans

View 8 Replies View Related

SQL Server 2008 :: How To Add More Partitions To Existed Table

Dec 31, 2011

I have a table that contains records of transactions with ID column is primary key

I use partition follow ID column, each partition have 1 million records.

CREATE PARTITION FUNCTION [pfTBLTRANS_ID](int) AS RANGE LEFT FOR VALUES (1000000, 2000000, 3000000, 4000000, 5000000, 6000000, 7000000, 8000000, 9000000, 10000000)

CREATE PARTITION SCHEME [psTBLTRANS_ID] AS PARTITION [pfTBLTRANS_ID] TO ([GROUP01], [GROUP02], [GROUP03], [GROUP04], [GROUP05], [GROUP06], [GROUP07], [GROUP08], [GROUP09], [GROUP10], [GROUP11])

But now I have more records with IDs that are greater than 11.000.000. So how can I add more partitions to this table ?

View 9 Replies View Related

Keeping A Table Moderatley Sized (partitions?)

Jan 29, 2008

Guys,

First off, I'm not very familiar with SQL Server. I need some guidance on what the best path to take is for this as it may not even be table partitions.

I have a huge table (155 million rows) and it's gotten so large than I can't even delete a large set of rows from it (i.e. delete everything older than 6 mo, which would be ~100 million rows). When trying to run a delete like this, it just goes for a LONG time and then just eventually runs out of memory.

The current data in this table can actually be completely cleared out soon (after Feb 1st) and I plan to do this with TRUNCATE TABLE, or just DROP and recreate. Once I do this, I want to create a way to keep this table moderately sized so it never grows that large again and it seems table partitions may be the way to go for this?

I'd like to keep the last 6mo of data in it (I have a datetime column to keep track of this). Anything older I'd like automatically removed. Can I do this with table partitioning? Create 6 partitions that store the 6 most recent months of data and everything older automatically gets dropped off?

If not partitions, what do you suggest to keep this DB modest size?

Thank you.

View 9 Replies View Related

Master Data Services :: Table Partitions In MDS?

Apr 13, 2015

Perhaps this task is not for MDS.... But another tool for rapid development & startUp - we don't have. And nevertheless....

We created table managers_plan in MDS :

year
month
id_manager (domain attr)
POS (domain attr)
plan_sum_USD
plan_unit
----------------------------
Entities:
Managers ~ 800 records
POS  ~ 100 000 records

managers_plan Total records for
1 year = 100K x 12 = 1 200 000

managers_plan  - table partitions  
- Will bemade ?

View 3 Replies View Related

DB Design :: Steps To Extend Table Partitions

Jun 1, 2015

SQL 2014 I've inherited a db that has several partitioned tables.  They are partitioned by month.  We're approaching the last partition, 11-30-2015, so we need to extend the tables.  My question is how do I do this?  There are Partition Schemes and Partition Functions setup at the db level.  I've figured out how to ALTER those.  Next I go to a table that I know is partitioned, right-click Storage and select Manage Partitions. 

My only option is to "create a staging table for partition switching".  Not knowing what switching is, I'm not sure if this is what I want to do.  All I want to do is add new partitions to the table - and remove some of the old ones since they are empty due to archiving of data.

So, what is the proper steps to adding new partitions to a table that is already partitioned?

View 4 Replies View Related

How Trustworthy Is Sys.partitions As A Means For Counting Rows In A Table?

Nov 20, 2007



Hi all,
I have the following function:




Code Block
create function udf_CountRows(@pTableName sysname)
returns int
as
begin
declare @ret int

select @ret = SUM(p.rows)
from sys.partitions p
inner join sys.objects o
on p.object_id = o.object_id
and o.[name] = 'Well'

return @ret
end





Can I trust sys.partitions to always return the correct value or does it suffer the same issue as sysindexes prior to SQL2005?

Thanks
Jamie

View 5 Replies View Related

SQL Server 2012 :: Partition Existing Table And Archive One Of The Partitions

Jun 22, 2015

I have some table that need to be partitioned and archive one of the partitions.

I did this in Oracle several years ago but not in SQL Server.

I'm looking for a basic example on how to do this.

I know the basic steps but the examples that I found on the Web were not quite what I'm looking for.

[url][/
Partition an existing SQL Server Table
url]

View 9 Replies View Related

Transact SQL :: Update Table With Max Value And Row Number (based On 2 Column Partitions)

Sep 15, 2015

I have 3 columns. I would like to update a table based on job_cd and permit_nbr column. if we have same job_cd and permit_nbr, reference number should be same else it should take max(reference number) from the table +1 for all rows where reference_nbr column is null

job_cd permit_nbr reference_nbr

ABC1 990 100002
ABC1 990 100002
ABC1 991 100003
ABC1 992 100004
ABC1 993 100005
ABC2 880 100006
ABC2 881 100007
ABC2 881 100007
ABC2 882 100008
ABC2 882 100008

View 3 Replies View Related

RAID 1 Or RAID 5 For Mdf Files?

Mar 27, 2008

I've always heard that RAID 5 (or better, RAID 10) is preferred for the actual database (mdf), but RAID 1 for logging.

If I have a dedicated physical volume for each, what's the performance hit for selecting RAID 1 for the MDF files? 3%, 20%, 200%?

Doing so (all RAID1) will allow me to have a separate physical volume for the TEMP database - that is heavily used by my app.

View 1 Replies View Related

RAID 5 Beats RAID 10

May 1, 2006

RAID 5 beats RAID 10Can I get some feedback on these results? We were having some seriousIO issues according to PerfMon so I really pushed for RAID 10. Theresults are not what I expected.I have 2 identical servers.Hardware:PowerEdge 28502 dual core dual core Xeon 2800 MHz4GB RAMController Cards: Perc4/DC (2 arrays), Perc4e/Di (1 array)PowerVault 220SEach Array consisted of 6-300 GB drives.Server 1 = Raid 103, 6-disk arraysServer 2 = Raid 5 (~838 GB each)3, 6-disk arrays (~1360 GB each)TestWinner% FasterSQL Server - UpdateRAID 513Heavy ETLRAID 516SQLIO - Rand WriteRAID 1040SQLIO - Rand ReadRAID 1030SQLIO - Seq WriteRAID 515SQLIO - Seq ReadRAID 5MixedDisktt - Seq WriteRAID 518Disktt - Seq ReadRAID 52000Disktt - Rand ReadRAID 562Pass Mark - mixedRAID 10VariesPass Mark -Simulate SQL ServerRAID 51%I have much more detail than this if anyone is interested.

View 13 Replies View Related

Data Warehousing :: Will Creating Partitions On Table Increase Insert Speed

Oct 8, 2015

I have table having around 100 million rows.Everyday we have an ETL process in which table will be trucnated and relaoded. Will creating a partition on the table increase the inserting speed?

View 4 Replies View Related

SQL/RAID

May 29, 2001

My SQL 7 is on RAID 5. Sometimes on non-peak hours, on RAID disks first
two lights ( from left ) are constantly on for hours. NT Task manager, nothing
unusual, SQL current activity - no running user processes. Isn't second light
on RAID comes on if any disk activity ( Read/Write ).

Suggestions are appreciated.

Thanks,
Ivan

View 2 Replies View Related

NT RAID /SQL

Sep 28, 1998

I`ve tried implementing NT Software Raid / Stripping with Parity
and am unable to stripe disc that are more than 2g and
use SQL. I have not found any info in technet. Any ideas! Thanks.

View 2 Replies View Related

RAID 5

Dec 20, 1998

Should one install RAID 5 for SQL Server or just use separate hard drives, one for the data and one for the transaction log?

View 4 Replies View Related

RAID

Apr 15, 2008

could any one tell me about the difference between RAID and SHARED DISK ARRAY

View 3 Replies View Related

Which Raid?

Jul 20, 2005

Hi,I was going to buy a server with Raid 1 as I thought that it meant that ifone of the two mirrored drives fail, you simply take it out and put a newone in. At which point presumably the hardware takes over and copies theother drive over to mirror it again.However, my sql server admin book, says raid 1 is bad, as it means you havelots of downtime, when recovering from a broken drive.Can anyone give me some advice on this? What is the best Raid to use whenyou are running SQL server on the server.ThanksJJ

View 1 Replies View Related

Raw Partitions

Jan 9, 2002

Does anyone have any statistics on the performance gains one can get using raw partitions. The database in question is very IO intensive and performs about 1,000,000 inserts/updates per select.

Thanks...

View 2 Replies View Related

Raid & Transaction Log ?

Sep 13, 2001

Im setting up a hardware raid 5 solution for one of our db servers. The data files will reside on the stripe. We dont realy want to raid more drives for the Transaction log if its not nessesary. If the drive with the log crashes is the data file for the database useless ?

View 1 Replies View Related

What Level Raid Is Best?

Nov 29, 2005

Hi guys,
On a new server with 4 disks, what level of raid is best to apply. In terms of what's important, I'd say speed is at the top of the list.

BJ

View 1 Replies View Related

Which Raid To Configure

Mar 17, 2004

Hello,
I run a small homw office. I am planning to purchase a dell powerdge 1750 server to install SQL server on that.
I am confused here about which RAID should I install on this server RAID 1 or RAID 5.
The dell customer rep could not tell me the advantages of installing only RAID 1 or only RAID 5 or installing both RAID 1 and RAID 5

View 7 Replies View Related

SAN RAID Setup

Jun 11, 2008

HI All,

I'm going to buy 1 IBM DS3400 SAN. I got 2 x SQL Servers
one for our ERP System other for Web

Physical Server are like this ATM

SQL1 (ERP) ( 3 RAID arrays)
-----

OS: 36GB (RAID 1)
DATA: 120GB (RAID 5)
Log : 15GB (RAID 5)
Backup:270GB (RAID 5)

SQL 2 (WEB) ( 2 RAID Arrays)
-----

OS: 120GB (RAID1)

Data/log/bak 569GB (RAID10) disk like this

DATA: 218gb

LOG:15gb

Backup: rest


So how do i raid the SAN and what sort of HDD i should buy?

View 2 Replies View Related

Dell Raid 0+1

Mar 18, 2006

I am recommending that we change our Raid Configuration on some of ourServers from Raid 5 to Raid 0+1; we are experiencing severe IObottlenecks.Our hardware guys are pushing back a bit. They claim that Dell has aweird implementation of 0+1 and told me something about one drivefilling up before it begins to write to the next. They claimed thatthis gets rid of most of the benefits of 0+1.I know that 0+1 is not as good as 10 for availability, fault tolerance,and rebuilding, but shouldn't the write throughput be about the same?Setup:Poweredge 2850Powervault 220SPerc 4/DC Controller 1Perc 4e/DI Controller 0

View 5 Replies View Related

Which Raid For SQL 2005

Jan 5, 2006

I have always used RAID 5 for my database apps.  I am wondering though is this the best solution.  I am purchasing a new server and deciding whether to go with  RAID 1 or RAID 5.  This server will support all our .NET Apps, SQL Server 2005, Reporting Services, and Integration Services.

Any Input?

View 4 Replies View Related

SQL Installed On Both Partitions

Oct 12, 1999

Please help!

I have a server that has SQL Server installed on both C and D drives. The SQL Server software is currently running from the C drive and the live databases and backups are stored on the D drive.

I need to have everything on the D drive. Is there an easy way to make the registry point to the D drive without reinstalling SQL Server? The software will needs to run from the D drive because the C drive is running out of disk space. I will also need to delete the whole C:mssql directory.

Thanks.

View 2 Replies View Related

Moving Partitions From One DB To Another

Jun 19, 2008

assuming that you have two databases, the OLTP db and the OLAP db (take not that both have the same structure -- archiving purposes)... using table partitioning, is there a way where we can move 1 partition from the OLTP db to the OLAP db???

i'm actually trying to use this example with both tables in the DB.. I tried to modify to use two databases but sql server is unable to move the partition...

ALTER TABLE [Production].[TransactionHistory]
SWITCH PARTITION 1
TO [Production].[TransactionHistoryArchive] PARTITION 2;

SlayerS_`BoxeR` + [ReD]NaDa

View 9 Replies View Related

Configurations For Partitions.

Aug 23, 2007

Hi experts,

In SQL Server 2005 database we have partitioned a very big table into 30 partitions each holding few million of records.

Im just curious to know whether there are some configuration related to processors or system hardware in order to benefit from partitioning ? (Ex : If we have multiple processors Whether they need be configured to do a parallel processing ? )

Any real time experience (other than referring links) would be really helpful for me.

Thanks in advance,

Hariarul

View 1 Replies View Related

Merging Partitions

May 21, 2008



Hi There,

I have a quick question regarding merging cube partitions.
If I create partitions sliced by date (let's assume we have year level partitions like 2006,2007,2008...)
Later, if I want to merge selected partition to another partition , for example I have history partition and 2006 partition and I want to merge 2006 to history partition then I can simply merge them using ' merge partition' through Management Studio.

My question is that in script, History partition has condition which is where clause and restricted by year level (i.g. WHERE date < '01-01-2006' ) ; however after merging , script won't change like WHERE date <'01-01-2007').

If so, whenever I merge partitions then I have to alter the script as well based on selected merge partion?
I need to refresh history partition once a month;however even if I merge 2006 partition , once I reprocess history partition then it only process what it was wrote in script. So, after reprocessing 2006 data won't appear in this history partition.
So, wondering it's mentatory to alter the script once partition is merged.
Please give me some comments on this issue.
Thanks in advance.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved