Database Size (PSOFT)
-
Hi, I'm working with people soft (CRM) database on Sql Server 2000, it's 97 Gb (mdf file) size.
It has been experience performance problems at application level, sometimes response slowly.
Master, tempdb, and CRM database are on separated physical disks, and CRM physical disk is near to capacity (100 Gb)
My question is...
Existssome rule, advice, recommendation to split, divide, a database in some particular size, or what is the best way to try to split my mdf file???, for gain better performance (I/O)
Thanks for any advice.
-
adding more memory, say 32gb will aid performance by increasing the data cache size.
excluding raid 5 overhead every doubling of the number of available spindles doubles i/o performance.
No doubt you'll get loads of advice about spliting indexes, filegroups and such - bear in mind that each such split adds to the complexity of the maintenance.
I'd advise you to monitor what exactly is giving you problems before you make any changes. if you're using raid 5 anywhere dump it for raid 10, if the tran logs are not on a seperate raid 1 then do that. BUT .. analyse what the bottleneck is first before you do anything!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Good advice, but I always go for the "cheapest" solution first... Have you rebuilt indexes and updated statistics lately?
If you wanted to physically separate the database files, you might consider moving the Log File off the same physical drive as the Data, and moving the nonclustered indexes to a separate physical drive or RAID...
-
Once a week on weekend, I have a task maintenance task that rebuid index, defrag, update statistics and other things.
Actually my mdf and log file are on different disks, indexs are on primary filegroup, the same as mdf.
Is it possible to move or realocate all of my table index??, I have saw other databases that has one ore more data file (mdf), one o more log file (lfd) and one index file (ndx).
How can I move my index to another file only por index information.
And this action is recomendable to do?
-
How about statistics ? Do your have autostats enabled for your crm db ?
Every once in a while (3 or 4 /year) manualy perform dbcc updateusage and sp_updatestats. Depending on the type of reindexing you use, the statst get messy.
Check your db for CLUSTERING indexes ! They help a lot with maintaining datapages (especialy freeing space).
You could create a new index-filegroup and alter the existing (non-clustering) indexes.
Generate a script (create index ... with drop existing).
Maybe I have such a script for you tomorow when i'm back at the office
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Like alzdba says, yes you can move all of your nonclustered indexes to a new filegroup on a different drive. You can't do this with clustered indexes though. Just create the filegroup on the new drive and drop/recreate non-clustered indexes on the new filegroup. You can actually use EM to generate the Index drop and create script for you and change the ON [PRIMARY] to ON [new_file_group] then run it.
-
Maybe this can help out generating your script :
-- I did change to not include Primary Keys !!
-- You'll have to drop/create the PK-constraints if you want to move them
Create view V_IXCOL
as
select SIK.* , C.name as ColName
from dbo.sysindexkeys SIK
inner join dbo.syscolumns C
on SIK.id = C.id
and SIK.colid = C.colid
go
Declare @NewFG varchar(128)
set @NewFG = 'YourNewFilegroup'
print '-- Move NCI Indexes to new FG'
print '-- keep column-ordinal / order equal to PK column-ordinal / order (asc/desc)'
select 'Create ' + case when (SIX.status & 2)<>0 then 'UNIQUE ' else '' end + 'index [' + SIX.name + '] on [' + U.name + '].[' + O.name + '] ( '+
IX1.ColName
+ case when IX2.ColName is null then '' else ', ' + IX2.ColName end
+ case when IX3.ColName is null then '' else ', ' + IX3.ColName end
+ case when IX4.ColName is null then '' else ', ' + IX4.ColName end
+ case when IX5.ColName is null then '' else ', ' + IX5.ColName end
+ case when IX6.ColName is null then '' else ', ' + IX6.ColName end
+ case when IX7.ColName is null then '' else ', ' + IX7.ColName end
+ case when IX8.ColName is null then '' else ', ' + IX8.ColName end
+ case when IX9.ColName is null then '' else ', ' + IX9.ColName end
+ case when IX10.ColName is null then '' else ', ' + IX10.ColName end
+ case when IX11.ColName is null then '' else ', ' + IX11.ColName end
+ case when IX12.ColName is null then '' else ', ' + IX12.ColName end
+ case when IX13.ColName is null then '' else ', ' + IX13.ColName end
+ case when IX14.ColName is null then '' else ', ' + IX14.ColName end
+ case when IX15.ColName is null then '' else ', ' + IX15.ColName end
+ case when IX16.ColName is null then '' else ', ' + IX16.ColName end
+ case when IX17.ColName is null then '' else ', ' + IX17.ColName end
+ case when IX18.ColName is null then '' else ', ' + IX18.ColName end
+ ' ) WITH DROP_EXISTING on [' + @NewFG + '] ' + char(10) + 'GO '
from V_IXCOL IX1
inner join dbo.sysobjects O
on IX1.id = O.id
and o.xtype = 'U'
inner join dbo.sysUsers U
on O.Uid = U.Uid
inner join dbo.sysindexes SIX -- no INFORMATION_SCHEMA available for this info
on IX1.id = SIX.id and IX1.indid = SIX.indid
and SIX.indid between 2 and 254 -- Select only NCI
and SIX.name not like '[_]WA[_]%'
left join V_IXCOL IX2
on IX1.id = IX2.id and IX1.keyno = 1 and IX2.keyno = 2
left join V_IXCOL IX3
on IX1.id = IX3.id and IX1.keyno = 1 and IX3.keyno = 3
left join V_IXCOL IX4
on IX1.id = IX4.id and IX1.keyno = 1 and IX4.keyno = 4
left join V_IXCOL IX5
on IX1.id = IX5.id and IX1.keyno = 1 and IX5.keyno = 5
left join V_IXCOL IX6
on IX1.id = IX6.id and IX1.keyno = 1 and IX6.keyno = 6
left join V_IXCOL IX7
on IX1.id = IX7.id and IX1.keyno = 1 and IX7.keyno = 7
left join V_IXCOL IX8
on IX1.id = IX8.id and IX1.keyno = 1 and IX8.keyno = 8
left join V_IXCOL IX9
on IX1.id = IX9.id and IX1.keyno = 1 and IX9.keyno = 9
left join V_IXCOL IX10
on IX1.id = IX10.id and IX1.keyno = 1 and IX10.keyno = 10
left join V_IXCOL IX11
on IX1.id = IX11.id and IX1.keyno = 1 and IX11.keyno = 11
left join V_IXCOL IX12
on IX1.id = IX12.id and IX1.keyno = 1 and IX12.keyno = 12
left join V_IXCOL IX13
on IX1.id = IX13.id and IX1.keyno = 1 and IX13.keyno = 13
left join V_IXCOL IX14
on IX1.id = IX14.id and IX1.keyno = 1 and IX14.keyno = 14
left join V_IXCOL IX15
on IX1.id = IX15.id and IX1.keyno = 1 and IX15.keyno = 15
left join V_IXCOL IX16
on IX1.id = IX16.id and IX1.keyno = 1 and IX16.keyno = 16
left join V_IXCOL IX17
on IX1.id = IX17.id and IX1.keyno = 1 and IX17.keyno = 17
left join V_IXCOL IX18
on IX1.id = IX18.id and IX1.keyno = 1 and IX18.keyno = 18
where IX1.keyno = 1
and not exists (select *
from sysobjects PK
where xtype = 'PK'
and PK.parent_obj = O.id
and PK.name = SIX.name )
order by U.name , O.name, SIX.name
go
drop view V_IXCOL
go
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks for the sugesstions, I'l try to move my non clustered index to a new filegroup.
Thanks
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply