| Author |
Topic |
|
Heinduplessis
Starting Member
23 Posts |
 Posted - 2011-08-02 : 11:44:25
|
| I'm managing a server that has about 140 mil records. The server has 8 cores, 16GB ram and I've optmised the Indexes as far as I can.Yet, every week we can see performance goes down as production requirements increase.It's clear that the limiting factor is the HDD IO. What would be the next step to improve it. Currently I'm considering:- A SAN (Would this really improve performance? Any recommendations re. brands / models?)- Super fast SCSI disks in some fancy Raid configuration (suggestions welcome)- SSD (Maybe the best solution)Any suggestions would be greatly appreciated.Many thanks |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2011-08-02 : 11:51:45
|
How scattered is the data? If you have a lot of random access reads then a SAN isn't going to help you all that much. OTH if you get good streaming then a big old RAID or SAN will give you a real benefit.We use these: http://www.fusionio.com/products/  They are freaking awesome!On our web nodes, which leads me to:Scaling out - replication or sharding (federation).It may be better to scale out your setup onto multiple servers. This will take you down the uncomfortable road of data replication.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
Heinduplessis
Starting Member
23 Posts |
Posted - 2011-08-02 : 13:50:57
|
| Thanks CharlieI'm already running a mirror so comfort is already long gone :-) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-08-02 : 15:28:59
|
What kind of wait stats are you experiencing?I have table with 800 million rows and sub-second reponse to most of my queries.Having correct indexing helps out a lot.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Heinduplessis
Starting Member
23 Posts |
Posted - 2011-08-03 : 04:44:11
|
quote:
Originally posted by tkizerHow did you determine that? How big is your database? 16GB of memory is not very much.
Well CPU idles and RAM usage does not seem to be high at all while inserts and selects are running, but HDD usage shoots up and stay there until the query is done.We get about the same performance from a 2GB RAM solution. |
|
|
|
Heinduplessis
Starting Member
23 Posts |
Posted - 2011-08-03 : 04:45:28
|
quote:
Originally posted by SwePeso
What kind of wait stats are you experiencing?I have table with 800 million rows and sub-second reponse to most of my queries.Having correct indexing helps out a lot.
N 56°04'39.26"E 12°55'05.63"
Selects are almost instant. No complaints there, but I ave about 9 indexes on the one table and inserting 100k records at a time takes 30 seconds, other times, 15 minutes, depending on the size of other user's selects. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-08-03 : 07:12:08
|
I would look at the server min and max memory configuration to start with.Allocating and deallocating memory is very costly for SQL Server.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-08-03 : 07:13:07
|
Have you considered Snapshot Isolation Level?
N 56°04'39.26"E 12°55'05.63" |
|
|
|
chris_cs
Posting Yak Master
223 Posts |
Posted - 2011-08-03 : 07:33:08
|
| It could be worth checking that you need all 9 indexes??The more indexes you have the slower the INSERT will be.This is just an ideaJunior DBA learning the ropes |
|
|
|
Heinduplessis
Starting Member
23 Posts |
Posted - 2011-08-03 : 08:52:00
|
quote:
Originally posted by SwePeso
Have you considered Snapshot Isolation Level?
N 56°04'39.26"E 12°55'05.63"
I'll research Snapshot Isolation Level! |
|
|
|
Heinduplessis
Starting Member
23 Posts |
Posted - 2011-08-03 : 08:54:43
|
quote:
Originally posted by chris_cs
It could be worth checking that you need all 9 indexes??The more indexes you have the slower the INSERT will be.This is just an ideaJunior DBA learning the ropes
Yes, unfortunately. Luckily it's single field indexes thanks to SQL 2008R2. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-08-03 : 12:35:53
|
quote:
Originally posted by Heinduplessis
quote:
Originally posted by tkizerHow did you determine that? How big is your database? 16GB of memory is not very much.
Well CPU idles and RAM usage does not seem to be high at all while inserts and selects are running, but HDD usage shoots up and stay there until the query is done.We get about the same performance from a 2GB RAM solution.
What are the values for avg disk read/sec and write? These are in PerfMon under logical disk.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-08-03 : 13:02:13
|
If disks are rushing it might as well be virtual memory file working.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-08-03 : 13:11:02
|
quote:
Originally posted by Heinduplessis
quote:
Originally posted by chris_cs
It could be worth checking that you need all 9 indexes??The more indexes you have the slower the INSERT will be.This is just an ideaJunior DBA learning the ropes
Yes, unfortunately. Luckily it's single field indexes thanks to SQL 2008R2.
Why only single field indexes? A general statement like that makes me think you don't have the proper indexes in place. Covering indexes are better, although you can't cover all queries generally.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-08-03 : 13:25:47
|
| If you get a chance check the disk fragmentation on the affected drives. If it's heavily fragmented you may want to try defragging them and see if it improves.Check the autogrow settings on the data and log files, the increment is probably too small, as well as the overall file sizes. |
|
|
|
Heinduplessis
Starting Member
23 Posts |
Posted - 2011-08-04 : 02:37:10
|
quote:
Why only single field indexes? A general statement like that makes me think you don't have the proper indexes in place. Covering indexes are better, although you can't cover all queries generally.
@tkizer: I'll check the values. We had joined indexes for a long time, but I understand from RC2 that SQL now intelligently joins indexes for the best result, so only single fields per index is good enough, sparing index size. The more indexes, and the more complex, the longer each insert takes.@SwePeso: I checked but there weren't a lot of swopping. But what's a lot :/@robvolk: Worth a check of the fragmentation, but one can't defrag a SQL file can one? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-08-04 : 02:51:53
|
Use command line tool CONTIG from sysinternals.comHaving a single column index is nearly useless in SQL Server. Oracle and other RDBMS can "merge" indexes to get the wanted output, but it's rare SQL Server does it..
N 56°04'39.26"E 12°55'05.63" |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-08-04 : 12:43:36
|
quote:
Originally posted by Heinduplessis
quote:
Why only single field indexes? A general statement like that makes me think you don't have the proper indexes in place. Covering indexes are better, although you can't cover all queries generally.
@tkizer: I'll check the values. We had joined indexes for a long time, but I understand from RC2 that SQL now intelligently joins indexes for the best result, so only single fields per index is good enough, sparing index size. The more indexes, and the more complex, the longer each insert takes.
Single field indexes are not good enough in lots of cases.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
HDD Bottleneck