Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wilhud
Starting Member
1 Post |
 Posted - 2008-08-29 : 19:08:20
|
| I had asked these questions on another message board and one of the most trusted members referred me here. Here's my questions...Please bear with me, as I'm very green when it comes to setting up large databases and indexes and such.We currently have SQL Server 2000 running on a Windows 2000 server. There is a database setup on the server that collects all of our order and customer data.We're a small/medium business that doesn't have a heavy load, but every year around the holiday season, we have customer service searching for products and the queries seem to bog down, since there is a fairly decent backlog of old order & customer data.What I'd like to do is somehow setup some indexes to speed up the queries, but I've never worked with indexes and don't really know anything about them. Does anyone have any resources they would suggest looking into to learn the basics of indexes and how to set them up? I checked the SQL wizard and it looks fairly straightfoward, but I don't want to actualy do anything without more knowledge. If I do start testing indexes out, can they be safely added and removed at will or do I need to be careful with them?Also, I have found some info online which says I should use SQL Profiler to find out where my indexes need to be applied to speed things up, but I can't find anything explaining sql profiler or how to do this and what to look for. Again does anyone know of any resources on this? I opened the profiler and it looks fairly robust, much more info required then the index wizard, so I didn't want to just start playing around without knowing what I was doing first.Also, is there a basic theory behind what columns to index, based on what your heavy duty queries are doing? For example, we have a page that searches the db for customer order history, based on a search the CSRs make for a name or order number or email. This query runs extremely slow because the system is attaching the orders and customers tables and then searching through hundreds of thousands of records to find a match. This is our slowest query in the system by far. Would an index solve the speed issue and if so, what columns do you index? Order ID? First & Last Name? Email? All of them? Our data seems normalized fairly well, so I have to assume the missing indexes is what is keeping the speed down.Any advice or assistance is much appreciated. Again, I've never done any work with indexes, so sorry if I'm unclear or off base with anything I'm saying. Just trying to get up to speed.Edit: I ran a check on our db and found that we do have indexes setup on the Primary Keys, Foreign Keys and First Name, Last Name, Email fields of the tables involved in the query. So now I'm not sure where the speed issue is coming from. Can anyone tell me how I can test for speed issues in the db?Thanks,Wil |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-29 : 21:17:17
|
| Go to :www.sql-server-Performance.com and see lots of articles regarding indexes. |
 |
|
|
|
|
|
|
|
Index Setup