SQL Server Performance Forum – Threads Archive

Squeezing more out of SQL 2k5

I’ve got 2 x DL580G2’s with Quad Xeon MP 2GHz, 8GB RAM, 4x72GB Ultra320 disks RAID1+0.
Each server is running Win2k3 Ent with Sql 2k5 Ent. Both have close to 1M+ Lock Requests/sec with ~0 deadlocks and ~0 lockwaits.
… no lock bottleneck 1000+ Write/sec, Disk Queue Length = ~10
… negligible disk bottleneck CPU = ~20%
… no CPU bottleneck With teamed network cards on GB switches the network is 1% utilized.
… no network bottleneck I’ve tuned everything I can think of but I can’t seem to get any more out of these boxes. Am I missing something? Next step: install RAID controller and external drive enclosure to split filegroups over 3 channels (Data, Index, Log). Thoughts?
Any suggestions on a ****-kicking dual channel RAID controller?

For a second your login resembles the recent Spammer here [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />], but its not once we look at your post. Anyway what is the issue you are having, any slow response of queries or any issues with the results? I presume you have upto date service packs applied on this server. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</center>

All SP’s applied. The servers are being used to process streaming feeds from AMEX, NASDAQ, NYSE, etc.
Currently I am about 1 week behind and need to catch up.
If I can get a 50% improvement then I should catch up within a week and keep pace. There are 3 x DL380 G3 (Dual Xeon HT 3GHz 2GB) doing the processing and they are no where near straining. All of the performance monitors are telling me that there are no bottlenecks but I can’t seem to get the data processed fast enough…

Have you collected the counters using PERFMON,http://sqlserver-qa.net/blogs/perft…t-memory-counters-what-you-need-to-check.aspx fyi. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

how can you claim no disk bottleneck with a queue of 10 on 4 physical disks
especially considering the writes/sec without proper analysis of the full set of relavent counters, i am guessing your app does lots of low row count inserts
hence the high writes/sec, mostly going to logs, with bursts to the data is the cpu 20% even across all processors?
or is one processor near 100%, if so, you do have a processor bottleneck if this app does do mostly 1-10 row inserts
then the log writes/sec should be 3000-5000/sec with proper configuration

oops… Avg. Disk Queue Length = 0.1 PerfMon has been used to monitor all of the usual suspects.
ie:http://www.sql-server-performance.com/sg_sql_server_performance_article.asp Page Faults/sec = ~3 4 CPUs are 30% (1,3,5,7) and the others are 0-20%. Logging has been set to ‘Simple’ in an effort to boost performance. App does 1000’s of single row SELECTs and INSERT/UPDATES evenly across 20 tables through stored procedures.
EG: ALTER PROCEDURE [dbo].[Update_Bar_AMEX_1] (
@SymbolId int,
@DateTime datetime,
@Last money,
@LastSize int,
@Bid money,
@BidSize int,
@Ask money,
@AskSize int
) AS DECLARE @BarId int
DECLARE @Open money
DECLARE @High money
DECLARE @Low money
DECLARE @Close money
DECLARE @Volume money
DECLARE @CurrentBid money
DECLARE @CurrentAsk money SELECT @BarId = BarId, @Open = [Open], @High = [High], @Low = [Low], @Close = [Close], @Volume = Volume, @CurrentBid = Bid, @CurrentAsk = Ask
FROM Bar_AMEX_1 WHERE SymbolId = @SymbolId AND DateTime = @DateTime if @BarId is null
begin
INSERT INTO Bar_AMEX_1 (SymbolId, DateTime, [Open], High, Low, [Close], Volume, Bid, Ask)
VALUES (@SymbolId, @DateTime, @Last, @Last, @Last,@Last, @LastSize, @Bid, @Ask)
SELECT @BarId = SCOPE_IDENTITY()
end
else
begin
if @Last > @High SET @High = @Last
if @Last < @Low SET @Low = @Last
SET @Close = @Last
SET @Volume = @Volume + @LastSize
if @Bid > @CurrentBid SET @CurrentBid = @Bid
if @Ask < @CurrentAsk SET @CurrentAsk = @Ask UPDATE Bar_AMEX_1 SET High = @High, Low = @Low, [Close] = @Close, Volume = @Volume, Bid = @CurrentBid, Ask = @CurrentAsk
WHERE BarId = @BarId
end

then you need the precise value for the log disk write latency
try the following every 10min: SELECT DbId, FileId, NumberWrites, BytesWritten, IoStallWriteMS
FROM ::fn_virtualfilestats(-1, 2) assuming FileId = 2 is the log (default unless you did created multiple log files)
you can susbstitute the specific dbid in the first value (replacing -1)

UPDATE:
I have purchased an Adaptec 2200S dual channel U320 controller. Using the controller I will split the drive cage into two and create two partitions on each RAID1. It will look something like this: Drive A (2x72GB U320 15k RAID1):
C: Windows
D: Data
Drive B (2x72GB U320 15k RAID1):
E: Index
F: Logs Once I have this in place I will measure the performance and follow the suggestions provided. In theory this should SIGNIFICANTLY increase disk performance (4 times?). (I did not realise that the DL580g2 drive cage is U320 but the on-board controller is only Ultra3 [single channel], so logically the first step was to upgrade the controller and split the cage.)

What difference you observed when changing the Database model to SIMPLE and FULL? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

]]>