SQL Server From Darkness To Light TIPS THAT

SQL Server: From Darkness To Light TIPS THAT WILL HELP YOU TO LIGHT YOUR PATH WHEN USE SQL SERVER, WHETHER YOU'RE DEVELOPER, SYSADMIN OR DBA.

Presenter • Victor Hugo Barajas Sosa • Sr. SQL Server DBA / Team Leader – HCL Technologies • Began as a. NET/SQL Developer Trainee on 2008 where I was in touch for the first time with SQL Server engine, I never was a fan of any RDBMS but everybody (including me) were always blaming the database as the reason of all our problems, therefore I decided to specialize on this technology until the point where I truly love it. • Contact Victor: • • Blog - https: //wisesql. com Twitter - @Voss. B • Certifications: MCTS: SQL Server 2008, implementation and maintenance. • MCSA: SQL Server 2012/2014. •

Agenda • Introduction • sp_Who. Is. Active • Wait Statistics • Index Basics • Instant File Initialization • Tempdb contention • Make backups faster • Q&A

Introduction • Why did I make this conference? • It took some years for me to discover these tips when I started. • I’ve interviewed many candidates for DBA positions and most of them don’t know these topics. Even candidates applying for Sr. positions. • I made this conference thinking in my “old me”, and how I would’ve liked somebody to talk to me about these topics. • Many recognized SQL Server Consultants recommend these tips.

sp_Who. Is. Active • Created by Adam Machanic, Boston-based SQL Server developer. (http: //sqlblog. com/blogs/adam_machanic/default. aspx) • sp_who/sp_who 2/DMVs combined as the best (personal opinion) lightweight free monitoring tool. • Helps with the following: • What’s currently running? • Why is SQL Server/query slow? • Important session information

sp_Who. Is. Active Demo

Wait Statistics • SQL Server’s method to keep record of why a query (thread) has to wait. • Understand Query Processing Resource Wait Time Signal Wait Time Total Wait Time SUSPENDED RUNNABLE RUNNING

Wait Statistics Demo

Index Basics • Heaps • Clustered Index • Non-Clustered Index • Index Keys and Included columns • Order of the key columns is very important • Include columns to avoid Key/RID Lookups • More types of indexes, we won’t cover those on this session

Index Demo

Instant File Initialization (1) • Very important setting to improve performance • Faster Data file allocations (CREATE and ALTER file) due to it allows a file to be created or grown ‘instantly’ by not having to zero the space in the file • Helps performance for CREATE DATABASE, ALTER DATABASE, RESTORE, and AUTOGROW • Does not work for Log File allocations • Small security risk

Instant File Initialization (2) • SQL Server 2016 makes it easy to enable IFI when SQL Server is installed

Instant File Initialization Demo

Tempdb Contention (1) • Many companies never change the Default tempdb Configuration when they install SQL Server • Many DBAs don’t know how to identify they have issues with tempdb • Small changes on configuration can make huge performance improvement

Tempdb Contention (2) • SQL Server 2016 makes tempdb default configuration better and easier to set since the installation setup

Tempdb Contention Demo

Make backups faster • Why? ? ? • We may have small time window for maintenance (Integrity Checks, Index rebuild/reorganize, backups) • Situation where TLog is full and database is inaccessible until we clear the log

Make backups faster Demo

Q&A

Thank You!!! Blog - https: //wisesql. com Twitter - @Voss. B