Microsoft SQL Server

How to remove Key Lookup on your query plan

2013-01-11T13:36:00.000Z

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

select ProductID, OrderQty

from Sales.SalesOrderDetail

where ProductID =776

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

2013-01-09T18:03:00.000Z

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

2012-12-17T14:03:00.002Z

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

2012-12-10T09:18:00.000Z

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

2012-11-21T13:42:00.000Z

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

Know your Snapshot database in SQL Server

2012-11-19T18:52:00.000Z

A snapshot database is a read-only purpose and static view of source database. The database snapshot is transactionally consistent with the source database as of the moment of the snapshot's creation.

1. How do you create snapshot Database? Using

T-SQL on Query Analyser

CREATE DATABASE [AdventureWorks_dbss1843] ON

( NAME = N'AdventureWorks_Data', FILENAME = N'E:\MSSQL\Data\AdventureWorks_data_1800.ss' )

AS SNAPSHOT OF [AdventureWorks]

2. Does snapshot database contain log file?

3. If my snapshot database become suspect can you fix it ?

4. Can you backup/restore snapshot database?

5. Can you change recovery model for snapshot database?

6. Can i create table/view in the snapshot database?

7. Can i use SSMS to create a snapshot database?

Source: Microsoft & Paul S Randal(SQLSkills)

E-Book Gallery for Microsoft Technologies

2012-11-10T14:18:00.003Z

Big Thanks to Monica Rush for her contribution to publish Microsoft eBooks, these eBooks are free you can download it. There are more than 50 ebooks available here.

Download content for ASP.NET, Office, SQL Server, Windows Azure, SharePoint Server and other Microsoft technologies in e-book formats. Reference, guide, and step-by-step information are all available. All the e-books are free. New books will be posted as they become available.

To view white papers for Microsoft technologies, click here.
To view community and partner content for Microsoft technologies, click here.
To learn more about how to install and read e-book content on your device, click here.

To learn about new free e-books for Microsoft technologies, follow us on Twitter here .
To learn about new white papers for Microsoft technologies, follow us on Twitter here .

To provide feedback on the page or to suggest new content, click here.

Source : Microsoft Technet

http://social.technet.microsoft.com/wiki/contents/articles/11608.e-book-gallery-for-microsoft-technologies.aspx

Introducing Microsoft SQL Server 2012 free ebook

2012-11-10T13:54:00.000Z

Complete version of Introducing Microsoft SQL Server 2012, by Ross Mistry and Stacia Misner is now ready as a free download! You can download the PDF version of this title below link (288 pages; 10.8 MB).

http://blogs.msdn.com/b/microsoft_press/archive/2012/03/15/free-ebook-introducing-microsoft-sql-server-2012.aspx

Restore Database to Point in Time (STOPAT)

2012-11-07T14:49:00.001Z

RESTORE LOG ... WITH RECOVERY, STOPAT option allows you to restore database backup to a point in time. This option gives you the ability to restore a database backup file prior to an event that occurred.
Each of these transactions has a LSN (log sequence number) along with a timestamp, so when you restoring the transaction log you have the ability to tell SQL Server where to stop reading transactions that need to be restored.

For expample my database got currupted at -- 13:58.
I have full backup at 13:00 and Transaction log backup at 14:06
I want to restore the log at this point 'Nov 07, 2012 01:57:00 PM'

Use AdventureWorks

-- Full Backup has taken 13:00

Select * from Emp where EmployeeID =5;

--Title =Tool Designer
--13:46

UPDATE Emp SET Title ='Tool Designer Engineer' where EmployeeID =5;

GO

Select * from Emp where EmployeeID =6;

--Title =Marketing Manager
-- 13:52

UPDATE Emp SET Title ='Assit Marketing Manager' where EmployeeID =6;

-- Database has been corrupted
-- 13:58

Select * from Emp where EmployeeID =7;

--Title =Production Supervisor - WC60
-- 14:01

UPDATE Emp SET Title ='Production Supervisor' where EmployeeID =7 ;

-- 14:06 Transaction Backup completed

-- Restore Full Database @ 13:00 with NORECOVERY

use master

