My company has a sql database with a 100 gig database. There are many
performance issues that I believe are linked to database design and
programming. I have a couple of questions that I hope can be
answered.
The database only has 26 gig of real data the rest are indexes.
Is this normal? I know the extra indexes cause performance problems
with inserts,updates and deletes.
The databse has huge stored procedures many pages long. Is it the
right thing to do putting all the work onto the sql server itself?
Shouldn't these long procedures be handled in the middle tier using vb
or c?
Triggers using inserted and deleted tables. These triggers are used
on tansactions for inserts udates and deletes on the database. From
what I have seen monitoring the server these triggers run twice as
long as the update delete or insert and since the trigger is fired
during a transaction I would guess that the transaction is not
commited until the trigger is done. Would I be correct in assuming
this?
Thats all I have for right now any help would be great. If you had
any documention to back this up would help alot. I seem to be in a
battle with the programming group on this whole performance issue. By
the way the server hardware is dual 2 gig xeons 4 gig memory 165 gig
hd space on raid 5.
Jim
jmaddox@.oaktreesys.comi have frequently seen databases where there was as much
space used for indexes as for data
i think the highest index to data size ratio i saw was
~2X, and i felt that one had unnecessary indexes. good
table design is also part of index minimization.
btw, each index adds between 15-40% overhead to the base
cost of modifying a row, depending on a few factors (SQL
Server Connections conference, Oct 2003, SDB417)
i like to maintain a script that executes each sp once
(plus consideration for multiple code paths)
then i can drop indexes one by one to look for table scans.
a big problem with very long sp's is recompiles, an insert
into a temp table or other factor could trigger a
recompile of the entire sp (fixed in Yukon), so if can't
fix the cause of the recompile, breaking a big proc into
smaller procs can be helpful
i prefer using sprocs and not triggers. triggers are good
if you are using sql statements, so you need only one
network roundtrip to handle the complete transaction.
i believe triggers to be less efficient in multi-row
operations, where the trigger may fire once per row,
>--Original Message--
>My company has a sql database with a 100 gig database.
There are many
>performance issues that I believe are linked to database
design and
>programming. I have a couple of questions that I hope
can be
>answered.
>The database only has 26 gig of real data the rest are
indexes.
>Is this normal? I know the extra indexes cause
performance problems
>with inserts,updates and deletes.
>The databse has huge stored procedures many pages long.
Is it the
>right thing to do putting all the work onto the sql
server itself?
>Shouldn't these long procedures be handled in the middle
tier using vb
>or c?
>Triggers using inserted and deleted tables. These
triggers are used
>on tansactions for inserts udates and deletes on the
database. From
>what I have seen monitoring the server these triggers run
twice as
>long as the update delete or insert and since the trigger
is fired
>during a transaction I would guess that the transaction
is not
>commited until the trigger is done. Would I be correct
in assuming
>this?
>Thats all I have for right now any help would be great.
If you had
>any documention to back this up would help alot. I seem
to be in a
>battle with the programming group on this whole
performance issue. By
>the way the server hardware is dual 2 gig xeons 4 gig
memory 165 gig
>hd space on raid 5.
>Jim
>jmaddox@.oaktreesys.com
>.
>|||I don't think triggers fire once per row in SQL Server since there is not
ROW level triggers like in Oracle.
They are set based only. But beware of triggers since they make it harder to
follow the flow of what is happening.
If you are running big inserts, deletes or updates (multiple rows per
command) and use the inserted, deleted table in joins in the trigger I'm not
sure the performance will be incredible. You should probably try to see if
sp's with all the logic of what the triggers are doing could be created and
called instead of relying on the trigger processing.
Triggers are part of your transaction. So if these commands are long lasting
and touch lots of data you can get into blocking problems. Which obviously
doesn't help performance. In a sp you could control the transactions
explicitly and commit (or rollback) at more than one point.
As for having sp's or a middle tier in vb or other :
I prefer having SQL code located on SQL Server. This way it's easy to
isolate and change SQL code that's not optimal. You can see that is what MS
thinks also in Yukon by having SQL Server host .NET so we can create more
complex procs.
The middle tier can perhaps generate the commands used to access the DB but
it should put it in a proc and use that next time around. This way the
middle tier can call one proc to return multiple datasets instead of
executing each command separately occurring a round trip each time. Also
your middle tier can perhaps cache some amount of data as to not always hit
the DB.
As for your index check this site out :
http://www.sql-server-performance.com/optimizing_indexes.asp
The above site holds a great deal of info you should probably browse it and
you will surely find a load of answers to your questions.
Chris.
"joe chang" <anonymous@.discussions.microsoft.com> wrote in message
news:0c7201c3be83$258e93c0$a001280a@.phx.gbl...
> i have frequently seen databases where there was as much
> space used for indexes as for data
> i think the highest index to data size ratio i saw was
> ~2X, and i felt that one had unnecessary indexes. good
> table design is also part of index minimization.
> btw, each index adds between 15-40% overhead to the base
> cost of modifying a row, depending on a few factors (SQL
> Server Connections conference, Oct 2003, SDB417)
> i like to maintain a script that executes each sp once
> (plus consideration for multiple code paths)
> then i can drop indexes one by one to look for table scans.
> a big problem with very long sp's is recompiles, an insert
> into a temp table or other factor could trigger a
> recompile of the entire sp (fixed in Yukon), so if can't
> fix the cause of the recompile, breaking a big proc into
> smaller procs can be helpful
> i prefer using sprocs and not triggers. triggers are good
> if you are using sql statements, so you need only one
> network roundtrip to handle the complete transaction.
> i believe triggers to be less efficient in multi-row
> operations, where the trigger may fire once per row,
> >--Original Message--
> >My company has a sql database with a 100 gig database.
> There are many
> >performance issues that I believe are linked to database
> design and
> >programming. I have a couple of questions that I hope
> can be
> >answered.
> >
> >The database only has 26 gig of real data the rest are
> indexes.
> >Is this normal? I know the extra indexes cause
> performance problems
> >with inserts,updates and deletes.
> >
> >The databse has huge stored procedures many pages long.
> Is it the
> >right thing to do putting all the work onto the sql
> server itself?
> >Shouldn't these long procedures be handled in the middle
> tier using vb
> >or c?
> >
> >Triggers using inserted and deleted tables. These
> triggers are used
> >on tansactions for inserts udates and deletes on the
> database. From
> >what I have seen monitoring the server these triggers run
> twice as
> >long as the update delete or insert and since the trigger
> is fired
> >during a transaction I would guess that the transaction
> is not
> >commited until the trigger is done. Would I be correct
> in assuming
> >this?
> >
> >Thats all I have for right now any help would be great.
> If you had
> >any documention to back this up would help alot. I seem
> to be in a
> >battle with the programming group on this whole
> performance issue. By
> >the way the server hardware is dual 2 gig xeons 4 gig
> memory 165 gig
> >hd space on raid 5.
> >
> >Jim
> >jmaddox@.oaktreesys.com
> >.
> >
