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

 

How to remove Key Lookup on your query plan

Key Lookup was Introduced in SQL Server 2005 Service Pack 2, the Key Lookup operator is a bookmark lookup on a table with a clustered index. The Argument column(Predicate) contains the name of the clustered index and the clustering key used to look up the row in the clustered index. Key Lookup is always accompanied by a Nested Loops operator. Query performance can be improved by adding a covering index on nonclustered index.

When you found a key lookup for a query execution plan window. The easiest way to remove key lookup from the execution plan is to add covering index for the specific column into that non-clustered in the table.

use AdventureWorks
go
sp_helpindex [Sales.SalesOrderDetail]

--Find list of index currently on table  [Sales.SalesOrderDetail]

index_name index_description index_keys
AK_SalesOrderDetail_rowguid nonclustered, unique located on PRIMARY rowguid
IX_SalesOrderDetail_ProductID nonclustered located on PRIMARY ProductID
PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID clustered, unique, primary key located on PRIMARY SalesOrderID, SalesOrderDetailID

-- Run the this two queries with display estimated execution plan

Select ProductID 

From Sales.SalesOrderDetail 

Where ProductID = 776

go

select ProductID, OrderQty 

from Sales.SalesOrderDetail 

where ProductID =776

go

As you can see Key Lookup operator for second query. How would you simply remove from the query plan.?  Add an QrderQty column into INCLUDE on non clustered Index idx_SalesOrderDetail.

As you can see the Key Lookup has been removed from display execution plan

Source : Craig Freedman thoughts from his blog

Bookmark lookup is not a cheap operation.  Assuming (as is commonly the case) that there is no correlation between the non-clustered and clustered index keys, each bookmark lookup performs a random I/O into the clustered index.  Random I/Os are very expensive.  When comparing various plan alternatives including scans, seeks, and seeks with bookmark lookups, the optimizer must decide whether it is cheaper to perform more sequential I/Os and touch more rows using an index scan or a seek with a less selective predicate that covers all required columns or to perform fewer random I/Os and touch fewer rows using a seek with a more selective predicate and a bookmark lookup.

How do you find SQL Server version info

There are five place you can find a SQL Server version information.

--1 select @@version

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) Apr 22 2011 11:57:00 Copyright
(c) Microsoft Corporation Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

--2 exec sp
exec sys.sp_MSgetversion
10.50.1617.0 1 3

 -- 3 SQL Server Logs
Date 09/01/2013 07:12:16Log SQL Server (Current - 09/01/2013 17:18:00)Source
ServerMessage
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) Apr 22 2011 11
:57:00 Copyright
(c) Microsoft CorporationEnterprise Evaluation Edition
on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

-- 4 SERVERPROPERTY
SELECT SERVERPROPERTY('edition'),SERVERPROPERTY('ProductVersion'),SERVERPROPERTY('ProductLevel')Enterprise Evaluation Edition 10.50.1617.0 RTM

--5 exec sys.xp_msver

Still you can find sql server version information from Windows Registry but i do not want to expose more on it here.

The service could not be started. Reason: Service 'MSSQLServerOLAPService' start request failed

Problem:When i try reinstall Microsoft SQL Server Analysis Services it's keep failing.

Error Message: The service could not be started. Reason:  Service 'MSSQLServerOLAPService' start request failed.
 Configuration file:C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20121217_132025\ConfigurationFile.ini
Detailed results:
  Feature:                       Analysis Services
  Status:                        Failed: see logs for details
  MSI status:                    Passed
  Configuration status:          Failed: see details below
  Configuration error code:      0xD448CDD2
  Configuration error description: The service could not be started. Reason:  Service 'MSSQLServerOLAPService' start request failed.
  Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20121217_132025\Detail.txt

Workaround :
Open the Event Viewer(Administrative Tools) and Clear out all logs for
Application
Security
System
Finally restart your SQL Server Analysis Services via SQL Server Configuration Manager

References :http://richbackbi.wordpress.com/2010/12/03/mssqlserverolapservice-start-request-failed/
http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/thread/dd04e9d8-e613-44bf-bf4a-a446efdc64aa

SQL Server Encryption

Encryption is the process of obfuscating data by the use of a key or password. This can make the data useless without the corresponding decryption key or password. Encryption does not solve access control problems. However, it enhances security by limiting data loss even if access controls are bypassed. For example, if the database host computer is misconfigured and a hacker obtains sensitive data, that stolen information might be useless if it is encrypted.

First of all run below T-SQL statment to get current value
select * from sys.dm_exec_connections
To find Encript_option column value ( It should be FALSE)

1. Open the SQL server Configuration Manager
2. SQL Server Network Configuration
   ( Protocols for MSSQLSERVER)  Right click to see propertise
3. You will see three tabs(Flags,Certificate,Advanced)
4. click Flags tab
Force encryption ( default is No)
 change to Yes

Then it is required to restart SQL Server service
Run the following  T-SQL statment
select * from sys.dm_exec_connections
To find encript_option column value ( It should be TRUE)

Important Note
Although encryption is a valuable tool to help ensure security, it should not be considered for all data or connections. When you are deciding whether to implement encryption, consider how users will access data. If users access data over a public network, data encryption might be required to increase security. However, if all access involves a secure intranet configuration, encryption might not be required. Any use of encryption should also include a maintenance strategy for passwords, keys, and certificates.
Source : http://technet.microsoft.com/en-us/library/bb510663.aspx

Pages and Extents in SQL Server

In SQL server data stored on page. Disk I/O operations are performed at the page level. A single page is consist of 8KB (8192 Bytes) and Eight physical contiguous pages are one Extents.

One Data Page (8KB) = 8192 Bytes

Page Header       =   96 Bytes
Data Rows         = 8060 Bytes
Row Offset Array  =   36 Bytes

Total Bytes per page is 8192 Bytes



If you want to read more about page and extents please check out this below Microsoft links
http://msdn.microsoft.com/en-us/library/ms190969(v=sql.105).aspx
Source : Microsoft