RESTORE DATABASE AdventureWorks FROM

DISK = 'E:\MSSQL\Backup\FULL\AdventureWorks_7_Nov2012_13h0m_Full_1.BAK',

DISK = 'E:\MSSQL\Backup\FULL\AdventureWorks_7_Nov2012_13h0m_Full_2.BAK',

DISK = 'E:\MSSQL\Backup\FULL\AdventureWorks_7_Nov2012_13h0m_Full_3.BAK'

WITH NORECOVERY , STATS = 5

-- Point in time restore

-- Restoring now Transaction log backup WITH RECOVERY, STOPAT = 'Nov 07, 2012 01:57:00 PM'

RESTORE LOG AdventureWorks

FROM DISK ='E:\MSSQL\Backup\TRAN\AdventureWorks_7_Nov2012_14h6m_TRAN_1.BAK'

WITH RECOVERY, STOPAT = 'Nov 07, 2012 01:57:00 PM' , STATS = 5
GO

select EmployeeID,NationalIDNumber, ManagerID,Title

from Emp where EmployeeID IN(5,6,7)

Thanks to Viewers of this blog

2012-11-06T18:17:00.000Z

I would like to say thanks to all my readers/viewers of this blog. I hope this blog helps you to either fix your SQL problem or learning new things in your life. I have decided to post at least 50 post per year and each post I am spending around 20 minutes to write it that means 50 posts x 20 minutes = 1000(minutes)/60 Minutes = 16.66 hours per year (Two working days) in my life spending here for SQL Community. Thanks very much for your valuable feedbacks and contacting me by email, I am trying to answer your question asap, If i get chance to open my email box. So far my blog has had more than 60000 viewers visited.

The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B

2012-10-30T11:53:00.004Z

Error: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running.
When you try to run ssis package you will get this above error only if you are not started Distributed transaction coordinator.

Make sure to start the Distributed Transaction Coordinator via services

Consider referencing only non-nullable values in SUM. ISNULL() may be useful for this

2012-10-17T19:53:00.002+01:00

Error:
Msg 8662, Level 16, State 0, Line 1
Cannot create the clustered index "SumSales_Summary_TestIndex" on view "AdventureWorks.dbo.vSumSales_Summary_TestByProductLine" because the view references an unknown value (SUM aggregate of nullable expression). Consider referencing only non-nullable values in SUM. ISNULL() may be useful for this.

-- Table design
CREATE TABLE [dbo].[Sales_Summary_Test](
[SaleID] [int] IDENTITY(1,1) NOT NULL,
[OrderDate] [datetime] NULL,
[ProductLine] [nvarchar](2) NULL,
[SubTotal] [numeric](38, 6) NULL) ON [PRIMARY]
GO

-- Creating a View
CREATE VIEW dbo.vSumSales_Summary_TestByProductLine
     WITH SCHEMABINDING
AS
SELECT s.ProductLine AS ProductLine,
COUNT_BIG(*) AS NumberOfProductLine,
SUM(s.SubTotal) AS SubTotal
FROM [dbo].[Sales_Summary_Test] AS s
WHERE ProductLine IS NOT NULL
GROUP BY s.ProductLine
go

SELECT * FROM vSumSales_Summary_TestByProductLine
go

-- Create a Index for view
CREATE UNIQUE CLUSTERED INDEX SumSales_Summary_TestIndex
ON dbo.vSumSales_Summary_TestByProductLine(ProductLine)
go

Msg 8662, Level 16, State 0, Line 1
Cannot create the clustered index "SumSales_Summary_TestIndex" on view "AdventureWorks.dbo.vSumSales_Summary_TestByProductLine" because the view references an unknown value (SUM aggregate of nullable expression). Consider referencing only non-nullable values in SUM. ISNULL() may be useful for this.

Solution:
You must change NULL column to NOT NULL column
CREATE TABLE [dbo].[Sales_Summary_Test](
[SaleID] [int] IDENTITY(1,1) NOT NULL,
[OrderDate]   [datetime] NOT NULL,
[ProductLine] [nvarchar](2) NOT NULL,
[SubTotal]    [numeric](38, 6) NOT NULL) ON [PRIMARY]
GO

