Care and Feeding of your MIIS SQL Server Database

So far the best presentation of the conference *sorry Brad
Highlights:
Clear out the run history of all your jobs. This can really impact your jobs if you let it get cluttered

Spedometer - Use PerfMon
MIIS Objects
For Imports Objects Read/Sec
Objects Synchronized/Sec
For Exports Objects Exported/Sec

But wait, there's a script: http://www.ilmbestpractices.com/Articles/Speed_of_Past_Runs

as tables grow - Page splits, fragmentation, more scans than seeks, growth of log and data files.

monitor with Page Splits/Sec in perf mon how do we avoid it

scan v seek you get more scans as get more fragmented indices

read entire tables versus using an index

monitor with PerfMon

Full Scans/Sec
Auto Create/Update stats are enabled

File Growth
if auto growth is enabled
clear run history script on website

http://www.ilmbestpractices.com

DBCC SHRINKDB('MicrosoftIdentityIntegrationServer', NO TRUNCATE)
maybe preset size if you can predict
Log file should be 1/4 of total data file
Transaction Log is key to performance as well


Recovery Models
Full - LOG EVERYTHING
Simple - only as good as last full or diff
Bulk Logged hardly ever used with MIIS - only good for bulk logged operations

Red Gate SQL Backup to compress backup files (compression eats CPU time but far less Disk IO

When you clear run history it will host your transaction log. Do this during a maintenance window and
a. Truncate log
b. Change to SImple Recovery Mode
c. Clear run history in small chunks
d. Then go to Full Recovery mode and Perform a Full Backup

you can also add some indices to better performance

indexdefrag on DB every month, index rebuild quarterly till SQL 2005 EE. With SQL2005EE we get to rebuild index with DB online as well as rebuild index in TempDB
http://www.sqlbestpractices.com