Re: SQL Server Performance (slowdown)
From: paker (paker_at_discussions.microsoft.com)
Date: 02/22/05
- Next message: Adam Machanic: "Re: WorkDay Stored Procedure"
- Previous message: Steve Kass: "Re: 837 patch and MS03-031"
- In reply to: David Gugick: "Re: SQL Server Performance (slowdown)"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 22 Feb 2005 07:53:06 -0800
David,
I made some tests on the production enviroment, actually I have been there
for some days
I don't think that database design causes to the slowness in this case,
because on the other sites I have actually the same environment and clients
did not fill any slowness.
I event did not add the rowcownt threshhold on the other sites.I simply
delete all the information for expired users(by @userid).
I have traced this site for about three month. I captured the information
for all kind of counters and really the system looks fine. Sometimes the
system has the I/O growing while the CA replication running in the full
synchronization mode. But in this case I did not capture any slowness in SQL
Profiler.
Some strange issues that I captured on the production server. When running
delete from the table, all the server looks like going "to be stucked". Even
permon does not refreshes the picture, only after delay; and cursor gets
stuck for some seconds. Even inserts to another tables take much more
time.When stopping the delete query it takes 10-15 minutes to return the
server to the ~normal state.
But when I captured the information from performance monitor all the
counters returns the normal values including Buffer Cache Hit Ratio ~99.9%,
page life expectancy >7000; Average disk queue lengh <1.5;
I did not capture any locking problems and It does not really looks like
Locking problems.
I agree with you about page splits, but when I testing the application on
my testing environment (actually I don't have RAID 1+0) with the same amount
of DML activity - I am not recreating the same symptoms.
I asked them to send the database backup. Probably there is some corruption
issue.
But I don't think that solution is to smart.
I try to find the answers to the following questions:
*Is 350 connections (active connections that inserts data) is a normal state
*800-900Transaction /sec makes SQL Server to slowdown
* Windows 2000 service pack 2 have known memory leaks
*AWE support influence (I actually left 1GB empty memory for another apps)
*Or some hardware problem on the clien's side
Thanks
"David Gugick" wrote:
> paker wrote:
> > Hi David,
> > Transaction log located on the same controller as data on RAID (1+0).
> > Actually, the clustered index contains three column
> > (UserID,ParameterID,Time). Application inserts the user's information
> > ~every 1 minute by using a short stored procedure with parameters not
> > in single transaction.The same picture with the others active users.
> > After a couple of days when user is going to be inactive (no inserts
> > for
> > this user) I am going to delete all information from the heavy load
> > table.The job deletes information per user(not simultaneously) by
> > using this part of stored procedure
> > exec ('
> > SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> > SET ROWCOUNT 500
> > while exists (select ''x'' from table where userid='+@userid+')
> > begin
> > delete from signals where userid='+@userid+'
> > waitfor delay ''00:00:01''
> > end
> > SET ROWCOUNT 0
> > ')
> >
>
> Because your clustered index is on a key like user id, you're bound to
> be dealing with page splits. Every time a new row is inserted into the
> table, SQL Server has to find the right location and probably split the
> page. Page splitting is very slow. Not to mention the database layout is
> not ideal given your high end hardware. You really need to move the
> t-logs and tempdb off the same array as the data. What you're dealing
> with is lots of page splitting (slow) and a lot of head movement on the
> drive array (very very slow).
>
> You could also speed up the removal query by just checking the
> @@rowcount after the delete rather than running the EXISTS. Just break
> the infinite loop when @@rowcount is 0. You should probably also wrap
> the delete in a transaction inside the loop and increase the rowcount
> from 500 to more. Not sure how many rows you're pruning from the table,
> but because you're deleting on the clustered key, you could try
> removing them all at once.
>
> But I might consider changing the key if page splitting is a problem.
> The other option is to leave some space in the index by applying a fill
> factor and reindexing as needed.
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
>
- Next message: Adam Machanic: "Re: WorkDay Stored Procedure"
- Previous message: Steve Kass: "Re: 837 patch and MS03-031"
- In reply to: David Gugick: "Re: SQL Server Performance (slowdown)"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
