Re: How to improve SQL Server 2000 performance?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 08/15/04 

Date: Sun, 15 Aug 2004 13:24:35 -0400

John,

Sorry I wasn't trying to be semantically picky<g>, I just wanted to
understand how you were actually inserting these rows. I would check to make
sure the compiled plans for these statements really are being reused. You
can look in syscacheobjects for this. If they are then not too big a deal.
But a few stored procedures to be rewritten if you switch back ends should
not be that big a deal. That's a lot of rows to be inserted individually.
How do you get the data to be inserted? Any chance you can use BULK INSERT
to load larger batches of rows at a time? That would significantly speed up
the overall operation and under the right conditions you may even be able to
get a minimally logged load which would also help with the tran log. But I
do agree given this information I suspect you have a large disk queue for
your log file and probably your data file as well. Perfmon will confirm
that easy enough. 

-- 
Andrew J. Kelly  SQL MVP
"John Smith" <johnsmith@no-reply.com> wrote in message
news:OdQDN2lgEHA.1392@TK2MSFTNGP11.phx.gbl...
> OK, to be semantically precise I should have said "an SQL INSERT
statement".
> We use parameterized statements such as
>
> INSERT INTO TABLE1 (col1, col2, col3, ...) VALUES(@value1, @value2,
@value3,
> ...)
>
> Then, using an ADO.NET command object, we can set the values of the
> parameters for repeated calls. This tends to be about 400% faster than
using
> non-parameterized statements. I believe what happens when using
> parameterized statements is that the SQL statement only compiles for the
> first call. After that it uses the compiled version for repeated calls.
It's
> a type of ad-hoc stored proc.
>
> We don't use stored procedures because we are not sure we will end up
using
> SQL Server. Also, using a stored procedure would probably only save time
for
> the first call of a statement... and there are a very limited number of
> different statements.
>
> Having read your earlier post I'm convinced that adding a 2nd drive for th
e
> transaction file would provide the biggest benefit. We only have a single
> SCSI drive at the moment and the transaction logs become huge.
>
> -- 
>
> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
> news:uklJM%23kgEHA.3148@TK2MSFTNGP10.phx.gbl...
> > John,
> >
> > I have no idea what that really means<g>.  An Insert is a statement not
a
> > query so I assume you mean there is an INSERT INTO OneTable SELECT xxx
> > FROM
> > Othertable? If so how does ADO.net play into this?    Why is this not
done
> > all in a stored procedure?  If you could post the actual Insert
statement
> > maybe we can suggest a more effecient alternative.
> >
> > -- 
> > Andrew J. Kelly  SQL MVP
> >
> >
> > "John Smith" <johnsmith@no-reply.com> wrote in message
> > news:%233M1JVjgEHA.3864@TK2MSFTNGP10.phx.gbl...
> >> Hi Andrew, thanks for the advice. The inserts are via parameterized sql
> >> queries using ADO.NET.
> >>
> >> -- 
> >>
> >> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
> >> news:%23KZJ8yfgEHA.2848@TK2MSFTNGP10.phx.gbl...
> >> > Run Perfmon to see where the bottlenecks really are.  Chances are it
is
> > in
> >> > disk I/O.  Your current and avg disk queues are most likely very high
> >> > during
> >> > this operation and especially during a checkpoint. It sounds like
> >> > everything
> >> > is all on one disk array.  By this I mean do you have your data, log,
> >> > tempdb
> >> > etc all on one physical drive?   You should at minimum place the
> >> > transaction
> >> > log file on a RAID 1 all by itself to get maximum insert performance.
> >> > Also
> >> > check the on board cache for the disk controller to see how the cache
> >> > is
> >> > configured.  You want 100% write back cache and by default most use a
> >> > 50
> > /
> >> > 50 ratio.  How are you inserting these rows?  Are you using Bulk
Insert
> > or
> >> > is this 1 million individual insert statements?
> >> >
> >> >
> >> >
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
> >> > Performance WP's
> >> > http://www.swynk.com/friends/vandenberg/perfmonitor.asp   Perfmon
> > counters
> >> >
http://www.sql-server-performance.com/sql_server_performance_audit.asp
> >> > Hardware Performance CheckList
> >> >
> >
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
> >> > SQL 2000 Performance tuning tips
> >> > http://www.support.microsoft.com/?id=q224587       Troubleshooting
App
> >> > Performance
> >> >
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
> >> > Disk Monitoring
> >> >
> >> >
> >> > -- 
> >> > Andrew J. Kelly  SQL MVP
> >> >
> >> >
> >> > "John Smith" <johnsmith@no-reply.com> wrote in message
> >> > news:eIowfbagEHA.2984@tk2msftngp13.phx.gbl...
> >> >> We have SQL Server 2000 running on a Dell Poweredge 1600SC (Dual
Xeon,
> >> > SCSI
> >> >> disk) with Windows Server 2003. The server currently has 1GB of ram
> >> >> and
> >> >> we
> >> >> are wondering if more memory will significantly improve performance
> > since
> >> >> one process in particular is taking a long time. The bottleneck is
in
> > the
> >> >> insertion of records into the database. During one prolonged
operation
> > we
> >> >> insert over 1 million records (each maybe 256 bytes). The
application
> >> >> uses
> >> >> asynchronous batch inserts, but it still ends up waiting at various
> >> >> points
> >> >> for the inserts to complete. Any ideas on how to possibly speed
things
> >> >> up?
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

Relevant Pages

  • Re: Extremely high % Disk Time .. How is this possible??
    ... Andrew J. Kelly SQL MVP ... > Slicing them into logical drives may look better but it does not increase ... I prefer to look at disk queue ... >>> Andrew J. Kelly SQL MVP ...
    (microsoft.public.sqlserver.clustering)
  • Re: How to improve SQL Server 2000 performance?
    ... > Hi Andrew, thanks for the advice. ... Your current and avg disk queues are most likely very high ... How are you inserting these rows? ... >> Andrew J. Kelly SQL MVP ...
    (microsoft.public.sqlserver.setup)
  • Re: Filegroups and indexes
    ... you have disk queue issues first. ... Andrew J. Kelly SQL MVP "Leila" wrote in message ...
    (microsoft.public.sqlserver.programming)
  • Re: Filegroups and indexes
    ... you have disk queue issues first. ... Andrew J. Kelly SQL MVP "Leila" wrote in message ...
    (microsoft.public.sqlserver.server)
  • Re: DB Locks
    ... Andrew J. Kelly SQL MVP ... >> Andrew J. Kelly SQL MVP ... >>> I'm getting Db locks when inserting into a simple table. ...
    (microsoft.public.sqlserver.server)