Thursday, June 23, 2016

SQL 2016 Features - Poster


Aquí les dejo el poster con todos los features del nuevo SQL Server 2016

Es siempre una muy buena fuente de consulta




Download
Microsoft SQL 2016 Infographic

Monday, June 6, 2016

Version actualizada de SP_DBA_CurrentlyExec

Acá les dejo una versión actualizada de SP_DBA_CurrentlyExec


https://onedrive.live.com/redir?resid=1FA872AB07A90D8A!49568&authkey=!ABwpIFtS7ZyHwb8&ithint=folder%2csql


Estos son algunos ejemplos de uso así como también el Change Log

Change Log
-- v4.1 . 2013-08-23
-- v4.2 . 2014-04-23 - Now is a SP
-- v4.3 . 2014-04-25 - Automatically Include Blocking Reports, Open Trans and Server Info
-- v4.31. 2014-05-14 - Fix Elap_Time = 23:59:59
-- v4.32. 2014-05-27 - Fix OpenTran
-- v4.4.  2014-06-10 - Elap_Time includes Days. Default Sort is Elap_Time / Start_Time
-- v4.41. 2014-06-22 - Minor Fixes
-- v4.42. 2014-07-28 - Fix Transaction log usage on named instances
-- v4.43. 2014-08-04 - Add "Rollback" Status
-- v4.5 . 2015-10-25 - Add Object schema and databases.state=0 // Filters
-- v4.6 . 2016-04-15 - Add MultiServer and Blocks
-- v4.7 . 2016-04-19 - Identify Blocking Transactions
-- v4.8 . 2016-06-06 - Excel output

Examples
--[dbo].[SP_DBA_CurrentlyExec]
--[dbo].[SP_DBA_CurrentlyExec] @Filter_Name='object_name',@Filter_value ='SP_BL'
--[dbo].[SP_DBA_CurrentlyExec] @Filter_Name='Host_name',@Filter_value ='NJUMTS'
--[dbo].[SP_DBA_CurrentlyExec] @Filter_Name='Login_Name',@Filter_value ='MyLogin'
--[dbo].[SP_DBA_CurrentlyExec] @Filter_Name='SPID',@Filter_value ='371'
--[dbo].[SP_DBA_CurrentlyExec] @MultiServer=1

--[dbo].[SP_DBA_CurrentlyExec] @Blocks=1
--[dbo].[SP_DBA_CurrentlyExec] @excel=1

Pronto estará disponible una serie de videos explicativos en YouTube como información adicional

Tuesday, May 31, 2016

Bug critico en SQL 2014 SP1 CU6 - No funciona el hint NOLOCK

Microsoft ha anunciado a travez de su Microsoft Release Serives Blog que si su servidor SQL tiene instalado la primer versión de SQL 2014 SP1 CU6 (12.0.4449.0) y se ejecuta un SELECT utilizando el hint NOLOCK y la query se ejecuta en paralelo puede bloquear otras queries.

Esto en un bug muy critico para las personas que utilizan NOLOCK (como por ejemplo yo..)

Microsoft ha retirado el SQL 2014 SP1 CU6 original que había publicado inicialmente y hoy 31/5/2016 lo ha re-publicado con ente problema ya solucionado.

Como darnos cuenta si tenemos el CU6 que no funciona bien?

select @@version

Si la versión instalada es la 12.0.4449.0 significa que hemos instalado la versión con el bug. Microsoft recomienda desinstalarla e instalar la nueva 12.0.4457.0 lo antes posible

Mas info

https://blogs.msdn.microsoft.com/sqlreleaseservices/cumulative-update-6-for-sql-server-2014-sp1/


Nueva versión

https://support.microsoft.com/en-us/kb/3167392

Monday, May 16, 2016

Presentación de SQL Server 2016 en Microsoft Argentina

El día 10 de Mayo se realizo el evento de presentación del nuevo SQL Server 2016 en Microsoft Argentina.

Junto a Federico Marty, Mariano Kovo, Alejandro Cabanchik y Maximiliano Accotto hemos presentado los siguientes temas

Seguridad  & Hybrid
Row Level Security 
Data Masking
Always Encrypted 
StrechDatabase 
Intro to SQL Server sobre Azure 
Always con Azure 
Backup to Azure 
Database Migration to Azure

