ALTER TABLE Live System Performance

[rac2]

With SQL Server 2000, web application has 10 users, inserting a row into the table about once a minute, table has 200,000 rows.

I need to add a column with a default value.

Will this cause cries of anguish or will it go unnoticed by my users?

 

[SQLSister]

Personally I never make database table structure changes when the system is live unless it is a 24/7 application, even then there will be a low period. I would make the change first to a development database and then test the user application, adding a column can break things depending on how the user interface is programmed.

If all goes well, tehn i make the change to production during offhours and with warnings to the users inadvance if possible to make sure I'm not intetroducing a change that might affect the system when they are ina crunch processing mode.

Questions about posting. See faq183-874

 

[donutman]

I think SQLSister was implying that in the 24/7 case you should also test making the change to the development server while the application is actually running. Or do you (Siss) already know that it would be ok as long as the application continues to work after a change?
Rac2, I sure don't envy your job.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

Always test in development first. In a 24/7 shop, you can't take the system down for long, so testing in development becomes even more critical.

Questions about posting. See faq183-874

 

[donutman]

I failed to make my question clear.

you should also test making the change to the development server while the application is actually running on the development server.

I was wondering if an Alter Table statement can be run while an application is running without destroying the current connections.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[rac2]

I hadnt thought about "adding a column can break things". I was just thinking about response time, a slow down, or worse a stop while the table is redefined.

So I should create a development database and application. By a fluke I have the development application although it is not synched with the live system, you know, tweaks here and there.

The performance issue is then shifted to what is the effect of copying the database? If I just copy the mdf(300MB) and ldf files to another server, will that affect the live system? In other words must that be an off-hours task, too?

And eventually the ALTER TABLE must be run on the live system. Good idea to do this during off-hours. But is this likely to be a matter of seconds, minutes, or hours?

Karl, I have a great job. Just dont want to mess it up.

 

[donutman]

If you don't have any development data to work with now, then just link the two servers and grab a few thousand rows off the production server. You must have a current version of the production application on your development server? Backups?
And I just want you to be able to keep that great job! I think that Admins who do this sort of thing routinely do so by first writing out a complete work plan and review it with others just to make sure they haven't overlooked something. 24/7 systems scare me...I make lots of mistakes.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

I'm fairly sure that if you make the changes through EM's table designer, that current connections would have to drop. EM's script actually creates a new table and deletes the old, so I imagine that would take the application down unless it's error checking handled that. But that still leaves the possibility of modifying the meta data through your own Alter Table statement.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

Whether you should create a development db during production hours, somewhat depends on the size of the db and the usage against the db. The simplest way is of course just to restore your backup on the development server. This will not involve the production server at all (assuming you havea a currect backup) and can easily be done during work hours.

Always, always do all developemnt on a development database. I don't even allow my developers to have rights beyond normal user rights on the production server, only dbas are allowed to change anything on it.

Even tiny changes can break things unexpectedly. For instance, suppose someone wrote a union query using Select * in the individual selects. (A poor but common practice.) Add a column to just one of these tables and suddenly something breaks. Or if you are copying records to an audit table through a trigger. If you change the main table without changing the audit table (A developer who is not the dba may not even be aware such a table exists.), then suddenly the trigger may not work and no one can change or delete records. A true problem on a production system and one which might take a while to track down as most people forget to look first at triggers when something like this happens.


Questions about posting. See faq183-874

 

[rac2]

My sys admin mentioned restoring from the backup. It took about 30 minutes to copy the backup file from machine to machine and about 5 minutes to restore the database.

The ALTER TABLE using Enterprise Manager took 10 to 15 seconds, and it did seem to stop the application during a query on the table.

Next time insomnia strikes ( likely over the next three days ) I will repeat this remotely. That way I wont hear their screams. Actually the third shift will probably be dozing anyway.

Thanks to all for helping me think through this.