TechEd 2013: Eron Kelly talks about SQL Server 2014

At TechEd 2013 in New Orleans, Megan Keller, SQL Server Pro Editorial Director, and I met with Eron Kelly, General Manager for SQL Server Marketing, to talk about the upcoming release of SQL Server 2014. The new SQL Server 2014 release will provide several significant new features. Related: Microsoft Announces SQL Server 2014

In-Memory OLTP Engine

The biggest feature in the upcoming SQL Server 2014 release is undoubtedly the In-Memory OLTP Engine (formerly code named Hekaton). The new In-Memory OLTP Engine will help you you to choose which tables go in memory. It will also help you to choose the stored procedure that will be compiled into machine code for high performance execution. EdgeNet, an early adopter, saw a 7X performance increase with no code changes.

New Azure Integration Options

There will also be new Azure integration options for backup and AlwaysOn Availability Groups. The new backup option is integrated into SQL Server Management Studio (SSMS) and it allows you to back up a SQL Server database to Azure. You can also use it to quickly restore to an Azure virtual machine (VM). AlwaysOn Availability Groups have also been extended to Azure, providing AlwaysOn in the cloud. This enables you to create asynchronous Availability Group replicas in Azure for disaster recovery. Like the new Azure backup, the ability to create Azure Availability Groups is integrated in SMSS.

Better Resource Management for Big Data

Another improvement that Kelly discussed with Megan and I is the ability to provide better resource management for big data. There's improved integration with Windows Server 2012’s storage enhancements. SQL Server 2014’s Resource Governor can take advantage of the automated storage-tiering provided by Windows Server 2012.

BI Improvements

Kelly also demonstrated some of the upcoming business intelligence (BI) improvements. Kelly illustrated how Data Explorer can provide new data visualizations, as well as how GEOFlow was able to provide a visual mapping of all the different TechEd attendees. He also pointed out that in the SQL Server 2014 release, PowerView will be able to work against multi-dimensional models in addition to tabular data models.

Significant Changes to Database Engine

For those of you following the SQL Server 2014 release cycle, you might note that the SQL Server 2014 release has skipped the traditional R2 release that Microsoft normally releases between major releases. Kelly explained that this was due to the significant changes that Microsoft needed to make to the database engine to support the new In-Memory OLTP Engine.

SQL Server 2014 Preview Expected in June 2013

Kelly told us that we could expect to see a preview of the SQL Server 2014 released this month, with general availability expected to be available in early 2014.

Source : http://sqlmag.com/blog/live-teched-2013-eron-kelly-talks-about-sql-server-2014

Find Backup history for one week

use msdb

go
SELECT
SERVERPROPERTY('Servername') AS Server_Name,
bs.Database_name,
bs.Backup_start_date,
bs.Backup_finish_date,
bs.Expiration_date,
CASE bs.type
WHEN 'D' THEN 'FULL'
WHEN 'I' THEN 'Differential '
WHEN 'L' THEN 'Log'
END AS Backup_type,
(bs.backup_size/1048576) AS Backup_size_MB ,bmf.Logical_device_name,
bmf.Physical_device_name FROM backupmediafamily AS bmf INNER JOIN backupset AS bs ON bmf.media_set_id = bs.media_set_id WHERE (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY bs.database_name,
bs.backup_finish_date
Reference :http://msdn.microsoft.com/en-us/library/ms186299.aspx

Operating system error 112(error not found) encountered

When you RESTORE database if error message says 112 then you must look at the first point to check enough disk free space on specific drive where database is going to restore.

Message

Error: 17053, Severity: 16, State: 1.

E:\MSSQL \DATA\Adventrueres.mdf: Operating system error 112(error not found) encountered.

Plan Caching and Reuse

Dynamic Management Views and functions are useful when exploring plan reuse and the following objects are most helpful:

sys.dm_exec_cached_plans

sys.dm_exec_query_plan

sys.dm_exec_sql_text

sys.dm_exec_plan_attributes

sys.dm_exec_cached_plan_dependent_object

The following sql query is useful to exploring the current plan cache contents in buffer pool:

 
SELECT DB_NAME( st.dbid) AS DatabaseName,
st.dbid AS Database_ID,
cp.objtype AS PlanType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.refcounts AS ReferenceCounts,
cp.usecounts AS UseCounts,
st.text AS SQLBatch,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY 

 sys.dm_exec_query_plan(cp.plan_handle) AS qp

CROSS APPLY 

 sys.dm_exec_sql_text(cp.plan_handle) AS st

WHERE st.[dbid] = DB_ID()

ORDER BY UseCounts DESC;

Rebuilding all indexes on a table and specifying options

If you are planning rebuild index  to use this statement below , you must run update statistics statement after rebuild index. Statistics are always updated when you rebuild index.  But STATISTICS_NORECOMPUTE=ON disable the auto update statistics from updating the specific statistics for an index (or column-level statistics)

-- Try to avoid this options
USE AdventureWorks2012;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);GO

UPDATE STATISTICS (Production.Product)
GO

(OR)

Alternatively you can use below options

-- By default STATISTICS_NORECOMPUTE = OFF

USE AdventureWorks2012;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = OFF);

--  For single index options
ALTER INDEX[IDX_INDEX_NAME] ON [dbo].[Product]   REBUILD WITH (STATISTICS_NORECOMPUTE=OFF)


Source: Microsoft needs to correct this page.
http://technet.microsoft.com/en-us/library/ms188388.aspx

Microsoft® SQL Server® 2012 Service Pack 1

Microsoft has released Service Pack 1 for SQL Server 2012.  You can download from the following link:  http://www.microsoft.com/en-us/download/details.aspx?id=35575

Schedule to Rebuild Index for your user database

Here is the script you can use to Rebuild Index job to be scheduled to run specific time.


USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBA Index Maintenance_MyDBTest',
  @enabled=1,
  @notify_level_eventlog=0,
  @notify_level_email=2,
  @notify_level_netsend=0,
  @notify_level_page=0,
  @delete_level=0,
  @description=N'MyDBTest',
  @category_name=N'Database Maintenance',
  @owner_login_name=N'sa',
  @notify_email_operator_name=N'DBA_Mail', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'ReIndex Maintenance_MyDBTest',
  @step_id=1,
  @cmdexec_success_code=0,
  @on_success_action=1,
  @on_success_step_id=0,
  @on_fail_action=2,
  @on_fail_step_id=0,
  @retry_attempts=0,
  @retry_interval=0,
  @os_run_priority=0, @subsystem=N'TSQL',
  @command=N'USE MyDBTest
GO
-- Ensure a USE statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag INTO #work_to_do FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'') WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;
-- 40 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 40.0
            SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REORGANIZE'';
        IF @frag >= 40.0
            SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REBUILD WITH ( ONLINE = ON )'';
        IF @partitioncount > 1
            SET @command = @command + N'' PARTITION='' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N''Executed: '' + @command;
    END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO
exec sp_updatestats
go',
  @database_name=N'master',
  @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'ReIndex Maintenance_MyDBTest',
  @enabled=1,
  @freq_type=8,
  @freq_interval=64,
  @freq_subday_type=1,
  @freq_subday_interval=0,
  @freq_relative_interval=0,
  @freq_recurrence_factor=1,
  @active_start_date=20120116,
  @active_end_date=99991231,
  @active_start_time=201000,
  @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO