Hi,

SQL Server 2008 R2

1 million row table
PK field_id (int)
IX nonclustered scheme_id (int), is_calc (bit). Not a covering index.

I have run DBCC FREEPROCCACHE, DBCC CHECKTABLE (it took 6 minutes),
UPDATE STATISTICS and DBCC CHECKUSAGE on the table. I have also
rebuilt all the indexes.

Without a query hint it runs a table scan which seems to hang (or take
forever).
With the query hint below, it comes up with a decent execution plan
(use nonclustered index and then key lookup), but still takes ~5
seconds... to return nothing!!!! There are no matches, so it shouldn't
even need to do the lookup, which means it's taking 5 seconds to
access the nonclustered index! This should be executing in 1ms!

SELECT
field_id, rft.is_calc_print AS fieldiscalcprint, rft.table_id AS
tableid,
field_name, translation, data_type, class_variable_result,
special_format, constant_value, net_class_variable_path
FROM report_field_translation rft WITH
(INDEX(IX_report_field_translation_scheme_id_is_ca lc_print))
WHERE scheme_id = 253
AND is_calc_print = 1
ORDER BY field_name

I have checked Resource Monitor and there doesn't seem to be any undue
strain on the server. There are 3 instances on the server, but
performance problems seem to be limited to this query.

Any ideas welcome!

Cheers,

James
MCDBA

JimLad (jamesdbirch (AT) yahoo (DOT) co.uk) writes:
I assume it is a typo, but the index definition you give above does
not match the query.

You did UPDATE STATISTICS, but did you do UPDATE STATISTICS WITH FULLSCAN on
the table? This can matter.

What exactly does the query plan look like when you force the index? Does it
use an Index Seek? Or does it say Index Scan?



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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

On 2011-12-05 13:06, JimLad wrote:
schemes, or 1 scheme associated with 500.000 rows, that would be Bad. You
can view the index' histogram to check the distribution (which will also
point to bad statistics).

FULLSCAN will help with getting the optimizer to a good plan, but if your
column distribution really is wildly skewed, and index on just that column
will not help a lot.

As Erland asked, index scan or index seek? An index seek taking 5 seconds
would be unusual (though not impossible).

Statistics on is_calc_print will also influence the query plan chosen. Check
the row estimates in the execution plan next to actual number of rows.

--
J.

Hi guys,

Sorry is_calc was meant to be is_calc_print. Just a typo. Each
scheme_id has about 4000 records so it's fairly selective. It does an
index seek with the index hint. I will use FULLSCAN in future.

Thanks for the advice, but I think I've found the problem. I think it
is due to having 7 sql instances on a 24GB server (I didn't set it up,
okay?). Min and max memory are not set for any of the instances, so
the instance I was looking at only had 2.5GB while one of the other
instances was hogging 12GB.

Unfortunately I mostly work as a .NET/SQL developer these days and DB
Server setup isn't my responsibility, but I still get called on to
solve these issues cos I know most about SQL in the company.

I have asked for the server memory to be upped 44GB as 3 of the
instances that I know about need 6GB each. I don't have any usage
stats on the relative load of the instances. Would we get away with
setting min server memory to something like 5GB on all instances, or
would you advise sharing out the memory using max server memory?

Also, it took me a while to figure out that the instance was low on
memory. What's the easiest way of spotting a struggling instance? I
know I could set up Perf Mon, but a) I don't have time b) it's not my
responsibility to set up proper monitoring of the server loads. I have
asked many times for it to be set up. Are there any nice commands from
within SQL to spot a struggling instance?

Cheers,

James

P.S. Erland - I reread your SQL TRY CATCH exception handling primer
the other day (we only just made it up to 2008 from 2000 and I'm
running a SQL course for everyone else). Thanks for that. Can't
believe it's taken 3 versions for MS to introduce a reraise though!

JimLad (jamesdbirch (AT) yahoo (DOT) co.uk) writes:
If you have seven instances on the same machine, I would definitely set
max server memory on all of them. With a total of 2-4 GB over for the
operating system.

You could set "Min server memory" if you like, but don't set it too high.
Max is the important one.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Hi Erland,

Can you explain why I should use max server memory rather than min? As
I understand it, that would prevent the more heavily used instances
being able to use free memory above their max allocation even if the
other instances aren't using it? Whereas min would avoid memory being
taken from some instances to give to other instances and allow some
memory management features to kick in? I imagine I'm being naive?

On the other incidental issue, I've just realised that of course I
could have changed the PK to nonclustered and the other index to
clustered.

Cheers,

James

Also should we use the keep locked in memory option? How much memory
should we allow per instance in addition to max memory setting?

Cheers,

James

JimLad (jamesdbirch (AT) yahoo (DOT) co.uk) writes:
I can't say that I'm an expert in server tuning, but my thinking is
that you don't want instances competing about memory. That would itself
cause an overhead that is undesireable.

But the assumption here is that the use of the instances are relatively
even. With even here I don't mean that they all need the same amount of
memory, but they have the same level of activity.

If you have an instance which is a sleep most of the time, and then
suddenly get a very high activity, for instance end-of-month reporting,
then it is of course a bit of waste of memory to give this instance 4GB
of memory that could be used by other instances in the meanwhile. Then
again, if that instance wakes up, do you really want to see degradation
on the other instances?

I guess you mean the "Lock pages in memory" privilege? Yes, keep that
one. I'm not sure that I understand the second question, but the sum
of max server memory should be well below the total in the machine.
I said 2-4 GB earlier, but since Max server memory only controls
the buffer pool, 6-8 GB may be better.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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

Select * from sys.dm_db_missing_index_details

95% of SQL Server people don't create enough indexes, sorry, hope that helps!