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

Alexa Is Listening, Recording, and Sending!

Check out this story where a couple in Oregon was having a private conversation and Alexa recorded and sent the conversation to one of their contacts!  They were not aware until the contact contacted them about what had happened.  The couple contacted Amazon and they verified the series of events.  But what's even more bizarre is the couple wanted to return their Amazon home devices but Amazon has not agreed to return the costs for the devices! Check out the article here  by Bruce Brown on  Digital Trends

The IT Helpdesk

One of the custom programs we created when I first started working with my current employer was an IT Helpdesk.  This was a program we wrote (ASP classic with SQL Server backend) to help track and manage our IT tasks/work. We managed our work into three categories: Production, Projects and List Items.  Production items were issues that needed to addressed right away ("on fire"), whether it be a system bug, a special report, etc.  Projects were larger tasks that would take a month or more to completes.  And list items where the requests that weren't really critical but could be worked on when Production and Projects were completed. 15 years ago it was more of a management tool for IT tasks, but now it has become a great resource for me when trying to figure out issues that have come up in the past.  This is especially helpful now that I'm the only developer/programmer here at my work.  When you have hundreds of pages and thousands of lines of code it's just har

Superbowl Weekend

Since I'm a huge sports fan I'm going to mostly talk about the big game this Sunday!  But first my rant about the Cincinnati Bengals!  I'm from the Cincinnati area and have grown up a Bengals fan.  When Marvin Lewis took over it was a good move and he did do a good job turning a losing program into a winning program.  But it's been 15 years now, no playoff wins (0-7) and the team has been getting worse the past 2 seasons (along with some really devastating losses).  So the Bengals extend his contract another 2 years?  I used to buy season tix for half the season but until the Bengals make some changes, starting with Marvin Lewis, I'm no longer buying tickets. Anyhow, I want the Eagles to win on Sunday but I think the Patriots will win.  I actually thought the Eagles were done with when Wentz got hurt but Foles has done a great job as the QB.  But the Patriots know how to win, and it doesn't matter who's on the roster as long as they have Brady and Belichic