June 9, 2003 at 10:19 am
We run a rubbish e-mail logging system that runs on SQL Server. We're getting poor response from it and so are looking at indexing (the package provided no relationships or indexes with the database...)
The problem we get is that whenever a row is inserted it sits straight on the end of the data as there is no clustering. This isn't too bad however as it is still in a logical time order. However the only real candidate indexes we can come up with are time realted. This means that loads of page splits will occur on the letter part of the index and nothing happens at the beginning.
Basically we can't decide whether its worth sticking an index on the table to increase the speed of some of the selects that the logging needs to insert the rows (to check correctness etc).
I worked out that the tables can grow anything up to about 10% per day (at peak). We reindex etc overnight. There is a reporting function that runs and has loads of selects (mostly by time) but that is not important as the performance is acceptable for the most part.
Any ideas?
Ta,
James
June 9, 2003 at 11:20 am
You do/dont have a primarykey already? If not, I'd start there if possible, probably go with identity or uniqueidentifier. I think probably worth indexing the datetime value, see what happens. Page splits cost of course, but not that much. I seldom have issues with insert performance, it's normally selects I have to work on.
Andy
June 9, 2003 at 11:27 am
True, data is kept in a heap unless there is a clustered index.
Indexing the tables will, probably, help with response time. Could you create a trace and run Profiler, say overnight, when the report queries are running? That might give you some ideas on the candidate indexes.
You say at one point there are no indexes, and then at another point you " reindex etc overnight". What are you reindexing?
Time related indexes are fine, they are good candidate indexes for primary keys (clustered) since primary keys are best used for a range of values.
I hope this helps...
Patrick
Quand on parle du loup, on en voit la queue
June 10, 2003 at 1:23 am
Thanks. We already have identifier fields in the table so I'll have a look at indexing those and the date time ones.
When I said we reindex it was more for the future as the database is included in our standard housekeeping procedures which reindex etc so when we have an index it'll be rebuilt every night.
James
June 11, 2003 at 11:50 am
Reindexing is good - but it depends on how many inserts, updates, deletes you have every day. You may be able to get by with a job that runs sp_updatestats.
A good way to choose candidate indexes is to look at your current queries. Look on the columns where you join tables and look at the columns in your criteria.
One way is to set up a SQL Profile trace, and run it while you are performing standard operations on your database. Be aware that this will add overhead to the server and if you have a lot of other users they may complain that it is slow. We have a program called "LoadRunner" that allows us to run scripts that simulate a number of users hitting the database.
Once you have the trace you can run it through Index Wizard to see what it recommends. It is only a recommendation, but it may give you a better idea of what to index and what not to index.
Patrick
Quand on parle du loup, on en voit la queue
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply