Posted by Greg D. Moore \(Strider\) on 03/23/07 19:59

"Zamdrist" <zamdrist@gmail.com> wrote in message
news:1174675879.584481.208100@l75g2000hse.googlegroups.com...
> On Mar 23, 12:38 pm, "Greg D. Moore \(Strider\)"
> <mooregr_deletet...@greenms.com> wrote:
>>
>> Just so you know, these aren't very large tables.
>>
>> However, I'd definitely agree you probably want some indexes.
>>
>> However, the question you're asking is a bit too generic. You probably
>> need
>> to look at what queries you're doing and optimize for those specifically.
>>
>> And generally you want to find not necessarily the longest running
>> queries,
>> but the ones called the most. If you have one query called 10 times a
>> day
>> that runs for 10 minutes and optimize it 10%, you'll save 10 minutes a
>> day.
>>
>> If you have one query called 10,000 times a day for a minute and optimize
>> it
>> 10%, you'll save 1000 minutes.
>
> A million records isn't large? Ok.

Nah, rather trivial these days. ;-)


>
> An application I didn't write and have NO support for is accessing the
> data, reading & writing to these tables. There *are* indexes but only
> one each and only on the primary key field.

If you have no access to these tables, you can't put indexes on it. So
perhaps I misunderstand.


>
> I have no access to the queries as the application is reading from the
> tables, probably using in-line string & code queries. There are no
> views or procedures used by the application (well very few and not in
> this instance). For all I know it could be using "Select * From
> TableName...".

Ack.

In any case, you probably CAN get this information via profiler and looking
at the queries as they come through the machine.

Also, there's some tools (only source I know of is via the SQL Server
Magazine website so they're copyrighted I believe) to follow wait
statistics, which can be VERY powerful to find out where your application is
doing a lot of querying.

You can also try (though generally I don't find it useful) the Index Wizard
in EM.

>
> There are only 4 fields in each table, so I suppose the query(ies)
> could only so complex as four fields would allow for.

And any joins.


> I know what one
> of the fields ties back into a more widely used table that does have
> more indexes, ones that appear to be useful.
>
> Thanks
>



--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com

[Back to original message]