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

On Mon, 29 Sep 2008 01:47:36 -0700 (PDT), time_error@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

> 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?

Yes, a poorly formulated query or lack of useful indexes can result in
excessive CPU utilization. SQL Server will sometimes throw parallelism at
such a problem, resulting in multiple processors being used for a single
query.

You can set CommandTimeout in application code so that queries that run over
the specified duration are cancelled automatically via an attention signal
from the client. However, I suggest proactive monitoring on the server side
in order to identify problem query(s) so the root cause can be addressed.

> 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?

There is no rule of thumb on index size per table. In the case of a narrow
table and a large clustered index key, it is quite likely that total index
size is much larger than the underlying table. This is because the
clustered index key is also stored in all non-clustered index leaf nodes for
use as a data row pointer.

> 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?

It is true that too many indexes can hurt performance INSERT, DELETE and
some UPDATE operations. However, consider that data are typically queried
much more than updated so additional indexes that optimize those SELECT
queries might be justified. There may be opportunities for index
consolidation depending the on the specifics of the indexes and how they are
used. The sys.dm_db_index_usage_stats DMV can identify unused indexes.

I suggest you identify problem queries and perform query/index tuning as
needed. If you need , post the complete table DDL and problem query
here.


--
Hope this s.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

(time_error@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 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, esquel@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