Query running slow – Significance of Non-Clustered index:

There was enough hue and cry when my customer’s query was running slow. Customer gave me a call and informed me that I had to take proper action to get this query faster. The query was nothing but an sp which was executing an insert command. The insert command was too slow. The coder was trying to get all the records in one go instead of taking them in chuncks. Good finding?? No there was more to this.

INTERVIEW PREPARATION IN 30 MINs – Read Following wires for a quick interview preparation.

  1. SQL Server DBA “Interview Questions And Answers”
  2. SQL Azure Interview Questions and Answers Part – 1
  3. SQL Azure Interview Questions and Answers Part – 2
  4. Powershell Interview Questions and Answers

I was surprised to execute the WHERE part of the query. This took total 18 min to give me count(1) from the table for only that part of data. What was missing?? Yes … our favourite non-clustered index. I was astonished to see the result as soon as I created the non clustered index. This took only 2 seconds instead of long 18 minutes.

This has been a pain for so many days and got resolved with the creation of a non clustered index. I had this table in all of my db partitions. So I created a query to create this index in all the db partitions as follows:

sp_MSforeachdb
‘USE ?
IF DB_ID(”?”) > 4
BEGIN
EXEC (”
CREATE NONCLUSTERED INDEX IX_datecreated ON tblmembername_cs (datecreated)
”)
END ‘

This significantly helped query to fetch data efficiently. So the lesson learnt: “Before taking deep dives, work on simple things”

Let me ask you some more questions if your query is running slow:
1. What is the recovery model of you DB? If full, can u set this to SIMPLE while the data is being extracted?
2. Do you have correct clustered and non clustered indexes in place?
3. Do you need to maintain IDENTITY key in table for every extraction? Are you maintaing t-logs? If the answer of both the two questions is NO. Then use TRUNCATE instead of DELETE FROM table.
4. I would also advise to delete the data in chunks if you are not going to use TRUNCATE.
5. Is anything else running which is resource intensive?
6. Would you like to change your cursor code with while loop?
…….. and the list goes on..

Regards,
Online SQL/Powershell Classes @ http://tuitionaffordable.webstarts.com

Advertisements

2 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s