Names must be in two-part format and an object cannot reference itself

2012-10-17T16:07:00.000+01:00

When you try to create a Indexed view for the query you will get this following error message in this case. if you include database on from clause.

CREATE VIEW dbo.vSales_Summary_ProductLine
WITH SCHEMABINDING
AS
SELECT S.ProductLine AS ProductLine,
COUNT_BIG(*) AS NumberOfProductLine,
SUM(S.SubTotal) AS SubTotal
FROM [AdventureWorks].[dbo].[Sales_Summary] AS S
GROUP BY S.ProductLine
go

Msg 4512, Level 16, State 3, Procedure vSales_Summary_TestByProductLine, Line 4
Cannot schema bind view 'dbo.vSSales_Summary_ProductLine' because name 'AdventureWorks.dbo.Sales_Summary' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Solution : You need to remove the database name from the above script

CREATE VIEW dbo.vSales_Summary_ProductLine
WITH SCHEMABINDING
AS
SELECT S.ProductLine AS ProductLine,
COUNT_BIG(*) AS NumberOfProductLine,
SUM(S.SubTotal) AS SubTotal
FROM [dbo].[Sales_Summary] AS S
GROUP BY S.ProductLine
go

How to find index usage against table

2012-10-13T11:46:00.002+01:00

If you want to find out index usage for user table then sys.indexes and sys.dm_db_index_usage_stats that give you more details. You should look for user seeks counts on this result

Select OBJECT_NAME(i.object_id) AS table_name,

i.name AS index_name,i.type_desc,
ius.user_seeks,ius.last_user_seek
From sys.indexes i
Inner Join sys.dm_db_index_usage_stats ius
on i.object_id = ius.object_id
and i.index_id = ius.index_id
and ius.database_id = DB_ID()
where ius.object_id = OBJECT_ID('dbo.Customers')
go

SQL Server 4.2

2012-10-09T11:54:00.002+01:00

SQL Books Collection

2012-10-08T17:29:00.002+01:00

SQL 2000 Books

2012-10-08T17:25:00.000+01:00

SQL Server 2000 is best friend for DBAs

SQL 7.0 Books

2012-10-08T17:21:00.002+01:00

The version SQL Server 7.0 are new era for SQL Server life for DBAs, I was one fortunate to read this books

SQL 6.5 Books

2012-10-08T17:19:00.000+01:00

This is one of my favourite SQL Server 6.5 Book and SQL Server Performance Optimization and Tuning Handbook

SQL Server Connections 2012 at the Bellagio in Las Vegas

2012-10-05T19:45:00.001+01:00

Oct 29-Nov 1 at the Bellagio in Las Vegas for the hottest industry event fo this year 2012:

SQL Server Connections is packed with sessions from expert speakers providing you with valuable information that you can take back to work and put into practice immediately.

http://www.devconnections.com/shows/fall2012/default.aspx?s=191

ps1 cannot be loaded because the execution of scripts is disabled on this system

2012-09-29T15:30:00.002+01:00

If your powershell script failed test.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see "get-help about_signing" for more details. At line:1 char:19
1. First run the powershell command

Get-ExecutionPolicy

You will see Restricted; if then

2. Enable powershell to run the below

Set-ExecutionPolicy RemoteSigned

You will see RemoteSigned:

Your powershell script will work now onwards

SQL Server 2012 Express Editions

2012-09-20T13:06:00.003+01:00

Microsoft also released a new set of SQL Server 2012 Express editions. SQL Server Express is a free edition of SQL Server, and it’s used as the internal database for hundreds of third-party products.
Although it's free, however, it also has several limitations. Compute-wise, the new SQL Server 2012 Express editions are limited to the lesser of one socket or four cores. The editions are also limited in size. The maximum memory for the database engine is limited to 1GB and the maximum relational database size is 10GB.
Although SQL Server Express is limited in scalability, a common misconception is that it’s limited to a single database or 10 connections. Neither of these is true. SQL Server Express can support multiple databases where each database can be up to 10GB. The myth about the number of connections is probably a holdover from the early days of MDSE (Microsoft Database Engine—the first version of the desktop SQL Server Engine that was released in the SQL Server 2000 timeframe.)
SQL Server 2012 Express has no limits on incoming connections or workload other than the CPU and memory limitations. And it comes in both 32-bit and 64-bit versions. You can download all of the SQL Server 2012 Express editions from Microsoft’s website.

