This customer had a very large PW (450GB) consisting of only Indepth/SQL and Indepth/Sybase data. I was asked to check if some of the data can be purged without a major lost of functionality.
First, beside the space requirements (which is also $$$) what are the problems with such a large PW:
- Backup of the PW database is time consuming
- PW loads are very slow. For example, “SQL Server Performance Load” took an average of 12 minutes to complete. That is, you almost always have PW loads running, viewing data in the UI is delayed and there are many locks while loading the data.
- UI requests are very slow and some will always fail with error. For example, Indepth/SQL Dashboard took more than a minute to complete.
- Foresight, reports are always running. If you install Indepth/SQL against the PW instance, you will see foresight statements that take 100% cpu and run for days (I have also worked on an Oracle PW with similar symptoms).
I have started my involvement with a day of webex--studying the PW. I was then able to identify why the PW size is so big, which objects are the top X objects with regard to space, what part of I3 performance is not accepatable, etc.
Here is the output of sp_spaceused when I started:
database_name database_size unallocated space
----------- ------------------ ------------------
I3 646511.56 MB 165313.41 MB
reserved data index_size unused
------------------ ------------------ ------------
451776344 KB 285716928 KB 163437072 KB 2622344 KB
I have recommended that the customer change some of the PW filter thresholds parameters and that we will manually purge data from the PW. The purged data is data that should have not been loaded if the recommended new thresholds were in place when I3 was installed.
The actual purge took several days and was performed in several webexes. That is, I have created some scripts and executed them from Query Analyzer. I checked the status every day, and submitted additional scripts. This was done with minimal I3 downtime possible. The end-results can be seen in the output of sp_spaceused when the project was completed:
database_name database_size unallocated space
----------- ------------------ ------------------
I3 197786.56 MB 28450.45 MB
reserved data index_size unused
------------------ ------------------ -----------
153105648 KB 88927552 KB 60314912 KB 3863184 KB
This is a 300GB saving! All the problems described above were resolved.