Tables seem too big - timeouts happening

[Ryan]

I have two problems I need some help with.

First, I've just inherited a system and am delving into a few timeout
problems that are causing problems for the users.

Now, if I do a simple select * from the table (which looks to be the
cause of the problem at this stage) in QA, I get the results back in
less than a second. If I open the table in EM it takes about 10. Is
there a difference in viewing the data this way ? I'm used to EM being
virtually the same speed. There is only one row. Minor question
really, just something I'd like to understand if there is a
difference.

CREATE TABLE [QUERY] (
[QUERY_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[CAT_ID] [numeric](18, 0) NOT NULL ,
[QUERY_DESCR] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[USER_NAME] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[USER_ID] [int] NOT NULL ,
[IND_EURO] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_QUERY_IND_EURO] DEFAULT ('N'),
[IND_DGCOLUMNS] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF_QUERY_IND_DGCOLUMNS] DEFAULT ('N'),
[NO_GROUPS] [int] NOT NULL CONSTRAINT [DF_QUERY_NO_GROUPS] DEFAULT
(0),
[NO_FIELDS] [int] NOT NULL CONSTRAINT [DF_QUERY_NO_FIELDS] DEFAULT
(0),
[NO_LINES] [int] NOT NULL CONSTRAINT [DF_QUERY_NO_LINES] DEFAULT (0),
CONSTRAINT [PK_QUERY] PRIMARY KEY CLUSTERED
(
[QUERY_ID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_QUERY_QUERY_CATEGORY] FOREIGN KEY
(
[CAT_ID]
) REFERENCES [QUERY_CATEGORY] (
[CAT_ID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO

I don't think any re-indexing has been done on this (or the other
tables in the db). I was wondering if constant adding/deleting rows
could cause the index to be massive and in need of a good clear out.
Any pointers would be appreciated. From what I can tell, there was
some problems trying to get replication to work. I need to dig deeper
to see if this is now correct.

-------------------------

Secondly, there is a another table in the same database.

CREATE TABLE [FIELD_DATA] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[DATA_ID] [numeric](18, 0) NOT NULL ,
[FIELD_ID] [numeric](18, 0) NULL ,
[FIELD_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[FIELD_VALUE] [numeric](15, 5) NULL ,
CONSTRAINT [PK_FIELDDATA] PRIMARY KEY CLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

It holds approx 4 million rows. The rest of the tables have minimal
data and about the same amount (consider them the same if you will).
Now, another 'copy' of this database is held elsewhere (different
client data) and this holds 40 million rows. The difference is that
the first DB is 4.5GB and the second 6.5GB (approx). Does this prove
my theory that re-indexing would be a good idea ?

Thanks

Ryan

[David Portas]

There are reasons why EM would be slow. The overhead of the GUI and its
locking strategy are two reasons.

Why on earth would you want to create indexes to optimize opening a
table in Enterprise Manager? EM isn't intended as an end user tool and
IMO should never be used to open tables on production systems. Your
goal should be to optimize real queries that you use in production and
they would surely not include "SELECT * FROM table" (!).

--
David Portas
SQL Server MVP
--

[Ryan]

David,

Sorry, probably wasn't clear. I don't want to create any more indexes,
nor would I use EM for production purposes. I would probably even
question the indexes that there are at the moment with the data it
holds currently. It was only a small question on that part about seeing
if there was a difference, mainly to confirm my suspicions about a
re-build of the indexes being needed. If it could be GUI only, then I
can ignore it, but if there is a difference in locking strategy, is
this relevant, if so, where can I look for more info ? Is the delay
nothing to do with my suspicions on indexes ? Not overly important if I
don't find the answer to this, but just nice to know if there is a
difference in returning the data (ignoring the GUI).

One part I had spotted was that it was very slow for something
relatively simple IMHO. Other tables in the db aren't a problem.

Quite correct about select *, however whilst trying to look at
something with 'disposable' code (from a development point of view)
which I will likely bin after a few seconds, there is nothing wrong
with this if I am having a quick glance at the data. I wouldn't put
this in a user system. If I'm honest, what they have developed I would
do differently, but I've only just inherited this a few days ago and
was looking to confirm my suspicions.

My main concern is with the indexes and the size they take up.

Ryan

[David Portas]

If you're not using EM in production then I wouldn't worry about the
performance of opening a table there. EM is a pain in lots of ways and
poor performance is just one of them.

Based on what you said about the relative sizes of your two DBs you
should check DBCC SHOWCONTIG to see if a REINDEX is in order. More info
here:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx

--
David Portas
SQL Server MVP
--