The at the end of this course you will know how use Microsoft SQL Server 2016 with Very Large Databases, how its been improved from earlier version and get years of experience in this short course.
A quick history of some important trace flags.
Creating database from the beginning, as per best practice, we will do this with more than one file group and enable the auto grow all files.
Continuation of database creation, enabling the database settings to make use of T1117 and T1118 behavior.
Section 1 knowledge quiz
Theory of memory and compression.
Uncompressed information vs Row and Page level compression and why that is helpful.
Server Memory Management.
Row and page level compression and how to enable them.
In memory tables.
Section 2 knowledge quiz
Altering an existing database, to add file groups and move data to the new file groups.
Making use of the DROP_EXISTING = ON statement
Creating and using Mount Points and how they are useful given limited driver lettering in Windows.
Remember that you only have A-z options for drive letters and at least one of those is already used by system disk C.
Why to use a Partition on a table.
What is improved by partitioning a table and what is not improved. For example UPDATE, INSERT and DELETE however read only quires do not improve in more case.
How to Partitioning table by date and why we need to think about Indexes when we do this.
Remember that indexes are created on tables and when tables are changed the Index will need to be changed or recreated as well.
How to Partitioning table by name or range so that you can see how else this could be done depending on data types in your table.
Section 3 knowledge quiz
How to create server side trace script from SQL profiler
Running the profiler script and then using the profiler trace to improve the database using database engine turning.
If you want to capture windows performance counters to compare with SQL Server Trace files I recommend setting the ones below. A complete list can be found on Microsoft site.
The scripts used during the course.
Section 4 knowledge quiz
John Hall is an IT professional with knowledge of Linux, Windows and Networking.
Spend large part of his career working for blue chip multinationals developing Private and Public cloud solutions for applications such as Sharepoint, SAP, J2EE and PaaS.
Working in IT since 1999 he is an Microsoft Certified IT Professional in SQL Server and Windows Server however most of his time has been spend hunting down performance issue and improving overall customer service.