Skip to main content

Nonclustered Index in SQL Server

So, I've run into an issue with one of my queries because it takes about 40 seconds to run and according to the Recent Expensive Queries section of the Activity Monitor in SQL Server, there is a section of code taking up about 51% of the query. 

First, let me say that I've been writing Transact SQL for quite awhile but have never ventured into the Clustered and NonClustered areas of forming Indexes.  I do use indexes on all my tables but that's just the regular Primary Unique key. 

We've been using this query for awhile and it does work but when the result set is about 5K records it takes a little while to run.  So here's the actual query itself

SELECT 
    PartNo, 
    SerialNo, 
    HeciCode, 
    ScanDate, 
    OrderType, 
    PoNo,  
    po.IssueDate, 
    SoNo 
FROM ScanHistory sh WITH (NOLOCK) 
LEFT OUTER JOIN trkRWPOHeader po WITH (NOLOCK) 
    ON sh.PoNo = po.PONum 
WHERE len(PoNo) > 2 
    AND ScanDate > '1/1/2009' 
    AND PartNo = 'RDH102472' 
    AND sh.SerialNo NOT IN (SELECT SerialNo 
                            FROM ScanHistory WITH (NOLOCK) 
                            WHERE PONo = '' 
                                OR (OrderType = 'SO' AND SoNo = '000001') 
                            ) 
    AND sh.SerialNo NOT IN (SELECT i.SerialNo       
                            FROM skp_SkidItem i with (nolock) 
                            INNER JOIN skp_Skid sk with (nolock) 
                                ON i.SkidNo = sk.SkidNo 
                            WHERE sk.SkidStatus = 'Shipped' 
                                or i.ItemStatus = 'Shipped'
                            ) 
ORDER BY PoNo
When you look at the recommended Execution Plan it says:
Missing Index (Impact 51.4221): CREATE NONCLUSTERED INDEX [<name of missing index, sysname,>] ON dbo.ScanHistory (PartNo, ScanDate)

I have posted this issue on Stack Flow and I have had some responses but nothing really makes sense at this point.  I've tried the above suggestion using a dummy missing index but that didn't seem to work.  Anyhow, if any of you are expert Transact SQL programmers I would love to hear from you on any suggestions or just general explanations on Nonclustered Indexes.

Thanks!



Comments

Popular posts from this blog

2018 Goals Update

Wow, the month of January 2018 has flown by!  I cannot believe it's Feb 1 already!  But I will say I've been pretty happy with the progress of keeping my goals for the 2018 year!

Here's what I've kept to thus far:

1. Recite the Optimist Creed every morning (well at least during work days)

2. No more Mountain Dew in the morning

3. I've learned PHP and in the progress of learning C# and ASP.net (progress is good!)

4.  I'm almost finished reading Soft Skills by John Sonmez (trying to read 1 book per Quarter)

5. Family vacation to Disney has been booked!

6. Blog has been started!

Book Review: The New Psycho-Cybernetics

The book The New Psycho-Cybernetics by Dr. Maxwell Maltz is truly an amazing book!  This book should be read by everyone!  Whether your self-image is good or bad this book talks about taking steps to improve your self-image, live a longer life, and ultimately be happy!

I myself feel like I have a good self-image, my self-esteem is high and overall I'm an optimistic and happy person.  But I can see how a book like this can help me strive for higher achievements and help me to reach those higher goals!

For those with poor self-images and low esteem this book will benefit you immensely.  The book talks about several examples where people have been down, sick, lonely.  But have gone through the exercises in the book and come out with a much better self-image and becoming much more happy.

There are so many examples in the book its hard to cover them all and that's why I will definitely be reading the book again, as well as a reference for future inspirations.  Towards the end of th…

Increasing Your Day's Productivity

So, there are a couple of chapters in John Sonmez's book, Soft Skills The Software Developer's Life Manual, that covers increasing your productivity.  Now, I consider myself a pretty focused person and pretty productive, but there's always room for improvement right?  Some sections talk about how ALL people procrastinate at some time or the other, but how can you get more focus and less procrastination (and minimize those distractions)?

Well, one of the recommendations is the Pomodoro Technique which is basically setting up blocks of time to focus, then take a break.  Usually in increments of 25 minutes of focused work, then a 5 minute break, then after each 4 small breaks you get a 15 minute break. 

There's a website called KanbanFlow.com that uses this technique and helps you track your time and your tasks, projects, etc.  I've been using it for about one week now and I really like it and I can tell I have become even more productive.  I know exactly what I need …