Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

SQL Server newbie here. I'm a MySQL guy. I'm having a look at something for a client in their 2008 SQL Server, and need some advice. Whoever designed the database chose to log insane amounts of stuff and never flush those log tables.

The largest table stores complete XML documents from transactions between the app and APIs of sites like eBay. I can only assume that the database being about 230 gigabytes hurts performance. I'm guessing these tables are not queried on in the app, but even so, I don't like the idea of such a huge database. After purging the log tables, I would anticipate a total remaining size of about 30GB.

I'd like some advice on how to go about this. From the little I've read on the subject, after deleting a bunch of data, the database file will not automatically shrink in size. I also read that shrinking and re-indexing are bad.

  • Is this large database hurting performance of other tables?
  • Should I do something about it?
  • How can I safely do something about it that will yield a performance increase?
share|improve this question
    
230G isn't "massive" by today's standards. What actual performance problems do you have? Are you 100% percent certain that these logs can be removed? (There are a lot of scenarios with legal obligations regarding all sorts of logs.) –  Mat Dec 20 '12 at 11:15
    
I am not certain yet. But I'm under the impression that it's hurting performance so I wanted to talk to the client about the possibility of purging the logs. I know with mysql and the innodb buffer pool, the database will choose what to cache in ram. I'm assuming sql server works in the same way. I can imagine that keeping the total size down would possibly allow more data to make it into the cache. Of course it will also ease backups. The logs just seem totally unnecessary to me and a complete waste of space. –  Brett Dec 20 '12 at 13:34
    
The reason I'm looking at the database is because we are looking into why this server is performing so poorly and what can be done fairly easily to fix it. The server is decent - a Dell PE R710. I feel that the drive configuration is a major bottleneck - 4 15k sas drives in a raid 5 config. Reformatting with ssd's wouldn't really be feasible. It needs to be an upgrade-in-place deal. We were looking into setting up cachecade on the lsi-based perc, but dell's implementation is pretty crippled in that they only support a dell ssd and only read caching even though the controller is battery backed. –  Brett Dec 20 '12 at 13:38
    
So I think we still may do the cachecade setup, but I was also thinking if the database wasn't so huge, I could stick in two 128mb or 256mb ssd's in a raid 1 and just put the database on it, and make sure any temporary tables and whatnot are created on that array as well. I figure that would significantly speed up db access. During working hours, we saw this server have a disk queue length of 1000, when it shouldn't really be more than 1 or 2. This giant database is trashing the disks and the performance of the application is suffering greatly. If anyone has any input please let me know. –  Brett Dec 20 '12 at 13:43
    
How much memory does the server have? –  Mark Storey-Smith Dec 20 '12 at 13:47

3 Answers 3

up vote 10 down vote accepted

If you are going to continue logging data in this database, the last thing on earth you want to do is shrink the database file (and then perform index maintenance that will require it to grow again). Never mind that these shrink and grow operations will interfere with performance, and that the end result is not going to leave you much better off than when you started.

Since the file is only going to grow again, this is an extremely futile operation - much like drying off while still in the shower. What are you going to do with all that disk space you free up temporarily? Lease it out to another application until the database needs to grow again? Of course not. If the database grew to that size once, it will grow to that size again, but it is going to be much more efficient to reuse the space within the file without all this unnecessary shrink-grow-shrink-grow roller coaster of the file itself.

