Performance and second table

[Matik]

Hi,

I have a small theoretical issue.
I have one table, which is prettyu large. There is lot of evaluations
running on this table, that's why, each process need to wait for
another to be finished. Sometimes, for some critical functions, it
takes to long time.

I don't think that I can speed up processes, by changing the indexes on
the tables (to increase scan time for example), because this is
something what I was experimenting with already, and it was not enought
good.

My question is, will it improve performance, if I will create second
table, exactly like this one, and I will split some evaluations, that
the one, which defenately need to run on the source table will run on
the first one, and the second evaluations, will run on the other one.

To keep data consistance between this two tables, I was thinking baout
trigger on insert on the mother table, which will transport the data to
another one.

Second part is: to improve selects on the table, should I set indexes
with option of Fill factor as possible close to 100% or as possible
close to 0%. Or maybe should I set the pad index option?

What about clustered indexes. Is it better to use them if I would like
to increase performace for selects?

Thanks in advance

Mateusz

[SQL]

For selects you should have your indexes as close to 100% as possible,
if this table is frequently modify make it around 80%-90%

Also did you check the fragmentation level of your table?
A lot of times this improves speed dramatically
run DBCC SHOWCONTIG ('YourTableName ') and look at Scan Density, Avg.
Bytes Free per Page and Fragmentation Levels
If your density is low and/or fragmentation high run DBCC INDEXDEFRAG
(dbname,tablena me,1)
Lookup DBCC SHOWCONTIG and DBCC INDEXDEFRAG in Books online

Have you tried horizontal partitioning? This might benefit you

http://sqlservercode.blogspot.com/

[Hugo Kornelis]

On 27 Sep 2005 09:35:55 -0700, Matik wrote:

Hi,

I have a small theoretical issue.
I have one table, which is prettyu large. There is lot of evaluations
running on this table, that's why, each process need to wait for
another to be finished. Sometimes, for some critical functions, it
takes to long time.
Hi Mateusz,

If the processes are only reading the data without modifying it, then
there is no need to wait. They can run concurrently.
My question is, will it improve performance, if I will create second
table, exactly like this one, and I will split some evaluations, that
the one, which defenately need to run on the source table will run on
the first one, and the second evaluations, will run on the other one.
I doubt it. SQL Server doesn't know that the data in both tables is
equal. So if one query reads row #12345 from table #1, and the other
query reads row #12345 from table #2, SQL Server will fetch the
corresponding data from both tables from disk to cache. In short, you
are effectively halving the amount of cache SQL Server can use for these
queries. I expect performance to decrease.
To keep data consistance between this two tables, I was thinking baout
trigger on insert on the mother table, which will transport the data to
another one.
And this will hurt performance even more. The speed of inserts will slow
down because the trigger has to be executed. As a result, locks on the
main table will live longer, keeping other queries blocked for longer
amounts of time. And the second table will be blocked as well.

Since the data is apparently updated while you are querying it, you
might find benefit in a variation on your idea: make a copy of the
table, but don't use triggers to copy over all modifications. Instead,
set up a job that will periodically synchronise the data. Now make sure
that all queries that don't need up-to-the-second precision are used on
the copy table (that is only update periodically).
Second part is: to improve selects on the table, should I set indexes
with option of Fill factor as possible close to 100% or as possible
close to 0%. Or maybe should I set the pad index option?

What about clustered indexes. Is it better to use them if I would like
to increase performace for selects?

There is no magic bullet here. Each problem needs it's own solution,
that's why there are so many options.

Read more about performance at www.sqlserver-performance.com, or post
here with full details of your tables, indexes, queries and execution
plans for more advise.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Hugo Kornelis]

On Tue, 27 Sep 2005 23:30:53 +0200, Hugo Kornelis wrote:

Read more about performance at www.sqlserver-performance.com

I goofed when typing that URL from memory. The correct URL is
http://www.sql-server-performance.co...erformance.asp.

Unfortunately, the site is revamped since my last visit. The content is
still there, but buried in lots of irritating advertising.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Matik]

Thanks Hugo and SQL :)

I see, that there is no better way, like just experiment with this
indexes, and maybe modify some statements. During past two days, I've
did that, and now is much better.

Anyway, the structure of the tables is bad, so there is also no
possibility to use better indexing.

I've one more question, if we are already by the topic of indexes. My
question is about the phisical memory the indexes are using.
When I've made all tables empty, truncated db, shrinkt aso. but the
file size is still a little to big. I know, that the indexes are taking
also memory to be stored (specialy clustered), but, after I've removed
all data, the indexes should be also cleared, right? Or maybe I need to
rebuild them?

Gratings

Mateusz