Curso a distancia: SQL Server performance tuning

Standard

Eventribe_tuning

En junio no te pierdas hacer este curso online sobre performance & tuning en SQL Server.

En el mismo mostrare:

·         Conceptos generales de performance

·         Como se interpretan los planes de ejecución (Query Plans)

·         Diseño de tablas OLTP optimas

o   Performance y tipos de datos (char vs varchar, Unicode, int vs bigint, etc)

o   Modelado y performance.

o   Como guardar datos grandes (imágenes, documentos, etc.) de forma performante.

o   Claves artificiales vs claves naturales.

o   Uso de GUID y performance.

·         Índices

o   Funcionamiento de índices en SQL Server.

o   Índices clustered y performance

o   Cómo funcionan los índices non-clustered.

o   Índices cover.

o   Diseñando índices eficientes.

o   Consideraciones en las consultas para usar los índices de forma óptima.

o   Fragmentación e impacto en la performance.

o   Índices avanzados

§  XML

§  FulltextSearch

§  ColumnStore index (SQL 2012 / 2014 o 2016)

§  Clustered column Store index (SQL 2014 o 2016)

§  Vistas indexadas

·         Estadísticas

o   Estadísticas de índices y su impacto en la performance

o   Estadísticas de columna y su impacto en la performance.

 

·         Optimización código TSQL

o   Cursores y Whiles

o   Tablas temporales

o   Variables tipo tabla

o   Convert_implicit

o   Vistas e impacto en la performance.

o   Triggers e impacto en la performance.

o   Funciones escalares e impacto en la performance.

o   UNION vs UNION ALL

o   Exists vs Count

o   Depurar registros de forma eficiente.

o   Hint querys

o   Codigo CLR y performance.

·         Stores procedures

o   Cache e impactos en la performance.

o   Uso eficiente de Stores procedures.

·         Transacciones

o   Uso eficiente de transacciones.

o   Buenas prácticas en el manejo de transacciones

o   Transacciones distribuidas y su impacto en la performance.

·          Bloqueos

o   Tipos de bloqueos

o   Niveles de aislamiento.

o   Uso de Snapshot.

o   Deadlocks

·         Particionamiento de tablas y performance.

·         Cargar datos a una tabla masivamente y de forma eficiente

o   Usando particiones

o   Sin usar particiones.

·         Tablas en memoria (SQL 2014 o 2016)

o   Uso y restricciones de tablas en memoria para aumentar la performance.

o   Uso y restricciones de Stores procedures en memoria

·         Herramientas para detectar y resolver problemas de performance

o   Uso de profiler y análisis de trazas.

o   Uso de Database Tuning Advisor.

o   Activity monitor.

 

Si queres registrate en el mismo acá esta el link

SQL 2016 VIDEOS: System versioned temporal tables

Standard

SQL 2016 incorpora System Versioned Temporal Tables, esta nueva funcionalidad nos permitirá tener un historial de los datos de las tablas pudiendo además consultar la información a una fecha dada.

Es una excelente funcionalidad para aquellos que necesitan guardar un tracking de datos ya sea para auditorias u otras cuestiones.

Temporal-HowWorks

En este video les mostrare esta excelente funcionalidad que ningún programador debería dejar de conocer.

SQL 2016 Temporal Tables Ejemplos

SQL 2012 Service Pack 3

Standard

Apps_Edge_0729_540x304_EN_US

Se libero el SQL Server Service Pack 3 para SQL Server 2012. Este nuevo SP viene con muchas novedades y arreglos.

Acá un resumen:

Performance & Escalabilidad

    • Trace flags 1236 and 9024 were introduced in SQL Server 2012 to address lock contention and log write waits  for systems with high number of transactions. SQL Server 2012 SP3 makes this behavior default without having to use the trace flags. This ensures that your system is able to scale up when required without any user action required. The default behavior was already introduced in SQL Server 2014 Service Pack 1.
    • Improvements to consistency check performance by estimating memory grants correctly and making optimum use of CPU and Memory (KB3029825).
    • Improvements while performing a SELECT INTO operation involving a temporary table. This is a common scenario when database operations involve temporary or staging tables.
    • Added new query hints for Resource Governor through MIN_GRANT_PERCENT and MAX_GRANT_PERCENT (KB3107401). This allows you to leverage these hints while running queries by capping their memory grants to prevent memory contention.
    • Improvements to enhance the performance of opening and reading the (.xel) Extended Event files(KB3112710). This allows you to analyze data faster while troubleshooting SQL Server related issues.
    • If you use spatial data in your application, then SP3 has improvements for spatial query performance (KB3107399).

Diagnostico & Troubleshooting

  • Added new logging capability for Lease Timeout messages so that the current time and the expected renewal times are logged.
  • There is a new error message for lease workers that clearly indicates the reason for the Lease Timeout. This would allow you to troubleshoot failover issues more effectively.
  • Lease stages for Availability Groups are now indicated through new extended events for lease workers.
  • Improvements in non-yield detection logic to prevent false positives for scheduler non-yield messages.
  • Improved diagnostic messages while performing backups using third-party applications that make use of SQLVDI.DLL.
  • Sys.dm_exec_query_stats DMV will now report (KB3107398) information on memory grants, degree of parallelism and the threads used for executing the query. This information is normally available in the XML plan which can be now retrieved using the DMV as well.
  • Spills originating from SORT operations are reported correctly while tracking execution statistics. Now, SET STATISTICS IO option now exposes information about SORT operations.
  • The actual rows read will now be reported in the query execution plans (KB3107397) to help improve query performance troubleshooting. This should negate the need to capture SET STATISTICS IO separately. This now allows you to see information related to a residual predicate pushdown in a query plan.
  • Hash Warning and Sort Warnings now have additional columns to track physical I/O statistics, memory used and rows affected. We also introduced a new hash_splill_details extended event. Now you can track more granular information for your hash and sort warnings (KB3107172). This improvement is also now exposed through the XML Query Plans in the form of a new attribute to the SpillToTempDbType complex type (KB3107400).
  • A new extended event was added to the list of tracing capabilities in SQL Server (query_memory_grant_usage) to track memory grants requested and granted. This provides better tracing and analysis capabilities for troubleshooting query execution issues related to memory grants (KB3107173).

PowerBI desktop actualización de noviembre

Standard

El día 20 de noviembre se libero una nueva versión de PowerBI Desktop.

image

Dentro de sus novedades vamos a encontrar

Report Authoring

  • Play Axis for Scatter Chart
  • Horizontal Slicers
  • Slicer Selection Behaviors (single vs. multi-select)
  • Control Z-order
  • Background Colors for Slides
  • Interactions between Visuals – Subview (tile by)
  • Duplicate Pages
  • Support for KPIs and Images in Tables, Matrices and Cards
  • Better Tooltips on Area Charts & Line Charts
  • Ability to change Text size in Cards & Tables/Matrix
  • Improved tooltips and labels in Field Well and Formatting panes
  • Ability to see Categories with no data
  • Improved Default Sort behaviors for visuals
  • Ability to control Axis Scale display units
  • Visuals Refresh Optimizations when applying basic modeling operations

Data Modeling

  • Basic Automatic Date Features

Data Connectivity

  • SSAS Multidimensional support
  • SAP Hana
  • R Script
  • DirectQuery for SQL Server, Azure SQL DB and Azure SQL DW
  • Azure Data Lake Store
  • Marketo

Data Transformations

  • Improved Function Invocation experience
  • Option to set Credentials at Server vs. Database level
  • Add Prefix/Suffix to a Text column
     

Other Improvements

  • New Documentation Website, now also including localized content.