Indices y las mejoras del desempeño

10 de Septiembre (12 pm GMT -5)

Kenneth UreñaResúmen:En esta sesión vamos a ver la organización de los índices además de como ejecutar estrategias como index coverages e identificar cuales son las prácticas que nos evita la utilización de los índices.

Próximos Eventos

Configurando réplicas de Lectura de SQL-Server AlwaysOn24 de Septiembre

Carlos Rojas

Recuperación de desastres apartir de respaldos

1 de OctubreKenneth Ureña

Aprendamos DAX

17 de SeptiembreJosé Ahias López

Portillo

Está por comenzar:

Indices y las mejoras del desempeño

10 de Setiembre 2014

Kenneth UreñaMCSA| MCITP | MCTSPASS Spanish Virtual Chapter Lead

Agenda

El viaje de una transacciónIndicesBuenas practices de codificación

EL VIAJE DE UNA TRANSACCIÓN

Actual

vacio

VirtualLogFile

Truncado

- Aceso Secuencial- Solo 1 archive

activo- Recomendado

Raid 1 ó 1 + 0- Commit, backup

log trigger I/O

Log

File

- Acceso aleatorio- Multiple archivos activos- Recomendado Raid 1/1+0

5+0 / 5 - Checkpoint, backup,

cargar / descargar Cache

lleno vacio lleno vacio

vacio lleno vacio lleno

Data Files

Fallo de cache

Cargar pagina

Actualizar

pagina

CommitLazy Writter /

Checkpoint

INDEXACIÓN

Indexes and OrganizationTable structure

Image from

SQL server 2008 books online

Indexes and OrganizationHeap Table

Image from:SQL server 2008 books online

Indexes and OrganizationClustered Table

Image from:SQL server 2008 books online

Clustered (example)

Image from:http://www.sqlservercentral.com/images/4695.jpg

Indexes and OrganizationNonClustered Index

Image from:SQL server 2008 books online

Column Index

Image fromhttp://magenic.com/

Data access patters

SeekScanBookmark Lookup

Data access patters

DEMO

CODING BEST PRACTICES

Coding Best Practices

Maximize coverageAvoid conversions (implicit/explicit)Avoid row by row access (cursor pattern)Avoid unnecessary sortingChoose the right index for the expected data pattern access method

Q&A

Kenneth Ureña http://www.sqlcr.com @sqlcr

/SpanishPASSVC

18

Manténgase conectado a nosotros!

Visítenos en http://globalspanish.sqlpass.org

/SpanishPASSVC

lnkd.in/dtYBzev

/user/SpanishPASSVC

/SpanishPASSVC

PASS Community News

August 2014

Planning on attending PASS Summit 2014? Start saving today!

• The world’s largest gathering of SQL Server & BI professionals

• Take your SQL Server skills to the next level by learning from the world’s SQL Server experts, in 190+ technical sessions

• Over 5000 attendees, representing 2000 companies, from 52 countries, ready to network & learn

Ask your Chapter Leader how to save $150 off registration!

$1,895UNTIL SEPTEMBER

26, 2014

PASS Virtual Chapters Listing

Check out the sqlpass.org for more information on all the Virtual Chapters:

• Application Development• Big Data• Book Readers• Business Analytics• Business Intelligence• Cloud• Data Architecture• Database Administration• DBA Fundamentals• Excel BI• Healthcare• High Availability and Disaster Recovery• InMemory• Master Data\Data Quality

• Oracle SQL• Performance• Powershell • Professional

Development• Security• Virtualization• Women in Technology• Global Chinese• Global French• Global Hebrew• Global Italian• Global Portuguese• Global Russian• Global Spanish

Virtual Chapter Meetings – August

VIRTUAL CHAPTER

MEETING TOPIC

DBA Fundamentals

Aug 5 11:00– 12:00 CDT (UTC -05:00)

Rolling Upgraders, The Easy Way Presented by Argenis Fernandez

Global Spanish Aug 6 12:00-12:30 (UTC -05:00) Los Caso de Uso Para In-Memory OLTP Presented by Warner Chaves

Global Hebrew Aug 10 17:30-18:30 JDT (UTC +02:00)

Take Your SQL Server Development Process Forward! Presented by Itai Binyamin

Professional Development

Aug 12 12:00-13:00 EDT (UTC -04:00)

Bridging the Communication Gap Between DBA and Developer Presented by Robert Davis

High Availability and Disaster Recovery

