Performance/stability issues

[time_error]

Please bear with me - I’m quite new to MSSQL and the whole db domain.

The db itself is pretty simple. There are approx. 15 tables. The two
largest tables’ holds a total of 10 mill. entries.
1)
Once or twice a week the CPU on our db server load (powerful quad
core) goes berserk. CPU load rises to 95% for a couple of hours and
then falls back down to a normal level. Is it possible that an
“inappropriate” SQL request (search) could result in such behaviour?
Are there any internal timeouts that should kill such a request?

2)
The largest table holds 6 mill. entries (id, datestamp, bigint,
varchar(50)) – size on disk: 600 MB. Indices uses 3.2 GB! Is there any
rule of thumb how much disk space the indices should occupy in
relation to the actual data?

3)
The table sketched in #2 has 9 associated indices – is it not correct
that any table should not have more than 3 – 4 indices because of
performance issues?

I'm using Microsoft SQL Server 2005

BR,
Jonas

[Roy Harvey (SQL Server MVP)]

On Mon, 29 Sep 2008 01:47:36 -0700 (PDT), ti********@hotmail.com
wrote:

>Please bear with me - I’m quite new to MSSQL and the whole db domain.

The db itself is pretty simple. There are approx. 15 tables. The two
largest tables’ holds a total of 10 mill. entries.

Ten million rows is not a lot these days.

>1)
Once or twice a week the CPU on our db server load (powerful quad
core) goes berserk. CPU load rises to 95% for a couple of hours and
then falls back down to a normal level. Is it possible that an
“inappropriate” SQL request (search) could result in such behaviour?
Are there any internal timeouts that should kill such a request?

A query that does not join properly can easily go a bit nuts. A query
that lacks the proper indexes to support it, or that is written so
that indexes can not be used, can too, but the worst case isn't
usually as bad. The dba should nail down what operation is causing
this.

>2)
The largest table holds 6 mill. entries (id, datestamp, bigint,
varchar(50)) – size on disk: 600 MB. Indices uses 3.2 GB! Is there any
rule of thumb how much disk space the indices should occupy in
relation to the actual data?

Sounds like someone got carried away following the advice of the Index
Tuning Advisor. That particular tool provided by Microsoft is of
questionable value, and unfortunately the advice it provides is best
filtered through someone who does not need to run it in the first
place. And yes, 3.2GB of indexes on a 600MB table is excessive.

>3)
The table sketched in #2 has 9 associated indices – is it not correct
that any table should not have more than 3 – 4 indices because of
performance issues?

That is a lot of indexes. However I really should not make a blanket
statement that it is too many. For all I know the table is subject to
a wide variety of demanding queries that absolutely must return
results in a very short time. I would expect INSERT performance to
suffer.

>I'm using Microsoft SQL Server 2005

Thanks for provide that important information. Too many posts do not.

The first thing I would do in your situation is check out the
fragmentation of all those indexes. Look at the Dynamic Management
View sys.dm_db_index_physical_stats for the indexes on that table. It
is possible that the indexes are not being defragmented which can
affect their size and performance.

The second thing, or perhaps it should have been first, is to inspect
sys.dm_db_index_usage_stats to see how heavily each of these indexes
is used. Note that these statistics only go back to the last time SQL
Server was started. Hopefully that would cover a wide range of time
so all major operations against the table have occurred. (It would
not be too great to drop an index used once a month for a monthly
report when without it the report runs for three hours). Indexes that
are not used waste space and CPU resources on INSERT and DELETE (and
to a lesser extent on UPDATE at times).

Roy Harvey
Beacon Falls, CT

[Erland Sommarskog]

(ti********@hotmail.com) writes:

1)
Once or twice a week the CPU on our db server load (powerful quad
core) goes berserk. CPU load rises to 95% for a couple of hours and
then falls back down to a normal level. Is it possible that an
"inappropriate" SQL request (search) could result in such behaviour?
Are there any internal timeouts that should kill such a request?

In addition to what Dan and Roy said, the Profiler is a good tool to
track down performance issues. You can set up a trace that filters
for long duration. Just be careful that you don't load trace too much,
as that alone could take a toll on the server. (The recommended for a
production trace is a server-side trace, but as a newcomer you are
probably better off with Profiler to start with.)

You can also use a tool that shows the current activity on the server.
There is some built-in in SQL Server Mgmt Studio, but I don't really
know how good it is. Myself, I tend to use my own beta_lockinfo,
which you find at http://www.sommarskog.se/sqlutil/beta_lockinfo.html.
It's really intended to help with blocking situations, but I've found
that it's good at identifying troublesome statements as well.

In both cases, it does take some experience to understand the output,
but you should be able to get some ideas of what is going on.

2)
The largest table holds 6 mill. entries (id, datestamp, bigint,
varchar(50)) – size on disk: 600 MB. Indices uses 3.2 GB! Is there any
rule of thumb how much disk space the indices should occupy in
relation to the actual data?

It depends a lot what the table is used for. Maybe there are functions
that permits users to search on a lot of columns. In such case those
indexes may be needed.

Another issue is whether you have the right indexes. The fact that you
seems to have something that taxes the servers is indication of that
you have not.

3)
The table sketched in #2 has 9 associated indices – is it not correct
that any table should not have more than 3 – 4 indices because of
performance issues?

Again it depends. For a transaction-type of table, there is reason to
be careful with indexes, as they cause an update overhead. For a summary
table that is updated once a night, this is far less of an issue.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx