Setitng a trigger for an Index (SQL Server 2000, Standard Web App stuff)

Topic

New Reply

I’ve got a big web-based reporting tool that has started to hit the ‘big time’ in terms of volumes of data. Now, performance issues are starting to crop up.

In particular, pages can load slowly and, at least once, INSERTS were timing out. After flailing around on this, I re-ran a clustered index on the PK of a main table and this sped things up. Today, the system started timing out again on pulling up some data on other tables. So…I re-can a clustered index on this table and… ta dah! it worketh. I went ahead and ran indexes on all the other tables just to feel better

Now, I’d like to just have SQL Server do this automatically. This may be a really simple problem to solve, but the UI is puzzling me. Triggers run SQL statements; I suppose you could put something like this is a SP:

CREATE UNIQUE CLUSTERED
INDEX [PK_Z_Counties] ON [dbo].[Z_Counties] ([County_Name])
WITH
DROP_EXISTING
ON [PRIMARY]

(this is on a ‘junk’ table as i don’t want to poke at production tables right now…)

But I’m again in flail mode

Anybody have anything to recommend here? Basically, I want SQL Server to re-run a clustered index on whatever, say, every two weeks. Perhaps this is a a SQL Server maintenance plan deal or somekind of Agent/Jobs task? It’s starting to look like triggers is not the way to go as they are tied to CRUD actions.

I’ll be looking around in the meantime, but any feedback on this would be appreciated. If you have any advise on Indexes or other things to offer re. why this might improve the performance of the db (and anything else that can help) I will definately appreciate it.

TIA

Reply | Quote

Replies

I don’t think I would use a trigger to do that sort of thing, but I’m a little puzzled about your index situation. If these are tables, then you shouldn’t have to rebuild indexes on any sort of regular basis – for example I recently build a 3M record table for a client in Waco, and I started the process with a table with the appropriate indexes, and even after appending all the records, and adding several thousand a week over the last 2 months, performance is the same as when we started.

Is perhaps what you are talking about an indexed view? In those cases, if you make design changes to the underlying tables or the view itself, it will drop all indexes most of the time. You should probably be running a maintenance task on your SQL Server database at least once a week – it sanity checks both tables and indexes and will correct them most of the time if it does find a problem. Frankly, we very seldom see a problem in SQL Server databases.

But you don’t want a trigger for what you are doing – there are only three ways to kick off a trigger – with an INSERT, an UPDATE or a DELETE. What you probably want is a stored procedure, and then you can use the job engine to kick off the sproc on a scheduled basis. I’ll be traveling for the next 10 days or so, and will have only sporadic internet access at best, but let us know how you get on, and I’ll try to check back somewhere along the line.

Reply | Quote

since writing the post i set up a Job on the server to index the tables, so the triggers part is no longer relevent.

What IS relevent is the issues with performance I am having. I don’t know why indexing (well, re-indexing) a table would have anything to do with performance — it was just a wild shot in the dark. However, the shot seems to have hit SOMETHING as the performance issues abate afterwards.

The first time this happened, I was getting a time-out when trying to run a INSERT command to a rather large table. The INSERT string itself has the following code appended to grab the ID of the just inserted row:

mySQL = mySQL + ” select IdentityInsert=Scope_Identity()”

set myRS = DataConnection.execute (mySQL)

ComplainantID = myRS(“IdentityInsert”)

“ComplainantID” is the PK on the table being targeted here and is the FK in all the other data tables, so this particular INSERT operation is essential to the entire system. When trying to find the problem with the timing error I thought ‘maybe there’s a problem with SQL Server finding the Scope_Identity() on the table. Scope_Identity() seems related to the whole concept of an Index. I haven’t Indexed this table for a while…lemme see…” So, I re-ran the index and, viola, time-out gone. (I also have already put Set Nocount On at the beginning of the SQL string BTW).

???

No one I have asked about this (including the guy who does ASP FAQ) has given me much insight into what is going on here; I haven’t had anyone say that my solution really makes any sense, tho. We’re not talking about a huge amount of data, not even 7000 records. Activity is, like 75 in a day, max. Strange…. Also, all the interaction is via ASP classic over https.

Odd, huh? I’m also checking the MDAC config on the server as that has lately been going a bit sideways on my machine, possibly others.3

TIA

Reply | Quote

Is your ComplainantID indeed a SQL identity table that autoincrements? And are you using ADO to link to the SQL tables? I have seen occasional delays in returning an identity value when using ODBC, but in general ADO behaves pretty well, especially if you are using the latest version.

Reply | Quote

of course it’s an keyed auto-increment identity field…

and, yes, I am using ADO. How do you check the version? Is ADO part of MDAC? If so, I recently upgraded to 2.8 RTM. I don’t know what’s running on the webserver as I don’t have access to it. If it’s SQL Server I need to check I do have access to that — we’re auditing it for MDAC problems anyway.

Reply | Quote

I haven’t done any serious checking, but I believe that MDAC 2.8 includes ADO version 2.8, and that appears to be the latest version. This http://msdn.microsoft.com/library/default….dk_overview.asp[/url] page gives you the MSDN view of the MDAC and it’s components. If you want to check the verison of ADO, try Progmatically detect which version of ADO is installed – it should work even on the webserver.

Reply | Quote

well, when i run compatibility checker i get:

msado15.dll which I take to mean ‘ADO 1.5’, file version number 2.80.1022.0, whatever that means.

Reply | Quote