Aug 12 12:00-13:00 CDT (UTC -05:00)

Mirroring Start to FinishPresented by Ryan Adams

Virtual Chapter Meetings – August

VIRTUAL CHAPTER

MEETING TOPIC

Global Spanish Aug 13 12:00-13:00 (UTC -05:00) Haciendo Preguntas Básicas en Power BIPresented by Andrei Garzon

Excel Business Intelligence

Aug 14 11:00-12:00 EDT (UTC-04:00)

Advanced Analytics: Excel SolverPresented by Dan Fylstra

Business Intelligence

Aug 14 11:00-12:00 EDT (UTC -04:00)

Continuous Integration for Reporting ServicesPresented by Nat Sundar

DBA Fundamentals Aug 19 11:00-12:00 CDT (UTC -05:00)

The Roadmap to Better Performance: Reading Query Plans Presented by Erin Stellato

Global Spanish Aug 20 12:00-12:30 (UTC -05:00) Fundamentos sobre lost indices end SQL Presented by Enrique Puig

Virtual Chapter Meetings – August

VIRTUAL CHAPTER

MEETING TOPIC

Global Russian Aug 20 15:00-16:30 (UTC +03:00)

SSIS - Повышение производительности обработки потоков данных

Global French Aug 21 20:00-21:00 RST (UTC +01:00)

Power BI Hybride avec la Passerelle de Gestion de Données Presented by Jean-Pierre Riehl

Business Analytics Aug 21 12:00-13:00 CDT (UTC -05:00)

Looking Through the Windshield: Creating forward-looking Dashboards and KPI’s Presented by Brian Larson

Business Intelligence

Aug 27 12:00-13:00 GMT (UTC-00:00)

Intro to RPresented by Stephanie Locke

Global Spanish Aug 27 12:00-13:00 (UTC-05:00) Visualización de Big Data Con Power ViewPresented by Eduardo Castro

Upcoming SQLSaturdays

Upcoming North America Events Upcoming International Events

• Aug 2 #324 Baton Rouge

• Aug 9 #304 Indianapolis

• Aug 23 #328 Birmingham

• Aug 23 #309 Oklahoma City

• Sep 6 #320 Raleigh

• Sep 13 #300 Kansas City

• Sep 20 #331 Denver

• Sept 20 # 340 San Diego

• Aug 30 #327 Johannesburg

• Aug 30 #317 Oslo

• Sep 6 #301 Cape Town

• Sep 13 # 323 Paris

• Sep 20 #290 Kiev

• Sep 20 #310 Dublin

• Sep 27 #325 Sao Paulo

Visit www.sqlsaturday.com to register for an event near you!

26

Volunteering with PASS

Volunteer Today!

Now it’s easier than ever to volunteer with PASS.

Let us know your volunteer preferences by updating the “myVolunteering” section of your

myPASS profile.

PASS would not exist without our passionate, dedicated, and hardworking volunteers from

around the globe.

27

Outstanding Volunteer Award

Do you know a volunteer that has gone above and beyond recently?

Nominate them for the monthly OVA.

VolunteerRecognition@sqlpass.org

Volunteering with PASS

PASSion Award

The highest of PASS accolades presented yearly to a volunteer. Nominations will open in August

2014.

Stay tuned!

Stay Involved!• Sign up for a free membership today at sqlpass.org

• Linked In: http://www.sqlpass.org/linkedin• Facebook: http://www.sqlpass.org/facebook• Twitter: @SQLPASS• PASS: http://www.sqlpass.org

Programa de Reconocimiento

Programa de Voluntario Sobresaliente

• PASS le invita a nominar a su voluntario favorito para ser “Voluntario Sobresaliente del Mes”

• Enviar nominaciones en todo momento a: VolunteerRecognition@sqlpass.org Favor proveer:

• Información de contacto del nominado,• una lista breve de los programas de PASS que a participado el nominado• los años que lleva activo en la comunidad• una corta descripción por el cual considera que esta persona debe ser reconocida

• Los nominados seleccionados serán anunciados en la edición del boletín PASS Connector y recibirán un certificado de apreciación.

Aprendamos DAX

17 de Septiembre (12 pm GMT -5)

José Ahias López PortilloResúmen:En esta sesión veremos los conceptos básicos de como programar consultas DAX y algunas de las funciones mas útiles que podrían salvarnos en un apocalipsis de generación de consultas complejas en modelos tabulares.

Próximo Evento