Re: Underutilized Hardware on a SQL Server
From: Ryan Stonecipher [MSFT] (ryanston_at_microsoft.com)
Date: 01/31/05
- Next message: wei xiao: "Re: Sp_lock results.. IS this of a convern..?"
- Previous message: Anthony Thomas: "Re: sp_rename error"
- In reply to: Anthony Thomas: "Re: Underutilized Hardware on a SQL Server"
- Next in thread: Anthony Thomas: "Re: Underutilized Hardware on a SQL Server"
- Reply: Anthony Thomas: "Re: Underutilized Hardware on a SQL Server"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 30 Jan 2005 23:21:53 -0800
Anthony, your comment regarding LOB data and MTL allocations actually isn't
correct. Assuming that the NTEXT isn't stored in-row (via the
sp_tableoption 'text in row' setting), LOB data is stored inside the server
in an internal tree structure, with each node of the tree containing a
fragment of the LOB column. If you need all of the data from an NTEXT
column for instance, SQL Server will have to perform additional IOs to get
each piece of the LOB stored in the text tree. These pages are normal
database pages, and as such are retrieved from the buffer pool like any
other data page, so the MTL impact is zero. However, this causes more
activity on the disk, and causes us to have to wait for the IO to complete
(adding to your wait times, reducing CPU utilization).
If you are seeing long IO waits, and have an application using a lot of LOB
types, consider sp_tableoption 'text in row' to push some of that data into
the data page.
Schema locks make me think recompiles; are they SCH-M or SCH-S? Take a look
at http://support.microsoft.com/default.aspx?scid=kb;en-us;308737,
http://support.microsoft.com/default.aspx?scid=kb;en-us;263889, and
http://support.microsoft.com/kb/243586/EN-US/.
See if this article on general application performance troubleshooting
helps: http://support.microsoft.com/default.aspx?scid=KB;[LN];224587.
Here's a good general-purpose article on blocking:
http://support.microsoft.com/default.aspx?scid=kb;en-us;224453.
-- Ryan Stonecipher Microsoft Sql Server Storage Engine, DBCC This posting is provided "AS IS" with no warranties, and confers no rights. "Anthony Thomas" <ALThomas@kc.rr.com> wrote in message news:O6L%23C61BFHA.904@TK2MSFTNGP12.phx.gbl... > Since you seem comfortable with "in-line" responses, I will continue the > activity. > > -- > > "SRK" <srkinyon@yahoo.com> wrote in message > news:1106934249.338419.280240@c13g2000cwb.googlegroups.com... > Questions from prior posts are answered below: > > "Are you seeing a lot of blocking or other kinds of waits in > sysprocesses?" > - Yes, the lock activity is not helping. However, most of them are > schema locks since most of the queries use the WITH(NOLOCK) option. I > don't recall seeing exclusive locks, the highest one i've seen is > RangeI during insert. > > --NOLOCK WILL NOT GENERATE SCHEMA LOCKS. You need to check your > TRANSACTION > ISOLATION LEVEL again. That sounds like REPEATABLE READ or SERIALIZABLE > to > me, perhaps with IMPLICIT_TRANSACTIONS set on. > > "Are your disks keeping up?" > - Possibly not. That would be a logical bottleneck in this case. What > is the best way to measure if they are? > > --Use the OS Performance Counters. You may have to issue the DISKPERF > command and reboot to get them set though. Check out DISKPERF by itself > to > see what the counter collection is currently set to and -? to see the > options, either physical, logical, or both. You then want to check your > Disk Queue Lengths and Avg. sec/Operation counters. These will tell you > the > typical disk latency per request. > > "How do you define "heavy load"?" > - For this database, a heavy load seems to be running 2 to 3 > concurrent sp's that access a few tables with 9+ million records > (queries run on NOLOCK most of the time). For a SQL server, I would > expect better performance, but I've never been in a db that had most > of the useful records in one table (the architecture is not mine, I'm > just using what is there since I'm not authorized to rewrite it). > One aspect that dominates this table structure is the use of guids and > an ntext datatype. Maybe there is a way to optimize the hardware to > cope with the ntext use? > > --Yes, BLOBs/CLOBs can be a pain in the you know what. Here's the deal, > for > 8 KB page requests (that is, data and index pages), SQL Server will > reserve > memory pages in the Buffer Pool to load the data; however, for many > BLOB/CLOB data that do not fit in these boundaries, SQL Server will have > to > use allocations from the MEM TO LEAVE region, which is outside of the > Buffer > Pool. If you have a lot of this activity going on, consider using the -g > startup parameter, which effects the amount of memory allocated to this > region. By default, there is 384 MB assigned to this area, given you have > sufficient memory on the server. The number comes from the number of > worker > threads times 512 KB (256 x 512 KB = 128 MB, by default) plus the default > value of this parameter (-g256). Be careful with this parameter, and > slowly > increment it as needed, but do not over do it. I think the largest value > I > have ever used was -g896, but -g384 or -g512 is more typical. > > "Are you seeing slow user response times, or are you just looking for > more use of resources?" > - Both. My thought is that better use of resources will lead to > better user response times. > > "What do the avg and current disk queues look like?" > - I don't know. What is the best way to measure this? > > --See answer above. > > Hope this helps. > > Sincerely, > > > Anthony Thomas > > >
- Next message: wei xiao: "Re: Sp_lock results.. IS this of a convern..?"
- Previous message: Anthony Thomas: "Re: sp_rename error"
- In reply to: Anthony Thomas: "Re: Underutilized Hardware on a SQL Server"
- Next in thread: Anthony Thomas: "Re: Underutilized Hardware on a SQL Server"
- Reply: Anthony Thomas: "Re: Underutilized Hardware on a SQL Server"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
