This is my first question here, so I would love help to make it work for SO:DBA.
I inherited an older relational system that is sitting on a SQL Server 2005 box and the database is at SQL2000 compatibility. In my initial inspection, this system is loaded with cursor functions and very cumbersome code (including *= joins). I have a feeling that this is because every table has one primary key column that is non-identity, and there are no PK-FK relationships built into any but two tables. This column happens to be the same value, DocID. There are few if any indexes outside the clustered index on the "PK" that all tables have. I am finding 2nd and even 1st normalized tables in several locations. There is no data dictionary that anyone knows of and the original builders of the system are now long gone, the company that built this system has been turned over 3-4 times and does not support this system anymore. There is only the one box, and a "test" environment is another database structure on the same server, and I do not have the ability to create a secondary server to test/build a solution. The last little wrinkle is that my company has decided to replace the system, and if nothing else, I will need to extract a great deal of baseline data from this system for import into the new system within the next 6-8 months, if nothing else, to leave the old system.
Currently, there are issues with severe slowdowns and from I/O requests and long lock conditions due to connection conditions and several massive stored procedures that touch most if not all the tables in rapid succession. That is what I am tasked to eliminate. I have already begun a manual mapping of the system's architecture from within SQL Server.
I have not experienced a database with this degree of brokenness. I come from the developer's side of the room, so I know how to write/refactor/update queries in set notation, but I am struggling with why the system was built without using any kind of relationship outside of query WHERE logic and such a huge number of cursor based update and inserts. (I suppose the why is moot at this point, but it would be nice to attribute the pain of this process to some “good” reason.)
What can I do to establish the relationships so that the DBMS can actually use its power to handle the requests that currently bring the system to a crawl 3-4 times a day? Would using the SQL Server 2005 Relational Diagram tool be useful for building the relationships and examining the index potential for the columns in each table? If I use the tool, will the relationship changes be done without "breaking" the legacy processes? For example, will the stored procedures with cursors still function correctly, if I re-key and index the table they are working on without adapting the Stored Procedure?
Best Answer
I would not make modifications like adding primary/unique or foreign key constraints to a legacy database that you haven't built yourself. Chances are that the original developer, given the problems and design issues you mention, may have built logic that breaks with such constraints.
For instance, if the app uses a 0 in a key column instead of NULL to represent "nothing", a foreign key constraint would fail that UPDATE/INSERT, even if the value is just used temporarily in a procedure.
The best place to start is probably to identify specific queries that show performance issues or cause locking, and deal with those. Just adding proper indexes on strategic tables may prove a huge improvement for you.
As for cursors.. some devs just don't know better. :)