Reference : http://www.microsoft.com/betaexperience/pd/SQLEXPCTAV2/enus/default.aspx

Microsoft Confirms October 25 Launch Event in NYC for Windows 8

2012-09-19T16:13:00.002+01:00

While Windows 8 will be made generally available via software upgrades and new PC purchases starting October 26, Microsoft confirmed today that it will hold a launch event for the product on October 25 in New York City. This confirms reports that the firm would hold a “midnight madness” event the night before in order to celebrate the launch and let customers get their hands on Windows 8 as soon as possible.

My Windows Weekly co-host Mary Jo Foley wrote about the October 25 event in Mark your Microsoft calendars: Windows 8, Windows Phone 8 launch dates revealed last month. “October 25, the day before general availability, will be Microsoft's big launch event for Windows 8 and the Surface RT,” she wrote. “And that event will be in New York City, the site of most recent previous Windows launches. Windows 8 and the Surface RT ARM-based devices should be available (at least technically) at midnight, following the launch event.”

Partitioning existing table SQL2008R2

2012-09-19T15:38:00.000+01:00

Create partioning for existing table with more than 1120962253 rows today. It was amazing to see the query performance.

Make sure to create a files groups ([PARTITION_FG1], [PARTITION_FG2],[PARTITION_FG3],[PARTITION_FG4] for partioned table
Also you can create Separate file group for Non-Clustered Index on different drive NCINDEX_FG5

USE [TestDB]
GO

--Step 1. Creating a Partition Function
CREATE PARTITION FUNCTION PFSvrId_Left (numeric (10,0))
AS RANGE LEFT FOR VALUES (399, 499, 699, 799);

--The result for this RANGE LEFT assignment is:
--{min … 399}, {400 … 499}, {500 … 699}, {799 … max}

--Step 2. Creating a Partition Scheme
CREATE PARTITION SCHEME SvrIdScheme
AS PARTITION PFSvrId_Left
TO ([PARTITION_FG1], [PARTITION_FG2],[PARTITION_FG3],[PARTITION_FG4],[PRIMARY])

--Step 3. CREATE CLUSTERED INDEX 1

-- Now create a Partitioned using clustered index based Scheme
CREATE CLUSTERED INDEX [idx_LoadID] ON [dbo].[MyTable]
(
[M_ID] ASC
) ON SvrIdScheme(s_id)

-- Step 4. CREATE NONCLUSTERED INDEX 2 on Separate file group
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED
( [t_id] ASC, [s_id] ASC
)ON [NCINDEX_FG5]
GO

--– Check for new partitions
SELECT partition_id, object_id, partition_number, rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('MyTable')
GO

Creating a database with multiple file groups

2012-09-18T16:57:00.001+01:00

USE [master]
GO
/****** Object: Database [Testdb] Script Date: 09/18/2012 16:52:59 ******/
CREATE DATABASE [Testdb] ON PRIMARY
( NAME = N'Testdb', FILENAME = N'F:\SQLDBs\Testdb.mdf' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ),
FILEGROUP [TEST_DATA]
( NAME = N'Test_Data', FILENAME = N'F:\SQLDBs\Test_Data.ndf' , SIZE = 5242880KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ),
FILEGROUP [TEST_DATA2]
( NAME = N'Test_Data2', FILENAME = N'F:\SQLDBs\Test_Data2.ndf' , SIZE = 5242880KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ),
FILEGROUP [TEST_INDEX]
( NAME = N'Test_Index', FILENAME = N'F:\SQLDBs\Test_Index.ndf' , SIZE = 5242880KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB )
LOG ON
( NAME = N'Testdb_log', FILENAME = N'G:\SQLLogs\Testdb_log.ldf' , SIZE = 1048576KB , MAXSIZE = 2048GB , FILEGROWTH = 1048576KB )
GO
ALTER DATABASE [Testdb] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Testdb].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO