Udemy
    •  
    •  
    •  
    •  
    •  
    •  
    •  
    •  
Subscribe
Turn what you know into an opportunity and reach millions around the world.
Learn More
Your cart is empty.
Keep shopping
SQL Server Administration Part 2
Rating: 4.7 out of 5(3,591 ratings)
19,386 students

SQL Server Administration Part 2

Learn how to optimize, improve performance, review and analysis statistics, use monitoring tools, and create indexes
Created byRaphael Asghar
Last updated 8/2018
English

What you'll learn

  • At the end of SQL Administration Part 2, the student will be able to create indexes for to gain performance, use the SQL Profiler to find and trouble shoot expensive queries, use the activity monitor for analysis, deploy the performance monitor to find specific about SQL Server internals, use DTA utility, create and investigate issues with DMVs and extended events, look at SQL logs, monitor the health of the SQL Server more efficiently and much more.

Course content

1 section • 49 lectures • 10h 12m total length

  • Introduction6:28
  • Install SQL Server 21414:40

    Understand what an index is and its types. Create indexes with SQL and GUI; know when not to use them; tune with SQL Profiler, Database Tuning Advisor, and monitor fragmentation.

  • Install SQL Suite11:20
  • POST ADDENDUM8:11
  • APPS NEEDED7:34

    Acquire and organize six essential tools for a production SQL database setup: VirtualBox with extension pack, Windows Server 2016 ISO, SQL Server, SSMS, AdventureWorks database, and Visual Studio.

  • CREATE VM18:22
  • INSTALL SQL SERVER 20179:43

    Install sql server 2017 in a virtual machine, set up a shared folder, and run setup to install the evaluation edition with defaults. Then install ssms for management.

  • ADVENTUREWORKS 2017 DB8:13

    Install SQL Server and SSMS, then restore the AdventureWorks 2017 database in a virtualized environment, connect to SQL Server, and compare interfaces across versions.

  • COMPARISON SQL SERVER 2014 VS 20172:38

    Compare SQL Server 2014 and 2017, showing similar front-end interfaces and how scripts from prior Learn SQL courses apply to 2017, with guidance to update AdventureWorks references to 2017.

  • Clustered Index16:59
  • Create non cluster index12:07
  • Composite Index18:06

    Explore how to create a composite index using two or more columns, like last name and first name, and learn the benefits for faster data retrieval.

  • Use or not use indexes11:20
  • Deciding factors in creating indexes20:09

    Explore when to create indexes by balancing table size, usage frequency, and where clause patterns, and learn how single, composite, clustered, and non-clustered indexes affect testing, cardinality, and performance.

  • using sql profler for indexes19:41

    Learn to use SQL profiler traces to identify indexing needs and analyze queries. Apply the database engine tuning advisor to recommend cluster and non-clustered indexes and validate improvements.

  • Reorganize and Rebuild Indexes15:47

    Learn how to manage index fragmentation in SQL Server by choosing reorganize or rebuild based on fragmentation thresholds of 5–30%, and apply online or offline maintenance techniques.

  • Into To SQL Profiler17:57

    Learn to use SQL profiler to monitor SQL Server events, capture statements and stored procedures, and save traces to files or tables for performance analysis and auditing.

  • SQL Profiler longest running query13:37
  • Audit logins12:11
  • SQL Profile with Index Tuning10:40
  • SQL Profiler Best Practice5:24
  • SQL Server Side Trace16:31
  • Database Engine Tuning Advisor12:10
  • Statistics11:44
  • Activity Monitor14:01

    Learn to monitor SQL Server performance with Activity Monitor and database management views to identify bottlenecks, view expensive queries, and terminate problematic processes.

  • SQL Performance Monitor16:24

    Leverage the Windows Performance Monitor (PerfMon) to capture SQL Server metrics, create baselines, and monitor memory, processor, disk, and network counters for performance health.

  • SQL Performance hardware issues12:49
  • SQL Performance Monitoring Hardware CPU16:47

    Identify CPU performance indicators and establish baselines using performance counters, monitor processor time and processor queue length, and tune queries and indexing before hardware upgrades.

  • SQL Performance Monitoring Hardware memory10:10
  • SQL Performance Monitoring Hardware disks8:46

    Monitor SQL Server disk performance with Performance Monitor, focusing on average seconds per read and average seconds per write to detect bottlenecks across physical and total disks.

  • 23 SQL Perfmon scehdule8:51
  • DMV13:53

    Learn to use dynamic management views and functions to monitor SQL Server health, diagnose performance issues, and tune performance, with DMVs categorized by group and database or server scope.

  • Block and Locks13:15
  • BLOCKING10:45
  • Deadlock4:45

    Learn to capture and view deadlocks graphically with SQL Profiler, configure deadlock graph events, trigger a deadlock with two transactions, and identify the victim SPID and lock modes.

  • Extended Events18:20

    Discover extended events in SQL Server, a GUI-based tool that replaces SQL Profiler. Create sessions and packages to monitor long running queries, blocking, and memory with minimal overhead.

  • Extended Events via TSQL11:59
  • Database Snapshots15:12
  • Import Export Data10:01
  • BCP12:59
  • Partition19:51

    Explore table partitioning to divide large tables into smaller file groups, map data with a partition function and scheme, and improve performance, archiving, and maintenance.

  • Partition part 219:51
  • Partition 312:46

    Demonstrate partitioning a table using a GUI, create and map file groups on drives D and E, and define a partition function by year 2005–2007 for a production table.

  • Contained datababase Part 111:47

    Explore contained databases, self contained with their own configurations and metadata, enabling movement without SQL logins, and note partially contained limitations like no replication or change data capture.

  • Contained datababase Part 210:12

    Explore contained databases in SQL Server, preventing orphaned logins by migrating data with partial containment, creating users within the database, and securely connecting to the contained database only.

  • Policy Based Management18:46
  • Linked Servers8:20

    Learn to create and use linked servers to access data from another SQL Server or other platforms via ODBC, enabling cross-server queries for reporting and production databases.

  • SQL server and agent logs5:24
  • SQL ADMINISTRATION PART 35:32

Requirements

  • All the student needs is a laptop or a desktop. All material where appropriate will be downloaded via internet. Plus, you will need as a pre-requisite SQL Administration Part 1. All scripts and documentation will be provided by me to the student via zip file.

Description

SQL Administration Part 2 will benefit the student by providing the necessary guide lines for enhancing SQL Server performance by utilizing indexes, reviewing DMVs for analysis, and setting up performance counters for gathering resource metrics; and also I will explain a handful of utilities to gather information about SQL Server internals, such as SQL Profiler, Database Tuning Advisor (DTA), Extended Events, SQL performance monitor, Activity monitor, and teach about partitions, creating contained database for recovery, reviewing SQL server logs and by installing SQL Server 2014 as the newer SQL version.The student will not only add to the knowledge gained bur SQL Administration Part 1, but get prepared for the next course SQL Administration Part 3 – the high availability solutions.

Who this course is for:

  • Student who are interested in managing SQL Server, Developers, IT Admins, System Admins and anyone who is responsible for the databases.

Report abuse