Course Information
Registration period
Year-round Recruitment
Course Level
Study Mode
Duration
1 Hour(s) 15 Minute(s)
Language
English
Taught by
Mike West
Rating
Course Overview
Real World Automated SQL Server Performance Tuning
Recent Reviews:
"Mike has given a great template that will allow DBAs to ease their admin tasks. Really well done." -- Doug Alderson
"This is a great tool to help DBAs and non- database system administrator for performance fine tuning" -- Bernard Bernard
Welcome to Building an Automated SQL Server Performance Tuning Engine.
In this course, we are going to build a completely automated application for tuning a SQL Server instance.
The application has been running on live production instances for several years. These SQL Servers have needed no performance tuning attention since the tool was installed.
I’ve created the software and named it SQL Black Box. I choose the name because for most in the organization the inner workings of the tool will be a black box.
The software in the course will be written in C# and the SQL Server code will obviously be transact-sql.
This is not a step by step on how I authored the code. This course assembles the sundry components that make up the application.
You’ll be able to take the application and install it on any SQL Server you want. You won’t be able to resale any portion of the code but that’s the only restriction.
Regardless of your skill level this application will ease your administrative burden.
Advanced students will be able to choose what modules they want to implement. I use the term module within the software to denote individual unit of work.
For example, there will be a module that removes exact duplicates and there will be a module that adds high impact indexes to the instance.
Please note I used the word instance and not databases. We don’t tune databases; we tune the instance they are on because all the critical resources are shared at that level.
Those students newer to SQL Server will be able to change a couple of lines of code, build the modules and then use task scheduler to automated their schedules.
Thank you for your interest in Building an Automated SQL Server Performance Tuning Engine and we will see you in the course.
Course Content
- 2 section(s)
- 20 lecture(s)
- Section 1 What is our Goal?
- Section 2 Building The Engine
What You’ll Learn
- At the end of the course you'll be able to take what you've learned and the code I've provided to completely automate tuning a production SQL Server instance. , The students will have a solid understanding of how the process of performance tuning at the instance level works.
Skills covered in this course
Reviews
-
SShane Darosh
There was a lot of information I didn't know and the tools provided will be helpful.
-
AAnonymized User
it was very good session.I got many idea from this session to improve sql performance
-
JJacob Andrew Wilkins
This maintenance/tuning solution is reasonable, and could prove especially helpful to shops that rely on SQL Server but don't have an experienced SQL Server DBA. The idea of using task scheduler instead of the SQL Server Agent for these tasks especially has some nice advantages (like running from a server without SQL Server installed at all and being more easily integrated with things like Azure SQL Database, which doesn't have an Agent, in addition to the advantages mentioned in the course). One point of caution, unless you take extra precautions, the connection strings stored in the compiled C# executables are not particularly secure, so access to those files should be especially restricted. Also, it's worth pointing out that the missing index DMVs do not account for the overhead to writes from the recommended indexes. For write-intensive workloads, even indexes shown by the DMVs to be very beneficial can actually be a substantial net negative. Fully working out this balance requires a more nuanced analysis. While it is important to be aware of this balancing act, in the most likely use cases for this solution (a shop without a very experienced SQL Server DBA), this solution may well be better than the available alternatives. Overall this course and solution is worth the time to investigate, although I would prefer the addition of some caveats like the above.
-
TThato Mekwa
I couldn't get the vs solution working on my machine, but its a reference problem. I will have to look at this in the future. Really an awesome skill.
