Solved

SQL Server Table/Index Tuning Query

Posted on 2013-11-20
22
539 Views
Last Modified: 2014-02-06
Hi All,
I have SQL server table with 15,000 rows.  When I try  "select * from tbl name" , it takes minutes to return results instead of seconds.

This table is main driving table . Due to time it is taking to execute SQL, I often see bad response time at front end with deadlock issues reported.

There are around 16 Indexes on table. All of them are reorganised and with no fragmentation reported. Why it is not using any of them? See attached snap for details.

Thanks
2013-11-20-10-54-23-Microsoft-SQ.png
0
Comment
Question by:crazywolf2010
  • 7
  • 5
  • 4
  • +4
22 Comments
 
LVL 13

Expert Comment

by:jonnidip
Comment Utility
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.
0
 

Author Comment

by:crazywolf2010
Comment Utility
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
0
 
LVL 13

Expert Comment

by:jonnidip
Comment Utility
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.
0
 

Author Comment

by:crazywolf2010
Comment Utility
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
0
 

Author Comment

by:crazywolf2010
Comment Utility
I did rebuild indexes with separate analyze but no change,
0
 
LVL 9

Expert Comment

by:rajeevnandanmishra
Comment Utility
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.
0
 
LVL 13

Expert Comment

by:jonnidip
Comment Utility
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.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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)
0
 

Author Comment

by:crazywolf2010
Comment Utility
Hi,

Output of queries

(0 row(s) affected)

(53 row(s) affected)

Results attached in excelsheet.
indexes.csv
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
Comment Utility
Wow, extremely hard to analyze since the column lists in the missing-index data were no longer comma-delimited but each was its own separate entry.

After some complex searching of it, though, it looks like the clustering key should be:
[iDeliveryId]

This command will create it:
CREATE CLUSTERED INDEX NmsDelivery_iDeliveryId ON NmsDelivery ( iDeliveryId ) WITH ( FILLFACTOR = 99, SORT_IN_TEMPDB = ON ) --ON [your_filegroup_name]
NOTE: This command will require rebuilding all the nonclustered indexes, but for only ~15K rows, that won't take that long anyway.

Afterward, you should see a big boost in performance, and we can review the usage stats again to determine if a different clus key would be better.


Btw, if at all possible, get rid of [iDeleteStatus].  Checking a "delete flag" will often force a full table scan when otherwise one wouldn't be necessary.  [Those "soft delete" flags are a hold-over from when entire files had to be read regardless, so at that time they didn't add overhead.  Now they can destroy performance.]
0
 

Author Comment

by:crazywolf2010
Comment Utility
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
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 13

Expert Comment

by:jonnidip
Comment Utility
Just open a profiler...
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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.
0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
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?
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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.
0
 

Expert Comment

by:dthansen
Comment Utility
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/
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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.
0
 

Expert Comment

by:dthansen
Comment Utility
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.
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
Comment Utility
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.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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.
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
Comment Utility
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.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now