June 26, 2003 at 2:56 pm
Does anyone add non-clustered indexes for maintenance scripts, run at the end of the day when the Users are off, and remove them after this "End-Of-Day Processing" is complete?
Can I run something like, Add them at 7pm and remove them at 7am, in SQL Server 7?
Would this be a Job in SQL Server?
Thanks in advance for your time.
June 27, 2003 at 6:57 am
Yes, you could do that as a job in SQL Server.
I suppose the questions are how big are these tables and why go through the contortions to create the indexes nightly?
Dr. Peter Venkman: Generally you don't see that kind of behavior in a major appliance.
Patrick
Quand on parle du loup, on en voit la queue
June 27, 2003 at 12:49 pm
I suppose it depends on the maintenace scripts as well. If the index were to be used by a single script, the command could just be part of the script.
Guarddata-
July 8, 2003 at 9:38 am
I have a command-line VBScript (cscript) that runs for 24 hours without an index and 30 minutes with one. But if I leave the index on this field, it will affect on-line performance.
Thanks for the suggestions.
July 8, 2003 at 7:33 pm
Hi there
This is a classic OP from oracle dba's, but then again, any dbms will benefit from it as your reducing the IO required for maintaining the indexes. Another thing to be aware of in constraints. I have a classic example where my generic "organisations" table has 16 fk references to it throughout the app (yes - we probably should have sub-typed but we didnt), and how a delete over this table can take an unacceptable amount of time to remove the row. Just something to be aware of in your testing.
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
July 8, 2003 at 8:07 pm
Hi
Just thinking aloud here... take care with plan caching, I believe sqlserver is smart enough (BUT), take care with cached plans that are NOT using indexes, as they were cached during the night when the indexes were missing. May need to use the with recompile option for selected stored procs.
Interesting one...
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply