1 / 16

Utility Database

Utility Database. Chris Shaw @ SQLShaw Chris@SQLShaw.com. In case you wanted to know…. Microsoft SQL Server MVP Spent most of the last 15 years administering SQL Server http ://chrisshaw.wordpress.com / Chris@SQLShaw.com. Session Notes. Growing presentation Feedback is welcome

mele
Download Presentation

Utility Database

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Utility Database Chris Shaw @SQLShaw Chris@SQLShaw.com

  2. In case you wanted to know… • Microsoft SQL Server MVP • Spent most of the last 15 years administering SQL Server • http://chrisshaw.wordpress.com/ • Chris@SQLShaw.com

  3. Session Notes • Growing presentation • Feedback is welcome • The Demo’s in your environment • This is just the starting point • Demo is designed to show you potential options - customize • Ask Questions • Feel free to email me • My environment • SQL Server Denali • Local Install • No Reporting Services on local install • Quiz Time • Is my impression accurate

  4. Quiz Time • Do you have Daily Tasks • Check jobs • Check disk space • Read error Logs • Validate performance • Processors • Disk • Procedure Execution • Checking Indexes • Documenting growth • How do you do it?

  5. What is a Utility Database? • A user defined, created database • Keeps information on your server about your server • Keeps the information you want as long as you want it • Presents information you want the way you want to see it • This is your database • Acts as a repository for you the DBA

  6. Is this your Daily To do?

  7. Why use a Utility Database? • Centralize your information • Forecast your usage • Ease your daily tasks • Automation • See potential issues before they happen • Understanding the baseline • Be one step ahead • One View

  8. Focus Your Efforts

  9. My Report • Delivered by email every day • My daily check list • Mixed counter information • Hardware information • Processor Utilization • Disk Space Free • SQL Server information • Error logs • Failed Jobs • Report run times • Business information • Performance Indicators • Identity Checks

  10. Demo • Building your database

  11. Approach Styles • With a table between the query and the DayCheckList Table • DBCC FreeProcCache • Using CLR • Why all the nested procedures • When to archive the data

  12. Demo • Building Your Report

  13. What’s Next • Thresholds • Alerts • Auto Re-index • Ola Hallengren (http://ola.hallengren.com/) • DMV’s • Glenn Berry (http://sqlserverperformance.wordpress.com/) • Other than SQL Server • WMI • Don’t reinvent the wheel

  14. Go Global • Start small • Add one at a time, see the use and then make a list of what items you need long term • Change the Schema • Add servers • SSIS • Linked Servers • Options • Data Collector • 3rd party tools • Report Server • ExecutionLog2

  15. Summary • Utility databases answer questions like: • What procedure performs the worst? • Is the performance getting better or worse? • What is the percentage of growth on my databases? • What errors have been registered in the error log? • What jobs failed last night? • Can the database support the work load you are asking it to. • When will you need to upgrade

  16. Questions Thank you, your feedback is welcome Chris@sqlshaw.com

More Related