Desarrollo
Novedades en SSMS
JSON
System Versioned Table
Cambios en TSQL
Novedades en Tablas in memory

Administacion y Performance
Live Query
Query Store
Cambios en el set up
Cambios en Always On
Scope Database Setting


Estas son algunas fotos del evento























Saturday, May 7, 2016

Presentación de SQL Server 2016 en Microsoft Argentina - 10 de Mayo

Amigos,

El día martes 10 de mayo a desde las 9 hs se realizara la presentación del nuevo SQL Server 2016 en Microsoft Argentina (Bouchard 710, Piso 4.Capital Federal)




Los espero!! 

Registracion:
https://www.microsoftevents.com/profile/form/index.cfm?PKformID=0x209210f84d


Agenda
09:00am – 09:30amAcreditacion y Desayuno
09:30am – 12:30pmKeynote y Sesiones
12:30pm – 01:00pmAlmuerzo
01:30pm – 03:00pmCharlas con expertos



Presenters:
Pablo Fernandes 
Director de Unidad de Negocios (Cloud & Enterprise) - Microsoft
Federico Marty 
Solution Specialist Application Platform - Microsoft
Alejandro Cabanchik 
Consultor en Data Warehousing/Business Intelligence. Especialista en Microsoft BI Platform. Fanático de SSAS y Power BI. SmartBI co-founder
Mariano Kovo 
Especialista de SQL Server desde su versión 6.5 – Presidente de SQL PASS Argentina y PRECISION IT (Microsoft Gold Partner Data Platform)
Javier Villegas 
Profesional de SQL Server por más de 15 años - MCP / MCTS - DBA Manager en Mediterranean Shipping Company (MSC)
Maximiliano Accotto
Especialista de SQL Server desde su versión 4.2 - Microsoft MVP Data Platform y Owner en TriggerDB Consulting (Microsoft Gold Partner Data Platform)

Sunday, March 27, 2016

SQL 2016 Automatic TempDB Configuration


SQL 2016 – Automatic TempDB Configuration


Various KBs, whitepapers and blogs have outlined the need for the creation of multiple, TEMPDB files, same sized files, trace flags and the like.  All of these configuration options increase the scalability of your SQL Server.

In an effort to simplify the tempdb configuration experience, SQL Server 2016 setup has been extended to configure various properties for tempdb for multi-processor environments. 

  1. A new tab dedicated to tempdb has been added to the Database Engine Configuration step of setup workflow.
  2. Configuration options:
Data Files
  1. Number of files – this will default to the lower value of 8 or number of logical cores as detected by setup. The value can be increased as required for a specific workload. The file names for secondary data files will follow the tempdb_mssql_#.ndf naming convention where # represents the file ordinal for each additional file.
  2. Initial size  - is specified in MB and applies to each tempdb data file. This makes it easier to configure all files of same size. Total initial size is the cumulative tempdb data file size (Number of files * Initial Size)  that will be created.
  3. Autogrowth – is specified in MB (fixed growth is preferred as opposed to a non-linear percentage based growth) and applies to each file. The default value of 64MB was chosen to cover one PFS interval. Since TF 1117 is enabled by default for tempdb, all files will grow at the same time by the specified size. Total autogrowth reflects the cumulative size the database will grow each time an autogrow is triggered.

Note: If you specify a very large initial size or autogrowth value, ensure that Instant File Initialization is enabled for SQL Server service startup account.

  1. Data directories – specify multiple folders/drives to spread the data files across several volumes. Each file will be placed in a round-robin manner. For example: if you have specified 8 data files and 3 volumes – files 1,4,7 will go to vol 1; files 2,5,8 will go to vol 2; files 3,6 will go to vol 3.
Log File
  1. Initial size  - is specified in MB and log file with that size is created.
  2. Autogrowth – is specified in MB (fixed growth is preferred as opposed to a non-linear percentage based growth)
A default value of 64MB is provided to so that the number of Virtual Log Files (VLFs) during initial creation is a small and manageable number and with appropriate size so that the unused log space can be reclaimed easily.

Enabling all these configuration settings as part of the standard setup experience allows one to deploy and run at peak scalability levels.

The following is a screen capture from SQL Server 2016 CTP3 setup showing the various TEMPDB options and defaults setup allows.

clip_image001