Transcript Slide 1
You Inherited a Database Now What? What you should immediately check and start monitoring for. Tim Radney, Senior DBA for a top 40 US Bank President of “Columbus GA SQL Users Group” Backups Do you have recent backups? • Is the current backup strategy adequate for the SLA of the system • How often is it being backed up, can you do a point in time recovery? • Do you have the right recovery model? • Is there a plan in place to validate the backups • When is the last time a test recovery has been performed? Consistency Checks Do you have corruption in your newly acquired databases? • Is there a scheduled task/job to run DBCC CHECKDB • It is debatable how often this should be ran. Some say daily and others say weekly, as with everything SQL Server related “IT DEPENDS”, in this case, how much data loss can you handle? DBCC CHECKDB DBCC CHECKALLOC DBCC CHECKFILEGROUP Statistics Are your statistics up to date? • Are your statistics up to date • sp_updatestats • Is there a job to manually update stats? • Is “Auto Update Statistics” turned on? • Auto Update Statistics typically update after 20% of the rows change • Impact of Statistics to the Query Optimizer • The Query Optimizer uses statistics to build the execution plan. Without current statistics you are pretty much guaranteed to NOT have the “good enough” execution plan. Auto Grow / Auto Shrink How do you control your database size? • Do you allow your databases to auto grow in size? I DO. • Over the years I have seen most defaults to auto grow by 1MB or 10%. Both are bad in my opinion. • Try setting auto grow by a fixed MB depending on the system. • Make sure you have adequate free space in the file to handle traditional growth. • When a database file or TLOG is grows, transactions stop until the growth completes. • Do you allow your database to auto shrink? I DO NOT • Shrinking a database is a very bad practice. • Are there times when shrinking is necessary? Sure • Shrinking a database can lead to fragmentation. Index Rebuilds Do you have Fragmented Indexes? • What causes fragmentation • Data modifications (Insert, Delete, Updates). • Why should you care about fragmented indexes • A whitepaper from Microsoft stated fragmentation can slow down systems from 13% to 460% based on the size of the environment and fragmentation level. • How do you fix this • • • • • You can rebuild, reorganize, or drop and recreate your indexes. Can schedule rebuilds using a maintenance plan (ok for small db’s). You can use a custom script and run it in a SQL Job. You can use third party tools. Check out www.sqlfool.com or ola.hallengren.com. • Duplicate Indexes • Indexes that are exact duplicates are bad and should be removed. • Lots of scripts and apps to detect duplicate indexes out there Ownerships Who owns what? • Who is the DBO of your databases • When these databases were restored or created, did the previous DBA let it default to their user id? Check out sp_helpdb • Who owns your jobs • Did the previous DBA create the jobs and let it default to their user id? • Who else touches your databases • Is there any documentation on what other processes use your database? Any SQL Jobs/SSIS/DTS? Who owns those jobs? database can lead to fragmentation. Security • Who has access to your databases • Who has access to your database and why? Who is DBO and why? • Who has Sys Admins rights • Regardless if you are responsible for the database only or the entire instance who has SA rights and why? Check out master.sys.syslogins • Who has Security Admin rights • Regardless if you are responsible for the database only or the entire instance who has Security Admin rights and why? These users can add themselves to the SA group and back out. Near Future You have checked the quick stuff, now what? • Long Running Queries • Start monitoring for long running queries. sys.dm_exec_query_stats • Wait Stats • Start monitoring and recording wait stats. sys.dm_os_wait_stats • Disk I/0 • Check for file placement for your data files, tlog, tempdb, backups. • Are your disk configured properly? • sys.dm_io_virtual_file_stats • Compatibility Level • Were these databases upgraded? Was the compatibility level updated? - sp_helpdb • Recovery Plan • You have backups, you can restore the backup, but what is your “Recovery Plan”? • What would you do in the event of a failure? Instance Related Items Regardless if you own the server or just a database check? • TempDB Contention • Is TempDB setup to best practice? Sized right, multiple files on high speed disk, simple mode, etc? • MSDB History • Are you purging backup history? • Security Updates • Are security updates being applied to the server? • Is SQL Patched? • Startup Parameters • Check and document any startup parameters. • Alerts and Monitoring • Review any alerts configured on the server, create any that you need. Does your environment have any monitoring tools, are they setup correctly for this server? How To Find Me • Blog • http://www.timradney.com • Twitter • http://www.twitter.com/tradney • LinkedIn • http://www.linkedin.com/in/tradney • Facebook • http://www.facebook.com/tradney • Email • [email protected]