SQL Server Table/Index Tuning Query

If you select as "select * from tbl name", you use no index at all, because you have no "where" in your query.
In the real-world applications, there should not be any case where you need to get 15k rows without any kind of filter.
Also very long reports usually do a sort of "paging" on data.

That's all.

Oh Sorry I didn't add a where condition but there are number of queries with where condition.

One frequently used is "WHERE (iDeliveryId = 0)"

Is there an easy way to locate all sqls fired on a given table using DMVs and not using profiler?

Thanks

One suggestion I can give you is to analyze all your indexes.
It seems to me that 16 indexes for a table with 15k records are too much (but it may be due to the number of columns).
You may see that many indexes are never used and some of the indexes may be merged.
Please take a look at this article for unused indexes: http://www.mssqltips.com/sqlservertutorial/256/discovering-unused-indexes/
and this page for missing indexes: http://msdn.microsoft.com/en-us/library/ms345434.aspx
Please note that "missing index" does not mean you "need" to create it.
A deep analysis of your indexes should be done to discover what the problems really are.

Lastly, my personal preference to access a db is one and only-one: Stored Procedures.
If you access a db through SPs, you can get the best control (personal thinking) on your db.
Dynamic queries usually tend to be junky and badly formed (usually because those are not made by a dba).

Regards.

Hi,
I ran a DMV SQL to locate which indexes on this table are Indexes under row-locking pressure.
This table is very active and results are below.

TableName      IndexName      row_lock_wait_in_ms      row_lock_wait_count
NmsDelivery      NULL      7563450      39849
NmsDelivery      NmsDelivery_id      870529      559

I did rebuild indexes with separate analyze but no change,

Hi,
As jonnidip said, if you are doing "select * from tbl_name", it will not use any indexes is true. Also, because you have not used any "Where" Clause, so the selectivity is 100% and hence all the rows and all the columns in any case need to be retrieved. And that is the reason it is taking time.

From your statement that the Table is a "main driving table", i assume that a lot of insert/update/delete (data modification) would have been happening on this table. And that ofcourse may lead to deadlock.

I suggest you to use "NOLOCK" hint for this table. This will help in reducing the time, that the "Select" query is putting to get "shared lock" on the modifying rows. This will certainly improve the time taken in reading the data. But reading the data (dirty) this way, may have severe ramification in your application.

Before modifying, Please check this link for understanding the impact of "NoLock" in detail http://technet.microsoft.com/en-us/library/ms173763.aspx.

Thanks.

As suggested by rajeevnandanmishra, adding with(nolock) in your select can give great help.
I did it on many select statements on critical tables and I achieved better reading performance.
I would anyway focus on the number of indexes and on the fact that the table seems to receive much editing (not reading) pressure.
Please note that an index can cost much at write-time.
A simple insert on a non-indexed table is limited to writing the row on the table.
But as many indexes you have, as many writing instructions you need to update the indexes too.
I may think also that the insert/update/delete operations on this table are done under transactions.
Much attention should be given at the developer-side of these operations. A wrong transaction level can easily translate into very bad performance for the whole table/db.

Regards.

First, you need to create the best clustered index for the table.  Heaps -- tables w/o a clustered index -- are not managed as well by SQL Server.  I guarantee you there's fragmentation in the heap, probably very severe. (An index name of "NULL" shows that the table is a heap.)

Please post the index usage stats and "missing" indexes for the table; that will at least be some help, although understanding the searches in the app code is often even more valuable for final determination of the best clustered index.


USE <db_name_containing_table> --<<-- change to your actual db name

DECLARE @table_name nvarchar(256)
SET @table_name = 'dbo.NmsDelivery' --<<-- verify/correct table name

SELECT OBJECT_NAME(ius.object_id) AS table_name, i.name AS index_name, ius.*
FROM sys.dm_db_index_usage_stats ius
LEFT OUTER JOIN sys.indexes i ON
    i.object_id = ius.object_id AND
    i.index_id = ius.index_id
WHERE
    ius.database_id = DB_ID() AND
    object_id = OBJECT_ID(@table_name)


SELECT
    OBJECT_NAME(mid.object_id) AS Table_Name,
    mid.equality_columns, mid.inequality_columns, mid.included_columns,
    migs.*,
    mid.statement, mid.object_id, mid.index_handle
FROM sys.dm_db_missing_index_details mid
LEFT OUTER JOIN sys.dm_db_missing_index_groups mig ON
        mig.index_handle = mid.index_handle
LEFT OUTER JOIN sys.dm_db_missing_index_group_stats migs ON
        migs.group_handle = mig.index_group_handle
