November 5, 2009 at 2:13 am
Hi all.
There is heavily loaded OLTP system under Sql Server 2008 Standart Edition. Database has some big tables. My question is about hard disk configuration. Database has problems with I/O (Latches). I have addition RAID and a think about dividing database. There are different options in my mind:
- Move big tables on additional RAID
- Move indexes
Help me, what is better.
I already moved TempDb on separate disk
November 5, 2009 at 2:32 am
suvorav (11/5/2009)
Hi all.There is heavily loaded OLTP system under Sql Server 2008 Standart Edition. Database has some big tables. My question is about hard disk configuration. Database has problems with I/O (Latches). I have addition RAID and a think about dividing database. There are different options in my mind:
- Move big tables on additional RAID
- Move indexes
Help me, what is better.
I already moved TempDb on separate disk
How big are the tables and what sort of raid do you have?
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
November 5, 2009 at 3:22 am
Thanks for reply
It is additional RAID 10
Most large tables are about
15 millions rows (Data space 2 594,500 MB , Index space 709,766 MB)
5 millions rows (Data space 1 163,180 MB, Index space 121,539 MB)
System workload mainly on reading
I tried to perform load testing with database profiler
At first I recorded the users workload on production server. After that I replayed one on test server with different database configurations. The replay time was measured, but it was the same time for different variants. Splitting my database does not affect the test execution time.
Is my testing correct?
Can you advice any variants of load testing for me?
November 6, 2009 at 8:03 am
my guess is you have 594.500 MB not 594,500 MB....if so these tables are probably not big enough to see much of a diff...
you said you moved the tempdb to its own spinal or raid, what about the OS and Transaction logs, are they a diff raids
then your DB?
another thing you said you getting mostly reads on these tables? so that tells me you are getting shared locks...which not be a problem.
have you measured your read/write performance of perf mon...if so can you please post the numbers
November 6, 2009 at 9:35 am
What are the results of the following queries?
DECLARE @db_id TINYINT
SELECT @db_id = DB_ID('northwind')
SELECT page_latch_wait_count, page_latch_wait_in_ms, page_io_latch_wait_count, page_io_latch_wait_in_ms
5.
FROM sys.dm_db_index_operational_stats (@db_id, null, NULL, NULL)
SELECT * FROM sys.dm_os_latch_stats
How is your tempdb setup for this database?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 11, 2010 at 3:05 am
The of TempDb is 7000 Mb
SELECT * FROM sys.dm_os_latch_stats where waiting_requests_count <> 0
latch_classwaiting_requests_countwait_time_msmax_wait_time_ms
ACCESS_METHODS_DATASET_PARENT250908472717032106
ACCESS_METHODS_HOBT_COUNT13618916
BUFFER278705183513078871529
BUFFER_POOL_GROW400
LOG_MANAGER21157601420
TRACE_CONTROLLER300
NESTING_TRANSACTION_READONLY16789680216
NESTING_TRANSACTION_FULL445171122431
ACCESS_METHODS_ACCESSOR_CACHE1700
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply