I have been asked to move the indexes on our membership database tables to seperate partitions on the server. This is a new concept to me and thought I could use some advice on how to go about doing it.
Thanks in advance.
Brad Keck
Hi all -
Quick question... I want to move all my non-clustered indexes to their own seperate drive array. How would I accomplish this?
Do I just add the filename at the end of the statement like you do in Oracle? (EXAMPLE: CREATE INDEX IDX_Cls_Code on dbo.Class(Code) on secondary.ndf)
If this isn't correct, can someone please post the correct syntax?
Thanks!
Rich
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
I have a requirement that I need to reload the last seven days worth of data each night to ensure that we pick up late arriving and updated records. To avoid having to do updates we delete the last seven days data and reload.
I was wondering if it is possible to set up the table as a partition, paritioned on a value (OLD, NEW) or similar.
The job would set the last day in the NEW partition to be old, the theory being that this would cause the rows to move to the OLD parition, and then truncate the new partion rather than deleting. The last seven days data could then be inserted into the empty new partition.
My questions is 1. Is my theory about the data moving from one partition to another correct. 2. Can I actually truncate and individual parition, 3. Do you think it will perform any quicker. We would expect data in the range of 100K to 500K rows in the seven days and will store up to 4 years of historical data.
Thanks for your thoughts
Stapsey
I have a table with millions of rows and about 70 columns that move through a number of states (11 possible states in all) from "New" via various states to "Processed" and eventually to "Archive" (there's a complicated state diagram that I won't bore you with)
Movement between states is based on a heap of business logic including the move to Archive (not just dates).
Different sorts of processing (querying and update both by users and overnight processing) are carried out on the data according to its state.
Maintaining the indexes for optimum performance across the board is a headache.
We have two problems in that we want better query performance and want to be able to easily switch out objects that are in the Archive state.
I had in mind partitioning the table (and its indexes) on state so that :
(a) Queries would be directed only at the appropriate partition (that is always use "where state=" as part of the query)
(b) The Archive partition could be swapped out of the table periodically
In my test setup 10 of 11 partitions are in [PRIMARY] but Archive is in a different filegroup.
Query performance is OK - execution plans look good.
However my update performance is now appalling when moving between any two states (10 times as long as on the unpartitioned table).
I understand that when you update a column which is used as a partition key it will cause the row to "move from one partition to another" as it says in another post.
Fine - because that's exactly what I want - logically.
I can also understand that moving from one filegroup (and hence the underlying file) to another must mean that the data has to physically move.
However is the data physically moving whenever you move between partitions or what's going on to cause such a degradation in performance ?
Kimberly Tripp describes a recipe for switching partitions in and out, thru the use of staging tables, when it comes time to "slide the window" on a partitioned table. She says that the clustered index (on staging) must be the same as that chosen for the partitioned table itself but she doesnt discuss whether or not all of the non clustered indexes need to be the same too once the
ALTER TABLE Orders
SWITCH PARTITION 1
TO OrdersOctober2002
and
ALTER TABLE OrdersOctober2004
SWITCH TO Orders PARTITION 24
run. For the data being switched out, I wouldnt want to do anything extra. For the data being switched in, I'd like to understand if she is implying that all other indexes would be built automatically as a result of the 2nd ALTER statement?
Kimberly's article is at http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm#_Toc79339965
Hi,
I need to separate the data and indexes onto different drives.Currently they r all on same drive..Now i need to move indexes onto a separate drive.There are no file groups now except the default.
I plan to create a file group on the new drive.I know if i Drop clustered indexes and recreate them on the new file group,it will separate the data pages from index pages.But the data pages need to be on the same drive as it is now.So i need to move index pages or non-clustered indexes only on to the new file group on the new drive.Even this can be done by choosing a different file group for the non clustered index in Enterprise manager.But this very cumbersome if there exists many tables as is the case now like 300 tables..
My Question is,
1.Is there any way to script only indexes so that it includes drop and create commands to be created on new file group???
2.Is there any other way to do this..
Please let me know if any body worked on this before..I really appreciate if some body can give me info on this..
thanks
Vish
I am trying to find out if it is possible to move indexes to a separate filegroup/disk drive during database restore. I am trying this to see if it improves performance. Also if I cannot move the indexes during restore, how would I move them afterwards to a different filegroup/disk drive? Thanks in advance for all the help.
View 7 Replies View RelatedI want to move all indexes into new separate drive for this I need to create new .ndf file in particular drive.
Mirroring has configured for the DB but the principal and mirror servers do not have same drives.
I want to move indexes to new drive and the derive is not present in mirror server.
Scenario:
We run a multiple database environment, with two of the databases receiving most of the user activity. (both write and read). These databases are roughly 25gb each and receive roughly the same amount of activity. Currently both of the .mdf files sit on the same drive shelf. Their log files are located on a separate drive shelf.
Debate: We have an extra fiber channel shelf available for us to use. We are not having too many problems related to performance, but we are always seeking for different ways to increase application/server performance. The debate centers on what to do with the extra shelf. There are two different suggestions on how best to use the shelf. They are:
1)Separate the .mdf files for two most utilized databases. This would separate the databases and the I/O associated with each across two different shelves
2)Break off the indexes for all 5 databases on to the extra shelf. This would leave all the .mdf files on the same shelf, but it would move the I/O associated with the indexes to a different shelf.
Can anyone provide the pros and cons of either suggestion?
I would like to see arguements for either side.
Dave
I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).
Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.
I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.
As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?
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...
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.
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
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.
Hi All,
I have got MSSQL 2000 set up on a machine in my rack at my local telehouse, and a web server set up at home on an ADSL line.
Both servers can see (ping) eachother fine , so you can rule out any kind of connectivity issues straight away, but when i try to get my forum to connect to the mssql database using the correct credentials it just fails saying that the credentials are incorrect ot the server does not exist.
I also installed an SQL database tool on my web server (Shusheng SQL Tool) and attempted to connect to my SQL server using that tool, and got the following message: '[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.'
The server is currently using mixed mode authentication (SQL/Windows) and has both TCP/IP and Named pipes enabled.
Is there some kind of 'Enable remote connections' option in SQL? I need to be able to allow connections to my SQL server from any system, anywhere...
Any ideas?
Hi,
Why we allocate .mdf and .ldf on seperate drives?
Please tell me a proper logical reason behind it.
MERRY CHRISTMAS EVERYONE :)
I need to update a table on our Test Server which is GCSQLTEST, with another table thats on our live server GCSQL. How would I go about doing that in a stored procedure??
CREATE PROCEDURE [InsertRevised_MainTable]
AS
INSERT INTO dbo.RevisedMainTable
([IR Number], [Date], [I/RDocument], [Violation Type])
SELECT [Incident Report No], [Date], [I/RDocument], TypeOfIncident
FROM dbo.RevisedMainTable
WHERE NOT EXISTS (SELECT * FROM dbo.RevisedMainTable
WHERE [IR Number] = [IR Number])
Hi. I have been talking with some developers who have built a hosted application supporting multiple customers. Their database approach is to create a new, dedicated database (same schema each time) for every customer that signs-up.
This approach is contrary to typical hosted DB designs that I have delt with -- that is, a single database holding multiple customer information rather than a unique database for each customer.
Does the improved security of a dedicated database out-weigh the additional maintenance requirements?
If anyone has some objective thoughts on this topic, I'd love to hear them.
Thanks,
Bill
My boss has asked me to look into this and I haven't been able to find any information on the web. I hope someone can answer this for me. We currently have a single database that is storing all the user information and transactions. Within the same database we are also logging different types of user activity. If both these tables are heavily used, would it make sense to separate it into different database, one for data and one for logging? Is there any pro or cons of having more than one database? Any opinion or suggestion would be greatly appreciated. I'm the closest thing they have to DBA and I'm really new to this. Thanks.
View 5 Replies View RelatedDear All,
i've a string to pass as a parametre to a procedure.
like
create preocedure myproc(@EMPID VARCHAR(50),'abc,def,ghi,jkl')
...
end
i need the output like this
1 abc
2 def
3 ghi
4 jkl
.....
how can i do that?
Vinod
Even you learn 1%, Learn it with 100% confidence.
Hello ALL,
I want to seperate a FULNAME into First name, last name and middle name using SSIS. Provided "," Seperator
u can take example
John first name,mathew middle name,
smith last name
Please give me the command how to do it.
Thank YOu
I have twon names in one row and in the same column.
Column Name
John Baker, Sarah Lynn
I want to seperate them using Derived Column like. First part before COMMA,
SECOND PART after Comma. into two diffrent directions. and later Union.
What is the command to extract the first name and second name.
Please le t me know
I have a field that contains values such as 8ft , 7ft, 18ft
I have a report in reporting services that shows this:
Before Restock After Restock
Date 1/12/2008 8ft 9ft
1/13/2008 10ft 7ft
1/14/2008 5ft 4ft
I want to create a subquery that grabs the before restock and figures out if it sheds the "ft" part of the value, and then put in a where before restock > (greater than) After Restock.
Is there a an easy way to do this?
I trying to get the moving total (juts as moving average). It always sum up the current record plus previous two records as well and grouped by EmpId.For example, attaching a image of excel calculation.
View 3 Replies View RelatedI am testing horizontal partitions to see whether it is a feasible
option for a project. IF I have a composite Primary Key and the
constraint column (a part of the Primary Key) that helps the
partitioned view is defined with DateTime Data Type, select on a restricted set of data through a partitioned view still tries to access all the tables instead of just one table that contains the data. Is this the case or am I
missing something ?
Any help on this is appreciated.
Here is what I am trying to do.
************************************************** **************
CREATE TABLE [dbo].[tst01] (
[Dt] datetime NOT NULL ,
[TID] int NOT NULL ,
[Nm] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tst02] (
[Dt] datetime NOT NULL ,
[TID] int NOT NULL,
[Nm] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tst01] ADD
CONSTRAINT [PK_tst01] PRIMARY KEY CLUSTERED
(
[Dt], [TID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tst02] ADD
CONSTRAINT [PK_tst02] PRIMARY KEY CLUSTERED
(
[Dt], [TID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tst01] ADD
CONSTRAINT [CK_tst01] CHECK (Dt between '11/1/2002' and '11/30/2002')
GO
ALTER TABLE [dbo].[tst02] ADD
CONSTRAINT [CK_tst02] CHECK (Dt between '12/1/2002' and '12/31/2002')
GO
insert into tst01 values('11/1/2002', 1, 'SS')
insert into tst01 values('11/2/2002', 2, 'KK')
insert into tst01 values('11/3/2002', 3, 'DD')
Go
insert into tst02 values('12/1/2002', 1, 'LL')
insert into tst02 values('12/2/2002', 2, 'MM')
insert into tst02 values('12/3/2002', 3, 'GG')
Go
CREATE VIEW vtst AS
SELECT * FROM tst01
UNION ALL
SELECT * FROM tst02
Go
SELECT * FROM vtst WHERE dt = '11/1/2002'
************************************************** *********
When I look at the Execution Plan, it shows that clustered index seek
would be performed on both the tables.
StmtText
---------------------------------------------------------------------------------------------------------------------------------
|--Concatenation
|--Filter(WHERE:(STARTUP EXPR(Convert([@1])<='Nov 30 2002
12:00AM' AND Convert([@1])>='Nov 1 2002 12:00AM')))
| |--Clustered Index
Seek(OBJECT:([test].[dbo].[tst01].[PK_tst01]),
SEEK:([tst01].[Dt]=Convert([@1])) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR(Convert([@1])<='Dec 31 2002
12:00AM' AND Convert([@1])>='Dec 1 2002 12:00AM')))
|--Clustered Index
Seek(OBJECT:([test].[dbo].[tst02].[PK_tst02]),
SEEK:([tst02].[Dt]=Convert([@1])) ORDERED FORWARD)
Thanks,
Sathish
I'm considering using horizontal partitions to separate my data by year.
For example, SomeTable_2004, SomeTable_2003, etc. This works well for backups, maintenance, etc. because I'm working with 150+ GB of data. I'll be a partitioned view for queries.
However, I'm new at this and have a few questions. I would also like to do partitioned updates or inserts. But I need to make sure that the tables don't use similar primary keys. Does that make sense? I need to make sure that the primary keys from the first table are not used again in the second table.
SomeTable_2003
primary keys: 1,5,8,9,15
SomeTable_2004
primary keys: 2,3,4,10
I don't really care what keys are used on what table, as long as they are different. I have apps that already use this data, and I don't want to change the application logic.
Thanks,
T
how to set up the partitions.I have a transaction table with 50 million records that's very hard to query. it holds data for the last 4 years but the application only ever looks at the last 6 months so i believe this is and ideal candidate for partitioning.
Would it be better to
1) create a partition based on each year for all data so would have a 2015, 2014, 2013, 2012?
2) create 1 partition based on month for this years data then 3 based on year so would have jan,feb,march,april,may..., 2014, 2013, 2012
For 1) would you have to perform some maintenance at the turn of each year for accommodating the next years data. For 2) although this would give better performance as query's are mostly in the last 6 months wouldn't this have more maintenance to move month data to year partitions come the turn of the year and then create the next years months partitions.
I just inherited a dev box, and need to do some performance analyzing on a 40 gig db for a client. Time is of the essence!
My question is that this dev box only has one disk partition (c: drive). Is it a huge deal that I don't have the db system files on one drive, with the data files on another, and tempdb on another,etc.....
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
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!
Hi champs!
The data i have is like this:
nr date value
------- -------------- --------
1 2007-10-03 45
1 2007-10-05 5
1 2007-10-11 -1
1 2007-10-30 23
2 2007-03-03 3
2 2007-03-13 -5
2 2007-03-03 6
3 2007-10-03 42
3 2007-10-03 11
.....
I want to summerize the value in each group and set the date to the 1'st og that month
i.e.
nr date value
------- -------------- --------
1 2007-10-01 72
2 2007-03-01 4
3 2007-10-01 53
any help is much appreciated.
thanks
I have 4 tables with the respective amount of records
1) 6755
2) 2021
3) 2021
4) 355
They all have the same columns. However, they need to be seperate, or at least when I query them. I'll be accessing this database via the web. i was first afraid that a large database would cause major slow down when accessing the db. So I broke it up into 4 tables. If I combined all 4 tables into one large table and just had a column that differentiated the 4, how significant would be the change in speed when accessing the table? It's not a big deal to keep them seperate, its just that when I have to add or remove a column from one table I have to remove it from all the tables. Furthermore, I'm using a module from DEVEXPRESS, don't know if anyone has heard of it, but when you use a gridview, it loads up the entire table even though your paging (which I think is retarded), so for that reason I was afraid it would slow up my access to the db. Any thoughts?