Can someone explain what "Chack your Indexes on the tables" means?

Question

Post reply

Can someone explain what "Chack your Indexes on the tables" means?

Chris Gilbert

SSC Enthusiast

Points: 191
More actions
June 20, 2005 at 4:22 pm

#186273

I did a job for a client a while ago and it was the first time i used SQL Server in a production environment. Recently after about a year of full production use I received an email from the web hosts Tech Support team informing me that 2 particular queries were using up too many resources on SQL Server and was affecting the other customers (it is shared hosting). The email I received said:
"...you need to check the indexes on all your tables and check these queries:
SELECT * FROM mainMenuTable ORDER BY mainMenuIndex ASC; (= Tablescan)
SELECT * FROM calendarTable WHERE eventDate = '15/06/2005' ORDER BY
eventTime ASC;"
Firstly what does he mean by 'check the indexes on all your tables'? I have heard of indexes and indexing with regards to performance but i don't really know how it works or exactly what it is.
Secondly, at the end of the first problem query he wrote (= tablescan). What does this mean and how does it affect/relate to performance?
I've since re-written the queries and, fingers crossed, everything should be ok now but any advice you can offer as to what steps I can take on an up and running shared server to improve performance would be appreciated.
Thanks in advance,
Chris Gilbert.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47794 More actions · Answer 1

Rather than repeat verbatim here, check the relevant topics in Books Online.

For starters try these,

Table and Index Architecture
Index Tuning Recommendations
Query Tuning

--------------------
Colt 45 - the original point and click interface

Ian Yates SSCoach Points: 19738 More actions · Answer 2

Your first query

SELECT * FROM mainMenuTable ORDER BY mainMenuIndex ASC; (= Tablescan)

needs to scan the entire table to retrieve all data (hence Tablescan) - probably not a lot you can do about that (and I doubt your mainMenuTable is very big anyhow).

The 2nd query

SELECT * FROM calendarTable WHERE eventDate = '15/06/2005' ORDER BY

eventTime ASC;"

Your query says that you want all data from the calendarTable given a certain eventDate. Think of the phone book - if you want to find Mr John Smith, you would go to S, then SM, find smith, then search alphabetically for J - using an index

If you do not have an index on your eventDate column, SQL server has no idea where the rows with the given event date are and thus must search through all rows in the table to find the rows you want. You can create an index on your calendar table so that SQL Server will be able to find the rows very quickly using a statement like

Create index ix_calendarTable_eventDate on CalendarTable(eventDate)

How did you end up rewriting your queries?

Chris Gilbert SSC Enthusiast Points: 191 More actions · Answer 3

Thanks Ian I'll try doing the index.

I didn't rewrite the query I rewrote the ASP.

Originally I was getting all the mainMenuItems which is around 10 and then looping 10 times getting all the subMenuItems where they related to these mainMenuItems. This resulted in around 11 recordSets being created. I now have just 2 by selecting all from mainMenuTable and alll from subMenuTable which totals around 40 rows in 2 recordSets. I then iterate through the recordSets.

Even this solution isn't ideal but I can't see another way of doing it. At least in ASP.Net the data containers are disconnected.

Chris.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Please do not cross-post. I already answered that question here :

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=65&messageid=192379

Chris Gilbert SSC Enthusiast Points: 191 More actions · Answer 5

Apologies for the apparent cross-post it wasn't intentional. I asked the question here first and then after getting some idea of where my problem lay I posted in the most suitable forum (Performance) to which Remi replied.

Apologies once again.

Chris