Solved

SQL Server Table/Index Tuning Query

Posted on 2013-11-20
22
540 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
ID: 39662186
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
ID: 39662191
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
ID: 39662206
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
ID: 39662211
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
ID: 39662221
I did rebuild indexes with separate analyze but no change,
0
 
LVL 9

Expert Comment

by:rajeevnandanmishra
ID: 39662356
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
ID: 39662398
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
ID: 39662793
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
ID: 39662826
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
ID: 39663358
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
ID: 39665526
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 13

Expert Comment

by:jonnidip
ID: 39665531
Just open a profiler...
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39665957
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
ID: 39669316
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
ID: 39669357
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
ID: 39712424
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
ID: 39712535
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
ID: 39713015
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
ID: 39755036
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
ID: 39755081
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
ID: 39755107
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
ID: 39755183
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

912 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

21 Experts available now in Live!

Get 1:1 Help Now