Even if you move the logging table to another database, you should do what you can to pre-allocate the data file to a size that will accommodate the amount of logging you want to save (be it a week, a month, what have you). Keep this database trim by purging data every day, and stop worrying about shrinking & re-indexing. If you size it right, there should always be some free space, but not excessive free space. And if you need to re-index (you shouldn't really, if your clustered index is datetime or otherwise monotonically-based), do so after the purge (when you have the most amount of free space), not after a shrink (when you have the least).

You can do what Mark suggests without introducing a new database to the application, or changing the application or its interface to the database at all (one important change, of course, would be the removal of any foreign keys or other database-dependent features). You can simply create your table(s) in the new database, then add an INSTEAD OF INSERT trigger to the table in the current database (I assume there are no updates to the logging table, but you may also need an INSTEAD OF DELETE trigger in case you don't directly control the process that performs the purging). That will help with writes, but you'll have to point reads elsewhere, since there are no INSTEAD OF SELECT triggers. Another alternative would be to rename the existing table and create a synonym or even a view that points to the new table.


If you're going to need to clean up the logs table that has grown, I would avoid a single atomic transaction like:

DELETE dbo.logs_table WHERE [datetime] < '20121201';

This will cause massive log growth and will take a long time. Instead you can break the cleanup into chunks, e.g.

BEGIN TRANSACTION;

SELECT 1;

WHILE @@ROWCOUNT > 0
BEGIN
  COMMIT TRANSACTION;

  -- if in simple: CHECKPOINT
  -- otherwise: BACKUP LOG

  BEGIN TRANSACTION;

  DELETE TOP (1000) FROM dbo.logs_table WHERE [datetime] < '20121201';
END

I picked 1000 and Dec. 1st arbitrarily, I don't know what fits your scenario best. The point is that you want to keep transactions short and contained, and prevent any long-term impact while you clean up the table. Another option I've used in the past, instead of deleting 99% of the junk in a table, move the 1% you want to keep to a new table and drop the old one.

BEGIN TRANSACTION;

SELECT * 
  INTO dbo.new_logs_table 
  FROM dbo.logs_table
  WHERE [datetime] >= '20121201'

COMMIT TRANSACTION;

-- create indexes/constraints/triggers on dbo.new_logs_table

BEGIN TRANSACTION;
  DROP TABLE dbo.logs_table;
  EXEC sp_rename N'dbo.new_logs_table', N'logs_table', N'OBJECT';
COMMIT TRANSACTION;

If you say the logs have never been purged, then you may very well be in a scenario where the database will be at a size that you don't anticipate you will ever need again (say, if you only intend to ever keep a week of logs at a time). In this case I might go along with a shrink operation, but only if truly necessary (e.g. you really do need the space for other purposes). A bunch of empty pages aren't going to affect your backups or other operations, and the pages will eventually be fully deallocated and reused.

share|improve this answer
    
I like the idea of setting it up so the logging data goes to the mechanical drives, and the essential data stays on an ssd array. I agree with you on shrinking being dumb. Im used to my table_per_file setup with mysql where it's much easier to regain space. BUT i need to do something because I'm sure the client does not want to pay a premium for ssd space that is going to be taken up by useless logs. Unfortunately it looks like the application developer never purged any logs. –  Brett Dec 20 '12 at 15:11
    
I wouldn't try a single operation, like "DELETE logs_table WHERE date < yesterday." I'll add a better alternative to the answer. –  Aaron Bertrand Dec 20 '12 at 15:17
    
Great stuff. Thanks for the examples. Would the synonym or view you spoke about redirect inserts/updates/deletes/selects to the alternate database? I know in mysql you can have an updatable/insertable view. –  Brett Dec 20 '12 at 15:38
    
Also, what would be the best way to go about purging old logs? In mysql/linux i would just setup a cron job. Would i schedule a task in windows or is there something built into sql server? Probably wouldn't be too efficient to run on a trigger anytime a new record is inserted. Some kind of batch operation every day may be the best. –  Brett Dec 20 '12 at 15:40
    
@Brett SQL Agent job. –  Mark Storey-Smith Dec 20 '12 at 15:44

The largest table stores complete XML documents from transactions between the app and APIs of sites like eBay.

I'm working with a system at the moment that does exactly the same. While Mat's comment about 230GB not being massive by today's standards is fair, it's still 200GB more than you need for the system to operate. It may be occupying buffer pool, it's certainly contributing to larger and longer backups and of primary concern, it will require longer restore time in the event of a disaster.

Preferable (if the application code is accessible and in a fit state to tolerate change) would be to push any non-critical logging to a different database. You can then switch it to SIMPLE recovery and forego transaction log backups. Obviously this is only suitable for data where loss of point-in-time recovery is deemed acceptable.

In the case I'm looking at, the API request/response documents that are being logged are in effect duplicate data. The transaction details are being stored in the database elsewhere, the documents are for debug purposes only.

Alternately, purge the data more frequently.

But... If your assessment is correct and the data of interest is 30GB, the 32GB server is dedicated to this database, that should be adequate. I'm inclined to suggest you do some more in depth analysis of where your problem is originating from, rather than leap straight into trying to resolve the logging/size issue prematurely.

Crack out the DMV queries. Glen Berry's DMV Diagnostic scripts contains examples for identifying the procedures and ad hoc queries that are consuming the most IO or CPU time. sp_whoisactive is very useful for live analysis.

If you find anything of interest, feel free to post a new question with the details.

share|improve this answer

Well the performance is affected from fragmented database then it is taking large time to give information because fragmentation database has not organize page in proper way.

You needs defragmentation to increase performance of database.

Because it will rearrange the page order of table logically and physically for easy use and reduces size

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.