WHERE
    mid.database_id = DB_ID() AND
    OBJECT_NAME(mid.object_id) = PARSENAME(@table_name, 1)

Hi,

Output of queries

(0 row(s) affected)

(53 row(s) affected)

Results attached in excelsheet.
indexes.csv

Hi ScottPletcher,
My SQL response time is still not right.  I can see execution plan now improved to show a clustered index.
Is there an easy way to locate all SQLs being run on a given table?

Thanks

Just open a profiler...

Re-run the index usage stats and missing index queries above and post the results.

Also, if possible, post the XML version of the current query plan.

It's possible the "IsDeleted" flag is going to cause a full table scan no matter what, so if it's a very large table, it will take time to scan.

Still odd, though, I would have thought converting it to have a clustered index would have cleaned up any old, heap-related performance issues.

scott did you take into account where he said

One frequently used is "WHERE (iDeliveryId = 0)"


was a frequently used here clause...

I'm have an uneasy feeling about the use of ideliveryid as the clustering index...

is deliveryid being updated?

if so with what frequency,

what is the cardinality of deliveryid (how many different values can it take...)

is there a process which physically deletes rows from the table?

what proportion of the rows will never be changed?

I normally figure that an "id" column is static.  Yes, it might be empty/0 and then assigned a value later, but then that value doesn't change.

Still, if it's queried most often, it may still be the correct clustering index.  The table definitely requires a clustered index.

With only 15,000 rows in a table, even without an indexes, it should never take more than a few seconds to return records. It is unlikely you're hitting a performance issue so much as you're hitting a locking issue and your query is sitting and waiting for other queries to release locks on that table so it can continue. Additional explanation on the use a NOLOCK here: http://sqlperformance.blog.com/2013/12/08/when-to-use-nolock/

Heaps can demonstrate odd performance issues on even a relatively limited number of rows.  While it could be locking, it could also be chains in the heap or some other heap-related issue.

Certainly can't disagree with ScottPletcher as heaps being one of the possibilites. I've seen his posts here for years and he doesn't miss.

You could check for sure where it is locking by running two tests

Test1: select * from sometable where IsDelivery = 0
Test2: select * from sometable (nolock) where IsDelivery = 0

If Test2 is materially faster than test1, I feel pretty confident it is a locking issue.

The other thing to consider in this case is that whenever * is used as the column list, the optimizer has 2 (or more) choices, but the choice is based on cost.

If I have 15000 rows and I have an index on iDeliveryId in an Index. It looks at the statistics to see how many 0 values it would expect.  If it uses the index to get the keys for all the iDeliveryId = 0 and the cost of Clustered index Lookups is higher than the cost of the Scan it will just do the scan.

Remember that when you use a * you are telling the optimizer in many cases that a Scan is OK, even though you want it to come back fast.  If stats are out of date or are skewed, it can trick the optimizer into scanning even then.

So it is not as easy as making sure that you have no fragmentation or that you have a clustered index (even though I say you should always have a clustered index), but that you should know what you expect back and plan appropriately.  You could get some value out of using a CTE and then using a JOIN to get the data back faster.

WITH C
AS (
SELECT ClusteredKeyField
FROM dbo.TableName
WHERE iDeliveryId = 0
)
SELECT T.*
FROM dbo.TableName T
INNER JOIN C ON T.ClusteredKeyField = C.ClusteredKeyField

This way you are getting only the keys and it does take advantage of the index to get the keys back and then you join on the clustered key which will have access to all the columns and will join well because it is the key field.

Just an idea.

This query:
SELECT ClusteredKeyField
FROM dbo.TableName
WHERE iDeliveryId = 0
itself requires a full table scan (unless the table also has a nonclustered index, in which case SQL will scan the nonclustered index).

The other key point is that a query like this:
SELECT T.*
FROM dbo.TableName T
WHERE
    T.ClusteredKeyColumn IN (1, 5, 7)
never requires a table scan.

I assumed that there was an index on iDeliveryId so if there is not then assuming got me there.  If it does have an index on iDeliveryId it can solely use that index to get me the values of the keys since the clustered key is in every non-clustered index.  If there is not a clustered index then you can ignore my post.

The last part just confirms my comment in that whenever you have 3 it will seek in the clustered index to find them. But at the same time if you have 3 keys in the CTE that comes back it will not scan the table either because it will join on the Clustered Key.

OK, I see what you're saying now.

But in that case SQL should generate exactly the same, or possibly even a better, plan for:

SELECT T.*
FROM dbo.TableName T
WHERE iDeliveryId = 0

If SQL can effectively use a nonclus index to get the clustering keys, then use those to go to the main index, it will do so.